Oracle入门基础第四讲(1)

集合(SET)

> union/union all

> intersect

> minus

--集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致 SQL>select empno, ename from emp where deptno=10;

SQL>select deptno,dname from dept where deptno<40;

--这两个列是同构，数据类型和数量都相同叫同构。

A = {1,2,3,4,5,6}

B = {2,4,6,8}

A union B = {1,2,3,4,5,6,8}

A union all B = {1,2,2,3,4,4,5,6,6,8}

A intersect B = {2,4,6}

A minus B = {1,3,5}

【例】查看部门10和20的职位，分别用union /union all/intersect/minus试一下。 SQL> select job from emp where deptno = 10;

SQL> select job from emp where deptno = 20;

--教师演示union

SQL>select job from emp where deptno = 10

union

select job from emp where deptno = 20;

数据操作语句（DML）

--之前我们用很长时间徘徊在select，现在我们学DML。首先建立新表： SQL> create table student_ning(

id number primary key,

name varchar2(20),

email char(40),

registtime date default sysdate);

--可以查询：

SQL> desc student_ning;

1、insert插入语句

--新增记录

SQL> INSERT INTO student_ning(id,name,email,registtime)

VALUES(1,'peter','peter@',sysdate);

--如果新增全部列,可以省略列名称，必须提供全部的列数据，数据的顺序必须按照表结构，如果有数据没提供，用 null 表示。

SQL>INSERT INTO student_ning

VALUES (2,'chris','chris@',sysdate);

--如果增加的不是全部字段,必须提供字段列表。

--建议采用写列名的方式 ，即使是插入全部数据，也建议把列名写全。 SQL> INSERT INTO student_ning(id, name,registtime)

VALUES(3,'bono',to_date('2012/11/01','yyyy/mm/dd'));

-- to_date 函数是oracle 独有的，在oracle 数据库中插入日期数据，一定要用

to_date处理。不要采用默认格式，比如：'01-NOV-11'。

--一次增加多条记录，使用子查询代替values 。在emp表中查询部门10的员工编号，员工姓名，那么id、name与empno和ename相对应。

SQL>insert into student_ning(id, name)

select empno, ename from emp

where deptno = 10;

--作此操作是为了开发时候保护数据，做备份。那么我现在不想创建一个表再复制数据，而直接复制一个表作为备份，怎么办？下面再介绍另一种向表里增加记录的方法。

2、复制表

2.1、复制全表

语法格式：create table 表名

as

查询语句;

2.2、只复制结构，不复制数据

【例】复制表：只复制结构，不复制数据

SQL>create table salgrade_yyy

as

select * from salgrade

where 1<>1; --红字部分是结果是false。

2.3、复制一部分数据(给查询语句加条件)

--如果复制表时的查询语句中有表达式或者函数(包括单行函数和组函数)，必须指定新表中的列名。指定方式：给列设置别名；或者在新表中设置列名。

【例】复制表：复制一部分数据（通过设置别名的方式）。

SQL>create table emp_yyy

as

select empno,ename,sal*12 year_sal --year_sal为新表的列名

from emp

where deptno = 10;

【例】复制表：复制一部分数据（指定新表中的列名）。

SQL>create table emp_count(did,emp_num) --新表中的列名

as

select deptno,count(*)

from emp

group by deptno;

2.4、创建一个空表，并同时向表中插入多条记录

【例】创建一个同emp 表结构相同的空表，将部门号为10的员工信息放入该表。 --情景描述：如果有一张表emp的数据量为一百万条，此时需要建立1张测试表只放入少量测试数据(如100 条) , 执行步骤如下所示：

第1步 创建一个空表

SQL>create table emp_bak

as

select * from emp

where 1=0;

第2步 插入少量测试数据

SQL>insert into emp_bak

(select * from emp where deptno=10);

【例】把表中的数据换为部门20和 30的员工记录

SQL>delete from emp_bak ; -- 删除全表的数据( from可以省略)

SQL>insert into emp_bak

(select * from emp where deptno in (20,30));

3、update

语法结构： update 表名 set 列名 = 新的列值, 列名 = 新的列值. ….

where 条件;

--复制emp表信息到表emp_ning;

SQL>drop table emp_ning;

SQL>create table emp_ning

as

select * from emp;

【例】修改员工‘SMITH’的信息，把他的工资改为$1000

SQL>update emp_ning set sal = 1000

where empno = 7369;

--大家想想我把where empno = 7369去掉，会出现什么结果？

【例】如果想改多个字段，把员工‘SMITH’的工资改为1500，部门改为20，职位改为‘salesman’。

SQL>update emp_ning set sal = 1500,

deptno = 20, job = 'salesman'

where empno = 7369;

【例】如果把部门编号换成99，此部门在dept表里不存在，看看会有什么结果。 SQL>update emp set deptno=99 where ename='SCOTT';

修改的值必须处与dept列表当中，否则也会报错，改为正确的形式：

SQL>update emp set deptno = 40 where ename = 'SCOTT';

4、delete

语法结构： delete [from] 表名 where 条件;

--drop table 会删除表结构和数据；truncate 删除表数据，保留表结构。drop 和truncate 都不可以回退。delete 仅删除数据，可以回退。

--举个例子：仓库里有一个货架，delete是相当于拿走货架上的商品暂时保存起来，需要时再放上去；truncate是相当于拿走货架上的商品并把它们销毁掉；而drop是把房子拆了。

【例】在emp_ning表中删除部门10里面的三行记录

SQL>delete emp_ning where deptno = 10;

【例】我们查询select * from emp；字段deptno中存在部门10，执行语句：

delete from dept where deptno=10；会报“以找到子记录”错误。为什么？ DML: insert / update/ delete

本文来源：https://www.bwwdw.com/article/th74.html