数据库与数据表操作.docx
《数据库与数据表操作.docx》由会员分享,可在线阅读,更多相关《数据库与数据表操作.docx(23页珍藏版)》请在冰豆网上搜索。
![数据库与数据表操作.docx](https://file1.bdocx.com/fileroot1/2023-5/15/80fac01e-fefa-4ecc-8321-4c932d925977/80fac01e-fefa-4ecc-8321-4c932d9259771.gif)
数据库与数据表操作
数据库与数据表操作
实验二数据定义
一、实验目的
1、掌握使用SQL语句创建和删除数据库;
2、掌握使用SQL语句创建和删除数据表,创建各种完整性约束,修改表的结构;
3、掌握索引的创建和删除方法。
二、预备知识
1、数据库的建立与删除
可以使用SQL语句建立数据库,语句格式为:
CREATEDATABASE<数据库名>
其中CREATEDATABASE为关键字,<数据库名>为用户创建的数据库的名称,由用户自定。
使用DROPDATABASE语句可以删除数据库,语句格式为:
DROPDATABASE<数据库名>
其中DROPDATABASE为关键字,<数据库名>为要删除的数据库的名称。
当数据库被删除时,数据库中的所有数据对象也一起被删除掉。
2、数据表的创建与删除
使用CreateTable语句创建数据表,语句格式为:
CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
…
[,<表级完整性约束条件>]);
其中CREATETABLE为关键字,<表名>为数据表的名称,由用户确定,<列名>为组成这个表的属性列的名称,由用户确定,<数据类型>为属性列的取值的类型,由用户在具体的DBMS所提供的数据类型中选择。
使用DropTable语句删除数据表,语句格式为:
DROPTABLE<表名>
注意:
删除一个表,表的定义以及表中的所有数据,以及该表的索引、约束、触发器等将自动删除,并且与表相关联的规则和默认对象也将失去与它的关联关系。
3、修改基本表
使用ALTERTABLE语句修改已建立好的基本表,该语句可以实现添加、修改、删除基本表中的属性列、约束等操作,下面分别给出语句格式及说明:
(1)添加操作
在ALTERTABLE语句中使用ADD子句添加新列和新的完整性约束,添加新列的语句格式如下:
ALTERTABLE<表名>ADD<新列名><数据类型>
添加新的完整性约束语句格式如下:
ALTERTABLE<表名>ADD<完整性约束规则>
(2)修改操作
在ALTERTABLE语句中使用ALTERCOLUMN子句修改原有列的定义,语句格式如下:
ALTERTABLE<表名>ALTERCOLUMN<列名><数据类型>
(3)删除操作
在ALTERTABLE语句中使用DROP子句删除已有的列和完整性约束条件,删除列的语句格式如下:
ALTERTABLE<表名>DROPCOLUMN<列名>
删除完整性约束条件的语句格式为:
ALTERTABLE<表名>DROP<约束名称>
关于ALTERTABLE语句更详细的语法格式请参照“联机丛书”,下面通过实例给出ALTERTABLE语句的具体用法,如下:
操作
类型
实例
添加
添加新列
AltertablesaddSidchar(20)
添加新的完整性约束
Altertablesaddunique(sid)
修改
修改原有列的定义
AltertablesaltercolumnSidchar(30)
删除
删除已有完整性约束
Altertablesdropuq_sid
删除已有列
AltertablesdropcolumnSid
4、索引的创建与删除
使用CREATEINDEX语句创建索引,语句格式为:
CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名称>
ON<表名>(列名[ASC|DESC][,...n])
其中UNIQUE、CLUSTERED、NONCLUSTERED指出所建索引的类型,分别为唯一性索引、聚集索引和非聚集索引。
注意:
不能在数据类型为text、ntext和image的列上建立索引。
使用DROPINDEX语句删除索引,语句格式为:
DROPINDEX<表名.索引名>
注意:
DropIndex语句能够删除CreateIndex语句所建立的索引,但是它不能删除SQLSever为PrimaryKey或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属性列的数据类型必须为int
2.修改表的结构
用SQL语句完成以下操作:
(1)给S表增加Sphone和Semail两个属性列,分别用来存放供应商的联系电话和电子信箱。
(2)删除Jname属性列取值唯一的约束。
(3)将QTY属性列的数据类型修改为Smallint型。
(4)删除S表中的属性列Semail
3.创建、删除索引
通过SQL语句分别在表S、P、J表中的Sno,Pno,Jno属性列上建立唯一索引(升序)
4.数据录入操作
在EXCEl中录入四个表的数据(具体数据可参照课本P75四个表中的数据),用数据导入功能将EXCEL中的数据分别导入到S,P,J和SPJ表中。
5.备份gongcheng数据库。
1.
createdatabasegongcheng
createtableS(Snovarchar(10)primarykey,
Snamevarchar(10)unique,
Statusvarchar(20),
Ctiyvarchar(10)
)
2.
createtableP(Pnovarchar(10)primarykey,
Pnamevarchar(10),
Colorvarchar(10),
Weightfloat(10)check(weight>=1andweight<=50)
)
3.
createtableJ(Jnovarchar(10)primarykey,
Jnamevarchar(10)uniquenotnull,
Ctiyvarchar(10)
)
4.
createtableSPJ(Snovarchar(10)foreignkey(Sno)referencesS(Sno),
Pnovarchar(10)foreignkey(Pno)referencesP(Pno),
Jnovarchar(10)foreignkey(Jno)referencesJ(Jno),
QTYint
)
1.altertableSaddSphonevarchar(20),Semailvarchar(20)
2.altertableJaltercolumnJnamevarchar(10)notnull
3.altertableSPJaltercolumnQTYsmallint
4.altertableSdropcolumnSemail
四.createuniqueindexSnumberonS(Snoasc)
createuniqueindexPnumberonP(Pnoasc)
createuniqueindexJnumberonJ(Jnoasc)
数据导入后
实验三数据查询
一、实验目的
掌握查询语句的使用方法,重点掌握连接查询和嵌套查询,理解查询的执行过程。
二、预备知识
查询语句的基本格式如下:
SELECT<目标列名序列>--需要哪些列
FROM<数据源>--来自于哪些表
[WHERE<检索条件>]--根据什么条件
[GROUPBY<分组依据列>]--分组
[HAVING<组提取条件>]--筛选
[ORDERBY<排序依据列>]--排序
三、实验内容
1、还原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。
2、在“gongcheng”数据库中完成以下查询
(1)查询所有供应商所在的城市。
selectsname,city
froms
(2)查询零件重量在10-20之间(包括10和20)的零件名和颜色。
selectpname,color
fromP
whereweightbetween10and20
(3)查询工程项目的总个数。
selectCOUNT(jno)工程项目的总个数
fromj
(4)查询所有零件的平均重量。
selectAVG(weight)零件的平均质量
fromp
(5)查询供应商S3供应的零件号。
selectsno,pno
fromspj
whereSno='s3'
(6)查询各个供应商号及其供应了多少类零件。
selectsno,COUNT(pno)供应的零件数
fromspj
groupbysno
(7)查询供应了2类以上零件的供应商号。
selectsno,COUNT(pno)供应的零件数
fromspj
groupbysno
havingCOUNT(pno)>2
(8)查询零件名以“螺”字开头的零件信息。
select*
fromp
wherepnamelike'螺%'
(9)查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。
selectjname,city
fromj
wherejnamelike'%厂'
(10)查询给每个工程供应零件的供应商的个数。
selectjno,COUNT(distinctsno)供应商个数
fromSPJ
groupbyjno
(11)查询供应数量在1000—2000之间(包括1000和2000)的零件名称。
selectpname
fromp
wherePnoin
(
selectPno
fromSPJ
groupbyPno
havingsum(QTY)between1000and2000
)
(12)完成课本P127页第5题的前七个小题。
1.selectsname,city
froms
2.selectpname,color,weight
fromp
3.selectjno
fromSPJ
whereSno='s1'
4.SelectP.Pno,SPJ.QTY
FromP,SPJ
WhereSPJ.Jno='J2'andP.Pno=SPJ.Pno
5.selectdistinctpno
froms,spj
wherecity='上海'ands.Sno=SPJ.sno
6.selectjname
fromJ
whereJnoin
(selectJno
fromSPJ
whereSnoin
(selectSno
froms
whereCity='上海'
))
7.
3、在“学生选课管理”数据库中完成以下查询:
(1)查询是“选修课”的课程号、课程名。
selectcno,cname
fromC表
(2)查询学号为“09105103”学生所学课程的课程名与学分。
selectcname,credit
fromC表,SC表,S表
whereS表.Sno='09105103'andS表.Sno=SC表.Sno
andC表.Cno=SC表.Cno
(3)查询选修课程号为“002”或“004”的学生的学号。
selectCno,sno
fromSC表
whereCno='002'orCno='004'
(4)查询选修了课程号为“002”和“004”的学生的学号。
selectsno
fromSC表
whereCno='002'andCno='004'
无
(5)查询学习全部课程的学生姓名。
selectsname
fromS表
wherenotexists(select*
fromC表
wherenotexists
(select*
fromSC表
whereSno=SC表.SnoandCno=C表.Cno))
实验四数据更新与视图
一、实验目的
1、掌握数据更新语句的使用;
2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。
二、预备知识
1.数据更新基本语句及其格式
(1)插入操作
SQL的数据插入语句INSERT有两种形式:
①插入单个元组,使用命令:
INSERTINTO基本表名(列名表)
VALUES(元组)
②插入子查询的结果:
INSERTINTO基本表名(列表名)
查询语句
(2)修改操作
当需要修改指定关系中元组的值时,可使用下列语句实现:
UPDATE基本表名
SET列名1=值表达式1[,列名2=值表达式2…]
[WHERE条件表达式]
(3)删除操作
删除关系中满足条件的元组语句格式如下:
DELETEFROM<表名>
Where<条件表达式>
三、实验内容
1.在实习二创建的“gongcheng”数据库中使用SQL语句完成以下操作:
(1)现有一供应商,代码为S9、姓名为英特尔、所在城市西安,供应情况如下:
供应零件P5给工程J7数量为600,供应零件P4给工程J4数量为500,请将此供应商的信息和供应信息插入数据库。
insert
intos(Sno,Sname,City)
values('s9','英特尔','西安')
insert
intoSPJ
values('s9','p5','j7','600')
insert
intoSPJ
values('s9','p4','j4','500')
(2)请将北京供应商的供应数量加20
updateSPJ
setQTY=QTY+20
whereSnoin
(selectSno
froms
whereCity='北京')
(3)零件P3已经停产,请将P3的相关信息从数据库中删除。
delete
fromSPJ
wherePno='p3'
(4)创建零件名为螺丝刀的供应情况的视图P_ls,包括供应商名(Sname),零件名(Pname),
零件重量(Weight),工程项目代码(Jno),供应数量(QTY).
createviewP_ls(Sname,Pname,Weight,Jno,Qty)
As
selectSname,Pname,Weight,Jno,Qty
froms,P,SPJ
wherePname='螺丝刀'ands.Sno=SPJ.SnoandP.Pno=SPJ.pno
(5)在视图P_ls中查询供应数量为500的供应商姓名。
selectsname
fromP_ls
whereQty=500
groupbysname
2.在实习一创建的“学生选课管理”数据库中使用SQL语句完成以下操作:
(1)学生孙天要退学,请删除该学生的所有记录。
delete
fromSC表
whereSnoin(
selectSno
fromS表
whereSname='孙天')
delete
fromS表
whereSname='孙天'
(2)将学号为09105103学生的所有课程成绩加10分。
updateSC表
setgrade=grade+10
whereSno='09105103'
(3)学校开了一门新课,课称号为056课程名为信息系统分析,请将此门课程的信息插入。
insert
intoC表(Cno,Cname,credit)
values('056','信息系统分析',4)
(4)创建查询每个学生的修课学分的视图,要求列出学生学号及总学分。
createviewCstudent(sno,sname,allCredit)
as
selectS表.sno,S表.sname,SUM(credit)
fromS表,C表,SC表
whereS表.Sno=SC表.snoandC表.Cno=SC表.cno
groupbyS表.Sno,S表.Sname
(5)在创建的视图中查询学生李丽所修的学分数。
selectsname,allCredit
fromCstudent
wheresname='李丽'
3.完成课本P127第5题(8)-(11)和第11题。
5.8。
updateP
setColor='蓝'
whereColor='红'
5.9.
updateSPJ
setSno='s3'
whereSno='s5'andJno='j4'andPno='p6'
5.10
delete
fromSPJ
whereSno='s2'
delete
froms
whereSno='s2'
5.11.
insert
intoSPJ
values('S2','J6','p4',200)
11.1
CREATEVIEWVSP
AS
SELECTSNO,PNO,QTY
FROMSPJ,J
WHERESPJ.JNO=J.JNOANDJ.JNAME='三建'
(1)
SELECTPNO,qty
FROMsanjian
(2)
SELECT*
FROMsanjian
WHERESNO='S1'