SQL整套学习资料.docx
《SQL整套学习资料.docx》由会员分享，可在线阅读，更多相关《SQL整套学习资料.docx（16页珍藏版）》请在冰豆网上搜索。
SQL整套学习资料
奉献给SQL初学者们得终极教材
<上一篇|下一篇>
此教材可以说涉及得范围就是非常广得。
。
我们平常写得SQL语句都就是出现在里面得。
。
且每一种方法都有案例，所以说如果您把所有得案例应用理解透得话。
。
可以说您已经成为高手了。
。
知识都就是得靠自己去掌握得。
。
多瞧，多想。
多问。
多动手。
相信您一定很快掌握得。
。
usemaster
go
--创建数据库book_manage
createdatabasebook_manage
on
（
name=book_manage_primary,
='d:
\data\book_manage、mdf',
size=10,
maxsize=20,
=5
）
logon
（
name=book_manage_log,
='d:
\data\book_manage、ldf',
size=5,
maxsize=20,
=5
）
go
--查瞧book_manage数据库信息
execsp_helpdbbook_manage
--修改数据库日志文件扩展空间
alterdatabasebook_managemodifyfile
（
name=book_manage_log,
=5
）
--创建新表
usebook_manage
go
createtabletb_bookinfo
（
book_IDchar（6）notnull,
booknamechar（30）notnull,
pricedecimal（18,2）notnull,
authorIDchar（4）,
publishIDchar（4）
）
createtabletb_authorinfo
（
authorIDchar（4）notnull,
authornamechar（20）notnull,
sexchar
（2）,
agetinyint,
authaddresschar（30）
）
go
createtabletb_pubinfo
（
publishIDchar（4）notnull,
pubnamechar（20）notnull,
pubaddresschar（30）
）
createtabletemp1
（
temIDchar（4）notnull,
temnamevarchar（30）notnulldefault'默认名称'
）
createtabletbl_a
（
emp_idchar
（2）notnull,
emp_namechar（10）notnull,
emp_agechar
（2）
）
createtabletbl_b
（
emp_idchar
（2）notnull,
spendchar（10）notnull,
levelchar（10）
）
--删除表
droptabletemp1
--添加tb_authorinfo表信息
insertintotb_bookinfovalues（'000008','asp、net',54、5,'A004','P106'）
insertintotb_authorinfovalues（'A004','黄薇','男',25,'济南'）
insertintotb_pubinfovalues（'P106','山东出版社','济南'）
--查询各个表信息
select*fromtb_bookinfo
select*fromtb_pubinfo
select*fromtb_authorinfo
--备份一个新表
select*intoBtb_bookinfofromtb_bookinfo
select*intoBtb_authorinfofromtb_authorinfo
select*intoBtb_pubinfofromtb_pubinfo
--查瞧单个表信息
execsp_helptb_authorinfo
--修改表,在temp1中添加一列
altertabletemp1addsagechar
（2）
--删除temp1中得以列
altertabletemp1dropcolumncol_new
--添加一个约束
altertabletemp1addcol_newvarchar（20）nullconstrainttem_uniqueunique--升序
--修改类型（有错误）
--altertabletemp1modifysagebit
--唯一元素（distinct）属性
select distinctpublishidfromtb_bookinfo
select publishid fromtb_bookinfo
selecttop3book_ID,booknamefromtb_bookinfo--orderbybookname默认升序 --desc降序--asc升
selectb、book_id,b、booknamefromtb_bookinfoasb
--将查出信息从新创将一个表
selectbook_idas书号,booknameas书名intotabfromtb_bookinfo
select*fromtb_bookinfowhereauthorIDin（'A002','B001'）--in在什么范围之内
select*fromtb_bookinfowherebooknamelike'计%'--模糊查询 '计\_%'
selectpublishID,avg（price）fromtb_bookinfogroupbypublishID--对相同组得类,显示平均值
--在groupby得后面添加一个avg得查询条件
selectpublishIDas出版社编号,avg（price）as平均价格fromtb_bookinfogroupbypublishIDhavingavg（price）>25
--内部连接查询
selecttbl_a、emp_id,tbl_a、emp_name,tbl_a、emp_age,tbl_b、spendfromtbl_ainnerjointbl_bontbl_a、emp_id=tbl_b、emp_id
select*fromtbl_aasa,tbl_basbwherea、emp_id=b、emp_id
--左连接查询
selecttbl_a、emp_id,tbl_a、emp_name,tbl_a、emp_age,tbl_b、spend,tbl_b、levelfromtbl_aleftouterjointbl_bontbl_a、emp_id=tbl_b、emp_id
--右连接查询
selecttbl_a、emp_id,tbl_a、emp_name,tbl_a、emp_age,tbl_b、spend,tbl_b、levelfromtbl_arightouterjointbl_bontbl_a、emp_id=tbl_b、emp_id
--全连接查询
selecttbl_a、emp_id,tbl_a、emp_name,tbl_a、emp_age,tbl_b、spend,tbl_b、levelfromtbl_afullouterjointbl_bontbl_a、emp_id=tbl_b、emp_id
-------------------------------------------------
--select*intoBtbl_bfromtbl_b
--修改价格updatetb_bookinfosetprice=price*0、8--/0、8
--select*fromtb_bookinfo
-------------------------------------------------
--清除表中所有信息
truncatetableBtbl_a
--将tbl_a表中得信息添加到Btbl_a
insertintoBtbl_aselect*fromtbl_a
select*fromBtbl_a
-------------------------------------------------
--安全管理--
--添加登陆帐户
execsp_addlogin'yonghuming','mima','shujuku'
--修改密码
execsp_password'oldpassword','newpassword','yonghuming'--oldpassword可以为空null
--删除用户
execsp_droplogin'yonghuming'
--------------------------------------------------
--角色管理
--sysadmin可以在sql中执行任何活动
--serveradmin可以设置服务器访问内得配置选项,关闭服务器
--setupadmin可以管理连接服务器及执行某些系统存储过程
--securityadmin管理登陆与createdatabase权限,日志,密码
--processadmin管理sql运行得进程
--dbcreator可以创建\修改\删除数据库
--diskadmin管理磁盘文件
--bulkadmin执行bulkinsert语句
--添加成员
execsp_addsrvrolemember'2','bulkadmin'--2为用户名bulkadmin为固定角色名称
--删除成员
execsp_dropsrvrolemember'2','bulkadmin'
--查瞧固定角色成员
execsp_helpsrvrolemember'bulkadmin'
---------------------------------------------------------
--添加数据库角色
--db_owner在数据库中右全部权限
--db_accessadmin可以添加删除用户
--db_datareader可以查瞧来自数据库中所有用户得标底全部数据
--db_datawriter更改来自数据库得所有表中全部数据
--db_addadmin可以添加\修改\删除数据库中得对象
--db_securityadmin管理数据库角色成员
--db_backupoperator规数据库进行备份
--db_denydatareader拒绝选择数据库得数据
--db_denydatawriter拒绝更改数据库得数据
usebook_manage
go
execsp_addrole'test'--test为角色自定义数据库角色
execsp_droprole'test'--在数据库角色删除角色组
execsp_addrolemember'test','2'--2为将要添加得角色得帐号
execsp_droprolemember'test','2'--2为将要删除得角色得帐号
--------------------------------------------------
--用户帐号管理--
execsp_grantlogin'sdzs\user'
--添加windows用户登陆sql域名\用户名
execsp_addlogin'2','2'
--添加sql用户登陆sql用户名密码
usebook_manage
go
execsp_grantdbaccess'sdzs\user','yonghuming'
--在windows用户下添加登陆用户访问（book_manage）数据库权限
execsp_grantdbaccess'2','2'
--添加用户2对该（book_manage）数据库得访问权限
-----------------------------------------------------
--权限管理
grantinsert,select,updatetableto2 --给用户2有添加查询修改得权限
revokecreatetablefrom2 --废除用户2创建表得权限
denycreatedatabaseto2--拒绝用户2创建数据库得权限
------------------------------------------------------
--创建视图
usebook_manage
go
ifexists（select*fromsysobjectswherename='pub_book'）
dropviewpub_book
go
createviewpub_book
as
selecta、bookname,a、price,b、authorname
fromtb_bookinfoasainnerjointb_authorinfoasb
ona、authorid=b、authoridinnerjointb_pubinfoasc
ona、publishid=c、publishid
wherec、pubname='清华大学出版社'
go
--查瞧视图
select*frompub_book
createviewauthinfo（作者编号,姓名,性别,年龄）
as
selectauthorid,authorname,sex,age
fromtb_authorinfo
go
select* fromauthinfo
--创建一个加密不能修改视图
usepubs
go
ifexists（selecttable_namefrominformation_schema、viewswheretable_name='emprange'）
dropviewemprange
as
createviewemprange（emp_id,fname,lname,pub_id,job_id）
withencryption
as
selectemp_id,fname,lname,pub_id,job_id
fromemployee
wherejob_idbetween11and12withcheckoption
go
select*fromemprange
go
updateemprangesetjob_id='5'whereemp_id='PCM98509F'
--不让修改原因为建立视图上面做了限制修改（withcheckoption）
go
execsp_helptext'emprange'
--显示该视图已经加密,不能修改,只有查瞧信息功能
go
------------------------------------------------------------
--修改view信息表
usepubs
go
createviewall_authors（au_fname,au_lname,address,city,zip）
as
selectau_fname,au_lname,address,city,zip
fromauthors
go
--给视图select权限给public
grantselectonall_authorstopublic
--修改视图查询
alterviewall_authors（au_fname,au_lname,address,city,zip）
as
selectau_fname,au_lname,address,city,zip
fromauthors
wherestate='UT'
go
----------------------------------------------------------
--添加视图
createviewyourview
as
selecttitle_id,title,mycount=@@rowcount,ytd_sales
fromtitles
go
select*fromyourview
alterviewyourview
as
selecttitle_id,title,mycount=@@rowcount,ytd_sales
fromtitles
wheretype='mod_cook'
go
select*fromyourview
--删除视图
dropviewall_authors
--------------------------------------------------------
--修改查询信息
alterviewall_authors（姓名,地址,城市,邮编,电话）
withencryption
as
selectau_fname+space
（1）+au_lname,address,city,zip,phone
fromauthors
withcheckoption
go
---------------------------------------------------------------
--修改视图名称
usebook_manage
go
execsp_rename'作者信息视图','authinfo'
execsp_rename'authinfo','作者信息视图'
---------------------------------------------------------------
usebook_manage
go
createviewv_author（authorid,authorname,age,sex,authaddress）
as
selectauthorid,authorname,age,sex,authaddress
fromtb_authorinfo
whereauthorname='张丽'
go
select*fromv_author
--在视图中添加信息
insertintov_authorvalues（'A009','李风',22,'男','大连'）
--------------------------------------------------------
--在视图中添加修改信息
createviewv_author1
as
select*fromtb_authorinfo
--修改信息
go
updatev_author1top1setauthorname='李峰'whereauthorname='李芬'
----------------------------------------------------------
usepubs
go
select*fromemployee
declare@emp_idvarchar（20）,@TempLastNamevarchar（25）
set@emp_id=99
select@emp_id=emp_id,@TempLastName=lnamefromemployeewherefname='Paolo'--orderbyemp_id
go
selectcount（*）fromemployee
--selectemp_id,lnamefromemployeewherefname='Paolo'
print'sqlserver得版本'+@@version
print'服务器得名称:
'+@@servername
insertintoemployeevalues（'pma42627m','smith','t','mr'）
print'当前错误号'+convert（varchar（5）,@@error）
---------------------------------------------------------
/*usebook_manage
--创建表,添加\修改\等
createtableinfo
（
namevarchar（20）notnull,
nochar（6）notnull,
ageintnotnull,
idnumeric（18,0）,
seatsmallintidentity（1,1）,
addresstext
）
insertintoinfo（name,no,age,id）values（'no1','1',21,3789451）
insertintoinfo（name,no,age,id）values（'no2','2',22,3789452）
insertintoinfo（name,no,age,id）values（'no3','3',23,3789453）
insertintoinfo（name,no,age,id）values（'no4','4',24,3789454）
updateinfosetname='no4'wherename='4'
createtablemarks
（
nochar（6）notnull,
writevarchar（3）,
labvarchar（3）
）
insertintomarksvalues（'1',59,76）
insertintomarksvalues（'4',68,80）
insertintomarksvalues（'4',86,87）
insertintomarksvalues（'4',98,55）
select*frommarks
select*frominfo
--添加表得约束于默认值等
altertableinfoaddconstraintPK_noprimarykey（no）--添加约束NO
altertableinfoaddconstraintUQ_idUnique（id）--添加ID
a
升级会员 