mysql5/navicat软件使用/多表查询/sql注入
- Navicat 可视化软件
- 多表查询练习题
- PY操作mysql
- 获取结果
- SQL注入问题
- 小知识点补充（了解）
Navicat 可视化软件
个人学习破解使用下载【推荐正版】
链接：https://pan.baidu.com/s/1dxmAzHPmW_8TIqeQcJCS1g
提取码：5s9i
链接数据库
多表查询练习
练习数据也存放在上述网盘中
练习题
1、查询所有的课程的名称以及对应的任课老师姓名
#先确定需要几张表 课程表 老师表
#简单查看每张表中的数据
#思考查询逻辑多表查询（连表操作）
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
4.查询平均成绩大于八十分的同学的姓名和平均成绩
1.先确定需要几张表 成绩表，学生表
2.简单查看表中数据
select * from stuent;
select * from score;
3.先查看成绩表中大于80分的数据
select student_id,num from score where num >80 group by student_id ;
3.1按照学生编号分组，利用聚合函数avg求出所有学生编号对应的平均成绩
select student,avg(num) from score group by student_id having avg(num) >80;
3.2基于上述分组之后的结果筛选出平均成绩大于80的数据
select student_id,avg(num) from score group by student_id having avg(num) >80;
4.最终结果需要上述sql语句的结果表中获取一个字段和学生表中获取一个字段
select * 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;
#最终答案
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.两种解题思路
--直接其它老师教的课然后一步步查到学生
--查报了李平老师的学生编号然后取反即可（推荐）
4.先获取李萍老师的课程id号
select tid from teacher where tname = '李平老师'；
5.子查询获取课程编号·
select cid from course where teacher_id = （select tid from teacher where tname = '李平老师'）
6.根据课程编号去成绩表中筛选出所有报了课程编号的数据
select distinct student_id from score where course_id in （select cid from course where teacher_id = (select tid from teacher where tname '李平老师')）
7.根据上述学生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.先获取物理和体育课程的id号
select cid from course where cname in ('物理'，'体育')；
4.根据课程的id号先去成绩表中过滤掉没有选择这些课程的数据
select * from score where course_id in (select cid from course where cname in('物理'，'体育'))
5.基于上述表统计每个学生编号报了几门课
select score.student_id from score where course_id in (select cid from course where cname in （'物理'，'体育')）
group by score.student_id having count(score.course_id)=1
6.根据上述学生id获取学生姓名
select sname from student where sid in (select score.student_id from score where course_id in (select cid from course where cname in ('物理','体育’））
group bt score.student_id having count(score.course_id) = 1)
9、查询挂科超过两门(包括两门)的学生姓名和班级
1.先筛选出分数小于60的数据
select student_id from score where num <60;
2.按照学生id分组然后计数即可
select student_id from score where num < 60 group by student_id having count(course_id ) >= 2;
3.先链接班级表和学生表
select * from class inner join student on class.cid = studnet.class_idl
4.#合并23的sql
select
class.caption,
student.sname
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
第三方模块： pip3 install pymysql
import pymysql
#链接服务器
conn = pymysql.connect(
host = '127.0.0.1',
port =3306,
user ='root',
password = '123',
database = 'db5',
charset = 'utf8mb4'
autocommit = True
)
#2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#3.编写sql语句
sql1 = 'select * from userinfo'
#4. 发送服务端
cursor.execute(sql1)
#获取命令的执行结果
res = cursor.fetchall()
print(res)
获取结果
cursor.fetchone() #获取结果集中一条数据
cursor.fetchall() #获取结果集中所有数据
cursor.fetchmany() #获取结果集中指定条的数据
#cursor.scorll(2,mode='relative') 基于当前位置往后移动
#cursor.scroll(0,mode = 'absolute') #j基于数据集开头的位置往后移动
SQL注入问题
前戏
只需要用户名即可登录
不需要用户名和密码也能登录
问题
SQL注入
select * from userinfo where name='jason' -- haha' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- heihei' and pwd=''
本质
利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
针对用户输入的数据不要自己处理 交给专门的方法自动过滤
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password)) # 自动识别%s 并自动过滤各种符合 最后合并数据
补充
cursor.executemany()
小知识点补充（了解）
1.as语法
给字段起别名，起表名
2.comment语法
给表，字段添加注释信息
create table server(id int) comment '这个是非常有意思的'
create table t1(
id int comment '用户编号',
name varchar(16) comment '用户名'
) comment '用户表'；
#查看
show create table use infomation_schema
3.concat,concat_ws 语法
concat_ws 如果有多个字段，并且分隔符一致，可以使用该方法减少代码
4.exists语法
select * from userinfo where exists (select * from department where id <100)
exists 后面的sql语句如果有结果那么执行前面的sql语句
如果没有结果则不执行
