+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int         | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

-- 創(chuàng)建一個(gè)學(xué)校表
create table school(
    sno int comment '學(xué)校編號',
    name varchar(255) not null comment '學(xué)校名稱',
    est_time date comment '建校時(shí)間'
);

-- 插入幾條數(shù)據(jù)來進(jìn)行測試(關(guān)于日期類型的插入, 其實(shí)底層會(huì)進(jìn)行str_to_date函數(shù)的調(diào)用, 把字符串轉(zhuǎn)化為日期)
insert into school (sno, name, est_time) values (1, '北京101中學(xué)', '1910-08-11');
insert into school (sno, name, est_time) values (2, '南開附中', '1912-04-13');
insert into school (sno, name, est_time) values (3, '清華附中', '1915-03-12');

-- select 查詢一下當(dāng)前的信息
select * from school;

-- 執(zhí)行結(jié)果如下
+------+-----------------+------------+
| sno  | name            | est_time   |
+------+-----------------+------------+
|    1 | 北京101中學(xué)     | 1910-08-11 |
|    2 | 南開附中        | 1912-04-13 |
|    3 | 清華附中        | 1915-03-12 |
+------+-----------------+------------+

-- 嘗試執(zhí)行下面的SQL
insert into school (sno, est_time) values (4, '1899-11-06');
-- 會(huì)直接報(bào)錯(cuò), 報(bào)錯(cuò)信息如下
ERROR 1364 (HY000): Field 'name' doesn't have a default value

create table [表名](
	...字段信息
	check(約束條件)
);

-- 創(chuàng)建一張學(xué)生表
create table t_stu(
    sno int,
    name varchar(255),
    age int,
    check(age > 18)
);

-- 執(zhí)行DML語句
insert into t_stu(sno, name, age) values (1, 'hh', 19);
insert into t_stu(sno, name, age) values (2, 'xx', 18);
-- 第一條是執(zhí)行成功的, 但是第二條 18 == 18, 不滿足check約束, 所以失敗

# 創(chuàng)建了一個(gè)學(xué)生表, 這個(gè)表的email字段是唯一的, 不可以重復(fù)
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255) unique
);
# 我們嘗試執(zhí)行下面的DML語句, 執(zhí)行成功成功的標(biāo)明 √, 否則為 ×
insert into t_stu (no, name, email) values (1, 'hh', 'hh@163.com');
insert into t_stu (no, name, email) values (2, 'xx', 'xx@163.com');
insert into t_stu (no, name) values (3, 'xx'), (4, 'wx');
# 查看一下當(dāng)前的信息
select * from t_stu;
+------+------+------------+
| no   | name | email      |
+------+------+------------+
|    1 | hh   | hh@163.com |
|    2 | xx   | xx@163.com |
|    3 | xx   | NULL       |
|    4 | wx   | NULL       |
+------+------+------------+
# 嘗試插入一條重復(fù)的數(shù)據(jù)
insert into t_stu (no, name, email) values (5, 'sf', 'hh@163.com');
# 直接報(bào)錯(cuò), 因?yàn)閑mail的位置重復(fù)了

create table t_stu(
	no int,
	name varchar(255),
	email varchar(255) not null unique
);

# unique的列級約束
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255),
	unique(email)
);
# unique的表級約束
create table t_stu(
	no int,
	name varchar(255),
	email varchar(255),
	unique(name,email)
);

# 表級約束起名的語法
constraint [約束名稱] [表級約束的主體];
# 起約束名的標(biāo)準(zhǔn)
表名_約束的字段_約束的簡稱(unique/pk/fk)

-- 創(chuàng)建一個(gè)班級表(設(shè)置班級編號為主鍵, 班級名稱為唯一鍵)
create table class(
    cno int comment '班級編號',
    cname varchar(255) comment '班級名稱',
    constraint class_cno_pk primary key(cno),
    constraint class_cname_unique unique(cname)
);

# 使用一下這個(gè)系統(tǒng)庫
use information_schema;
# 找到class表的約束名稱
select constraint_name from table_constraints where table_name = 'class';

create table t_student(
	id bigint primary key,
	sno varchar(255) unique,
	sname varchar(255) not null
)

create table t_user(
	no int,
	name varchar(255),
	age int,
	primary key(no,name)
);

create table t_vip(
	no int primary key auto_increment,
	name varchar(255)
);

-- 創(chuàng)建一個(gè)學(xué)校表(把學(xué)校編號設(shè)置為主鍵)
create table t_school(
    sch_no int primary key auto_increment,
    sch_name varchar(255) unique
);

-- 創(chuàng)建一個(gè)學(xué)生表(把學(xué)生編號設(shè)置為主鍵, 然后名字添加default約束, 年齡添加check約束, 學(xué)校名稱為外鍵)
create table t_student(
    stu_no int primary key auto_increment,
    stu_name varchar(255) default '無名氏',
    age int,
    sch_no int,
    check(age >= 18),
    constraint t_student_sch_no_fk foreign key(sch_no) references t_school(sch_no)
);

-- 插入幾條測試數(shù)據(jù)(按照我們舉出來的例子)
insert into t_school (sch_name) values ('清華大學(xué)'), ('北京大學(xué)'), ('浙江大學(xué)'), ('復(fù)旦大學(xué)');
insert into t_student (stu_name, age, sch_no) values ('hxh', 19, 1), ('dwv', 18, 1), ('cac', 19, 1), ('fqe', 20, 1);
insert into t_student (stu_name, age, sch_no) values ('ger', 18, 2), ('he', 21, 2);
insert into t_student (stu_name, age, sch_no) values ('few', 20, 3), ('rhr', 22, 3), ('wgh', 22, 3);
insert into t_student (stu_name, age, sch_no) values ('rhre', 22, 4), ('wg', 21, 4);

# 基礎(chǔ)語法就是
alter table [表名] drop constraint [約束名稱];
# 刪除學(xué)生表的外鍵約束
alter table t_student constraint t_student_sch_no_fp;

# 基礎(chǔ)語法
alter table [表名] add constraint [約束名稱] [約束主體];
# 比如我們從新把剛才刪除外鍵約束添加回來
alter table t_student add constraint stu_pk
foreign key(sch_no) references t_school(sch_no);

-- 只需要在創(chuàng)建約束的時(shí)候在末端加上一個(gè) (on delete cascade)
[創(chuàng)建約束主體] on delete cascade;

-- 首先刪除一下之前的外鍵約束
alter table t_student drop constraint stu_pk;
-- 創(chuàng)建一個(gè)新的外鍵約束(并加上一個(gè)級聯(lián)刪除的信息)
alter table t_student add constraint t_student_sch_no_fk foreign key(
sch_no) references t_school(sch_no) on delete cascade;
-- 下面我們從新進(jìn)行測試
delete from t_school where sch_no = 4;
-- 此時(shí)就會(huì)顯示執(zhí)行成功, 我們從新查看一下學(xué)生表中的數(shù)據(jù), 執(zhí)行結(jié)果如下圖

[約束主體] on update cascade;

-- 首先還是一下上一個(gè)約束
alter table t_student drop constraint t_student_sch_no_fk;
-- 創(chuàng)建一個(gè)級聯(lián)更新約束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on update cascade;
-- 把學(xué)校編號為3改為學(xué)校編號為5
update t_school set sch_no = 5 where sch_no = 3;
-- 查看一下當(dāng)前的學(xué)生信息
select * from t_student;
-- 執(zhí)行結(jié)果見下圖

[約束主體] on delete set null;

--還是先刪除約束
alter table t_student drop constraint t_student_sch_no_fk;
-- 加一個(gè)級聯(lián)置空的約束
alter table t_student add constraint t_student_sch_no_fk 
foreign key(sch_no) references t_school(sch_no) on delete set null;
-- 上一個(gè)級聯(lián)更新我們不是3編號更新為5了么, 我們現(xiàn)在刪除5數(shù)據(jù)對應(yīng)的編號學(xué)校
delete from t_school where sch_no = 5;
-- 執(zhí)行結(jié)果見下圖