第十六天

集合操作：

select employee_id, job_id from employees

union all

select employee_id, job_id from job_history; // 两个查询取交集

 

select employee_id, job_id from employees

union

select employee_id, job_id from job_history;

 

select employee_id, job_id from employees

intersect

select employee_id, job_id from job_history;

 

select employee_id from employees

minus

select employee_id from job_history;

 

DML （增删改） 可以回滚

SQL> create table my_emp as select * from employees; //快速复制一张已有的表，只有本身数据，没有附加结构。

SQL> create table avg_sal as select department_id, avg(salary) avg_sal from employees group by department_id;

SQL> create table my_emp as select * from employees where 1=0; //复制一张空的表结构，没有数据。

SQL> create table my_emp as select last_name, salary from employees where department_id=50;

// 把select查询的结果插入新建立的表中。

SQL> insert into my_emp select * from employees;  

 

update:

SQL> update my_emp set salary=salary*1.1;

SQL> update my_emp set salary=salary*1.1 where department_id=50;

SQL> update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;

 

delete:

SQL> delete from my_emp where employee_id=197;

SQL> delete from my_emp where department_id=50;

SQL> delete from my_emp;

 

DDL （创建、删除、修改表） 不可回滚

字符串：

SQL> create table t1(x char(10), y varchar2(10));

SQL> insert into t1 values('x', 'y');

SQL> select dump(x), dump(y) from t1;

数值：

SQL> create table t1(x number(5,2), y number(5));

SQL> insert into t1 values (123.45, 12345);

SQL> insert into t1 values (12.345, 12345);

SQL> insert into t1 values (12.345, 123.45);

SQL> select * from t1;

SQL> insert into t1 values (12.345, 112345);

日期时间：

SQL> create table t1(a date, b timestamp, c timestamp with time zone, d timestamp with local time zone);

SQL> insert into t1 values (sysdate, systimestamp, systimestamp, systimestamp);

SQL> alter session set time_zone='+9:00';

SQL> select * from t1;

修改表结构：

SQL> alter table t1 add(e char(10));

SQL> alter table t1 drop(e);

SQL> alter table t1 modify(d not null);

 

约束条件：

字段（列）：not null, check(salary>0)

行与行：primary key, unique

表与表之间：foreign key

例  ：

create table dept (

    deptno int constraint dept_deptno_pk primary key,

    dname varchar2(20) constraint dept_dname_nn not null);

 

create table emp (

    empno int constraint emp_empno_pk primary key,

    ename varchar2(20) constraint emp_ename_nn not null,

    email varchar2(50) constraint emp_email_uq unique,

    salary int constraint emp_salary_ck check(salary>0),

    deptno int constraint emp_deptno_fk references dept(deptno))