数据库系统概论-实验一：SQL定义功能、数据插入

实验一：SQL定义功能、数据插入  

1．建立教学数据库的三个基本表：

S(Sno,Sname,Ssex,Sage,Sdept)   学生（学号，姓名，性别，年龄，系）

SC(Sno,Cno,Grade)              选课（学号，课程号，成绩）

C(Cno,Cname,Cpno,Ccredit)      课程（课程号，课程名，先行课，学分）

2．DROP TABLE、ALTER TABLE、CREATE INDEX、DROP INDEX 及INSERT语句输入数

 

/*建立student表*/

CREATE TABLE S

(Sno CHAR(9) PRIMARY KEY,/*Sno是主码*/

Sname CHAR(20) UNIQUE,/*Sname取唯一值*/

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

)

/*建立course表*/

CREATE TABLE C

(Cno CHAR(4) PRIMARY KEY,/*Cno是主码*/

Cname CHAR(40) NOT NULL,/*Cname不能取空值*/

Cpno CHAR(4),/*Cpno的含义是先修课*/

Credit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES C(Cno)

/*表级完整性约束条件，Cpno是外码，被参照表是C，被参照列是Cno*/

)

/*创建SC表*/

CREATE TABLE SC

(Sno CHAR(9),

Cno CHAR(4),

Grade INT,

PRIMARY KEY (Sno,Cno),/*SC的主码由Sno和Cno两个属性构成*/

FOREIGN KEY (Sno) REFERENCES S(Sno),/*Sno是外码，被参照表是S*/

FOREIGN KEY (Cno) REFERENCES C(Cno)/*Cno是外码，被参照表是C*/

)

 

/*插入数据*/

/*先插入S表*/

INSERT

INTO  S(Sno,Sname,Ssex,Sage,Sdept)

VALUES('201215101','李一','男',16,'IS'),

('201215102','王二','男',18,'MA'),

('201215103','张三','女',21,'IS'),

('201215104','李四','男',19,'IS'),

('201215201','魏六','女',18,'IS'),

('201215202','刘七','女',17,'CS'),

('201215203','高八','男',22,'CS'),

('201215301','蔡九','男',18,'SC')

/*插入C表*/

INSERT

INTO  C

VALUES('1','数据库','5',4),

('2','数学',NULL,2),

('3','信息系统','1',4),

('4','操作系统','6',3),

('5','数据结构','7',4),

('6','数据处理',NULL,2),

('7','PASCAL语言','６',4),

('８','JAVA语言','６',2),

('９','上机实验',NULL,1)

/*插入SC表*/

INSERT

INTO  SC

VALUES('201215101','1',91),('201215101','2',92),

('201215101','3',93),('201215101','4',59),

('201215101','5',95),('201215101','6',60),

('201215101','7',NULL),('201215101','8',18),

('201215101','9',NULL ),

('201215102','1',71),('201215102','2',82),

('201215102','3',NULL ),('201215102','5',NULL ),

('201215102','6',55),('201215102','7',96),

('201215103','1',91),('201215103','4',91),

('201215103','6',90),('201215103','7',88),

('201215103','9',74),

('201215104','1',91),('201215104','2',62),

('201215104','3',75),('201215104','5',99),

('201215104','6',76),('201215104','7',NULL),

('201215104','8',88),('201215104','9',99),

('201215201','9',100),

('201215202','3',62),

('201215202','1',66),('201215202','7',79),

('201215202','4',NULL),('201215202','6',60),

('201215203','1',NULL),('201215203','2',NULL),

('201215203','3',NULL),('201215203','4',59),

('201215203','5',NULL),('201215203','6',60),

('201215203','7',NULL),('201215203','8',NULL),

('201215203','9',NULL ),

('201215301','1',100 ), ('201215301','2',99 ),

('201215301','3',95 ), ('201215301','4',81 ),

('201215301','5',67), ('201215301','6',100 ),

('201215301','7',88 ), ('201215301','8',96),

('201215301','9',100 )

/*查看表内容*/
SELECT * FROM S

SELECT * FROM C

SELECT * FROM SC

结果如下：

S表：

C表：

 

SC表：

 

/*使用ALTER修改表*/
ALTER TABLE S ADD Sentrance DATE/*在S表中新增加“入学时间”列，其数据类型为日期型*/

/*使用UPDATE修改元组*/
UPDATE S
SET Sdept='CS'
WHERE S.Sno='201215201'
SELECT * FROM S

/*使用UPDATE修改元组*/
UPDATE S
SET Sentrance='2012'

修改表后：

 

/*分别建立S，C，SC表的唯一索引*/
CREATE UNIQUE INDEX STU ON S(Sno)
CREATE UNIQUE INDEX COU ON C(Cno)
CREATE UNIQUE INDEX SCSCNO ON SC(Sno ASC,Cno DESC)

/*删除已建立S，C，SC表的唯一索引*/
DROP INDEX S.STU
DROP INDEX C.COU
DROP INDEX SC.SCSCNO