数据库02 查询

最新推荐文章于 2024-08-16 21:23:11 发布

原创最新推荐文章于 2024-08-16 21:23:11 发布 · 385 阅读

0 ·

CC 4.0 BY-SA版权

SQL 同时被 2 个专栏收录

6 篇文章

订阅专栏

orcale

5 篇文章

订阅专栏


查询语句基本格式 
select <目标序列名称>
	from <数据源>
	[where <检索条件>]
	[group by <分组依据>]
	[having <组提取条件>]
	[order by <排序依据列>]

#更改日期格式
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';

# where and ;  to_date
1.查询1985年前出生的女生信息
select * from TB_Student 
	where sex='F' and
	birthday<to_date('1985-01-01','rrrr-mm-dd');
注:to_date('1985','rrrr')   表示1986前(包括了1985)


2.成绩表中总评成绩高于80的课程超过两门的学生学号，班级号，课程数，按班级号，学号升序。
select stuid,classid,count(*)
	from TB_grade 
	where TotalScore>80 
	group by Stuid,classid 
	having count(*)>2 
	order by classid,stuid;

3普通连接
	计算机系所有女生学号，姓名，性别，生日，按生日降序
	select stuid,stuname,sex,birthday 
		from TB_student join TB_dept on  
		TB_student.deptid=TB_dept.deptid  
		where deptname='计算机系' and sex='F' 
		order by birthday desc ;
3笛卡儿积
	计算机系所有女生学号，姓名，性别，生日，按生日降序
	select stuid,stuname,sex,birthday 
		from TB_student , TB_dept 
		where TB_student.deptid=TB_dept.deptid 
		and sex='F' and deptname='计算机系' 
		order by birthday desc;

4.自然连接
	计算机系所有女生学号，姓名，性别，生日，按生日降序
	select stuid,stuname,sex,birthday 
		from TB_student natural join TB_dept 
		where deptname='计算机系' and sex='F' 
		order by birthday desc ;
		
	注:两表之间的公共属性为连接条件的，选择自然连接；有公共属性但不全是连接条件的，选择普通连接；其他选择普通连接或笛卡儿积.



例:
	1.以系为组统计人数
	SQL> select TB_dept.deptname ,count(*) as 学生人数
  	    from TB_student join TB_dept on TB_student.deptid=TB_dept.deptid
    	group by deptname ;

DEPTNAME               学生人数
-------------------- ----------
计算机系                     30
机电工程系                   10
电子工程系                   10
	
	2.统计学生的选课门数 
SQL> select stuid,count(*) as 选课门数,AVG(totalscore)
    from TB_grade
    group by stuid;   # select后的字段要么属于group by 要么被聚合函数包围

STUID      选课门数 AVG(TOTALSCORE)
-------- ---------- ---------------
04080104          3          69.354
04080202          2           73.25
04080206          1            77.7
04080101          4            74.3
04080109          3      80.1333333
04080210          1            51.6
04080110          3      78.0666667
04080207          1            82.3
04080107          3      62.9666667
04020104          2            81.3
04080102          3              76

STUID      选课门数 AVG(TOTALSCORE)
-------- ---------- ---------------
04080106          4          80.925
04080108          3      89.1666667
04080205          2           69.05
04020101          1            87.6
04080208          1            83.2
04080201          3              85
04080209          1            75.8
04080103          4           71.15
04080105          3      66.9666667
04080203          2            70.9
04080204          1            65.1

已选择22行。

3 查询成绩表中所有平均成绩大于60的课程号和平均成绩。
SQL> select courseid , AVG(totalscore) from TB_grade
    group by courseid
    having  AVG(totalscore)>=60;
	注：此处必须用group by聚合,不然平均成绩是以什么为单位呢

4.列出每门课程的平均，最高，最低分数
SQL> select coursename ,AVG(totalscore),MAX(totalScore),MIN(totalscore)
    from TB_grade join TB_course on TB_grade.courseid=TB_course.courseid
    group by coursename ;

5.查询有二门以上（含二门）低于70分的课程的学生姓名及其平均成绩。
SQL> select stuname , AVG(totalscore)
    from TB_student join TB_grade on TB_student.stuid=TB_grade.stuid
    where stuname in ( select stuname
    from TB_student join TB_grade on TB_student.stuid=TB_grade.stuid
    where totalscore<=70
    group by stuname
    having count(*)>=2)
    group by stuname;







6.根据课程班和任课教师对学生平均分进行分类汇总，并显示平均成绩大于75分的信息，要求显示标题为：课程班号、教师姓名、平均分。
SQL> select TB_courseclass.courseclassid,teachername,AVG(totalscore)
        from TB_grade left outer join TB_courseclass on TB_grade.courseclassid=TB_courseclass.courseclassid left outer join TB_teacher on
    TB_courseclass.teacherid=TB_teacher.teacherid
    group by TB_courseclass.courseclassid, teachername, TB_grade.stuid
    having AVG(totalscore)>=75 ;

在这里插入图片描述

SQL Server课程

select语句结构

在这里插入图片描述

别名

select sname 姓名 , sno 编号 from student
select sname as 姓名  from student
select sname=姓名 from student

条件查询

在这里插入图片描述
注释 where name like ‘%数据库%’ 中的%匹配任意长度的字符串。

聚合函数

count()
Sum()
Avg()
Max()
Min()

#总的学生数目
select count(*) 总人数 from TB_Student
#课程分数总和，平均成绩，最高，最低成绩
select sum(endscore),ave(endscore),max(endscore),min(endscore) from TB_score where cno='class01'

分组统计

格式 Select <字段列表> From<表名>
[Where <查询条件> ]
Group by <分类字段> [having <检索条件>]

Group by子句将查询的过节按照若干列的值分组，值相等的为一组。若未对查询结果分组，聚集函数将作用于整个查询结果；分组后聚集函数将作用于每一个组。
Having 子句用于对分组结果进行筛选，通常在Group by子句中使用。

#score表中，筛选出平均分在80以上的学生的学号和均分
select sno,avg(endorse) from score group by son having avg(endorse)>=80 

#查询没门课程平均成绩 降序输出
select cno,ave(endorese) from score group by cno order by avg(endorse) desc

多表查询内连接

指将表中的一些记录按照一定条件与其他表的一些记录连接起来，连接条件通常是表间列的相等关系

格式1 select <列表字段> from <表1>,<表2>
where <表1.列名=表2.列名> [and条件表达式]
格式2 select<列表字段> from <表1> Inner join <表2>
on <表1.列名=表2.列名> [where 条件表达式]
注 Inner join 内连接 on


select student.son,sname,cno from student,score
where student.sno=score.son

select sno,sname,cno from student Inner join score 
on student.sno=score.son where sex='男'

多表查询外连接

外连接通常用于相连接的表中有一个表需要 显示所有数据行 的情况
外连接的结果集中不但包含满足连接条件的记录，还包含相应表中的所有记录。即输出某些不满足连接条件的记录
外连接分为左右全三类
左右外连接
格式 select <列表字段> from <表1> [left,right] [outer] join <表2>
on <表1.列名=表2.列名>
该结果集合中包含所有的左或右表的所有记录，若左表记录在右表没有匹配行，则右表显示为NULL.

全外连接
格式 select <列表字段> from <表1> full join <表2>
on <表1.列名=表2.列名>
显示左右两表的全部记录

子查询，嵌套查询

即在一个查询中包含另一个查询,子查询块必须是单表查询，通常有带in，any或all，exists的谓词查询
格式select <列表字段> from<单个目标表或 where <字段>
in (select <列表字段> from <> where <>)>

in，给定值是否在子查询的结果集合中

#查询选修了三门及以上课程的学生
select sname from studnt 
where sno in 
(
select sno from score 
group by sno having count(*)>=3
)