MySQL之Navicat和pymysql模块

Navicat可视化软件

下载安装

官网地址：http://www.navicat.com.cn/

主要功能介绍

1.可以创建、管理和维护数据库
2.可以充当各种数据库软件的客户端，并且提供操作数据库的快捷方式
3.用以方便管理 MySQL、Oracle、PostgreSQL、SQLite、SQL Server、MariaDB 和 MongoDB 等不同类型的数据库
4.支持创建库，表，增删改查等

多表查询练习题

# 查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定好需要几张表的数据  course teacher
# 2.查看一下表的数据是否是我们需要的
-- select * from course;
-- select * from teacher;
# 3.先获取课程名称再获取老师数据(连表操作)
-- select course.cname,teacher.tname FROM course inner JOIN teacher on teacher_id=tid;

#4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 1.先确定需要查询几张表的数据 score  student
-- select * from score; 
-- select * from student;
-- 2.以学生id为单位 先查询平均成绩
-- select student_id,avg(num) from score GROUP BY student_id HAVING avg(num) >80;
-- 3.再用多表查询 连表操作
-- select student.sname,stu.avg(num) from student inner join (select student_id,avg(num) from score GROUP BY student_id HAVING avg(num) >80) as stu on student.sid = stu.student_id; 

#7、查询没有报李平老师课的学生姓名
# 1.先确认要查询几张表的数据 student teacher course 
# 2.先确定李平老师的授课id号
-- SELECT tid FROM teacher WHERE tname='李平老师';
# 3.再获取李平老师课程编号
-- select cid from course where teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师');
# 4.查询李平老师课程的学生id
-- select DISTINCT student_id from score where course_id in(select cid from course where teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师'));
# 5.获取没有李平老师课程的学生姓名
-- 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、查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)
# 1.先确定要查找数据的表有几个  score student course 
# 2.先获取体育课和物理课程的id
-- select cid from course where cname in ('物理','体育');
# 4.以学生为单位查询分别报了两门课程的学生 筛选出只选了一门课程的学生
-- select score.student_id FROM score where course_id in (select cid from course where cname in ('物理','体育')) GROUP BY score.student_id having COUNT(score.student_id) =1;
# 根据学生id 查询学生姓名
-- select sname from student where sid in(select score.student_id FROM score where course_id in (select cid from course where cname in ('物理','体育')) GROUP BY score.student_id having COUNT(score.student_id) =1);

#9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先筛选出成绩小于60分的学生
-- select student_id from score where num<60;
# 查询成绩不合格超过两门的学生
-- select student_id from score where num<60 GROUP BY student_id HAVING COUNT(student_id)>=2;

# 获取学生姓名 
select sname from student where sid in (select student_id from score where num<60 GROUP BY student_id HAVING COUNT(student_id)>=2) ;

pyMySQL模块的基本使用

python操作数据库

import pymysql
# 1.连接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db',
    charset='utf8mb4'
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组成字典的形式返回
# 3.编写sql语句
sql1 = 'select * from teacher'
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall()   # fetchone() 查询一条  # fetchmany() 
print(res)

1.数据执行结果的三种获取方式
fetchall()  # 获取所有数据
 第一次获取的是所有值 在打印一次获取的是空字典 因为第一次已经把值取完了
fetchone()  # 获取第一条数据
第二次获取的时候是获取第二个值
fetchmany() 
# 在括号内填入要获取的数据条数 默认是1条 超过数据量的话返回最大数据量


------------
类似于光标移动的概念
cursor.scroll(2,mode='relative')    # 基于当前位置往后移动两位
cursor.scroll(1,mode='absolute')   # 基于数据开头位置往后移动
res = cursor.fetchone() 
print(res)

python操作MySQL数据库的增删改查需要在python解释器上做二次确认才可以操作成功
conn.commit() # 二次确认
每次增删改查数据都需要二次确认 为了更方便操作 可以在连接服务端的配置里加上
autocommit =Ture

execute 有一个返回值 每次插入或者查找数据的时候会返回一个现在的操作影响了几行数据的一个数据值

获取结果集注意事项

获取用户输入

import pymysql
# 1.连接服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db',
    charset='utf8mb4'
)
# 2.产生一个游标对象（等待输入命令）
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组成字典的形式返回
# 3.获取用户输入
username = input('username>>:').strip()
password = input('password>>>>:').strip()
# 4.编写sql语句
sql1 = "select * from userinfo where name ='%s' and pwd ='%s'"%(username,password)
# 4.发送给服务端
cursor.execute(sql1)
# 5.获取命令的执行结果
res = cursor.fetchall()
if res:
    print('登录成功')
else:
    print('用户名或密码错误')

sql注入问题

问题
	SQL注入
	select * from userinfo where name='jason' -- haha' and pwd=''
	select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''
本质
	利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
	针对用户输入的数据不要自己处理 交给专门的方法自动过滤
    sql = "select * from userinfo where name=%s and pwd=%s"
    cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据
补充
	 cursor.executemany() # 可以一次性插入多行数据 用列表插入

sql语句补充知识点

1.as语法：可以起别名，起表名
2.comment ：给表和字段添加注释的 
  eg:create table server(id int) comment '这个server意思是服务器表'
	create table t1(
    	id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';
 查看注释的两个地方：
   1.show create tables;
   2.use information_schema  # mysql自带的临时的库


3.concat ：用于分组之前多个字段数据的拼接
4.concat_ws：如果有多个字段，并且分隔符一直 ，那么可以减少分隔符的写入
5.exists: exists后面的如果sql语句如果有结果则执行前面的sql语句，没有结果则不执行
eg:select * from table where exists(select * from department where id<100)