当前位置:Gxlcms > mysql > SQLMSSQL常用代码大全

SQLMSSQL常用代码大全

时间:2021-07-01 10:21:17 帮助过:45人阅读

这里是作者整理的一些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 < (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
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(...)
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 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,