实验一 数据库定义与操作语言——数据库管理系统原理与设计

数据库管理系统原理与设计

实验一 数据库定义与操作语言

---

实验1.1 数据库定义

1. 实验目的
   理解和掌握数据库DDL语言，能够熟练使用SQL DDL 语句创建、修改和删除数据库、模式和基本表。
2. 实验内容和要求
   理解和掌握SQL DDL语句的语法，特别是各种参数的具体含义和使用方法；使用SQL语句创建、修改和删除数据库、模式和基本表。掌握SQL语句常见语法错误的调试方法。
3. 实验过程
   首先在win10系统下安装MySQL数据库管理系统。安装成功后，打开cmd，输入mysql -u root -p 回车可见MySQL版本信息。（我装了5.7.17版本）
   
   实验过程中的SQL语句都必须以分号作为结束符。
   ①创建一个数据库：create database 数据库名；
   创建一个名为test的数据库，库名大小写无关。
   
   ②显示创建的数据库：show databases;
   
   ③修改数据库：use 数据库名;
   
   (1)创建数据库表：create table 表名(列声明);
   以创建students表为例，表中存放学号(sid)、姓名(sname)、年龄(sage)、联系电话(stel);
   用show tables; 显示创建的表名；
   用describe students; 显示创建表的具体信息。
   
   (2)向表中添加数据：
   insert into 表名 values(对应列名数据) 这里添加两个数据
   
   显示表的信息：select * from 表名
   
   (3)更新表中数据：详细见1.4数据更新，这里给学生的年龄加一
   update 表名 set 列名称=新数据 where 更新条件
   
   (4)修改表：
   重命名表：
   alter table 表名 rename 新表名
   
   
   添加列：
   alter table 表名 add 列名 列数据类型 after 插入位置(省略after自动添加到最后)
   
   修改列：这里把stel改成tel
   alter table 表名 change 列名 列新名 新数据类型
   
   删除列：这里删除majorin
   alter table 表名 drop 列名
   
   (5)删除表中的数据：
   delete from 表名 where 删除条件
   如删除学号为15331000的学生数据
   
   (6)删除整张表：可见new_students表被删除，该数据库没有表
   drop table 表名
   
   (7)删除整个数据库：
   drop database 数据库名;
   可见test数据库被删除了。
   
   ④创建模式：
   create schema 模式名
   
   然后用show schemas; 显示所创建的模式：
   
   ⑤删除模式：
   drop schema 模式名;
   

实验1.2 数据基本查询

1. 实验目的
   掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。
2. 实验内容和要求
   针对TPC-H数据库设计各种单表查询SQL语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并测试通过。
3. 实验过程
   基本查询语句：
   select [DISTINCT] select-list
from from-list
where qualification
   首先创建三个数据库表：sailors, boats, reserves.
   create table 表名(列声明)
   然后向表中添加数据
   insert into 表名 values(对应列名数据)
   
   
   
   
   添加数据后各个表的信息：
   
   
   
   ①单表查询：
   例如查询等级在7以上的所有水手，如下
   
   ②分组统计查询：使用group by语句
   例如，按照s.rating来对sailors分组统计查询如下
   
   ③连接查询
   (1)多表连接查询：
   例如，查询预订了一艘红色船只或绿色船只的水手，如下
   
   (2)使用UNION语句
   例如，查询预订了一艘红色船只或绿色船只的水手，如下
   
   (3)INTERSECT语句：由于MySQL不支持该语句，可使用cross join来实现交集的功能，如下
   

实验1.3 数据高级查询

1. 实验目的
   掌握SQL嵌套查询和集合查询等各种高级查询的设计方法等。
2. 实验内容和要求
   针对TPC-H数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询
3. 实验过程
   ①嵌套查询：可以使用IN、EXISTS来实现嵌套查询。
   例如，查询预订了103号船只的水手的名字，如下利用IN
   
   如下利用EXISTS
   
   ②集合查询：
   (1)ANY语句
   例如，查询等级比名为Horatio的水手还要高的水手，如下
   
   (2)ALL语句
   例如，查询等级最高的水手
   

实验1.4 数据更新

1. 实验目的
   熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。
2. 实验内容和要求
   针对TPC-H数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。
3. 实验过程
   ①表中数据修改：见实验1.1③修改数据库->(3)更新表中数据
   update 表名 set 列名称=新数据 where 更新条件
   ②把一个表的数据复制到另一个表中
   insert into 新表 select * from 旧表
   例如，创建一个空表(列声明和被复制的表一样)new_boats，如下
   
   下图可见复制成功
   
   ③利用一个表更新另一个表的数据：
   update 表名 set 属性名=新数据
where EXISTS(子查询)
   例如，下图把new_boats中的nid与boats中的bid相同的船只的颜色改成black
   
   ④用一个表删除另一个表的数据
   例如，我们先在new_boats中插入一行(106, “new”, “white”)，然后通过boats(不存在于boats，但存在于new_boats)来删除它。
   
   
   ⑤向表中批量插入数据：数据之间使用逗号’,’隔开即可，如下
   insert into 表名 values(),(),()...
   

实验1.5 视图

1. 实验目的
   熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。
2. 实验内容和要求
   针对给定的数据库模式，以及对应的应用需求，创建视图、创建带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。
3. 实验过程
   ①创建一个视图：
   
create view 视图名称(视图列名)
AS select 表中某些列名
from 表名
where 限定条件

   例如，创建一个视图，用于查询预订了船只且等级大于7的水手的名字以及他所预订的船只的名字，如下
   
   ②更新视图：
   例如把名字为Lubber的水手的rating改为9，观察视图的变化，可见视图中对应的列也变为9，如下
   
   ③WITH CHECK OPTION语句：该语句的作用是对更新视图的过程作了限制条件，所有对视图更新的操作都必须符合带WITH CHECK OPTION 语句的视图中where语句中的限制条件。
   例如，创建一个带有WITH CHECK OPTION语句的新视图view2，分别以把s_rating改为6和8尝试更新视图
   
   改为6，如下：可见不能更新，因为6小于7；
   改为8，如下：可以更新，因为8大于7。
   
   ④删除视图：
   drop view 视图名称
   如删除view
   

---

实验总结与心得体会

根据MySQL数据库管理系统的SQL语句，大体上完成了实验内容各个要求。
简单说说心得。

• 每一行命令以分号’;’结束，否则会等待继续输入；
• MySQL中命令大小写不区分，数据库名也不区分，但在表中的数据是严格区分大小写的；
• 日期Date的输入格式为“xxxx-xx-xx”；
• MySQL不支持INTERSECT和MINUS等操作，可通过cross join或者inner join等操作来代替实现；
• WITH CHECK OPTION可以限制更新，保证每次更新都必须满足限制条件；
• 具有group by语句的视图是不能更新的。

通过这次实验，可以进一步了解MySQL，巩固了课本的知识，理解熟悉数据库SQL语言的诸多操作，对接下来的学习有着巨大的帮助。

学习MySQL时的参考资料
http://www.cnblogs.com/mr-wid/archive/2013/05/09/3068229.html