《数据库原理及应用》实验

《数据库原理及应用》实验

姓名黄鸿波学号3100717208 班别计本10-2 数据库原理及应用（本科）

实验指导书

-计算机信息教研室-

桂林理工大学信息科学与工程学院

二○一二年五月

1

目录

实验1创建数据库与数据表 (2)

实验2简单查询和连接查询 (10)

实验3嵌套查询和集合查询……………………………………………………………1 2 实验4数据完整性………………………………………………………………………1 4

2

时间：机房号：得分：

实验1 创建数据库与数据表

实验目的：

1．熟悉SQL SERVER环境；

2．掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令；3．掌握数据表的创建和数据记录的插入方法。

实验内容：

1. 创建教学管理“JXGL”数据库，并建立学生STUDENT、课程COURSE和选修SC三个数据表，各表中的数据如下所示：

学生STUDENT：

Sno Sname Ssex Sage Sdept

95001 李勇M 20 CS

95002 刘晨 F 19 IS

95003 王敏 F 18 MA

3

95004 张立M 18 IS 课程COURSE：

Cno Cname Cpno Ccredit

1 数据库 5 4

2 数学 2

1 4

3 信息系

统

4 操作系

6 3

统

7 4

5 数据结

构

2

6 数据处

理

7 C语言 6 4

选修SC：

Sno Cno Grade

95001 1 92

95001 2 85

4

95001 3 88

95002 2 90

95002 3 80

2. 创建供应系统“GYXT”数据库，其中包括供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表，各表中数据如下所示。供应商S：

SNO SNAME CITY

S1 精益天津

S2 万胜北京

S3 东方北京

S4 丰泰隆上海

S5 康健南京

零件P：

PNO PNAME COLOR WEIGHT

P1 螺母红12

P2 螺栓绿17

P3 螺丝刀蓝14

5

P4 螺丝刀红14

P5 凸轮蓝40

P6 齿轮红30 工程项目J：

JNO JNAME CITY

J1 三建北京

J2 一汽长春

J3 弹簧厂天津

J4 造船厂天津

J5 机车厂唐山

常州

J6 无线电

厂

南京

J7 半导体

厂

供应情况SPJ：

SNO PNO JNO QTY

S1 P1 J1 200

S1 P1 J3 100

6

S1 P1 J4 700

S1 P2 J2 100

S2 P3 J1 400

S2 P3 J2 200

S2 P3 J4 500

S2 P3 J5 400

S2 P5 J1 400

S2 P5 J2 100

S3 P1 J1 200

S3 P3 J1 200

S4 P5 J1 100

S4 P6 J3 300

S4 P6 J4 200

S5 P2 J4 100

S5 P3 J1 200

S5 P6 J2 200

S5 P6 J4 500

实验步骤：

1．创建教学管理“JXGL”数据库。(注：可

7

采用可视窗体和SQL命令两种方法创建。以下是SQL命令方法)

(1) 创建教学管理“JXGL”数据库。

在命令窗格中输入如下命令，然后单击“运行”钮执行该命令。

CREATE DATABASE JXGL;

(2) 清空命令窗格后，在JXGL数据库中建立STUDENT表，并插入记录，然后执行。

CREATE TABLE STUDENT

( Sno char(5) not null unique,

Sname char(20) not null unique,

Ssex char(1),

Sage int,

Sdept char(20));

INSERT INTO STUDENT V ALUES(‘95001’,’李勇’,’M’,20,’CS’);

INSERT INTO STUDENT V ALUES(‘95002’,’刘晨’,’F’,19,’IS’);

INSERT INTO STUDENT

8

V ALUES(‘95003’,’王敏’,’F’,18,’MA’);

INSERT INTO STUDENT V ALUES(‘95004’,’张立’,’M’,18,’IS’);

(3) 清空查询窗格后，JXGL数据库中建立COURSE表，并插入记录，然后执行。

CREATE TABLE COURSE

( Cno char(2) not null PRIMARY KEY(Cno),

Cname char(20),

Cpno char(2),

Ccredit smallint);

INSERT INTO COURSE V ALUES(‘1’,’数据库’,’5’,4);

INSERT INTO COURSE V ALUES(‘2’,’数学’,’’,2);

INSERT INTO COURSE V ALUES(‘3’,’信息系统’,’1’,4);

INSERT INTO COURSE V ALUES(‘4’,’操作系统’,’6’,3);

9

INSERT INTO COURSE V ALUES(‘5’,’数据结构’,’7’,4);

INSERT INTO COURSE V ALUES(‘6’,’数据处理’,’’,2);

INSERT INTO COURSE V ALUES(‘7’,’C语言’,’6’,4);

(4) 清空查询窗格后，JXGL数据库中建立SC表，并插入记录，然后执行。

CREATE TABLE SC

( Sno char(5) not null,

Cno char(2) not null,

Grade smallint,

PRIMARY KEY(Sno,Cno),

FOREIGN KEY(Sno) REFERENCES STUDENT(Sno),

FOREIGN KEY(Cno) REFERENCES COURSE(Cno));

INSERT INTO SC V ALUES(‘95001’,‘1’,92);

10

INSERT INTO SC V ALUES(‘95001’,‘2’,85);

INSERT INTO SC V ALUES(‘95001’,‘3’,88);

INSERT INTO SC V ALUES(‘95002’,‘2’,90);

INSERT INTO SC V ALUES(‘95002’,‘3’,80);

(5) 查看三个表中的内容。

分别执行以下命令，查看STUDENT、COURSE和SC数据表中的内容。

SELECT * FROM STUDENT;

SELECT * FROM COURSE;

SELECT * FROM SC;

(6) ALTER TABLE、DROP TABLE、DROP DATABASE命令运用。

①向STUDENT表增加“入学时间”列，其数据类型为日期型，用SELECT命令查看表中内容。

11

ALTER TABLE STUDENT ADD SCOME DATETIME;

SELECT * FROM STUDENT;

②删除“入学时间”列，再用SELECT命令查看表中内容。

ALTER TABLE STUDENT DROP COLUMN SCOME；

SELECT * FROM STUDENT;

③删除数据表。

DROP TABLE SC;

DROP TABLE STUDENT;

DROP TABLE COURSE;

④删除数据库命令。

切换到其他任意数据库，然后可用如下命令删除JXGL数据库。

DROP DATABASE JXGL;

重复(1)~ (4)过程，建立数据库备以后的查询使用。

2．创建供应系统“GYXT”数据库。(注：可

12

采用可视窗体和SQL命令两种方法创建。) 下面写出实现如下操作的SQL语句：

(1) 创建供应系统“GYXT”数据库。

CREATE DATABASE GYXT;

(2) 建立供应商表S。

CREATE TABLE S

(SNO CHAR(5) PRIMARY KEY,

SNAME CHAR(6) NOT NULL,

CITY CHAR(10) NOT NULL

);

(3) 建立零件表P。

CREATE TABLE P

(PNO CHAR(2)PRIMARY KEY,

PNAME CHAR(6)NOT NULL,

COLOR CHAR(2)DEFAULT('红'),

WEIGHT INT,

);

(4) 建立工程项目表J。

CREATE TABLE J

(JNO CHAR(5)PRIMARY KEY,

JNAME CHAR(10)NOT NULL,

CITY CHAR(10)NOT NULL

);

);*/

CREATE TABLE SPJ (5) 建立供应情况表SPJ。

13

CREATE TABLE SPJ

(SNO char(5)not null,

PNO char(2)not null,

JNO char(5)not null,

QTY int,

PRIMARY KEY(SNO,PNO,JNO),

FOREIGN KEY(SNO)REFERENCES S(SNO),

FOREIGN KEY(PNO)REFERENCES P(PNO),

FOREIGN KEY(JNO)REFERENCES J(JNO),

);

(6) 应用INSERT INTO命令将相应数据写入到供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表。

INSERT INTO S VALUES('S1','精益','天津');

INSERT INTO S VALUES('S2','万胜','北京');

INSERT INTO S VALUES('S3','东方','北京');

INSERT INTO S VALUES('S4','丰泰隆','上海');

INSERT INTO S VALUES('S5','南京','南京');*/

INSERT INTO P VALUES('P1','螺母','红','12');

INSERT INTO P VALUES('P2','螺栓','绿','17');

INSERT INTO P VALUES('P3','螺丝刀','蓝','14');

INSERT INTO P VALUES('P4','螺丝刀','红','14');

INSERT INTO P VALUES('P5','凸轮','蓝','40');

INSERT INTO P VALUES('P6','齿轮','红','30');

INSERT INTO J VALUES('J1','三建','北京');

INSERT INTO J VALUES('J2','一汽','长春');

INSERT INTO J VALUES('J3','弹簧厂','天津');

INSERT INTO J VALUES('J4','造船厂','天津');

INSERT INTO J VALUES('J5','机车厂','唐山');

INSERT INTO J VALUES('J6','无线电厂','常州');

INSERT INTO J VALUES('J7','半导体厂','南京'

INSERT INTO SPJ VALUES('S1','P1','J1','200');

INSERT INTO SPJ VALUES('S1','P1','J3','100');

INSERT INTO SPJ VALUES('S1','P1','J4','700');

INSERT INTO SPJ VALUES('S1','P2','J2','100');

14

INSERT INTO SPJ VALUES('S2','P3','J1','400'); INSERT INTO SPJ VALUES('S2','P3','J2','200'); INSERT INTO SPJ VALUES('S2','P3','J4','500'); INSERT INTO SPJ VALUES('S2','P3','J5','400'); INSERT INTO SPJ VALUES('S2','P5','J1','400'); INSERT INTO SPJ VALUES('S2','P5','J2','100'); INSERT INTO SPJ VALUES('S3','P1','J1','200'); INSERT INTO SPJ VALUES('S3','P3','J1','200'); INSERT INTO SPJ VALUES('S4','P5','J1','100'); INSERT INTO SPJ VALUES('S4','P6','J3','300'); INSERT INTO SPJ VALUES('S4','P6','J4','200'); INSERT INTO SPJ VALUES('S5','P2','J4','100'); INSERT INTO SPJ VALUES('S5','P3','J1','200'); INSERT INTO SPJ VALUES('S5','P6','J2','200'); INSERT INTO SPJ VALUES('S5','P6','J4','500');

15

时间：机房号：得分：

实验2 简单查询和连接查询

实验目的：

1．熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2．熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

实验内容：

1．基于实验一创建的教学管理JXGL数据库，实现数据的基本查询操作。

2．基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。

实验步骤：

写出下列操作的SQL语句。

1．在教学管理JXGL数据库中进行如下操作：

(1) 求数学系学生的学号和姓名。

USE JXGL

SELECT Sno,Sname

FROM STUDENT

16

WHERE Sdept='MA';

(2) 求选修了课程的学生学号。

USE JXGL

SELECT distinct Sno

FROM sc;

(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

SELECT Sno,Grade

FROM SC

WHERE Cno=2

ORDER BY Grade DESC,Sno ASC;

(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

SELECT Sno,Grade=0.8*Grade

FROM SC

WHERE Cno=2 AND Grade BETWEEN 80 AND 90

ORDER BY Grade DESC,Sno ASC;

(5) 求数学系或计算机系姓刘的学生的信息。

SELECT*

FROM STUDENT

WHERE Sname LIKE'刘%'AND(Sdept='MA'OR Sdept='IS')

17

(6) 求缺少了成绩的学生的学号和课程号。

SELECT Sno,Cno

FROM SC

WHERE Grade IS NULL;

(7) 查询每个学生的情况以及他（她）所选修的课程。

SELECT STUDENT.Sno, Sname Ssex, Sage,Sdept,https://www.360docs.net/doc/df16984550.html,o,Grade,Cname FROM STUDENT, SC ,COURSE

WHERE STUDENT.Sno = SC.Sno AND https://www.360docs.net/doc/df16984550.html,o=https://www.360docs.net/doc/df16984550.html,o

(8) 求学生的学号、姓名、选修的课程名及成绩。

SELECT STUDENT.Sno, Sname,Grade,Cname

FROM STUDENT, SC ,COURSE

WHERE STUDENT.Sno = SC.Sno AND https://www.360docs.net/doc/df16984550.html,o=https://www.360docs.net/doc/df16984550.html,o

(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。

SELECT STUDENT.Sno, Sname,Grade,Cname

FROM STUDENT, SC ,COURSE

WHERE STUDENT.Sno = SC.Sno AND https://www.360docs.net/doc/df16984550.html,o=https://www.360docs.net/doc/df16984550.html,o AND

SC.Grade>=90

AND https://www.360docs.net/doc/df16984550.html,ame='数学';

(10)查询每一门课的间接先行课（即先行课的先行课）。

SELECT https://www.360docs.net/doc/df16984550.html,o,https://www.360docs.net/doc/df16984550.html,ame,https://www.360docs.net/doc/df16984550.html,o,https://www.360docs.net/doc/df16984550.html,ame

from COURSE t1, COURSE t2,COURSE t3

WHERE t1.Cpno=https://www.360docs.net/doc/df16984550.html,o and t2.Cpno=https://www.360docs.net/doc/df16984550.html,o

18