数据库实验之SQL语法练习-DDL、DML

  • 实验目的
  1. 熟悉SQL语句;
  2. 学会数据库、表、索引的定义与实现,进行数据操作;
  3. 学会根据需求进行操作处理。
  • 实验环境

Windows10

Wampserver3.1.9

  • 实验内容

1.完成数据库、表、索引的定义与实现,进行数据的插入、删除、修改、及多表联合查询。

2.用SQL语句创建《数据库系统概论》第五版P70习题6中的4个表,并录入相应的数据,之后完成P130习题5

  • 实验过程

1.完成数据库、表、索引的定义与实现,进行数据的插入、删除、修改、及多表联合查询。

图1 左击数据库-新建数据库shiyan2

鼠标左键点击数据库,然后新建数据库shiyan2。再点击新建查询。

图2 新建表

新建表有两种方式。第一个是如图所示代码,注意代码执行完时候要把它注释掉(否则会再执行一遍)。第二个是在想创建的数据库的位置右键点击新建表。

图3 为三个表创建索引

图4 向Student表中插入数据

图4 向Course、SC表中插入数据(代码没有截完整)

 

图5 对课程(Cno)的先导课(Cpno)进行修改

图6 目前的Course表

图7 目前的Student表

图8 目前的SC表

图9 将学生200215126年龄改为22岁

图9 将学生200215126年龄修改后的Student表

图10 将所有学生的年龄增加1岁

图11 将计算机科学系全体学生的成绩置零

注意:

RDBMS在执行修改语句时会检查修改操作

是否破坏表上已定义的完整性规则

实体完整性

主码不允许修改

用户定义的完整性

 (1)NOT NULL约束

 (2)UNIQUE约束

 (3)值域约束

图12 删除学号为200215128的学生记录

图13 报错约束冲突

原因:SC表中的Sno,Cno是主码,删除Student表中的学号为200215128的学生记录,但是SC表中存在相关的数据。

需要将成绩表中引用该记录的所有数据处理掉才能删除开学生表中的该条记录。

外键取值规则:空值或参照的主键值。

(1)插入非空值时,如果主键表中没有这个值,则不能插入。

(2)更新时,不能改为主键表中没有的值。

(3)删除主键表记录时,你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

(4)更新主键记录时,同样有级联更新和拒绝执行的选择。

删除规则

指定当数据库的最终用户尝试删除某一行,而该行包含外键关系所涉及的数据时所发生的情况。如果设置为:

(1)Restrict(限制)︰如果有从行就不能删除父行。

(2)Cascade(级联)︰删除父表中的行会自动删除从表中的任何相关行。

(3)No Action(无动作)︰这是默认的情况,如果有从行就不能删除父行。和restrict的区别在于何时执行约束。

(4)Set Null(置空)︰该规则确保在父表删除一行时,讲从表的外键置空(如果允许空的话),行的其它列保持不变。

删除操作:

  1. 语句格式

       DELETE

       FROM     <表名>

       [WHERE <条件>];

  1. 功能
    1. 删除指定表中满足WHERE子句条件的元组
  2. WHERE子句
    1. 指定要删除的元组
    2. 缺省表示要删除表中的全部元组,表的定义仍在字典中
  3. 三种删除方式

1. 删除某一个元组的值

2. 删除多个元组的值

3. 带子查询的删除语句

图14 解决报错约束冲突

成功删除学号为200215128的学生记录。

图14 删除学号为200215128的学生记录后的SC表

图15 删除学号为200215128的学生记录后的Student表

数据查询

(1)连接查询

  1. 连接查询:同时涉及多个表的查询
  2. 连接条件或连接谓词:用来连接两个表的条件

     一般格式:

  1. [<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>
  2. [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
  1. 连接字段:连接谓词中的列名称
  1. 连接条件中的各连接字段类型必须是可比的,但名字不必是相同的

连接操作的执行过程:

  1. 嵌套循环法(NESTED-LOOP)
    • 首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。
    • 表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。
    • 重复上述操作,直到表1中的全部元组都处理完毕

排序合并法(SORT-MERGE)

常用于=连接

    • 首先按连接属性对表1和表2排序
    • 对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续

排序合并法

    • 找到表1的第二条元组,然后从刚才的中断点处继续顺序扫描表2,查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时,对表2的查询不再继续
    • 重复上述操作,直到表1或表2中的全部元组都处理完毕为止

图16 查询全体学生的学号和姓名

图17 查询全体学生的详细信息

图18 查询全体学生的姓名和出生年份

注意到运行出来的结果并不会显示2024-Sage。

图18 查询全体学生的姓名、出生年份和所在院系,要求用小写字母表示系名

图19在上一个例子的基础上设置列标题

图20 查询选修了课程的学生学号

图21 在上一个例子的基础上去掉重复元组

图22 查询计算机科学系所有学生的名单

图23 查询所有年龄在20岁以下的学生姓名及其年龄

图24 查询所有年龄不在20-23岁的学生姓名、系别、年龄

图25 查询既不是计算机科学系、数学系,也不是信息系的学生的姓名和性别

图26 查询所有姓刘的同学的姓名学号和性别

图27 查询名字第二个字是“成“的学生的姓名和学号

图28 查询以PA开头,倒数第三个字母是L的课程的详细情况

图29 查询缺少成绩的学生的学号和相对应的课程号

图30 为了后续操作修改SC表

图31修改后的SC表

图32 查询选修了3号课程的学生的学号和成绩,查询结果按分数的降序排列

图33 查询所有学生的情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列

图34 查询选修了课程的学生人数

图35 计算选修1号课程的学生的平均成绩

图36 求各个课程号及相应的选课人数

图37 查询选修了三门以上课程的学生的学生学号

图38 查询平均成绩大于40分的学生的学号和平均成绩

图39 查询每个学生及其选修课情况

图40 查询每个学生及其选修课情况, 用自然连接实现

图41 查询每一门课的间接先修课(即先修课的先修课)

图42 查询每个学生及其选修课情况,用外连接实现

图43 查询选修2号课程且成绩在90分以上的所有学生

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

图45 嵌套查询

查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成

图46 确定“刘晨”所在系名

图47 查找所有在CS系学习的学生

图48 将第一步查询嵌入到第二步查询的条件中

图49 用自身连接完成查询要求

图50 查询选修了课程名为“信息系统”的学生学号和姓名

图51 查询选修了课程名为“信息系统”的学生学号和姓名,连接查询实现

图52 带有比较运算符的子查询实现上例

图53 找出每个学生超过他选修课程平均成绩的课程号

图54 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

图55 用聚集函数实现

图56 查询没有选修1号课程的学生姓名

图57查询计算机科学系的学生及年龄不大于19岁的学生

2.用SQL语句创建《数据库系统概论》第五版P70习题6中的4个表,并录入相应的数据,之后完成P130习题5

(1)建表部分

图58 建S、P、J、SPJ表

供应商表S:供应商代码SNO、供应商姓名SNAME、供应商状态STATUS、供应商所在城市CITY

零件表P:零件代码PNO、零件名PNAME、颜色COLOR、重量WEIGHT

工程项目表J:工程项目代码JNO、工程项目名JNAME、工程项目所在城市CITY

供应情况表SPJ: 供应商代码SNO、零件代码PNO、工程项目代码JNO、供应数量QTY

图59 S表

图60 J表

图60 P表

图61 SPJ表

(2)习题部分

图62 所有供应商的姓名和所在城市(波浪线是因为没有清除缓存)

图63 找出所有零件的名称颜色和重量(波浪线是因为没有清除缓存)

图64 使用供应商S1所供应的零件的工程号码

图65 工程项目J2所使用的零件的名称和数量

图66 上海厂商供应的所有零件的号码

图67 使用上海产的零件的工程名称

图68 没有使用天津产的零件的工程号码

图69 把所有红色零件的颜色改成蓝色

图70 把所有红色零件的颜色改成蓝色后的P表

图71 由S5供给J4的零件P6改成由S3供应

图72由S5供给J4的零件P6改成由S3供应后的SPJ表

图73 从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录

图74从供应情况关系中删除S2相应的记录后的SPJ表

图75 从供应商关系中删除S2的记录后的S表

图76 插入要求插入的元组到SPJ表

图77 插入后的SPJ图

  • 28
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值