数据库的查询
--1.查询pxscj中表XSB的所有数据（同视图的查询，一个意思）
SELECT *
FROM XSB
GO
--2.查询XSB表中的各个同学的姓名，专业和总学分
SELECT 姓名,专业,总学分
FROM XSB
--3.查询XSB表中计算机专业同学的学号，姓名和总学分，查询表中XSB中所有的列
SELECT 学号,姓名,总学分
FROM XSB
WHERE 专业='计算机'
GO
SELECT *
FROM XSB
定义列别名
--4.查询表XSB中计算机系同学的学号，姓名和总学分，结果中个列的标题分别指定为number，name和，mark。
USE PXSCJ
GO
SELECT 学号 AS number,姓名 AS name ,总学分 AS mark
FROM XSB
WHERE 专业='计算机'
--将上述AS 换成= 或者（空格）都可以。比如：
SELECT number=学号,name=姓名,mark=总学分
FROM XSB
WHERE 专业='计算机'
--当自定义的列标题中含有空格的时候，必须使用''(引号)括起来，比如：
SELECT 'student number '=学号,姓名 AS 'student name','mark'=总学分
FROM XSB
WHERE 专业='计算机'
USE PXSCJ
GO
--5.替换查询结果中的数据，要用CASE表达式
--查询XSB表中计算机系个同学的学号，姓名和总学分，对其总学分按以下规则进行替换：
--若总学分为空值，则替换为“尚未选课”；若总学分小于，则替换为不及格；
--若总学分在和之间，则替换为合格；若总学分大于，则为优秀。列标题改为等级。
SELECT 学号,姓名,等级=
CASE
WHEN 总学分 IS NULL THEN '尚未选课'
WHEN 总学分 <50 THEN '不及格'
WHEN 总学分>=50 and 总学分<=52 THEN '合格'
ELSE '优秀'
END
FROM XSB
WHERE 专业='计算机'
--6.计算列值，按分计算并显示学号为的学生的成绩情况。
SELECT 学号,课程号,成绩=成绩*1.20
FROM CJB
WHERE 学号='081101'
--7.消除结果重复行，对PXSCJ数据库的表XSB只选择专业和总学分，消除结果集中的重复行。
SELECT DISTINCT 专业,总学分
FROM XSB
--8.限制结果集返回行数。对PXSCJ数据库的表的XSB选择姓名,专业,和总学分，返回结果的前六行。
SELECT TOP 6 姓名,专业,总学分
FROM XSB
聚合函数
--若一个SELECT里只有一个GROUP BY则这个聚合函数对所有列起作用，若无，则只产生一行作为结果。
USE PXSCJ
GO
--9.求选修课程的学生的平时成绩
SELECT AVG(成绩) AS '课程平均成绩'
FROM CJB
WHERE 课程号='101'
--10.求学号为的同学所学课程的总成绩
SELECT SUM(成绩) AS '课程总成绩'
FROM CJB
WHERE 学号='081101'
--11.求选修课程的学生的最高分和最低分。
SELECT MAX(成绩) AS '课程的最高分',MIN(成绩) AS '课程的最低分'
FROM CJB
WHERE 课程号='101'
--12.求学生总数
SELECT COUNT(*) AS '学生总数'
FROM XSB
--13.统计备注不为空的学生数
SELECT COUNT(备注) AS '备注不为空的学生数'
FROM XSB
--14.和.统计在哪个学分在分以上的人数
SELECT COUNT(总学分) AS '50分以上的人数'
FROM XSB
WHERE 总学分>50
--*15.求选修了课程的学生总数
SELECT COUNT(DISTINCT 学号)
FROM CJB
--16.查询同学的情况
SELECT 姓名,学号,总学分
FROM XSB
WHERE 学号='081101'
--18.XSB中通信专业总学分大于等于的同学的情况
SELECT *
FROM XSB
WHERE 专业='通信工程' AND 总学分>=42
--19.查询XSB里姓王且单名的学生情况
SELECT *
FROM XSB
WHERE 姓名 LIKE '王_'
--20.查询XSB中学号到户第三个数字为，且倒数第一个数字在~5的学生的学号，姓名，专业。
SELECT 学号,姓名,专业
FROM XSB
WHERE 学号 LIKE '%1_[12345]'
--21.查询XSB中名字包含下划线的学生学号和姓名
SELECT 学号,姓名
FROM XSB
WHERE 学号 LIKE '%#_%' ESCAPE '#'
--22.查询XSB中不在年出生的学生的情况
SELECT 学号,姓名,专业,出生日期
FROM XSB
WHERE 出生日期 NOT BETWEEN '1989-1-1' and '1989-12-31'
--23.查询XSB中专业为计算机，通信，或无线电的学生情况
SELECT *
FROM XSB
WHERE 专业 IN ('计算机','通信工程','无线电')
--等价
SELECT *
FROM XSB
WHERE 专业 = '计算机' or '通信工程' or '无线电'
USE PXSCJ
GO
--24.空值比较。查询总学分尚不确定的学生
SELECT *
FROM XSB
WHERE 总学分 IS NULL
子查询
--25.查找选修了课程号为的课程的学生的情况
SELECT *
FROM XSB
WHERE 学号 IN
( SELECT 学号
FROM CJB
WHERE 课程号='206')
--26.查找为选修离散数学的学生的情况
SELECT*
FROM XSB
WHERE 学号 NOT IN
(SELECT 学号
FROM CJB
WHERE 课程号 IN
(SELECT 课程号
FROM KCB
WHERE 课程名='离散数学'
)
)
--27.查找选修了离散数学的学生的学号
SELECT 学号
FROM CJB
WHERE 课程号=
(SELECT 课程号
FROM KCB
WHERE 课程名='离散数学')
--28.查找比所有计算机系学生年龄都大的学生
SELECT *
FROM XSB
WHERE 出生日期<ALL
(SELECT 出生日期
FROM XSB
WHERE 专业='计算机'
)
--29.查找选修课程的学生的姓名
SELECT 姓名
FROM XSB
WHERE EXISTS
(SELECT *
FROM CJB
WHERE 学号=XSB.学号 AND 课程号='206')
--30.查找选修了全部课程的同学的姓名
SELECT 姓名
FROM XSB
WHERE NOT EXISTS
(SELECT *
FROM KCB
WHERE NOT EXISTS
(SELECT *
FROM CJB
WHERE 学号=XSB.学号 AND 课程号=KCB.课程号))
--31.从表XSB中查找所有女学生的姓名学号及其号学生的年龄差距
SELECT 学号,姓名,YEAR(出生日期)-YEAR(
(SELECT 出生日期
FROM XSB
WHERE 学号='081101')
) AS 年龄差距
FROM XSB
WHERE 性别=0
USE PXSCJ
GO
--34.查找学生计算机基础课程的成绩
SELECT 成绩
FROM CJB,KCB
WHERE CJB.课程号=KCB.课程号
AND 学号='081101'
AND 课程名='计算机基础'
SELECT 姓名,学号,总学分
FROM ( SELECT 姓名,学号,性别,总学分
FROM XSB
WHERE 总学分>50)AS STUDENT
WHERE 性别=1
--35.在表查找.1.1以前出生的学生的姓名和专业，分别使用别名stu_name和speciality表示
SELECT m.stu_name,m.speciality
FROM (SELECT *
FROM XSB
WHERE 出生日期<'19900101')
AS m(num,stu_name,sex,birthday,speciality,score,mem)
USE PXSCJ
GO
--36.查找XSB中年月日以前出生的学生的姓名和总学分，并列出其属于计算机专业还是通信工程专业的情况，表示是，表示否。
SELECT 姓名,总学分,计算机,通信工程
FROM XSB
PIVOT
(
COUNT(学号)
FOR 专业 --要转化的列
IN (计算机,通信工程) --列的值
)AS PVT
WHERE 出生日期<'1990-01-01'
--将表KCB中的开课学期和学分列转换为行输出。
SELECT 课程号,课程名,选项,内容
FROM KCB
UNPIVOT
(
内容
FOR 选项
IN (学分,开课学期)
)unpvt
USE PXSCJ
GO
连接
--38.查找每个学生的情况及选项的课程的情况
SELECT XSB.*,CJB.*
FROM XSB,CJB
WHERE XSB.学号 = CJB.学号
--39.自然连接查询
SELECT XSB.*,CJB.成绩,CJB.课程号
FROM XSB,CJB
WHERE XSB.学号 = CJB.学号
--40.若选择的字段名在各表中是唯一的
SELECT XSB.*,成绩,课程号
FROM XSB,CJB
WHERE XSB.学号 = CJB.学号
--41.查找选修了计算机基础课程且成绩在分以上的学生学号，姓名，课程名及成绩。
SELECT XSB.学号,姓名,课程名,成绩
FROM XSB,CJB,KCB
WHERE XSB.学号 = CJB.学号
AND KCB.课程号=CJB.课程号
AND 课程名='计算机基础'
AND 成绩>=80
JOIN 内连接，外连接，交叉连接
--42.查找每个学生的情况以及选修的课程的情况
SELECT *
FROM XSB INNER JOIN CJB
ON XSB.学号=CJB.学号
--43.用FROM子句的JOIN关键字表达：查找选修了号课程且成绩在分以上的学生的姓名及成绩
SELECT 姓名,成绩
FROM XSB JOIN CJB
ON XSB.学号=CJB.学号
WHERE 课程号='206' AND 成绩>80
--44.用FROM子句的JOIN关键字表达：查找选修了计算机基础课程且成绩在分以上的学生的学号，姓名
--课程名及成绩
SELECT XSB. 姓名,课程名,成绩
FROM XSB JOIN CJB JOIN KCB
ON CJB.课程号=KCB.课程号
ON XSB.学号=CJB.学号
WHERE 课程名='计算机基础' AND 成绩>80
--45.若要早一个表中查找具有相同列值的行，则用自连接。需指定两个别名，所有列都要用别名限定查找不同课程成绩的学生的学号,课程号和成绩
SELECT a.学号,a.课程号,b.课程号,a.成绩
FROM CJB a JOIN CJB b
ON a.成绩=b.成绩 AND a.学号=b.学号 AND A.课程号!=B.课程号
--46.左外连接.查找所有学生情况以及他们选修的课程号,若学生为选修课,也要包括
SELECT XSB.*,课程号
FROM XSB LEFT OUTER JOIN CJB
ON XSB.学号=CJB.学号
--47.右外连接.查找被选修了的课程的选修情况,和所有开设的课程名
SELECT CJB.*,课程名
FROM CJB RIGHT OUTER JOIN KCB
ON CJB.课程号=KCB.课程号
--48.交叉连接=笛卡尔积.列出学生所有可能的选课的情况
SELECT 学号,姓名,课程号,课程名
FROM XSB CROSS JOIN KCB
GROUP BY
--49.将数据库中各专业输出
SELECT 专业
FROM XSB
GROUP BY 专业
--50.求各专业的学生数.
SELECT 专业,
COUNT(*) AS '学生数'
FROM XSB
GROUP BY 专业
--51.求被选修的哥们课程的平均成绩和选修该课程的人数
SELECT 课程号,AVG(成绩) AS'平均成绩',COUNT(学号) AS '选修人数'
FROM CJB
GROUP BY 课程号
--52.在数据库上产生一个结果集,包括每个专业的男生人数,女生人数,总人数,及学生总数.
SELECT 专业,性别,COUNT(*) AS '人数'
FROM XSB
GROUP BY ROLLUP(专业,性别)
--53.在数据库上产生一个结果集,包括每个专业的男生人数,女生人数,总人数,及男生总数，女生总数，
--学生总人数.
SELECT 专业,性别,COUNT(*) AS '人数'
FROM XSB
GROUP BY CUBE(专业,性别)
--54.生成一个结果集，分别根据专业和性别对人数进行聚合。
SELECT 专业,性别,COUNT(*) AS '人数'
FROM XSB
GROUP BY GROUPING SETS(专业,性别)
USE PXSCJ
GO
HAVING
--55.查找平均成绩在分以上的学生的学号和平时成绩
SELECT 学号, AVG(成绩) AS '平均成绩'
FROM CJB
GROUP BY 学号
HAVING AVG(成绩)>=85
--56.查找选修课程超过门且成绩都在分以上的学生的学号
SELECT 学号
FROM CJB
WHERE 成绩>=80
GROUP BY 学号
HAVING COUNT(*)>2
--57.查找通信专业平均成绩在分以上的学生的学号和平时成绩。
SELECT 学号,AVG(成绩) AS '平时成绩'
FROM CJB
WHERE 学号 IN
(
SELECT 学号
FROM XSB
WHERE 专业='通信工程')
GROUP BY 学号
HAVING AVG(成绩)>85
USE PXSCJ
GO
ORDER BY排序
--58.将通信工程专业的学生按出生时间先后顺序排序
SELECT *
FROM XSB
WHERE 专业='通信工程'
ORDER BY 出生日期
--59.将计算机专业的学生计算机基础课程成绩按降序排序
SELECT 姓名,课程名,成绩
FROM XSB,KCB,CJB
WHERE XSB.学号=CJB.学号
AND CJB.课程号=KCB.课程号
AND 课程名='计算机基础'
AND 专业='计算机'
ORDER BY 成绩 DESC
--60.查找通信工程专业学生的学号，姓名，出生日期，并产生一个学生总人数
SELECT 学号,姓名,出生日期
FROM XSB
WHERE 专业='通信工程'
COMPUTE COUNT(学号)
--61.将学生按专业排序，并汇总各专业人数和平均学分。
SELECT 学号,姓名,出生日期,总学分
FROM XSB
ORDER BY 专业
COMPUTE COUNT(学号),AVG(总学分) BY 专业
USE PXSCJ
GO
SELECT 语句的其他用法
--62.INTO，用XSB表创建‘计算机系学生’表，包括学号和姓名
SELECT 学号,姓名
INTO 计算机系学生 --不能与COMPUTE一起用
FROM XSB
WHERE 专业='计算机'
--63.UNION,查找学号为和学号为的两位同学的信息
SELECT *
FROM XSB
WHERE 学号='081101'
UNION ALL
SELECT *
FROM XSB
WHERE 学号='081210'
--64.EXCEPT.查找专业为计算机但性别不为难的学生的信息
SELECT * FROM XSB WHERE 专业='计算机'
EXCEPT
SELECT * FROM XSB WHERE 性别=1
--65.INTERSECT查找总学分大于且性别为男生的学生信息
SELECT * FROM XSB WHERE 总学分>42
INTERSECT
SELECT * FROM XSB WHERE 性别=1
WITH
--66.使用CTE从表CJB中查询选了号课程的学生学号，成绩，并定义新的列名为number，point。
--再使用SELECT语句从CTE 和XSB中查询姓名为王林的学生的学号和成绩情况。
WITH cte_stu(number,point)
AS (SELECT 学号,成绩 FROM CJB WHERE 课程号='101')
SELECT number,point
FROM cte_stu,XSB
WHERE XSB.姓名='王林'
AND XSB.学号=cte_stu.number
--67.计算数字~10的阶乘
WITH MYCTE(n,njc)
AS (
SELECT n=1,njc=1
UNION ALL
SELECT n=n+1,njc=njc*(n+1)
FROM MYCTE
WHERE n<10
)
SELECT n,njc FROM MYCTE
浙公网安备 33010602011771号