SQL语言_数据查询_单表查询_PAGE2

合集 - 数据库系统原理(3)

1.SQL语言_SQL语言基础及数据定义功能_PAGE12024-11-20

2.SQL语言_数据查询_单表查询_PAGE22024-11-20

3.SQL语言_多表查询和TOP限制结果集_PAGE32024-11-23

收起

数据查询

单表查询

--01.选择表中若干列
SELECT Sid,Sname FROM Student--查询指定列

SELECT * FROM Student--查询全部列

SELECT Sid,2024-Sage AS 年龄 FROM Student--查询经过计算的列，并为列起别名

--02.选择表中若干元组
SELECT DISTINCT Sage FROM Student--去掉查询结果的重复行

--常用查询条件
--=,<,>,<=,>=,<> OR !=
--BETWEEN AND,NOT BETWEEN AND
--IN,NOT IN
--LIKE,NOT LIKE
--IS NULL,IS NOT NULL
--AND,OR

SELECT Sname FROM Student
	WHERE (Sdept='计算机系' OR Sdept='软件系') 
		AND Sage<=20--确定范围,多重条件查询,AND优先级高于OR
--也可以成
SELECT Sname FROM Student
	WHERE Sdept IN('计算机系','软件系') 
		AND Sage<=20

SELECT Sname FROM Student
	WHERE Sage NOT BETWEEN 20 AND 24

SELECT Sname FROM Student
	WHERE Sdept IN('软件系','计算机系','大数据系')--与IN中某个常量值相等结果为Ture

SELECT Sname FROM Student
	WHERE Sdept NOT IN('软件系','计算机系','大数据系')--与IN中某个常量值相等结果为False
--此句等价于
SELECT Sname FROM Student
	WHERE Sdept！='软件系' AND Sdept！='计算机系'AND Sdept！='大数据系'

--字符串匹配
--_:匹配任意一个字符
--%:匹配0个或多个字符
--[]:匹配[]中的任意一个字符。[a-b]表示连续字母匹配,[abcg]表示匹配a、b、c、g中任意一个
--[^]:不匹配[]中的任意一个字符。==NOT LIKE

SELECT * FROM Student
	WHERE Sname LIKE '张%'--姓‘张’

SELECT * FROM Student
	WHERE Sname LIKE '[张刘李]%'--姓‘张’、姓‘刘、’姓‘李’,等价于OR

SELECT * FROM Student
	WHERE Sname LIKE '_[小]%'--第二个字是‘小’

SELECT * FROM Student
	WHERE RTRIM(Sname) LIKE '王_'--姓‘王’且只有两个字，去掉尾随空格(RTRIM函数)

SELECT * FROM Student
	WHERE Grade IS NULL--查询空值数据

--03.对查询结果进行排序
SELECT * FROM Student
	ORDER BY Sage ASC
	
SELECT * FROM Student
	ORDER BY Sdept,Grade DESC--系名升序排序，同一系年龄降序

--04.聚合函数
SELECT 
	COUNT(*) AS 选课门数,
	SUM(Grade) AS 总成绩,
	AVG(Grade) AS 平均分,
	MAX(Grade) AS 最高分,
	MIN(Grade) AS 最低分
	FROM SC WHERE Cno='100001'

--05.查询结果分组统计
SELECT Sid,COUNT(*) AS 选课门数 FROM SC
	WHERE Ssex='女'
	GROUP BY Sdept--统计每个系的女生的选课情况人数

SELECT Sdept,Ssex,COUNT(*) AS 人数,MAX(Sage) AS 最大年龄
	FROM Student
	GROUP BY Sdept,Ssex
	ORDER BY Sdept
	
SELECT Sno,COUNT(*) AS 选课门数,AVG(Grade)  AS 平均成绩
	FROM SC
	GROUP BY Sno
	HAVING AVG(Grade)>=80
	--HAVING子句用于分组后的结果再进行筛选(=WHERE),但是用于组，通常和GROUP BY一起用

SELECT Sdept,COUNT(*) FROM Student
	WHERE Ssex='男'--先挑选出全部男生
	GROUP BY Sdept--按系排序，执行聚合函数
	HAVING COUNT(*)>=2--筛选满足条件的结果

--注意执行效率
SELECT Sdept,COUNT(*) FROM Student
	WHERE Sdept IN('计算机系','软件系')
	GROUP BY Sdept--参与分组的数据比较少，效率更高
--vs
~~SELECT Sdept,COUNT(*) FROM Student
	GROUP BY Sdept
	HAVING Sdept IN('计算机系','软件系')~~