四、查询与统计数据
1、查询语言简介:Transact-SQL
SQL语言由数据定义语言(DDL)、数据操纵语言(DML)和 数据控制语言(DCL)。
数据定义语言:通常包括对数据库对象的CREATE、ALTER 和 DROP 等语句。
数据操纵语言:包括SELECT、INSERT、UPDATE、DELETE语句
数据控制语言:对SQL Server登录账号授予或回收对某些数据库对象操作的权限,常用的语句由GRANT(授权)和REVOKE(收回)等。
2、查询单个数据表数据
SELECT字句的书写格式:
SELECT 选择列表
FROM 表的列表
WHERE 查询的条件
考察知识点:
①使用(*)显示表的所有列
②改变查询显示结果的列标题 (= \ AS)
③消除查询结果的重复行(DISTINCT)
⑤返回查询结果的前n(%)行
⑥在查询结果中增加要显示的字符串
(逻辑运算符 AND OR NOT)和 (算数运算符 > < = >= <= != !> !< <>)
⑦表达式作为查询列
⑧重新排序查询结果(ORDER BY ASC \ DESC)
⑨使用LIKE实现模糊查询(% , _ ^)
--查询Course表的所有信息(所有行和列)
USE Xk
GO
SELECT *
FROM Course
GO
--查询有哪些种类的选修课?学分是多少?
USE Xk
GO
SELECT Kind,Credit
FROM Course
GO
--修改显示结果的列名
USE Xk
GO
SELECT '课程种类'=Kind,'学分'=Credit
FROM Course
GO
--消除查询结果中的重复行
USE Xk
GO
SELECT DISTINCT '课程种类'=Kind,'学分'=Credit
FROM Course
GO
--查询Course的前十行(前10$)
USE Xk
GO
SELECT top 10 *
FROM Course
GO
--查询Course的前10%
USE Xk
GO
SELECT top 10 PERCENT *
FROM Course
GO
USE Xk
GO
--查询学号为“00000001”同学是否报名了课程编号为“003”的课程
SELECT *
FROM StuCou
WHERE StuNo='00000001'
AND
CouNo='003'
GO
--查询报名人数在15-40范围内的课程信息
SELECT *
FROM Course
WHERE WillNum>=15 AND WillNum<=40
GO
SELECT *
FROM Course
WHERE WillNum BETWEEN 15 AND 40
GO
--查询课程编号分别“004”,“007 ,“013”
SELECT *
FROM Course
WHERE CouNo='004' OR CouNo='007' OR CouNo='013'
GO
SELECT *
FROM Course
WHERE CouNo IN ('004','007','013')
GO
--查询课程编号分别不为“004”,“007 ,“013”
SELECT *
FROM Course
WHERE CouNo NOT IN ('004','007','013')
GO
--查询课程信息、报名人数与限选人数之比(CONVERT函数、CAST函数 (问题:小数点可以只保留2位吗?)
SELECT *,CONVERT(decimal(4,2),WillNum/LimitNum) AS '报名人数与限选人数之比'
FROM Course
GO
SELECT *,CAST(WillNum/LimitNum AS decimal(4,2)) AS '报名人数与限选人数之比'
FROM Course
GO
--查询课程信息、报名人数与限选人数之比 按升序排序(默认状态)
SELECT *,CAST(WillNum/LimitNum AS decimal(4,2)) AS '报名人数与限选人数之比'
FROM Course
ORDER BY WillNum
GO
--查询课程信息、报名人数与限选人数之比 按降序排序(默认状态)
SELECT *,CAST(WillNum/LimitNum AS decimal(4,2)) AS '报名人数与限选人数之比'
FROM Course
ORDER BY WillNum DESC
GO
--查询查询课程信息、报名人数与限选人数之比,按降序排列,且只显示前10行
SELECT top 10 *,CAST(WillNum/LimitNum AS decimal(4,2)) AS '报名人数与限选人数之比'
FROM Course
ORDER BY WillNum DESC
GO
--查询选修课的任课教师名、课程号、课程名。要求查询结果先按教师名排序,当同名时,按课程号升序排列。
SELECT Teacher,CouNo,CouName
FROM Course
ORDER BY Teacher DESC,CouNo ASC
--查询课程信息,(要求在结果中增加字符串)
SELECT '课程编码'=CouNo,'课程名称为:','课程名称'=CouName
FROM Course
GO
--查询带有“制作”字的课程
SELECT '课程编码'=CouNo,'课程名称为:','课程名称'=CouName
FROM Course
WHERE CouName LIKE '%制作%'
GO
--查询“周二”上的课
SELECT *
FROM Course
WHERE SchoolTime LIKE '周二%'
3、使用聚合函数查询
SUM 计算一组数据的和
MIN 给出一组数据的最小值
MAX 给出一组数的最大值
--1、查看选课数据表中的平均学分
SELECT AVG(Credit) AS '平均学分'FROM Course
GO
--2、查看学生总人数SELECT COUNT(*)FROM StudentGO
--3、查看最大的学分、查看最小的学分、平均学分
SELECT MAX(Credit) AS '最大学分',MIN(Credit) AS '最小学分',CONVERT(DECIMAL(3,1),AVG(Credit)) AS '平均学分'
FROM CourseGO
--4、查看有多少种类的选修课
SELECT COUNT(DISTINCT Kind) AS '共有种类'
FROM CourseGO
--5、统计course表中提供了多少门选修课程SELECT COUNT(*)FROM Course
--6、查看课程表中能提供多少人选修课程
SELECT SUM(LimitNum) AS '可供人数'FROM CourseGO
--7、查看课程表中最少报名人数、最多报名人数和平均报名人数
SELECT '最少报名人数'=MAX(WillNum),'最多报名人数'=MIN(WillNum),'平均报名人数'=AVG(WillNum)
FROM Course
GO
4、分组查询结果
GROUP BY ALL 用于显示所有的组,包括那些被WHERE排除的组
COMPUTE 字句可以根据需要完成计算并作为新的一行
COMPUTE BY 字句用来分组,并可对每组进行计算。分组查询时,如果需要显示明细,则可以用COMPUTE BY字句
CUBE运算符 在查询结果中增加一个汇总行(可以用于所有的聚合函数)
HAVING 字句可以对分组后的数据进行条件限定,写在GROUP BY字句后面
--1、按课程类别分组统计各类课程的门数(新增加了一列)
SELECT Kind AS '课程类别', COUNT(Kind) AS '该类课程门数'
FROM Course
GROUP BY Kind
GO
--2、按课程类别分组统计各类课程的门数(增加行和列)
SELECT Kind AS '课程类别', COUNT(Kind) AS '该类课程门数'
FROM Course
GROUP BY Kind WITH CUBE
GO
--3、查看报名人数大于15的各类课程的最少报名人数和最多报名人数
SELECT MAX(WillNum) AS '最多报名人数',MIN(WillNum) AS '最少报名人数'
FROM Course
WHERE WillNum > 15
GROUP BY Kind
--4、查看报名人数大于15并且每组平均报名人数大于30的课程类别和各组的平均报名人数。
SELECT Kind,AVG(WillNum) AS '平均报名人数'
FROM Course
WHERE WillNum > 15
GROUP BY Kind
HAVING AVG(WillNum) > 30
GO
--5、查看课程类别为“信息技术”,”管理”的平均报名人数,要求也显示其他课程类别。
--group by all(显示类别但不显示平均报名人数)
SELECT Kind,AVG(WillNum) AS 平均报名人数
FROM Course
WHERE Kind IN ('信息技术','管理')
GROUP BY ALL Kind
--6、查看课程表中系部编号为“01”的课程信息,同时显示该系部的平均报名人数(增加行)
SELECT *
FROM Course
WHERE DepartNo = '01'
COMPUTE AVG(WillNum)
5、使用子查询
子查询是返回单值的SELECT查询。子查询可以用在SELECT、INSERT、UPDATE 或 DELETE语句中,也可以用在WHERE 或 HAVING子句中。
--1、查询报名人数大于平均报名人数的课程信息(子查询)
SELECT *
FROM Course
WHERE WillNum > (SELECT AVG(WillNum)FROM Course)
GO
--2、查询学生选课报名状态为‘报名’的课程名称
SELECT CouName
FROM Course
WHERE CouNo IN (SELECT CouNo FROM StuCou WHERE State = '报名')
GO
--3、查询已经报名选修课程的学生信息,显示学号和姓名。
SELECT StuNo,StuName
FROM Student
WHERE EXISTS (SELECT CouNo FROM StuCou WHERE State = '报名' AND StuNo = Student.StuNo)
GO
6、排序查询结果
ROW_NUMBER() OVER (ORDER BY 列名 ) 在查询结果中给出每行的序号,其中 order by 列名为所要排序的列
--使用排序函数--查询课程信息,要求按照人数降序排列
SELECT * FROM Course ORDER BY WillNum DESC
--ROW_NUMBER() OVER (要排序的列)
SELECT ROW_NUMBER() OVER (ORDER BY WillNum DESC) AS '序号',*
FROM Course--RANK() OVER (要排序的列)
序号有间断
SELECT RANK() OVER (ORDER BY WillNum DESC),*
FROM Course--DENSE_RANK() OVER (要排序的列)
SELECT DENSE_RANK() OVER (ORDER BY WillNum DESC),*
FROM Course
7、多表连接查询数据
交叉连接查询:使用CROSS JOIN 字句将一个以上的表连接起来的查询称为交叉连接查询。输出的结果为笛卡儿积。(两表中第一个表的每一行于第二个表的每一行进行连接,结果集的行数是第一个表的行数乘以第二个表的行数,结果集的列数是第一个表的列数加上第二个表的列数)
内连接查询:只保留满足连接条件的数据行的连接称为内连接,使用JOIN 字句进行连接,连接条件写在 ON 字句中。(相等条件:主表的主键=从表的外键)
(相等连接查询:将连接两个表的公共列做相等比较的连接)
(自然连接查询:在相等连接中只保留一个连接列的连接)
(比较连接查询:标语表之间的连接不是使用=进行连接,而是使用比较运算符进行连接)
(自连接查询:一个表和它自身进行连接,是多表连接的特殊情况。)
--多表连接
--查询学生信息和班级信息(笛卡儿积)
SELECT *
FROM Student,Class
GO
--交叉连接(数据存在错误)
SELECT *
FROM Student CROSS JOIN Class
GO
--相等连接
SELECT *
FROM Student,Class
WHERE Student.ClassNo = Class.ClassNo
GO
SELECT *
FROM Student JOIN Class
ON Student.ClassNo = Class.ClassNo
GO
--自然连接(相等比较的列只保留一列)
SELECT Student.*,ClassName,DepartNo
FROM Student JOIN Class
ON Student.ClassNo = Class.ClassNo
GO
--1、查询学生的学号、姓名以及所在的班级号码和班级名称
SELECT StuNo,StuName,Class.ClassNo,ClassName
FROM Student JOIN Class
ON Student.ClassNo = Class.ClassNo
--2、查询学生的学号,所选的课程号、所选的课程名称、学分
SELECT Student.StuNo,Course.CouNo,CouName,Credit
FROM Student,Course,StuCou
WHERE Student.StuNo = StuCou.StuNo AND Course.CouNo = StuCou.CouNo
GO
--1、查询学生的学号,所选的课程号、所选的课程名称、学分,要求按照课程号排序,课程号相同时按照学号排序
SELECT Student.StuNo,Course.CouNo,CouName,Credit
FROM Student,Course,StuCou
WHERE Student.StuNo = StuCou.StuNo AND Course.CouNo = StuCou.CouNo
ORDER BY CouName,Student.StuNo
Go
--使用JOIN和ON
SELECT Student.StuNo,Course.CouNo,CouName,Credit
FROM Student JOIN StuCou
ON Student.StuNo = StuCou.StuNo
JOIN Course
ON Course.CouNo = StuCou.CouNo
ORDER BY CouName,Student.StuNo
Go
--2、查询同学报名“计算机应用工程系”选修课程情况,显示学号、姓名、课程名、教师、上课时间,按照课程编号排序,课程编号相同时,按照学号排序
SELECT Student.StuNo,Student.StuName,CouName,Teacher,SchoolTime
FROM Student,Course,StuCou,Department
WHERE Student.StuNo = StuCou.StuNo AND Course.CouNo = StuCou.CouNo AND DepartName = '计算机应用工程系' AND Department.DepartNo = Course.DepartNo
ORDER BY Course.CouNo,Student.StuNo
--
SELECT s.StuNo,s.StuName,CouName,Teacher,SchoolTime
FROM Student s JOIN StuCou sc
ON s.StuNo = sc.StuNo
JOIN Course c
ON c.CouNo = sc.CouNo
JOIN Department d
ON d.DepartNo = c.DepartNo
WHERE DepartName = '计算机应用工程系'
ORDER BY c.CouNo,s.StuNo
GO
--练一练,查询各班同学的信息,要求显示:班级编号、班级名称、学号、姓名,按照班级编号排序,当课程编号相同时,按照学号排序
SELECT Class.ClassNo,ClassName,StuNo,StuName
FROM Class,Student
WHERE Class.ClassNo = Student.ClassNo
ORDER BY Class.ClassNo,Student.StuNo
GO
--3、不等于运算符的使用 课本73页(2.35)
SELECT ClassName,CouName,Kind,Credit,Teacher,SchoolTime,WillNum
FROM Class,Course
WHERE Class.DepartNo<>Course.DepartNo
GO
--4、课本74页(2.36)
--查询课程类别相同但开课系部不同的课程信息,要求显示课程编号、课程名称、课程类别、系部编号、按照编号升序排列
SELECT '课程编号'=c1.CouNo,'课程名称'=c1.CouName,'课程类别'=c1.Kind,'系部编号'=c1.DepartNo
FROM Course c1,Course c2
WHERE c1.Kind = c2.Kind AND c1.DepartNo <> c2.DepartNo
ORDER BY 课程编号
GO
外连接查询:
(左外连接查询:在两个表进行内连接查询结果的基础上,再增加不满足连接条件的那些行,这些行的右表列值显示为空值(NULL))
(右外连接查询:在两个表进行内连接查询结果的基础上,再增加不满足连接条件的那些行,这些行的左边列值显示为空值(NULL))
(全外连接查询:完成左外连接查询和右外连接查询的操作,查询结果包括了左外连接和右外连接都不满足条件的那些数据行)
--外连接查询
--观察如下查询数据
--查询课程信息
SELECT *
FROM Course
--查询选课表信息
SELECT *
FROM StuCou
--查询所有同学报名选修课的详细情况(包括有人报名和无人报名的),显示学号、课程号和课程名
SELECT '学号'=StuNo,'课程号'=Course.CouNo,'课程名'=CouName
FROM Course LEFT JOIN StuCou /*左外连接结局的是JOIN左边丢失的数据019*/
ON Course.CouNo = StuCou.CouNo
ORDER BY Course.CouNo
GO
SELECT '学号'=StuNo,'课程号'=StuCou.CouNo,'课程名'=CouName
FROM Course RIGHT JOIN StuCou /*右外连接解决的是JOIN右边丢失的数据020*/
ON Course.CouNo = StuCou.CouNo
ORDER BY Course.CouNo
GO
--使用全外连接同时解决左右表信息丢失的问题
SELECT '学号'=StuNo,'课程号'=StuCou.CouNo,'课程名'=CouName
FROM Course FULL JOIN StuCou
ON Course.CouNo = StuCou.CouNo
ORDER BY Course.CouNo
GO
五、管理数据表数据
1、合并多个查询结果中的数据
使用UNION运算符,可以将两个或更多个查询结果合并为一个查询结果。
①用来合并的每个查询结果中的列数必须相同,并且对应列的数据类型必须相同或兼容。
②第一个查询结果的列标题作为合并后查询结果的列标题。
③UNION ALL字句包含重复出现的那些行。
④INTO字句只能出现在第一个查询中,不可以出现在其他查询中,并且INTO后面的标明再数据库中不能存在。
⑤ORDER BY 和 COMPUTE Y字句只能用在最后一个查询中,用来排序和汇总合并后的结果。
⑥GROUP BY和HAVING字句仅可用在其他查询中,而不可用在最后的合并结果中。
⑦UNION运算符也可以和INSERT语句一起使用。
--查看课程编号、课程名称和报名状态的情况。
USE Xk
GO
SELECT CouName,CouNo
FROM Course
UNION ALL
SELECT State,CouNo
FROM StuCou
GO
--使用UNION去掉查询结果中重复出现的那些行
USE Xk
GO
SELECT CouName,CouNo
FROM Course
UNION
SELECT State,CouNo
FROM StuCou
GO
2、抽取一个表的数据到另一个表中
SELECT 列名
INTO 新表名
FROM 表名1,表名2,表3......
WHERE 条件1,条件2,条件3......
--抽取数据到另一个表中
--将Course表中报名人数大于20的课程编号、课程名称、学分和上课时间抽取到 New_Course表中。
USE Xk
GO
SELECT '课程编号'=CouNo,'课程名称'=CouName,'学分'=Credit,'上课时间'=SchoolTime
FROM Course
WHERE WillNum > 20
GO
3、维护数据
(1)向表中添加数据
①使用INSERT语句
②使用SQL Server Management Studio
--维护数据
--向Department表中添加如下三行数据
--DepartNo='11',DepartName='数学系'
--DepartNo='12',DepartName='物理系'
--DepartNo='13',DepartName='化学系'
USE Xk
GO
INSERT Department
VALUES('11','数学系')
INSERT Department(DepartNo,DepartName)
VALUES('12','物理系')
INSERT Department(DepartNo,DepartName)
VALUES('13','化学系')
--在表中添加多行数据还可以使用INSERT和UNION运算符
--在表中添加如下多行数据
--'20110001','03 旅游管理','03'
--'20110002','03 旅游管理','03'
--'20110003','02 建筑管理','02'
--'20110004','02 建筑管理','02'
USE Xk
GO
INSERT INTO Class
SELECT '20110001','03 旅游管理','03'
UNION
SELECT '20110002','03 旅游管理','03'
UNION
SELECT '20110003','02 建筑管理','02'
UNION
SELECT '20110004','02 建筑管理','02'
GO
(2)将表中数据复制到新表中
--将表中的数据复制到新表中
USE Xk
GO
SELECT * INTO CourseWillNum
FROM Course
WHERE WillNum>=10
GO
(3)更新数据表数据
UPDATE 要更新的表名
SET 要更新的列名 = 更新后的值
[FROM 涉及的表名]
[WHERE 条件]
--更新数据表
USE Xk
GO
UPDATE Course
SET SchoolTime = '周二晚'
FROM Course
WHERE CouName = '电子出版概论'
GO
(4)删除数据表数据
DELETE 要删除数据的表名
[FROM 涉及的表名列表]
--删除数据表
USE Xk
GO
DELETE StuCou
FROM StuCou
WHERE StuNo = '00000005' AND
CouNo = '017'
GO
UPDATE Course
SET WillNum = WillNum - 1
WHERE CouNo = '017'
GO