MySQL数据库（五）

「爱情、让人受尽委屈。」 2022-02-17 12:55 178阅读 0赞

### 表设计之关联关系 ###

#### 一对一 ####

*  什么是一对一： 有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);//失败 数据污染

1.  修改和删除

*  只能对视图中存在的数据进行操作  
    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 表名(字段(字段长度));
    
    1.  通过title字段创建索引  
        create index i\_item\_title on item2(title);
    2.  再次测试查询速度  
        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;