Navicat及python第三方模块pymysql

可视化软件Navicat
python操作MySQL
- pymysql补充说明

可视化软件Navicat

可视化软件就是第三方开发的用来充当数据库客户端的简单快捷的操作界面，底层的本质还是SQL,第三方的可视化最出名还是Navicat

1.Navicat的下载

官网直接下载，版本很多，能充当数据库客户端也很多版本，版本越新兼容性越强。关于破解方式，可以直接下载老版本，或者下载新版本先试用再破解（比如修改日期）

常用的操作有：创建库、表、记录、外键；逆向数据库到模型、模型创建；新建查询可以编写SQL语句并自带提示功能；运转、转储SQL文件等操作

SQL语句注释
单行注释   -- 、# 、
		eg: -- SELECT * from userinfo;
多行注释  \**\

2.Navicat下的多表查询练习题
数据表关系

小技巧：编写复杂的SQL，要明确思路，一步一步的写

1.先确定需要用到几张表

2.预览表中的数据，做到心中有数

3.确定多表查询的思路（连表、子查询、混合操作）

1、查询所有的课程的名称以及对应的任课老师姓名

#  1.先确定需要用到几张表  课程表 课程表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- select * from course inner join teacher on course.teacher_id=teacher.tid
select teacher.tname,course.cname from course inner join teacher on course.teacher_id=teacher.tid

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

#  1.先确定需要用到几张表  分数表  学生表
# 2.预览表中的数据 做到心中有数
select * from score;
select * from student;
# 3.根据已知条件80分 选择切入点 分数
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
select student_id,avg(num)as avg_num from score group by student_id
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.思路1：正向推导   思路2：筛选所有报了李平老师课程的学生id 然后取反即可
#先获取李平老师教授的课程id
select tid from teacher where tname= '李平老师';
select cid from course where teacher_id=(select tid from teacher where tname= '李平老师');
根据课程id筛选出所有报了李平老师的学生id
select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname= '李平老师'));
根据学生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= '李平老师')));

4、查询没有同时选修物理课程和体育课程的学生姓名

# 1.先确定需要的表 课程表  学生表 分数表
# 2.预览表数据
# 3.根据物理和体育筛选课程表id
select cid from course where cname in ('物理','体育');

# 4.根据课程id筛选出所有物理和体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'));
# 统计每个学生报了的课程数，筛选出等于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;

# 子查询获取学生姓名即可
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);

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

# 1.先确定需要的表 学生表 班级表 分数表
# 2.预览数据
select * from class;
select * from student;
select * from score;
# 3.先筛选出小于60分
select * from score where num < 60;
# 4.统计每个学生挂科的次数
select student_id,count(course_id) from score where num <60 group by student_id;
# 5.筛选出次数大于等于2的学生id
select student_id,count(course_id)  from score where num <60 group by student_id having count(course_id) >=2; 

# 6.连接班级表和学生表 然后基于学生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);

python操作MySQL

pymysql模块，需要下载 >>> pip3 install pymysql

1.连接MySQL服务器

import pymysql
conn = pymysql.connect(
	host= '127.0.0.1',
    post= 3306,
    user= 'root',
    password= '123',
    db='db4_03',
    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语句影的行数
print(affect_rows)

5.获取SQL语句执行之后的结果

res = cursor.fetchall()
print(res)

pymysql补充说明

1.获取数据

fetchall()   获取所有的结果
fetchone()   获取结果集的第一个数据
fetchmany()  获取指定数量的结果集
这三种有点类似于文件光标移动的特性
如图

cursor.scroll(1,'relative')  # 基于当前位置往后移动
cursor.scroll(0,'absolute')  # 基于数据的开头往后移动
如图

2.增删改查

1.直接配置，自动确认
autocommit = True   # 针对增、删、改
2.代码确认，需要自动手动代码编辑
conn.commit()      # 针对增、删、改

练习题

1、查询所有的课程的名称以及对应的任课老师姓名
2、查询学生表中男女生各有多少人
3、查询物理成绩等于100的学生的姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
5、查询所有学生的学号，姓名，选课数，总成绩
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
8、 查询物理课程比生物课程高的学生的学号
9、 查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
24、任课最多的老师中学生单科成绩最高的学生姓名

答案：

https://www.cnblogs.com/Dominic-Ji/p/10875493.html