Kingbase数据库实验二 单表查询、连接查询和视图的使用

一、实验目的

1. 掌握无条件查询的使用方法。
2. 掌握条件查询的使用方法。
3. 掌握库函数及汇总查询的使用方法。
4. 掌握分组查询的使用方法。
5. 掌握查询的排序方法。
6. 掌握连接查询的使用方法。
7. 掌握创建视图的方法。
8. 掌握修改视图的方法。
9. 掌握查询视图的方法。
10. 掌握更新视图的方法。
11. 掌握删除视图的方法

二、实验内容及源代码

注：仅针对使用人大金仓Kingbase数据库的用户

1．查询各位学生的学号、班级和姓名。
SELECT 学号,专业班级,姓名
FROM "PUBLIC".学生表

2．查询课程的全部信息。
SELECT *
FROM "PUBLIC".课程表

3．查询数据库中有哪些专业班级。
SELECT 专业班级
FROM "PUBLIC".学生表

4．查询学时数大于60的课程信息。
SELECT *
FROM "PUBLIC".课程表
WHERE 学时数>60

5．查询在1986年出生的学生的学号、姓名和出生日期。
SELECT 学号,姓名,出生日期
FROM "PUBLIC".学生表
WHERE 出生日期 LIKE '1986%'

6．查询三次作业的成绩都在80分以上的学号、课程号。
SELECT 学号,课程号
		FROM "PUBLIC".学生作业表
WHERE (作业1成绩>80) AND (作业2成绩>80) AND (作业3成绩>80)

7．查询姓张的学生的学号、姓名和专业班级。
SELECT 学号,姓名,专业班级
		FROM "PUBLIC".学生表
			WHERE 姓名 LIKE '张%'
			
8．查询05级的男生信息。
SELECT *
		FROM "PUBLIC".学生表
WHERE 专业班级 LIKE '__05' AND 性别='男'

9．查询没有作业成绩的学号和课程号。
SELECT 学号,课程号
		FROM "PUBLIC".学生作业表
WHERE 作业1成绩 IS NULL OR 作业2成绩 IS NULL OR 作业3成绩 IS NULL

10．查询学号为0538的学生的作业1总分。
SELECT SUM (作业1成绩)
		FROM "PUBLIC".学生作业表
WHERE 学号=0538

11．查询选修了K001课程的学生人数。
SELECT COUNT(课程号)
		FROM "PUBLIC".学生作业表
WHERE 课程号='K001'

12．查询数据库中共有多少个班级。
SELECT COUNT(DISTINCT 专业班级)
FROM "PUBLIC".学生表

13．查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
SELECT "学生作业表".学号,AVG(作业1成绩)AS 作业1平均成绩,AVG(作业2成绩)AS 作业2平均成绩,AVG(作业3成绩)AS 作业3平均成绩,COUNT(课程号)AS 选修课数目
FROM "学生作业表" INNER JOIN "学生表" ON "学生表"."学号" ="学生作业表".学号 
GROUP BY "学生作业表".学号
HAVING COUNT(课程号)>=3

14．查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。
内连接查询：
SELECT "学生表".学号,"学生表".姓名,"课程表".课程名
FROM "课程表" INNER JOIN "学生作业表" ON "课程表".课程号="学生作业表".课程号
INNER JOIN "学生表" ON "学生作业表".学号="学生表".学号
WHERE "学生表".姓名='于兰兰'

外连接查询：
SELECT R1.学号,R1.姓名,R3.课程名 FROM
(SELECT 学号,姓名 FROM "学生表" WHERE(姓名='于兰兰'))AS R1
INNER JOIN(SELECT 学号,课程号 FROM "学生作业表")AS R2
ON R2.学号=R1.学号 
INNER JOIN(SELECT 课程名,课程号 FROM "课程表")AS R3
ON R2.课程号=R3.课程号

15．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
子查询：
SELECT *
FROM "PUBLIC".学生表
WHERE "专业班级"=(	SELECT "专业班级"
					FROM "PUBLIC".学生表
WHERE "姓名"='张志国')
连接查询：
SELECT R1.*FROM "PUBLIC".学生表 AS R1,"学生表"AS R2
	WHERE R2."姓名"='张志国' AND R1."专业班级"=R2."专业班级";
 
（2）查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
连接查询：
SELECT "课程表1".*
FROM "课程表" AS "课程表1","课程表" AS "课程表2"
WHERE "课程表2".课程名='计算机应用基础' AND "课程表1".学时数>"课程表2".学时数;
子查询：
SELECT * FROM "课程表"
	WHERE "学时数">(SELECT "学时数"
	FROM "课程表"
WHERE 课程名='计算机应用基础');
 
（3）查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
连接查询：
SELECT "学生表"."学号","学生表"."姓名" FROM "学生作业表","学生表"
WHERE "学生作业表"."课程号" = 'K002' AND "学生作业表"."学号"="学生表"."学号";
 
普通子查询：
SELECT DISTINCT 学生表."学号",学生表."姓名"
FROM 学生作业表,学生表
WHERE 学生表."学号" IN(
		SELECT 学生表."学号"
		FROM 学生作业表
		WHERE 学生作业表."课程号"='K002' AND 学生作业表."学号"=学生表."学号"
);
 

相关子查询：
SELECT DISTINCT 学生表."学号",学生表."姓名"
FROM 学生表,学生作业表
WHERE 学生表."学号" IN(
			SELECT 学生作业表."学号"
			FROM 学生作业表
			WHERE 学生作业表."课程号"='K002'
);

使用exists关键字的相关子查询：
SELECT DISTINCT 学生表."学号",学生表."姓名"
FROM 学生作业表,学生表
WHERE EXISTS(
			SELECT *
			FROM 学生作业表
			WHERE 学生作业表."课程号" ='K002' AND 学生作业表."学号"=学生表."学号"
);  

（4）查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。
SELECT DISTINCT 学号,课程号,作业1成绩,作业2成绩,作业3成绩 FROM 学生作业表
WHERE 学号 NOT IN
(SELECT 学号 FROM 学生作业表 WHERE 课程号='K001' OR 课程号='M001');
 
16．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
INSERT INTO "PUBLIC".学生表("学号","姓名","性别","专业班级")
	VALUES('0593','张乐','男','电子05');
 
（2）将所有课程的学分数变为原来的两倍。
UPDATE "学生作业表"
SET 作业1成绩 = 2 * 作业1成绩;
UPDATE "学生作业表"
SET 作业2成绩 = 2 * 作业2成绩;
UPDATE "学生作业表"
SET 作业3成绩 = 2 * 作业3成绩;
 
（3）删除张乐的信息。
DELETE FROM "学生表"
WHERE 姓名='张乐'
 
根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
17．创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。
CREATE VIEW 电子05(学号,姓名,性别,专业班级,出生日期)
AS SELECT 学生表."学号","姓名","性别","专业班级","出生日期"FROM 学生表
WHERE "专业班级"='电子05'
SELECT *FROM 电子05
 
18．创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。
CREATE VIEW 生物05(学号,姓名,课程名,作业1成绩,作业2成绩,作业3成绩)
AS SELECT 学生表."学号",学生表."姓名",课程表."课程名",学生作业表.作业1成绩,学生作业表."作业2成绩",学生作业表."作业3成绩"
FROM 学生表,课程表,学生作业表
WHERE 学生表."学号"=学生作业表."学号" AND 课程表."课程号"=学生作业表."课程号"
SELECT *FROM 生物05
 
19．创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。
CREATE VIEW 平均成绩(学号,作业1平均成绩,作业2平均成绩,作业3平均成绩)
AS SELECT 学号,AVG(作业1成绩),AVG(作业2成绩),AVG(作业3成绩)
	FROM 学生作业表	
GROUP BY 学号
SELECT *FROM 平均成绩
 
20．修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。
CREATE VIEW 生物05修改情况
AS SELECT 学号,姓名,课程名,作业1成绩
FROM 生物05
SELECT *FROM 生物05修改情况
 
21．向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
INSERT INTO 电子05(学号,姓名,性别,专业班级,出生日期) 
VALUES (0596,'赵亦','男','电子05','1986-6-8');
 
22．将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
UPDATE 电子05 SET 性别='女' WHERE 姓名='赵亦'; 

23．删除电子05的学生视图中赵亦的记录。
DELETE FROM 电子05
WHERE 学号='0596';

24．删除电子05的学生视图（给出SQL语句即可）。
DROP VIEW 电子05;