数据库学习笔记-3

数据库学习笔记-3

一、约束

1.唯一性约束

unique

唯一性约束修饰的字段具有唯一性，不能重复。但可以为null

/*列级约束*/
create table t_user(
    id int,
username varchar(255) unique
);

多字段添加 （表级约束，约束的字段不能都相同，但可以部分相同）：

create table t_user(
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username)  
);

not null约束只有列级约束，没有表级约束

---

2.主键约束

主键约束：不能为null也不能重复，一张表的主键约束只能有1个

/*列级约束*/
create table t_user(
	id int primary key,
);
/*表级约束*/
create table t_user(
    id int,
	primary key(id)
);

最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候，主键也可能需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键重复

主键值自增：

create table t_user(
    /*id字段自动维护一个自增的数字，从1开始，以1递增*/
    id int primary key auto_increment,
	username varchar(255)
);

insert into t_user(username) values('a'); 
insert into t_user(username) values('b');
insert into t_user(username) values('c');

Oracle当中提供的自增机制为序列(sequence)对象

---

3.外键约束

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表
删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，再添加子表
创建表的时候，先创建父表，再创建子表
删除表的时候，先删除子表，再删除父表

create table t_class(
	cno int,
    cname varchar(255),
    primary key(cno)
);

create table t_student(
	sno int,
	sname varchar(255),
	classno int,
	primary key(sno),
	foreign key(classno) references t_class(cno)
);

外键可以为null

外键字段引用其他表的某个字段的时候，被引用的字段不一定是主键，但至少是具有unique约束，具有唯一性，不可重复

---

二、存储引擎

存储引擎：表的存储方式

/* 建表的时候可以指定存储引擎，也可以指定字符集*/
CREATE TABLE `t_x` (
	`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

show engines \G查看存储引擎列表

---

1.MyISAM

MyISAM采用三个文件组织一个表：
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点:不支持事务。

---

2.InnoDB

表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法转换成只读
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制
InnoDB支持级联删除和级联更新（例如父表发生变动同步修改在子表上）

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障

---

3.MEMORY(HEPA)

优点：查询速度最快

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的

---

三、事务(Transaction)

一个事务是一个完整的业务逻辑单元，不可再分。事务的存在是为了保证数据的完整性，安全性

例如从A账户向B账户转账10000元，需要执行两条update语句：

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';       

想要保证以上的两条DML(insert delete update)语句同时成功或者同时失败，那么就要使用数据库的"事务机制"

事务包括四大特性：ACID
A:原子性：事务是最小的工作单元，不可再分
B:一致性：事务必须保证多条DML语句同时成功或者同时失败
C:隔离性：事务A与事务B之间具有隔离
D:持久性：持久性说的是最终数据必须持久化到硬盘中，事务才算成功结束

---

1.隔离级别

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交(read uncommitted)

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据，存在脏读(Dirty Read) 现象读到脏数据**（数据在缓存或其它地方，一回滚断点就失去意义）**

第二级别：读已提交(read committed)

对方事务提交之后的数据我方可以读取到，不可重复读**（当前事务未结束，其它事务进行的修改直接作用在当前数据库上，导致每次读到的内容都不一样）**

第三级别：可重复读(repeatable read)

解决了不可重复读问题，存在的问题是读取到幻象（只是数据库的备份）

第四级别：序列化读/串行化读

解决了所有问题但效率低，需要事务排队

---

2.示例

mysql中的事务是支持自动提交的，只要执行一条DML语句，则提交一次

关闭默认提交：start transaction;

关闭后rollback可回滚到最近的一次commit之后

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;

---

四、索引

索引底层采用的数据结构是：B + Tree

查询一张表的时候有两种检索方式：
1.全表扫描
2.根据索引检索（缩小了扫描的范围）

数据一旦修改，索引需要重新排序，进行维护，适用于DML操作少的字段

create index 索引名称 on 表名(字段名);
create index emp_sal_index on emp(sal);
drop index 索引名称 on 表名;
drop index emp_sal_index on emp(sal);

主键具有unique约束的字段会自动添加索引

查看sql语句的执行计划(explain)：

explain select ename,sal from emp where sal = 5000;

/*模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的*/
select ename from emp where ename like ' %A% ';

---

五、视图(view)

create view myview as select empno,ename from emp;
select * from myview;
drop view myview;

只有DQL语句才能以视图对象的方式创建出来，对视图进行增删改查，会影响到原表数据(通过视图影响原表数据，不是直接操作的原表)

create table emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp_bak;
/*通过视图修改原表数据*/
update myview1 set ename = 'hehe',sal = 1 where empno 7369;  
/*通过视图删除原表数据*/
delete from myview1 where empno = 7369;  

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

---

六、数据库设计三范式

---

第一范式

任何一张表都应该有主键，并且每一个字段原子性不可再分

---

第二范式

建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部份依赖**（复合主键）**

多对多关系使用三张表，关系表+两个含有外键表

	    t_student 学生表
	    sno(pk)       sname
	    ---------------------
	     1             张三
	     2             李四 
	     3             王五

	     t_teacher 讲师表
	     tno(pk)    tname
	     ----------------------
	      1			王老师
	      2			张老师
	      3			李老师

	      t_student_teacher_relation 学生讲师关系表
	      id(pk)        sno(fk)          tno(fk)
	      -------------------------------------------
	       1	           1                 3
	       2	           1			     1
	       3	           2			     2
	       4	           2			     3
	       5	           3			     1
	       6	           3			     3

---

第三范式

建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖**（字段A依赖字段B，字段B依赖主键，这样B就相当于A的主键了，要单建一张表）**

一对多关系使用两张表，关系中多的表加外键

	    班级 t_class
	    cno(pk)          cname
	    --------------------------
	      1              班级1
	      2              班级2

	    学生 t_student
	    sno(pk)  	sname   classno(fk)
	    --------------------------------------------
	     101	     张1	      1
	     102         张2	      2
	     103	     张3	      2
	     104	     张4	      1
	     105	     张5	      2

---

一对一设计

1.主键共享

t_user_login 用户登陆表
id(pk)       username        password
----------------------------------------
1             zs                123
2             ls                456

t_user_detail 用户详细信息表
id(pk+fk)    realname            tel          ...
----------------------------------------------------
1             张三            11111111112234
2	          李四            12112523432412

2.外键唯一

t_user_login 用户登陆表
id(pk)       username        password
----------------------------------------
1             zs                123
2             ls                456
     
t_user_detail 用户详细信息表
id(pk)         realname          tel         userid(fk+unique)      
----------------------------------------------------
1                张三         111111114            2
2	             李四         121432412            1