Mysql 从入门到精通（6）- 视图-约束-索引

###面试题：
1. 设计表
交易时间、交易金额、交易类型、姓名、性别、关系
- 流水表：
    create table trade(id int primary key auto_increment,time date,money int,type varchar(5),person_id int);

- 人物表：
    create table person(id int primary key auto_increment,name varchar(10),gender varchar(5),rel varchar(5));
如果考虑数据冗余 可以把交易类型 和关系 单独拆分到一张表当中 
2. 插入数据
    insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友');
    insert into trade values(null,'2018-08-20',-20,'微信',1),
    (null,'2018-04-20',500,'现金',2),
    (null,'2018-05-10',-50,'现金',2),
    (null,'2018-05-20',50000,'支付宝',3),
    (null,'2018-06-20',-5,'支付宝',3),
    (null,'2018-06-21',2000,'微信',4),
    (null,'2018-08-20',-20000,'微信',5);
3. 统计2018年2月15号到现在的所有红包收益
    select sum(money) from trade where time>str_to_date('2018年2月15号','%Y年%c月%d号');
4. 查询2018年2月15号到现在金额大于100，所有女性亲戚的名字和红包金额
    select p.name,t.money
    from trade t join person p
    on t.person_id=p.id
    where time>str_to_date('2018年2月15号','%Y年%c月%d号')
    and t.money not between -100 and 100
    and p.gender='女'
    and p.rel='亲戚';
5. 查询三个平台分别收入的红包金额
    select type,sum(money) from trade 
    where money>0 
    group by type;

###视图
- 数据库中包含多种对象，表和视图都是数据库中的对象，视图可以理解成一张虚拟的表，视图本质就是取代了一段sql查询语句
- 为什么使用视图：因为有些数据的查询需要写大量的SQL语句，每次书写比较麻烦，通过使用视图相当于把当了的SQL查询语句进行保存，下次从视图中查询就不用再次书写大量SQL语句，从而提高开发效率。隐藏敏感字段

- 视图格式： create view 视图名 as (子查询);
    create view v_emp_10 as (select * from emp where deptno=10);
    delete from emp where sal=1300;
    select * from v_emp_10;

1. 创建一个没有工资的视图
    create view v_emp_nosal as (select empno,ename,comm,mgr from emp);
2. 创建视图，视图中显示每个部门的工资总和，平均工资，最高工资，最低工资
    create view v_emp_info as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);

- 视图的分类：
1. 简单视图：创建视图的时候不包含：去重、分组、函数、关联查询的视图称为简单视图，可以对视图中的数据进行增删改查
2. 复杂视图：和简单视图相反，只能进行查询操作
- 简单视图的增删改操作  操作方式和操作table一样
- 插入数据
    insert into v_emp_10 (empno,ename,deptno) values (10011,'悟空',10);
    insert into v_emp_10 (empno,ename,deptno) values (10012,'八戒',20);(数据污染)
- 往视图中插入一条在视图中不显示但是在原表中显示的数据，称为数据污染
- 通过 with check option 关键字解决数据污染问题
    create view v_emp_20 as (select * from emp where deptno=20) with check option;
    -测试：
    insert into v_emp_20 (empno,ename,deptno) values (10013,'刘备',20); //成功
    insert into v_emp_20 (empno,ename,deptno) values (10014,'张飞',30); //失败
- 删除和修改：只能操作视图中存在的数据 
- 别名：如果创建视图时使用别名 则 操作视图时只能使用别名
    create view v_emp_30 as (select ename name from emp where deptno=30);
    select * from v_emp_30 where ename='james';

###视图总结：
1. 视图时数据库中的对象，可以理解成一张虚拟的表，本质就是一段SQL语句
2. 作用： 重用SQL，隐藏敏感字段
3. 分类：简单视图(不包含去重 分组 函数 关联查询，可以增删改查)和复杂视图(反之，查询)
4. 通过with check option 解决数据污染
5. 删除和修改时 只能操作视图中存在的数据
6. 起了别名 只能用别名、

###约束
- 约束： 约束是创建表时给字段添加的限制条件
####非空约束 not null
- 字段值不能为null

    create table t1(id int,age int not null);
    insert into t1 values(1,18);//成功
    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);//失败
####主键约束
- 字段的值唯一且非空
- 创建表时添加主键约束: create table t3(id int primary key,age int);
- 创建表之后添加主键约束： 
        create table t3(id int,name varchar(10));
        alter table t3 add primary key(id);
- 删除主键约束 
    alter table t3 drop primary key;
#### 自增
- 数值只增不减
- 从历史最大值的基础上+1
- 字段值赋值为null的时候自动+1
- 使用delete 删除全表数据 自增数值不变
- 使用truncate 自增清零
####默认约束 default
- 给字段添加默认值,当插入数据不给该字段赋值时 默认值生效
        create table t4(id int,age int default 10);
        insert into t4 values(1,20);
        insert into t4 values(2,null);
        insert into t4 (id) values(3);//默认值生效
####检查约束 check
- 语法支持,但是没有效果.
        create table t5(id int,age int check(age>10));
        insert into t5 values(1,5);
####外键约束 
- 外键约束作用：为了保证两个表之间的关系正确建立
1. 插入数据时外键值可以为null，可以重复，但是不能是另外一张表不存在的数据
2. 被依赖的表不能被先删除
3. 被依赖的数据不能先删除
- 如何使用外键：
1. 创建部门表
    create table dept(id int primary key auto_increment,name varchar(10));
2. 创建员工表
    create table emp(id int primary key auto_increment,name varchar(10),deptid int, constraint fk_dept foreign key(deptid)references dept(id));
- 介绍：
    constraint 约束名称 foreign key(外键字段名)references 表名(字段名)
- 测试：
    insert into dept values(null,'神仙'),(null,'妖怪');
    insert into emp values(null,'悟空',1);//成功
    insert into emp values(null,'八戒',1);//成功
    insert into emp values(null,'超人',3);//失败  因为有外键约束，不能添加dept中没有的部门员工
    drop table dept;//失败    
    delete from dept where id=2;//成功  因为emp中没有数据与部门2对应
    delete from dept where id=1;//失败  。。。。。有。。。。。。。。
###索引
- 什么是索引： 索引是数据库中提高查询效率的技术，类似于字典的目录
- 为什么使用索引：如果不使用索引数据会零散的保存在每一个磁盘块当中，查询数据时需要挨个的遍历每一个磁盘块查找数据，如果数据量超级大，遍历每一个磁盘块是件非常耗时的事情，添加索引后，会将磁盘块以树桩结构进行保存，查询数据时会有目的性的访问部分磁盘块，因为访问的磁盘块数量降低所以能起到提高查询效率的作用  
- 索引是越多越好吗？ 
    不是，因为索引会占磁盘空间，通过某个字段创建的索引可能永远用不上，则这个索引完全没有存在的意义，只需要对查询时频繁使用的字段创建索引
- 有索引就一定好吗？
    不一定，如果数据量小使用索引反而会降低查询效率 
- 索引的分类(了解)
1. 聚集索引(聚簇索引)： 通过主键创建的索引为聚集索引，添加了主键约束的表会自动添加聚集索引，聚集索引的树桩结构中保存了数据
2. 非聚集索引：通过非主键字段创建的索引叫做非聚集索引，树桩结构中只保存了数据所在磁盘块的地址并没有数据。
- 导入数据：
- windows电脑 把文件放到D盘下
    source d:/item_backup.sql;

- Linux系统 把文件放到桌面
    source /home/soft01/桌面/item_backup.sql;
- 测试：
1. show tables; 看是否有item2这张表
2. select count(*) from item2; 看是否有一万条数据（因为批量插入数据太多我试过20万的数据量，服务器当掉了，所以用一万多条条数据实验），

### 我们不做正面验证，因为需要添加的数据太多，我们做反面的验证，数据量较少时，添加索引反而效率没有不添加时快：

3. select * from item2 where title='戴尔(DELL) XPS15升级版 ';  //看一下耗时 0.028秒
- 创建索引的格式：
    create index 索引名 on 表名(字段名[(字符长度)]);
    
    create index i_item_title on item2(title);  
    -创建完后继续执行
    select * from item2 where title='戴尔(DELL) XPS15升级版 ';  //看一下耗时 0.158秒 。，速度拖慢了很多
- 查看索引：
    show index from item2;
    
- 删除索引
    drop index 索引名 on 表名;
    drop index i_item_title on item2;

- 复合索引
    通过多个字段创建的索引称为复合索引
    -格式：create index 索引名 on 表名(字段1,字段2);
    频繁使用多个字段进行数据查询时为了提高查询效率可以创建复合索引
    select * from item2 where title='100' and price<100;
    create index i_item2_title_price on item2(title,price); 
- 总结：
1. 索引是用于提高查询效率的技术，类似目录
2. 索引会占用磁盘空间不是越多越好
3. 如果数据量小的话 添加索引会降低查询效率
4. 尽量不要在频繁改动的表上添加索引

####事务
- 数据库中执行SQL语句的最小工作单元，保证事务中的多条SQL全部成功或全部失败
- 事务的ACID特性：
1. Atomicity： 原子性， 最小不可拆分 保证全部成功或全部失败
2. Consistency： 一致性，从一个一致状态到另外一个一致状态
3. Isolation：隔离性，多个事务之间互相隔离互不影响
4. Durability:持久性， 事务提交后 数据持久保存到数据库文件中
- 事务相关指令：
- 查看自动提交状态： show variables like '%autocommit%'
- 修改自动提交:  set  autocommit=0/1;
- 提交： commit;
- 回滚： rollback;
- 保存回滚点： savepoint s1;
- 回滚到指定回滚点： rollback to s1;

###group_concat()  分组连接函数
1. 查询员工表中 每个部门的所有员工工资 要求所有工资显示到一条数据中
    select deptno,group_concat(sal) from emp group by deptno;
2. 查询员工表中每个部门 的员工姓名和对应的工资 要求显示到一条数据中
    select deptno,group_concat(ename,'：',sal) from emp group by deptno;
###面试题：
有个学生表student (id,name,subject,score)
    create table student(id int primary key auto_increment,name varchar(10),subject varchar(5),score int);
保存以下数据：
张三 语文 66  ，  张三 数学 77  ， 张三 英语 55 ， 张三 体育 77
李四 语文 59  ，  李四 数学 88  ， 李四 英语 78 ， 李四 体育 95
王五 语文 75  ，  王五 数学 54  ， 王五 英语 98 ， 王五 体育 88
    insert into student values(null,'张三','语文',66), (null,'张三','数学',77),(null,'张三','英语',55),(null,'张三','体育',77);
    insert into student values(null,'李四','语文',59), (null,'李四','数学',88),(null,'李四','英语',78),(null,'李四','体育',95);
    insert into student values(null,'王五','语文',75), (null,'王五','数学',54),(null,'王五','英语',98),(null,'王五','体育',88);
1. 查询每个人的平均分 从大到小排序
    select name,avg(score) a from student group by name order by a desc;
2. 每个人的姓名 科目 成绩 一行显示出来
    select name,group_concat(subject,':',score) from student group by name;
3. 查询每个人的最高分和最低分
    select name,max(score),min(score) from student group by name;
4. 查询每个人不及格的科目以及分数和不及格的科目数量
    select name,group_concat(subject,':',score),count(*) from student 
    where score<60 group by name;