SQL操作六

文章目录

1. 1. Day06
   1. 1.1. 视图
      1. 1.1.1. 视图概述
      2. 1.1.2. 视图分类
      3. 1.1.3. 视图数据污染
      4. 1.1.4. 往视图中插入数据，删除数据，修改数据
      5. 1.1.5. 避免视图数据污染(with check option)
      6. 1.1.6. 修改视图
      7. 1.1.7. 删除视图
   2. 1.2. 案例
   3. 1.3. 索引原理
      1. 1.3.1. 索引概述
      2. 1.3.2. 创建索引
      3. 1.3.3. 查看索引
      4. 1.3.4. 删除索引
      5. 1.3.5. 索引是越多越好吗？有索引就一定好吗？
      6. 1.3.6. 复合索引
      7. 1.3.7. 创建表的时候直接创建索引
      8. 1.3.8. 总结
   4. 1.4. 约束
      1. 1.4.1. 唯一约束 unique
      2. 1.4.2. 主键约束 (primary key)
      3. 1.4.3. 自增约束(auto_increment)
      4. 1.4.4. 外键约束（foreign key）
         1. 1.4.4.1. 创建外键约束
         2. 1.4.4.2. 外键总结
      5. 1.4.5. 非空约束(not null)
      6. 1.4.6. 默认约束(default)
      7. 1.4.7. check 约束
      8. 1.4.8. 什么是事务
      9. 1.4.9. 事务的ACID性质(重要，面试常考)
      10. 1.4.10. MySQL事务
      11. 1.4.11. 事务案例

Day06

视图

视图概述

• 什么是视图： 在数据库中存在多种对象，表和视图都是数据库中的对象，创建视图时视图名称不能和表名相同，实际上，视图就代表一个sql查询语句，也可以理解成视图是一张虚拟的表，但是虚拟表中的数据会随着原表数据的改变而改变
• 为什么使用视图：
  • 因为有些数据的查询需要书写大量的sql语句，每次书写比较麻烦，使用视图可起到重用sql语句的作用
  • 可以通过视图隐藏敏感信息，比如隐藏员工工资的字段，那么我们可以创建一个视图，其中不包含工资这个字段
• 创建视图的格式： create view 视图名 as 子查询
  • create view view_emp_10 as(select * from emp where deptno=10); 创建一个视图view_emp_10 （简单视图）
  • create view view_dep_20 as(select * from EMP where deptno=20 and sal<3000 ); 创建emp表部门是20，工资小于3000的视图
  • create view view_emp_sum_max(select max(sal),sum(sal),min(sal) from EMP); 复杂视图，只能查看，不能删除修改插入
• 如何查看视图： 查询的方式和表的查询方式一样(select)

视图分类

• 简单视图 ： 创建视图的子查询中 不包含去重，函数，聚合，关联查询的视图成为简单视图
  • 简单视图可以实现增删改查
• 复杂视图 ： 和简单视图相反
  • 复杂视图是能查询

视图数据污染

• 什么是数据污染： 在视图中插入一条数据，在视图中不显示，但是在原表中显示的数据，称为数据污染
• 在视图中只要进行insert操作时才会造成数据污染，因为update和delete只能操作视图中存在的数据
• 如果一旦执行插入语句，但是插入的数据不符合创建视图时的子查询条件，那么就会插入视图中失败，但是会插入到原表中，这个是我们不需要的，这个就叫做视图数据污染。

往视图中插入数据，删除数据，修改数据

• 简单视图：
  • 删除数据： 如果将视图中的数据删除了，那么原表中的数据也会删除掉
  • 插入数据： 如果插入的数据符合创建视图的子查询的条件，那么就会将数据插入进视图和原表中，如果不符合创建视图时的子查询的条件，那么数据只会插入到原表中，不会插入进视图。
    • 比如上面创建的view_emp_10视图，子查询条件为deptno=10,如果插入视图的数据的的deptno字段为10，那么就会显示在视图和表中，如果不为10，那么只会显示在表中。
  • 更新数据： 如果更新后的数据不符合创建视图时子查询的条件的话，那么这些数据就会移除视图，但是原表中还会显示，只是不在视图中显示。 如果满足子查询的条件，那么就会成功更新在视图中，原表和视图的数据都会更新并且都会显示
    • 比如上面创建的view_emp_10的视图，如果我们将视图中的一条数据的deptno改为11，那么这条数据将不会显示在视图中，只会显示在原表中。
  • 总结：
    • 更新和删除都是对视图中已经存在的数据进行操作，如果存在就会直接删除和更新，并且原表中的数据也会同时被删除和更新，但是如果执行更新操作，更新后的条件不符合创建视图时子查询的条件，那么这些数据将不会显示在视图中，但是在表中还是存在的
    • 插入数据有可能会造成数据污染

避免视图数据污染(with check option)

• 我们在创建的视图的时候加上with check option 即可
  • create view v_emp_30 as(select * from EMP where deptno=30) with check option;
  • 现在我们在往v_emp_30的视图中插入数据，其中字段deptno不等于30，那么就会报错，因为不符合创建视图时的子查询条件
  • 但是如果我们没有使用with check option，那么我们就会插入成功，虽然不会在视图中显示，但是插入到原表中了，造成了视图数据污染

修改视图

• 我们创建一个视图
  • create view view_emp_10 as(select * from emp where deptno=10);
• 现在我们需要为子查询加上一个条件 工资大于3000的，即是 创建一张视图，里面数据是部门号为10，工资大于3000的全部员工信息，那么我们就需要在原有的view_emp_10的基础上修改
  • create or replace view view_emp_10 as(select * from EMP where deptno=10 and sal>3000);直接在create后面加上or replace即可，有就替换

删除视图

• 格式 ：drop view 视图名称
• drop view_emp_10; 删除视图view_emp_10，如果存在就删除，不存在就报错
• 加上关键字 if exists 如果存在就删除，不存在也不报错
  • drop view if exists view_emp_10;
• 如果创建视图的时候对视图中的字段使用了别名，那么以后对视图的操作只能使用别名来操作
  • create view view_1 as(select ename name from emp where deptno=10); 这里面的子查询将字段ename起了别名，那么我们在以后操作的时候只能使用别名对这个字段操作

案例

• 创建视图显示每个部门对应的员工的名字
  • `create view view_emp_dept as(select d.dname,e.ename from EMP e join Dept d on d.deptno=e.deptno);
• 修改上面的视图在上面题的前提下只显示工资在3000以内的
  • create or replace view view_emp_dept as(select d.dname,e.ename from EMP e join Dept d on d.deptno=e.deptno where e.sal<3000);
• 删除上面的视图
  • drop view if exists v_emp_dept;

索引原理

索引概述

• 什么是索引：
  • 索引是用来提高查询速度的技术，类似于一个目录，查询数据时会从索引中对数据进行定位，然后直接找到数据所在的位置
• 为什么使用索引 ：
  • 因为不使用索引的话，查询数据会按照磁盘块一块一块的去查，如果数据量很大，效率很低
• 索引分为聚集索引和非聚集索引
  • 在mysql中数据库会为主键自动创建聚集索引，聚集索引中数据是有序保存
• 索引内部实现原理 ：
  • B+tree
• 数据库中创建索引的过程是数据库内部自己控制，然后使用索引的过程也是数据库自己操作的，不需要程序猿干涉

创建索引

• 格式： create index 索引名 on 表名(字段名([长度]));
• 创建索引之前先查询title=’100’的数据，看看查询时间
  • select * from item2 where title=’100’;
• 创建title索引
  • create index index_title on item2(title);
• 再次查询，查看时间
  • select * from item2 where title=’100’;

查看索引

• 格式: show index from 表名
• 其中包含主键的索引，这个是自动创建的

删除索引

• 格式：drop index 索引名 on 表名
• drop index index_title on item2;

索引是越多越好吗？有索引就一定好吗？

• 因为索引会占用磁盘空间，所以创建索引需谨慎，只创建查询需求的索引
• 索引要建立在大量的数据的表中，如果数据量不够大，可能会降低查询效率

复合索引

• 创建索引的时候指定多个字段，此时如果查询数据正好过滤条件为这多个字段的话，可以降低磁盘块的访问，从而提高查询效率
• 创建复合索引: create index index_title_price on item2(title,price);
• 执行查询语句
  • select * from item2 where title='100' and price <100000; 可以看出查询效率很高

创建表的时候直接创建索引

• create table t_index(id int,age int ,index index index_age(age));
• 直接在字段后面写入 index 名字(字段)

总结

• 索引会占磁盘空间，不是越多越好
• 数据量小的表不要创建索引
• 对于经常出现在where ，order by，distinct 后面的字段创建索引 ，效果更好
• 不要在频繁修改的表中创建索引

约束

• 什么是约束： 约束就是对表字段的数据进行限制的规则

唯一约束 unique

• 添加唯一约束的字段，这个字段的值不能重复,否则报错
• crate table t(id int ,age int unique);

主键约束 (primary key)

• 创建表时添加主键约束
  • create table t(id int primary key auto_increment,age int);
• 创建表之后添加主键 (primary key(字段名) )
  • alter table t add primary key(id);
• 删除主键约束
  • 格式： alter table 表名 drop primary key
  • alter table t drop primary key;

自增约束(auto_increment)

• 当字段赋的值为null时，字段会自动增长
• 如果删除了某条数据，自增数值不会减少
• 自增的基础是根据字段的最大值来自增的
• create table t(id int primary key auto_increment,age int);
• 如果使用delete清空表(delete from t) ,那么自增的值不会从头开始
• 如果使用truncate table t 的方式清空表，那么自增的值会从头开始，则从1开始

外键约束（foreign key）

• 外键约束是保证一个表或者两个表之间数据一致性和完整性的约束
• 工作中除非特殊情况，一般不使用外键约束，通过代码逻辑进行限制，避免测试时不必要的麻烦
• 外键的值通常是另外一张表的主键
• 外键可以重复，可以为null，但不能是另外一张表中不存在的数据 -
• 使用外键约束的条件：
  1. 必须保证两张表使用相同的引擎(engine)
  2. 引擎必须是innodb,myisam不支持外键约束
• 外键和关联字段必须是相同的数据类型，比如一张表的主键id的外键，那么这个外键一定要是int类型
• 外键所对应的关联字段如果不是主键，会自动为该字段创建索引

创建外键约束

• 格式 ：
  • create table t(id int primary key auto_increment,deptid int,constraint 约束名 foregin key(deptid) references 关联的表名(关联表的字段名))
• 创建两张表 t_emp 和 t_dept
  • 先创建部门表 create table t_dept(id int primary key auto_increment,name varchar(10));
  • 创建t_emp create table t_emp(id int primary key auto_increment,name varchar(10),deptid int,constraint fk_dept foreign key(deptid) references t_dept(id));
• 测试：
  • 如果插入数据到t_emp中的时候，其中的deptid的值在t_dept中的id不存在的话，那么插入失败，因为两个是外键关联的
  • 如果想要删除t_dept的数据，但是在t_emp中的还有关联的数据(即是deptid)，那么删除失败，只有将t_emp中关联的数据字段deptid设置为null，此时在删除才会成功

外键总结

• 保证一个表或两个表之间的数据一致性和完整性，工作不怎用，外键的值是关联表的主键，值可以是null可以重复，不能是不存在的数据，使用外键必须两张表使用innodb引擎，数据类型要一致，会自动添加索引

非空约束(not null)

• 该字段的值不能为null，否则报错

默认约束(default)

• 给字段设置默认值
• create table t(id int primary key auto_increment,age int not null default 0); 设置字段age设置默认值为0 ，如果插入数据的时候没有插入age的值，那么默认赋值为0

check 约束

• 在mysql中不生效，但是语法不报错
• create table t_check(id int,age int,check(age>10));

什么是事务

• 事务是数据库执行sql语句的工作单元或者最小单元，写在事务里面的sql要么同时成功，要么同时失败

事务的ACID性质(重要，面试常考)

• Automicity ： 原子性 ： 执行的sql语句要么同时成功，要么同时失败
• Consistency： 一致性 ： 无论事务是否执行成功，必须保证一个一致性的标准，比如转账，必须保证转账前后的总金额不变
• Isolation： 隔离性 ： 事务和事务之间互不影响
• Durablity ： 持久性 ： 事务执行完之后数据持久保存到数据库中

MySQL事务

• show variables lile “%autocommit%”
• set autocommit=0/1
• 开启 begin
• commit
• rollback
• savepoint s1
• rollback to s1

事务案例

• 转账