mysql-day06

##视图

- 什么是视图:在数据库中存在多种对象,表和视图都是数据库中的对象,创建视图时名称不能和表重名,视图实际上就代表一段sql查询语句,也可以理解成视图是一张虚拟的表,此虚拟表中的数据会随着原表的改变而改变.

-创建视图:

  -格式:create view 视图名 as 子查询

  案例:创建10号部门员工的视图

  create view v_emp_10 as (select * from emp where deptno=10);

如何查看视图

  select * from v_emp_10;

 

-为什么使用视图:因为有些数据查询需要写大量的sql,每次书写比较麻烦,使用视图可以起到重用sql的作用,还可以隐藏敏感信息

   1. 案例:创建隐藏工资的emp表视图;

　　create view v_emp_nosal as (select empno,ename,job,comm from emp);

   2.案例:创建商品表的单价小于100的视图,视图中只显示标题,单价和库存;

　　create view v_t_item_price as (select title,price,num from t_item where price<100);     

   3.创建emp表视图,显示工资总和,平均工资,最小工资;

　　  create view v_emp_sal as (select sum(sal),avg(sal),min(sal) from emp);         

###视图的分类

- 简单视图:创建视图的时候 不包含:去重,函数,聚合,分组,关联查询,则该视图称为简单视图,简单视图可以对数据进行增删改操作

- 复杂视图:和简单视图相反,不能对数据进行增删改,只提供数据查看功能

-简单视图的增删改操作:

　　 查看所有视图 和 查看表是一样的

 1.插入数据

   insert into v_emp_10(empno,ename,deptno) values(123321,'高渐离',10);

 insert into v_emp_10(empno,ename,deptno) values(123321,'兔兔威',10) character set utf8;

-插入20号部门的狄仁杰

 insert into v_emp_30(empno,ename,deptno) values(456652,'狄仁杰',30);

-数据污染:往视图中插入一条在视图中不显示,但是在原表中显示的数据,称为数据污染,通常工作中不允许出现,可以通过以下关键字,避免数据污染.

-with check option

-案例:创建30号部门员工的视图

  create view v_emp_30 as (select * from emp where deptno=30)with check option;

  -此时如果插入不符合条件的数据就会报错

  insert into v_emp_30(empno,ename,deptno) values(456652,'狄仁杰',10);

2.修改数据

  update v_emp_10 set ename='诸葛亮' where empno=123321;

 -修改数据只能修改视图中存在的数据

  update v_emp_10 set sal=200 where deptno=20;

3.删除数据

  delete from v_emp_10 where empno=123321;

  -删除数据只能删除视图中存在的数据

  delete from v_emp_10 where deptno=20;

###修改视图

　create or replace view v_emp_10 as (select * from emp where deptno=10 and mgr is not null);

###删除视图

    drop view if exists v_emp_10;

-如果创建视图的时候字段名起了别名,那么之后对视图的操作必须使用别名

 

1.创建视图显示每个员工名称和对应的部门名称

 create view v_emp_1 as(select ename,deptno from emp);

2.修改第一题的视图 添加条件 只显示工资低于3000的信息

create or replace view v_emp_1 as(select ename,deptno,sal from emp where sal<3000);

create or replace view v_emp_10 as (select * from emp where deptno=10 and empno=7782);--------对的,是需要这样写

select  * from  v_emp_1;

3.删除第二题视图 

drop view if exists v_emp_1;

*****数据库的字符集是latin1

*****表的字符集是uft8

*****插入数据时,为什么会出现乱码?

还是要修改数据库的字符集    alter db1 character set utf8

##索引

###什么是索引

　　索引是数据库中提高查询速度的技术,类似于一个目录,查询数据会先从目录中对数据进行定位,然后再找到某位置的数据

###为什么使用索引

　　因为数据库中的数据最终保存在磁盘的磁盘块中(4kb),如果保存的数据量大,则数据会无序分布在大量的磁盘块中,要想查询数据会依次遍历每一个磁盘块,这种查询方式效率非常低,

　　如果使用了索引,会在数据库中保存一个树状的目录,查询数据时会通过树状目录去查询,这样的话大大的降低磁盘块的访问量

###索引分类

　　1.聚集索引(聚簇索引):类似于字典中的拼音目录,数据会按照索引的顺序依次在磁盘块中保存,在磁盘块中保存数据本身

　　在mysql中会自动为主键添加聚集索引

　　2.非聚集索引:在磁盘块中没有数据,只有和数据对应的磁盘块地址

###索引的原理图


###如何创建索引

1.先执行下面sql看查询时间

select * from item2 where title='100';

2.对title字段创建索引

  create index index_title on item2(title);

3.再次查询看时间:

select * from item2 where title='100';

###查看表的索引

 show index from 表名

举例:show index from item

###删除索引

   -格式: drop index 索引名 on 表名;

　　举例:drop index index_title on item2;

###索引是越多越好吗?

不是,索引会占磁盘的储存空间,不常用的字段不建议创建索引

###有索引一定好吗?

　　数据量如果很小,创建索引有可能会降低效率

###复合索引

复合索引就是创建索引的时候指定的多个字段

-格式:create index 索引名 on 表名 (字段1,字段2);

-应用场景:当查询数据时经常使用多个字段同时作为查询条件时,使用复合索引

###索引的总结

　　索引是数据库中提高查询效率的技术,索引会占磁盘空间,不是越多越好,数据量小的表不建议创建索引.***频繁修改的表,不建议添加索引

　　对于经常出现在where/order by/distinct中的字段添加索引效果更好. 表中聚集索引最多只能有一个,给表添加主键时自动创建,非聚集索引可以创建多个

##mysql约束

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

###唯一约束 unique

　　-添加约束后的字段 不能插入重复数据

　　create table t_uni(id int unique); 

　　insert into t_uni values(18);

　　-插入重复数据会报错

###非空约束  not null

　　-添加非空约束的字段 值不能为null

　　create table t_notnull (id int not null);

　　insert into t_notnull values(null);

　　-插入null值时会报错

###主键约束 primary key

-创建表时添加主键约束

　　create table t(id int primary key auto_increment);

-创建表后添加主键

　　-格式:alter table t add primary key(字段名)

练习:创建一个有id字段的表 不带主键 通过上面的方式添加主键.

　　create table a (id int);

　　alter table a add primary key(id);

 -删除主键

alter table a drop primary key;

###自增约束 auto_increment

1.当赋值为null,字段的值会自动增长

2.删除数据主键值不会减

3.如果插入数据指定了一个比较大的数值,下次插入数据会在表中最大值的基础+1;

4.使用delete删除全表数据,数值不会从头开始

5.使用truncate数值会从头开始

###外键约束(foreign key)

-什么是外键约束:外键约束是保证两个表之间数据的一致性和完整性的约束

-如果添加了外键约束,删除数据时不能删除被关联的数据,插入数据时外键值可以为null,但是如果赋值,值必须是在关系表中出现的值,如果关系表没有这个值则会报错

-外键值可以null,可以重复,不可以是关系表不存在的值

-工作中如果没有特殊情况,通常不使用外键约束,避免测试数据时的麻烦,外键约束是通过代码逻辑实现

-外键的值通常为另外一张表的主键值

-外键数据类型要和关系表主键的数据类型一致

-使用外键约束的条件:

　　1.必须保证两张表都是相同的引擎

　　2.mysiam不支持外键,只能使用InnoDB引擎

###如何使用外键约束:

　　-格式:

　　create table 表名(empno int,deptno int,constrain 约束名 foregin key(deptno) references 关联的表名(关联表字段名));

　　-测试:创建两张表 t_emp 和 t_dept

　　1.创建部门表

　　create table t_dept(id int primary key auto_increment,name varchar(10));

　　2.创建员工表

　　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));

　　测试:部门表插入神仙部和妖怪部

　　insert into t_dept values(null,'神仙部'),(null,'妖怪部');

　　insert into t_emp values(null,'观音',1),(null,兔兔威",2);

###外键总结:

1.保证两个表指之间数据的一致性完整性

2.工作中使用逻辑外键 因为测试太麻烦

3,可以是null,可以重复,但是不能是关系表中不存在的值

##默认约束

　　-给字段添加默认值

　　create table t_def(id int,age int default 0);

　　insert into t_def(id) values(1);

　　-如果不给字段赋值,此字段的值为默认值

##check约束

　　-给字段添加一个条件,但是mysql中不生效,不过不报错

　　create table t_check(id int,age int,check(age>20));

##事务

###什么是事务

　　执行sql语句的最小工作单元,写在事务里面的sql要么同时成功,要么同时失败

###事务的ACID特性  面试重点

　　Automicity:原子性,最小,不能拆分

　　Consistency:一致性,同时成功或同时失败

　　Isolation:隔离性,多事务之间互不影响

　　Durability:持久性,事务执行完后数据保持到数据库文件中持久生效

###事务相关sql

　　查看自动提交状态:

　　show variables like '%autocommit%'

　　设置自动提交

　　set autocommit=0/1;

　　手动提交  commit　　

　　回滚  rollback

　　保存回滚点 savepoint s1

　　回滚到某个保存点

　　rollback to s1