Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit ed1abc1

Browse filesBrowse files
bjmashibingbjmashibing
committed
upload
1 parent 0a3f594 commit ed1abc1
Copy full SHA for ed1abc1

File tree

Expand file treeCollapse file tree

3 files changed

+315
-0
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

3 files changed

+315
-0
lines changed
Open diff view settings
Collapse file

‎database/code/03关联查询.sql‎

Copy file name to clipboardExpand all lines: database/code/03关联查询.sql
+131Lines changed: 131 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,3 +31,134 @@ select * from emp e,dept d;
3131
--在92语法中，多张表的连接条件会方法where子句中，同时where需要对表进行条件过滤
3232
--因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法
3333

34+
35+
--99语法
36+
/*
37+
CROSS　JOIN
38+
NATURAL JOIN
39+
USING子句
40+
ON子句
41+
LEFT OUTER JOIN
42+
RIGHT OUTER JOIN
43+
FULL OUTER JOIN
44+
Inner join
45+
46+
*/
47+
--cross join 等同于92语法中的笛卡儿积
48+
select * from emp cross join dept;
49+
--natural join 相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接
50+
--当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系
51+
select * from emp e natural join dept d ;
52+
select * from emp e natural join salgrade sg;
53+
--on子句，可以添加任意的连接条件，
54+
--添加连接条件 相当于92语法中的等值连接
55+
select * from emp e join dept d on e.deptno = d.deptno;
56+
--相当于92语法中的非等值连接，
57+
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
58+
--left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可
59+
select * from emp e left outer join dept d on e.deptno = d.deptno;
60+
select * from emp e,dept d where e.deptno = d.deptno(+);
61+
--right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可
62+
select * from emp e right outer join dept d on e.deptno = d.deptno;
63+
select * from emp e,dept d where e.deptno(+) = d.deptno;
64+
--full outer join ,相当于左外连接和右外连接的合集
65+
select * from emp e full outer join dept d on e.deptno = d.deptno;
66+
--inner outer join，两张表的连接查询，只会查询出有匹配记录的数据
67+
select * from emp e inner join dept d on e.deptno = d.deptno;
68+
select * from emp e join dept d on e.deptno = d.deptno;
69+
--using,除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表
70+
select deptno from emp e join dept d using(deptno);
71+
select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;
72+
--总结:两种语法的SQL语句没有任何限制，再公司中可以随意使用，但是建议使用99语法，不要使用92语法，SQL显得清楚明了
73+
74+
--检索雇员名字、所在单位、薪水等级
75+
select e.ename, d.loc, sg.grade
76+
from emp e
77+
join dept d
78+
on e.deptno = d.deptno
79+
join salgrade sg
80+
on e.sal between sg.losal and sg.hisal;
81+
82+
83+
/*
84+
子查询：
85+
嵌套再其他sql语句中的完整sql语句，可以称之为子查询
86+
分类：
87+
单行子查询
88+
多行子查询
89+
90+
*/
91+
--有哪些人的薪水是在整个雇员的平均薪水之上的
92+
--1、先求平均薪水
93+
select avg(e.sal) from emp e;
94+
--2、把所有人的薪水与平均薪水比较
95+
select * from emp e where e.sal > (select avg(e.sal) from emp e);
96+
--我们要查在雇员中有哪些人是经理人
97+
--1、查询所有的经理人编号
98+
select distinct e.mgr from emp e;
99+
--2、再雇员表中过滤这些编号即可
100+
select * from emp e where e.empno in (select distinct e.mgr from emp e);
101+
--每个部门平均薪水的等级
102+
--1、先求出部门的平均薪水
103+
select e.deptno,avg(e.sal) from emp e group by e.deptno;
104+
--2、跟薪水登记表做关联，求出平均薪水的等级
105+
select t.deptno, sg.grade
106+
from salgrade sg
107+
join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
108+
on t.vsal between sg.losal and sg.hisal;
109+
110+
111+
--1、求平均薪水最高的部门的部门编号
112+
--求部门的平均薪水
113+
select e.deptno,avg(e.sal) from emp e group by e.deptno;
114+
--求平均薪水最高的部门
115+
select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t
116+
--求部门编号
117+
select t.deptno
118+
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
119+
where t.vsal =
120+
(select max(t.vsal)
121+
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
122+
--2、求部门平均薪水的等级
123+
--3、求部门平均的薪水等级
124+
--求部门每个人的薪水等级
125+
select e.deptno, sg.grade
126+
from emp e
127+
join salgrade sg
128+
on e.sal between sg.losal and sg.hisal;
129+
--按照部门求平均等级
130+
select t.deptno, avg(t.grade)
131+
from (select e.deptno, sg.grade
132+
from emp e
133+
join salgrade sg
134+
on e.sal between sg.losal and sg.hisal) t
135+
group by t.deptno;
136+
--限制输出，limit，mysql中用来做限制输出的，但是oracle中不是
137+
--再oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，
138+
--但是rownum不能直接使用，需要嵌套使用
139+
--4、求薪水最高的前5名雇员
140+
select *
141+
from (select * from emp e order by e.sal desc) t1
142+
where rownum <= 5
143+
144+
select * from emp e where rownum <=5 order by e.sal desc
145+
--5、求薪水最高的第6到10名雇员
146+
select t1.*,rownum
147+
from (select * from emp e order by e.sal desc) t1
148+
where rownum <= 10
149+
--使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出
150+
select *
151+
from (select t1.*, rownum rn
152+
from (select * from emp e order by e.sal desc) t1
153+
where rownum <= 10) t
154+
where t.rn > 5
155+
and t.rn <= 10;
156+
157+
158+
select *
159+
from (select t1.*, rownum rn
160+
from (select * from emp e order by e.sal desc) t1) t
161+
where t.rn > 5
162+
and t.rn <= 10;
163+
164+
Collapse file

‎database/code/04行专列.sql‎

Copy file name to clipboard
+92Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
create table tmp(rq varchar2(10),shengfu varchar2(5));
2+
3+
insert into tmp values('2005-05-09','胜');
4+
insert into tmp values('2005-05-09','胜');
5+
insert into tmp values('2005-05-09','负');
6+
insert into tmp values('2005-05-09','负');
7+
insert into tmp values('2005-05-10','胜');
8+
insert into tmp values('2005-05-10','负');
9+
insert into tmp values('2005-05-10','负');
10+
11+
/*
12+
胜 负
13+
2005-05-09 2 2
14+
2005-05-10 1 2
15+
16+
*/
17+
18+
select rq,decode(shengfu,'胜',1),decode(shengfu,'负',2) from tmp;
19+
20+
select rq,
21+
count(decode(shengfu, '胜', 1)) 胜,
22+
count(decode(shengfu, '负', 2)) 负
23+
from tmp
24+
group by rq;
25+
26+
27+
create table STUDENT_SCORE
28+
(
29+
name VARCHAR2(20),
30+
subject VARCHAR2(20),
31+
score NUMBER(4,1)
32+
);
33+
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
34+
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
35+
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
36+
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
37+
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
38+
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
39+
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
40+
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
41+
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);
42+
43+
44+
/*
45+
姓名 语文 数学 英语
46+
王五 89 56 89
47+
*/
48+
--至少使用4中方式下写出
49+
--decode
50+
select ss.name,
51+
max(decode(ss.subject, '语文', ss.score)) 语文,
52+
max(decode(ss.subject, '数学', ss.score)) 数学,
53+
max(decode(ss.subject, '英语', ss.score)) 英语
54+
from student_score ss group by ss.name
55+
--case when
56+
select ss.name,
57+
max(case ss.subject
58+
when '语文' then
59+
ss.score
60+
end) 语文,
61+
max(case ss.subject
62+
when '数学' then
63+
ss.score
64+
end) 数学,
65+
max(case ss.subject
66+
when '英语' then
67+
ss.score
68+
end) 英语
69+
from student_score ss
70+
group by ss.name;
71+
--join
72+
select ss.name,ss.score from student_score ss where ss.subject='语文';
73+
select ss.name,ss.score from student_score ss where ss.subject='数学';
74+
select ss.name,ss.score from student_score ss where ss.subject='英语';
75+
76+
select ss01.name, ss01.score 语文, ss02.score 数学, ss03.score 英语
77+
from (select ss.name, ss.score
78+
from student_score ss
79+
where ss.subject = '语文') ss01
80+
join (select ss.name, ss.score
81+
from student_score ss
82+
where ss.subject = '数学') ss02
83+
on ss01.name = ss02.name
84+
join (select ss.name, ss.score
85+
from student_score ss
86+
where ss.subject = '英语') ss03
87+
on ss01.name = ss03.name;
88+
89+
--union all
90+
select ss01.name,ss01.score 语文,0 数学,0 英语 from student_score ss01 where ss01.subject='语文' union all
91+
select ss02.name,0 语文,ss02.score 数学,0 英语 from student_score ss02 where ss02.subject='数学' union all
92+
select ss03.name,0 语文,0 数学,ss03.score 英语 from student_score ss03 where ss03.subject='英语'
Collapse file

‎database/code/04行专列.~sql‎

Copy file name to clipboard
+92Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
create table tmp(rq varchar2(10),shengfu varchar2(5));
2+
3+
insert into tmp values('2005-05-09','ʤ');
4+
insert into tmp values('2005-05-09','ʤ');
5+
insert into tmp values('2005-05-09','��');
6+
insert into tmp values('2005-05-09','��');
7+
insert into tmp values('2005-05-10','ʤ');
8+
insert into tmp values('2005-05-10','��');
9+
insert into tmp values('2005-05-10','��');
10+
11+
/*
12+
ʤ ��
13+
2005-05-09 2 2
14+
2005-05-10 1 2
15+
16+
*/
17+
18+
select rq,decode(shengfu,'ʤ',1),decode(shengfu,'��',2) from tmp;
19+
20+
select rq,
21+
count(decode(shengfu, 'ʤ', 1)) ʤ,
22+
count(decode(shengfu, '��', 2)) ��
23+
from tmp
24+
group by rq;
25+
26+
27+
create table STUDENT_SCORE
28+
(
29+
name VARCHAR2(20),
30+
subject VARCHAR2(20),
31+
score NUMBER(4,1)
32+
);
33+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 78.0);
34+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 88.0);
35+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 98.0);
36+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 89.0);
37+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 76.0);
38+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 90.0);
39+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 99.0);
40+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 66.0);
41+
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 91.0);
42+
43+
44+
/*
45+
���� ���� ��ѧ Ӣ��
46+
���� 89 56 89
47+
*/
48+
--����ʹ��4�з�ʽ��д��
49+
--decode
50+
select ss.name,
51+
max(decode(ss.subject, '����', ss.score)) ����,
52+
max(decode(ss.subject, '��ѧ', ss.score)) ��ѧ,
53+
max(decode(ss.subject, 'Ӣ��', ss.score)) Ӣ��
54+
from student_score ss group by ss.name
55+
--case when
56+
select ss.name,
57+
max(case ss.subject
58+
when '����' then
59+
ss.score
60+
end) ����,
61+
max(case ss.subject
62+
when '��ѧ' then
63+
ss.score
64+
end) ��ѧ,
65+
max(case ss.subject
66+
when 'Ӣ��' then
67+
ss.score
68+
end) Ӣ��
69+
from student_score ss
70+
group by ss.name;
71+
--join
72+
select ss.name,ss.score from student_score ss where ss.subject='����';
73+
select ss.name,ss.score from student_score ss where ss.subject='��ѧ';
74+
select ss.name,ss.score from student_score ss where ss.subject='Ӣ��';
75+
76+
select ss01.name, ss01.score ����, ss02.score ��ѧ, ss03.score Ӣ��
77+
from (select ss.name, ss.score
78+
from student_score ss
79+
where ss.subject = '����') ss01
80+
join (select ss.name, ss.score
81+
from student_score ss
82+
where ss.subject = '��ѧ') ss02
83+
on ss01.name = ss02.name
84+
join (select ss.name, ss.score
85+
from student_score ss
86+
where ss.subject = 'Ӣ��') ss03
87+
on ss01.name = ss03.name;
88+
89+
--union all
90+
select ss01.name,ss01.score ����,0 ��ѧ,0 Ӣ�� from student_score ss01 where ss01.subject='����' union all
91+
select ss02.name,0 ����,ss02.score ��ѧ,0 Ӣ�� from student_score ss02 where ss02.subject='��ѧ' union all
92+
select ss03.name,0 ����,0 ��ѧ,ss03.score Ӣ�� from student_score ss03 where ss03.subject='Ӣ��'

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.