MySQL中的排序和多表操作

最新推荐文章于 2024-07-05 16:05:26 发布

九久呀

最新推荐文章于 2024-07-05 16:05:26 发布

阅读量527

点赞数

分类专栏： MySQL数据库

本文链接：https://blog.csdn.net/qq_38851184/article/details/105440978

版权

MySQL数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

排序操作
- 使用 ORDER BY 进行排序
- LIMIT
多表操作

排序操作

在数据库中，也可以进行排序操作，通过ORDER BY 字段名 ASC|DESC实现，ASC表示升序，DESC表示降序。

使用 ORDER BY 进行排序

这是一个简单的数据库：

+----+--------+-----+--------+------+
| id | name   | age | sex    | pid  |
+----+--------+-----+--------+------+
|  1 | Jony   |  18 | 男     |    1 |
|  2 | Kitty  |  16 | 女     |    2 |
|  3 | Rabbit |  19 | 男     |    1 |
|  4 | Tim    |  14 | 女     |    2 |
|  5 | Ella   |  10 | 保密   |    1 |
|  6 | fdasf  |  19 | 女     |    3 |
+----+--------+-----+--------+------+

我们对这个数据库按照省份的代号，从小到大进行排序：

mysql> SELECT * FROM test1 ORDER BY pid ASC;
+----+--------+-----+--------+------+
| id | name   | age | sex    | pid  |
+----+--------+-----+--------+------+
|  1 | Jony   |  18 | 男     |    1 |
|  3 | Rabbit |  19 | 男     |    1 |
|  5 | Ella   |  10 | 保密   |    1 |
|  2 | Kitty  |  16 | 女     |    2 |
|  4 | Tim    |  14 | 女     |    2 |
|  6 | fdasf  |  19 | 女     |    3 |
+----+--------+-----+--------+------+

另外，SQL支持多排序，将要排序的字段按照比如：如果省份号相同，则按照姓名升序排列：

mysql> SELECT * FROM test1 ORDER BY pid ASC,name ASC;
+----+--------+-----+--------+------+
| id | name   | age | sex    | pid  |
+----+--------+-----+--------+------+
|  5 | Ella   |  10 | 保密   |    1 |
|  1 | Jony   |  18 | 男     |    1 |
|  3 | Rabbit |  19 | 男     |    1 |
|  2 | Kitty  |  16 | 女     |    2 |
|  4 | Tim    |  14 | 女     |    2 |
|  6 | fdasf  |  19 | 女     |    3 |
+----+--------+-----+--------+------+

LIMIT

LIMIT 有两个参数：偏移量，数量。也就是从哪里开始查询和要查询的数量。

mysql> SELECT * FROM test1 ORDER BY pid ASC,name ASC LIMIT 3;
+----+--------+-----+--------+------+
| id | name   | age | sex    | pid  |
+----+--------+-----+--------+------+
|  5 | Ella   |  10 | 保密   |    1 |
|  1 | Jony   |  18 | 男     |    1 |
|  3 | Rabbit |  19 | 男     |    1 |
+----+--------+-----+--------+------+
3 rows in set (0.00 sec)

多表操作

数据库会由多个表组成，如果按照第一范式到第三范式的规则进行设计数据库的画，那么这些表中的一部分表会有关联，而怎么进行相互关联的表的操作？

内连接和外连接

内连接：JOIN|CROSS JOIN INNER JOIN，通过ON连接条件，显示为两个表中符合连接条件的记录。
这时另外一个表：

+------+--------+
| pid  | pName  |
+------+--------+
|    1 | 北京   |
|    2 | 上海   |
|    3 | 广州   |
+------+--------+
3 rows in set (0.00 sec)

将这两个表进行内连接

mysql> SELECT * FROM test1 JOIN prov ON test1.pid=prov.pid;
+----+--------+-----+--------+------+------+--------+
| id | name   | age | sex    | pid  | pid  | pName  |
+----+--------+-----+--------+------+------+--------+
|  1 | Jony   |  18 | 男     |    1 |    1 | 北京   |
|  2 | Kitty  |  16 | 女     |    2 |    2 | 上海   |
|  3 | Rabbit |  19 | 男     |    1 |    1 | 北京   |
|  4 | Tim    |  14 | 女     |    2 |    2 | 上海   |
|  5 | Ella   |  10 | 保密   |    1 |    1 | 北京   |
|  6 | fdasf  |  19 | 女     |    3 |    3 | 广州   |
+----+--------+-----+--------+------+------+--------+
6 rows in set (0.10 sec)

外连接分为左外连接与右外连接，LEFT|RIGHT JOIN 。如果是左链接，那么左边的表为主表；如果是右链接，那么右边的表为主表。
左连接：

mysql> SELECT * FROM test1 LEFT JOIN prov ON test1.pid=prov.pid;
+----+--------+-----+--------+------+------+--------+
| id | name   | age | sex    | pid  | pid  | pName  |
+----+--------+-----+--------+------+------+--------+
|  1 | Jony   |  18 | 男     |    1 |    1 | 北京   |
|  3 | Rabbit |  19 | 男     |    1 |    1 | 北京   |
|  5 | Ella   |  10 | 保密   |    1 |    1 | 北京   |
|  2 | Kitty  |  16 | 女     |    2 |    2 | 上海   |
|  4 | Tim    |  14 | 女     |    2 |    2 | 上海   |
|  6 | fdasf  |  19 | 女     |    3 |    3 | 广州   |
+----+--------+-----+--------+------+------+--------+
6 rows in set (0.00 sec)

右连接：

mysql> SELECT * FROM test1 RIGHT JOIN prov ON test1.pid=prov.pid;
+------+--------+------+--------+------+------+--------+
| id   | name   | age  | sex    | pid  | pid  | pName  |
+------+--------+------+--------+------+------+--------+
|    1 | Jony   |   18 | 男     |    1 |    1 | 北京   |
|    2 | Kitty  |   16 | 女     |    2 |    2 | 上海   |
|    3 | Rabbit |   19 | 男     |    1 |    1 | 北京   |
|    4 | Tim    |   14 | 女     |    2 |    2 | 上海   |
|    5 | Ella   |   10 | 保密   |    1 |    1 | 北京   |
|    6 | fdasf  |   19 | 女     |    3 |    3 | 广州   |
+------+--------+------+--------+------+------+--------+
6 rows in set (0.00 sec)

外键

首先提出一个问题：为什么要使用外键?
现在我们有两张表，一张公司部分表（部分编号、部门名称），一张员工表（员工编号、员工姓名、所属部门编号），可以看到通过员工表能获得该员工的所属部门编号，进而获得部门名称。接下来，当公司撤销一个部门的时候，就没有员工属于该部门，所以就把属于该部门的员工和该部门一起删除掉。这种方法很可能会产生错误，如果忘记对员工进行修改，那么就会产生错误。这个时候外键就派上用场了，部门表和员工表通过外键相连，那么进行删除操作的时候就会保证不会出错。

联合查询

使用UNION关键字，将要查询的内容合并到一起，当有重复时，重复的只输出一个。
UNION ALL是简单的合并到一起。

mysql> SELECT name FROM test1 UNION SELECT pid FROM prov;
+--------+
| name   |
+--------+
| Jony   |
| Kitty  |
| Rabbit |
| Tim    |
| Ella   |
| fdasf  |
| 1      |
| 2      |
| 3      |
+--------+
9 rows in set (0.06 sec)

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层的查询结果为外层查询的条件。
引发子查询的情况：

使用[NOT]IN的子查询
使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>
使用[NOT]EXISTS的子查询
使用ANY|SOME或者ALL的子查询
如果我们要看test1表中人员所来自的省份，我们可以这个样子：

mysql> SELECT pid FROM test1;
+------+
| pid  |
+------+
|    1 |
|    2 |
|    1 |
|    2 |
|    1 |
|    3 |
+------+

再记住pid，进行prov表的查询：

mysql> SELECT pName FROM prov WHERE pid in(1,2,3);
+--------+
| pName  |
+--------+
| 北京   |
| 上海   |
| 广州   |
+--------+
3 rows in set (0.00 sec)

但是如果我们使用子查询的话，就可以这样写：

mysql> SELECT pName FROM prov WHERE pid IN(SELECT pid FROM test1);
+--------+
| pName  |
+--------+
| 北京   |
| 上海   |
| 广州   |
+--------+

ANY、SOME、ALL：
<=ANY()或者<ANY：最大值
<=SOME或者<SOME：最大值
<=ALL或者<ALL：最小值

<、<=ALL：最小值
= ANY 或者= SOME ：任意值

=>ANY()或者>ANY：最小值
=>SOME或者>SOME：最小值
=>ALL或者>ALL：最大值
学生表：

mysql> SELECT * FROM student;
+------+-------+-------+
| sno  | sname | score |
+------+-------+-------+
|    1 | king1 |    99 |
|    2 | king2 |    89 |
|    3 | king3 |    79 |
|    4 | king4 |    69 |
|    5 | king5 |    59 |
+------+-------+-------+
5 rows in set (0.00 sec)

奖学金表：

mysql> SELECT * FROM ship;
+-------+-------+
| level | monwy |
+-------+-------+
|    90 |     1 |
|    80 |     2 |
|    70 |     3 |
|    60 |     4 |
|    50 |     5 |
+-------+-------+
5 rows in set (0.00 sec)

查寻所有大于level的学生名字和成绩：

mysql> SELECT sname,score FROM student WHERE score>=ALL(SELECT level FROM ship);
+-------+-------+
| sname | score |
+-------+-------+
| king1 |    99 |
+-------+-------+
1 row in set (0.00 sec)

查寻任何大于level的学生名字和成绩：

mysql> SELECT sname,score FROM student WHERE score>ANY(SELECT level FROM ship);
+-------+-------+
| sname | score |
+-------+-------+
| king1 |    99 |
| king2 |    89 |
| king3 |    79 |
| king4 |    69 |
| king5 |    59 |
+-------+-------+
5 rows in set (0.00 sec)