经典必收藏的sql应用案例

--查询出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
use master
go
create database _1102studb
 on
(
 name=_1102studb_data,
 filename=d:8203\_1102studb_data.mdf,
 size=5mb,
 maxsize=100mb,
 filegrowth=5mb
)
log  on
(
 name=_1102studb_log,
 filename=d:8203\_1102studb_data.ldf,
 size=4mb,
 filegrowth=5%
)
go
use _1102studb
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
insert into student values(韩愈,男,2011-1-1,20112033,计算机)
insert into student values(李小婉,女,2011-3-3,20112034,法学)
insert into student values(韩三,男,2011-5-5,20112035,生科)
insert into student values(无何,女,2011-5-3,20112036,计算机)
insert into student values(海风,男,2011-9-9,20112037,计算机)
select * from student
c#---------------------------
--Button
string s1=textBox1.text;
string s2=textBox2.text;
int i1,i2;
if(int.TryParse(s1,out i1)==false)
{
 MessageBox.Show("输入的不是数字");
}
if(int.TryParse(s2,out s2==false))
{
 MessageBox.Show("输入的不是整数");
}
int i3=i1+i2;
textBox3.Text=Convert.ToString(i3);