DML：select  insert  update  delete（CRUD）

Union和unionall 是重复值的区别  unionall是把所有重复值全保留下来，union是把重复值只保留一个。

Intersect交集。两个表都有，重复的部分。

Minus集合的差。

集合查询时，列的数量和数值类型是一样的。

集合排序时，order by必须写在最后。

Insert不管怎么变化，values只代表一行数据。

update更新是说更新列的值

Transactions（交易）的几个特性：

A原子性

C一致性

I隔离性

D持久性

Ddl不会改变数据库内部结构。比如：create  alter   drop  

命名必须以字母开头，列名也必须以字母开头，数据库里合法的字符集都可以作为对象

字符类型：

Char定长

Varchar2 可变长度

约束：

字段：not null（非空）   check（检查约束）   

行与行之间：primarykey （PK）主键（非空而且唯一）  unique 唯一性约束（所有的行在一列上必须是唯一的，可以为空）

表与表之间：

Foreign key（references）  外键：确保用户所写的编号是真实存在的

集合操作

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;

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

delete和truncate：

delete            truncate

语句类型 dml            ddl

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

空间管理                  不释放            释放

语法 where            删除全部数据（回车自动生效，不能恢复）

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

instead of trigger视图触发器

序列：

SQL> create sequence test_seq increment by 1 start with 1 maxvalue 1000 nocycle cache 20;

SQL> create table t1(x int primary key, y int);

SQL> insert into t1 values (test_seq.nextval, 11); 反复执行

SQL> select * from t1;

索引：

主键和唯一性约束自动创建索引：

SQL> select constraint_name, constraint_type from user_constraints where table_name=‘EMPLOYEES‘;

SQL> select index_name, index_type from user_indexes where table_name=‘EMPLOYEES‘;

SQL> set autot on

SQL> select last_name from employees where employee_id=100; 走索引

SQL> select email from employees; 走索引

SQL> select last_name from employees where salary=2100; 全表扫描

SQL> create index emp_salary_ix on employees(salary);

SQL> select last_name from employees where salary=2100; 走索引

SQL> set autot off

8月1