SQL实例(sqlserver)

　　姑且如此命名吧,是自己的一点实现,虽然我不喜欢为了更新而更新,不过看着有空白日期就是心里不爽,这篇算是最低级的了,对不熟悉SQL的人可能会有点帮助:)

create database bbs

create table bbsusers
(
uid int IDENTITY(1,1) primary key,
uname varchar(32) not null,
upassword varchar(16) not null default('88888') check(len(upassword)>=6),
uemail varchar(32) default('p@p.com') check(uemail like'%@%'),
ubirthday datetime,
usex bit not null default('1'),
uclass int default('1'),
ustatement varchar(255),
uregdate datetime not null default(getdate()),
ustate int default('1'),
upoint int default('20'),
)

create table bbssection
(
sid int identity(1,1) primary key,
sname varchar(32) not null,
smasterid int not null,
foreign key(smasterid) references bbsusers(uid),
sstatement varchar(255),
sclickcount int,
stopiccount int,
)

create table bbstopic
(
tid int identity(1,1) primary key,
tnumber varchar(32) not null,
tsid int not null,
foreign key(tsid) references bbssection(sid),
tuid int not null,
foreign key(tuid) references bbsusers(uid),
treplycount int,
tetmotion int,
ttopic varchar(255) not null,
tcontents ntext not null,
ttime datetime default(getdate()),
tclickcount int,
tflag int not null default('1'),
tlastclickt datetime default(getdate()),
)

create table bbsreply
(
rid int identity(1,1) primary key,
rnumber varchar(32) not null,
rtid int not null,
foreign key(rtid) references bbstopic(tid),
rsid int not null,
foreign key(rsid) references bbssection(sid),
ruid int not null,
foreign key(ruid) references bbsusers(uid),
remotion int,
rtopic varchar(255) not null,
rcontents ntext not null,
rtime datetime default(getdate()),
rclickcount int,
)


--查询
select * from bbsusers

--插入

insert bbsusers(uname,upassword,uemail,ubirthday,usex,uclass,ustatement,ustate,upoint)
select '岳飞','8888888','1@2.com','1985-09-09',1,1,'1',1,'30000' union
select '彭德怀','8898978','223@s.com','1984-09-09',1,1,'2',1,'30000' union
select '曹操','9898798','454@siu.com','1984-08-17',1,3,'2',3,'11111111' union
select 'sheshe','sheshe','454@siu.com','1984-08-17',1,3,'2',3,'11111111' union
select 'express','express','454@siu.com','1984-08-17',1,3,'2',3,'11111111' union
select '刘备','1357854','shu@sango.com','1968-08-17',1,3,'2',3,'8879875'


delete from bbsusers
where uname='刘备'

select *from bbsusers
where uname = '刘备'

update bbsusers set uname='元帅'
where uname='彭德怀'


drop table bbsusers


select uid as '学号',uname as '姓名',uemail as '邮箱',usex as '性别','天堂' as 来自哪里
from bbsusers
where uemail like '%@_.com'
order by uid desc

create table bbssex
(
xid int identity(1,1) primary key,
xsex varchar(2) not null,
)

insert bbssex(xsex)
select '男' union
select '女' union
select '无'

select top 4 *from bbsusers
where uregdate > 2006-09-15

update bbsusers set usex='2'
where uname='貂禅'

--常用函数
select abs(0)

select lower('ABCDEFGHIJK')

SELECT upper('abcdefg')

select getdate()

select power(4,4)

select sqrt(16)

select convert(varchar(6),6545)

select system_user

update bbsusers set upassword=replace(upassword,'1','L')

select *from bbsusers

update bbsusers set upassword='1348795'
where upassword='8888888'

select uname as '姓名','密码同用户名' as 状态
from bbsusers
where upassword=uname

create table abcd
(
atemp int,
ate varchar(32),
)

--已有表复制数据
insert abcd(ate)
select uname
from bbsusers

--查询
select uname as 姓名,count(uname) as 共有几人
from bbsusers
group by uname
order by uname desc

--复制时创新表
select uname,usex
into abcde
from bbsusers

select *from abcde

create database students on(name='stu_data',filename='d:\project\stu.mdf',size=1,filegrowth=15%) log on(name='stu_log',filename='d:\project\stu_log.ldf',size=1,filegrowth=10%)

create table sinfo
(
sname varchar(8) primary key,
sid varchar(6) not null unique check(sid like 'S253[0-9][0-9]'),
ssex varchar(2) not null default('男'),
sage int not null check(sage between 15 and 50),
sseat int identity(1,1) check(sseat <=30),
sadd varchar(40) default('地址不详'),
)
create table smark
(
sno varchar(11) primary key check(sno like 'E200507[0-9][0-9][0-9][0-9]'),
ssid varchar(6) not null,
foreign key(ssid) references sinfo(sid),
swex int not null check(swex between 0 and 100) default(0),
slex int not null check(slex between 0 and 100) default(0),
)

insert sinfo(sname,sid,ssex,sage,sadd)
select '穆','s25301','男',20,'西藏' union
select '撒加','s25302','男',28,'希腊' union
select '艾奥里亚','s25303','男',20,'希腊' union
select '沙加','s25304','男',20,'印度' union
select '水野亚美','s25305','女',15,'日本' union
select '春丽','s25306','女',18,'中国'


insert smark(sno,ssid,swex,slex)
select 'E2005070001','s25301',95,97 union
select 'E2005070002','s25302',93,96 union
select 'E2005070003','s25303',97,95 union
select 'E2005070004','s25306',90,92


--1.查询两表的数据--

select sname as 姓名,sid as 学号,ssex as 性别,sage as 年龄,sseat as 座号,sadd as 地址 from sinfo
select sno as 考号,ssid as 学号,swex as 笔试成绩,slex as 机试成绩 from smark

--2.查询男学员名单--

select * from sinfo where ssex='男'

--3.查询笔试成绩优秀的学员情况（成绩在75～100之间)

select * from smark where swex between 75 and 100

--4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩--

select sinfo.sname,smark.swex,smark.slex
from smark inner join sinfo
on smark.ssid = sinfo.sid

--5.统计笔试考试平均分和机试考试平均分--

select avg(swex) as 笔试平均分,avg(slex) as 机度平均分 from smark

--6.统计参加本次考试的学员人数

select count(sno) as 参考人数 from smark

--7.查询没有通过考试的人数（笔试或机试小于60分）--

select * from smark where swex < 60 or slex < 60

--8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分--

select ssid as 学号,swex as 笔试,slex as 机试, (swex+slex)/2 as 平均成绩 from smark

--9.排名次（按平均分从高到低排序），显示学号、平均分--

select * from smark
order by (swex+slex)/2 desc

--10.排名次(按平均分从高到低排序),显示姓名,笔试成绩,机试成绩,平均分-

select sinfo.sname as 姓名,smark.swex as 笔试成绩,smark.slex as 机试成绩,(smark.swex+smark.slex)/2 as 平均成绩
from smark inner join sinfo
on smark.ssid = sinfo.sid
order by (smark.swex+smark.slex)/2 desc

--11.根据平均分,显示前两名信息,包括姓名、笔试成绩、机试成绩、平均分-

select top 2 sinfo.sname as 姓名,smark.swex as 笔试成绩,smark.slex as 机试成绩,(smark.swex+smark.slex)/2 as 平均成绩
from smark inner join sinfo
on smark.ssid = sinfo.sid
order by (smark.swex+smark.slex)/2 desc

--都提5分--
--update stuMarks set writtenExam=writtenExam+5
--100分封顶（加分后超过100分的，按100分计算）--

update smark set swex=100 where swex >= 95
update smark set slex=100 where slex >= 95
update smark set swex=swex+5 where swex <= 95
update smark set slex=slex+5 where slex <= 95