Navicat可视化软件与python操作MySQL的方法

Navicat可视化软件

Navicat使用

1.下载
1.1下载网址
http://www.navicat.com.cn/download/navicat-premium
1.2Navicat是一款能让我们更方便管理数据库的软件，且功能强大操作简单，所以他是收费的，只有14天的试用期(不想付钱就去找破解方法)

2.主要功能
2.1启动后先连接MySQL(或者其他数据库)，输入用户名和密码在点击确认即可，然后右键连接点启动连接

2.2启动后就可以右键点新建库，或运行SQL文件(导入已有的数据库)

2.3右键数据库在点击逆向数据库到模型，就可以看到数据库的关系

多表查询练习

1、查询所有的课程的名称以及对应的任课老师姓名
# 查看表里面的数据
-- select * from teacher;
-- select * from course;
# 查询所有的课程的名称以及对应的任课老师姓名
select cname,tname from teacher inner join course on tid = teacher_id;

2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 查看表里面的数据
-- select * from student;
-- select * from score;
# 按照学生编号分组并筛选出平均成绩大于80的平均成绩
-- select distinct student_id,avg(num) from score group by student_id having avg(num) >= 80;
# 通过学生编号获取学生姓名
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、查询没有报李平老师课的学生姓名
# 查看表里面的数据
-- select * from score;
-- select * from teacher;
-- select * from course;
-- select * from student;
# 先获取李平老师教授的id号
-- select tid from teacher where tname = '李平老师';
# 获取李平老师教授课程编号
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
#根据课程编号去成绩表中筛选出所有报了课程编号的学生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、查询没有同时选修物理课程和体育课程的学生姓名
# 查看表里面的数据
-- select * from score;
-- select * from course;
-- select * from student;
# 先获取物理和体育课程的id号
-- select cid from course where cname in ('体育','物理');
# 获取上物理和体育课程的学生的id号
-- select student_id from score where course_id in (select cid from course where cname in ('体育','物理'));
# 获取物理和体育课程都选的学生的id号
-- select student_id from score where course_id in (select cid from course where cname in ('体育','物理'))group by student_id having count(student_id) = 2;
# 获取除了上述学生id的学生姓名
select sname from student where sid not in (select student_id from score where course_id in (select cid from course where cname in ('体育','物理'))group by student_id having count(student_id) = 2);

5、查询挂科超过两门(包括两门)的学生姓名和班级
# 查看表里面的数据
-- select * from score;
-- select * from student;
-- select * from class;
# 获取不及格的学生id
-- select student_id from score where num < 60;
# 获取挂科超过两门的学生的id
-- select student_id from score where num < 60 group by student_id having count(student_id) >= 2;
# 获取挂科超过两门的学生的班级id
-- select class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(student_id) >= 2);
# 拼接获取班级表和学生表的班级和学生姓名
-- select caption,sname from class inner join student on class.cid = student.class_id;
# 获取挂科超过两门的学生班级和学生姓名
select caption,sname from class inner join student on class.cid = student.class_id where sid in (select class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(student_id) >= 2)); 

python操作MySQL

python操作MySQL基本使用

1.python操作MySQL的模块
pip3、install、pymysql

2. pymysql
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',  # 连接服务端
    port=3306,  # 端口号
    user='root',  # 用户名
    password='321',  # 密码
    database='db1',  # 库
    charset='utf8mb4',  # 字符编码
    autocommit=True)  # 执行增、改、删操作自动执行conn.commit

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 产生一个游标对象
sql = 'select * from apple'
# 编写SQL语句
cursor.execute(sql)
# 发送给服务端
res = cursor.fetchall()
# 获取执行命令后的结果
print(res)
# 结果是列表套字典的格式

获取结果

1.res1 = cursor.fetchall()
# 获取结果集中所有数据

2.res2 = cursor.fetchone()
# 获取结果集中一条数据

3.res3 = cursor.fetchmany()
# 获取结果集中指定条的数据

4.cursor.scroll(2, mode='relative')
# 基于当前位置往后移动

5.cursor.scroll(0, mode='absolute')
# 基于数据集开头的位置往后移动

SQL注入问题

1.只需要用户名即可登录和不需要用户名与密码也能登录
select * from userinfo where name='jason' -- haha' and pwd=''
select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''
'利用一些SQL语句的特殊符号组合产生了特殊含义从而绕开了正常的业务逻辑'

2.解决方法
'用专门的方法自动过滤'
sql = 'select * from userinfo where name=%s and pwd=%s'
cursor.execute(sql, (username, password))
# 支持%s的使用，并且会自动过滤特殊符号
cursor.executemany()

小知识点补充

1.as语法
给字段起别名、起表名

2.comment语法
给表、字段添加注释信息
create table server(id int comment '这里写注释') comment '这里写注释';
'''
查看注释的地方
show create table
use information_schema
'''

3.concat、concat_ws语法
3.1concat用于分组之前多个字段数据的拼接
3.2concat_ws可以给分隔符一致的多个字段减少代码

4.exists语法
select * from apple where exists (select * from department where id<100)
'exists后面的SQL语句如果有结果则执行前面的SQL语句，没有则不执行'

作业

1、查询所有的课程的名称以及对应的任课老师姓名
-- select * from teacher;
-- select * from course;
select cname,tname from teacher inner join course on tid = teacher_id;

2、查询学生表中男女生各有多少人
-- select * from student;
select gender,count(gender) from student group by gender;

3、查询物理成绩等于100的学生的姓名
-- select * from student;
-- select * from score;
-- select * from course;
-- select cid from course where cname = '物理';
-- select student_id from score where num = 100 and course_id in (select cid from course where cname = '物理');
select sname from student where sid in (select student_id from score where num = 100 and course_id in (select cid from course where cname = '物理'));

4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- select * from student;
-- select * from score;
-- select distinct student_id,avg(num) from score group by student_id having avg(num) >= 80;
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;

5、查询所有学生的学号，姓名，选课数，总成绩
-- select * from student;
-- select * from score;
-- select sid,sname from student;
-- select student_id,sum(num),count(course_id) from score group by student_id having count(course_id);
select student.sid,student.sname,t1.count_course_id,t1.sum_num from student inner join (select student_id,sum(num) as sum_num,count(course_id) as count_course_id from score group by student_id having count(course_id))as t1 on student.sid = t1.student_id;

6、 查询姓李老师的个数
-- select * from teacher;
select tname from teacher where tname like '李%';

7、 查询没有报李平老师课的学生姓名
-- select * from score;
-- select * from teacher;
-- select * from course;
-- select * from student;
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师')); 
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、 查询物理课程比生物课程高的学生的学号

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

10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名