数据库实验一作业

2019-05-15

实验一：数据定义/数据操纵语言

一、实验目的
熟悉SQL Server上机环境以及SQL Server客户端的配置；熟练掌握和使用DDL语言，建立、修改和删除数据库表、主键、外键约束关系和索引；熟练掌握和使用DML语言，对数据进行增加、修改和删除操作。
二、学时数
三、实验内容
SQL数据定义语句， SQL数据操纵语句。
四、实验要求
1．熟悉SQL Server上机环境；
2．建立数据库表，修改数据库表结构，建立、删除索引。
教学管理数据库各表的数据结构和数据如下： CS：计算机系 MA：数学系 IS：信息系

SQL数据定义语句

1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。

-- 创建表student 
CREATE TABLE Student (
	SNO VARCHAR(5)
	,SNAME VARCHAR(8)
	,SDEPT VARCHAR(2) NOT NULL
	,SCLASS VARCHAR(2) NOT NULL
	,SAGE VARCHAR(2)
	,PRIMARY KEY (SNO)
	);

-- 创建表course 
CREATE TABLE Course (
	cno VARCHAR(3)
	,cname VARCHAR(16) NOT NULL UNIQUE 
	,ctime VARCHAR(3)
	,PRIMARY KEY (cno)
	);

-- 创建表Teach
CREATE TABLE teach (
	tname VARCHAR(8)
	,tsex VARCHAR(2)
	,cno VARCHAR(3)
	,tdate date 
	,tdept VARCHAR(2)
	,PRIMARY KEY (
		tname
		,cno
		,tdept
		)
	,FOREIGN KEY (CNO) REFERENCES COURSE(CNO)
	);

-- 创建表score 
CREATE TABLE score (
	SNO  VARCHAR(5)
	,CNO VARCHAR(3)
	,score NUMERIC(5,2)
	,PRIMARY KEY (
		SNO
		,CNO
		)
	,FOREIGN KEY (SNO) REFERENCES Student(SNO)
	,FOREIGN KEY (CNO) REFERENCES COURSE(cno)
	);

1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。

ALTER TABLE student ADD SSEX varchar(2);

1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空

/** mysql不支持下面语句修改表中的列属性 sqlserver语句
ALTER TABLE student ALTER COLUMN SNAME varchar(10) NUT NULL;**/   
-- mysql使用modify关键字修改列属性
ALTER TABLE student MODIFY SNAME varchar(10) NOT NULL;

1-4： (建立索引) 为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。

CREATE INDEX SC_NDXSNO ON score(sno asc); 
CREATE INDEX SC_NDXCNO ON score(cno desc);                      
-- 为Score表按分数降序建立索引，索引名为GRADE_NDX。 
CREATE INDEX GRADE_NDX ON score(score desc);  
-- sql语句创建普通索引CREATE  INDEX SC_NDXSNO ON student(sno asc);
-- sql语句创建组合索引CREATE  INDEX SC_NDXSNO ON student(sno,cno desc) 
-- sql语句创建唯一索引CREATE UNIQUE  INDEX SC_NDXSNO ON student(sno asc);
-- 显示索引信息SHOW INDEX FROM score;

1-5： (删除索引) 删除索引SC_NDX_SNO。

DROP   INDEX GRADE_NDX ON score;

SQL数据操纵语句:

2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。

-- 在表student添加数据
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('96001','马小燕','CS','01','女','21');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('96002','黎明','CS','01','男','18');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('96003','刘东明','MA','01','男','18');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('96004','赵志勇','IS','02','男','20');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('97001','马蓉','MA','02','女','19');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('97002','李成功','CS','01','男','20');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('97003','黎明','IS','03','女','19');	
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('97004','李丽','CS','02','女','19');
insert into student(SNO,SNAME,SDEPT,SCLASS,SSEX,SAGE) values('96005','司马志明','CS','02','男','18');	
-- 在表course中添加数据
insert into course(CNO,CNAME,CTIME) values('001','数学分析','144');
insert into course(CNO,CNAME,CTIME) values('002','普通物理','144');
insert into course(CNO,CNAME,CTIME) values('003','微机原理','80');
insert into course(CNO,CNAME,CTIME) values('004','数据结构','72');
insert into course(CNO,CNAME,CTIME) values('005','操作系统','80');
insert into course(CNO,CNAME,CTIME) values('006','数据库原理','80');
insert into course(CNO,CNAME,CTIME) values('007','编译原理','60');
insert into course(CNO,CNAME,CTIME) values('008','程序设计','40');		
-- 在表Teach添加数据
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('王成刚','男','004','1999.9.5','CS');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('李正科','男','003','1999.9.5','CS');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('严敏 ','女','001','1999.9.5','MA');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('赵高','男','004','1999.9.5','IS');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('李正科','男','003','2002.2.23','MA');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('刘玉兰','女','006','2002.2.23','CS');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('王成刚','男','004','2002.2.23','IS');
insert into teach(TNAME,TSEX,CNO,TDATE,TDEPT) values('马悦','男','008','2000.9.6','CS');
-- 在表Score添加数据
insert into score(SNO,CNO,SCORE) values('96001','001','77.5');
insert into score(SNO,CNO,SCORE) values('96001','003','89');
insert into score(SNO,CNO,SCORE) values('96001','004','86');
insert into score(SNO,CNO,SCORE) values('96001','005','82');
insert into score(SNO,CNO,SCORE) values('96002','001','88');
insert into score(SNO,CNO,SCORE) values('96002','003','92.5');
insert into score(SNO,CNO,SCORE) values('96002','006','90');
insert into score(SNO,CNO,SCORE) values('96005','004','92');
insert into score(SNO,CNO,SCORE) values('96005','005','90');
insert into score(SNO,CNO,SCORE) values('96005','006','89');
insert into score(SNO,CNO,SCORE) values('96005','007','76');
insert into score(SNO,CNO,SCORE) values('96003','001','69');
insert into score(SNO,CNO,SCORE) values('97001','001','96');
insert into score(SNO,CNO,SCORE) values('97001','008','95');
insert into score(SNO,CNO,SCORE) values('96004','001','87');
insert into score(SNO,CNO,SCORE) values('96003','003','91');
insert into score(SNO,CNO,SCORE) values('97002','003','91');
insert into score(SNO,CNO)		 values('97002','004');
insert into score(SNO,CNO,SCORE) values('97002','006','92');
insert into score(SNO,CNO,SCORE) values('97004','005','90');
insert into score(SNO,CNO,SCORE) values('97004','006','95');

2-2： (多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表S1中。

CREATE table S1 LIKE student;
INSERT INTO S1
SELECT * FROM student
WHERE SDEPT='CS';

2-4： (修改数据) 将S1表中所有学生的年龄加2。

UPDATE S1 
SET SAGE = SAGE + 2;

2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。

UPDATE Course 
SET CTIME =
(SELECT a.ctime from(SELECT CTIME FROM Course WHERE CNAME='数据结构') as a
)
WHERE CNAME='程序设计';

2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因

INSERT INTO Score VALUES('98001','001','95');
-- 原因：违反完整约束条件,未找到父项关键字（主表里面没有98001）

2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。

INSERT INTO Score VALUES ('97001','010','80');
-- 原因：违反唯一约束条件（97001已存在）

2-8： (删除数据) 删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。

DELETE SCORE WHERE SNO='96001';
-- 原因：违反完整约束条件，已找到子记录（没有删除子表记录的情况下，删除主表记录，触发了完整性约束错误。）