数据库基础-2

1 数据完整性

数据库基础-1: 点击访问.

1.1 数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

 - 实体完整性(行完整性):
 - 域完整性(列完整性):
 - 引用完整性(关联表完整性):

1.2 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：

• 主键约束（primary key）
• 唯一约束(unique)*
• 自动增长列(auto_increment)*

1.2.1 主键约束（primary key）

注：每个表中要有一个主键，但一张表的主键约束只能有1个
特点：数据唯一，且不能为null
主键约束，主键字段中的数据不能为NULL，也不能重复。
主键相关的术语？

	- 主键约束 : primary key
	- 主键字段 : id字段添加primary key之后，id叫做主键字段
	- 主键值 : id字段中的每一个值都是主键值。

主键有什么作用？

 - 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
 - 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类？

1：根据主键字段的字段数量来划分：
	 - 单一主键（推荐的，常用的。）
	 - 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
	 - 
2：根据主键性质来划分：
	- 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
	- 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）
				最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要        
				，随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。           
	

第一种添加方式：在列名后面直接添加：

	--stuId作为主键
	CREATE TABLE student( id int primary key, stuId varchar(50) );     

第二种添加方式：此种方式优势在于，可以创建联合主键

	CREATE TABLE student
	( 
		id int, name varchar(50),
		primary key(id)
	); 
	
	--可以创建联合主键
	CREATE TABLE student
	(
		classid int, stuid int, name varchar(50),
		primary key(classid,stuid)
	);

第三种添加方式：在创建好表之后添加

	CREATE TABLE student
	( 
		id int, name varchar(50) 
	); 
	--在创建表之后对表的列进行添加主键
	ALTER TABLE student ADD PRIMARY KEY (id);

1.2.2 唯一约束(unique)

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

	create table t_user(
		id int,
		username varchar(255) unique  // 唯一约束
	);

1.2.3 自动增长列(auto_increment)

自增是给主键添加自动增长的数值，列只能是整数类型

	create table t_user(
		id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。    
		username varchar(255)
	);
	--用了自增，插入数据时，就不需要自己赋值
	insert into t_user(username) values('a');
	insert into t_user(username) values('b');
	insert into t_user(username) values('c');

1.3 域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)

1.3.1 数据类型

只选取了常见的数据类型：
字符型：

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYTEXT	0-255字节	短文本字符串
TEXT	0-65 535字节	长文本数据

数值类型

类型	大小	范围（有符号）	范围（无符号 ）	用途
tinyint	1 字节	(-128，127)	(0，255)	小整数值
smallint	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值

日期类型:

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值

1.3.2 非空约束

非空约束就是在字段的后面加上：not null

CREATE TABLE student
( 
	Id int primary key, 
	Name varchar(50) not null, --表示name字段一定要有值，不能为空
	Sex varchar(10) --sex字段，就可以为空，因为没有任务约束
); 

1.3.3 默认值约束

默认值约束：就是使用default，可以指定默认指定的值

CREATE TABLE stud
( 
	Id int primary key, Name varchar(50) not null,
	Sex varchar(10) default '男' 
);

insert stud values(1,'tom','女'); --也可以自己写
insert stud values(2,'jerry',default); --自己也可以直接使用defalut       
insert stud values(3,'jerry',DEFAULT);

1.4 引用完整性（外键约束：FOREIGN KEY）

关于外键约束的相关术语：

	 - 外键约束: foreign key
	 - 外键字段：添加有外键约束的字段
	 - 外键值：外键字段中的每一个值

业务背景：

		请设计数据库表，用来维护学生和班级的信息？
		分析：多个学生是对应一个班级的，一个班级里可以有多个学生，
			 可以说明是典型的一对多的关系，所有需要建立两张表
			 表关系：在多的表中建立一个外键，引用另一张表的主键

sql设计

		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)--引用班级表的主键                           
		);	

2 索引

2.1 索引的作用

索引的作用：

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
		在数据库方面，查询一张表的时候有两种检索方式：
			第一种方式：全表扫描
			第二种方式：根据索引检索（效率很高）
		索引为什么可以提高检索效率呢？
			其实最根本的原理是缩小了扫描的范围。

什么时候考虑给字段添加索引？（满足什么条件）

	* 数据量庞大。（根据客户的需求，根据线上的环境）
	* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
	* 该字段经常出现在where子句中。（经常根据哪个字段查询）

但注意：

		索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中   
		的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改  
		这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。   
		

2.2 创建索引

		创建索引对象：
			create index 索引名称 on 表名(字段名);
		
		删除索引对象：
			drop index 索引名称 on 表名;

2.3 索引的实现原理

简单的描述：

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

 - 通过B+Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
   最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

索引什么时候失效？

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

3 数据库的三大范式和事务

请点击访问：三大范式和事务介绍

4 SQL 优化

	1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引              
	2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，  
		如：select id from stu where num is null
		提示：最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
	3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
	4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描    
		如：select id from t where num=10 or Name = 'admin'
		可以这样查询：
				select id from t where num = 10 union all select id from t where Name = 'admin'
	5.in 和 not in 也要慎用，否则会导致全表扫描	            

5 拓展（如何修改数据库密码）

5.1 方法一

1.打开cmd进入MySQL的bin目录；（如我的路径是D:\MySQL\mysql-5.7.24-winx64\bin）

2.通过输入命令 mysql -u root -p 指定root用户登录MySQL，输入后回车会提示输入密码，输入我们原来的密码然后回车。

3.修改MySQL的root用户密码，格式：mysql> set password for 用户名@localhost = password(‘新密码’);
例如（上面例子将用户root的密码更改为root）：mysql> set password for root@localhost = password(‘root’);

4.退出mysql重新登录，输入新密码root登录就可以了；

5.2 方法二

1.打开cmd进入MySQL的bin目录

2.修改MySQL的root用户密码格式：mysqladmin -u用户名 -p旧密码； password 新密码
例如（第一种方法中我将密码改成了root，这里再改回123）：
mysqladmin -uroot -proot password 123（这里一定要注意-uroot和 -proot是整体，不要写成-u root -p root）

重新登录，输入新密码123就ok了；

最后，如果有问题，希望指正，一起进步。