--存在子查询
SELECT stuName,stuAge,stuSex FROM dbo.stuInfo
WHERE EXISTS (SELECT classGuid FROM dbo.classInfo WHERE className LIKE '%一')
--(8)查询前10行的数据
SELECT TOP 10 stuId,stuName,stuAge,stuSex FROM dbo.stuInfo ORDER BY stuId SELECT TOP 10 WITH TIES stuId,stuName,stuAge,stuSex FROM dbo.stuInfo ORDER BY stuId --带上并列的 --(9)随机查询10行数据
SELECT TOP 10 stuId,stuName,stuAge,stuSex FROM dbo.stuInfo ORDER BY NEWID() --(10)简单的CASE表达式 SELECT stuId,stuName,stuAge， CASE FLOOR(stuSex) WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '无'
END AS 性别 FROM dbo.stuInfo ORDER BY stuId DESC --学生性别按“男、女”来查询 --(11)利用CASE表达式搜索 SELECT stuName,stuSex, CASE
WHEN stuAge<=40 THEN 'A' WHEN stuAge<=30 THEN 'B'
WHEN stuAge<=25 THEN 'C' WHEN stuAge<=20 THEN 'D' ELSE 'E' END AS 等级
FROM dbo.stuInfo ORDER BY stuAge DESC --学生按年龄分为五个等级（ABCDE）查询 --(12)去除重复的字段
SELECT DISTINCT stuAge FROM dbo.stuInfo --查询所有不重复的年龄
SELECT COUNT(DISTINCT stuAge)AS '记录' FROM dbo.stuInfo --查询年龄不重复的记录
--(13)主外键值相关联的嵌套查询（联合查询） SELECT stuName,stuAge,stuSex FROM dbo.stuInfo
WHERE classGuid IN (SELECT classGuid FROM dbo.classInfo WHERE className='大一' OR className='大二') --查询班级名为大一或大二的具体学生。也可以这样写： SELECT stuName,stuAge,stuSex FROM dbo.stuInfo WHERE classGuid IN （
SELECT classGuid FROM dbo.classInfo WHERE className='大一' UNION
SELECT classGuid FROM dbo.classInfo WHERE className='大二') --(14)查询是否为空的记录
SELECT * FROM dbo.stuInfo WHERE stuAge IS NUll --查询年龄为空的学生信息 SELECT *,ISNULL(stuAge, '空') AS 'Age' FROM dbo.stuInfo
--(15)聚合函数1
SELECT stuName，REPLACE(REPLACE(sex,1,'男'),0,'女') AS 性别 FROM dbo.stuInfo
--将性别作为单独的一个字段，将1代替为男，0代替为女。
SELECT stuName FROM dbo.stuInfo WHERE LEFT(stuName, 1) IN ('孙, '刘', '姚') --(16)聚合函数2（别名AS的3种写法）
SELECT COUNT(*) AS 'count' FROM dbo.stuInfo --获取表中的记录数 SELECT AVG(stuAge) 'Age' FROM dbo.stuInfo --获取年龄的平均值 SELECT 'max' = MAX(stuAge) FROM dbo.stuInfo --获取年龄的最大值
--(17)查询到新表（临时表）
SELECT * INTO #NewTable1 FROM dbo.stuInfo SELECT * FROM #NewTable1
-- 创建一个表格，结构和 dbo.stuInfo一样，但是没有数据 SELECT * INTO NullRoomType FROM dbo.stuInfo WHERE 1 <> 1 INSERT INTO NullRoomType
SELECT * FROM dbo.stuInfo WHERE stuSex='1'
--(18)双表查询 -- 简单的双表查询
SELECT stuInfo.stuName, stuInfo.stuAge, classInfo.className FROM stuInfo,classInfo WHERE classInfo.classGuid = stuInfo.classGuid -- 利用 AS 简化
SELECT A.stuName, A.stuAge, B.className FROM stuInfo AS A,classInfo AS B WHERE B.classGuid = A.classGuid
-- 用 JOIN 实现双表查询
SELECT stuName, stuAge,className FROM stuInfo INNER JOIN classInfo ON stuInfo.classGuid = classInfo.classGuid -- 左连接 LEFT JOIN
SELECT stuName, stuAge,className FROM stuInfo LEFT OUTER JOIN classInfo ON stuInfo.classGuid = classInfo.classGuid -- 右连接 RIGHT JOIN
SELECT stuName, stuAge,className FROM stuInfo RIGHT OUTER JOIN classInfo ON stuInfo.classGuid = classInfo.classGuid -- 全连接 FULL JOIN
SELECT stuName, stuAge,className FROM stuInfo FULL JOIN classInfo ON stuInfo.classGuid = classInfo.classGuid -- 交叉连接 CROSS JOIN
SELECT stuName, stuAge，className FROM stuInfo CROSS JOIN classInfo
--(19)多表查询
SELECT Col1, Col2, Col3 FROM
Table1 INNER JOIN Table2 ON Table1.Col_A = Talbe2.Col_A INNER JOIN Table2.Col_A = Table3.Col_A
--(20)查询视图
SELECT * FROM View_Students WHERE stuName LIKE '%孙%' --(21)分组统计 [GROUP BY],
SELECT AVG(stuAge) AS UnitAge,stuName,stuAge FROM stuInfo GROUP BY stuName,stuAge
【注意】WITH CUBE 、WITH ROLLUP 配合Group by进行统计。CUBE会对Group by后的每个字段值进行组合统计,而ROLLUP,Group by后靠左的字段不会对靠右的字段值产生所有可能的组合统计。