SQL-约束

#約束
/**
1.1基础知识：为什么需要约束？
为了保证数据的完整性
1.2什么叫约束？ 对表中字段的限制
1.3 约束的分类？
角度一：约束的字段的个数？
单列约束 vs 多列约束
列级约束:将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明玩，所有字段的后面声明的约束
角度3：约束的作用
1.not null 非空约束
2.unique 唯一性约束
3.primary key 主键约束
4.foreign key 外键约束
5.check 检查约束
6.default 默认值约束

1.4 如何添加约束
create table时添加约束

alter table 时增加约束，删除约束
*/
#如何查看表中的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'employees';
#not null 非空约束
#3.1 在create table 时添加 约束
CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(20) NOT NULL,
email VARCHAR(25),
salary DOUBLE(10,2)
);
DESC test1;
INSERT INTO test1
VALUES(1,'Tom','tom126@163.com',3400);
INSERT INTO test1
VALUES(NULL,'Tom','tom126@163.com',3400);#错误代码： 1048
#error:Column 'id' cannot be null
UPDATE test1
SET last_name = NULL
WHERE id = 1;# error
#3.2在 alter table 时添加约束
DESC test1;
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
# unique(唯一性约束)
CREATE TABLE test2(
id INT UNIQUE NOT NULL, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary DECIMAL(10,2),

#表级约束：
CONSTRAINT uk_test2_email UNIQUE(email)
);
DESC test2;
#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
INSERT INTO test2()
VALUES(2,'Tom','tom@163.com',3500);
DESC test2;
SELECT * FROM test2;
INSERT INTO test2()
VALUES(1,'Tom','null',3500);
#可以向声明为unique的字段上添加null值，而且可以多次添加null
#可以在alter table 添加约束
DESC test2;
UPDATE test2
SET salary = 1050,last_name = 'Jerry'
WHERE id =1;
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);
#
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

#4.3 复合的唯一性约束
CREATE TABLE USER(
id INT,
NAME VARCHAR (15),
PASSWORD VARCHAR (25),
#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(NAME,PASSWORD));
#
INSERT INTO USER
VALUES(1,'Tom','abc');
#可以成功的
INSERT INTO USER
VALUES(1,'Tom1','abc');
#案例：复合的唯一性约束
#学生表
CREATE TABLE student(
sid INT, #学号
sname VARCHAR(20), #姓名
tel CHAR(11) UNIQUE KEY, #电话
cardid CHAR(18) UNIQUE KEY #身份证号
);

#课程表
CREATE TABLE course(
cid INT, #课程编号
cname VARCHAR(20) #课程名称
);

#选课表
CREATE TABLE student_course(
id INT,
sid INT,
cid INT,
score INT,
UNIQUE KEY(sid,cid) #复合唯一
);
INSERT INTO student VALUES(1,'张三','13710011002','101223199012015623');#成功
INSERT INTO student VALUES(2,'李四','13710011003','101223199012015624');#成功
INSERT INTO course VALUES(1001,'Java'),(1002,'MySQL');#成功
#删除唯一性约束
ALTER TABLE test2
DROP INDEX last_name;

#主键约束 如何声明主键约束 最多有一个主键约束
CREATE TABLE test3(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
#表级约束
CREATE TABLE test4(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
CONSTRAINT uk_test5_id PRIMARY KEY(id)
);
INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4000,'tom134@163.com');

#
CREATE TABLE user1(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY(NAME,PASSWORD)

);
INSERT INTO user1
VALUES (1,'Tom','abc');
INSERT INTO user1
VALUES (1,'Tom1','abc');
SELECT *FROM user1;
#如果是多列复合的主键 那么都不能为空
CREATE TABLE test6(
id INT,
NAME VARCHAR(15),
PASSWORD VARCHAR(25),
PRIMARY KEY(NAME,PASSWORD)

);
ALTER TABLE test6
ADD PRIMARY KEY(id);
DESC test6;
#如何删除主键约束（在实际开发中根本不会删除主键约束）
ALTER TABLE test6
DROP PRIMARY KEY;
#自增长列 auto_increment
CREATE TABLE test7(
id INT PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(15)
);
INSERT INTO test7
VALUES(5,'Tom');
INSERT INTO test7
VALUES(0,'Jerry');#当我们添加0或者null时会自动的自增
#开发中：一旦主键作用的字段声明有auto_increment，则我们在添加数据时，就不要给主键
#对应的字段去赋值了
#6.2 在alter table 时添加
CREATE TABLE test8(
id INT PRIMARY KEY,
last_name VARCHAR(15)
);
DESC test8
ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;
#6.4mysql 8.0 中新增

#7 foregin key 外键约束
#7.1 在create table 时 创建

#主表和从表 父表和子表

CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
CREATE TABLE emp1(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT ,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(depat_id)
);
#上述操作报错，因为主表中的dept_id 上 没有主键约束 或者唯一性约束
#1.添加
ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
DESC dept1;
#
CREATE TABLE emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(15),
department_id INT ,
CONSTRAINT fk_emp1_dept_id FOREIGN KEY(department_id) REFERENCES dept1(dept_id)
);

#7.2演示外键的效果
#添加失败
INSERT INTO emp2
VALUES (1001,'Tom',10);
#
INSERT INTO dept1
VALUES(10,'IT');
#在主表添加10号部门以后 才能添加外表的数据
INSERT INTO emp2
VALUES (1001,'Tom',10);

#7.3 在alter able 时添加外键约束
CREATE TABLE emp2(

);
#check 约束
CREATE TABLE test10(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK(salary > 2000)
);
#default 约束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL(10,2)DEFAULT 2000
);
DESC test11;
INSERT INTO test11
VALUES(1,'tom',3000);
INSERT INTO test11
VALUES(2,'tom1',NULL);
SELECT *
FROM test11;