数据学习经验

最新推荐文章于 2025-03-18 15:36:29 发布

xuexiaoyaani

最新推荐文章于 2025-03-18 15:36:29 发布

阅读量219

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.youkuaiyun.com/xuexiaoyaani/article/details/80611431

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

1 数据系统知识
- 1.1 数据安全修改
2. 数据查询知识
- 2.1 having 和 where区别
3. 多表联查
4. 一对多连表查询

1 数据系统知识

1.1 数据安全修改

在使用update时，若不加where子句时会修改全表，是很危险的一个动作，因此将变量进行修改，要修改变量sql_safe_updates,默认情况系下是off，因此通过配置文件修改变量为ON，这样不加where子句就无法修改

mysql> show variables like "%safe%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
| sql_safe_updates               | OFF   |
+--------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set global sql_safe_updates = on;
Query OK, 0 rows affected (0.00 sec)

#重新登陆数据库生效，但是重启服务后丢失

mysql> show variables like '%safe%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
| sql_safe_updates               | ON    |
+--------------------------------+-------+
2 rows in set (0.01 sec)

2. 数据查询知识

2.1 having 和 where区别

where是对查询过程中每个单元格进行过滤，having通常是伴随着group by出现，通常是通过group by 查询出结果后，然后对group by的查询结果进行再次过滤，
总结：where相当于是对单元格过滤，having是对group分组过滤

2.1.1 举例说明

数据库结构如下：

mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)         | NO   |     | NULL    |                |
| Age       | tinyint(3) unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

2.1.2 分组查询中用having对单元格过滤

预期：分组查询中having对单元格过滤会报错，因为having是对group分组后的结果过滤的

mysql> select Gender,avg(age) from students group by Gender having age >30;
#应该having是对group分组查询后的结果进行过滤的，此案列中应该对avg(age)过滤
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'

2.1.3 验证having对group分组后的结果过滤

预期：分组后的结果过滤就没有问题

avg(age)不过滤的结果如下

mysql> select Gender,avg(age) from students group by Gender;
+--------+----------+
| Gender | avg(age) |
+--------+----------+
| F      |  19.0000 |
| M      |  33.0000 |
+--------+----------+
2 rows in set (0.00 sec)

avg(age)过滤结果如下

mysql> select Gender,avg(age) from students group by Gender having avg(age) >30;
+--------+----------+
| Gender | avg(age) |
+--------+----------+
| M      |  33.0000 |
+--------+----------+
1 row in set (0.01 sec)

2.1.4 先对单元格过滤后对分组过滤

预期：先对单元格过滤后，查询出一个结果，再通过分组查询，分组查询完后再对其过滤

mysql> select Gender,avg(age) from students where age > 30 group by Gender having avg(age) > 20;
+--------+----------+
| Gender | avg(age) |
+--------+----------+
| M      |  52.8000 |
+--------+----------+
1 row in set (0.00 sec)

3. 多表联查

在多表联查中，联查的基本条件和前提就是：将多张表组合到一块，然后筛选出自己想要的数据，组合的方式从大类上讲可以分为两种水平组合和垂直组合，水平组合又可分为很多种，不管采取什么方式，最终肯定有表和表之间的连接条件，连接条件能够决定组合表的最终形态，最为经典及常用的组合如下图：
这里写图片描述
若想想得帮助信息，可以通过DML语言中获取更多的语言信息。

3.1 innor内连接取交集

在表的查询中通过innor内连接查询数据用的非常多，连接条件通常是某值相等，具体如下所示：

#组合表的最终形态
mysql> select * from students as s inner join teachers as t on s.teacherid = t.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.01 sec)
#若想从组合表的形态中获取学生信息和老师信息，如那个学习选则了那个老师
mysql> select s.name,t.name from students as s inner join teachers as t on s.teacherid = t.tid;
+-------------+---------------+
| name        | name          |
+-------------+---------------+
| Yu Yutong   | Song Jiang    |
| Shi Zhongyu | Miejue Shitai |
| Ding Dian   | Lin Chaoying  |
+-------------+---------------+
3 rows in set (0.00 sec)

3.2 左右外连接取部分全集和交集

在表的组合：A表的全集显示，B表的与A表的交集在B表部分显示

mysql> select * from students as s left join teachers as t on s.teacherid = t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)

3.3 左右外连接除部分全集和交集

在表的组合：A表的全集显示，B表的与A表的交集在B表部分不不不不不不显示

mysql> select * from students as s left join teachers as t on s.teacherid = t.tid and s.teacherid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID  | Name | Age  | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 | NULL | NULL | NULL | NULL   |
|     2 | Shi Potian    |  22 | M      |       1 |         7 | NULL | NULL | NULL | NULL   |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 | NULL | NULL | NULL | NULL   |
|     4 | Ding Dian     |  32 | M      |       4 |         4 | NULL | NULL | NULL | NULL   |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 | NULL | NULL | NULL | NULL   |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL | NULL | NULL | NULL | NULL   |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL | NULL | NULL | NULL | NULL   |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL | NULL | NULL | NULL | NULL   |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL | NULL | NULL | NULL | NULL   |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL | NULL | NULL | NULL | NULL   |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL | NULL | NULL | NULL | NULL   |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL | NULL | NULL | NULL | NULL   |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL | NULL | NULL | NULL | NULL   |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL | NULL | NULL | NULL | NULL   |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
25 rows in set (0.00 sec)

3.4 垂直合并多表

在垂直合并，在垂直合并的过程中,遇到重复的行会自动去重
但是在垂直合并注意两点：

第一点，垂直合并时字段约束得一致，如字符串和int无法垂直合并
第二点，会自动去重

mysql> select stuid,age from students union select tid,age from teachers;
+-------+-----+
| stuid | age |
+-------+-----+
|     1 |  22 |
|     2 |  22 |
|     3 |  53 |
|     4 |  32 |
......
|    22 |  20 |
|    23 |  23 |
|    24 |  27 |
|    25 | 100 |
|     1 |  45 |
|     2 |  94 |
|     3 |  77 |
|     4 |  93 |
+-------+-----+
29 rows in set (0.01 sec)

3.5 多表合并取并集

在多表合并得过程取并集，如图中得full join连接，取并集可以采用将左连接和右连接进行union，即可实现

mysql> select * from students as s left join teachers as t on s.teacherid = t.tid
    -> union
    -> select * from students as s right join teachers as t on s.teacherid = t.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name          | Age  | Gender | ClassID | TeacherID | TID  | Name          | Age  | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
|     1 | Shi Zhongyu   |   22 | M      |       2 |         3 |    3 | Miejue Shitai |   77 | F      |
|     2 | Shi Potian    |   22 | M      |       1 |         7 | NULL | NULL          | NULL | NULL   |
|     3 | Xie Yanke     |   53 | M      |       2 |        16 | NULL | NULL          | NULL | NULL   |
|     4 | Ding Dian     |   32 | M      |       4 |         4 |    4 | Lin Chaoying  |   93 | F      |
|     5 | Yu Yutong     |   26 | M      |       3 |         1 |    1 | Song Jiang    |   45 | M      |
|     6 | Shi Qing      |   46 | M      |       5 |      NULL | NULL | NULL          | NULL | NULL   |
|     7 | Xi Ren        |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|     8 | Lin Daiyu     |   17 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|     9 | Ren Yingying  |   20 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    10 | Yue Lingshan  |   19 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    11 | Yuan Chengzhi |   23 | M      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    12 | Wen Qingqing  |   19 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    13 | Tian Boguang  |   33 | M      |       2 |      NULL | NULL | NULL          | NULL | NULL   |
|    14 | Lu Wushuang   |   17 | F      |       3 |      NULL | NULL | NULL          | NULL | NULL   |
|    15 | Duan Yu       |   19 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    16 | Xu Zhu        |   21 | M      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    17 | Lin Chong     |   25 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    18 | Hua Rong      |   23 | M      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    19 | Xue Baochai   |   18 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    20 | Diao Chan     |   19 | F      |       7 |      NULL | NULL | NULL          | NULL | NULL   |
|    21 | Huang Yueying |   22 | F      |       6 |      NULL | NULL | NULL          | NULL | NULL   |
|    22 | Xiao Qiao     |   20 | F      |       1 |      NULL | NULL | NULL          | NULL | NULL   |
|    23 | Ma Chao       |   23 | M      |       4 |      NULL | NULL | NULL          | NULL | NULL   |
|    24 | Xu Xian       |   27 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|    25 | Sun Dasheng   |  100 | M      |    NULL |      NULL | NULL | NULL          | NULL | NULL   |
|  NULL | NULL          | NULL | NULL   |    NULL |      NULL |    2 | Zhang Sanfeng |   94 | M      |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec)

3.6 子查询()的返回值

在mysql默认情况下select语句返回的是一个表，如何让select语句返回一个值呢？如查询学生的年龄，查询平均年龄后，作为where的条件

3.6.1 作用1`返回值为单一值`

当查询的结果为单一值时，（）可以作为查询返回值

mysql> select name,age from students where age > (select avg(age) from students);
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+
5 rows in set (0.00 sec)

3.6.2 作用2`返回值为表`

当查询的结果作为一张表时，返回值就是一张表，此时需要用一个变量来接，如as语句，有点类似python中的with语句

mysql> select * from (select name,age from students where age > 30) as s;
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+
5 rows in set (0.00 sec)
#若不加as则出现报错
mysql> select * from (select name,age from students where age > 30);
ERROR 1248 (42000): Every derived table must have its own alias
#错误1248：每一个表必须永有它自己的名称

3.7 取多表的并集并除多表的交集

可以根据先取并集，然后再次并集中查询，查询条件为A表的null和B表的null，此时就实现了上述功能：

mysql> select * from (select s.stuid,s.name as sname,t.tid,t.name as tname from students as s left join teachers as t on s.teacherid = t.tid 
union 
select s.stuid,s.name as sname,t.tid,t.name as tname from students as s right join teachers as t on s.teacherid = t.tid) as f where stuid is null or tid is null;
+-------+---------------+------+---------------+
| stuid | sname         | tid  | tname         |
+-------+---------------+------+---------------+
|     2 | Shi Potian    | NULL | NULL          |
|     3 | Xie Yanke     | NULL | NULL          |
|     6 | Shi Qing      | NULL | NULL          |
|     7 | Xi Ren        | NULL | NULL          |
|     8 | Lin Daiyu     | NULL | NULL          |
|     9 | Ren Yingying  | NULL | NULL          |
|    10 | Yue Lingshan  | NULL | NULL          |
|    11 | Yuan Chengzhi | NULL | NULL          |
|    12 | Wen Qingqing  | NULL | NULL          |
|    13 | Tian Boguang  | NULL | NULL          |
|    14 | Lu Wushuang   | NULL | NULL          |
|    15 | Duan Yu       | NULL | NULL          |
|    16 | Xu Zhu        | NULL | NULL          |
|    17 | Lin Chong     | NULL | NULL          |
|    18 | Hua Rong      | NULL | NULL          |
|    19 | Xue Baochai   | NULL | NULL          |
|    20 | Diao Chan     | NULL | NULL          |
|    21 | Huang Yueying | NULL | NULL          |
|    22 | Xiao Qiao     | NULL | NULL          |
|    23 | Ma Chao       | NULL | NULL          |
|    24 | Xu Xian       | NULL | NULL          |
|    25 | Sun Dasheng   | NULL | NULL          |
|  NULL | NULL          |    2 | Zhang Sanfeng |
+-------+---------------+------+---------------+
23 rows in set (0.00 sec)

4. 一对多连表查询

如：学生表，老师表，成绩表，成绩表关联学生和老师，查询学生选的课和成绩

mysql> select s.name,cs.course,c.score from students as s inner join scores as c on s.stuid = c.stuid
    -> inner join courses as cs on c.courseid = cs.courseid;
+-------------+----------------+-------+
| name        | course         | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian |    77 |
| Shi Zhongyu | Weituo Zhang   |    93 |
| Shi Potian  | Kuihua Baodian |    47 |
| Shi Potian  | Daiyu Zanghua  |    97 |
| Xie Yanke   | Kuihua Baodian |    88 |
| Xie Yanke   | Weituo Zhang   |    75 |
| Ding Dian   | Daiyu Zanghua  |    71 |
| Ding Dian   | Kuihua Baodian |    89 |
| Yu Yutong   | Hamo Gong      |    39 |
| Yu Yutong   | Dagou Bangfa   |    63 |
| Shi Qing    | Hamo Gong      |    96 |
| Xi Ren      | Hamo Gong      |    86 |
| Xi Ren      | Dagou Bangfa   |    83 |
| Lin Daiyu   | Taiji Quan     |    57 |
| Lin Daiyu   | Jinshe Jianfa  |    93 |
+-------------+----------------+-------+
15 rows in set (0.00 sec)