day1

mysql8.0之后版本改密码：alter user '用户名'@'localhost
'identified with mysql_native_password by '新密码';

-- 如果存在名为school的数据库就删除它
drop database if exists school;

-- 创建名为school的数据库并指定默认的字符集为utf-8
create database school default charset utf8;

-- 切换到school数据库上下文环境
use school;

-- 创建学生表（DDL - 数据定义语言 - create， drop， alter）
create table tb_student
(
stuid int not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth date,
primary key (stuid)
);



-- 修改学生表
alter table tb_student drop column stusex;
alter table tb_student add column stusex boolean not null default 1after stuname;
alter table tb_student add column stuaddr varchar(255);
alter table tb_student change column stuaddr stuaddr varchar(511);
alter table tb_student drop column stuaddr;



-- 修改学生表添加学院编号(colid)列
alter table tb_student add column colid int;

-- 修改学生表添加外键约束(参照完整性)
alter table tb_student add constraint fk_student_colid foreign key (colid) references tb_college (colid);
-- 插入数据（DML - 数据操作语言 insert，delete， update， select）
insert into tb_student values (1001, '张三', 1, '1997-07-30', '西安');
insert into tb_student (stuid, stuname, stubirth) values (1002, '张三丰', '1990-11-09');
insert into tb_student (stuid, stuname, stubirth) values 
(1003, '王浩', '1998-08-09'),
(1004, '王二', '1998-08-09'),
(1005, '王雨燕', '1998-08-09');

-- 查询学生表中的所有信息
select * from tb_student;
select * from tb_student\G  -- 竖着显示表中的信息

-- 删除表中的信息
delete from tb_student where stuid=1002;
delete from tb_student where stuid in (1003, 1005);

-- 更新学生表中的信息
update tb_student set stuaddr = '四川成都' where stuid=1001;
update tb_student set stuname = '王二麻子', stusex = 0, stuaddr = '四川绵阳' where stuid=1004;

-- 更新学生表为学生指定所属学院
update tb_student set colid=1 where stuid between 1001 and 1006;
update tb_student set colid=2 where stuid in (1007, 1008);
update tb_student set colid=3 where stuid=1009;


-- 创建老师表
create table tb_teacher
(
teaid int not null comment '工号',
teaname varchar(20) not null comment '姓名',
teasex bit default 1 comment '性别',
teabirth date comment '生日',
teatitle varchar(10) default '助教' comment '职称',
colid int not null comment '所在学院'
-- primary key (teaid),
-- foreign key (colid) references tb_college (colid)
);

alter table tb_teacher add constraint pk_teacher_teaid primary key (teaid);

alter table tb_teacher add constraint fk_teacher_colid foreign key (colid) references tb_college (colid);