多表关联和事务及索引

多表关联关系

• 一对一关系

  通过主键关联主键实现

  通过外键关联主键可以实现

  案例：游戏用户（user）和账号(game_num)

• 一对一实现

  第一种方式，通过主键关联主键实现，建完表后添加外键
  用户表user
  create table user(
  	id int primary key auto_increment,
  	name varchar(50),
  	age int);
  账号表game_num
  create table game_num(
  	id int primary key auto_increment,
  	nickname varchar(50),
  	level int);
  没有添加外键约束可以任意删除
  外键
  alter table game_num
  	add constraint foreign key(id) references user(id);
  constraint可以省略
  
  添加数据
  insert into user values(1,"张伟强",22);
  insert into game_num values(1,"国服李白",15);
  insert into game_num values(2,"国服韩信",25); --error
  
  第二种方式实现：建表的时候添加外键
  用户表user
  create table user(
  	id int primary key auto_increment,
  	name varchar(50),
  	age int);
  账号表game_num
  create table game_num(
  	id int primary key auto_increment,
  	nickname varchar(50),
  	level int,
  	foreign key(id) references user(id)
  	);
  

• 一对多关系

  通过外键关联可以实现，外键添加可以添加在多的一方

  中间表进行关联，多的一方设置唯一约束

  案例：店铺（shop），商品（goods）

  通过外键关联可以实现，外键添加可以添加在多的一方
  创建店铺表
  create table shop(
  	id int primary key,
  	sname varchar(50)
  	);
  创建商品表
  create table goods(
  	id int primary key,
  	gname varchar(50),
  	price float,
  	sid int
  	);
  添加外键约束
  alter table goods(
  	add foreign 
      references shop(id)
  	);
  
  insert into shop values(1,"小米旗舰店");
  insert into goods values(1,"小米10",3999,1);
  insert into goods values(2,"平衡车",2999,1);
  insert into goods values(3,"小米笔记本",5999,1);
  
  
  中间表进行关联，多的一方设置唯一约束
  创建店铺表
  create table shop(
  	id int primary key,
  	sname varchar(50)
  	);
  创建商品表
  create table goods(
  	id int primary key,
  	gname varchar(50),
  	price float,
  	);
  关系表
  create table shop_goods(
  	id int primary key,
  	sid int,
  	foreign key(sid) references shop(id),
  	gid int unique,
  	foreign key(gid) references goods(id)
  );
  insert into shop values(1,"小米旗舰店");
  insert into goods values(1,"小米10",3999);
  insert into goods values(2,"平衡车",2999);
  insert into goods values(3,"小米笔记本",5999);
  insert into shop_goods values(1,1,1);
  insert into shop_goods values(2,1,2);
  insert into shop_goods values(3,1,3);
  

• 多对对关系

  • 中间表多对多关联，不设置唯一约束即可
  • 案例：学生(stu)和课程(course)

  创建学生表
  create table stu(
  	id int primary key,
  	sname varchar(50)
  	);
  创建课程表
  create table course(
  	id int primary key,
  	cname varchar(50)
  	);
  关系表
  create table stu_course(
  	id int primary key,
  	sid int,
  	foreign key(sid) references stu(id),
  	cid int,
  	foreign key(cid) references course(id)
  );
  张伟强选了 语文，数学和英语
  张威选了 数学和物理
  插入学生数据
  insert into stu values(1,"张伟强");
  insert into stu values(2,"张威");
  插入课程数据
  insert into course values(1,"语文"),(2,"数学"),(3,"英语"),(4,"物理");
  建立关系
  insert into stu_course values(1,1,1);
  insert into stu_course values(2,1,2);
  insert into stu_course values(3,1,3);
  insert into stu_course values(4,2,1);
  insert into stu_course values(5,2,4);
  

多表连接查询

• 比单表查询会耗费更多的系统资源

  创建两个测试表a和b
  create table a(id int,name varchar(10));
  create table b(id int,name varchar(10));
  
  insert into a values(1,"a")
  insert into a values(2,"b")
  insert into a values(4,"d")
  
  insert into b values(1,"x")
  insert into b values(2,"y")
  
  分类
  交叉连接
  不指定条件连接，结果集就是笛卡尔积，记录数就是a表记录m乘以b表记录n
  select * from a cross join b;
  select * from a join b;
  
  内连接
  指定条件连接，结果集包含符合连接条件的记录数
  select * from a inner join b on a.id=b.id;
  select * from a join b on a.id=b.id;
  
  左连接(左外连接)
  指定条件连接，结果集包含左表全部记录，右表中符合连接条件的记录和右表中不符合连接条件的记录(用NULL填充)
  select * from a left outer join b on a.id=b.id;
  select * from a left join b on a.id=b.id;
  
  右连接(右外连接)
  指定条件连接，结果集包含右表全部记录，左表中符合连接条件的记录和左表中不符合连接条件的记录(用NULL填充)
  select * from a right outer join b on a.id=b.id;
  select * from a right join b on a.id=b.id;
  

• 案例操作

  查看 近卫 部门所有员工信息
  select * from employee,department where employee.deptno=department.deptno and department.deptname="近卫";
  
  查询每个部门有哪些员工（内连接）
  select * from employee e inner join department d on e.deptno=d.deptno;
  
  查询每个部门有哪些员工（左连接）
  select * from employee e left join department d on e.deptno=d.deptno;
  
  查询每个部门有哪些员工（右连接）
  select * from employee e right join department d on e.deptno=d.deptno;
  
  stu和class多表连接查询
  例1：显示学生的学号，姓名，性别，班号，班级名称以及对应班主任，按照班号和学号排序
  select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班级名称,c.teacher 班主任 from stu s inner join class c on s.cno=c.cno order by s.cno,s.sno;
  
  例2：显示学生的学号，姓名，性别，班号，班级名称以及对应班主任，无班主任的显示“暂无”，按照班号和学号排序
  select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班级名称,if(c.teacher is null,"暂无",c.teacher) 班主任 from stu s left join class c on s.cno=c.cno order by s.cno,s.sno;
  
  select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班级名称,ifnull(c.teacher,"暂无") 班主任 from stu s left join class c on s.cno=c.cno order by s.cno,s.sno;
  
  

• 复合查询

  • 用集合运算符对多个查询结果进行运算，产生新的查询结果集

    对两个结果进行并集操作
    并集 union，union all
    交集 
    差集
    
    示例：
    创建class1表
    create table class1 as select * from class where cno=1;
    insert into class1 values(5,'5班',"张伟强");
    
    例1：查看class和class1表的并集，重复记录，重复显示   
    select * from class union all select * from class1;
    
    例2：查看class和class1表的交集
    select c.cno,c.cname,c.teacher from class c join class1 c1 on c.cno=c1.cno;
    
    例3：查看class和class1表的差集，class中有，class1没有的
    1.左连接查询
    select * from class c left join class1 c1 on c.cno=c1.cno;
    2.去除相同行
    select * from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
    3.取出右边的显示
    select c.cno,c.cname,c.teacher from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
    
    例4：查看class和class1表的差集，class1中有，class没有的
    1.右连接查询
    select * from class c right join class1 c1 on c.cno=c1.cno;
    2.去除相同行
    select * from class c right join class1 c1 on c.cno=c1.cno where c.cno is null;
    3.取出右边的显示
    select c1.cno,c1.cname,c1.teacher from class c right join class1 c1 on c.cno=c1.cno where c.cno is null;
    

• 子查询

  结构

  select 列1，列2...from 表名 where 列=值 group by 分组列 having 分组列=值 order by 列

  使用一个查询语句实现更复杂的查询，嵌套的select 往往被称为子查询，需要用（）括起来

  • 非关联子查询：子查询可以单独用于主查询执行，仅执行一次，效率较高

  • 关联子查询：子查询不可以单独用于主查询执行，如果主查询有N行，子查询将执行N次，效率相对较低，但是灵活度高

  • 非关联子查询
    例1：查询学生中哪些人比张飞的体重重？
    select weight from stu where sname="张飞";
    select * from stu where weight>(select weight from stu where sname="张飞");
    
    例2：2班3班中哪些同学的身高比1班的平均身高高？
    select avg(weight) from stu where cno=1;
    select * from stu where cno in (2,3) and height>(select avg(weight) from stu where cno=1);
    
    例3：每个班的高考状元
    select cno,max(score) from stu group by cno;
    select * from stu where (cno,score) in (select cno,max(score) from stu group by cno)
    
    例4：哪些同学的体重比所有班的平均体重重？
    select avg(weight) from stu group by cno;
    select * from stu where weight>all(select avg(weight) from stu group by cno);
    all ：比所有的值都大
    any：比所有的值都小
    
    例5：哪些同学的身高高于本班平均身高？
    select cno,avg(height) from stu where cno is not null group by cno;
    select * from stu s,(select cno,avg(height) havg where cno is not null group by cno) a  where s.cno=a.cno and s.height>a.havg and s.cno is not null; 
    
    例6：不用多表连接方式，列出3班的学生姓名和3班的班主任
    select s.sname 学生姓名,(select teacher from class where cno=3) 班主任  from stu s where s.cno=3;
    

  • 关联子查询
    例1：不用多表连接的方式，列出每个学生的班号，姓名，和所在班的班主任
    select s.cno 班号,s.sname 姓名,(select teacher from class where cno=s.cno) from stu s where s.cno is not null order by 1;
    #子查询不能够单独执行，主查询一般需要起别名
    
    例2：使用关联子查询，在已分班的学生中列出身高高于本班平均身高的学生
    select * from stu s1 where s1.cno is not null and s1.height>(select avg(height) from stu s2 where s2.cno=s1.cno);
    
    例3：体重最重的学生的班主任是谁？
    1.最重的体重
    select max(weight) from stu;
    2.查询每个学生的班主任
    select sname,weight,teacher from stu,class where stu.cno=class.cno;
    3.加上条件
    select sname,weight,teacher from stu,class where stu.cno=class.cno and weight=(select max(weight) from stu);
    
    练习1.从学生表中和班级表中找出姓“曹”的人，标注其角色，学生或老师
    select sname from stu where sname like "曹%";
    select sname,"学生" 角色 from stu where sname like "曹%";
    
    select teacher,"老师" 角色 from class where teacher like "曹%";
    取并集 union all 
    select sname,"学生" 角色 from stu where sname like "曹%" union all select teacher,"老师" 角色 from class where teacher like "曹%";
    

事务

• 事务是一种机制，一个操作序列，包含了一组数据库操作命令。事务是一个不可分割的工作逻辑单元，在数据库执行并发操作时，事务是最小的控制单元。

• A用户和B用户是银行的储户，现在A要给B转500块钱
  检查账户余额>=500元
  A账户扣除500元
  B账户增加500元
  正常流程走下来，A账户扣除500元，B账户增加500元
  如果A扣了钱之后，系统出了故障，A损失了500，B没有收到500元
  所谓事务，就是一个操作序列要么都执行，要么都不执行，是一个不可分割的工作单位
  

• 数据库引擎

  • 数据库存储引擎，是数据库底层构建的核心，负责底层数据持久化和软件交互的序列化操作、

    校验过程以及交互过程，通过数据库存储引擎完成创建、查询、更新和删除数据。不同的存

    储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获

    得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

  • InnoDB

  InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上

  图也看到了，InnoDB 是默认的 MySQL 引擎。

  • MyISAM

  MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下

  最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事物。

• 事务的特性

  • 原子性(atomicity)
  • 一致性(consistency)
  • 隔离性(isolation)
  • 持久性(durability)

  原子性：一个事务必须视为一个不可分割的最小的工作单元，整个事务中的所有操作要么全部执行成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
  
  一致性：从一个一致性的状态转换到另一个一致性的状态，如现在A要给B转500块钱，A账户扣除500元，B账户增加500元
  
  隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
  
  持久性：一旦事务提交，其所做的修改会永久的保存到数据库
  

• 事务的隔离级别

  如果数据库严格按照四大特性进行管理，数据库的操作会比较缓慢

  Read Uncommitted(读未提交)   脏读，不可重复读
  操作效率最高，数据稳定性最差     
  一个事务可以读取到另一个事务未提交的更新结果
  
  Read committed(读已提交)    不可重复读，幻读
  操作效率较高，数据稳定性较差   
  一个事务可以读取到另一个事务已提交的更新结果
  
  Repeat Read（可重复读）     幻读
  操作效率较低，数据稳定性较差  
  在整个事务过程中，对同一个数据的读取结果是相同的，不管其他事务是否同时在对同一笔数据进行更新，提交
  
  Serializable（可串行化）序列化
  操作效率最低，数据稳定性最高
  所有的操作，全部排队，任何事情必须等待前一件事情全部完成才能开始
  

• 事务的并发问题

  脏读（dirty read）：A 事务读取 B 事务尚未提交的更改数据，并在这个数据基础上操作。如 果 B 事务回滚，那么 A 事务读到的数据根本不是合法的，称为脏读。 
  
  不可重复读（unrepeatable read）：A 事务读取了 B 事务已经提交的更改（或删除）数据。比 如 A 事务第一次读取数据，然后 B 事务更改该数据并提交，A 事务再次读取数据，两次读取 的数据不一样。 
  
  幻读（phantom read）：A 事务读取了或意识到了 B 事务已经提交的新增数据。注意和不可重复读的区别，这里是新增，不可重复读是更改（或删除）。
  幻读的重点在于新增或者删除(数据条数的变化)
  目前5000元工资的员工有10人
  事务1，读取工资为5000的员工
  事务2，向数据库表插入一条员工记录，工资也是5000
  事务1，再次读取工资为5000的员工
  

• 事务的操作

  开启事务
  begin;
  或者
  start transaction
  
  提交事务
  commit;
  
  回滚事务
  rollback;
  
  设置事务的隔离级别
  set session transaction isolation leval 隔离级别;
  

• 案例

  create tabel user(
  	id int primary key auto_increment,
  	name varchar(20));
  
  一、读未提交与脏读
  终端1 设置事务隔离级别并开启，终端2，设置事务隔离级别并开启
  set session transaction isolation level read uncommitted;
  开启事务  begin;
  插入数据  insert into user values(1,"张威");
  1还没有提交，2就可以读取到数据
  
  二、读已提交和不可重复读
  终端1 设置事务隔离级别并开启，终端2，设置事务隔离级别并开启
  set session transaction isolation level read  committed;
  开启事务  begin;
  插入数据  insert into user values(1,"张威");
  1还没有提交，2不可以读取到数据，但A提交之后，B就读取到了
  
  三、可重复读
  终端1 设置事务隔离级别并开启，终端2，设置事务隔离级别并开启
  set session transaction isolation level repeatable read;
  开启事务  begin;
  插入数据  insert into user values(1,"张威");
  1还没有提交，2不可以读取到数据，但A提交之后，B就读取到了
  无法避免幻读
  在1插入数据前，2查看user为空
  在1插入数据后并并提交，2查看user为空
  在2中执行insert into user values(1,"张威");
  出现主键冲突，插入不进去，仿佛数据存在但是看不到，出现了幻觉
  

索引

• 索引：数据库中的一个数据对象，用来提升查询效率

• 索引的种类

  普通索引：可以为空，可以重复
  主键索引：不可为空，不可以重复
  唯一索引：可以为空，不可以重复
  

• 索引的优点和缺点

  索引的优点：创建索引可以大大提高系统的性能。 
  • 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
  • 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 
  • 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
  • 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
  
  索引的缺点：增加索引也有许多不利的方面
  • 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 
  • 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  • 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就 降低了数据的维护速度。
  

• 索引的使用

  普通索引 ALTER TABLE 表名 ADD INDEX(字段名); 
  唯一索引 ALTER TABLE 表名 ADD UNIQUE(字段名); 
  主键索引 
  主键本身就是主键索引，我们创建表，添加了主键，就相当于添加了主键索引 
  ALTER TABLE 表名 ADD PRIMARY KEY(字段名); 
  多列索引 
  ALTER TABLE 表名 ADD INDEX(字段名,字段名,字段名); 
  删除索引 
  DROP INDEX 索引名 ON 表名;删除主键索引需要先删除主键的自增属性 
  注意：由于索引和数据是存储在同一个文件中，因此在使用独立表空间时，InnoDB引擎使用drop命令删除索引并不会释放磁盘空间
  ALTER TABLE 表名 DROP PRIMARY KEY 
  查看索引 
  SHOW INDEX FROM student;
  

• 百万数据查询实验

  存储过程
  delimiter $$
  #存储过程名proc1，参数cnt为准备插入数据行的数量，调用时输入该值
  create procedure proc1(cnt int)
  begin
    #定义变量i为整型，默认值为1
    declare i int default 1;
    #开启事务
    start transaction;
    #MySQL repeat循环结构
  repeat
  	#插入test库t表，id列值对应为i，
  #name列对应值为字符‘a’与i值合并后的字符串
  	insert into test.t (id,name) values (i,concat('a',i));
  	#变量i自增1
  	set i = i + 1;
    #当i值大于输入cnt值时，退出循环体
    until i > cnt end repeat;
    commit;
  end$$
  delimiter ;
  
  创建数据库和数据表
  create database test;
  use test;
  create table t(id int,name varchar(30));
  调用存储过程，设置插入一百万行数据
  call proc1(1000000);
  

  实验一：百万记录取1条
  select * from t where id=1;
  select * from t where id=900000;
  创建索引后再查询
  create unique index idx_id on t(id);
  drop index idx_id on t;
  

  • 总结：

    主要优点使select查询速度大幅度提高，包括更新少量数据

    副作用：1.占用额外的磁盘空间

    ​ 2.使得数据表的增删改操作变慢

视图

• 查询可能会遇到的问题

  • 查询复杂，带来服务器性能的严重消耗
  • 语法复杂，很多地方进行编写时经常出错

• 视图是一种虚拟表，是从一个表或多个表中导出来的数据组成的虚拟表，并不在数据库中真

  的存在，作用和真实表一样，包含一系列带有行和列的数据。通俗的来说，视图就是一条select语句执行后返回的结果

• 视图的优缺点

  • 优点：隐藏了复杂的SQL语句，提供简单的语法，减轻开发人员的压力

    ​ 隐藏一些涉及到安全的数据，提供展示通用数据，做到保密操作

  • 缺点：视图查询可能会很慢

    ​ 视图依赖于真实表，当表结构发生变化，视图必须修改

• 视图的使用

  定义视图，建议以v_开头
  create view 视图名称 as select语句;
  
  查看表会将所有的视图也列出来
  show tables;
  查看视图创建语句
  show create view 视图名称
  
  使用视图
  select * from 视图;
  
  删除视图
  drop view 视图名称
  

• 案例操作

  创建一个查询学生信息和班级的视图v_stu_info,展示学生姓名，性别，班级，班主任
  1.写查询语句
  select s.sname,s.sex,c.cname,c.teacher from stu s join class c on s.cno=c.cno;
  2.创建视图
  create view v_stu_info as select s.sname,s.sex,c.cname,c.teacher from stu s join class c on s.cno=c.cno;
  3.查询视图
   select * from v_stu_info;
  原本两张表联合查询得到的结果，现在通过简单的单表查询语句就可以得到的具体的数据，将复杂的SQL语句实现隐藏起来，提供简单的操作方式
  

• 修改视图信息

  视图v_stu_info 学生姓名，分数，班级，班主任
  alter view v_stu_info as 
  select s.sname,s.score,c.cname,c.teacher from stu s join class c on s.cno=c.cno;
  查询视图
  select * from v_stu_info;
  

• 数据的更新

  视图的本身是没有数据的，数据都是创建视图的源表中的真实数据，所以对真实表的操作会直接反映到视图中，我们可以删除真实表中的数据

  删除赵云
  delete from stu where sname="赵云";
  查询视图
  select * from v_stu_info;
  此时赵云已经不存在了
  

• 直接删除视图中的数据，会不会对源表产生影响？

  删除张辽
  delete from v_stu_info where sname="张辽";
  视图默认情况下都是只读的，不允许通过视图直接进行数据的改动，这也是数据安全隔离的一种操作手段。
  
  如果需求确认创建可更新(update、 delete、insert)的视图，需要确认下面的条件 之一满足的话就不允许通过视图更新表中的数据 
  （1）聚合函数； 
  （2）DISTINCT 关键字； 
  （3）GROUP BY 子句； 
  （4）ORDER BY 子句； 
  （5）HAVING 子句； 
  （6）UNION 运算符； 
  （7）位于选择列表中的子查询； 
  （8）FROM 子句中包含多个表； 
  （9）SELECT 语句中引用了不可更新视图； （
  10）WHERE 子句中的子查询，引用 FROM 子句中的表； 
  （11）ALGORITHM 选项指定为 TEMPTABLE（使用临时表总会使视图成为不可更新 的）。
  
  例：
  create view v_stu_info1 as
  select * from stu where cno=2;
  删除视图中的数据
  delete from v_stu_info1 where sno=7;
  真实数据已被删除
  drop view v_stu_info1;
  查看视图信息
  show table status where comment='view';
  

触发器

• 触发器是满足出发条件执行的一段代码，触发器作用在表上

• 触发器自动完成满足特定条件相关的操作

• 语法

  create 
  	trigger trigger_name
  	trigger_time trigger_event
  	on table_name for each row
  	trigger_body
  trigger_name:触发器名称
  trigger_time：触发时间  before,after 在触发事件之前/之后 执行触发器中的代码
  trigger_event：触发事件  insert,update,delete
  
  查看触发器
  show triggers;
  删除触发器
  drop trigger trigger_name;
  drop trigger auto_insert;
  
  

  关键字	insert	delete	update
  new.column.name	引用插入行中的某列数据	无	引用更新行中的某列新数据（更新后的值）
  old.column.name	无	引用删除行中的某列数据	引用更新行中的某列原来数据（更新前的值）

• 案例操作

  create table a(
  	id int,
  	name varchar(50));
  每次删除stu表中的数据，都往a表中插入数据(1,"张伟强")
  
  \d $$
  create trigger auto_insert
  	after delete on stu for each row
  	begin
  	insert into a values(1,"张伟强");
  	end$$;
  \d ;
  
  delimiter:设置mysql语句结束符号
  	默认情况下，数据库引擎遇到分号；表示这样结束，可以直接执行
  	delimiter $$，告诉数据库引擎，遇到$$表示可以执行
  	begin  程序开始的位置
  	end  程序结束的位置
  

• 商品和订单触发器案例

  买了商品会自动生成订单
  商品表
  create table goods(
  	gid int primary key auto_increment comment "商品编号",
  	gname varchar(50) comment "商品名称",
  	gprice float comment "商品价格",
  	gstock int comment "商品库存"
  	);
  订单表
  create table goods_order(
  	oid int primary key auto_increment comment "订单编号",
  	oname varchar(50) comment "订单名称",
  	oprice float comment "单价",
  	gcount int comment "数量",
  	totalprice float comment "订单总额"
  );
  
  \d $$
  create trigger auto_order
  	after update on goods for each row
  	begin
  	-- 声明两个变量
          declare buycount int;
          declare totalprice float;
          -- 购买数量小于商品库存
          if new.gstock<old.gstock
          then
          -- 计算购买数量
          set buycount=old.gstock-new.gstock;
          -- 计算总价
          set totalprice=buycount*old.gprice;
          -- 订单表新增数据
          insert into goods_order values(old.gid,old.gname,old.gprice,buycount,totalprice);
          end if;
  	end$$;
  \d ;
  
  测试
  select * from goods;
  select * from goods_order;
  insert into goods values(1,"小米10",4000,10);
  update goods set gstock=8 where gid=1;
  此时订单表中新增了数据
  

存储过程

• 存储过程一组为例完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过执行存储过程的名字并给定参数来调用执行（call调用）

• 存储过程优点

  1.增强了sql语言的功能和灵活性
  2.存储过程在第一次编译后再调用后就不需要再遍历，比sql语句执行效率高
  3.可以避免开发人员编写相同的sql语句
  4.减少客户端和服务端的数据传输
  

• 语法

  \d 标识符
  	create procedure proc_name([参数])
  	begin
  	...
  	end 标识符
  \d;
  调用存储过程
  call proc_naem()
  

  案例操作，向class中增加数据
  \d $$
  	create procedure insert_class(in_cno int,in_cname varchar(50),in_teacher varchar(50))
  	begin
  		insert into class values(in_cno,in_cname,in_teacher);
  	end $$
  \d ;
  调用
  call insert_class(5,"5班","张伟强");
  

用户管理

• 修改密码

  从8.0开始修改密码有了变化，在user表中加了字段authentication_string,修改密码前先改为空

  set password for root@localhost="1234";
  或者
  use mysql;
  update user set authentication_string='' where user='root' and host='localhost';
  alter user root@localhost identified by "root";
  alter user 'root'@'localhost' identified with mysql_native_password by "root";
  

• 密码丢失

  绕过密码登录的方式
  skip-grant-tables在8.0版本失效，先停止mysql服务 net stop mysql
  在cmd中使用 mysqld --console --skip-grant-tables --shared-memory
  再另开一个cmd窗口，即可不用密码登录
  use mysql;
  update user set authentication_string='' where user='root' and host='localhost';
  flush privileges;
  启动服务net start mysql，登录mysql,修改密码
  alter user root@localhost identified by "root";
  

• 创建用户

  create user "han"@"%" identified by "root";
  CREATE USER：用于创建一个新用户的关键字 
  • han：创建的用户账号 
  • localhost：指定创建用户账号的数据库主机 
  • IDENTIFIED BY：用于设置用户登录数据库的密码 
  • 'root'：设置的登录数据库的明文密码
  
  远程登录 mysql -h ip地址 -u用户名 -p
  mysql -h 10.10.12.130 -uhan -p
  此时用户没有权限
  show processlist 查看连接的用户
  

• 删除用户

  drop user 'han'@"%";
  

• 远程登录root用户

  查看用户名，地址和密码
  use mysql;
  select user,host,authentication_string from user;
  update user set host="%" where user="root";
   flush privileges;
  远程连接
  mysql -h 10.10.12.130 -uroot -p
  

权限管理

• 对于用户的合理的权限分配，

  能极大程度的区分不同用户在数据库中的数据操作边界，提高系统的安全性。

• 权限操作主要通过 grant 和 revoke 命令进行操作。

• 常用权限：create,alter,drop,insert,update,delete,select,分配所有权限 all privileges

• 语法

  新版的mysql将创建用户和赋予权限分开了
  1.创建用户：create user "用户名"@"访问主机" identified by "密码";
  2.赋予权限
  grant 权限列表 on 数据库 to "用户名"@"主机";  (修改权限在后面加上 with grant option)
  3.刷新授权表
  flush privileges;
  

• 案例操作

  创建一个新用户han,并授权用户对demo数据库中的stu表的查询权限
  create user "han"@"%" identified by "root";
  grant select on demo.stu to "han"@"%";
  flush privileges;
  
  给demo库下的stu表加上删除权限
  grant delete on demo.stu to "han"@"%" with grant option;
  flush privileges;
  
  创建一个新用户han,并授权用户对demo数据库中的所有表的查询权限
  grant select on demo.* to "han"@"%";
  创建一个新用户han,并授权用户对所有数据库中的所有表的查询权限
  grant select on *.* to "han"@"%";
  创建一个新用户han,并授权用户对demo数据库下所有表的所有权限
  grant all privileges on demo.* to "han"@"%";
  

• 收回权限

  revoke 权限列表 on 数据库.数据表 from "用户名"@"主机";
  revoke delete on demo.stu from "han"@"%";
  

数据库的备份

mysql数据库备份：
1.冷备份：关闭数据库，拷贝数据
2.温备份：不需要关闭数据库，不允许数据库操作
3.热备份：不需要关闭数据库，用户可以数据库进行写操作(innerdb支持热备份)

冷备份
1.停止mysql服务 net stop mysql
2.拷贝data目录下的数据
3.重启mysql 服务 net start mysql

热备份 mysqldump
备份所有数据库
mysqldump -uroot -p --all-databases > file
mysqldump -uroot -p --all-databases > "E:\python0421\day24\all.sql"

备份指定数据库
mysqldump -uroot -p --databases db1 db2 > file
mysqldump -uroot -p --databases demo > "E:\python0421\day24\demo.sql"

备份一个数据中的表
mysqldump -uroot -p dbname tbname > file
mysqldump -uroot -p demo stu > "E:\python0421\day24\stu.sql"

PyMySQL 数据库操作

• python 连接mysql数据库，需要pymysql模块

• python操作数据库步骤

  1.连接数据库
  2.获取一个访问数据的操作对象 cursor() 游标
  3.执行sql语句
  4.提交事务
  5.关闭和数据库的连接，释放资源
  

• 连接数据库

  conn = pymysql.connect(
  	host="localhost", #数据库主机ip地址
  	user="root",	  #数据库的登录账号
  	password="root",  #登录密码
  	database="demo",  #连接的数据库   
  	port=3306,        #端口号
  	charset="utf8"
  	)
  	
  conn.close()
  

• cursor对象

  用于执行sql语句，使用频率最高的是select ,insert,update,delete
  使用cursor()方法获取游标对象
  cur = conn.cursor()
  
  对象的方法
  close() 关闭
  excute() 执行语句
  fetchone()  获取查询结果集的第一个数据，返回一个元组
  fetchall() 获取结果集的所有行，一行是一个元组
  

• 案例操作

  查询所有数据库并显示
  import pymysql
  conn = pymysql.connect(
  	host="localhost", #数据库主机ip地址
  	user="root",	  #数据库的登录账号
  	password="root",  #登录密码 
  	)
  获取游标对象
  cur = conn.cursor()
  ret = cur.execute("show databases;")
  print(ret)
  cur.close()
  conn.close()