/*********************************************************/
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
/*********************************************************/
目錄清單CONTEXT LIST
/*********************************************************/
1.數(shù)據(jù)庫DataBase
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.2數(shù)據(jù)庫備份與恢復(fù)backup/restore database
/*********************************************************/
2.數(shù)據(jù)查詢DATA QUERY LANGUAGE
2.1選擇查詢Select Query
2.2聚集查詢Aggregate Query
2.3子查詢 Sub Query
2.4連接查詢Table Joins
2.5匯總查詢Group Query
/*********************************************************/
3.數(shù)據(jù)修改DATA MODIFY LANGUAGE
3.1插入數(shù)據(jù)Insert
3.2修改數(shù)據(jù)Update
3.3刪除數(shù)據(jù)Delete
/*********************************************************/
4.數(shù)據(jù)定義DATA DEFINE LANGUAGE
4.1表Table
4.2列Column
4.3序列Identity
4.4約束Constraints
4.5索引Index
4.6視圖view
4.7權(quán)限Privilege
/*********************************************************/
5.數(shù)據(jù)庫函數(shù)Functions
5.1轉(zhuǎn)換函數(shù)Data Convert Functions
5.2聚集函數(shù)Aggregate Functions
5.3字符函數(shù)char Functions
5.4日期函數(shù)Date Functions
5.5數(shù)學(xué)函數(shù)Math Functions
5.6分析函數(shù)Analytical Functions
/*********************************************************/
6.數(shù)據(jù)庫腳本Script
6.1數(shù)據(jù)類型Data Types
6.2腳本語法Statements
6.3腳本游標(biāo)Cursor
6.4存儲過程Procedure
6.5存儲函數(shù)Function
6.6觸發(fā)器Trigger
6.7事務(wù)Transaction
6.8其它Other
/*********************************************************/
SQL明細(xì) SQL DETAIL
/**********************************************************/
1.數(shù)據(jù)庫DataBase
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.2備份與恢復(fù)backup/restore database
/**********************************************************/
1.1數(shù)據(jù)庫建立/刪除create/drop database
1.1.1.建立數(shù)據(jù)庫
語法:create database <數(shù)據(jù)庫名> ［其它參數(shù)］
代碼:
//建立數(shù)據(jù)庫 hr
create database hr
1.1.2.刪除數(shù)據(jù)庫。
語法:drop database <數(shù)據(jù)庫名>
代碼:
//刪除數(shù)據(jù)庫hr
drop database hr
//如果存在hr數(shù)據(jù)庫,則刪除數(shù)據(jù)庫hr
IF DB_ID('hr') IS NOT NULL
DROP DATABASE TestDB
-----------------------------------------------------------
1.2備份與恢復(fù)backup/restore database
1.2.1.添加備份設(shè)備
語法:sp_addumpdevice <keyword> <devicename> <devicepath>
代碼:
//添加備份設(shè)備為本地硬盤
sp_addumpdevice 'disk', 'localbackup', 'e:\database\backup\localbak.bak'
//備份到網(wǎng)絡(luò)硬盤
sp_addumpdevice 'disk', 'netbackup', '\\computer1\database\backup\netbak.bak'
//備份到磁帶
sp_addumpdevice 'tape', 'tapebackup', '\\.\tape1bak'
//備份到命名管道
sp_addumpdevice 'pipe', 'pipebackup', 'e:\database\backup\pipebak'
1.2.2.備份數(shù)據(jù)庫
語法:backup database <databasename> to <devicename>| disk=<backupnamepath>
代碼:
//備份數(shù)據(jù)庫到備份設(shè)備
backup database pubs to localbackup
//備份數(shù)據(jù)庫到指定路徑下面的指定文件
backup database pubs to disk='e:\database\backup\pubsbak.bak'
1.2.3.恢復(fù)數(shù)據(jù)庫
語法:restore database <databasename> from <devicename>| disk=<backupnamepath>
代碼:
//從備份設(shè)備中恢復(fù)數(shù)據(jù)庫
restore database pubs from localbackup
//從備份文件中恢復(fù)數(shù)據(jù)庫
/**********************************************************/
2.數(shù)據(jù)查詢DATA QUERY LANGUAGE
2.1選擇查詢Select Query
2.2子查詢 Sub Query
2.3連接查詢Table Joins
2.4匯總查詢Group Query
-----------------------------------------------------------
2.1選擇查詢Select Query
語法:
select [top n][/all]/[distinct] [*] / [columnlist...] [<columnlist as alias...] [const/sql/function expression]
from (<tablelist,>...) [as alias]
[where search expression...]
[group by groupnamelist ....]
[having search-expression...]
[order by sort-expression...]
//select選項說明:
top n:只顯示第一條到n條記錄
//重復(fù)與不重復(fù)記錄
all:表示包含重復(fù)的記錄
distinct:表示去掉重復(fù)的記錄
//所有字段與選中字段和字段別名
*:表示所有的列名
columnlist:表示字段列表
columnlist as alias:表示字段的別名
//其它字段
const-expression:常量表達(dá)式(如數(shù)字/字符串/日期/時間常量)
sql-expression:常見的sql語句的加減乘除表達(dá)式運(yùn)算字段
function expression:數(shù)據(jù)庫函數(shù)和自定義函數(shù)字段
//測試條件
比較測試條件(=,<>,>,<,>=,<=)
范圍測試條件(betweeen 下限值 and 上限值)
成員測試條件(in,not in)
存在測試條件(exists,not exists)
匹配測試條件(like)
限定測試條件(any,all)
空值測試條件(is null)
//復(fù)合搜索條件(and, or,not,())
and:邏輯與運(yùn)算
and:邏輯或運(yùn)算
not:邏輯非運(yùn)算
():可改變優(yōu)先級的運(yùn)算符
//子句說明
select子句:指出檢索的數(shù)據(jù)項
from 子句:指出檢索的數(shù)據(jù)表
where 子句:指出檢索的數(shù)據(jù)條件
group by子句:指出檢索的數(shù)據(jù)進(jìn)行匯總
having子句:指出檢索的數(shù)據(jù)進(jìn)行匯總之前的條件
order by子句:指出檢索的數(shù)據(jù)條件進(jìn)行排序
代碼:
//所有字段方式顯示orders全部記錄
select * from orders
//按字段顯示全部記錄
select order_num,order_date,amount from orders
//按字段顯示全部記錄,但除掉重復(fù)的記錄
select order_num,order_date,amount from orders
//用sql-expression乘運(yùn)算計算列
select amount,amount*0.08 as discount_amt from orders
//用自定義函數(shù)計算指定列
select order_num,order_date,amount,f_amt_to_chn(amount) as 金額 from orders
select選項太多，代碼例子就省略...
-----------------------------------------------------------
2.2子查詢 Sub Query
語法:select ...
from <tablename>
where / having column 測試條件 (Sub Query)
//測試條件
比較測試條件(=,<>,>,<,>=,<=)
范圍測試條件(betweeen 下限值 and 上限值)
成員測試條件(in,not in)
存在測試條件(exists,not exists)
匹配測試條件(like)
限定測試條件(any,all)
空值測試條件(is null)
代碼:
//列出沒有完成銷售目標(biāo)10%的銷售人員清單[<測試]
select name from salesreps where quota < (0.1 * select sum(target) from offices))
//列出公司的銷售目標(biāo)超過各個銷售人員定額總和的銷售點[>測試]
select city from offices where target > (select sum(quota) from salesreps where rep_office=office)
//列出超過銷售目標(biāo)的銷售點的業(yè)務(wù)人員[in測試]
select name from salesreps where office in (select office from offies where sales > target)
//列出訂單大于2500元的產(chǎn)品名稱[exists測試]
select description from products where exists (
select * from orders where product=prodct_id and amount > 2500.00
)
//列出完成銷售目標(biāo)10%的銷售人員清單[any測試]
select name from salesreps where (0.1* quota) < any(select amount from orders where rep=empl_num)
