【数据库】实验 1：数据库定义与操作语言实验

---

前言

本篇文章相当于是一个简单的SQL语言归纳总结，包括数据库定义和一些基本的数据操作。值得注意的是，不同的数据库系统有着自己的特点，语法不完全相同，在使用具体系统是可以查阅各产品的用户手册。

---

提示：以下是本篇文章正文内容，下面案例可供参考

样例选择

本实验所使用的数据库为学生-课程数据库S-T

• 学生-课程数据库模式 S-T :

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

实验 1.1 数据库定义实验

（1）实验目的

理解和掌握数据库 DDL 语言，能够熟练地使用 SQL DDL 语句创建、修改和 删除数据库、模式和基本表。

（2）实验内容和要求

理解和掌握 SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法； 使用 SQL 语句创建、修改和删除数据库、模式和基本表。掌握 SQL 语句常见语法错误的调试方法。

（3）实验重点和难点

实验重点：创建数据库、基本表。

实验难点：创建基本表时，为不同的列选择合适的数据类型，正确创建表 级和列级完整性约束，如列值是否允许为空、主码和外码等。注意：数据完整 性约束，可以在创建基本表时定义，也可以先创建表然后定义完整性约束。由于完整性约束的限制，被引用的表要先创建。

（4）实验记录

1.DDL语言

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言

常用的语句：create, alter, drop, rename

2.SQL DDL 语句

数据库：

#创建数据库
CREATE DATABASE lab1;

#修改数据库字符集
ALTER DATABASE lab1 CHARSET utf8;

#删除数据库
DROP DATABASE lab1;

模式：

严格地说，在MySql中，Database和Schema之间的区别是不存在的。

但是，在其他数据库引擎(如SQL Server)中并非如此。 在SQL server：中，

每个表都属于数据库中称为数据库模式的一组对象。 它是容器或命名空间(查询Microsoft SQL Server 2012)

#创建模式
CREATE SCHEMA S-T AUTHORIZATION QUAN;

#修改模式
SQL标准不提供修改模式定义的操作

#删除模式  CASCADE(级联)/RESTRICT(限制) 二只必选其一
DROP SCHEMA S-T CASCADE;

基本表

数据类型

#创建基本表
mysql> CREATE TABLE Student
    -> (Sno CHAR(9) PRIMARY KEY,  /*列级完整性约束条件，Sno是主码*/
    -> Sname CHAR(20) UNIQUE,   /*Sname取唯一值*/
    -> Sex CHAR(2),
    -> Sage SMALLINT,
    -> Sdept CHAR(20)
    -> );

mysql> CREATE TABLE Course
    -> (Cno CHAR(4) PRIMARY KEY,  /*列级完整性约束条件，Cno是主码*/
    -> Cname CHAR(40) NOT NULL,    /*列级完整性约束条件，Cname不能取空值*/
    -> Cpno CHAR(4),
    -> Ccredit SMALLINT,
    -> FOREIGN KEY(Sno) REFERENCES Student(Sno),
    -> FOREIGN KEY(Cpno) REFERENCES Course(Cno)
                       /*表级完整性约束条件，Cpno是外码，被参照表是Course,被参照列是Cno*/
    -> );
    
mysql> CREATE TABLE SC
    -> (Sno CHAR(9),
    -> Cno CHAR(4),
    -> Grade SMALLINT,
    -> PRIMARY KEY (Sno,Cno),
    -> FOREIGN KEY(Cno) REFERENCES Course(Cno)
                       /*表级完整性约束条件，Cno是外码，被参照表是Course,被参照列是Course*/
    -> );

#修改基本表
ALTER TABLE Student ALTER COLUMN Sage INT; /*修改年龄的数据类型为整数*/

#删除基本表
mysql> DROP TABLE SC CASCADE;

实验 1.2 数据基本查询实验

（1）实验目的

掌握 SQL 程序设计基本规范，熟练运用 SQL 语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。

（2）实验内容和要求

针对某个数据库设计各种单表查询 SQL 语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握 SQL 查询语句 各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并 调试通过。

说明：简单地说，SQL 程序设计规范包含 SQL 关键字大写、表名、属性名、 存储过程名等标识符大小写混合、SQL 程序书写缩进排列等编程规范。

（3）实验重点和难点

实验重点：分组统计查询、单表自身连接查询、多表连接查询。

实验难点：区分元组过滤条件和分组过滤条件；确定连接属性，正确设计连接条件.

（4）实验记录

1.单表查询（实现投影操作）

查询学生学号和姓名

SELECT Sno,Sname
FROM Student;

2.单表查询（实现选择操作）

查询分数大于90的选课信息

SELECT *
FROM SC
WHERE Grade>90;

3.不带分组过滤条件的分组统计查询

统计每个学生的选修课数目

SELECT Student.Sno SUM(*)
FROM Student,SC
WHERE Student.Sno=SC.Sno
GROUP BY Student.Sno;

4.带分组过滤条件的分组统计查询

查询选课数大于2的学生学号和姓名

SELECT Student MAX(Student.Sname)
FROM Student,SC
WHERE Student.Sno=SC.Sno
GROUP BY Student.Sno
HAVING SUM(*)>2;

5.单表自身连接查询

查询每门课的间接先修课（先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Con;

6.两表连接查询

查询每个学生及其选修课程的情况

SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;

7.两表连接查询（自然连接）

查询每个学生及其选修课程的情况

SELECT Student.Sno,Sname,Sex,Sage,Sdept,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno;

8.三表连接查询

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.cno;

实验 1.3 数据高级查询实验**

（1）实验目的

掌握 SQL 嵌套查询和集合查询等各种高级查询的设计方法等。

（2）实验内容和要求

针对自定义数据库，正确分析用户查询要求，设计各种嵌套查询和集合查 询。

（3）实验重点和难点

实验重点：嵌套查询。

实验难点：相关子查询、多层 EXIST 嵌套查询。

（4）实验记录

1.IN嵌套查询

查询与”刘晨“在同一个系学习的学生

SELECT *
FROM Student
WHERE Sdept IN (SELECT Sdept
                FROM Student
    			WHERE Sname='刘晨';)

2.单层EXISTS嵌套查询

查询所有选修了1号课程的学生姓名

SELECT Sname
FROM Student
WHERE EXISTS(SELECT *
    		 FROM SC
			 WHERE Sno=Student.Sno AND Cno='1';)

3.双层EXISTS嵌套查询

查询选修了全部课程的学生姓名=没有一门课程是他不选修的

SELECT Sname
FROM Student
WHERE NOT EXISTS
		  (SELECT *
          FROM Course
          WHERE NOT EXISTS
          			(SELEST *
                    FROM SC
                    WHERE Sno=Student.Sno
                    AND Cno=Course.Cno));

4.集合查询（交）

查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

5.集合查询（并）

查询选修了课程1或者选修了课程2的学生

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

6.集合查询（差）

查询计算机科学系的学生与年龄不大于19岁的学生的差集

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;

实验 1.4 数据更新实验

（1）实验目的

熟悉数据库的数据更新操作，能够使用 SQL 语句对数据库进行数据的插入、 修改、删除操作。

（2）实验内容和要求

针对自定义数据库设计单元组插入、批量数据插入、修改数据和删除数据 等 SQL 语句。理解和掌握 INSERT、UPDATE 和 DELETE 语法结构的各个组成成分， 结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句， 并调试成功。

（3）实验重点和难点

实验重点：插入、修改和删除数据的 SQL。

实验难点：与嵌套SQL子查询相结合的插入、修改和删除数据的SQL 语句； 利用一个表的数据来插入、修改和删除另外一个表的数据。

（4）实验记录

1.INSERT基本语句（插入全部列的数据）

插入一条学生信息，要求每一列都给一个合理的值。

INSERT INTO Student
VALUES('201215121','李勇'，'男',20,'CS');

2.INSERT基本语句（插部分列的数据）

插入一条课程记录，给出必要的几个字段值。

INSERT INTO Course(Cno,Cname,Ccredit)
VALUES('2','数学',2);

3.批量数据INSERT语句

• 创建一个新的学生表，把所有选了1号课程的学生插入到新的学生表中。

  CREATE TABLE NewStudent AS SELECT * FROM Student WITH NO DATA;
  /*WITH NO DATA 子句使得SELECT查询只生成一个结果模式，不查询出实际数据*/
  INSERT INTO NewStudent
  SELECT S.*
  FROM Student S,SC C
  WHERE S.Sno=C.Sno AND C.Cno='1';
  

• 创建一个学生选课统计表，记录每个学生及其选课总数和总学分等信息

  CREATE TABLE CourseStat
  (Sno CHAR(9),
   quantity REAL,
   totalcredits 
  );
  INSERT INTO CourseStat
  SELECT Student.Sno,COUNT(*),SUM(Ccredit)
  FROM Student,Course,SC
  WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno
  GROUP BY Student.Sno;
  

4.UPDATE语句（修改部分记录的部分列值）

将计算机科学系全体学生成绩下降10%

UPDATE SC
SET Grade=Grade*0.9
WHERE Sno IN
		(SELECT Sno
        FROM Student
        WHERE Sdept='CS');

5.UPDATE语句(利用一个表中的数据修改另一个表中的数据)

利用Course表中学分来修改SC表中的Grade,其中Grade=Course.Ccredit*Grade

UPDATE SC
SET SC.Grade=Course.Ccredit*SC.Grade
FROM Course
WHERE SC.Cno=Course.Cno;

6.DELET基本语句（删除给定条件的所有记录）

删除学生李勇的所有选课记录

DELETE FROM Student
WHERE Sname='李勇'；

DELETE RFOM SC
WHERE Sno = (SELECT SC.Sno
             FROM SC,Student
             WHERE SC.Sno=Student.Sno AND Student.Sname='李勇'
)

实验 1.5 视图**

（1）实验目的

熟悉SQL语言有关视图的操作，能够熟练使用 SQL语句来创建需要的视图， 定义数据库外模式，并能使用所创建的视图实现数据管理。

（2）实验内容和要求

针对给定的数据库模式，以及相应的应用需求，创建视图和带 WITH CHECK OPTION 的视图，并验证视图 WITH CHECK OPTION 选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

（3）实验重点和难点

实验重点：创建视图。

实验难点：可更新的视图和不可更新的视图的区别， WITH CHECK OPTION 的验证。

（4）实验记录

1.创建视图（省略视图列名）

省略则隐含该视图由子查询中SELECT子句目标列中的诸字段组成

建立信息系学生的视图

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Stept='IS';

2.创建视图（不能省略视图列名的情况）

• 某个目标列不是单纯的属性名，而是聚集函数或列表达式
• 多表连接时选出了几个同名列作为视图的字段
• 需要在视图中为某个列启用新的更合适的名字

建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno AND 
	  Student.Stept='IS' AND
	  SC.Cno='1';

3.创建视图（WITH CHECK OPTION）

WITH CHECK OPTION 表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图中定义的谓词条件（即子查询中的条件表达式）

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Stept='IS'
WITH CHECK OPTION;

4.可更新的视图（行列子集视图）

行列子集视图:从单个基本表导出，并且只是去掉了基本表的某些列和某些行，但是保留了主码

建立信息系学生的视图

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Stept='IS';

5.不可更新的视图

DB2规定以下视图不允许更新：

• 视图由两个以上基本表导出
• 视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作
• 视图的字段来自聚集函数
• 视图定义中含有GROUP BY子句
• 视图定义中含有DISTINCT短语
• 视图定义中含有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表
• 一个不允许更新的视图上定义的视图

将SC表中成绩在平均成绩之上的元祖定义成一个视图

CREATE VIEW GOOD_SC
AS
SELECT Sno,Cno,Grade
FROM SC
WHERE Grade >
			(SELECT AVG(Grade)
            FROM SC);

6.删除视图（RESTRICT/CASCADE）

删除视图GOOD_SC

DROP VIEW GOOD_SC CASCADE; /*删除了视图GOOD_SC和由它导出的所有视图*/

总结

查询是重点，连接查询是重中之重