2019北京培训：MySQL day3（索引、视图、事务以及设计规范）

1.视图

手动创建一张虚表，可以简化查询语句。

创建视图：create view 视图名 as 查询语句

删除视图：drop view 视图名

使用方法：和普通表使用方式一致。

注意：

（1）使用视图只能简化查询语句，不能提高查询效率

（2）只能查询，不能修改数据

作用：安全，隐藏真实的表名

#连接查询 select t.id,t.name,t.sex,t.birthdate,t.score,t.cid,c.name cname from t_student t     left join t_class c on t.cid=c.id; #查询所有女生 select t.id,t.name,t.sex,t.birthdate,t.score,t.cid,c.name cname from t_student t     left join t_class c on t.cid=c.id where sex=1; select * from( select t.id,t.name,t.sex,t.birthdate,t.score,t.cid,c.name cname from t_student t left join t_class c on t.cid=c.id )r1 where sex=1; #创建视图，简化查询语句 create view v_student as select t.id,t.name,t.sex,t.birthdate,t.score,t.cid,c.name cname from t_student t     left join t_class c on t.cid=c.id;      select * from v_student where sex=1; drop view v_student;

2.索引

提高查询效率

创建索引：create index 索引名 on 表名（字段）;

删除索引：drop index 索引名 on 表名；（MySQL）

drop index 索引名；（其他数据库）

使用方式：只需要在where条件中使用创建了索引的字段进行查询

注意：（1）索引不是越多越好，创建索引多了会占空间，影响insert、update、delete的效率

（2）主键约束字段和唯一约束字段自动创建索引，使用这样的字段查询效率最高

（3）字符串类型模糊查询时不使用索引，效率低。

#创建索引 create index sname on t_student(name); #使用name字段查询时效率高 select * from t_student where name = 'zhangsan'; #删除索引 drop index sname on t_student;

3.自增字段（MySQL）

防止唯一约束字段重复

创建自增字段：

create table t_course( id int primary key auto_increment,#自增字段 name varchar(50) not null, description varchar(50) not null ) charset=utf8 auto_increment=100;#自增初始值，可选

插入数据的时候，如果指定了字段值，则插入指定的值。

如果没指定自增值，则为自增值。

如果指定的值超过了自增值，则自增值改变为指定值+1

改变自增值：

alter table 表名 auto_increment = 值；

alter table t_course auto_increment=2000;

注意：一个表中只能有一个自增字段

4.默认值

创建表时，指定一个字段的默认值，插入数据时，如果没有指定这个字段的值，则这个字段为默认值。

create table t_card( id int primary key, sid int not null references t_student(id), info varchar(50) default 'no info'#指定默认值 ) ;   insert into t_card values(1,1,'zhangsan'); insert into t_card values(2,2,null); #指定了字段的值 insert into t_card (id,sid) values (3,3);#未指定值

插入时如为null也属于为这个字段赋值。

5.分页查询

查询语句 limit n;

查询前n条

查询语句 limit m,n;

从m后一条开始查询n条

select * from t_student; #查询前3条数据 select * from t_student limit 3; #查询考试前三名的学生 select * from t_student order by ifnull(score,-1) desc limit 3; #查询：从第4条开始查找3个学生 select * from t_student limit 3,3;

需求：每页显示m条，查询第n页的数据：Limit m*(n-1),m

6.事务

一个事务就是一个原子操作à不可分割的操作

在一个事务中，如果所有的操作都成功了，事务才能成功完成，有一个操作不成功，所有操作无效，还原成操作前的状态

事务的边界：

开始：上一个事务结束后的第一条DML语句（insert、update、delete）

结束：TCL语句（commit、rollback）

如果一个事务中所有的操作都成功了，则使用commit提交事务，提交后不可恢复

如果有一个操作不成功，则使用rollback回滚事务，同一个事务中的所有的操作还原

事务的原理：

事务的特性：（ACID）

1. 原子性：一个事务是一个不可分割的原子操作，要成功都成功，有一个操作失败，全部回滚
2. 一致性：事务提交前后，数据是一致的
3. 隔离性：多个事务之间是不会互相干扰的
4. 永久性：事务一旦提交，对数据库的影响就是永久的

#账户表 create table t_account( username varchar(50) primary key, balance double not null ); insert into t_account values('zhangsan',100000); insert into t_account values('lisi',100000); select * from t_account; update t_account set balance=100000 where username in('zhangsan','lisi'); #转账，以下两个操作为原子操作 update t_account set balance=balance-10000 where username='zhangsan'; update t_account set balance=balance+10000 where username='lisi'; rollback;#提交事务 commit;#回滚事务

 

7.数据的设计规范（三大范式）

第一范式（1NF）

数据库中的字段都是不可分割的原子项

缺点：没有规定主键

第二范式（2NF）

数据库表的每个字段都要和主键相关

第三范式（3NF）

数据库表的每个字段和要和主键直接相关