一、查询语句SELECT
语法:
SELECT *|字段 FROM 表名 [WHERE 条件判断] [ORDER BY 字段] [GROUP BY 字段] [HAVING 条件判断] [LIMIT OFFSET COUNT]
查询顺序:
from,where,group by,having,having只能在group by后面使用,where不能查询聚合函数,而having可以,且运算顺序为如果两者同时存在,where在having之前进行筛选顺序
mysql> CREATE TABLE fruits(f_id CHAR(10) NOT NULL,
s_id INT NOT NULL,
f_name VARCHAR(255) NOT NULL,
f_price DECIMAL(8,2) NOT NULL,
PRIMARY KEY(f_id));
mysql> INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES
('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a2',103,'apricot',2.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.7),
('m2',105,'pear',2.6),
('t4',107,'peach',3.6),
('m3',105,'durian',30.3),
('b5',107,'haw',3.6),
('t2',102,'grape',5.3);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
1、单表查询
1)where查询练习
关键字
AND 并且
OR 或者
查询水果名和水果的单价
mysql> SELECT f_name,f_price FROM fruits;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| haw | 3.60 |
| orange | 11.20 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| mango | 15.70 |
| pear | 2.60 |
| durian | 30.30 |
| coconut | 9.20 |
| banana | 10.30 |
| grape | 5.30 |
| peach | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
查询水果表中价格为10.2的水果记录
mysql> SELECT * FROM fruits WHERE f_price=10.2;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
+------+------+------------+---------+
查询水果表中名字是‘mango‘的水果记录
mysql> SELECT * FROM fruits WHERE f_name='mango';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| m1 | 106 | mango | 15.70 |
+------+------+--------+---------+
查询水果表中价格在5到10之间的水果,显示水果名和价格
/*方法一*/
mysql> SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 5 AND 10;
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| berry | 7.60 |
| melon | 8.20 |
| lemon | 6.40 |
| coconut | 9.20 |
| grape | 5.30 |
+---------+---------+
/*方法二*/
mysql> SELECT f_name,f_price FROM fruits WHERE f_price >= 5 AND f_price <= 10;
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| berry | 7.60 |
| melon | 8.20 |
| lemon | 6.40 |
| coconut | 9.20 |
| grape | 5.30 |
+---------+---------+
查询水果表中价格不再5到10之间的水果记录
/*方法一*/
mysql> SELECT * FROM fruits WHERE f_price NOT BETWEEN 5 AND 10;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
/*方法二*/
mysql> SELECT * FROM fruits WHERE f_price<5 OR f_price>10;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
查询水果表中价格小于10,且s_id为102的水果记录
mysql> SELECT * FROM fruits WHERE f_price<10 AND s_id=102;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t2 | 102 | grape | 5.30 |
+------+------+--------+---------+
查询水果表中供应商编号(s_id)是101或者102的记录
/*方法一*/
mysql> SELECT * FROM fruits WHERE s_id=102 OR s_id=101;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
/*方法二*/
mysql> SELECT * FROM fruits WHERE s_id IN (101,102);
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| b1 | 101 | blackberry | 10.20 |
| bs1 | 102 | orange | 11.20 |
| c0 | 101 | cherry | 3.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
+------+------+------------+---------+
查询水果表中价格在5-20之间,且名字以m开头的名字记录
/*方法一*/
mysql> SELECT * FROM fruits WHERE f_price BETWEEN 5 AND 20 AND f_name REGEXP '^m';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs2 | 105 | melon | 8.20 |
| m1 | 106 | mango | 15.70 |
+------+------+--------+---------+
/*方法二*/
mysql> SELECT * FROM fruits WHERE f_price BETWEEN 5 AND 20 AND f_name LIKE 'm%';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs2 | 105 | melon | 8.20 |
| m1 | 106 | mango | 15.70 |
+------+------+--------+---------+
查询水果表中名字还有t或者o或者a的水果记录
/*方法一*/
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[toa]';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
/*方法二*/
mysql> SELECT * FROM fruits WHERE f_name REGEXP 't|o|a';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
查询水果表中名字以abo开头的水果记录
方法一、
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^a' OR f_name REGEXP '^b' OR f_name REGEXP '
^o'; /*或者使用LIKE也可以*/
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
方法二、
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^a|^b|^o';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
方法三、
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^[abo]';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
2)order by排序查询练习
将指定字段进行排序
排序的方式分为升序和降序,默认为升序(ASC),降序为(DESC)
mysql> SELECT * FROM fruits ORDER BY f_price; /*默认为升序,以价格进行排序*/
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a2 | 103 | apricot | 2.20 |
| m2 | 105 | pear | 2.60 |
| c0 | 101 | cherry | 3.20 |
| t4 | 107 | peach | 3.60 |
| b5 | 107 | haw | 3.60 |
| a1 | 101 | apple | 5.20 |
| t2 | 102 | grape | 5.30 |
| l2 | 104 | lemon | 6.40 |
| b2 | 104 | berry | 7.60 |
| bs2 | 105 | melon | 8.20 |
| o2 | 103 | coconut | 9.20 |
| b1 | 101 | blackberry | 10.20 |
| t1 | 102 | banana | 10.30 |
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.70 |
| m3 | 105 | durian | 30.30 |
+------+------+------------+---------+
mysql> SELECT * FROM fruits ORDER BY f_price DESC; /*价格降序排序*/
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| m3 | 105 | durian | 30.30 |
| m1 | 106 | mango | 15.70 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
| b1 | 101 | blackberry | 10.20 |
| o2 | 103 | coconut | 9.20 |
| bs2 | 105 | melon | 8.20 |
| b2 | 104 | berry | 7.60 |
| l2 | 104 | lemon | 6.40 |
| t2 | 102 | grape | 5.30 |
| a1 | 101 | apple | 5.20 |
| b5 | 107 | haw | 3.60 |
| t4 | 107 | peach | 3.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | pear | 2.60 |
| a2 | 103 | apricot | 2.20 |
+------+------+------------+---------+
给多个字段进行排序,为了实现数据最终有序化形态,在不影响第一个字段排序的基础之上,如果第一个字段有重复值再按照第二个字段进行排序,以此类推
mysql> SELECT * FROM fruits ORDER BY f_price DESC,f_name DESC;
/*将价格以降序排序,在有重复值的情况下,
并且不影响前者(价格)排序的情况下,以名字排序,注意看价格中3.6和上面的位置变化*/
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| m3 | 105 | durian | 30.30 |
| m1 | 106 | mango | 15.70 |
| bs1 | 102 | orange | 11.20 |
| t1 | 102 | banana | 10.30 |
| b1 | 101 | blackberry | 10.20 |
| o2 | 103 | coconut | 9.20 |
| bs2 | 105 | melon | 8.20 |
| b2 | 104 | berry | 7.60 |
| l2 | 104 | lemon | 6.40 |
| t2 | 102 | grape | 5.30 |
| a1 | 101 | apple | 5.20 |
| t4 | 107 | peach | 3.60 |
| b5 | 107 | haw | 3.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | pear | 2.60 |
| a2 | 103 | apricot | 2.20 |
+------+------+------------+---------+
3)group by查询分组练习
给指定字段分组,去重复值的过程
如果每个数据都是唯一值,则没有必要分组聚合函数
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
group_concat 将分组过后的数据补充完整
as 名 起别名(用于表头过长)
COUNT(统计)
例:
mysql> SELECT COUNT(*) FROM fruits; /*统计每条记录都不一样的数据由几条*/
+----------+
| COUNT(*) |
+----------+
| 16 |
+----------+
mysql> SELECT s_id,COUNT(*) FROM fruits GROUP BY s_id;
/*对s_id进行分组,并且统计每个id对应的几条记录*/
+------+----------+
| s_id | COUNT(*) |
+------+----------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+----------+
mysql> SELECT s_id,f_name,COUNT(*) FROM fruits GROUP BY s_id;
/*对s_id进行分组,显示s_id对应的f_name(显示的名字不完整),并且统计数量*/
+------+---------+----------+
| s_id | f_name | COUNT(*) |
+------+---------+----------+
| 101 | apple | 3 |
| 102 | orange | 3 |
| 103 | apricot | 2 |
| 104 | berry | 2 |
| 105 | melon | 3 |
| 106 | mango | 1 |
| 107 | haw | 2 |
+------+---------+----------+
mysql> SELECT s_id,GROUP_CONCAT(f_name),COUNT(*) FROM fruits GROUP BY s_id;
/*将分组过后的数据补充完整,查看每个供应商提供的水果种类并统计有几条记录*/
+------+-------------------------+----------+
| s_id | GROUP_CONCAT(f_name) | COUNT(*) |
+------+-------------------------+----------+
| 101 | apple,blackberry,cherry | 3 |
| 102 | grape,banana,orange | 3 |
| 103 | apricot,coconut | 2 |
| 104 | lemon,berry | 2 |
| 105 | pear,durian,melon | 3 |
| 106 | mango | 1 |
| 107 | haw,peach | 2 |
+------+-------------------------+----------+
COUNT(*)和COUNT(字段名)
COUNT(*):将表中的所有字段中,数据最全的一条作为最后的结果,即便一条数据中含有多个空值,
只要有一个字段不为空就会被COUNT(*)记录
COUNT(字段):只统计本字段的非空值的个数,会忽略本字段的空值(NULL)。
mysql> CREATE TABLE a (id INT ,name VARCHAR(30));
mysql> INSERT INTO a VALUES(1,'a'),(2,'b'),(3,NULL),(4,NULL);
mysql> SELECT * FROM a;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | NULL |
| 4 | NULL |
+------+------+
mysql> SELECT COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
mysql> SELECT COUNT(name) FROM a;
+-------------+
| COUNT(name) |
+-------------+
| 2 |
+-------------+
MAX(求最大值)
例:
求水果表价格最高的水果,只显示价格
SELECT MAX(f_price) f_price FROM fruits;
+---------+
| f_price |
+---------+
| 30.30 |
+---------+
SUM(求和)
例:
求水果表中的价格之和,显示价格
mysql> SELECT SUM(f_price) f_price FROM fruits;
+---------+
| f_price |
+---------+
| 134.80 |
+---------+
AVG(求平均值)
求水果表中的价格平均数,显示价格
mysql> SELECT s_id,AVG(f_price) f_price FROM fruits WHERE s_id=101;
+------+----------+
| s_id | f_price |
+------+----------+
| 101 | 6.200000 |
+------+----------+
4)HAVING(二次查询)
查看提供水果种类大于两种的供应商,显示供应商编号,以及提供的水果名
mysql> SELECT s_id,GROUP_CONCAT(f_name)水果种类,COUNT(*)数量 FROM fruits
GROUP BY s_id HAVING COUNT(*)>2;
+------+-------------------------+--------+
| s_id | 水果种类 | 数量 |
+------+-------------------------+--------+
| 101 | apple,blackberry,cherry | 3 |
| 102 | grape,banana,orange | 3 |
| 105 | pear,durian,melon | 3 |
+------+-------------------------+--------+
5)LIMIT(限制输出条件)
LIMIT [OFFSET] COUNT 限制输出的条目数量
OFFSET:位置(偏移量),由0开始计算,不写offset,默认从0开始,0表示第一条数据
COUNT:设定条目数量
mysql> SELECT * FROM fruits LIMIT 1,5;
/*偏移量为1,显示条目数量为5,所以显示的内容从第二条开始显示五条*/
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
+------+------+------------+---------+
求水果表中价格最高的水果记录
mysql> SELECT * FROM fruits ORDER BY f_price DESC LIMIT 1;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| m3 | 105 | durian | 30.30 |
+------+------+--------+---------+
6)DISTINCT给指定字段去重
SELECT DISTINCT 字段 FROM 表名
2、多表查询(连接查询)
1)内连接
(内连接使用比较运算符进行表与表之间的数据比较,列出符合条件的数据行,组成新的数据,如果有不满足要求的数据,将不会进行显示)
INNER JOIN ON 替代 WHERE 条件判断
由于只显示符合条件的数据,所以会造成数据不完整的问题
创建练习表(用来和上一个练习表一同使用)
mysql> CREATE TABLE suppliers
(s_id INT NOT NULL AUTO_INCREMENT,
s_name CHAR(50) NOT NULL,
s_city CHAR(50) NULL,
s_zip CHAR(10) NULL,
s_call VARCHAR(50) NOT NULL,
PRIMARY KEY (s_id));
mysql> INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call) VALUES
(101,'FastFruit Inc','Tianjin',300000,48075),
(102,'LT Suppliers','Chongqing',400000,44333),
(103,'ACME','Shanghai',528437,11111),
(104,'FNK Inc','Zhongshan',30000,22222),
(105,'Good Set','Taiyuan',200000,45678),
(106,'Just Eat Ours','Beijing',010,55555),
(107,'DK Inc','Zhengzhou',450000,33332);
mysql> SELECT * FROM suppliers;
+------+---------------+-----------+--------+--------+
| s_id | s_name | s_city | s_zip | s_call |
+------+---------------+-----------+--------+--------+
| 101 | FastFruit Inc | Tianjin | 300000 | 48075 |
| 102 | LT Suppliers | Chongqing | 400000 | 44333 |
| 103 | ACME | Shanghai | 528437 | 11111 |
| 104 | FNK Inc | Zhongshan | 30000 | 22222 |
| 105 | Good Set | Taiyuan | 200000 | 45678 |
| 106 | Just Eat Ours | Beijing | 10 | 55555 |
| 107 | DK Inc | Zhengzhou | 450000 | 33332 |
+------+---------------+-----------+--------+--------+
连接查询
mysql> SELECT suppliers.s_id,s_name,f_name,f_price
FROM fruits,suppliers
WHERE fruits.s_id=suppliers.s_id;
+------+---------------+------------+---------+
| s_id | s_name | f_name | f_price |
+------+---------------+------------+---------+
| 101 | FastFruit Inc | apple | 5.20 |
| 103 | ACME | apricot | 2.20 |
| 101 | FastFruit Inc | blackberry | 10.20 |
| 104 | FNK Inc | berry | 7.60 |
| 107 | DK Inc | haw | 3.60 |
| 102 | LT Suppliers | orange | 11.20 |
| 105 | Good Set | melon | 8.20 |
| 101 | FastFruit Inc | cherry | 3.20 |
| 104 | FNK Inc | lemon | 6.40 |
| 106 | Just Eat Ours | mango | 15.70 |
| 105 | Good Set | pear | 2.60 |
| 105 | Good Set | durian | 30.30 |
| 103 | ACME | coconut | 9.20 |
| 102 | LT Suppliers | banana | 10.30 |
| 102 | LT Suppliers | grape | 5.30 |
| 107 | DK Inc | peach | 3.60 |
+------+---------------+------------+---------+
/*这三条都是内连接*/
mysql> SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits,suppliers WHERE fruits.s_id=suppliers.s_id;
mysql> SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id;
mysql> SELECT suppliers.s_id,s_name,f_name,f_price FROM fruits JOIN suppliers ON fruits.s_id=suppliers.s_id;
2)外连接
在suppliers中插入一条fruits中没有的s_id
mysql> INSERT INTO suppliers VALUES(108,'jianjian','Linfen',999999,191919);
(1)左连接 LEFT [OUTER] JOIN 返回包括左表在内的所有记录和右表中与之连接字段相等的记录
SELECT * FROM 表名 LEFT [OUTER] JOIN 表名 ON 对应条件判断
会显示左表中的所有数据(suppliers)
mysql> SELECT fruits.s_id,suppliers.s_id,f_name FROM suppliers
LEFT JOIN fruits ON fruits.s_id=suppliers.s_id;
+------+------+------------+
| s_id | s_id | f_name |
+------+------+------------+
| 101 | 101 | apple |
| 103 | 103 | apricot |
| 101 | 101 | blackberry |
| 104 | 104 | berry |
| 107 | 107 | haw |
| 102 | 102 | orange |
| 105 | 105 | melon |
| 101 | 101 | cherry |
| 104 | 104 | lemon |
| 106 | 106 | mango |
| 105 | 105 | pear |
| 105 | 105 | durian |
| 103 | 103 | coconut |
| 102 | 102 | banana |
| 102 | 102 | grape |
| 107 | 107 | peach |
| NULL | 108 | NULL |
+------+------+------------+
(2)右连接 RIGHT [OUTER] JOIN 返回包括右表在内的所有记录和左表中与之连接字段相等的记录
SELECT * FROM 表名 RIGHT [OUTER] JOIN 表名 ON 对应条件判断
会显示右表中的所有数据(fruits)
mysql> SELECT fruits.s_id,suppliers.s_id,f_name FROM suppliers
RIGHT JOIN fruits ON fruits.s_id=suppliers.s_id;
+------+------+------------+
| s_id | s_id | f_name |
+------+------+------------+
| 101 | 101 | apple |
| 103 | 103 | apricot |
| 101 | 101 | blackberry |
| 104 | 104 | berry |
| 107 | 107 | haw |
| 102 | 102 | orange |
| 105 | 105 | melon |
| 101 | 101 | cherry |
| 104 | 104 | lemon |
| 106 | 106 | mango |
| 105 | 105 | pear |
| 105 | 105 | durian |
| 103 | 103 | coconut |
| 102 | 102 | banana |
| 102 | 102 | grape |
| 107 | 107 | peach |
+------+------+------------+
3、子查询(嵌套查询)
只在一个查询语句内嵌套另外一个查询语句(在一条记录中,会存在两个或者两个以上的查询语句)
关键字
ANY
ALL
EXISTS
NOT EXISTS
创建练习表
mysql> CREATE TABLE ex1(num1 INT NOT NULL);
mysql> CREATE TABLE ex2(num2 INT NOT NULL);
mysql> INSERT INTO ex1 VALUES(1),(5),(13),(30);
mysql> INSERT INTO ex2 VALUES(6),(15),(19),(27);
ANY
使用比较运算时,需要满足比较运算要求的其中一个条件即可输出
mysql> SELECT num1 FROM ex1 WHERE num1 > ANY (SELECT num2 FROM ex2);
/*查询ex1表中num1字段中大于ex2的num2字段的任意一个字段值即输出*/
满足任何一个子查询结果
+------+
| num1 |
+------+
| 13 |
| 30 |
+------+
ALL
使用比较运算时,需要满足所有条件才能输出
mysql> SELECT num1 FROM ex1 WHERE num1 > ALL (SELECT num2 FROM ex2);
/*查询ex1表中num1字段中大于ex2的num2字段的所有字段值即输出*/
满足所有子查询结果
+------+
| num1 |
+------+
| 30 |
+------+
EXISTS
mysql> SELECT * FROM fruits WHERE EXISTS (SELECT s_id FROM suppliers WHERE s_id=103);
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
/*如果子查询中的id108在suppliers表中存在,则进行外层查询,返回外层查询结果
(与子查询的结果无关)*/
NOT EXISTS
mysql> SELECT * FROM fruits WHERE NOT EXISTS (SELECT s_id FROM suppliers WHERE s_id=109);
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
+------+------+------------+---------+
/*如果子查询中的id108在suppliers表中不存在,则进行外层查询,返回外层查询结果
(与子查询的结果无关)*/
查询城市在北京的供应商提供哪些水果,显示供应商的编号以及水果名字
mysql> SELECT s_id,f_name FROM fruits WHERE s_id IN
(SELECT s_id FROM suppliers WHERE s_city='Beijing');
+------+--------+
| s_id | f_name |
+------+--------+
| 106 | mango |
+------+--------+
4、联合查询
将两个或者多个查询语句的结果合并显示
语法:
SELECT 字段 FROM 表名
UNION [ALL]
SELECT 字段 FROM 表名
/*带有ALL数据不会做任何更改,直接输出所有内容,不带有ALL将会过滤重复数据*/
UNION联合查询前后的查询语句没有任何联系,要什么查什么
mysql> SELECT * FROM fruits UNION ALL SELECT s_id,s_name,s_zip,s_call FROM suppliers;
+------+---------------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+---------------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | haw | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | pear | 2.60 |
| m3 | 105 | durian | 30.30 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | peach | 3.60 |
| z0 | 102 | starfruit | 10.20 |
| 101 | FastFruit Inc | 300000 | 48075 |
| 102 | LT Suppliers | 400000 | 44333 |
| 103 | ACME | 528437 | 11111 |
| 104 | FNK Inc | 30000 | 22222 |
| 105 | Good Set | 200000 | 45678 |
| 106 | Just Eat Ours | 10 | 55555 |
| 107 | DK Inc | 450000 | 33332 |
+------+---------------+------------+---------+
二、子查询中判断两个字段
求每个供应商提供的价格最高的水果,显示供应商编号,以及水果的名字
/*mysql> SELECT s_id,GROUP_CONCAT(f_name),f_price FROM fruits
WHERE
(s_id,f_price) IN (SELECT s_id,MAX(f_price) FROM fruits GROUP BY s_id)
GROUP BY s_id;
+------+----------------------+---------+
| s_id | GROUP_CONCAT(f_name) | f_price |
+------+----------------------+---------+
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 103 | coconut | 9.20 |
| 104 | berry | 7.60 |
| 105 | durian | 30.30 |
| 106 | mango | 15.70 |
| 107 | haw,peach | 3.60 |
+------+----------------------+---------+*/