MySQL自关联、视图、事务、检索

自关联

每新建一张新表对于数据库的开销是很大，故如果整体数据不是很大且几张表的结构相近似的情况下可以用自相关，即用一张表来表示


建立表areas，并建立外键

create table areas(
aid int primary key, 
atitle varchar(20),
 pid int, 
 foreign key(pid) references areas(id)
 );

查询省的名称未山西省的所有城市

select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='山西省'

特点在于，将areas中city列提取作为一个逻辑新表city，并与areas中的province进行关联

视图

• 对于复杂的查询，在多次使用后，维护是一件非常麻烦的事情
• 解决：定义视图
• 视图本质就是对查询的一个封装

创建视图：

create view v_stuscore as
select students.*, scores. score from scores 
inner join students on scores. stuid=students. id;

其中，v_stuscore就是视图的名称，其含义就是select的内容

查询视图

select * from v_stuscore;

事务

当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退回
使用事务可以完成退回的功能，保证业务逻辑的正确性
事务四大特性（简称ACID）

1. 原子性（Atomicity）：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均术执行
2. 一致性（Consistency）：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
3. 隔离性（lsolation）：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
4. 持久性（Durability）：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

要求：表的类型必须是innodb或bdb类型(即引擎)，才可以对此表使用事务 查看表的创建语句

查看表的创建语句

show create table students;

修改表的类型

alter table '表名' engine=innodb;

事务语句

开启：begin;
提交：commt;
回滚：rollback;

事务的使用情况：当对数据进行更改时，即需要使用事务

事务的格式：

begin;

对数据进行操作的语句

commit或者rollback；

其物理意义是：当begin开开启一个事务后，然后对某个行的数据进行操作，就会将这个行进行锁定，其它人都动不了，与此同时也生成了一个临时的内存存储空间，将你改动的数据进行了记录，直到你commit后，才会将临时存储储空间中变动的数据更新到物理存储空间中去。如过你写的是rollback，则begin后面到rollback之间的改动都会取消，不会更新到物理存储器，从而保证了物理存储器中的数据没有被修改

索引

• 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快
• 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂
• 尽量避免NULL：应该指定列为NOTNULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

建立索引的时候，将等号的放在前面，范围的放置最后，这样会大大加快索引，如果范围的放在了中间，则范围后面的等号基本不起作用

尽可能不要用or

查看索引

show index from table_name;

创建索引

create index 索引名称 on mytable(username(length));

删除索引

drop indes [索引名称] on mytable;

**

案例

**

查看执行时间

show profiles;

为表areas的atitle列创建索引

creat index titleIndex on areas(atitle(20))

执行查询语句

select * from areas where atitle='北京市';

再次查看时间

show profiles;