MySQL学习笔记5: MySQL查询语句案例及pymysql模块

Hojas

已于 2023-10-09 16:26:40 修改

阅读量730

点赞数

分类专栏： MySQL 文章标签： mysql 学习笔记

于 2023-10-09 16:19:37 首次发布

本文链接：https://blog.csdn.net/huangh0914/article/details/133705804

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL学习笔记5: MySQL查询语句案例及pymysql模块

文章目录

MySQL学习笔记5: MySQL查询语句案例及pymysql模块
总结
- - pymysql补充

一些MySQL语句案例

1、查询所有的课程的名称以及对应的任课老师姓名

-- SELECT
-- 	course.cname,
-- 	teacher.tname 
-- FROM
-- 	course
-- 	INNER JOIN teacher ON course.teacher_id = teacher.tid;

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

-- SELECT
-- 	student.sname,
-- 	t1.avg_num 
-- FROM
-- 	student
-- 	INNER JOIN (
-- 	SELECT
-- 		score.student_id,
-- 		avg( num ) AS avg_num 
-- 	FROM
-- 		score
-- 		INNER JOIN student ON score.student_id = student.sid 
-- 	GROUP BY
-- 		score.student_id 
-- 	HAVING
-- 		AVG( num ) > 80 
-- 	) AS t1 ON student.sid = t1.student_id;

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

# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
-- SELECT
-- 	student.sname 
-- FROM
-- 	student 
-- WHERE
-- 	sid NOT IN (
-- 	SELECT DISTINCT
-- 		score.student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) 
-- 	);

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

--     (只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
-- SELECT
-- 	student.sname 
-- FROM
-- 	student 
-- WHERE
-- 	student.sid IN (
-- 	SELECT
-- 		score.student_id 
-- 	FROM
-- 		score 
-- 	WHERE
-- 		score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
-- 	GROUP BY
-- 		score.student_id 
-- 	HAVING
-- 		COUNT( score.course_id ) = 1 
-- 	);

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

# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2 
	);

pymysql模块

"""
支持python代码操作数据库MySQL
"""
pip3 install pymysql

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day48',
    charset = 'utf8'  # 编码千万不要加-
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 产生一个游标对象(就是用来帮你执行命令的)
"""
cursor=pymysql.cursors.DictCursor将查询结果以字典的形式返回
"""
sql = 'select * from teacher;'
res = cursor.execute(sql)
# print(res)  # execute返回的是你当前sql语句所影响的行数  改返回值一般不用
# 获取命令执行的查询结果
# print(cursor.fetchone())  # 只拿一条
# print(cursor.fetchall())  # 拿所有
# print(cursor.fetchmany(2))  # 可以指定拿几条
print(cursor.fetchone())
print(cursor.fetchone())  # 读取数据类似于文件光标的移动
# cursor.scroll(1,'relative')  # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1,'absolute')  # 相对于数据的开头往后继续移动1位
print(cursor.fetchall())

sql注入

"""
利用一些语法的特性 书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''

select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
"""
日常生活中很多软件在注册的时候都不能含有特殊符号
因为怕你构造出特定的语句入侵数据库 不安全

# 敏感的数据不要自己做拼接 交给execute帮你拼接即可
# 结合数据库完成一个用户的登录功能？
import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day48',
    charset = 'utf8'  # 编码千万不要加-
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password))  # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('用户名密码错误')

总结

pymysql模块

# 直接安装 无需考虑版本
pip3 install pymysql

import pymysql

conn = pymysql.connect(
		host = '127.0.0.1',
    	port = 3306
    	user = 'root',
    	password = '123456',  # 还可以简写passwd = '123456'
    	database = 'db666',  # 还可以简写db = 'db666'
    	charset = 'utf8'  # 千万不要加横杆
)
# cursor = conn.cursor()  # 括号内不加参数的话 那么查询出来的数据是元组的形式 数据不够明确 容易混乱
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 字典形式返回数据 数据有具体的描述信息 更加的合理方便

sql = 'select * from user'
affect_rows = cursor.execute(sql)  # 返回值是当前sql语句执行的受影响的行数
cursor.fetchone()  # 只能结果的一条  数据本身
cursor.fetchall()  # 拿所有		列表套多个数据
cursor.fetchmany(n)  # 指定获取几条
"""
上述三个方法在读取数据的时候有一个类似于文件指针的特点
"""
cursor.scroll(1,'relative')  # 相对于光标所在的当前位置往后移动
cursor.scroll(1,'absolute')  # 相对于数据开头往后移动

sql注入

"""
就是利用一些特殊字符 结合软件固定的一些语句句式
非法侵入并违规操作

利用MySQL注释的语法 造成了sql注入的问题
	用户不需要属于用户名和密码也能够登录并且获取到整个用户表的数据

日常应用软件在获取用户输入的内容时 都会限制一些特殊符号的输入

如何解决上述问题？
	所有敏感的信息不要自己去做拼接操作 交互固定的模块帮你去过滤数据防止sql注入

在pymysql中 execute就能够帮你过滤
"""
# sql注入
sql = "select * from user where username='%s' and password='%s'"%(username,password)
# 不要自己拼接
sql = "select * from user where username=%s and password=%s"
execute(sql,(username,password))  # 只能识别%s

pymysql补充

# 1.针对增删改 pymysql需要二次确认才能真正的操作数据
import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 增
sql = 'insert into user(name,password) values(%s,%s)'
# rows = cursor.execute(sql,('jackson',123))
rows = cursor.executemany(sql,[('xxx',123),('ooo',123),('yyy',123)])
print(rows)
# conn.commit()  # 确认
# 修改
# sql = 'update user set name="jasonNB" where id=1'
# rows = cursor.execute(sql)
# print(rows)
# conn.commit()  # 确认
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit()  # 确认
# 查
# sql = 'select * from user'
# cursor.execute(sql)
# print(cursor.fetchall())

"""
增删改查中
    删改增它们的操作设计到数据的修改 
    需要二次确认
"""

# 还可以一次性插入N多条数据
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])