【8.18】MySQL数据库（5）

标签：-- 数据库 course 8.18 student MySQL where id select

学习内容概要

Navicat可视化软件
多表查询练习题
python操作MySQL
- 需要用到pymysql 第三方模块
SQL语法知识点补充了解
- as 语法
- exist 语法
- concat 语法
- concat_ws 语法
- comment 语法

内容详细

Navicat可视化软件

介绍：
- 可以从当很多数据库软件的客户端主要用于MySQL的客户端

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级

-- 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 student;
-- select * from score;
# 3.先查询成绩表中平均成绩大于80分的数据
# 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.简单的查看表中数据
# 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 = '李平老师' )) )




-- 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、查询挂科超过两门(包括两门)的学生姓名和班级
# 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',  # IP地址
    port=3306,  # mysql的端口号
    suer='root',  # 用户
    password='123',  # 密码
    database='db5',  # 链接想要链接的库
    charset='utf8mb4',  # 指定字符编号 
    
)


# 2.产生一个游标对象（类似于cmd的等待输入的光标）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.编写SQL语句
sql1 = 'select * from emp'  # 查看emp表语句

# 4.发送给服务端

cursor.execute(sql1)

# 获取命令的执行结果
res = curson.fetchall()
print(res)  # 获取emp表内的数据


=============================================================
=============================================================
# 执行  增 、改 、 删 、 操作 代码会有变动


import pymysql

#1. 链接服务端
conn = pymysql.connect(
   host='127.0.0.1',  # IP地址
    port=3306,  # mysql的端口号
    suer='root',  # 用户
    password='123',  # 密码
    database='db5',  # 链接想要链接的库
    charset='utf8mb4',  # 指定字符编号 
    autocommit=True  # 自动执行conn.commit
    
)


# 2.产生一个游标对象（类似于cmd的等待输入的光标）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.编写SQL语句

# sql1 = 'insert into emp(name,pwd) values("make1","123")'  # 增加数据

# sql1 = 'delete from emp'  # 删除数据

# sql1 = 'update emp set name="make" where id=1 '  # 改动数据


# 4.发送给服务端

ret = cursor.execute(sql1)
print(ret)  # 返回执行SQL语句后 表中受影响的行数（无关紧要）

获取结果的方式

使用不同的命令会获取到不同的结果
- cursor.fetchone() 获取一条数据
- cursor.fetchall() 获取所有数据
- cursor.fetchmany() 在括号里写入数字几就获取几条（超过所有的数据条数则显示所有）



import pymysql

# 查看表中数据

#1. 链接服务端
conn = pymysql.connect(
   host='127.0.0.1',  # IP地址
    port=3306,  # mysql的端口号
    suer='root',  # 用户
    password='123',  # 密码
    database='db5',  # 链接想要链接的库
    charset='utf8mb4',  # 指定字符编号 
    
)


# 2.产生一个游标对象（类似于cmd的等待输入的光标）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)


# 3.编写SQL语句
sql1 = 'select * from emp'  # 查看emp表语句


# 4.发送给服务端
cursor.execute(sql1)



# 获取命令的执行结果
# res = curson.fetchall()
# print(res)  # 获取emp表内所有的数据

# res = curson.fetchone()
# print(res)  # 获取emp表内一条的数据

# res = curson.fetchmany()
# print(res)  # 括号里写几  就可以对应获取emp表内几条的数据 （超过表内所有数据条数  则显示所有条数） 


拓展：类似于文件光标移动 和 文件获取的关系！！
res = curson.fetchone()
print(res)  # 拿到第一条数据
curson.scroll(1,mode='relative')  # 基于当前位置 往后移动 1 （参数是几 就往后移动几）
res = curson.fetchone()
print(res)  # 此打印结果就为第3条数据
========================================================
res = curson.fetchone()
print(res)  # 拿到第一条数据
corsor.scroll(2,mode='absolute')  # 基于数据开头的位置往后移动 2 （参数是几 就往后移动几）
res = curson.fetchone()
print(res)  # 此打印结果就为第3条数据

SQL注入问题

含义：
- 利用一些特殊符号的组合从而产生特殊了特殊的含义蒙蔽逃脱了正常的逻辑检测

# 事项：
	在python中操作mysql 去查看数据库的数据时
    在正常的情况需要验证用户名密码
    
SQL注入：	
	select * from userinfo where name='jason' -- haha' and pwd=''  
    
	select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''
    
# 过程：上述利用sql注入漏洞去获取了数据

# 措施：针对输入问题 在 cursor.execute 后面加入方法会自动过滤(sql,(username,password))

sql = "select * from emp where name=%s and pwd=%s"
cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据
    
# 补充：
	  cursor.executemany()  # 利用占位符%s 来插入多条数据 执行多条SQL语句

 
    sql = 'insert into emp(name,pwd) values(%S,%s)'
    cursor.executemany(sql,[('make1',123),('make2',123),('make3',123),('make4',123)])

SQL语法知识点补充


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语句
	如果没有结果则不执行

标签：--,数据库,course,8.18,student,MySQL,where,id,select
From： https://www.cnblogs.com/55wym/p/16600471.html

【8.18】MySQL数据库（5）

学习内容概要

内容详细

Navicat可视化软件

多表查询练习题

python操作MySQL

获取结果的方式

SQL注入问题

SQL语法知识点补充

相关文章

赞助商

阅读排行