...
这里是作者整理的一些sqlserver下常用的代码,需要的朋友可以参考下.
/*********************************************************/
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:\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 to | disk= 代码: //备份数据库到备份设备 backup database pubs to localbackup //备份数据库到指定路径下面的指定文件 backup database pubs to disk='e:\database\backup\pubsbak.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...] [from (...) [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 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 //列出公司的销售目标超过各个销售人员定额总和的销售点[>测试] 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) ----------------------------------------------------------- 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 ----------------------------------------------------------- 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 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 [...] values(...); 代码: //不省略字段清单 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 [(...)] values(...) ; 代码: //把一批数据批量插入到一个备份表中 insert into history_order(order_num,order_date,amount) select order_num,order_date,amount from orders where order_date ----------------------------------------------------------- 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 ) ----------------------------------------------------------- 3.3删除数据Delete 语法1:delete from [where ...] 代码: //所有删除记录 delete from orders 语法2:truncate table 代码2: //所有删除记录 truncate table orders //按条件删除记录 delete from orders where order_date /**********************************************************/ 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 //绑定规则约束到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 ... ) 方法2: alter table tb_hr_gz add constraint tb_hr_gz_ck check(gz >=0 and gz //检查约束删除 语法: 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 //补充说明 常用选项是下面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 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 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 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(), expression,