Skip to content

Commit 6ff5eaf

Browse files
author
msbbigdata
committed
upload
1 parent 61de803 commit 6ff5eaf

File tree

6 files changed

+355
-0
lines changed

6 files changed

+355
-0
lines changed

database.zip

3.3 MB
Binary file not shown.

database/code/01.sql

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,3 +27,138 @@ select e.empno as
2727
select e.empno as "雇员 编号",e.ename as "雇员 名称",e.job as "雇员 工作" from emp e where e.deptno = 10;
2828
--查询表中的所有字段,可以使用*,但是在项目中千万不要随便使用*,容易被打死
2929
select * from emp;
30+
31+
32+
/*
33+
=,!=,<>,<,>,<=,>=,any,some,all
34+
is null,is not null
35+
between x and y
36+
in(list),not in(list)
37+
exists(sub-query)
38+
like _ ,%,escape ‘\‘ _\% escape ‘\’
39+
40+
*/
41+
-- =
42+
select * from emp where deptno = 20;
43+
--!=
44+
select * from emp where deptno !=20;
45+
--<> 不等于
46+
select * from emp where deptno <> 20;
47+
--<,
48+
select sal from emp where sal <1500;
49+
-->,
50+
select sal from emp where sal >1500;
51+
--<=,
52+
select sal from emp where sal <=1500;
53+
-->=,
54+
select sal from emp where sal >=1500;
55+
--any,取其中任意一个
56+
select sal from emp where sal > any(1000,1500,3000);
57+
--some,some跟any是同一个效果,只要大于其中某一个值都会成立
58+
select sal from emp where sal > some(1000,1500,3000);
59+
--all,大于所有的值才会成立
60+
select sal from emp where sal > all(1000,1500,3000);
61+
--is null,在sql的语法中,null表示一个特殊的含义,null != null,不能使用=,!=判断,需要使用is ,is not
62+
select * from emp where comm is null;
63+
--,is not null
64+
select * from emp where comm is not null;
65+
select * from emp where null is null;
66+
--between x and y,包含x和y的值
67+
select * from emp where sal between 1500 and 3000;
68+
select * from emp where sal >=1500 and sal <=3000;
69+
--需要进行某些值的等值判断的时候可以使用in和not in
70+
--in(list),
71+
select * from emp where deptno in(10,20);
72+
--可是用and 和or这样的关键字,and相当于是与操作,or相当于是或操作
73+
--and和or可能出现在同一个sql语句中,此时需要注意and和or的优先级
74+
--and 的优先级要高于or,所以一定要将or的相关操作用()括起来,提高优先级
75+
select * from emp where deptno = 10 or deptno = 20;
76+
--not in(list)
77+
select * from emp where deptno not in(10,20);
78+
select * from emp where deptno != 10 and deptno !=20;
79+
/*exists(sub-query),当exists中的子查询语句能查到对应结果的时候,
80+
意味着条件满足
81+
相当于双层for循环
82+
--现在要查询部门编号为10和20的员工,要求使用exists实现
83+
*/
84+
select * from emp where deptno = 10 or deptno = 20;
85+
--通过外层循环来规范内层循环
86+
select *
87+
from emp e
88+
where exists (select deptno
89+
from dept d
90+
where (d.deptno = 10 or d.deptno = 20)
91+
and e.deptno = d.deptno)
92+
/*
93+
模糊查询:
94+
like _ ,%,escape ‘\‘ _\% escape ‘\’
95+
96+
在like的语句中,需要使用占位符或者通配符
97+
_,某个字符或者数字仅出现一次
98+
%,任意字符出现任意次数
99+
escape,使用转义字符,可以自己规定转义字符
100+
101+
使用like的时候要慎重,因为like的效率比较低
102+
使用like可以参考使用索引,但是要求不能以%开头
103+
涉及到大文本的检索的时候,可以使用某些框架 luence,solr,elastic search
104+
*/
105+
--查询名字以S开头的用户
106+
select * from emp where ename like('S%')
107+
--查询名字以S开头且倒数第二个字符为T的用户
108+
select * from emp where ename like('S%T_');
109+
select * from emp where ename like('S%T%');
110+
--查询名字中带%的用户
111+
select * from emp where ename like('%\%%') escape('\')
112+
/*
113+
114+
order by进行排序操作
115+
默认情况下完成的是升序的操作,
116+
asc:是默认的排序方式,表示升序
117+
desc:降序的排序方式
118+
119+
排序是按照自然顺序进行排序的
120+
如果是数值,那么按照从大到小
121+
如果是字符串,那么按照字典序排序
122+
123+
在进行排序的时候可以指定多个字段,而且多个字段可以使用不同的排序方式
124+
125+
每次在执行order by的时候相当于是做了全排序,思考全排序的效率
126+
会比较耗费系统的资源,因此选择在业务不太繁忙的时候进行
127+
*/
128+
select * from emp order by sal;
129+
select * from emp order by sal desc;
130+
select * from emp order by ename;
131+
select * from emp order by sal desc,ename asc;
132+
--使用计算字段
133+
--字符串连接符
134+
select 'my name is '||ename name from emp;
135+
select concat('my name is ',ename) from emp;
136+
--计算所有员工的年薪
137+
select ename,(e.sal+e.comm)*12 from emp e;
138+
--null是比较特殊的存在,null做任何运算都还是为null,因此要将空进行转换
139+
--引入函数nvl,nvl(arg1,arg2),如果arg1是空,那么返回arg2,如果不是空,则返回原来的值
140+
select ename,(e.sal+nvl(e.comm,0))*12 from emp e;
141+
--dual是oracle数据库中的一张虚拟表,没有实际的数据,可以用来做测试
142+
select 100+null from dual;
143+
--A
144+
select * from emp where deptno =30;
145+
--B
146+
select * from emp where sal >1000;
147+
--并集,将两个集合中的所有数据都进行显示,但是不包含重复的数据
148+
select * from emp where deptno =30 union
149+
select * from emp where sal >1000;
150+
--全集,将两个集合的数据全部显示,不会完成去重的操作
151+
select * from emp where deptno =30 union all
152+
select * from emp where sal >1000;
153+
--交集,两个集合中交叉的数据集,只显示一次
154+
select * from emp where deptno =30 intersect
155+
select * from emp where sal >1000;
156+
--差集,包含在A集合而不包含在B集合中的数据,跟A和B的集合顺序相关
157+
select * from emp where deptno =30 minus
158+
select * from emp where sal >1000;
159+
160+
161+
162+
163+
164+

database/code/01.~sql

Lines changed: 164 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,164 @@
1+
--��������ע��
2+
comment on table emp is '��Ա��';
3+
--��������ע��
4+
comment on column emp.ename is '��Ա����';
5+
6+
/*sql���ѧϰ
7+
8+
SELECT [DISTINCT] {*,column alias,..}
9+
FROM table alias
10+
Where ��������ʽ
11+
12+
*/
13+
14+
--��ѯ��Ա���в��ű����10��Ա��
15+
select empno,ename,job from emp where deptno = 10;
16+
--dinstinct ȥ���ظ�����
17+
select distinct deptno from emp;
18+
--ȥ��Ҳ������Զ���ֶΣ�����ֶ�ֵֻҪ��һ����ƥ������Dz�ͬ�ļ�¼
19+
select distinct deptno,sal from emp;
20+
21+
22+
--�ڲ�ѯ�Ĺ����п��Ը������ӱ�����ͬʱҲ���Ը������ӱ���
23+
select e.empno ��Ա���,e.ename ��Ա����,e.job ��Ա���� from emp e where e.deptno = 10;
24+
--������������Լ�as��Ҳ���Բ��ӣ���������
25+
select e.empno as ��Ա���,e.ename as ��Ա����,e.job as ��Ա���� from emp e where e.deptno = 10;
26+
--�������������������а����ո���ô��Ҫ�����������á�����������
27+
select e.empno as "��Ա ���",e.ename as "��Ա ����",e.job as "��Ա ����" from emp e where e.deptno = 10;
28+
--��ѯ���е������ֶ�,����ʹ��*,��������Ŀ��ǧ��Ҫ���ʹ��*,���ױ�����
29+
select * from emp;
30+
31+
32+
/*
33+
��������,<>��<,>,<=,>=,any,some,all
34+
is null,is not null
35+
between x and y
36+
in��list����not in��list��
37+
exists��sub��query��
38+
like _ ,%,escape ��\�� _\% escape ��\��
39+
40+
*/
41+
-- =
42+
select * from emp where deptno = 20;
43+
--!=
44+
select * from emp where deptno !=20;
45+
--<> ������
46+
select * from emp where deptno <> 20;
47+
--<,
48+
select sal from emp where sal <1500;
49+
-->,
50+
select sal from emp where sal >1500;
51+
--<=,
52+
select sal from emp where sal <=1500;
53+
-->=,
54+
select sal from emp where sal >=1500;
55+
--any,ȡ��������һ��
56+
select sal from emp where sal > any(1000,1500,3000);
57+
--some,some��any��ͬһ��Ч����ֻҪ��������ijһ��ֵ�������
58+
select sal from emp where sal > some(1000,1500,3000);
59+
--all���������е�ֵ�Ż����
60+
select sal from emp where sal > all(1000,1500,3000);
61+
--is null,��sql���﷨�У�null��ʾһ������ĺ��壬null != null,����ʹ��=����=�жϣ���Ҫʹ��is ,is not
62+
select * from emp where comm is null;
63+
--,is not null
64+
select * from emp where comm is not null;
65+
select * from emp where null is null;
66+
--between x and y,����x��y��ֵ
67+
select * from emp where sal between 1500 and 3000;
68+
select * from emp where sal >=1500 and sal <=3000;
69+
--��Ҫ����ijЩֵ�ĵ�ֵ�жϵ�ʱ�����ʹ��in��not in
70+
--in��list����
71+
select * from emp where deptno in(10,20);
72+
--������and ��or�����Ĺؼ��֣�and�൱�����������or�൱���ǻ����
73+
--and��or���ܳ�����ͬһ��sql����У���ʱ��Ҫע��and��or�����ȼ�
74+
--and �����ȼ�Ҫ����or������һ��Ҫ��or����ز����ã�����������������ȼ�
75+
select * from emp where deptno = 10 or deptno = 20;
76+
--not in��list��
77+
select * from emp where deptno not in(10,20);
78+
select * from emp where deptno != 10 and deptno !=20;
79+
/*exists��sub��query��,��exists�е��Ӳ�ѯ����ܲ鵽��Ӧ�����ʱ��
80+
�����������
81+
�൱��˫��forѭ��
82+
--����Ҫ��ѯ���ű��Ϊ10��20��Ա����Ҫ��ʹ��existsʵ��
83+
*/
84+
select * from emp where deptno = 10 or deptno = 20;
85+
--ͨ�����ѭ�����淶�ڲ�ѭ��
86+
select *
87+
from emp e
88+
where exists (select deptno
89+
from dept d
90+
where (d.deptno = 10 or d.deptno = 20)
91+
and e.deptno = d.deptno)
92+
/*
93+
ģ����ѯ��
94+
like _ ,%,escape ��\�� _\% escape ��\��
95+
96+
��like������У���Ҫʹ��ռλ������ͨ���
97+
_,ij���ַ��������ֽ�����һ��
98+
%�������ַ������������
99+
escape,ʹ��ת���ַ�,�����Լ��涨ת���ַ�
100+
101+
ʹ��like��ʱ��Ҫ���أ���Ϊlike��Ч�ʱȽϵ�
102+
ʹ��like���Բο�ʹ������������Ҫ������%��ͷ
103+
�漰�����ı��ļ�����ʱ�򣬿���ʹ��ijЩ��� luence��solr��elastic search
104+
*/
105+
--��ѯ������S��ͷ���û�
106+
select * from emp where ename like('S%')
107+
--��ѯ������S��ͷ�ҵ����ڶ����ַ�ΪT���û�
108+
select * from emp where ename like('S%T_');
109+
select * from emp where ename like('S%T%');
110+
--��ѯ�����д�%���û�
111+
select * from emp where ename like('%\%%') escape('\')
112+
/*
113+
114+
order by�����������
115+
Ĭ���������ɵ�������IJ�����
116+
asc:��Ĭ�ϵ�����ʽ����ʾ����
117+
desc�����������ʽ
118+
119+
�����ǰ�����Ȼ˳����������
120+
�������ֵ����ô���մӴ�С
121+
������ַ�������ô�����ֵ�������
122+
123+
�ڽ��������ʱ�����ָ������ֶΣ����Ҷ���ֶο���ʹ�ò�ͬ������ʽ
124+
125+
ÿ����ִ��order by��ʱ���൱��������ȫ����˼��ȫ�����Ч��
126+
��ȽϺķ�ϵͳ����Դ�����ѡ����ҵ��̫��æ��ʱ�����
127+
*/
128+
select * from emp order by sal;
129+
select * from emp order by sal desc;
130+
select * from emp order by ename;
131+
select * from emp order by sal desc,ename asc;
132+
--ʹ�ü����ֶ�
133+
--�ַ������ӷ�
134+
select 'my name is '||ename name from emp;
135+
select concat('my name is ',ename) from emp;
136+
--��������Ա������н
137+
select ename,(e.sal+e.comm)*12 from emp e;
138+
--null�DZȽ�����Ĵ��ڣ�null���κ����㶼����Ϊnull�����Ҫ���ս���ת��
139+
--���뺯��nvl��nvl(arg1,arg2),���arg1�ǿգ���ô����arg2��������ǿգ��򷵻�ԭ����ֵ
140+
select ename,(e.sal+nvl(e.comm,0))*12 from emp e;
141+
--dual��oracle���ݿ��е�һ���������û��ʵ�ʵ����ݣ���������������
142+
select 100+null from dual;
143+
--A
144+
select * from emp where deptno =30;
145+
--B
146+
select * from emp where sal >1000;
147+
--�����������������е��������ݶ�������ʾ�����Dz������ظ�������
148+
select * from emp where deptno =30 union
149+
select * from emp where sal >1000;
150+
--ȫ�������������ϵ�����ȫ����ʾ���������ȥ�صIJ���
151+
select * from emp where deptno =30 union all
152+
select * from emp where sal >1000;
153+
--���������������н�������ݼ���ֻ��ʾһ��
154+
select * from emp where deptno =30 intersect
155+
select * from emp where sal >1000;
156+
--�,������A���϶���������B�����е����ݣ���A��B�ļ���˳�����
157+
select * from emp where deptno =30 minus
158+
select * from emp where sal >1000;
159+
160+
161+
162+
163+
164+

database/code/02函数.sql

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
--函数的测试
2+
/*
3+
组函数又称为聚合函数
4+
输入多个值,最终只会返回一个值
5+
组函数仅可用于选择列表或查询的having子句
6+
单行函数
7+
输入一个值,输出一个值
8+
9+
10+
*/
11+
12+
--查询所有员工的薪水总和
13+
select sum(sal) from emp;
14+
--查看表中有多少条记录
15+
select deptno,count(*) from emp group by deptno where count(*) >3;
16+
--字符函数
17+
--concat:表示字符串的连接 等同于||
18+
select concat('my name is ', ename) from emp;
19+
--将字符串的首字母大写
20+
select initcap(ename) from emp;
21+
--将字符串全部转换为大写
22+
select upper(ename) from emp;
23+
--将字符串全部转换为小写
24+
select lower(ename) from emp;
25+
--填充字符串
26+
select lpad(ename,10,'*') from emp;
27+
select rpad(ename,10,'*') from emp;
28+
--去除空格
29+
select trim(ename) from emp;
30+
select ltrim(ename) from emp;
31+
select rtrim(ename) from emp;
32+
--查找指定字符串的位置
33+
select instr('ABABCDEF','A') from emp;
34+
--查看字符串的长度
35+
select length(ename) from emp;
36+
--截取字符串的操作
37+
select substr(ename,0,2) from emp;
38+
--替换操作
39+
select replace('ababefg','ab','hehe') from emp;
40+
41+
--数值函数
42+
--给小数进行四舍五入操作,可以指定小数部分的位数
43+
select round(123.123,2) from dual;
44+
select round(123.128,2) from dual;
45+
select round(-123.128,2) from dual;
46+
47+
--截断数据,按照位数去进行截取,但是不会进行四舍五入的操作
48+
select trunc(123.128,2) from dual;
49+
--取模操作
50+
select mod(10,4) from dual;
51+
select mod(-10,4) from dual;
52+
--向上取整
53+
select ceil(12.12) from dual;
54+
--向下取整
55+
select floor(13.99) from dual;
56+
70.1 KB
Binary file not shown.

database/ppt/02-oracle函数.pdf

3.26 MB
Binary file not shown.

0 commit comments

Comments
 (0)