此教材可以说涉及的范围是非常广的。。我们平常写的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','asp.net',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 o.name = '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 [email protected] and [email protected]

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) [email protected]_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 [email protected] 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

支持一下