mysql-5

Navicat可视化软件

可以充当很多数据库软件的客户端 提供可视化操作数据库的快捷方式（鼠标点击） 最主要的用于MySQL
1.下载---官网https://www.navicat.com.cn/（正版收费）---破解版（百度查询）

多表查询练习题

	1、查询所有的课程的名称以及对应的任课老师姓名
	-- 1、查询所有的课程的名称以及对应的任课老师姓名
	# 1.先确定需要几张表 课程表 老师表
	# 2.简单查看每张表中的数据
	-- select * from course;
	-- select * from teacher;
	# 3.思考查询逻辑  多表查询(连表操作)
	-- SELECT
	-- 	course.cname,
	-- 	teacher.tname 
	-- FROM
	-- 	course
	-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;

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

	1.确定需要哪些表（成绩表 学生表）
	2.查看所需表中数据
	SELECT *from score;
	SELECT * from student;
	3.1按照学生编号分组 利用聚合函数avg求出学生编号对应的平均成绩
	SELECT student_id,avg(num) from score GROUP BY student_id;
	3.2基于上述分组之后的结构筛选出平均成绩大于80的数据
	SELECT student_id,avg(num) from score GROUP BY student_id HAVING avg(num) > 80;
	\* 针对select后面通过函数或者表达式编写的字段为了后续取值方便 一般需要重命名成普通字段*\
	SELECT student_id,avg(num) as 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;
	\* 将sql语句当做表来使用 连接的时候需要使用as起表名*\
	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;

3、查询没有报李平老师课的学生姓名

-- 1.先确定用到的表（老师表 课程表 学生表 分数表）
-- 2.查看表中数据
-- SELECT *from teacher;
-- SELECT * from course;
-- SELECT *from student;
-- SELECT *from score;
-- 3.两种解题思路
-- 	1.直接查看其他老师教的课然后再查询学生
-- 	2.查看报名李平老师课的学生编号然后取反即可（推荐）
-- 4.先获取李平老师老师教授的课程号
-- 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 ='李平老师')))

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

# 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 BY score.student_id HAVING count(score.course_id) = 1)

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

-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1.先筛选出分数小于60的数据
-- SELECT * 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 = student.class_id;
-- 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


# 1.链接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db5',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删操作自动执行conn.commit
)
# 2.产生一个游标对象(等待输入命令)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.编写SQL语句
sql1 = 'select * from userinfo'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall()
print(res)

获取结果

cursor.fetchone()   # 获取结果集中一条数据
cursor.fetchall()   # 获取结果集中所有数据
cursor.fetchmany()  # 获取结果集中指定条的数据
'''类似于文件光标的概念'''
# cursor.scroll(2, mode='relative')  # 基于当前位置往后移动
cursor.scroll(0, mode='absolute')  # 基于数据集开头的位置往后移动

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 '这个server意思是服务器表'
	create table t1(
    	id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';
	"""
	查看注释的地方
		show create table 
		use information_schema
	"""
3.concat、concat_ws语法
	concat用于分组之前多个字段数据的拼接
 	concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码
4.exists语法
	select * from userinfo where exists (select * from department where id<100)
	exists后面的sql语句如果有结果那么执行前面的sql语句
	如果没有结果则不执行