SQL查询——示列代码（一）

use StuDb
go

select stuNo,stuName,stuSex from stuInfo where stuSex='女'


-- 显示性别为男的学号，姓名，笔试成绩，机试成绩
select stuInfo.stuNo,stuName,stuSex,writtenExam,labExam from
stuInfo,stuMarks
where stuInfo.stuNo=stuMarks.stuNo
and stuSex='男'

-- 查询学生的年龄信息
select distinct(stuAge) from stuInfo

-- 返回前3行
select top 3 * from stuInfo

-- 排序
返回年龄最小的3个学生
select top 3 * from stuInfo order by stuAge asc

-- 查询姓张，名字3个字，以三结尾
select stuName from stuInfo where stuName like '张_三'

-- 查询名字中含有张
select stuName from stuInfo where stuName like '%张%'


-- 查询18-20岁的
select * from stuInfo where stuAge between 18 and 20

-- 查询18,20岁的
select * from stuInfo where stuAge in(18,20)

-- 查询机试成绩为NULL的
select * from stuMarks where labExam is NULL

--
select * from sys.objects where name='stuInfo'

if(exists(select * from sys.objects where name='stuInfo'))
	print '表stuInfo已经存在'
else
	print '创建stuInfo表'
	
	
-- 查询参加考试的学生信息
select * from stuInfo
where stuNo in(select stuNo from stuMarks)

select * from stuInfo
where exists(select stuNo from stuMarks where stuNo=stuInfo.stuNo)

-- 查询比张三小的
select * from stuInfo
where stuAge < (select stuAge from stuInfo where stuName='张三')

select * from stuMarks

-- 统计学生数量
select COUNT(*) from stuInfo

-- 统计笔试总分
select SUM(writtenExam) from stuMarks


-- 统计平均分
select avg(writtenExam) from stuMarks

-- 获取最高分
select max(writtenExam) from stuMarks

-- 获取最低分
select min(writtenExam) from stuMarks

select * from bank

-- 统计总收入
select SUM(income) from bank

-- 分组统计总收入
select role,SUM(income) from bank group by role

-- 查询收入大于1000的组
select role,SUM(income) from bank group by role
having SUM(inCome)>1000


select * from stuInfo
-- 查询男生，女生各自的人数

select stuSex,COUNT(*) from stuInfo group by stuSex


-- 查询人数最多的性别

select top 1 stuSex,COUNT(*) from stuInfo group by stuSex
order by COUNT(*) desc

-- 查询每个年龄的人数
select stuAge,COUNT(*) from stuInfo group by stuAge

-- 查询某年龄段人数大于1人的组
select stuAge,COUNT(*) from stuInfo group by stuAge
having COUNT(*)>1

-- 查询年龄段人数最少的组
select top 1 stuAge,COUNT(*) from stuInfo group by stuAge
order by COUNT(*) asc

-- 查询学生学号，笔试成绩、机试成绩，总分，平均分

select stuNo,writtenExam,labExam,(writtenExam+labExam) as 'totalScore',(writtenExam+labExam)/2 as 'avgScore'  from stuMarks

select * from stuInfo 
where stuName like '张三' escape '\'

-- 查询缺考的学生

select * from stuInfo
select * from stuMarks

select * from stuInfo
where stuNo not in
(select stuNo from stuMarks)

-- 查询比张小三大的
select * from stuInfo
where stuAge >
(select stuAge from stuInfo where stuName='张小三')

-- 并
select 1,2
union all
select 1,2

-- 交
select 1,2
intersect
select 1,3

-- 差
select 1,2
union
select 1,3
except
select 1,3