mysql、表操作、表的约束：unique唯一约束、主键约束、not null不能为空、default默认值、外键约束包括（外键配置、添加、删除、链表查询等）

/*表约束*/
/* unique唯一约束*/
create table if not exists y1(
    id int unsigned not null auto_increment primary key,
    name varchar(20) unique,/*唯一约束，不能出现相同的名字*/
    beiyong varchar(20)
)engine=innodb default charset=utf8mb4 auto_increment=2022021900;
select * from y1;
insert into y1 values(null,'wzt',null),(null,'yz',null);
insert into y1 value(null,'wzt','hello');/*数据中已经有wzt，无法添加成功*/
alter table y1 modify beiyong varchar(20) unique;/*将beiyong修改为唯一约束*/
desc y1;

/*主键约束,主键不能重复，不能为空*/
create table if not exists y2(id int);
alter table y2 modify id int primary key;/*将id修改为主键*/
desc y2;

create table if not exists y3(
    id int unsigned not null auto_increment primary key,/*primary key 设置主键*/
    name varchar(20) not null,
    beiyong varchar(255)
)engine=innodb default charset=utf8mb4 auto_increment=3118010000;
desc y3;

create table if not exists bc1(
    id char(36) default (uuid()) primary key,
    name varchar(20) not null unique
)engine=innodb default charset=utf8mb4;
insert into bc1(name) value('wzt');
select * from bc1;


/*not null不能为空约束*/
create table if not exists y4(
    name varchar(20) not null/*name不能为空值必须填写数据*/
);

/*default 默认值约束*/
create table if not exists y5(
    id int unsigned not null auto_increment primary key,/*primary key 设置主键*/
    name varchar(20) not null,
    genter enum('男','女') default '男'/*如果genter为空则默认值为男*/
)engine=innodb default charset=utf8mb4 auto_increment=3118010000;/*默认字符集utf8mb4,默认id从3118010000开始自增*/

/*外键约束*/
create table if not exists xueyuan(
    xid int unsigned not null auto_increment primary key,
    xname varchar(20) not null unique
)engine=innodb default charset=utf8mb4 auto_increment=20220;
insert into xueyuan values(null,'信电'),(null,'经管'),(null,'医学院'),(null,'土木');
insert into xueyuan values(20220,'信电');
select * from xueyuan order by xid asc ;
desc xueyuan;
create table if not exists zhuanye(
    zid int unsigned not null auto_increment primary key,
    zname varchar(20) not null unique,
    zxid int unsigned
    /*constraint zyfk foreign key (zxid) references xueyuan(xid)*/
)engine=innodb default charset=utf8mb4 auto_increment=00;
insert into zhuanye values(null,'计算机科学与技术',20220),(null,'软件工程',20220),(null,'会计',20221),(null,'临床护理',20222);
insert into zhuanye values(1,'计算机科学与技术',20220),(2,'软件工程',20220);
select * from zhuanye order by zid asc ;
desc zhuanye;
create table if not exists stu(
    sid int unsigned not null auto_increment primary key,
    sname varchar(20) unique,
    sxid int unsigned,
    szid int unsigned
    /*constraint stfk foreign key (sxid) references xueyuan(xid),foreign key (szid) references zhuanye(zid)*/
)engine=innodb default charset=utf8mb4 auto_increment=000;
insert into stu values (null,'张三',20222,4),(null,'李四',20222,3),(null,'王二',20221,2),(null,'麻子',20221,2),(null,'wzt',20220,1),(null,'yz',20220,1),(null,'xk',20220,1);
select * from stu order by sid asc;
desc stu;
/*关联方法一，没有外键配置，直接关联查询*/
select s.sid '学号',s.sname '姓名',x.xname '学院',z.zname '专业' from stu s join xueyuan x on s.sxid = x.xid join zhuanye z on s.szid = z.zid;/*s、x、z取得别名防止表与表之间列名冲突*/
select s.sid '学号',s.sname '姓名',x.xname '学院',z.zname '专业' from stu s,xueyuan x,zhuanye z where s.sxid = x.xid and s.szid = z.zid;/*效率低，不建议使用*/
update zhuanye set zname='数钱的' where zid=3;
select s.sid '学号',s.sname '姓名',x.xname '学院',z.zname '专业' from stu s join xueyuan x on s.sxid = x.xid join zhuanye z on s.szid = z.zid;
/*方法二，添加外键*/
alter table zhuanye add constraint zyfk foreign key (zxid) references xueyuan(xid);
alter table stu add constraint stfk1 foreign key (sxid) references xueyuan(xid);
alter table stu add constraint stfk2 foreign key (szid) references zhuanye(zid);
/*删除外键*/
alter table zhuanye drop constraint zyfk;
alter table stu drop constraint stfk1;
alter table stu drop constraint stfk2;
/*添加极联删除和修改功能的外键*/
alter table zhuanye add constraint zyfk foreign key (zxid) references xueyuan(xid) on delete cascade ;/*如果把学院删除，与之该学院相关的专业全部随之删除*/
alter table stu add constraint stfk1 foreign key (sxid) references xueyuan(xid) on delete set null ;/*如果把学院删除，与之该学院相关外键之为空null*/
alter table stu add constraint stfk2 foreign key (szid) references zhuanye(zid) on delete set null on update cascade ;/*如果把专业删除，与之该专业相关外键之为空null，如果修改，则于该专业的值都改*/
delete from xueyuan where xid=20220;
select * from stu;