My SQL 基础知识学习篇（二）

MySQL列属性

#MySQL列属性
/null、not null、default、primary key、auto_increment、comment/
MySQL真正约束字段的是数据类型,但是数据类型的约束太单一,需要有一些额外的约束,来更加保证数据的合法性.
MySQL常用列属性有：null、not null、default、primary key、auto_increment、comment.

/空属性: null和not null/
空属性: null(空,默认) 和 not null(不为空). mysql数据库默认字段都是为null的,实际开发过程中尽可能保证所有的数据都不应该为null,空数据没有意义.
例如: create table test(
a int not null,
b int
);
insert into test (a,b) values(10,null);
insert into test (a,b) values(null,10);–报错

/默认值: default/
default: 自定义默认值属性,通常配合not null一起使用.
例如: create table test1(
a int not null default 200,
b int
);
insert into test1(b) values(20);–或 insert into test1(a,b) values(default,20);

/主键|唯一索引/
Mysql中提供了多种索引? (下文索引更多解析)
1.主键索引:primary key
2.唯一索引:unique key
3.全文索引:fulltext index
4.普通索引:key 或 index

主键:primary key 一张表中只能有一个字段可以使用对应的主键,用来唯一的约束该字段里面的数据,不能重复和不能为null.
设置主键有两种方式：
(1)在定义一个字段的时候直接在后面进行设置primary key
例如: create table test2(
id int(10) unsigned not null primary key,
name char(20) not null default ‘’
);

(2)定义完字段后再定义主键
例如: create table test3(
id int(10) unsigned not null,
name char(20) not null default ‘’,
primary key (id)
);

唯一键:unique key 解决表中多个字段需要唯一性约束的问题.
例如:create table test4(
id int(10) unsigned not null,
name char(20) not null default ‘’,
goods varchar(100) not null default ‘’,
primary key (id),
unique key (name,goods)
);

/自动增长: auto_increment/
自增长属性:每次插入记录的时候,自动的为某个字段的值加1(基于上一个记录). 通常跟主键搭配.
自增长规则：(1)任何一个字段要做自增长前提必须是一个索引 (2)自增长字段必须是整型数字
例如: create table test5(
id int(10) unsigned not null auto_increment,
name char(20) not null default ‘’,
primary key (id)
);

/列描述 comment/
列描述(注释):comment 与其他的注释符不同之处在于,这里的注释内容属于列定义的一部分.
例如:create table user(
id int(10) unsigned not null auto_increment comment ‘id’,
name char(20) not null default ‘’ comment ‘名字’,
primary key (id)
)engine=InnoDB default charset=utf8 comment=‘用户表’;

索引

#索引
/索引的概述和优缺点和种类/
什么是索引？
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.
类比理解:数据库中的索引相当于书籍目录一样,能加快数据库的查询速度.
没有索引的情况,数据库会遍历全部数据后选择符合条件的选项.
创建相应的索引,数据库会直接在索引中查找符合条件的选项.

索引的性质分类：
索引分为聚集索引和非聚集索引两种,聚集索引是索引中键值的逻辑顺序决定了表中相应行的物理顺序,而非聚集索引是不一样;
聚集索引能提高多行检索的速度,而非聚集索引对于单行的检索很快.

索引的优点：
(1)加快数据检索速度 (创建索引主要原因)
(2)创建唯一性索引,保证数据库表中每一行数据的唯一性
(3)加速表和表之间的连接
(4)使用分组和排序子句对数据检索时,减少检索时间
(5)使用索引在查询的过程中,使用优化隐藏器,提高系统的性能

索引的缺点：
(1)创建索引和维护索引要耗费时间,时间随着数据量的增加而增加
(2)索引需要占用物理空间和数据空间
(3)表中的数据操作插入、删除、修改, 维护数据速度下降

索引种类
(1)普通索引: 仅加速查询
(2)唯一索引: 加速查询 + 列值唯一（可以有null）
(3)主键索引: 加速查询 + 列值唯一（不可以有null）+ 表中只有一个
(4)组合索引: 多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
(5)全文索引: 对文本的内容进行分词,进行搜索 (注意:目前仅有MyISAM引擎支持)

/创建表时直接指定索引/
–创建主键索引
例如:create table student(
id int(10) unsigned not null auto_increment comment ‘id主键索引’,
name char(20) not null default ‘’ comment ‘名字’,
class varchar(50) not null default ‘’ comment ‘班级’,
seat_number smallint(5) not null default 0 comment ‘座位编号’,
primary key (id)
)engine=InnoDB default charset=utf8 comment=‘学生表’;

/创建索引/
create [unique唯一索引][clustered聚集索引] index <索引名>
on <表名>(<列名称>[<排序>],<列名称>[<排序>]…);

语法解析:其中unique和clustered为可选项. 注意:基本表上最多仅仅能建立一个聚集索引.
“列名称”:规定需要索引的列. “排序”:可选asc(升序)和desc(降序)缺省值为asc.

–创建唯一索引(unique key 简写 unique)
例如:create unique index number on student(seat_number desc);

–创建普通索引
例如:create index name_class on student(name asc,class desc);

/删除索引/
drop index <索引名> on 表名;
例如:drop index number on student;

外键

/*外键约束: foreign key */
外键约束: foreign key 被约束的表叫做副表,外键设置在副表上面,外键引用主键字段所在的表叫做主表. (作用:约束两张表的数据)
外键定义语法: constraint 外键约束名称 foreign key(外键字段) references 主表名称(引用字段)
外键数据操作：
1.当有外键约束之后,添加数据的时候,先添加主表数据,再添加副表数据
2.当有了外键约束,修改数据的时候,先改副表的数据,在修改主表的数据
3.当有了外键约束,删除数据的时候,也是先删除副表的数据,再删除主表的数据
复制代码

外键注意事项：
1.外键约束只有InnoDB存储引擎才支持.
2.实际项目中往往会用到外键的设计思想,但往往不会真正的从语法上进行外键约束. 因为外键约束的级联操作可能会带来一些现实的逻辑问题. 另外使用外键会较低mysql的效率.

储存引擎

储存引擎: engine/
常见的引擎：Myisam InnoDB BDB Memory Archive 等

不同的引擎在保存表的结构和数据时采用不同的方式?
MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
InnoDB表文件含义：.frm表定义，表数据空间文件(存放表的数据和索引)和日志文件
注意:项目中一般用InnoDB引擎.

MySQL中innodb与myisam的区别
(1):InnoDB支持事物,MyISAM不支持事物
(2):InnoDB支持行级锁,MyISAM支持表级锁
(3):InnoDB支持快照数据(MVCC),MyISAM不支持
(4):InnoDB支持外键,MyISAM不支持
(5):InnoDB不支持全文索引,MyISAM支持

innodb使用的是行级锁:只有在增删改查时匹配的条件字段带有索引时,innodb才会使用行级锁.(行级锁:要为匹配条件字段加索引)
innodb使用的是表级锁:如果增删改查时匹配的条件字段不带有索引时,innodb使用的将是表级锁.(表级锁:数据库全表查询,需要将整张表加锁,保证查询匹配的正确性)
触发读锁:就是用select命令时触发读锁.
触发写锁:就是在你使用update,delete,insert时触发写锁,并且使用rollback或commit后解除本次锁定
更好的区分:读写锁和表级锁行级锁. 将读写锁叫做权限锁(决定了加锁后用户有哪些操作权限),将表级锁行级锁叫做对象锁(决定将锁加在某一行还是整张表)

MVCC多版本并发控制,也可称之为一致性非锁定读;它通过行的多版本控制方式来读取当前执行时间数据库中的行数据. 实质上使用的是快照数据.
为什么要使用MVCC?
消除锁的开销. (如果要保证数据的一致性,最简单的方式就是对操作数据进行加锁,但是加锁不可避免的会有锁开销. 所以使用MVCC能避免进行加锁的方式并提高并发.)

三范式

/数据库三范式/
–建表规范

• 每个表保存一个实体信息
• 每个具有一个ID字段作为主键
• ID主键 + 原子表

–第一范式
字段不能再分,确保每列保持原子性.

–第二范式
满足第一范式的基础上,不能有部分依赖.
消除符合组合主键(A,B)就可以避免部分依赖. 增加单列关键字.

–第三范式
满足第二范式的基础上,不能有传递依赖.
某个字段依赖于主键,而有其他字段依赖于该字段. 这就是传递依赖.
将一个实体信息的数据放在一个表内实现

今天暂时写这么多，好累哦。休息休息。