MySQL数据库进阶
- navicat可视化界面操作数据库
- 数据库查询题目讲解(多表操作)
- python如何操作MySQL(pymysql模块)
- sql注入问题
- pymysql模块增删改查数据操作
1 Navicat软件
navicat可以用可视化界面操作数据库
"""
一开始学习python的时候 下载python解释器然后直接在终端书写
pycharm能够更加方便快捷的帮助书写python代码
我们在终端操作MySQL 也没有自动提示也无法保存等等 不方便开发
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标点点即可完成操作 无需书写sql语句
"""
1.1 Navicat安装
直接百度搜索 有破解版的也有非破解
非破解的有试用期 你如果不嫌麻烦 你就用使用
到期之后重新装再使用 或者破解一下也很简单
https://www.cr173.com/soft/126934.html
下载完成后是一个压缩包 直接解压 然后点击安装 有提醒直接点击next即可
navicat能够充当多个数据库的客户端
navicat图形化界面有时候反应速度较慢 你可以选择刷新或者关闭当前窗口再次打开即可
当你有一些需求该软件无法满足的时候 你就自己动手写sql
1.2 Navicat提示
"""
1 MySQL是不区分大小写的
验证码忽略大小写
内部统一转大写或者小写比较即可
upper
lower
2 MySQL建议所有的关键字写大写
3 MySQL中的注释 有两种
--
#
4 在navicat中如何快速的注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于上述操作再来一次就是解开注释
如果你的navicat版本不一致还有可能是
ctrl + shift + ？解开注释
"""
1.3 练习题
"""
课下一定要把握上课将的这几道题全部自己独立的理解并写出来
在解决sql查询问题的时候 不要慌
一步一步慢慢来 最终能够东拼西凑出来就过关了！！！
"""
-- 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
);
2 pymysql模块
python中的pymysql模块
"""
pip3 install pymysql
支持python代码操作数据库MySQL
"""
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拿到的数据是((2, ‘李平老师‘), (3, ‘刘海燕老师‘), (4, ‘朱云海老师‘), (5, ‘李杰老师‘))
# 括号内加了cursor=pymysql.cursors.DictCursor后拿到的数据是[{‘tid‘: 2, ‘tname‘: ‘李平老师‘}, {‘tid‘: 3, ‘tname‘: ‘刘海燕老师‘}, {‘tid‘: 4, ‘tname‘: ‘朱云海老师‘}, {‘tid‘: 5, ‘tname‘: ‘李杰老师‘}]
sql = ‘select * from teacher‘ # 要执行的sql语句
res = cursor.execute(sql)
print(res) # res是sql语句影响的行数
print(cursor.fetchone()) # 只拿一条(fetch操作是类似指针的形式,取一条后再取一条就是第二条)
print(cursor.fetchall()) # 拿所有数据(拿了除了第一条的所有数据)
print(cursor.fetchmany(2)) # 拿2条数据(已经没数据了,拿到的是空)
cursor.scroll(-1) # 相对现在指针位置,左移一位
print(cursor.fetchall()) # 拿所有数据(拿了最后一条数据)
cursor.scroll(1, ‘absolute‘) # 从头开始 右移1位
print(cursor.fetchall()) # 拿所有数据(拿了除了第一条的所有数据)
print(type(cursor)) # <class ‘pymysql.cursors.DictCursor‘>
3 sql注入
"""
注入就是利用一些语法的特性 书写一些特定的语句实现固定的语法
MySQL注入利用的是MySQL的注释语法
select * from user where name=‘jason‘ -- jhsadklsajdkla‘ and password=‘‘
select * from user where name=‘xxx‘ or 1=1 -- sakjdkljakldjasl‘ and password=‘‘
"""
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(‘>>>:‘)
# wu‘ -- dgagsdg
password = input(‘>>>:‘)
# 输入任何字符
sql = "select * from user where name=‘{}‘ and password=‘{}‘".format(username, password)
print(sql)
# select * from user where name=‘wu‘ -- dgagsdg‘ and password=‘‘
# 该sql语句变成了select * from user where name=‘wu‘ + 注释
# 导致直接登录成功
rows = cursor.execute(sql)
if rows:
print(‘登录成功‘)
print(cursor.fetchall())
else:
print(‘用户名密码错误‘)
# 甚至更进一步,用户名输入fas‘ or 1=1 -- fdaf
# select * from user where name=‘fas‘ or 1=1 -- fdaf‘ and password=‘‘
# 不仅能不用 用户名和密码登录成功, 并且能直接获取数据库中所有的用户名和密码
# 日常生活中很多软件在注册的时候都不能含有特殊符号
# 是为了防止用户构造出特定的语句入侵数据库
# 防止sql注入的问题的解决方案:
# 敏感的数据不要自己做拼接 交给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(‘用户名密码错误‘)