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;

 

posted @   左叔  阅读(358)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示