SQL整套学习资料.docx
《SQL整套学习资料.docx》由会员分享，可在线阅读，更多相关《SQL整套学习资料.docx（16页珍藏版）》请在冰豆网上搜索。
SQL整套学习资料
SQL整套学习资料
奉献给SQL初学者们的终极教材
<上一篇|下一篇>
此教材可以说涉及的范围是非常广的。
。
我们平常写的SQL语句都是出现在里面的。
。
且每一种方法都有案例，所以说如果你把所有的案例应用理解透的话。
。
可以说你已经成为高手了。
。
知识都是得靠自己去掌握的。
。
多看，多想。
多问。
多动手。
相信你一定很快掌握的。
。
usemaster
go
--创建数据库book_manage
createdatabasebook_manage
on
（
name=book_manage_primary,
filename='d:
\data\book_manage.mdf',
size=10,
maxsize=20,
filegrowth=5
）
logon
（
name=book_manage_log,
filename='d:
\data\book_manage.ldf',
size=5,
maxsize=20,
filegrowth=5
）
go
--查看book_manage数据库信息
execsp_helpdbbook_manage
--修改数据库日志文件扩展空间
alterdatabasebook_managemodifyfile
（
name=book_manage_log,
filegrowth=5
）
emp_idchar
（2）notnull,
spendchar（10）notnull,
levelchar（10）
）
--删除表
droptabletemp1
--添加tb_authorinfo表信息
insertintotb_bookinfovalues（'000008','',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,370123456789789451）
insertintoinfo（name,no,age,id）values（'no2','2',22,370123456789789452）
insertintoinfo（name,no,age,id）values（'no3','3',23,370123456789789453）
insertintoinfo（name,no,age,id）values（'no4','4',24,370123456789789454）
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
altertableinfoaddconstraintDF_addressdefault（'地址不详'）foraddress--添加默认值
altertableinfoaddconstraintCK_agecheck（agebetween15and40）--添加年龄限制
altertablemarksaddconstraintFK_noforeignkey（no）referencesinfo（no）--添加外按键
altertableinfodropconstraintDF_address--删除默认约束
--创建视图查看学员成绩平均分
ifexists（select*fromsysobjectswherename='view_info_marks'）
dropviewview_info_marks
go
createviewview_info_marks
as
select姓名=name,学号=info.no,笔试成绩=write,机试成绩=lab,平均分=floor（write+lab）/2frominfoleftjoinmarksoninfo.no=marks.no
go
select
升级会员 