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 Class

select 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);