多表查询
1.查询所有的课程的名称以及对应的任课老师姓名
1.1 确定涉及表 课程表 教师表
1.2 查看表中内容 需要两张表中的各一条数据
1.3 多表查询-连表操作
通过教师id号做连表操作获得课程的名称以及对应的任课老师姓名
SELECT
teacher.tname,
course.cname
FROM
teacher
INNER JOIN course ON teacher.tid = course.teacher_id;
2.查询平均成绩大于八十分的同学的姓名和平均成绩
2.1 确定涉及表 学生表 成绩表
2.2 查看表中内容 需要两张表中的各一条数据
2.3 多表查询-连表操作
通过成绩表获得平均成绩大于80的学生编号(需要将平均成绩重命名成普通字段 后续需要该数据)
SELECT student_id,avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num )> 80;
通过上述获得的学生与平均成绩大于80的表与学生表根据学生id号连表获得同学的姓名和平均成绩(对上述表重命名s1)
SELECT
student.sname,
s1.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 s1 ON student.sid = s1.student_id;
3.查询没有报李平老师课的学生姓名
3.1 确定涉及表 教师表(与课程表) 学生表(与成绩表) 课程表(教师表、成绩表) 成绩表(学生表 课程表)
3.2 查看表中内容 只需要学生姓名来源于一张表但涉及多表数据
3.3 多表查询-子查询
通过教师表与课程表子查询获得李平老师教授的课程编号(教师表)
select tid from teacher where tname='李平老师'; # 获得李平老师id号
获得李平老师教师的课程编号(课程表)
select cid from course where teacher_id in (select tid from teacher where tname='李平老师');
通过课程id号得到选择李平老师课程的学生编号(成绩表)
select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'))
通过选择的学生编取反得到没有报李平老师课的学生姓名(学生表)
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 = '李平老师' )))
4.查询没有同时选修物理课程和体育课程的学生姓名
4.1 确定涉及表 课程表(成绩表) 学生表(成绩表) 成绩表(课程表 学生表)
4.2 查看表中内容 需要数据是课程表与学生表中的数据 这两表需要通过成绩表连接
4.2 多表查询-子查询
通过课程表获得物理课程和体育课程的id号
select cid from course where cname in ('物理','体育');
通过成绩表得到选择物理课程和体育课程的学生id并统计课程数(选择一门 选择两门)
select student_id,count(course_id) from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id;
筛选掉两门课程都选择的学生id 获得没有同时选修物理课程和体育课程的学生id
select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id having count(course_id)<2; # 不能对count(course_id)起别名
连表获得没有同时选修物理课程和体育课程的学生姓名
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 )< 2 );
结果：理解 钢蛋 刘三
5.查询挂科超过两门(包括两门)的学生姓名和班级
5.1 确定涉及表 课程表 学生表 班级表
5.2 查看表中内容 学生姓名和班级来源于两个表
5.3 多表查询-连表操作
通过成绩表获得挂科超过两门的学生id
select student_id from score where num<60 group by student_id having count(num)>=2;
连接班级表于学生表获得学生与班级信息
select * from student inner join class on student.class_id=class.cid;
通过上表子查询获得挂科超过两门(包括两门)的学生姓名和班级
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( num )>= 2 );
结果：理解 三年二班
Navicat使用
1.Navicat下载与安装
1.1打开浏览器搜索“navicat”官网并进入；
1.2.点击“产品”功能页面；
1.3.选择你需要下载的版本；
1.4.打开并运行安装包；
1.5.选择“我同意”点击“下一步”；
1.6.选择安装的路径，点击“下一步”；
1.7.选择快捷方式的创建路径，点击“下一步”；
1.8.选择是否创建桌面图标，点击“下一步”；
1.9.点击“安装”即可
2.Navicat使用
2.1 先建立连接 需要输入主机id 端口(3306) 用户名及密码
支持很多数据库的操作(如mysql、postgresql、MariDB等等)
2.2 涉及板块-新建查询(sql语句编写)表、视图、函数、查询、图表、模型等
2.3 使用：直接通过鼠标点就可以新建数据库-新建表-数据的增删改查(并且显示sql语句)
表中有逆向表到模型可以查看表与表之间的图表关系更加直接方便
也可以在新建查询中直接编写sql语句
sql语句美化-使语句更加美观
python操作MySQL
1.第三方模块：pip3 install pymysql
import pymysql
# 连接服务端
conn = pymysql.connect(
host='127.0.0.1', # ip地址
port=3306, # 端口
user='root', # 用户名
password='xxx', # 密码
database='db5', # 数据库
charset='utf8mb4', # 字符编码
autocommit=True # 执行增、删、改时自动二次确认 减少代码conn.commit
)
# 创建游标对象(执行sql 获取数据)
cursor = conn.cursor()
# 编写sql语句
sql1 = 'select * from class'
# 发送给服务端
cursor.execute(sql1)
2 # 获得返回的结果
res2 = cursor.fetchmany(2) # 获取指定条数 # ((1, '三年二班'), (2, '三年三班'))
print(res2)
res = cursor.fetchone() # 获取一行数据 # (3, '一年二班')
print(res)
res1 = cursor.fetchall() # 获取所有数据 # ((4, '二年九班'),)
print(res1)
# 类似于文件光标问题(数据取完后再取数据为空)涉及到光标移动问题
cursor.scroll(2, mode='relative') # 基于当前位置向后移动(数据取完会报错）
res = cursor.fetchone() # 获取一行数据
print(res) # (3, '一年二班')
cursor.scroll(2, mode='absolute') # 基于开头向后移动(从开头移动超过数据范围会报错）
res = cursor.fetchone() # 获取一行数据
print(res) # (3, '一年二班')
3.SQL注入问题
3.1 本质：利用一些特殊符号的组合产生了特殊的含义从而逃脱正常业务逻辑(--注掉了)
select * from userinfo where name='jason' --xixi 'and pwd=''
select * from userinfo where name='xxx' --xixi 'and pwd=''
3.2 解决措施：针对用户输入法人数据不要自己处理 交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据
sql语法
1.as语法：字段起别名、起表名
2.comment语法：给表、字段添加注释
查看注释：show create table
use infomation_schema(基于内存储存的数据表)
3.concat、concat_ws语法
concat用于分组前多个字段的拼接
concat_ws用于多字段拼接并且需要统一分隔符
4.exists语法：exists后面的sql语句如果有结果那么执行前面的sql语句
如果没有结果则不执行
作业
1.利用pymysql编写用户注册登录功能
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
database='db6',
user='root',
password='xxx',
charset='utf8mb4',
autocommit=True
)
cursor = conn.cursor()
# zql语句
sql = "select * from t1 where name=%s and pwd=%s"
username = input('username>>>:').strip()
password = input('password>>>:').strip()
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if res:
print('登录成功')
else:
print('用户或密码错误')
2、查询学生表中男女生各有多少人
2.1 确定涉及表 学生表
2.2 查看表中数据
2.3 姓名分组统计人数
select gender,count(gender) from student group by gender;
3、查询物理成绩等于100的学生的姓名
3.1 确定涉及表 成绩表 学生表 课程表
3.2 查看表中数据 需要成绩及姓名
3.3 多表查询-连表操作
select student_id,num from score where num=100 and course_id=(select cid from course where cname='物理')
select student.sname,num from student inner join (select student_id,num from score where num=100 and course_id=(select cid from course where cname='物理')) as s1 on student.sid=s1.student_id
4、查询所有学生的学号，姓名，选课数，总成绩
4.1 确定涉及表 学生表 课程表
4.2 查看表数据
4.3 通过成绩表得到数据学生编号、总成绩、课程数与学生表拼接
SELECT
student.sid,
student.sname,
num,
course_num
FROM
student
LEFT JOIN ( SELECT student_id, sum( num ) AS num, count( course_id ) AS course_num FROM score GROUP BY student_id ) AS s1 ON student.sid = s1.student_id;
posted on
浙公网安备 33010602011771号