SQL整套学习资料

奉献给SQL初学者们的终极教材<上一篇 | 下一篇>此教材可以说涉及的范围是非常广的。

我们平常写的SQL语句都是出现在里面的。

且每一种方法都有案例，所以说如果你把所有的案例应用理解透的话。

可以说你已经成为高手了。

知识都是得靠自己去掌握的。

多看，多想。

多问。

多动手。

相信你一定很快掌握的。

use mastergo--创建数据库book_managecreate database book_manageon(name = book_manage_primary,filename = 'd:\data\book_manage.mdf',size = 10,maxsize = 20,filegrowth = 5)log on(name = book_manage_log,filename = 'd:\data\book_manage.ldf',size = 5,maxsize = 20,filegrowth = 5)go--查看book_manage数据库信息exec sp_helpdb book_manage--修改数据库日志文件扩展空间alter database book_manage modify file(name = book_manage_log,filegrowth = 5--创建新表use book_managegocreate table tb_bookinfo(book_ID char(6) not null,bookname char(30) not null,price decimal(18,2) not null,authorID char(4),publishID char(4))create table tb_authorinfo(authorID char(4) not null,authorname char(20) not null,sex char(2),age tinyint,authaddress char(30))gocreate table tb_pubinfo(publishID char(4) not null,pubname char(20) not null,pubaddress char(30))create table temp1(temID char(4) not null,temname varchar(30) not null default '默认名称' )create table tbl_a(emp_id char(2) not null,emp_name char(10) not null,emp_age char(2))create table tbl_bemp_id char(2) not null,spend char(10) not null,level char(10))--删除表drop table temp1--添加tb_authorinfo表信息insert into tb_bookinfo values('000008','',54.5,'A004','P106')insert into tb_authorinfo values('A004','黄薇','男',25,'济南')insert into tb_pubinfo values('P106','山东出版社','济南')--查询各个表信息select * from tb_bookinfoselect * from tb_pubinfoselect * from tb_authorinfo--备份一个新表select * into Btb_bookinfo from tb_bookinfoselect * into Btb_authorinfo from tb_authorinfoselect * into Btb_pubinfo from tb_pubinfo--查看单个表信息exec sp_help tb_authorinfo--修改表,在temp1中添加一列alter table temp1 add sage char(2)--删除temp1中的以列alter table temp1 drop column col_new--添加一个约束alter table temp1 add col_new varchar(20) null constraint tem_unique unique --升序--修改类型(有错误)--alter table temp1 modify sage bit--唯一元素( distinct ) 属性select distinct publishid from tb_bookinfoselect publishid from tb_bookinfoselect top 3 book_ID,bookname from tb_bookinfo --order by bookname 默认升序--desc 降序--asc 升select b.book_id,b.bookname from tb_bookinfo as b--将查出信息从新创将一个表select book_id as 书号,bookname as 书名into tab from tb_bookinfoselect * from tb_bookinfo where authorID in('A002','B001') --in 在什么范围之内select * from tb_bookinfo where bookname like'计%' --模糊查询'计\_%'select publishID,avg(price) from tb_bookinfo group by publishID --对相同组的类,显示平均值--在group by 的后面添加一个avg的查询条件select publishID as 出版社编号,avg(price) as 平均价格from tb_bookinfo group by publishID having avg(price)>25--内部连接查询select tbl_a.emp_id,tbl_a.emp_name,tbl_a.emp_age,tbl_b.spend from tbl_a inner join tbl_b on tbl_a.emp_id=tbl_b.emp_idselect * from tbl_a as a,tbl_b as b where a.emp_id =b.emp_id--左连接查询select tbl_a.emp_id,tbl_a.emp_name,tbl_a.emp_age,tbl_b.spend,tbl_b.level from tbl_a left outer join tbl_b on tbl_a.emp_id=tbl_b.emp_id--右连接查询select tbl_a.emp_id,tbl_a.emp_name,tbl_a.emp_age,tbl_b.spend,tbl_b.level from tbl_a right outer join tbl_b on tbl_a.emp_id=tbl_b.emp_id--全连接查询select tbl_a.emp_id,tbl_a.emp_name,tbl_a.emp_age,tbl_b.spend,tbl_b.level from tbl_a full outer join tbl_b on tbl_a.emp_id=tbl_b.emp_id---------------------------------------------------select * into Btbl_b from tbl_b--修改价格update tb_bookinfo set price=price*0.8 --/0.8--select * from tb_bookinfo---------------------------------------------------清除表中所有信息truncate table Btbl_a--将tbl_a表中的信息添加到Btbl_ainsert into Btbl_a select * from tbl_aselect * from Btbl_a---------------------------------------------------安全管理----添加登陆帐户exec sp_addlogin 'yonghuming','mima','shujuku'--修改密码exec sp_password 'oldpassword','newpassword','yonghuming' --oldpassword可以为空null--删除用户exec sp_droplogin 'yonghuming'----------------------------------------------------角色管理--sysadmin 可以在sql中执行任何活动--serveradmin 可以设置服务器访问内的配置选项,关闭服务器--setupadmin 可以管理连接服务器及执行某些系统存储过程--securityadmin 管理登陆和create database 权限,日志,密码--processadmin 管理sql运行的进程--dbcreator 可以创建\修改\删除数据库--diskadmin 管理磁盘文件--bulkadmin 执行bulk insert 语句--添加成员exec sp_addsrvrolemember '2','bulkadmin' --2为用户名bulkadmin 为固定角色名称--删除成员exec sp_dropsrvrolemember '2','bulkadmin'--查看固定角色成员exec sp_helpsrvrolemember 'bulkadmin'-----------------------------------------------------------添加数据库角色--db_owner 在数据库中右全部权限--db_accessadmin 可以添加删除用户--db_datareader 可以查看来自数据库中所有用户的标底全部数据--db_datawriter 更改来自数据库的所有表中全部数据--db_addadmin 可以添加\修改\删除数据库中的对象--db_securityadmin 管理数据库角色成员--db_backupoperator 规数据库进行备份--db_denydatareader 拒绝选择数据库的数据--db_denydatawriter 拒绝更改数据库的数据use book_managegoexec sp_addrole 'test' --test为角色自定义数据库角色exec sp_droprole 'test' --在数据库角色删除角色组exec sp_addrolemember 'test','2' --2为将要添加的角色的帐号exec sp_droprolemember 'test','2' --2为将要删除的角色的帐号----------------------------------------------------用户帐号管理--exec sp_grantlogin 'sdzs\user'--添加windows用户登陆sql 域名\用户名exec sp_addlogin '2','2'--添加sql用户登陆sql 用户名密码use book_managegoexec sp_grantdbaccess 'sdzs\user','yonghuming'--在windows用户下添加登陆用户访问(book_manage)数据库权限exec sp_grantdbaccess '2','2'--添加用户2对该(book_manage)数据库的访问权限-------------------------------------------------------权限管理grant insert,select,update table to 2 -- 给用户2有添加查询修改的权限revoke create table from 2 --废除用户2创建表的权限deny create database to 2 --拒绝用户2创建数据库的权限--------------------------------------------------------创建视图use book_managegoif exists (select * from sysobjects where name='pub_book')drop view pub_bookgocreate view pub_bookasselect a.bookname,a.price,b.authornamefrom tb_bookinfo as a inner join tb_authorinfo as bon a.authorid=b.authorid inner join tb_pubinfo as con a.publishid=c.publishidwhere c.pubname='清华大学出版社'go--查看视图select * from pub_bookcreate view authinfo(作者编号,姓名,性别,年龄)asselect authorid,authorname,sex,agefrom tb_authorinfogoselect * from authinfo--创建一个加密不能修改视图use pubsgoif exists(select table_name from information_schema.views where table_name = 'emprange')drop view emprangeascreate view emprange(emp_id,fname,lname,pub_id,job_id)with encryptionasselect emp_id,fname,lname,pub_id,job_idfrom employeewhere job_id between 11 and 12 with check optiongoselect * from emprangegoupdate emprange set job_id='5' where emp_id='PCM98509F'--不让修改原因为建立视图上面做了限制修改(with check option)goexec sp_helptext 'emprange'--显示该视图已经加密,不能修改,只有查看信息功能go--------------------------------------------------------------修改view信息表use pubsgocreate view all_authors(au_fname,au_lname,address,city,zip) asselect au_fname,au_lname,address,city,zipfrom authorsgo--给视图select权限给publicgrant select on all_authors to public--修改视图查询alter view all_authors(au_fname,au_lname,address,city,zip) asselect au_fname,au_lname,address,city,zipfrom authorswhere state='UT'go------------------------------------------------------------添加视图create view yourviewasselect title_id,title,mycount=@@rowcount,ytd_salesfrom titlesgoselect * from yourviewalter view yourviewasselect title_id,title,mycount=@@rowcount,ytd_salesfrom titleswhere type = 'mod_cook'goselect * from yourview--删除视图drop view all_authors----------------------------------------------------------修改查询信息alter view all_authors(姓名,地址,城市,邮编,电话)with encryptionasselect au_fname+space(1)+au_lname,address,city,zip,phonefrom authorswith check optiongo-----------------------------------------------------------------修改视图名称use book_managegoexec sp_rename '作者信息视图','authinfo'exec sp_rename 'authinfo','作者信息视图'---------------------------------------------------------------use book_managegocreate view v_author(authorid,authorname,age,sex,authaddress)asselect authorid,authorname,age,sex,authaddressfrom tb_authorinfowhere authorname = '张丽'goselect * from v_author--在视图中添加信息insert into v_author values('A009','李风',22,'男','大连')----------------------------------------------------------在视图中添加修改信息create view v_author1asselect * from tb_authorinfo--修改信息goupdate v_author1 top 1 set authorname='李峰' where authorname='李芬' ----------------------------------------------------------use pubsgoselect * from employeedeclare @emp_id varchar(20),@TempLastName varchar(25)set @emp_id=99select @emp_id=emp_id,@TempLastName=lname from employee where fname='Paolo' --order by emp_idgoselect count(*) from employee--select emp_id,lname from employee where fname = 'Paolo'print 'sqlserver的版本'+@@versionprint '服务器的名称:'+@@servernameinsert into employee values('pma42627m','smith','t','mr')print '当前错误号'+convert(varchar(5),@@error)---------------------------------------------------------/*use book_manage--创建表,添加\修改\等create table info(name varchar(20) not null,no char(6) not null,age int not null,id numeric(18,0),seat smallint identity(1,1),address text)insert into info(name,no,age,id) values('no1','1',21,370123456789789451) insert into info(name,no,age,id) values('no2','2',22,370123456789789452) insert into info(name,no,age,id) values('no3','3',23,370123456789789453) insert into info(name,no,age,id) values('no4','4',24,370123456789789454) update info set name='no4' where name='4'create table marks(no char(6) not null,write varchar(3),lab varchar(3))insert into marks values('1',59,76)insert into marks values('4',68,80)insert into marks values('4',86,87)insert into marks values('4',98,55)select * from marksselect * from info--添加表的约束于默认值等alter table info add constraint PK_no primary key (no) --添加约束NOalter table info add constraint UQ_id Unique (id) --添加IDalter table info add constraint DF_address default ('地址不详') for address --添加默认值alter table info add constraint CK_age check(age between 15 and 40) --添加年龄限制alter table marks add constraint FK_no foreign key (no) references info(no) --添加外按键alter table info drop constraint DF_address --删除默认约束--创建视图查看学员成绩平均分if exists (select * from sysobjects where name='view_info_marks')drop view view_info_marksgocreate view view_info_marksasselect 姓名=name,学号=info.no,笔试成绩=write,机试成绩=lab,平均分=floor(write+lab)/2 from info left join marks on info.no = marks.nogoselect * from view_info_marks--逻辑控制语句declare @myavg floatselect @myavg=avg(write) from marksprint '本班平均分'+convert(varchar(5),@myavg)if(@myavg>75)beginprint '本班比试成绩优秀,前三名的成绩为'select top 3 * from marks order by write descendelsebeginprint '本版比试成绩较差,后三名的成绩为'select top 3 * from marks order by writeenduse book_managego--查询个人平均分于等级select * from marksselect *,(write+lab)/2 as 平均分,成绩= caseWhen (write+lab)/2<60 then 'e'When (write+lab)/2 between 60 and 69 then 'd'When (write+lab)/2 between 70 and 79 then 'c'When (write+lab)/2 between 80 and 89 then 'b'else 'a'endfrom marks*/-------------------------------------------------------------------查询存储过程use pubsgoif exists (select name from sysobjects where name = 'oakland_authors' and type ='p')drop proc oakland_authorsgocreate proc oakland_authorsasselect au_fname,au_lname,address,city,zipfrom pubs..authors where city='oakland' and state='ca'order by au_lname,au_fnameGO--查看该存储过程原代码select o.id,c.text from sysobjects o inner join syscomments con o.id =c.idwhere o.type = 'p' and = 'oakland_authors'goalter proc oakland_authorswith encryption --加密asselect au_fname,au_lname,address,city,zipfrom pubs.authorswhere state = 'ca' order by au_lname,au_fnamego--select * from oakland_authors--------------------------------------------------------------------------创建一个带参数的存储过程if exists (select name from sysobjects where name = 'au_info' and type ='p') drop proc au_infogocreate proc au_info@lastname char(20),@fristname char(20)asselect au_lname,au_fname,title,pub_namefrom authors a inner join titleauthor taon a.au_id = ta.au_id inner join titles ton t.title_id=ta.title_id join publishers pon t.pub_id=p.pub_idwhere au_fname=@fristname and au_lname=@lastnameGO--declare @write int,@lastname char(20),@fristname char(20)--exec @write = au_info 'White','Johnson'exec au_info @lastname='White',@fristname='Johnson'use book_managegoif exists (select name from sysobjects where name = 'find' and type='p')drop proc findgocreate proc find @auname varchar(20)asif exists (select * from tb_authorinfo where authorname = @auname) return 1elsereturn 0godeclare @result intexec @result=find @auname='赵阳'--/exec @result = find '赵阳'if(@result=1)print '找到此人'elseprint '查无此人'go--查看存储过程sp_helptext find--查看存储过程参数sp_help find--存储过程相关性sp_depends find------------------------------------------------------------创建一个INSERT信息的触发器use pubsgo--if exists (select name from sysobjects where name='employeeinfo' and type='p')-- drop trigger employeeinfocreate trigger employeeinfo on employeefor insert,updateasdeclare @min_lvl tinyint,@max_lvl tinyint,@job_id smallint,@pub_id varchar(10),@job_lvl tinyintselect @min_lvl = j.min_lvl,@max_lvl = j.max_lvl,@pub_id =e.pub_id,@job_id = e.job_idfrom employee e,jobs j,publishers pwhere e.pub_id = p.pub_id and e.job_id = j.job_idif(not @job_lvl between @min_lvl and @max_lvl)beginraiserror('the level for job_id:%d should be between %dand %d .',16,1,@job_id,@min_lvl,@max_lvl)rollback transactionendinsert into employee(emp_id,fname,lname,job_id,job_lvl,pub_id) --@job_lvl范围,emp_id约束控制values('PMA42623M','huang','wei',6,130,9999)--创建修改UPDATE触发器use pubsgocreate trigger unemployee on employeefor updateas raiserror ('update has been done successfully',16,10)update employee set fname='huang' where emp_id='PMA42628M' select fname from employee where emp_id = 'PMA42628M'--使用delete触发器use pubsgocreate trigger dpub_info on pub_infofor deleteif(@@rowcount=0)returnif(@@rowcount>1)beginrollback transactionraiserror ('you can only delete one information at one time',16,1) endreturndelete from pub_info where pub_id='0736' --删除select * from pub_info where pub_id = '0736' --查询select * into #tmp from pub_info where pub_id='0736' --备份select * from pub_info --查询delete from pub_info where pub_id='0736' --删除select * from pub_info where pub_id = '0736' --查询insert into pub_info select * from #tmp --添加drop table #tmp --删除--修改触发器名称exec sp_rename oldname,newname--触发器信息exec sp_help /sp_helptext /sp_depends------------------------------------------------------------一个有返回值的存储过程/*use book_managegocreate table student(sno char(5) not null primary key ,sname varchar(10) not null unique,ssex char(2) not null,sage int,sdept char(15))create table coures(cno char(5) not null primary key,cname varchar(10) not null,cpno char(5) not null,ccredit char(1))create table sc(sno char(5) not null,cno char(5) not null,grade float(3))if exists select name from sysobjects where name='Scroe' and type='p'drop proc Scroegocreate proc Scroe@Class char(4),@Cname varchar(30),@avg decimal output,@max decimal outputasdeclare @erroesave intset @errorsave=0select @avg=age(成绩) from 成绩表as sc inner join 学生表as ston sc.学号= st.学号inner join 课程表as coon sc.课程编号= co.课程编号group by 班级,课程名称having 班级= @class and 课程名称= @Cnameif(@@error<>0)set @errorsave=@@errorselect @max=mac(成绩)from 成绩表as sc inner join 学生表as ston sc.学号=st.学号inner join 课程号as coon sc.课程编号=co.课程编号group by 班级,课程名称having 班级=@class and 课程名称= @Cnameif(@@error<>0)set @errorsave = @@errorreturn @errorsavegodeclare @retcode int,@avggrade decimal,@maxgrade decimaldeclare @retcode=Scroe '0203','数据结构',@avggrade output,@maxgrade outputgo*/--------------------------------------------------------------------删除重复信息,使用临时表select distinct * from tb_authorinfoselect distinct * into #tmp from tb_authorinfodrop table tb_authorinfoselect * into tb_authorinfo from #tmpdrop table #tmp。