MySQL数据库（五）

表设计之关联关系

一对一

• 什么是一对一： 有AB两张表，A表中一条数据对应B表一条数据，同时B表一条也对应A表中的一条数据，这种关系称为一对一
• 应用场景：用户表和用户信息扩展表，商品表和商品详情表
• 如何建立关系：在从表中添加外键指向主表的主键

一对多

• 什么是一对多：有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中一条数据对应A表的一条数据，称为一对多。
• 场景： 员工表和部门表 商品表和商品分类表
• 如何建立关系：在多的表中添加外键指向另外一张表的主键

多对多

• 什么是多对多： 有AB两张表，A表中一条数据对应B表中的多条数据，同时B表中的一条数据也对应A表中的多条数据，称为多对多
• 场景：老师表和学生表
• 如何建立关系，通过单独的关系表保存两张表的主键

视图view

• 什么是视图：数据库中表和视图都是其内部的对象，视图可以理解成一张虚拟的表，数据来自原表，视图本质其实就是取代了一段SQL查询语句
• 为什么使用视图：视图可以起到SQL重用的作用，提高开发效率。可以隐藏敏感信息
• 格式：
  create view 视图名 as (子查询)

1. 创建一个10号部门的视图
   create view emp_10 as (select * from emp where deptno=10);
   create table emp_20 as (select * from emp where deptno=10);

2. 创建一个没有工资的员工表
   create view v_emp_nosal as (select empno,ename,job,mgr,comm from emp);

3. 创建一个显示每个部门平均工资、最高工资、最低工资、工资总和、部门人数的视图
   create view v_emp_info as (select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) from emp group by deptno);

视图分类

1. 简单视图：创建视图的子查询中不包含：去重、函数、分组、关联查询 的视图称为简单视图，可以对视图中的数据进行增删改查操作
2. 复杂视图：和简单视图相反，只能对复杂视图中的数据进行查询操作

对简单视图进行增删改操作

1. 插入数据
   insert into emp_10 (empno,ename,deptno) values(10010,‘Tom’,10);
   insert into emp_10 (empno,ename,deptno) values(10011,‘Jerry’,20);

• 数据污染：往视图中插入一条视图中不可见，但是在原表中可见的数据称为数据污染
• 如果不希望出现数据污染可以使用with check option 解决
  create view v_emp_30 as (select * from emp where deptno=30) with check option;
  insert into v_emp_30 (empno,ename,deptno) values(10012,‘Lily’,30);//成功
  insert into v_emp_30 (empno,ename,deptno) values(10013,‘Lucy’,10);//失败 数据污染

2. 修改和删除

• 只能对视图中存在的数据进行操作
  delete from v_emp_30 where deptno=10; //不会有数据被删除
  delete from v_emp_30 where ename=‘Lily’; //删除成功
  update emp_10 set ename=‘abc’ where empno=10010;//修改成功
  update emp_10 set ename=‘abc’ where empno=10011;//修改失败 视图中不存在

修改视图

• 格式： create or replace view 视图名 as (子查询);
  create or replace view v_emp_30 as (select * from emp where deptno=30 and comm>0);

删除视图

• 格式： drop view 视图名;
  drop view v_emp_30;

视图别名

• 如果创建视图的子查询中使用了别名，那么对视图进行操作时只能使用别名
  create view v_emp_30 as (select ename name,deptno from emp where deptno=30);
  delete from v_emp_30 where ename=‘xxx’; //报错 不认识ename

约束

• 什么约束： 约束就是给表字段添加的限制条件

非空约束 not null

• 值不能为null
  create table t1(id int,age int not null);
  • 测试：
    insert into t1 values(1,20);//成功
    insert into t1 values(2,null); //失败

唯一约束 unique

• 值不能重复
  create table t2(id int,age int unique);
  • 测试
    insert into t2 values(1,20);//成功
    insert into t2 values(2,20);//失败

默认约束 default

• 设置默认值
  create table t3(id int,age int default 20);
  • 测试
    insert into t3 values(1,10);
    insert into t3 values(2,null);
    insert into t3 (id) values(2); //默认值生效

主键约束 primary key

• 唯一且非空

外键约束

• 外键：用来建立关系的字段称为外键
• 外键约束：值可以为null，可以重复，但不能是关系表中不存在的数据，如果建立好关系后 被依赖的数据不能先删除，被依赖的表不能先删除
• 如何使用：
  1. 创建部门表
     create table mydept(id int primary key auto_increment,name varchar(10));
  2. 创建员工表
     create table myemp(id int primary key auto_increment,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id) references mydept(id));
  • 格式介绍：
    constraint 约束名 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名)
  • 插入数据
    insert into mydept values(null,‘神仙’),(null,‘妖怪’);
  • 测试：
    insert into myemp values(null,‘悟空’,1); //成功
    insert into myemp values(null,‘赛亚人’,3);//失败 不存在3
    delete from mydept where id=1; //失败 数据被依赖
    drop table mydept; //失败 表被依赖
    drop table myemp; //成功
    drop table mydept; //成功 没有被依赖
• 外键约束工作中除非特定场景 一般使用外键的机会较少，因为添加外键后影响测试效率

索引

• 什么是索引：索引是数据库中提高查询效率的技术，类似字典中的目录

• 为什么使用索引：如果不使用索引，查询数据时会挨个遍历每一个磁盘块查询数据，使用索引后，磁盘块会以树状结构保存，查询数据时能够大大的降低磁盘块的访问量，从而提高查询效率

• 有索引一定好吗？
  不是，如果数据量小，使用索引反而会降低查询效率

• 如何使用索引：

  1. 准备数据
     导入测试数据
     source d:/item_backup.sql
  2. 测试没有索引的情况下 查询速度
     select * from item2 where title=‘100’; //耗费1.06秒

• 创建索引格式：
  create index 索引名 on 表名(字段(字段长度));

  3. 通过title字段创建索引
     create index i_item_title on item2(title);
  4. 再次测试查询速度
     select * from item2 where title=‘100’; //耗费0.02秒

• 索引是越多越好吗？
  不是，因为索引会占用存储空间，只针对常用的查询字段创建索引

• 如何查看索引
  show index from item2;

• 索引分类：（了解）
  聚集索引： 给表添加主键约束后自动创建的索引，索引的树桩结构中保存着数据
  非聚集索引：通过非主键字段创建的索引，索引的树桩结构中没有数据保存的是主键值，通过非聚集索引找到主键值后还需要去聚集索引中查询具体数据

• 删除索引
  drop index 索引名 on 表名;
  drop index i_item_title on item2;

• 复合索引
  通过多个字段创建的索引
  create index i_item_title_price on item2(title,price);

索引总结

1. 索引是用来提高查询效率的技术，类似目录
2. 因为索引会占用磁盘空间索引不是越多越好，只针对常用的查询字段创建索引
3. 如果数据量小添加索引会降低查询效率，所以不是有索引就一定好

事务

• 数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部执行失败

事务的ACID特性

• ACID特性是保证事务正确执行的四大基本要素
• Atomicity：原子性， 最小不可拆分，保证全部成功或全部失败
• Consistency：一致性，从一个一致状态到另外一个一致状态
• Isolation：隔离性，多个事务之间互不影响
• Durability：持久性，当事务提交后持久保存到磁盘中

事务的相关指令

1. 开启事务 begin
2. 提交事务 commit
3. 回滚事务 rollback
4. 保存回滚点 savepoint xxx;
5. 回滚到某个回滚点 rollback to xxx;

group_concat() 组连接

查询每个部门的员工姓名和对应的工资，要求一个部门的信息显示到一行
10 张三：3000 李四：4000
20 王五：5000 赵六：6000

select deptno,group_concat(ename,’-’,sal) from emp group by deptno;