SQL整套学习资料

奉献给SQL初学者们的终极教材

<上一篇 | 下一篇>

此教材可以说涉及的范围是非常广的。。我们平常写的SQL语句都是出现在里面的。。且每一种方法都有案例，所以说如果你把所有的案例应用理解透的话。。可以说你已经成为高手了。。知识都是得靠自己去掌握的。。多看，多想。多问。多动手。相信你一定很快掌握的。。

use master

go

--创建数据库book_manage

create database book_manage

on

(

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_manage

go

create 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)

)

go

create 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','https://www.360docs.net/doc/261406663.html,',54.5,'A004','P106')

insert into tb_authorinfo values('A004','黄薇','男',25,'济南')

insert into tb_pubinfo values('P106','山东出版社','济南')

--查询各个表信息

select * from tb_bookinfo

select * from tb_pubinfo

select * from tb_authorinfo

--备份一个新表

select * into Btb_bookinfo from tb_bookinfo

select * into Btb_authorinfo from tb_authorinfo

select * 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_bookinfo

select publishid from tb_bookinfo

select 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_bookinfo

select * 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_id

select * 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_a

insert into Btbl_a select * from tbl_a

select * 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_manage

go

exec 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_manage

go

exec 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_manage

go

if exists (select * from sysobjects where name='pub_book')

drop view pub_book

go

create view pub_book

as

select a.bookname,a.price,b.authorname

from tb_bookinfo as a inner join tb_authorinfo as b

on a.authorid=b.authorid inner join tb_pubinfo as c

on a.publishid=c.publishid

where c.pubname='清华大学出版社'

go

--查看视图

select * from pub_book

create view authinfo(作者编号,姓名,性别,年龄)

as

select authorid,authorname,sex,age

from tb_authorinfo

go

select * from authinfo

--创建一个加密不能修改视图

use pubs

go

if exists(select table_name from information_schema.views where table_name = 'emprange')

drop view emprange

as

create view emprange(emp_id,fname,lname,pub_id,job_id)

with encryption

as

select emp_id,fname,lname,pub_id,job_id

from employee

where job_id between 11 and 12 with check option

go

select * from emprange

go

update emprange set job_id='5' where emp_id='PCM98509F'

--不让修改原因为建立视图上面做了限制修改(with check option)

go

exec sp_helptext 'emprange'

--显示该视图已经加密,不能修改,只有查看信息功能

go

------------------------------------------------------------

--修改view信息表

use pubs

go

create view all_authors(au_fname,au_lname,address,city,zip) as

select au_fname,au_lname,address,city,zip

from authors

go

--给视图select权限给public

grant select on all_authors to public

--修改视图查询

alter view all_authors(au_fname,au_lname,address,city,zip) as

select au_fname,au_lname,address,city,zip

from authors

where state='UT'

go

----------------------------------------------------------

--添加视图

create view yourview

as

select title_id,title,mycount=@@rowcount,ytd_sales

from titles

go

select * from yourview

alter view yourview

as

select title_id,title,mycount=@@rowcount,ytd_sales

from titles

where type = 'mod_cook'

go

select * from yourview

--删除视图

drop view all_authors

--------------------------------------------------------

--修改查询信息

alter view all_authors(姓名,地址,城市,邮编,电话)

with encryption

as

select au_fname+space(1)+au_lname,address,city,zip,phone

from authors

with check option

go

---------------------------------------------------------------

--修改视图名称

use book_manage

go

exec sp_rename '作者信息视图','authinfo'

exec sp_rename 'authinfo','作者信息视图'

---------------------------------------------------------------

use book_manage

go

create view v_author(authorid,authorname,age,sex,authaddress)

as

select authorid,authorname,age,sex,authaddress

from tb_authorinfo

where authorname = '张丽'

go

select * from v_author

--在视图中添加信息

insert into v_author values('A009','李风',22,'男','大连')

--------------------------------------------------------

--在视图中添加修改信息

create view v_author1

as

select * from tb_authorinfo

--修改信息

go

update v_author1 top 1 set authorname='李峰' where authorname='李芬' ----------------------------------------------------------

use pubs

go

select * from employee

declare @emp_id varchar(20),@TempLastName varchar(25)

set @emp_id=99

select @emp_id=emp_id,@TempLastName=lname from employee where fname='Paolo' --order by emp_id

go

select count(*) from employee

--select emp_id,lname from employee where fname = 'Paolo'

print 'sqlserver的版本'+@@version

print '服务器的名称:'+@@servername

insert 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 marks

select * from info

--添加表的约束于默认值等

alter table info add constraint PK_no primary key (no) --添加约束NO

alter table info add constraint UQ_id Unique (id) --添加ID

alter 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_marks

go

create view view_info_marks

as

select 姓名=name,学号=info.no,笔试成绩=write,机试成绩=lab,平均分

=floor(write+lab)/2 from info left join marks on info.no = marks.no

go

select * from view_info_marks

--逻辑控制语句

declare @myavg float

select @myavg=avg(write) from marks

print '本班平均分'+convert(varchar(5),@myavg)

if(@myavg>75)

begin

print '本班比试成绩优秀,前三名的成绩为'

select top 3 * from marks order by write desc

end

else

begin

print '本版比试成绩较差,后三名的成绩为'

select top 3 * from marks order by write

end

use book_manage

go

--查询个人平均分于等级

select * from marks

select *,(write+lab)/2 as 平均分,成绩= case

When (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'

end

from marks

*/

-----------------------------------------------------------------

--查询存储过程

use pubs

go

if exists (select name from sysobjects where name = 'oakland_authors' and type ='p')

drop proc oakland_authors

go

create proc oakland_authors

as

select au_fname,au_lname,address,city,zip

from pubs..authors where city='oakland' and state='ca'

order by au_lname,au_fname

GO

--查看该存储过程原代码

select o.id,c.text from sysobjects o inner join syscomments c

on o.id =c.id

where o.type = 'p' and https://www.360docs.net/doc/261406663.html, = 'oakland_authors'

go

alter proc oakland_authors

with encryption --加密

as

select au_fname,au_lname,address,city,zip

from pubs.authors

where state = 'ca' order by au_lname,au_fname

go

--select * from oakland_authors

------------------------------------------------------------------------

--创建一个带参数的存储过程

if exists (select name from sysobjects where name = 'au_info' and type ='p') drop proc au_info

go

create proc au_info

@lastname char(20),@fristname char(20)

as

select au_lname,au_fname,title,pub_name

from authors a inner join titleauthor ta

on a.au_id = ta.au_id inner join titles t

on t.title_id=ta.title_id join publishers p

on t.pub_id=p.pub_id

where au_fname=@fristname and au_lname=@lastname

GO

--declare @write int,@lastname char(20),@fristname char(20)

--exec @write = au_info 'White','Johnson'

exec au_info @lastname='White',@fristname='Johnson'

use book_manage

go

if exists (select name from sysobjects where name = 'find' and type='p')

drop proc find

go

create proc find @auname varchar(20)

as

if exists (select * from tb_authorinfo where authorname = @auname) return 1

else

return 0

go

declare @result int

exec @result=find @auname='赵阳'

--/exec @result = find '赵阳'

if(@result=1)

print '找到此人'

else

print '查无此人'

go

--查看存储过程

sp_helptext find

--查看存储过程参数

sp_help find

--存储过程相关性

sp_depends find

----------------------------------------------------------

--创建一个INSERT信息的触发器

use pubs

go

--if exists (select name from sysobjects where name='employeeinfo' and type='p')

-- drop trigger employeeinfo

create trigger employeeinfo on employee

for insert,update

as

declare @min_lvl tinyint,@max_lvl tinyint,@job_id smallint,@pub_id varchar(10),@job_lvl tinyint

select @min_lvl = j.min_lvl,@max_lvl = j.max_lvl,@pub_id =

e.pub_id,@job_id = e.job_id

from employee e,jobs j,publishers p

where e.pub_id = p.pub_id and e.job_id = j.job_id

if(not @job_lvl between @min_lvl and @max_lvl)

begin

raiserror('the level for job_id:%d should be between %d

and %d .',16,1,@job_id,@min_lvl,@max_lvl)

rollback transaction

end

insert 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 pubs

go

create trigger unemployee on employee

for update

as 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 pubs

go

create trigger dpub_info on pub_info

for delete

if(@@rowcount=0)

return

if(@@rowcount>1)

begin

rollback transaction

raiserror ('you can only delete one information at one time',16,1) end

return

delete 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_manage

go

create 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 Scroe

go

create proc Scroe

@Class char(4),@Cname varchar(30),@avg decimal output,@max decimal output

as

declare @erroesave int

set @errorsave=0

select @avg=age(成绩) from 成绩表as sc inner join 学生表as st

on sc.学号= st.学号inner join 课程表as co

on sc.课程编号= co.课程编号

group by 班级,课程名称

having 班级= @class and 课程名称= @Cname

if(@@error<>0)

set @errorsave=@@error

select @max=mac(成绩)

from 成绩表as sc inner join 学生表as st

on sc.学号=st.学号inner join 课程号as co

on sc.课程编号=co.课程编号

group by 班级,课程名称

having 班级=@class and 课程名称= @Cname

if(@@error<>0)

set @errorsave = @@error

return @errorsave

go

declare @retcode int,@avggrade decimal,@maxgrade decimal

declare @retcode=Scroe '0203','数据结构',@avggrade output,@maxgrade output

go

*/

------------------------------------------------------------------

--删除重复信息,使用临时表

select distinct * from tb_authorinfo

select distinct * into #tmp from tb_authorinfo

drop table tb_authorinfo

select * into tb_authorinfo from #tmp

drop table #tmp