MySQL实验08

create database lienci1121;
use lienci1121;

CREATE TABLE student
(sno CHAR(9) PRIMARY KEY,
sname CHAR(20)UNIQUE NOT NULL,
ssex CHAR(2)CHECK(Ssex IN('男','女')),
sage SMALLINT,
sdept CHAR(20));

CREATE TABLE course
(cno CHAR(4) PRIMARY KEY,
cname CHAR(40)UNIQUE NOT NULL,
cpno CHAR(4),
ccredit SMALLINT,
FOREIGN KEY(cpno)REFERENCES course(cno));

CREATE TABLE sc
(sno CHAR(9),
cno CHAR(4),
grade SMALLINT CHECK(grade>=0 AND grade<=100),
PRIMARY KEY(sno,cno),
FOREIGN KEY(cno)REFERENCES course(cno)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(sno)REFERENCES student(sno)
ON DELETE NO ACTION
ON UPDATE CASCADE
);

INSERT INTO student VALUES('2019001','李勇','男',19,'CS');
INSERT INTO student VALUES('2019002','刘晨','女',19,'CS');
INSERT INTO student VALUES('2019003','王敏','女',18,'CS');
INSERT INTO student VALUES('2019004','刘星','男',21,'IS');
INSERT INTO student VALUES('2019005','王离','女',18,'CS');
INSERT INTO student VALUES('2019006','董琦','女',19,'IS');


INSERT INTO course VALUES('1','数据库',NULL,4);
INSERT INTO course VALUES('2','数学',NULL,2);
INSERT INTO course VALUES('3','信息系统',NULL,4);
INSERT INTO course VALUES('4','操作系统',NULL,3);
INSERT INTO course VALUES('5','数据结构',NULL,4);
INSERT INTO course VALUES('6','数据处理',NULL,2);

INSERT INTO sc VALUES('2019001','1',92);
INSERT INTO sc VALUES('2019001','2',85);
INSERT INTO sc VALUES('2019002','3',88);
INSERT INTO sc VALUES('2019002','2',90);
INSERT INTO sc VALUES('2019003','3',80);

INSERT INTO student VALUES('2019101',null,'男',22,'CS'); /* not null约束*/
INSERT INTO student VALUES('2019001','胡汉','男',20,'CS');/*primary key约束*/
INSERT INTO course VALUES('9','数据库',NULL,3);          /* unique约束*/
INSERT INTO student VALUES('2019022','张斌','南',17,'IS');/*check约束，mysql不支持check约束*/

insert INTO sc values('2019010','2',90);

update sc set sno='2019002' where sno='2019010' AND cno '3';/*将学号为2019002且选修了3号课程的学生的学号修改为2019010；*/

delete from student where sname='王敏';/*删除学生表中王敏的全部信息；*/

update student set  sno='2019010' where sname='刘晨';/*修改学生表中刘晨的学号sno，改为2019010；*/

CREATE TABLE Student1
(sno CHAR(9) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
sname CHAR(20) CONSTRAINT C2 NOT NULL,
ssex CHAR(2) CONSTRAINT C3 CHECK(Ssex IN ('男','女')),
sage SMALLINT CONSTRAINT C4 CHECK (Sage<30), 
sdept CHAR(20),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

CREATE TABLE student1
(sno CHAR(9) PRIMARY KEY,
sname CHAR(20) UNIQUE NOT NULL,
ssex CHAR(2) CHECK(Ssex IN ('男','女')),
sage SMALLINT,
sdept CHAR(20));

CREATE TRIGGER Student_new
AFTER INSERT ON Student
FOR EACH ROW
INSERT INTO Student1 VALUES(new.Sno,new.sname,new.ssex,new.sage,new.sdept);

INSERT INTO student VALUES('2019015','董丽','女',21,'IS');
INSERT INTO student VALUES('2019016','张武','女',21,'IS');
INSERT INTO student VALUES('2019017','李四','男',21,'IS');

CREATE TABLE time1
(ttime DATE)

CREATE TRIGGER trige_time
AFTER INSERT ON sc
FOR EACH ROW 
INSERT INTO time1 VALUES(NOW());

INSERT INTO sc VALUES('2019006','5',72);
INSERT INTO sc VALUES('2019006','6',88);

DROP TRIGGER trige_time ON sc;/* 错误*/

DROP TRIGGER IF EXISTS trige_time;

INSERT INTO sc VALUES('2019005','6',68);

注意：执行更新操作时应保持数据的完整性。

方法一：创建表时增加级联更新和级联删除

CREATE TABLE SC
( Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT, PRIMARY KEY(Sno,Cno)，
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE NO ACTION
/当删除course 表中的元组造成了与SC表不一致时拒绝删除/
ON UPDATE CASCADE
/当更新course表中的cno时，级联更新SC表中相应的元组/
);

方法二、表结构已建好，增加级联更新和级联删除

ALTER TABLE sc
ADD FOREIGN KEY(sno)REFERENCES student(sno)ON DELETE CASCADE ON UPDATE CASCADE ;

ADD FOREIGN KEY(cno)REFERENCES course(cno)ON DELETE CASCADE ON UPDATE CASCADE;