oracle初级（续）
时间：2021-07-01 10:21:17
帮助过：4人阅读
select * from emp
order by deptno
asc ;
2
3 select * from dept;
4 --oracle 拼接字符串
5 --oracle中字符串需要在英文或其他文字两端用单引号包含
6 select empno, ename,
‘职位是‘ || job, sal
from emp;
7
8 --列名的别名起别名利用空格
9 select empno, ename,
‘职位是‘ || job haha, sal
from emp;
10 --如果使用双引号声明别名，则可以区分大小写
11 select empno, ename,
‘职位是‘ || job "haha", sal
from emp;
12 --如果想在别名中显示空格，一定要有双引号
13
14 --–关键字AS
15 select empno, ename,
‘职位是‘ || job
as "ha ha", sal
from emp;
16 --–注意：推荐使用as声明别名，容易区分表达式与别名的界限
17
18 -- 去掉重复行
19 select distinct deptno
from emp;
20 -注意：distinct只能放在select与列名之间的位置
21 -- select distinct 列1，列2，..列n
22 --select和from 书写顺序 select…from
23 执行顺序
from…
select
24
25 --不等于
26 select * from emp
where deptno
!= 20;
27 select * from emp
where deptno
<>20;
--推荐这种写法
28
29 --查询条件中的 与关系
30 问题一：
31 查询在10号部门工作，并且月薪高于2000，
32 并且职位是MANAGER的员工信息
33
34 select * from emp
where deptno
= 10 and sal
>2000 and job
= ‘MANAGER‘;
35 --或关系or
36 问题一：
37 查询在10号部门工作或者工资高于2000的员工
38
39 select * from emp
where deptno
=10 or sal
>2000;
40 --如果and与or一起使用
41 –and的优先级要比or高（重要）
42 如果要想修改优先级，加括号查询在10号部门工作或者工资高于2000,并且职位是MANAGER的员工
43
44
45 select * from emp
where (deptno
= 10 or sal
> 2000)
and job
= ‘MANAGER‘;
46
47 -- 特殊的比较运算符
48
49 --beetween ... and
50
51 --beetween 上限 and 下限
52
53 查询emp表中，20号部门月薪在1000
~2000之间的员工信息
54
55 select * from emp
where (sal
between 1000 and 2000)
and deptno
= 20;
56
57 –
between and 使用范围 数值和日期
58 –数据库的常见数据类型：
59 –
1）文字
60 –
2）数值
61 –
3）日期
/时间
62
63 select * from emp
where hiredate
between ‘1-1月-1982‘ and ‘31-12月-1982‘;
64
65 --between and 也可以比较字符串，比较顺序是按照字典的顺序比较
66
67
68 select * from emp
where ename
between ‘A‘ and ‘C‘;
69
70 --上下限反着写
71 select * from emp
where sal
between 2000 and 1000;
72 -- 语句不报错，但是逻辑关系没有，没有结果
73 --关键字in 格式 列 in（值1，值2，值3…值n）列的取值是值1或值2或值3…或值n
74 select ename, mgr
from emp
where mgr
in(
7902,
7698,
7788);
75 --like… 像…（重点）
76 模糊查询
77 通配字符
78 % :
0-n个任意字符
79 _:1一个任意字符
80 --查询所有员工首字母为B的员工信息
81 select * from emp
where ename
like ‘B%‘;
82 --–练习：查询倒数第二个字符是R的员工信息
83
84 --like只能用于文字类型的模糊查询匹配。
85 select * from emp
where ename
like ‘%R_‘;
86
87 --is null
88 用于筛选空值
89 因为
=null的逻辑关系是null，不能筛选出空值
90 查询没有奖金的人
91
92
93 select * from emp
where comm
= 0 or comm
is null;
94
95 --NOT
96 逻辑非关系,表示取反
97
98 --not between and
99
100 查询月薪不在1000
~2000之间的员工信息
101
102 select * from emp
where sal
not between 1000 and 2000;
103 列名在not前后结果一致，保证列名在between前即可
104 --not in
105
106 查询其上司的员工编号不是7902,
7698,7788的员工的信息
107
108 select * from emp
where mgr
not in(
7902,
7698,
7788);
109
110 --not like
111
112 查询所有不姓张的员工
113
114 select * from emp
115 where ename
not like ‘张%‘;
116 --is not null
117 select * from emp
118 where comm
is not null;
119
120
121 排序
122 –
order by
123 –升序 由小到大
124 –数值：由小到大
125 –日期：由早到晚
126 –文字：字典顺序
127
128
129 查询所有10号部门的员工信息，按照月薪升序进行排序
130
131
132 select * from emp
where deptno
= 10 order by sal ;
133
134 有的环境下不加升序排列，环境默认的是升序，
135 oracle默认的是升序
136
137 select * from emp
where deptno
= 10 order by sal
asc ;
138
139 推荐写法
140
141 SQl语句执行顺序
142 书写顺序:
select…
from…
where…
order by
143 执行顺序:
from…
where…
select…
order by
144 where不能使用别名，因为select中尚未执行，
145 所以还没有创建出别名，
order by可以使用别名，
146 因为order by在select之后执行，列的别名已经被创建
147 --多列排序
148 查询员工信息，按照部门编号排序，月薪降序
149 select * from emp
order by deptno
asc, sal
desc;
150
151 --oracle函数
152 单行函数、多行函数（分组函数）
153 单行函数
154 dual
155 --系统显示当前日期
156 select sysdate
from dual;
157
158 使用的单行函数，对查询结果的每一行记录都有效，
159 会影响到每一行的记录
160 dual表为了补全sql语句
161
162 --转大写 upper(str) 将str字符串转成大写
163
164 select upper(
‘hAHa‘)
as 描述
from dual;
165
166 可以为列增加别名
167
168 --转小写lower(str) 将str字符串转成小写
169
170 select lower(
‘hAHa‘)
from dual;
171
172 --首字母转大写，其余字母小写initcap(str)
173
174 select initcap(
‘hAHa‘)
from dual;
175
176 select initcap(
‘i LiKe GIRL‘)
from dual;
177
178 --以单词为单位，每个单词的首字母大写，其余的小写
179
180 select * from emp
where lower(ename)
= lower(
‘ScOTT‘);
181 --lower()替换为upper()效果一样
182
183 字符截取函数
184 --substr(str,n1,n2)
185 str:被截取的字符串
186 n1：开始截取的位置（以1的位置开始）
187 n2：截取字符的个数
188 将str字符串从n1位置起，截取n2个字符
189 结果：字符型类
190
191
192 select substr(
‘0123456‘,
3,
3)
from dual;
193
194 从n1的位置开始，要包含该位置的字符
195
196 字符查找函数
197 --instr(str1,str2,n1,n2)
198 在str1中寻找str2的字符串，
199 从n1的位置起，第n2次出现的位置
200 结果是一个位置，位置是一个数值
201
202 select instr(
‘I LIKE GIRL‘,
‘I‘,
2,
1)
from dual;
203
204 --字符拼接函数
205 select ‘haha‘ || ‘xixi‘ || ‘hehe‘ from dual;
206 –将str1和str2拼接组成一个字符串，功能等同于
||
207 --concat(str1,str2)
208 select concat(
‘haha‘,
‘xixi‘)
from dual;
209
210 select concat(concat(
‘haha‘,
‘xixi‘),
‘hehe‘)
from dual;
211
212 字符替换函数
213 --replace(str1,str2,str3)
214 在str1字符串中，使用str3来替换全部的str2
215
216 select replace(
‘ABCDECD‘,
‘CD‘,
‘HAHA‘)
from dual;
217
218 字符串的长度函数
219 --length(str)
220 返回str字符串的文字的个数
221
222 select length(
‘abcde‘), length(
‘哈哈‘)
from dual;
223
224 字符串补齐函数
225 lpad(str1,n1,str2)
226 rpad(str1,n1,str2)
227 lpad:将str1字符串，使用str2在左侧补充到n1个长度
228 rpad:将str1字符串，使用str2在右侧补充到n1个长度
229
230 select lpad(
‘abc‘,
6,
‘哈呵‘), rpad(
‘abc‘,
2,
‘*‘)
231 from dual;
232
233 --trunc(n1,n2)
234 –将n1数字舍弃，精确到小数点后n2位
235 select trunc(
3.1415,
3), trunc(
3.14,
0), trunc(
6.14,
-1)
from dual;
236 --MOD(n1,n2)
237 求n1除以n2后得到的余数 取余
238
239 select mod(
5,
3), mod(
5,
-3), mod(
-5,
3), mod(
-5,
-3)
240 from dual;
241 取余运算区分正负值，看被除数 编程也是遵循这样原则
242
243 日期函数（重要）
244 --sysdate（函数）
245 获取数据库所在服务器的当前系统时间
246 select sysdate
from dual;
247
248
249 --months_between(date1,date2) date1与date2之间的月数差
250 假设emp表中，所有员工至今未辞职，计算他们现在的司龄
251 months_between(date1,date2)与between and上下限不是对应的
252
253
254 select ename, hiredate,trunc(months_between(sysdate, hiredate)
/ 12,
0)
as 司龄
from emp;
255 用于计算年龄，司龄，工龄
256 这些类似的随着时间流逝而改变的数据
257
258
259 --add_months(date1,d1)
260 为date1的时间，追加d1个月，结果还是一个日期
261 设公司的试用期为6个月，计算emp表中员工的转正日期
262
263
264 select ename, hiredate, add_months(hiredate,
6)
as 转正日期
from emp;
265
266 --last_day(date1)
267 date1所在月份最后一天的日期（结果的类型还是日期）
268
269 select ename, hiredate, last_day(hiredate)
270 from emp;
271
272 两个日期之间的天数(重要)
273
274 差值就是天数，没有小数点
275
276 --next_day(date1,n1)
277 返回date1起之后周几的日期
278 1:周日
2：周一 …
7:周六
279
280 select next_day(sysdate,
7)
from dual;
281
282
283 转换函数（重点）
284 --to_number(str)
285 将str字符串转为数字
286
287 select to_number(
‘1234‘)
from dual;
288
289 按照特定格式转换
290 --to_number(str1,fmt) fmt格式也一定要是一个字符串 fmt也叫做格式码
291 –$ : 美元符号
292 –, :三位分隔符或者千位分割符
100,
222,
119.00
293 –
9 ：一位数字
294 –
0 ：一位数字或者前导0(补全前面的位置)（了解）
295
296
297 如果实际数据超过fmt定义的长度，则数值无法显示
298 实际数据小于fmt定义的长度，数值可以显示
299
300
301 --to_char(n1,fmt)
302 将数值转换为fmt表示的文字
303
304 select to_number(
‘$23,412,123.34‘,
‘$99,999,999.99‘)
from dual;
305
306 select to_char(
2236778,
‘$9,999,999.99‘)
from dual;
307
308 –日期转字符 to_char(date1,fmt)
309 –日期的格式码：
310 –YYYY 年
311 –MM 月
312 –DD 日
313 –HH24 24进制的小时
314 –HH12 12进制的小时
315 –MI 分
316 –SS 秒
317 – -
/ 常规日期分割符
318 –: 常规时间分割符
319 DATE类型 精确到秒
320 查询员工姓名和入职日期
321 入职日期按照”年
/月
/日 时:分:秒”的格式显示
322
323 select ename, to_char(hiredate,
‘YYYY/MM/DD HH24:MI:SS‘)
from emp;
324 YY,RR 也是表示年
325
326 --to_date
327
328 select to_date(
‘2017-4-7‘,
‘YYYY-MM-DD‘)
from dual;
329
330 查询在1982年期间入职的员工 按照规定格式显示
331 XXXX-XX-XX
332 select * from empwhere hiredate
between to_date(
‘1982-1-1‘,
‘YYYY-MM-DD‘)
and to_date(
‘1982-12-31‘,
‘YYYY-MM-DD‘);
333
334
335 通用函数
336 --nvl(expr1,expr2)
337 当expr1值不是null时，函数结果是expr1
338 当expr1值是null时，函数的结果是expr2
339 计算员工月收入，如果员工有奖金，则奖金
+500
340 如果员工没有奖金，则不发奖金
341
342 select ename, comm, sal
+ nvl(comm
+ 500,
0)
from emp;
343 --nvl2(expr1,expr2,expr3)
344 判断expr1的值是否为null
345 如果是null使用expr3的值
346 如果不是null使用expr2的值
347
348 select comm, nvl2(comm,
1,
2)
from emp;
349 给员工发节日福利，有奖金的人节日福利是1000,没有奖金的人节日福利是800
350 select comm, nvl2(comm,
1000,
800)
from emp;
351
352 多行函数（分组函数）
353 由多行记录为一组统计出的数据，利用的是分组函数（多行函数）
354 常见的统计型数据
355 求和
356 --sum()
357 –查询emp表中，所有月薪的总和
358 select sum(sal)
from emp?
359 求平均
360 --avg()
361 求emp中平均司龄
362 select avg(months_between(sysdate,hiredate)
/ 12)
from emp;
363 最大值
364 --max()
365 –查询emp表中，最高月薪
366 select max(sal)
from emp;
367 最小值
368 --min()
369 –查询emp表中，最低月薪
370 select min(sal)
from emp;
371 计数
372 --count()
373 查询emp表中，有多少个员工
374 select count(empno)
from emp;
375 select count(
distinct deptno)
from emp;
376 去掉重复数据用distinct，在count函数内部
377 查询每个部门的平均工资
378 --group by 依据XX分组 不是分组函数
379 只有使用了分组函数，才能使用group
by
380
381 select deptno ,
avg(sal)
from emp
group by deptno;
382
383 只有在group by中出现的列，才能写在select之后（必须记住）
384 多次分组，第二次分组是在第一次分组的基础上进行的
385 查询10号和20号部门的平均工资
386
387 select deptno,
avg(sal)
from emp
where deptno
in(
10,
20)
group by deptno
order by deptno
asc;
388 --书写顺序
389 --执行顺序 from...where...group by...select...order by...
390 分组函数是在group
by 执行位置进行计算
391 查询平均工资高于2500的部门id和其平均工资
392 select deptno,
avg(sal)
from emp
where avg(sal)
> 2500 --错误写法group by deptno
393 -- where 后面不允许使用分组函数
394 --having
395 –专门处理分组函数的筛选
396 –
group by…
having…
397
398 –查询平均工资在2000
~2500之间的部门ID和其平均工资
399 select deptno,
avg(sal)
from emp
group by deptno
having avg(sal)
between 2000 and 2500;
400 select deptno,
avg(sal)
from emp
group by deptno
having avg(sal)
>= 2000 and avg(sal)
<= 2500;
401 select deptno,
avg(sal)
from emp
group by deptno
having avg(sal)
> 2500;
402 --执行顺序
403
404 --from ...where... group by ... having... select ...order ...by
今天先写这些，未完，待续。。。。
oracle初级（续）
标签：区分 max 等于 整理 比较 排列 red 创建 辞职
