SQL Server 各种查询方法演示

首先创建数据库、数据表、测试数据

--创建学生信息数据库
create database students
go

use students
go

--创建学生表
create table stuinfo
(
  stuid  int,
  stuname varchar(20),
  stusex char(2)
)
go

--插入测试数据
insert into stuinfo values(101,'张三','男')
insert into stuinfo(stuid,stuname,stusex)values(102,'李四','男')
insert into stuinfo(stuid,stuname,stusex)values(103,'张三','男')
insert into stuinfo(stuid,stuname,stusex)values(104,'张三','女')
insert into stuinfo(stuid,stuname,stusex)values(105,'李四','女')
insert into stuinfo(stuid,stuname,stusex)values(106,'张三丰','女')

然后开始各种方法查询：

--去除重复记录查询姓名，性别
select distinct stuname,stusex  from stuinfo  
--查询前5条记录
select top 5 * from stuinfo
--筛选学生信息表中性别为男的学生记录
select * from stuinfo where stusex='男'
--筛选学生信息表中性别不是男的学生记录
select * from stuinfo where stusex<>'男'
--查询学生编号在101-103之间的学生信息
select * from stuinfo where  stuid between  101 and 103
--查询学号为101、103学员的信息
select * from stuinfo where stuid in(101,103) 
--查询除了学号为101、103学员的信息
select *from stuinfo where stuid not in(101,103)
--查询姓名为张三、性别为女的信息
select *from stuinfo where stuname='张三'and stusex='女'
--查询表中姓张学生的信息
select * from stuinfo where stuname like '张%'
--查询表中学号包含1，3的信息
select * from stuinfo where stuid like '%[1,3]%'
--根据学号升序排列显示信息
select * from stuinfo order by stuid
--降序排
select * from stuinfo order by stuid desc
--筛选信息为女生的信息之后再对学号，姓名排序
select * from stuinfo where stusex='女'  order by stuid,stuname
--查询前十名学生的信息
select top 10 * from stuinfo

创建成绩表，加入测试数据：

--创建成绩表
create table stumarks
(
 examno int,
 stuid int,
 subject char(20),
 score int
)
go
--插入测试数据
insert into stumarks values(1,101,'java',80)
insert into stumarks values(2,102,'java',90)
insert into stumarks values(3,103,'html',90)
select * from stumarks

下面是运算查询：

--求java成绩的总和
select SUM(score) 总成绩 from stumarks where subject='java'
--求java科目所有学生的平均成绩
select AVG(score) 平均成绩 from stumarks where subject='java'
--求java成绩的最高分
select MAX (score)as 最大值 from stumarks where subject='java'
--求java成绩的最低分
select MIN (score)as 最小值 from stumarks where subject='java'
-- 求表中记录有多少条
select COUNT (stuid)from stumarks
--分别统计男生和女生的人数  （使用group by 语句）
select stusex , COUNT(stusex) from stuinfo group by stusex
--根据科目求平均成绩
select subject ,avg(score) 平均值 from stumarks
group by subject
--根据科目求平均成绩,并筛选出大于85分的记录
select subject ,avg(score) 平均值 from stumarks
group by subject having AVG(score)>85

接着就是联接查询：

--联接查询实际上是通过各个表之间共同列的关联性来查询数据
select * from stuinfo
select * from stumarks
--内联接查询
--语法: select <列名> from  表1  inner join 表2  on 条件表达式
select * from stuinfo inner join stumarks on  stuinfo.stuid=stumarks.stuid
--内联接查询仅仅返回那些存在字段匹配的记录
--查询指定列的内联接
--可以通过  表名.列名  进行指定列
select stuinfo.stuid,stuname,stusex,subject,score 
from stuinfo inner join stumarks 
on stuinfo.stuid=stumarks.stuid

--带条件的内联接
--查询分数在85分以上的学生信息,包含科目和成绩
select stuinfo.stuid,stuname,stusex,subject,score 
from stuinfo inner join stumarks 
on stuinfo.stuid=stumarks.stuid
where score>=85
--给表起别名的联接查询
select * from stuinfo s1 inner join stumarks s2 on s1.stuid=s2.stuid
--一旦给表起别名,就必须使用它的别名
--使用where完成内联接的效果
select * from stuinfo,stumarks  where stuinfo.stuid=stumarks.stuid

--更复杂的联接查询
--语法: select <列名> from 表1 inner join 表2 on 条件表达式1
--   inner join  表3  on 条件表达式2
--   inner join 表4  on 条件表达式3
--   ......

--自连接查询
--查询班里成绩一样的学生信息
select s1.stuid,s3.stuname,s1.subject,s1.score from stumarks s1 inner join stumarks s2 
on s1.score=s2.score  and s1.stuid<>s2.stuid
inner join stuinfo s3  on s1.stuid=s3.stuid
--外联接查询
--语法: select <列名> from  表1  left/right [outer] join  表2  on  条件表达
--查询班中没有参加考试的同学姓名
--左外联接查询,左表为主表,显示左表中所有的记录,
select * from stuinfo s1 left join stumarks s2 on s1.stuid=s2.stuid  where score is null
--右外联接查询,右表为主表
select * from stuinfo s1 right join stumarks s2 on s1.stuid=s2.stuid