Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
The table of contents is too big for display.
Diff view
Diff view
  •  
  •  
  •  
The diff you're trying to view is too large. We only load the first 3000 changed files.
27 changes: 23 additions & 4 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -19,7 +19,7 @@
```
javase : java基础语法部分
database : 数据库部分
javaweb : java web部分
web 前端 : java web部分
```

​ 每个具体阶段的目录又按照如下目录存放资料,大家需要哪部分下载即可。
Expand Down Expand Up @@ -70,9 +70,28 @@ ppt //存放上课使用的ppt文件,已转换成对应的pdf,避免不同
27 jdk新特性-lambda表达式 2019/10/19 15:00-17:00
28 jdk新特性-stream api及自定义注解 2019/10/19 20:00-22:00
29 javase复习 2019/10/20 15:00-17:00
30 oracle安装介绍 2019/10/26 15:30-17:30
31 oracleSQL 2019/10/26 20:00-22:00
32 oracle函数和多表连接 2019/10/27 15:30-17:30
30 oracle安装介绍 2019/10/26 15:00-17:00
31 oracle SQL 2019/10/26 20:00-22:00
32 oracle函数和多表连接 2019/10/27 15:00-17:00
39 oracle的join语法及行专列 2019/11/2 15:00-17:00
40 oracle视图、用户管理、序列、DML 2019/11/2 20:00-22:00
41 数据库的事务 2019/11/3 15:00-17:00
42 数据库表的创建、表的约束、索引、数据库设计的范式 2019/11/9 15:00-17:00
43 JDBC及代码分层 2019/11/9 20:00-22:00
44 反射技术实现及封装 2019/11/10 15:00-17:00
45 DBUtil及数据库连接池(dbcp、c3p0) 2019/11/16 15:00-17:00
46 数据库连接池(druid、hikariCP)及索引原理 2019/11/16 20:00-22:00
47 索引原理2、mysql基本架构及日志实现 2019/11/17 15:00-17:00
48 mysql的执行计划 2019/11/23 15:00-17:00
49 mysql的锁机制 2019/11/23 20:00-22:00
50 mysql的主从复制和读写分离 2019/11/24 15:00-17:00
51 html的介绍及标签的使用 2019/11/30 15:00-17:00
52 CSS样式及选择器 2019/11/30 20:00-22:00
53 CSS盒子模型及定位 2019/12/1 15:00-17:00
54 CSS浮动及轮播图演示 2019/12/1 20:00-22:00
55 js介绍及内置功能函数、数据类型、变量 2019/12/7 15:00-17:00
56 js的变量及运算符 2019/12/7 20:00-22:00
57 js的流程控制语句 2019/12/8 15:00-17:00
......持续更新
```

131 changes: 131 additions & 0 deletions database/code/03关联查询.sql
Original file line number Diff line number Diff line change
Expand Up @@ -31,3 +31,134 @@ select * from emp e,dept d;
--��92�﷨�У����ű������������᷽��where�Ӿ��У�ͬʱwhere��Ҫ�Ա�������������
--��ˣ��൱�ڽ��������������������ൽһ��̫���ˣ���˳�����99�﷨


--99�﷨
/*
CROSS��JOIN
NATURAL JOIN
USING�Ӿ�
ON�Ӿ�
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Inner join

*/
--cross join ��ͬ��92�﷨�еĵѿ�����
select * from emp cross join dept;
--natural join �൱���ǵ�ֵ���ӣ�����ע�⣬����Ҫд����������������ű����ҵ���ͬ����������
--�����ű��в�������ͬ��������ʱ�򣬻���еѿ���������,��Ȼ���Ӹ�92�﷨��������û���κι�ϵ
select * from emp e natural join dept d ;
select * from emp e natural join salgrade sg;
--on�Ӿ䣬�����������������������
--������������ �൱��92�﷨�еĵ�ֵ����
select * from emp e join dept d on e.deptno = d.deptno;
--�൱��92�﷨�еķǵ�ֵ���ӣ�
select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;
--left outer join ,�������е�ȫ������������ʾ���ұ�û�ж�Ӧ������ֱ����ʾ�ռ���
select * from emp e left outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno = d.deptno(+);
--right outer join ,����ұ��е�ȫ������������ʾ�������û�ж�Ӧ�ļ�¼�Ļ���ʾ�ռ���
select * from emp e right outer join dept d on e.deptno = d.deptno;
select * from emp e,dept d where e.deptno(+) = d.deptno;
--full outer join ,�൱���������Ӻ��������ӵĺϼ�
select * from emp e full outer join dept d on e.deptno = d.deptno;
--inner outer join�����ű������Ӳ�ѯ��ֻ���ѯ����ƥ���¼������
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;
--using,���˿���ʹ��on��ʾ��������֮�⣬Ҳ����ʹ��using��Ϊ��������,��ʱ�����������в��ٹ������κ�һ�ű�
select deptno from emp e join dept d using(deptno);
select e.deptno,d.deptno from emp e join dept d on e.deptno = d.deptno;
--�ܽ�:�����﷨��SQL���û���κ����ƣ��ٹ�˾�п�������ʹ�ã����ǽ���ʹ��99�﷨����Ҫʹ��92�﷨��SQL�Ե��������

--������Ա���֡����ڵ�λ��нˮ�ȼ�
select e.ename, d.loc, sg.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade sg
on e.sal between sg.losal and sg.hisal;


/*
�Ӳ�ѯ��
Ƕ��������sql����е�����sql��䣬���Գ�֮Ϊ�Ӳ�ѯ
���ࣺ
�����Ӳ�ѯ
�����Ӳ�ѯ

*/
--����Щ�˵�нˮ����������Ա��ƽ��нˮ֮�ϵ�
--1������ƽ��нˮ
select avg(e.sal) from emp e;
--2���������˵�нˮ��ƽ��нˮ�Ƚ�
select * from emp e where e.sal > (select avg(e.sal) from emp e);
--����Ҫ���ڹ�Ա������Щ���Ǿ�����
--1����ѯ���еľ����˱��
select distinct e.mgr from emp e;
--2���ٹ�Ա���й�����Щ��ż���
select * from emp e where e.empno in (select distinct e.mgr from emp e);
--ÿ������ƽ��нˮ�ĵȼ�
--1����������ŵ�ƽ��нˮ
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--2����нˮ�ǼDZ������������ƽ��нˮ�ĵȼ�
select t.deptno, sg.grade
from salgrade sg
join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
on t.vsal between sg.losal and sg.hisal;


--1����ƽ��нˮ��ߵIJ��ŵIJ��ű��
--���ŵ�ƽ��нˮ
select e.deptno,avg(e.sal) from emp e group by e.deptno;
--��ƽ��нˮ��ߵIJ���
select max(t.vsal) from (select e.deptno,avg(e.sal) vsal from emp e group by e.deptno) t
--���ű��
select t.deptno
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
where t.vsal =
(select max(t.vsal)
from (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t);
--2������ƽ��нˮ�ĵȼ�
--3������ƽ����нˮ�ȼ�
--����ÿ���˵�нˮ�ȼ�
select e.deptno, sg.grade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal;
--���ղ�����ƽ���ȼ�
select t.deptno, avg(t.grade)
from (select e.deptno, sg.grade
from emp e
join salgrade sg
on e.sal between sg.losal and sg.hisal) t
group by t.deptno;
--���������limit��mysql����������������ģ�����oracle�в���
--��oracle�У������Ҫʹ����������ͷ�ҳ�Ĺ��ܵĻ�������Ҫʹ��rownum��
--����rownum����ֱ��ʹ�ã���ҪǶ��ʹ��
--4����нˮ��ߵ�ǰ5����Ա
select *
from (select * from emp e order by e.sal desc) t1
where rownum <= 5

select * from emp e where rownum <=5 order by e.sal desc
--5����нˮ��ߵĵ�6��10����Ա
select t1.*,rownum
from (select * from emp e order by e.sal desc) t1
where rownum <= 10
--ʹ��rownum��ʱ�����Ҫ���������Ƕ�ף���ʱ���ܽ�rownum��Ϊ���е�һ���У�Ȼ���ٽ����������
select *
from (select t1.*, rownum rn
from (select * from emp e order by e.sal desc) t1
where rownum <= 10) t
where t.rn > 5
and t.rn <= 10;


select *
from (select t1.*, rownum rn
from (select * from emp e order by e.sal desc) t1) t
where t.rn > 5
and t.rn <= 10;


92 changes: 92 additions & 0 deletions database/code/04行专列.sql
Original file line number Diff line number Diff line change
@@ -0,0 +1,92 @@
create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','ʤ');
insert into tmp values('2005-05-09','ʤ');
insert into tmp values('2005-05-09','��');
insert into tmp values('2005-05-09','��');
insert into tmp values('2005-05-10','ʤ');
insert into tmp values('2005-05-10','��');
insert into tmp values('2005-05-10','��');

/*
ʤ ��
2005-05-09 2 2
2005-05-10 1 2

*/

select rq,decode(shengfu,'ʤ',1),decode(shengfu,'��',2) from tmp;

select rq,
count(decode(shengfu, 'ʤ', 1)) ʤ,
count(decode(shengfu, '��', 2)) ��
from tmp
group by rq;


create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 91.0);


/*
���� ���� ��ѧ Ӣ��
���� 89 56 89
*/
--����ʹ��4�з�ʽ��д��
--decode
select ss.name,
max(decode(ss.subject, '����', ss.score)) ����,
max(decode(ss.subject, '��ѧ', ss.score)) ��ѧ,
max(decode(ss.subject, 'Ӣ��', ss.score)) Ӣ��
from student_score ss group by ss.name
--case when
select ss.name,
max(case ss.subject
when '����' then
ss.score
end) ����,
max(case ss.subject
when '��ѧ' then
ss.score
end) ��ѧ,
max(case ss.subject
when 'Ӣ��' then
ss.score
end) Ӣ��
from student_score ss
group by ss.name;
--join
select ss.name,ss.score from student_score ss where ss.subject='����';
select ss.name,ss.score from student_score ss where ss.subject='��ѧ';
select ss.name,ss.score from student_score ss where ss.subject='Ӣ��';

select ss01.name, ss01.score ����, ss02.score ��ѧ, ss03.score Ӣ��
from (select ss.name, ss.score
from student_score ss
where ss.subject = '����') ss01
join (select ss.name, ss.score
from student_score ss
where ss.subject = '��ѧ') ss02
on ss01.name = ss02.name
join (select ss.name, ss.score
from student_score ss
where ss.subject = 'Ӣ��') ss03
on ss01.name = ss03.name;

--union all
select t.name,sum(t.����),sum(t.��ѧ),sum(t.Ӣ��) from (select ss01.name,ss01.score ����,0 ��ѧ,0 Ӣ�� from student_score ss01 where ss01.subject='����' union all
select ss02.name,0 ����,ss02.score ��ѧ,0 Ӣ�� from student_score ss02 where ss02.subject='��ѧ' union all
select ss03.name,0 ����,0 ��ѧ,ss03.score Ӣ�� from student_score ss03 where ss03.subject='Ӣ��') t group by t.name
92 changes: 92 additions & 0 deletions database/code/04行专列.~sql
Original file line number Diff line number Diff line change
@@ -0,0 +1,92 @@
create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','ʤ');
insert into tmp values('2005-05-09','ʤ');
insert into tmp values('2005-05-09','��');
insert into tmp values('2005-05-09','��');
insert into tmp values('2005-05-10','ʤ');
insert into tmp values('2005-05-10','��');
insert into tmp values('2005-05-10','��');

/*
ʤ ��
2005-05-09 2 2
2005-05-10 1 2

*/

select rq,decode(shengfu,'ʤ',1),decode(shengfu,'��',2) from tmp;

select rq,
count(decode(shengfu, 'ʤ', 1)) ʤ,
count(decode(shengfu, '��', 2)) ��
from tmp
group by rq;


create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '����', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', '��ѧ', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('����', 'Ӣ��', 91.0);


/*
���� ���� ��ѧ Ӣ��
���� 89 56 89
*/
--����ʹ��4�з�ʽ��д��
--decode
select ss.name,
max(decode(ss.subject, '����', ss.score)) ����,
max(decode(ss.subject, '��ѧ', ss.score)) ��ѧ,
max(decode(ss.subject, 'Ӣ��', ss.score)) Ӣ��
from student_score ss group by ss.name
--case when
select ss.name,
max(case ss.subject
when '����' then
ss.score
end) ����,
max(case ss.subject
when '��ѧ' then
ss.score
end) ��ѧ,
max(case ss.subject
when 'Ӣ��' then
ss.score
end) Ӣ��
from student_score ss
group by ss.name;
--join
select ss.name,ss.score from student_score ss where ss.subject='����';
select ss.name,ss.score from student_score ss where ss.subject='��ѧ';
select ss.name,ss.score from student_score ss where ss.subject='Ӣ��';

select ss01.name, ss01.score ����, ss02.score ��ѧ, ss03.score Ӣ��
from (select ss.name, ss.score
from student_score ss
where ss.subject = '����') ss01
join (select ss.name, ss.score
from student_score ss
where ss.subject = '��ѧ') ss02
on ss01.name = ss02.name
join (select ss.name, ss.score
from student_score ss
where ss.subject = 'Ӣ��') ss03
on ss01.name = ss03.name;

--union all
select t.name,sum(t.����),sum(t.��ѧ),sum(t.Ӣ��) from (select ss01.name,ss01.score ����,0 ��ѧ,0 Ӣ�� from student_score ss01 where ss01.subject='����' union all
select ss02.name,0 ����,ss02.score ��ѧ,0 Ӣ�� from student_score ss02 where ss02.subject='��ѧ' union all
select ss03.name,0 ����,0 ��ѧ,ss03.score Ӣ�� from student_score ss03 where ss03.subject='Ӣ��') t group by t.name
Loading