/*********************************************************/
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...] [,=,,=, (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
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 < '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 =0 and gz @y
print 'x > y' --打印字符串'x > y'
else if @y > @z
print 'y > z'
else print 'z > y'

6.2.3.2 CASE语句
--CASE

6.2.4循环语句(while)
--WHILE

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:

-----------------------------------------------------------
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

代码:

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。