關于資料庫的一些操作

1.關于使用者的操作

--注冊windows登陸帳号
exec sp_grantlogin 'niit8-2\pl'
exec sp_droplogin 'niit8-2\pl'

--建立角色
exec sp_addrole 'r_test'
--為角色配置設定資料表權限
grant all on stuinfo to r_test
grant update,delete,insert on stutemp to r_test
--添加登入使用者，密碼及預設的資料庫
exec sp_addlogin 'test','123456','student'


exec sp_defaultdb 'student'
--添加使用者安全帳戶
exec sp_grantdbaccess 'test','u_test'
--添加u_test為角色r_test的角色
exec sp_addrolemember 'r_test','u_test'
--拒絕使用者對stutemp表進行更新操作
deny select on stutemp to u_test


--删除
exec sp_droplogin 'test'
exec sp_revokedbaccess 'u_test'
exec sp_droprole 'r_test'


select * from stuinfo
           

2.建立庫和表

--建立資料庫
create database studb
on primary
(
name='studb',
filename='d:\studb.mdf',
size=3mb,
maxsize=100mb,
filegrowth=10%
)

log on
(
	name='studb_log',
	filename='d:\studb.ldf',
	size=1mb,
	filegrowth=1mb
)
go

use studb
--建立表
create table score
(
	
	
	id int identity(1,1) primary key,
	studentsid int,
	courseid int ,
	score int
)
go

create table course
(
	
	
	id int identity(1,1) primary key,
	courseid int,
	courseName nvarchar(20) ,
)
go
--删庫
if exists(select * from sysdatabases where name='studb')
drop database studb

--删表
if exists(select * from sysobjects where name='score')
drop table score


use student
go

select * from stuinfo
select * from score

insert into course
select 4,'地理' union
select 5,'曆史' union
select 3,'英語' union
select 2,2,78 union
select 6,2,68 union
select 4,3,76 union
select 4,1,76 union
select 5,2,55 union
select 6,3,79 



alter table score
add constraint fk_stuid_studentsid
foreign key(studentsid) 
references stuinfo(stuid)

select * from stuinfo
select * from score

select stuid,stuinfo.stuname,courseid,score
from  stuinfo left join score
on score.studentsid=stuinfo.stuid


select A.stuname,C.coursename,B.score
from stuinfo as A
left join
score as B
on A.stuid=B.studentsid 
right join
course as C
on C.courseid=B.courseid

select stuage,IDENTITY(int,1,1) AS ID 
into temptable
from stuinfo

select * from temptable

select avg(stuage) as 奇數行平均年齡
from temptable
where id%2=1

insert into score(studentsid,courseid,score)
select stuid,0,0
from  stuinfo left join score
on score.studentsid=stuinfo.stuid
where score.score is null


select * from score

delete from score where studentsid is null


           

資料庫中的函數

select stuid,stuaddress+'.'+stuname as 姓名,stusex as 性别
 from stuinfo 
where stuname like '王五'

select * from stuinfo

order by stuname desc

insert into stuinfo
values(9,'test',0,'','')

update stuinfo 
set stuname='姓名不詳',stuSex='1'
where stuid=9

select *,'學員' as 角色 from stuinfo 
where stuEmail is not null

select * from stuinfo order by stuaddress,stuname

update stuinfo set stuname='王五1'
where stuid=5

insert stuinfo values(10,'王五2',0,'','北京')

select * from stuinfo 
where stuaddress='上海' and stusex=1
--字元串函數
select charindex('abc','123abc456')
select len('12345678')
select lower('ABCefg')
select upper('ABCefg')
select ltrim('  ABCDEFG  ')
select rtrim(' ABCDEFG  ')
select right('ABCDEDFG',3)
select left('ABCDEFG',3)
select replace('ABCDEFG','CD','test')
select stuff('ABCDEFG',2,3,'test')

--日期函數
select getdate()
select dateadd(dd,-50,'9-1-2011')
select datediff(yy,'2011-9-1','1980-8-6')
select datename(dw,'2011-10-1')
select datepart(YY,'2011-9-1')

--數字函數
select abs(-100)
select ceiling(23.1)
select floor(23.9)
select power(8,4)
select round(round(23.45,1),0)
select sign(-100)
select sqrt(9.9)

--系統函數
select convert(nvarchar(10),123456)
select CURRENT_USER
select datalength('123１２３')
select HOST_NAME()
select SYSTEM_USER
SELECT USER_NAME(1)

select * from stutemp1

update stutemp1 
set stuname=replace(replace(stuname,'真的姓名','姓名'),'真的','')

select stuname as 姓名
 from stuinfo
union
select stuname as 姓名
from stutemp1
order by 姓名 desc

select * from stutemp1 
where stuname =''

update stutemp1 set stuname=''
where id>9

select * from stutemp1
where stuid between 2 and 9

select * from stutemp1
where stuid>=2 and stuid<=9

select * from stutemp1
where stuaddress 
in('上海','天津','無錫')

select * from stuinfo
select sum(stuage) from stuinfo
select avg(stuage) from stuinfo
select max(stuage) from stuinfo
select min(stuage) from stuinfo
select count(*) 
from stuinfo
where stuEmail is not null and stuEmail <> ''

select (yuwen+suxue+yinyu)/3

select stuaddress,count(*),avg(stuage) as 平均年齡 
from stuinfo
where stuaddress<>''
group by stuaddress
having avg(stuage)>=22
order by 平均年齡


select stuid,avg(stuage) from stuinfo
group by stuid
order by stuid

select stuaddress,avg(stuage) as 平均年齡
from stuinfo
group by stuaddress


select * from stuinfo

           

建立限制

--注冊windows登陸帳号
exec sp_grantlogin 'niit8-2\pl'
exec sp_droplogin 'niit8-2\pl'

--建立角色
exec sp_addrole 'r_test'
--為角色配置設定資料表權限
grant all on stuinfo to r_test
grant update,delete,insert on stutemp to r_test
--添加登入使用者，密碼及預設的資料庫
exec sp_addlogin 'test','123456','student'
--exec sp_defaultdb 'student'
--添加使用者安全帳戶
exec sp_grantdbaccess 'test','u_test'
--添加u_test為角色r_test的角色
exec sp_addrolemember 'r_test','u_test'
--拒絕使用者對stutemp表進行更新操作
deny select on stutemp to u_test


--删除
exec sp_droplogin 'test'
exec sp_revokedbaccess 'u_test'
exec sp_droprole 'r_test'

select * from stutemp1
select * from stutemp

--添加主鍵限制
alter table stutemp1 
add constraint pk_id 
primary key (id)

--添加外鍵限制
alter table stutemp
add constraint fk_stuid_id 
foreign key(stuid)
references stutemp1(id)

--添加唯一限制
alter table stutemp1
add constraint  un_stuid 
unique(stuid) 

--添加預設值
alter table stutemp1
add constraint de_stuname
default('姓名不詳') for stuname

--添加檢查限制
alter table stutemp1
add constraint ch_stuid
check(stuid<50)

--删除外鍵限制
alter table stutemp
drop constraint fk_stuid_id

--禁用限制
alter table stutemp1
nocheck constraint ch_stuid


           

視圖和存儲過程

use student
select * from stuinfo
select * from score
select * from course
delete from stuinfo where stuid=1
update stuinfo set stuid=12 
where stuName='郭靖'

select stuid,stuName,courseName,score
from stuinfo
join score on stuid=studentsid
join course on score.courseid=course.courseid

create view
view_stuinfo_score_course
as
select stuid,stuName,courseName,score
from stuinfo
join score on stuid=studentsid
join course on score.courseid=course.courseid

select * from view_stuinfo_score_course

select * from stuinfo
select * from score

create view 
view_stuinfo_score
as
select stuId,stuName,stuage,courseid,score
from stuinfo
join
score
on stuinfo.stuid=score.studentsid
go

select * from view_stuinfo_score

--針對一張基表的更新可以執行
update view_stuinfo_score set stuName='王二'
where stuName='王五'
--針對多張基表的更新不可以執行
update view_stuinfo_score 
set stuName='王五',score=85
where stuName='王二'
--針對一張基表的資料插入可以執行
insert into 
view_stuinfo_score
(stuid,stuName,stuage)
values(11,'郭靖',30)
--針對多張基表的資料插入不可以執行
insert into 
view_stuinfo_score
(stuid,stuname,stuage,courseid,score)
values(12,'test',40,3,99)

exec sp_databases

--create建立存貯過程，alter修改存貯過程，drop删除存貯過程
--insert
create proc insert_stuinfo
@stuName nvarchar(10)='未知',
@stuage int,
@stuSex bit,
@stuEmail nvarchar(50),
@stuAddress nvarchar(50)='未知'
as
insert into stuinfo
values(@stuName,@stuage,@stuSex,@stuEmail,@stuaddress)
go

--update
alter proc update_stuinfo
@stuid int,
@stuaddress varchar(20)
as
declare @max int
declare @min int
select @max=max(stuid) from stuinfo
select @min=min(stuid) from stuinfo
if @stuid<@min or @stuid>@max
	raiserror('您輸入的學員編号不正确！',1,1)
else
begin
	begin tran
	update stuinfo set [email protected]
	where [email protected]
	if(@@error>0)
		rollback tran
	else
		commit tran
end
--delete
create proc delete_stuinfo
@stuid int
as 
delete from score where [email protected]
delete from stuinfo where [email protected]
go

--添加學員資訊 參數：學員姓名（預設值‘未知’）,學員年齡，學員性别，學員Email,學員位址（預設值:未知）
exec insert_stu default,22,1,null
exec update_stu 0,'北京'
exec delete_stu 16

rollback tran

update score set studentsid=14
where studentsid=8

select * from score
where score.studentsid in 
(select stuid from stuinfo)


create view
view_stuinfo
as
select * from stuinfo

--統計學員的平均年齡
create proc avgAge_stuinfo
@avgage int output
as
select @avgage=avg(stuage)
from stuinfo

declare @age int
exec avgage_stuinfo @age output
print @age
           

事務

use test
CREATE TABLE bank
(
    customerName CHAR(10),  --顧客姓名
    currentMoney MONEY     --目前餘額
)
GO
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney    
       CHECK(currentMoney>=1)
GO
INSERT INTO bank(customerName,currentMoney)
        VALUES('張三',1000)
INSERT INTO bank(customerName,currentMoney)
        VALUES('李四',1)

delete from bank

begin transaction 
declare @error int
set @error=0

update bank 
set currentMoney=currentMoney+800
where customerName='李四'
set @error=@@error

update bank 
set currentMoney=currentMoney-800 
where customerName='張三'

set @[email protected]+@@error
if(@error=0)
	begin
		print '轉帳成功'
		commit transaction
	end
else
	begin
		print '轉帳失敗'
		rollback transaction
	end 

select * from bank

select * from stuinfo
begin transaction

insert into stuinfo 
values(1,'張三',1,'','上海')

update stuinfo set stuId=2 

commit transaction

rollback transaction
use student
select * from stuinfo 


CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]  
INDEX   index_name
ON table_name (column_name…)

create clustered
index ix_stuName
on stuinfo(stuname)

create nonclustered
index ix_stuAddress
on stuinfo(stuaddress)

--删除索引
if exists(select * from sysindexes 
 where name='ix_stuaddress')
drop index stuinfo.ix_stuaddress

--使用索引
select * from stuinfo 
with(index=ix_stuname)
where stuaddress='上海'