Skip to content

Commit ed1abc1

Browse files
bjmashibingbjmashibing
committed
upload
1 parent 0a3f594 commit ed1abc1

File tree

3 files changed

+315
-0
lines changed

3 files changed

+315
-0
lines changed

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

Lines 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+

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

Lines 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='英语'

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

Lines 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)