快速上手MySql && MySql GUI工具 SQLyog Community （2）

/*约束.约束是在表上强制执行的数据校验规则.约束主要用于保证数据库的完整性。.当表中数据有相互依赖性时，可以保护相关的数据不被删除。.大部分数据库保护以下五类完整性约束：NOT NULL、UNIQUE KEY唯一键、PRIMARY KEY 主键、FOREIGN KEY外键、CHECK检查。.约束作为数据库对象，存放在系统表中，也有自己名字。.创建约束时机：

BIG_GENERAL_DD

502人浏览 · 2017-06-14 16:03:14

BIG_GENERAL_DD · 2017-06-14 16:03:14 发布

（本节内容主要是DDL语言）

/*约束

.约束是在表上强制执行的数据校验规则
.约束主要用于保证数据库的完整性。
.当表中数据有相互依赖性时，可以保护相关的数据不被删除。
.大部分数据库保护以下五类完整性约束：
NOT NULL、UNIQUE KEY唯一键、PRIMARY KEY 主键、FOREIGN KEY外键、CHECK检查。

.约束作为数据库对象，存放在系统表中，也有自己名字。
.创建约束时机：
-在建表同时创建
-建表后创建（修改）

可定义列级或表级约束:
-有单列约束和多列约束
-非空约束（属于列级约束），保证字段不为空，

NULL值，所有数据都可以是NULL，空字符不等于NULL，0也不等于NULL。

唯一约束
.唯一性约束条件确保所在字段或者字段组合不出现重复值
.唯一性约束条件的字段允许出现多个NULL
.同一张表内可建多个唯一约束
.唯一约束可以由多列组合组成
.建唯一约束时MySql会为之建立对应的索引
.如果不给唯一约束起名，该唯一约束默认与列名相同。

主键约束
.主键从功能上看相当于非空且唯一
.作用：主键是表中唯一确定一行数据的字段
.主键字段可以是单字段或者多字段（联合组建）
.当建立主键约束时，MySql为主键创建对应的索引
.主键约束名是PRIMARY

外键约束
.构建于一个表的两个字段或者两个表的两个字段之间的关系
.确保了相关两个字段的关系
.子从表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）
.当主表的记录被子表参照时，主表记录不允许被删除
.外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录
.格式FOREIGN KEY（外键列名）REFERENCES 主表（参照列）

CHECK约束（在MySql中不起作用）
.既可以列级约束，也可以表级约束
.定义在字段上的每一条记录都要满足条件
.在check中定义检查的条件表达式，数据需要符合设置的条件
.条件表达式不允许使用
.-参照其他记录的值

/*
#数据冗余,解决办法：通过一个字段就可以查询到其他相关信息。
#当tb_emp里面的dept_id引用自tb_dept表，如果部门表里没有id为3的部门，这个字段值不能为3
#当tb_emp表里面有dept_id为3的数据，就不能删除tb_emp表里面id为3的记录，因为有关联引用
#外键参照的只能是主表tb_dept主键或唯一键*/

CREATE TABLE tb_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(18),
description VARCHAR(200)
);

#1.在建表同时创建约束
CREATE TABLE tb_emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18) NOT NULL,
sex VARCHAR(2) CHECK(sex='男' OR sex='女'),#检查约束在MySql中不起作用
age INT CHECK(age > 18 OR age <60),
address VARCHAR(200),
email VARCHAR(100) UNIQUE,
dept_id INT REFERENCES tb_dept(id)#外键约束不起作用
);

#CONSTRAINT FOREIGN KEY 外键名[表名+约束名](外键) REFERENCES 主表(字段)
CONSTRAINT FOREIGN KEY tb_emp_fk (dept_id) REFERENCES tb_dept(id)

#插入数据测试
INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id)
VALUE('a','S',2,'gz','aaatom&163.com',1);

#2.在建表最后最后创建约束
CREATE TABLE tb_emp(
id INT AUTO_INCREMENT,
NAME VARCHAR(18) NOT NULL,
sex VARCHAR(2) CHECK(sex='男' OR sex='女'),
age INT CHECK(age > 18 OR age <60),
address VARCHAR(200),
email VARCHAR(100) UNIQUE,
dept_id INT,
PRIMARY KEY (id)
);

#3.建表后建立约束，表级
CREATE TABLE tb_emp(
id INT,
NAME VARCHAR(18),
sex VARCHAR(2),
age INT ,
address VARCHAR(200),
email VARCHAR(100),
dept_id INT
);

#添加主键
ALTER TABLE tb_emp
ADD PRIMARY KEY (id)

#更改自动增长
ALTER TABLE tb_emp
MODIFY COLUMN id INT AUTO_INCREMENT

#添加外键
ALTER TABLE tb_emp
ADD CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_emp(id)

#添加唯一键
ALTER TABLE tb_emp
ADD UNIQUE(email)

#添加检查约束
ALTER TABLE tb_emp
ADD CONSTRAINT CHECK(age > 18 AND age < 60)

#最常用的方式（重点方式）
#注意 AUTO_INCREMENT只是MySql特有的，其他数据库没有，
CREATE TABLE tb_emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18) NOT NULL,
sex VARCHAR(2) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age > 18 OR age <60),
address VARCHAR(200),
email VARCHAR(100) UNIQUE,
dept_id INT,
CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_emp(id)
);

INSERT INTO tb_emp(NAME,dept_id)
VALUE('a',1)

/*
删除约束
.约束可以被删除，删除约束不会对数据产生影响
.当删除被外键参照的主键时候，应该采用CASCADE关键字来级联删除外键，否则无法删除主键
.语法如下 -alter table 表名 drop constraint 约束名

删除定义的约束
-删除 NOT NULL约束
ALTER TABLE 表名 MODIFY 列名类型；

-删除UNIQUE约束
ALTER TABLE 表名 DROP INDEX 唯一约束名；

-删除PRIMARY KEY约束
ALTER TABLE 表名 DROP PRIMARY KEY；

-删除FOREIGN KEY约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名；
*/

#删除约束
#修改非空约束
ALTER TABLE tb_emp MODIFY NAME VARCHAR(18)

#删除唯一约束
ALTER TABLE tb_emp DROP INDEX email

#删除primary key 时候，注意自动增长问题
ALTER TABLE tb_emp MODIFY id INT
ALTER TABLE tb_emp DROP PRIMARY KEY #(一开始时候总是失败的。但是当删除外键约束后，再运行，却成功删除主键)

#删除外键约束
ALTER TABLE tb_emp DROP FOREIGN KEY tb_emp_ibfk_1

/*
非空约束删除
.非空约束可以通过MODIFY来增加非空约束或删除非空约束
-增加：alter table 表名 modify 列名 not null
-删除：alter table 表名 modify 列名 null
.但add constraint的用法不能用来增加非空约束
.可以采用drop constraint约束名的用法来删除非空约束

自动增长和默认值
-为新的行产生位移标识
-一个表只能有一个AUTO_INCREMENT属性，且该属性必须为主键的一部分，
AUTO_INCREMENT属性可以是任何整数类型

默认值：指定默认值

表的存储引擎（MySql特有的）
MyISAM：应用于以读写操作为主，很少更新、删除，对事物完整性、并发性要求不高
InnoDB:应用于堆事物的完整性要求高，在并发条件下要求数据的一致性的情况。(MySql安装后的默认值)
MEMORY：表的数据存放在内存中，访问效率高，但一旦服务关闭，表中数据全部丢失
MERGE：是一组MyISAM表的组合，可以突破对单个MyISAM表大小限制，并提高访问效率

*/