SQL Server基本操作语法.docx
《SQL Server基本操作语法.docx》由会员分享，可在线阅读，更多相关《SQL Server基本操作语法.docx（34页珍藏版）》请在冰豆网上搜索。
SQLServer基本操作语法
数据基本操作语句简介
数据库的物理实现一般包括：
●创建数据库
●创建表
●添加各种约束
●创建数据库的登录帐户并授权
●创建数据库或表时一般需要预先检测是否存在该对象
●数据库从master系统数据库的sysdatabases表中查询
●表从该数据库的系统表sysobjects表中查询
●1、说明：
创建数据库
●CREATEDATABASEdatabase-name
●2、说明：
删除数据库
●dropdatabasedbname
●3、说明：
备份sqlserver
●---创建备份数据的device
●USEmaster
●EXECsp_addumpdevice'disk','testBack','c:
\mssql7backup\MyNwind_1.dat'
●---开始备份
●BACKUPDATABASEpubsTOtestBack
●4、说明：
创建新表
●createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）
●根据已有的表创建新表：
●A：
createtabletab_newliketab_old（使用旧表创建新表）
●B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
●5、说明：
删除新表
●droptabletabname
●6、说明：
增加一个列
●Altertabletabnameaddcolumncoltype
●注：
列增加后将不能删除。
DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
●7、说明：
添加主键：
Altertabletabnameaddprimarykey（col）
●说明：
删除主键：
Altertabletabnamedropprimarykey（col）
●8、说明：
创建索引：
create[unique]indexidxnameontabname（col….）
●删除索引：
dropindexidxname
●注：
索引是不可更改的，想更改必须删除重新建。
●9、说明：
创建视图：
createviewviewnameasselectstatement
●删除视图：
dropviewviewname
●10、说明：
使用外连接
●A、leftouterjoin：
●左外连接（左连接）：
结果集几包括连接表的匹配行，也包括左连接表的所有行。
●SQL:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
●B：
rightouterjoin:
●右外连接（右连接）：
结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
●C：
fullouterjoin：
●全外连接：
不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
●11、说明：
几个简单的基本的sql语句
●选择：
select*fromtable1where范围
●插入：
insertintotable1（field1,field2）values（value1,value2）
●删除：
deletefromtable1where范围
●更新：
updatetable1setfield1=value1where范围
●查找：
select*fromtable1wherefield1like’%value1%’---like的语法很精妙!
●排序：
select*fromtable1orderbyfield1,field2[desc]
●总数：
selectcount*astotalcountfromtable1
●求和：
selectsum（field1）assumvaluefromtable1
●平均：
selectavg（field1）asavgvaluefromtable1
●最大：
selectmax（field1）asmaxvaluefromtable1
●最小：
selectmin（field1）asminvaluefromtable1
●12、说明：
几个高级查询运算词
●A：
UNION运算符
●UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。
当ALL随UNION一起使用时（即UNIONALL），不消除重复行。
两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。
创建登陆账户
EXECsp_addlogin'qin','123456'//SQL登录帐户
EXECsp_grantlogin'jbtraining\S26301'//添加Windows登录帐户
EXECsp_grantdbaccess‘登录帐户名’，’数据库用户名’//创建数据库用户
USEstuDB
GO
EXECsp_grantdbaccess
'jbtraining\S26301','S26301DBUser'
EXECsp_grantdbaccess
'zhangsan','zhangsanDBUser'
数据库的用户授权
GRANT权限[ON表名]TO数据库用户
USEstuDB
GO
/*--为zhangsanDBUser分配对表stuInfo的select,insert,update权限--*/
GRANTselect,insert,update
ONstuInfoTOzhangsanDBUser
/*--为S26301DBUser分配建表的权限--*/
GRANTcreatetableTOS26301DBUser
创建数据库
CREATEDATABASEstuDB
ONPRIMARY--默认就属于PRIMARY主文件组，可省略
（
NAME='stuDB_data',--主数据文件的逻辑名
FILENAME='D:
\project\stuDB_data.mdf',--主数据文件的物理名
SIZE=5mb,--主数据文件初始大小
MAXSIZE=100mb,--主数据文件增长的最大值
FILEGROWTH=15%--主数据文件的增长率
）
LOGON//日志文件描述
（
NAME='stuDB_log',
FILENAME='D:
\project\stuDB_log.ldf',
SIZE=2mb,
FILEGROWTH=1MB
）
GO
约束
ALTERTABLE表名
ADDCONSTRAINT约束名约束类型具体的约束说明
●约束名的取名规则推荐采用：
约束类型_约束字段
●主键（PrimaryKey）约束：
如PK_stuNo
●唯一（UniqueKey）约束：
如UQ_stuID
●默认（DefaultKey）约束：
如DF_stuAddress
●检查（CheckKey）约束：
如CK_stuAge
●外键（ForeignKey）约束：
如FK_stuNo
添加
ALTERTABLEstuInfo
ADDCONSTRAINTPK_stuNoPRIMARYKEY（stuNo）
//添加主键约束（stuNo作为主键）
ALTERTABLEstuInfo
ADDCONSTRAINTUQ_stuIDUNIQUE（stuID）
//添加唯一约束（因为每人的身份证号全国唯一）
ALTERTABLEstuInfo
ADDCONSTRAINTDF_stuAddress
DEFAULT（'地址不详'）FORstuAddress
//添加默认约束（如果地址不填，默认为“地址不详”）
ALTERTABLEstuInfo
ADDCONSTRAINTCK_stuAge
CHECK（stuAgeBETWEEN15AND40）
//添加检查check约束，要求年龄只能在15－40岁之间
ALTERTABLEstuMarks
ADDCONSTRAINTFK_stuNo
FOREIGNKEY（stuNo）REFERENCESstuInfo（stuNo）
//添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）
GO
删除
ALTERTABLE表名
DROPCONSTRAINT约束名
ALTERTABLEstuInfo
DROPCONSTRAINTDF_stuAddress
删除表
Truncatetable用户表//TRUNCATETABLE不能用于参与了索引视图的表
Deletefrom用户表
Droptable用户表
插入
insertintobookvalues（'十万个为什么','文学出版社','300.00','A0006'）
//正常的全插入语句（将所有的数据信息全部插入到表内）
查询
Select*frombook//最简单的查询
SQL>SELECTStudentID,ScoreFROMSCoreWHEREScore
BETWEEN60AND80
//把某一字段中内容在特定范围内的记录查询出来
SQL>SELECTSNameAS学员姓名,SAddressAs地址FROMStudentsWHERESAddressIN（'北京','广州','上海'）
//把某一字段中内容与所列出的查询内容列表匹配的记录查询出来
SQL>selectsum（price）总和frombookwherebookconcern='文学出版社'
//求bookconcern字段的值为‘文学出版社’的price字段的值之和
SQL>selectavg（price）平均值frombookwhereprice>200.00
selectmax（price）最贵的,min（price）最便宜的,count（*）frombook
selectbookconcern,avg（price）frombookgroupbybookconcern
//对bookconcern（出版社）进行排序，并且计算出每个出版社下所有price（图书的价格）的平均值
selectbookname,column_bookID,avg（price）frombookgroupbycolumn_bookID,bookname
//多列分组查询多列分组查询是按照最右边的列进行先分组,一次向左进行
selectbookconcern,avg（price）frombookgroupbybookconcernhavingcount（bookconcern）>1
//加入having条件
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
●WHERE子句从数据源中去掉不符合其搜索条件的数据
●GROUPBY子句搜集数据行到各个组中，统计函数为各个组计算统计值
●HAVING子句去掉不符合其组搜索条件的各组数据行
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
连接查询
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
●内联结（INNERJOIN）
●外联结
——左外联结（LEFTJOIN）
——右外联结（RIGHTJOIN）
——完整外联结（FULLJOIN）
●交叉联结（CROSSJOIN）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
内链接
selectusers.用户名称,book.图书名称,reads.借阅时间,reads.归还时间from
借阅表asreads
innerjoin图书表asbookon（reads.图书ID=book.图书ID）
innerjoin用户表asuserson（reads.用户ID=users.用户ID）
查询结果
左连接
selectreads.借阅ID,book.图书名称,book.图书简介from借阅表asreadsleftjoin图书表asbookonreads.图书ID=book.图书ID
selectreads.借阅ID,book.图书名称,book.图书简介from图书表asbookleftjoin借阅表asreadsonreads.图书ID=book.图书ID
//由此可以看出当表的顺序变化时，左连接查询结果发生的变化
查询结果
右连接
selectreads.借阅ID,book.图书名称,book.图书简介
from借阅表asreads
rightouterjoin图书表asbook
onreads.图书ID=book.图书ID
selectreads.借阅ID,book.图书名称,book.图书简介
from图书表asbook
rightouterjoin借阅表asreads
onreads.图书ID=book.图书ID
select图书名称,identity（int,1,1）asIDintoaafrom图书表select图书名称as图书名称奇数列总汇fromaawhereID%2<>0
select图书名称as图书名称偶数列总汇fromaawhereID%2=0
//复制原表的某列到一个新表内，并给新表增加一个标识列，对新建的表进行奇偶分别取出
原表为
INSERTINTOAccount（Account.CardID,Account.Score）
SELECTUsers.CardID,NULLFROMUsers
LEFTJOINAccountON（Account.CardID=Users.CardID）
WHEREAccount.CardIDISNULL
//当插入列为NULL的时候，结果为下
INSERTINTOAccount（Account.CardID,Account.Score）
SELECTUsers.CardID,2FROMUsers
LEFTJOINAccountON（Account.CardID=Users.CardID）
WHEREAccount.CardIDISNULL
//当插入的列为
【deletefromAccountwhereCountIDisnull】
//用来多次执行此操作的时候删除冗余数据
selectuserNameintounamefromUsers
//此语句可以将以前的表中数据列添加到新表中
selectCountID,CardID,ScoreintounamefromAccountwhereCardIDbetween16and29groupbyCountID,CardID,ScoreorderbyCardIDdesc
原表
结果表—>uname
排序
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT<列名>
FROM<表名>
[WHERE<查询条件表达式>]
[ORDERBY<排序的列名>[ASC或DESC]]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select*from图书表orderby图书价格
select*from图书表orderby图书价格desc
select*from图书表where图书价格>900
//增加查询条件
select*from图书表where图书价格isnull
//使用null条件可以查询
selecttop3*from图书表
//显示前三行数据
selecttop20percent*from图书表
//按百分制显示
select图书价格+1from图书表
//为图书价格这列数据值统一加1
update图书表set图书价格=500where图书ID='B001'
//修改数据行的某列信息
子查询
select*from图书表where图书价格>（select图书价格from图书表where图书名称='易经'）
select*from借阅表
select*from用户表
select用户名称from用户表where用户IDin（select用户IDfrom借阅表）
//范围查询
usemaster
ifexists（select*fromsysDatabaseswherename='pei'）
begin
dropdatabasepei
createdatabasepei
end
go
//此操作为查看数据库是否存在，如果存在则删除原有数据库并新建数据库
IFEXISTS（SELECT*FROMstuMarksWHEREwrittenExam>80）
BEGIN
print'本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：
'
UPDATEstuMarksSETwrittenExam=writtenExam+2
SELECT*FROMstumarks
END
ELSE
BEGIN
print'本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩：
'
UPDATEstuMarksSETwrittenExam=writtenExam+5
SELECT*FROMstumarks
END
GO
/*--采用EXISTS子查询，进行酌情加分--*/
事务
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元
●数据库事务具有如下特性：
●原子性
●一致性
●隔离性
●持久性
以上四个特性也称为ACID特性
●T-SQL使用下列语句来管理事务：
●开始事务：
BEGINTRANSACTION
●提交事务：
COMMITTRANSACTION
●回滚（撤销）事务：
ROLLBACKTRANSACTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BEGINTRANSACTION//开始事务
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE@errorSumINT
SET@errorSum=0--初始化为，即无错误
/*--转账：
张三的账户少元，李四的账户多元*/
UPDATEbankSETbalance=balance-10000
WHEREuID=2
SET@errorSum=@errorSum+@@error
UPDATEbankSETbalance=balance+1000
WHEREuID=3
SET@errorSum=@errorSum+@@error--累计是否有错误
IF@errorSum<>0--如果有错误
BEGIN
print'交易失败，回滚事务'
ROLLBACKTRANSACTION//如果出错，回滚事务
END
ELSE
BEGIN
print'交易成功，提交事务，写入硬盘，永久的保存'
COMMITTRANSACTION//如果成功，提交事务
END
GO
print'查看转账事务后的余额'
SELECT*FROMbank
GO
BEGINTRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE@errorSumINT
SET@errorSum=0--初始化为，即无错误
/*--转账：
张三的账户少元，李四的账户多元*/
UPDATEbankSETbalance=balance+10000
WHEREuID=2
SET@errorSum=@errorSum+@@error
UPDATEbankSETbalance=balance-10000
WHEREuID=3
SET@errorSum=@errorSum+@@error--累计是否有错误
IF@errorSum<>0--如果有错误
BEGIN
print'交易失败，回滚事务'
ROLLBACKTRANSACTION
END
ELSE
BEGIN
print'交易成功，提交事务，写入硬盘，永久的保存'
COMMITTRANSACTION
END
GO
print'查看转账事务后的余额'
SELECT*FROMbank
GO
视图
createviewchakan
as
selectu.用户名称,t.图书名称,t.图书简介from借阅表asinnerjoin用户表asuon
升级会员 