基础SELECT实例

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 = 22;解析：查询name和age这两列，age 等于22的数据。

mysql> select age+2,age/2,age-2,age*2 from temp where age-2 > 22;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进行连接，会导致连接后的数据成为nullmysql> select * from t1;+------+--------+--------+| id   | First  | Last   |+------+--------+--------+|    1 | zhang  | jiacai ||    2 | linghu | NULL   |+------+--------+--------+mysql> select id,concat(First,' ',Last) from t1;+------+------------------------+| id   | concat(First,' ',Last) |+------+------------------------+|    1 | zhang jiacai           ||    2 | NULL                   |+------+------------------------+

mysql> select id,concat_ws(':',First,Last) "Full Name" from t1;+------+--------------+| id   | Full Name    |+------+--------------+|    1 | zhang:jiacai ||    2 | linghu       |+------+--------------+圆括号里的第一个位置用来指定字符串连接的分隔符

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

mysql> select * from t2;+------+| num  |+------+|    1 ||    1 ||    1 |
|    2 |+------+mysql> select distinct num from t2;+------+| num  |+------+|    1 ||    2 |+------+distinct 多列：去重的是同行多列组合的重复数据mysql> select distinct id, age from temp;

where语句中的条件比较：大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>mysql> select * from tableName where a>2 or a>=3 or a<5 or a<=6 or a=7 or a<>0;

mysql> select * from temp where age>20 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);解析：取小于等于20的数据mysql> select * from temp where id not in(1, 2);解析：查询id数不是1，也不是2的

mysql> select * form temp where age between 20 and 25;解析：查询age在20和25之间的

mysql> select * from temp where id in (1, 2, 3);解析：查询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 3,5; mysql> select playerno,name from players order by playerno asc limit 5 offset 3;解析：跳过前面的3行，从第4行开始取，取5行

mysql> select salary,count(*) from salary_tab    -> where salary>=2000    -> group by salary    -> having count(*)>=0;+---------+----------+| salary  | count(*) |+---------+----------+| 2000.00 |        1 || 3000.00 |        1 |+---------+----------+