MySQL实验08

安之海

已于 2023-12-11 12:19:15 修改

阅读量63

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-11-21 11:25:36 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_50006364/article/details/134527850

版权

MySQL 专栏收录该内容

9 篇文章

订阅专栏

文章详细介绍了在SQL中创建数据库表，包括添加字段、主键、外键以及使用级联操作（CASCADE）来维护数据一致性。涵盖了INSERT、UPDATE和DELETE语句的示例，以及如何在现有表结构中添加级联约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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表中相应的元组/
);