ORACLE数据----查询
ORACLE数据----查询
数据查询命令执行顺序:
5:SELECT 字段
1:FROM 表
2:WHERE 筛选条件
3:GROUP BY 分组字段1,分组字段1......
4:HAVING 筛选条件
6:ORDER BY 排序字段
数据查询语言(DQL):包括基本查询语句,GROUP BY子句 ,ORDER BY子句等
1、全部查询:
SELECT * FROM 表名
SELECT * FROM EMP;
2、WHERE条件查询
SELECT * FROM 表名 WHERE 条件
SELECT * FROM EMP WHERE DEPTNO = 20;
3、操作符
算术运算符,操作运算符,逻辑运算符
(1)、算术运算符:+,-,×,/
SELECT E.*,SAL+1000 FROM EMP E
(2)、操作运算符:=,!=,<>,>,>=,<,<=
查询工资大于2000的所有员工
SELECT * FROM EMP WHERE SAL > 2000
(3)、逻辑运算符:NOT>AND >OR(不是,并且,或)
--查询10号部门的员工,并且工资大于等于3000的员工信息
SELECT * FROM EMP WHERE DEPTNO = 10 AND SAL >3000;
4、NULL操作('',NULL)
-- 查询奖金不为空的所有员工信息
SELECT * FROM EMP WHERE COMM IS NOT NULL;
空值的特性:空值跟任何值进行算术运算得到的都是空值
SELECT E.*,E.SAL + E.COMM FROM EMP E;
空值跟任何值进行关系运算,得到的结果都为不成立
SELECT * FROM EMP WHERE COMM <> 0;
空值不参与任何聚合运算
SELECT COUNT(*),COUNT(COMM),AVG(COMM),MAX(COMM),MIN(COMM),SUM(COMM)/COUNT(1) FROM EMP;
空值排序时永远是最大的
SELECT * FROM EMP ORDER BY COMM;
5、随机函数
随机生成数字:DBMS_RANDOM.VALUE
0~1
SELECT DBMS_RANDOM.VALUE FROM DUAL;
1~2
SELECT DBMS_RANDOM.VALUE(1,2) FROM DUAL;
随机生成字符串:DBMS_RANDOM.STRING(A代表大小写混合;U代表大写;L代表小写)
SELECT DBMS_RANDOM.STRING('A',5) FROM DUAL;
随机生成日期:
日期转数字:TO_CHAR(日期,'J')
SELECT TO_CHAR(SYSDATE,'J') FROM DUAL;
数字转日期:TO_DATE(数字,'J')
SELECT TO_DATE(2459584,'J') FROM DUAL;
6、NVL空值转换函数,NVL(要处理的字段或者值,为空时的转换值)
将奖金为空的值转为0
SELECT ENAME,SAL,NVL(COMM,0) FROM EMP;
7、IN操作
--查询出工作职责是'SALESMAN'或者'PRESIDENT'或者'ANALYST'的员工信息。
SELECT * FROM EMP WHERE JOB IN ('SALESMAN','PRESIDENT','PRESIDENT');
8、BETWEEN .. AND ..(前小后大)
查询列值包含在某个指定区间内,包含边界,闭区间
查询工资大于等于1500且小于等于2000的员工信息
SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 2000;
9、LIKE模糊查询(针对字符串)
字符匹配操作可以使用通配符,'%'和'_'(%代表任意一个或多个字符,_代表任意一个字符)
--查询员工姓名倒数第二位为 E 的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%E_';
10、聚合函数
聚合函数:聚合函数可以对多行数据进行操作,并返回一个结果
聚合函数可以与GROUP BY 使用
注意:SELECT 中除了聚合值之外,只能包含group by 中的分组字段(根据什么进行分组,那么就只能展示什么字段)
AVG 平均值
SUM 求和
MAX、MIN 最大、最小值
COUNT 统计个数
-- 计算员工表的总体平均工资、总工资、总人数、工龄最大和最小的入职日期
SELECT AVG(SAL),SUM(SAL),COUNT(*),MIN(HIREDATE),MAX(HIREDATE) FROM EMP;
--统计各个管理者手下的员工数量,手下员工平均工资低于2000 的不做展示
SELECT MGR,COUNT(1),AVG(SAL) AVG_SAL FROM EMP
GROUP BY MGR
HAVING AVG(SAL) >= 2000
ORDER BY AVG_SAL DESC;
11、排序:默认排序是升序排列ASC,降序排列DESC
--展示emp表中所有员工信息,按照员工的部门进行升序排列,部门内部按照工资从高到低排列(相同工资的情况下,老员工排前面)
SELECT * FROM EMP
ORDER BY DEPTNO,SAL DESC,HIREDATE;
去重DISTINCT:DISTINCT必须放在所有字段之前
SELECT DISTINCT DEPTNO FROM EMP;
去重ROWID:主要用于删除重复数据
DELETE FROM STU_NO WHERE ROWID NOT IN (SELECT MAX(ROWID)
FROM STU_NO
GROUP BY SNO);
COMMIT;
12、集合运算(数据的纵向合并)
交集:INTERSECT
并集:(去重)UNION
并集:(不去重)UNION ALL
补集:MINUS
注意:
(1)查询所返回的列数以及返回类型必须匹配,列名可以不同
(2)只有UNION ALL不会去重。其他三个都需要排序后去重,性能比较差
(3)优化:能用UNION ALL的时候不用UNION
--INTERSECT
SELECT DEPTNO FROM DEPT
INTERSECT
SELECT DEPTNO FROM EMP;
--UNION
SELECT DEPTNO FROM DEPT
UNION
SELECT DEPTNO FROM EMP;
--UNION ALL
SELECT DEPTNO FROM DEPT
UNION ALL
SELECT DEPTNO FROM EMP;
--MINUS
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;
13、子查询
子查询在SELECT、UPDATE、DELETE、INSERT 语句内部都可以出现SELECT语句
(1)、单行子查询:不向外部返回结果或者只返回一行结果
(2)、多行子查询:向外部返回零行、一行或多行结果
<1>、FROM 之后的子查询、是将子查询结果作为一个数据集来使用
<2>、WHERE条件中的子查询
<3>、SELECT之后的子查询
列出所有员工的姓名及其直接上级的姓名
SELECT YG.ENAME,
(SELECT LD.ENAME FROM EMP LD WHERE LD.EMPNO = YG.MGR) LD_NAME
FROM EMP YG ;
列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
14、单行函数
常用的单行函数包括:数字函数、日期函数、字符函数
(1)数字函数:
ABS(X) 绝对值
ROUND(X,Y) 四舍五入
MOD(X,Y) 取余
POWER(X,Y) x的y次幂
CEIL(X) 向上取整
FLOOR(X) 向下取整
TRUNC(X,Y) 截断,X在第Y位进行截断,保留小数点几位,零是各位,负数表示向前移动几位小数
SELECT ABS(-1),ABS(-5) FROM DUAL;
SELECT ROUND(4567.2589),
ROUND(4567.2589,2),
ROUND(4567.2589,-1)
FROM DUAL;
SELECT MOD(50,47) FROM DUAL;
SELECT POWER(5,3) FROM DUAL;
SELECT
CEIL(-3.15), -- -3
CEIL(-3), -- -3
CEIL(3.15), -- 4
CEIL(3), -- 3
FLOOR(-3.15), -- -4
FLOOR(-3), -- -3
FLOOR(3.15), -- 3
FLOOR(3) -- 3
FROM DUAL;
(2)日期函数:
SYSDATE当前系统时间
SELECT SYSDATE FROM DUAL;
日期和日期可以进行相减,但是不能相加
SELECT CEIL(SYSDATE-DATE'2000-01-01') FROM DUAL;
ADD_MONTHS(某个日期,+月)
SELECT ADD_MONTHS(DATE'2021-01-01',12) FROM DUAL;
LAST_DAY(某个日期) : 当前日期所在月的最后一天的日期
SELECT LAST_DAY(SYSDATE) FROM DUAL;
TRUNC配合日期函数使用
TRUNC(d[,fmt]):
fmt: 'DDD' 截取到当月的当天(默认格式)
fmt: 'DD' 截取到当月的当天
fmt: 'D' 截取到当周的第一天
fmt: 'MM' 截取到当月的第一天
fmt: 'Q' 截取到当季度的第一天
fmt: 'Y' 截取到当年的第一天
SELECT TRUNC(SYSDATE), -- 时分秒 00:00:00
TRUNC(SYSDATE,'DDD'),
TRUNC(SYSDATE,'DD'),
TRUNC(SYSDATE,'D'), --当周的第几天(星期天开始,星期六结束)
TRUNC(SYSDATE,'MM'), --当月的第一天
TRUNC(SYSDATE,'Q'), --当季度的第一天
TRUNC(SYSDATE,'Y') --当年的第一天
FROM DUAL;
(3)字符函数:
ASCII(X) 阿克斯码
CONCATE(X) 连接字符,将两个字符串进行拼接,或||
WM_CONCATE() 合并拼接的字段
LENGTH(X) 字符长度
LENGTHB(X) 字节长度
LOWER(X) 转小写
POWER(X) 转大写
REPLACE(X,old,new) 对x进行查找替换,查找old,替换成new
TRANSLATE(X,from,to) 对位替换,对x进行替换,规则就是from和to对位替换
LTRIM(X,y) 从左边截取字符,默认去点空格
RTRIM(X,y) 从右边截取字符,默认去点空格
TRIM
INSTR(X,STR,I,J) 获取字符下标,x字符串中找str的下标,从i位开始查,找第j次出现的下标
SUBSTR(X,I,J) 截取字符串,截取X字符串,从i位开始截取,截取j位
SELECT ASCII('A'),ASCII('a') FROM DUAL;
SELECT CONCAT(CONCAT('BI','20121208'),'BIGDATA') FROM DUAL;
SELECT DEPTNO,WM_CONCAT(ENAME) FROM EMP GROUP BY DEPTNO;
SELECT LENGTH('ABC'),LENGTH('你好'),LENGTHB('ABC'),LENGTHB('你好') FROM DUAL;
SELECT LOWER('aBCde'),UPPER('aBCde') FROM DUAL;
SELECT REPLACE('ABCSDFWQASABACABCD','BC','XXXXX'),TRANSLATE('ABCSDFWQASABACABCD','ABC','12') FROM DUAL;
SELECT LTRIM(' AA A B C A AA '),
RTRIM(' AA A B C A AA '),
LTRIM('AA A B C A AA','A'),
RTRIM('AA A B C A AA','A'),
TRIM(' AA A B C A AA '),
TRIM('A' FROM 'AA A B C A AA')
FROM DUAL;
SELECT INSTR('ABCD#ASFD#RGDC#CJDI#JKKL#FEASD','#',7,2)FROM DUAL; --从第7位开始找,默认找第二个
SELECT SUBSTR('ABCD#ASFD#RGDC#CJDI#JKKL#FEASD',25,2) FROM DUAL;
15、转换函数
TO_CHAR(值/列,格式) 将日期或数字转为字符串
SELECT TO_CHAR(123456789.12,'FM999,999,999,999,999.00') FROM DUAL;
SELECT
TO_CHAR(DATE'2021-12-13','YYYY'),
TO_CHAR(DATE'2021-12-13','MM'),
TO_CHAR(DATE'2021-12-13','DD'),
TO_CHAR(DATE'2021-12-13','YYYYMM'),
TO_CHAR(DATE'2021-12-13','YYYYMMDD'),
TO_CHAR(DATE'2021-01-08','WW'), --02 自然周,从开年第一天开始7天算一周
TO_CHAR(DATE'2021-01-11','IW'), --02 按照完整的周一到周天为一周进行计算
TO_CHAR(DATE'2021-01-11','D'), --当周的第几天 从星期天开始为 第一天
TO_CHAR(DATE'2021-01-11','DAY')
FROM DUAL;
TO_NUMBER(X) 将字符串转为数字
SELECT TO_NUMBER(' 12') FROM DUAL;
TO_DATE(x [,fmt]) 将字符串或数字转为日期
SELECT TO_DATE('2021-12-13','YYYY-MM-DD'),TO_DATE(20211213,'YYYYMMDD') FROM DUAL;
16、条件转换函数
DECODE,CASE WHEN THEN ELSE END
DECODE(列|值,判断值1,返回值1,判断值2,返回值2,判断值3,返回值3.......默认值);
SELECT 56,DECODE(56,
10,100,
20,200,
30,300,
1000) X
FROM DUAL;
CASE WHNE 条件1 WHEN 返回值1 条件2 WHEN 返回值2 条件3 WHEN 返回值3 .....ELSE 默认值 END;
SELECT 56,CASE WHEN 56 = 10 THEN 100
WHEN 56 = 20 THEN 200
WHEN 56 = 30 THEN 300
ELSE 1000
END X
FROM DUAL;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库