常用的SQL和T-SQL語句(二))

--資料操作

　　 SELECT --從資料庫表中檢索資料行和列
　　　　　　INSERT --向資料庫表添加新資料行
　　　　　　DELETE --從資料庫表中刪除資料行
　　　　　　UPDATE --更新資料庫表中的資料

　　--資料定義

　　 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 --從資料庫中刪除一個域

　　--資料控制

　　 GRANT --授予使用者存取權限
　　　　　　DENY --拒絕使用者訪問
　　　　　　REVOKE --解除使用者存取權限

　　--事務控制

　　 COMMIT --結束當前事務
　　　　　　ROLLBACK --中止當前事務
　　　　　　SET TRANSACTION --定義當前交易資料訪問特徵

　　--程式化SQL

　　 DECLARE --為查詢設定遊標
　　　　　　EXPLAN --為查詢描述資料訪問計劃
　　　　　　OPEN --檢索查詢結果開啟一個遊標
　　　　　　FETCH --檢索一行查詢結果
　　　　　　CLOSE --關閉遊標
　　　　　　PREPARE --為動態執行準備SQL 陳述式
　　　　　　EXECUTE --動態地執行SQL 陳述式
　　　　　　DESCRIBE --描述準備好的查詢　　

　　---局部變數

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

　　---全域變數

　　---必須以@@開頭　　

　　--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'
　　　　　　--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
　　　　　　--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
　　　　　　--WAITFOR

　　--例 等待1 小時2 分零3 秒後才執行SELECT 語句

　　waitfor delay ’01:02:03’
　　　　　　select * from employee

　　　　--例 等到晚上11 點零8 分後才執行SELECT 語句

　　　　waitfor time ’23:08:00’

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　

　　 insert　

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

　　 update　　

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

　　 delete　　

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

　　 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約束

 

常用函數(function)　　

    轉換函式

    convert(資料類型,值,格式)

　　統計函數

　　AVG --求平均值
　　COUNT --統計數目
　　MAX --求最大值
　　MIN --求最小值
　　SUM --求和　

　　AVG

　　use pangu
　　select avg(e_wage) as dept_avgWage
　　from employee
　　group by dept_id　　

　　MAX

　　--求工資最高的員工姓名
　　use pangu
　　select e_name
　　from employee
　　where e_wage =
　　 (select max(e_wage)
　　 from employee)　　

　　STDEV()

　　--STDEV()函數返回運算式中所有資料的標準差
　　--STDEVP()
　　--STDEVP()函數返回總體標準差　　

　　VAR()

　　--VAR()函數返回運算式中所有值的統計變異數　　

　　VARP()

　　--VARP()函數返回總體變異數　　

　　算術函數　　

　　三角函數

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

　　反三角函數

　　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)
　　 ------把弧度轉換為角度返回與運算式相同的資料類型可為
　　 ------INTEGER/MONEY/REAL/FLOAT 類型
　　RADIANS(numeric_expression)
------把角度轉換為弧度返回與運算式相同的資料類型可為

　　 ------INTEGER/MONEY/REAL/FLOAT 類型
　　EXP(float_expression) --返回運算式的指數值
　　LOG(float_expression) --返回運算式的自然對數值
　　LOG10(float_expression)--返回運算式的以10 為底的對數值
　　SQRT(float_expression) --返回運算式的平方根

　　取近似值函數

　　CEILING(numeric_expression)
-------返回>=運算式的最小整數返回的資料類型與運算式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 類型
　　FLOOR(numeric_expression)
-------返回<=運算式的最小整數返回的資料類型與運算式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 類型
　　ROUND(numeric_expression)
-------返回以integer_expression 為精度的四捨五入值返回的資料
　　 -------類型與運算式相同可為INTEGER/MONEY/REAL/FLOAT 類型
　　ABS(numeric_expression)
-------返回運算式的絕對值返回的資料類型與運算式相同可為
　　 -------INTEGER/MONEY/REAL/FLOAT 類型
　　SIGN(numeric_expression)
-------測試參數的加號或減號返回0 零值1 正數或-1 負數返回的資料類型
　　 -------與運算式相同可為INTEGER/MONEY/REAL/FLOAT 類型
　　PI() -------傳回值為π 即3.1415926535897936
　　RAND([integer_expression])
-------用任選的[integer_expression]做種子值得出0-1 間的隨機浮點數

字串函數

　　ASCII() ------函數返回字元運算式最左端字元的ASCII 碼值
　　CHAR() ------函數用於將ASCII 碼轉換為字元
　　 ------如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數會返回一個NULL 值
　　LOWER() ------函數把字串全部轉換為小寫
　　UPPER() ------函數把字串全部轉換為大寫
　　STR() ------函數把數值型資料轉換為字元型資料
　　LTRIM() ------函數把字串頭部的空格去掉
　　RTRIM() ------函數把字串尾部的空格去掉
　　LEFT(),RIGHT(),SUBSTRING() --函數返回部分字串
　　CHARINDEX(),PATINDEX() --函數返回字串中某個指定的子串出現的開始位置
　　SOUNDEX() ------函數返回一個四位字元碼
　　 ------SOUNDEX函數可用來尋找聲音相似的字串但SOUNDEX函數對數字和漢字均只返回0 值
　　DIFFERENCE() ------函數返回由SOUNDEX 函數返回的兩個字元運算式的值的差異
　　 ------0 兩個SOUNDEX 函數傳回值的第一個字元不同
　　 ------1 兩個SOUNDEX 函數傳回值的第一個字元相同
　　 ------2 兩個SOUNDEX 函數傳回值的第一二個字元相同
　　 ------3 兩個SOUNDEX 函數傳回值的第一二三個字元相同
　　 ------4 兩個SOUNDEX 函數傳回值完全相同同
　　QUOTENAME() ------函數返回被特定字元括起來的字串

　　/**//**//**//*select quotename('abc', '{') quotename('abc')
　　運行結果如下

　　{
　　{abc} [abc]*/
　　REPLICATE() ------函數返回一個重複character_expression 指定次數的字串
　　/**//**//**//*select replicate('abc', 3) replicate( 'abc', -2)

　　運行結果如下

　　abcabcabc NULL*/
　　REVERSE() ------函數將指定的字串的字元排列順序顛倒
　　REPLACE() ------函數返回被替換了指定子串的字串
　　/**//**//**//*select replace('abc123g', '123', 'def')

　　運行結果如下
　　
　　abcdefg*/　　

　　SPACE() ------函數返回一個有指定長度的空白字串
　　STUFF() ------函數用另一子串替換字串指定位置長度的子串　　

　　資料類型轉換函式

　　CAST() 函數文法如下
　　CAST() ( AS [ length ])
　　CONVERT() 函數文法如下
　　CONVERT() ([ length ], [, style])
　　select cast(100+99 as char) convert(varchar(12), getdate())

　　運行結果如下
　　199 Jan 15 2000　

　　日期函數

　　DAY() ------函數返回date_expression 中的日期值
　　MONTH() ------函數返回date_expression 中的月份值
　　YEAR() ------函數返回date_expression 中的年份值
　　DATEADD( , ,)
　　 -----函數返回指定日期date 加上指定的額外日期間隔number 產生的新日期
　　DATEDIFF( , ,)
　　 -----函數返回兩個指定日期在datepart 方面的不同之處

　　DATENAME( ,  ------函數以字串的形式返回日期的指定部分
　　DATEPART( ,  ------函數以整數值的形式返回日期的指定部分
　　GETDATE() ------函數以DATETIME 的預設格式返回系統當前的日期和時間　　

　　系統函數

　　APP_NAME() ------函數返回當前執行的應用程式的名稱
　　COALESCE() -----函數返回眾多運算式中第一個非NULL 運算式的值
　　COL_LENGTH(<'table_name'>, <'column_name'> ----函數返回表中指定欄位的長度值
　　COL_NAME(,  ----函數返回表中指定欄位的名稱即列名
　　DATALENGTH() -----函數返回日期運算式的資料的實際長度
　　DB_ID(['database_name']) ------函數返回資料庫的編號
　　DB_NAME(database_id) ------函數返回資料庫的名稱
　　HOST_ID() -----函數返回伺服器端電腦的名稱
　　HOST_NAME() -----函數返回伺服器端電腦的名稱
　　IDENTITY([, seed increment]) [AS column_name])
　　 --IDENTITY() 函數只在SELECT INTO 語句中使用用於插入一個identity column列到新表中
　　/**//**//**//*select identity(int, 1, 1) as column_name
　　 into newtable
　　 from oldtable*/

　　ISDATE() ----函數判斷所給定的運算式是否為合理日期
　　ISNULL(,  --函數將運算式中的NULL 值用指定值替換
　　ISNUMERIC() ----函數判斷所給定的運算式是否為合理的數值
　　NEWID() ----函數返回一個UNIQUEIDENTIFIER 類型的數值
　　NULLIF(, 
　　 ----NULLIF 函數在expression1 與expression2 相等時返回NULL 值若不相等時則返回xpression1 的值