关于数据库的一些操作

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 stuaddress=@stuaddress
	where stuid=@stuid
	if(@@error>0)
		rollback tran
	else
		commit tran
end
--delete
create proc delete_stuinfo
@stuid int
as 
delete from score where studentsid=@stuid
delete from stuinfo where stuid=@stuid
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 @error=@error+@@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='上海'