MYSQL编程：SQL MSSQL 经常使用代码年夜全
任何规模的组织都可能受益于外包服务，并在一个标准化和优化的平台上统一其数据库管理任务。基于其本身的特性，DBaaS提供了敏捷和高效的数据库服务，它可以支持多变的需求。/*********************************************************/function:SQLMSSQLTECHNOLOGYARTICLE
file:SQL-MSSQL.TXT
author:chinayaosirQQ:44633197
Tools:MSSQLQUERYANALYSIS
date:4/01/2010
blog:http://blog.csdn.net/chinayaosir
note:克制别的网站转载此文章
/*********************************************************/
目次清单CONTEXTLIST
/*********************************************************/
1.数据库DataBase
1.1数据库创建/删除create/dropdatabase
1.2数据库备份与恢复backup/restoredatabase
/*********************************************************/
2.数据查询DATAQUERYLANGUAGE
2.1选择查询SelectQuery
2.2会萃查询AggregateQuery
2.3子查询SubQuery
2.4毗连查询TableJoins
2.5汇总查询GroupQuery
/*********************************************************/
3.数据修正DATAMODIFYLANGUAGE
3.1拔出数据Insert
3.2修正数据Update
3.3删除数据Delete
/*********************************************************/
4.数据界说DATADEFINELANGUAGE
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转换函数DataConvertFunctions
5.2会萃函数AggregateFunctions
5.3字符函数charFunctions
5.4日期函数DateFunctions
5.5数学函数MathFunctions
5.6剖析函数AnalyticalFunctions
/*********************************************************/
6.数据库剧本Script
6.1数据范例DataTypes
6.2剧本语法Statements
6.3剧本游标Cursor
6.4存储历程Procedure
6.5存储函数Function
6.6触发器Trigger
6.7事件Transaction
6.8别的Other
/*********************************************************/
SQL明细SQLDETAIL
/**********************************************************/
1.数据库DataBase
1.1数据库创建/删除create/dropdatabase
1.2备份与恢复backup/restoredatabase
/**********************************************************/
1.1数据库创建/删除create/dropdatabase
1.1.1.创建数据库
语法:createdatabase<数据库名>［别的参数］
代码:
//创建数据库hr
createdatabasehr
1.1.2.删除数据库。
语法:dropdatabase<数据库名>
代码:
//删除数据库hr
dropdatabasehr
//假如存在hr数据库,则删除数据库hr
IFDB_ID(hr)ISNOTNULL
DROPDATABASETestDB
-----------------------------------------------------------
1.2备份与恢复backup/restoredatabase
1.2.1.增加备份设备
语法:sp_addumpdevice<keyword><devicename><devicepath>
代码:
//增加备份设备为当地硬盘
sp_addumpdevicedisk,localbackup,e:databaseackuplocalbak.bak
//备份到收集硬盘
sp_addumpdevicedisk,netbackup,computer1databaseackup
etbak.bak
//备份到磁带
sp_addumpdevicetape,tapebackup,. ape1bak
//备份到定名管道
sp_addumpdevicepipe,pipebackup,e:databaseackuppipebak
1.2.2.备份数据库
语法:backupdatabase<databasename>to<devicename>disk=<backupnamepath>
代码:
//备份数据库到备份设备
backupdatabasepubstolocalbackup
//备份数据库到指定路径上面的指定文件
backupdatabasepubstodisk=e:databaseackuppubsbak.bak
1.2.3.恢单数据库
语法:restoredatabase<databasename>from<devicename>disk=<backupnamepath>
代码:
//从备份设备中恢单数据库
restoredatabasepubsfromlocalbackup
//从备份文件中恢单数据库
/**********************************************************/
2.数据查询DATAQUERYLANGUAGE
2.1选择查询SelectQuery
2.2子查询SubQuery
2.3毗连查询TableJoins
2.4汇总查询GroupQuery
-----------------------------------------------------------
2.1选择查询SelectQuery
语法:
select/
[*]/[<columnlistasalias...]
from(<tablelist,>...)
//select选项申明:
topn:只显现第一条到n笔记录
//反复与不反复纪录
all:暗示包括反复的纪录
distinct:暗示往失落反复的纪录
//一切字段与选中字段和字段别号
*:暗示一切的列名
columnlist:暗示字段列表
columnlistasalias:暗示字段的别号
//别的字段
const-expression:常量表达式(如数字/字符串/日期/工夫常量)
sql-expression:罕见的sql语句的加减乘除表达式运算字段
functionexpression:数据库函数和自界说函数字段
//测试前提
对照测试前提(=,,>,<,>=,<=)
局限测试前提(betweeen上限值and下限值)
成员测试前提(in,notin)
存在测试前提(exists,notexists)
婚配测试前提(like)
限制测试前提(any,all)
空值测试前提(isnull)
//复合搜刮前提(and,or,not,())
and:逻辑与运算
and:逻辑或运算
not:逻辑非运算
():可改动优先级的运算符
//子句申明
select子句:指出检索的数据项
from子句:指出检索的数据表
where子句:指出检索的数据前提
groupby子句:指出检索的数据举行汇总
having子句:指出检索的数据举行汇总之前的前提
orderby子句:指出检索的数据前提举行排序
代码:
//一切字段体例显现orders全体纪录
select*fromorders
//按字段显现全体纪录
selectorder_num,order_date,amountfromorders
//按字段显现全体纪录,但撤除反复的纪录
selectorder_num,order_date,amountfromorders
//用sql-expression乘运算盘算列
selectamount,amount*0.08asdiscount_amtfromorders
//用自界说函数盘算指定列
selectorder_num,order_date,amount,f_amt_to_chn(amount)as金额fromorders
select选项太多，代码例子就省略...
-----------------------------------------------------------
2.2子查询SubQuery
语法:select...
from<tablename>
where/havingcolumn测试前提(SubQuery)
//测试前提
对照测试前提(=,,>,<,>=,<=)
局限测试前提(betweeen上限值and下限值)
成员测试前提(in,notin)
存在测试前提(exists,notexists)
婚配测试前提(like)
限制测试前提(any,all)
空值测试前提(isnull)
代码:
//列出没有完成发卖方针10%的发卖职员清单[<测试]
selectnamefromsalesrepswherequota<(0.1*selectsum(target)fromoffices))
//列出公司的发卖方针凌驾各个发卖职员定额总和的发卖点[>测试]
selectcityfromofficeswheretarget>(selectsum(quota)fromsalesrepswhererep_office=office)
//列出凌驾发卖方针的发卖点的营业职员
selectnamefromsalesrepswhereofficein(selectofficefromoffieswheresales>target)
//列出定单年夜于2500元的产物称号
selectdescriptionfromproductswhereexists(
select*fromorderswhereproduct=prodct_idandamount>2500.00
)
//列出完成发卖方针10%的发卖职员清单
selectnamefromsalesrepswhere(0.1*quota)<any(selectamountfromorderswhererep=empl_num)
-----------------------------------------------------------
2.3毗连查询TableJoins
多表毗连范例可分为三类(内/外/交织毗连)
主从表大概父子表举行多表毗连多以主键和外键举行联系关系
Outerjoins(LEFTOUTER,RIGHTOUTER,andFULLOUTERjoins)
leftouterjoin:查询的了局以右边表行数为准
rightouterjoin:查询的了局以右侧表行数为准
2.3.1.内毗连innerjoin
功效:
语法:
SELECTselect_list
FROMtable_1
JOINtable_2
ONjoin_condition_1
[JOINtable_3
ONjoin_condition_2]...
代码:
//没有where子句的内毗连
SELECT*
FROMProducts
INNERJOINSuppliers
ONProducts.SupplierID=Suppliers.SupplierID
//有where子句的内毗连
SELECTp.ProductID,s.SupplierID,p.ProductName,s.CompanyName
FROMProductsp
INNERJOINSupplierss
ONp.SupplierID=s.SupplierID
WHEREp.ProductID<4
-----------------------------------------------------------
2.3.2.外毗连outerjoin
功效:包含三种毗连LEFTOUTER,RIGHTOUTER,andFULLOUTERjoins
leftouter:查询的了局以右边表行数为准
rightouter:查询的了局以右侧表行数为准
语法:select...fromtable1table2where...
代码:
//以Customers表行数为尺度往毗连Orders表
SELECTc.CustomerID,CompanyName
FROMCustomersc
LEFTOUTERJOINOrderso
ONc.CustomerID=o.CustomerID
WHEREo.CustomerIDISNULL
-----------------------------------------------------------
2.3.3.交织毗连crossjoin
功效:以主从表大概父子表之间的主键举行毗连，终极以笛卡尔乘积运算的了局
语法:select...fromtable1crossjointable2where...
代码:
//显现了局以表1行数*表2行数
假定Departments为4行纪录
假定Jobs为3行纪录
上面的显现了局为4*3=12行纪录
SELECTdeptname,jobdescFROMDepartmentsCROSSJOINJobs
//用关头字婚配的交织毗连
oc_head/oc_detail是主从表
oc_head(主键oc_number)
oc_detail(主键oc_number,item_number,ship_date)
SELECTh.customerid,d.item_number,d.ship_date
fromoc_headashCROSSJOINoc_detailasd
whereh.oc_number=d.oc_number
-----------------------------------------------------------
2.4汇总查询GroupQuery
//汇总查询相称于管帐报表中的小计汇总的功效
语法:select...
from<tablename>
groupby<column-name>
代码:
//求出每名发卖职员的发卖金额
selectrep,sum(amount)fromordersgroupbyrep
//每一个发卖点分派了几发卖职员
selectrep_office,count(*)fromsalesrepsgroupbyrep_office
//盘算每名发卖职员的每一个客户和定单金额
selectcust,rep,sum(amount)fromordersgroupbycust,rep
//Having子句使用
selectrep,avg(amount)fromordershavingsum(quota)>3000.00
/**********************************************************/
3.数据修正DATAMODIFYLANGUAGE
3.1拔出数据Insert
3.2修正数据Update
3.3删除数据Delete
-----------------------------------------------------------
3.1拔出数据Insert
3.1.1.单行拔出
语法:insertinto<tablename>[<columnlist,>...]values(<valuelist,>...);
代码:
//不省略字段清单
insertintosalesreps(name,age,empl_no,sales,title,hire_date,rep_office)
values(jacktoms,36,111,0.00,salesmgr,10-05-2010,13)
//省略字段清单
insertintosalesreps
values(jacktoms,36,111,0.00,salesmgr,10-05-2010,13)
3.1.2.多行拔出
语法:insertinto<tablename>[(<columnlist,>...)]values(<valuelist,>...)<selectQuery>;
代码:
//把一批数据批量拔出到一个备份表中
insertintohistory_order(order_num,order_date,amount)
selectorder_num,order_date,amount
fromorderswhereorder_date<01/01/2000
-----------------------------------------------------------
3.2修正数据Update
语法:update<tablename>set(cloumn=expression...)
代码:
//更新一切纪录
updatesalesrepssetquota=1.05*quota
//按前提更新表纪录
updatesalesrepssetquota=1.08*quotawherearea=china
//按子查询更新表纪录
updatecustomerssetcust_rep=105
wherecust_repin(
selctempl_numfromsalesrepswheresales<(0.8*quota)
)
-----------------------------------------------------------
3.3删除数据Delete
语法1:deletefrom<tablename>
代码:
//一切删除纪录
deletefromorders
语法2:truncatetable<tablename>
代码2:
//一切删除纪录
truncatetableorders
//按前提删除纪录
deletefromorderswhereorder_date<01/01/2000
/**********************************************************/
4.数据界说DATADEFINELANGUAGE
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.创建表
语法:
createtable<表名>(
<列名><数据范例>[长度]<,>
<列名...>
)
代码:
//创建公司部门表
createtabletb_basic_dept(
idintnotnull,
namevarchar(20),
chairvarchar(20)
)
4.1.2.删除表
语法:
droptable<表名>
代码:
//删除部门表
droptabletb_basic_dept
-----------------------------------------------------------
4.2列Column
4.2.1.列增加
语法:
altertable<表名>add
<列名><数据范例>[长度]<,>
<列名...>
代码:
altertabletb_basic_deptadd
remarkvarchar(50)
4.2.2.列删除
语法:altertable<表名>dropcolumn<列名>
代码:
altertabletb_basic_deptdropcolumnremark
4.2.3.列修正
语法:altertable<表名>altercolumn
<列名><数据范例>[长度]
代码:
//修正人为列为dec(8,2)
altertabletb_hr_gzaltercolumngzdec(8,2)null
-----------------------------------------------------------
4.3序列Identity
//出格请求
IDENTITY字段数据范例只能是(int,bigint,smallint,tinyint,decimal,ornumeric(x,0))
IDENTITY字段必需是notnull束缚
4.3.1Identity
语法:
IDENTITY(<data_type>[,<seed>,<increment>])AScolumn_name,
代码:
//利用Identity
CREATETABLEMyTable(
key_colintNOTNULLIDENTITY(1,1),
abcchar(1)NOTNULL
)
INSERTINTOMyTableVALUES(a)
INSERTINTOMyTableVALUES(b)
INSERTINTOMyTableVALUES(c)
-----------------------------------------------------------
4.4束缚Constraints
4.4.1缺省束缚(default)
4.4.2非空束缚(notnull)
4.4.3划定规矩束缚(rule)
4.4.4反省束缚(check)
4.4.5独一束缚(unique)
4.4.6主键束缚(primarykey)
4.4.7外键束缚(foreignkey)
4.4.8贸易划定规矩(businessrule)
以上面两个表为例举行演示
createtabletb_hr_bm(
bmvarchar(20)notnull,
remarkvarchar(100)default
)
createtabletb_hr_gz(
idintnotnull,
namevarchar(30)notnull,
hridchar(18)null,
workageintnull,
bmvarchar(20)null,
gzrealnull,
remarkvarchar(100)null
)
hrid=身份证号码
workage=事情年纪
gz=人为金额
-----------------------------------------------------------
4.4.1缺省束缚(default)
语法:CREATEDEFAULTdefault_nameASexpression
代码:CREATEDEFAULTzip_defaultAS94710
-----------------------------------------------------------
4.4.2非空束缚(notnull)
//表的主键和别的必填字段必需为notnull.
语法:createtable(column-namedatatypenotnull...)
代码:createtabletb_hr_gz(idintnotnull,...)
-----------------------------------------------------------
4.4.3划定规矩束缚(rule)
语法:CREATERULErulenameAScondition
代码:
//邮编号码6位100000-999999
//创建一个自界说zip范例
CREATETYPEzipFROMCHAR(6)NOTNULL
//创建一个划定规矩束缚
CREATERULEzip_ruleAS@number>100000and@number<999999
//绑定例则束缚到zip范例
EXECsp_bindrulezip_rule,zip
//使用自界说zip范例
2>CREATETABLEaddress(
cityCHAR(25)NOTNULL,
zip_codeZIP,
streetCHAR(30)NULL
)
-----------------------------------------------------------
4.4.4反省束缚(创建/删除)
//反省束缚创建
语法:
altertablename
addconstraint<反省束缚名>check<取值局限表达式>
代码:
//人为增加取值局限0~1000000
办法1:
createtabletb_hr_gz(
gzrealdefault0.0check(gz>=0andgz<=1000000),
...
)
办法2:
altertabletb_hr_gz
addconstrainttb_hr_gz_ckcheck(gz>=0andgz<=1000000)
//反省束缚删除
语法:
altertablenamedropconstraint<反省束缚名>
代码:
//删除人为的反省束缚
altertabletb_hr_gzdropconstrainttb_hr_gz_ck
-----------------------------------------------------------
4.4.5独一束缚
4.4.5.1.独一束缚增加
语法:
altertablenameaddconstraint<独一束缚名>unique<列名>
代码:
//列如身份证号码是独一的!
altertabletb_hr_gzAddconstrainttb_hr_gz_ukunique(hrid)
4.4.5.2.独一束缚删除
语法:
altertablenamedropconstraint<独一束缚名>
代码:
altertabletb_hr_gzdropconstrainttb_hr_gz_uk
-----------------------------------------------------------
4.4.6主键束缚
4.4.6.1主键束缚增加
语法:
altertabletable_name
addconstraint<主键称号>PrimaryKey<列名>
代码:
createtabletb_hr_bm(
bmvarchar(20)notnull,
remarkvarchar(100)default
)
altertabletb_hr_bm
addconstrainttb_hr_bm_pkPrimaryKey(bm)
4.4.6.2主键束缚删除
语法:
altertabletable_name
dropconstraint<主键称号>
代码:
altertabletable_name
dropconstrainttb_hr_bm_pk
-----------------------------------------------------------
4.4.7外键束缚
4.4.7.1外键束缚增加
语法:
altertable<表名>
addconstraint<外键名>
foreignkey(列名)
references<参考表名><列名>
<ONUPDATEONDELETE(RESTRICTCASCADESETNULLSETDEFAULT)>
//增补申明
经常使用选项是上面3项:
ONUPDATESETNULL//级联更新
ONDELETECASCADE//级联删除
ONDELETESETNULL//级联置空
ONUPDATE(RESTRICTCASCADESETNULLSETDEFAULT)暗示父表更新后,子表的举动
ONDELETE(RESTRICTCASCADESETNULLSETDEFAULT)暗示父表删除后,子表的举动
RESTRICT限定功效:父表一行纪录不克不及更新/删除，当子表有一笔记录以上时
CASCADE级联功效:父表一行纪录纪录更新/删除删除,子表对应一切的纪录主动更新/删除
SETNULL置空功效:父表一行纪录纪录更新/删除删除,子表对应一切的纪录主动为空
SETDEFAULT默许值功效:父表一行纪录纪录更新/删除删除,子表对应一切的纪录主动写进默许值
代码:
创建外键的次要代码
altertabletb_hr_personl_info
addconstrainttb_hr_personl_info__bm_fk
foreignkey(bm)
referencestb_hr_bm(bm)
onupdatecascade
ondeletecascade
//创建参考表部门
createtabletb_hr_bm
(
bmvarchar(20)notnull,
remarkvarchar(100)default
)
altertabletb_hr_bm
addconstrainttb_hr_bm_pkPrimaryKey(bm)
//创建团体信息表
usehr
createtabletb_hr_personl_info
(
useridintnotnull,
usernamevarchar(20)null,
bmvarchar(20)null
)
/*为此表增加主键束缚*/
altertabletb_hr_personl_info
addconstrainttb_hr_personl_info_pkPrimaryKey(userid)
/*为团体信息表增加外键束缚*/
altertabletb_hr_personl_info
addconstrainttb_hr_personl_info__bm_fk
foreignkey(bm)
referencestb_hr_bm(bm)
onupdatecascade
ondeletecascade
-----------------------------------------------------------
4.4.7.2外键束缚删除
语法:
altertable<表名>
dropconstraint<外键名>
代码:
//删除tb_hr_personl_info表的外键
altertabletb_hr_personl_infodropconstrainttb_hr_personl_info__bm_fk;
-----------------------------------------------------------
4.4.8贸易划定规矩(businessrule)
//用触发器大概存储历程来完成
-----------------------------------------------------------
4.5索引Index
//4.5.1创建索引
语法:
createindex<索引名>
on<表名><列名清表>
代码:
createindextb_hr_personl_info_ix
ontb_hr_personl_info(userid)
//4.5.2删除索引
语法:
dropindex<表名><.><索引名>
代码:
//删除索引名tb_hr_personl_info_ix
dropindextb_hr_personl_info.tb_hr_personl_info_ix
-----------------------------------------------------------
4.6视图view
4.6.1视图view的观点：
视图不是表，也不是表数据的备份，在数据库形式中只是select语句的汇合!
-----------------------------------------------------------
4.6.2创建视图CreateView
语法:
CREATEVIEW<viewname>
AS
<SELECTstatement>
WITHCHECKOPTION
代码:
CREATEVIEWvw_customerlist
AS
SELECT*
FROMCustomers
-----------------------------------------------------------
4.6.3查询视图Queryview
语法:select*fromviewname
代码:select*fromvw_customerlist
-----------------------------------------------------------
4.6.4修正视图ALTERVIEW
语法:select*fromviewname
代码:select*fromvw_customerlist
-----------------------------------------------------------
4.6.5视图删除DROPVIEW
//4.6.2视图删除
语法:
dropview<视图名>
代码:
//视图删除v_hr_personl_info
dropviewv_hr_personl_info
-----------------------------------------------------------
4.6.6.过滤视图Filterview
语法:
select*fromviewnamewhere/havingexpressions
代码:
CREATEVIEWBankersMin
AS
SELECTBankerName,BankerState
FROMBankers
whereBankerID<5
SELECT*FROMBankersMin
WHEREBankerState=CA
ORDERBYBankerName
-----------------------------------------------------------
4.6.7.可更新的视图UpdatableView
语法:
CREATEVIEW<viewname>
AS
SELECTstatement
WITHCHECKOPTION
代码:
CREATEVIEWOregonShippers_vw
AS
SELECTShipperID,
CompanyName,
Phone
FROMShippers
WITHCHECKOPTION
//此视图的纪录能够举行delete/update/insert
insertinto<viewname>values(values....)
deletefrom<viewname>where/havingexpressions
update<viewname>setcolumn=values...where/havingexpressions
-----------------------------------------------------------
4.7权限Privilege
4.7.1数据库用户增加
语法:
sp_addlogin[@loginame=]login
[,[@passwd=]password]
[,[@defdb=]database]
[,[@deflanguage=]language]
[,[@sid=]sid]
[,[@encryptopt=]encryption_option]
代码:
数据库testdb下面增加一个上岸用户test,暗码为tt
EXECsp_addlogintest,tt,testdb,us_english
EXECsp_addloginyao,it,mtyjxc,us_english
-----------------------------------------------------------
4.7.2数据库用户删除
语法:DROPLOGIN<上岸称号>
代码:DROPLOGINtest
-----------------------------------------------------------
4.7.3用户权限授与grant
grant语法:
GRANTprivilege[,...]ONobject[,...]
TO{PUBLICGROUPgroupusername}
privilege取值局限以下:
SELECT:会见声明的表/视图的一切列/字段．
INSERT:向声明的表中拔出一切列字段．
UPDATE:更新声明的表一切列/字段．
DELETE:从声明的表中删除一切行．
RULE:在表/视图上界说划定规矩（拜见CREATERULE语句）．
ALL:付与一切权限．
object取值局限以下:
table
view
sequence
PUBLIC:代表是一切用户的简写.
GROUP:将要付与权限的组group
username:将要付与权限的用户名．
假如乐成，前往输入CHANGE信息．
代码:
GRANTallonmtyjxctoyao
-----------------------------------------------------------
7.7.4用户权限排除REVOKE
REVOKE{ALLstatement[,...n]}
FROMsecurity_account[,...n]
ALL:
指定将删除一切合用的权限。
关于语句权限，只要sysadmin流动服务器脚色成员可使用ALL。
关于工具权限，sysadmin流动服务器脚色成员、db_owne流动数据库脚色成员和数据库工具一切者都可使用ALL。
statement:
是要删除其权限的受权语句。语句列表能够包含:
*CREATEDATABASE
*CREATEDEFAULT
*CREATEFUNCTION
*CREATEPROCEDURE
*CREATERULE
*CREATETABLE
*CREATEVIEW
*BACKUPDATABASE
*BACKUPLOG
FROM:
指定平安帐户列表。
security_account:
是以后数据库内将要被删除权限的平安帐户。
平安帐户能够是:SQLServer用户,SQLServer脚色。
代码:
REVOKEallONmtyjxc.*TOyao
REVOKEallONmtyjxcTOyao
/**********************************************************/
5.数据库函数Functions
5.1转换函数DataConvertFunctions
5.2会萃函数AggregateFunctions
5.3字符函数charFunctions
5.4日期函数DateFunctions
5.5数学函数MathFunctions
5.6剖析函数AnalyticalFunctions
-----------------------------------------------------------
5.1转换函数DataConvertFunctions
5.1.1CAST()
功效:数据范例转换
语法:CAST(expressionASdata_type)
代码:
SELECTBillingDate,
BillingTotal,
CAST(BillingDateASvarchar)ASvarcharDate,
CAST(BillingTotalASint)ASintegerTotal,
CAST(BillingTotalASvarchar)ASvarcharTotal
FROMBillings
-----------------------------------------------------------
5.1.2COALESCE()
功效:前往表达式列表中第一个非空值表达式的值
语法:COALESCE(expression1,expression2,...expressionN)
代码:
SELECTBankerName,
COALESCE(CAST(BillingTotalASvarchar),NoBillings)ASBillingTotal
FROMBankersLEFTJOINBillings
ONBankers.BankerID=Billings.BankerID
ORDERBYBankerName
-----------------------------------------------------------
5.1.3CONVERT()
功效:把表达式值转换为指定sytle的数据范例
语法:CONVERT(data_type(<length>),expression,<style>)
代码:
//日期作风转换
datetime转指定日期格局stylenumber清单
NumberStyleNumberOutputTypeStyle
-0or100Defaultmonddyyyyhh:miAM(orPM)
1101USAmm/dd/yyyy
2102ANSIyyyy.mm.dd
3103British/Frenchdd/mm/yyyy
4104Germandd.mm.yyyy
5105Italiandd-mm-yyyy
6106-ddmonyyyy
7107-mondd,yyyy
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd
14114-hh:mi:ss:mmm(24h)
//字符串转数字
CONVERT(INTEGER,12345)
//字符转日期
CONVERT(datetime,20000704)
CREATETABLEmy_date(Col1datetime)
GO
INSERTINTOmy_dateVALUES(CONVERT(char(10),GETDATE(),112))
GO
droptablemy_date;
GO
-----------------------------------------------------------
5.1.4ISNULL()
功效:反省check_expression是空值，就用replacement_value替换
语法:ISNULL(check_expression,replacement_value)
代码:
SELECTBillingDate,
ISNULL(BillingDate,1900-01-01)ASNewDate
FROMBillings
-----------------------------------------------------------
5.1.5NULLIF()
功效:两个表达式相称，前往null,不然前往第1个表达式
语法:ISNULL(expression1,expression2)
代码:
DECLARE@Value1int
DECLARE@Value2int
SET@Value1=55
SET@Value2=955
SELECTNULLIF(@Value1,@Value2)
GO
输入
55
DECLARE@Value1int
DECLARE@Value2int
SET@Value1=55
SET@Value2=55
SELECTNULLIF(@Value1,@Value2)
GO
输入
NULL
-----------------------------------------------------------
5.2会萃函数AggregateFunctions
语法:selectAggregateFunctions(column-name)
sum(column-name):盘算字段总和
avg(column-name):盘算字段均匀值
min(column-name):盘算字段最小值
max(column-name):盘算字段最年夜值
count(column-name):盘算字段非空值的个数
count(*):盘算查询了局的纪录个数
代码:
//usepubs
selectsum(qty)assum_qty,
avg(qty)asavg_qty,
min(qty)asmin_qty,
max(qty)asmax_qty,
count(qty)ascount_qty,
count(*)astotal_qty
fromsales
-----------------------------------------------------------
5.3字符函数charFunctions
1.ASCII()
//函数前往字符表达式最左端字符的ASCII码值
2.Char()
//函数用于将ASCII码转换为字符--假如没有输出0~255之间的ASCII码值CHAR函数会前往一个NULL
3.CHARINDEX()
//函数前往字符串中某个指定的子串呈现的入手下手地位
4.DIFFERENCE()
5.FORMATMESSAGE()
6.LEFT()
7.LEN()
8.LOWER()
//函数把字符串全体转换为小写
9.LTRIM()
//函数把字符串头部的空格往失落
10.nchar()
11.PATINDEX()
12.QUOTENAME()
13.REPLACE()
//函数前往被交换了指定子串的字符串
14.REPLICATE()
/函数前往一个反复指定次数的字符串
15.REVERSE()
//函数将指定的字符串的字符分列按次倒置
16.Right()
17.RTRIM()
/函数把字符串尾部的空格往失落
18.SOUNDEX()
19.SPACE()
//函数前往一个有指定长度的空缺字符串
20.STR()
//函数把数值型数据转换为字符型数据
21.STUFF()
//函数用另外一子串交换字符串指定地位长度的子串
22.SUBSTRING()
//函数前往子字符串
23.UNICODE()
24.UPPER()
//函数把字符串全体转换为年夜写
-----------------------------------------------------------
5.4日期函数DateFunctions
5.4.1.CURRENT_TIMESTAMP
功效:
失掉以后数据库的日期
代码:
//间接失掉以后日期
SELECTCURRENT_TIMESTAMP
go
//挪用变量中确当前日期
DECLARE@todaydatetime
SELECT@today=current_timestamp
select@today
go
-----------------------------------------------------------
5.4.2.日期盘算Datecalculation
功效:日期盘算
代码:
DECLARE@MonthCharVarChar(2),@DayCharVarChar(2),@DateOutChar(8)
SET@MonthChar=CAST(MONTH(GETDATE())ASVarChar(2))
SET@DayChar=CAST(DAY(GETDATE())ASVarChar(2))
--主动补齐月份到2位
IFLEN(@MonthChar)=1
SET@MonthChar=0+@MonthChar
IFLEN(@DayChar)=1
SET@DayChar=0+@DayChar
--天生日期字符串
SET@DateOut=@MonthChar+@DayChar+CAST(YEAR(GETDATE())ASChar(4))
SELECT@DateOut
GO
运转了局是mmddyyyy格局的字符串
-----------------------------------------------------------
5.4.3.DATEADD()
功效:日期相加大概相减n天后的日期
语法:DATEADD(what_to_add,number_to_add,date_to_add_it_to)
代码:
//4-29-2009加90天，保留到day
SELECTDATEADD(DY,90,4-29-2009)
GO
//4-29-2009减60天，保留到day
SELECTDATEADD(DY,-60,4-29-2009)
GO
-----------------------------------------------------------
5.4.4.DATEDIFF()
功效:日期相加大概相减n天后的日期
语法:DATEDIFF(datepart,startdate,enddate)
datepart列表:
day:单元=天
month:单元=月
year:单元=年
hour:单元=小时
minute:单元=分
second:单元=秒
week:单元=周
代码:
//10/01/2009国庆到明天的天数
SELECTDATEDIFF(day,10/1/2009,CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到明天的月数
SELECTDATEDIFF(month,10/1/2009,CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到明天的年纪
SELECTDATEDIFF(year,10/1/2009,CURRENT_TIMESTAMP)
GO
//10/01/2009国庆到明天的周数
SELECTDATEDIFF(week,10/1/2009,CURRENT_TIMESTAMP)
GO
-----------------------------------------------------------
5.4.5.DATEFIRST()
功效:设置大概查询一周的第一天
SELECT@@DATEFIRSTFirstDayoftheWeek
GO
valueis7
SELECTDATEPART(weekday,CAST(20091001ASDATETIME)+@@DATEFIRST);
GO
valueis3
-----------------------------------------------------------
6.DATEFORMAT()
功效:设置日期格局
语法:SETDATEFORMAT<format>
format(ymd,mdy,dmy)
代码:setdataformatmdy
-----------------------------------------------------------
7.DATENAME()
功效:日期date按datepart作风以后酿成字符串
语法:DATENAME(datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代码:
selectdatename(day,CURRENT_TIMESTAMP)
selectdatename(month,CURRENT_TIMESTAMP)
selectdatename(year,CURRENT_TIMESTAMP)
selectdatename(hour,CURRENT_TIMESTAMP)
selectdatename(minute,CURRENT_TIMESTAMP)
selectdatename(week,CURRENT_TIMESTAMP)
selectdatename(weekday,CURRENT_TIMESTAMP)
-----------------------------------------------------------
8.DATEPART()
功效:日期date按datepart作风以后酿成字符串
语法:DATENAME(datepart,date)
datepart列表(day,month,year,hour,minute,second,week,weekday)
代码:
-----------------------------------------------------------
9.Day()
功效:求日期的天
语法:day(date)
代码:selectday(CURRENT_TIMESTAMP)
-----------------------------------------------------------
10.GETDATE()
功效:求以后日期和工夫
语法:GETDATE()
代码:selectGETDATE()和selectCURRENT_TIMESTAMP不异
-----------------------------------------------------------
11.GETUTCDATE()
-----------------------------------------------------------
12.ISDATE()
-----------------------------------------------------------
13.MONTH()
功效:求日期的月
语法:MONTH(date)
代码:selectmonth(CURRENT_TIMESTAMP)
-----------------------------------------------------------
14.Year()
功效:求日期的年
语法:Year(date)
代码:selectYear(CURRENT_TIMESTAMP)
-----------------------------------------------------------
5.5数学函数MathFunctions
1.ABS()
2.ACOS()
3.ASIN()
4.ATAN()
5.CEILING()
6.COS()
7.COT()
8.DEGREES()
9.EXP()
10.FLOOR()
11.ISNUMERIC()
12.LOG()
13.LOG10()
14.PI()
15.Power()
16.RADIANS()
11.17.RAND()
18.ROUND()
19.SIGN()
20.Sin()
21.SQRT()
22.SQUARE()
23.TAN()
-----------------------------------------------------------
5.6剖析函数AnalyticalFunctions
1.COMPUTE()
2.CUBE()
3.DENSE_RANK()
4.GROUPING()
5.NTILE()
6.PARTITION()
7.PIVOT()
8.ROLLUP()
9.ROW_NUMBER()
10.STDEV()
11.STDEVP()
12.VAR()
13.VARP()
/**********************************************************/
6.TransactSQL
6.1数据范例DataTypes
6.2剧本语法sytanx
6.3剧本游标Cursor
6.4存储历程Procedure
6.5存储函数Function
6.6触发器Trigger
6.7事件Transaction
6.8别的other
/**********************************************************/
6.1数据范例DataTypes
1.bigint
2.bit
3.bitwiseoperators
4.Char
5.collate
6.CreateType
7.Datatype
8.DateType
9.datetime
10.decimal
11.Float
12.FULLTEXT
13.integer
14.LargeText
15.money
16.nchar
17.nVarChar
18.OPENROWSET
19.READTEXT
20.smalldatetime
21.Smallint
22.SQL_VARIANT
23.text
24.TEXTPTR
25.timestamp
26.VARBINARY
27.VARCHAR
28.WRITETEXT
29.Unicode
-----------------------------------------------------------
6.2剧本语法syntax
6.2.0部分/全局变量界说
部分变量(以@开首)
格局:declare@变量名数据范例
代码:declare@xint
全局变量(必需以@@开首)
格局:declare@@变量名范例
代码:select@@id=10010001
6.2.1块语句
格局:
begin
...
end
-----------------------------------------------------------
6.2.2赋值语句set/select
set@id=10010001
select@id=10010001
6.2.3前提语句(if/case)
6.2.3.1if语句
declare@xint@yint@zint
select@x=1@y=2@z=3
if@x>@y
printx>y--打印字符串x>y
elseif@y>@z
printy>z
elseprintz>y
6.2.3.2CASE语句
--CASE
复制代码代码以下:
usepangu
updateemployee
sete_wage=
case
whenjob_level=1thene_wage*1.08
whenjob_level=2thene_wage*1.07
whenjob_level=3thene_wage*1.06
else
e_wage*1.05
end
6.2.4轮回语句(while)
--WHILE
复制代码代码以下:
declare@xint@yint@cint
select@x=1@y=1
while@x<3
begin
print@x--打印变量x的值
while@y<3
begin
select@c=100*@x+@y
print@c--打印变量c的值
select@y=@y+1
end
select@x=@x+1
select@y=1
end
6.2.5准时实行(waitfor)
--WAITFOR
--例守候1小时2分零3秒后才实行SELECT语句
waitfordelay01:02:03
select*fromemployee
--例比及早晨11点零8分后才实行SELECT语句
waitfortime23:08:00
select*fromemployee
-----------------------------------------------------------
6.3剧本游标Cursor
//游标使用按次
1.DECLARE--为查询设定游标
2.OPEN--检索查询了局翻开一个游标
3.FETCH--检索一行查询了局
4.CLOSE/DEALLOCATE--封闭游标大概从头分派游标
语法:
DECLARE<游标称号>CURSORFOR(selectsql)
OPEN<游标称号>
while@@fetch_status=0
begin
FETCHNEXTFROM<游标称号>INTO<变量名清单>
{别的代码处置}
end
CLOSE<游标称号>
代码1:
复制代码代码以下:
/*带游标的存储历程*/
createprocedurep_fill_remark_tb_hr_gz
as
declare@id1int
declare@name1varchar(30)
declare@bm1varchar(20)
begin
declarecursor1cursorforselectid,name,bmfromtb_hr_gz
opencursor1
fetchnextfromcursor1into@id1,@name1,@bm1
while@@fetch_status0
begin
updatetb_hr_gzsetremark=@name1+-+@bm1whereid=@id1
fetchnextfromcursor1into@id1,@name1,@bm1
end
closecursor1
end
//测试带游标的存储历程
EXECdbo.p_fill_remark_tb_hr_gz
-----------------------------------------------------------
6.4存储历程Procedure
//存储历程创建
语法:
createprocedure<存储历程名>(
[输出参数列表],[前往参数列表output]
)
as
[部分变量界说]
begin
{语句体}
end
代码:
createprocedurep_update_name_tb_hr_gz(@idint,@newnamevarchar(30))
as
begin
if(exists(select*fromtb_hr_gzwhereid=@id))
begin
updatetb_hr_gzsetname=@newnamewhereid=@id
end
end
//测试
EXECdbo.p_update_name_tb_hr_gz112,chenglei
//存储历程删除
语法:
dropprocedure<存储历程名>
代码:
dropprocedurep_update_name_tb_hr_gz
-----------------------------------------------------------
6.5存储函数Function
//存储函数创建
语法:
CREATEFUNCTION<函数名>(参数变量列表)
[前往值RETURNS数据范例]
AS
BEGIN
{函数代码体....}
END
代码:
复制代码代码以下:
//函数f_amt_to_eng()功效:数字金额转换为英笔墨母金额
CREATEFUNCTIONf_amt_to_eng(@numnumeric(15,2))
RETURNSvarchar(400)WITHENCRYPTION
AS
BEGIN
DECLARE@iint,@hundredsint,@tenthint,@oneint
DECLARE@thousandint,@millionint,@billionint
DECLARE@numbersvarchar(400),@svarchar(15),@resultvarchar(400)
SET@numbers=onetwothreefourfive
+sixseveneightnineten
+eleventwelvethirteenfourteenfifteen
+sixteenseventeeneighteennineteen
+twentythirtyfortyfifty
+sixtyseventyeightyninety
SET@s=RIGHT(000000000000000+CAST(@numASvarchar(15)),15)
SET@billion=CAST(SUBSTRING(@s,1,3)ASint)--将12位整数分红4段:十亿、百万、千、百十个
SET@million=CAST(SUBSTRING(@s,4,3)ASint)
SET@thousand=CAST(SUBSTRING(@s,7,3)ASint)
SET@result=
SET@i=0
WHILE@i<=3
BEGIN
SET@hundreds=CAST(SUBSTRING(@s,@i*3+1,1)ASint)--百位0-9
SET@tenth=CAST(SUBSTRING(@s,@i*3+2,1)ASint)
SET@one=(CASE@tenthWHEN1THEN10ELSE0END)+CAST(SUBSTRING(@s,@i*3+3,1)ASint)--个位0-19
SET@tenth=(CASEWHEN@tenth<=1THEN0ELSE@tenthEND)--十位0、2-9
IF(@i=1and@billion>0and(@million>0or@thousand>0or@hundreds>0))or
(@i=2and(@billion>0or@million>0)and(@thousand>0or@hundreds>0))or
(@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds>0))
SET@result=@result+,--百位不是0则每段之间加毗连符,
IF(@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds=0and(@tenth>0or@one>0)))
SET@result=@result+and--百位是0则加毗连符AND
IF@hundreds>0
SET@result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+hundred
IF@tenth>=2and@tenth<=9
BEGIN
IF@hundreds>0
SET@result=@result+and
SET@result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF@one>=1and@one<=19
BEGIN
IF@tenth>0
SET@result=@result+-
ELSE
IF@hundreds>0
SET@result=@result+and
SET@result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF@i=0and@billion>0
SET@result=@result+billion
IF@i=1and@million>0
SET@result=@result+million
IF@i=2and@thousand>0
SET@result=@result+thousand
SET@i=@i+1
END
IFSUBSTRING(@s,14,2)00
BEGIN
SET@result=@result+AND
IFSUBSTRING(@s,14,1)=0
SET@result=@result+zero
ELSE
SET@result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1)ASint)*10-9,10))
IFSUBSTRING(@s,15,1)0
SET@result=@result++RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1)ASint)*10-9,10))
END
RETURN(@result)
END
复制代码代码以下:
CREATEFUNCTIONf_amt_to_chn(@numnumeric(14,2))
RETURNSvarchar(100)WITHENCRYPTION
AS
BEGIN
DECLARE@n_dataVARCHAR(20),@c_dataVARCHAR(100),@n_strVARCHAR(10),@iint
SET@n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100)ASbigint)ASvarchar(20)),14)
SET@c_data=
SET@i=1
WHILE@i<=14
BEGIN
SET@n_str=SUBSTRING(@n_data,@i,1)
IF@n_str
BEGIN
IFnot((SUBSTRING(@n_data,@i,2)=00)or((@n_str=0)and((@i=4)or(@i=8)or(@i=12)or(@i=14))))
SET@c_data=@c_data+SUBSTRING(零壹贰叁肆伍陆柒捌玖,CAST(@n_strASint)+1,1)
IFnot((@n_str=0)and(@i4)and(@i8)and(@i12))
SET@c_data=@c_data+SUBSTRING(仟佰拾亿仟佰拾万仟佰拾元角分,@i,1)
IFSUBSTRING(@c_data,LEN(@c_data)-1,2)=亿万
SET@c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET@i=@i+1
END
IF@num<0
SET@c_data=负+@c_data
IF@num=0
SET@c_data=零元
IF@n_str=0
SET@c_data=@c_data+整
RETURN(@c_data)
END
//测试函数
selectname,gz,dbo.f_amt_to_chn(gz)as中文金额,dbo.f_amt_to_eng(gz)as英文金额fromtb_hr_gz
//删除函数
语法:
dropfunction<函数称号>
代码:
dropfunctionf_num_to_eng
-----------------------------------------------------------
6.6触发器Trigger
22.1.Trigger(14)22.10.Triggerorder(2)
22.2.AlterTrigger(4)22.11.Droptrigger(2)
22.3.Triggerforafter(4)22.12.COLUMNS_UPDATED(1)
22.4.TriggerforDelete(4)22.13.Updatefunction(3)
22.5.Triggerforinsert(1)22.14.Deletedtable(2)
22.6.Triggerforupdate(4)22.15.Insertedtable(5)
22.7.Triggerondatabase(2)22.16.RECURSIVE_TRIGGERS(1)
22.8.Triggeronserver(1)22.17.Utilitytrigger(4)
22.9.Triggeronview(3)
//触发器创建
语法:
createtrigger<触发器称号>on<表名>
as
[界说变量]
begin
{代码块...}
end
代码0:
复制代码代码以下:
createtriggertg_tb_hr_bmontb_hr_bm
forinsert,update,delete
as
declare@bm_dvarchar(20)
declare@bm_ivarchar(20)
begin
set@bm_d=(selectbmfromdeleted)
set@bm_i=(selectbmfrominserted)
ifexists(select*fromtb_hr_gz,deletedwhere(tb_hr_gz.bm=deleted.bm))
begin
updatetb_hr_gzsetbm=wherebm=@bm_d
end
ifupdate(bm)
begin
updatetb_hr_gzsetbm=@bm_iwherebm=@bm_i
end
end
//删除触发器
语法:
droptrigger<触发器称号>
代码:
droptriggertg_w_house_center
-----------------------------------------------------------
6.7事件Transaction
事件(COMMIT/ROLLBACK)
SETTRANSACTION--界说以后事件数据会见特性
COMMIT--提交以后事件
ROLLBACK--作废以后事件如果互联网服务提供商，支撑数据的云服务，或它们之间任一点网络被堵塞或中断，他们就会遇到与数据延迟或应用程序故障有关的问题。如果问题发生在企业内部，解决方案提供商可以排除故障找出原因。 分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。 代替了原来VB式的错误判断。比Oracle高级不少。 其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！ 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 SP4包括用于以下SQLServer2000组件的程序包：Database组件（下载文件：SQL2000-KB884525-SP4-x86.EXE）更新SQLServer2000的32位Database组件，包括数据库引擎、复制、客户端连接组件及工具。有关其他信息，请参阅ReadmeSql2k32Sp4.htm。AnalysisServices组件（下载文件：SQL2000.AS-KB884525-SP4-x86.EXE）更新SQLServer2000的32位AnalysisServices。 习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。 学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!
页:
[1]