数据库多表联查
MySQL 多表联查及PySQL模块使用
- 多表查询的两种方式
/*原始数据
mysql> select * from emp1;
+----+--------+--------+-----+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+-----+--------+
| 1 | jason | male | 18 | 200 |
| 2 | dragon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+--------+--------+-----+--------+
6 rows in set (0.02 sec)
mysql> select * from dep;
+-----+--------------+
| id | name |
+-----+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
| 205 | 财务 |
+-----+--------------+
5 rows in set (0.02 sec)
*/
-- 方式1：连表操作
-- inner join 内连接
mysql> select * from emp1 inner join dep on emp1.dep_id=dep.id;
+----+--------+--------+-----+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+-----+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+--------+--------+-----+--------+-----+--------------+
5 rows in set (0.23 sec)
/*
通过上面使用的内连接的方式 inner join的方式对两个数据表进行查询，的到的是字段与字段条件成立的数据，也就是数据的交集部分
*/
left join 左连接
> 左连接会以左边的表为基准，如果右侧不存在数据则使用NULL填充
mysql> select * from emp1 left join dep on emp1.dep_id = dep.id;
+----+--------+--------+-----+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+-----+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+--------+--------+-----+--------+------+--------------+
6 rows in set (0.01 sec)
right join 右连接
> 右连接会以右边的表为基准，如果右侧不存在数据则使用NULL填充
mysql> select * from emp1 right join dep on emp1.dep_id=dep.id;
+------+--------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+------+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 财务 |
+------+--------+--------+------+--------+-----+--------------+
6 rows in set (0.02 sec)
union 全链接
-- 全连接就是使用左连接和右连接查询之后将数据和到一起，查出所有数据，没有的数据使用null填充
mysql> select * from emp1 left join dep on emp1.dep_id = dep.id
UNION
select * from emp1 right join dep on emp1.dep_id=dep.id
+------+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 财务 |
+------+--------+--------+------+--------+------+--------------+
-
多表联查思想
- 通过连表操作可以将查询出来的结果表当做一张表，起别名后向后查询，如果存在多张关系表，依次查询即可
-
多表联查的第二种方式
-
子查询
将一条SQL语句使用括号括起来当做另一条SQL语句的查询条件
举例题目：求姓名是jason的员工的部门名称
-- 通过用户表获取用户的部门id
select dep_id from emp where name='jason';
-- 通过查询出来的部门id获取部门名称
select name from dep where id=200;
-- 最后将sql合并，通过子查询的方式获取用户的部门名称
select name from dep where id=(select dep_id from emp where name='jason');
- MySQL中小知识点的补充说明
- concat与concat_ws
concat 用于分组之前的字段拼接操作
select concat(name,'|',sex) from emp;
concat_ws拼接多个字段并且之间的连接符一致
select concat_ws('|', name,sex,age,dep_id) from emp;
-
exists 用于判断是否存在
sql1 exists sql2
sql2 有结果的情况下才会执行sql1 否则不执行sql1 返回空数据 -
表相关操作
-- 修改表名
-- alter table 表名 rename 新表名
alter table t2 rename t1;
-- 添加新字段
-- alter table 表名 add 字段名 字段类型 约束条件
alter table t1 add age int not null;
-- 添加指定字段下面的新字段
-- alter table 表名 add 字段名 字段类型 约束条件 after 现有字段
alter table t1 add namekey varchar(20) not null after name;
-- 添加新字段至首行
-- alter table 表名 add 字段名 字段类型 约束条件 firse
alter table t1 add vl varchar(20) not null first;
-- 修改字段名和属性
-- alter table 表名 change 旧字段 新字段 字段类型 约束条件
alter table t1 change old new int not null;
-- 修改字段名的类型约束等
-- alter table 表名 modify 字段名 字段类型 约束条件
alter table t1 modify new varchar(20) not null;
-- 删除字段
-- alter table 表名 drop 表名
alter table t1 drop new;
- Python操作MySQL
python操作mysql使用了pymysql模块，接下来介绍pymysql简单的用法
需要现在python环境中安装pymysql pip3 install pymysql
# 导入pymysql模块
1. 链接服务端必备条件
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db',
charset='utf8mb4',
autocommit=True
)
"""
1.pymysql.connect产生一个链接对象conn
2.host 链接地址
3.port 端口号
4.user 用户名
5.password 密码
6.db 需要链接的数据库
7.charset 链接的字符集
8.autocommit 自动确认提交，由于更删改需要二次确认，如果没有此配置，需要代码中使用对象conn.commit()提交
"""
2. 产生游标对象
# 括号内如果不写其他参数，数据获取时以元组返回，辨识度不高
# cursor = conn.cursor()
# 括号内填写cursor=pymysql.cursors.DictCursor,f返回的是列表套字典
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
3. 编写SQL语句
# 编写SQL语句
sql = 'select * from emp;'
4. 发送SQL语句
# 发送SQL语句
cursor.execute(sql) # execute也有返回值，可以使用变量接收，值为sql语句影响的行数
5. 获取SQL语句执行之后的结果
res = cursor.fetchall() # 获取返回的所有结果
print(res)
- pymysql补充说明
1. 获取数据
fetchall() # 获取数据集的全部
fetchone() # 获取数据集的第一条
fetchmany() # 获取指定数量的结果集, 参数可填数字，获取的记录条数
"""需要注意这三者类似于文件光标移动的特新，如果不做额外的操作，好哦吗重复获取，将无法获取到数据
"""
2. 游标移动
cursor.scroll(1, 'relative') # 基于当前位置往后移动参数1为行数
cursor.scroll(0, 'absolute') # 基于数据的开头往后移动
3. 更删改提交二次确认
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
- 接收数据集时注意事项
import pymysql
conn = pysmysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123'
db='db',
charset='utf8mb4',
autocommit=True
)
cursor = conn.cursors(cursor=pymysql.cursors.DictCursor)
sql = 'select * from t1;'
cursor.execute(sql)
#
res = cursor.fetchall()
res1 = cursor.fetchone()
res2 = cursor.fetchone()
print(res)
print(res1)
print(res2)
# 打印结果
[{'id': 1, 'name': 'jason', 'sex': 'male', 'age': 18, 'dep_id': 200}, {'id': 2, 'name': 'dragon', 'sex': 'female', 'age': 48, 'dep_id': 201}, {'id': 3, 'name': 'kevin', 'sex': 'male', 'age': 18, 'dep_id': 201}, {'id': 4, 'name': 'nick', 'sex': 'male', 'age': 28, 'dep_id': 202}, {'id': 5, 'name': 'owen', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 6, 'name': 'jerry', 'sex': 'female', 'age': 18, 'dep_id': 204}]
[]
[]
"""
这里需要注意游标的特性类似于文件的光标，当第一次数据集直接取完，后面也就取不到了，接下来使用cursor.scroll()改变游标位置
"""
1. 第一种使用方式
res = cursor.fetchone()
cursor.scroll(-1, 'relative')
# res接收数据值后，scroll回滚-1回到取值之前的位置，负数往回滚，整数向前滚
2. 第二种使用方式
res1 = cursor.fetchall()
cursor.scroll(0,'absolute')
# 每次fetchall()接收完数据值后都可以通过absolute回到最开始位置
- 多表查询联系题
在整理好思路的前提下 一步一步写
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid
2、查询平均成绩大于八十分的同学的姓名和平均成绩
select student.sname, t1.avg_num from student inner join (select student_id, avg(num) as avg_num from score GROUP BY student_id HAVING avg_num > 80) AS t1 on t1.student_id = student.sid
3、查询没有报李平老师课的学生姓名
SELECT sname from student WHERE sid not in (SELECT DISTINCT student_id FROM score where score.course_id in (SELECT cid from course where teacher_id = (select tid from teacher where tname = '李平老师')) ORDER BY student_id)
4、查询没有同时选修物理课程和体育课程的学生姓名
select sname from student, (SELECT t1.student_id,GROUP_CONCAT(t1.course_id) as count_num from (SELECT student_id,course_id from score WHERE course_id in (SELECT cid from course where cid in (2,3))) as t1 GROUP BY t1.student_id HAVING LENGTH(count_num) < 2) as t2 where student.sid = t2.student_id
5、查询挂科超过两门(包括两门)的学生姓名和班级
select t1.sname,caption from class inner JOIN (SELECT * FROM student, (SELECT student_id,GROUP_CONCAT(course_id) as a from score where num < 60 GROUP BY student_id HAVING LENGTH(a) >= 2) as t1 where student.sid = t1.student_id) as t1 on t1.class_id = class.cid
浙公网安备 33010602011771号