SQL整套学习资料.doc

奉献给SQL初学者们的终极教材 此教材可以说涉及的范围是非常广的。我们平常写的SQL语句都是出现在里面的。且每一种方法都有案例，所以说如果你把所有的案例应用理解透的话。可以说你已经成为高手了。知识都是得靠自己去掌握的。多看，多想。多问。多动手。相信你一定很快掌握的。use mastergo-创建数据库book_managecreate database book_manageon(name = book_manage_primary,filename = d:databook_manage.mdf,size = 10,maxsize = 20,filegrowth = 5)log on (name = book_manage_log,filename = d:databook_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_b(emp_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_bookinfo select * 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 sdzsuser-添加windows用户登陆sql 域名用户名exec sp_addlogin 2,2-添加sql用户登陆sql 用户名 密码use book_managegoexec sp_grantdbaccess sdzsuser,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 authors go-给视图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=UTgo-添加视图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_cookgoselect * from yourview-删除视图drop view all_authors-修改查询信息alter view all_authors(姓名,地址,城市,邮编,电话)with encryptionas select au_fname+space(1)+au_lname,address,city,zip,phone from authors with check optiongo-修改视图名称use book_managegoexec sp_rename 作者信息视图,authinfoexec sp_rename authinfo,作者信息视图-use book_managegocreate view v_author(authorid,authorname,age,sex,authaddress)asselect authorid,authorname,age,sex,authaddress from 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 = Paoloprint 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=4create 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.no goselect * from view_info_marks-逻辑控制语句declare myavg float select myavg=avg(write) from marks print 本班平均分+convert(varchar(5),myavg)if(myavg75)begin print 本班比试成绩优秀,前三名的成绩为 select top 3 * from marks order by write descendelsebegin print 本版比试成绩较差,后三名的成绩为 select top 3 * from marks order by writeenduse book_managego-查询个人平均分于等级select * from marksselect *,(write+lab)/2 as 平均分,成绩 = case When (write+lab)/21)begin rollback transaction raiserror (you can only delete one information at one time,16,1)end returndelete 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 c