【无标题】MySQL小作业

1. 创建数据库gradem。

CREATE DATABASE gradem;

2、创建student表

CREATE table if not exists student(
sno CHAR(10) NOT NULL,PRIMARY KEY(sno),
sname VARCHAR(8) null,
ssex char(2) NULL CHECK (ssex in(男,女)),
sbirthday date NULL,
saddress VARCHAR(50) NULL,
sdept CHAR(16) NULL,
speciality VARCHAR(20) NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;

3、创建course表

create table if not exists course(
cno CHAR(5) not NULL,primary key(cno),
cname VARCHAR(20) not null
)ENGINE = INNODB DEFAULT CHARSET=utf8;

4、创建sc表

create table if not EXISTS sc(
sno char(10) not NULL,
cno char(5) not NULL,
degree DECIMAL(3,1) CHECK(degree>=1 and degree<=100),
PRIMARY key(sno,cno),
CONSTRAINT fk_emp_student01
FOREIGN KEY (sno) REFERENCES student(sno),
CONSTRAINT fk_emp_course01
FOREIGN KEY (cno) REFERENCES course(cno)
)ENGINE=INNODB DEFAULT charset=utf8;

5、创建teacher表

CREATE table if not exists teacher(
tno char(3) not NULL,PRIMARY KEY(tno),
tname VARCHAR(8) null,
tsex CHAR(2) null CHECK(tsex in(男,女)),
tbirthday date null,
dept char(16) NULL
)ENGINE = INNODB DEFAULT charset = utf8;

6、创建teaching表

CREATE TABLE if not EXISTS teaching(
cno char(5) not NULL,
tno char(3) not null,
cterm TINYINT(1) NULL CHECK(cterm>=1 and cterm<=10),
PRIMARY KEY(cno,tno),
CONSTRAINT fk_emp_course02
FOREIGN KEY (cno) REFERENCES course(cno),
CONSTRAINT fk_emp_teacher01
FOREIGN KEY (tno) REFERENCES teacher(tno)
)ENGINE=INNODB DEFAULT charset=utf8;

7、向student插入数据

INSERT into student
(sno,sname,ssex,sbirthday,saddress,sdept,speciality)
VALUES
(2020010101,李勇,男,2001-01-12,山东济南,计算机工程系,计算机应用);
INSERT into student
VALUES
(2020020101,刘晨,女,2002/06/04,山东青岛,信息工程,电子商务);
INSERT into student
(sno,sname,ssex,sbirthday,saddress,sdept,speciality)
VALUES
(2020030101,王敏,女,2002/12/23,江苏苏州,数学系,数学);
INSERT into student
(sno,sname,ssex,sbirthday,saddress,sdept,speciality)
VALUES
(2020020201,张立,男,2002/08/25,河北唐山,信息工程,电子商务);

8、向course表输入数据

INSERT INTO course
(cno,cname)
VALUES
(C01,数据库);
INSERT INTO course
(cno,cname)
VALUES
(C02,数学);
INSERT INTO course
(cno,cname)
VALUES
(C03,信息工程系统);
INSERT INTO course
(cno,cname)
VALUES
(C04,操作系统);

9、向sc表输入数据

INSERT INTO sc
(sno,cno,degree)
VALUES
(2020010101,C01,92);
INSERT INTO sc
(sno,cno,degree)
VALUES
(2020010101,C02,85);
INSERT INTO sc
(sno,cno,degree)
VALUES
(2020010101,C03,88);
INSERT INTO sc
(sno,cno,degree)
VALUES
(2020020201,C02,90);
INSERT INTO sc
(sno,cno,degree)
VALUES
(2020020201,C03,80);

10、向teacher表插入数据

INSERT INTO teacher
(tno,tname,tsex,tbirthday,dept)
VALUES
(101,李新,男,1987/01/12,计算机工程系);
INSERT INTO teacher
(tno,tname,tsex,tbirthday,dept)
VALUES
(102,钱军,女,1990/06/04,计算机工程系);
INSERT INTO teacher
(tno,tname,tsex,tbirthday,dept)
VALUES
(201,王小花,女,1989/12/23,信息工程系);
INSERT INTO teacher
(tno,tname,tsex,tbirthday,dept)
VALUES
(202,张小青,男,1978/08/25,信息工程系);

11、向teaching表插入数据

INSERT INTO teaching
(cno,tno,cterm)
VALUES
(C01,101,2);
INSERT INTO teaching
(cno,tno,cterm)
VALUES
(C02,102,1);
INSERT INTO teaching
(cno,tno,cterm)
VALUES
(C03,201,3);
INSERT INTO teaching
(cno,tno,cterm)
VALUES
(C04,202,4);

12、修改表结构。

one、 向student表中增加“入学时间”列，其数据类型为日期时间类型

ALTER TABLE student ADD login_date date;

Two、修改studet表中sdept字段长度改为20.

ALTER table student MODIFY sdept CHAR(20);

Three、将studen表中的speciality字段删除。

ALTER TABLE student DROP speciality;

Four、删除student表

先查看外键：
SHOW CREATE TABLE sc;

然后解除外键：
ALTER table sc DROP FOREIGN KEY fk_emp_student01;

最后删除student表：

DROP TABLE student;