查询语句
语法：
SELECT {* | 字段列表}
[ FROM <表1> <表2>
[ WHERE <表达式>]
[ GROUP BY <group by definition>]
[ HAVING <expression> [{<operator> <expression>} ...]]
[ ORDER BY <order by definition>v]
[ LIMIT [<offset>], <row count>]
];- {* | <字段列表>}包含星号通配符和字段列表，表示查询的字段。其中，字段列表至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不加逗号。
- FROM <表1>,<表2>...，表1和表2表示查询数据的来源，可以是单个或者多个。
- WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
- GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
- ORDER BY <字段>，该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）、降序（DESC）。
- LIMIT [
,] ，该子句告诉MySQL每次显示查询出来的数据条数。
创建表
mysql> create table food(
-> id int primary key auto_increment,
-> name varchar(100),
-> price float default 0,
-> produce_by varchar(100) not null,
-> taste enum('good', 'normal', 'bad')
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc food;
+------------+-----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| price | float | YES | | 0 | |
| produce_by | varchar(100) | NO | | NULL | |
| taste | enum('good','normal','bad') | YES | | NULL | |
+------------+-----------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)插入数据
mysql> insert into food (name, price, produce_by, taste) values
-> ('breed', 10, 'ronie', 'good'),
-> ('noodles', 5, 'jack', 'normal'),
-> ('chicken', 15, 'kelly', 'good'),
-> ('fish', 12, 'lucy', 'bad'),
-> ('rice', 3, 'jerry', 'normal');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
单表查询
查询所有字段：在SELECT语句中使用星号（*）通配符查询所有字段。语法：
SELECT * FROM 表名;。使用星号（*）通配符时，将返回所有列，列按照定义表时候的顺序显示。mysql> select * from food; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 2 | noodles | 5 | jack | normal | | 3 | chicken | 15 | kelly | good | | 4 | fish | 12 | lucy | bad | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 5 rows in set (0.00 sec)查询指定字段：SELECT关键字后面的字段名为将要查找的数据，因此可以将表中所有字段的名称跟在SELECT子句后面，如果忘记了字段名称，可以使用DESC命令查看表的结构。语法：
SELECT 字段名1,字段名2,…,字段名n FROM 表名;mysql> desc food; +------------+-----------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | price | float | YES | | 0 | | | produce_by | varchar(100) | NO | | NULL | | | taste | enum('good','normal','bad') | YES | | NULL | | +------------+-----------------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> select name from food; +---------+ | name | +---------+ | breed | | noodles | | chicken | | fish | | rice | +---------+ 5 rows in set (0.00 sec) mysql> select name, price from food; +---------+-------+ | name | price | +---------+-------+ | breed | 10 | | noodles | 5 | | chicken | 15 | | fish | 12 | | rice | 3 | +---------+-------+ 5 rows in set (0.00 sec) mysql>查询指定记录：数据库中包含大量的数据，根据特殊要求，可能只需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句可以对数据进行过滤。语法：
SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件。WHERE条件判断符：- =：相等
- <>,!=：不等于
- <：小于
- <=：小于等于
- >：大于
- >=：大于等于
- BETWEEN：两者之间
mysql> select name, taste from food where name='fish'; +------+-------+ | name | taste | +------+-------+ | fish | bad | +------+-------+ 1 row in set (0.00 sec) mysql> select name, taste from food where price<10; +---------+--------+ | name | taste | +---------+--------+ | noodles | normal | | rice | normal | +---------+--------+ 2 rows in set (0.00 sec) mysql>带IN关键字的查询：IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。
mysql> select * from food where id in (1,2,3,4); +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 2 | noodles | 5 | jack | normal | | 3 | chicken | 15 | kelly | good | | 4 | fish | 12 | lucy | bad | +----+---------+-------+------------+--------+ 4 rows in set (0.00 sec) mysql> select * from food where id not in (1,4); +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 2 | noodles | 5 | jack | normal | | 3 | chicken | 15 | kelly | good | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 3 rows in set (0.00 sec)带BETWEEN AND的范围查询：BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值。
mysql> select * from food where price between 4 and 11; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 2 | noodles | 5 | jack | normal | +----+---------+-------+------------+--------+ 2 rows in set (0.00 sec) mysql> select * from food where price not between 4 and 11; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 3 | chicken | 15 | kelly | good | | 4 | fish | 12 | lucy | bad | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 3 rows in set (0.00 sec)带LIKE的字符匹配查询：使用LICK通配符进行匹配查找。
百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符
mysql> select * from food; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 2 | noodles | 5 | jack | normal | | 3 | chicken | 15 | kelly | good | | 4 | fish | 12 | lucy | bad | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 5 rows in set (0.00 sec) mysql> select * from food where produce_by like 'j%'; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 2 | noodles | 5 | jack | normal | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 2 rows in set (0.00 sec) mysql> select * from food where produce_by like '%e%'; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 1 | breed | 10 | ronie | good | | 3 | chicken | 15 | kelly | good | | 5 | rice | 3 | jerry | normal | +----+---------+-------+------------+--------+ 3 rows in set (0.00 sec)下划线通配符‘_’，一次只能匹配任意一个字符
mysql> select * from food where produce_by like 'j___'; +----+---------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+---------+-------+------------+--------+ | 2 | noodles | 5 | jack | normal | +----+---------+-------+------------+--------+ 1 row in set (0.00 sec) mysql> select * from food where produce_by like 'j____'; +----+------+-------+------------+--------+ | id | name | price | produce_by | taste | +----+------+-------+------------+--------+ | 5 | rice | 3 | jerry | normal | +----+------+-------+------------+--------+ 1 row in set (0.00 sec) mysql>
查询空值：数据表创建的时候，设计者可以指定某列中是否包含空值（NULL）。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。
mysql> alter table food add taster varchar(100); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update food set taster='ronie' where id in (2,5); Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from food; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 1 | breed | 10 | ronie | good | NULL | | 2 | noodles | 5 | jack | normal | ronie | | 3 | chicken | 15 | kelly | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | | 5 | rice | 3 | jerry | normal | ronie | +----+---------+-------+------------+--------+--------+ 5 rows in set (0.00 sec) mysql> select * from food where taster is null; +----+---------+-------+------------+-------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+-------+--------+ | 1 | breed | 10 | ronie | good | NULL | | 3 | chicken | 15 | kelly | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | +----+---------+-------+------------+-------+--------+ 3 rows in set (0.00 sec) mysql> select * from food where taster is not null; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 2 | noodles | 5 | jack | normal | ronie | | 5 | rice | 3 | jerry | normal | ronie | +----+---------+-------+------------+--------+--------+ 2 rows in set (0.00 sec)带AND的多条件查询：使用SELECT查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开。
mysql> select * from food where taster is not null and produce_by like 'ja%'; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 2 | noodles | 5 | jack | normal | ronie | +----+---------+-------+------------+--------+--------+ 1 row in set (0.00 sec) mysql> select * from food where price <= 10 and taster is null; +----+-------+-------+------------+-------+--------+ | id | name | price | produce_by | taste | taster | +----+-------+-------+------------+-------+--------+ | 1 | breed | 10 | ronie | good | NULL | +----+-------+-------+------------+-------+--------+ 1 row in set (0.00 sec)带OR的多条件查询：与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
mysql> select * from food where price <= 10 or taste='good'; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 1 | breed | 10 | ronie | good | NULL | | 2 | noodles | 5 | jack | normal | ronie | | 3 | chicken | 15 | kelly | good | NULL | | 5 | rice | 3 | jerry | normal | ronie | +----+---------+-------+------------+--------+--------+ 4 rows in set (0.00 sec) mysql> select * from food where taster is not null or taste='good' and produce_by='kelly'; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 2 | noodles | 5 | jack | normal | ronie | | 3 | chicken | 15 | kelly | good | NULL | | 5 | rice | 3 | jerry | normal | ronie | +----+---------+-------+------------+--------+--------+ 3 rows in set (0.00 sec) mysql>查询结果不重复：使用DISTINCT关键字指示MySQL消除重复的记录值。
mysql> select distinct price from food; +-------+ | price | +-------+ | 10 | | 5 | | 15 | | 12 | | 3 | +-------+ 5 rows in set (0.00 sec)对查询结果排序
单列排序：使用ORDER BY子句对指定的列数据进行排序。使用DESC关键字进行降序排列，使用ASC关键字进行升序排列。ASC是默认的排序方式。
mysql> select * from food order by price; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 5 | rice | 3 | jerry | normal | ronie | | 2 | noodles | 5 | jack | normal | ronie | | 1 | breed | 10 | ronie | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | | 3 | chicken | 15 | kelly | good | NULL | | 6 | duck | 15 | mike | normal | lucy | +----+---------+-------+------------+--------+--------+ 6 rows in set (0.00 sec) mysql> select * from food order by price desc; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 3 | chicken | 15 | kelly | good | NULL | | 6 | duck | 15 | mike | normal | lucy | | 4 | fish | 12 | lucy | bad | NULL | | 1 | breed | 10 | ronie | good | NULL | | 2 | noodles | 5 | jack | normal | ronie | | 5 | rice | 3 | jerry | normal | ronie | +----+---------+-------+------------+--------+--------+ 6 rows in set (0.00 sec) mysql> select * from food order by price asc; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 5 | rice | 3 | jerry | normal | ronie | | 2 | noodles | 5 | jack | normal | ronie | | 1 | breed | 10 | ronie | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | | 3 | chicken | 15 | kelly | good | NULL | | 6 | duck | 15 | mike | normal | lucy | +----+---------+-------+------------+--------+--------+ 6 rows in set (0.00 sec)多列排序：对多列数据进行排序，要将需要排序的列之间用逗号隔开。
mysql> select * from food order by price, name desc; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 5 | rice | 3 | jerry | normal | ronie | | 2 | noodles | 5 | jack | normal | ronie | | 1 | breed | 10 | ronie | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | | 6 | duck | 15 | mike | normal | lucy | | 3 | chicken | 15 | kelly | good | NULL | +----+---------+-------+------------+--------+--------+ 6 rows in set (0.00 sec) mysql>
分组查询：分组查询是对数据按照某个或多个字段进行分组。
- MySQL中使用GROUP BY关键字对数据进行分组，语法：
[GROUP BY 字段] [HAVING <条件表达式>]。GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。 - 使用WITH ROLLUP关键字，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
- 使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中再根据第2个字段的值进行分组，以此类推。
- ORDER BY用来对查询的记录排序，如果和GROUP BY一起使用可以完成对分组的排序。
mysql> insert into food (name, price, produce_by, taste, taster) values -> ('meat', 12, 'ronie', 'good', 'lucy'), -> ('fish', 12, 'mike', 'normal', 'ronie'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from food; +----+---------+-------+------------+--------+--------+ | id | name | price | produce_by | taste | taster | +----+---------+-------+------------+--------+--------+ | 1 | breed | 10 | ronie | good | NULL | | 2 | noodles | 5 | jack | normal | ronie | | 3 | chicken | 15 | kelly | good | NULL | | 4 | fish | 12 | lucy | bad | NULL | | 5 | rice | 3 | jerry | normal | ronie | | 6 | duck | 15 | mike | normal | lucy | | 7 | meat | 12 | ronie | good | lucy | | 8 | fish | 12 | mike | normal | ronie | +----+---------+-------+------------+--------+--------+ 8 rows in set (0.00 sec) mysql> select price, count(price) as num from food group by price; +-------+-----+ | price | num | +-------+-----+ | 10 | 1 | | 5 | 1 | | 15 | 2 | | 12 | 3 | | 3 | 1 | +-------+-----+ 5 rows in set (0.00 sec) mysql> select price, group_concat(name) as food_name from food group by price; +-------+----------------+ | price | food_name | +-------+----------------+ | 3 | rice | | 5 | noodles | | 10 | breed | | 12 | fish,meat,fish | | 15 | chicken,duck | +-------+----------------+ 5 rows in set (0.00 sec) mysql> select price, group_concat(name) as food_name from food group by price having count(name) > 1; +-------+----------------+ | price | food_name | +-------+----------------+ | 12 | fish,meat,fish | | 15 | chicken,duck | +-------+----------------+ 2 rows in set (0.00 sec) mysql> select price, group_concat(name) as food_name from food group by price with rollup; +-------+------------------------------------------------+ | price | food_name | +-------+------------------------------------------------+ | 3 | rice | | 5 | noodles | | 10 | breed | | 12 | fish,meat,fish | | 15 | chicken,duck | | NULL | rice,noodles,breed,fish,meat,fish,chicken,duck | +-------+------------------------------------------------+ 6 rows in set (0.00 sec) mysql> select group_concat(name) as food_name,price from food group by price, name; +-----------+-------+ | food_name | price | +-----------+-------+ | rice | 3 | | noodles | 5 | | breed | 10 | | fish,fish | 12 | | meat | 12 | | chicken | 15 | | duck | 15 | +-----------+-------+ 7 rows in set (0.00 sec) mysql> select group_concat(name) as food_name,price from food group by price order by price desc; +----------------+-------+ | food_name | price | +----------------+-------+ | chicken,duck | 15 | | fish,meat,fish | 12 | | breed | 10 | | noodles | 5 | | rice | 3 | +----------------+-------+ 5 rows in set (0.00 sec)- MySQL中使用GROUP BY关键字对数据进行分组，语法：
使用LIMIT限制查询结果的数量：SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回第一行或者前几行，可使用LIMIT关键字，
LIMIT [位置偏移量,] 行数。mysql> select group_concat(name) as food_name,price from food group by price order by price desc limit 1; +--------------+-------+ | food_name | price | +--------------+-------+ | chicken,duck | 15 | +--------------+-------+ 1 row in set (0.00 sec) mysql> select group_concat(name) as food_name,price from food group by price order by price desc limit 1,2; +----------------+-------+ | food_name | price | +----------------+-------+ | fish,meat,fish | 12 | | breed | 10 | +----------------+-------+ 2 rows in set (0.00 sec)
Comments | NOTHING