经典必收藏的sql应用案例

exec sp_password null,123,sa
alter login sa enable
go
drop database _110studb
go
create database _1101studb
on
(
 name=studb_data,
 filename=D:8203studb_data.mdf,
 size=5mb,
 maxsize=100mb,
 filegrowth=5mb
)
log on
(
 name=studb_log,
 filename=D:8203studb_data.ldf,
 size=3mb,
 filegrowth=10%
)
go
use _1101studb
go
create table student
(
 stuid int identity(1001,1) primary key,
 stuname nvarchar(20) not null,
 sex char(2),
 birthday datetime,
 stuno varchar(20),
 remark text
)
go
--删除
drop table student
go
select * from student
select * from student
--插入数据
insert into student(stuname,sex,birthday,stuno,remark)
values(韩语,男,2011-1-1,20110101,计算机)
insert into student(stuname,sex,birthday,stuno,remark)
values(李玉,男,2011-2-2,20111012,计算机)
insert into student values(李海,男,2011-3-2,20111033,外语)
insert into student values(占小河,女,2011-4-4,20110303,法学)
insert into student values(王思,女,2011-2-4,20110404,历史)
insert into student values(可依,女,2011-8-7,20110713,广告学)
insert into student values(李小婉,女,2011-1-12,20110833,电信)
go
create table grade
(
 gradeid int identity(101,1) primary key,
 score float,
 stuid int,
 crousename nvarchar(20)
)

--1001
insert into grade values(80.5,1001,语文)
insert into grade values(85,1001,数学)
insert into grade values(98.6,1001,英语)
--1002
insert into grade values(84,1002,语文)
insert into grade values(85.4,1002,数学)
--1003
insert into grade values(78.4,1003,语文)
insert into grade values(54,1003,数学)
insert into grade values(64,1003,英语)
--1004
insert into grade values(65,1004,语文)
insert into grade values(33,1004,数学)
insert into grade values(33,1004,英语)
-1005
insert into grade values(78,1005,语文)
insert into grade alues(24,1005,数学)
insert into grade alues(98,1005,英语)
--聚合函数
--count(),max(),sum(),avg(),min()
--查询成绩表里又少条记录
select count(*) from grade
--count(列名)要比count(*)执行效率要高
select count(0) from grade
select count(2) from grade
--查询所有成绩额度总分
select sum(score) 全部总分 from grade
select avg(score) 全部平均分 from gradee
--链接查询
--inner join
select * from student
inner join grade
--on 后面连接条件
on student.stuid=grade.stuid
--左外连接
--left join grade
select * from student
left join grade
on student.stuid=grade.stuid
--右外连
--right join
select * from student
right join grade
on student.stuid=grade.stuid
--给表起别名
select * from stuent a
right join grade b
on a.stuid=b.stuid
--查询分数最高额学生信息
select * from student where stuid
in
(
select stuid from grade where score=
(select max(score) from grade)
)

--查询出id号为1001，1003，1004，1005的学生的信息
--in
select * from student
where stuid
in(1001，1003，1004，1005)
--查询出语文成绩及格的学生信息
select * from student
where stuid in
(select stuid from grade where crousename=语文 and scose>60 )
 
--查询表中前4条记录
-top
select top 4 * from student
select top 4 stuno,stuname from student
select top(4) * from student
--查询表的第3条到第5条记录
--不再前两条，的前三条
select top 3 * from student
where stuid not in
(
select top 2 stuid from student
)
--分组
--统计每个学生的总分数
--group by
select * from student
inner join
(
select stuid,sum(score) as 总分数 from grade
group by stuid
) b
on a.stuid=b.stuid
--统计每个学生的平均分
--给性别为女的学生成绩加5分
update grade
set score=score+5
where stuid in
(select stuid from student where sex=女)
--所有女生的信息
select a.stuid,stuname,birthday,stuno,sex,remark,score from student a
inner join grade b
on a.stuid=b.stuid and sex=女
8:57 2011-7-11