数据库理论：
主管 下属1 下属2 下属3— 字段列
A A1 A2 A3
B B1 B2
C C1
D D1 D2 D3 D4(需要增加一个字段存放下属4)
第一范式：列不可再分
数据表不能因为插入数据而更改表结构
主管 下属
A A1
A A2
A A3
ID name address province city code
1 杨欧 东风大桥 四川省 1 成都市 11 610000
2 苟双林 东风大桥 四川省 1 成都市 11 610000
3 高榜群 川师 四川省 1 成都市 11 610068
第二范式：行不可再分
消除数据冗余，数据冗余不可能完全消除，我们把文字冗余变为数字冗余
省市区表
id name parent_id code
1 四川省 0 *******
2 成都市 1 *******
3 青羊区 2 *******
ID name address province city code
1 杨欧 东风大桥 1 2 610000
2 苟双林 东风大桥 1 2 610000
3 高榜群 川师 1 2 610068
订单表
id order_id price num sum_price
1 12165154 2.00 5 10.00
2 21545564 5.20 5 20.80
第三范式：表不可再分
数据表中不存在多余的字段
三大范式：
1NF:列不可再分 数据表不能因为插入数据而更改表结构
2NF:行不可再分 把文字冗余变为数字冗余
3NF:表不可再分 数据表中不存在多余的字段
作业:
1.身份证上有：姓名 性别 民族 住址和身份证号，试设计符合三大范式的数据表
2.为公司创建通讯录，记录每个员工的姓名，性别，所在部门，职位，办公地址，电话和邮箱，记住办公地址可能有多个，试设计符合三大范式的数据表
民族表
id name
身份证表
id name sex(0表示女 1表示男) nation address id_card
部门表
id name
职位表
id name
办公地址
id address_name
员工表
id name sex partiment position address tel email
数据库字段类型：
第一种是数字型 第二种是字符型
数字型： int float double decimal(这个专门用来存价格)
1.无符号类型：就是规定了字段不能是负数 那么我们预计存储的字段是非负数的时候，我们就需要选择无符号
1B = 8位
tinyint 1 有符号-128~127 无符号0~255
smallint 2 -32768~32767 0~65535
mediumint 3 -8388608~8388607 0~16777215
int 4 -2147483648~2147483647 0~4294967295
bigint 8 -9223372036854775808~9223372036854775807 0~18446744073709551615
2.我们在存储开关，性别等字段时我们直接选择tinyint类型
3.一般情况下 我们需要设置字段为not null
4.任何类型的数据表，字段空间本着足够用，不浪费原则
5.一般来说，我们存储日期的话选择int类型，存放时间戳
存放2015-05-20 11：05：32 显示05/20/2015 11:05 改变显示样式很不方便 date(“Y-m-d H/i/s”,time());
查询2个时间点之间的数据 字符串的大于小于不好比较 时间戳的话好比较大小
字符型
定长表：就是固定长度的表，不包含任何变长字段的数据表，为定长表
变长表：就是包含任何varchar,text等变长字段的数据表为变长表
表1
id name(char(10))
1 red [red ]
2 blue [blue ]
3 yellow [yellow ]
4 gray [gray ]
表2
id name(varchar(10))
1 red [red]
2 blue [blue]
3 yellow [yellow]
4 gray [gray]
比如我们要查询red的话，定长表是如果第一个字符不符合就不会一个个字符的去查找，变长表的话则会一个个字符的去匹对
所以说定长表的查询速度快于变长表，经常被查询的表就要设为定长表
admin (经常被查询，所以设为定长表)
id name pwd
新闻表
id title intro content
第一张表 定长表
id title intro
第二张表 变长表
id news_id content
视图：由查询结果组成的一张虚拟表
创建视图:create ALGORITHM = merge view 视图名 as select语句;
特点(注意)
1.视图名字不能和已有的表名重复
2.视图可以用来进行权限控制
3.视图和原表存在数据同步
视图的调用:就和普通表的操作一样 select * from 视图名
删除视图:drop view 视图名
视图可以简化我们的查询
select user.,power.power_name from user LEFT JOIN power on user.power_id = power.id
create ALGORITHM = MERGE view user_power as select user.,power.power_name from user LEFT JOIN power on user.power_id = power.id
视图的类型：
merge 会将引用视图的语句的文本与视图定义合并起来，是的视图定义的某一部分取代语句的对应部分，效率较高，推荐使用
temptable 视图的结果将被置于临时表中，然后使用它执行语句，方便子查询使用，实用性较低
undefined Mysql将选择所用使用的算法，如果可能的话，它将倾向于merge，因为merge更加有效，如果使用了临时表，视图是不可更新的
存储过程：
就是一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用于通过指定的存储过程名字来调用并且执行它。
创建：
create procedure 方法名()
begin
里面是你的方法体里面的sql语句集
end
CREATE PROCEDURE add_user()
BEGIN
insert into user(name,pwd,power_id) VALUES(‘高榜群’,‘123456’,1);
insert into user(name,pwd,power_id) VALUES(‘王志琼’,‘654321’,2);
end
存储过程的调用 call 方法名()
删除 drop procedure 方法名()
CREATE PROCEDURE add_user(in in_name char(20),in in_pwd char(60),in in_power_id int,out insert_id int)
BEGIN
insert into user(name,pwd,power_id) VALUES(in_name,in_pwd,in_power_id);
set insert_id = LAST_INSERT_ID();# LAST_INSERT_ID()表示获取最新插入的id
end
call add_user(‘何鑫’,‘444444’,2,@insert_uid);
select @insert_uid as t
触发器:
就是个特殊的存储过程，它的执行不需要人为调用，它是由数据库操作的事件引发的
语法
create trigger 名字
after/before insert/update/delete on 表名
for each row
begin
sql语句
end;
create TRIGGER keng
after insert on user
for each ROW
BEGIN
update power set power_name = ‘坑爹的触发器’ where id = 1;
end;
drop trigger keng;
触发器的优缺点：
优点：触发器它能回滚，就是触发器发生错误，能回滚此次操作
缺点：维护困难，给阅读和修改带来一定的麻烦
索引：其实就是相当于一本书的目录，能大大地加快查询速度，索引优化和查询优化都是相辅相成的。
索引的本质其实就是根据某字段或者字段组合排好顺序的一个文字
索引类型：
primary(PK):主键，在所有的索引当中速度是最快的，但是每个表最多只能有一个。
Unique：唯一索引，速度仅次于主键，但是它所涉及到的字段或者字段组合的值必须是唯一的，一个表中可以存在多个
normal(index):普通索引，速度在前三者中最慢，但是他没有任何限制
Full Text：全文索引，主要用于模糊查询。但是记住，这个的话表类型必须为myisam(engine = myisam),涉及到的字段必须是字符型
索引特点：
1.在查询语句中，涉及到索引字段的话就会自动使用索引
2.索引字段是字符的话，根据首字母排序，如果首字母相同，则第二个字母
asd dss ads edd 排序前
ads asd dss edd 排序后
3.每个语句最多使用一个索引，我们把能排除更多不符合数据的索引放前面
select * from user where name=“admin” and pwd = “312321”;选择name索引
select * from user where pwd = “312321” and name=“admin”;选择pwd索引
已知：我为name和pwd分别创建了索引，然后问那条语句速度更快？
因为name索引的话，通过name="admin"能排除更多不符合的数据，所以name索引要快
4.建立索引组合时，建议把能排除更多不符合数据的字段放在前面，那样重复率就更低
5.order by,group by 都会使用索引
6.不要为每个字段都创建索引
7.索引设计字段的值重复率越高，索引效率越低
group by 分组查询 一般用作查询数量
select count(),power_id from user group by power_id;
power_id count()
1 6
2 7
create table user(
id int …
)engine = myisam default charset=utf8;
engine就是规定表的存储引擎，存储引擎就是如何存储数据，如何为存储的数据建立索引和如何更新和查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以成为表类型
myisam:mysql的默认存储引擎，每个myisam的表在磁盘上都存储为三个文件，都是以表名作为文件名。
.frm 存储表定义
.myd mydata存储数据
.myi myindex存储索引
非事务表，支持全文搜索，搜索速度快，但是写入速度慢，不支持外键，一般用在查询频繁的数据表
innodb:具有了提交，回滚等事务操作，支持外键，写入速度快，搜索速度慢，多用于数据安全性要求高的数据表，不支持全文搜索
the ‘innodb’ feature is disabled;…
在my.ini就是数据库的配置文件 appserv
找到skip-innodb 把前面的#去掉 然后重启服务
事务：
事务就是在执行sql时，要做到“要么不做，要么全做！”只能对innodb表进行操作
转账：
高榜群有4000 王志琼有1000
高榜群给王志琼转5000
4000-5000 错误
1000+5000 正确的
因为上面有个操作是错误的 我们就需要把这次的整个操作都取消，只有当上面都正确的时候，我才真正的提交
$sql1 = "begin;"
mysql_query($sql1);
$sql2 = "update money_innodb set money=money-5000 where id=1";
if(mysql_query($sql2)){
$sql3 = "update money_innodb set money=money+5000 where id=2";
if(mysql_query($sql3)){
mysql_query("commit");
echo "修改成功";
}else{
mysql_query("rollback");
echo "修改失败";
}
}
begin 就是开启事务
commit 提交事务
rollback 回滚事务
外键：
外键就是描述两个表字段之间的关系。
1.两个表的引擎都必须是innodb
2.两个关联字段的类型和长度等都必须一样
查询优化：
1.任何使用到where的sql语句，都最好涉及到索引。最好是主键索引，其次是唯一，其次是普通
2.注意查询语句中一些关键字的使用 = and 优于 < > or like in 优于 !=,not in,not like 越到后面查询时涉及到的行数越多
3.尽量避免使用join关联查询，group by等关键词
4.在复杂的查询条件中，查询语句的速度取决于速度最慢的一条
5.涉及到group by,order by的字段必须设置索引
6.where条件，能排除更多不符合条件数据的条件放在前面
select * from user where name = ‘123’ and pwd = ‘321’;
7.越简单的语句通常效率越高，我们宁愿多写几条简单的sql语句，也不把一条sql语句写的很复杂。
8.尽量不要使用rand(),curdata()等数据库自带的函数
9.对于涉及行数过多的sql语句，可以使用limit来限制行数，因为行数越少，查询越快
10.能使用php实现的效果，就不用mysql去做
order by rand();随机排序 array_rand()
11.select * 所查即所得，就是我需要什么字段就查询什么字段，除非都需要才使用*
explain :分析查询语句的效率
explain select * from user where id = 1;
select_type：查询类型
SIMPLE：最简单的sql语句，最常见。大部分有效率的语句，类型都是SIMPLE
PRIMARY：查询的数据表是一个动态表，数据表是有一条sql语句生成的结果集构建出的临时表，该表无法使用索引，如select * from (select * from news)n，通过n得到结果集来查询，那n就是动态表
UNION：使用union关键字，用于合并两个或多个SELECT 语句的结果集(一般少用)
SUBQUERY：子句查询，条件使用动态结果集，如explain select * from t0 where id = (select id from t0 where id = 90 )
DEPENDENT SUBQUERY：字句查询，关键字使用in
type：查询类型，包括语句使用索引的情况
system：理论上最快的，使用了主键，全表只有1行
const：在实际中最快的类型，使用了主键索引，而且返回的行数只有1行
eq_ref：使用了唯一索引，而且返回1行
ref：使用了普通索引，返回少数的行（where条件使用的是= and 等关键字）,但是最终还要看rows的数量
ref_or_null：同上，但是字段允许为空
unique_subquery：字句查询中使用了索引，通过与DEPENDENT SUBQUERY等搜索一起出来
range：范围搜索，使用了索引，返回结果集是一个范围（在where字句中使用> < in等关键字），在返回行数不高的前提下，可以使用
index：基本同下，但是或许有用到索引，不过索引使用的地方不在where条件中，如果他不是一个全表搜索，那么可以接受explain select * from t0 order by id asc limit 10000使用了index，但只有1w行，可以接受
all：全表搜索，并且没有用到索引
key与possible_keys
key：sql语句使用的索引
possible_keys：sql语句可能或可以使用的索引->若存在可能使用的索引，而且其他的索引比现在所使用索引更有效率，可以通过FORCE INDEX(索引名)强制使用索引
key_len：索引涉及字段（组合）每行的长度（大小）（例如字段是int，长度就是4）
rows：sql语句查询的行数，该数字越少表示语句越有效率
extra：在查询中额外使用的工具或状态（有好有不好）
Using index：仅使用索引完成，没有使用实体表，效率极高。注意下面语句select中字段的写法
explain select id from t0 where id < 1000
Using where：使用条件（where，limit）限制了返回条数
Using filesort：必须优化，表示在查询中使用数据库的排序工具，效率极差。通过出现在排序的字段没有使用索引
Using temporary ：必须优化，语句运行使用了临时表，通常出现在关联查询的排序中，并且很多时候与Using filesort一起出现。有时会出现在group by中
扫一扫
9207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
