/*********************************************************/
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 :禁止其它網站轉載此文章
/*********************************************************/
目錄清單CONTEXT LIST
/*********************************************************/
1.數據庫DataBase
1.1數據庫建立/刪除create/drop database
1.2數據庫備份與恢復backup/restore database
/*********************************************************/
2.數據查詢DATA QUERY LANGUAGE
2.1選擇查詢Select Query
2.2聚集查詢Aggregate Query
2.3子查詢 Sub Query
2.4連接查詢Table Joins
2.5匯總查詢Group Query
/*********************************************************/
3.數據修改DATA MODIFY LANGUAGE
3.1插入數據Insert
3.2修改數據Update
3.3刪除數據Delete
/*********************************************************/
4.數據定義DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Identity
4.4約束Constraints
4.5索引Index
4.6視圖view
4.7權限Privilege
/*********************************************************/
5.數據庫函數Functions
5.1轉換函數Data Convert Functions
5.2聚集函數Aggregate Functions
5.3字符函數char Functions
5.4日期函數Date Functions
5.5數學函數Math Functions
5.6分析函數Analytical Functions
/*********************************************************/
6.數據庫腳本Script
6.1數據類型Data Types
6.2腳本語法Statements
6.3腳本游標Cursor
6.4存儲過程Procedure
6.5存儲函數Function
6.6觸發器Trigger
6.7事務Transaction
6.8其它Other
/*********************************************************/
SQL明細 SQL DETAIL
/**********************************************************/
1.數據庫DataBase
1.1數據庫建立/刪除create/drop database
1.2備份與恢復backup/restore database
/**********************************************************/
1.1數據庫建立/刪除create/drop database
1.1.1.建立數據庫
語法:create database <數據庫名> ［其它參數］
代碼:
//建立數據庫 hr
create database hr
1.1.2.刪除數據庫。
語法:drop database <數據庫名>
代碼:
//刪除數據庫hr
drop database hr
//如果存在hr數據庫,則刪除數據庫hr
IF DB_ID('hr') IS NOT NULL
DROP DATABASE TestDB
-----------------------------------------------------------
1.2備份與恢復backup/restore database
1.2.1.添加備份設備
語法:sp_addumpdevice <keyword> <devicename> <devicepath>
代碼:
//添加備份設備為本地硬盤
sp_addumpdevice 'disk', 'localbackup', 'e:\database\backup\localbak.bak'
//備份到網絡硬盤
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.備份數據庫
語法:backup database <databasename> to <devicename>| disk=<backupnamepath>
代碼:
//備份數據庫到備份設備
backup database pubs to localbackup
//備份數據庫到指定路徑下面的指定文件
backup database pubs to disk='e:\database\backup\pubsbak.bak'
1.2.3.恢復數據庫
語法:restore database <databasename> from <devicename>| disk=<backupnamepath>
代碼:
//從備份設備中恢復數據庫
restore database pubs from localbackup
//從備份文件中恢復數據庫
/**********************************************************/
2.數據查詢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條記錄
//重復與不重復記錄
all:表示包含重復的記錄
distinct:表示去掉重復的記錄
//所有字段與選中字段和字段別名
*:表示所有的列名
columnlist:表示字段列表
columnlist as alias:表示字段的別名
//其它字段
const-expression:常量表達式(如數字/字符串/日期/時間常量)
sql-expression:常見的sql語句的加減乘除表達式運算字段
function expression:數據庫函數和自定義函數字段
//測試條件
比較測試條件(=,<>,>,<,>=,<=)
范圍測試條件(betweeen 下限值 and 上限值)
成員測試條件(in,not in)
存在測試條件(exists,not exists)
匹配測試條件(like)
限定測試條件(any,all)
空值測試條件(is null)
//復合搜索條件(and, or,not,())
and:邏輯與運算
and:邏輯或運算
not:邏輯非運算
():可改變優先級的運算符
//子句說明
select子句:指出檢索的數據項
from 子句:指出檢索的數據表
where 子句:指出檢索的數據條件
group by子句:指出檢索的數據進行匯總
having子句:指出檢索的數據進行匯總之前的條件
order by子句:指出檢索的數據條件進行排序
代碼:
//所有字段方式顯示orders全部記錄
select * from orders
//按字段顯示全部記錄
select order_num,order_date,amount from orders
//按字段顯示全部記錄,但除掉重復的記錄
select order_num,order_date,amount from orders
//用sql-expression乘運算計算列
select amount,amount*0.08 as discount_amt from orders
//用自定義函數計算指定列
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)
//列出超過銷售目標的銷售點的業務人員[in測試]
select name from salesreps where office in (select office from offies where sales > target)
//列出訂單大於2500元的產品名稱[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
多表連接類型可分為三類(內/外/交叉連接)
主從表或者父子表進行多表連接多以主鍵和外鍵進行關聯
Outer joins(LEFT OUTER, RIGHT OUTER, and FULL OUTER joins)
left outer join:查詢的結果以左邊表行數為准
right outer join:查詢的結果以右邊表行數為准
2.3.1.內連接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子句的內連接
SELECT *
FROM Products
INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
//有where子句的內連接
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 :查詢的結果以左邊表行數為准
right outer :查詢的結果以右邊表行數為准
語法:select ... from table1 [left/right/full outer join ]table2 where ...
代碼:
//以Customers表行數為標准去連接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
功能:以主從表或者父子表之間的主鍵進行連接，最終以笛卡爾乘積運算的結果
語法:select ... from table1 cross join table2 where ...
代碼:
//顯示結果以表1行數*表2行數
假設Departments為4行記錄
假設Jobs為3行記錄
下面的顯示結果為4*3=12行記錄
SELECT deptname,jobdesc FROM Departments CROSS JOIN Jobs
//用關鍵字匹配的交叉連接
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子句應用
select rep,avg(amount) from orders having sum(quota) > 3000.00
/**********************************************************/
3.數據修改DATA MODIFY LANGUAGE
3.1插入數據Insert
3.2修改數據Update
3.3刪除數據Delete
-----------------------------------------------------------
3.1插入數據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>;
代碼:
//把一批數據批量插入到一個備份表中
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修改數據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刪除數據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.數據定義DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Indentity
4.4約束Constraints
4.5索引Index
4.6視圖view
4.7權限Privilege
/**********************************************************/
4.1表Table
4.1.1.建立表
語法:
create table <表名>(
<列名> <數據類型> [長度] <,>
<列名...>
)
代碼:
//建立公司部門表
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
<列名> <數據類型> [長度] <,>
<列名...>
代碼:
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
<列名> <數據類型> [長度] [null | not null]
代碼:
//修改工資列為dec(8,2)
alter table tb_hr_gz alter column gz dec(8,2) null
-----------------------------------------------------------
4.3序列Identity
//特別要求
IDENTITY字段數據類型只能是(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規則約束(rule)
4.4.4檢查約束(check)
4.4.5唯一約束(unique)
4.4.6主鍵約束(primary key)
4.4.7外鍵約束(foreign key)
4.4.8商業規則(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=工作年數
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規則約束(rule)
語法:CREATE RULE rulename AS condition
代碼:
//郵編號碼6位100000-999999
//建立一個自定義zip類型
CREATE TYPE zip FROM CHAR(6) NOT NULL
//建立一個規則約束
CREATE RULE zip_rule AS @number >100000 and @number < 999999
//綁定規則約束到zip類型
EXEC sp_bindrule zip_rule, 'zip'
//應用自定義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 //級聯更新
ON DELETE CASCADE //級聯刪除
ON DELETE SET NULL //級聯置空
ON UPDATE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表更新後,子表的行為
ON DELETE(RESTRICT|CASCADE|SET NULL|SET DEFAULT) 表示父表刪除後,子表的行為
RESTRICT 限制功能:父表一行記錄不能更新/刪除，當子表有一條記錄以上時
CASCADE 級聯功能:父表一行記錄記錄更新/刪除刪除,子表對應所有的記錄自動更新/刪除
SET NULL 置空功能:父表一行記錄記錄更新/刪除刪除,子表對應所有的記錄自動為空
SET DEFAULT 默認值功能:父表一行記錄記錄更新/刪除刪除,子表對應所有的記錄自動寫入默認值
代碼:
建立外鍵的主要代碼
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商業規則(business rule)
//用觸發器或者存儲過程來實現
-----------------------------------------------------------
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的概念：
視圖不是表，也不是表數據的備份，在數據庫模式中只是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權限Privilege
4.7.1數據庫用戶添加
語法:
sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt= ] 'encryption_option' ]
代碼:
數據庫testdb上面添加一個登陸用戶test,密碼為tt
EXEC sp_addlogin 'test', 'tt', 'testdb', 'us_english'
EXEC sp_addlogin 'yao', 'it', 'mtyjxc', 'us_english'
-----------------------------------------------------------
4.7.2數據庫用戶刪除
語法:DROP LOGIN <登陸名稱>
代碼:DROP LOGIN test
-----------------------------------------------------------
4.7.3用戶權限授予grant
grant語法:
GRANT privilege [, ...] ON object [, ...]
TO { PUBLIC | GROUP group | username }
privilege取值范圍如下:
SELECT:訪問聲明的表/視圖的所有列/字段．
INSERT:向聲明的表中插入所有列字段．
UPDATE:更新聲明的表所有列/字段．
DELETE:從聲明的表中刪除所有行．
RULE:在表/視圖上定義規則 （參見 CREATE RULE 語句）．
ALL:賦予所有權限．
object取值范圍如下:
table
view
sequence
PUBLIC:代表是所有用戶的簡寫.
GROUP:將要賦予權限的組 group
username:將要賦予權限的用戶名．
如果成功，返回輸出CHANGE信息．
代碼:
GRANT all on mtyjxc to 'yao'
-----------------------------------------------------------
7.7.4用戶權限解除REVOKE
REVOKE { ALL | statement [ ,...n ] }
FROM security_account [ ,...n ]
ALL:
指定將刪除所有適用的權限。
對於語句權限，只有 sysadmin 固定服務器角色成員可以使用 ALL。
對於對象權限，sysadmin 固定服務器角色成員、db_owne 固定數據庫角色成員和數據庫對象所有者都可以使用 ALL。
statement:
是要刪除其權限的授權語句。語句列表可以包括:
* CREATE DATABASE
* CREATE DEFAULT
* CREATE FUNCTION
* CREATE PROCEDURE
* CREATE RULE
* CREATE TABLE
* CREATE VIEW
* BACKUP DATABASE
* BACKUP LOG
FROM:
指定安全帳戶列表。
security_account:
是當前數據庫內將要被刪除權限的安全帳戶。
安全帳戶可以是:SQL Server用戶,SQL Server角色。
代碼:
REVOKE all ON mtyjxc.* TO yao
REVOKE all ON mtyjxc TO yao
/**********************************************************/
5.數據庫函數Functions
5.1轉換函數Data Convert Functions
5.2聚集函數Aggregate Functions
5.3字符函數char Functions
5.4日期函數Date Functions
5.5數學函數Math Functions
5.6分析函數Analytical Functions
-----------------------------------------------------------
5.1轉換函數Data Convert Functions
5.1.1 CAST()
功能:數據類型轉換
語法: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()
功能:把表達式值轉換為指定sytle的數據類型
語法:CONVERT(data_ type(<length>), expression, <style>)
代碼:
//日期風格轉換
datetime轉指定日期格式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)
//字符串轉數字
CONVERT (INTEGER , '12345')
//字符轉日期
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聚集函數Aggregate Functions
語法:select AggregateFunctions(column-name)
sum(column-name):計算字段總和
avg(column-name):計算字段平均值
min(column-name):計算字段最小值
max(column-name):計算字段最大值
count(column-name):計算字段非空值的個數
count(*):計算查詢結果的記錄個數
代碼:
//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字符函數char Functions
1. ASCII()
//函數返回字符表達式最左端字符的ASCII 碼值
2. Char()
//函數用於將ASCII 碼轉換為字符--如果沒有輸入0 ~ 255 之間的ASCII 碼值CHAR 函數會返回一個NULL
3. CHARINDEX()
//函數返回字符串中某個指定的子串出現的開始位置
4. DIFFERENCE()
5. FORMATMESSAGE()
6. LEFT()
7. LEN()
8. LOWER()
//函數把字符串全部轉換為小寫
9. LTRIM()
//函數把字符串頭部的空格去掉
10.nchar()
11.PATINDEX()
12.QUOTENAME()
13.REPLACE()
//函數返回被替換了指定子串的字符串
14.REPLICATE()
/函數返回一個重復指定次數的字符串
15.REVERSE()
//函數將指定的字符串的字符排列順序顛倒
16.Right()
17.RTRIM()
/函數把字符串尾部的空格去掉
18.SOUNDEX()
19.SPACE()
//函數返回一個有指定長度的空白字符串
20.STR()
//函數把數值型數據轉換為字符型數據
21.STUFF()
//函數用另一子串替換字符串指定位置長度的子串
22.SUBSTRING()
//函數返回子字符串
23.UNICODE()
24.UPPER()
//函數把字符串全部轉換為大寫
-----------------------------------------------------------
5.4日期函數Date Functions
5.4.1. CURRENT_TIMESTAMP
功能:
得到當前數據庫的日期
代碼:
//直接得到當前日期
SELECT CURRENT_TIMESTAMP
go
//調用變量中的當前日期
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
運行結果是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國慶到今天的天數
SELECT DATEDIFF(day,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的月數
SELECT DATEDIFF(month,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的年數
SELECT DATEDIFF(year,'10/1/2009',CURRENT_TIMESTAMP)
GO
//10/01/2009國慶到今天的周數
SELECT DATEDIFF(week,'10/1/2009',CURRENT_TIMESTAMP)
GO
-----------------------------------------------------------
5.4.5. DATEFIRST()
功能:設置或者查詢一周的第一天
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()
功能:設置日期格式
語法: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數學函數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分析函數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數據類型Data Types
6.2腳本語法sytanx
6.3腳本游標Cursor
6.4存儲過程Procedure
6.5存儲函數Function
6.6觸發器Trigger
6.7事務Transaction
6.8其它other
/**********************************************************/
6.1數據類型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 @變量名 數據類型
代碼: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
復制代碼 代碼如下:
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循環語句(while)
--WHILE
復制代碼 代碼如下:
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定時執行(waitfor)
--WAITFOR
--例 等待1 小時2 分零3 秒後才執行SELECT 語句
waitfor delay '01:02:03'
select * from employee
--例 等到晚上11 點零8 分後才執行SELECT 語句
waitfor time '23:08:00'
select * from employee
-----------------------------------------------------------
6.3腳本游標Cursor
//游標應用順序
1.DECLARE --為查詢設定游標
2.OPEN --檢索查詢結果打開一個游標
3.FETCH --檢索一行查詢結果
4.CLOSE / DEALLOCATE--關閉游標或者重新分配游標
語法:
DECLARE <游標名稱> CURSOR FOR(select sql)
OPEN <游標名稱>
while @@fetch_status = 0
begin
FETCH NEXT FROM <游標名稱> INTO <變量名清單>
{其它代碼處理}
end
CLOSE <游標名稱>
代碼1:
復制代碼 代碼如下:
/*帶游標的存儲過程*/
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 <存儲過程名>(
[輸入參數列表],[返回參數列表 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存儲函數Function
//存儲函數建立
語法:
CREATE FUNCTION <函數名>(參數變量列表)
[返回值RETURNS 數據類型] [WITH ENCRYPTION]
AS
BEGIN
{函數代碼體....}
END
代碼:
復制代碼 代碼如下:
//函數f_amt_to_eng()功能:數字金額轉換為英文字母金額
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位整數分成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
復制代碼 代碼如下:
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 @num<0
SET @c_data='負'+@c_data
IF @num=0
SET @c_data='零元'
IF @n_str='0'
SET @c_data=@c_data+'整'
RETURN(@c_data)
END
//測試函數
select name, gz,dbo.f_amt_to_chn(gz) as 中文金額,dbo.f_amt_to_eng(gz) as 英文金額 from tb_hr_gz
//刪除函數
語法:
drop function <函數名稱>
代碼:
drop function f_num_to_eng
-----------------------------------------------------------
6.6觸發器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 )
//觸發器建立
語法:
create trigger <觸發器名稱> on <表名>
[for insert | update | delete]
as
[定義變量]
begin
{代碼塊...}
end
代碼0:
復制代碼 代碼如下:
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
//刪除觸發器
語法:
drop trigger <觸發器名稱>
代碼:
drop trigger tg_w_house_center
-----------------------------------------------------------
6.7事務Transaction
事務(COMMIT/ROLLBACK)
SET TRANSACTION --定義當前事務數據訪問特征
COMMIT --提交當前事務
ROLLBACK --取消當前事務
