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子句常使用运算符来指定条件
代码语言：javascript复制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的数据
代码语言：javascript复制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(过滤掉重复的结果)
代码语言：javascript复制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 可以同时对多个字段进行排序，但是后面的字段要在第一个字段排好的顺序的基础上，再进行排序；
代码语言：javascript复制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(字段) //把分组里面的该字段显示完整；
代码语言：javascript复制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() //求最小值
代码语言：javascript复制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
代码语言：javascript复制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开头的内容
代码语言：javascript复制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表、插入数据
代码语言：javascript复制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：查询水果的批发商编号，批发商名字，水果名字，水果价格
代码语言：javascript复制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 别名； 为了显示的时候更清楚；
代码语言：javascript复制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 连接条件；
代码语言：javascript复制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’的水果供应商提供的其他水果
代码语言：javascript复制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 条件； 除了显示相关联的行，还会将左表中所有的记录行都显示出来；
问题一： 我的水果都是来自于哪个供应商，我准备重新选择各种水果的供应商 所有供应商信息和水果的供应商信息
代码语言：javascript复制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 条件
代码语言：javascript复制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的供应商信息；
代码语言：javascript复制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块的水果的供应商信息
代码语言：javascript复制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：查询所有供应商各自都给我们供应了哪些水果（为了看起来方便，可以给供应商分组）
代码语言：javascript复制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水果的供应商信息
代码语言：javascript复制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这些关键字的子查询；
代码语言：javascript复制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中的值的数据。
代码语言：javascript复制mysql> select * from tb1 where num1>any(select * from tb2);
------
| num1 |
------
| 13 |
| 21 |
------
2 rows in set (0.00 sec)问题2：查询tb2中大于tb1中的值的数据
代码语言：javascript复制mysql> select * from tb2 where num2>all(select * from tb1);
------
| num2 |
------
| 24 |
------
1 row in set (0.00 sec)带有EXISTS关键词的子查询； EXISTS 关键字后面的参数是任意一个子查询，如果子查询有返回记录行，则为true，外层查询语句将会进行查询，如果子查询没有返回任何记录行，则为FALSE，外层的查询语句将不会进行查询
代码语言：javascript复制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；
代码语言：javascript复制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连接查询结果；
代码语言：javascript复制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后面加
代码语言：javascript复制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)发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/101972.html原文链接：
