第六章《MySQL查询》

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)

发布了22 篇原创文章 · 获赞 8 · 访问量 3345
App 阅读领勋章
微信扫码 下载APP
阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览