/*********************************************************/
function:SQL MSSQL TECHNOLOGY ARTICLE
file :SQL-MSSQL.TXT
author :chinayaosir QQ:44633197
Tools :MSSQL QUERY ANALYSIS
date :4/01/2010
blog :http://blog.csdn.net/chinayaosir
note :禁止其它網(wǎng)站轉(zhuǎn)載此文章
/*********************************************************/
目錄清單CONTEXT LIST
/*********************************************************/
1.數(shù)據(jù)庫DataBase
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.2數(shù)據(jù)庫備份與恢復(fù)backup/restore database
/*********************************************************/
2.數(shù)據(jù)查詢DATA QUERY LANGUAGE
2.1選擇查詢Select Query
2.2聚集查詢Aggregate Query
2.3子查詢 Sub Query
2.4連接查詢Table Joins
2.5匯總查詢Group Query
/*********************************************************/
3.數(shù)據(jù)修改DATA MODIFY LANGUAGE
3.1插入數(shù)據(jù)Insert
3.2修改數(shù)據(jù)Update
3.3刪除數(shù)據(jù)Delete
/*********************************************************/
4.數(shù)據(jù)定義DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Identity
4.4約束Constraints
4.5索引Index
4.6視圖view
4.7權(quán)限Privilege
/*********************************************************/
5.數(shù)據(jù)庫函數(shù)Functions
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.2聚集函數(shù)Aggregate Functions
5.3字符函數(shù)char Functions
5.4日期函數(shù)Date Functions
5.5數(shù)學(xué)函數(shù)Math Functions
5.6分析函數(shù)Analytical Functions
/*********************************************************/
6.數(shù)據(jù)庫腳本Script
6.1數(shù)據(jù)類型Data Types
6.2腳本語法Statements
6.3腳本游標(biāo)Cursor
6.4存儲過程Procedure
6.5存儲函數(shù)Function
6.6觸發(fā)器Trigger
6.7事務(wù)Transaction
6.8其它Other
/*********************************************************/

SQL明細(xì) SQL DETAIL
/**********************************************************/
1.數(shù)據(jù)庫DataBase
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.2備份與恢復(fù)backup/restore database
/**********************************************************/
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.1.1.建立數(shù)據(jù)庫
語法:create database <數(shù)據(jù)庫名> ［其它參數(shù)］
代碼:
//建立數(shù)據(jù)庫 hr
create database hr

1.1.2.刪除數(shù)據(jù)庫。
語法:drop database <數(shù)據(jù)庫名>
代碼:
//刪除數(shù)據(jù)庫hr
drop database hr
//如果存在hr數(shù)據(jù)庫,則刪除數(shù)據(jù)庫hr
IF DB_ID('hr') IS NOT NULL
DROP DATABASE TestDB
-----------------------------------------------------------
1.2備份與恢復(fù)backup/restore database
1.2.1.添加備份設(shè)備
語法:sp_addumpdevice <keyword> <devicename> <devicepath>

代碼:
//添加備份設(shè)備為本地硬盤
sp_addumpdevice 'disk', 'localbackup', 'e:\database\backup\localbak.bak'
//備份到網(wǎng)絡(luò)硬盤
sp_addumpdevice 'disk', 'netbackup', '\\computer1\database\backup\netbak.bak'
//備份到磁帶
sp_addumpdevice 'tape', 'tapebackup', '\\.\tape1bak'
//備份到命名管道
sp_addumpdevice 'pipe', 'pipebackup', 'e:\database\backup\pipebak'

1.2.2.備份數(shù)據(jù)庫
語法:backup database <databasename> to <devicename>| disk=<backupnamepath>

代碼:
//備份數(shù)據(jù)庫到備份設(shè)備
backup database pubs to localbackup
//備份數(shù)據(jù)庫到指定路徑下面的指定文件
backup database pubs to disk='e:\database\backup\pubsbak.bak'

1.2.3.恢復(fù)數(shù)據(jù)庫
語法:restore database <databasename> from <devicename>| disk=<backupnamepath>
代碼:
//從備份設(shè)備中恢復(fù)數(shù)據(jù)庫
restore database pubs from localbackup
//從備份文件中恢復(fù)數(shù)據(jù)庫

/**********************************************************/
2.數(shù)據(jù)查詢DATA QUERY LANGUAGE
2.1選擇查詢Select Query
2.2子查詢 Sub Query
2.3連接查詢Table Joins
2.4匯總查詢Group Query
-----------------------------------------------------------
2.1選擇查詢Select Query
語法:
select [top n][/all]/[distinct] [*] / [columnlist...] [<columnlist as alias...] [const/sql/function expression]
from (<tablelist,>...) [as alias]
[where search expression...]
[group by groupnamelist ....]
[having search-expression...]
[order by sort-expression...]

//select選項說明:
top n:只顯示第一條到n條記錄
//重復(fù)與不重復(fù)記錄
all:表示包含重復(fù)的記錄
distinct:表示去掉重復(fù)的記錄
//所有字段與選中字段和字段別名
*:表示所有的列名
columnlist:表示字段列表
columnlist as alias:表示字段的別名

//其它字段
const-expression:常量表達式(如數(shù)字/字符串/日期/時間常量)
sql-expression:常見的sql語句的加減乘除表達式運算字段
function expression:數(shù)據(jù)庫函數(shù)和自定義函數(shù)字段

//測試條件
比較測試條件(=,<>,>,<,>=,<=)
范圍測試條件(betweeen 下限值 and 上限值)
成員測試條件(in,not in)
存在測試條件(exists,not exists)
匹配測試條件(like)
限定測試條件(any,all)
空值測試條件(is null)

//復(fù)合搜索條件(and, or,not,())
and:邏輯與運算
and:邏輯或運算
not:邏輯非運算
():可改變優(yōu)先級的運算符

//子句說明
select子句:指出檢索的數(shù)據(jù)項
from 子句:指出檢索的數(shù)據(jù)表
where 子句:指出檢索的數(shù)據(jù)條件
group by子句:指出檢索的數(shù)據(jù)進行匯總
having子句:指出檢索的數(shù)據(jù)進行匯總之前的條件
order by子句:指出檢索的數(shù)據(jù)條件進行排序
代碼:
//所有字段方式顯示orders全部記錄
select * from orders
//按字段顯示全部記錄
select order_num,order_date,amount from orders
//按字段顯示全部記錄,但除掉重復(fù)的記錄
select order_num,order_date,amount from orders
//用sql-expression乘運算計算列
select amount,amount*0.08 as discount_amt from orders
//用自定義函數(shù)計算指定列
select order_num,order_date,amount,f_amt_to_chn(amount) as 金額 from orders

select選項太多，代碼例子就省略...
-----------------------------------------------------------
2.2子查詢 Sub Query
語法:select ...
from <tablename>
where / having column 測試條件 (Sub Query)
//測試條件
比較測試條件(=,<>,>,<,>=,<=)
范圍測試條件(betweeen 下限值 and 上限值)
成員測試條件(in,not in)
存在測試條件(exists,not exists)
匹配測試條件(like)
限定測試條件(any,all)
空值測試條件(is null)

代碼:
//列出沒有完成銷售目標(biāo)10%的銷售人員清單[<測試]
select name from salesreps where quota < (0.1 * select sum(target) from offices))
//列出公司的銷售目標(biāo)超過各個銷售人員定額總和的銷售點[>測試]
select city from offices where target > (select sum(quota) from salesreps where rep_office=office)
//列出超過銷售目標(biāo)的銷售點的業(yè)務(wù)人員[in測試]
select name from salesreps where office in (select office from offies where sales > target)
//列出訂單大于2500元的產(chǎn)品名稱[exists測試]
select description from products where exists (
select * from orders where product=prodct_id and amount > 2500.00
)
//列出完成銷售目標(biāo)10%的銷售人員清單[any測試]
select name from salesreps where (0.1* quota) < any(select amount from orders where rep=empl_num)

-----------------------------------------------------------
2.3連接查詢Table Joins
多表連接類型可分為三類(內(nèi)/外/交叉連接)
主從表或者父子表進行多表連接多以主鍵和外鍵進行關(guān)聯(lián)
Outer joins(LEFT OUTER, RIGHT OUTER, and FULL OUTER joins)
left outer join:查詢的結(jié)果以左邊表行數(shù)為準(zhǔn)
right outer join:查詢的結(jié)果以右邊表行數(shù)為準(zhǔn)

2.3.1.內(nèi)連接inner join
功能:
語法:
SELECT select_list
FROM table_1
[INNER] JOIN table_2
ON join_condition_1
[[INNER] JOIN table_3
ON join_condition_2]...
代碼:
//沒有where子句的內(nèi)連接
SELECT *
FROM Products
INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID

//有where子句的內(nèi)連接
SELECT p.ProductID, s.SupplierID, p.ProductName, s.CompanyName
FROM Products p
INNER JOIN Suppliers s
ON p.SupplierID = s.SupplierID
WHERE p.ProductID < 4

-----------------------------------------------------------
2.3.2.外連接outer join
功能:包括三種連接LEFT OUTER, RIGHT OUTER, and FULL OUTER joins
left outer :查詢的結(jié)果以左邊表行數(shù)為準(zhǔn)
right outer :查詢的結(jié)果以右邊表行數(shù)為準(zhǔn)
語法:select ... from table1 [left/right/full outer join ]table2 where ...
代碼:
//以Customers表行數(shù)為標(biāo)準(zhǔn)去連接Orders表
SELECT c.CustomerID, CompanyName
FROM Customers c
LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL

-----------------------------------------------------------
2.3.3.交叉連接cross join
功能:以主從表或者父子表之間的主鍵進行連接，最終以笛卡爾乘積運算的結(jié)果
語法:select ... from table1 cross join table2 where ...

代碼:
//顯示結(jié)果以表1行數(shù)*表2行數(shù)
假設(shè)Departments為4行記錄
假設(shè)Jobs為3行記錄
下面的顯示結(jié)果為4*3=12行記錄
SELECT deptname,jobdesc FROM Departments CROSS JOIN Jobs
//用關(guān)鍵字匹配的交叉連接
oc_head/oc_detail是主從表
oc_head(主鍵oc_number)
oc_detail(主鍵oc_number,item_number,ship_date)

SELECT h.customerid,d.item_number,d.ship_date
from oc_head as h CROSS JOIN oc_detail as d
where h.oc_number=d.oc_number
-----------------------------------------------------------
2.4匯總查詢Group Query
//匯總查詢相當(dāng)于會計報表中的小計匯總的功能

語法: select ...
from <tablename>
group by <column-name >
[having search expression]

代碼:
//求出每名銷售人員的銷售金額
select rep,sum(amount) from orders group by rep
//每個銷售點分配了多少銷售人員
select rep_office,count(*) from salesreps group by rep_office
//計算每名銷售人員的每個客戶和訂單金額
select cust,rep,sum(amount) from orders group by cust,rep
//Having子句應(yīng)用
select rep,avg(amount) from orders having sum(quota) > 3000.00

/**********************************************************/
3.數(shù)據(jù)修改DATA MODIFY LANGUAGE
3.1插入數(shù)據(jù)Insert
3.2修改數(shù)據(jù)Update
3.3刪除數(shù)據(jù)Delete
-----------------------------------------------------------
3.1插入數(shù)據(jù)Insert
3.1.1.單行插入
語法:insert into <tablename>[<columnlist,>...] values(<valuelist,>...);

代碼:
//不省略字段清單
insert into salesreps(name,age,empl_no,sales,title,hire_date,rep_office)
values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13)
//省略字段清單
insert into salesreps
values('jack toms',36,111,0.00,'sales mgr','10-05-2010',13)
3.1.2.多行插入
語法:insert into <tablename>[(<columnlist,>...)] values(<valuelist,>...) <select Query>;

代碼:
//把一批數(shù)據(jù)批量插入到一個備份表中
insert into history_order(order_num,order_date,amount)
select order_num,order_date,amount
from orders where order_date < '01/01/2000'
-----------------------------------------------------------
3.2修改數(shù)據(jù)Update
語法:update <tablename> set (cloumn=expression...) [where ...] [SubQuery..]

代碼:
//更新所有記錄
update salesreps set quota=1.05 * quota
//按條件更新表記錄
update salesreps set quota=1.08 * quota where area='china'
//按子查詢更新表記錄
update customers set cust_rep=105
where cust_rep in (
selct empl_num from salesreps where sales < (0.8 * quota)
)

-----------------------------------------------------------
3.3刪除數(shù)據(jù)Delete
語法1:delete from <tablename> [where ...]
代碼:
//所有刪除記錄
delete from orders

語法2:truncate table <tablename>
代碼2:
//所有刪除記錄
truncate table orders

//按條件刪除記錄
delete from orders where order_date < '01/01/2000'

/**********************************************************/
4.數(shù)據(jù)定義DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Indentity
4.4約束Constraints
4.5索引Index
4.6視圖view
4.7權(quán)限Privilege
/**********************************************************/
4.1表Table
4.1.1.建立表
語法:
create table <表名>(
<列名> <數(shù)據(jù)類型> [長度] <,>
<列名...>
)

代碼:
//建立公司部門表
create table tb_basic_dept(
id int not null,
name varchar(20) ,
chair varchar(20)
)

4.1.2.刪除表
語法:
drop table <表名>
代碼:
//刪除部門表
drop table tb_basic_dept
-----------------------------------------------------------
4.2列Column
4.2.1.列添加
語法:
alter table <表名> add
<列名> <數(shù)據(jù)類型> [長度] <,>
<列名...>

代碼:
alter table tb_basic_dept add
remark varchar(50)
4.2.2.列刪除
語法:alter table <表名> drop column <列名>
代碼:
alter table tb_basic_dept drop column remark

4.2.3.列修改
語法:alter table <表名> alter column
<列名> <數(shù)據(jù)類型> [長度] [null | not null]

代碼:
//修改工資列為dec(8,2)
alter table tb_hr_gz alter column gz dec(8,2) null
-----------------------------------------------------------
4.3序列Identity
//特別要求
IDENTITY字段數(shù)據(jù)類型只能是(int, bigint, smallint, tinyint, decimal, or numeric(x,0))
IDENTITY字段必須是not null約束

4.3.1Identity
語法:
IDENTITY(<data_type> [, <seed>, <increment>]) AS column_name,

代碼:
//使用Identity
CREATE TABLE MyTable (
key_col int NOT NULL IDENTITY (1,1),
abc char(1) NOT NULL
)
INSERT INTO MyTable VALUES ('a')
INSERT INTO MyTable VALUES ('b')
INSERT INTO MyTable VALUES ('c')

-----------------------------------------------------------
4.4約束Constraints
4.4.1缺省約束(default)
4.4.2非空約束(not null)
4.4.3規(guī)則約束(rule)
4.4.4檢查約束(check)
4.4.5唯一約束(unique)
4.4.6主鍵約束(primary key)
4.4.7外鍵約束(foreign key)
4.4.8商業(yè)規(guī)則(business rule)

以下面兩個表為例進行演示
create table tb_hr_bm(
bm varchar(20) not null ,
remark varchar(100) default ''
)
create table tb_hr_gz(
id int not null,
name varchar(30) not null,
hrid char(18) null,
workage int null ,
bm varchar(20) null,
gz real null,
remark varchar(100) null
)
hrid=身份證號碼
workage=工作年數(shù)
gz=工資金額
-----------------------------------------------------------
4.4.1缺省約束(default)
語法:CREATE DEFAULT default_name AS expression
代碼:CREATE DEFAULT zip_default AS 94710
-----------------------------------------------------------
4.4.2非空約束(not null)
//表的主鍵和其它必填字段必須為not null.
語法:create table (column-name datatype not null... )
代碼:create table tb_hr_gz(id int not null,...)
-----------------------------------------------------------
4.4.3規(guī)則約束(rule)
語法:CREATE RULE rulename AS condition
代碼:
//郵編號碼6位100000-999999
//建立一個自定義zip類型
CREATE TYPE zip FROM CHAR(6) NOT NULL
//建立一個規(guī)則約束
CREATE RULE zip_rule AS @number >100000 and @number < 999999
//綁定規(guī)則約束到zip類型
EXEC sp_bindrule zip_rule, 'zip'
//應(yīng)用自定義zip類型
2> CREATE TABLE address(
city CHAR(25) NOT NULL,
zip_code ZIP,
street CHAR(30) NULL
)

-----------------------------------------------------------

4.4.4檢查約束(建立/刪除)
//檢查約束建立
語法:
alter table name
add constraint <檢查約束名> check<取值范圍表達式>

代碼:
//工資添加取值范圍0 ~ 1000000
方法1:
create table tb_hr_gz(
gz real default 0.0 check(gz >=0 and gz <=1000000),
...
)
方法2:
alter table tb_hr_gz
add constraint tb_hr_gz_ck check(gz >=0 and gz <=1000000)

//檢查約束刪除
語法:
alter table name drop constraint <檢查約束名>
代碼:
//刪除工資的檢查約束
alter table tb_hr_gz drop constraint tb_hr_gz_ck
-----------------------------------------------------------
4.4.5唯一約束
4.4.5.1.唯一約束添加
語法:
alter table name add constraint <唯一約束名> unique<列名>
代碼:
//列如身份證號碼是唯一的!
alter table tb_hr_gz Add constraint tb_hr_gz_uk unique(hrid)

4.4.5.2.唯一約束刪除
語法:
alter table name drop constraint <唯一約束名>
代碼:
alter table tb_hr_gz drop constraint tb_hr_gz_uk

-----------------------------------------------------------
4.4.6主鍵約束
4.4.6.1主鍵約束添加
語法:
alter table table_name
add constraint <主鍵名稱> Primary Key <列名>
代碼:
create table tb_hr_bm(
bm varchar(20) not null ,
remark varchar(100) default ''
)
alter table tb_hr_bm
add constraint tb_hr_bm_pk Primary Key (bm)

4.4.6.2主鍵約束刪除
語法:
alter table table_name
drop constraint <主鍵名稱>
代碼:
alter table table_name
drop constraint tb_hr_bm_pk

-----------------------------------------------------------
4.4.7外鍵約束
4.4.7.1外鍵約束添加
語法:
alter table <表名>
add constraint <外鍵名>
foreign key(列名)
references <參考表名><列名>
<ON UPDATE|ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT)>

//補充說明
常用選項是下面3項:
ON UPDATE SET NULL //級聯(lián)更新
ON DELETE CASCADE //級聯(lián)刪除
ON DELETE SET NULL //級聯(lián)置空

ON UPDATE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表更新后,子表的行為
ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表刪除后,子表的行為
RESTRICT 限制功能:父表一行記錄不能更新/刪除，當(dāng)子表有一條記錄以上時
CASCADE 級聯(lián)功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動更新/刪除
SET NULL 置空功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動為空
SET DEFAULT 默認(rèn)值功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動寫入默認(rèn)值


代碼:
建立外鍵的主要代碼
alter table tb_hr_personl_info
add constraint tb_hr_personl_info__bm_fk
foreign key(bm)
references tb_hr_bm (bm)
on update cascade
on delete cascade


//建立參考表部門
create table tb_hr_bm
(
bm varchar(20) not null ,
remark varchar(100) default ''
)
alter table tb_hr_bm
add constraint tb_hr_bm_pk Primary Key (bm)
//建立個人信息表
use hr
create table tb_hr_personl_info
(
userid int not null ,
username varchar(20) null,
bm varchar(20) null
)
/*為此表添加主鍵約束*/
alter table tb_hr_personl_info
add constraint tb_hr_personl_info_pk Primary Key (userid)
/*為個人信息表添加外鍵約束*/
alter table tb_hr_personl_info
add constraint tb_hr_personl_info__bm_fk
foreign key(bm)
references tb_hr_bm (bm)
on update cascade
on delete cascade
-----------------------------------------------------------

4.4.7.2外鍵約束刪除
語法:
alter table <表名>
drop constraint <外鍵名>
代碼:
//刪除tb_hr_personl_info表的外鍵
alter table tb_hr_personl_info drop constraint tb_hr_personl_info__bm_fk;
-----------------------------------------------------------
4.4.8商業(yè)規(guī)則(business rule)
//用觸發(fā)器或者存儲過程來實現(xiàn)

-----------------------------------------------------------
4.5索引Index
//4.5.1建立索引
語法:
create index <索引名>
on <表名> <列名清表>
代碼:
create index tb_hr_personl_info_ix
on tb_hr_personl_info (userid)

//4.5.2刪除索引
語法:
drop index <表名><.><索引名>
代碼:
//刪除索引名tb_hr_personl_info_ix
drop index tb_hr_personl_info.tb_hr_personl_info_ix
-----------------------------------------------------------
4.6視圖view
4.6.1視圖view的概念：
視圖不是表，也不是表數(shù)據(jù)的備份，在數(shù)據(jù)庫模式中只是select語句的集合!

-----------------------------------------------------------
4.6.2建立視圖Create View
語法:
CREATE VIEW <view name>
AS
<SELECT statement>
WITH CHECK OPTION

代碼:
CREATE VIEW vw_customerlist
AS
SELECT *
FROM Customers
-----------------------------------------------------------
4.6.3查詢視圖Query view
語法:select * from viewname
代碼:select * from vw_customerlist
-----------------------------------------------------------
4.6.4修改視圖ALTER VIEW
語法:select * from viewname
代碼:select * from vw_customerlist
-----------------------------------------------------------
4.6.5視圖刪除DROP VIEW
//4.6.2視圖刪除
語法:
drop view <視圖名>
代碼:
//視圖刪除v_hr_personl_info
drop view v_hr_personl_info

-----------------------------------------------------------
4.6.6.過濾視圖Filter view
語法:
select * from viewname where/having expressions
代碼:
CREATE VIEW BankersMin
AS
SELECT BankerName, BankerState
FROM Bankers
where BankerID < 5

SELECT * FROM BankersMin
WHERE BankerState = 'CA'
ORDER BY BankerName

-----------------------------------------------------------
4.6.7.可更新的視圖Updatable View
語法:
CREATE VIEW <view name>
AS
SELECT statement
WITH CHECK OPTION
代碼:
CREATE VIEW OregonShippers_vw
AS
SELECT ShipperID,
CompanyName,
Phone
FROM Shippers
WITH CHECK OPTION

//此視圖的記錄可以進行delete/update/insert
insert into <view name> values(values....)
delete from <view name> where/having expressions
update <view name> set column =values... where/having expressions

-----------------------------------------------------------
4.7權(quán)限Privilege
4.7.1數(shù)據(jù)庫用戶添加
語法:
sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt= ] 'encryption_option' ]

代碼:
數(shù)據(jù)庫testdb上面添加一個登陸用戶test,密碼為tt
EXEC sp_addlogin 'test', 'tt', 'testdb', 'us_english'
EXEC sp_addlogin 'yao', 'it', 'mtyjxc', 'us_english'
-----------------------------------------------------------
4.7.2數(shù)據(jù)庫用戶刪除
語法:DROP LOGIN <登陸名稱>
代碼:DROP LOGIN test

-----------------------------------------------------------
4.7.3用戶權(quán)限授予grant
grant語法:
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege取值范圍如下:
SELECT:訪問聲明的表/視圖的所有列/字段．
INSERT:向聲明的表中插入所有列字段．
UPDATE:更新聲明的表所有列/字段．
DELETE:從聲明的表中刪除所有行．
RULE:在表/視圖上定義規(guī)則 （參見 CREATE RULE 語句）．
ALL:賦予所有權(quán)限．

object取值范圍如下:
table
view
sequence

PUBLIC:代表是所有用戶的簡寫.
GROUP:將要賦予權(quán)限的組 group
username:將要賦予權(quán)限的用戶名．
如果成功，返回輸出CHANGE信息．
代碼:
GRANT all on mtyjxc to 'yao'

-----------------------------------------------------------
7.7.4用戶權(quán)限解除REVOKE
REVOKE { ALL | statement [ ,...n ] }
FROM security_account [ ,...n ]
ALL:
指定將刪除所有適用的權(quán)限。
對于語句權(quán)限，只有 sysadmin 固定服務(wù)器角色成員可以使用 ALL。
對于對象權(quán)限，sysadmin 固定服務(wù)器角色成員、db_owne 固定數(shù)據(jù)庫角色成員和數(shù)據(jù)庫對象所有者都可以使用 ALL。
statement:
是要刪除其權(quán)限的授權(quán)語句。語句列表可以包括:
* CREATE DATABASE
* CREATE DEFAULT
* CREATE FUNCTION
* CREATE PROCEDURE
* CREATE RULE
* CREATE TABLE
* CREATE VIEW
* BACKUP DATABASE
* BACKUP LOG

FROM:
指定安全帳戶列表。
security_account:
是當(dāng)前數(shù)據(jù)庫內(nèi)將要被刪除權(quán)限的安全帳戶。
安全帳戶可以是:SQL Server用戶,SQL Server角色。
代碼:
REVOKE all ON mtyjxc.* TO yao
REVOKE all ON mtyjxc TO yao

/**********************************************************/
5.數(shù)據(jù)庫函數(shù)Functions
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.2聚集函數(shù)Aggregate Functions
5.3字符函數(shù)char Functions
5.4日期函數(shù)Date Functions
5.5數(shù)學(xué)函數(shù)Math Functions
5.6分析函數(shù)Analytical Functions
-----------------------------------------------------------
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.1.1 CAST()
功能:數(shù)據(jù)類型轉(zhuǎn)換
語法:CAST(expression AS data_type)
代碼:
SELECT BillingDate,
BillingTotal,
CAST(BillingDate AS varchar) AS varcharDate,
CAST(BillingTotal AS int) AS integerTotal,
CAST(BillingTotal AS varchar) AS varcharTotal
FROM Billings
-----------------------------------------------------------
5.1.2 COALESCE()
功能:返回表達式列表中第一個非空值表達式的值
語法:COALESCE(expression1, expression2, ... expressionN)
代碼:
SELECT BankerName,
COALESCE(CAST(BillingTotal AS varchar), 'No Billings') AS BillingTotal
FROM Bankers LEFT JOIN Billings
ON Bankers.BankerID = Billings.BankerID
ORDER BY BankerName

-----------------------------------------------------------
5.1.3 CONVERT()
功能:把表達式值轉(zhuǎn)換為指定sytle的數(shù)據(jù)類型
語法:CONVERT(data_ type(<length>), expression, <style>)
代碼:
//日期風(fēng)格轉(zhuǎn)換
datetime轉(zhuǎn)指定日期格式style number清單
Number Style Number Output Type Style
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yyyy
2 102 ANSI yyyy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yyyy
5 105 Italian dd-mm-yyyy
6 106 - dd mon yyyy
7 107 - mon dd, yyyy
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
14 114 - hh:mi:ss:mmm (24h)

//字符串轉(zhuǎn)數(shù)字
CONVERT (INTEGER , '12345')
//字符轉(zhuǎn)日期
CONVERT(datetime, '20000704')

CREATE TABLE my_date (Col1 datetime)
GO
INSERT INTO my_date VALUES (CONVERT(char(10), GETDATE(), 112))
GO
drop table my_date;
GO

-----------------------------------------------------------
5.1.4 ISNULL()
功能:檢查check_expression是空值，就用replacement_value替代
語法:ISNULL(check_expression, replacement_value)

代碼:
SELECT BillingDate,
ISNULL(BillingDate, '1900-01-01') AS NewDate
FROM Billings

-----------------------------------------------------------
5.1.5 NULLIF()
功能:兩個表達式相等，返回null,否則返回第1個表達式
語法:ISNULL(expression1, expression2)

代碼:
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 955
SELECT NULLIF(@Value1, @Value2)
GO
輸出
55
DECLARE @Value1 int
DECLARE @Value2 int
SET @Value1 = 55
SET @Value2 = 55
SELECT NULLIF(@Value1, @Value2)
GO
輸出
NULL

-----------------------------------------------------------
5.2聚集函數(shù)Aggregate Functions
語法:select AggregateFunctions(column-name)
sum(column-name):計算字段總和
avg(column-name):計算字段平均值
min(column-name):計算字段最小值
max(column-name):計算字段最大值
count(column-name):計算字段非空值的個數(shù)
count(*):計算查詢結(jié)果的記錄個數(shù)

代碼:
//use pubs
select sum(qty) as sum_qty,
avg(qty) as avg_qty,
min(qty) as min_qty,
max(qty) as max_qty,
count(qty) as count_qty,
count(*) as total_qty
from sales

-----------------------------------------------------------
5.3字符函數(shù)char Functions
1. ASCII()
//函數(shù)返回字符表達式最左端字符的ASCII 碼值
2. Char()
//函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符--如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數(shù)會返回一個NULL
3. CHARINDEX()
//函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置
4. DIFFERENCE()
5. FORMATMESSAGE()
6. LEFT()
7. LEN()
8. LOWER()
//函數(shù)把字符串全部轉(zhuǎn)換為小寫
9. LTRIM()
//函數(shù)把字符串頭部的空格去掉
10.nchar()
11.PATINDEX()
12.QUOTENAME()
13.REPLACE()
//函數(shù)返回被替換了指定子串的字符串
14.REPLICATE()
/函數(shù)返回一個重復(fù)指定次數(shù)的字符串
15.REVERSE()
//函數(shù)將指定的字符串的字符排列順序顛倒
16.Right()
17.RTRIM()
/函數(shù)把字符串尾部的空格去掉
18.SOUNDEX()
19.SPACE()
//函數(shù)返回一個有指定長度的空白字符串
20.STR()
//函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)
21.STUFF()
//函數(shù)用另一子串替換字符串指定位置長度的子串
22.SUBSTRING()
//函數(shù)返回子字符串
23.UNICODE()
24.UPPER()
//函數(shù)把字符串全部轉(zhuǎn)換為大寫

-----------------------------------------------------------

5.4日期函數(shù)Date Functions
5.4.1. CURRENT_TIMESTAMP
功能:
得到當(dāng)前數(shù)據(jù)庫的日期
代碼:
//直接得到當(dāng)前日期
SELECT CURRENT_TIMESTAMP
go

//調(diào)用變量中的當(dāng)前日期
DECLARE @today datetime
SELECT @today = current_timestamp
select @today
go
-----------------------------------------------------------
5.4.2. 日期計算Date calculation
功能:日期計算
代碼:
DECLARE @MonthChar VarChar(2), @DayChar VarChar(2), @DateOut Char(8)
SET @MonthChar = CAST(MONTH(GETDATE()) AS VarChar(2))
SET @DayChar = CAST(DAY(GETDATE()) AS VarChar(2))
--自動補齊月份到2位
IF LEN(@MonthChar) = 1
SET @MonthChar = '0'+@MonthChar
IF LEN(@DayChar) = 1
SET @DayChar = '0' + @DayChar
--生成日期字符串
SET @DateOut = @MonthChar + @DayChar + CAST(YEAR(GETDATE()) AS Char(4))
SELECT @DateOut
GO
運行結(jié)果是mmddyyyy格式的字符串
-----------------------------------------------------------
5.4.3. DATEADD()
功能:日期相加或者相減n天后的日期
語法:DATEADD(what_to_add,number_to_add,date_to_add_it_to)
代碼:
//4-29-2009加90天，保存到day
SELECT DATEADD(DY, 90,'4-29-2009')
GO
//4-29-2009減60天，保存到day
SELECT DATEADD(DY, -60,'4-29-2009')
GO
-----------------------------------------------------------
5.4.4. DATEDIFF()
功能:日期相加或者相減n天后的日期
語法:DATEDIFF ( datepart , startdate , enddate )
datepart列表:
day:單位=天
month:單位=月
year:單位=年
hour:單位=小時
minute:單位=分
second:單位=秒
week:單位=周
代碼:
//10/01/2009國慶到今天的天數(shù)
SELECT DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的月數(shù)
SELECT DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的年數(shù)
SELECT DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的周數(shù)
SELECT DATEDIFF(week,'10/1/2009',CURRENT_TIMESTAMP)
GO

-----------------------------------------------------------
5.4.5. DATEFIRST()
功能:設(shè)置或者查詢一周的第一天
SELECT @@DATEFIRST 'First Day of the Week'
GO
value is 7
SELECT DATEPART(weekday, CAST('20091001' AS DATETIME) + @@DATEFIRST);
GO
value is 3
-----------------------------------------------------------
6. DATEFORMAT()
功能:設(shè)置日期格式
語法:SET DATEFORMAT <format>
format(ymd,mdy,dmy)
代碼:set dataformat mdy
-----------------------------------------------------------
7. DATENAME()
功能:日期date按datepart風(fēng)格之后變成字符串
語法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代碼:
select datename(day,CURRENT_TIMESTAMP)
select datename(month,CURRENT_TIMESTAMP)
select datename(year,CURRENT_TIMESTAMP)
select datename(hour,CURRENT_TIMESTAMP)
select datename(minute,CURRENT_TIMESTAMP)
select datename(week,CURRENT_TIMESTAMP)
select datename(weekday,CURRENT_TIMESTAMP)
-----------------------------------------------------------
8. DATEPART()
功能:日期date按datepart風(fēng)格之后變成字符串
語法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代碼:
-----------------------------------------------------------
9. Day()
功能:求日期的天
語法:day(date)
代碼:select day(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10. GETDATE()
功能:求當(dāng)前日期和時間
語法:GETDATE()
代碼:select GETDATE() 和select CURRENT_TIMESTAMP相同
-----------------------------------------------------------
11. GETUTCDATE()

-----------------------------------------------------------
12. ISDATE()

-----------------------------------------------------------
13. MONTH()
功能:求日期的月
語法:MONTH(date)
代碼:select month(CURRENT_TIMESTAMP)
-----------------------------------------------------------
14. Year()
功能:求日期的年
語法:Year(date)
代碼:select Year(CURRENT_TIMESTAMP)
-----------------------------------------------------------
5.5數(shù)學(xué)函數(shù)Math Functions
1. ABS()
2. ACOS()
3. ASIN()
4. ATAN()
5. CEILING()
6. COS()
7. COT()
8. DEGREES()
9. EXP()
10. FLOOR()
11. ISNUMERIC()
12. LOG()
13. LOG10()
14. PI()
15. Power()
16. RADIANS()
11. 17. RAND()
18. ROUND()
19. SIGN()
20. Sin()
21. SQRT()
22. SQUARE()
23. TAN()
-----------------------------------------------------------
5.6分析函數(shù)Analytical Functions
1. COMPUTE()
2. CUBE()
3. DENSE_RANK()
4. GROUPING()
5. NTILE()
6. PARTITION()
7. PIVOT()
8. ROLLUP()
9. ROW_NUMBER()
10. STDEV()
11. STDEVP()
12. VAR()
13. VARP()
/**********************************************************/
6.Transact SQL
6.1數(shù)據(jù)類型Data Types
6.2腳本語法sytanx
6.3腳本游標(biāo)Cursor
6.4存儲過程Procedure
6.5存儲函數(shù)Function
6.6觸發(fā)器Trigger
6.7事務(wù)Transaction
6.8其它other
/**********************************************************/
6.1數(shù)據(jù)類型Data Types
1. bigint
2. bit
3. bitwise operators
4. Char
5. collate
6. Create Type
7. Data type
8. Date Type
9. datetime
10. decimal
11. Float
12. FULLTEXT
13. integer
14. Large Text
15. money
16. nchar
17. nVarChar
18. OPENROWSET
19. READTEXT
20. smalldatetime
21. Smallint
22. SQL_VARIANT
23. text
24. TEXTPTR
25. timestamp
26. VARBINARY
27. VARCHAR
28. WRITETEXT
29. Unicode

-----------------------------------------------------------
6.2腳本語法syntax

6.2.0局部/全局變量定義
局部變量 (以@開頭)
格式:declare @變量名 數(shù)據(jù)類型
代碼:declare @x int

全局變量 (必須以@@開頭)
格式:declare @@變量名 類型
代碼:select @@id = '10010001'

6.2.1塊語句
格式:
begin
...
end
-----------------------------------------------------------
6.2.2賦值語句set/select
set @id = '10010001'
select @id = '10010001'

6.2.3條件語句(if/case)
6.2.3.1 if語句
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'

6.2.3.2 CASE語句
--CASE

6.2.4循環(huán)語句(while)
--WHILE

6.2.5定時執(zhí)行(waitfor)

--WAITFOR
--例 等待1 小時2 分零3 秒后才執(zhí)行SELECT 語句
waitfor delay '01:02:03'
select * from employee
--例 等到晚上11 點零8 分后才執(zhí)行SELECT 語句
waitfor time '23:08:00'
select * from employee

-----------------------------------------------------------
6.3腳本游標(biāo)Cursor
//游標(biāo)應(yīng)用順序
1.DECLARE --為查詢設(shè)定游標(biāo)
2.OPEN --檢索查詢結(jié)果打開一個游標(biāo)
3.FETCH --檢索一行查詢結(jié)果
4.CLOSE / DEALLOCATE--關(guān)閉游標(biāo)或者重新分配游標(biāo)

語法:
DECLARE <游標(biāo)名稱> CURSOR FOR(select sql)
OPEN <游標(biāo)名稱>
while @@fetch_status = 0
begin
FETCH NEXT FROM <游標(biāo)名稱> INTO <變量名清單>
{其它代碼處理}
end
CLOSE <游標(biāo)名稱>

代碼1:

-----------------------------------------------------------
6.4存儲過程Procedure



//存儲過程建立
語法:
create procedure <存儲過程名>(
[輸入?yún)?shù)列表],[返回參數(shù)列表 output]
)
as
[局部變量定義]
begin
{語句體}
end

代碼:
create procedure p_update_name_tb_hr_gz(@id int,@newname varchar(30))
as
begin
if (exists(select * from tb_hr_gz where id=@id))
begin
update tb_hr_gz set name=@newname where id=@id
end
end

//測試
EXEC dbo.p_update_name_tb_hr_gz '112','chenglei'

//存儲過程刪除
語法:
drop procedure <存儲過程名>
代碼:
drop procedure p_update_name_tb_hr_gz

-----------------------------------------------------------
6.5存儲函數(shù)Function

//存儲函數(shù)建立
語法:
CREATE FUNCTION <函數(shù)名>(參數(shù)變量列表)
[返回值RETURNS 數(shù)據(jù)類型] [WITH ENCRYPTION]
AS
BEGIN
{函數(shù)代碼體....}

END

代碼:




//測試函數(shù)
select name, gz,dbo.f_amt_to_chn(gz) as 中文金額,dbo.f_amt_to_eng(gz) as 英文金額 from tb_hr_gz

//刪除函數(shù)
語法:
drop function <函數(shù)名稱>

代碼:
drop function f_num_to_eng
-----------------------------------------------------------
6.6觸發(fā)器Trigger

22. 1. Trigger( 14 ) 22. 10. Trigger order( 2 )
22. 2. Alter Trigger( 4 ) 22. 11. Drop trigger( 2 )
22. 3. Trigger for after( 4 ) 22. 12. COLUMNS_UPDATED( 1 )
22. 4. Trigger for Delete( 4 ) 22. 13. Update function( 3 )
22. 5. Trigger for insert( 1 ) 22. 14. Deleted table( 2 )
22. 6. Trigger for update( 4 ) 22. 15. Inserted table( 5 )
22. 7. Trigger on database( 2 ) 22. 16. RECURSIVE_TRIGGERS( 1 )
22. 8. Trigger on server( 1 ) 22. 17. Utility trigger( 4 )
22. 9. Trigger on view( 3 )



//觸發(fā)器建立
語法:
create trigger <觸發(fā)器名稱> on <表名>
[for insert | update | delete]
as
[定義變量]
begin
{代碼塊...}
end

代碼0:

//刪除觸發(fā)器
語法:
drop trigger <觸發(fā)器名稱>
代碼:
drop trigger tg_w_house_center
-----------------------------------------------------------
6.7事務(wù)Transaction
事務(wù)(COMMIT/ROLLBACK)
SET TRANSACTION --定義當(dāng)前事務(wù)數(shù)據(jù)訪問特征
COMMIT --提交當(dāng)前事務(wù)
ROLLBACK --取消當(dāng)前事務(wù)