实验三SQL3查询(子查询、分组查询)学生

SQL语句汇总（三）——聚合函数分组子查询及组合查询1.聚合函数-SUM：计算指定列的和```sqlSELECT SUM(column_name) FROM table_name;```-COUNT：计算指定列的行数```sqlSELECT COUNT(column_name) FROM table_name;```-AVG：计算指定列的平均值```sqlSELECT AVG(column_name) FROM table_name;```-MAX：找出指定列的最大值```sqlSELECT MAX(column_name) FROM table_name;```-MIN：找出指定列的最小值```sqlSELECT MIN(column_name) FROM table_name;```2.分组-GROUPBY：按照指定列的值进行分组```sqlSELECT column_name1, column_name2, ... FROM table_name GROUP BY column_name1, column_name2, ...;```-HAVING：对分组后的结果进行过滤```sqlSELECT column_name1, column_name2, ... FROM table_name GROUP BY column_name1, column_name2, ... HAVING condition;```3.子查询-子查询作为列```sqlSELECT column_name, (SELECT column_name FROM table_name WHERE condition) AS subquery_column FROM table_name;```-子查询作为条件```sqlSELECT column_name FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);```4.组合查询-UNION：将两个SELECT语句的结果合并并去除重复行```sqlSELECT column_name1, column_name2, ... FROM table_name1 UNIONSELECT column_name1, column_name2, ... FROM table_name2;```-UNIONALL：将两个SELECT语句的结果合并并包含重复行```sqlSELECT column_name1, column_name2, ... FROM table_name1 UNIONALLSELECT column_name1, column_name2, ... FROM table_name2;。

实验三数据查询（参考答案）实验目的□学会利用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.查询选修了“数据结构”课程的学生学号、姓名及课程成绩。

SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理SQL语言多表查询、分组统计、子查询、数据表的更新操作、事务处理3.1、多表查询3.1.1、基本语法但是在多表查询之前首先必须处理一个问题：例如：现在求出雇员表中的总记录数（14条记录）SELECT COUNT(*) FROM emp ;例如：现在求出部门表的总记录数（4条记录）SELECT COUNT(*) FROM dept ;所谓的多表查询就是直接在FROM语句之后加入若干张表，下面将emp和dept表进行多表查询SELECT * FROM emp,dept ;以上确实完成了两张表的联合查询，但是查询出来的结果是56条记录。

部门表的记录总数* 雇员表的记录总数= 56条记录。

那么这样的结果在数据库中就称为笛卡尔积。

对于这样的结果明显不是最终查询者需要返回的结果，应该想办法去掉笛卡尔积。

所以如果要使用多表查询，则必须按照以下的语句形式进行编写：SELECT 字段FROM 表1,表2WHERE 将两张表的关联字段进行比较，去掉笛卡尔积以emp和dept表为例1、雇员表结构：No. 字段名称字段类型字段作用1 EMPNO NUMBER(4) 表示的是雇员编号，长度为四位的整数2 ENAME VARCHAR2(10) 雇员的姓名，使用字符串表示，字符串的长度最大为103 JOB VARCHAR2(9) 工作，字符串表示，最大长度为94 MGR NUMBER(4) 雇员的直接上级领导编号5 HIREDATE DATE 雇佣日期6 SAL NUMBER(7,2) 工资，工资长度一共是7位，其中整数占5位，小数占2位7 COMM NUMBER(7,2) 奖金（佣金）8 DEPTNO NUMBER(2) 部门编号2、部门表结构：No. 字段名称字段类型字段作用1 DEPTNO NUMBER(2) 雇员编号2 DNAME VARCHAR2(14) 部门名称3 LOC VARCHAR2(13) 部门位置两张表中都存在deptno字段，一般在数据库建表的时候都会把关联字段的名称统一。

实验3 数据库的多表连接查询和子查询实验一、实验目的本实验的目的是使学生加深对SQL和SQL语言的查询语句的理解。

熟练掌握数据库的多表连接查询和子查询。

二、实验时数2学时三、实验内容㈠多表连接查询：1.对两张数据表使用内连接进行查询1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。

2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。

2.对多张数据表使用内连接进行查询1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。

2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。

3.对两张数据表使用左连接进行查询1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

2）使用左连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

4.对两张数据表使用右连接进行查询1）使用右连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

2）使用右连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

㈡子查询：1.多表单值子查询1)查询编号为1001的员工的部门名。

2)查询‘客户中心’部门的员工数。

3)查询陈晨所在的项目编号。

2.多表多值子查询1)查询1985年以后出生的员工在所在项目的工作天数。

2)查询参与项目天数超过40天的员工姓名。

3)查询工资低于2000的员工的所属部门。

3.多层嵌套子查询1)查询‘客户中心'部门中，收入高于平均工资的员工。

2)查询“开发系统”项目的参与部门。

3)查询'宣传'部门雇员参加的项目名称。

4．相关子查询1)查询参与两个项目以上的员工姓名。

实验3：SQL语言操作一、实验目的：1．熟悉SQL语言的基本语法和规则2．了解CREATE、DROP语句的使用。

3．掌握INSERT、UPDA TE、DELETE语言的基本语法和规则4．掌握在查询分析器或企业管理器中执行INSERT、UPDATE、DELETE操作的方法5．掌握SQL语言函数、程序控制语句的使用二、基本原理：SQL语言的基本语法规则和相关SQL程序结构控制语法理论。

三、实验仪器设备：1.硬件：PC台/人（内存：256M以上，cpu：P4）2.软件：SQL Server2000四、实验内容：1.用Create创建表名为class和student，2.利用select和Insert语句向表中插入记录3.利用UPDATE语句更新class和student记录4.利用DELETE删除class表中的记录5.利用DROP语句删除表6.依据SQL语法，编写SQL程序执行数据库相关操作。

五、实验步骤：1).在企业管理器中用Create创建数据库或表，名为student和class，并用ALTER修改数据库和表。

建立一个名为test的数据库，可以输入如下的SQL语句：CREATE DATABASE test例如，要创建一个销售数据库，并设定数据文件为d:\销售.LDF，大小为5MB，最大为20MB，每次增长5MB。

事务日志文件为d:\ 销售.LDF，大小为5MB，最大为10MB，每次增长为1MB。

则创建的SQL语句为：CREATE DATABASE【TABLE】销售数据库ON (NAME = 销售数据,FILENAME = 'd:\Program Files\Microsoft SQL Server\MSSQL\data\销售数据.MDF',SIZE = 10MB,MAXSIZE = 50MB,FILEGROWTH = 10MB)LOG ON(NAME = 销售数据日志,FILENAME = 'd:\Program Files\Microsoft SQL Server\MSSQL\data\销售数据日志.LDF',SIZE = 10MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)2)例如，为销售数据库新增一个逻辑名为“销售数据2”的数据文件，其大小及其最大值分别为10MB和50MB。

实验三数据库SQL基本表查询数据库原理及应用实验报告实验三SQL 基本表查询实验报告实验类型__综合设计__实验室_软件实验室一__SQL基本表查询1．实验项目的目的和任务：熟练掌握查询语句的一般格式，熟练掌握连接、嵌套和集合查询的使用。

2.程序代码(部分)select * from student where s# in( select s# from sc where c#!='c2') //查询没有选修了2号课程的学生名单select s#,avg(grade) from sc group by s#;//查询每个学生的平均成绩select c#,avg(grade) from sc group by c#;//查询每门课的平均成绩select * from student where s# in(select s# from sc where c#='c2') //嵌套查询select sname from student wherenot exists(select * from c wherenot exists(select * from sc where s#=student.s# and c#=c.c#))// 查询选修了全部课程的学生姓名3运行结果如图所示4实验总结通过具体的上机实践操作，掌握了SQL基本表查询。

（1）确定集合（利用“IN”操作可以查询属性值属于指定集合的元组。

利用“NOT IN”可以查询指定集合外的元组。

（2）GROUP BY 子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

（3）在where子句中包含一个形如select-from-where的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。

嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。

实验三SQL查询3（子查询、分组查询）一、实验目的使用SQL语言完成数据定义、数据查询、数据更新等功能。

二、实验要求1. 掌握使用SQL定义、删除和修改基本表。

2. 掌握使用SQL完成单表查询、多表查询。

3. 掌握连接查询和嵌套查询。

4. 掌握使用SQL完成数据插入、数据修改、数据删除等功能。

三、实验内容在“FLY飞翔公司数据库”数据库中，完成以下SQL查询实验。

（以下的SQL查询实验可能会多种SQL命令描述方式，建议同学多思考、多尝试）实验1使用SQL命令，查询公司机构C1和C2中基本工资低于3000的员工信息。

提示：使用两个IN函数，其中一个IN函数是IN(“C1”, “C2”)，另外一个IN函数是嵌套SELECT语句实验2使用SQL命令，查询公司机构C1和C2中基本工资低于3000的员工信息。

实验3使用SQL命令，查询显示基本工资大于2000，且不超过3000元的员工编号及其所在公司名称。

实验4使用SQL命令，查询显示查询供应商“兆海公司”未供应的商品类型名称。

提示：使用NOT IN 函数实现实验5使用SQL命令，查询显示员工的姓名，所属公司名称和工资收入（工资收入＝基本工资＋任务工资＋奖金-住房公积金－扣款）。

提示：产生新字段要使用As实验6使用SQL命令，分组统计供应了商品的供应商所提供的商品种类数量，显示供应商名称和商品种类数量。

提示：使用COUNT函数实验7使用SQL命令，分组统计每笔订单的净收益，显示订单编号和金额为200到500之间的净收益。

（净收益＝（销售价格-购入价格）*数量*折扣）。

提示：group by是分组，having是判断，同学选做。

实验8使用SQL命令，查询与员工王芳签订过订单的零售商名称和订单编号，并将结果存在一个新表New_temp中。

提示： Select 零售商名称, 订单编号 Into New_temp 是将所选字段存入新表New_temp中。

实验9使用SQL命令，查询未与员工E3签订过订单的零售商名称和所在城市。

以下是一个SQL三表查询的示例题目：
假设有三个表：学生表（Students）、教师表（Teachers）和成绩表（Scores）。

学生表包含学生的学号（S#）和学生姓名（Sname），教师表包含教师的姓名（Tname），成绩表包含学生的学号（S#）、教师的姓名（Tname）和学生的分数（Score）。

现在要查询橘右京老师的学生所有分数，并按照成绩倒序排列。

请使用SQL语句来完成这个任务。

SQL语句如下：
```sql
SELECT S#, AVG(Score)
FROM Students
INNER JOIN Scores ON Students.S# = Scores.S#
INNER JOIN Teachers ON Scores.Tname = Teachers.Tname WHERE Teachers.Tname = '橘右京'
GROUP BY Students.S#
ORDER BY AVG(Score) DESC;
```
解释：
1. 使用了两个INNER JOIN来连接学生表、成绩表和教师表，以获取橘右京老师所教学生的分数。

2. 使用WHERE子句来过滤出橘右京老师的名字。

3. 使用GROUP BY子句按照学生学号进行分组。

4. 使用AVG函数计算每个学生的平均分数。

5. 使用ORDER BY子句按照平均分数降序排列结果。

实验三、SQL的数据查询1、实验目的熟悉SQL语句的数据查询语言，能够SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。

2、实验内容实验内容主要是对数据库进行查询操作，包括如下四类查询方式:（1）单表查询●查询的目标表达式为所有列、指定的列或指定的列的运算三种不同.●使用DISTINCT保留字消除重复行。

●对查询结果排序和分组。

●集合分组使用集函数进行各项统计.（2）连接查询●笛卡儿连接和等值连接。

●自连接.●外连接●复合条件连接。

●多表连接。

（3）嵌套查询●通过实验验证对子查询的两个限制条件.●体会相关子查询和不相关子查询的不同.●考察四类谓词的用法，包括：第一类，IN、NOT IN；第二类，带有比较运算符的子查询；第三类，SOME、ANY或ALL谓词的子查询,查询最大值和最小值；第四类，带有EXISTS谓词的子查询，实现“所有”等情况(如王宏的“所有”课程,“所有”女生选修的课程）（4）集合运算●使用保留字UNION进行集合或运算。

●采用逻辑运算符AND或OR来实现集合交和减运算.3、实验步骤以University_Mis数据库为例，该数据库中有四张如实验一，其中Score是每门课的考试成绩,Scredit是学生所有考试合格课程所获得的积分总数，Ccredit每门课程的学分数。

在数据库中，存在这样的联系：学生可以选择课程，一个课程对应一个教师.在表Reports 中保存学生的选课记录和考试成绩.请先输入如下元组，再对数据库进行有关的查询操作：图1.1、Students表图1.2、Teachers表图1。

3、Courses表图1.4、Reports表（1）查询性别为“男"的所有学生的名称并按学号升序排列。

SELECT wjy_SnameFROM Wangjy_StudentsWHERE wjy_Ssex='男’ORDER BY wjy_Sno（2）查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。

sqlsever实训三简单查询⽬录数据结构说明第⼀关简单查询1相关知识使⽤SQL语句中的 Select 语句。

SQL语句中关键词⼤⼩写不区分。

考虑到本系统的测评规则，在题⽬中要求对列名⼩写的，请务必按要求书写，否则会评判为错误！性别⽤’m’表⽰男，’f’表⽰⼥注：请务必将select⼦句中出现的列名⼩写，顺序必须和题⽬要求⼀致查询CS系男同学的学号，姓名，年龄SELECT sno,sname,sageFROM StudentWHERE Sdept='CS'and Ssex='m'输出结果查询不及格的学⽣选课信息，列出学号，课程号，成绩SELECT sno,cno,gradeFROM SCWHERE grade<60输出结果查询先⾏课程不为空的课程（使⽤*表⽰查询结果）SELECT cno,cname,cpno,ccreditFROM CourseWHERE cpno IS NOT NULL输出结果查询姓名中带有’n’字母的学⽣的学号，姓名（使⽤like语句）SELECT sno,snameFROM StudentWHERE sname like'%n%'输出结果使⽤distinct关键字查询学⽣表中不同的系，列出系（去除重复元祖）SELECT DISTINCT sdept FROM student输出结果第⼆关简单查询2查询90分以上学⽣的选课信息，列出学号，姓名，课程号，成绩SELECT A.sno,A.sname,o,C.gradeFROM Student A,SC CWHERE grade>90and A.Sno=C.Sno输出结果查询‘DB’课程的选课情况，列出学号，成绩SELECT A.sno,C.gradeFROM Student A,Course B,SC CWHERE ame='DB'and o=o and A.sno=C.sno输出结果第三关简单查询3查询CS系的学⽣选择‘DB’课程的情况，列出学号，成绩SELECT A.sno,C.gradeFROM Student A,Course B,SC CWHERE cname='DB'and A.sno=C.sno and o=o and sdept='CS'输出结果查询⼥同学的选课情况，列出学号，课程号，课程名，成绩SELECT A.sno,o,ame,C.gradeFROM Student A,Course B,SC CWHERE A.ssex='f'and A.sno=C.sno and o=o输出结果。