7.1 单表查询
7.1.1查询所有字段
SELECT * FROM 表名;
7.1.2 查询指定记录
使用WHERE子句进行数据过滤。
SELECT 字段名1,字段名2……
FROM 表名
WHERE 条件;
mysql> SELECT f_id,f_name
-> FROM fruits
-> WHERE price>10;
+------+------------+
| f_id | f_name |
+------+------------+
| 103 | banana |
| 104 | mango |
| 106 | watermelon |
+------+------------+
3 rows in set (0.00 sec)
7.1.3 带IN关键字的查询
满足IN条件范围内的一个值(或多个)即可。
mysql> SELECT f_id,f_name,price
-> FROM fruits
-> WHERE price IN (10,20);
+------+------------+-------+
| f_id | f_name | price |
+------+------------+-------+
| 101 | orange | 10 |
| 104 | mango | 20 |
| 106 | watermelon | 20 |
+------+------------+-------+
3 rows in set (0.00 sec)
7.1.4 带BETWEEN AND的范围查询
闭区间。
mysql> SELECT *
-> FROM fruits
-> WHERE price BETWEEN 8 AND 15;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 101 | orange | 10 |
| 102 | melon | 8 |
| 103 | banana | 12 |
+------+--------+-------+
3 rows in set (0.00 sec)
- 加not
mysql> SELECT *
-> FROM fruits
-> WHERE price NOT BETWEEN 8 AND 15;
+------+------------+-------+
| f_id | f_name | price |
+------+------------+-------+
| 100 | apple | 5 |
| 104 | mango | 20 |
| 105 | water | 5 |
| 106 | watermelon | 20 |
+------+------------+-------+
4 rows in set (0.00 sec)
7.1.5 带LIKE的字符匹配查询
- %,匹配任意长度的字符,包括零字符
mysql> SELECT *
-> FROM fruits
-> WHERE f_name LIKE "a%pple";
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM fruits
-> WHERE f_name LIKE "a%";
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
- _,匹配任意一个字符
mysql> SELECT *
-> FROM fruits
-> WHERE f_name LIKE "appl_";
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
+------+--------+-------+
1 row in set (0.00 sec)
7.1.6 查询空值
IS NULL和IS NOT NULL
mysql> SELECT *
-> FROM fruits
-> WHERE price IS NULL;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 107 | pear | NULL |
+------+--------+-------+
1 row in set (0.01 sec)
mysql> SELECT * FROM fruits WHERE price IS NOT NULL;
+------+------------+-------+
| f_id | f_name | price |
+------+------------+-------+
| 100 | apple | 5 |
| 101 | orange | 10 |
| 102 | melon | 8 |
| 103 | banana | 12 |
| 104 | mango | 20 |
| 105 | water | 5 |
| 106 | watermelon | 20 |
+------+------------+-------+
7 rows in set (0.00 sec)
7.1.7 带AND的多条件查询
类似于交集,可以通过添加多个AND添加多个条件。
mysql> SELECT * FROM fruits WHERE f_id IN (100,101,102) AND price>5;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 101 | orange | 10 |
| 102 | melon | 8 |
+------+--------+-------+
2 rows in set (0.01 sec)
7.1.8 带OR的多条件查询
并集,可以添加多个。
mysql> SELECT * FROM fruits WHERE f_id IN (100,101) OR price>12;
+------+------------+-------+
| f_id | f_name | price |
+------+------------+-------+
| 100 | apple | 5 |
| 101 | orange | 10 |
| 104 | mango | 20 |
| 106 | watermelon | 20 |
+------+------------+-------+
4 rows in set (0.00 sec)
OR和AND可以一起使用,注意AND优先级高。
7.1.9 查询结果不重复
SELECT DISTINCT 字段名 FROM 表名;
mysql> SELECT price from fruits;
+-------+
| price |
+-------+
| 5 |
| 10 |
| 8 |
| 12 |
| 20 |
| 5 |
| 20 |
| NULL |
+-------+
8 rows in set (0.00 sec)
mysql> SELECT DISTINCT price FROM fruits;
+-------+
| price |
+-------+
| 5 |
| 10 |
| 8 |
| 12 |
| 20 |
| NULL |
+-------+
个人理解:若有多个字段名,所有都一样才会消除重复记录。例如只查询id,则id一样的会被消除,查询id,price,只有id,price值都一样才会被消除。
7.1.10 对查询结果排序
使用ORDER BY 对查询结果进行排序,ASC为升序排列(默认),DESC为降序。字母升序:A~Z。
- 多列排序:首先先按第一列排,再按第二列排。第一列必须要有相同的值,才会对第二列进行排序,如果第一列的值都是唯一的,即所有值都不同,则不再对第二列排序。
mysql> SELECT f_name,price FROM fruits ORDER BY price,f_name;
+------------+-------+
| f_name | price |
+------------+-------+
| pear | NULL |
| apple | 5 |
| water | 5 |
| melon | 8 |
| orange | 10 |
| banana | 12 |
| mango | 20 |
| watermelon | 20 |
+------------+-------+
8 rows in set (0.00 sec)
- 指定排序方向:
mysql> SELECT price FROM fruits ORDER BY price DESC;
+-------+
| price |
+-------+
| 20 |
| 20 |
| 12 |
| 10 |
| 8 |
| 5 |
| 5 |
| NULL |
+-------+
8 rows in set (0.00 sec)
- 对多列指定排序方向:
注意:若要让多个字段都按降序排列,则应在每个后面加DESC。
7.1.11 分组查询
通常与MAX(),MIN(),COUNT(),SUM(),AVG()等一起使用。
[GROUP BY 字段] [HAVING <条件表达式>
通过HAVING增加条件
mysql> SELECT price,COUNT(*) FROM fruits GROUP BY price HAVING price>10;
+-------+----------+
| price | COUNT(*) |
+-------+----------+
| 12 | 1 |
| 20 | 2 |
+-------+----------+
2 rows in set (0.00 sec)
- GROUP_CINCAT()函数:将每个分组中的值显示出来。
mysql> SELECT price,GROUP_CONCAT(f_name) AS name FROM fruits GROUP BY price;
+-------+------------------+
| price | name |
+-------+------------------+
| NULL | pear |
| 5 | apple,water |
| 8 | melon |
| 10 | orange |
| 12 | banana |
| 20 | mango,watermelon |
+-------+------------------+
6 rows in set (0.00 sec)
- WITH_ROLLUP():在所有记录后加一条记录,用于计算查询出的所有记录的总和。
mysql> SELECT price,GROUP_CONCAT(f_name) AS name FROM fruits GROUP BY price WITH ROLLUP;
+-------+-------------------------------------------------------+
| price | name |
+-------+-------------------------------------------------------+
| NULL | pear |
| 5 | apple,water |
| 8 | melon |
| 10 | orange |
| 12 | banana |
| 20 | mango,watermelon |
| NULL | pear,apple,water,melon,orange,banana,mango,watermelon |
+-------+-------------------------------------------------------+
7 rows in set (0.01 sec)
- 多字段分组:分组从左到右进行,先按第一个字段分组,再在第一个字段分组中根据第二个字段分组,以此类推。
SELECT * FROM fruits GROUP BY price,f_id;
- GROUP BY 和ORDER BY 一起使用
mysql> SELECT * FROM fruits GROUP BY price ORDER BY f_id;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
| 101 | orange | 10 |
| 102 | melon | 8 |
| 103 | banana | 12 |
| 104 | mango | 20 |
| 107 | pear | NULL |
+------+--------+-------+
6 rows in set (0.00 sec)
注意:当使用ROLLUP时,不能同时使用ORDER BY ,即ROLLUP和ORDER BY 互相排斥。
7.1.12 使用LIMIT限制查询结果的数量
这里位置偏移量为从第几个开始,例如为2,则从第3条数据开始,不写则为零,从第一条数据开始。
LIMIT [位置偏移量,] 行数
mysql> SELECT * FROM fruits LIMIT 4;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
| 101 | orange | 10 |
| 102 | melon | 8 |
| 103 | banana | 12 |
+------+--------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM fruits LIMIT 2,4;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 102 | melon | 8 |
| 103 | banana | 12 |
| 104 | mango | 20 |
| 105 | water | 5 |
+------+--------+-------+
4 rows in set (0.00 sec)
7.2 使用集合函数查询
- COUNT()函数:返回某列的行数。
COUNT(*) 计算表中总的行数,不管某列有数值还是NULL。
COUNT(字段名)计算指定列下总得行数,会忽略NULL。
mysql> SELECT COUNT(*) FROM fruits;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(price) FROM fruits;
+--------------+
| COUNT(price) |
+--------------+
| 7 |
+--------------+
1 row in set (0.00 sec)
- SUM()函数:求和函数,返回指定列值得总和。(忽略值为NULL的行)
mysql> SELECT SUM(price) FROM fruits;
+------------+
| SUM(price) |
+------------+
| 80 |
+------------+
1 row in set (0.00 sec)
- AVG()函数:求平均值
mysql> SELECT AVG(price) FROM fruits;
+------------+
| AVG(price) |
+------------+
| 11.4286 |
+------------+
1 row in set (0.00 sec)
- MAX()函数:返回指定列中最大值。(可以为字母,a~z,z最大)
mysql> SELECT MAX(f_name) FROM fruits;
+-------------+
| MAX(f_name) |
+-------------+
| watermelon |
+-------------+
1 row in set (0.00 sec)
- MIN()函数:返回查询列中最小值。
mysql> SELECT MIN(f_name) FROM fruits;
+-------------+
| MIN(f_name) |
+-------------+
| apple |
+-------------+
1 row in set (0.00 sec)
上述函数都可以与GROUP BY 一起使用,相应的计算各个分组中的值。
7.3 连接查询
7.3.1 内连接(等值连接)
使用INNER JOIN 语法进行查询,ON后面加条件。(使用WHERE 在某些时候会影响查询的性能)
mysql> SELECT fruits.* ,person.* FROM fruits INNER JOIN person WHERE f_id=p_id;
+------+--------+-------+------+--------+-------------+
| f_id | f_name | price | p_id | p_name | address |
+------+--------+-------+------+--------+-------------+
| 100 | apple | 5 | 100 | Tom | Tom Street |
| 101 | orange | 10 | 101 | Lucy | Lucy Street |
| 102 | melon | 8 | 102 | Jack | NULL |
+------+--------+-------+------+--------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT fruits.* ,person.* FROM fruits INNER JOIN person ON f_id=p_id;
+------+--------+-------+------+--------+-------------+
| f_id | f_name | price | p_id | p_name | address |
+------+--------+-------+------+--------+-------------+
| 100 | apple | 5 | 100 | Tom | Tom Street |
| 101 | orange | 10 | 101 | Lucy | Lucy Street |
| 102 | melon | 8 | 102 | Jack | NULL |
+------+--------+-------+------+--------+-------------+
7.3.2 自连接
一个连接查询中涉及的两张表都是同一张表
mysql> select * from fruits f1,fruits f2 where f1.f_id=f2.f_id+3;
+------+------------+-------+------+--------+-------+
| f_id | f_name | price | f_id | f_name | price |
+------+------------+-------+------+--------+-------+
| 103 | banana | 12 | 100 | apple | 5 |
| 104 | mango | 20 | 101 | orange | 10 |
| 105 | water | 5 | 102 | melon | 8 |
| 106 | watermelon | 20 | 103 | banana | 12 |
| 107 | pear | NULL | 104 | mango | 20 |
+------+------------+-------+------+--------+-------+
5 rows in set (0.00 sec)
7.3.3 外连接查询
- 左外连接(LEFT JOIN):返回左表中的所有要查询的记录和右表中符合条件的记录。
mysql> SELECT * FROM fruits LEFT OUTER JOIN person ON f_id=p_id;
+------+------------+-------+------+--------+-------------+
| f_id | f_name | price | p_id | p_name | address |
+------+------------+-------+------+--------+-------------+
| 100 | apple | 5 | 100 | Tom | Tom Street |
| 101 | orange | 10 | 101 | Lucy | Lucy Street |
| 102 | melon | 8 | 102 | Jack | NULL |
| 103 | banana | 12 | NULL | NULL | NULL |
| 104 | mango | 20 | NULL | NULL | NULL |
| 105 | water | 5 | NULL | NULL | NULL |
| 106 | watermelon | 20 | NULL | NULL | NULL |
| 107 | pear | NULL | NULL | NULL | NULL |
+------+------------+-------+------+--------+-------------+
8 rows in set (0.00 sec)
- 右外连接(RIGHT JOIN):返回右表中所有要查询的记录和左表中符合条件的记录。
mysql> SELECT * FROM fruits RIGHT OUTER JOIN person ON f_id= p_id;
+------+--------+-------+------+--------+-------------+
| f_id | f_name | price | p_id | p_name | address |
+------+--------+-------+------+--------+-------------+
| 100 | apple | 5 | 100 | Tom | Tom Street |
| 101 | orange | 10 | 101 | Lucy | Lucy Street |
| 102 | melon | 8 | 102 | Jack | NULL |
+------+--------+-------+------+--------+-------------+
3 rows in set (0.00 sec)
- 复合条件连接查询:通过AND等增加条件。
mysql> SELECT * FROM fruits INNER JOIN person ON f_id=p_id+3 AND price<12;
+------+--------+-------+------+--------+---------+
| f_id | f_name | price | p_id | p_name | address |
+------+--------+-------+------+--------+---------+
| 105 | water | 5 | 102 | Jack | NULL |
+------+--------+-------+------+--------+---------+
1 row in set (0.00 sec)
7.4 子查询
一个查询语句嵌套在另一个查询语句的内部的查询。先计算子查询。
7.4.1带ANY,SOME关键字的子查询
ANY和SOME作用一样,子查询先返回一个结果,再进行比较,满足任意一个就返回一个结果。
mysql> SELECT num FROM tbl1 WHERE num> ANY(SELECT num2 FROM tbl2);
+------+
| num |
+------+
| 3 |
| 5 |
| 7 |
+------+
3 rows in set (0.00 sec)
7.4.2 带ALL关键字的子查询
类似于ANY,SOME,但要同时满足所有内层查询的条件。
mysql> SELECT num2 FROM tbl2 WHERE num2>ALL(SELECT num FROM tbl1);
+------+
| num2 |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
7.4.3 带EXISTS关键字的子查询
- EXISTS关键字后面的参数是一个任意的子查询,子查询返回至少一行,则EXISTS结果为TRUE,那么外层查询将进行查询;如果子查询没有返回任何行,则EXISTS结果为FALSE,那么外层查询将不再进行。
mysql> SELECT * FROM fruits WHERE EXISTS (SELECT p_id FROM person WHERE p_id=100);
+------+------------+-------+
| f_id | f_name | price |
+------+------------+-------+
| 100 | apple | 5 |
| 101 | orange | 10 |
| 102 | melon | 8 |
| 103 | banana | 12 |
| 104 | mango | 20 |
| 105 | water | 5 |
| 106 | watermelon | 20 |
| 107 | pear | NULL |
+------+------------+-------+
8 rows in set (0.00 sec)
- NOT EXISTS和EXISTS使用方法相同,但结果相反。
7.4.4 带IN关键字的子查询
- IN
mysql> SELECT f_id FROM fruits WHERE price IN (SELECT price FROM fruits WHERE price>12);
+------+
| f_id |
+------+
| 104 |
| 106 |
+------+
2 rows in set (0.01 sec)
- NOT IN:和IN 作用相反。
mysql> SELECT f_id FROM fruits WHERE price NOT IN (SELECT price FROM fruits WHERE price>12);
+------+
| f_id |
+------+
| 100 |
| 101 |
| 102 |
| 103 |
| 105 |
+------+
5 rows in set (0.01 sec)
7.4.5 带比较运算符的子查询
<,<=,>,>=,<>,=
mysql> SELECT p_name FROM person WHERE p_id=(SELECT f_id FROM fruits WHERE f_name="apple");
+--------+
| p_name |
+--------+
| Tom |
+--------+
1 row in set (0.00 sec)
7.5 合并查询结果
两个表对应的列数和数据类型必须相同。使用UNION或UNION ALL。
- UNION ,会删除重复的记录
- UNION ALL,不删除重复行,不会对结果自动排序
mysql> SELECT f_id,f_name FROM fruits
-> UNION ALL
-> SELECT p_id,p_name FROM person;
+------+------------+
| f_id | f_name |
+------+------------+
| 100 | apple |
| 101 | orange |
| 102 | melon |
| 103 | banana |
| 104 | mango |
| 105 | water |
| 106 | watermelon |
| 107 | pear |
| 100 | Tom |
| 101 | Lucy |
| 102 | Jack |
+------+------------+
11 rows in set (0.00 sec)
7.6为表和字段取别名
- 为表取别名:
表名 [AS] 表别名
(不能与数据库中其他表名冲突)
mysql> SELECT * FROM fruits f WHERE f.price<10;
+------+--------+-------+
| f_id | f_name | price |
+------+--------+-------+
| 100 | apple | 5 |
| 102 | melon | 8 |
| 105 | water | 5 |
+------+--------+-------+
3 rows in set (0.00 sec)
- 为字段取别名:
列名 [AS] 列别名
mysql> SELECT f_id ID,f_name NAME FROM fruits WHERE price<10;
+-----+-------+
| ID | NAME |
+-----+-------+
| 100 | apple |
| 102 | melon |
| 105 | water |
+-----+-------+
3 rows in set (0.00 sec)