/*********************************************************/
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ù)學函數(shù)Math Functions
5.6分析函數(shù)Analytical Functions
/*********************************************************/
6.數(shù)據(jù)庫腳本Script
6.1數(shù)據(jù)類型Data Types
6.2腳本語法Statements
6.3腳本游標Cursor
6.4存儲過程Procedure
6.5存儲函數(shù)Function
6.6觸發(fā)器Trigger
6.7事務(wù)Transaction
6.8其它Other
/*********************************************************/
SQL明細 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)
代碼:
//列出沒有完成銷售目標10%的銷售人員清單[測試]
select name from salesreps where quota (0.1 * select sum(target) from offices))
//列出公司的銷售目標超過各個銷售人員定額總和的銷售點[>測試]
select city from offices where target > (select sum(quota) from salesreps where rep_office=office)
//列出超過銷售目標的銷售點的業(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
)
//列出完成銷售目標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ù)為準
right outer join:查詢的結(jié)果以右邊表行數(shù)為準
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ù)為準
right outer :查詢的結(jié)果以右邊表行數(shù)為準
語法:select ... from table1 [left/right/full outer join ]table2 where ...
代碼:
//以Customers表行數(shù)為標準去連接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
//匯總查詢相當于會計報表中的小計匯總的功能
語法: 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 限制功能:父表一行記錄不能更新/刪除，當子表有一條記錄以上時
CASCADE 級聯(lián)功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動更新/刪除
SET NULL 置空功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動為空
SET DEFAULT 默認值功能:父表一行記錄記錄更新/刪除刪除,子表對應(yīng)所有的記錄自動寫入默認值
代碼:
建立外鍵的主要代碼
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:
是當前數(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ù)學函數(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>)
代碼:
//日期風格轉(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
功能:
得到當前數(shù)據(jù)庫的日期
代碼:
//直接得到當前日期
SELECT CURRENT_TIMESTAMP
go
//調(diào)用變量中的當前日期
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風格之后變成字符串
語法: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風格之后變成字符串
語法:DATENAME (datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代碼:
-----------------------------------------------------------
9. Day()
功能:求日期的天
語法:day(date)
代碼:select day(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10. GETDATE()
功能:求當前日期和時間
語法: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ù)學函數(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腳本游標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
復(fù)制代碼 代碼如下:
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
6.2.4循環(huán)語句(while)
--WHILE
復(fù)制代碼 代碼如下:
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
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腳本游標Cursor
//游標應(yīng)用順序
1.DECLARE --為查詢設(shè)定游標
2.OPEN --檢索查詢結(jié)果打開一個游標
3.FETCH --檢索一行查詢結(jié)果
4.CLOSE / DEALLOCATE--關(guān)閉游標或者重新分配游標
語法:
DECLARE 游標名稱> CURSOR FOR(select sql)
OPEN 游標名稱>
while @@fetch_status = 0
begin
FETCH NEXT FROM 游標名稱> INTO 變量名清單>
{其它代碼處理}
end
CLOSE 游標名稱>
代碼1:
復(fù)制代碼 代碼如下:
/*帶游標的存儲過程*/
create procedure p_fill_remark_tb_hr_gz
as
declare @id1 int
declare @name1 varchar(30)
declare @bm1 varchar(20)
begin
declare cursor1 cursor for select id,name,bm from tb_hr_gz
open cursor1
fetch next from cursor1 into @id1,@name1,@bm1
while @@fetch_status > 0
begin
update tb_hr_gz set remark=@name1+'-'+@bm1 where id=@id1
fetch next from cursor1 into @id1,@name1,@bm1
end
close cursor1
end
//測試帶游標的存儲過程
EXEC dbo.p_fill_remark_tb_hr_gz
-----------------------------------------------------------
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
代碼:
復(fù)制代碼 代碼如下:
//函數(shù)f_amt_to_eng()功能:數(shù)字金額轉(zhuǎn)換為英文字母金額
CREATE FUNCTION f_amt_to_eng(@num numeric(15,2))
RETURNS varchar(400) WITH ENCRYPTION
AS
BEGIN
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--將12位整數(shù)分成4段:十億、百萬、千、百十個
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--個位0-19
SET @tenth=(CASE WHEN @tenth=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
SET @result=@result+', '--百位不是0則每段之間加連接符,
IF (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' and '--百位是0則加連接符AND
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred'
IF @tenth>=2 and @tenth=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one=19
BEGIN
IF @tenth>0
SET @result=@result+'-'
ELSE
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' billion'
IF @i=1 and @million>0
SET @result=@result+' million'
IF @i=2 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)>'00'
BEGIN
SET @result=@result+' AND '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
復(fù)制代碼 代碼如下:
CREATE FUNCTION f_amt_to_chn (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
SET @c_data=''
SET @i=1
WHILE @i=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str>' '
BEGIN
IF not ((SUBSTRING(@n_data,@i,2)='00') or ((@n_str='0') and ((@i=4) or (@i=8) or (@i=12) or (@i=14))))
SET @c_data=@c_data+SUBSTRING('零壹貳叁肆伍陸柒捌玖',CAST(@n_str AS int)+1,1)
IF not ((@n_str='0') and (@i>4) and (@i>8) and (@i>12))
SET @c_data=@c_data+SUBSTRING('仟佰拾億仟佰拾萬仟佰拾元角分',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)='億萬'
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET @i=@i+1
END
IF @num0
SET @c_data='負'+@c_data
IF @num=0
SET @c_data='零元'
IF @n_str='0'
SET @c_data=@c_data+'整'
RETURN(@c_data)
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ù)制代碼 代碼如下:
create trigger tg_tb_hr_bm on tb_hr_bm
for insert,update,delete
as
declare @bm_d varchar(20)
declare @bm_i varchar(20)
begin
set @bm_d=(select bm from deleted)
set @bm_i=(select bm from inserted)
if exists(select * from tb_hr_gz ,deleted where(tb_hr_gz.bm =deleted.bm ))
begin
update tb_hr_gz set bm='' where bm =@bm_d
end
if update(bm)
begin
update tb_hr_gz set bm=@bm_i where bm =@bm_i
end
end
//刪除觸發(fā)器
語法:
drop trigger 觸發(fā)器名稱>
代碼:
drop trigger tg_w_house_center
-----------------------------------------------------------
6.7事務(wù)Transaction
事務(wù)(COMMIT/ROLLBACK)
SET TRANSACTION --定義當前事務(wù)數(shù)據(jù)訪問特征
COMMIT --提交當前事務(wù)
ROLLBACK --取消當前事務(wù)
