MySQL中的排序和多表操作

1001-高同学

发表文章数:265

热门标签

首页 » MySQL » 正文

排序操作

在数据库中,也可以进行排序操作,通过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)

子查询

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

  1. 使用[NOT]IN的子查询
  2. 使用比较运算符的子查询:=、>、<、>=、<=、<>、!=、<=>
  3. 使用[NOT]EXISTS的子查询
  4. 使用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)

拜师教育学员文章:作者:1001-高同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《MySQL中的排序和多表操作》 发布于2020-04-10

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录