前言
本篇文章相当于是一个简单的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文章来源:https://www.toymoban.com/news/detail-431432.html
DROP VIEW GOOD_SC CASCADE; /*删除了视图GOOD_SC和由它导出的所有视图*/
总结
查询是重点,连接查询是重中之重文章来源地址https://www.toymoban.com/news/detail-431432.html
到了这里,关于【数据库】实验 1:数据库定义与操作语言实验的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!