目录
- 一、Navicat可视化软件
- 二、使用Navicat操作MySQL
- 三、使用可视化软件实现（MySQL多表查询）
- 四、python操作MySQL
- 五、注入问题
- 六、小知识点的补充
- 作业：利用pymysql编写用户注册登录功能
一、Navicat可视化软件
可以充当很多数据库软件的客户端，并且提供操作数据库的快捷方式，主要用于MySQL
1.什么是可视化软件？
数据可视化可将文本数字数据转化为可视化的图标，图形和表格通过将重要数据引入中央界面，它被用作创建应用程序/系统性能或操作仪表板的方法。
2.什么是Navicat？
官网下载Navicat：http://www.navicat.com.cn/download/navicat-for-mysql
Navicat是一个可多重连接的数据库管理工具，它可以连接到MySQL、Oracle、PostgreSQL、SQLite、SQL Server和/或MariaDB数据库，让数据库管理更加方便。Navicat的功能可以满足专业开发人员的所有需求，对数据库服务器的新手来说学起来也不会很难。有了极完备的图形用户界面（GUI）,Nacivat可以简便、安全地创建、组织、访问和共享信息。
Navicat Premium是一套数据库管理工具，结合了其它Navicat成员的功能，Navicat Premium可满足现今数据库管理系统的使用功能，包括存储过程、事件、触发器、函数、视图等。
Nacivat该软件是收费的，但是有许多破解版可以去下载破解版，正式版只能免费试用14天。它虽然很好用，但是为了我们以后提高我们的开发的效率，不能过于依赖这个软件，其实这些软件的底层还是执行的SQL语句来操作数据库的，只不过是将SQL语句封装到了软件内。
二、使用Navicat操作MySQL
1.Navicat连接本地
2.连接成功后进行后续的操作
3.可以直观的看到表与表之间的关系
三、使用可视化软件实现（MySQL多表查询）
1.做题思路
写多表查询的时候跟写代码一样，先写思路再写sql
不要想着一次性写完 写完一点再写一点
2.练习题
# -- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要几张表 课程表 老师表
# 2.简单查询每张表中的数据
-- select * from course
-- select * from teacher
# 3.思考查询逻辑 多表查询(连表查询)
-- select * from course INNER JOIN teacher on teacher.tid = course.teacher_id;
# 查询出来的结果可以看作是两张表联合在一起又形成的一张表
SELECT course.cname,teacher.tname from course INNER JOIN teacher on teacher.tid = course.teacher_id;
# -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 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后面通过函数或者表达式编写的字段为了后续取值方便，一般需要重命名成普通字段
# 4.连接上述表和学生表
-- SELECT
-- *
-- FROM
-- student
-- INNER JOIN ( SELECT student_id, avg( num ) FROM score GROUP BY student_id HAVING AVG( num ) > 80 ) AS t1 ON student.sid = t1.student_id;
# 5.筛选学生名与平均分
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、查询没有报李平老师课的学生姓名
# 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到的表，老师表 课程表 分数表 学生表
# 2.简单查看表中的数据
# 3.俩种解题思路
-- 直接查看其他老师教的课程一步步查到学生
-- 查看李平老师教授的课程id号
# 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 BY score.student_id HAVING COUNT(score.course_id) = 1)
# 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先筛选出分数小于60的数据
select * from score where num < 60
# 2.统计每个学生挂科的数量
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.筛选挂科的学生姓名和班级
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
1.python如何操作MySQL?
为了使python连接上数据库，你需要一个驱动，这个驱动是用于与数据库交互的库。
PyMySQL ： 这是一个使Python连接到MySQL的库，它是一个纯Python库(文件夹)。
PyMySQL 是一个纯 Python 实现的 MySQL 客户端操作库，支持事务、存取过程、批量执行，实现增删改查等。
2.什么是PyMySQL?
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb。
PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
3.PyMySQL的安装
1.命令行输入(下载模块 pymysql)
pip3 install pymysql
2.切换下载的源(仓库)
pip3 install pymysql -i 源地址
下载第三方模块的方式
4.pyMySQL模块基本使用
import pymysql
# 创建链接
conn = pymysql.connect( # 赋值给 conn连接对象
host='127.0.0.1', # 本地地址
port=3306, # 默认端口
user='root', # 用户名
password='123456', # 密码
database='db1', # 连接数据库名称
charset='utf8mb4' # 编码
)
# 生成一个游标对象(相当于cmd打开mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
cursor.execute(sql)
# 获取返回结果
res = cursor.fetchall()
print(res)
5.pymysql主要方法
import pymysql
# 创建链接
conn = pymysql.connect( # 赋值给 conn连接对象
host='127.0.0.1', # 本地地址
port=3306, # 默认端口
user='root', # 用户名
password='123456', # 密码
database='db1', # 连接数据库名称
charset='utf8mb4' # 编码
)
# 生成一个游标对象(相当于cmd打开mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
ret = cursor.execute(sql)
print(ret) # 返回值是执行SQL语句之后受影响的行数
# 获取返回结果
# res = cursor.fetchall() # 列表套字典
# res = cursor.fetchone() # 数据字典
# res = cursor.fetchmany(3) # 列表套字典
print(res)
'''
execute返回值是执行SQL语句之后受影响的行数
fetchall()获取所有的结果
fetchone()获取结果集第一个结果
fetchmany()括号内可以指定获取几个结果集
'''
6.验证光标的存在
import pymysql
# 创建链接
conn = pymysql.connect( # 赋值给 conn连接对象
host='127.0.0.1', # 本地地址
port=3306, # 默认端口
user='root', # 用户名
password='123456', # 密码
database='db1', # 连接数据库名称
charset='utf8mb4' # 编码
)
# 生成一个游标对象(相当于cmd打开mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
ret = cursor.execute(sql)
print(ret) # 返回值是执行SQL语句之后受影响的行数
# 获取返回结果
res = cursor.fetchall()
res1 = cursor.fetchall()
print(res)
print(res1)
-
fetchone异常返回结果的原因
- 相对移动与绝对移动
'''
相对移动:相对当前位置移动
绝对移动:从起始位置移动
'''
import pymysql
# 创建链接
conn = pymysql.connect( # 赋值给 conn连接对象
host='127.0.0.1', # 本地地址
port=3306, # 默认端口
user='root', # 用户名
password='123456', # 密码
database='db1', # 连接数据库名称
charset='utf8mb4' # 编码
)
# 生成一个游标对象(相当于cmd打开mysql中的 mysql>)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
# 定义SQL语句
sql = 'select * from userinfo'
# 执行SQL语句
ret = cursor.execute(sql)
print(ret) # 返回值是执行SQL语句之后受影响的行数
# 获取返回结果
res = cursor.fetchone() # 获取结果集的第一个结果
# cursor.scroll(1, 'relative') # 相对当前位置移动(相对移动)
# cursor.scroll(0, 'absolute') # 相对数据开头位置移动(绝对移动)
res1 = cursor.fetchall() # 获取所有结果
print(res)
print(res1)
五、注入问题
前戏
只需要用户名即可登录
不需要用户名和密码也能登录
问题
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 并自动过滤各种符合 最后合并数据
execute方法，自动将 用户名和密码放在对应的%s内，并且放之前会自动对用户名和密码做特殊符号的校验，确保安全性
补充
cursor.executemany()
import pymysql
# 创建链接
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123456',
database='db1',
charset='utf8'
)
# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让数据自动组织成字典
登录功能
1.获取用户名和密码
2.基于用户名和密码直接精准查找
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 构造SQL语句
sql = "select * from info where name=%s and password=%s" # 针对核心数据 不要自己拼接 交由execute方法帮你筛选再拼接
print(sql)
# 执行sql语句
cursor.execute(sql,(username, password))
# 获取所有返回结果
res = cursor.fetchall()
# if判断
if res:
print(res)
print('登录成功')
else:
print('用户名或密码错误')
六、小知识点的补充
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语句
如果没有结果则不执行