oracle数据库的操作2--多表查询sql、事务、拷贝、DDL、DML、约束

最新推荐文章于 2024-06-05 11:15:00 发布
最新推荐文章于 2024-06-05 11:15:00 发布
阅读量394 收藏
点赞数
分类专栏： 数据库-oracle 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/S_L__/article/details/105168760
版权

这里将oracle的多表查询的sql用法。

基础的语法参考另一篇oracle数据库的操作--基础sql：https://blog.csdn.net/S_L__/article/details/105030191

1.--笛卡尔积

select * from employees e,departments d;

2.内连接 inner join

select e.*,d.*
from employees e
join departments d on e.department_id=d.department_id;

3.等值连接  

select e.*,d.*
from employees e,departments d
where e.department_id=d.department_id;

4.左外连接

4.1 通用版，mysql一样:left join on

select e.*,d.*
from employees e
left join departments d on e.department_id=d.department_id;

4.2 oracle专用：左外连接:left join on

--oracle自己的左外连接:等号的右边末位添加(+)
select e.*,d.*
from employees e,departments d
where e.department_id=d.department_id(+);

5.右外连接:right join on

5.1 通用版，mysql一样:right join on

select e.*,d.*
from employees e
right join departments d on e.department_id=d.department_id;

5.2 oracle专用：左外连接:right join on

--oracle自己的右外连接:等号的左边添加(+)
select e.*,d.*
from employees e,departments d
where e.department_id(+)=d.department_id;

6.全连接 full join on

select e.*,d.*
from employees e
full join departments d on e.department_id=d.department_id;

7.非等值连接

 即：2个表没有关系，字段范围处理
select e.salary,j.*
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal

select e.salary,j.*
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;


8.自连接

本质就是同一张表,同时拥有主键，外键
employees,主键employee_id，外键manager_id
员工姓名 员工工资  上级姓名 上级工资
select e.first_name||e.last_name,e.salary,m.first_name||m.last_name,m.salary
from employees e,employees m
where e.manager_id=m.employee_id

自连接适用场景（菜单树）：
   菜单树
   系统管理
       员工管理
   基础数据管理    
       商品管理
       商品类型管理

 

9.支持SQL1999（了解，不常用）

9.1交叉连接==笛卡尔积

CROSS JOIN——它在两个表格中创建了一个笛卡尔积，就象是在Oracle8i中没写WHERE时一样
select e.*,d.*
from employees e
cross join departments d;

9.2 自然连接

即:不能使用*,2个表的字段有相同
自动把2个表相同的列名做等值连接
NATURAL JOIN——它通过从WHERE子句中自动连接标准来改善SQL的稳定性
select e.first_name||e.last_name,d.department_name
from employees e
natural join departments d;

select e.first_name||e.last_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id
and e.manager_id=d.manager_id;

9.3 USING子句

 它可以通过名字来具体指定连接
(填写那个字段，就是那个字段进行等值连接)
写字段的时候不能写别名，直接写字段名称
select e.first_name||e.last_name,d.department_name
from employees e
join departments d using(department_id,manager_id);


10.子查询

--①单行单列子查询：只包含一个字段的查询，返回的查询结果也只包含一行数据
--查询工资大于平均工资的员工
1.平均工资
select avg(salary) from employees;--4400
2.大于平均工资
select *
from employees
where salary>
              (
              select avg(salary) from employees
              )

②多行单列子查询：只包含了一个字段，但返回的查询结果可能多行或者零行

--分组，只要平均工资
select avg(salary)
from employees
group by department_id

 

11. Top-N （2层查询）

语法: ROWNUM为oracle自带的，用来截取数据（前面多少条，或者后面多少条） ，相当于maysql的limit num.

注意：rownum关键字直接使用，只能使用<=,不支持>用法，但是使用别名可以用>符号，在分页的使用需要用
SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list]
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;
      
--查询工资最高的3名员工
select a.*,rownum r2
from
    (
      select e.*,rownum r1
      from employees e
      order by e.salary desc
    )a
where rownum<=3  

 

12.分页查询（3层查询）

两种方式：第一种为oracle标准的分页查询方法，因为所查询的条数更低，效率更高。

第一种分页sql：
select b.*,rownum r3
from
    (select a.*,rownum r2
      from
        (
          select e.*,rownum r1
          from employees e
          order by e.salary desc
        )a
      where rownum<=6  --把一个查询条件推到第2层
    )b
where r2>3

第二种分页sql：
select b.*,rownum r3
from
    (select a.*,rownum r2
      from
        (
          select e.*,rownum r1
          from employees e
          order by e.salary desc
        )a
    )b
where r2>3 and r2<=6

13.从其他表拷贝数据

第一种：表，字段，内容全部复制

备份employees表到employees_bck
create table employees_bck
as
select * from employees;

第二种：只复制表结构，不要数据

创建employees_bck2表，复制employees表结构
create table employees_bck2
as
select * from employees
where 1=2 ;

第三种：只复制部分字段

使用employees表中的某几列创建employees_test表(id,fullname,hire_date,salary)。
create table employees_test
(id,fullname,hire_date,salary)
as
select employee_id,first_name||last_name,hire_date,salary
from employees;

14.DML增删改

①--insert插入语句

1，插入一行数据，可以空值（和mysql一样）
insert into employees_bck2(employee_id,first_name,last_name)
values(1,'军','军');
2，插入时使用函数(hire_date-sysdate)
insert into employees_bck2(employee_id,hire_date)
values(20,sysdate);
3.insert+select  （oracle特殊场景写法）
insert into employees_bck2
select * from employees;
注意：这样写法要求字段名完全一直，否则按照下面的写法
insert into employees_bck2
(employee_id,first_name,last_name,hire_date,salary)
select employee_id,first_name,last_name,hire_date,salary
from employees;

②--update

1，简单的修改语句，在线运行系统注意先备份数据在修改
update employees_bck2
set first_name='张',last_name='二娃'
where employee_id>10000
2，修改10006号员工的6000工资和10002号员工4000相同--注意，在mysql中的对同一张表的更新问题。

oracle写法：

update employees_bck2
set salary=(select salary from employees_bck2 where employee_id=10002)
where employee_id=10006

mysql写法:
update employees  
   set salary =
    (  
      select salary from
      (  
         select * from employees  
      ) as e  
      where e.employee_id = 10002
   )  
   where employee_id = 10006;


③--delete

1.简单的删除语句 （和mysql一样）
delete from employees_bck2
where employee_id<100

      

15. merge导数据

--场景：在线系统又更新了一些数据 ，备份表更新已有的数据，插入新的数据
Merge语句(oracle9i后新增)：
MERGE INTO table_name table_alias
  USING (table|view|sub_query) alias
  ON (join condition)
  WHEN MATCHED THEN
    UPDATE SET
    col1 = col_val1,
    col2 = col2_val
  WHEN NOT MATCHED THEN
    INSERT (column_list)
    VALUES (column_values);
    
 使用案例：
merge into employees_bck2 e2
 using employees e
 on (e.employee_id=e2.employee_id)
 when matched then
   update set
   e2.first_name=e.first_name,
   e2.last_name=e.last_name
 when not matched then
   insert(e2.first_name,e2.last_name)
   values(e.first_name,e.last_name)

 

16.事务

①--事务开始和结束条件

（存在于DML和DDL）
1、开始于第一个执行的语句
2、结束于:
      用户执行COMMIT 或 ROLLBACK
      单个的DDL or DCL 语句
      用户连接异常错误，或者用户断开连接
      系统崩溃

②事务的自动处理

1、当下列情况发生时事务自动隐式提交:
      执行一个 DDL 语句
      从 SQL*Plus正常退出(EXIT 或者 QUIt)
2、当从SQL*PLUS中强行退出或连接异常中断时,事务自动回滚


③Sqlplus的自动提交

在SQLPLus中有一个与事务相关的环境变量-autocommit，如果这个参数被启用，在SQLPLus中所有修改将在执行时自动提交，启动的方法是在SQLplus环境下执行set autocommit on命令。
--默认状态下oracle的事务自动提交是关闭的
SQL> show autocommit;  
autocommit OFF
--下面的语句就是开启事务自动提交
SQL> set autocommit on
SQL> show autocommit;
autocommit IMMEDIATE


④ 事务点

   场景：大量导入数据需要回滚的时候，可以回滚到指定事务点
--保存事务点(内存),不能手动点击提交或者回滚
sp1 没有数据:如果回到sp1点,sp2和sp3事务点就生效
select * from test t
savepoint sp1;
sp2 有11条数据
insert into test
select employee_id,first_name
from employees;

savepoint sp2;

sp3 删除了11条数据
delete from test
savepoint sp3;
--回滚指定事务点
rollback to sp1;

⑤--事务悲观锁、乐观锁

--悲观锁
第一人:必须把事务提交或者回滚:释放锁
select * from table id=100 for update对这一行记录加锁,如果事务没有提交,其他就只能只读访问，不能修改
update from table where id=100
第二人
select * from table id=100
update from table where id=100--不能执行

--乐观锁:数据库表里面添加了一个字段version
第一人
select *,version=10 from emp where id=11
先判断当前的版本是否是10,是
update  ...where id=11 and version=10
update成功,version=11
第二人
select *,version=10 from emp where id=11
update  ...where id=11 and version=10
提交的时候发现数据库version==11,
否version=11抛出异常version以及被其他人修改了

 

17.DDL增删改

①---增加字段

语法：
ALTER TABLE table
ADD       (column datatype [DEFAULT expr]
           [, column datatype]...);
案例：     
alter table test
add (sex number(1))

②--修改字段

语法：
ALTER TABLE table
MODIFY       (column datatype [DEFAULT expr]
           [, column datatype]...);
不一定会成功，如：把一个字符串变成number,先把字符串的字段设置为null
案例：
alter table test
modify (sex char(1))

③--删除字段

语法：
ALTER TABLE table
DROP  column     (columns);
案例：
alter table test
drop column sex--只能删除一列

drop 删除列  不可回滚
            从每行中删除掉字段占据的长度和数据,释放在数据块中占用的空间。删除大表中的字段将需要比较长的时间.
truncate 删除表 ，不可回滚
         使用：  TRUNCATE TABLE table_name
delete ：（DML语句）以选择删除表中的一部分  可回滚

 

18. 约束

--Oracle 支持下面五类完整性约束:
1、NOT NULL    非空
2、UNIQUE Key    唯一键
3、PRIMARY KEY    主键(非空且唯一)
4、FOREIGN KEY    外键
5、CHECK        检查
案例：
create table t_user
(
   id int primary key,--列级建立约束
   name varchar2(20) not null unique,
   password varchar2(20),
   age number(3) check(age>0 and age<150)
);

双主键约束，只能创建表的时候控制，如下：
create table t_user_role
(
   u_id int,
   r_id int,
   constraint sys_c_ur_pk primary key(u_id,r_id),--双主键,表级
   --双外键
   constraint sys_c_ur_fk1 foreign key (u_id) references t_user(id),
   constraint sys_c_ur_fk2 foreign key (r_id) references t_role(id)
);

 

 

 

 

 

S_L__
关注
专栏目录
【云原生进阶之数据库技术】第二章-Oracle-使用-3.2.2-Oracle ogg安装部署
junbaozi的专栏
05-27 892
在目标端，主要做了4个操作，共包括2个进程，分别是MANAGER和REPLICAT。在目标端，主要做了4个操作，共包括2个进程，分别是MANAGER和REPLICAT。
Day19 Oracle 数据库 创建表 约束 JDBC BaseDAO
weixin_44695396的博客
05-18 586
一 创建表 1.创建表(不加约束) (1)创建新表(不加约束) 创建表 create table tb_user( userid number(5), username varchar2(30), userpwd varchar2(20), gender char(3) ) 加入注释 comment on table tb...
数据库多表查询SQL语句
beyond_mk的博客
09-25 7456
最近在做“进销存业务系统”，作为项目组成员的一部分，我负责统计查询，简单说就是多表查询，网上有很多，但是很明确的却没有，所以让我这个初学者了费，最终还是搞出来了，在这里也和大家共享一下成果和其中出现的问题，高手可绕道，不求点赞，只为方便大家。下面开始：我一共建了4个表，分别是Supplier、PurchaseMaster、PruchaseDetail、Product四个表（大家在测试的时候可以随意建
ORACLE 数据库基本参数查询
cko82679的博客
09-15 239
sys用户 select * from v$version;数据库相关服务的版本信息； select * from dba_registry_database;数据库所在服务器的版本信息； select * from...
Oracle中对数据表的各种操作
技术小学僧的博客
07-25 199
1、建表语句 -- Create table create table FB_CURRENCY ( CURRENCY_CODE VARCHAR2(40) not null, CURRENCY_NAME VARCHAR2(40), S_CMPY VARCHAR2(40), S_TDEPT VARCHAR2(40), S_TNAME VAR...
Oracle数据库的多表关联查询SQL语句
Satisfy_555的博客
10-16 5283
转至 数据库（学习整理）----7--Oracle多表查询，三种join连接 ======================= 聚合函数:（都会忽略null数据） 常用的有5种：将字段中所有的数据聚合在一条中 1、sum(字段名) ：求总和 2、avg(字段名) ：求平均值 3、max(字段...
Oracle数据库之多表查询
好好学java
01-04 206
oracle安装参照： Oracle数据库之安装教程 Oracle数据库总结： Oracle数据库之基本查询 Oracle数据库之单行函数 Oracle数据库之多行函数 Oracle数据库之多表查询 SQL&gt; –等值连接 SQL&gt; –查询员工信息：员工号 姓名 月薪 部门名称 SQL&gt; select e.empno,e.ename,e.sal,d.dname ...
oracle数据库dcl,Oracle的DML、DDL、DCL详解
weixin_34101914的博客
04-04 1207
DML 全称Data Manipulation Language，即数据操作语言：insert update delete selectDDL 全称Data Definition Language，即数据定义语言：create alter drop runcateDCL 全称Data Control Language，即数据控制语言：commit rollback表名和列名命名规则：必须以字母开头...
OCI操作oracle数据库[收集].pdf
10-11
OCI 操作 Oracle 数据库 OCI（Oracle Call Interface）是 Oracle 公司提供的由头文件和库函数等组成的一个访问 Oracle 数据库的应用程序编程接口（API）。它允许开发人员在第三代编程语言（包括 C、C++、COBOL 与 ...
【云原生进阶之数据库技术】第二章-Oracle-原理-4.4.2-SGA架构
最新发布
junbaozi的专栏
06-05 1543
SGA是一块可读写内存区域，与Oracle后台进程（background processes）一起构成了数据库实例。所有代表用户执行的服务器进程都能读取实例SGA里的信息。有一些进程能在数据库运行时写入SGA。需要注意的是，服务器和后台进程本身并不在SGA中，而是存在于独立的内存空间中。每个数据库实例都有自己的SGA。Oracle数据库会在实例启动时自动为SGA分配内存，并在实例关闭时回收内存。正如图1，SGA由多个为了满足特定内存分配需求的内存池组成。
Oracle数据库中的多表查询
lxixi的博客
08-04 228
--多表查询    1.交叉连接:表结构中的数据两两组合(没什么作用)      select t1.*,t2.*      from t_student t1,t_class t2      --交叉连接获取的结果是一个笛卡尔乘积         2.等值连接(在交叉连接的基础上，性能问题，不适用于数据量大)      select t1.*,t2.* -- 100000  2      f...
Oracle(五)：oracle多表操作
yu1755128147的博客
01-28 242
一、多表更新 语法 UPDATE table1 { [ INNER ] JOIN {LEFT | RIGHT} [ OUTER ] JOIN} table2 ON conditional_expr SET col1 = {expr1 | DEFAULT} [, col2 = {expr2 | DEFAULT} ] .. . [ WHERE where_condition ] 实例 ...
多表查询sql语句
06-01 474
多表查询sql语句 1 --解锁SCOTT用户 2 alter user scott account unlock 3 --检索指定的列 4 select job,ename,empno from emp; 5 --带有表达是的select子句 6 select sal*(1+0.2),sal from emp; 7 --显示不重复的记录 8 sele...
【多表查询】一条sql语句
啊迪
04-14 1043
sql 多表 union
Oracle多表查询语句
TylerYue的博客
06-25 598
1、连接条件: 等值于非等值连接，可以规避笛卡尔积错误 (1)连接条件数量： 连接n个表至少需要n-1个连接条件 2、连接方式： 所有连接都在内连接基础之上的操作 **(1)自连接：**单个表中（模拟多表情况）构建连接查询符合条件的结果，语法格式和内连接一致 **(2)内连接：**合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行 理解：返回满足多个表条件的行 (3)外连接：左/右外连接 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左
oracle数据库的多表查询语句,Oracle数据库-多表联合查询&子查询
weixin_36218360的博客
04-03 898
多表联合查询当需要获取的数据分布在多张中，考虑使用联合查询1、SQL92方式2、SQL99方式SQL92方式1、笛卡尔积:将多个表的数据进行一一对应，所得到结果为多表的笛卡尔积。结果的数量为所有表的数量的乘积。select * from emp,dept2、等值连接筛选概念：先做表的笛卡尔积，然后筛选，筛选条件为等值筛选。注意：条件为字段的值相同来进行筛选，字段的名字可以不同查询员工姓名，工作，薪...
oracle sql语句之多表查询
Mr_王小峰的博客
09-12 991
--多表之间有公共列   多表查询的分类： 联合条件 1、内联接（查询两张表之间相同数据） 2、外联接（查询 两张表中的数据，一张表显示所有数据（主），另外一张表只显示满足条件的数据（从)，没有对应的数据以null填充）   2.1、左外联接（左侧表为主表，右侧表为从表(null))   2.2、右外联接(右侧表为主表，左侧为从表（null))   2.3、完全外联接(了解：) 3、
Oracle从零开始05——SQL语句04——多表查询
weixin_34198583的博客
01-23 69
6.多表查询 1) 基本语法 a) 用WHERE消除笛卡尔积 SELECT * FROM emp，dept WHERE emp.deptno=dept.deptno； 如果表名太长，可以给表起别名： SELECT * FROM emp e，dept d WHERE e.deptno=d.deptno； b)自相关 如查询每个雇员的姓名、工作、雇员的直接上...
oracle数据库操作---4 多表查询，使用连接在多表中查询数据
yikuki的博客
01-02 606
多表查询，使用连接在多表中查询数据 多表查询 在了解了单表查询后，与单表查询类似的想法——多表查询也就应运而生 按照常理，其语法格式与单表查询类似，于是按照我们的想法有如下的代码： select * from employees;--107 select * from departments;--27 select * from employees,departments order by sal...
Oracle数据库SQL详解：DDL、DML与查询操作
在Oracle中，SQL主要分为四大类别：DDL（Data Definition Language）、DML（Data Manipulation Language）、TCL（Transaction Control Language）和DCL（Data Control Language）。 1. DDL用于定义数据库结构，包括...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值