数据库原理实验

实验二

1 2	-- 创建数据库 CREATE DATABASE EXP02

SNo	SN	SEX	MC	BT	PhoneNo	BD
0433	张艳	女	生物 04	1986-9-13		1986-09-13
0496	李越	男	电子 04	1984-2-23	1381290xxxx	1984-02-23
0529	赵欣	男	会计 05	1984-1-27	1350222xxxx	1984-01-27
0531	张志国	男	生物 05	1986-9-10	1331256xxxx	1986-09-10
0538	于兰兰	女	生物 05	1984-2-20	1331200xxxx	1984-02-20
0591	王丽丽	女	电子 05	1984-3-20	1332080xxxx	1984-03-20
0592	王海强	男	电子 05	1986-11-1		1986-11-01

-- 创建学生表
create table 学生表
(
	SNo char(4) NOT NULL CONSTRAINT S_Prim PRIMARY KEY,
	SN  NVARCHAR(10) NOT NULL,
	SEX NCHAR(1) DEFAULT N'男',
	MC NVARCHAR(10) NOT NULL,
	BT VARCHAR(10),
	PhoneNo VARCHAR(11) 
)
-- 创建课程表
CREATE TABLE 课程表
(
	CNo VARCHAR(4) NOT NULL CONSTRAINT C_Prim PRIMARY KEY,
	CN  NVARCHAR(10) NOT NULL,
	CREDITS FLOAT NOT NULL,
	SP SMALLINT NOT NULL,
	TN NVARCHAR(10) NOT NULL
)
-- 创建学生作业表
CREATE TABLE 学生作业表
(
	CNo VARCHAR(4) NOT NULL,
	SNo CHAR(4) NOT NULL CONSTRAINT SW_Fore FOREIGN KEY REFERENCES 学生表(SNo),
	G1 TINYINT NULL,
	G2 TINYINT NULL,
	G3 TINYINT NULL
)

学生表
课程表
学生作业表

实验三

1. 查询学生学号、班级和姓名

1	SELECT SNo, MC, SN FROM 学生表

EXP3-1

1	SELECT CNo, CN, CREDITS, SP, TN FROM 课程表

EXP3-2

3.
1
SELECT MC FROM 学生表

1 2	SELECT CNo, CN, CREDITS, SP,TN FROM 课程表 WHERE (SP>60)

EXP3-4

1 2	SELECT SNo, SN, BT FROM 学生表 WHERE BT LIKE '1986%'

EXP3-5

1 2	SELECT SNo, CNo FROM 学生作业表 WHERE (G1 >= 80) AND (G2 >= 80) AND (G3 >= 80)

EXP3-6

1 2	SELECT SNo, SN, MC FROM 学生表 WHERE SN LIKE N'张%'

EXP3-7

1 2	SELECT SNo, SN, SEX, MC, BT, PhoneNo FROM 学生表 WHERE (SEX = N'男') AND (MC LIKE '___05')

EXP3-8

1 2	SELECT SNo, CNo FROM 学生作业表 WHERE ( G1 IS NULL) OR (G2 IS NULL) OR (G3 IS NULL)

EXP3-9

10.

1 2	SELECT SUM(G1) AS 总分 FROM 学生作业表 WHERE (SNo = '0538')

EXP3-10

11.

1 2	SELECT COUNT(SNo) AS 人数 FROM 学生作业表 WHERE (CNo = 'K001')

EXP3-11

12.

1	SELECT COUNT(MC) AS 班级数 FROM 学生表

EXP3-12

13.

SELECT SNo,
CAST(AVG(G1*1.0) AS DECIMAL(3,1)) 作业1平均分,
CAST(AVG(G2*1.0) AS DECIMAL(3,1)) 作业2平均分,
CAST(AVG(G3*1.0) AS DECIMAL(3,1)) 作业3平均分
FROM 学生作业表
GROUP BY SNo
HAVING (COUNT(*) >=3)

EXP3-13

1. 查询于兰兰的选课信息，列出学号、姓名、课程名（两种连接查询方法）。

/* 内连接查询*/
SELECT 学生表.SNo, SN, CN
FROM 学生表,课程表,学生作业表
WHERE 学生表.SNo = 学生作业表.SNo
AND 课程表.CNo = 学生作业表.CNo
AND SN = N'于兰兰'

/* (2). 外连接查询 */
SELECT 学生表.SNo, SN, CN
FROM 学生表
LEFT OUTER JOIN 学生作业表
ON 学生表.SNo = 学生作业表.SNo
LEFT OUTER JOIN 课程表
ON 课程表.CNo = 学生作业表.CNo
WHERE SN = N'于兰兰'

EXP3-14

实验四

1（1）. 查询与张国志同班级的学生信息

/* 自连接查询 */ 
SELECT S2.SNo, S2.SN, S2.SEX, S2.MC, S2.BT, S2.PhoneNo
FROM 学生表 AS S1 ,学生表	AS S2
WHERE S1.SN = N'张志国' AND S2.MC = S1.MC

/* 子查询 */
SELECT SNo, SN, SEX, MC, BT, PhoneNo FROM 学生表
WHERE MC = (SELECT MC FROM 学生表
			WHERE SN = N'张志国')

EXP4-1(1)

1.(2)

/* 自连接查询 */ 
SELECT C2.CNo, C2.CN, C2.CREDITS, C2.SP, C2.TN
FROM 课程表 AS C1, 课程表 AS C2
WHERE C1.CN = N'计算机应用基础' AND C2.SP > C1.SP

/* 普通子查询 */
SELECT CNo, CN, CREDITS, SP, TN FROM 课程表
WHERE SP > (SELECT SP FROM 课程表
			WHERE CN = N'计算机应用基础')

EXP4-1(2)

1.(3)

1
2
3

/* 连接查询 */
SELECT 学生表.SNo, SN FROM 学生表,学生作业表
WHERE 学生表.SNo = 学生作业表.SNo AND 学生作业表.CNo = 'K002'

/* 普通子查询 */
SELECT SNo, SN FROM 学生表
WHERE (SNo = ANY (SELECT SNo
				  FROM 学生作业表
				  WHERE CNo = 'K002'))

/* 相关子查询 */
SELECT DISTINCT SNo, SN
FROM 学生表
WHERE ('K002' IN ( SELECT CNo
				FROM 学生作业表
				WHERE SNo = 学生表.SNo))

/* 使用exists关键字的相关子查询 */
SELECT SNo, SN
FROM 学生表
WHERE EXISTS (SELECT * FROM 学生作业表
			  WHERE SNo = 学生表.SNo AND CNo = 'K002')

EXP4-1(3)

1.(4)

SELECT SNo, CNo, G1, G2, G3
FROM 学生作业表
WHERE (CNo <> ALL (SELECT CNo FROM 学生作业表
			  WHERE CNo = 'K001' OR CNo = 'M001'))

EXP4-1(4)

2.(1)

1
2
3

INSERT INTO 学生表 (SNo, SN, SEX, MC)
VALUES ('0593',N'张乐', N'男', N'电子 05')
SELECT SNo, SN, SEX, MC, BT, PhoneNo FROM 学生表

EXP4-2(1)

2.(2)

1
2
3

UPDATE 课程表
SET CREDITS = CREDITS * 2
SELECT CNo, CN, CREDITS, SP, TN FROM 课程表

EXP4-2(2)

2.(3)

1
2
3

DELETE FROM 学生表
WHERE SN =N'张乐'
SELECT SNo, SN, SEX, MC, BT, PhoneNo FROM 学生表

EXP4-2(3)

实验五

创建电子05的学生视图

CREATE VIEW Sub_DZ05
AS SELECT SNo, SN, SEX, MC, BT
FROM 学生表
WHERE MC = N'电子 05'

显示结果

1
2
3

/* 查询视图 Sub_DZ05 */
SELECT SNo,SN,SEX,MC,BT
FROM Sub_DZ05

创建生物05的学生作业情况视图

CREATE VIEW ZY_SW05
AS SELECT 学生表.SNo, SN, CN, G1, G2, G3
from 学生表, 课程表, 学生作业表
WHERE 学生表.SNo = 学生作业表.SNo AND 
课程表.CNo = 学生作业表.CNo AND
学生表.MC = N'生物 05'

显示结果

1
2
3

/* 查询视图 ZY_SW05 */
SELECT SNo, SN, CN, G1, G2, G3
FROM ZY_SW05

创建学生作业平均成绩视图

CREATE VIEW AVGGRADE(SNo, A1, A2, A3)
AS SELECT 学生作业表.SNo, AVG(G1), AVG(G2), AVG(G3)
FROM 学生作业表
GROUP BY SNo

显示结果

1
2
3

/* 查询视图 AVGGRADE */
SELECT SNo, A1, A2, A3
FROM AVGGRADE

修改第2题中生物05的学生作业情况视图，去掉作业2，3的成绩

ALTER VIEW ZY_SW05
AS SELECT 学生表.SNo, SN, CN, G1
from 学生表, 课程表, 学生作业表
WHERE 学生表.SNo = 学生作业表.SNo AND 
课程表.CNo = 学生作业表.CNo AND
学生表.MC = N'生物 05'

显示结果

1
2
3

/* 查询更改后视图 ZY_SW05 */
SELECT SNo, SN, CN, G1
FROM ZY_SW05

EXP5-4

1 2	INSERT INTO Sub_DZ05 (SNo, SN, SEX, MC, BT) VALUES ('0596',N'赵亦',N'男',N'电子 05','1986-6-8')

显示结果

1
2
3

/* 查询更新后视图 Sub_DZ05 */
SELECT SNo,SN,SEX,MC,BT
FROM Sub_DZ05

1
2
3

UPDATE Sub_DZ05
SET SEX = N'女'
WHERE SN = N'赵亦'

显示结果

1
2
3

/* 查询更新后视图 Sub_DZ05 */
SELECT SNo,SN,SEX,MC,BT
FROM Sub_DZ05

EXP5-6-1
EXP5-6-2

1 2	DELETE FROM Sub_DZ05 WHERE SN = N'赵亦'

显示结果

1
2
3

/* 查询更新后视图 Sub_DZ05 */
SELECT SNo,SN,SEX,MC,BT
FROM Sub_DZ05

8.
1
DROP VIEW Sub_DZ05

{{date}}		{{temperature}}		{{humidity}}
{{time}}
{{ip}}	{{city}}		{{daylight}}

数据库原理 实验

实验二

实验三

实验四

实验五

数据库原理实验