日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利

轉(zhuǎn):SQL Server T-SQL高級查詢

系統(tǒng) 2808 0

SQL Server T-SQL高級查詢

高級查詢在數(shù)據(jù)庫中用得是最頻繁的,也是應(yīng)用最廣泛的。

? 基本常用查詢

          --
          
            select
          
        
          
            select
          
           * 
          
            from
          
           student;
        
          ?
        
          --
          
            all
          
           查詢所有
        
          
            select
          
          
            all
          
           sex 
          
            from
          
           student;
        
          ?
        
          --
          
            distinct
          
           過濾重復(fù)
        
          
            select
          
          
            distinct
          
           sex 
          
            from
          
           student;
        
          ?
        
          --
          
            count
          
           統(tǒng)計
        
          
            select
          
          
            count
          
          (*) 
          
            from
          
           student;
        
          
            select
          
          
            count
          
          (sex) 
          
            from
          
           student;
        
          
            select
          
          
            count
          
          (
          
            distinct
          
           sex) 
          
            from
          
           student;
        
          ?
        
          --
          
            top
          
           取前N條記錄
        
          
            select
          
          
            top
          
           3 * 
          
            from
          
           student;
        
          ?
        
          --
          
            alias
          
          
            column
          
           name 列重命名
        
          
            select
          
           id 
          
            as
          
           編號, name 
          
            '名稱'
          
          , sex 性別 
          
            from
          
           student;
        
          ?
        
          --
          
            alias
          
          
            table
          
           name 表重命名
        
          
            select
          
           id, name, s.id, s.name 
          
            from
          
           student s;
        
          ?
        
          --
          
            column
          
           列運算
        
          
            select
          
           (age + id) col 
          
            from
          
           student;
        
          
            select
          
           s.name + 
          
            '-'
          
           + c.name 
          
            from
          
           classes c, student s 
          
            where
          
           s.cid = c.id;
        
          ?
        
          --
          
            where
          
           條件
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id = 2;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id > 7;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id < 3;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id <> 3;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id >= 3;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id <= 5;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id !> 3;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id !< 5;
        
          ?
        
          --
          
            and
          
           并且
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id > 2 
          
            and
          
           sex = 1;
        
          ?
        
          --
          
            or
          
           或者
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id = 2 
          
            or
          
           sex = 1;
        
          ?
        
          --
          
            between
          
           ... 
          
            and
          
           ... 相當(dāng)于并且
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id 
          
            between
          
           2 
          
            and
          
           5;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id 
          
            not
          
          
            between
          
           2 
          
            and
          
           5;
        
          ?
        
          --
          
            like
          
           模糊查詢
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            '%a%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            '%[a][o]%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            not
          
          
            like
          
          
            '%a%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'ja%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            not
          
          
            like
          
          
            '%[j,n]%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            '%[j,n,a]%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            '%[^ja,as,on]%'
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            '%[ja_on]%'
          
          ;
        
          ?
        
          --
          
            in
          
           子查詢
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id 
          
            in
          
           (1, 2);
        
          ?
        
          --
          
            not
          
          
            in
          
           不在其中
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id 
          
            not
          
          
            in
          
           (1, 2);
        
          ?
        
          --
          
            is
          
          
            null
          
           是空
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           age 
          
            is
          
          
            null
          
          ;
        
          ?
        
          --
          
            is
          
          
            not
          
          
            null
          
           不為空
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           age 
          
            is
          
          
            not
          
          
            null
          
          ;
        
          ?
        
          --
          
            order
          
          
            by
          
           排序
        
          
            select
          
           * 
          
            from
          
           student 
          
            order
          
          
            by
          
           name;
        
          
            select
          
           * 
          
            from
          
           student 
          
            order
          
          
            by
          
           name 
          
            desc
          
          ;
        
          
            select
          
           * 
          
            from
          
           student 
          
            order
          
          
            by
          
           name 
          
            asc
          
          ;
        
          ?
        
          --
          
            group
          
          
            by
          
           分組
        
          按照年齡進(jìn)行分組統(tǒng)計
        
          
            select
          
          
            count
          
          (age), age 
          
            from
          
           student 
          
            group
          
          
            by
          
           age;
        
          按照性別進(jìn)行分組統(tǒng)計
        
          
            select
          
          
            count
          
          (*), sex 
          
            from
          
           student 
          
            group
          
          
            by
          
           sex;
        
          按照年齡和性別組合分組統(tǒng)計,并排序
        
          
            select
          
          
            count
          
          (*), sex 
          
            from
          
           student 
          
            group
          
          
            by
          
           sex, age 
          
            order
          
          
            by
          
           age;
        
          按照性別分組,并且是id大于2的記錄最后按照性別排序
        
          
            select
          
          
            count
          
          (*), sex 
          
            from
          
           student 
          
            where
          
           id > 2 
          
            group
          
          
            by
          
           sex 
          
            order
          
          
            by
          
           sex;
        
          查詢id大于2的數(shù)據(jù),并完成運算后的結(jié)果進(jìn)行分組和排序
        
          
            select
          
          
            count
          
          (*), (sex * id) 
          
            new
          
          
            from
          
           student 
          
            where
          
           id > 2 
          
            group
          
          
            by
          
           sex * id 
          
            order
          
          
            by
          
           sex * id;
        
          ?
        
          --
          
            group
          
          
            by
          
          
            all
          
           所有分組
        
          按照年齡分組,是所有的年齡
        
          
            select
          
          
            count
          
          (*), age 
          
            from
          
           student 
          
            group
          
          
            by
          
          
            all
          
           age;
        
          ?
        
          --
          
            having
          
           分組過濾條件
        
          按照年齡分組,過濾年齡為空的數(shù)據(jù),并且統(tǒng)計分組的條數(shù)和現(xiàn)實年齡信息
        
          
            select
          
          
            count
          
          (*), age 
          
            from
          
           student 
          
            group
          
          
            by
          
           age 
          
            having
          
           age 
          
            is
          
          
            not
          
          
            null
          
          ;
        
          ?
        
          按照年齡和cid組合分組,過濾條件是cid大于1的記錄
        
          
            select
          
          
            count
          
          (*), cid, sex 
          
            from
          
           student 
          
            group
          
          
            by
          
           cid, sex 
          
            having
          
           cid > 1;
        
          ?
        
          按照年齡分組,過濾條件是分組后的記錄條數(shù)大于等于2
        
          
            select
          
          
            count
          
          (*), age 
          
            from
          
           student 
          
            group
          
          
            by
          
           age 
          
            having
          
          
            count
          
          (age) >= 2;
        
          ?
        
          按照cid和性別組合分組,過濾條件是cid大于1,cid的最大值大于2
        
          
            select
          
          
            count
          
          (*), cid, sex 
          
            from
          
           student 
          
            group
          
          
            by
          
           cid, sex 
          
            having
          
           cid > 1 
          
            and
          
          
            max
          
          (cid) > 2;
        

? 嵌套子查詢

??? 子查詢是一個嵌套在select、insert、update或delete語句或其他子查詢中的查詢。任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢也稱為內(nèi)部查詢或內(nèi)部選擇,而包含子查詢的語句也成為外部查詢或外部選擇。

?

# from (select … table)示例

          將一個table的查詢結(jié)果當(dāng)做一個新表進(jìn)行查詢
        
          
            select
          
           * 
          
            from
          
           (
        
          
            select
          
           id, name 
          
            from
          
           student 
          
            where
          
           sex = 1
        
          ) t 
          
            where
          
           t.id > 2;
        

上面括號中的語句,就是子查詢語句(內(nèi)部查詢)。在外面的是外部查詢,其中外部查詢可以包含以下語句:

???? 1、 包含常規(guī)選擇列表組件的常規(guī)select查詢

???? 2、 包含一個或多個表或視圖名稱的常規(guī)from語句

???? 3、 可選的where子句

???? 4、 可選的group by子句

???? 5、 可選的having子句

?

# 示例

          查詢班級信息,統(tǒng)計班級學(xué)生人生
        
          
            select
          
           *, (
          
            select
          
          
            count
          
          (*) 
          
            from
          
           student 
          
            where
          
           cid = classes.id) 
          
            as
          
           num 
        
          
            from
          
           classes 
          
            order
          
          
            by
          
           num;
        

?

# in, not in子句查詢示例

          查詢班級id大于小于的這些班級的學(xué)生信息
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           cid 
          
            in
          
           (
        
          
            select
          
           id 
          
            from
          
           classes 
          
            where
          
           id > 2 
          
            and
          
           id < 4
        
          );
        
          ?
        
          查詢不是班的學(xué)生信息
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           cid 
          
            not
          
          
            in
          
           (
        
          
            select
          
           id 
          
            from
          
           classes 
          
            where
          
           name = 
          
            '2班'
          
        
          )
        

in、not in 后面的子句返回的結(jié)果必須是一列,這一列的結(jié)果將會作為查詢條件對應(yīng)前面的條件。如cid對應(yīng)子句的id;

?

# exists和not exists子句查詢示例

          查詢存在班級id為的學(xué)生信息
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
          
            exists
          
           (
        
          
            select
          
           * 
          
            from
          
           classes 
          
            where
          
           id = student.cid 
          
            and
          
           id = 3
        
          );
        
          ?
        
          查詢沒有分配班級的學(xué)生信息
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
          
            not
          
          
            exists
          
           (
        
          
            select
          
           * 
          
            from
          
           classes 
          
            where
          
           id = student.cid
        
          );
        

exists和not exists查詢需要內(nèi)部查詢和外部查詢進(jìn)行一個關(guān)聯(lián)的條件,如果沒有這個條件將是查詢到的所有信息。如:id等于student.id;

?

# some、any、all子句查詢示例

          查詢班級的學(xué)生年齡大于班級的學(xué)生的年齡的信息
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           cid = 5 
          
            and
          
           age > 
          
            all
          
           (
        
          
            select
          
           age 
          
            from
          
           student 
          
            where
          
           cid = 3
        
          );
        
          ?
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           cid = 5 
          
            and
          
           age > 
          
            any
          
           (
        
          
            select
          
           age 
          
            from
          
           student 
          
            where
          
           cid = 3
        
          );
        
          ?
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           cid = 5 
          
            and
          
           age > 
          
            some
          
           (
        
          
            select
          
           age 
          
            from
          
           student 
          
            where
          
           cid = 3
        
          );
        

?

? 聚合查詢

1、 distinct去掉重復(fù)數(shù)據(jù)

          
            select
          
          
            distinct
          
           sex 
          
            from
          
           student;
        
          
            select
          
          
            count
          
          (sex), 
          
            count
          
          (
          
            distinct
          
           sex) 
          
            from
          
           student;
        

?

2、 compute和compute by匯總查詢

          對年齡大于的進(jìn)行匯總
        
          
            select
          
           age 
          
            from
          
           student 
        
          
            where
          
           age > 20 
          
            order
          
          
            by
          
           age 
          
            compute
          
          
            sum
          
          (age) 
          
            by
          
           age;
        
          ?
        
          對年齡大于的按照性別進(jìn)行分組匯總年齡信息
        
          
            select
          
           id, sex, age 
          
            from
          
           student 
        
          
            where
          
           age > 20 
          
            order
          
          
            by
          
           sex, age 
          
            compute
          
          
            sum
          
          (age) 
          
            by
          
           sex;
        
          ?
        
          按照年齡分組匯總
        
          
            select
          
           age 
          
            from
          
           student 
        
          
            where
          
           age > 20 
          
            order
          
          
            by
          
           age, id 
          
            compute
          
          
            sum
          
          (age);
        
          ?
        
          按照年齡分組,年齡匯總,id找最大值
        
          
            select
          
           id, age 
          
            from
          
           student 
        
          
            where
          
           age > 20 
          
            order
          
          
            by
          
           age 
          
            compute
          
          
            sum
          
          (age), 
          
            max
          
          (id);
        

compute進(jìn)行匯總前面是查詢的結(jié)果,后面一條結(jié)果集就是匯總的信息。compute子句中可以添加多個匯總表達(dá)式,可以添加的信息如下:

???? a、 可選by關(guān)鍵字。它是每一列計算指定的行聚合

???? b、 行聚合函數(shù)名稱。包括sum、avg、min、max、count等

???? c、 要對其執(zhí)行聚合函數(shù)的列

???? compute by適合做先分組后匯總的業(yè)務(wù)。compute by后面的列一定要是order by中出現(xiàn)的列。

?

3、 cube匯總

cube匯總和compute效果類似,但語法較簡潔,而且返回的是一個結(jié)果集。

          
            select
          
          
            count
          
          (*), sex 
          
            from
          
           student 
          
            group
          
          
            by
          
           sex 
          
            with
          
          
            cube
          
          ;
        
          
            select
          
          
            count
          
          (*), age, 
          
            sum
          
          (age) 
          
            from
          
           student 
          
            where
          
           age 
          
            is
          
          
            not
          
          
            null
          
          
            group
          
          
            by
          
           age 
          
            with
          
          
            cube
          
          ;
        

cube要結(jié)合group by語句完成分組匯總

?

? 排序函數(shù)

?? 排序在很多地方需要用到,需要對查詢結(jié)果進(jìn)行排序并且給出序號。比如:

?? 1、 對某張表進(jìn)行排序,序號需要遞增不重復(fù)的

?? 2、 對學(xué)生的成績進(jìn)行排序,得出名次,名次可以并列,但名次的序號是連續(xù)遞增的

?? 3、 在某些排序的情況下,需要跳空序號,雖然是并列

基本語法

          排序函數(shù) 
          
            over
          
          ([分組語句] 排序子句[
          
            desc
          
          ][
          
            asc
          
          ])
        
          排序子句 
          
            order
          
          
            by
          
           列名, 列名
        
          分組子句 partition 
          
            by
          
           分組列, 分組列
        

?

# row_number函數(shù)

根據(jù)排序子句給出遞增連續(xù)序號

          按照名稱排序的順序遞增
        
          
            select
          
           s.id, s.name, cid, c.name, row_number() 
          
            over
          
          (
          
            order
          
          
            by
          
           c.name) 
          
            as
          
           number 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        

?

# rank函數(shù)函數(shù)

根據(jù)排序子句給出遞增的序號,但是存在并列并且跳空

          順序遞增
        
          
            select
          
           id, name, rank() 
          
            over
          
          (
          
            order
          
          
            by
          
           cid) 
          
            as
          
           rank 
          
            from
          
           student;
        
          ?
        
          跳過相同遞增
        
          
            select
          
           s.id, s.name, cid, c.name, rank() 
          
            over
          
          (
          
            order
          
          
            by
          
           c.name) 
          
            as
          
           rank 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        

?

# dense_rank函數(shù)

根據(jù)排序子句給出遞增的序號,但是存在并列不跳空

          不跳過,直接遞增
        
          
            select
          
           s.id, s.name, cid, c.name, dense_rank() 
          
            over
          
          (
          
            order
          
          
            by
          
           c.name) 
          
            as
          
           dense 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        

?

# partition by分組子句

可以完成對分組的數(shù)據(jù)進(jìn)行增加排序,partition by可以與以上三個函數(shù)聯(lián)合使用。

          利用partition by按照班級名稱分組,學(xué)生id排序
        
          
            select
          
           s.id, s.name, cid, c.name, row_number() 
          
            over
          
          (partition 
          
            by
          
           c.name 
          
            order
          
          
            by
          
           s.id) 
          
            as
          
           rank 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        
          ?
        
          
            select
          
           s.id, s.name, cid, c.name, rank() 
          
            over
          
          (partition 
          
            by
          
           c.name 
          
            order
          
          
            by
          
           s.id) 
          
            as
          
           rank 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        
          ?
        
          
            select
          
           s.id, s.name, cid, c.name, dense_rank() 
          
            over
          
          (partition 
          
            by
          
           c.name 
          
            order
          
          
            by
          
           s.id) 
          
            as
          
           rank 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        

?

# ntile平均排序函數(shù)

將要排序的數(shù)據(jù)進(jìn)行平分,然后按照等分排序。ntile中的參數(shù)代表分成多少等分。

          
            select
          
           s.id, s.name, cid, c.name, 
        
          ntile(5) 
          
            over
          
          (
          
            order
          
          
            by
          
           c.name) 
          
            as
          
           ntile 
        
          
            from
          
           student s, classes c 
          
            where
          
           cid = c.id;
        

?

? 集合運算

操作兩組查詢結(jié)果,進(jìn)行交集、并集、減集運算

1、 union和union all進(jìn)行并集運算

          --
          
            union
          
           并集、不重復(fù)
        
          
            select
          
           id, name 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'ja%'
          
        
          
            union
          
        
          
            select
          
           id, name 
          
            from
          
           student 
          
            where
          
           id = 4;
        
          ?
        
          --并集、重復(fù)
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'ja%'
          
        
          
            union
          
          
            all
          
        
          
            select
          
           * 
          
            from
          
           student;
        

?

2、 intersect進(jìn)行交集運算

          --交集(相同部分)
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'ja%'
          
        
          
            intersect
          
        
          
            select
          
           * 
          
            from
          
           student;
        

?

3、 except進(jìn)行減集運算

          --減集(除相同部分)
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'ja%'
          
        
          
            except
          
        
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           name 
          
            like
          
          
            'jas%'
          
          ;
        

?

? 公式表表達(dá)式

查詢表的時候,有時候中間表需要重復(fù)使用,這些子查詢被重復(fù)查詢調(diào)用,不但效率低,而且可讀性低,不利于理解。那么公式表表達(dá)式可以解決這個問題。

我們可以將公式表表達(dá)式(CET)視為臨時結(jié)果集,在select、insert、update、delete或是create view語句的執(zhí)行范圍內(nèi)進(jìn)行定義。

          --表達(dá)式
        
          
            with
          
           statNum(id, num) 
          
            as
          
        
          (
        
          
            select
          
           cid, 
          
            count
          
          (*) 
        
          
            from
          
           student 
        
          
            where
          
           id > 0
        
          
            group
          
          
            by
          
           cid
        
          )
        
          
            select
          
           id, num 
          
            from
          
           statNum 
          
            order
          
          
            by
          
           id;
        
          ?
        
          
            with
          
           statNum(id, num) 
          
            as
          
        
          (
        
          
            select
          
           cid, 
          
            count
          
          (*) 
        
          
            from
          
           student 
        
          
            where
          
           id > 0
        
          
            group
          
          
            by
          
           cid
        
          )
        
          
            select
          
          
            max
          
          (id), 
          
            avg
          
          (num) 
          
            from
          
           statNum;
        

?

? 連接查詢

1、 簡化連接查詢

          --簡化聯(lián)接查詢
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s, classes c 
          
            where
          
           s.cid = c.id;
        

?

2、 left join左連接

          --左連接
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s 
          
            left
          
          
            join
          
           classes c 
          
            on
          
           s.cid = c.id;
        

?

3、 right join右連接

          --右連接
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s 
          
            right
          
          
            join
          
           classes c 
          
            on
          
           s.cid = c.id;
        

?

4、 inner join內(nèi)連接

          --內(nèi)連接
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s 
          
            inner
          
          
            join
          
           classes c 
          
            on
          
           s.cid = c.id;
        
          ?
        
          --inner可以省略
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s 
          
            join
          
           classes c 
          
            on
          
           s.cid = c.id;
        

?

5、 cross join交叉連接

          --交叉聯(lián)接查詢,結(jié)果是一個笛卡兒乘積
        
          
            select
          
           s.id, s.name, c.id, c.name 
          
            from
          
           student s 
          
            cross
          
          
            join
          
           classes c
        
          --
          
            where
          
           s.cid = c.id;
        

?

6、 自連接(同一張表進(jìn)行連接查詢)

          --自連接
        
          
            select
          
          
            distinct
          
           s.* 
          
            from
          
           student s, student s1 
          
            where
          
           s.id <> s1.id 
          
            and
          
           s.sex = s1.sex;
        

?

? 函數(shù)

1、 聚合函數(shù)

max最大值、min最小值、count統(tǒng)計、avg平均值、sum求和、var求方差

          
            select
          
        
          
            max
          
          (age) max_age, 
        
          
            min
          
          (age) min_age, 
        
          
            count
          
          (age) count_age, 
        
          
            avg
          
          (age) avg_age, 
        
          
            sum
          
          (age) sum_age, 
        
              var(age) var_age 
        
          
            from
          
           student;
        

?

2、 日期時間函數(shù)

          
            select
          
           dateAdd(
          
            day
          
          , 3, getDate());--加天
        
          
            select
          
           dateAdd(
          
            year
          
          , 3, getDate());--加年
        
          
            select
          
           dateAdd(
          
            hour
          
          , 3, getDate());--加小時
        
          --返回跨兩個指定日期的日期邊界數(shù)和時間邊界數(shù)
        
          
            select
          
           dateDiff(
          
            day
          
          , 
          
            '2011-06-20'
          
          , getDate());
        
          --相差秒數(shù)
        
          
            select
          
           dateDiff(
          
            second
          
          , 
          
            '2011-06-22 11:00:00'
          
          , getDate());
        
          --相差小時數(shù)
        
          
            select
          
           dateDiff(
          
            hour
          
          , 
          
            '2011-06-22 10:00:00'
          
          , getDate());
        
          
            select
          
           dateName(
          
            month
          
          , getDate());--當(dāng)前月份
        
          
            select
          
           dateName(
          
            minute
          
          , getDate());--當(dāng)前分鐘
        
          
            select
          
           dateName(weekday, getDate());--當(dāng)前星期
        
          
            select
          
           datePart(
          
            month
          
          , getDate());--當(dāng)前月份
        
          
            select
          
           datePart(weekday, getDate());--當(dāng)前星期
        
          
            select
          
           datePart(
          
            second
          
          , getDate());--當(dāng)前秒數(shù)
        
          
            select
          
          
            day
          
          (getDate());--返回當(dāng)前日期天數(shù)
        
          
            select
          
          
            day
          
          (
          
            '2011-06-30'
          
          );--返回當(dāng)前日期天數(shù)
        
          
            select
          
          
            month
          
          (getDate());--返回當(dāng)前日期月份
        
          
            select
          
          
            month
          
          (
          
            '2011-11-10'
          
          );
        
          
            select
          
          
            year
          
          (getDate());--返回當(dāng)前日期年份
        
          
            select
          
          
            year
          
          (
          
            '2010-11-10'
          
          );
        
          
            select
          
           getDate();--當(dāng)前系統(tǒng)日期
        
          
            select
          
           getUTCDate();--utc日期
        

?

3、 數(shù)學(xué)函數(shù)

          
            select
          
           pi();--PI函數(shù)
        
          
            select
          
           rand(100), rand(50), rand(), rand();--隨機數(shù)
        
          
            select
          
           round(rand(), 3), round(rand(100), 5);--精確小數(shù)位
        
          --精確位數(shù),負(fù)數(shù)表示小數(shù)點前
        
          
            select
          
           round(123.456, 2), round(254.124, -2);
        
          
            select
          
           round(123.4567, 1, 2);
        

?

4、 元數(shù)據(jù)

          
            select
          
           col_name(object_id(
          
            'student'
          
          ), 1);--返回列名
        
          
            select
          
           col_name(object_id(
          
            'student'
          
          ), 2);
        
          --該列數(shù)據(jù)類型長度
        
          
            select
          
           col_length(
          
            'student'
          
          , col_name(object_id(
          
            'student'
          
          ), 2)); 
        
          --該列數(shù)據(jù)類型長度
        
          
            select
          
           col_length(
          
            'student'
          
          , col_name(object_id(
          
            'student'
          
          ), 1)); 
        
          --返回類型名稱、類型id
        
          
            select
          
           type_name(type_id(
          
            'varchar'
          
          )), type_id(
          
            'varchar'
          
          );
        
          --返回列類型長度
        
          
            select
          
           columnProperty(object_id(
          
            'student'
          
          ), 
          
            'name'
          
          , 
          
            'PRECISION'
          
          );
        
          --返回列所在索引位置
        
          
            select
          
           columnProperty(object_id(
          
            'student'
          
          ), 
          
            'sex'
          
          , 
          
            'ColumnId'
          
          );
        

?

5、 字符串函數(shù)

          
            select
          
           ascii(
          
            'a'
          
          );--字符轉(zhuǎn)換ascii值
        
          
            select
          
           ascii(
          
            'A'
          
          );
        
          
            select
          
          
            char
          
          (97);--ascii值轉(zhuǎn)換字符
        
          
            select
          
          
            char
          
          (65);
        
          
            select
          
          
            nchar
          
          (65);
        
          
            select
          
          
            nchar
          
          (45231);
        
          
            select
          
          
            nchar
          
          (32993);--unicode轉(zhuǎn)換字符
        
          
            select
          
           unicode(
          
            'A'
          
          ), unicode(
          
            '中'
          
          );--返回unicode編碼值
        
          
            select
          
           soundex(
          
            'hello'
          
          ), soundex(
          
            'world'
          
          ), soundex(
          
            'word'
          
          );
        
          
            select
          
           patindex(
          
            '%a'
          
          , 
          
            'ta'
          
          ), patindex(
          
            '%ac%'
          
          , 
          
            'jack'
          
          ), patindex(
          
            'dex%'
          
          , 
          
            'dexjack'
          
          );--匹配字符索引
        
          
            select
          
          
            'a'
          
           + 
          
            space
          
          (2) + 
          
            'b'
          
          , 
          
            'c'
          
           + 
          
            space
          
          (5) + 
          
            'd'
          
          ;--輸出空格
        
          
            select
          
           charIndex(
          
            'o'
          
          , 
          
            'hello world'
          
          );--查找索引
        
          
            select
          
           charIndex(
          
            'o'
          
          , 
          
            'hello world'
          
          , 6);--查找索引
        
          
            select
          
           quoteName(
          
            'abc[]def'
          
          ), quoteName(
          
            '123]45'
          
          );
        
          --精確數(shù)字
        
          
            select
          
           str(123.456, 2), str(123.456, 3), str(123.456, 4);
        
          
            select
          
           str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
        
          
            select
          
           difference(
          
            'hello'
          
          , 
          
            'helloWorld'
          
          );--比較字符串相同
        
          
            select
          
           difference(
          
            'hello'
          
          , 
          
            'world'
          
          );
        
          
            select
          
           difference(
          
            'hello'
          
          , 
          
            'llo'
          
          );
        
          
            select
          
           difference(
          
            'hello'
          
          , 
          
            'hel'
          
          );
        
          
            select
          
           difference(
          
            'hello'
          
          , 
          
            'hello'
          
          );
        
          
            select
          
           replace(
          
            'abcedef'
          
          , 
          
            'e'
          
          , 
          
            'E'
          
          );--替換字符串
        
          
            select
          
           stuff(
          
            'hello world'
          
          , 3, 4, 
          
            'ABC'
          
          );--指定位置替換字符串
        
          
            select
          
           replicate(
          
            'abc#'
          
          , 3);--重復(fù)字符串
        
          
            select
          
          
            subString
          
          (
          
            'abc'
          
          , 1, 1), 
          
            subString
          
          (
          
            'abc'
          
          , 1, 2), 
          
            subString
          
          (
          
            'hello Wrold'
          
          , 7, 5);--截取字符串
        
          
            select
          
           len(
          
            'abc'
          
          );--返回長度
        
          
            select
          
           reverse(
          
            'sqlServer'
          
          );--反轉(zhuǎn)字符串
        
          ?
        
          
            select
          
          
            left
          
          (
          
            'leftString'
          
          , 4);--取左邊字符串
        
          
            select
          
          
            left
          
          (
          
            'leftString'
          
          , 7);
        
          
            select
          
          
            right
          
          (
          
            'leftString'
          
          , 6);--取右邊字符串
        
          
            select
          
          
            right
          
          (
          
            'leftString'
          
          , 3);
        
          
            select
          
          
            lower
          
          (
          
            'aBc'
          
          ), 
          
            lower
          
          (
          
            'ABC'
          
          );--小寫
        
          
            select
          
          
            upper
          
          (
          
            'aBc'
          
          ), 
          
            upper
          
          (
          
            'abc'
          
          );--大寫
        
          --去掉左邊空格
        
          
            select
          
           ltrim(
          
            ' abc'
          
          ), ltrim(
          
            '# abc#'
          
          ), ltrim(
          
            '  abc'
          
          );
        
          --去掉右邊空格
        
          
            select
          
           rtrim(
          
            ' abc    '
          
          ), rtrim(
          
            '# abc#   '
          
          ), rtrim(
          
            'abc'
          
          );
        

?

6、 安全函數(shù)

          
            select
          
          
            current_user
          
          ;
        
          
            select
          
          
            user
          
          ;
        
          
            select
          
           user_id(), user_id(
          
            'dbo'
          
          ), user_id(
          
            'public'
          
          ), user_id(
          
            'guest'
          
          );
        
          
            select
          
           user_name(), user_name(1), user_name(0), user_name(2);
        
          
            select
          
          
            session_user
          
          ;
        
          
            select
          
           suser_id(
          
            'sa'
          
          );
        
          
            select
          
           suser_sid(), suser_sid(
          
            'sa'
          
          ), suser_sid(
          
            'sysadmin'
          
          ), suser_sid(
          
            'serveradmin'
          
          );
        
          
            select
          
           is_member(
          
            'dbo'
          
          ), is_member(
          
            'public'
          
          );
        
          
            select
          
           suser_name(), suser_name(1), suser_name(2), suser_name(3);
        
          
            select
          
           suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
        
          
            select
          
           is_srvRoleMember(
          
            'sysadmin'
          
          ), is_srvRoleMember(
          
            'serveradmin'
          
          );
        
          
            select
          
           permissions(object_id(
          
            'student'
          
          ));
        
          
            select
          
          
            system_user
          
          ;
        
          
            select
          
           schema_id(), schema_id(
          
            'dbo'
          
          ), schema_id(
          
            'guest'
          
          );
        
          
            select
          
           schema_name(), schema_name(1), schema_name(2), schema_name(3);
        

?

7、 系統(tǒng)函數(shù)

          
            select
          
           app_name();--當(dāng)前會話的應(yīng)用程序名稱
        
          
            select
          
          
            cast
          
          (2011 
          
            as
          
           datetime), 
          
            cast
          
          (
          
            '10'
          
          
            as
          
           money), 
          
            cast
          
          (
          
            '0'
          
          
            as
          
           varbinary);--類型轉(zhuǎn)換
        
          
            select
          
          
            convert
          
          (datetime, 
          
            '2011'
          
          );--類型轉(zhuǎn)換
        
          
            select
          
          
            coalesce
          
          (
          
            null
          
          , 
          
            'a'
          
          ), 
          
            coalesce
          
          (
          
            '123'
          
          , 
          
            'a'
          
          );--返回其參數(shù)中第一個非空表達(dá)式
        
          
            select
          
           collationProperty(
          
            'Traditional_Spanish_CS_AS_KS_WS'
          
          , 
          
            'CodePage'
          
          );
        
          
            select
          
          
            current_timestamp
          
          ;--當(dāng)前時間戳
        
          
            select
          
          
            current_user
          
          ;
        
          
            select
          
           isDate(getDate()), isDate(
          
            'abc'
          
          ), isNumeric(1), isNumeric(
          
            'a'
          
          );
        
          
            select
          
           dataLength(
          
            'abc'
          
          );
        
          
            select
          
           host_id();
        
          
            select
          
           host_name();
        
          
            select
          
           db_name();
        
          
            select
          
           ident_current(
          
            'student'
          
          ), ident_current(
          
            'classes'
          
          );--返回主鍵id的最大值
        
          
            select
          
           ident_incr(
          
            'student'
          
          ), ident_incr(
          
            'classes'
          
          );--id的增量值
        
          
            select
          
           ident_seed(
          
            'student'
          
          ), ident_seed(
          
            'classes'
          
          );
        
          
            select
          
           @@
          
            identity
          
          ;--最后一次自增的值
        
          
            select
          
          
            identity
          
          (
          
            int
          
          , 1, 1) 
          
            as
          
           id 
          
            into
          
           tab 
          
            from
          
           student;--將studeng表的烈屬,以/1自增形式創(chuàng)建一個tab
        
          
            select
          
           * 
          
            from
          
           tab;
        
          
            select
          
           @@
          
            rowcount
          
          ;--影響行數(shù)
        
          
            select
          
           @@cursor_rows;--返回連接上打開的游標(biāo)的當(dāng)前限定行的數(shù)目
        
          
            select
          
           @@error;--T-SQL的錯誤號
        
          
            select
          
           @@procid;
        

?

8、 配置函數(shù)

          
            set
          
           datefirst 7;--設(shè)置每周的第一天,表示周日
        
          
            select
          
          
            @@datefirst
          
          
            as
          
          
            '星期的第一天'
          
          , datepart(dw, getDate()) 
          
            AS
          
          
            '今天是星期'
          
          ;
        
          
            select
          
           @@dbts;--返回當(dāng)前數(shù)據(jù)庫唯一時間戳
        
          
            set
          
          
            language
          
          
            'Italian'
          
          ;
        
          
            select
          
          
            @@langId
          
          
            as
          
          
            'Language ID'
          
          ;--返回語言id
        
          
            select
          
          
            @@language
          
          
            as
          
          
            'Language Name'
          
          ;--返回當(dāng)前語言名稱
        
          
            select
          
           @@lock_timeout;--返回當(dāng)前會話的當(dāng)前鎖定超時設(shè)置(毫秒)
        
          
            select
          
           @@max_connections;--返回SQL Server 實例允許同時進(jìn)行的最大用戶連接數(shù)
        
          
            select
          
          
            @@MAX_PRECISION
          
          
            AS
          
          
            'Max Precision'
          
          ;--返回decimal 和numeric 數(shù)據(jù)類型所用的精度級別
        
          
            select
          
           @@SERVERNAME;--
          
            SQL
          
           Server 的本地服務(wù)器的名稱
        
          
            select
          
           @@SERVICENAME;--服務(wù)名
        
          
            select
          
           @@SPID;--當(dāng)前會話進(jìn)程id
        
          
            select
          
           @@
          
            textSize
          
          ;
        
          
            select
          
           @@version;--當(dāng)前數(shù)據(jù)庫版本信息
        

?

9、 系統(tǒng)統(tǒng)計函數(shù)

          
            select
          
           @@CONNECTIONS;--連接數(shù)
        
          
            select
          
           @@PACK_RECEIVED;
        
          
            select
          
           @@CPU_BUSY;
        
          
            select
          
           @@PACK_SENT;
        
          
            select
          
           @@TIMETICKS;
        
          
            select
          
           @@IDLE;
        
          
            select
          
           @@TOTAL_ERRORS;
        
          
            select
          
           @@IO_BUSY;
        
          
            select
          
           @@TOTAL_READ;--讀取磁盤次數(shù)
        
          
            select
          
           @@PACKET_ERRORS;--發(fā)生的網(wǎng)絡(luò)數(shù)據(jù)包錯誤數(shù)
        
          
            select
          
           @@TOTAL_WRITE;--sqlserver執(zhí)行的磁盤寫入次數(shù)
        
          
            select
          
           patIndex(
          
            '%soft%'
          
          , 
          
            'microsoft SqlServer'
          
          );
        
          
            select
          
           patIndex(
          
            'soft%'
          
          , 
          
            'software SqlServer'
          
          );
        
          
            select
          
           patIndex(
          
            '%soft'
          
          , 
          
            'SqlServer microsoft'
          
          );
        
          
            select
          
           patIndex(
          
            '%so_gr%'
          
          , 
          
            'Jsonisprogram'
          
          );
        

?

10、 用戶自定義函數(shù)

# 查看當(dāng)前數(shù)據(jù)庫所有函數(shù)

          --查詢所有已創(chuàng)建函數(shù)
        
          
            select
          
           definition,* 
          
            from
          
           sys.sql_modules m 
          
            join
          
           sys.objects o 
          
            on
          
           m.object_id = o.object_id
        
          
            and
          
           type 
          
            in
          
          (
          
            'fn'
          
          , 
          
            'if'
          
          , 
          
            'tf'
          
          );
        

?

# 創(chuàng)建函數(shù)

          
            if
          
           (object_id(
          
            'fun_add'
          
          , 
          
            'fn'
          
          ) 
          
            is
          
          
            not
          
          
            null
          
          )
        
          
            drop
          
          
            function
          
           fun_add
        
          
            go
          
        
          
            create
          
          
            function
          
           fun_add(@num1 
          
            int
          
          , @num2 
          
            int
          
          )
        
          
            returns
          
          
            int
          
        
          
            with
          
          
            execute
          
          
            as
          
           caller
        
          
            as
          
        
          
            begin
          
        
          
            declare
          
           @
          
            result
          
          
            int
          
          ;
        
          
            if
          
           (@num1 
          
            is
          
          
            null
          
          )
        
          
            set
          
           @num1 = 0;
        
          
            if
          
           (@num2 
          
            is
          
          
            null
          
          )
        
          
            set
          
           @num2 = 0;
        
          
            set
          
           @
          
            result
          
           = @num1 + @num2;
        
          
            return
          
           @
          
            result
          
          ;
        
          
            end
          
        
          
            go
          
        
          調(diào)用函數(shù)
        
          
            select
          
           dbo.fun_add(id, age) 
          
            from
          
           student;
        
          ?
        
          --自定義函數(shù),字符串連接
        
          
            if
          
           (object_id(
          
            'fun_append'
          
          , 
          
            'fn'
          
          ) 
          
            is
          
          
            not
          
          
            null
          
          )
        
          
            drop
          
          
            function
          
           fun_append
        
          
            go
          
        
          
            create
          
          
            function
          
           fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
        
          
            returns
          
           nvarchar(2048)
        
          
            as
          
        
          
            begin
          
        
          
            return
          
           @args + @args2;
        
          
            end
          
        
          
            go
          
        
          ?
        
          
            select
          
           dbo.fun_append(name, 
          
            'abc'
          
          ) 
          
            from
          
           student;
        

?

# 修改函數(shù)

          
            alter
          
          
            function
          
           fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
        
          
            returns
          
           nvarchar(1024)
        
          
            as
          
        
          
            begin
          
        
          
            declare
          
           @
          
            result
          
          
            varchar
          
          (1024);    
        
                  --coalesce返回第一個不為null的值    
        
          
            set
          
           @args = 
          
            coalesce
          
          (@args, 
          
            ''
          
          );
        
          
            set
          
           @args2 = 
          
            coalesce
          
          (@args2, 
          
            ''
          
          );;
        
          
            set
          
           @
          
            result
          
           = @args + @args2;
        
          
            return
          
           @
          
            result
          
          ;
        
          
            end
          
        
          
            go
          
        
          ?
        
          
            select
          
           dbo.fun_append(name, 
          
            '#abc'
          
          ) 
          
            from
          
           student;
        

?

# 返回table類型函數(shù)

          --返回table對象函數(shù)
        
          
            select
          
           name, object_id, type 
          
            from
          
           sys.objects 
          
            where
          
           type 
          
            in
          
           (
          
            'fn'
          
          , 
          
            'if'
          
          , 
          
            'tf'
          
          ) 
          
            or
          
           type 
          
            like
          
          
            '%f%'
          
          ;
        
          ?
        
          
            if
          
           (
          
            exists
          
           (
          
            select
          
           * 
          
            from
          
           sys.objects 
          
            where
          
           type 
          
            in
          
           (
          
            'fn'
          
          , 
          
            'if'
          
          , 
          
            'tf'
          
          ) 
          
            and
          
           name = 
          
            'fun_find_stuRecord'
          
          ))
        
          
            drop
          
          
            function
          
           fun_find_stuRecord
        
          
            go
          
        
          
            create
          
          
            function
          
           fun_find_stuRecord(@id 
          
            int
          
          )
        
          
            returns
          
          
            table
          
        
          
            as
          
        
          
            return
          
           (
          
            select
          
           * 
          
            from
          
           student 
          
            where
          
           id = @id);
        
          
            go
          
        
          ?
        
          
            select
          
           * 
          
            from
          
           dbo.fun_find_stuRecord(2);
        

轉(zhuǎn):SQL Server T-SQL高級查詢


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯(lián)系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。?!

發(fā)表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 五莲县| 奇台县| 临夏县| 伊川县| 开江县| 交口县| 格尔木市| 辽阳市| 集安市| 三门县| 卓尼县| 建瓯市| 西吉县| 汪清县| 布拖县| 翁牛特旗| 咸阳市| 锡林郭勒盟| 红原县| 望奎县| 广宁县| 宕昌县| 会泽县| 南皮县| 桃江县| 平远县| 工布江达县| 绍兴县| 巢湖市| 河南省| 桐城市| 郸城县| 社旗县| 招远市| 五河县| 冀州市| 广饶县| 刚察县| 怀化市| 收藏| 威远县|