MySQL-01 DDL、DML、DCL常用SQL

DDL-Date Definition Language#

对库操作

# 删库语句
drop database if exists study;
# 建库语句
create database if not exists study default charset utf8mb4;
# 查看所有数据库
show databases ;
# 使用指定数据库
use study;
# 查看正在使用哪个数据库
select database();

建表操作

create table if not exists student
(
    id        varchar(16) primary key comment '编号',
    name      varchar(8) not null comment '姓名',
    age       tinyint unsigned comment '年龄',
    gender    int(1) default 0 comment '性别',
    exam_id   varchar(12) unique comment '准考证号',
    course_id varchar(2) comment '课程编号'
) comment '学生表';


create table course
(
    id          int(2) auto_increment comment '课程编号' primary key,
    course_name varchar(4) comment '课程名称'
) comment '课程表';

create table course_student
(
    id         int(4) auto_increment comment '主键' primary key,
    course_id  int(2) comment '课程id',
    student_id varchar(16) not null comment '学生id',
    constraint fk_course foreign key (course_id) references course (id) on update cascade on delete set null,
    constraint fk_stu foreign key (student_id) references student (id) on update cascade on delete cascade
) comment '学生课程中间表';