SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [MAX_STATEMENT_TIME = N]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr [, select_expr ...]

    [FROM table_references

      [PARTITION partition_list]

    [WHERE where_condition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_condition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC], ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [INTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

    [FOR UPDATE | LOCK IN SHARE MODE]]

mysql> select * from temp;

解析：*代表所有列，temp代表表名，不带条件就查询所有数据

mysql> select name,age from temp where age = ;

解析：查询name和age这两列，age 等于22的数据。

mysql> select age+,age/,age-,age* from temp where age- > ;

mysql> select PLAYRNO,AMOUNT,AMOUNT*6.5 “Ren Min Bi” from PENALTIES;

解析：查询AMOUNT列数据乘6.5并改列名为Ren Min Bi

使用小括号可以改变运算的优先级

mysql> select NAME,concat(TOWN,STREET,HOUSENO) “player Home Address” from PLAYERS;

解析：利用concat函数将TOWN,STREET,HOUSENO三列的字符串连接起来，别名列为player Home Address(可以在concat里加' '进行分隔)

注意：concat和null进行连接，会导致连接后的数据成为null

mysql> select * from t1;

+------+--------+--------+

| id   | First  | Last   |

+------+--------+--------+

|     | zhang  | jiacai |

|     | linghu | NULL   |

+------+--------+--------+

mysql> select id,concat(First,' ',Last) from t1;

+------+------------------------+

| id   | concat(First,' ',Last) |

+------+------------------------+

|     | zhang jiacai           |

|     | NULL                   |

+------+------------------------+

mysql> select id,concat_ws(':',First,Last) "Full Name" from t1;

+------+--------------+

| id   | Full Name    |

+------+--------------+

|     | zhang:jiacai |

|     | linghu       |

+------+--------------+

圆括号里的第一个位置用来指定字符串连接的分隔符

mysql> select id as 'num' from t1;

mysql> select id 'num',First from t1;

mysql> select * from t2;

+------+

| num  |

+------+

|     |

|     |

|     |
|     |

+------+

mysql> select distinct num from t2;

+------+

| num  |

+------+

|     |

|     |

+------+

distinct 多列：去重的是同行多列组合的重复数据

mysql> select distinct id, age from temp;

where语句中的条件比较：大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>

mysql> select * from tableName where a> or a>= or a< or a<= or a= or a<>;

mysql> select * from temp where age> and name=‘jack’;

解析：查询name等于jack并且年龄大于20的

mysql> select * from tmep where name=‘jack’ or name=‘jackson’;

解析：查询name是jack或是jackson的

mysql> select * from temp where not (age > );

解析：取小于等于20的数据

mysql> select * from temp where id not in(, );

解析：查询id数不是1，也不是2的

mysql> select * form temp where age between  and ;

解析：查询age在20和25之间的

mysql> select * from temp where id in (, , );

解析：查询id在括号中出现的数据

%:替代0个或多个字符

_：替代一个字符

mysql> select * from temp where name like ‘j%’;

解析：查询name以j开头的(%通配所有)

mysql> select * from temp where name like ‘%k%’;

解析：查询name包含k的

escape转义

mysql> select * from temp where name like ‘\_%’ escape ‘\’;

解析：指定\为转义字符，上面的就可以查询name中以“_”开头的数据

mysql> select * from temp where name is null;

解析：查询为null的数据

mysql> select * from temp where name is not null;

解析：查询不为null的数据

mysql> select * from temp order by id;  (默认asc升排序)

mysql> select * from temp order by id desc;  (指定降序排)

多列组合

mysql> select * from temp order by id, age;

mysql> select playerno,name from players order by playerno asc limit ,;

mysql> select playerno,name from players order by playerno asc limit  offset ;

解析：跳过前面的3行，从第4行开始取，取5行

mysql> select salary,count(*) from salary_tab

    -> where salary>=

    -> group by salary

    -> having count(*)>=;

+---------+----------+

| salary  | count(*) |

+---------+----------+

| 2000.00 |         |

| 3000.00 |         |

+---------+----------+