使用Navicat可视化界面对数据库进行操作
一、Navicat的引入及特点
1.1 Navicat的引入
一开始学习Python的时候 下载Python解释器 然后直接在终端书写
pycharm能够更加方便快捷地帮助你书写python代码，
我们平时还是用Excel，Word，PPT等工具进行书写
我们在终端操作MySQL 既没有自动提示 也无法保存，十分的不方便开发，也需要像pycharm一样的集成了开发环境帮助我们进行数据库的书写操作
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候，只需要鼠标点点点即可完成操作，无需书写sql语句
1.2 Navicat的特点
Navicat能够充当多个数据库的客户端，MySQL，阿里云......
Navicat图形化界面有时候反应比较慢 刷新或者关闭后再次打开即可
MySQL是不区分大小写的，MySQL建议所有的关键字大写
验证码忽略大小写
内部统一转大写 或者 小写比较即可
upper lower
MySQL中的注释有2种
‘__’
‘#’
在Navicat中如何快速 注释 和 解开注释
ctrl + ? 加注释
ctrl + ? 解开注释
不同版本不同，可能还是：
ctrl + shift + ? 加注释
ctrl + shift + ? 解开注释
当你有一些需求 该软件无法满足你的时候 你就自己动手写sql
二、数据库查询题目练习(多表操作)
-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
-- ame,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 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;
-- 7、 查询没有报李平老师课的学生姓名
# 分步操作
# 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 = '李平老师' )
-- );
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名
-- (只要选了一门的 选了两门和没有选的都不要)
# 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 ame IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- COUNT( score.course_id ) = 1
-- );
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 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
);
三：Python如何操作MySQL(pymysql模块)
# 支持python代码操作数据库MySQL
安装pymysql：
pip3 install pymysql
# 导入pymysql模块
import pymysql
# 连接数据库
conn = pymysql.connect(
host='127.0.0.1', # 'ip'
port=3306, # '端口'
user='root', # 'mysql用户名'
password='yumi_0405', # 'mysql密码'
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('用户名密码错误')
