第十六天
集合操作:
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))