SQL整套学习资料docxWord下载.docx
《SQL整套学习资料docxWord下载.docx》由会员分享,可在线阅读,更多相关《SQL整套学习资料docxWord下载.docx(28页珍藏版)》请在冰豆网上搜索。
alterdatabasebook_managemodifyfile
--创建新表
usebook_manage
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)
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
spendchar(10)notnull,
levelchar(10)
--删除表
droptabletemp1
--添加tb_authorinfo表信息
insertintotb_bookinfovalues('
000008'
'
'
54.5,'
A004'
P106'
insertintotb_authorinfovalues('
黄薇'
男'
25,'
济南'
insertintotb_pubinfovalues('
山东出版社'
济南'
--查询各个表信息
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)属性
selectdistinctpublishidfromtb_bookinfo
selectpublishidfromtb_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的后面添加一个
selectpublishIDas出版社编号
avg的查询条件
avg(price)as
平均价格
fromtb_bookinfogroup
bypublishIDhavingavg(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'
--oldpassword
可
以为空null
--删除用户
execsp_droplogin'
--------------------------------------------------
--角色管理
--sysadmin
可以在sql中执行任何活动
--serveradmin可以设置服务器访问内的配置选项,关闭服务器
--setupadmin可以管理连接服务器及执行某些系统存储过程
--securityadmin
管理登陆和createdatabase
权限,日志,密码
--processadmin
管理sql运行的进程
--dbcreator
可以创建修改删除数据库
--diskadmin
管理磁盘文件
--bulkadmin
执行bulkinsert语句
--添加成员
execsp_addsrvrolemember'
2'
bulkadmin'
--2
为用户名bulkadmin
为固定角色
名称
--删除成员
execsp_dropsrvrolemember'
--查看固定角色成员
execsp_helpsrvrolemember'
---------------------------------------------------------
--添加数据库角色
--db_owner在数据库中右全部权限
--db_accessadmin可以添加删除用户
--db_datareader可以查看来自数据库中所有用户的标底全部数据
--db_datawriter更改来自数据库的所有表中全部数据
--db_addadmin可以添加修改删除数据库中的对象
--db_securityadmin管理数据库角色成员
--db_backupoperator规数据库进行备份
--db_denydatareader拒绝选择数据库的数据
--db_denydatawriter拒绝更改数据库的数据
execsp_addrole'
test'
--test为角色自定义数据库角色
execsp_droprole'
--在数据库角色删除角色组
execsp_addrolemember'
为将要添加的角色的帐号
execsp_droprolemember'
为将要删除的角色的帐号
--用户帐号管理--
execsp_grantlogin'
sdzs\user'
--添加windows用户登陆sql域名用户名
--添加sql用户登陆sql用户名密码
execsp_grantdbaccess'
--在windows用户下添加登陆用户访问(book_manage)数据库权限execsp_grantdbaccess'
--添加用户
2对该(book_manage)
数据库的访问权限
-----------------------------------------------------
--权限管理
grantinsert,select,updatetableto2
--给用户
2有添加查询修改的权限
revokecreatetablefrom2
--废除用户
2创建表的权限
denycreatedatabaseto2--
拒绝用户
2创建数据库的权限
------------------------------------------------------
--创建视图
ifexists(select*fromsysobjectswherename='
pub_book'
dropviewpub_book
createviewpub_book
as
selecta.bookname,a.price,b.authorname
fromtb_bookinfoasainnerjointb_authorinfoasb
ona.authorid=b.authoridinnerjointb_pubinfoasc
ona.publishid=c.publishid
wherec.pubname='
清华大学出版社'
--查看视图
select*frompub_book
createviewauthinfo(作者编号,姓名,性别,年龄)
selectauthorid,authorname,sex,age
fromtb_authorinfo
select*fromauthinfo
--创建一个加密不能修改视图
usepubs
ifexists(selecttable_namefrominformation_schema.viewswheretable_name='
emprange'
dropviewemprange
createviewemprange(emp_id,fname,lname,pub_id,job_id)
withencryption
selectemp_id,fname,lname,pub_id,job_id
fromemployee
wherejob_idbetween11and12withcheckoptiongo
select*fromemprange
updateemprangesetjob_id='
5'
whereemp_id='
PCM98509F'
--不让修改原因为建立视图上面做了限制修改(withcheckoption)
execsp_helptext'
--显示该视图已经加密,不能修改,只有查看信息功能
------------------------------------------------------------
--修改view信息表
createviewall_authors(au_fname,au_lname,address,city,zip)
selectau_fname,au_lname,address,city,zip
fromauthors
--给视图select权限给public
grantselectonall_authorstopublic
--修改视图查询
alterviewall_authors(au_fname,au_lname,address,city,zip)
wherestate='
UT'
----------------------------------------------------------
--添加视图
createviewyourview
selecttitle_id,title,mycount=@@rowcount,ytd_salesfromtitles
select*fromyourview
alterviewyourview
wheretype='
mod_cook'
--删除视图
dropviewall_authors
--------------------------------------------------------
--修改查询信息
alterviewall_authors(姓名,地址,城市,邮编,电话)
selectau_fname+space
(1)+au_lname,address,city,zip,phone
withcheckoption
---------------------------------------------------------------
--修改视图名称
execsp_rename'
作者信息视图'
authinfo'
createviewv_author(authorid,authorname,age,sex,authaddress)
selectauthorid,authorname,age,sex,authaddress
whereauthorname='
张丽'
select*fromv_author
--在视图中添加信息
insertintov_authorvalues('
A009'
李风'
22,'
男'
大连'
--在视图中添加修改信息
createviewv_author1
--修改信息
updatev_author1top1setauthorname='
李峰'
whereauthorname='
李芬'
select*fromemployee
declare@emp_idvarchar(20),@TempLastNamevarchar(25)
set@emp_id=99
select@emp_id=emp_id,@TempLastName=lnamefromemployeewherefname='
Paolo'
--orderbyemp_id
selectcount(*)fromemployee
--selectemp_id,lnamefromemployeewherefname='
print'
sqlserver的版本'
+@@version
服务器的名称:
+@@servername
insertintoemployeevalues('
pma42627m'
smith'
t'
mr'
当前错误号'
+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)
no2'
22,370123456789789452)
no3'
3'
23,370123456789789453)
insertintoinf