oracle中查询：子查询，连接查询

最新推荐文章于 2024-05-06 21:10:29 发布
最新推荐文章于 2024-05-06 21:10:29 发布
阅读量2.9k 收藏 3
点赞数
文章标签： oracle date null sqlserver 工作 join
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lichenchen919621797/article/details/7866418
版权

--序列：sequence

--作自动增长的主键

 

--创建序列,初始值1，增长的步长默认是1

CREATESEQUENCE stu_seq;

 

CREATESEQUENCE test_seq

STARTWITH1         --初始值

INCREMENTBY5        --步长为5

MAXVALUE2000         --最大值2000

MINVALUE1            --最小值

NOCYCLE               --不循环nocycle达到最大值不循环，报错，循环cycle，达到最大值是否循环，

CACHE20              --在内存的缓冲中存放序列的个数

 

--访问序列的下一个值和当前值，dual是oracle中自带的一张虚表

SELECT stu_seq.nextval,stu_seq.currval FROM dual;

SELECT test_seq.nextval FROM dual;

 

CREATETABLE usrinfo(

userid NUMBERPRIMARYKEY,

NAMEVARCHAR2(20)

);

--将userid作为自动增长的主键，使用序列的nextval进行访问

INSERTINTO usrinfo values(stu_seq.nextval,'tom');//掌握

 

--删除序列

DROPSEQUENCE test_seq;

 

--数据库表中的几种约束

--主键约束；PRIMARY key，唯一性，并且不能为空

--唯一约束：Unique key，唯一性，允许有null值，只能有一个null值

--外键约束；foreign key，建立和另外一张主表之间的关联，外键字段的值必须要参照主表中主键的值

--非空约束：NOT NULL

--检查约束：check

 

CREATETABLE depts(

deptno NUMBER(2) PRIMARYKEY,

dname VARCHAR2(20),

loc VARCHAR2(20)

);

 

CREATETABLE emps(

empno NUMBER(5),

ename VARCHAR2(20) NOTNULL,

JOB VARCHAR2(20),

MGR NUMBER(5),

HIREDATE DATEDEFAULTSYSDATE,--default：设置字段的默认值，默认为系统日期

SAL NUMBER(10,2) CHECK(sal>100),--check约束

comm NUMBER(10,2),--表示数字位最大长度为7，其中小数位为2，整数位最大为5

deptno NUMBER(2),

idcard VARCHAR2(18) UNIQUECHECK(LENGTH(idcard)=18),--检查身份证号码长度是否为18位

CONSTRAINT fk_deptnos FOREIGNKEY(deptno) references depts(deptno),--外键约束

CONSTRAINT pk_empnos PRIMARYKEY(empno)--主键约束

);

 

DROPTABLE emps;//删除表，连带数据库中的表数据信息一块删除，delete只删除外在的表

INSERTINTO emps ALUES(1234,'honey','clerk',NULL,SYSDATE,3000,500,NULL,'123456789456123045');

INSERTINTO emps VALUES(1250,'toney','sale',NULL,SYSDATE,3500,500,NULL,'189456234567123045');

INSERTINTO emps(empno,ename) VALUES(1236,'soney');

 

INSERTINTO depts VALUES(1,'开发部','无锡');

INSERTINTO emps VALUES(1235,'honey','clerk',NULL,SYSDATE,3000,500,1,'456712389456123045');

 

SELECT * FROM emps;

SELECT * FROM depts;

 

--删除部门1的信息

--先删从表（外间所在的表），再删主表（关联的主键表）

DELETEFROM emps WHERE deptno=1;

DELETEFROM depts WHERE deptno=1;

 

--Group by子句使用：对分组后的数据进行统计（结合聚合函数使用）

--在分组语句查询中，只有group by后面的字段才可以出现在select子句中，

--单行的目标不能和聚合函数放在一起使用

--查询各个部门的所有员工总工资、最高工资、最低工资、平均工资、部门人数

SELECT deptno,SUM(sal),MAX(sal),MIN(sal),AVG(sal),COUNT(*)

FROM emp

GROUPBY deptno;

 

--查询各工作岗位的所有员工总工资、最高工资、最低工资、平均工资、部门人数

SELECT job,SUM(sal),MAX(sal),MIN(sal),AVG(sal),COUNT(*)

FROM emp

GROUPBY job;

 

--查询各部门从事不同工作岗位的人数

SELECT deptno,job,COUNT(*)

FROM emp

GROUPBY deptno,job     --按照多个字段分组，多个字段之间用逗号（‘，’）隔开

ORDERBY deptno;        --按照部门编号升序排序

 

--对分组后的数据进行再次筛选，要使用Having子句

--不能使用where子句，而且在where子句中不能使用聚合函数

--查询各部门总工资高于10000的部门编号和总工资

SELECT deptno,SUM(sal)

FROM emp

GROUPBY deptno

HAVINGSUM(sal)>10000;   

 

--order by子句：排序默认升序asc 降序desc

--查询所有员工的信息，按照部门编号升序排列、工资降序排序

SELECT *

FROM emp

ORDERBY deptno,sal DESC;

 

--连接查询

--查询员工编号、员工姓名、员工工资、员工所在部门名称

--等值连接（内连接）；选择两张表中互相匹配的记录

--第一种，较常用，一般都是用这种方法

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

WHERE emp.deptno = dept.deptno;

 

--第二种方法，不常用

SELECT empno,ename,sal,dname,emp.deptno

FROM emp

INNERJOIN dept

ON emp.deptno = dept.deptno;

 

--左连接(left join)：包含左边表中的所有记录，甚至是右边表中没有和它匹配的记录

--侧重点：查看的是所有员工的信息，即使员工没有信息

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

WHERE emp.deptno = dept.deptno(+);--该方法在oracle中可以使用，在SQLServer中不可以使用

 

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

LEFTJOIN dept

ON emp.deptno = dept.deptno;

 

--右连接(right join)：包含右边表中的所有记录，甚至是左边表中没有和它匹配的记录

--侧重点：查看的是部门的信息，即使部门中没有员工

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

WHERE emp.deptno(+) = dept.deptno;

 

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

RIGHTJOIN dept

ON emp.deptno = dept.deptno;

 

--全连接(full join)：左右表中的所有记录都会显示

SELECT empno,ename,sal,dname,emp.deptno

FROM emp,dept

FULLJOIN dept

ON emp.deptno = dept.deptno;

 

--自身连接：自己表和自己表作连接，通常会对表指定别名的方式来实现

--查询所有比SMITH员工工资高的员工工号、员工姓名、员工工资以及SMITH的工资

SELECT other.empno,other.ename,other.sal othersal,smith.sal smithSal

FROM emp other,emp smith

WHERE smith.ename = 'SMITH'

AND other.sal > smith.sal;

 

--子查询

--查询比JAMES工资高的员工信息

SELECT empno,ename,job,sal

FROM emp

WHERE sal>(SELECT sal

             FROM emp

             WHERE ename='JAMES'

           );

 

--查询工作岗位与7369相同，并且工资高于7876的所有员工信息

SELECT empno,ename,job,sal

FROM emp

WHERE job = (SELECT job

                FROM emp

                WHERE empno = 7369)

   AND sal >(SELECT sal

                FROM emp

                WHERE empno = 7876);

               

--查询工作岗位与7369相同，并且同属一个领导的员工信息

SELECT empno,ename,job,mgr,sal

FROM emp

WHERE (job,mgr) = (SELECT job,mgr

                FROM emp

                WHERE empno = 7499);--条件一致的情况下才可以用到这种写法，可以提高查询效率

 

--查询比部门30的最低工资还低的部门编号和部门最低工资

SELECT deptno,MIN(sal)

FROM emp

GROUPBY deptno

HAVINGMIN(sal) <(SELECTMIN(sal)

                    FROM emp

                    WHERE deptno = 30);

--多行的子查询(使用多行运算符：in、ANY、ALL)IN表示在某一个集合范围之内

--查询每个部门中最低工资的员工信息

SELECT empno,ename,sal,deptno

FROM emp

WHERE sal IN (SELECTMIN(sal)

                FROM emp

                GROUPBY deptno);

 

--显示工作岗位不是CLERK的雇员，并且检查这些雇员的薪水是否低于CLERK雇员中任意一个雇员的薪水

--< ANY:小于列表中的最大值

--> ANY:大于列表中的最小值

SELECT empno,ename,job,sal

FROM emp

WHERE job != 'CLERK'

AND sal < ANY (SELECT sal

                FROM emp

                WHERE job = 'CLERK');

               

SELECT empno,ename,job,sal

FROM emp

WHERE job != 'CLERK'

AND sal < (SELECTMAX(sal)

                FROM emp

                WHERE job = 'CLERK');

 

 

--显示工作岗位不是SALESMAN的雇员，并且检查这些雇员的薪水是否低于SALESMAN雇员中所有雇员的薪水

--< ALL:小于列表中的最小值

--> ALL:大于列表中的最大值

SELECT empno,ename,job,sal

FROM emp

WHERE job != 'SALESMAN'

AND sal < ALL(SELECT sal

                FROM emp

                WHERE job = 'SALESMAN');

            

SELECT empno,ename,job,sal

FROM emp

WHERE job != 'SALESMAN'

AND sal < (SELECTMIN(sal)

                FROM emp

                WHERE job = 'SALESMAN');

 

--查询没有下属的员工信息(注意NULL值运算)

SELECT empno,ename,job,mgr,sal

FROM emp

WHERE empno NOTIN (SELECT mgr

                      FROM emp

                      WHERE mgr ISNOTNULL);

              

--case表达式使用

SELECT empno,ename,job,

           (CASE job

           WHEN'CLERK'THEN'普通员工'

             WHEN'SALESMAN'THEN'销售人员'

               WHEN'MANAGER'THEN'经理'

               WHEN'ANALYST'THEN'分析人员'

                 ELSE'老板'

                   END

                   )jobname

        FROM emp;

 

--dual是一个虚表

SELECT * FROM dual;

 

--转换函数

--to_char(date,format):将日期格式化成字符串

SELECT to_char(SYSDATE,'YYYY"年"MM"月"DD"日" hh24:MI:SS')

FROM dual;

 

SELECT to_char(hiredate,'yyyy.mm.dd') FROM emp;

 

--to_date(char,format):将字符串转换为日期

SELECT to_date('2005-12-06','yyyy-mm-dd') FROM dual;

 

--to_number(char):将数字子串转换为数字

SELECT to_number('100') FROM dual;

 

--查询2月份入职的员工信息

SELECT * FROM emp WHERE to_char(hiredate,'mm') = '02';

 

--日期函数使用

--add_months(date,count):在指定的日期上添加count个月，返回值是日期类型

SELECT add_months(SYSDATE,3) FROM dual;

 

--months_between(date1,date2)：返回两个日期之间相差的月份

SELECT months_between(SYSDATE,to_date('2012-06-25','yyyy-mm-dd')) differ FROM dual;

 

--last_day(date)：返回指定日期所在月的最后一天的日期

SELECT to_char(last_day(SYSDATE),'yyyy/mm/dd') lastday FROM dual;

 

--next_day(date,day)：返回指定日期的下一个day所在的日期

SELECT next_day(SYSDATE,'星期六') nextday FROM dual;

 

--trunc(date,format)：返回按指定格式截取后的日期

SELECTTRUNC(SYSDATE,'mi') FROM dual;

 

--greatest(date1,date2,...)：返回日期列表中最晚的那个日期

SELECTGREATEST(SYSDATE,to_date('2012-08-01','yyyy-mm-dd')) great FROM dual;

 

--least(date1,date2,...)：返回日期列表中最早的那个日期

SELECTLEAST(SYSDATE,to_date('2012-08-01','yyyy-mm-dd')) le FROM dual;

 

--两个日期之间相差的天数,round是取整

SELECT (SYSDATE - to_date('2012-08-01 10:02:00','yyyy-mm-dd hh:mi:ss')) days FROM dual;

--日期和日期之间相加减得到的是数字

--日期和数字之间相加减得到的仍然是日期格式

--查询每个月倒数第二天入职的员工信息

SELECT * FROM emp WHERE hiredate = last_day(hiredate) - 1;

SELECT * FROM emp;

--查询在15年前入职的员工信息

SELECT * FROM emp

WHERE months_between(SYSDATE,hiredate) > 15 * 12;

 

--显示正好为5个字符的员工的姓名

SELECT * FROM emp

WHERELENGTH(ename) = 5;

 

--显示不带有"R"的员工姓名

SELECT * FROM emp

WHEREINSTR(ename,'R') = 0;

 

--查询所有员工的年薪

SELECT empno,ename,sal,comm,(sal + NVL(comm,0)) * 12 totalsal

FROM emp;

 

NVL(comm,0)--相当于

 

--decode和case表达式的用法很相似

SELECT empno,ename,deptno,

       DECODE(deptno,

                  10,'第一部门',

                  20,'第二部门',

                  30,'第三部门',

                  '其他部门'

                  ) deptname

   FROM emp;

lichenchen919621797
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【Oracle数据库_子查询_详解易懂版】
shigecainiao的博客
10-25 2317
子查询可以说是select的嵌套。单行子查询，多行子查询。多列子查询，关联子查询，嵌套子查询。
【Oracle】查询基本使用
兮动人
12-29 1812
select 1+1; 在Oracle等于报错 ,在MYSQL中输出结果是2 dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构 查询语法 select * from emp; 别名用法 在查询的结果列中可以使用别名 select 列名 别名，列名 别名，... from emp; select ename 姓名, sal 工资 from emp; 别名中，有没有双引号的区别就在于别名中有没有特殊的符号或者关键字 select ename "姓 名",.
【ORACLE】数据库的查询
最新发布
2301_76210107的博客
05-06 905
【例1】SELECT 姓名 AS NAME,地址 AS ADDRESS,电话号码 AS PHONENUMBER FROM EMPLOYEES EHERE SEX=1;完全外连接（FULL OUTER JOIN）：结果表中除了满足连接条件的行外，还包括两个表所有行。左外链接（LEFT OUTER JOIN）：结果表中除了满足连接条件的行外，还包括左表的所有行。外连接的结果不但包含满足连接条件的行，还包括相应表中的所有行。结果表中包含编号相同的EMPLOYEES表中的姓名列和SALARY表中的所有列。
Oracle 查询
cs_quan的博客
05-27 3658
2 Oracle 查询 目录 单表查询 连接查询 子查询 分页查询 单行函数 行列转换 分析函数 集合运算 总结 1 单表查询 简单条件查询 select * from T_OWNERS where watermeter='30408' 模糊查询 select * from t_owners where name like '%刘%' and 和 or 运算符 select * from t_owners where name like '%刘%' and housenumber li
Oracle之子查询
Libraxu的博客
07-28 948
【代码】Oracle之子查询。
Oracle快速入门（多表查询大全）
2401_84003941的博客
04-02 256
针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。下面的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题（都整理成文档，小部分截图）最新整理电子书《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》点击传送门即可获取！深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。
Oracle数据库中基本的查询优化与子查询优化讲解
09-10
在子查询中避免使用对性能影响大的函数，比如在日期操作中，避免在子查询中直接对大量数据执行函数计算。例如，使用`to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')`可能会降低效率。可以考虑先...
python针对Oracle常见查询操作实例分析
09-08
本篇将深入探讨Python中针对Oracle的子查询和多表查询。 1. 子查询 子查询是指在SQL查询语句中嵌套另一查询语句，用于获取主查询所需的数据。子查询可以作为表达式的一部分，用于比较、筛选或组合。以下是一些子...
深入sql oracle递归查询
09-10
在示例中，查询1号节点的所有子节点的SQL语句如下： ```sql SELECT * FROM aaa START WITH id = 1 CONNECT BY pid = PRIOR id ``` 这里，`PRIOR`关键字用于引用上一层的字段，`id = PRIOR pid`表示当前行的`pid`...
oracle基本查询用法入门示例
09-08
Oracle数据库是世界上最广泛使用...通过这些基本查询和函数，你可以根据需求从Oracle数据库中提取所需信息。随着熟练度的提高，还可以进一步学习更复杂的查询技术，如联接、分组、聚合等，以应对更复杂的数据分析任务。
Oracle 11GR2的递归WITH子查询方法
09-09
Oracle 11GR2的递归WITH子查询是一种强大的工具，它允许你在SQL查询中创建递归关系，处理层级数据，比如组织结构、家族树或依赖关系等。WITH子查询，也称为公共表表达式（Common Table Expression，简称CTE），自SQL...
Oracle查询语句大全-精华版
08-05
Oracle查询语句大全，这个主要适合新人或者转数据库的人做字典型查询
SQL中的查询命令
asdfddsasf的博客
01-11 1万+
SQL：Structured Query Language 结构话查询语言 第一个命令：（命令的结尾必须是；， sql命令是不区分大小写的） show databases; 列出服务器管理的数据库 第二个命令：(数据库名必须是唯一） use +数据库名 ；进入到哪个数据库中 第三个命令： show tables； 显示该数据库中所有的表 第四个命令： show columns form 表名； 显示该表所有的列名 Field: 列名称 Type: 列数据类型 ...
oracle左连接查询博客,Oracle中left join,right join,inner join分析
weixin_36386962的博客
04-03 1275
在开始分析之前，我们先准备一些测试数据。首先，初始化三张表，分别为test_user,test_team,test_type,建表语句如下create table TEST_USER(user_id NUMBER,user_name VARCHAR2(30 CHAR),user_team NUMBER,user_type VARCHAR2(30 CHAR));create table test...
ORACLE中的子查询
luwei502的专栏
11-29 2012
“子查询”就是查询中嵌套着另一个查询，也即通过SELECT语句的嵌套使用形成子查询。当我们不知道特定的查询条件时,可以用子查询来为父查询提供查询条件以获得查询结果。ORACLE中常见的子查询有下面几类：A、单行子查询(Single-row subqueries)。B、多行子查询(Multirow subqueries)。C、内部视图型子查询(Inline views)。D、多列子查询(Mult
Oracle 数据库的子查询（关联子查询）
liaowenxiong的博客
08-06 2690
子查询结果返回一个值（单列单行） 选择比较运算符：> < >= <= = <> select ename from emp_xxx where salary > ( select salary from emp_xxx where ename = '张无忌' ) ; 子查询结果返回多行（单列多行） 选择比较运算符：>ALL >ANY <ALL <ANY in select ename from emp_xxx where
oracle查询sql语句
热门推荐
十色花的博客
07-08 2万+
Oracle查询语句 select*from scott.emp ;1.--dense_rank()分析函数（查找每个部门工资最高前三名员工信息）select*from(selectdeptno,ename,sal,dense_rank()over(partitionby deptno orderby sal desc) a fromscott.emp)where a&lt;=3orderbyde...
Oracle-SQL基本查询.ppt
11-25
Oracle，大型数据库技术课件

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值