sql作业实验报告.doc

1、sql作业实验报告实验报告基本表的创建、查询及更新任务 实验日期和时间：实验室：班级：学号：姓名：实验环境：硬件：软件：实验目的：本次实验的主要目的是 实验主要任务：(不够时另附页) 一.建立各个表的 SQL 语句。二.单表查询操作及（任选三题结果截图附上）。三.多表查询操作其结果截图（任选三题结果截图附上）。四.数据更新及其结果截图（最后附上四个基本表的数据截图）。一利用查询分析p 器在数据库 ShiYan 中建立以下四个数据表（S 表，P 表，J 表，SPJ表），并依次向各数据表中输入相应的数据记录。(1) 供应商表 S（NO，SNAME，STATUS，CITY）SNO：表示供应商编号，定

2、义其为长度为 4 字节的字符串类型。SNAME：表示供应商的名称，定义其为最大长度为 20 字节的变长字符串类型。STATUS：表示供应商的基本状况，定义其为短整数类型。CITY：表示供应商所在的城市，定义其为最大长度为 30 字节的变长字符串类型。要求：SNO 字段为表的主键，其属性值必须不重复且不允许取空值，SNAME 属性值不允许取空值。SNO SNAME STATUS CITY S1 精 益 20 天津 S2 盛 锡 10 北京 S3 东方红 30 北京 S4 丰泰盛 20 天津 S5 为 民 30 上海 PNO PNAME COLOR WEIGHT P1 螺 母 红 12 P2 螺

3、栓 绿 17 P3 螺丝刀 蓝 14 P4 螺丝刀 红 14 P5 凸 轮 蓝 40(2) 零件表 P（PNO，PNAME，COLOR，WEIGHT）PNO：表示零件的编号，定义其为长度为 4 字节的字符串类型。PNAME：表示零件的名称，定义其为最大长度为 20 字节的变长字符串类型。COLOR：表示零件的颜色，定义其为长度为 6 字节的字符串类型。WEIGHT：表示零件的重量，定义其为实数类型且缺省值为 0。要求：PNO 字段为表的主键，PNAME 属性值不允许取空值。(3) 工程项目表 J（JNO，JNAME，CITY）JNO：表示工程项目的编号，定义其为长度为 4 字节的字符串类型。J

4、NAME：表示工程项目的名称，定义其为最大长度为 20 字节的变长字符串类型。CITY：表示工程项目所在的施工城市，定义其为最大长度为 30 字节的变长字符串类型。要求：JNO 字段为表的主键，其属性值必须不重复且不允许取空值，JNAME 属性值不允许取空值。P6 齿 轮 红 30 JNO JNAME CITY J1 三建 北京 J2 一汽 长春 J3 弹 簧 厂 天津 J4 造 船 厂 天津 J5 机 车 厂 唐山 J6 无线电厂 常州 J7 半导体厂 南京 (4) 供应商-零件-工程项目关联关系表 SPJ（SNO，PNO，JNO，QTY）SNO：表示为某工程项目供应零件的供应商的编号，定义

5、其为长度为 4 字节的字符串类型。PNO：表示某供应商为工程项目所供应的零件的编号，定义其长度为 4 字节的字符串类型。JNO：表示正在被施工的工程项目的编号，定义其为长度为 4 字节的字符串类型。QTY：表示某供应商为工程项目所供应的零件的数量，定义其为整数类型。要求：SNO、PNO、JNO 属性值不允许取空值；SPJ 表的主键为 SNO、PNO 和 JNO 三属性的组合；SNO、PNO、JNO 均为外码，且 SNO 字段的取值参照 S 表中 SNO 字段的取值，PNO 字段的取值参照 P 表中 PNO 字段的取值，JNO 字段的取值参照 J 表中 JNO 字段的取值；QTY 属性值不能为空

6、值，且 QTY 属性值限制在 110000 范围内。SNO PNO JNO QTY S1 P1 J1 20_S1 P1 J3 100 S1 P1 J4 700 S1 P2 J2 100 S2 P3 J1 400 S2 P3 J2 20_S2 P3 J4 500 S2 P3 J5 400 S2 P5 J1 400 S2 P5 J2 100 S3 P1 J1 20_S3 P3 J1 20_S4 P5 J1 100 S4 P6 J3 300 S4 P6 J4 20_S5 P2 J4 100 S5 P3 J1 20_S5 P6 J2 20_S5 P6 J4 500 二、针对实验数据库 ShiYan，完

7、成以下单表查询操作：1查询为工程 J1 供应零件的供应商号码 SNO。2查询为工程 J1 供应零件 P1 的供应商号码 SNO。3找出所有供应商的名称和所在城市。4找出零件的所有信息，以及仅找出零件的颜色和重量。5找出使用供应商 S1 所供应零件的工程号码。6找出为工程供应零件的总数量不低于 500 的供应商号码及供应总数量，结果按供应商号码分类并且按供应总数量降序排列。7从 J 表中分别检索出第 1 条及前 33的工程项目信息。8统计 P 表中颜色为红色的零件的个数，并指定该查询列的名称为“红色零件数”。9 查询 P 表中各工程项目编号，名称及重量按 86计算后的信息，其中重量按 86计算后

8、的查询列名改为“零件净重”。10查询 SPJ 表，要求查询结果式样为“供应商 S1 为工程项目 J1 供应零件 P1 的数量为300”。11查询 S 表 STATUS 值大于 20 且小于 40，或 SNAME 字段值的第一个字为“精”或第三个字为“益”或“民”的供应商信息。12查询 J 表中 JNAME 值为三建和机车厂的工程项目信息。三、完成以下多表查询操作：1查询为工程 J1 供应红色零件的供应商号码 SNO。2查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号 JNO。3查询至少选用了供应商 S1 所供应的全部零件的工程号 JNO。4找出工程项目 J2 使用

9、的各种零件的名称及其重量。5找出上海厂商供应的所有零件号码。6找出使用上海产的零件的工程名称。7找出没有使用天津产的零件的工程号码。8找出重量最轻的红色零件的零件编号 PNO。9找出供应商与工程所在城市相同的供应商提供的零件号码。10找出所有这样的一些CITY，CITY，PNAME三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称为 PNAME。11重复第 15 题，但不检索两个 CITY 值相同的三元组。12找出供应商 S1 为工程名中含有“厂”字的工程供应的零件数量总和。四、针对实验数据库 ShiYan，完成下列数据更新操作：1在 S 表中插入元组“s6，华誉，40，广州，02

10、085268888”。2在 J 表中插入元组“j8，传感器厂”。3对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库。4将 P 表中 PNO 值为 p6 的元组的 color 属性值改为绿，weight 属性值改为 60。5将 SPJ 表中前 4 个元组的 qty 属性值统一修改为 300。6将 S 表中 city 属性名含有“京”或“津”的相应 status 属性值增加 100。7将供应商 s2 为“一汽”工程项目所供应的零件数量修改为 20_。8将全部红色零件的颜色修改为浅红色。9由 s5 供给 j4 的零件 p6 改为由 s3 供应，请在数据库中作必要的数据修改。10在

11、 SPJ 表中新增一列属性名为 SDATE 的属性列，对该表中的每一元组在 SDATE 属性列上填上实验当时的日期和时间。开头结尾相互照应！实验一（2.20）实验内容：熟悉sql server 20_5实验目的：掌握sql服务器的启动和停止方法、熟悉management studio 的操作界面和新建查询。重点:使用数据库引擎启动数据库服务器、停止数据库服务；服务器的认识以及如何启动查询分析p 器难点：无实验操作：1.使用数据库引擎启动数据库服务器：开始->程序-> sql server 20_5->sql server management studio->数据库引擎

12、->服务器名称（选择）->连接。2.分别打开数据库，安全性和管理文件夹查看文件内容。3.点击新建查询建立一个查询窗口。4.打开model数据库，查看其中的文件。实验二（2.27）实验内容：使用manegement studio和查询分析p 器创建数据表实验目的：掌握创建数据表的方法重点：学会使用查询分析p 器创建数据表难点：字段数据类型的选择以及数据长度的定义实验操作：在basetest数据库中创建学生表，学生（sno,sname,e_,sage,sdept）。其中sno,sname,e_,sdept字段数据类型设为char，长度为10；sage字段数据类型设为int!create

13、 table 学生实验三（3.5）实验内容：添加表的约束实验目的：掌握使用alter table 语句修改表的时候添加表的约束重点：为表添加主键外键约束难点：为列设置列级完整性约束；使用sql语句为表设置外键。实验操作：1：为学生表添加主键约束pk约束,设置sno为主键alter table 学生add constraint pk约束primary key (sno)实验四（3.12）查询（一）实验目的：掌握Select语句的使用方法实验重点：Select语句的语法结构实验难点：连接查询实验步骤：1.练习查找指定列的查询2.练习查找经过计算的列3.练习为查找字段设置字段别名4.Where子句的

14、使用5.多表连接查询实验五（3.19）查询（二）实验目的：掌握SQL语句的使用方法实验重点：统计查询和嵌套查询和联合查询实验难点：子查询的方法实验步骤：1.1.Compute与Compute By的区别2.子查询 In与E_sits的区别3.比较运算的子查询4.联合查询5.OrderBy子句的使用6.统计函数的使用7.Group By 与Having的使用实验六（3.36）查询（三）实验目的：掌握SQL语句的使用方法实验重点：简单查询和统计查询和嵌套查询实验难点：嵌套查询的方法实验步骤：1.在student表中查询CS系学生信息，并产生一个总人数行2.查询学生信息，统计总人数并对每个系产生一个

15、学生总人数行3.按系分组，并在每组下显示本系中年龄最大学生年龄4.按系和性别分组并统计最大年龄和最小年龄实验七（4.2）实验内容：创建索引实验目的：掌握创建索引的方法重点：掌握索引的基本功能难点：使用sql语句创建索引实验操作：1：使用manegement studio 创建索引2：创建一表myfriend,所包含的属性列为(名字char(10),性别(10),地址(100),并在该表上基于名字创建一个唯一聚集索引wj索引，索引的填充为100。实验八（4.9）实验内容：创建视图实验目的：掌握创建视图的方法重点：掌握视图的基本功能难点：使用sql语句创建视图1、在basetest数据库中创建一个MA系的学生信息视图2、创建IS系女生选课信息，指定字段中文名3、使用WITH CHECK OPTION子句创建男生信息视图4、在CS系学生信息视图中查询女生的信息5、修改cs系学生05008的年龄为21岁实验九（4.16）实验内容：流程控制语句与用户自定义函数的使用实验目的：掌握流程控制语句与用户自定义函数的使用重点：掌握自定义函数的创建难点：表值用户自定义函数的创建实验操作：1：计算1+2+3+100的和2：计算n!(n=