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 '當前使用的語言的名稱

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

print '受上一個sql語句影響的行數'+convert(varchar(20),@@rowcount)

print '本地伺服器的名稱

print '該計算機上的sql服務的名稱

print '當前計算機上每刻度的微秒數'+convert(varchar(100),@@timeticks)

print '當前連線開啟的事務數--未能顯示，不知為何？

print 'sql server 的版本

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

流程控制語句

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

----------------------------------------------
--查詢姓為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@]