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