SQL MSSQL 經常使用代碼年夜全。本站提示廣大學習愛好者：（SQL MSSQL 經常使用代碼年夜全）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL MSSQL 經常使用代碼年夜全正文
/*********************************************************/
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 --撤消以後事務
