Maturest

# DDL

通常将SQL分为四类，分别是DDL（数据定义语言）、DML（数据操作语言）、DCL（数据控制语言）、TCL（事务控制语言）

常见的DDL语言包括 create、alter、drop等。

# 建表建库

# 删除数据库 school
drop database if exists `school`;

# 创建数据库 school
create database `school` character set utf8mb4 collate unt8mb4_unicode_ci

# 切换到 school 数据库上下文环境
use `school`

# 创建学院表
create table `college` 
(
    `id` int unsigned auto_increment comment '编号',
    `name` varchar(50) not null comment '学院名称',
    `intro` varchar(500) default '' comment '学院描述',
    primary key (`id`)
) engine=innodb auto_increment=1 comment '学院表';


# 创建学生表
create table student 
(
    id int(10) unsigned auto_increment comment '学号',
    `name` varchar(50) not null comment '学生姓名',
    `sex` TINYINT(1) default 1 not null comment '性别',
    `birthday` date not null comment '出生日期',
    `address` varchar(255) default '' comment '籍贯',
    `college_id` int(10) unsigned not null comment '所属学院',
    primary key (`id`),
    constraint `student_college_id` foreign key (`college_id`) references `college` (`id`)
) engine=innodb comment '学生表';

# 创建教师表
create table teacher
(
    id int(10) unsigned auto_increment comment '工号',
    `name` varchar(50) not null comment '姓名',
    `title` varchar(10) default '助教' comment '职称',
    `college_id` int(10) unsigned not null comment '所属学院',
    primary key (`id`),
    constraint `teacher_college_id` foreign key (`college_id`) references `college` (`id`)
) engine=innodb comment '老师表';

# 创建课程表
create table course
(
    id int(10) unsigned auto_increment comment '编号',
    `name` varchar(50) not null comment '课程名称',
    `credit` int(1) unsigned not null comment '学分',
    `teacher_id` int(10) unsigned not null comment '授课老师',
    primary key (`id`),
    constraint `course_teacher_id` foreign key (`teacher_id`) references `teacher` (`id`)
) engine=innodb comment '课程表';


# 创建学生选课记录表
create table record
(
    id bigint unsigned auto_increment comment '选课记录号',
    `student_id` int unsigned not null comment '学号',
    `course_id` int unsigned not null comment '课程编号',
    `created_at` datetime not null comment '选课日期',
    `score` decimal(4,1) comment '考试成绩',
    primary key (`id`),
    constraint `record_student_id` foreign key (`student_id`) references `student` (`id`),
    constraint `record_course_id` foreign key (`course_id`) references `course` (`id`),
    constraint `uk_record_student_course` unique (`student_id`,`course_id`)
) engine=innodb comment '学生选课记录表';

# 删除表和修改表

# 删除表
drop table if exists `student`

# 给学生表添加手机号字段
alter table `student` add column `phone` varchar(20) not null default '' comment '联系方式' after `name`;

# 删除指定的列
alter table `student` drop column `phone`

# 修改列的数据类型
alter table `student` modify column `sex` char(1) not null default 'M' comment '性别';

# 修改列的命名
alter table `student` change `sex` `gender` boolean not null default 1 comment '性别';

# 删除外键约束
alter table `student` drop foreign key `student_college_id`;

# 添加外键约束
alter table `student` add constraint `student_college_id` foreign key (`college_id`) references `college` (`id`);

# 修改表的名字
alter table `student` rename to `stu_info`;

← DML →