数据库理论（二）

------

数据库三大范式:

数据库：

id 主管 下属1 下属2 下属3

1 A a1 a2

2 B b1

3 C c1 c2 c3

表不合理

第一范式： 数据表的结构不能因为插入的数据而更改

id 主管 下属

1 A a1

2 A a2

数据库：

id 名字 省 城市 区域 邮编

1 张三 四川省 成都 武侯区 610001

2 李四 四川省 成都 金牛区 610002

3 小花 四川省 成都 成华区 610003

表结构不合理

第二范式： 消除数据冗余，数据库的冗余是消除不了，我们会把文字冗余变成数字冗余（把重复率高的字段分表）

冗余： 重复率

省份表：

id province

1 四川省

2 湖北省

城市表：

id city pro_id

1 成都 1

2 南充 1

3 遂宁 1

区域表：

id area city_id code

1 武侯区 1610001

2 金牛区 1610002

3 顺庆区 2620001

用户表：

id name area_id

1 张三 1

数据库:

id ordernum product num price allprice

1 001 鞋子 3 50 150

表结构不合理

第三范式： 数据库表中不能存在对于的字段

总价 = 单价*数量

1.统计身份证： 名字 性别 名族 住址 身份证号 设计符合三大范式的数据表结构

2. 建立公司的员工信息表，需要员工的名字 职位 电话 办公地址 注意：一个员工可以有多个办公地址，设计符合三大范式的数据表结构

职位表

id 职位

员工表

id name tel 职位id

办公地址

id 地址 员工id

三大范式重点在于约束表结构

------

如何选择字段的数据类型

1. 字段一般都要勾选 not null

2. 预计不会有负数的字段，比如年龄，评论数量，点赞数..，必须设置unsigned(无符号类型) 全部>=0

3. 存储开关（是否 男女 支付状态...）等字段，它的值只有两种/三种，会使用tinyint 0 1

4. 任何字段类型本着空间够用就好，不要浪费内存

5. 一般日期存放的格式是int的时间戳格式，整数占的空间远比字符串占的空间小，并且时间戳查询出来之后可以转换成我们想要的任意格式

---------

视图： 就是将查询的结果复制成一张虚拟的表

语法：create view 视图名 as select....

对视图的查询和对普通表的查询是一样

create view fgoods as select * from `hn_goods`

select * from fgoods where id=19

作用：1. 复制表出来之后 可以直接对复制的表执行查询操作 原来的表就只需要执行写的操作 （实现读写分离，减少数据表压力）

create view fgoodsandcate as select `hn_goods`.`id`,`hn_goods`.`title`,`hn_goods`.`info`,`hn_goods`.`shopprice`,`hn_goodscate`.`id` as cateid,`hn_goodscate`.`name` from `hn_goods` inner join `hn_goodscate` on `hn_goods`.`cate_id`=`hn_goodscate`.`id`

select * from fgoodsandcate

作用：2、 我们会直接将多张表使用关联查询查询出来，并且复制成一种表，以后需要查询这些表的时候，就直接查询复制的表（简化sql语句）

create view fadmin as select `id`,`name` from `hn_admin`

select * from fadmin

作用：3、 权限控制 需要别人查询到的字段就复制出来，不需要查询到的就不复制

注意：1.视图名不能和已有的表名相同

2.在复制多表联查的时候，如果有重复的字段名需要使用as取别名

3.视图的操作语法和普通表的操作语法是一样，视图和原来的表是相关联的，无论那边变化，另外以便跟着变化

删除视图 drop view 视图名;

-------------------

存储过程：是一组为了完成特定操作的sql语句集，相当于php中的函数

语法：

create procedure 方法名(in n char(20),in p char(32),out insertid int)

begin

insert into `ah_admin` (`name`,`pwd`) value(n,p);

set insertid = last_insert_id();

end

set 变量名 = 值; sql语句中定义变量

last_insert_id() sql自带函数 返回最新添加的数据的id

调用存储过程：

call fun1('bbb','bbb123',@insertid);

select @insertid as id

删除存储过程:

drop procedure fun1

------------------

触发器： 是一个特殊的存储过程，不需要调用， 由某些时间点触发

语法：

create trigger 触发器名

after/before insert/update/delete on 表名

for each row

begin

sql语句集

end

create trigger aa

after delete on `ah_admin`

for each row

begin

insert into ah_nav (`name`,`url`) VALUES('首页','index.php');

end

触发器的缺点： 维护困难 触发器是不可见的，阅读也困难

删除触发器： drop trigger 触发器名

显示触发器： show triggers;

---------

索引： 相当于一本书的目录 专门优化查询

索引的本质 把某些字段的数据按照英文字母/数字的方式排列好

索引类型：

primary key(主键)：主键索引，在所有索引类型中查询速度最快的，但是一张表中只能有一个主键

unique: 唯一索引，查询速度仅次于主键，但是涉及到唯一索引的字段/字段组数据必须是唯一，一张表中可以存在多个唯一索引

normal(index):普通索引，查询速度最慢。没有任何显示（默认）

full text:全文索引，主要用于模糊查询，表类型一定是myisam,涉及到的字段必须是字符类型

使用方法：

admin

id name pwd 这张表怎么键索引

id 主键索引

name和pwd 使用唯一索引组

select * from admin where name='aa' and pwd='123'

1. 在查询的sql语句中，涉及到索引字段会自动使用索引

2. 索引的排序是自动按照数字/英文字母排序

3. 每个查询的sql语句中，where条件后面最多使用一个索引

where id=1 给id加主键索引

where name='aa' and pwd='123' name和pwd使用唯一索引组

where a=1 or b=2 会把重要的条件放在前面 然后给它加索引

4. 建索引的时候把重复率低的放前面，重复率高额放后面

5. 涉及到order by、 where 、group by 等关键字后面的字段必须加索引

6. 不要每个字段都键索引

7. 索引涉及的字段重复率越高效率越低

----------

数据库的优化 优化查询效率 减少查询的次数以及行数

1. 任何sql语句where条件后面的字段加索引 最好是主键 其次是唯一索引 最后普通索引

2. 一些关键字 = and 速度快于 or like in 速度快于 != not like not in 越往后查询的行数就越多

3. 尽量不要使用join，group by ,如果真的使用到了 后面的字段加索引

4. 越简单的sql语句运行效率越高，尽量不要去写复杂的sql语句

5. 尽量不要使用数据库自带的函数 last_insert_id() last_insert_sql()..

6. 查询的行数过多，会采用limit进行限制查询的条数 （查询条数越少，效率越高）

7. 在select中不要使用*代替所有，需要什么字段就写什么字段，所查即所得