数据库与数据表操作

数据库与数据表操作数据库与数据表操作实验二数据定义一、实验目的1、掌握使用SQL语句创建和删除数据库；2、掌握使用SQL语句创建和删除数据表，创建各种完整性约束，修改表的结构；3、掌握索引的创建和删除方法。

二、预备知识1、数据库的建立与删除可以使用SQL语句建立数据库，语句格式为：CREATE DATABASE <数据库名>其中CREATE DATABASE为关键字，<数据库名>为用户创建的数据库的名称，由用户自定。

使用DROP DATABASE 语句可以删除数据库，语句格式为：DROP DATABASE <数据库名>其中DROP DATABASE为关键字，<数据库名>为要删除的数据库的名称。

当数据库被删除时，数据库中的所有数据对象也一起被删除掉。

2、数据表的创建与删除使用Create Table语句创建数据表，语句格式为：CREATE TABLE <表名>（<列名> <数据类型> [列级完整性约束条件][,<列名> <数据类型> [列级完整性约束条件]]…[,<表级完整性约束条件>]）;其中CREATE TABLE为关键字，<表名>为数据表的名称，由用户确定，<列名>为组成这个表的属性列的名称，由用户确定，<数据类型>为属性列的取值的类型，由用户在具体的DBMS所提供的数据类型中选择。

使用Drop Table 语句删除数据表，语句格式为：DROP TABLE <表名>注意：删除一个表，表的定义以及表中的所有数据，以及该表的索引、约束、触发器等将自动删除，并且与表相关联的规则和默认对象也将失去与它的关联关系。

3、修改基本表使用AL TER TABLE语句修改已建立好的基本表，该语句可以实现添加、修改、删除基本表中的属性列、约束等操作，下面分别给出语句格式及说明：（1）添加操作在AL TER TABLE语句中使用ADD子句添加新列和新的完整性约束，添加新列的语句格式如下：AL TER TABLE <表名> ADD <新列名> <数据类型> 添加新的完整性约束语句格式如下：AL TER TABLE <表名> ADD <完整性约束规则> （2）修改操作在AL TER TABLE语句中使用AL TER COLUMN子句修改原有列的定义，语句格式如下：AL TER TABLE <表名> AL TER COLUMN <列名> <数据类型> （3）删除操作在AL TER TABLE语句中使用DROP子句删除已有的列和完整性约束条件，删除列的语句格式如下：AL TER TABLE <表名> DROP COLUMN <列名>删除完整性约束条件的语句格式为：AL TER TABLE <表名> DROP <约束名称>关于AL TER TABLE语句更详细的语法格式请参照“联机丛书”，下面通过实例给出AL TER TABLE语句的具体用法，如下：4、索引的创建与删除使用CREATE INDEX语句创建索引，语句格式为：CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名称>ON <表名>（列名[ ASC | DESC ] [ ,...n ] ）其中UNIQUE、CLUSTERED、NONCLUSTERED 指出所建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。

注意：不能在数据类型为text、ntext和image的列上建立索引。

使用DROP INDEX语句删除索引，语句格式为：DROP INDEX <表名.索引名>注意：Drop Index语句能够删除Create Index语句所建立的索引，但是它不能删除SQL Sever为Primary Key或Unique约束所建立的索引，这些索引只能通过删除约束或表的方法来删除。

二、实验内容1．建立数据表在创建的“gongcheng”数据库中使用SQL语句建立4个关系（可参考课本P74习题5），如下：供应商表S （Sno,Sname,Status,Ctiy）零件表P（Pno,Pname,Color,Weight）工程项目表J（Jno,Jname,City）供应情况表SPJ（Sno,Pno,Jno,QTY）要求：（1）创建每个关系的主键，有外键的创建外键。

（2）S表中的Sname属性列的取值唯一（3）P表中weight属性列的取值范围在1-50之间（4）J表中的Jname取值不能为空并且是唯一的（5）SPJ表中QTY属性列的数据类型必须为int2．修改表的结构用SQL语句完成以下操作：（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。

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

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

（4）删除S表中的属性列Semail3．创建、删除索引通过SQL语句分别在表S、P、J表中的Sno，Pno，Jno属性列上建立唯一索引（升序）4．数据录入操作在EXCEl中录入四个表的数据（具体数据可参照课本P75四个表中的数据），用数据导入功能将EXCEL中的数据分别导入到S，P，J和SPJ表中。

5．备份gongcheng数据库。

1.create database gongchengcreate table S(Sno varchar(10)primary key,Sname varchar(10)unique,Status varchar(20),Ctiy varchar(10))2.create table P(Pno varchar(10)primary key,Pname varchar(10),Color varchar(10),Weight float(10)check(weight>=1 and weight<=50))3.create table J(Jno varchar(10)primary key,Jname varchar(10)unique not null,Ctiy varchar(10))4.create table SPJ(Sno varchar(10)foreign key (Sno)references S(Sno), Pno varchar(10)foreign key (Pno)references P(Pno),Jno varchar(10)foreign key (Jno)references J(Jno),QTY int)1．alter table S add Sphone varchar(20),Semail varchar(20)2．alter table J alter column Jname varchar(10)not null3．alter table SPJ alter column QTY smallint4．alter table S drop column Semail四. create unique index Snumber on S(Sno asc)create unique index Pnumber on P(Pno asc) create unique index Jnumber on J(Jno asc)数据导入后实验三数据查询一、实验目的掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。

二、预备知识查询语句的基本格式如下：SELECT <目标列名序列> --需要哪些列FROM <数据源> --来自于哪些表[WHERE <检索条件>] --根据什么条件[GROUP BY <分组依据列>] --分组[HAVING <组提取条件>] --筛选[ORDER BY <排序依据列>] --排序三、实验内容1、还原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。

2、在“gongcheng”数据库中完成以下查询（1）查询所有供应商所在的城市。

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

select pname,colorfrom Pwhere weight between 10 and 20（3）查询工程项目的总个数。

select COUNT(jno)工程项目的总个数from j（4）查询所有零件的平均重量。

select AVG(weight)零件的平均质量from p（5）查询供应商S3供应的零件号。

select sno,pnofrom spjwhere Sno='s3'（6）查询各个供应商号及其供应了多少类零件。

select sno,COUNT(pno)供应的零件数from spjgroup by sno（7）查询供应了2类以上零件的供应商号。

select sno,COUNT(pno)供应的零件数from spjgroup by snohaving COUNT(pno)>2（8）查询零件名以“螺”字开头的零件信息。

select*from pwhere pname like'螺%'（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。

select jname,cityfrom jwhere jname like'%厂'（10）查询给每个工程供应零件的供应商的个数。

select jno,COUNT(distinct sno)供应商个数from SPJgroup by jno（11）查询供应数量在1000—2000之间（包括1000和2000）的零件名称。

select pnamefrom pwhere Pno in(select Pnofrom SPJgroup by Pnohaving sum(QTY)between 1000 and 2000)（12）完成课本P127页第5题的前七个小题。

1．select sname,cityfrom s2．select pname,color,weightfrom p3.select jnofrom SPJwhere Sno='s1'4．Select P.Pno,SPJ.QTYFrom P,SPJWhere SPJ.Jno='J2'and P.Pno=SPJ.Pno5．select distinct pnofrom s,spjwhere city='上海'and s.Sno=SPJ.sno6．select jnamefrom Jwhere Jno in(select Jnofrom SPJwhere Sno in(select Snofrom swhere City='上海'))7．3、在“学生选课管理”数据库中完成以下查询：（1）查询是“选修课”的课程号、课程名。