《数据库与数据表操作.pdf》由会员分享，可在线阅读，更多相关《数据库与数据表操作.pdf（17页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、数据库与数据表操作数据库与数据表操作实验二数据定义一、实验目的一、实验目的1、掌握使用 SQL 语句创建和删除数据库；2、掌握使用 SQL 语句创建和删除数据表，创建各种完整性约束，修改表的结构；3、掌握索引的创建和删除方法。二、预备知识二、预备知识1 1、数据库的建立与删除、数据库的建立与删除可以使用 SQL 语句建立数据库，语句格式为：CREATEDATABASE其中 CREATEDATABASE为关键字，为用户创建的数据库的名称，由用户自定。使用 DROPDATABASE 语句可以删除数据库，语句格式为：DROPDATABASE其中 DROPDATABASE为关键字，为要删除的数据库的名
2、称。当数据库被删除时，数据库中的所有数据对象也一起被删除掉。2 2、数据表的创建与删除、数据表的创建与删除使用 CreateTable 语句创建数据表，语句格式为：CREATETABLE 列级完整性约束条件, 列级完整性约束条件,;其中 CREATE TABLE为关键字，为数据表的名称，由用户确定，为组成这个表的属性列的名称，由用户确定，为属性列的取值的类型，由用户在具体的 DBMS 所提供的数据类型中选择。使用 DropTable 语句删除数据表，语句格式为：DROPTABLE注意：删除一个表注意：删除一个表 ，表的定义以及表中的所有数据，以及该表的索引、约束、，表的定义以及表中的所有数据，
3、以及该表的索引、约束、触发器等将自动删除触发器等将自动删除，并且与表相关联的规则和默认对象也将失去与它的关联关并且与表相关联的规则和默认对象也将失去与它的关联关系。系。3 3、修改基本表、修改基本表使用 ALTER TABLE 语句修改已建立好的基本表，该语句可以实现添加、修改、删除基本表中的属性列、约束等操作，下面分别给出语句格式及说明：1添加操作在 ALTERTABLE 语句中使用 ADD 子句添加新列和新的完整性约束，添加新列的语句格式如下：ALTERTABLEADD添加新的完整性约束语句格式如下：ALTERTABLEADD2修改操作在 ALTERTABLE 语句中使用 ALTERCOL
4、UMN 子句修改原有列的定义，语句格式如下：ALTERTABLEALTERCOLUMN3删除操作在 ALTER TABLE 语句中使用 DROP 子句删除已有的列和完整性约束条件，删除列的语句格式如下：ALTERTABLEDROPCOLUMN删除完整性约束条件的语句格式为：ALTERTABLEDROP关于 ALTER TABLE 语句更详细的语法格式请参照“联机丛书”，下面通过实例给出 ALTERTABLE 语句的具体用法，如下：操作操作添加修改删除类型类型添加新列添加新的完整性约束修改原有列的定义删除已有完整性约束删除已有列实例实例Altertablesad dSidchar(20)Alte
5、rtablesad dunique(sid)AltertablesaltercolumnSidchar(30)Altertablesdropuq_sidAltertablesdropcolumnSid4 4、索引的创建与删除、索引的创建与删除使用 CREATEINDEX 语句创建索引，语句格式为：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXON 列名 ASC|DESC,.n其中 UNIQUE、CLUSTERED、NONCLUSTERED 指出所建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。注意：不能在数据类型为注意：不能在数据类型为 texttext、
6、ntextntext 和和 imageimage 的列上建立索引。的列上建立索引。使用 DROPINDEX 语句删除索引，语句格式为：DROPINDEX注意：注意：DropDropIndexIndex 语句能够删除语句能够删除 CreateCreate IndexIndex 语句所建立的索引，但语句所建立的索引，但是它不能删除是它不能删除 SQLSQL SeverSever 为为 PrimaryPrimary KeyKey 或或 UniqueUnique 约束所建立的索引，这约束所建立的索引，这些索引只能通过删除约束或表的方法来删除。些索引只能通过删除约束或表的方法来删除。二、实验内容1建立数
7、据表在创建的“gongcheng”数据库中使用 SQL 语句建立 4 个关系可参考课本P74 习题 5 ，如下：供给商表 S Sno,Sname,Status,Ctiy 零件表 PPno,Pname,Color,Weight工程项目表 JJno,Jname,City供给情况表 SPJSno,Pno,Jno,QTY要求：1创建每个关系的主键，有外键的创建外键。2S 表中的 Sname 属性列的取值唯一3P 表中 weight 属性列的取值范围在 1-50 之间4J 表中的 Jname 取值不能为空并且是唯一的5SPJ 表中 QTY 属性列的数据类型必须为 int2修改表的结构用 SQL 语句完成
8、以下操作：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
9、 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(1
10、0) uniquenot 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)1alter table S add Sphone varchar(20), Semail varchar(20)2alter table J alter column
11、Jname varchar(10) not null3alter table SPJ alter column QTY smallint4alter 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)数据导入后四.实验三数据查询一、实验目的一、实验目的掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。二、预备知识二、预备知识查
12、询语句的基本格式如下：SELECT-需要哪些列FROM-来自于哪些表WHERE-根据什么条件GROUPBY-分组HAVING-筛选ORDERBY-排序三、实验内容三、实验内容1、复原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。2、在“gongcheng”数据库中完成以下查询1查询所有供给商所在的城市。select sname,cityfrom s2查询零件重量在 10-20 之间包括 10 和 20的零件名和颜色。select pname,colorfrom Pwhere weight between 10 and 203查询工程项目的总个数。select
13、 COUNT(jno)工程项目的总个数from j4查询所有零件的平均重量。select AVG(weight)零件的平均质量from p5查询供给商 S3 供给的零件号。select sno,pnofrom spjwhere Sno=s36查询各个供给商号及其供给了多少类零件。select sno,COUNT(pno)供给的零件数from spjgroup by sno7查询供给了 2 类以上零件的供给商号。select sno,COUNT(pno)供给的零件数from spjgroup by snohaving COUNT(pno)28查询零件名以“螺”字开头的零件信息。select *f
14、rom pwhere pname like 螺%9查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。select jname ,cityfrom jwhere jname like %厂10查询给每个工程供给零件的供给商的个数。select jno ,COUNT(distinct sno)供给商个数from SPJgroup by jno11查询供给数量在 10002000 之间包括 1000 和 2000的零件名称。select pnamefrom pwhere Pno in(select Pnofrom SPJgroup by Pnohaving sum( QTY) between
15、 1000 and 2000)12完成课本 P127 页第 5 题的前七个小题。1select sname,cityfrom s2select pname,color,weightfrom p3.select jnofrom SPJwhere Sno=s14Select P.Pno ,SPJ.QTYFrom P ,SPJWhere SPJ .Jno=J2 andP .Pno =SPJ .Pno5select distinct pnofrom s,spjwhere city=上海 and s.Sno=SPJ.sno6select jnamefrom Jwhere Jno in(select Jn
16、ofrom SPJwhere Sno in( select Snofrom swhere City=上海)73、在“学生选课管理”数据库中完成以下查询：1查询是“选修课”的课程号、课程名。select cno,cnamefrom C 表2查询学号为“09105103”学生所学课程的课程名与学分。select cname,creditfrom C表,SC表,S表where S表.Sno=09105103 and S表.Sno=SC表.Snoand C 表 o=SC 表 o3查询选修课程号为“002”或“004”的学生的学号。select Cno, snofrom SC表where Cno=002
17、 or Cno =0044查询选修了课程号为“002”和“004”的学生的学号。select snofrom SC表where Cno=002 and Cno =004无5查询学习全部课程的学生。select snamefrom S表where not exists(select *from C表where not exists(select *from SC表where Sno=SC 表.Sno and Cno=C 表 o)实验四数据更新与视图一、实验目的一、实验目的1、掌握数据更新语句的使用；2、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过程。二、预备知识二、预备知识
18、1数据更新基本语句及其格式1插入操作SQL 的数据插入语句 INSERT 有两种形式： 插入单个元组，使用命令：INSERTINTO 基本表名 (列名表)VALUES(元组) 插入子查询的结果：INSERTINTO 基本表名列表名查询语句2修改操作当需要修改指定关系中元组的值时，可使用以下语句实现：UPDATE 基本表名SET 列名 1值表达式 1，列名 2值表达式 2WHERE 条件表达式3删除操作删除关系中满足条件的元组语句格式如下:DELETEFROMWhere三、实验内容三、实验内容1在实习二创建的“gongcheng”数据库中使用 SQL 语句完成以下操作：1现有一供给商，代码为S9
19、、为英特尔、所在城市西安，供给情况如下：供给零件 P5 给工程 J7 数量为 600，供给零件 P4 给工程 J4 数量为 500，请将此供给商的信息和供给信息插入数据库。insertinto s(Sno,Sname,City)values (s9,英特尔,西安)insertinto SPJvalues (s9,p5,j7,600)insertinto SPJvalues (s9,p4,j4,500)2请将北京供给商的供给数量加 20update SPJset QTY=QTY+20where Sno in(select Snofrom swhere City=北京)3零件 P3 已经停产，请将
20、 P3 的相关信息从数据库中删除。deletefrom SPJwhere Pno=p34创建零件名为螺丝刀的供给情况的视图 P_ls，包括供给商名Sname ，零件名Pname ，零件重量Weight ，工程项目代码Jno ，供给数量QTY.create view P_ls(Sname,Pname,Weight,Jno,Qty)Asselect Sname,Pname,Weight,Jno,Qtyfrom s,P,SPJwhere Pname=螺丝刀 and s.Sno=SPJ.Sno and P.Pno=SPJ.pno5在视图 P_ls中查询供给数量为 500 的供给商。select sna
21、mefrom P_lswhere Qty=500group by sname2在实习一创建的“学生选课管理”数据库中使用 SQL 语句完成以下操作：1学生孙天要退学，请删除该学生的所有记录。deletefrom SC表where Sno in(select Snofrom S表where Sname=孙天)deletefrom S表where Sname=孙天2将学号为 09105103 学生的所有课程成绩加 10 分。update SC表set grade=grade+10where Sno=091051033学校开了一门新课，课称号为056 课程名为信息系统分析，请将此门课程的信息插入。i
22、nsertinto C表(Cno,Cname,credit)values(056,信息系统分析,4)4创建查询每个学生的修课学分的视图，要求列出学生学号及总学分。create view Cstudent(sno,sname,allCredit)asselect S表.sno,S表.sname,SUM(credit)from S表,C表,SC表where S表.Sno=SC表.sno and C表 o=SC表 ogroup by S表.Sno,S表.Sname5在创建的视图中查询学生李丽所修的学分数。select sname,allCreditfrom Cstudentwhere sname =
23、李丽3完成课本 P127 第 5 题811和第 11 题。5.8 。update PsetColor=蓝whereColor=红5.9.update SPJset Sno=s3where Sno=s5 and Jno=j4 and Pno=p6deletefrom SPJwhere Sno=s2deletefrom swhere Sno=s25.11.insertinto SPJvalues(S2,J6,p4,200)CREATE VIEW VSPASSELECT SNO,PNO,QTYFROM SPJ,JWHERE SPJ.JNO=J.JNO AND J.JNAME=三建1SELECTPNO,qtyFROMsanjian2 2SELECT*FROM sanjianWHERE SNO=S1