多表联查，navicat,pymysql

目录

• 多表联查的两种方式
• 小知识点
• navicat可视化
• 多表查询练习题
• pymysql操作MySQL
• sql注入问题
• 作业

多表联查的两种方式

学会连表操作之后也就课连接N多张表

思路：将拼接之后的表起别名当初一张表再去与其他表拼接 在起别名当作一张表 再去与其他表拼接 其次往复即可

1. 连表操作

   • inner join 内连接

     select * from emp inner join dep on emp.dep_id = dep.id;
     # 只连接两张表中的共有的数据部分
     

     

   • left join 左连接

     select * from emp left join dep on emp.dep_id = dep.id;
     # 以左表为基准 展示左表所有的数据 如果没有对应项则用null填充
     

     

   • right join 右连接

     select * from emp right join dep on emp.dep_id = dep.id;
     # 以右表为基准，展示所有右表的数据如果没有对应则用null填充
     

     

   • union 全连接

     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;
     # 以左右表为基准 展示所有的数据各自没有的全部null填充
     

     

2. 子查询
   将一条SQL语句用括号括起来当初另外一条SQL语句的查询条件

   # 题目：求姓名是jason的员工部门名称
   #子查询类似于我们日常生活中解决问题的方式：分布操作
   #1.先根据jason获取部门编号
   select dep_id from emp where name='jason';
   #2.再根据部门编号获取部门名称
   select name from dep where id =200;
   # 总结
   select name from dep where id =(select dep_id from emp where name='jason');
   '''
   很多时候多表查询需要结合实际情况判断用那种更所时候甚至是相互配合使用
   '''
   

   

3. 很多时候多表查询需要结合实际情况判断用那种更所时候甚至是相互配合使用

小知识点

1. concat 与 concat_ws

   • concat用于分组之前的字段拼接操作

     select concat(name,'$',sex) from emp;
     

   • concat_ws也是分组之前拼接多个字段并且中间的连接符一致

     select concat_ws('|',name,sex,age,dep_id) from emp;
     

2. exists
   sql1 exists sql2
   sql2有结果的情况下才会执行sql1否则不执行sql1返回空数据

   select name from dep where exists (select dep_id from emp where name='jj');
   
   #常见exists
   drop table if exists e1;
   

   

3. 表相关SQL补充

   • 修改表名

     alter table 表名 rename 新表名;
     

   • 添加字段

     alter table 表名 add 字段名 字段类型(数字) 约束条件;
     # 约束条件可以不写 默认添加最后一行
     alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;  # 添加字段到指定字段下面
     alter table 表名 add 字段名 字段类型(数字) 约束条件 first;# 添加字段到第一行
     

   • 修改字段

     alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
      	alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
     

   • 删除字段

     alter table 表名 drop 字段名;  # 删除字段
     

   • 关于外键的添加和删除
     增加

     #1.再已经创建好的表后必须要先添加字段，然后在添加外键。
     alter table 表名 add 字段 字段类型(数字) 约束条件;
     #2.添加外键
     alter table 表名 add constraint 外键名称 foreign key(本表字段) references 要关联表(要关联字段);
     
     

     

     删除

     #1.先删除外键
     alter table 表名 drop foreign key 外键名;
     #key 名这个也要删除
     alter table 表名 drop key 名;
     # 最后删除这个外键字段
     alter table 表名 drop 字段名
     

     

navicat可视化

第三方开发的用来充当数据库客户端简单快捷的操作界面

无论第三方软件有多花里胡哨 底层的本质还是sql

能操作数据库的第三方可视化软件有很多 其中针对mysql最出名的就是navicat

1. 浏览器搜索navicat直接下载
   版本很多、能够充当的数据库客户端也很多

2. 本地化方式
   推荐无限使用，

3. 常用操作
   有些功能可能需要自己修改SQL预览
   创建库 表 记录 外键
   逆向数据库到模型 模型创建
   新建查询可以编写SQL语句并自带提示功能

4. SQL语句注释语法

   --单行注释
   # 单行注释

   \**\ 多行注释

   转存储SQL文件，与导入SQL文件

多表查询练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级

-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表  课程表 分数表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- SELECT
-- 	teacher.tname,
-- 	course.cname
-- FROM
-- 	course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
-- 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;
-- 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid NOT IN (
-- 		SELECT DISTINCT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					teacher_id = (
-- 						SELECT
-- 							tid
-- 						FROM
-- 							teacher
-- 						WHERE
-- 							tname = '李平老师'
-- 					)
-- 			)
-- 	)
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid IN (
-- 		SELECT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					cname IN ('物理', '体育')
-- 			)
-- 		GROUP BY
-- 			student_id
-- 		HAVING
-- 			count(course_id) = 1
-- 	) 
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);

pymysql操作MySQL

pymysql模块
	pip3 install pymysql
 
import pymysql

# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db4_03',
    charset='utf8mb4'
)
# 2.产生游标对象
# cursor = conn.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

cursor.scorll()  # 设置游标函数

sql注入问题

首先上代码，与数据库数据

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='d7',
    charset='utf8',
    autocommit=True
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
while True:
    name = input('username>>>:')
    pwd = input('password>>>:')
    sql = """select * from user where name='%s' and pwd= '%s' """ % (name, pwd)

    restaue = cursor.execute(sql)
    if restaue <= 0:
        print('登录失败用户名或密码错误')
        continue
    print('登录成功')
    result = cursor.fetchall()
    print(result)
    
    
    
# 数据库数据 
INSERT INTO `user` (`id`, `name`, `pwd`) VALUES (1, 'jj', '123');
INSERT INTO `user` (`id`, `name`, `pwd`) VALUES (2, 'qwe', 'qwe');
INSERT INTO `user` (`id`, `name`, `pwd`) VALUES (3, 'qq', '1234');

看上去没什么问题实际运行试一下

确实没问题，那么请看下图

只输入正确用户名了，竟然能登录成功了，明明没有输入密码，

还有下面现象

再不知道用户名和密码的情况下，然也能登录成功。

这就是著名的sql注入
sql注入：利用特殊符号的组合产生特殊的含义从而避开正常的逻辑

select * from user where name='qwe' -- kasdjksajd' and pwd=''


select * from user where name='xyz' or 1=1 -- aksdjasldj' and pwd='' 

针对上述的sql注入问题 核心在于手动拼接了关键数据 交给execute处理即可

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    database='d7',
    charset='utf8',
    autocommit=True
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
while True:
    name = input('username>>>:')
    pwd = input('password>>>:')
    sql = """select * from user where name=%s and pwd= %s """

    restaue = cursor.execute(sql,(name,pwd))
    if restaue <= 0:
        print('登录失败用户名或密码错误')
        continue
    print('登录成功')
    result = cursor.fetchall()
    print(result)

这样就解决了sql注入问题

作业

1. 查询所有的课程的名称以及对应的任课老师姓名

2. 查询学生表中男女生各有多少人

3. 查询物理成绩等于100的学生的姓名

4. 查询平均成绩大于八十分的同学的姓名和平均成绩

5. 查询所有学生的学号，姓名，选课数，总成绩

6. 查询姓李老师的个数

7. 查询没有报李平老师课的学生姓名

8. 查询物理课程比生物课程高的学生的学号

   SELECT t1.student_id FROM
   (SELECT * FROM score WHERE course_id in (
   select cid from course where cname ='物理')) t1
   INNER JOIN
   (SELECT * FROM score WHERE course_id in (
   select cid from course where cname ='生物') )t2
   ON t1.student_id = t2.student_id
   
   WHERE t1.num>t2.num;
   

9. 查询没有同时选修物理课程和体育课程的学生姓名

   SELECT sname FROM
   student
   WHERE sid IN(
   
   
   SELECT score.student_id from
   score
   INNER JOIN
   course
   ON
   score.course_id=course.cid
   WHERE cname in ('物理','体育')
   GROUP BY score.student_id
   HAVING COUNT(score.course_id) !=2);
   

10. 查询挂科超过两门(包括两门)的学生姓名和班级

    SELECT student.sname,class.caption from 
    student
    INNER JOIN
    class
    ON
    student.class_id=class.cid
    WHERE
    student.sid in 
    (
    SELECT student_id FROM
    score
    WHERE num<60
    GROUP BY student_id
    HAVING COUNT(course_id)>=2); 
    
    

11. 查询选修了所有课程的学生姓名

    SELECT sname FROM student
    WHERE sid in (
    
    
    SELECT score.student_id FROM
    course
    left JOIN
    score
    ON
    course.cid = score.course_id
    GROUP BY score.student_id
    HAVING COUNT(score.course_id)=(SELECT count(*) FROM course)
    );
    

12. 查询李平老师教的课程的所有成绩记录

    SELECT score.num FROM score
    INNER JOIN
    (
    SELECT cid FROM course WHERE teacher_id in (
    SELECT tid FROM teacher WHERE tname = '李平老师'
    )) as t1
    on
    score.course_id = t1.cid;
    

13. 查询全部学生都选修了的课程号和课程名

    SELECT cid,cname FROM course
    WHERE cid in(
    SELECT course_id FROM score GROUP BY course_id
    HAVING COUNT(student_id) = (SELECT COUNT(*) FROM student));
    

14. 查询每门课程被选修的次数

    SELECT GROUP_CONCAT(distinct course.cname),count(score.student_id) FROM
    course
    LEFT JOIN
    score
    ON
    score.course_id=course.cid
    GROUP BY course.cid;
    

15. 查询只选修了一门课程的学生姓名和学号

    SELECT sname,sid FROM student
    WHERE sid in (
    SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=1
    );
    

16. 查询所有学生考出的成绩并按从高到低排序（成绩去重）

    SELECT distinct num FROM score ORDER BY num DESC;
    

17. 查询平均成绩大于85的学生姓名和平均成绩

    SELECT student.sname,t1.num FROM student
    RIGHT JOIN
    (SELECT student_id,avg(num) as num FROM score GROUP BY student_id HAVING avg(num)>85) t1
    ON
    student.sid = t1.student_id;
    

18. 查询生物成绩不及格的学生姓名和对应生物分数

    SELECT student.sname,score.num FROM score
    INNER JOIN
    student
    ON
    score.student_id= student.sid
    WHERE score.course_id=(SELECT cid FROM course WHERE cname = '生物')
    AND score.num<60;
    

19. 查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

    SELECT sname FROM student
    WHERE sid = 
    (
    SELECT t1.id FROM(
    SELECT score.student_id as id,AVG(num) as num FROM score
    INNER JOIN
    (
    SELECT cid FROM course WHERE teacher_id in (
    SELECT tid FROM teacher WHERE tname = '李平老师'
    )) as t1
    on
    score.course_id = t1.cid
    GROUP BY score.student_id
    HAVING
    AVG(num)) as t1
    ORDER BY t1.num desc LIMIT 1);
    

20. 查询每门课程成绩最好的前两名学生姓名

21. 查询不同课程但成绩相同的学号，课程号，成绩

22. 查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23. 查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24. 任课最多的老师中学生单科成绩最高的学生姓名