SQL语句.docx
《SQL语句.docx》由会员分享，可在线阅读，更多相关《SQL语句.docx（15页珍藏版）》请在冰豆网上搜索。
SQL语句
//书
//书
connectionconn
conn.setAuto（false）//表示手动提交
mit//提交
conn.rollback（）;//事务回滚
--内联接
usepubs
selecta.au_fname,a.au_lname,p.pub_name
fromauthorsainnerjoinpublishersp
ona.city=p.city
orderbyp.pub_nameasc,
a.au_lnameasc,
a.au_fnameasc
--左外联接
usepubs
selecta.au_fname,a.au_lname,p.pub_name
fromauthorsaleftjoinpublishersp
ona.city=p.city
orderbyp.pub_nameasc,
a.au_lnameasc,
a.au_fnameasc
--使用子查询
USEpubs
GO
SELECTdistinctpub_name
FROMpublishers
WHEREpub_idIN
（SELECTpub_id
FROMtitles
WHEREtype='business'）
GO
--如果平均价格少于$30，WHILE循环就将价格加倍，然后选择最高价。
--如果最高价少于或等于$50，WHILE循环重新启动并再次将价格加倍。
--该循环不断地将价格加倍直到最高价格超过$50
USEpubs
GO
WHILE（SELECTAVG（price）FROMtitles）<$30
BEGIN
UPDATEtitles
SETprice=price*2
SELECTMAX（price）FROMtitles
IF（SELECTMAX（price）FROMtitles）>$50
BREAK
ELSE
CONTINUE
END
-=--------------------------------------------------------------------------------------------------
--如果平均价格少于$30，WHILE循环就将价格加倍，然后选择最高价。
--如果最高价少于或等于$50，WHILE循环重新启动并再次将价格加倍。
--该循环不断地将价格加倍直到最高价格超过$50
USEpubs
GO
WHILE（SELECTAVG（price）FROMtitles）<$30
BEGIN
UPDATEtitles
SETprice=price*2
SELECTMAX（price）FROMtitles
IF（SELECTMAX（price）FROMtitles）>$50
BREAK
ELSE
CONTINUE
END
CREATEPROCEDUREau_info
@lastnamevarchar（40）,
@firstnamevarchar（20）
AS
SELECTau_lname,au_fname,title,pub_name
FROMauthorsaINNERJOINtitleauthorta
ONa.au_id=ta.au_idINNERJOINtitlest
ONt.title_id=ta.title_idINNERJOINpublishersp
ONt.pub_id=p.pub_id
WHEREau_fname=@firstname
ANDau_lname=@lastname
GO
EXECUTEau_info'Dull','Ann'
--或者
EXECUTEau_info@lastname='Dull',@firstname='Ann'
--创建存储过程
CREATEPROCEDUREtitles_sum@TITLEvarchar（40）,@SUMmoneyOUTPUT
AS
SELECT@SUM=SUM（price）
FROMtitles
WHEREtitleLIKE@TITLE
GO
DECLARE@TOTALCOSTmoney
EXECUTEtitles_sum'The%',@TOTALCOSTOUTPUT
select@TOTALCOST
go
CREATEPROCEDUREOakland_authors
AS
SELECTau_fname,au_lname,address,city,zip
FROMauthors
WHEREcity='Oakland'
andstate='CA'
ORDERBYau_lname,au_fname
GO
--sp_helptextOakland_authors
ALTERPROCEDUREOakland_authors
AS
SELECTau_fname,au_lname,address,city,zip
FROMauthors
WHEREstate='CA'
ORDERBYau_lname,au_fname
GO
--sp_helptextOakland_authors
--提交事务后，所有书籍支付的版税增加10%。
begintransactionMyTransaction
updateroysched
setroyalty=royalty*1.10
committransactionMyTransaction
--rollbacktransactionMyTransaction
selectroyaltyfromroysched
--select@@trancount
--1.创建试验实验表
createtabletemptrigger
（
id_tempvarchar
（2）notnullprimarykey,
temp_namevarchar（10）null,
temp_ageintnull
）
go
inserttemptriggervalues（'01','张三','10'）
inserttemptriggervalues（'02','李四','11'）
inserttemptriggervalues（'03','王五','12'）
inserttemptriggervalues（'04','赵六','11'）
select*fromtemptrigger
go
--2.创建insert,update触发器
createtriggertemptrigger_modify
ontemptrigger
forinsert,update
as
begin
if（selecttemp_agefrominserted）>15
begin
rollbacktransaction
print'年龄不能超过15岁！
'
end
end
--inserttemptriggervalues（'04','大朋','17'）
--inserttemptriggervalues（'05','大朋','17'）
--inserttemptriggervalues（'05','大朋','14'）
--updatetemptriggersettemp_age='18'whereid_temp='01'
--updatetemptriggersettemp_age='9'whereid_temp='01'
--3.创建delete触发器
--droptriggertemptrigger_delete
createtriggertemptrigger_delete
ontemptrigger
fordelete
as
begin
print@@rowcount
if@@rowcount>1
begin
rollbacktransaction
print'一次删除记录不能多于1条'
end
end
--deletefromtemptrigger
--deletefromtemptriggerwhereid_temp='01'
--创建聚集索引
createclusteredindexclindx_titleid
onroysched（title_id）
--sp_helproysched
--创建非聚集索引
createnonclusteredindexunclindx_titleid
onroysched（title_id）
--sp_helproysched
--查看索引统计
dbccshow_statistics（roysched,titleidind）
--更新索引统计
updatestatisticsauthors
--重建索引
dbccdbreindex（'roysched',unclindx_titleid）
--删除索引
dropindexroysched.unclindx_titleid
--sp_helproysched
--
--1
--创建ssn（社会保险号）的基于varchar的自定义数据类型。
--用于存储11位社会保险号（999-99-999）的列。
该列不能
--为null。
usepubs
execsp_addtypessn,'varchar（11）','NOTNULL'
--2
--查看创建的数据类型
--sp_helpssn
--3
/*
--使用创建的数据类型
createtablemytable
（
myidvarchar
（2）primarykey,
myssnssn
）
*/
--4
--删除创建的数据类型
--droptablemytable
--execsp_droptypessn
///////////////////////////////////////////////////////////////////////////////
------SQL总结------
--1:
建数据库
createdatabaseaaa
on
（
name=aaa_mdf,
filename='e:
\aaa.mdf',
size=15,
maxsize=40,
filegrowth=5
）
logon
（
name=aaa_ldf,
filename='e:
\aaa.ldf',
size=10,
maxsize=25,
filegrowth=5
）
usemaster
selectnamefromsysdatabases--查看有几个数据库
execsp_dbremovedatabase_name--删除数据库
-------------------------------------------------------------------
--2:
修改数据库属性
execsp_dboption'aaa','readonly','true'
execsp_dboption'aaa','autostrink','true'
-------------------------------------------------------------------
--3:
创建表
useaaa
createtablestudent
（
stuidintidentity（1,1）primarykey,----主键约束
stunamenvarchar（20）uniquenotnull,----唯一约束非空约束
class_numintforeignkeyreferencesclass（class_num）,----外键约束
stuageintcheck（stuage>0andstuage<100）,----检查约束
sexchar（5）default'男',----默认值
[primarykey（stuid,stuname）]--设组合键为主键
）
-------------------------------------------------------------------
--4:
约束
execsp_help--查看默认约束名
execsp_helpconstraint'表名'
execsp_helptext
--
（1）主键约束：
altertablestudentaddconstraintpk_stuidprimarykey（stuid）--创建主键约束
altertablestudentdropconstraintpk_stuid--删除主键约束
altertablestudentaddconstraintpk_stuid_stunameprimarykey（stuid,stuname）--设组合键为主键
--
（2）外键约束
altertablestudentaddconstraintfk_class_numforeignkey（class_num）referencesclass（classnum）
altertablestudentdropconstraintfk_class_num
/*特别注意：
外键必须是被引用表的主键*/
--（3）唯一约束
altertablestudentaddconstraintuq_stunameunique（stuname）
altertablestudentdropconstraintuq_stuname
--（4）一次增加多个约束
altertablestudentaddconstraintpk_stuidprimarykey（stuid）,constraintuq_stunameunique（stuname）
--（5）检查约束
altertablestudentaddconstraintcheck_agecheck（stuage>0andstuage<100）
--（6）default约束
altertablestudentaddconstraintdf_sexdefault'男'forsex
-------------------------------------------------------------------
--5:
数据的插入，删除，修改
--
（1）插入
insertintostudent[（stuname,sex）]values（'zhao','男'）
select*intob/*（新表）*/froma/*（'源表'）*/where1<>1
select*intotest6fromtestwhere1<>1--只复制了表结构没有数据
select*intotest6fromtest--数据和表结构一起复制
insertintostudent
select/*字段列*/from/*另一表*/
where条件
--
（2）删除
deletefromstudent
where'条件'
deletefromstudent--删除全部数据
truncatetablestudent
--注意:
truncate不能删除有外键约束的主表
--（3）修改
updatestudentsetname='',sex=''--不加条件是全部修改
where'条件'
--=================================================================
--6:
全球唯一鉴别号
createtableaa
（
iduniqueidentifierdefaultnewid（）,
namechar（10）
）
insertintoaa（name）values（'qian'）
insertintoaavalues（newid（）,'sun'）
--=================================================================
--7:
表结构修改
--
（1）增加新列
altertablestudentaddaaintnotnullcheck（aa>54）
--
（2）删除列
altertablestudentdropcolumnaa
--注意：
删除列时必须保证基于该列的所有约束和索引（包括默认值约束）首先被删除掉
--（3）修改列
altertablestudentaltercolumnstunamechar（10）unique
/*特别注意一：
修改列时如果表里有数据就不能添加主键约束和唯一约束，
因为表里有NULL值，必须是空表时才可以*/
/*特别注意二:
不能对有约束的列进行更改
不对：
altertablestudentaltercolumnstunamechar（8）notnull*/
--（4）表和表字段的重命名
execsp_rename'旧表名','新表名'
execsp_rename'旧字段名','新字段名','column'
--=====================================================================
--8:
identity
setidentity_insert表名on/off
--为on时可以自己输入数据
insertintostudent（num）values（58）--58为自己设置的，此句有效
--为off时不可以
--=====================================================================
--9:
连接
--
（1）连接
select*fromtable1,table2
--等价于
select*fromtable1crossjointable2
select*fromtable1,table2
wheretable1.row=table2.row
--
（2）自连接
--笛卡耳集select*fromemplolye1,emplolye2
selecte1.name,e2.namefromemploye1,employe2
wheree1.name=e2.name
--（3）内连接
selectstunameas'姓名',classnameas'班级'fromstudent
innerjoinclassonstudent.stuid=class.stuid
innerjoin'表名'on条件--连接多个表
--它等价于:
selectstunameas'姓名',classnameas'班级'
fromstudent,class
wherestudent.stuid=class.stuid
--------------操作过程----
1）从student表中取出一条符合条件的记录
2）扫描class表，分别检查每条记录是否在连接属性上同表student取出的记录相等
3）相等就显示信息，继续从表class中取下一条记录，重复步骤2
--（4）外连接：
（outerjoin）
允许限制一张表中的行，而不限制另外一张表中的行。
--注意:
外连接不一定非要有外键约束
1:
leftouterjoin--不能用leftoutjoin
左表中的记录全部会出现在结果集中，匹配不上的显示NULL
2:
rightouterjoin
右表中的记录全部会出现在结果集中，匹配不上的显示NULL
3:
fullouterjoin|fulljoin--不能用fulloutjoin
返回两个表中的匹配和不匹配的所有记录。
--=====================================================================
--10:
查询
--
（1）select
select*fromstudent
where'条件'
--
（2）orderby
select*fromstudent
orderbystuname[asc|desc]--asc为升序
select*fromstudent
orderbystuiddesc,stunamedesc,classnum[asc]
--不能对数据类型为text或image的列使用ORDERBY
--（3）groupby
select*fromstudent
groupbyclassnum
havingclassnum>3
--（4）示例
usenorthwind
select*fromcustomers
selectcity,count（*）fromcustomers
wherecityin（'berlin','MéxicoD.F.'）--where的位置不能放在groupby之后,必须在其前边
groupbycity
havingcityin（'MéxicoD.F.','Berlin','Strasbourg'）
orderbycount（*）desc
/*groupby的特征：
1.能按列分组2.同聚合函数一起用，为每一组产生一个值。
对数据分组时，也可以用条件
进行选择，这就是having子句。
having子句与where子句相似，都是给出查询条件，所不同的是，where子句是
检查每条记录是否满足条件，而having子句
升级会员 