SQLserv单表查询

作者: gw

------------------------数据查询----------------------------

--select [all|distinct]<目标列的表达式>[,<目标列的表达式>]...
--from <表名或视图名>[,<表名或视图名>]...
--where <表达式>
--[group by <列名1>[having<条件表达式>]]
--[group by <列名2>[asc|desc]]

distinct  不显示重复的结果
asc      升序
desc    降序

--查询 所有学生的学号和姓名
--select sno,sname from student

--select sno,sname,sdept from student

--select * from student

--下面的 as brithday 是为计算后的列起一个名字
--select sname,2014-sage as brithday from student

--select sname ,‘year of birth:‘,2014-sage,upper(sdept) from student

--select sname ,‘year of birth:‘ as year_of_birth,2014-sage as year,upper(sdept) from student

--取消重复的行 distinct
--select sno from sc
--select distinct sno from sc

--select sname ,sdept from student where sdept=‘ma‘

--select sname ,sage from student where sage>21

--select sno from sc where grade<90

--select distinct sno from sc where grade<90

----查询条件
----比 较:=,>,<,!=,!
----确定范围:between and ,not between and
----确定集合:in , not in
----字符匹配:like, not like
----空 值: is null,is not null
----多重条件:and,or,not

-- between and 闭区间 包括头尾
--select sname ,sdept,sage from student where sage between 20 and 22

--select sname ,sdept,sage from student where sage not between 20 and 22

--in
--select sname ,sdept from student where sdept in(‘cs‘,‘ma‘)

--select sname ,sdept from student where sage in(20,21,22)

--select sname,ssex from student where sdept not in(‘cs‘,‘ma‘,‘is‘)

--like
--注意这里用%表示任意多字符而不是* 用_表示一个任意字符
--(!!如果是汉字的话 在ASCII中 __代表一个汉字, 在GBK中只需要一个_)
--select * from student where sname like ‘高%‘

--select * from student where sno like ‘2014%‘

--select * from student where sname like ‘王_‘

--字符串中最好不要有%或_ 自找苦吃

--涉及到空值得查询
--select sno,cno from sc where grade is null

--select sno,cno from sc where grade is not null

--多条件查询

--select sname,sdept,sage from student where sdept=‘ma‘ and sage>21

--select * from student where sdept=‘ma‘ and sage>21 and (ssex=‘男‘ or sdept=‘is‘)

--注意在多条件查询的过程中 执行顺序是 -> 从左到右的
--select * from student where sdept=‘ma‘ and sage>21 and ssex=‘男‘ or sdept=‘is‘

--order by 对查询结果进行排序升序asc 降序 desc
--select sno,grade from sc where cno=‘2‘ order by grade desc

--select sno,grade from sc where cno=‘2‘ order by grade

--select * from student order by sdept,sage desc

--函数
--select count(*)as sum from student

--select count(distinct sno) from sc
--select count( sno) from sc

--select avg(grade)as average_grade from sc

--select avg(grade)as average_grade from sc where cno=‘1‘

--select avg(grade) from sc where grade is null

--select max(grade) from sc

--select sum(grade) from sc

--group by
--把课程号相同的分成一组 然后统计每一组的人数
--select cno ,count(sno) from sc group by cno
--注意区别
--select count(distinct sno) from sc

--!!select cno, count(sno) from sc group by grade

--标注!! 的地方 或者有错或者是值得注意的地方

--分析下面的三句话是等价的
--select cno,count(cno) from sc group by cno
--select cno ,count(sno) from sc group by cno
--select cno ,count(*) from sc group by cno

--不明白 请看下面两句
--select cno ,sum(grade) from sc group by cno

--select cno ,avg(grade) from sc group by cno

--select cno ,sum(grade)as sum,avg(grade)as avg,count(distinct sno)as num from sc group by cno

--select sno,count(cno)as select_num from sc group by sno

--select sno from sc group by sno having count(*)>=3

--查询平均成绩大于80分的同学的学号和平均成绩
--select sno,avg(grade)as average_grade from sc group by sno having avg(grade)>=80

--上面是对单表的查询
--下面是对多表的查询

--查询每个学生及其选修课的情况

--select student.* ,sc.* from student,sc

-- = 等值连接
--select student.* ,sc.* from student ,sc where student.sno=sc.sno

-- 上面的 查询结果中 有两个sno出现 去掉的办法
--select student.* ,cno,grade from student ,sc where student.sno=sc.sno

-- 自身连接 例如查询现行课的先行课
select c1.cname ,c1.cno,c2.cpno from course c1,course c2 where c1.cpno=c2.cno