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)