1.约束(Constraint)
1.1 基础概念
约束：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见约束：
| 约束 | 概念 |
|---|---|
| 非空约束(not null) | 约束的字段不能为NULL |
| 唯一约束(unique) | 约束的字段不能重复 |
| 主键约束(primary key) | 约束的字段即不能为NULL，也不能重复(简称PK) |
| 外键约束(foreign key) | 简称FK |
| 检查约束(check) | Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束 |
1.2 非空约束
建表：
create table t_user (
id int,
username varchar (255) not null,
password varchar (255)
);
插入：
insert into t _user(id,password) values(1,'123 ');
报错：ERROR 1364 (HY000): Field ‘username’ doesn’t have a default value
正确插入：
insert into t_user(id,username ,password) values(1, 'lisi' , '123' ) ;
1.3 唯一性约束(unique)
唯一性约束修饰的字段具有唯一性，不能重复，但可以为NULL
案例：
drop table if exists t_user;
create table t_user (
id int,
username varchar (255) unique//列级约束
) ;
insert into t_user values (1, ' zhangsan ' ) ;
insert into t_user values (2 , ' zhangsan ' ) ;
报错：ERROR 1062 (23000): Duplicate entry ‘zhangsan’ for key 'username ’
下面可以：
insert into t_user (id)values (2);
insert into t_user (id)values (3) ;
insert into t_user (id)values (4);
案例：给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user (
id int,
usercode varchar (255),
username varchar (255) ,
unique (usercode , username)//多个字段联合起来添加1个约束unique【表级约束】
) ;
insert into t_user values (1,'111','zs');
insert into t_user values (2 ,'111',' ls');
insert into t_user values (3,'222','zs');
select * from t_user;
insert into t_user values (3,'111','zs');
报错：ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'
另外一个情况：
drop table if exists t_user;
create table t _user (
id int,
usercode varchar (255) unique,
username varchar (255) unique
) ;
insert into t_user values (1,'111','zs ');
insert into t_user values (2,'111','ls ');
报错：ERROR 1062 (23000) : Duplicate entry '111' for key 'usercode '
注意：not null 约束只有列级约束，没有表级约束
1.4 主键约束
drop table if exists t _user;
create table t_user (
id int primary key ,//列级约束
username varchar (255),
email varchar(255)
) ;
insert into t_user(id,username ,email) values(1, 'zs', ' zs@123.com' ) ;
insert into t_user(id,username , email) values(2, '1s', ' 1s@123.com' ) ;
insert into t_user(id, username , email) values(3 , 'ww', ' ww@123.com ' ) ;
select * from t _user;
insert into t_user(id,username , email) values(1 , ' jack', ' jack@123.com' ) ;
报错：ERROR 1062 (23000) : Duplicate entry '1' for key 'PRIMARY
insert into t_user(username , email) values ( 'jack' , ' jack@123.com' ) ;
报错：ERROR 1364 (HY000): rield 'id' doesn't have a default value
根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，也不能重复
主键特点：不能为NULL，也不能重复
主键相关术语：
主键约束: primary key
主键字段: id字段添加primary key之后,id叫做主键字段
主键值: id字段中的每一个值都是主键值。
表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
主键作用：主键值是这行记录在这张表当中的唯一标识（就想一个人的身份证号码一样）
主键分类：
根据主键字段的字段数量来划分：
| 主键 | 分类 |
|---|---|
| 单一主键 | 推荐的，常用的 |
| 复合主键 | 多个字段联合起来添加一个主键约束【复合主键不建议使用，因为复合主键违背三范式】 |
根据主键性质来划分：
| 主键 | 分类 |
|---|---|
| 自然主键 | 主键值最好就是一个和业务没有任何关系的自然数。【推荐的】 |
| 业务主键 | 主键值和系统的业务挂钩，例如:拿着银行卡的卡号做主键，拿着身份证号码作为主键。(不推荐用)最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。 |
注意：一张表的主键约束只能有1个
使用表级约束方式定义主键：
drop table if exists t_user;
create table t_user(
id int,
username varchar (255),
primary key (id)
);
insert into t_user(id, username) values(1,'zs') ;
insert into t_user(id,username) values(2,'ls');
insert into t_user(id, username) values(3,'ws') ;
insert into t_user(id,username) values (4,'cs') ;
select * from t_user;
insert into t_user(id,username) values(4 , 'cx');
报错：ERROR 1062 (23000) : Duplicate entry '4' for key 'PRIMARY'
复合主键：
drop table if exists t_user;
create table t_user (
id int,
username varchar (255),
password varchar (255),
primary key (id,username)
);
主键值自增:auto_increment：
drop table if exists t_user;
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') ;
insert into t_user(username) values( 'd');
insert into t_user(username) values ( 'e');
insert into t_user(username) values ('f');
select * from t_user;
1.5 外键约束 foreign key
相关术语：
| 术语 | 概念 |
|---|---|
| 外键约束 | foreign key |
| 外键字段 | 添加有外键约束的字段 |
| 外键值 | 外键字段中的每一个值 |
业务背景：设计数据库表，用来维护学生和班级的信息
第一种方法：一张表存储所有数据
缺点：冗余
第二种：两张表（班级表和学生表）
建表语句为：
t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。
顺序要求:
- 删除数据的时候,先删除子表,再删除父表。
- 添加数据的时候,先添加父表,在添加子表。
- 创建表的时候,先创建父表,再创建子表。
- 删除表的时候,先删除子表,在删除父表。
#先删除子表再删除父表
drop table if exists t_student;
drop table if exists 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)//此时写入的classno必须是父表中有的数据
) ;
#给父表插入数据
insert into t class values (101,' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ) ;
insert into t_class values (102, ' yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy' ) ;
#给子表插入数据
insert into t_student values (1,'zs1',101);
insert into t_student values (2,'zs2',101);
insert into t_student values (3,'zs3',102);
insert into t_student values (4,'zs4',102) ;
insert into t_student values (5,'zs5',102) ;
insert into t_student values (6,'zs6',102);
##查询表格
select *from t_class;
select *from t_student;
注意：
- 外键可以为NULL
- 被引用的字段不一定是主键，但至少具有unique约束
2.存储引擎
2.1 基础概念
完整的建表语句：
CREATE TABLE `t_x` (
`id` int (11) DEFAULT NULL
)ENGINE=InnoDB DEFAUIT CHARSET=utf8;
注意:在MySQL当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。
- 建表的时候可以指定存储引擎，也可以指定字符集
- mysql默认使用的存储引擎是InnoDB，默认采用的字符集是UTF-8
- 存储引擎这个名字只有在mysql中存在（Oracle中有对应的机制，但是不叫做存储引擎，就叫表的存储方式）
查看当前mysql支持的存储引擎 : show engines \G
2.2 常见分类
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎
2.2.1 MyISAM存储引擎
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: No
XA: NO
Savepoints: No
- MyISAM这种存储引擎不支持事务
- MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的
- MyISAM采用三个文件组织一张表：
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点：可被压缩，节省存储空间，并且可以转换为只读表，提高检索效率
缺点：不支持事务
2.2.2 InnoDB存储引擎
Engine: InnoDB
Support: DEFAULT
Comment: supports transactions,row-level locking,and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
- 表的结构存储在xxx.frm文件中
- 数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
- 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
- InnoDB支持级联删除和级联更新。
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
2.2.3 MEMORY存储引擎
Engine: MEMORY
Support: YES
Comment: Hash based,stored in memory, useful for temporary tables
Transactions: NO
XA: No
Savepoints: No
优点：查询速度最快
缺点：不支持事务，数据容易丢失【因为所有数据和索引都是存储在内存当中的】
以前叫HEPA引擎
3.事务(Transaction)
3.1 事务概述
一个事务是一个完整的业务逻辑单元，不可再分
比如：银行账户转账，从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语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的"事务机制”。
注意：
- 和事务相关的语句只有：DML语句【insert delete update】【因为这三个语句都是和数据库表当中的“数据”相关的、事务的存在是为了保证数据的完整性和安全性】
- 如果所有的业务都能使用1条DML语句搞定，就不需要事务了
- 实际情况下，通常一个“事（事务【业务】）”需要多条DML语句共同联合完成
3.2 事务原理
案例：假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete，这个事儿才算完成
开始：
开启事务机制(开始)
历史：
执行insert语句-->insert..(这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。)
执行update语句---> updat...(这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据)
执行delete语句---->delete..(这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据)
结束：
提交事务或者回滚事务〔结束)
注意：
- 提交事务【把历史操作同步到硬盘文件当中，硬盘文件一旦更改，历史操作全部清空】
- 回滚事务【历史操作全部清空，不和硬盘文件交互】
3.3 事务特性
事务包括四大特性：ACID
| 特性 | 概念 |
|---|---|
| A：原子性 | 事务是最小的工作单元，不可再分 |
| C：一致性 | 事务必须保证多条DML语句同时成功或者同时失败 |
| I：隔离性 | 事务A和事务B之间具有隔离 |
| D：持久性 | 最终数据必须持久化到硬盘文件中，事务才算成功的结束 |
3.4 事务的隔离性
事务隔离性存在隔离级别，理论上隔离级别包括4个：
| 隔离级别 | 概念 |
|---|---|
| 第一级别：读未提交(read uncommitted) | 对方事务还没有提交，当前事务可以读取到对方未提交的数据。读未提交存在脏读（Dirty Read）现象【表示读到了脏的数据】 |
| 第二级别：读已提交(read committed) | 对方事务提交之后的数据我方可以读取到。解决了脏读现象。出现问题：不可重复读【做不到事务从头到尾查到的数据是一样的】。 |
| 第三级别：可重复读(repeatable read) | 解决了不可重复读问题。出现问题：读取到的数据是幻想 |
| 第四级别：序列化读/串行化读 | 【两个事务并发，排队，一个事务没有结束之前，别的事务不能开启】解决了所有问题。但是效率低，需要事务排队。 |
注意：
- oracle数据库默认的隔离级别是：读已提交
- mysql数据库默认的隔离级别是：可重复读
3.4.1 演示事务
mysql事务默认情况下是自动提交的【只要执行任意一条DML语句则提交一次】
关闭自动提交：start transaction;
演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次
准备表：
drop table if exists t_user;
create table t_user (
id int primary key auto_increment,//主键，自增
username varchar (255)
);
mysql> insert into t_user(username) values ( 'zs');
Query oK,1 row affected (0.o3 sec)
mysq1> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+-—--+----------+
1 row in set (0.00 sec)
mysq1> rollback ;
Query OK,0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+-—--+----------+
1 row in set (0.00 sec)
演示：使用start transaction;关闭自动提交机制
mysql> start transaction;
Query OK,0 rows affected (0.00 sec)
mysql> insert into t_user(username) values ( 'lisi' );
Query OK,1 row affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
+-—--+----------+
2 rows in set (0.00 sec)
mysql> insert into t_user(username) values ( 'wangwu ' ) ;
Query OK,1 row affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 2 | lisi |
| 3 | wangwu |
+-—--+----------+
3 rows in set (0.00 sec)
mysql> rollback ;
Query OK,0 rows affected (0.02 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+-—--+----------+
1 row in set (0.00 sec)
3.4.2 演示 第一级别：读未提交
设置事务的全局隔离级别：set global transaction isolation level read uncommitted;
查看事务的全局隔离级别：select @@global.tx_isolation;
先用上面的方法设置全局隔离级别，再打开两个窗口：
右边没有提交但是在左边就能读取到了
3.4.3 演示 第二级别：读已提交
先设置级别：
再进行演示：
右边没有提交，所以左边读取不到
只有右边提交了，左边才读取到
3.4.4 演示 第三级别：可重复读
先设置级别：
再进行演示：
先将表删除，再提交，但是数据还在
只要这个事务没有结束，读取到的数据都是一样的
3.4.5 演示 第四级别：串行化读
先设置级别：
再进行演示：
左边的事务没有结束，右边的没法去查找
4.索引
4.1 基础概念
- 创建索引对象
create index 索引名称 on 表名（字段名）； - 删除所有对象
drop index 索引名称 on 表名； - 索引相当于一本书的目录，通过目录可以快速的找到对应的资源
- 在数据库方面，查询一张表的时候有两种检索方式：
第一种：全表扫描
第二种方式：根据索引检索（效率很高） - 索引为什么可以提高检索效率：最根本的原理是缩小了扫描的范围
- 索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的。比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
- 添加索引是给某一个字段，或者说某些字段添加索引
select ename ,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。
-
满足以下条件的时候考虑给字段添加索引
数据量庞大(根据客户的需求，根据线上的环境)
该字段很少的DML操作(因为字段进行修改操作，索引也需要维护)
该字段经常出现在where子句中(经常根据哪个字段查询) -
主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高，尽量根据主键检索 -
查看sql语句的执行计划
mysql> explain select ename ,sal from emp where sal = 5000;
+----+--------------+-------+------+---------------+-----+---------+-----+------+------------
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extral
+----+--------------+-------+------+---------------+-----+---------+-----+------+------------
1 | SIMPLE |emp | ALL | NULL |NULL | NULL | NULL| 14 | Using where
+----+--------------+-------+------+---------------+-----+---------+-----+------+------------
- 给上面sql语句中薪资sal字段添加索引
create index emp_sal_index on emp(sal);
然后再查看执行计划
mysql> explain select ename ,sal from emp where sal = 5000;
+----+--------------+-------+------+---------------+---------------+---------+------+------+------------
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extral
+----+--------------+-------+------+---------------+---------------+---------+------+------+------------
1 | SIMPLE |emp | ref | emp_sal_index |emp_sal_index | 9 | const| 11 | Using where
+----+--------------+-------+------+---------------+---------------+---------+------+------+------------
4.2 索引实现原理
索引底层采用的数据结构是：B+ Tree
总结：通过B-Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址= Ox3 ;
4.3 索引的分类
| 索引 | 概念 |
|---|---|
| 单一索引 | 给单个字段添加索引 |
| 复合索引 | 给多个字段联合起来添加1个索引 |
| 主键索引 | 主键上会自动添加索引 |
| 唯一索引 | 有unique约束的字段上会自动添加索引 |
4.4 索引失效
模糊查询的时候，第一个通配符使用的是%，这个时候索引失效，如：
select ename from emp where enaine like '%A%";
5.视图(view)
5.1 基础概念
-
视图：站在不同的角度去看待数据【同一张表的数据，通过不同的角度去看待】
-
创建视图：create view myview as select empno ,ename from emp;
-
删除视图：drop view myview ;
-
只有DQL语句才能以视图对象的方式创建出来
-
对视图进行增删改查，会影响到原表数据。(通过视图影响原表数据的，不是直接操作的原表)
-
可以对视图进行CRUD操作
5.2 具体操作
面向视图操作：mysql> select * from myview;
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;//通过视图删除原表数据。
5.3 视图的作用
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD.
6.数据库数据的导入和导出
导出：
导出整个库
在windows的dos命令窗口中执行:
mysqldump 数据库名字 > 存入地址 -u用户名 -p密码
导出指定表
在windows的dos命令窗口中执行:
mysqldump 数据库名字 表名 > 存入地址 -u用户名 -p密码
导入：
create database 数据库名;
use 数据库名 ;
source 路径
7.数据库设计三范式
设计范式：设计表的依据。按照这个三范式设计的表不会出现数据冗余
| 范式 | 概念 |
|---|---|
| 第一范式 | 任何一张表都应该有主键，并且每一个字段原子性不可再分 |
| 第二范式 | 建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖 |
| 第三范式 | 建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖 |
注意：
- 二范：多对多情况下，建立三张表,关系表两个外键。如下图：
- 三范：一对多，两张表，多的表加外键
总结：在实际的开发中，以满足客户的需求为主,有的时候会拿冗余换执行速度。
8.表的设计经典设计方案
一对一设计有两种方案：
- 主键共享
- 外键唯一
扫一扫
4201
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
