sql和t-sql语句学习

本文的部分内容从网上查找得到的，并给出了原文地址。

        一 常用函数(function)

        1 数据类型转换函数

        CAST ( expression AS data_type ) -- 将某种数据类型的表达式显式转换为另一种数据类型
        CONVERT (data_type[(length)], expression [, style])-- 将某种数据类型的表达式显式转换为另一种数据类型

        2 统计函数

        AVG -- 返回组中值的平均值。空值将被忽略。
        COUNT--返回组中项目的数量。
        MAX--返回表达式的最大值。
        MIN--返回表达式的最小值。
        SUM--返回表达式中所有值的和，或只返回 DISTINCT 值。SUM 只能用于数字列。空值将被忽略。

        STDEV()
　　--STDEV()函数返回表达式中所有数据的标准差
　　--STDEVP()
　　--STDEVP()函数返回总体标准差　

　　VAR()
　　--VAR()函数返回表达式中所有值的统计变异数　　

　　VARP()
　　--VARP()函数返回总体变异数　

        3 数学函数

        (1) 取近似值函数

        SQRT( float_expression )--返回给定表达式的平方根。
        CEILING( numeric_expression )--返回大于或等于所给数字表达式的最小整数。
        FLOOR( numeric_expression )--返回小于或等于所给数字表达式的最大整数。
        ROUND(numeric_expression , length)--返回数字表达式并四舍五入为指定的长度或精度。
        SIGN( numeric_expression )--返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。
        ABS ( numeric_expression )--返回给定数字表达式的绝对值。
        PI(), 返回 PI 的常量值。
        RAND(), RAND( seed )返回 0 到1 之间的随机float 值。  

        (2)三角函数

        SIN(float_expression)--返回以弧度表示的角的正弦
        COS(float_expression)--返回以弧度表示的角的余弦
        TAN(float_expression)--返回以弧度表示的角的正切
        COT(float_expression)--返回以弧度表示的角的余切

        (3)反三角函数

        ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
        ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角
        ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角
        ATAN2(float_expression1,float_expression2)--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
        DEGREES(numeric_expression)--当给出以弧度为单位的角度时，返回相应的以度数为单位的角度。
        RADIANS(numeric_expression)------对于在数字表达式中输入的度数值返回弧度值。
        EXP(float_expression)--返回表达式的指数值
        LOG(float_expression)--返回表达式的自然对数值
        LOG10(float_expression)--返回表达式的以10为底的对数值
        SQRT(float_expression)--返回表达式的平方根

        4 字符串函数

        ASCII ( character_expression )--返回字符表达式最左端字符的 ASCII 代码值。
        CHAR ( integer_expression )--将 int ASCII 代码转换为字符的字符串函数。 
        LOWER ( character_expression )--将大写字符数据转换为小写字符数据后返回字符表达式。
        UPPER ( character_expression )--返回将小写字符数据转换为大写的字符表达式。
        STR ( float_expression [ , length [ , decimal ] ] ) --由数字数据转换来的字符数据。
        LTRIM( character_expression  )--删除起始空格后返回字符表达式。
        RTRIM ( character_expression )--截断所有尾随空格后返回一个字符串。
        LEFT ( character_expression , integer_expression ) --返回从字符串左边开始指定个数的字符。
        RIGHT ( character_expression , integer_expression ) --返回字符串中从右边开始指定个数的 integer_expression 字符。
        SUBSTRING ( expression , start , length )--截取字符串
        CHARINDEX ( expression1 , expression2 [ , start_location ] ) --返回字符串中指定表达式的起始位置，没有返回0 
        PATINDEX ( '%pattern%' , expression ) -- 返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
        REPLICATE ( character_expression , integer_expression )-- 以指定的次数重复字符表达式。
        REVERSE ( character_expression )-- 返回字符表达式的反转。 
        REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )--用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
        STUFF ( character_expression , start , length , character_expression )--删除指定长度的字符并在指定的起始点插入另一组字符。
        SPACE ( integer_expression )-- 返回由重复的空格组成的字符串。

        5 日期函数

　　DAY()--函数返回date_expression中的日期值
　　MONTH()--函数返回date_expression中的月份值
　　YEAR()--函数返回date_expression中的年份值
　　DATEADD( datepart , number, date )--函数返回指定日期date加上指定的额外日期间隔number产生的新日期
　　DATEDIFF( datepart , startdate , enddate )--函数返回两个指定日期在datepart方面的不同之处
　　DATENAME(datepart , date )------函数以字符串的形式返回日期的指定部分
        DATEPART( datepart , date )--函数以整数值的形式返回日期的指定部分
        GETDATE()------函数以DATETIME的缺省格式返回系统当前的日期和时间　　

        6 系统函数

        APP_NAME()------函数返回当前执行的应用程序的名称
        COALESCE()-----函数返回众多表达式中第一个非NULL表达式的值
        COL_LENGTH ( 'table' , 'column' ) ----函数返回表中指定字段的长度值
        COL_NAME ( table_id , column_id )----返回数据库列的名称，该列具有相应的表标识号和列标识号。
        DATALENGTH()-----函数返回数据表达式的数据的实际长度
        DB_ID ( [ 'database_name' ] ) ------函数返回数据库的编号
        DB_NAME(database_id)------函数返回数据库的名称
        HOST_ID()-----函数返回服务器端计算机的名称
        HOST_NAME()-----函数返回服务器端计算机的名称
        IDENTITY ( data_type [ , seed , increment ] ) AS column_name --IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中
        ISDATE()----函数判断所给定的表达式是否为合理日期
        ISNULL ( check_expression , replacement_value ) --函数将表达式中的NULL值用指定值替换
        ISNUMERIC()----函数判断所给定的表达式是否为合理的数值
        NEWID()----函数返回一个UNIQUEIDENTIFIER类型的数值
        NULLIF ( expression , expression )--NULLIF函数在expression1与expression2相等时返回NULL值若不相等时则返回xpression1的值

        [来源： http://stevieliu.blogchina.com/stevieliu/4720568.html]
        [参考《SQL Server联机丛书》，略有修改]

        二 SQL Server中各个系统表的作用
 
        sysaltfiles    主数据库               保存数据库的文件 
        syscharsets    主数据库               字符集与排序顺序 
        sysconfigures  主数据库               配置选项 
        syscurconfigs  主数据库               当前配置选项 
        sysdatabases   主数据库               服务器中的数据库 
        syslanguages   主数据库               语言 
        syslogins      主数据库               登陆帐号信息 
        sysoledbusers  主数据库               链接服务器登陆信息 
        sysprocesses   主数据库               进程 
        sysremotelogins主数据库               远程登录帐号 
        syscolumns     每个数据库             列 
        sysconstrains  每个数据库             限制 
        sysfilegroups  每个数据库             文件组 
        sysfiles       每个数据库             文件 
        sysforeignkeys 每个数据库             外部关键字 
        sysindexs      每个数据库             索引 
        sysmenbers     每个数据库             角色成员 
        sysobjects     每个数据库             所有数据库对象 
        syspermissions 每个数据库             权限 
        systypes       每个数据库             用户定义数据类型 
        sysusers       每个数据库             用户

        三 Transact_SQL

        1  语法

语 句 功 能(1) 数据操作 SELECT从数据库表中检索数据行和列INSERT向数据库表添加新数据行DELETE 从数据库表中删除数据行UPDATE更新数据库表中的数据(2)数据定义 CREATE TABLE 创建一个数据库表DROP TABLE从数据库中删除表ALTER TABLE修改数据库表结构CREATE VIEW 创建一个视图DROP VIEW从数据库中删除视图CREATE INDEX 为数据库表创建一个索引DROP INDEX 从数据库中删除索引CREATE PROCEDURE创建一个存储过程DROP PROCEDURE从数据库中删除存储过程CREATE TRIGGER创建一个触发器DROP TRIGGER 从数据库中删除触发器CREATE SCHEMA 向数据库添加一个新模式DROP SCHEMA 从数据库中删除一个模式CREATE DOMAIN 创建一个数据值域ALTER DOMAIN改变域定义DROP DOMAIN 从数据库中删除一个域(3)数据控制 GRANT授予用户访问权限DENY拒绝用户访问REVOKE解除用户访问权限(4)事务控制 COMMIT结束当前事务ROLLBACK  中止当前事务SET TRANSACTION 定义当前事务数据访问特征(5)程序化SQL DECLARE  为查询设定游标EXPLAN为查询描述数据访问计划OPEN检索查询结果打开一个游标FETCH 检索一行查询结果CLOSE关闭游标PREPARE为动态执行准备SQL 语句EXECUTE动态地执行SQL 语句DESCRIBE描述准备好的查询

                      

 

 

     

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

          (6) 局部变量

        declare @id char(10)
        --set @id = '10010001'
        select @id = '10010001'

        (7)全局变量

        ---必须以@@开头

        (8) IF ELSE

        --举例：

        declare @x int @y int @z int
        select @x = 1 @y = 2 @z=3
        if @x > @y
                print 'x > y' --打印字符串'x > y'
        else if @y > @z
                print 'y > z'
        else print 'z > y'

        (9) CASE

        --举例：

        use pangu
        update employee
        set e_wage =
        case
                when job_level = ’1’ then e_wage*1.08
                when job_level = ’2’ then e_wage*1.07
                when job_level = ’3’ then e_wage*1.06
                else e_wage*1.05
        end

        (10) WHILE CONTINUE BREAK

        --举例：

        declare @x int @y int @c int
        select @x = 1 @y=1
        while @x < 3
                 begin
                        print @x --打印变量x 的值
                        while @y < 3
                                begin
                                        select @c = + @y
                                        print @c --打印变量c 的值
                                        select @y = @y + 1
                                end
                        select @x = @x + 1
                          select @y = 1
                end

        (11) WAITFOR

        --举例：

        --例 等待1 小时2 分零3 秒后才执行SELECT 语句
        waitfor delay ’01:02:03’
        select * from employee
        --例 等到晚上11 点零8 分后才执行SELECT 语句
        waitfor time ’23:08:00’
        select * from employee

        2 常见用法举例

        (1) SELECT

        select *(列名) from table_name(表名) where column_name operator value
        ex:(宿主)
        select * from stock_information where stockid   = str(nid)
        stockname = 'str_name' 
        stockname like '% find this %' 
        stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
        stockname like '[^F-M]%'   --------- (^排除指定范围)
         --------- 只能在使用like关键字的where子句中使用通配符)
        or stockpath = 'stock_path'
        or stocknumber < 1000
        and stockindex = 24
        not stocksex = 'man'
        stocknumber between 20 and 100
        stocknumber in(10,20,30)
        order by stockid desc(asc) --------- 排序，desc-降序，asc-升序
        order by 1,2 --------- by列号
        stockname = (select stockname from stock_information  where stockid  = 4)
         --------- 子查询
        --------- 除非能确保内层select只返回一个行的值，
        --------- 否则应在外层where子句中用一个in限定符
        select distinct column_name form table_name --------- distinct指定检索独有的列值，不重复
        select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
        select stockname , "stocknumber" = count(*) from table_name group by stockname
                                      --------- group by 将表按行分组,指定列中有相同的值
                 having count(*) = 2  ---------  having选定指定的组
        
        select * 
        from table1, table2                  
        where table1.id *= table2.id -------- 左外部连接，table1中有的而table2中没有得以null表示
        table1.id =* table2.id -------- 右外部连接

        select stockname from table1
        union [all]  -----  union合并查询结果集，all-保留重复行
        select stockname from table2

        (2) insert

        insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
                value (select Stockname , Stocknumber from Stock_table2)---value为select语句

        (3) update

        update table_name set Stockname = "xxx" [where Stockid = 3]
                Stockname = default
                Stockname = null
                Stocknumber = Stockname + 4

        (4) delete

        delete from table_name where Stockid = 3
        truncate table_name ----------- 删除表中所有行，仍保持表的完整性
        drop table table_name --------------- 完全删除表

        (5) alter table

        alter table database.owner.table_name add column_name char(2) null ..
        sp_help table_name ---- 显示表已有特征
        create table table_name (name char(20), age smallint, lname varchar(30))
        insert into table_name select  ----- 实现删除列的方法（创建新表）
        alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束

        四 MS-SQL数据库开发常用汇总

        1 按姓氏笔画排序

        Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

        2 数据库加密

        select encrypt('原始密码')
        select pwdencrypt('原始密码')
        select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
        select pwdencrypt('原始密码')
        select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

        3 取回表中字段

        declare @list varchar(1000),@sql nvarchar(1000) 
        select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
        set @sql='select '+right(@list,len(@list)-1)+' from 表A' 
        exec (@sql)

        4 查看硬盘分区

        EXEC master..xp_fixeddrives

        5 比较A,B表是否相等
        if (select checksum_agg(binary_checksum(*)) from A)
        =
        (select checksum_agg(binary_checksum(*)) from B)
        print '相等'
        else
        print '不相等'

        6 杀掉所有的事件探察器进程
        DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) 
                FROM master.dbo.sysprocesses
                WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
        EXEC sp_msforeach_worker '?'

        7 记录搜索

        (1) 开头到N条记录

        Select Top N * From 表

        (2) N到M条记录(要有主索引ID)

        Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

        (3)N到结尾记录

        Select Top N * From 表 Order by ID Desc

        8 如何修改数据库的名称

        sp_renamedb 'old_name', 'new_name'

        9 获取当前数据库中的所有用户表

        select Name from sysobjects where xtype='u' and status>=0

        或者：

        select   *   from   information_schema.tables

        10 获取某一个表的所有字段

        select name from syscolumns where 表名')

        11 查看与某一个表相关的视图、存储过程、函数

        select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

        12 查看当前数据库中所有存储过程

        select name as 存储过程名称 from sysobjects where xtype='P'

        13 查询用户创建的所有数据库

        select * from master..sysdatabases D 
                where sid not in(select sid from master..syslogins where )

        或者

        select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01

        14 查询某一个表的字段和数据类型

        select column_name,data_type from information_schema.columns
        where table_name = '表名' 

       15 判断一个表是否存在
 
        if   exists(select   1   from   sysobjects   where      and   xtype='U')   
                print   '在'   
        else     
                print   '不在'   

         或者
 
        if   objectproperty(object_id('要判断的表名'),'isusertable')   is   null   
                print   '无此表'   
                else   
        print   '有此表'

       16 在存储过程中删除表的列

       http://blog.csdn.net/scucj/archive/2006/07/14/919525.aspx

       17 创建一个表和两个字段，并指定其中一个字段为自增的关键字

       CREATE TABLE '+ @TABLENAME + ' (tableID BigInt identity(1,1) primary key,myUserID BigInt)'