17.08.01

 

集合操作

当列的数量和列的数值类型,要完全一致,

不能确定的时候,要用null和数值类型函数来确定

 

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;

 

select employee_id, job_id, salary from employees

union all

select employee_id, job_id, null from job_history;

 

select employee_id, job_id, to_char(salary) from employees

union all

select employee_id, job_id, 'no salary' from job_history;

 

集合排序:

排序写在最后,但是来自第一个查询里面列的排序

select employee_id, job_id, salary from employees

union all

select employee_id, job_id, null from job_history

order by salary;

 

select employee_id, job_id, null from job_history

union all

select employee_id, job_id, salary from employees

order by 3;

DML

insert:

SQL> create table t1(x int, y char(1), z date);

SQL> insert into t1(x, y, z) values (1, 'a', sysdate);

SQL> insert into t1(x, z, y) values (2, sysdate+1, 'b');

SQL> insert into t1(x, y, z) values (1, null, sysdate);

SQL> insert into t1(x, z) values (2, sysdate+1);

SQL> insert into t1 values (1, null, sysdate);

 

SQL> create table my_emp as select * from employees;     (只是复制了数据和基本的表结构)

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

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> insert into my_emp select * from employees;(因为表结构相同,所以可以复制数据)

例:将department_id是50的,员工姓名和工资,保存在一张新的表里。

         Create table创建空表结构,insert into插入数据

 

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;

 

子查询:

SQL> create table my_emp as select * from employees;

SQL> alter table my_emp add(department_name varchar2(30));

SQL> update my_emp outer set department_name=(select department_name from departments where department_id=outer.department_id);

 

update (select t1.department_name as aname,t2.department_name bname from my_emp t1 ,departments t2 where t1.department_id=t2.department_id) set aname=bname;

练习:

new_dept表中删除没有员工的部门

SQL> create table my_dept as select * from departments;

delete from my_dept outer

where not exists

(select 1 from my_emp

where department_id=outer.department_id);

 

deletetruncate

                                   delete                                truncate

语句类型                 dml                                     ddl (数据定义语言)

undo数据                产生大量undo数据      不产生undo数据

空间管理                 不释放                            释放

语法                          where                                删除全部数据

 

 

ACID : A原子性  C一致性  I 隔离性  D持久性)

dml 语句不自动提交,直到遇到commit 或者rollback才会提交

ddl 语句自动提交

dml 语句回滚(rollback)不能回滚到ddl语句前面

 

DML:select,insert,update,delete  (CRUD)增删查改

DDL:create,alter,drop

 

Char 定长

Varchar2 可变长

 

五种约束条件:

字段:

    Not null

    Check(salary>0)

行与行之间:

    Primary key(pk)主键约束 非空,一张表只能有一个主键

    Unique 唯一性约束 同一列唯一,允许为空,一张表可以有多个唯一性约束

表与表之间:

    Foreign key(references) 外键

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))

 

SQL> select constraint_name, constraint_type from user_constraints where table_name in('DEPT', 'EMP');

 

SQL> insert into emp values (100, 'abc', 'abc@123.com', 10000, 10);

insert into emp values (100, 'abc', 'abc@123.com', 10000, 10)

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP_DEPTNO_FK) violated - parent key not

found

 

 

SQL> insert into dept values (10, 'sales');

 

1 row created.

 

SQL> insert into dept values (10, 'market');

insert into dept values (10, 'market')

*

ERROR at line 1:

ORA-00001: unique constraint (HR.DEPT_DEPTNO_PK) violated

 

 

SQL> insert into dept values (20, 'market');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> insert into emp values (101, 'def', 'def@123.com', 10000, 20);

 

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) on delete set null)或者on delete cascade

 

SQl>  create table t1(x int constraint t1_x_pk primary key);

SQL>  alter table t1 add(y int);

SQL>  alter table t1 add(z int,a int);

SQL>  alter table t1 drop(z,a);

SQL>  alter table t1 modify(y char(1));

SQL>  alter table t1 modify(y default ‘a’);

SQL>  alter table t1 disable novalidate constraint t1_x_pk;

SQL>insert into t1 values (1, ‘a’);

SQL>insert into t1 values (1, ’b’);

SQL>alter table t1 enable validate constraint t1_x_pk;报错

SQL>delete t1;

SQL>alter table t1 enable validate constraint t1_x_pk;

SQL>drop table t1;

 

 

posted on 2017-08-19 08:46  zhang_x  阅读(110)  评论(0编辑  收藏  举报

导航