s4day60 上节回顾： 1. MySQL：文件管理的软件 2. 三部分： - 服务端 - SQL语句 - 客户端 3. 客户端： - mysql - navicat 4. 授权操作 - 用户操作 - 授权操作 5. SQL语句 - 数据库操作 - create database xx default charset utf8; - drop database xx; - 数据表 - 列 - 数字 整数 小数 - 字符串 - 时间 - 二进制 - 其他：引擎，字符编码，起始值 - 主键索引 - 唯一索引 - 外键 - 一对多 - 一对一 - 多对多 - 数据行 - 增 - 删 - 改 - 查 - in not in - between and - limit - group by having - order by - like "%a" - left join xx on 关系 - 临时表 select * from (select * from tb where id< 10) as B; - select id, name, 1, (select count(1) from tb) from tb2 SELECT student_id, (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文, (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学, (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语 from score as s1; - 条件 select course_id, max(num), min(num), min(num)+1, case when min(num) <10 THEN 0 ELSE min(num) END as c from score GROUP BY course_id select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc; PS: 数据放在硬盘上 思想： - 操作 - 设计 今日内容： 1. 练习题 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； -- select score.student_id,student.sname from score -- -- left join student on score.student_id=student.sid -- -- where course_id =1 or course_id =2 GROUP BY student_id HAVING count(course_id) > 1 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名； -- select student_id from score where course_id in ( -- select cid from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师" -- ) GROUP BY student_id having count(course_id) = (select count(cid) from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师") -- -- 10、查询有课程成绩小于60分的同学的学号、姓名； -- select student_id from score where num < 60 GROUP BY student_id -- select DISTINCT student_id from score where num < 60 -- 查询没有学全所有课的同学的学号、姓名； 11、查询没有学全所有课的同学的学号、姓名； -- select student_id,count(1) from score GROUP BY student_id HAVING count(1) < (select count(cid) from course); -- -- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； -- select course_id from score where student_id = 1; -- select student_id from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id -- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名； -- select course_id from score where student_id = 1; -- select student_id,count(1) from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(course_id) from score where student_id = 1) -- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； -- 获取和方少伟选课个数相同的通许 -- select count(1) from score where student_id = 1; -- -- select student_id from score where student_id in ( -- select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1) -- ) and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1) -- -- -- insert into tb(student_id,course_id,num) -- -- select student_id,2,(SELECT AVG(num) from score where course_id = 2) from score where course_id != 2 -- 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； -- 1 90 80 99 -- 2 90 80 99 -- SELECT -- student_id, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学, -- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语 -- from score as s1; -- -- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； -- select course_id,max(num),min(num),min(num)+1,case when min(num) <10 THEN 0 ELSE min(num) END as c from score GROUP BY course_id -- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc; pymysql模块： pip3 install pymysql -i https://pypi.douban.com/simple Python模块：对数据库进行操作（SQL语句） 1. Python实现用户登录 2. MySQL保存数据 - 连接、关闭（游标） - execute() -- SQL注入 - 增删改： conn.commit() - fetchone fetchall - 获取插入数据自增ID 练习： 权限管理 权限表： 1 订单管理 2 用户管理 3 菜单管理 4 权限分配 5 Bug管理 用户表： 1 Alex 2 egon 用户权限关系表： 1 1 1 2 2 1 Python实现： 某个用户登录后，查看自己拥有所有权限