一、多表查询
方式一:关键字链接
- inner join
- left join
- right join
- union
# 数据准备————随便建立两表
CREATE TABLE `class_info` (
`id` int(11) NOT NULL DEFAULT '0',
`class_name` varchar(255) DEFAULT NULL,
`teacher_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `class_info` VALUES (1, '一年级一班', '李华');
INSERT INTO `class_info` VALUES (2, '一年级二班', '张红');
INSERT INTO `class_info` VALUES (3, '二年级一班', '米老师');
INSERT INTO `class_info` VALUES (4, '二年级二班', '刘老师');
INSERT INTO `class_info` VALUES (5, '三年级一班', '王老师');
INSERT INTO `class_info` VALUES (6, '三年级二班', '范老师');
___________________________________________
CREATE TABLE `student_info` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`class` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `student_info` VALUES (0, '小刘', 15, '1');
INSERT INTO `student_info` VALUES (2, '小王', 14, '1');
INSERT INTO `student_info` VALUES (3, '小红', 16, '2');
INSERT INTO `student_info` VALUES (4, '小李', 16, '3');
INSERT INTO `student_info` VALUES (5, '小肖', 17, '4');
INSERT INTO `student_info` VALUES (6, '小风', 18, '5');
INSERT INTO `student_info` VALUES (7, '小双', 18, '6');
INSERT INTO `student_info` VALUES (8, '小哲', 15, '2');
INSERT INTO `student_info` VALUES (9, '小智', 17, '3');
INSERT INTO `student_info` VALUES (10, '小霍', 16, '5');
# 1.inner join ____ on ____ 内连接 只拼接双表共有的数据
SELECT student_info.id as '学号',student_info.`name` as '姓名', class_info.class_name as '班级', class_info.teacher_name as '班主任' from student_info INNER JOIN class_info on student_info.class = class_info.id;
# 2.left join 左连接 ,以关键字左边的表为基准,展示左表所有的数据
# 先去给class_info底下加一行,但是学生表不动,我们拿班级表放在左边为例
SELECT student_info.id as '学号',student_info.`name` as '姓名', class_info.class_name as '班级', class_info.teacher_name as '班主任' from class_info LEFT JOIN student_info on student_info.class = class_info.id;
# 3. right join 和left join 同理,就不再描述了
# 4. union 全链接
# 以左右表为基准 展示所有的数据 各自没有的全部NULL填充
方式二:子查询
将一张表的查询结果括号括起来当作另外一条SQL语句的条件
类似我们处理事情先处理一步,然后基于第一步的结果进行第二步
# 还是拿上面的两表做例子
# 比如说查询小智的班级的班主任名字
1.先获取小智的班级id
SELECT class from student_info WHERE name = '小智'; # 3
2.根据班级id获取班级的班主任名字
SELECT teacher_name FROM class_info WHERE id = 3; # 米老师
子查询:# 合并
SELECT teacher_name FROM class_info WHERE id = (SELECT class from student_info WHERE name = '小智'); # 米老师
# 注意,不要把1的分号也复制到子查询中哦~
二、第三方可视化SQL管理软件——navicat
可以充当很多数据库软件的客户端 最主要的用于MySQL
该软件也支持MariaDB、MongoDB等
从官网下载 然后网上找破解工具
# 推荐使用注册机的方法 可以使用最新版16
https://cmsblogs.cn/3902.html
# 使用参考
https://www.bilibili.com/video/BV1yA41147Vi/?from=search&seid=14234973416271175041&spm_id_from=333.337.0.0&vd_source=f92dcdbc952254e0ee168b365a4b444e
- 注意 MYSQL 的端口号3306
三、多表查询练习题
- 前提:把结构和数据的文件通过navicat ——库——右键——运行SQL文件——选中执行,再刷新一下库刷新一下表,准备好练习的素材
# 注意:做多表查询的题,一定一步步来,先确定需要用到几张表,去喽一眼这些表的数据长啥样,然后思考数据间的逻辑分步来
1.查询所有的课程的名称以及对应的任课老师姓名
# 第一步:要用到课程表,老师表,去看下数据长啥样
# 第二步:连表,取两个字段,用join类方法好一些
# 第三步:课程表做左表,老师表做右表,inner join ...on ...链接
SELECT
cname AS '课程名',
teacher.tname AS '任课老师'
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
———————————————————————————————————————————————————————————
2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 第一步,要用到学生表,成绩表,去看下数据长啥样
# 第二步,连表,取两个字段,用join类方法好一些
# 第三步,先去按学生id分组课程表,拿到每个学生的Id和平均分
SELECT student_id,AVG(num) FROM score GROUP BY student_id;
# 第四步,拼接学生表,学生表做左表,基于第三步的表做右表,inner join ...on ...链接
SELECT
student.sname AS '姓名',
t.a AS '平均分'
FROM
student
INNER JOIN ( SELECT student_id, AVG( num ) AS a FROM score GROUP BY student_id ) AS t ON student.sid = t.student_id;
———————————————————————————————————————————————————————————
3.查询没有报李平老师课的学生姓名
# 第一步,通过看库的逆向模型 直接的要用到很多表,学生表,课程表,老师表,分数表
# 第二步,先去拿李平老师的老师ID号
SELECT tid FROM teacher WHERE tname = '李平老师';
# 第三步,根据第二步的ID号去课程表拿到这个ID教了几门课的课程ID号
SELECT cid FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师');
# 第四步,根据第三步的课程号,去筛选成绩表中报了这个课程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 = '李平老师'));
# 这里就很简单了,基于第四步的学生ID号去学生表拿到姓名即可,然后取反
SELECT student.sname as '姓名' 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 = '李平老师')));
# 最后再点击一键美化
————————————————————————————————————————————————————————————
4.查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)
# 第一步,要用到三张表,学生表,课程表,成绩表
# 第二步,学生表做最后一表,课程表做最内一表
# 第三步,先去课程表拿到物理、体育的课程ID号
SELECT cid FROM course WHERE cname = '体育' or cname = '物理';
# 第四步,拿着第三步的课程ID号去成绩表,找选了这些课的学生ID
SELECT * FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理');
# 第五步,根据题意,只要选这两门课数为1的学生ID号,以学生ID分组
SELECT student_id FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理') GROUP BY student_id HAVING COUNT(course_id) = 1;
# 第六步,很简答了,不用连表,子查询使用
SELECT sname FROM student WHERE sid in (SELECT student_id FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理') GROUP BY student_id HAVING COUNT(course_id) = 1);
# 最后再点击一键美化
——————————————————————————————————————————————————————————————
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 第一步,要用到学生表,班级表,成绩表
# 第二步,去成绩表找出成绩<60的数据
select * from score where num < 60;
# 第三步,按照学生ID分组,计数挂科>=2的
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 第四步,需要两个字字段,用连表方式,inner join ...on ...链接班级表和学生表
select from class inner join student on class.cid = student.class_id;
# 第五步,把第三步的数据添加到筛选条件即可
SELECT student.sname as '学生名', class.caption as '班级名' 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);
四、python操作mysql
1.先下载第三方模块,pymysql
2.代码实操
#1.先连接mysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='15679',
db='db2',
charset='utf8mb4',
autocommit=True # 自动二次验证增删改查
)
#2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 上面这句的括号内,不填参数是返回元组,加参数会反字典,K是表的字段名
#3.编写sql语句
sql = 'select * from score;'
#4.发送sql语句
cursor.execute(sql)
# 5.获取
# res = cursor.fetchall() # 这个是拿所有的返回数据
res = cursor.fetchmany(2) # many可以自己指定多少条
# one是第一个数据
print(res)
conn.close()
标签:info,多表,Python,class,查询,student,WHERE,id,SELECT
From: https://www.cnblogs.com/wznn125ml/p/16933577.html