- select 语法
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ]
- select from
mysql> desc t_user; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | uid | int(11) | NO | PRI | NULL | auto_increment | | uname | varchar(20) | YES | | NULL | | | tel | varchar(20) | YES | | NULL | | | lvl | char(4) | YES | | NULL | | | salart | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> select * from t_user; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 1 | alex | 23131231 | A | 1100 | | 2 | jet | 21341517 | A | 4300 | | 3 | kit | 87416574 | B | 1200 | | 4 | poker | 41874521 | C | 1560 | +-----+-------+----------+------+--------+ 4 rows in set (0.00 sec) mysql> select uid,uname from t_user; +-----+-------+ | uid | uname | +-----+-------+ | 1 | alex | | 2 | jet | | 3 | kit | | 4 | poker | +-----+-------+ 4 rows in set (0.00 sec)
- select from where
select ... from tb_name where-clause [others-clause]
where-clause:布尔条件表达式 = # 等值比较 <=> # 等值比较,包括与NULL的安全比较 <>或!= # 不等值比较 <,<=,>,>= # 其它比较符 IS NULL # 是否为空值 IS NOT NULL # 是否不为空值 LIKE # 支持的通配符有%和_ RLIKE或REGEXP # 正则表达式匹配 IN # 判指定字段的值是否在给定的列表中 BETWEEN … AND … # 在某取值范围内
mysql> select * from t_user where uid=2; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 2 | jet | 21341517 | A | 4300 | +-----+-------+----------+------+--------+ 1 row in set (0.00 sec) mysql> select * from t_user where salart>1300; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 2 | jet | 21341517 | A | 4300 | | 4 | poker | 41874521 | C | 1560 | +-----+-------+----------+------+--------+ 2 rows in set (0.00 sec) mysql> select * from t_user where uname='jet'; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 2 | jet | 21341517 | A | 4300 | +-----+-------+----------+------+--------+ 1 row in set (0.00 sec) mysql> select * from t_user where lvl='A'; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 1 | alex | 23131231 | A | 1100 | | 2 | jet | 21341517 | A | 4300 | +-----+-------+----------+------+--------+ 2 rows in set (0.00 sec) mysql> select * from t_user where uid in (2,3,4); +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 2 | jet | 21341517 | A | 4300 | | 3 | kit | 87416574 | B | 1200 | | 4 | poker | 41874521 | C | 1560 | +-----+-------+----------+------+--------+ 3 rows in set (0.00 sec) mysql> select * from t_user where uname in ('jet','kit'); +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 2 | jet | 21341517 | A | 4300 | | 3 | kit | 87416574 | B | 1200 | +-----+-------+----------+------+--------+ 2 rows in set (0.00 sec)
- 组合条件查询
NOT,! # 非 AND,&& # 和 OR,|| # 或
mysql> select * from t_user where lvl='A' OR salart > 1300 -> ; +-----+-------+----------+------+--------+ | uid | uname | tel | lvl | salart | +-----+-------+----------+------+--------+ | 1 | alex | 23131231 | A | 1100 | | 2 | jet | 21341517 | A | 4300 | | 4 | poker | 41874521 | C | 1560 | +-----+-------+----------+------+--------+ 3 rows in set (0.00 sec)
- 聚合函数查询
SUM() # 求总和 AVG() # 求平均值 MAX() # 求最大值 MIN() # 求最小值 COUNT() # 求记录总数 #注:count(*)效率最低,可指定某一字段求总数,如count(Name)
mysql> select max(salart) from t_user; +-------------+ | max(salart) | +-------------+ | 4300 | +-------------+ 1 row in set (0.00 sec) mysql> select uid,max(salart) from t_user; +-----+-------------+ | uid | max(salart) | +-----+-------------+ | 1 | 4300 | +-----+-------------+ 1 row in set (0.00 sec) mysql> select AVG(salart) from t_user; +-------------+ | AVG(salart) | +-------------+ | 2040.0000 | +-------------+ 1 row in set (0.00 sec)