一、查询语句SELECT

语法:

SELECT *|字段 FROM 表名 [WHERE 条件判断] [ORDER BY 字段] [GROUP BY 字段] [HAVING 条件判断] [LIMIT OFFSET COUNT]

查询顺序:

from,where,group by,having,having只能在group by后面使用,where不能查询聚合函数,而having可以,且运算顺序为如果两者同时存在,where在having之前进行筛选顺序

创建一个fruits表,进行查询练习

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 |
+------+----------------------+---------+*/

评论




正在载入...
PoweredHexo
HostedAliyun
DNSAliyun
ThemeVolantis
UV
PV
BY-NC-SA 4.0