【Transact-SQL】“一键”创建三张表（Student、Course、SC），并插入教材中所有的数据

HBU_David

已于 2025-03-12 17:18:43 修改

阅读量1w

点赞数 28

分类专栏： Transact-SQL 文章标签： sql 数据库

于 2020-03-06 15:31:54 首次发布

本文链接：https://blog.youkuaiyun.com/qq_38975453/article/details/104696976

版权

Transact-SQL 专栏收录该内容

18 篇文章

订阅专栏

前面我们提到，教材里的三张表会反复用到~

当我们练习“删除数据”和“修改数据”之后，三张表的内容会变得“面目全非”。

如何快速“复原”，像一切都没发生过一样？运行下面的程序就可以啦。

这段代码难度不大，前面学过“建表”和“插入”语句，现在只是组合在一起了而已。

看不懂的地方，可以在评论区留言。

--Edit by HBU_David @ HeBei University 2020.3.6

DROP TABLE IF EXISTS SC
DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Course

CREATE TABLE Student          
 (	
 Sno CHAR(9) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
 Sname CHAR(20) UNIQUE,          /* Sname取唯一值*/
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 

CREATE TABLE  Course
 (	
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),               	                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  Course(Cno) /* 表级完整性约束条件， Cpno是外码，被参照表是自身*/
 ); 

CREATE TABLE  SC
 (
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                      /* 主码由两个属性构成，必须作为表级完整性进行定义*/
 FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件，Sno是外码，被参照表是Student*/
 FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件，Cno是外码，被参照表是Course*/
 ); 


INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);

SELECT * FROM Student

INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('7','Pascal语言',NULL,4);

UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 

SELECT * FROM Course

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);

SELECT * FROM SC

执行后，效果如下图所示：

第六版

--Edit by David @ HeBei University 2025.3.12
 
DROP TABLE IF EXISTS SC
DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Course
 
--[例3.5]  建立“学生”表Student
CREATE TABLE Student          
      (		Sno   		CHAR(8)		PRIMARY KEY,                                                           
        	Sname 		VARCHAR(20) UNIQUE,          
        	Ssex    	CHAR(6),
        	Sbirthdate 	Date,
        	Smajor  	VARCHAR(40)
      ); 

--[例3.6 ] 建立一个“课程”表Course
CREATE TABLE  Course
      (		Cno			CHAR(5)		PRIMARY KEY,
        	Cname  		VARCHAR(40) NOT NULL,            
          	Ccredit  	SMALLINT,
          	Cpno     	CHAR(5),
          	FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
       ); 

--[例3.7] 建立“学生选课”表SC
CREATE TABLE SC
 	 (		Sno 			CHAR(8),
   			Cno 			CHAR(5),
   			Grade 			SMALLINT,                
			Semester 		CHAR(5),         
   			Teachingclass 	CHAR(8),        
			PRIMARY KEY (Sno,Cno),    	
    		FOREIGN KEY (Sno) REFERENCES Student(Sno),
			FOREIGN KEY (Cno) REFERENCES Course(Cno)
	);
 
 
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180001','李勇','男','2000-3-8','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180002','刘晨','女','1999-9-1','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180003','王敏','女','2001-8-1','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180004','张立','男','2000-1-8','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180005','陈新奇','男','2001-11-1','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180006','赵明','男','2000-6-20','计算机科学与技术');
INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate ,Smajor) VALUES('20180007','王佳佳','女','2001-12-7','计算机科学与技术');
 
SELECT * FROM Student
 
INSERT INTO Course VALUES('81001','程序设计基础与C语言',4,null);
INSERT INTO Course VALUES('81002','数据结构',4,'81001');
INSERT INTO Course VALUES('81003','数据库系统概论',4,'81002');
INSERT INTO Course VALUES('81004','信息系统概论',4,'81003');
INSERT INTO Course VALUES('81005','操作系统',4,'81001');
INSERT INTO Course VALUES('81006','Python语言',3,'81002');
INSERT INTO Course VALUES('81007','离散数学',4,null);
INSERT INTO Course VALUES('81008','大数据技术概论',4,'81003');
 
SELECT * FROM Course
 
INSERT INTO SC VALUES('20180001','81001',85,'20192','81001-01');
INSERT INTO SC VALUES('20180001','81002',96,'20201','81002-01');
INSERT INTO SC VALUES('20180001','81003',87,'20202','81003-01');
INSERT INTO SC VALUES('20180002','81001',80,'20192','81001-02');
INSERT INTO SC VALUES('20180002','81002',98,'20201','81002-01');
INSERT INTO SC VALUES('20180002','81003',71,'20202','81003-02');
INSERT INTO SC VALUES('20180003','81001',81,'20192','81001-01');
INSERT INTO SC VALUES('20180003','81002',76,'20201','81002-02');
INSERT INTO SC VALUES('20180004','81001',56,'20192','81001-02');
INSERT INTO SC VALUES('20180004','81003',97,'20201','81002-02');
INSERT INTO SC VALUES('20180005','81003',68,'20202','81003-01');
 
SELECT * FROM SC