1.单表查询：
查询的语法：
select {*|字段} FROM 表名 [WHERE 条件判断] [GROUP BY 字段] [HAVING expr] [ORDER BY 字段 ASC(升序)/DESC(降序)] [limit 偏移量，行数]
1.WHERE子句：
（1）使用where子句来设定查询条件；
（2）where子句中可以指定任何条件
（3）你可以使用AND或者OR指定一个或多个条件
（4）where子句也可以运用于SQL的DELETE或者UPDATE命令
（5）where子句常使用运算符来指定条件
mysql> use TEST2
Database changed
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)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc FRUITS;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| F_ID | char(10) | NO | PRI | NULL | |
| S_ID | int(11) | NO | | NULL | |
| F_NAME | varchar(255) | NO | | NULL | |
| F_PRICE | decimal(8,2) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> INSERT INTO FRUITS VALUES('A',101,'APPLE',5.2),('B1',101,'BLACKBERRY',10.2),('BS1',102,'ORANGE',11.2),('BS2',105,'MELON',8.2),('T1',102,'BANANA',10.3),('T2',102,'GRAPE',5.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.6),('M2',105,'XBABAY',2.6),('M3',105,'XXTT',11.6),('B5',107,'XXWWW',3.6);
Query OK, 15 rows affected (0.00 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM FRUITS;
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| A2 | 103 | APRICOT | 2.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| B2 | 104 | BERRY | 7.60 |
| B5 | 107 | XXWWW | 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.60 |
| M2 | 105 | XBABAY | 2.60 |
| M3 | 105 | XXTT | 11.60 |
| O2 | 103 | COCONUT | 9.20 |
| T1 | 102 | BANANA | 10.30 |
| T2 | 102 | GRAPE | 5.30 |
+------+------+------------+---------+
15 rows in set (0.00 sec)
mysql> SELECT F_NAME,F_PRICE FROM FRUITS;
+------------+---------+
| F_NAME | F_PRICE |
+------------+---------+
| APPLE | 5.20 |
| APRICOT | 2.20 |
| BLACKBERRY | 10.20 |
| BERRY | 7.60 |
| XXWWW | 3.60 |
| ORANGE | 11.20 |
| MELON | 8.20 |
| CHERRY | 3.20 |
| LEMON | 6.40 |
| MANGO | 15.60 |
| XBABAY | 2.60 |
| XXTT | 11.60 |
| COCONUT | 9.20 |
| BANANA | 10.30 |
| GRAPE | 5.30 |
+------------+---------+
15 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_NAME='APPLE';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| A | 101 | APPLE | 5.20 |
+------+------+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_NAME IN ('APPLE','ORANGE');
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| A | 101 | APPLE | 5.20 |
| BS1 | 102 | ORANGE | 11.20 |
+------+------+--------+---------+
2 rows in set (0.01 sec)
mysql> SELECT F_PRICE FROM FRUITS WHERE F_NAME IN ('APPLE','ORANGE');
+---------+
| F_PRICE |
+---------+
| 5.20 |
| 11.20 |
+---------+
2 rows in set (0.00 sec)
mysql> SELECT F_ID,F_NAME,F_PRICE FROM FRUITS WHERE F_NAME IN ('APPLE','ORANGE');
+------+--------+---------+
| F_ID | F_NAME | F_PRICE |
+------+--------+---------+
| A | APPLE | 5.20 |
| BS1 | ORANGE | 11.20 |
+------+--------+---------+
2 rows in set (0.00 sec)
mysql> SELECT F_ID,F_NAME,F_PRICE FROM FRUITS WHERE F_NAME NOT IN ('APPLE','ORANGE');
+------+------------+---------+
| F_ID | F_NAME | F_PRICE |
+------+------------+---------+
| A2 | APRICOT | 2.20 |
| B1 | BLACKBERRY | 10.20 |
| B2 | BERRY | 7.60 |
| B5 | XXWWW | 3.60 |
| BS2 | MELON | 8.20 |
| C0 | CHERRY | 3.20 |
| L2 | LEMON | 6.40 |
| M1 | MANGO | 15.60 |
| M2 | XBABAY | 2.60 |
| M3 | XXTT | 11.60 |
| O2 | COCONUT | 9.20 |
| T1 | BANANA | 10.30 |
| T2 | GRAPE | 5.30 |
+------+------------+---------+
13 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_PRICE BETWEEN 5 AND 15;
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| B2 | 104 | BERRY | 7.60 |
| BS1 | 102 | ORANGE | 11.20 |
| BS2 | 105 | MELON | 8.20 |
| L2 | 104 | LEMON | 6.40 |
| M3 | 105 | XXTT | 11.60 |
| O2 | 103 | COCONUT | 9.20 |
| T1 | 102 | BANANA | 10.30 |
| T2 | 102 | GRAPE | 5.30 |
+------+------+------------+---------+
10 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_PRICE=5.2;
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| A | 101 | APPLE | 5.20 |
+------+------+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_PRICE LIKE 5.20;
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| A | 101 | APPLE | 5.20 |
+------+------+--------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_NAME LIKE 'A%';
+------+------+---------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+---------+---------+
| A | 101 | APPLE | 5.20 |
| A2 | 103 | APRICOT | 2.20 |
+------+------+---------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_NAME LIKE '%N';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| BS2 | 105 | MELON | 8.20 |
| L2 | 104 | LEMON | 6.40 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_NAME LIKE 'APPL_';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| A | 101 | APPLE | 5.20 |
+------+------+--------+---------+
1 row in set (0.01 sec)
2.在mysql的表里面，所谓的空值并不是指字符串“ ”或者0，一般表示数据未知或者在以后添加数据，也会是在添加数据后，其字段上默认为NULL，也就是说你在插入数据时该字段不插入任何值就为NULL。
SELECT * FROM 表名 WHERE 字段名 IS NULL; //查询该字段为NULL的数据
SELECT * FROM 表名 WHERE 字段名 IS NOT NULL;//查询该字段不为NULL的数据
mysql> SELECT * FROM FRUITS WHERE S_ID=101;
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| C0 | 101 | CHERRY | 3.20 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE S_ID=101 AND F_PRICE>5;
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
+------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE S_ID=101 OR F_PRICE>5;
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| B2 | 104 | BERRY | 7.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.60 |
| M3 | 105 | XXTT | 11.60 |
| O2 | 103 | COCONUT | 9.20 |
| T1 | 102 | BANANA | 10.30 |
| T2 | 102 | GRAPE | 5.30 |
+------+------+------------+---------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE S_ID=102 OR F_NAME='MELON';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| BS1 | 102 | ORANGE | 11.20 |
| BS2 | 105 | MELON | 8.20 |
| T1 | 102 | BANANA | 10.30 |
| T2 | 102 | GRAPE | 5.30 |
+------+------+--------+---------+
4 rows in set (0.00 sec)
3.关键字DISTINCT(过滤掉重复的结果)
mysql> SELECT S_ID FROM FRUITS;
+------+
| S_ID |
+------+
| 101 |
| 103 |
| 101 |
| 104 |
| 107 |
| 102 |
| 105 |
| 101 |
| 104 |
| 106 |
| 105 |
| 105 |
| 103 |
| 102 |
| 102 |
+------+
15 rows in set (0.00 sec)
mysql> SELECT DISTINCT S_ID FROM FRUITS;
+------+
| S_ID |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)
4.关键字order by（给查询的结果进行排序）
ORDER BY 字段 ASC(升序)/DESC(降序)
order by 可以同时对多个字段进行排序，但是后面的字段要在第一个字段排好的顺序的基础上，再进行排序；
mysql> SELECT DISTINCT S_ID FROM FRUITS ORDER BY S_ID ASC;
+------+
| S_ID |
+------+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
+------+
7 rows in set (0.00 sec)
mysql> SELECT DISTINCT S_ID FROM FRUITS ORDER BY S_ID DESC;
+------+
| S_ID |
+------+
| 107 |
| 106 |
| 105 |
| 104 |
| 103 |
| 102 |
| 101 |
+------+
7 rows in set (0.00 sec)
mysql> SELECT S_ID,F_NAME,F_PRICE FROM FRUITS ORDER BY S_ID ASC,F_PRICE DESC;
+------+------------+---------+
| S_ID | F_NAME | F_PRICE |
+------+------------+---------+
| 101 | BLACKBERRY | 10.20 |
| 101 | APPLE | 5.20 |
| 101 | CHERRY | 3.20 |
| 102 | ORANGE | 11.20 |
| 102 | BANANA | 10.30 |
| 102 | GRAPE | 5.30 |
| 103 | COCONUT | 9.20 |
| 103 | APRICOT | 2.20 |
| 104 | BERRY | 7.60 |
| 104 | LEMON | 6.40 |
| 105 | XXTT | 11.60 |
| 105 | MELON | 8.20 |
| 105 | XBABAY | 2.60 |
| 106 | MANGO | 15.60 |
| 107 | XXWWW | 3.60 |
+------+------------+---------+
15 rows in set (0.00 sec)
5.分组查询（GROUP BY）
GROUP_CONCAT(字段) //把分组里面的该字段显示完整；
mysql> SELECT S_ID,F_NAME FROM FRUITS GROUP BY S_ID ORDER BY S_ID;
+------+---------+
| S_ID | F_NAME |
+------+---------+
| 101 | APPLE |
| 102 | ORANGE |
| 103 | APRICOT |
| 104 | BERRY |
| 105 | MELON |
| 106 | MANGO |
| 107 | XXWWW |
+------+---------+
7 rows in set (0.00 sec)
mysql> SELECT S_ID,GROUP_CONCAT(F_NAME) FROM FRUITS GROUP BY S_ID ORDER BY S_ID;
+------+-------------------------+
| S_ID | GROUP_CONCAT(F_NAME) |
+------+-------------------------+
| 101 | APPLE,CHERRY,BLACKBERRY |
| 102 | GRAPE,BANANA,ORANGE |
| 103 | COCONUT,APRICOT |
| 104 | BERRY,LEMON |
| 105 | XBABAY,XXTT,MELON |
| 106 | MANGO |
| 107 | XXWWW |
+------+-------------------------+
7 rows in set (0.00 sec)
6.常用到的集合函数
COUNT() //计算行数
SUM() //求和
AVG() //求平均数
MAX() //求最大值
MIN() //求最小值
mysql> SELECT S_ID,COUNT(F_NAME),GROUP_CONCAT(F_NAME) FROM FRUITS GROUP BY S_ID ORDER BY S_ID;
+------+---------------+-------------------------+
| S_ID | COUNT(F_NAME) | GROUP_CONCAT(F_NAME) |
+------+---------------+-------------------------+
| 101 | 3 | APPLE,CHERRY,BLACKBERRY |
| 102 | 3 | GRAPE,BANANA,ORANGE |
| 103 | 2 | COCONUT,APRICOT |
| 104 | 2 | BERRY,LEMON |
| 105 | 3 | XBABAY,XXTT,MELON |
| 106 | 1 | MANGO |
| 107 | 1 | XXWWW |
+------+---------------+-------------------------+
7 rows in set (0.00 sec)
mysql> SELECT SUM(F_PRICE) FROM FRUITS;
+--------------+
| SUM(F_PRICE) |
+--------------+
| 112.40 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT MAX(F_PRICE) FROM FRUITS;
+--------------+
| MAX(F_PRICE) |
+--------------+
| 15.60 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT MIN(F_PRICE) FROM FRUITS;
+--------------+
| MIN(F_PRICE) |
+--------------+
| 2.20 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT S_ID,COUNT(F_NAME),GROUP_CONCAT(F_NAME),AVG(F_PRICE) FROM FRUITS GROUP BY S_ID ORDER BY S_ID;
+------+---------------+-------------------------+--------------+
| S_ID | COUNT(F_NAME) | GROUP_CONCAT(F_NAME) | AVG(F_PRICE) |
+------+---------------+-------------------------+--------------+
| 101 | 3 | APPLE,CHERRY,BLACKBERRY | 6.200000 |
| 102 | 3 | GRAPE,BANANA,ORANGE | 8.933333 |
| 103 | 2 | COCONUT,APRICOT | 5.700000 |
| 104 | 2 | BERRY,LEMON | 7.000000 |
| 105 | 3 | XBABAY,XXTT,MELON | 7.466667 |
| 106 | 1 | MANGO | 15.600000 |
| 107 | 1 | XXWWW | 3.600000 |
+------+---------------+-------------------------+--------------+
7 rows in set (0.00 sec)
7.limit[位置偏移量]，行数
通过limit可以选择显示数据表中的任意行数的数据；
位置偏移量 指定输出数据是从哪一行开始的（默认偏移量是0）；
行数：指定要输出内容的行数；
select 语句执行的排序；
where–>group–>having–>order by–>limit
mysql> select * from FRUITS limit 3,5;
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| B2 | 104 | BERRY | 7.60 |
| B5 | 107 | XXWWW | 3.60 |
| BS1 | 102 | ORANGE | 11.20 |
| BS2 | 105 | MELON | 8.20 |
| C0 | 101 | CHERRY | 3.20 |
+------+------+--------+---------+
5 rows in set (0.00 sec)
1.查看水果表中的F_ID 和 F_NAME 和 F_PRICE信息；
2.查询价格低于10元的水果F_ID,F_NAME和S_ID,并对S_ID进行降序排序；
3.以S_ID分组，查询S_ID,F_NAME和每组里面最贵的水果价格是多少；
4.查询价格不低于10元，并且F_NAME是以x开头的内容
mysql> SELECT F_ID,F_NAME FROM FRUITS;
+------+------------+
| F_ID | F_NAME |
+------+------------+
| A | APPLE |
| A2 | APRICOT |
| B1 | BLACKBERRY |
| B2 | BERRY |
| B5 | XXWWW |
| BS1 | ORANGE |
| BS2 | MELON |
| C0 | CHERRY |
| L2 | LEMON |
| M1 | MANGO |
| M2 | XBABAY |
| M3 | XXTT |
| O2 | COCONUT |
| T1 | BANANA |
| T2 | GRAPE |
+------+------------+
15 rows in set (0.00 sec)
mysql> SELECT F_ID,F_NAME,S_ID FROM FRUITS WHERE F_PRICE<10 order by S_ID DESC;
+------+---------+------+
| F_ID | F_NAME | S_ID |
+------+---------+------+
| B5 | XXWWW | 107 |
| BS2 | MELON | 105 |
| M2 | XBABAY | 105 |
| B2 | BERRY | 104 |
| L2 | LEMON | 104 |
| A2 | APRICOT | 103 |
| O2 | COCONUT | 103 |
| T2 | GRAPE | 102 |
| C0 | CHERRY | 101 |
| A | APPLE | 101 |
+------+---------+------+
10 rows in set (0.00 sec)
mysql> SELECT S_ID,GROUP_CONCAT(F_NAME),MAX(F_PRICE) FROM FRUITS GROUP BY S_ID;
+------+-------------------------+--------------+
| S_ID | GROUP_CONCAT(F_NAME) | MAX(F_PRICE) |
+------+-------------------------+--------------+
| 101 | APPLE,CHERRY,BLACKBERRY | 10.20 |
| 102 | GRAPE,BANANA,ORANGE | 11.20 |
| 103 | COCONUT,APRICOT | 9.20 |
| 104 | BERRY,LEMON | 7.60 |
| 105 | XBABAY,XXTT,MELON | 11.60 |
| 106 | MANGO | 15.60 |
| 107 | XXWWW | 3.60 |
+------+-------------------------+--------------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM FRUITS WHERE F_PRICE>10 AND F_NAME LIKE 'X%';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| M3 | 105 | XXTT | 11.60 |
+------+------+--------+---------+
1 row in set (0.01 sec)
mysql> SELECT * FROM FRUITS WHERE F_PRICE>10 AND F_NAME REGEXP '^B';
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| B1 | 101 | BLACKBERRY | 10.20 |
| T1 | 102 | BANANA | 10.30 |
+------+------+------------+---------+
2 rows in set (0.00 sec)
创建suppliers表、插入数据
mysql> CREATE TABLE suppliers(
-> s_id INT NOT NULL PRIMARY KEY,
-> s_name VARCHAR(50) NOT NULL,
-> s_city VARCHAR(50) NOT NULL,
-> s_call CHAR(5)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC suppliers;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| s_id | int(11) | NO | PRI | NULL | |
| s_name | varchar(50) | NO | | NULL | |
| s_city | varchar(50) | NO | | NULL | |
| s_call | char(5) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into suppliers values(101,'suppliers a','tianjin','18075'),(102,'suppliers b','chongqing','44333'),(103,'suppliers c','shanghai','11223'),(104,'suppliers d','guangzhou','33554'),(105,'suppliers e','yunnan','55235'),(106,'suppliers f','beijing','95235'),(107,'suppliers g','yiwu','12378');
mysql> select * from suppliers;
+------+-------------+-----------+--------+
| s_id | s_name | s_city | s_call |
+------+-------------+-----------+--------+
| 101 | suppliers a | tianjin | 18075 |
| 102 | suppliers b | chongqing | 44333 |
| 103 | suppliers c | shanghai | 11223 |
| 104 | suppliers d | guangzhou | 33554 |
| 105 | suppliers e | yunnan | 55235 |
| 106 | suppliers f | beijing | 95235 |
| 107 | suppliers g | yiwu | 12378 |
+------+-------------+-----------+--------+
7 rows in set (0.00 sec)
添加外键：
mysql> alter table FRUITS add foreign key(S_ID) references suppliers(s_id);
Query OK, 15 rows affected (0.02 sec)
Records: 15 Duplicates: 0 Warnings: 0
二.多表查询：
查询问题1：查询水果的批发商编号，批发商名字，水果名字，水果价格
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 | suppliers a | APPLE | 5.20 |
| 103 | suppliers c | APRICOT | 2.20 |
| 101 | suppliers a | BLACKBERRY | 10.20 |
| 104 | suppliers d | BERRY | 7.60 |
| 107 | suppliers g | XXWWW | 3.60 |
| 102 | suppliers b | ORANGE | 11.20 |
| 105 | suppliers e | MELON | 8.20 |
| 101 | suppliers a | CHERRY | 3.20 |
| 104 | suppliers d | LEMON | 6.40 |
| 106 | suppliers f | MANGO | 15.60 |
| 105 | suppliers e | XBABAY | 2.60 |
| 105 | suppliers e | XXTT | 11.60 |
| 103 | suppliers c | COCONUT | 9.20 |
| 102 | suppliers b | BANANA | 10.30 |
| 102 | suppliers b | GRAPE | 5.30 |
+------+-------------+------------+---------+
15 rows in set (0.00 sec)
小知识：
（1）为表可以取别名 格式：AS 别名；
在前面我们为表取上别名，后面的部分都可以用别名
（2）为字段取别名 格式：AS 别名；
为了显示的时候更清楚；
mysql> select s.s_id as su_sid,s_name,F_NAME,F_PRICE FROM FRUITS as f,suppliers as s where f.S_ID=s.s_id;
+--------+-------------+------------+---------+
| su_sid | s_name | F_NAME | F_PRICE |
+--------+-------------+------------+---------+
| 101 | suppliers a | APPLE | 5.20 |
| 103 | suppliers c | APRICOT | 2.20 |
| 101 | suppliers a | BLACKBERRY | 10.20 |
| 104 | suppliers d | BERRY | 7.60 |
| 107 | suppliers g | XXWWW | 3.60 |
| 102 | suppliers b | ORANGE | 11.20 |
| 105 | suppliers e | MELON | 8.20 |
| 101 | suppliers a | CHERRY | 3.20 |
| 104 | suppliers d | LEMON | 6.40 |
| 106 | suppliers f | MANGO | 15.60 |
| 105 | suppliers e | XBABAY | 2.60 |
| 105 | suppliers e | XXTT | 11.60 |
| 103 | suppliers c | COCONUT | 9.20 |
| 102 | suppliers b | BANANA | 10.30 |
| 102 | suppliers b | GRAPE | 5.30 |
+--------+-------------+------------+---------+
15 rows in set (0.00 sec)
内连接查询：
内连接和我们上面的那个例子中用“，”将两个表连接起来的效果一样，只是语法格式不同。
格式;表名1 INNER JOIN 表名1 ON 连接条件；
mysql> select suppliers.s_id,s_name,F_NAME,F_PRICE FROM FRUITS inner join suppliers on FRUITS.S_ID=suppliers.s_id;
+------+-------------+------------+---------+
| s_id | s_name | F_NAME | F_PRICE |
+------+-------------+------------+---------+
| 101 | suppliers a | APPLE | 5.20 |
| 103 | suppliers c | APRICOT | 2.20 |
| 101 | suppliers a | BLACKBERRY | 10.20 |
| 104 | suppliers d | BERRY | 7.60 |
| 107 | suppliers g | XXWWW | 3.60 |
| 102 | suppliers b | ORANGE | 11.20 |
| 105 | suppliers e | MELON | 8.20 |
| 101 | suppliers a | CHERRY | 3.20 |
| 104 | suppliers d | LEMON | 6.40 |
| 106 | suppliers f | MANGO | 15.60 |
| 105 | suppliers e | XBABAY | 2.60 |
| 105 | suppliers e | XXTT | 11.60 |
| 103 | suppliers c | COCONUT | 9.20 |
| 102 | suppliers b | BANANA | 10.30 |
| 102 | suppliers b | GRAPE | 5.30 |
+------+-------------+------------+---------+
15 rows in set (0.00 sec)
特殊的内连接：自连接 涉及到的两张表都是同一张表。
问题：查询供应商f_id=’a1’的水果供应商提供的其他水果
mysql> select f2.F_ID,f2.F_NAME from FRUITS as f1 inner join FRUITS as f2 on f1.s_id=f2.s_id and f1.F_ID='a';
+------+------------+
| F_ID | F_NAME |
+------+------------+
| A | APPLE |
| B1 | BLACKBERRY |
| C0 | CHERRY |
+------+------------+
3 rows in set (0.00 sec)
外连接：
外连接分为左连接和右连接，不同的连接方式输出的内容不同；
左连接：格式;表名 LEFT JOIN 表名2 ON 条件；
除了显示相关联的行，还会将左表中所有的记录行都显示出来；
问题一： 我的水果都是来自于哪个供应商，我准备重新选择各种水果的供应商
所有供应商信息和水果的供应商信息
mysql> select s.s_id,s.s_name,f.F_ID,f.F_NAME FROM suppliers as s left join FRUITS as f on s.s_id=f.S_ID order by s.s_id;
+------+-------------+------+------------+
| s_id | s_name | F_ID | F_NAME |
+------+-------------+------+------------+
| 101 | suppliers a | A | APPLE |
| 101 | suppliers a | B1 | BLACKBERRY |
| 101 | suppliers a | C0 | CHERRY |
| 102 | suppliers b | BS1 | ORANGE |
| 102 | suppliers b | T1 | BANANA |
| 102 | suppliers b | T2 | GRAPE |
| 103 | suppliers c | A2 | APRICOT |
| 103 | suppliers c | O2 | COCONUT |
| 104 | suppliers d | B2 | BERRY |
| 104 | suppliers d | L2 | LEMON |
| 105 | suppliers e | BS2 | MELON |
| 105 | suppliers e | M2 | XBABAY |
| 105 | suppliers e | M3 | XXTT |
| 106 | suppliers f | M1 | MANGO |
| 107 | suppliers g | B5 | XXWWW |
| 108 | suppliers t | NULL | NULL |
| 109 | suppliers y | NULL | NULL |
+------+-------------+------+------------+
17 rows in set (0.01 sec)
右连接：格式：表1 RIGHT JOIN 表2 ON 条件
mysql> select s.s_id,s.s_name,f.F_ID,f.F_NAME FROM FRUITS as f left join suppliers as s on s.s_id=f.S_ID order by s.s_id;
+------+-------------+------+------------+
| s_id | s_name | F_ID | F_NAME |
+------+-------------+------+------------+
| 101 | suppliers a | A | APPLE |
| 101 | suppliers a | C0 | CHERRY |
| 101 | suppliers a | B1 | BLACKBERRY |
| 102 | suppliers b | BS1 | ORANGE |
| 102 | suppliers b | T1 | BANANA |
| 102 | suppliers b | T2 | GRAPE |
| 103 | suppliers c | A2 | APRICOT |
| 103 | suppliers c | O2 | COCONUT |
| 104 | suppliers d | L2 | LEMON |
| 104 | suppliers d | B2 | BERRY |
| 105 | suppliers e | M3 | XXTT |
| 105 | suppliers e | BS2 | MELON |
| 105 | suppliers e | M2 | XBABAY |
| 106 | suppliers f | M1 | MANGO |
| 107 | suppliers g | B5 | XXWWW |
+------+-------------+------+------------+
15 rows in set (0.00 sec)
问题2：在fruits表和suppliers表中使用内连接查询suppliers表中s_id为107的供应商信息；
mysql> select s.s_id,s.s_name,f.F_NAME,s.s_city,s.s_call from FRUITS as f inner join suppl
+------+-------------+--------+--------+--------+
| s_id | s_name | F_NAME | s_city | s_call |
+------+-------------+--------+--------+--------+
| 107 | suppliers g | XXWWW | yiwu | 12378 |
+------+-------------+--------+--------+--------+
问题3：查询水果价格不高于8块的水果的供应商信息
mysql> select s.s_id,s.s_name,f.F_ID,f.F_NAME,s.s_call,s.s_city FROM FRUITS as f inner join suppliers as s on s.s_id=f.S_ID where f.F_PRICE<8 order by s.s_id;
+------+-------------+------+---------+--------+-----------+
| s_id | s_name | F_ID | F_NAME | s_call | s_city |
+------+-------------+------+---------+--------+-----------+
| 101 | suppliers a | A | APPLE | 18075 | tianjin |
| 101 | suppliers a | C0 | CHERRY | 18075 | tianjin |
| 102 | suppliers b | T2 | GRAPE | 44333 | chongqing |
| 103 | suppliers c | A2 | APRICOT | 11223 | shanghai |
| 104 | suppliers d | B2 | BERRY | 33554 | guangzhou |
| 104 | suppliers d | L2 | LEMON | 33554 | guangzhou |
| 105 | suppliers e | M2 | XBABAY | 55235 | yunnan |
| 107 | suppliers g | B5 | XXWWW | 12378 | yiwu |
+------+-------------+------+---------+--------+-----------+
8 rows in set (0.00 sec)
问题4：查询所有供应商各自都给我们供应了哪些水果（为了看起来方便，可以给供应商分组）
mysql> select s.s_id,s.s_name,group_concat(f.F_NAME),s.s_call,s.s_city FROM FRUITS as f inner join suppliers as s on s.s_id=f.S_ID group BY s.s_id;
+------+-------------+-------------------------+--------+-----------+
| s_id | s_name | group_concat(f.F_NAME) | s_call | s_city |
+------+-------------+-------------------------+--------+-----------+
| 101 | suppliers a | CHERRY,APPLE,BLACKBERRY | 18075 | tianjin |
| 102 | suppliers b | GRAPE,ORANGE,BANANA | 44333 | chongqing |
| 103 | suppliers c | APRICOT,COCONUT | 11223 | shanghai |
| 104 | suppliers d | BERRY,LEMON | 33554 | guangzhou |
| 105 | suppliers e | XBABAY,XXTT,MELON | 55235 | yunnan |
| 106 | suppliers f | MANGO | 95235 | beijing |
| 107 | suppliers g | XXWWW | 12378 | yiwu |
+------+-------------+-------------------------+--------+-----------+
7 rows in set (0.00 sec)
子查询：将查询一张表得到的结果来充当另一个查询条件，这样嵌套的查询就称为子查询；
演示问题：查询水果价格为15.6水果的供应商信息
mysql> select * from suppliers where s_id=(select s_id from FRUITS where F_PRICE=15.6);
+------+-------------+---------+--------+
| s_id | s_name | s_city | s_call |
+------+-------------+---------+--------+
| 106 | suppliers f | beijing | 95235 |
+------+-------------+---------+--------+
1 row in set (0.00 sec)
mysql> select S_ID from FRUITS where F_PRICE=15.6;
+------+
| S_ID |
+------+
| 106 |
+------+
1 row in set (0.00 sec)
带有ANY/SOME、ALL这些关键字的子查询；
mysql> CREATE TABLE tb1 (num int not null);
Query OK, 0 rows affected (0.02 sec)
mysql> drop table tb1;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE tb1 (num1 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE tb2 (num2 int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1 values(1),(5),(13),(21);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into tb2 values(6),(8),(16),(24);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
问题1：查询tb1中大于任意一个tb2中的值的数据。
mysql> select * from tb1 where num1>any(select * from tb2);
+------+
| num1 |
+------+
| 13 |
| 21 |
+------+
2 rows in set (0.00 sec)
问题2：查询tb2中大于tb1中的值的数据
mysql> select * from tb2 where num2>all(select * from tb1);
+------+
| num2 |
+------+
| 24 |
+------+
1 row in set (0.00 sec)
带有EXISTS关键词的子查询；
EXISTS 关键字后面的参数是任意一个子查询，如果子查询有返回记录行，则为true，外层查询语句将会进行查询，如果子查询没有返回任何记录行，则为FALSE，外层的查询语句将不会进行查询
mysql> select * from tb1 where exists(select * from tb2 where num2=3);
Empty set (0.00 sec)
mysql> select * from tb1 where exists(select * from tb2 where num2=6);
+------+
| num1 |
+------+
| 1 |
| 5 |
| 13 |
| 21 |
+------+
4 rows in set (0.00 sec)
带IN关键字的子查询：
当我们外部查询的条件要从子查询中取值的时候，子查询返回的结果多于1个，这时候我们可以IN；
mysql> select * from FRUITS where s_id in(select s_id from suppliers where s_city='tianjin');
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| C0 | 101 | CHERRY | 3.20 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
合并结果查询：
利用UNION关键词，可以将查询出的结果合并到一张表中，，也就是通过UNION将SELECT语句连接起来。
注意：这种方式只是增加了表中的记录行数，并不是增加字段，也就是说我们要合并的结果是相同字段的不同记录
语法：
SELECT 字段名… FROM 表名 UNION [ALL] SELECT 字段名… FROM 表名；
UNION 后面不加ALL，它会过滤掉重复的记录（重复的记录只显示1条），加上ALL不会删除重复的记录
问题1：查询所有价格小于9的水果的信息，查询s_id=101和103的所有水果信息，使用UNION连接查询结果；
mysql> select * from FRUITS where f_price<9 union all select * from FRUITS where s_id in(101,103);
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| A2 | 103 | APRICOT | 2.20 |
| B2 | 104 | BERRY | 7.60 |
| B5 | 107 | XXWWW | 3.60 |
| BS2 | 105 | MELON | 8.20 |
| C0 | 101 | CHERRY | 3.20 |
| L2 | 104 | LEMON | 6.40 |
| M2 | 105 | XBABAY | 2.60 |
| T2 | 102 | GRAPE | 5.30 |
| A | 101 | APPLE | 5.20 |
| A2 | 103 | APRICOT | 2.20 |
| B1 | 101 | BLACKBERRY | 10.20 |
| C0 | 101 | CHERRY | 3.20 |
| O2 | 103 | COCONUT | 9.20 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> select * from FRUITS where f_price<9 union select * from FRUITS where s_id in(101,103);
+------+------+------------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+------------+---------+
| A | 101 | APPLE | 5.20 |
| A2 | 103 | APRICOT | 2.20 |
| B2 | 104 | BERRY | 7.60 |
| B5 | 107 | XXWWW | 3.60 |
| BS2 | 105 | MELON | 8.20 |
| C0 | 101 | CHERRY | 3.20 |
| L2 | 104 | LEMON | 6.40 |
| M2 | 105 | XBABAY | 2.60 |
| T2 | 102 | GRAPE | 5.30 |
| B1 | 101 | BLACKBERRY | 10.20 |
| O2 | 103 | COCONUT | 9.20 |
+------+------+------------+---------+
11 rows in set (0.01 sec)
where子句里面的条件它是不区分大小写的，如果要区分在where后面加
mysql> select * from FRUITS where f_name='grape';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| T2 | 102 | GRAPE | 5.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
mysql> select * from FRUITS where f_name='GRAPE';
+------+------+--------+---------+
| F_ID | S_ID | F_NAME | F_PRICE |
+------+------+--------+---------+
| T2 | 102 | GRAPE | 5.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/101972.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
