alin的学习之路（数据库篇：二）(select查询，where条件查询，order by排序，单行函数，多行函数，group by分组)
1. SQL语句
1.1 sql语言类型
sql是一门独立的语言, 有自己的语法结构, 所有的关系型数据库都是支持sql这种语言的
- sql的执行是在客户端, 相当于个服务器发送请求, 服务器通过不同的sql回复不同的数据
不管是什么类型的语言对应的都是不同语法格式的字符串
- DML – 数据操纵语言 （
Data Manipulation Language）- insert （添加数据）
- delete （删除数据）
- update（修改数据）
- select （查询数据）
- DDL – 数据定义语言（
Data Definition Language）- create table （创建表）
- alter table （修改表）
- truncate table（清空表）
- drop table （删除表）
- create view （视图）
- create index （索引）
- create sequence（序列）
- create synonym（同义词）
- DCL – 数据控制语言（
Data Control Language）- commit （提交）
- rollback （回滚）
2. select 语句基础查询
select 列名 from 表 where 条件;
2.1 查看表中的所有字段
使用语句 desc 表名;
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) 员工编号
ENAME VARCHAR2(50) Y 员工姓名
JOB VARCHAR2(20) Y 职位
MGR NUMBER(4) Y 直属领导(编号)
HIREDATE DATE Y 入职时间
SAL NUMBER(7,2) Y 工资
COMM NUMBER(7,2) Y 佣金
DEPTNO NUMBER(2) Y 所属部门的编号
其中：Name这一列下都是 字段 名
字段代表表中的列，记录代表表中的行。
2.2 查询所有列
使用语句select * from 表名;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------------------------------------- -------------------- ----- -----------
7369 文斯莫克·山治 厨师 7566 1980/12/17 3200.00 800.00 20
。。。。。。
。。。。。。
从得到的查询中抽离出以上的信息，那么第一行代表所有的字段，第二行以及下面的每一行，代表记录
2.3 查询指定列
语法：
SELECT 列名1|表达式, 列名2|表达式(1+2), ... FROM 表名;
-- 查询"emp" 表中员工号, 姓名, 月薪, 佣金
SQL> select empno,ename,sal,comm from emp;
EMPNO ENAME SAL COMM
----- -------------------------------------------------- --------- ---------
7369 文斯莫克·山治 3200.00 800.00
7499 罗罗诺亚·索隆 3450.00 300.00
7521 娜美 4560.00 500.00
7566 蒙奇·D·路飞 5450.00 1000.00
7654 妮可·罗宾 5500.00 2400.00
7698 特拉法尔加·D·瓦铁尔·罗 5655.00 1000.00
7782 贝波 2450.00 20.00
7788 乌索普 3000.00
7839 尾田_荣一郎 5000.00
7844 弗兰奇 1500.00 0.00
7876 布鲁克 1100.00
7900 甚平 2000.00
7902 香克斯 6000.00 1500.00
7934 托尼托尼·乔巴 800.68 10.00
7840 岸本_齐史 5000.00
7709 Jinbe 3550.00 600.00
7699 夏奇 2050.00 200.00
7700 佩金 3000.00 200.00
7937 拉基·路 3600.00 500.00
7938 洛克斯达 2600.00 200.00
7783 强巴鲁 3600.00 200.00
7784 伊卡库 3000.00 200.00
7785 库里奥尼 3500.00 200.00
7935 本·贝克曼 5500.00
7936 耶稣布 5500.00 500.00
6100 马歇尔·D·帝奇 5675.00
6101 芝沙斯·巴沙斯 4321.00 5.00
6102 阿巴罗·皮萨罗 2540.00
6103 范·奥卡 3450.00
1000 Monkey·D·Luffy 5500.00
1001 Trafalgar·D·Water·Law 5800.00
1002 Marshall·D·Teach 7800.00
1003 Portgas·D·Ace 5500.00
1004 Hagwar·D·Sauro 3450.00
1005 Gol·D·Roger 7500.00
1006 Monkey·D·Dragon 6550.00
1007 Monkey·D·Garp 7890.00
7701 Roronoa Zoro 2000.00 800.00
7702 Nami 3000.00 1000.00
7703 Usopp 2500.00 500.00
7704 Sanji 2800.00 1200.00
7705 Tony Tony Chopper 100.00 300.00
7706 Nico Robin 5000.00 800.00
7707 Franky 2000.00 500.00
7708 Brook 3000.00 900.00
45 rows selected
-- 表达式: 基于列做加减乘除等相关操作
-- 查询"emp" 表中员工号, 姓名, 月薪, 佣金, 年薪(sal*12)。
-- 年薪的列名依也就是sal*12
SQL> select empno,ename,sal,comm,sal*12 from emp;
EMPNO ENAME SAL COMM SAL*12
----- -------------------------------------------------- --------- --------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400
7499 罗罗诺亚·索隆 3450.00 300.00 41400
7521 娜美 4560.00 500.00 54720
7566 蒙奇·D·路飞 5450.00 1000.00 65400
7654 妮可·罗宾 5500.00 2400.00 66000
7698 特拉法尔加·D·瓦铁尔·罗 5655.00 1000.00 67860
7782 贝波 2450.00 20.00 29400
7788 乌索普 3000.00 36000
7839 尾田_荣一郎 5000.00 60000
7844 弗兰奇 1500.00 0.00 18000
7876 布鲁克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000
7934 托尼托尼·乔巴 800.68 10.00 9608.16
7840 岸本_齐史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600
7699 夏奇 2050.00 200.00 24600
7700 佩金 3000.00 200.00 36000
7937 拉基·路 3600.00 500.00 43200
7938 洛克斯达 2600.00 200.00 31200
7783 强巴鲁 3600.00 200.00 43200
7784 伊卡库 3000.00 200.00 36000
7785 库里奥尼 3500.00 200.00 42000
7935 本·贝克曼 5500.00 66000
7936 耶稣布 5500.00 500.00 66000
6100 马歇尔·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852
6102 阿巴罗·皮萨罗 2540.00 30480
6103 范·奥卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000
7702 Nami 3000.00 1000.00 36000
7703 Usopp 2500.00 500.00 30000
7704 Sanji 2800.00 1200.00 33600
7705 Tony Tony Chopper 100.00 300.00 1200
7706 Nico Robin 5000.00 800.00 60000
7707 Franky 2000.00 500.00 24000
7708 Brook 3000.00 900.00 36000
45 rows selected
2.4 查询指定别名
在查询的时候, 表头默认是列的名字, 如果列名不直观, 可以指定别名, 别名主要用于显示, 显示到表头
上, 替换原来的列名
语法： select 列|表达式 （as，可不写） "别名"…… from 表;
SQL> select empno "员工编号",ename "姓名",sal "月薪",comm "佣金",sal*12 "年薪" from emp;
员工编号 姓名 月薪 佣金 年薪
----- -------------------------------------------------- --------- --------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400
7499 罗罗诺亚·索隆 3450.00 300.00 41400
7521 娜美 4560.00 500.00 54720
7566 蒙奇·D·路飞 5450.00 1000.00 65400
7654 妮可·罗宾 5500.00 2400.00 66000
7698 特拉法尔加·D·瓦铁尔·罗 5655.00 1000.00 67860
7782 贝波 2450.00 20.00 29400
7788 乌索普 3000.00 36000
7839 尾田_荣一郎 5000.00 60000
7844 弗兰奇 1500.00 0.00 18000
7876 布鲁克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000
7934 托尼托尼·乔巴 800.68 10.00 9608.16
7840 岸本_齐史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600
7699 夏奇 2050.00 200.00 24600
7700 佩金 3000.00 200.00 36000
7937 拉基·路 3600.00 500.00 43200
7938 洛克斯达 2600.00 200.00 31200
7783 强巴鲁 3600.00 200.00 43200
7784 伊卡库 3000.00 200.00 36000
7785 库里奥尼 3500.00 200.00 42000
7935 本·贝克曼 5500.00 66000
7936 耶稣布 5500.00 500.00 66000
6100 马歇尔·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852
6102 阿巴罗·皮萨罗 2540.00 30480
6103 范·奥卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000
7702 Nami 3000.00 1000.00 36000
7703 Usopp 2500.00 500.00 30000
7704 Sanji 2800.00 1200.00 33600
7705 Tony Tony Chopper 100.00 300.00 1200
7706 Nico Robin 5000.00 800.00 60000
7707 Franky 2000.00 500.00 24000
7708 Brook 3000.00 900.00 36000
45 rows selected
2.5 表达式中的空字段
查询员工号, 姓名, 月薪, 佣金, 年薪, 总收入 。 [ 总收入 = 年薪 + 佣金 ]
查询总收入时遇到一个问题：因为佣金有的为NULL，当用NULL 与一个数据进行计算时，得出的结果也是NULL
SQL> select empno "员工编号",ename "姓名",sal "月薪",comm "佣金",sal*12 "年薪",sal*12+comm "总收入" from emp;
员工编号 姓名 月薪 佣金 年薪 总收入
----- -------------------------------------------------- --------- --------- ---------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400 39200
7499 罗罗诺亚·索隆 3450.00 300.00 41400 41700
7521 娜美 4560.00 500.00 54720 55220
7566 蒙奇·D·路飞 5450.00 1000.00 65400 66400
7654 妮可·罗宾 5500.00 2400.00 66000 68400
7698 特拉法尔加·D·瓦铁尔·罗 5655.00 1000.00 67860 68860
7782 贝波 2450.00 20.00 29400 29420
7788 乌索普 3000.00 36000
7839 尾田_荣一郎 5000.00 60000
7844 弗兰奇 1500.00 0.00 18000 18000
7876 布鲁克 1100.00 13200
7900 甚平 2000.00 24000
7902 香克斯 6000.00 1500.00 72000 73500
7934 托尼托尼·乔巴 800.68 10.00 9608.16 9618.16
7840 岸本_齐史 5000.00 60000
7709 Jinbe 3550.00 600.00 42600 43200
7699 夏奇 2050.00 200.00 24600 24800
7700 佩金 3000.00 200.00 36000 36200
7937 拉基·路 3600.00 500.00 43200 43700
7938 洛克斯达 2600.00 200.00 31200 31400
7783 强巴鲁 3600.00 200.00 43200 43400
7784 伊卡库 3000.00 200.00 36000 36200
7785 库里奥尼 3500.00 200.00 42000 42200
7935 本·贝克曼 5500.00 66000
7936 耶稣布 5500.00 500.00 66000 66500
6100 马歇尔·D·帝奇 5675.00 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852 51857
6102 阿巴罗·皮萨罗 2540.00 30480
6103 范·奥卡 3450.00 41400
1000 Monkey·D·Luffy 5500.00 66000
1001 Trafalgar·D·Water·Law 5800.00 69600
1002 Marshall·D·Teach 7800.00 93600
1003 Portgas·D·Ace 5500.00 66000
1004 Hagwar·D·Sauro 3450.00 41400
1005 Gol·D·Roger 7500.00 90000
1006 Monkey·D·Dragon 6550.00 78600
1007 Monkey·D·Garp 7890.00 94680
7701 Roronoa Zoro 2000.00 800.00 24000 24800
7702 Nami 3000.00 1000.00 36000 37000
7703 Usopp 2500.00 500.00 30000 30500
7704 Sanji 2800.00 1200.00 33600 34800
7705 Tony Tony Chopper 100.00 300.00 1200 1500
7706 Nico Robin 5000.00 800.00 60000 60800
7707 Franky 2000.00 500.00 24000 24500
7708 Brook 3000.00 900.00 36000 36900
45 rows selected
那么解决这个问题就需要用到一个函数 nvl(参数1，参数2)
该函数用于当参数1为空时，为参数1指定一个代替的值，为参数2
nvl(参数1,参数2)
那么可以把语句修改为：
SQL> select empno "员工编号",ename "姓名",sal "月薪",comm "佣金",sal*12 "年薪",sal*12+nvl(comm,0) "总收入" from emp;
员工编号 姓名 月薪 佣金 年薪 总收入
----- -------------------------------------------------- --------- --------- ---------- ----------
7369 文斯莫克·山治 3200.00 800.00 38400 39200
7499 罗罗诺亚·索隆 3450.00 300.00 41400 41700
7521 娜美 4560.00 500.00 54720 55220
7566 蒙奇·D·路飞 5450.00 1000.00 65400 66400
7654 妮可·罗宾 5500.00 2400.00 66000 68400
7698 特拉法尔加·D·瓦铁尔·罗 5655.00 1000.00 67860 68860
7782 贝波 2450.00 20.00 29400 29420
7788 乌索普 3000.00 36000 36000
7839 尾田_荣一郎 5000.00 60000 60000
7844 弗兰奇 1500.00 0.00 18000 18000
7876 布鲁克 1100.00 13200 13200
7900 甚平 2000.00 24000 24000
7902 香克斯 6000.00 1500.00 72000 73500
7934 托尼托尼·乔巴 800.68 10.00 9608.16 9618.16
7840 岸本_齐史 5000.00 60000 60000
7709 Jinbe 3550.00 600.00 42600 43200
7699 夏奇 2050.00 200.00 24600 24800
7700 佩金 3000.00 200.00 36000 36200
7937 拉基·路 3600.00 500.00 43200 43700
7938 洛克斯达 2600.00 200.00 31200 31400
7783 强巴鲁 3600.00 200.00 43200 43400
7784 伊卡库 3000.00 200.00 36000 36200
7785 库里奥尼 3500.00 200.00 42000 42200
7935 本·贝克曼 5500.00 66000 66000
7936 耶稣布 5500.00 500.00 66000 66500
6100 马歇尔·D·帝奇 5675.00 68100 68100
6101 芝沙斯·巴沙斯 4321.00 5.00 51852 51857
6102 阿巴罗·皮萨罗 2540.00 30480 30480
6103 范·奥卡 3450.00 41400 41400
1000 Monkey·D·Luffy 5500.00 66000 66000
1001 Trafalgar·D·Water·Law 5800.00 69600 69600
1002 Marshall·D·Teach 7800.00 93600 93600
1003 Portgas·D·Ace 5500.00 66000 66000
1004 Hagwar·D·Sauro 3450.00 41400 41400
1005 Gol·D·Roger 7500.00 90000 90000
1006 Monkey·D·Dragon 6550.00 78600 78600
1007 Monkey·D·Garp 7890.00 94680 94680
7701 Roronoa Zoro 2000.00 800.00 24000 24800
7702 Nami 3000.00 1000.00 36000 37000
7703 Usopp 2500.00 500.00 30000 30500
7704 Sanji 2800.00 1200.00 33600 34800
7705 Tony Tony Chopper 100.00 300.00 1200 1500
7706 Nico Robin 5000.00 800.00 60000 60800
7707 Franky 2000.00 500.00 24000 24500
7708 Brook 3000.00 900.00 36000 36900
45 rows selected
2.6 去重查询
使用 distinct 关键字：select distinct 列 from 表;
- 查询员工表不同部门的编号
SQL> select distinct deptno from emp;
DEPTNO
------
30
20
40
50
10
60
6 rows selected
- 查看不同部门的不同工种
SQL> select distinct deptno,job from emp;
DEPTNO JOB
------ --------------------
20 考古学家
30 航海士
20 厨师
20 航海士
60 海贼王
40 干部
40 副船长
40 狙击手
60 海军中将
20 船长
60 革命军首领
20 狙击手
20 船工
40 船长
50 提督
50 狙击手
20 医生
30 船员
60 队长
30 船长
20 音乐家
20 舵手
40 船员
50 船长
60 船长
20 剑士
10 漫画家
60 提督
28 rows selected
由以上两条查询可以看出，distinct 关键字写在select后面，作用于后面全部的列
3. where 条件查询
SELECT *|{[DISTINCT] column |expression [alias],...}
FROM 表名 [WHERE expression]
3.1 使用比较运算符
可以使用的比较运算符: > , < , = , >= , <= , != , <> , between and
- 查询部门ID为20的员工
SQL> select ename,deptno from emp where deptno=20;
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
罗罗诺亚·索隆 20
娜美 20
蒙奇·D·路飞 20
妮可·罗宾 20
乌索普 20
弗兰奇 20
布鲁克 20
甚平 20
托尼托尼·乔巴 20
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
19 rows selected
- 查询员工名字为 Nico Robin 的员工信息
注意：如果字段的值是字符串的话，描述的时候要加上 ’ ’ 两个单引号
SQL> select ename,deptno from emp where ename='Nico Robin';
ENAME DEPTNO
-------------------------------------------------- ------
Nico Robin 20
- 查询薪水不等于5500员工的信息
SQL> select ename,sal from emp where sal!=5500;
SQL> select ename,sal from emp where sal<>5500;
ENAME SAL
-------------------------------------------------- ---------
文斯莫克·山治 3200.00
罗罗诺亚·索隆 3450.00
娜美 4560.00
蒙奇·D·路飞 5450.00
特拉法尔加·D·瓦铁尔·罗 5655.00
贝波 2450.00
乌索普 3000.00
尾田_荣一郎 5000.00
弗兰奇 1500.00
布鲁克 1100.00
甚平 2000.00
香克斯 6000.00
托尼托尼·乔巴 800.68
岸本_齐史 5000.00
Jinbe 3550.00
夏奇 2050.00
佩金 3000.00
拉基·路 3600.00
洛克斯达 2600.00
强巴鲁 3600.00
伊卡库 3000.00
库里奥尼 3500.00
马歇尔·D·帝奇 5675.00
芝沙斯·巴沙斯 4321.00
阿巴罗·皮萨罗 2540.00
范·奥卡 3450.00
Trafalgar·D·Water·Law 5800.00
Marshall·D·Teach 7800.00
Hagwar·D·Sauro 3450.00
Gol·D·Roger 7500.00
Monkey·D·Dragon 6550.00
Monkey·D·Garp 7890.00
Roronoa Zoro 2000.00
Nami 3000.00
Usopp 2500.00
Sanji 2800.00
Tony Tony Chopper 100.00
Nico Robin 5000.00
Franky 2000.00
Brook 3000.00
40 rows selected
- 查询工资介于 1000 - 2000 之间的员工的信息
SQL> select ename,sal from emp where sal between 1000 and 2000;
ENAME SAL
-------------------------------------------------- ---------
弗兰奇 1500.00
布鲁克 1100.00
甚平 2000.00
Roronoa Zoro 2000.00
Franky 2000.00
3.2 使用逻辑运算符
逻辑运算符包括:
或: or
与: and
非: not
- 查询部门ID为10, 或者部门ID为20 的员工信息
SQL> select ename,deptno from emp where deptno=10 or deptno=20;
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
罗罗诺亚·索隆 20
娜美 20
蒙奇·D·路飞 20
妮可·罗宾 20
乌索普 20
尾田_荣一郎 10
弗兰奇 20
布鲁克 20
甚平 20
托尼托尼·乔巴 20
岸本_齐史 10
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
21 rows selected
- 查询部门ID为 20, 并且工资为 5500 的员工信息
SQL> select ename,sal,deptno from emp where deptno=20 and sal=5500;
ENAME SAL DEPTNO
-------------------------------------------------- --------- ------
妮可·罗宾 5500.00 20
- 查询1981年2月（含2月）到82年2月（不含2月）入职的员工信息
-- 需要使用日期转换函数 to_date() ，可以将字符串类型转换为日期，然后通过日期的比较来进行判断
to_char('1981/2/1', 'yyyy-mm-dd')，to_char('1982/1/31','yyyy-mm-dd')
SQL> select ename, hiredate from emp where hiredate>=to_date('1981/2/1', 'yyyy-mm-dd') and hiredate<=to_date('1982/1/31','yyyy-mm-dd');
ENAME HIREDATE
-------------------------------------------------- -----------
罗罗诺亚·索隆 1981/2/20
娜美 1981/2/22
蒙奇·D·路飞 1981/4/2
妮可·罗宾 1981/9/28
特拉法尔加·D·瓦铁尔·罗 1981/5/1
贝波 1981/6/9
尾田_荣一郎 1981/11/17
弗兰奇 1981/9/8
甚平 1981/12/3
香克斯 1981/12/3
托尼托尼·乔巴 1982/1/23
岸本_齐史 1981/11/17
夏奇 1981/5/1
佩金 1981/5/1
强巴鲁 1981/5/1
伊卡库 1981/5/1
库里奥尼 1981/5/1
17 rows selected
-- 数据库中日期和字符直接是可以自动转换的
-- 要求日期格式和字符串格式能匹配上就可以进行比较, 如果格式对不上就不能比较
-- 数据库中日期的默认存储形式: NLS_DATE_FORMAT DD-MON-RR
-- 不推荐使用这种方式, 适用性不强
select * from emp where hiredate>'01-2月-81' and hiredate<='31-1月-82';
- 查询佣金 为空 的员工的信息 - null
查询佣金 不为空 的员工的信息 - null
-- 如何判断空字段
-- 使用 is null 关键字，判断不为空要使用 is not null 关键字
--如果使用 =null 或者 !=null ，这两个判断得到的结果都是false，则不能进行正确的判断
SQL> select ename, comm from emp where comm is null;
ENAME COMM
-------------------------------------------------- ---------
乌索普
尾田_荣一郎
布鲁克
甚平
岸本_齐史
本·贝克曼
马歇尔·D·帝奇
阿巴罗·皮萨罗
范·奥卡
Monkey·D·Luffy
Trafalgar·D·Water·Law
Marshall·D·Teach
Portgas·D·Ace
Hagwar·D·Sauro
Gol·D·Roger
Monkey·D·Dragon
Monkey·D·Garp
17 rows selected
SQL> select ename, comm from emp where comm is not null;
ENAME COMM
-------------------------------------------------- ---------
文斯莫克·山治 800.00
罗罗诺亚·索隆 300.00
娜美 500.00
蒙奇·D·路飞 1000.00
妮可·罗宾 2400.00
特拉法尔加·D·瓦铁尔·罗 1000.00
贝波 20.00
弗兰奇 0.00
香克斯 1500.00
托尼托尼·乔巴 10.00
Jinbe 600.00
夏奇 200.00
佩金 200.00
拉基·路 500.00
洛克斯达 200.00
强巴鲁 200.00
伊卡库 200.00
库里奥尼 200.00
耶稣布 500.00
芝沙斯·巴沙斯 5.00
Roronoa Zoro 800.00
Nami 1000.00
Usopp 500.00
Sanji 1200.00
Tony Tony Chopper 300.00
Nico Robin 800.00
Franky 500.00
Brook 900.00
28 rows selected
- 查询部门编号为 20 或者部门编号为 30 并且工资为 3000 的员工信息.
-- 逻辑比较符使用的时候要注意优先级：
-- not > and > or
-- 如果要修改优先级，需要加上()括号
SQL> select deptno, sal from emp where (deptno=20 or deptno=30) and sal=3000;
DEPTNO SAL
------ ---------
20 3000.00
30 3000.00
30 3000.00
20 3000.00
20 3000.00
3.3 使用in集合
集合的表示：in (元素, 元素, 元素)
语法格式: in() -> 表示要判断的数据在集合里边, 只有集合中有一个元素满足判断条件即可，in集合相当于or
- 查询部门ID为10, 或者部门ID为20的员工的信息
-- 如果使用逻辑运算符
SQL> select ename, deptno from emp where deptno=10 or deptno=20;
-- 使用in集合
SQL> select ename, deptno from emp where deptno in(10,20);
ENAME DEPTNO
-------------------------------------------------- ------
文斯莫克·山治 20
罗罗诺亚·索隆 20
娜美 20
蒙奇·D·路飞 20
妮可·罗宾 20
乌索普 20
尾田_荣一郎 10
弗兰奇 20
布鲁克 20
甚平 20
托尼托尼·乔巴 20
岸本_齐史 10
Jinbe 20
Roronoa Zoro 20
Nami 20
Usopp 20
Sanji 20
Tony Tony Chopper 20
Nico Robin 20
Franky 20
Brook 20
21 rows selected
-- in集合可不可以出现null，比如：in(10,20,null)
-- in集合相当于是or操作，而与null的判断恒为false，所以不会影响结果的产生，如果误写上去不会造成查询的影响
SQL> select ename, deptno from emp where deptno in(10,20,null);
- 查询部门ID不为(10 和 部门ID20)的员工的信息
-- not in 表示不在集合中，查询以上信息可以使用not in
-- not in 是相当于 and 比较，不等于元素1 and 不等于元素2 。。。
-- 使用逻辑运算符进行查询
SQL> select ename, deptno from emp where deptno!=10 and deptno!=20;
-- 使用not in集合
SQL> select ename, deptno from emp where deptno not in(10,20);
ENAME DEPTNO
-------------------------------------------------- ------
特拉法尔加·D·瓦铁尔·罗 30
贝波 30
香克斯 40
夏奇 30
佩金 30
拉基·路 40
洛克斯达 40
强巴鲁 30
伊卡库 30
库里奥尼 30
本·贝克曼 40
耶稣布 40
马歇尔·D·帝奇 50
芝沙斯·巴沙斯 50
阿巴罗·皮萨罗 50
范·奥卡 50
Monkey·D·Luffy 60
Trafalgar·D·Water·Law 60
Marshall·D·Teach 60
Portgas·D·Ace 60
Hagwar·D·Sauro 60
Gol·D·Roger 60
Monkey·D·Dragon 60
Monkey·D·Garp 60
24 rows selected
-- not in中是否可以加null？ 不可以！
-- null与值的判断得到的结果是false，而not in做的是and的逻辑判断，出现一个false，则无法查询到任何的记录
-- 结论：如果在not in 集合中出现了null，查询结果为空
SQL> select ename, deptno from emp where deptno not in(10,20,null);
ENAME DEPTNO
-------------------------------------------------- ------
3.4 like 模糊查询
在sql查询中, like 关键字出现在where 语句中字段名的后边, 字段值是前边
模糊查找中需要使用通配符, 常用的有两个:
- % - 匹配任意多个字符
- 0个或者1个或者多个
- _ - 匹配一个字符
- 查询员工名字中有D的员工信息
SQL> select ename from emp where ename like '%D%';
ENAME
--------------------------------------------------
蒙奇·D·路飞
特拉法尔加·D·瓦铁尔·罗
马歇尔·D·帝奇
Monkey·D·Luffy
Trafalgar·D·Water·Law
Marshall·D·Teach
Portgas·D·Ace
Hagwar·D·Sauro
Gol·D·Roger
Monkey·D·Dragon
Monkey·D·Garp
11 rows selected
- 查询员工编号为79开头的员工信息
SQL> select empno from emp where empno like '79%';
EMPNO
-----
7900
7902
7934
7935
7936
7937
7938
7 rows selected
- 查询名字是四个字母的员工信息
SQL> select ename from emp where ename like '____';
ENAME
--------------------------------------------------
拉基·路
洛克斯达
库里奥尼
范·奥卡
Nami
- 查询姓名中带 _ 的员工信息
-- 模糊查询中需要查询的普通字符串中有一个 _ ，那么需要做的就是对 _ 进行转义
-- 使用 escape 关键字进行转义，sql中没有一个固定的转义字符，所以需要自定义一个转义字符，使用escape
SQL> select ename from emp where ename like '%*_%' escape '*';
ENAME
--------------------------------------------------
尾田_荣一郎
岸本_齐史
--其中 escape 指定了 * 为转义字符，那么跟在它后面的 _ 被转义成了普通的字符
4. 排序
-- 排序的关键字：order by
-- order by 有两种排序的方式：asc升序和desc降序，默认是asc升序，默认可不写
select 列名|表达式 from 表名 where 条件 order by 列名|别名|表达式|number(序号) asc|desc;
4.1 使用列名排序
- 员工信息按照入职先后排序
-- 升序
SQL> select ename,hiredate from emp order by hiredate;
ENAME HIREDATE
-------------------------------------------------- -----------
文斯莫克·山治 1980/12/17
Brook 1980/12/17
Franky 1980/12/17
Nico Robin 1980/12/17
Roronoa Zoro 1980/12/17
Sanji 1980/12/17
- 员工薪水从高到低排序
SQL> select ename, sal from emp order by sal desc;
ENAME SAL
-------------------------------------------------- ---------
Monkey·D·Garp 7890.00
Marshall·D·Teach 7800.00
Gol·D·Roger 7500.00
Monkey·D·Dragon 6550.00
香克斯 6000.00
Trafalgar·D·Water·Law 5800.00
马歇尔·D·帝奇 5675.00
- 查询员工信息按照 佣金 逆序
SQL> select ename, comm from emp order by comm desc;
-- 当使用该语句时会发现一个问题，所有的comm为null的记录全部显示在最上面
-- 那么也就是null在排序的时候当作最大值来排序
-- 将值为 null 的记录显示在最下面，需要添加关键字：nulls last
SQL> select ename, comm from emp order by comm desc nulls last;
ENAME COMM
-------------------------------------------------- ---------
妮可·罗宾 2400.00
香克斯 1500.00
Sanji 1200.00
蒙奇·D·路飞 1000.00
特拉法尔加·D·瓦铁尔·罗 1000.00
Nami 1000.00
Brook 900.00
文斯莫克·山治 800.00
- 员工信息按照部门升序, 按照薪水降序排列
-- 当有多个嵌套排序时，在前一个排序规则后面接着写下一个排序规则即可
-- 先升序排序deptno ，当deptno相等的时候，再降序排序sal
SQL> select ename, deptno, sal from emp order by deptno, sal desc;
ENAME DEPTNO SAL
-------------------------------------------------- ------ ---------
岸本_齐史 10 5000.00
尾田_荣一郎 10 5000.00
妮可·罗宾 20 5500.00
蒙奇·D·路飞 20 5450.00
Nico Robin 20 5000.00
娜美 20 4560.00
Jinbe 20 3550.00
罗罗诺亚·索隆 20 3450.00
文斯莫克·山治 20 3200.00
Brook 20 3000.00
乌索普 20 3000.00
Nami 20 3000.00
Sanji 20 2800.00
Usopp 20 2500.00
甚平 20 2000.00
Franky 20 2000.00
Roronoa Zoro 20 2000.00
弗兰奇 20 1500.00
布鲁克 20 1100.00
托尼托尼·乔巴 20 800.68
Tony Tony Chopper 20 100.00
特拉法尔加·D·瓦铁尔·罗 30 5655.00
强巴鲁 30 3600.00
库里奥尼 30 3500.00
佩金 30 3000.00
伊卡库 30 3000.00
贝波 30 2450.00
夏奇 30 2050.00
香克斯 40 6000.00
耶稣布 40 5500.00
本·贝克曼 40 5500.00
拉基·路 40 3600.00
洛克斯达 40 2600.00
马歇尔·D·帝奇 50 5675.00
芝沙斯·巴沙斯 50 4321.00
范·奥卡 50 3450.00
阿巴罗·皮萨罗 50 2540.00
Monkey·D·Garp 60 7890.00
Marshall·D·Teach 60 7800.00
Gol·D·Roger 60 7500.00
Monkey·D·Dragon 60 6550.00
Trafalgar·D·Water·Law 60 5800.00
Portgas·D·Ace 60 5500.00
Monkey·D·Luffy 60 5500.00
Hagwar·D·Sauro 60 3450.00
45 rows selected
4.2 使用序号排序
-- 序号指的是在查询字段在结果集中的位置，位置编号从1号开始
-- 假如有：select a,b,c,d ，那么a就是1号，b就是2号，c就是3号，d就是4号
-- order by 指定编号 即可实现排序
查询员工编号, 员工姓名和工资, 按照 工资的序号 进行排序
SQL> select empno, ename, sal from emp order by 3 desc;
EMPNO ENAME SAL
----- -------------------------------------------------- ---------
1007 Monkey·D·Garp 7890.00
1002 Marshall·D·Teach 7800.00
1005 Gol·D·Roger 7500.00
1006 Monkey·D·Dragon 6550.00
7902 香克斯 6000.00
1001 Trafalgar·D·Water·Law 5800.00
6100 马歇尔·D·帝奇 5675.00
7698 特拉法尔加·D·瓦铁尔·罗 5655.00
-- sql语句中的 3 号代表sal
4.3 使用别名排序
-- 别名: 就是数据库表中给某个字段设置的别名.
按照员工的年收入进行排序
SQL> select ename "姓名", sal*12+nvl(comm,0) "年收入" from emp order by "年收入" desc;
姓名 年收入
-------------------------------------------------- ----------
Monkey·D·Garp 94680
Marshall·D·Teach 93600
Gol·D·Roger 90000
Monkey·D·Dragon 78600
香克斯 73500
Trafalgar·D·Water·Law 69600
特拉法尔加·D·瓦铁尔·罗 68860
妮可·罗宾 68400
马歇尔·D·帝奇 68100
耶稣布 66500
-- 直接在order by 后面指定前面设定好的别名即可
4.4 使用表达式排序
按照员工的年收入进行排序
SQL> select ename "姓名", sal*12+nvl(comm,0) "年收入" from emp order by sal*12+nvl(comm,0) desc;
姓名 年收入
-------------------------------------------------- ----------
Monkey·D·Garp 94680
Marshall·D·Teach 93600
Gol·D·Roger 90000
Monkey·D·Dragon 78600
香克斯 73500
Trafalgar·D·Water·Law 69600
特拉法尔加·D·瓦铁尔·罗 68860
妮可·罗宾 68400
马歇尔·D·帝奇 68100
耶稣布 66500
--直接在 order by 后面指定表达式接即可
5. 单行函数
任何东西，只要它
能接收输入，对输入进行加工并产生输出，它就可以被称为函数。
单行函数只对表中的一行数据进行操作，并且对每一行数据只产生一个输出结果。单行函数可以接受一个或多个参数，其产生的输出结果的数据类型可能与参数的数据类型不同。单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数
5.1 字符串相关函数
-
LOWER、UPPER、INITCAP
-- LOWER(列名|表达式)： 该函数是把字符转换成小写。 -- UPPER(列名|表达式)： 该函数是把字符转换成大写。 -- INITCAP(列名|表达式)： 该函数是把每个字的头一个字符转换成大写，其余的转换成小写。 -- dual是一个虚表, 没有数据, 主要作用是和select配合使用, 组成一个完整的sql语句 -- 虚表是一个没有任何数据的表, 在select的时候使用dual目的是将这个sql语句补充完整, 但是这个dual在这个查询语句中起不到任何作用 -- lower() 字母转换为小写 SQL> select lower(ename) from emp; SQL> select lower('Hello WORLD') from dual; LOWER('HELLOWORLD') ------------------- hello world -- upper() 字母转换为大写 SQL> select upper(ename) from emp; SQL> select upper('Hello World') from dual; UPPER('HELLOWORLD') ------------------- HELLO WORLD -- initcap(), 单词首字母转换为大写 SQL> select initcap(ename) from emp; SQL> select initcap('hello world') from dual; INITCAP('HELLOWORLD') --------------------- Hello World -
CONCAT
-- 有两个参数,连接两个字符串 -- CONCAT(列名|表达式，列名|表达式)：该函数是把头一个字符串和第二个字符串连接成一个字符串。 -- 注意事项: -- 该函数只能连接两个字符串, 如果需要连接多个, 需要使用 || -- 使用 函数 concat() 连接两个字符串 SQL> select concat (empno, ename) from emp; SQL> select concat('hello,', ' world') from dual; CONCAT('HELLO,','WORLD') ------------------------ hello, world -- 使用 || 实现多个字符串的连接 SQL> select empno || '-' || ename || sal from emp; SQL> select 'hello world' || 'abcdefg' || 'xxx' from dual; 'HELLOWORLD'||'ABCDEFG'||'XXX' ------------------------------ hello worldabcdefgxxx -
SUBSTR
-- SUBSTR(列名|表达式，pos,[len])：字符串截取, 返回的字符串是从第pos个字符开始，其长度为len。 SQL> select substr(ename, 1, 5) from emp; -- 从第8个字符开始, 截取长度为5的子字符串 SQL> select substr('hello, world', 8, 5) from dual; SUBSTR('HELLO,WORLD',8,5) ------------------------- world -
LENGTH、LENGTHB
-- LENGTH(列名|表达式)： 该函数是返回列中或表达式中 字符串 的长度。 -- LENGTHB(列名|表达式)：该函数是返回列中或表达式中字符串的 字节 的长度。 -- 查询字符串长度 SQL> select dname, length(dname) from dept; DNAME LENGTH(DNAME) -------------------------------------------------- ------------- 黑胡子海贼团 6 黑桃海贼团 5 D之一族 4 周刊少年Jump 8 草帽海贼团 5 红心海贼团 5 红发海贼团 5 7 rows selected -- 查询字节数 SQL> select dname, lengthb(dname) from dept; DNAME LENGTHB(DNAME) -------------------------------------------------- -------------- 黑胡子海贼团 18 黑桃海贼团 15 D之一族 10 周刊少年Jump 16 草帽海贼团 15 红心海贼团 15 红发海贼团 15 7 rows selected -- 判断字符串中有没有中文 SQL> select ename from emp where length(ename)=lengthb(ename); ENAME -------------------------------------------------- Jinbe Roronoa Zoro Nami Usopp Sanji Tony Tony Chopper Nico Robin Franky Brook 9 rows selected -
INSTR
-- INSTR(列名|表达式，'子字符串'，[m],[n])：该函数是返回所给子字符串出现的位置，没有返回0 -- 参数: m表示从第m个字符开始搜索，n表示所给字符串出现的次数，它们的默认值都为1。 SQL> select ename, instr(ename, 'D', 2, 2) from emp; ENAME INSTR(ENAME,'D',2,2) Portgas·D·Ace 0 Hagwar·D·Sauro 0 Gol·D·Roger 0 Monkey·D·Dragon 10 Monkey·D·Garp 0 Roronoa Zoro 0 -
TRIM
-- TRIM([leading|trailing|both] '要去掉的字符' FROM '源字符串')：去掉字符串两端的指定字符 -- TRIM('要去掉的字符' FROM '源字符串'): 默认是both 去掉两端的指定字符 -- 去空格(前后都去掉) SQL> select trim(' hello, world ') || 'end' from dual; TRIM('HELLO,WORLD')||'END' -------------------------- hello, worldend -- 其他字符 -- 去掉头部的 * SQL> select trim(leading '*' from '*******hello, world*******') || 'end' from dual; TRIM(LEADING'*'FROM'*******HELLO,WORLD*******')||'END' ------------------------------------------------------ hello, world*******end -- 去掉尾部的 * SQL> select trim(trailing '*' from '*******hello, world*******') || 'end' from dual; TRIM(TRAILING'*'FROM'*******HELLO,WORLD*******')||'END' ------------------------------------------------------- *******hello, worldend -- 头部和尾部的 * 都去掉 SQL> select trim(both '*' from '*******hello, world*******') || 'end' from dual; TRIM(BOTH'*'FROM'*******HELLO,WORLD*******')||'END' --------------------------------------------------- hello, worldend -
REPLACE
-- REPLACE(正文表达式，old，new) -- 该函数是在"正文表达式"中查找"old"，如果找到了就用"new"替代。 -- 注意：该函数会替换所有正文表达式中包含的old为new SQL> select replace('aaaaccbb', 'cc', 'xx') from dual; REPLACE('AAAACCBB','CC','XX') ----------------------------- aaaaxxbb -
LPAD、RPAD
-- LPAD(列名|表达式, len, ch): 返回len长度的字符串, 如果不够len, 在字符串左侧填充ch -- RPAD(列名|表达式, len, ch): 返回len长度的字符串, 如果不够len, 在字符串右侧填充ch -- 使用这两个函数也可以用来做截断，即指定比当前字符串len小的一个值 SQL> select lpad('aaaaccbb', 12, '*') from dual; -- 在左侧填充*, 最终得到12个字节 LPAD('AAAACCBB',12,'*') ----------------------- ****aaaaccbb -- 在左侧填充*, 最终得到6个字节, 如果字符串长度>=6字节, 不需要填充并且删除多余的部分 SQL> select lpad('aaaaccbb', 6, '*') from dual; LPAD('AAAACCBB',6,'*') ---------------------- aaaacc -- 在右侧填充*, 最终得到16个字节 SQL> select rpad('aaaaccbb', 16, '*') from dual; RPAD('AAAACCBB',16,'*') ----------------------- aaaaccbb********
5.2 数值函数
-
ROUND
-- ROUND(列名|表达式，[n])：该函数将列名或表达式所表示的数值四舍五入到小数点后的n位。 SQL> select round(12.346, 2) from dual; ROUND(12.346,2) --------------- 12.35 SQL> select round(12.346) from dual; ROUND(12.346) ------------- 12 SQL> select round(12.546) from dual; ROUND(12.546) ------------- 13 -
TRUNC
-- TRUNC(列名|表达式，[n])：该函数将列名或表达式所表示的数值截取到小数点后的n位。 SQL> select trunc(12.546) from dual; TRUNC(12.546) ------------- 12 SQL> select trunc(12.546, 2) from dual; TRUNC(12.546,2) --------------- 12.54 -
MOD
-- MOD(m,n)：该函数将m除以n并取余数。 SQL> select mod(5, 3) from dual; MOD(5,3) ---------- 2 -
CEIL、FLOOR
-- CEIL(m): 向上取整 -- FLOOR(m)：向下取整 SQL> select ceil(12.6) from dual; CEIL(12.6) ---------- 13 SQL> select ceil(12.1) from dual; CEIL(12.1) ---------- 13 SQL> select floor(12.1) from dual; FLOOR(12.1) ----------- 12 SQL> select floor(12.9) from dual; FLOOR(12.9) ----------- 12
5.3 转换函数
-
TO_CHAR
-
日期转换
-- TO_CHAR(日期，‘fmt')：将日期型数据转换成指定模式(fmt)的字符串 SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS') --------------------------------------- 2019-11-28 17:20:09 SQL> select to_char(sysdate, 'yyyy/mm/dd -- hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YYYY/MM/DD--HH24:MI:SS') ----------------------------------------- 2019/11/28 -- 17:20:22常用的日期模式 (不区分大小写):
日期模式 解释 YYYY 完整的年份数字表示(如2001) YEAR 年份的英文表示(如NINETEEN EIGHTY-SEVEN) MM 用两位数字来表示月份 MONTH/MON 月份中文名 DAY/DY 表示星期几 DD 使用两位数字表示日期 YY/RR 使用两位数字表示年份 HH/HH24 HH24表示24小时计时法的时间, hh需要在时间尾部加 am MI 使用两位数表示分钟 SS 使用两位数表示秒数 # MONTH/MON: 如果月份是用英文表示的, month表示完整的英文月份, mon表示英文月份的缩写 # DAY/DY: 如果月份是用英文表示的, DAY表示星期几的完整的英文, DY用3个英文字符的缩写来表示星期几 -
数字转换
-- TO_CHAR(数字，‘fmt')：该函数的这种格式把数字型数据转换成变长字符串。 SQL> select ename, to_char(sal, 'L9,999.99') from emp; ENAME TO_CHAR(SAL,'L9,999.99') 文斯莫克·山治 ￥3,200.00 罗罗诺亚·索隆 ￥3,450.00 娜美 ￥4,560.00 蒙奇·D·路飞 ￥5,450.00 妮可·罗宾 ￥5,500.00 特拉法尔加·D·瓦铁尔·罗 ￥5,655.00-
常用的数字模式
数字模式 解释 9 表示数字 0 表示数字 $ 显示美元符号 L 显示本地货币符号 . 显示小数点 , 显示千位符号, 如: 9,999 MI 在数的右边显示减号 PR 把负数用尖括号扩起来
-
-
-
TO_NUMBER
-- TO_NUMBER(字符串 ,'fmt‘)：该函数把字符串转换成数字。 SQL> select to_number('￥3,090.73', 'L9,999.99') from dual; TO_NUMBER('￥3,090.73','L9,999.99') ---------------------------------- 3090.73 -
TO_DATE
-- TO_DATE(字符串 ,'fmt’)：该函数把字符串转换成日期型数据。 SQL> select to_date('1970/01/01 13:24:34', 'yyyy/mm/dd hh24:mi:ss') from dual; TO_DATE('1970/01/0113:24:34','YYYY/MM/DDHH24:MI:SS') ---------------------------------------------------- 1970/1/1 13:24:34 -
隐式转换
-
将变长字符型(VARCHAR2)或定长字符型(CHAR)转换成数字型(NUMBER)；
-
将变长字符型(VARCHAR2)或定长字符型(CHAR)转换成日期型(DATE)；
-
将数字型(NUMBER)转换成变长字符型(VARCHAR2)；
-
将日期型(DATE)转换成变长字符型(VARCHAR2)。
-
5.4 日期函数
-
SYSDATE
SYSDATE是我们在Oracle开发中经常要用到的一种单行函数(single row function), 该函数用以返回当前的日期与时间，常和DUAL伪表一起合作。
SYSDATE实际上指的是SYSDATE()函数，但是因为这个函数没有参量，所以这里可以省略()圆括号。通常用于获取数据库所在的操作系统的当前时间值的, 我们可以使用 TO_CHAR 函数来获得我们想要的SYSDATE日期格式 。
注意：sysdate函数不要写括号，否则会出错
-
MONTHS_BETWEEN：
-- MONTHS_BETWEEN(日期1，日期2)：该函数是返回日期1和日期2之间的月数, 返回值=日期1-日期2 SQL> select months_between('11-1月-19', '9-10月-18') from dual; MONTHS_BETWEEN('11-1月-19','9-10月-18') ------------------------------------- 3.06451612903226 SQL> select months_between('11-1月-19', '9-10月-19') from dual; MONTHS_BETWEEN('11-1月-19','9-10月-19') ------------------------------------- -8.93548387096774 -
ADD_MONTHS：
-- ADD_MONTHS(日期，n)：该函数是把n个月加到日期上。 SQL> select add_months(sysdate, 10) from dual; ADD_MONTHS(SYSDATE,10) ---------------------- 2020/9/29 9:07:44 -
NEXT_DAY
-- NEXT_DAY(日期，'星期几'|1-7)：该函数返回指定日期的下一个星期几是多少号。 SQL> select next_day(sysdate, '星期五') from dual; -- 星期一, 星期二, ..... NEXT_DAY(SYSDATE,'星期五') ----------------------- 2019/12/6 9:09:39 -- 1: 星期天, 2: 星期一, 3:星期二.... SQL> select next_day(sysdate, 5) from dual; NEXT_DAY(SYSDATE,5) ------------------- 2019/12/5 9:10:24 -
LAST_DAY
-- LAST_DAY(日期)：该函数是返回该日期所在月的最后一天。 SQL> select last_day('1-2月-19') from dual; LAST_DAY('1-2月-19') ------------------- 2019/2/28 SQL> select last_day('1-2月-00') from dual; LAST_DAY('1-2月-00') ------------------- 2000/2/29 -
ROUND和TRUNC函数用于日期型数据
-- ROUND(日期，日期模式): 对指定日期四舍五入
-- TRUNC(日期，日期模式): 对指定日期截断
SQL> select hiredate, round(hiredate, 'mm') from emp;
HIREDATE ROUND(HIREDATE,'MM')
----------- --------------------
1980/12/17 1981/1/1
1981/2/20 1981/3/1
SQL> select hiredate, round(hiredate, 'yyyy') from emp;
HIREDATE ROUND(HIREDATE,'YYYY')
----------- ----------------------
1980/12/17 1981/1/1
1981/2/20 1981/1/1
-- TRUNC(日期，日期模式): 对指定日期截断
SQL> select hiredate, trunc(hiredate, 'mm') from emp;
HIREDATE TRUNC(HIREDATE,'MM')
----------- --------------------
1980/12/17 1980/12/1
1981/2/20 1981/2/1
SQL> select hiredate, trunc(hiredate, 'yyyy') from emp;
HIREDATE TRUNC(HIREDATE,'YYYY')
----------- ----------------------
1980/12/17 1980/1/1
1981/2/20 1981/1/1
1981/2/22 1981/1/1
5.5 通用函数
-
NVL
-- NVL(expr1, expr2): -- 如果oracle第一个参数为空那么显示第二个参数的值， -- 如果第一个参数的值不为空，则显示第一个参数本来的值 SQL> select ename,NVL(comm, -1) from emp; -
NVL2
-- NVL2(expr1, expr2, expr3) -- 如果该函数的第一个参数为空那么显示第三个参数的值 -- 如果第一个参数的值不为空，则显示第二个参数的值 SQL> select ename, comm, nvl(comm,0), nvl2(comm, 0, 1) from emp;
ENAME COMM NVL(COMM,0) NVL2(COMM,0,1)
文斯莫克·山治 800.00 800 0
罗罗诺亚·索隆 300.00 300 0
娜美 500.00 500 0
蒙奇·D·路飞 1000.00 1000 0
妮可·罗宾 2400.00 2400 0
特拉法尔加·D·瓦铁尔·罗 1000.00 1000 0
贝波 20.00 20 0
乌索普 0 1
尾田_荣一郎 0 1
弗兰奇 0.00 0 0
布鲁克 0 1
甚平 0 1
香克斯 1500.00 1500 0
托尼托尼·乔巴 10.00 10 0
岸本_齐史 0 1
## 5.6 条件表达式
```sql
sql> select ...,
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
from table;
-- 查询的sql语句语法:
select 列名|表达式 from 表名 [where 条件 order by 类名 asc|desc];
select 列名|带条件的表达式 from 表名;
-- c语言条件判断: if ... else
-- sql条件判断: when ... then ... when ... then ... else (当某个条件满足, 我就去干
什么)
老板打算给员工涨工资, 要求：
船长涨1000，厨师涨800，其他人涨400, 请将涨前，涨后的薪水列出。
ENAME JOB 涨薪前 涨薪后
-------------------------------------------------- -------------------- --------- ----------
文斯莫克·山治 厨师 3200.00 4000
罗罗诺亚·索隆 剑士 3450.00 3850
娜美 航海士 4560.00 4960
蒙奇·D·路飞 船长 5450.00 6450
妮可·罗宾 考古学家 5500.00 5900
特拉法尔加·D·瓦铁尔·罗 船长 5655.00 6655
贝波 航海士 2450.00 2850
乌索普 狙击手 3000.00 3400
尾田_荣一郎 漫画家 5000.00 5400
弗兰奇 船工 1500.00 1900
布鲁克 音乐家 1100.00 1500
甚平 舵手 2000.00 2400
香克斯 船长 6000.00 7000
托尼托尼·乔巴 医生 800.68 1200.68
岸本_齐史 漫画家 5000.00 5400
Jinbe 舵手 3550.00 3950
夏奇 船员 2050.00 2450
佩金 船员 3000.00 3400
拉基·路 干部 3600.00 4000
洛克斯达 船员 2600.00 3000
强巴鲁 船员 3600.00 4000
伊卡库 船员 3000.00 3400
库里奥尼 船员 3500.00 3900
本·贝克曼 副船长 5500.00 5900
耶稣布 狙击手 5500.00 5900
马歇尔·D·帝奇 提督 5675.00 6075
芝沙斯·巴沙斯 船长 4321.00 5321
阿巴罗·皮萨罗 船长 2540.00 3540
范·奥卡 狙击手 3450.00 3850
Monkey·D·Luffy 船长 5500.00 6500
Trafalgar·D·Water·Law 船长 5800.00 6800
Marshall·D·Teach 提督 7800.00 8200
Portgas·D·Ace 队长 5500.00 5900
Hagwar·D·Sauro 海军中将 3450.00 3850
Gol·D·Roger 海贼王 7500.00 7900
Monkey·D·Dragon 革命军首领 6550.00 6950
Monkey·D·Garp 海军中将 7890.00 8290
Roronoa Zoro 剑士 2000.00 2400
Nami 航海士 3000.00 3400
Usopp 狙击手 2500.00 2900
Sanji 厨师 2800.00 3600
Tony Tony Chopper 医生 100.00 500
Nico Robin 考古学家 5000.00 5400
Franky 船工 2000.00 2400
Brook 音乐家 3000.00 3400
45 rows selected
6. 多行函数
和单行函数相比，oracle提供了丰富的基于组的，多行的函数。这些函数能在select或select的having
子句中使用，当用于select子串时常常都和GROUP BY一起使用。多行函数分为接收多个输入，返回一
个输出。
多行函数又称为分组函数，因为尝与group by 搭配分组使用。
6.1 分组函数
多行函数也可以称之为分组函数, 分组函数具有过滤空字段的功能.
-
COUNT(列名)
-- 统计员工总数 -- 只要ename的值不为空，则计算在内，计数+1 SQL> select count(ename) from emp; COUNT(ENAME) ------------ 45 -- count(*) 表示只要表中的记录不为全空，则计算在内 SQL> select count(*) from emp; COUNT(*) ---------- 45 -- 统计工种总数 SQL> select count(job) from emp; COUNT(JOB) ---------- 45 -- 以上的sql语句查询结果中有非常多的重复项，没有做去重的处理 -- 去重使用distinct关键字，放在count()函数的括号里面 SQL> select count(distinct job) from emp; COUNT(DISTINCTJOB) ------------------ 19 -
AVG(列名)
-- 求员工的平均薪资 SQL> select avg(sal) from emp; AVG(SAL) ---------- 3932.03733 -- 求员工的平均佣金 单独使用avg不计算为null的记录 SQL> select avg(comm) from emp; AVG(COMM) ---------- 583.392857 -
SUN(列名)
-- 求员工的平均薪资 SQL> select sum(sal)/count(*) from emp; SUM(SAL)/COUNT(sal ) ----------------- 3932.03733333333 -- 求员工的平均佣金 SQL> select sum(comm)/count(comm) from emp; SUM(COMM)/COUNT(COMM) --------------------- 583.392857142857 -- 如果使用count(*)作为除数，那么计算结果将与以上不同。 -- 原因：count(comm)不统计comm为null的记录。count(*)统计了comm为null的记录，所以除数变大。 SQL> select sum(comm)/count(*) from emp; SUM(COMM)/COUNT(*) ------------------ 363 -
MAX(列名)
-- 求员工的最高工资 SQL> select max(sal) from emp; MAX(SAL) ---------- 7890 -
MIN(列名)
-- 求员工的最低工资 SQL> select min(sal) from emp; MIN(SAL) ---------- 100 -
在使用多行函数进行统计的时候, 如何将空字段也统计在内?
-
使用
nvl(comm,0) -
SQL> select sum(comm)/count(nvl(comm,0)) from emp; SUM(COMM)/COUNT(NVL(COMM,0)) ---------------------------- 363
-
6.2 分组统计
-- 基本格式
-- 按照group by后给定的表达式, 将from后的table进行分组, 针对每一组使用分组函数
-- 如果select后边的列名没有出现在分组函数中, 那么一定出现在group by的后边
select 列名1, 列名2, ..., 分组函数(列名) from 表名 where ... group by 列名1, 列名2 having ...;
结论：select后面写什么 group by后面就写什么，select后面的内容必须时group by后面内容的真子集。该内容不包括分组函数。
- 统计各个部门的平均工资
-- 根据部门进行分组, 然后再计算每个部门的平均薪资
-- 在进行分组统计的时候, 出现在select后边的字段名, 也必须出现在group by的后边,
-- 也就是说要对那些信息进行分组统计, 那么就在结果集中显示哪些信息
SQL> select deptno,avg(sal) from emp group by deptno order by deptno;
DEPTNO AVG(SAL)
------ ----------
10 5000
20 2868.98315
30 3322.14285
40 4640
50 3996.5
60 6248.75
6 rows selected
- 统计各个部门不同工种的平均工资
-- 根据部门和工种进行分组，然后在组内进行平均工资的计算
SQL> select deptno, job, avg(sal) from emp group by deptno,job;
DEPTNO JOB AVG(SAL)
------ -------------------- ----------
20 考古学家 5250
30 航海士 2450
20 厨师 3000
20 航海士 3780
60 海贼王 7500
40 干部 3600
40 副船长 5500
40 狙击手 5500
60 海军中将 5670
20 船长 5450
60 革命军首领 6550
20 狙击手 2750
20 船工 1750
40 船长 6000
50 提督 5675
50 狙击手 3450
20 医生 450.34
30 船员 3030
60 队长 5500
30 船长 5655
20 音乐家 2050
20 舵手 2775
40 船员 2600
50 船长 3430.5
60 船长 5650
20 剑士 2725
10 漫画家 5000
60 提督 7800
28 rows selected
- 统计哪些部门的平均工资高于4000
-- 先分组计算每个部门的平均工资，然后筛选出大于4000的
SQL> select deptno, avg(sal) from emp group by deptno having avg(sal)>4000;
DEPTNO AVG(SAL)
------ ----------
40 4640
10 5000
60 6248.75
- 求20号部门的平均薪水
-- 有两种方法
-- 1. 先分组求出每个部门的平均薪水，然后筛选出部门号为20号的。
SQL> select deptno,avg(sal) from emp group by deptno having deptno=20;
DEPTNO AVG(SAL)
------ ----------
20 2868.98315
-- 2. 先筛选出部门号为20号的，然后使用分组求平均薪资
SQL> select deptno,avg(sal) from emp where deptno=20 group by deptno;
DEPTNO AVG(SAL)
------ ----------
20 2868.98315
-- 有关效率：方法2比方法1高
-- 原因：方法1计算了每个部门平均薪资后筛选出20号部门，方法2先筛选出20号部门后只计算20号部门内的数据，计算量来看方法2较少。
-- 结论：方法2更优
7. 总结oracle基础命令
- 使用sqlplus登陆到服务器：
sqlplus / as sysdba - 启动数据库实例：
SQL> start up - 关闭数据库实例：
SQL> shutdown immediate - 退出sqlplus客户端：
quit - 启动数据库监听器：
lsnrctl start - 关闭数据库监听器：
lsnrctl stop - 锁定用户：
alter user 用户名 account lock; - 解锁用户：
alter user 用户名 account unlock; - 修改用户密码：
alter user 用户名 identified by 新密码; - 查看当前语言环境：
select userenv('language') from dual; - 切换用户：
conn 用户/密码 as 角色
在sql语句中什么时候使用单引号''什么时候使用双引号""？ 字符串类型用单引号，别名用双引号。
658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
