day48---MySQL数据库知识进阶（三）

MySQL数据库知识进阶（三）

可视化工具Navicat连接数据库

"""
host:surpass
IP:192.168.80.137
PORT:3306
"""

逆向数据库模型

• MySQL规范

"""
1.所有的关键字大写
2.使用# 和 -- 进行注释
3.在 Navicat中可以使用crtl + ?快速注释
"""

mysql练习题

1. 查询所有的课程名称和对应的任课老师姓名

SELECT
	course.cname AS "课程名称",
	teacher.tname AS "任课老师" 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;

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

SELECT
	student.sname AS "姓名",
	t1.avg_score AS "平均成绩" 
FROM
	student
	INNER JOIN (
	SELECT
		score.student_id,
		AVG( score.num ) AS avg_score 
	FROM
		score 
	GROUP BY
		score.student_id 
	HAVING
	AVG( score.num ) > 80 
	) AS t1 ON student.sid = t1.student_id;

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

SELECT
	student.sname AS "学生姓名" 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
	WHERE
		score.course_id IN (
		SELECT
			t1.cid 
		FROM
			( SELECT course.cid, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid ) AS t1 
		WHERE
			t1.tname = "李平老师" 
		));

4. 查询没有同时选修物理课程和体育课程的学生姓名
   (只要选了一门的 选了两门和没有选的都不要)

SELECT
	t1.sname AS "学生姓名" 
FROM
	(
	SELECT
		result.sname 
	FROM
		( SELECT student.sname, score.course_id FROM student INNER JOIN score ON student.sid = score.student_id ) AS result 
	WHERE
		result.course_id IN (
		SELECT
			course.cid 
		FROM
			course 
		WHERE
		course.cname IN ( "物理", "体育" ))) AS t1 
GROUP BY
	t1.sname 
HAVING
	Count( t1.sname ) = 1;

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

SELECT
	t1.caption AS "班级",
	t1.sname AS "学生" 
FROM
	( SELECT student.sname, student.sid, class.caption FROM student INNER JOIN class ON student.class_id = class.cid ) AS t1 
WHERE
	t1.sid IN (
	SELECT
		result.student_id 
	FROM
		( SELECT student_id FROM score WHERE num < 60 ) AS result 
	GROUP BY
		result.student_id 
	HAVING
		COUNT( result.student_id ) >= 2 
	);

安装pymysql模块

[root@surpass ~]# pip install pymysql

安装完记得重启pycharm

sql注入问题

"""
SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

根据相关技术原理，SQL注入可以分为平台层注入和代码层注入。前者由不安全的数据库配置或数据库平台的漏洞所致;后者主要是由于程序员对输入未进行细致地过滤，从而执行了非法的数据查询。基于此，SQL注入的产生原因通常表现在以下几方面:①不当的类型处理;②不安全的数据库配置;③不合理的查询集处理;④不当的错误处理;⑤转义字符处理不合适;⑥多个提交处理不当。
"""

用户登录

import pymysql

conn = pymysql.connect(
    host='192.168.80.137',
    port=3306,
    user='root',
    password='123456',
    database='day48',  # 访问的数据库
    charset='utf8'  # 编码一定要记得加
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 设置以字典的形式返回查询结果，不设置则默认为元组

while True:
    username = input('请输入用户名>>>:').strip()
    password = input('请输入密码>>>:').strip()
    password_again = input('请再次输入密码>>>:').strip()
    if password_again == password:
        """
        不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
        """
        sql = "SELECT * FROM user WHERE name = %s and password = %s"
        if cursor.execute(sql, (username, password)):
            print(f'用户[{username}]登录成功！')
        else:
            print('登录失败，用户名或密码错误！')
        break
    else:
        print('两次输入密码不一致，登录失败！')

cursor._rows存储了所有的数据

sql = "SELECT * FROM user "
cursor.execute(sql)
# print(cursor._rows) # 取出所有的数据
# print(cursor.fetchone()) # 取出一条数据
# print(cursor.fetchmany(2)) # 取出多条数据
# print(cursor.fetchall()) # 取出所有的数据

# print(cursor.fetchone()) # 先取出一个
# cursor.scroll(-1,'relative') # 相对当前位置向左偏移一位
# print(cursor.fetchmany(2)) # 再取出2个

print(cursor.fetchone()) # 先取出一个
cursor.scroll(2,'absolute') # 以起始位置绝对偏移两位
print(cursor.fetchall()) # 再取出所有的

sql注入示例

# --后面的内容注释了
select * from user where name='jason' -- jhsadklsajdkla' and password=''

# or 后面逻辑判断肯定为真
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''

action

敏感的数据不要自己拼接sql语句，交给execute帮你拼接即可。
"""
通过cursor对象执行sql语句查询和查询结果的显示。
cursor.excute()的返回结果是sql语句影响的条数
cursor.fetchonefetchmanyfetchall()获取查询结果
cursor.scroll()回滚光标的位置。支持相对模式relative和绝对模式absolute
其实，查询结果都保存在_rows中(元组或列表)，fetch和scroll只是修改在_rows中取值的索引
"""