|
本文的部分內(nèi)容從網(wǎng)上查找得到的，并給出了原文地址。 一 常用函數(shù)(function) 1 數(shù)據(jù)類型轉(zhuǎn)換函數(shù) CAST ( expression AS data_type ) -- 將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型 CONVERT (data_type[(length)], expression [, style])-- 將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型 2 統(tǒng)計(jì)函數(shù) AVG -- 返回組中值的平均值。空值將被忽略。 COUNT--返回組中項(xiàng)目的數(shù)量。 MAX--返回表達(dá)式的最大值。 MIN--返回表達(dá)式的最小值。 SUM--返回表達(dá)式中所有值的和，或只返回 DISTINCT 值。SUM 只能用于數(shù)字列?？罩祵⒈缓雎浴?br> STDEV() --STDEV()函數(shù)返回表達(dá)式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差 --STDEVP() --STDEVP()函數(shù)返回總體標(biāo)準(zhǔn)差 VAR() --VAR()函數(shù)返回表達(dá)式中所有值的統(tǒng)計(jì)變異數(shù) VARP() --VARP()函數(shù)返回總體變異數(shù) 3 數(shù)學(xué)函數(shù) (1) 取近似值函數(shù) SQRT( float_expression )--返回給定表達(dá)式的平方根。 CEILING( numeric_expression )--返回大于或等于所給數(shù)字表達(dá)式的最小整數(shù)。 FLOOR( numeric_expression )--返回小于或等于所給數(shù)字表達(dá)式的最大整數(shù)。 ROUND(numeric_expression , length)--返回?cái)?shù)字表達(dá)式并四舍五入為指定的長(zhǎng)度或精度。 SIGN( numeric_expression )--返回給定表達(dá)式的正 (+1)、零 (0) 或負(fù) (-1) 號(hào)。 ABS ( numeric_expression )--返回給定數(shù)字表達(dá)式的絕對(duì)值。 PI(), 返回 PI 的常量值。 RAND(), RAND( seed )返回 0 到1 之間的隨機(jī)float 值。 (2)三角函數(shù) SIN(float_expression)--返回以弧度表示的角的正弦 COS(float_expression)--返回以弧度表示的角的余弦 TAN(float_expression)--返回以弧度表示的角的正切 COT(float_expression)--返回以弧度表示的角的余切 (3)反三角函數(shù) 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)--當(dāng)給出以弧度為單位的角度時(shí)，返回相應(yīng)的以度數(shù)為單位的角度。 RADIANS(numeric_expression)------對(duì)于在數(shù)字表達(dá)式中輸入的度數(shù)值返回弧度值。 EXP(float_expression)--返回表達(dá)式的指數(shù)值 LOG(float_expression)--返回表達(dá)式的自然對(duì)數(shù)值 LOG10(float_expression)--返回表達(dá)式的以10為底的對(duì)數(shù)值 SQRT(float_expression)--返回表達(dá)式的平方根 4 字符串函數(shù) ASCII ( character_expression )--返回字符表達(dá)式最左端字符的 ASCII 代碼值。 CHAR ( integer_expression )--將 int ASCII 代碼轉(zhuǎn)換為字符的字符串函數(shù)。 LOWER ( character_expression )--將大寫字符數(shù)據(jù)轉(zhuǎn)換為小寫字符數(shù)據(jù)后返回字符表達(dá)式。 UPPER ( character_expression )--返回將小寫字符數(shù)據(jù)轉(zhuǎn)換為大寫的字符表達(dá)式。 STR ( float_expression [ , length [ , decimal ] ] ) --由數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換來的字符數(shù)據(jù)。 LTRIM( character_expression )--刪除起始空格后返回字符表達(dá)式。 RTRIM ( character_expression )--截?cái)嗨形搽S空格后返回一個(gè)字符串。 LEFT ( character_expression , integer_expression ) --返回從字符串左邊開始指定個(gè)數(shù)的字符。 RIGHT ( character_expression , integer_expression ) --返回字符串中從右邊開始指定個(gè)數(shù)的 integer_expression 字符。 SUBSTRING ( expression , start , length )--截取字符串 CHARINDEX ( expression1 , expression2 [ , start_location ] ) --返回字符串中指定表達(dá)式的起始位置，沒有返回0 PATINDEX ( ‘%pattern%‘ , expression ) -- 返回指定表達(dá)式中某模式第一次出現(xiàn)的起始位置；如果在全部有效的文本和字符數(shù)據(jù)類型中沒有找到該模式，則返回零。 REPLICATE ( character_expression , integer_expression )-- 以指定的次數(shù)重復(fù)字符表達(dá)式。 REVERSE ( character_expression )-- 返回字符表達(dá)式的反轉(zhuǎn)。 REPLACE ( ‘string_expression1‘ , ‘string_expression2‘ , ‘string_expression3‘ )--用第三個(gè)表達(dá)式替換第一個(gè)字符串表達(dá)式中出現(xiàn)的所有第二個(gè)給定字符串表達(dá)式。 STUFF ( character_expression , start , length , character_expression )--刪除指定長(zhǎng)度的字符并在指定的起始點(diǎn)插入另一組字符。 SPACE ( integer_expression )-- 返回由重復(fù)的空格組成的字符串。 5 日期函數(shù) DAY()--函數(shù)返回date_expression中的日期值 MONTH()--函數(shù)返回date_expression中的月份值 YEAR()--函數(shù)返回date_expression中的年份值 DATEADD( datepart , number, date )--函數(shù)返回指定日期date加上指定的額外日期間隔number產(chǎn)生的新日期 DATEDIFF( datepart , startdate , enddate )--函數(shù)返回兩個(gè)指定日期在datepart方面的不同之處 DATENAME(datepart , date )------函數(shù)以字符串的形式返回日期的指定部分 DATEPART( datepart , date )--函數(shù)以整數(shù)值的形式返回日期的指定部分 GETDATE()------函數(shù)以DATETIME的缺省格式返回系統(tǒng)當(dāng)前的日期和時(shí)間 6 系統(tǒng)函數(shù) APP_NAME()------函數(shù)返回當(dāng)前執(zhí)行的應(yīng)用程序的名稱 COALESCE()-----函數(shù)返回眾多表達(dá)式中第一個(gè)非NULL表達(dá)式的值 COL_LENGTH ( ‘table‘ , ‘column‘ ) ----函數(shù)返回表中指定字段的長(zhǎng)度值 COL_NAME ( table_id , column_id )----返回?cái)?shù)據(jù)庫列的名稱，該列具有相應(yīng)的表標(biāo)識(shí)號(hào)和列標(biāo)識(shí)號(hào)。 DATALENGTH()-----函數(shù)返回?cái)?shù)據(jù)表達(dá)式的數(shù)據(jù)的實(shí)際長(zhǎng)度 DB_ID ( [ ‘database_name‘ ] ) ------函數(shù)返回?cái)?shù)據(jù)庫的編號(hào) DB_NAME(database_id)------函數(shù)返回?cái)?shù)據(jù)庫的名稱 HOST_ID()-----函數(shù)返回服務(wù)器端計(jì)算機(jī)的名稱 HOST_NAME()-----函數(shù)返回服務(wù)器端計(jì)算機(jī)的名稱 IDENTITY ( data_type [ , seed , increment ] ) AS column_name --IDENTITY()函數(shù)只在SELECTINTO語句中使用用于插入一個(gè)identitycolumn列到新表中 ISDATE()----函數(shù)判斷所給定的表達(dá)式是否為合理日期 ISNULL ( check_expression , replacement_value ) --函數(shù)將表達(dá)式中的NULL值用指定值替換 ISNUMERIC()----函數(shù)判斷所給定的表達(dá)式是否為合理的數(shù)值 NEWID()----函數(shù)返回一個(gè)UNIQUEIDENTIFIER類型的數(shù)值 NULLIF ( expression , expression )--NULLIF函數(shù)在expression1與expression2相等時(shí)返回NULL值若不相等時(shí)則返回xpression1的值 [來源： http://stevieliu.blogchina.com/stevieliu/4720568.html] [參考《SQL Server聯(lián)機(jī)叢書》，略有修改] 二 SQL Server中各個(gè)系統(tǒng)表的作用 sysaltfiles 主數(shù)據(jù)庫 保存數(shù)據(jù)庫的文件 syscharsets 主數(shù)據(jù)庫 字符集與排序順序 sysconfigures 主數(shù)據(jù)庫 配置選項(xiàng) syscurconfigs 主數(shù)據(jù)庫 當(dāng)前配置選項(xiàng) sysdatabases 主數(shù)據(jù)庫 服務(wù)器中的數(shù)據(jù)庫 syslanguages 主數(shù)據(jù)庫 語言 syslogins 主數(shù)據(jù)庫 登陸賬號(hào)信息 sysoledbusers 主數(shù)據(jù)庫 鏈接服務(wù)器登陸信息 sysprocesses 主數(shù)據(jù)庫 進(jìn)程 sysremotelogins主數(shù)據(jù)庫 遠(yuǎn)程登錄賬號(hào) syscolumns 每個(gè)數(shù)據(jù)庫 列 sysconstrains 每個(gè)數(shù)據(jù)庫 限制 sysfilegroups 每個(gè)數(shù)據(jù)庫 文件組 sysfiles 每個(gè)數(shù)據(jù)庫 文件 sysforeignkeys 每個(gè)數(shù)據(jù)庫 外部關(guān)鍵字 sysindexs 每個(gè)數(shù)據(jù)庫 索引 sysmenbers 每個(gè)數(shù)據(jù)庫 角色成員 sysobjects 每個(gè)數(shù)據(jù)庫 所有數(shù)據(jù)庫對(duì)象 syspermissions 每個(gè)數(shù)據(jù)庫 權(quán)限 systypes 每個(gè)數(shù)據(jù)庫 用戶定義數(shù)據(jù)類型 sysusers 每個(gè)數(shù)據(jù)庫 用戶 三 Transact_SQL 1 語法 語 句 功 能 (1) 數(shù)據(jù)操作 SELECT 從數(shù)據(jù)庫表中檢索數(shù)據(jù)行和列 INSERT 向數(shù)據(jù)庫表添加新數(shù)據(jù)行 DELETE 從數(shù)據(jù)庫表中刪除數(shù)據(jù)行 UPDATE 更新數(shù)據(jù)庫表中的數(shù)據(jù) (2)數(shù)據(jù)定義 CREATE TABLE 創(chuàng)建一個(gè)數(shù)據(jù)庫表 DROP TABLE 從數(shù)據(jù)庫中刪除表 ALTER TABLE 修改數(shù)據(jù)庫表結(jié)構(gòu) CREATE VIEW 創(chuàng)建一個(gè)視圖 DROP VIEW 從數(shù)據(jù)庫中刪除視圖 CREATE INDEX 為數(shù)據(jù)庫表創(chuàng)建一個(gè)索引 DROP INDEX 從數(shù)據(jù)庫中刪除索引 CREATE PROCEDURE 創(chuàng)建一個(gè)存儲(chǔ)過程 DROP PROCEDURE 從數(shù)據(jù)庫中刪除存儲(chǔ)過程 CREATE TRIGGER 創(chuàng)建一個(gè)觸發(fā)器 DROP TRIGGER 從數(shù)據(jù)庫中刪除觸發(fā)器 CREATE SCHEMA 向數(shù)據(jù)庫添加一個(gè)新模式 DROP SCHEMA 從數(shù)據(jù)庫中刪除一個(gè)模式 CREATE DOMAIN 創(chuàng)建一個(gè)數(shù)據(jù)值域 ALTER DOMAIN 改變域定義 DROP DOMAIN 從數(shù)據(jù)庫中刪除一個(gè)域 (3)數(shù)據(jù)控制 GRANT 授予用戶訪問權(quán)限 DENY 拒絕用戶訪問 REVOKE 解除用戶訪問權(quán)限 (4)事務(wù)控制 COMMIT 結(jié)束當(dāng)前事務(wù) ROLLBACK 中止當(dāng)前事務(wù) SET TRANSACTION 定義當(dāng)前事務(wù)數(shù)據(jù)訪問特征 (5)程序化SQL DECLARE 為查詢?cè)O(shè)定游標(biāo) EXPLAN 為查詢描述數(shù)據(jù)訪問計(jì)劃 OPEN 檢索查詢結(jié)果打開一個(gè)游標(biāo) FETCH 檢索一行查詢結(jié)果 CLOSE 關(guān)閉游標(biāo) PREPARE 為動(dòng)態(tài)執(zhí)行準(zhǔn)備SQL 語句 EXECUTE 動(dòng)態(tài)地執(zhí)行SQL 語句 DESCRIBE 描述準(zhǔn)備好的查詢 (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 = 100*@x + @y print @c --打印變量c 的值 select @y = @y + 1 end select @x = @x + 1 select @y = 1 end (11) WAITFOR --舉例： --例 等待1 小時(shí)2 分零3 秒后才執(zhí)行SELECT 語句 waitfor delay ’01:02:03’ select * from employee --例 等到晚上11 點(diǎn)零8 分后才執(zhí)行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關(guān)鍵字的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列號(hào) stockname = (select stockname from stock_information where stockid = 4) --------- 子查詢 --------- 除非能確保內(nèi)層select只返回一個(gè)行的值， --------- 否則應(yīng)在外層where子句中用一個(gè)in限定符 select distinct column_name form table_name --------- distinct指定檢索獨(dú)有的列值，不重復(fù) 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合并查詢結(jié)果集，all-保留重復(fù)行 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 ----- 實(shí)現(xiàn)刪除列的方法（創(chuàng)建新表） alter table table_name drop constraint Stockname_default ---- 刪除Stockname的default約束 四 MS-SQL數(shù)據(jù)庫開發(fā)常用匯總 1 按姓氏筆畫排序 Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2 數(shù)據(jù)庫加密 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 查看硬盤分區(qū) 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 殺掉所有的事件探察器進(jìn)程 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到結(jié)尾記錄 Select Top N * From 表 Order by ID Desc 8 如何修改數(shù)據(jù)庫的名稱 sp_renamedb ‘old_name‘, ‘new_name‘ 9 獲取當(dāng)前數(shù)據(jù)庫中的所有用戶表 select Name from sysobjects where xtype=‘u‘ and status>=0 或者： select * from information_schema.tables 10 獲取某一個(gè)表的所有字段 select name from syscolumns where id=object_id(‘表名‘) 11 查看與某一個(gè)表相關(guān)的視圖、存儲(chǔ)過程、函數(shù) select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ‘%表名%‘ 12 查看當(dāng)前數(shù)據(jù)庫中所有存儲(chǔ)過程 select name as 存儲(chǔ)過程名稱 from sysobjects where xtype=‘P‘ 13 查詢用戶創(chuàng)建的所有數(shù)據(jù)庫 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=‘sa‘) 或者 select dbid, name AS DB_NAME from master..sysdatabases where sid ＜> 0x01 14 查詢某一個(gè)表的字段和數(shù)據(jù)類型 select column_name,data_type from information_schema.columns where table_name = ‘表名‘ 15 判斷一個(gè)表是否存在 if exists(select 1 from sysobjects where name=‘要判斷的表名‘ and xtype=‘U‘) print ‘在‘ else print ‘不在‘ 或者 if objectproperty(object_id(‘要判斷的表名‘),‘isusertable‘) is null print ‘無此表‘ else print ‘有此表‘ 16 在存儲(chǔ)過程中刪除表的列 http://blog.csdn.net/scucj/archive/2006/07/14/919525.aspx 17 創(chuàng)建一個(gè)表和兩個(gè)字段，并指定其中一個(gè)字段為自增的關(guān)鍵字 CREATE TABLE ‘+ @TABLENAME + ‘ (tableID BigInt identity(1,1) primary key,myUserID BigInt)‘ 本文參考： |
|
|
