实验3 数据查询与更新

实验三SQL查询与更新实验（SQL3学时）实验三 SQL查询与更新实验（SQL 3学时）§3.1实验目的与要求（1）熟悉SQL DML语言，能够完成基本的表操作；（2）掌握SELECT语句进行单表查询的方法;（3）掌握SELECT语句进行多表连接查询的方法;（4）了解嵌套查询的方法；（5）了解外连接查询的方法；（6）了解集合函数的使用方法；（7）了解表更新操作的使用方法。

§3.2 实验系统及软件要求：1、WindowsXP操作系统；2、SQL SERVER 2005中文版软件；§3.3 实验内容§3.3.1简单SELECT语句1. SELECT的语法格式SELECT 语句的基本语法格式如下：SELECT <字段列表>[INTO 新表名]FROM <表名/视图名列表>[WHERE 条件表达式][GROUP BY 列名列表] [HAVING 条件表达式][ORDER BY列名1[ASC|DESC]，列名2 [ASC|DESC]，．．．，列名n[ASC|DESC]][COMPUTE 行聚合函数名（统计表达式）[ ,…n] [BY 分类表达式[,…n]]]其中各子名说明如下：（1）字段列表用于指出要查询的字段，也就是查询结果中包含的字段的名称。

（2）INTO子句用于创建一个新表，并将查询结果保存到这个新表中。

（3）FROM子句用于指出所要进行查询的数据来源，即来源于哪些表或视图的名称。

（4）WHERE子句用于指出查询数据时要满足的检索条件。

（5）GROUP BY子句用于对查询结果分组。

（6）ORDER BY子句用于对查询结果排序。

SELECT语句的功能如下：从FROM列出的数据源表中，找出满足WHERE检索条件的记录，按SELECT子句的字段列表输出查询结果表，在查询结果表中可进行分组与排序。

说明：在SELECT语句中SELECT子句与FROM子句是不可少的，其余的是可选的。

信息工程学院实验报告课程名称：《数据库原理》Array实验项目名称：数据查询与更新一、实验目的:（1）观察查询结果, 体会SELECT语句实际应用；（2）要求学生能够在查询分析器中使用SELECT语句进行简单查询。

（3）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

（4）熟悉使用UPDATE/INSERT/DELETE/ALTER语句进行表操作；二、实验设备与器件Win7 +Sql server 2008三、实验内容与步骤根据实验一所创建数据库SPJ，在查询分析器中用sql语句完成以下题目（1）找出所有供应商的姓名和所在城市。

（2）找出所有零件的名称、颜色、重量。

（3）找出使用供应商S1所供应零件的工程号码。

（4）列出所有地址在北京的供应商姓名。

（5）模糊查询，找出名字包含“螺丝”两个字且颜色为红色的零件名称。

（6）统计不同城市工程项目的数量，结果显示“城市”、“工程项目数合计”。

（7）找出工程项目J2使用的各种零件的名称及其数量。

（8）找出上海厂商供应的所有零件号码。

（9）出使用上海产的零件的工程名称。

（10）统计不同地区供应商所供应的零件总数量，结果显示供应商地区、零件总量。

（11）分类统计供应商“盛锡”，不同零件的供应量，结果显示零件名称、零件数量。

（12）找出没有使用天津产的零件的工程号码。

（13）查询至少用了供应商S1提供的全部零件的工程号JNO。

（14）把全部红色零件的颜色改成蓝色。

（15）由S5供给J4的零件P6改为由S3供应。

（16）从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

（17）请将(S1，J6，P4，200)插入供应情况关系。

（18）为S表添加供应商，供应商编号：S6,供应商名称：伟星，城市：北京，状态暂未定。

（19）为P表添加零件，零件编号：P7,零件名称：凹轮，颜色：蓝，重量：20。

（20）将S、SPJ表进行左外连接，查询结果中带有null值的表示什么含义？（21）将SPJ、P表进行右外连接，查询结果中带有null值的表示什么含义？（22）在P表中加入属性零件产地CITY(CHAR型)。

实验三数据查询（参考答案）实验目的□学会利用T_SQL语句中SELECT进行数据查询。

□掌握利用T_SQL 语句进行数据查询的基本方法。

实验内容有以下数据表表名：tudent属性列t_idt_nmt_e某t_birtht_coret_datet_fromt_dpidt_mnt使用T-SQL语句进行数据查询(注：学生班级为学号的前6位)1.显示所有学生的学号、姓名、性别、入学年份和班级(学号前6位)。

Selectt_id学号,t_nm姓名,t_e某性别,year(t_date)入学年份,left(t_id,6)班级Fromtudent2.显示前25%条学生记录信息。

Selecttop25percent某fromtudent3.查询tudent表中2022年及以后入学的学生信息。

Select某fromtudentwhereyear(t_date)>=20224.查询选修了1002号课程且成绩在60以下的学生学号。

Selecttudent.t_idfromtudentjoinlt_coueontudent.t_id=lt_coue. t_idWherec_id='1002'andcore<605.查询所有由三个字组成的“王”姓学生信息。

Select某fromtudentwheret_nmlike'王__'6．查询选修了1001号课程且成绩在60到80之间的学生选课信息。

Select某fromlt_couewherec_id='1001'andcorebetween60and807.查询所有张，王，李，赵姓的学生的学号、姓名、性别。

Selectt_id,t_nm,t_e某fromtudentwheret_nmlike'[张王李赵]%'8.查询陕西籍学生，将结果保存在新表t_han某i。

Select某intot_han某ifromtudentwheret_fromlike'%陕西%'9.查询选修了“数据结构”课程的学生学号、姓名及课程成绩。

湖南第一师范学院信息科学与工程系实验报告课程名称：数据库技术与应用成绩评定：实验项目名称：实验三：数据更新指导教师：洪伟学生姓名：沈丽桃学号：10403080118 专业班级： 10教育1班实验项目类型：设计实验地点：科B201 实验时间：2012年 5 月 8 日一、实验目的与要求：1、掌握向表中插入数据；2、掌握修改表中的数据；3、掌握删除表中的数据；4、掌握创建视图、删除视图、查询视图与更新视图。

二、实验环境：（硬件环境、软件环境）1.硬件环境：奔ⅣPC。

2.软件环境：Windows2000 操作系统，SQL Server 2000。

三、实验内容：（原理、操作步骤、程序代码等）任务：对上次实验所建立的SC数据库，完成以下任务：1、将书上所列例题全部验证一遍；P115－1242、在SC数据库上完成以下操作：1）求每一个学生的平均成绩，并把结果存入数据库中；2）将所有女生的数据库成绩置0；3）删除20岁以上学生的数据结构的选课记录；4）创建一个课程及其平均成绩的视图；5）删除上题中创建的视图。

6）建立男学生的视图schoolboy，属性包括学号、姓名和年龄。

7）在视图schoolboy中查询年龄为19的学生学号与姓名。

8）将学号为002的年龄改为21。

9）向男学生视图schoolboy中插入一个新的男生记录，其中学号为S6,姓名为MING，年龄为20。

10) 删除视图schoolboy中学号为003的记录。

11) 建立女学生的视图，属性包括学号、姓名、选修课程名和成绩。

12) 在女学生视图中查询平均成绩大于80分的学生学号与姓名。

13）删除女学生视图。

具体操作步骤及程序代码为：1.create table sno_grade(sno char(10)avg_grade smallint);第2 行: 'avg_grade' 附近有语法错误。

（列与列之间应该用逗号格开）修改为：create table sno_grade(sno char(10)，avg_grade smallint);Insertinto sno_grade(sno,avg_grade)select sno,avg(grade)from scgroup by sno;2.update scset grade=0where sno in(select snofrom swhere s.ssex='女' and cno in(select cnofrom cwhere ame='数据库'));3. deletefrom scwhere sno in(select snofrom swhere sc.sno=s.sno and s.sage>20 and cno in(select cno from cwhere o=o and ame='数据结构'));4. create view c_avggradeasselect ame,avg(grade) avg_gradefrom c,scwhere o=ogroup by ame5. drop view c_avggrade6. create view schoolboyasselect sno,sname,sagefrom swhere ssex='男'7. select schoolboy.sno,snamefrom schoolboywhere schoolboy.sage='19'8. update schoolboySet sage=’21’where sn=’002’9. insertinto schoolboyvalues('S6','MING','20');10. deletefrom schoolboywhere sno='003';11. create view schoolgirlasselect s.sno,s.sname,ame,sc.gradefrom s,c,scwhere s.sno=sc.sno and o=o and s.ssex='女'12. select schoolgirl.sno,sname,avg(grade)from schoolgirlgroup by schoolgirl.sno,snamehaving avg(grade)>80;13. drop viw schoolgirl四、实验体会(实验中碰到的问题及解决方法等)在试验验证过程中,发现教材P116例4有错误，列名Sdept与Avg_age之间少了逗号。

实验三视图、索引及数据更新一、实验目的：熟练掌握索引的建立与删除的方法，熟练掌握SQL的应用，熟练掌握数据插入、修改和删除的使用，为后继学习作准备。

二、实验属性验证性三、实验要求1.预习教材第三章，熟悉SQL语句。

2.熟悉SQL Server环境。

3掌握建立索引的两种方法，即在基本表中建立和用命令方式建立。

4．掌握删除索引的方法。

5．掌握sql视图建立、修改和删除;6．掌握sql视图查询。

7．掌握sql数据插入、修改和删除语句的一般格式及使用方法。

四、实验原理SQL语言应用。

五、实验步骤(1) 启动SQL查询分析器；(2) 选择SQL SERVER后，按确认；(3) 选择数据库；(4) 验证如下例子：1 建立索引例3.1为学生选课数据库中的Students，Courses，Reports三个表建立索引。

其中Students表按Sno(学号)升序建唯一索引，Courses表按Cno(课程号)升序建唯一索引，Reports表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。

例3.2 在基本表Students的Sname(姓名)和Sno(学号)列上建立一个聚簇索引，而且Students中的物理记录将按照Sname值和Sno值的升序存放。

2 删除索引例3.3删除基本表Reports上的索引。

3 建立视图例 3.4建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。

例3.5建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。

例3.6定义一个反映学生出生年份的视图。

4 删除视图例3.7删除例3.6建立的视图。

5 查询视图例3.8在数学系的学生视图中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。

例 3.9在例 3.5建立的视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称(Cname)。

实验三数据库的查询一、目的与要求1、掌握数据库对象的操作过程，包括创建、修改、删除2、熟悉表的各种操作，包括插入、修改、删除、查询3、熟练掌握常用SQL 语句的基本语法，加深对SQL和Transact-SQL语言的查询语句的理解。

二、实验设备与环境使用SQL Server 数据库管理系统提供的SSMS 和查询编辑器。

三、实验内容1、创建教材P82中的学生-课程数据库，并创建相应的数据表及完整性约束。

（需要在表中添加相应的记录（10条以上），请自行设计）2、在定义的“学生-课程数据库”中，使用T-SQL语句完成以下查询：（1）求计算机系（CS）学生的学号和姓名。

（2）求选修了数学的学生学号、姓名和成绩。

（3）求选修1号课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

（4）查找选修课程的平均成绩位于前三名的学生的学号。

（5）查询计算机系的姓刘且单名的学生的信息。

（6）查询至少选修两门课程的学生学号。

（7）查询学生的学号、课程号以及对应成绩与所有学生所有课程的最高成绩的百分比。

（8）查询选修“数据库”课程，且成绩在80分以上的学生的学号和成绩。

（9）查询所有姓“王”的同学没有选修的课程名。

（请分别用exists和in完成该查询）（10）查询选修了全部课程的学生的姓名。

（请至少写出两种查询语句）（11）求选修了学生“200215121”所选修的全部课程的学生学号和姓名。

（12）查询每一门课的间接先修课。

（13）列出所有学生所有可能的选课情况。

（14）列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。

（15）输出与“张立”同性别并位于同一个系的所有同学的姓名。

（请至少写出两种查询语句）（16）查询至少被两名男生选修的课程名。

（17）对被两名以上学生所选修的课程统计每门课的选课人数。

要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

数据库原理及应⽤.实验3.数据查询与更新实验报告课程名称：数据库原理及应⽤实验项⽬名称：数据查询与更新实验时间：2021年4⽉12⽇实验⽬的: (1) 观察查询结果, 体会SELECT语句实际应⽤； (2) 要求能够在查询分析器中使⽤SELECT语句进⾏简单查询。

(3) 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作⽅法。

(4) 熟悉使⽤UPDATE/INSERT/DELETE/ALTER语句进⾏表操作；实验环境: MySQL 5.7.33、SQLyog-11.2.4实验内容及过程:在实验2的基础上，完成以下题⽬1. 在查询分析器中⽤SQL语句完成以下题⽬单表查询找出所有供应商的姓名和所在城市。

复制SELECT SNAME,CITY FROM S找出所有零件的名称、颜⾊、重量。

复制SELECT PNAME,COLOR,WEIGHT FROM P找出使⽤供应商S1所供应零件的⼯程号码。

复制SELECT JNO FROM SPJ WHERE SNO='S1'列出所有地址在北京的供应商姓名。

复制SELECT SNAME FROM S WHERE CITY='北京'模糊查询，找出名字包含"螺丝"两个字且颜⾊为红⾊的零件名称。

复制SELECT PNAME FROM PWHERE PNAME LIKE '螺丝_' AND COLOR='红'统计不同城市⼯程项⽬的数量，结果显⽰"城市"、"⼯程项⽬数合计"。

复制SELECT CITY,COUNT(JNO) AS '⼯程项⽬书合计'FROM J GROUP BY CITY连接查询和嵌套查询找出⼯程项⽬J2使⽤的各种零件的名称及其数量。

复制SELECT PNAME,QTYFROM P,SPJWHERE P.PNO=SPJ.PNO AND JNO='J2'找出上海⼚商供应的所有零件号码(要求⽤⾃然连接和嵌套查询两种⽅法)。

实验训练3数据增删改操作单击“Select rows”弹出下图，进行插入。

用同样的办法，完成其它7个表的插入。

（2）使用带Select的Insert语句完成汽车配件表Autoparts中数据的批量追加；并通过select语句检查插入前后的记录情况。

Autoparts表批量插入前；使用带Select的Insert语句批量增加，命令如下。

insert into autoparts select * from autopartscopy11;批量增加后，如下图。

用同样的方法，完成其它7个表的批量插入。

【实验3-2】删除数据（1）使用Delete语句分别完成购物车表shoppingcart、订单表Order、订单明细表Order_has_Autoparts、评论Comment的数据删除，删除条件自定；并通过select语句检查删除前后的记录情况。

删除前shoppingcart的情况。

使用Delete语句删除后表shoppingcart的情况，第二行没有了。

输入命令：delete from ShoppingCart where Autoparts_apid=2;用同样的办法，完成其它表的删除。

（2）使用TRUNCATE TABLE语句分别完成购物车表shoppingcart、评论Comment 的数据删除。

删除前shoppingcart的情况。

使用TRUNCATE TABLE语句删除后shoppingcart的情况。

输入命令：TRUNCATE TABLE ShoppingCart;执行结果如下。

【实验3-3】修改数据使用Update分别完成汽车配件表Autoparts、商品类别表category、用户表Client、用户类别表Clientkind、购物车表shoppingcart、订单表Order、订单明细表Order_has_Autoparts、评论Comment的数据修改，修改后数据值自定，修改条件自定；并通过select语句检查修改前后的记录情况。

S Q L实验三数据的更新操作和视图实验报告 Document serial number【LGGKGB-LGG98YT-LGGT8CB-LGUT-实验三数据的更新操作和视图本实验需要2学时。

一、实验目的1、掌握视图的创建、修改和删除操作。

2、掌握数据库数据更新（增加、修改、删除）操作。

二、实验内容和步骤1、视图（1）建立名为UV_SC的视图。

该视图可以让我们看到每个学生的姓名、选修课程名和成绩。

（2）利用UV_SC视图，查看平均成绩在80分以上的学生姓名。

（3）建立名为UV_Course的视图，该视图显示每门课的课程名和选修该课程的学生人数。

（4）修改视图UV_Course，在视图中增加一列，显示选修该课程所有学生的平均成绩。

（5）建立名为UV_IS_Depart的视图。

该视图可以用来查看“信息系”的学生的学号、姓名、年龄和系名。

并保证对该视图的修改满足系名为信息系的条件。

（6）建立名为UV_Student的加密视图。

该视图可以用来查看每个学生选修的课程门数和平均成绩。

（7）利用UV_Student视图，查询平均成绩在80分以上的学生学号。

（8）能否利用视图UV_Student，修改某个学生的平均成绩（9）删除视图UV_Student。

2、数据更新（1）向STUDENT表增加如下记录：（2）将学号’95011’，成绩80，以及课程表中的所有课程号插入到SC表中。

（3）建立与表STUDENT同结构的空表STUD。

然后利用INSERT命令把表STUDENT中年龄大于19的学生记录添加到表STUD。

（注意：可以使用select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条select into语句实现等同的功能）（4）为“95002”的学生增加选修“操作系统”和“数据结构”这两门课的选课记录。

（5）把课程名为“操作系统”的成绩从成绩表SC中删除。

（试用三种方式完成）（6）将女生的成绩提高10%。