可视化软件Navicat，python操作MySQL
可视化软件Navicat
第三方开发的用来充当数据库客户端的简单快捷的操作界面
无论第三方软件有多么的花里胡哨，底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多，其中针对MySQL最出名的就是Navicat。
软件下载与安装
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
2.破解方式
先试用在破解、直接下载破解版(老版本)、修改试用日期
3.常用操作
有些功能可能需要自己修改SQL预览
创建库、表、记录、外键
逆向数据库到模型、模型创建
新建查询可以编写SQL语句并自带提示功能
SQL语句注释语法
--、#、\**\
运行、转储SQL文件
编写复杂的SQL不要想着一口气写完，一定要先明确思路，然后一步步写一步步查一步步补。
1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表——课程表、讲师表
# 2.预览表中的数据，做到心中有数
-- select * from course; -- select * from teacher;
# 3.确定多表查询的思路——连表、子查询、混合操作
SELECT
teacher.tname,
course.cname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表——学生表、分数表
# 2.预览表中的数据
-- select * from student; -- select * from score;
# 3.根据已知条件80分，选择切入点——分数表
- #求每个学生的平均成绩，按照student_id分组，然后avg求num即可
SELECT student_id, avg(num) as avg_num FROM score GROUP BY student_id HAVING avg_num>80;
# 4.确定最终的结果需要几张表。需要两张表，采用连表更加合适
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;
3、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表——学生表、分数表、课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理、体育相关的学生信息
select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 6.统计每个学生报了的课程数，筛选出等于1的
select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) GROUP BY student_id HAVING COUNT(course_id)=1;
# 7.子查询获取学生姓名即可
select sname FROM student WHERE sid in(select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) GROUP BY student_id HAVING COUNT(course_id)=1);
4、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表——分数表、学生表、班级表
# 2.预览表数据
# 3.根据条件确定以分数表作为起手条件
- 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
- 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
- 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
- 步骤4 连接班级表与学生表 然后基于学生id筛选即可
select student.sname, class.caption FROM student INNER JOIN class ON student.class_id = class.cid WHERE student.sid IN(select student_id from score where num < 60 group by student_id having count(course_id) >= 2);
更多练习题参考：https://www.cnblogs.com/Dominic-Ji/p/10875493.html
pymysql模块下载
pip3 install pymysql
导入模块
import pymysql
代码实操
1.连接MySQL服务端
conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', db='db5_02', charset='utf8mb4' )
2.产生游标对象
cursor = conn.cursor() # 括号内不填写额外参数，数据是元组，指定性不强 [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 添加参数之后，数据是字典的形式，可以很清楚地识别每个数据所表示的字段名 [{},{}]
3.编写SQL语句
sql = 'select * from teacher;' # 查询老师表中的信息
# sql = 'select * from score;' # 查询分数表中的信息
4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值，接收的是SQL语句影响的行数
# 收的是SQL语句影响的行数
print(affect_rows) # 5
5.获取SQL语句执行之后的结果
res = cursor.fetchall() print(res) # [{'tid': 1, 'tname': '张磊老师'}, {'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集，括号里面可以传数字参数，表示一次获取的数据量
ps: 三者都有类似于文件光标移动的特性
cursor.scroll(1,'relative') # 基于当前位置往后移动
res1 = cursor.fetchone() print(res1) # {'tid': 1, 'tname': '张磊老师'} cursor.scroll(1,'relative') res2 = cursor.fetchone() print(res2) # {'tid': 3, 'tname': '刘海燕老师'}
cursor.scroll(0,'absolute') # 基于数据的开头往后移动res1 = cursor.fetchone() print(res1) # {'tid': 1, 'tname': '张磊老师'} cursor.scroll(0,'absolute') res2 = cursor.fetchone() print(res2) # {'tid': 1, 'tname': '张磊老师'}
2.增删改查
如果想要通过代码往上述表格中插入、修改或删除数据，默认是无法操作的，必须要经过二次确认才可以。
conn.commit() # 针对增、删、改，二次确认(代码确认)
我们也可以直接在连接MySQL服务端的括号内添加一条配制，自动确认
conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', db='db5_02', charset='utf8mb4'， autocommit=True # 针对增、删、改，自动确认(直接配置) )
