SQL语句练习

数据库和数据表操作

1.采用可视化软件建立数据库UNIVERSITY，其中包括六个数据表

2.采用SQL语言删除1中建立的数据库和数据表

 drop database university;

3.采用SQL语言建立数据库UNIVERSITY

CREATE DATABASE UNIVERSITY;

4.采用SQL语言创建UNIVERSITY数据库中的六个数据表

CREATE TABLE Department ( Dno INT, Dname VARCHAR ( 50 ), Daddress VARCHAR ( 50 ), PRIMARY KEY ( Dno ) );
CREATE TABLE Student (Sno CHAR ( 11 ),Sname CHAR ( 8 ),Ssex CHAR ( 2 ),Sage INT,Dno INT,PRIMARY KEY ( Sno ),FOREIGN KEY ( Dno ) REFERENCES department ( Dno ) );
CREATE TABLE Teacher (Tno INT PRIMARY KEY,Tname CHAR ( 8 ),Ttitle CHAR ( 8 ),Dno INT,FOREIGN KEY ( Dno ) REFERENCES department ( Dno ) );
CREATE TABLE course ( Cno INT PRIMARY KEY, Cname CHAR ( 50 ), Cpno INT, CCredit INT, FOREIGN KEY ( Cpno ) REFERENCES Course ( Cno ) );
CREATE TABLE SC (Sno CHAR ( 11 ),Cno INT,Grade INT,PRIMARY KEY ( Sno, Cno ),FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ),FOREIGN KEY ( Cno )REFERENCES Course ( Cno ) );
CREATE TABLE TC (Tno INT,Cno INT,Site CHAR ( 50 ),PRIMARY KEY ( Tno, Cno ),FOREIGN KEY ( Tno ) REFERENCES Teacher ( Tno ),FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) );

5.采用SQL语言为Student表的Sname建立唯一索引

CREATE UNIQUE INDEX stuname ON Student ( Sname );

6.采用SQL语言删除Student表Sname上的唯一索引

ALTER TABLE student DROP INDEX stuname；

7.采用SQL语言给Teacher表添加一个字段Tsex

ALTER TABLE Teacher ADD Tsex CHAR ( 1 );

8.采用SQL语言删除Teacher表中的字段Tsex

ALTER TABLE Teacher DROP COLUMN Tsex;

---

数据表的数据操作

1.分别采用UI界面和SQL语言为UNIVERSITY的Department表输入数据

INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (1, '地球科学学院', '主楼东');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (2, '资源学院', '主楼西');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (3, '材化学院', '材化楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (4, '环境学院', '文华楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (5, '工程学院', '水工楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (6, '地球物理学院', '物探楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (7, '机械与电子信息学院', '教二楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (8, '经济管理学院', '经管楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (9, '外语学院', '北一楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (10, '信息工程学院', 'NULL');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (11, ' 数学与物理学院', '基委楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (12, '珠宝学院', '珠宝楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (13, '政法学院', '政法楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (14, '计算机学院', '北一楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (15, '远程与继续教育学院', 'NULL');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (16, '国际教育学院', 'NULL');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (17, '体育部', '体育馆');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (18, '艺术与传媒学院', '艺传楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (19, '马克思主义学院', '保卫楼');
INSERT INTO `university`.`department` (`Dno`, `Dname`, `Daddress`) VALUES (20, '江城学院', '江城校区');

2.分别采用UI界面和SQL语言为UNIVERSITY的Student表输入数据

INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20081001197, '李子聪', 'M', 17, 5);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20081001266, '蔡景学', 'F', 19, 5);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20081001888, '赵林云', 'F', 20, 5);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20081003492, '易家新', 'M', 19, 5);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000231, '吕岩', 'M', 18, 14);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000481, '姜北', 'M', 17, 5);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000863, '孟飞', 'M', 19, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000934, '罗振俊', 'M', 19, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000961, '曾雪君', 'F', 18, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091000983, '巴翔', 'M', 19, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091001175, '周雷', 'M', 18, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091001261, '马欢', 'M', 17, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091001384, '陈亮', 'M', 20, 8);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091001598, '王海涛', 'M', 20, 14);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091003085, '袁恒', 'M', 18, 14);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20091004391, '颜容', 'M', 19, 14);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20101000199, '孙中孝', 'M', 18, 11);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20101000424, '杨光', 'M', 17, 11);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20101000481, '张永强', 'M', 16, 11);
INSERT INTO `university`.`student` (`Sno`, `Sname`, `Ssex`, `Sage`, `Dno`) VALUES (20101000619, '陈博', 'M', 20, 11);

3.分别采用UI界面和SQL语言为UNIVERSITY的Course表输入数据

INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (1, '数据库原理', 5, 4);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (2, '高等数学', NULL, 8);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (3, '信息系统', 1, 2);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (4, '操作系统', 5, 4);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (5, '数据结构', 6, 4);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (6, 'C语言程序设计', NULL, 4);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (7, '大学物理', NULL, 8);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (8, '大学化学', NULL, 3);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (9, '汇编语言', 6, 2);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (10, '软件工程', NULL, 2);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (11, '空间数据库', 1, 3);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (12, '美国简史', NULL, 2);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (13, '中国通史', NULL, 6);
INSERT INTO `course` (`Cno`, `Cname`, `Cpno`, `CCredit`) VALUES (14, '大学语文', NULL, 3);

4.分别采用UI界面和SQL语言为UNIVERSITY的Teacher表输入数据

INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (1, '何小峰', '副教授', 14);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (2, '刘刚才', '教授', 14);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (3, '李星星', '教授', 11);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (4, '翁正平', '讲师', 14);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (5, '李川川', '讲师', 14);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (6, '王媛媛', '讲师', 14);
INSERT INTO `teacher` (`Tno`, `Tname`, `Ttitle`, `Dno`) VALUES (7, '孔夏芳', '副教授', 14);

5.分别采用UI界面和SQL语言为UNIVERSITY的SC表输入数据

INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20081001197, 1, 79);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20081001266, 1, 97);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20081001888, 1, 60);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000481, 1, 78);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000863, 1, 98);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000863, 6, 90);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000934, 1, 89);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000934, 6, 90);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000961, 1, 85);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091000961, 6, 87);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20091003085, 1, 90);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20101000199, 1, 65);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20101000424, 11, 78);
INSERT INTO `sc` (`Sno`, `Cno`, `Grade`) VALUES (20101000481, 11, 69);

6.分别采用UI界面和SQL语言为UNIVERSITY的表输入数据

INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (1, 1, '教一楼407');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (1, 6, '教一楼307');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (2, 10, '教二楼217');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (3, 2, '教三楼507');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (4, 5, '教三楼208');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (5, 9, '教一楼207');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (6, 3, '综合楼207');
INSERT INTO `tc` (`Tno`, `Cno`, `Site`) VALUES (7, 4, '教二楼817');

7.采用SQL语言查询所有的学生信息

SELECT* FROM Student;

8.采用SQL语言查询所有女生姓名

SELECT* FROM Student WHERE Ssex = "F";

9.采用SQL语言查询各个院系学生人数

SELECT count(Sno) TotalNum ,Dno FROM Student GROUP BY Dno;

10.采用SQL语言查询各个院系老师人数

SELECT count(Tno) TotalNum ,Dno FROM Teacher GROUP BY Dno;

11.采用SQL语言查询所有选修了数据库原理且成绩在60~100分之间的学生的姓名和成绩，并按照成绩的降序排列

SELECT
	student.Sname,
	Grade 
FROM
	SC,
	Course,
	student 
WHERE
	SC.Cno = course.Cno 
	AND student.Sno = sc.Sno 
	AND course.Cname = "数据库原理" 
	AND SC.Grade BETWEEN 60 AND 100 
ORDER BY
	Grade DESC;

12.采用SQL语言编写一个连接查询，查询经济管理学院年龄在20岁以下的男生的姓名和年龄

SELECT
	Sname,
	Sage 
FROM
	student,
	department 
WHERE
	student.Dno = department.Dno 
	AND department.Dname = "经济管理学院" 
	AND student.Sage < 20;

13.采用SQL语言编写一个嵌套查询，查询选修课程总学分在5个学分以上的学生的姓名

SELECT * 
FROM(
	SELECT
		Sname,
		SUM( CCredit ) Total_Credit 
	FROM
		sc,
		course,
		student 
	WHERE
		student.Sno = SC.Sno 
		AND SC.Cno = course.Cno 
	GROUP BY
		SC.Sno 
	) AS A 
WHERE
	Total_Credit > 5;

不用嵌套，用HAVING 子句也可以

SELECT
	Sname,
	SUM( CCredit ) Total_Credit 
FROM
	sc,
	course,
	student 
WHERE
	student.Sno = SC.Sno 
	AND SC.Cno = course.Cno 
GROUP BY
	SC.Sno 
HAVING
	SUM( CCredit )> 5;

14.采用SQL语言编写一个嵌套查询，查询各门课程的最高成绩的学生姓名及成绩

SELECT
	course.Cname,
	#course.cno,
	Sname,
	MAX( Grade ) Max_Grade 
FROM
	sc,
	course,
	student 
WHERE
	student.Sno = SC.Sno 
	AND SC.Cno = course.Cno 
GROUP BY
	SC.Cno;

15.采用SQL语言查询所有选修了何小峰老师开设课程的学生姓名及其所在院系名称。

SELECT
	Sname,Dname
FROM
	SC,
	student,
	department 
WHERE
	SC.Sno = student.Sno 
	AND student.Dno = department.Dno 
	AND SC.Cno IN 
	( SELECT Cno FROM teacher, course 
	WHERE teacher.Tno = course.Cpno 
	AND teacher.Tname = "何小峰" 
	);

16.采用SQL语言，在数据库中删除学号为20091003085的学生的所有信息（包括其选课记录）

DELETE　FROM　 SC,
Student　 
WHERE
	SC.Sno = student.Sno 
	AND student.Sno = "20091003085";

17.采用SQL语言，将学号为20091000863的学生的“数据库原理”这门课的成绩修改为80分。

UPDATE SC 
SET grade = 80 
WHERE
	Sno = "20091000863" 
	AND Cno = ( SELECT Cno FROM COurse WHERE Cname = "数据库原理" );

---

视图的创建和使用

1.使用数据库管理系统的操作界面创建一个计算机学院的学生信息视图CSS，并新加入一条学生记录（20191000911，钟晓年，M，16）；然后修改该学生年龄为18岁，最后删除该视图。

2.采用SQL语言完成上一内容

新建视图

CREATE VIEW CSS AS SELECT Sno,Sname,Ssex,Sage 
FROM Student WHERE Dno = '14';

插入数据

INSERT INTO Css VALUES( "20191000911", "钟晓年", 'M', '16' );

实际操作发现Mysql在插入上述数据时，并没有将Dno设置为14，而是设置为了NULL，所以为了避免出错，将其手动修改为14

UPDATE student SET Dno = 14 WHERE Sno = 20191000911

修改年龄

UPDATE CSS  SET Sage = 18 WHERE Sno = 20191000911

删除数据

DELETE FROM css WHERE Sno = 20191000911

删除视图

DROP VIEW CSS;

3.采用SQL语言创建一个计算机学院教师视图CST

CREATE VIEW CST AS SELECT
Tno,Tname,Ttitle 
FROM
	Teacher 
WHERE
	Dno = ( SELECT Dno FROM department WHERE Dname = "计算机学院" );

4.采用SQL语言构建一个用到CSS和CST视图的查询，查询所有选修了计算机学院老师开设课程的计算机学院的学生姓名。

SELECT-- 3.最后在满足条件的学生中查找计算机学院的学生，返回姓名
Sname 
FROM CSS,
	(
		SELECT-- 2.再根据课程号找到选修了这些课程的学生
		* 
	FROM
		sc 
	WHERE
		cno IN (
			SELECT-- 1.先找到计算机学院老师开设的课程号
			cno 
		FROM
			course 
		WHERE
		Cpno IN ( SELECT Tno FROM cst ))) AS s 
WHERE
	s.sno = css.sno;

5.采用SQL语言写出在CST视图中删除教师编号为1 的记录，执行并观察结果。

DELETE FROM CST WHERE Tno = 1;

结果：不能直接删除编号为1的教师，因为TC表的Tno的外码是该列Tno。

---

安全性：

1.新建七个用户U1~U7，并将连接权限赋予这几个用户。

新建用户

CREATE USER U1 IDENTIFIED BY "test";
CREATE USER U2 IDENTIFIED BY "test";
CREATE USER U3 IDENTIFIED BY "test";
CREATE USER U4 IDENTIFIED BY "test";
CREATE USER U5 IDENTIFIED BY "test";
CREATE USER U6 IDENTIFIED BY "test";
CREATE USER U7 IDENTIFIED BY "test";

查看用户是否创建成功

Mysql中没有赋予连接connect这种权限。

2.将Student表的查询权限授予U1

GRANT SELECT ON student TO U1;

3.把Student表和Course表的全部权限授予U2和U3

GRANT ALL ON Student TO U2,U3;
GRANT ALL ON Course  TO U2,U3;

4.将SC表的查询权限授予所有用户

GRANT SELECT ON sc TO U1,U2,U3,U4,U5,U6,U7;

5.将SC表的更新和查询权限授予U4

GRANT SELECT,UPDATE ON sc TO U4;

6.将SC表的插入权限授予U5，并允许将此权限再授予其他用户

GRANT INSERT ON SC TO U5 WITH GRANT OPTION;

7.用U5连接数据库服务器，将SC表的插入权限授予U6，不允许U6再转授

GRANT INSERT ON SC TO U6;

8.用U6 连接数据库服务器，将SC表的插入权限授予U7，查看是否授权成功。

GRANT INSERT ON SC TO U7;

报错：

9.收回U4修改SC表的权限

REVOKE UPDATE ON SC FROM U4;

10.收回所有用户对SC表的查询权限

REVOKE INSERT ON SC FROM U1,U2,U3,U4,U5,U6,U7;

---

完整性：

1.在UNIVERSITY数据库中创建表STU_T,该表与Student有相同字段，主码为Sno。

a.创建STU_T表时定义完整性（列级实体完整性），然后删除STU_T

CREATE TABLE STU_T ( Sno CHAR ( 11 ) PRIMARY KEY, Sname CHAR ( 8 ), Ssex CHAR ( 2 ), Sage INT, Dno INT );

删除

DROP TABLE STU_T;

b.创建STU_T表时定义完整性（表级实体完整性），然后删除STU_T

CREATE TABLE STU_T (
	Sno CHAR ( 11 ),
	Sname CHAR ( 8 ),
	Ssex CHAR ( 2 ),
	Sage INT,
	Dno INT,
	PRIMARY KEY ( Sno )
);

删除

DROP TABLE STU_T;

c.创建STU_T表后再定义其实体完整性PK_SNO（提示：采用alter命令添加实体完整性）

CREATE TABLE STU_T (
	Sno CHAR ( 11 ),
	Sname CHAR ( 8 ),
	Ssex CHAR ( 2 ),
	Sage INT,
	Dno INT
);


ALTER TABLE stu_T ADD CONSTRAINT pk_sno PRIMARY KEY ( Sno );

d.删除数据表STU_T中的实体完整性PK_SNO，然后删除STU_T

Mysql中不支持删除constraint

DROP TABLE STU_T;

2.在UNIVERSITY数据库中创建表SC_T，该表与SC表具有相同字段，其主码为Sno，Cno

a.创建S_T表时定义完整性（表级实体完整性），然后删除SC_T

CREATE TABLE SC_T (
	Sno CHAR ( 11 ),
	Cno INT,
	Grade INT,
	PRIMARY KEY ( Sno, Cno )
);

删除

DROP TABLE SC_T;

b.创建SC_T表后再定义其实体完整性PK_SC（提示：采用alter命令添加实体完整性），然后删除数据表SC_T中的实体完整性PK_SC，并删除SC_T

CREATE TABLE SC_T (
	Sno CHAR ( 11 ),
	Cno INT,
	Grade INT
);

ALTER TABLE SC_T ADD CONSTRAINT pk_sc PRIMARY KEY ( Sno, Cno );

删除

DROP TABLE SC_T;

3.在UNIVERSITY数据库中创建表TC_T，该表与TC表具有相同字段，其主码为Tno，Cno

a.创建TC_T表时定义实体完整性和参照完整性（采用表级实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno），然后删除TC_T表。

CREATE TABLE TC_T (
	Tno INT,
	Cno INT,
	Site CHAR ( 50 ),
	PRIMARY KEY ( Tno, Cno )
);

删除

DROP TABLE TC_T;

b.创建TC_T表后再定义其实体完整性和参照完整性（提示：采用alter命令增加实体完整性和参照完整性，被参照的数据表分别为Teacher表中的Tno和Course表中的Cno)。

CREATE TABLE TC_T (
	Tno INT,
	Cno INT,
	Site CHAR ( 50 )
);

ALTER TABLE TC_T add CONSTRAINT pk_a PRIMARY KEY(Tno,Cno);
ALTER TABLE TC_T add CONSTRAINT pk_b FOREIGN KEY(Tno) REFERENCES teacher(Tno);
ALTER TABLE TC_T add CONSTRAINT pk_c FOREIGN KEY(Cno) REFERENCES course(Cno);

4.在UNIVERSITY数据库中创建表DEP_T，该表与Department表具有相同字段，其主码为Dno

a.创建DEP_T表时定义实体完整性（列级实体完整性），并定义Dname唯一且非空，Daddress的默认值为“北一楼”，然后删除DEP_T。

CREATE TABLE DEP_T
 ( 
Dno INT PRIMARY KEY, 
Dname VARCHAR ( 50 ), 
Daddress VARCHAR ( 50 ) DEFAULT ( "北一楼" ) 
);

删除

DROP TABLE DEP_T;

b.创建DEP_T表时定义实体完整性（表级实体完整性），并限制Dno的取值范围为00~99（提示：使用Check进行约束），然后删除DEP_T。

CREATE TABLE DEP_T
 ( 
Dno INT , 
Dname VARCHAR ( 50 ), 
Daddress VARCHAR ( 50 ) , 
PRIMARY KEY(Dno),
check(Dno<100 AND Dno>0)
);

数据库设计：

设计一个大学教学信息管理应用数据库UNIVERSITY。其中，一个教授属于一个系，一个系有多名教师，每个系都有自己的办公地点。每个教师可以讲授多门课程，每个学生属于一个系，可以选修多门课程。每门课程具有一定的学分，并可能有先导课程。

1.数据库概念结构设计

根据描述，识别出四个实体：教师、系、课程、学生
其中：教师与系是一对多的关系，教师与课程是一对多的关系。
系与学生是一对多的关系、学生与课程是多对多的关系
根据以上信息，可以画出E-R图：

2.数据库逻辑结构

根据E-R图以及概念结构向逻辑结构转换规则，设计逻辑结构如下：

UNIVERSITY关系数据库模式：

系的信息表Department(Dno,Dname,Daddress)
学生信息表Student(Sno,Sname,Ssex,Sage,Dno)
教师信息表Teacher(Tno,Tname,Ttitlt,Dno) 课程信息表Course(Cno,Cname,Cpno,Ccredit)
选课信息表SC(Sno,Cno,Grade) 授课信息表TC(Tno,Cno,Site)

3.数据库物理结构设计

CREATE TABLE Department ( Dno INT, Dname VARCHAR ( 50 ), Daddress VARCHAR ( 50 ), PRIMARY KEY ( Dno ) );
CREATE TABLE Student (Sno CHAR ( 11 ),Sname CHAR ( 8 ),Ssex CHAR ( 2 ),Sage INT,Dno INT,PRIMARY KEY ( Sno ),FOREIGN KEY ( Dno ) REFERENCES department ( Dno ) );
CREATE TABLE Teacher (Tno INT PRIMARY KEY,Tname CHAR ( 8 ),Ttitle CHAR ( 8 ),Dno INT,FOREIGN KEY ( Dno ) REFERENCES department ( Dno ) );
CREATE TABLE course ( Cno INT PRIMARY KEY, Cname CHAR ( 50 ), Cpno INT, CCredit INT, FOREIGN KEY ( Cpno ) REFERENCES Course ( Cno ) );
CREATE TABLE SC (Sno CHAR ( 11 ),Cno INT,Grade INT,PRIMARY KEY ( Sno, Cno ),FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ),FOREIGN KEY ( Cno )REFERENCES Course ( Cno ) );
CREATE TABLE TC (Tno INT,Cno INT,Site CHAR ( 50 ),PRIMARY KEY ( Tno, Cno ),FOREIGN KEY ( Tno ) REFERENCES Teacher ( Tno ),FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) );

存储过程与函数：

1.定义一个无参数的存储过程DecreaseGrade，更新所有学生成绩，将其降低5%，并调用该存储过程。

命令行客户端中，如果有一行命令以delimiter( 默认是分号; )结束，那么回车后，mysql将会执行该命令。为了避免语句在中途被执行需要 事先把delimiter换成其它符号，这里换成了 ￥￥

DELIMITER $
CREATE PROCEDURE DecreaseGrade()
BEGIN 
update SC set Grade=Grade*0.95;
END
$
DELIMITER ;

CALL DecreaseGrade;

2.定义一个带输入参数的存储过程IncreaseGrade，将课程号为1的所有学生成绩提升5%；要求课程号作为存储过程参数传入，并调用该存储过程。

DELIMITER $
CREATE PROCEDURE IncreaseGrade(IN ccno INT)
BEGIN 
update SC set Grade=Grade*1.05 WHERE Cno=ccno;
END
$
DELIMITER ;


CALL IncreaseGrade(1)

3.定义一个带有输入和输出参数的存储过程AverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，以计算结果（该生的所有选修课平均成绩）为输出参数，调用该存储过程，并输出计算结果。

DELIMITER $
CREATE PROCEDURE AverageStudentGrade(IN xno VARCHAR(20),OUT xgrade FlOAT)
BEGIN 
DECLARE g FLOAT DEFAULT 0.0;
SELECT sg.ag INTO g 
FROM (
    SELECT Sno s,AVG(Grade) ag
    FROM SC GROUP BY Sno
)sg
WHERE sg.g = xno;
END
$
DELIMITER ;

CALL AverageStudentGrade(20091000863,@g);

4.删除过程IncreaseGrade和DecreaseGrade。

DROP PROCEDURE DecreaseGrade;
DROP PROCEDURE IncreaseGrade;

5.定义一个带有输入参数的自定义函数CalculateAverageStudentGrade，计算一个学生的所有选修课程的平均成绩，要求以学号为输入参数，返回该生的所有选修课平均成绩，调用函数，并输出计算结果。

DELIMITER $
CREATE FUNCTION CalculateAverageStudentGrade(xno VARCHAR(11))
RETURNS INT 
BEGIN 
    DECLARE g INT;
    SELECT sg.ag INTO g 
    FROM(
        SELECT Sno s,AVG(Grade) ag
        FROM SC GROUP BY Sno   
    )sg
    WHERE sg.g=xno;
    RETURN g;
END
$
DELIMITER ;

CALL CalculateAverageStudentGrade('20091000863')

6.删除函数CalculateAverageStudentGrade

DROP FUNCTION CalculateAverageStudentGrade;

7.定义一个存储过程，采用普通无参游标实现计算机学院开设的所有课程的学分之和。

DELIMITER $
CREATE PROCEDURE CS_Total_Credit()
BEGIN 
DECLARE credit INT;
DECLARE credit_sum INT DEFAULT FALSE;
DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT CCredit FROM Course;
DECLARE continue HANDLER FOR NOT found SET done = TRUE;
OPEN cur;
    read_loop:loop
        FETCH cur INTO credit;
        IF done THEN 
            LEAVE read_loop;
        END IF;
        SET credit_sum=credit_sum+credit;
    END loop;
CLOSE cur;
SELECT credit_sum;
END
$
DELIMITER ;



CALL CS_Total_Credit();

8.定义一个存储过程，采用REF CURSOR实现计算机学院所有学生选修课程的成绩之和。

DELIMITER $
CREATE PROCEDURE CS_Total_Stu_Credit()
BEGIN 
DECLARE grade int;
DECLARE grade_sum int DEFAULT 0;
DECLARE done int DEFAULT FALSE;
DECLARE cur_grade CURSOR FOR SELECT Grade FROM SC;
DECLARE CONTINUE HANDLER FOR NOT found SET done=TRUE;
OPEN cur_grade;
read_loop:LOOP
    FETCH cur_grade INTO grade;
    IF done THEN 
        LEAVE read_loop;
    END IF;
    SET grade_sum = grade_sum+grade;
END LOOP;
CLOSE cur_grade;
SELECT grade_sum;
END
$
DELIMITER ;


CALL CS_Total_Stu_Credit();

9.定义一个存储过程，采用带参数的游标实现按照学号计算学生的平均成绩。

DELIMITER $
CREATE PROCEDURE average_grade(IN sno_in char(11))
BEGIN
DECLARE avg_grade int DEFAULT 0;
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT sg.ag
    FROM
        (SELECT Sno s,avg(Grade) ag
        FROM SC GROUP BY Sno )sg
    WHERE sg.s = sno_in;
DECLARE CONTINUE HANDLER FOR NOT found SET done=TRUE;
OPEN cur;
read_loop:LOOP
    FETCH cur INTO avg_grade;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SELECT avg_grade;
END LOOP;
CLOSE cur;

END
$
DELIMITER ;

CALL average_grade('20091000863');