MySQL 快速入门（三）

目录

• MySQL快速入门（三）
• 多表查询
  • 多表查询练习1
  • 多表查询练习题2
• pymysql模块
  • 光标移动
• sql注入问题
  • 解决sql注入问题
• 完整的sql配置
• 视图
  • 注意
• 触发器
  • 基本语法
• 事务
• 存储过程
  • 基本使用
  • 三种开发模型
  • 示例
  • pymysql中调用存储过程

MySQL快速入门（三）

多表查询

多表查询主要有两种方式：

• 子查询：将SQL语句查询的结果用括号括起来，当作一个整体供另外一条SQL语句使用，使用了分步操作的思想
• 连表操作：将表拼接，之后提取需要的数据

连接表的几种常见方法：

方法	说明
inner join	内连接，拼接两个表共有的部分
left join	左连接，以左表为基准拼接，右表没有的内容用NULL填充
right join	右连接，以右表为基准拼接，左表没有的内容用NULL填充
union	全连接，拼接左右表的所有数据，不同的，没有的内容用NULL填充

---

多表查询练习1

拼接两张表可以看作一个全新的表，同时也可以与另外一张表做拼接，以此类推拼接N张表·····

数据准备：

# 数据准备
#建表
create table dep(
id int primary key auto_increment,
name varchar(20)
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;

insert into emp(name,sex,age,dep_id) values
('hammer','male',18,200),
('hans','female',48,201),
('lili','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

子查询示例

# 查询hammer所在的部门名称
select name from dep where id=(select dep_id from emp where name='hammer');

拼接示例

# 查询hammer所在的部门名称
select dep.name from dep INNER JOIN emp ON dep.id=emp.dep_id where emp.name='hammer';

拼接详细示例

inner join：内连接
select * from emp inner join dep on emp.dep_id = dep.id;

left join：左连接
select * from emp left join dep on emp.dep_id = dep.id;

right join：右连接
select * from emp right join dep on emp.dep_id = dep.id;

union:全连接
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

多表查询练习题2

表模型如下

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

# 涉及到到课程表course和teacher表，确定采用拼接表的方式，然后获取想要的数据
select course.cname,teacher.tname from course INNER JOIN teacher on course.teacher_id=teacher.tid;

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

# 涉及到成绩表和学生表，成绩表筛选平均成绩大于80的学生id和成绩
select student_id,avg(num) as avg_score from score GROUP BY student_id HAVING avg(num)>80;
# 学生姓名在学生表，连接表取数据
select student.sname,t1.avg_score from student INNER JOIN (select student_id,avg(num) as avg_score from score GROUP BY student_id HAVING avg(num)>80) AS t1 on student.sid=t1.student_id;

查询没有报李平老师课的学生姓名

# 涉及老师表和学生表，想要联系起两个表中间还有成绩表和课程表
1、先获取李平老师教哪些课id
select course.cid from course where course.teacher_id = (select tid from teacher where tname='李平老师');
2、获取学生的学生id
select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id =
(select tid from teacher where tname ='李平老师'));
3、取反整合
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
score.student_id
FROM
score
WHERE
course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) )
);

查询没有同时选修物理课程和体育课程的学生姓名(只要了报了一门的 两门和一门没报的都不要)

# 1.先获取两门课程的id号
-- select course.cid from course where cname in ('物理','体育');
# 2.再去分数表中先筛选出所有报了物理和体育的学生id(两门 一门)
-- select * from score where course_id in (select course.cid from course where cname in ('物理','体育'));
# 3.如何筛选出只报了一门的学生id 按照学生id分组 然后计数 并过滤出计数结果为1的数据
-- select score.student_id from score where course_id in (select course.cid from course where cname in ('物理','体育'))
-- group by score.student_id
-- having count(score.course_id) = 1
-- ;
# 4.根据学生id号去student表中筛选学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid IN (
-- SELECT
-- score.student_id
-- FROM
-- score
-- WHERE
-- course_id IN ( SELECT course.cid FROM course WHERE cname IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- count( score.course_id ) = 1
-- );

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

# 1.先筛选出小于60分的数据
-- select * from score where num < 60;
# 2.按照学生id分组 然后统计挂科数量
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 3.筛选出挂科超过两门的学生id
-- select student_id from score where num < 60 group by student_id
-- having count(course_id) >=2;
# 4.先将上述结果放在一边 去连接student和class表
SELECT
student.sname,
class.caption
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

pymysql模块

import pymysql

conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '7410',
database = 'oldboy', #
charset = 'utf8' # 编码不要写utf-8
)

# 产生一个游标对象
# cursor = conn.cursor() # 这样返回的结果只返回值，比较乱
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 返回结果设置成字典的形式，将字段名也返回
sql = 'select*from db1;'

# 执行命令
res = cursor.execute(sql) # 查询返回结果，比如返回多少条 >>5
# print(res) # execute返回的是你当前sql语句所影响的行数，该变返回值一般不用

# 获取命令执行的查询结果
print(cursor.fetchone()) # 只拿一条
print(cursor.fetchall()) # 拿所有数据
print(cursor.fetchmany(3)) # 拿指定的条数

ps：注意拿数据的时候，fetchone、fetchall、fetchmany类似光标移动，取到末尾就没得取了，再取会出问题；

光标移动

• cursor.scroll(1,’relative’) ：相对当前光标位置，移动1位
• cursor.scroll(1,’absolute’)：相对数据起始位置，往后移动一位

sql注入问题

利用一些语法的特性，书写一些特点的语句实现固定的语法

MySQL中 利用的是MySQL的注释语法来钻了漏洞

select * from user where name = 'xxx' or 1=1 -- aasafvs
'''
日常生活中很多软件在注册的时候都不能含有特殊符号
因为怕构造出特定的语句入侵数据库，不安全
'''

# 敏感的数据不要做拼接

解决sql注入问题

# 将要获取比对的数据，以元组的形式放在sql后面，交给execute方法
sql = 'select * from user where name = %s and password=%s'
res = cursor.execute(sql,(username,password))
if res:
print('登录成功')
#print(cursor.fetchall())
else:
print('登录失败，用户名或密码错误')

完整的sql配置

import pymysql

conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '7410',
database = 'oldboy', #
charset = 'utf8', # 编码不要写utf-8
autocommit = True
)
cursor = conn.cursor(pymysql.cursor.DictCursor)
sql = '语句'
row = cursor.execute(sql,('加入的值')) # 加入一条数据，以元组的形式
rows = cursor.executemany(sql,[('插入的值1'),('插入的值2'),('插入的值3')]) # 插入多条数据

视图

什么是视图？

视图就是通过查询得到一张虚拟表，保存下来然后下次可以直接使用

为什么用？

如果要频繁的操作一张虚拟表（拼表），就可以做成视图

如何操作？

create view 视图名 as 虚拟表的查询sql语句

# 示例
select * from teacher inner join course on teacher.tid = course.cid;

create view tea_cour as select * from teacher inner join course on teacher.tid = course.cid;

注意

• 创建视图在硬盘上只会有表结构（.frm文件），没有表数据，数据还是来自之前的表
• 视图一般只用来查询，里面的数据不要修改，可能会影响了原来的表
• 视图的使用频率不太高，不方便维护

触发器

在满足对表数据进行增、删、改的情况下，自动触发的功能

使用 触发器可以帮助我们实现监控、日志····

触发器可以在六种情况下自动触发，增前、增后、删前、删后、改前、改后

基本语法

create trigger 触发器的名字 before/after insert/update/delete
on 表名 for each row
begin
sql语句
end

# 针对触发器的名字 我们通常要见名知意

# 针对值
create trigger tri_berofe_insert_t1 before insert
on t1 for each row
begin
sql语句
end

修改MySQL默认的语句结束符

delimiter $$ 将默认的结束符号;改为$$

# 写触发器的时候，改默认结束符号，创建完触发器再改回去

事务

什么是事务？

开启一个事务可以包含多条sql语句，要么同时成功，要不同时失败（连坐），称之为事务的原子性

事务的作用？

保证了对数据操作的安全性

比如还钱，如果有失败的语句，那么转账就会失败

事务的四大特性

• ACID
• A：原子性，一个事务是一个不可分隔的单位，事务中包含诸多操作，要么同时成功，要么同时失败
• C：一致性，事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态，一致性是跟原子性是密切相关的
• I：隔离性，一个事务的执行不能被其他事务干扰，（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间是互不干扰的）
• D：持久性，一个事务一但提交执行成功，那么它对数据库中的数据的修改是永久的

如何使用事务？

# 事务相关的关键字

1、开启关键字
start transaction;

2、回滚操作（回到事务操作之前的状态）
rollback;

3、二次确认（确认之后无法回滚）
commit;

# 模拟转账功能
create table user(
id int primary key auto_increment,
name char(16),
balabce int
);
insert into user(name,balance) values ('jason',1000),
('hammer',1000),('hans',1000);

# 开启事务
start transaction;
# sql语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='hammer';
update user set balance=1090 where name='hans';
# 回滚
rollback
# 二次确认
commit

存储过程

存储过程类似于python中的自定义函数，内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端，你可以直接调用存储过程触发内部sql语句的执行

基本使用

create procedure 存储过程的名字(形参1,形参2···)
begin
sql语句
end

# 调用
call 存储过程的名字()

三种开发模型

第一种（不常用）

应用层序：程序员代码开发
MySQL:提前编写好存储过程，供应用程序调用

好处：开发效率提高了，执行效率提高
缺点：考虑到人为元素、跨部门沟通的问题，后续的存储过程扩展性差

第二种

应用程序：程序代码开发之外，自己设计数据库
优点：扩展性高
缺点：开发效率低，编写sql语句太过繁琐

第三种

应用程序：只写程序代码、不写sql语句、基于别人写好的MySQL框架直接调用（ORM框架）

优点：开发效率高
缺点：语句扩展性差，可能执行效率低下

示例

delimiter $$
create procedure p1(
in m int, # in代表的意思是只进不出，m不能返回出去
in n int,
out res int, # 该形参可以返回出去
)
begin
select t_name from teacher where tid>m and tid<n;
set res = 0
end $$
delimiter ;
# call p1(1,5,10);
# 针对形参不能直接传数据，应该定义一个变量
# 定义
set @ret = 10;
select @ret; # 查看

pymysql中调用存储过程

import pymysql

conn = pymysql.connect(
host = '127.0.0.1',
port=3306,
user = 'root',
passwd = '7410',
db = 'oldboy',
charset = 'utf8',
autocommit=True
)
cursor = conn.cursor(pymysql.cursor.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10)) # 存储过程名，参数
'''
这里就不需要像mysql中@ret定义变量了
过程：
@_p1_0=1
@_p1_1=5
@_p1_2=10
'''
cursor.execute('select @_pl_2;')
print(cursor.fetchall())

ps--->函数:和存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数