SQL Server 2012 - SQL查询

-- Top Percent 选择百分比
SELECT top 30 percent * FROM [SchoolDB].[dbo].[Student]-- Top 支持定义变量查询
declare @per int = 30
SELECT top ( @per) percent * FROM [SchoolDB].[dbo].[Student]

-- Top Percent 选择百分比
SELECT top 30 percent * FROM [SchoolDB].[dbo].[Student]-- Top 支持定义变量查询
declare @per int = 30
SELECT top ( @per) percent * FROM [SchoolDB].[dbo].[Student]--重命名
select StuName as [姓 名] FROM [SchoolDB].[dbo].[Student]--通配符查询
select * FROM [SchoolDB].[dbo].[Student] where Height like '1[67][0-9]'--IsNull 把Null数据转换为其他值。NULL和任何字段做比较返回的都是NULL
select * FROM [SchoolDB].[dbo].[Student] where ISNULL(StuBirthday,0) <'1990-01-01'-- Exists 存在，适用于内外表的查询，Exists不关心查询到的内容，只关心是否能查询到数据
select *From [Student] as Stu1 where exists(
--select 1
select * From [Student] as Stu2 where StuID=Stu1.StuID and Stu2.Height>160
)--CharInedx 字符a在字符串aa中出现的位置
select CHARINDEX('a','sdafasdad',1)--PatIndex ,查询通配符字符串的位置
select * From [Student] where PatIndex ('王_',StuName)>0

-------------聚合函数---------------group by 单字段分组-- --每个班级的人数
select class as '班级', count(*) as '人数' from [Student]
group by Class--每个班级的平均身高、最小生日
select class as '班级',AVG(Height) as '平均身高',Min(StuBirthday) as '最小生日' from [Student]
group by Classselect class as '班级',Min(StuBirthday) as '平均身高' from [Student]
group by Class--group by 多字段分组--
select class as '班级',StuSex as '性别',COUNT(*),AVG(Height) as '平均身高',Min(StuBirthday) as '最小生日' from [Student]
group by Class,StuSex
-- having Class='1' --分组后进行过滤
having AVG(Height)>=159 --分组后进行过滤--SUM求和，AVG平均，MAX最大，Cast转化
select class as '班级', SUM(height) as '总身高',MAX(height) as '身高最高', CAST(AVG(height/1.0) as decimal(18,2)) as '平均高' from [Student]
group by Class--Distinct
select class, count(*) as '班级人数',count(1) as '班级人数',count(StuID) as '班级人数',count(StuSex) as '班级性别',count(distinct(StuSex)) as '班级不同的性别'
from [Student]
group by Class

------------嵌套查询----------------------1.1子查询 ----------------
SELECT *
FROM ( SELECT dbo.Student.* ,
dbo.ClassInfo.ClassName
FROM dbo.Student
JOIN dbo.ClassInfo ON Student.Class = dbo.ClassInfo.ID
) AS T;
-- T 是临时的查询结果集------------1.1 嵌套子查询（内部子查询结果一次性提供结果集供外部查询） ----------------
SELECT *
FROM dbo.Student
WHERE Class IN ( SELECT ID
FROM dbo.ClassInfo ); ------------1.2 相关子查询（外部表中的数据逐条作为参数传递给内部表中的数据） ----------------
SELECT *
FROM dbo.Student AS Stu
WHERE EXISTS ( SELECT * FROM dbo.ClassInfo WHERE ID=Stu.Class );-- IN :子查询数据量小，而外表数据大 (子查询数据逐条循环)
-- Exist:子查询数据量大，而外表数据小 （Exist只关心是否能查询到数据）
-- 能用关联查询，尽量不用子查询-----------2. 创建物理临时表 -----------
CREATE TABLE #TableTemp
(
id INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY ,
name NVARCHAR(10)
);INSERT INTO #TableTemp
( name )
VALUES ( N'你好' -- name - nvarchar(10)
);SELECT *
FROM #TableTemp;DROP TABLE #TableTemp;-----------3. into 临时表(物理表)---------
SELECT dbo.Student.* ,
dbo.ClassInfo.ClassName
INTO #TableTemp
FROM dbo.Student
JOIN dbo.ClassInfo ON Student.Class = dbo.ClassInfo.ID;SELECT *
FROM #TableTemp;DROP TABLE #TableTemp;----------4.复制表结构----------
SELECT *
INTO Student_His
FROM dbo.Student
WHERE 1 = 2;----------5.创建表变量----------
DECLARE @tmp_Table TABLE
(
id INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY ,
name NVARCHAR(20)
);INSERT @tmp_Table
( name )
VALUES ( -- id - int
N'表变量' -- name - nvarchar(20)
);SELECT *
FROM @tmp_Table;----------6.公共表表达式， CTE:Common Table Expression。可以生成多个临时表，然后再进行连接查询----------
;WITH T AS ( SELECT dbo.Student.* ,
dbo.ClassInfo.ClassName
FROM dbo.Student
JOIN dbo.ClassInfo ON Student.Class = dbo.ClassInfo.ID
),
T1 AS(
SELECT * FROM dbo.Student
)
-- Select、Update 紧跟在 With As 才有效
SELECT T.*
FROM T JOIN T1 ON t.StuID=T1.StuID ----------7.使用ANY、SOME、ALL关键字----------
SELECT *
FROM dbo.Student
WHERE Class =ALL ( SELECT ID
FROM dbo.ClassInfo WHERE ID=1);