数据库查询知识及汇总

--------------------数据查询-------------------
--格式
select 目标列名
from 表名
where 条件
order by 列名 ASC/DESC
-----------------------------------------------
--列的检索
--选择所有列 星号(*)
--例 查询所有学生信息
select * From student

--指定列
--例 查询所有学生的学号,姓名
select sname,sno
from student

--计算列 + - * / %
select 5+6
--例 查询所有学生的学号,姓名,入学平均分(入学成绩/6)
select sno as '学号',sname,sscore/6+5 as 'avg' from student

--用“＋”连接字符串
select '这就是别名'=sno+'的姓名是:'+sname from student

--别名
-- 列名 AS ‘别名’(AS关键字可省)
--‘别名’＝列名
--例 将以上案例中的无列名列加别名

--函数
select year(getdate())
--例 查询学生的姓名与年龄
select sname,year(getdate())-year(sbirthday) as sage
from student

--说明列/常量列
select sno,'姓名:',sname from student
-------------------------------------------------------

----------------------行的检索-----------------------
--格式
where 条件

--功能:比较运算、逻辑运算、模糊匹配、范围、列表、空值

--比较运算 = > < <> !=
--例 查询刘天新的信息
select * From student
where sname='刘天新'

--例 查询入学成绩大于450分的学生信息
select * From student
where sscore>=450

--逻辑运算 and or not
--例 查询1986-6-30之后出生的女同学
select * From student
where sbirthday>'1986-6-30' and ssex='女'

--例 查询班号为07030112,07040112,07050212班的学生信息
select * From student
where classno='07030112' or classno='07040112' or
classno='07050212'

--模糊匹配
--格式
where 列名 like '匹配字符串'

--匹配字符串 可用四种通配符
% --匹配任意多个字符(0~n个)
_ --匹配任意一个字符
[] --匹配括号内任意一个字符
[^] --不匹配括号内任意一个字符

--例 查询所有姓李的学生信息
select * From student
where sname like '李%'

--例 查询阿华的信息
select * From student
where sname like '%华%'

--例 查询姓名第二个字为华的信息
select * From student
where sname like '_华%'

--例 查询姓张,王的学生信息
select * From student
where sname like '[张王]%'

--例 查询所有不姓张,王的学生信息
--方法1 [^]
select * From student
where sname like '[^张王]%'
--方法2 not like
select * From student
where sname not like '[张王]%'
--方法3 逻辑运算符 not
select * From student
where not sname like '[张王]%'

--注:反向查询的性能很低,一般不用

--范围
--例 查询入学成绩在450~470分之间的学生信息

--思路1:列名>=最小值 and 列名<=最大值
select * From student
where sscore>=450 and sscore<=470
--思路２:列名 between 最小值 and 最大值
select * From student
wher

e sscore between 450 and 470

--列表
--例 查询班号为07030112,07040112,07050212班的学生信息
--思路1: 列名='值1' or 列名='值2' or 列名='值3' ......
select * From student
where classno='07030112' or classno='07040112' or
classno='07050212'
--思路2:列名 in('值1',值2','值3'......)
select * From student
where classno in('07030112','07040112','07050212')

--练 查询07040111,07050112,06010111这三个班中所有出生日期
--在1987-6-1到1988-4-15之间的姓张的女生
select * from student
where classno in('07040111','07050112','06010111')
and sbirthday between '1987-6-1' and '1988-4-15'
and sname like '张%'
and ssex='女'
------------------------------------------------------
--空值 is null
--例 查询所有缺考的学生的学号,课程号
select * From choice
where grade is null

--distinct
--功能:删除结果集中重复的行
--格式:select distinct 列名 from 表名
select distinct ssex from student

--例 查询学生的出生年份
select distinct year(sbirthday) from student

--top 关键字
--功能:取结果集中前N行或前百分之N行的数据
格式:select top n [percent] 列名 from 表名

--例 查询前10名女学生信息
select top 10 * from student
where ssex='女'

--order by 排序
--格式:order by 列名 ASC/DESC,......
--注:ASC 升序(默认,可不写) DESC 降序(必写)

--例 查询学生信息按入学成绩由大到小排序
select * from student
order by sscore desc

--例 查询学生信息按年龄由大到小排序
select * from student
order by sbirthday

--例 查询学生信息按班级与性别排序
select * from student
order by classno desc,ssex asc

--取最高/最低的多少条信息时 top+order by
--例 查询入学成绩最高的3名学生
select top 3 * from student
order by sscore desc




--------------------------------------------------------
--------------------多表连接----------------------------
--格式
select 目标列名
from 表1
[连接类型] 表2 on 表1.列=表2.列
[连接类型] 表3 on 表3.列=表12.列
......
--连接类型
-- 内连接: [inner] join 左连接 left join
-- 右连接: right join 全连接 full join
-------------------------------------------------------
--例 用不同的连接类型连接班级与学生表,观察输出结果
select *
from student
right join class on student.classno=class.classno
--输入一个班级信息,再观察以上连接
insert into class values('c102','10软件11班','0502')

例 查询'计算机工程系'的专业名称
select * From department
select * from professional
--连接
select pname
from department d --注:d p 为表的别名
join professional p on d.deptno=p.deptno
where deptname='计算机工程系'

练 查询'计算机应用技术'专业的所有班级名
select classname
from class c --注:c p 为表的别名

join professional p on c.pno=p.pno
where pname='计算机应用技术'

例 查询'计算机工程系'的所有班级名
select classname
from department d
join professional p on d.deptno=p.deptno
join class c on c.pno=p.pno
where deptname='计算机工程系'

例 查询孙晓所学的所有课程名称与成绩
select cname,grade
from student
join choice on student.sno=choice.sno
join course on o=o
where sname='孙晓'

例 查询学习了美术基础这门课的学生姓名,班级名
select sname,classname
from student
join choice on student.sno=choice.sno
join course on o=o
join class on class.classno=student.classno
where cname='艺术设计基础'


------------------------外连接---------------------
--查询还没有选课的学生的学号,姓名
select s.sno,sname
from student s
left join choice ch on s.sno=ch.sno
where cno is null

--查询没上课的教师信息
select tname
from teacher
left join teaching on teacher.tno=teaching.tno
where cno is null

-----------------------------------------------------

---------------------子查询--------------------------
--在一个查询语句中套入另一个查询
--分类:无关子查询（掌握）、相关子查询（了解）
--位置：where条件中、数据来源处

例 查询与张三同班的学生信息
select classno from student where sname='张三'
select * from student where classno='06010111'
--子查询
select *
from student
where classno in(select classno
from student
where sname='张三')

--把子查询用于数据源
select *
from (select sno,sname from student) as stu1

--例 查询“计应0611"班的所有学生的学号，姓名
--多表
select sno,sname
from student s
join class c on s.classno=c.classno
where classname='计应0611'
--子查询
select sno,sname
from student
where classno =(select classno from class
where classname='计应0611')


--例 查询学习了JAVA程序设计的学生的学号，姓名
select sno,sname
from student
where sno =any(select sno from choice
where cno=(select cno from course
where cname='JAVA程序设计'))

--例 查询还没有选课的学生的学号,姓名
select sno,sname
from student
where sno <>all(select sno from choice)

--例 查询'范君召'所上的课程名称
--多表
select cname
from teacher t
join teaching te on t.tno=te.tno
join course c on o=o
where tname='范君召'
--子查询
select cname
from course
where cno in(select cno from teaching
where tno in(select tno from teacher
where tname='范君召'))

-----------------------数据汇总---------------------
--聚合函数
AVG(列名) --平均值
MAX(列名) --最大值
MIN(列名) --最

小值
SUM(列名) --求和
COUNT(列名)/(*) --计数
--注:使用聚合函数时，不考虑NULL值,*号除外

--例 理解NULL在统计函数中的计算方式(设有一名学生的性别为NULL)
select count(ssex), count(sname),count(*)
from student

--例 查询学生总人数,入学成绩的最高分,最低分,平均分,总分
select count(*) as '总人数',
max(sscore) as 'maxscore',
min(sscore) as 'minscore',
avg(sscore) as 'avgscore',
sum(sscore) as 'sumscore'
from student

注意:在使用统计函数后,不能同时输出表中的原始数据
统计函数是查询的结果集做统计

--例 求男生的人数
select count(*)
from student
where ssex='男'
--从结果可看出:选筛选,后统计

--例 求张三的平均分
select avg(grade)
from student s
join choice c on s.sno=c.sno
where sname='张三'

----------------------分组汇总--------------------------
--格式
group by 分组列1,分组列2,.......
having 条件

--having 与where 的区别
--having 是对统计结果做筛选
--where 是原始数据做筛选

--求男女生各自的人数
select ssex,count(*)
from student
group by ssex

--注意：
--使用GROUP BY子句为每一个组产生一个汇总结果，每个组只返回一行，不返回详细信息。
--SELECT子句中指定的列必须是GROUP BY子句中指定的列，或者是和聚合函数一起使用。
--如果包含WHERE子句，则只对满足WHERE条件的行进行分组汇总。
--如果GROUP BY子句使用关键字ALL，则WHERE子句将不起作用。
--HAVING子句可进一步排除不满足条件的组

--查询每个学生的选课门数,平均成绩
select s.sno,sname,count(cno) 'num',avg(grade) 'avg'
from choice c
right join student s on s.sno=c.sno
group by s.sno,sname

--统计每门课程的选课人数,平均成绩
select o,cname,count(o) 'num',avg(grade) 'avg'
from choice c
right join course s on o=o
group by o,cname

-------------------------having-------------------
--统计人数在5个人以上的最高分在90分以上课程的选课人数,平均成绩
select o,cname,count(o) 'num',avg(grade) 'avg'
from choice c
right join course s on o=o
group by o,cname
having count(o)>=5 and max(grade)>80