use master
go
/*
查詢(xún)數據庫系統表 如果該數據庫存在 就刪除該數據庫
*/
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
ON PRIMARY
(
filename='d:\project\stuDB.mdf',
name='stuDB',
size=1mb,
filegrowth=15%
)
log on
(
name='stuDB_Log',
filename='d:\project\stuDB.log',
size=1mb,
filegrowth=15%
)
GO
use StuDB
go
--判斷當前新建表是否已經(jīng)存在
if exists(select * from sysobjects where [name]='stuInfo' and type='u')
drop table stuInfo
create table stuInfo
(
stuName varchar(20) not null,
stuNo varchar(6) check(stuNo like 's253__') primary key,
stuSex varchar(8) check(stuSex='男' or stuSex='女') default '男' not null,
stuAge int check(stuAge between 15 and 50) not null,
stuSeat int identity(1,1) check(stuSeat<=30) not null,
stuAddress text default '地址不詳',
)
--檢查當前新建表是否存在
if exists(select * from sysobjects where [name]='stuMarks' and type='u')
drop table stuMarks
create table stuMarks
(
examNo varchar(11) check(examNo like 'E200507____') primary key,
stuNo varchar(6) foreign key references stuInfo(stuNo) not null,
writtenExam decimal(8,2) check(writtenExam between 0 and 100) default 0,
labExam decimal(8,2) check(labExam between 0 and 100) default 0,
)
go
insert into stuInfo values('張秋麗','s25301','男',18,'北京海淀')
insert into stuInfo values('李斯文','s25303','女',18,'河南洛陽(yáng)')
insert into stuInfo values('李文才','s25302','男',18,default)
insert into stuInfo values('歐陽(yáng)俊雄','s25304','男',28,'新疆威武哈')
select * from stuInfo
insert into stuMarks values('E2005070001','s25301',80,58)
insert into stuMarks values('E2005070002','s25302',50,default)
insert into stuMarks values('E2005070003','s25303',97,82)
select * from stuMarks
select * from stuInfo where stuSex='男'
select stuNo as 學(xué)號 ,writtenExam as 筆試成績(jì) from stuMarks
where writtenExam between 75 and 100
select stuInfo.stuName as 學(xué)員姓名,stuMarks.writtenExam as 筆試成績(jì),stuMarks.labExam as 機試成績(jì)
from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
select avg(writtenExam) as 筆試平均分 from stuMarks
select avg(labExam) as 機試平均分 from stuMarks
select count(*) as 參加考試的人數 from stuInfo inner join stuMarks
on stuInfo.stuNo=stuMarks.stuNo
select count(*) as 不及格人數 from stuMarks inner join stuInfo
on stuInfo.stuNo=stuMarks.stuNo and (writtenExam<60 or labExam<60)
select stuNo as 學(xué)號,(writtenExam+labExam)/2 as 平均分,
writtenExam as 筆試成績(jì),labExam as 機試成績(jì)
from stuMarks
update stuMarks set writtenExam=writtenExam+5 where writtenExam<95
update stuMarks set writtenExam=100 where writtenExam>=95
---創(chuàng )建SQL登陸賬戶(hù) 第一個(gè)參數是登錄名 第二個(gè)參數是密碼
exec sp_addlogin 'banzhuren','accp'
---為登陸帳戶(hù)賦予數據庫訪(fǎng)問(wèn)權限 第一個(gè)是用戶(hù)名 第二個(gè)是角色
exec sp_grantdbaccess 'banzhuren','accp'
select * from sysUsers
grant select,update,insert ,delete on stuInfo to accp
grant select on stuMarks to accp
---創(chuàng )建SQL登陸賬戶(hù) 第一個(gè)參數是登錄名 第二個(gè)參數是密碼
exec sp_addlogin 'jiaoyuan','accp'
---為登陸帳戶(hù)賦予數據庫訪(fǎng)問(wèn)權限 第一個(gè)是用戶(hù)名 第二個(gè)是角色
exec sp_grantdbaccess 'jiaoyuan','jiaoyuan'
select * from sysUsers
grant select,update,insert ,delete on stuMarks to jiaoyuan
grant select on stuInfo to jiaoyuan
//===================================================
use Demo
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','勝')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-09','負')
insert into #tmp values('2005-05-10','勝')
insert into #tmp values('2005-05-10','負')
insert into #tmp values('2005-05-10','負')
select * from #tmp
select rq, sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq
select rq,sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq
select N.rq,N.勝,M.負 from (
select rq,勝=count(*) from #tmp where shengfu='勝'group by rq)N inner join
(select rq,負=count(*) from #tmp where shengfu='負'group by rq)M on N.rq=M.rq
select a.col001,a.a1 勝,b.b1 負 from
(select col001,count(col001) a1 from temp1 where col002='勝' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='負' group by col001) b
where a.col001=b.col001
2.請教一個(gè)面試中遇到的SQL語(yǔ)句的查詢(xún)問(wèn)題
表中有A B C三列,用SQL語(yǔ)句實(shí)現：當A列大于B列時(shí)選擇A列否則選擇B列，當B列大于C列時(shí)選擇B列否則選擇C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面試題：一個(gè)日期判斷的sql語(yǔ)句？
請取出tb_send表中日期(SendTime字段)為當天的所有記錄?(SendTime字段為datetime型，包含日期與時(shí)間)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一張表，里面有3個(gè)字段：語(yǔ)文，數學(xué)，英語(yǔ)。其中有3條記錄分別表示語(yǔ)文70分，數學(xué)80分，英語(yǔ)58分，
請用一條sql語(yǔ)句查詢(xún)出這三條記錄并按以下條件顯示出來(lái)（并寫(xiě)出您的思路）：
大于或等于80表示優(yōu)秀，大于或等于60表示及格，小于60分表示不及格。
顯示格式：
語(yǔ)文 數學(xué) 英語(yǔ)
及格 優(yōu)秀 不及格
------------------------------------------
select
(case when 語(yǔ)文>=80 then '優(yōu)秀'
when 語(yǔ)文>=60 then '及格'
else '不及格') as 語(yǔ)文,
(case when 數學(xué)>=80 then '優(yōu)秀'
when 數學(xué)>=60 then '及格'
else '不及格') as 數學(xué),
(case when 英語(yǔ)>=80 then '優(yōu)秀'
when 英語(yǔ)>=60 then '及格'
else '不及格') as 英語(yǔ),
from table
5.在sqlserver2000中請用sql創(chuàng )建一張用戶(hù)臨時(shí)表和系統臨時(shí)表，里面包含兩個(gè)字段ID和IDValues,類(lèi)型都是int型，并解釋下兩者的區別?
------------------------------------------
用戶(hù)臨時(shí)表:create table #xx(ID int, IDValues int)
系統臨時(shí)表:create table ##xx(ID int, IDValues int)
區別:
用戶(hù)臨時(shí)表只對創(chuàng )建這個(gè)表的用戶(hù)的Session可見(jiàn),對其他進(jìn)程是不可見(jiàn)的.
當創(chuàng )建它的進(jìn)程消失時(shí)這個(gè)臨時(shí)表就自動(dòng)刪除.
全局臨時(shí)表對整個(gè)SQL Server實(shí)例都可見(jiàn),但是所有訪(fǎng)問(wèn)它的Session都消失的時(shí)候,它也自動(dòng)刪除.
6.sqlserver2000是一種大型數據庫，他的存儲容量只受存儲介質(zhì)的限制，請問(wèn)它是通過(guò)什么方式實(shí)現這種無(wú)限容量機制的。
------------------------------------------
它的所有數據都存儲在數據文件中(*.dbf),所以只要文件夠大,SQL Server的存儲容量是可以擴大的.
SQL Server 2000 數據庫有三種類(lèi)型的文件：
主要數據文件
主要數據文件是數據庫的起點(diǎn)，指向數據庫中文件的其它部分。每個(gè)數據庫都有一個(gè)主要數據文件。主要數據文件的推薦文件擴展名是 .mdf。
次要數據文件
次要數據文件包含除主要數據文件外的所有數據文件。有些數據庫可能沒(méi)有次要數據文件，而有些數據庫則有多個(gè)次要數據文件。次要數據文件的推薦文件擴展名是 .ndf。
日志文件
日志文件包含恢復數據庫所需的所有日志信息。每個(gè)數據庫必須至少有一個(gè)日志文件，但可以不止一個(gè)。日志文件的推薦文件擴展名是 .ldf。
7.請用一個(gè)sql語(yǔ)句得出結果
從table1,table2中取出如table3所列格式數據，注意提供的數據及結果不準確，只是作為一個(gè)格式向大家請教。
如使用存儲過(guò)程也可以。
table1
月份mon 部門(mén)dep 業(yè)績(jì)yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部門(mén)dep 部門(mén)名稱(chēng)dname
--------------------------------
01 國內業(yè)務(wù)一部
02 國內業(yè)務(wù)二部
03 國內業(yè)務(wù)三部
04 國際業(yè)務(wù)部
table3 （result）
部門(mén)dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部門(mén)名稱(chēng)dname,b.業(yè)績(jì)yj as '一月份',c.業(yè)績(jì)yj as '二月份',d.業(yè)績(jì)yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部門(mén)dep = b.部門(mén)dep and b.月份mon = '一月份' and
a.部門(mén)dep = c.部門(mén)dep and c.月份mon = '二月份' and
a.部門(mén)dep = d.部門(mén)dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
