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 代码:
//添加备份设备为本地硬盘
sp_addumpdevice 'disk','localbackup','e:databasebackuplocalbak.bak'
//备份到网络硬盘
sp_addumpdevice 'disk','netbackup','computer1databasebackupnetbak.bak'
//备份到磁带
sp_addumpdevice 'tape','tapebackup','.tape1bak'
//备份到命名管道
sp_addumpdevice 'pipe','pipebackup','e:databasebackuppipebak' 1.2.2.备份数据库
语法:backup database to | disk= 代码:
//备份数据库到备份设备
backup database pubs to localbackup
//备份数据库到指定路径下面的指定文件
backup database pubs to disk='e:databasebackuppubsbak.bak' 1.2.3.恢复数据库
语法:restore database from | disk=
代码:
//从备份设备中恢复数据库
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,amount from orders
//用sql-expression乘运算计算列
select amount,amount0.08 as discount_amt from orders
//用自定义函数计算指定列
select order_num,amount,f_amt_to_chn(amount) as 金额 from orders select选项太多，代码例子就省略...
-----------------------------------------------------------
2.2子查询 Sub Query
语法:select ...
from
where / having column 测试条件 (Sub Query)
//测试条件
比较测试条件(=,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,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行记录
下面的显示结果为43=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
group by
[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 [<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',13)
3.1.2.多行插入
语法:insert into [(<columnlist,>...)] values(<valuelist,>...) ; 代码:
//把一批数据批量插入到一个备份表中
insert into history_order(order_num,amount)
select order_num,amount
from orders where order_date < '01/01/2000'
-----------------------------------------------------------
3.2修改数据Update
语法:update 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 [where ...]
代码:
//所有删除记录
delete from orders 语法2:truncate table
代码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( [,,]) 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_infobm_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_infobm_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
AS

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
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 values(values....)
delete from where/having expressions
update 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 datatype)
代码:
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(),expression,