【MYSQL】select from

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)