MySQL之多表查询、Navicat及pymysql

一、多表查询

1.1 数据准备

-- 建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);


-- 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

如何查询jason所在的部门名称？

首先，涉及到SQL查询题目，一定要先明确到底需要几张表。

先查询jason所在的部门编号

select dep_id from emp where name='jason';

根据部门编号查询部门名称
```
select name from dep where id=(select dep_id from emp where name='jason');
```
一条SQL语句的查询结果既可以看成是一张表也可以看成是查询条件。

补充：

MySQL的两种注释语法：

# 注释

-- 注释

1.2 多表查询思路

子查询

即将SQL语句的结果当做另外一条SQL语句的查询条件，对应到日常生活中就是我们常见的解决问题的方式：分步操作
连表操作：通过连接将需要使用到的表拼接成一张大表，之后基于单表查询完成
- inner join：内连接
- left join：左连接
- right join：右连接
- union：全连接
涉及到多表查询的时候，为了避免表字段重复，需要在字段名的前面加上表名限制，及使用表名.字段名的方式加以区分。

-- inner join:只拼接两张表中共有的部分(有对应关系)
select * from emp inner join dep on emp.dep_id = dep.id;

-- left join:以左表为基准展示所有的内容，没有的用NULL填充
select * from emp left join dep on emp.dep_id = dep.id;

-- right join:以右表为基准展示所有的内容，没有的用NULL填充
select * from emp right join dep on emp.dep_id = dep.id;

-- union:左右表所有的数据都在 没有的用NULL填充
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

"""
疑问:上述操作一次只能连接两张表 如何做到多张表?
	将两张表的拼接结果当成一张表与跟另外一张表做拼接
	依次往复 即可拼接多张表
"""

上述操作一次只能连接两张表，那如何做到连接多张表？

其实只需要遵循子查询的思路就可以，即将两张表的拼接结果当成一张表再与另外一张表做拼接即可，以此往复，即可拼接多张表。

二、Navicat可视化软件

Navicat内部封装了很多SQL的操作，大部分操作用户只需要使用鼠标点点的方式就能完成，其内部会自动构建SQL语句并执行。

以下是关于这款软件的下载与详细使用教程：

MySQL可视化软件：Navicat的下载与使用

三、多表查询练习题

现设有如下五张表，其涉及到的字段名称和所建立的表关系如下图所示：

class表对student表：一对多
student表对course表：多对多，两者表关系记录在score表中。
teacher表对course表：一对多

编写较为复杂的SQL语句不要想着一次性写完，可以边写边看。

-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
	teacher.tname,
	course.cname 
FROM
	teacher
	INNER JOIN course ON teacher.tid = course.teacher_id;



-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
	student.sname,
	AVG( num ) 
FROM
	score
	INNER JOIN student ON student.sid = score.student_id 
GROUP BY
	student_id 
HAVING
	AVG( num ) > 80;
    
-- 子查询：
-- 1.1 按照学生id分组并获取平均成绩
select student_id,avg(num) from score group by student_id;
-- 1.2 筛选出平均成绩大于80的数据  (针对聚合函数的字段结果 最好起别名防止冲突)
select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
-- 1.3 将上述SQL的结果与student表拼接
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 student.sid = t1.student_id;



-- 3、查询没有报李平老师课的学生姓名
-- 1.先查询李平老师教授的课程编号
select course.cid from course where teacher_id = (select tid from teacher where tname ='李平老师');
-- 2.再根据课程id号筛选出所有报了对应课程的学生id号
select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id = 
(select tid from teacher where tname ='李平老师'));
-- 3.最后去学生表中根据id号取反筛选学生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
	WHERE
	course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
	);



-- 4、查询没有同时选修物理课程和体育课程的学生姓名(只筛选了报了一门课程的，两门和一门没报的都不要)
-- 1.先获取两门课程的id号
select course.cid from course where cname in ('物理','体育');
-- 2.然后去分数表中先筛选出所有报了物理和体育课程的学生id(包含两门和一门)
select * from score where course_id in (select course.cid from course where cname in ('物理','体育'));
-- 3.再筛选出只报了一门的学生id(按照学生id分组，然后计数，并过滤出计数结果为1的数据)
select score.student_id from score where course_id in (select course.cid from course where cname in ('物理','体育')) group by score.student_id having count(score.course_id) = 1;
-- 4.最后根据学生id号去student表中筛选学生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT course.cid FROM course WHERE cname IN ( '物理', '体育' ) ) 
	GROUP BY
		score.student_id 
	HAVING
	count( score.course_id ) = 1 
	);



-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1.先筛选出小于60分的数据
select * from score where num < 60;
-- 2.再按照学生id分组，统计挂科数量，筛选出挂科超过两门的学生id
select student_id from score where num < 60 group by student_id having count(course_id) >=2;
-- 3.最后通过连接student和class表，查询所需数据
SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

更多练习可以参考该篇博客：https://www.cnblogs.com/Dominic-Ji/p/10875493.html

四、Python操作MySQL模块：pymysql

4.1 基本使用

该模块为第三方模块，需要下载使用：pip3 install pymysql

import pymysql


# 创建连接，可以连接到MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_5',
    charset='utf8'
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
cursor.execute(sql)
# 获取返回结果
res = cursor.fetchall()
print(res)

4.2 SQL注入问题

import pymysql

# 创建链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='456852',
    database='mydb',
    charset='utf8'
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组织成字典
# 获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 出现问题SQL语句
# sql = "select * from userinfo where name='%s' and password='%s';" % (username, password)
# cursor.execute(sql)
# 针对核心数据 不要自己拼接 交由execute方法帮你筛选再拼接
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
# 执行SQL语句
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if res:
    print(res)
    print('登录成功')
else:
    print('用户名或密码错误')

SQL注入问题的产生，是由于特殊符号的组合会产生特殊的效果，从而避免常规的逻辑。

在实际生活中，尤其是在注册用户名的时候，会非常明显的提示你很多特殊符号不能用，其内部原因也是一样的。

结论:

涉及到敏感数据部分，尽量不要自己拼接，交给现成的方法拼接即可；

SQL注入问题的解决方式：execute方法自动帮你解决；

4.3 功能补充

import pymysql

# 创建链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='456852',
    database='mydb',
    charset='utf8',
    autocommit=True  # 涉及到增删改 自动二次确认
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组织成字典
sql1 = 'select * from userinfo'
sql2 = 'insert into userinfo(name,password) values(%s,%s)'
sql3 = 'update userinfo set name="jasonNB" where id=1'
sql4 = 'delete from userinfo where id=2'

# 1.查询语句可以正常执行并获取结果
# cursor.execute(sql1)
# 2.插入语句能够执行 但是并没有影响表数据
# cursor.execute(sql2,('jackson',666))
# 3.更新语句能够执行 但是并没有影响表数据
# res = cursor.execute(sql3)
# print(res)
# 4.删除语句能够执行 但是并没有影响表数据
# res = cursor.execute(sql4)
# print(res)

'''针对增删改操作 需要二次确认才可生效'''
# cursor.execute(sql2,('jackson',666))
# conn.commit()
# cursor.execute(sql3)
# conn.commit()
# cursor.execute(sql4)
# conn.commit()

# 执行多次SQL语句
cursor.executemany(sql2, [('jason111', 123), ('jason222', 321), ('jason333', 222)]) 

# 主动关闭链接 释放资源
# conn.close()

posted @ 2022-02-23 23:10 JZEason 阅读(156) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Loading

JZEason's Blog