— 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
id int , # 给id字段添加了一个主键约束
name varchar(20),
age int
)

drop table st5 ;

desc st5;

在已有表中添加主键约束
ALTER TABLE st5 ADD PRIMARY KEY(id);

— 插入重复的主键值
insert into st5 values (1, ‘关羽’, 30);
— 错误代码： 1062 Duplicate entry ‘1’ for key ‘PRIMARY’
insert into st5 values (1, ‘关云长’, 20);

select * from st5;

— 插入 NULL 的主键值, Column ‘id’ cannot be null
insert into st5 values (null, ‘赵云’, 20);

— 删除 st5 表的主键约束
alter table st5 drop primary key;

——————————————————————————————

— 指定起始值为 1000
create table st4 (
id int primary key ,#auto_increment,
name varchar(20)
) #auto_increment = 1000;

insert into st4 values (null, ‘孔明’);

select * from st4 ;
# 创建好以后修改起始值
alter table st4 auto_increment = 2000;
#DELETE：删除所有的记录之后，自增长没有影响。
delete from st4 ;

TRUNCATE：删除以后，自增长又重新开始。
truncate table st4 ;

————————————————————————————————

— 创建学Th表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学Th
create table st7 (
id int,
name varchar(20) #not null unique # 唯一约束
)

主键约束在一张表中只能有一个.但是not null unique就受限制了.可以出现很多个

— 添加一个同名的学Th
insert into st7 values (1, ‘张三’);

select * from st7;

— Duplicate entry ‘张三’ for key ‘name’
insert into st7 values (2, ‘张三’);

— 重复插入多个 null 会怎样？
insert into st7 values (2, null);

insert into st7 values (3, null);

为已经创建的表添加唯一约束
ALTER TABLE st7 ADD unique(name);

— 删除 st7 表的唯一键约束[通过删除唯一索引来实现删除唯一约束]
ALTER TABLE st7 DROP INDEX name;

查看唯一性约束[key_name就是唯一的约束名.也是唯一索引名]
show keys from st7;

desc st7;
# ——————————————————————————————————
— 创建表学Th表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
create table st8 (
id int,
name varchar(20) not null,
gender char(1)
)

— 添加一条记录其中姓名不赋值
insert into st8 values (1,’张三疯’,’男’);

select * from st8;
— Column ‘name’ cannot be null
insert into st8 values (2,null,’男’);

———————————————————————————————————-

— 创建一个学Th表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
id int,
name varchar(20),
address varchar(20) default ‘广州’
)

— 添加一条记录,使用默认地址
insert into st9 values (1, ‘李四’, default);

select * from st9;

insert into st9 (id,name) values (2, ‘李白’);

— 添加一条记录,不使用默认地址
insert into st9 values (3, ‘李四光’, ‘深圳’);

insert into st9(id,address) values (7,’黄石’);

insert into st9 values (8,default,default);

———————————————————————————————————————————-
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);

— 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘张三’, 20, ‘研发部’, ‘广州’);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘李四’, 21, ‘研发部’, ‘广州’);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘王五’, 20, ‘研发部’, ‘广州’);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘老王’, 20, ‘销售部’, ‘深圳’);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘大王’, 22, ‘销售部’, ‘深圳’);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘小王’, 18, ‘销售部’, ‘深圳’);
# —————————————————————————————————————————————-

— 解决方案：分成 2 张表
— 创建部门表(id,dep_name,dep_location)
— 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);

— 创建员工表(id,name,age,dep_id)
— 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int — 外键对应主表的主键
— 创建外键约束
#constraint emp_depid_fk foreign key (dep_id) references department(id)
)

— 添加 2 个部门
insert into department values(null, ‘研发部’,’广州’),(null, ‘销售部’, ‘深圳’);

select * from department;

— 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘李四’, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘王五’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘老王’, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘大王’, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘小王’, 18, 2);

insert into employee values (null,’老张’,19,6)
delete from employee where id = 10 ;

select * from employee;

已有表增加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_dept_dept_id_fk FOREIGN KEY (dep_id) REFERENCES department(id);
# 删除掉外键约束
alter table employee drop foreign key emp_dept_dept_id_fk;

desc employee;

————————————————————————————————————————-

外键的级联
select * from employee;

select * from department;
— 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
— Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where dep_name=’销售部’;

— 要删除部门 id 等于 1 的部门, 能不能直接删除呢？
— Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

—————————————————————————————————————————-
# 什么是级联操作
#级联更新
update department set id=5 where dep_name=’销售部’;

级联删除
delete from department where id=1;

—————————————————————————————————————————-
# 检查约束check

create table st10 (
id int,
name varchar(20),
sex char(1) CHECK(sex = ‘男’ or sex = ‘女’)
)

insert into st10 values(1,’小李’,’神’) # 不起作用,因为mysql不支持检查约束

SELECT * from st10 ;