侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

select

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 127 阅读 / 2,263 字
温馨提示:
本文最后更新于 2022-04-28,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
/ DATA / select
问题
1.
or 和 is null 在大表里都非常慢.

sp_help 表名 可以快速查看表结构 ,表上绑定的所有规则
IFNULL(jp.themeId,0)
... Where column is null
查询出的表可以放在from当子表用
Like %:任意个任意字符;_ : 一个任意字符. 
注意distinct的位置 : Select distinct name from stu;
Between ... and ...
In(集合); not in (集合);
Limit x,y; x为从第几个开始(0开始数),y为输出几位.(mysql)
Top x; (oraclemysql)
from 1 join from 2 using(字段名)
any 和 all
exist
前面不用写count(*)后面也能用
select Sno from score group by sno having count(*) >= 2

c.SafetyStock is not null
Sql中的并(UNION)、交(INTERSECT)、差(minus)、除去(EXCEPT)详解

注意join是,如果左或右数据不全会不显示这条数据.
null * number = null

函数 sum(),avg()如果没有值的话返回null
ifnull(value,0) isnull() 如果value==null,则返回0
Oracle 没有 ISNULL() 函数。不过,我们可以使用 NVL() 函数达到相同效果

查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) (语句内 if 的使用) 
课程名称  平均分  及格率
企业管理  70  50%;
SELECT
 cno,
 avg( score ) “平均分”,
 concat(( sum( CASE WHEN score >= 60 THEN 1 END ) / count( * ) ) * 100,”%”) AS “及格率” 
FROM
 score 
GROUP BY
 cno;
统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] (列变行)
SELECT
 cno,
 count( CASE WHEN degree > 85 AND degree <= 100 THEN 1 END ) AS "[100-85]",
 count( CASE WHEN degree > 70 AND degree <= 85 THEN 1 END ) AS "[85-70]",
 count( CASE WHEN degree > 60 AND degree <= 70 THEN 1 END ) AS "[70-60]",
 count( CASE WHEN degree <= 60 THEN 1 END ) AS "[60-0]" 
FROM
 score 
GROUP BY
 cno;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
比自己分数高的人不超过两人. 子查询   ,要进行多次读库
SELECT
 a.cno,
 a.degree 
FROM
 score a 
WHERE
 ( SELECT COUNT( cno ) FROM score WHERE cno = a.cno AND a.degree < degree ) <= 2 
ORDER BY
 cno ASC,
 degree DESC

比自己分数高的不超过两人. 自联结
SELECT
a.*
FROM
score a,
score b
WHERE
a.cno = b.cno
AND a.degree <= b.degree
GROUP BY
a.id,
a.cno
HAVING
count( * ) <= 3
ORDER BY
a.cno,
a.degree DESC;



7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
思路:先过滤到只剩下叶平老师的课,再数数有几节 
SELECT
	 sNo,
	 Sname 
FROM Student 
WHERE
	 sNo IN (
			SELECT
				 sNo 
			FROM SC,Course,Teacher 
			WHERE
				 SC.cNo = Course.cNo 
				 AND Teacher.tNo = Course.tNo 
				 AND Teacher.Tname = '叶平' 
			GROUP BY sNo 
			HAVING
		 count( SC.cNo ) = ( SELECT count( cNo ) FROM Course, Teacher WHERE Teacher.tNo = Course.tNo AND Tname = '叶平' ) 
	 );
select sid
-- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)
from (
	-- 学过该老师课程的同学学号(包含只学一门)
	select sid,count(cid) num
	from sc
	where cid in (
		-- 该老师教了哪些课程
		select cid
		from course
		where tid in (
			select tid
			from teacher
			where tname = '叶平'
		)
	)group by sid
) t
where t.num = (
	-- 统计该老师总课程数
	select count(cid)
	from course
	where tid = (
		select tid
		from teacher
		where tname = '叶平'
	)
)
21、查询不同老师所教不同课程平均分从高到低显示 (using 的使用)
SELECT
 tname,
 cname,
 avg( score ) AS avg 
FROM
 teacher
 JOIN course USING ( tno )
 JOIN score USING ( cno ) 
GROUP BY
 tname,
 cname
0

评论区