数据库day02

1. 删除名为school的数据库

drop database if exists `school`;

2. 创建名为school的数据库

create database `school` default character set utf8mb4;

3. 切换到school数据库

use `school`;

4. 创建学院表

create table `tb_college`
(
`col_id` integer not null auto_increment comment '编号',
`col_name` varchar(50) not null comment '名称',
`col_intro` varchar(1000) not null default '' comment '介绍',
`col_fdate` date not null comment '成立日期',
`col_url` varchar(1024) not null default '' comment '学院网址',
primary key (`col_id`)
) engine=InnoDB auto_increment=1 comment '学院表';

5. 创建学生表

create table `tb_student`
(
`stu_id` integer not null comment '学号',
`stu_name` varchar(10) not null comment '姓名',
`stu_sex` boolean not null default 1 comment '性别',
`stu_birth` date not null comment '出生日期',
`stu_addr` varchar(200) not null comment '家庭住址',
primary key (`stu_id`)
)engine=InnoDB auto_increment=1 comment '学生表';

6. 修改学生表添加学院编号

alter table `tb_student` add column `col_id` integer not null comment '学院编号';

7. 修改学生表添加外键约束

alter table `tb_student` add constraint foreign key (`col_id`) 
references `tb_college` (`col_id`);

8. 创建老师表

create table `tb_teacher`
(
`tea_id` integer not null comment '工号',
`tea_name` varchar(20) not null comment '姓名',
`tea_sex` boolean not null default 1 comment '性别',
`tea_title` varchar(10) not null comment '职称',
`tea_birth` date not null comment '出生日期',
`col_id` integer not null comment '学院编号',
primary key (`tea_id`),
foreign key (`col_id`) references `tb_college` (`col_id`)
);

9. 创建课程表

create table `tb_course`
(
`cou_id` integer unsigned not null comment '编号',
`cou_name` varchar(100) not null comment '名称',
`cou_credit` integer not null comment '学分',
`tea_id` integer not null comment '任课老师',
primary key (`cou_id`),
constraint `fk_course_tea_id` foreign key (`tea_id`) references `tb_teacher` (`tea_id`)
);

10. 删除约束

alter table `tb_course` drop primary key;
alter table `tb_course` drop constraint `fk_course_tea_id`;

11. 引入中间表

关系型数据库中无法通过两张表维系多对多关系，所以需要引入中间表;

相当于把原来的多对多关系拆解为两个多对一关系。

create table `tb_record`
(
`rec_id` bigint unsigned not null auto_increment comment '流水号',
`stu_id` integer not null comment '学号',
`cou_id` integer unsigned not null comment '课程号',
`sel_date` datetime not null comment '选课日期',
`score` decimal(4,1) comment '考试成绩',
primary key (`rec_id`),
foreign key(`stu_id`) references `tb_student` (`stu_id`),
foreign key(`cou_id`) references `tb_course` (`cou_id`),
constraint `uk_record_stu_cou` unique (`stu_id`,`cou_id`)
) engine=InnoDB comment '选课记录表';

12. 插入、删除、更新

-- DML (insert / delete / update)

use `school`;

insert into `tb_college` values (default, '计算机学院', 'XXXXXXXX');

insert into `tb_college`
	(`col_name`, `col_intro`)
values 
	('外国语学院', 'YYYYYYYY');

insert into `tb_college`
	(`col_name`, `col_intro`)
values
	('经济管理学院', 'ZZZZZZZZ'),
    ('文学院', 'aaaaaaa'),
    ('理学院', 'bbbbbbb');

-- Error Code:Duplicate entry
-- insert into `tb_college` values (10, '计算机学院', 'hello');

insert into `tb_student` values (1001, '小明', 1, '1998-11-03', '四川成都', 1);
-- delete from `tb_student` where `stu_id`=1001;
insert into `tb_student` values (1002, '王大锤', 1, '1995-01-23', '四川成都', 3);

-- insert into `tb_student` values (1003, '狄仁杰', 1, '1988-10-10', '四川成都', 10);

update `tb_student` set `stu_birth`='1990-02-03', `stu_addr`='四川绵阳' 
where `stu_id`=1002;

show variables;