数据定义和查询

实验二   数据定义和查询

一、实验目的

1、掌握使用SQL语句创建和删除数据表，创建各种完整性约束。

2、掌握使用SQL语句修改表的结构。

3、掌握查询语句的使用方法，重点掌握连接查询和嵌套查询。

二、实验内容

1．使用SQL语句建立4个关系，如下：

供应商表S （Sno, Sname, City）

零件表P（Pno, Pname, Color, Weight）

工程项目表J（Jno, Jname, City）

供应情况表 SPJ（Sno, Pno, Jno, QTY）

其中：

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商所在城市（CITY）组成；

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；

工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；

供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量组成（QTY），表示某供应商供应某种零件给某工程项目的数量为QTY。

数据如下：

要求：

（1）创建每个关系的主键，有外键的创建外键。

（2）S表中的Sname属性列的取值唯一。

（3）P表中weight属性列的取值范围在1-50之间。

（4）J表中的Jname取值不能为空并且是唯一的。

（5）SPJ表中QTY属性列的数据类型必须为NUMBER(5)。

2．用SQL语句完成以下操作：

（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。

（2）删除Jname属性列取值唯一的约束。

（3）将QTY属性列的数据类型修改为Integer型。

（4）删除S表中的属性列Semail

3．在J表的Jname属性列上创建唯一性索引。

4．练习EXP和IMP命令的使用方法，使用EXP命令将数据表导出。

5．完成以下查询：

（1）查询所有供应商所在的城市。

（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。

（3）查询工程项目的总个数。

（4）查询所有零件的平均重量。

（5）查询供应商S3供应的零件号。

（6）查询各个供应商号及其供应了多少类零件。

（7）查询供应了2类以上零件的供应商号。

（8）查询零件名以“螺”字开头的零件信息。

（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。

（10）查询给每个工程供应零件的供应商的个数。

（11）查询供应数量在1000—2000之间（包括1000和2000）的零件名称。

6．在实验一创建的表中完成以下查询：

（1）查询“信息管理与信息系统”专业学生的姓名和年龄。

（2）查询成绩在70-80分（包括70和80分）之间的学生的学号、课程号和成绩。

（3）查询107号课程的最高成绩。

（4）统计每个专业的学生人数。

（5）统计每门课程的修课人数和考试最高分。

（6）统计每个学生的选课门数和考试总成绩，并按总成绩的递减顺序显示结果。rou

Select sno,count(cno),sum(grade)

From spj

Group by sno

Order by sum(grade)

（7）查询总成绩超过200分的学生，要求列出学号和总成绩。

（8）查询姓名为田丕龙的学生所学课程的课程名与学分。

Select cname,credit

From s,c,sc

Where s.sno=sc.sno and

         c.cno=sc.cno and

         sname=’ 田丕龙’;

（9）查询选修课程号为“160”或“304”的学生的学号。

（10）查询选修了课程号为“160”和“304”的学生的学号。

（11）查询学习全部课程的学生姓名。

（12）查询1994年1月1日以前出生的学生的姓名和专业。

（13）查询选修了“大学英语4”课程且成绩在90分以上的学生姓名。

（14）查询选修了5门以上课程的学生学号和姓名。

（15）查询未选修“政治经济学”课程的学生情况。

（16）统计102和378号课程的选课人数及平均成绩。

（17）查询比所有“信息管理与信息系统”专业学生年龄都大的学生。

（18）将“计算机科学与技术”专业的学生按出生时间先后排序。

三，实验总结

1，本次实验内容主要涉及基本表得定义，删除，修改，建立索引，数据查询

2，定义基本表的时候注意列级完整性约束和表级完整性约束的使用方法，主码由两个属性构成时必须作为表级完整性约束进行定义。定义外码需要使用表及完整性约束条件。

3，建立索引一般要加上unique，unique表明此索引的每一个索引值只对应唯一的数据记录

4，做单表查询的过程中你，先看需要查询那些属性列，判断需要查询的属性列是否都在同一个表中，最后考虑查询条件

5，查询某一列需要用小写字母表示时，加上lower（）

6，可以通过指定别名来改变查询结果的列标题，如 select sname姓名 from student

7，消除取值相同的行时可以用distinct

8，注意有关like，notlike使用过程中%和/的使用方法，注意当要查询的字符串本身就含有%和/时，就要用escape对通配符进行转义。注意like，not like后面的匹配串不含通配符时，可以用=，<>进行替换

9，Order by 语句用于对查询结果的一个或者多个属性进行排序，默认为升序，将序排列使用desc

10，           聚集函数包括count，sum,avg,max,min等，特别注意，聚集函数只用于select子句和having子句中。Count空值也会进行处理，其他都跳过空值只处理非空值

11，           一般having和order by同时使用，因为having短语作用于组，从中选择满足条件的组

12，           连接查询：当查询涉及两个或两个以上的表时，通常使用连接查询，注意，当两个表都出现sno或者cno时，引用时必须加上表的前缀

13，           当涉及到出生日期或者是年龄的比较时，注意对birth类型进行转化

14，           In谓词的使用：当子查询的结果是一个集合时，in在嵌套查询中经常使用

15，           若子查询的返回结果只有一个，可以用=代替in

16，           有关Any和all之类的查询一般要使用嵌套查询，注意any，all也可以用min，max谓词的使用来代替

17，           Union，intersect，minus的使用主要是针对同一张表来说的

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！