SQL server#1

=============================

数据库建立

=============================

create database mydb
on primary
(
name='mydb',
filename='D:projectmydb_mdf.mdf',
size=10mb,
maxsize=200mb,
filegrowth=10%
)
log on
(
name='mydb1',
filename='D:projectmydb_log.ldf',
size=10mb,
maxsize=200mb,
filegrowth=10%
)
use mydb
go

create table student
(
stuid int not null primary key identity(1,1),
stuname varchar(10) not null unique,
stuage smalldatetime not null check(stuage>'1980-1-1'and stuagestuaddress varchar(20) default '地址不详',
)
create table subject
(
subid int not null primary key identity(2,1),
subname varchar(20) not null
)
create table stuscores
(
scoresid int not null primary key identity(3,1),
scores numeric(8,1)not null,
stuid int not null references student(stuid),
subid int not null references subject(subid)
)

insert into student values('张朝阳','1984-1-1','居无定所')
insert into subject values('c语言')
insert into stuscores values(50,1,2)

alter table subject add constraint CK_subname check(subname not like'a%' )
================================

sql的全局变量

================================

--输出信息
print '最后一次错误'+convert(varchar(5),@@error)

print '当前使用的语言的名称'+@@language

print 'max_connection'+convert(varchar(20),@@max_connections)

print '受上一个sql语句影响的行数'+convert(varchar(20),@@rowcount)

print '本地服务器的名称'+@@servername

print '该计算机上的sql服务的名称'+@@servicename

print '当前计算机上每刻度的微秒数'+convert(varchar(100),@@timeticks)

print '当前连接打开的事务数'+@@transcount--未能显示，不知为何？

print 'sql server 的版本'+@@version

================================================

流程控制语句

================================================

----------------------------------------------
--查找姓为Green的电话前三位与其他人相同的记录
----------------------------------------------
declare @name varchar(20)
set @name = 'Green'
declare @number varchar(3)
select @number = substring(phone,0,3) from authors where au_lname = @name
select * from authors where substring(phone,0,3) = @number
------------------------------------------------------------------------

----------------------------------------------------------------------
--查询图书平均价格大于30块的记录如果有则显示前三条，并打印信息
--否则也显示前三条，并打印信息
---------------------------
declare @avg float
select @avg=avg(price) from titles
if(@avg>30)
begin
print '这些作者写的书好贵'
select top 3 * from titles order by price desc
end
else
begin
print '这些书还很便宜'
select top 3 * from titles order by price asc
end
----------------------------------------------------------------------
------------------------------------------
--判断书的价格是否有低于5块的，如果有每本书每次加价2块，显示增加后的结果
------------------------------------------
declare @count int
select @count=count(*) from titles where price<5
if(@count!=0)
begin
while(1=1)
begin
update titles set price = price + 2
select @count=count(*) from titles where price<5
if(@count=0)
break
end
end

select * from titles

-------------------------------------------------------------
--图书价格登记划分:
--大于5块小于10块，好书
--大于10块小于15块，一般书
--大于15块小于30块，让让人活，买了书吃啥？
-------------------------------------------------------------

select title_Id,title,price, 等级划分=case when price between 5 and 10 then '好书'
when price between 10 and 15 then '一般书'
when price between 15 and 30 then '让不让人活，买了书吃啥?'
else
'看情况再买吧'
end
from titles

==================================

sql的权限

==================================

--判断是否存在这个数据库，如果存在先删除再建立，如果不存在直接建立
if exists(select * from sysdatabases where name='sqlchapter3')
drop database sqlchapter3
create database sqlchapter3
on primary
--建立主数据文件
(
name='dab1',
filename='d:123db_mdf.mdf',
size=10mb,
maxsize=100mb,
filegrowth=10%
)
--日志文件的建立
log on
(
name='db2',
filename='d:123db_ldf.ldf',
size=12mb,
maxsize=100mb,
filegrowth=10%
)
--转到建好的数据库
use sqlchapter3
go

--判断是否存在这个表如果存在就删除，如果不存在直接建立
if exists (select * from sysobjects where name='studb')
drop table studb
create table studb
(
studid int not null primary key identity(1,1),
stuname varchar(20) not null ,
stuage smalldatetime not null check(stuage>'1980-1-1' and stuagestuaddress varchar(50) default'地址不详',
stuscoresid int not null
)

select * from studb
--更改年龄的check约束
alter table studb
add constraint CK_stuage check(stuage<>0)
--更改地址的长度
alter table studb
alter column stuaddress varchar(100)

create table scores
(
scoresid int not null primary key identity(2,1),
scores float not null check(scores>0 and scores<100)
)
--更改成绩编号的外键
alter table studb
add constraint RF_stuscoresid foreign key (stuscoresid) references scores(scoresid)

--删除约束
alter table studb
drop constraint CK_stuage
--创建登陆帐户
exec sp_addlogin 'kong','123'
--设定kong登陆时直接进入sqlchapter3这个数据库
exec sp_defaultdb 'kong','sqlchapter3'
--给kong设置数据库访问权限
exec sp_grantdbaccess 'kong','sqlchapter3'
exec sp_revokedbaccess 'sqlchapter3'
exec sp_grantdbaccess 'kong','kong'
--授权
grant select,insert,update,delete on studb TO kong

--收回权限
revoke select,insert,delete,update on studb to kong

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10395457/viewspace-933894/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10395457/viewspace-933894/