表数据的增、删、改



向表中增加数据
1、insert into emp1 ‘sysdate’或者
    values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),20000)
2、insert into emp1
    values (1002,'BB',to_date('1998-08-08','yyyy-mm-dd'),null)

3、insert into emp1(employee_id,last_name,hire_date) 注意：这里是只给部分列赋值，有非空约束的必须赋值
     values (1004,'DD',to_date('1990-08-08','yyyy-mm-dd'))
4、注意：只想赋值一部分列的话，其它必须是允许放空值的列，这里默认salary是NULL（即有非空约束的必须赋值）
insert into emp1(employee_id,last_name,hire_date)
values (1005,'EE',to_date('1996-08-08','yyyy-mm-dd'))
5、弹窗式的插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
values (&id,'&last_name','&hire_date',&salary)
基于现有表的记录插入数据
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,last_name,hire_date,salary
from employees
where department_id = 80

更新数据：
update emp1
set salary = 22000
where employee_id = 179
更新114员工的工作和工资使其与206号员工相同
1、select employee_id,job_id,salary
from employees1
where employee_id in (114,205)
2、update employees1
set job_id = (
select job_id from employees1 where employee_id = 205
),salary = (
select salary from employees1 where employee_id = 205
)
where employee_id = 114

调整与employee_id为200的员工的job_id相同的
员工的department_id为employee_id为100的员工的department_id
update employees1
set department_id = (select department_id from employees1 where employee_id = 100)
where job_id = (select job_id from employees1 where employee_id = 200)
容易出现的数据完整性的错误如：
update employees
set department_id = 55
where department_id = 100; 问题出现在表中55号部门本来就不存在
从employees表中删除departments部门名称中含有Public字符的部门id
delete from employees1
where department_id = (select department_id from departments where department_name like '%Public%')

增：
insert into ...
values(...)

insert into ...
select...from...where...
改：
update ...
set ...
where ...
删：
delete from ...
where ...

事务：
commit;
savepoint A;
rollback to savepoint A;
当用户操作表的时候，还没有commit之前，其它用户是不能够对当前的表进行操作的
更改108号员工的信息：使其工资变为所在部门中的最高工资，job变为公司中平均工资最低的 job
update employees
set salary = (select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108)
group by department_id),
job_id = (select job_id
from employees
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id )
group by job_id)
where employee_id = 108
删除108号员工所在部门中工资最低的那个员工
delete from employees
where employee_id = (
select employee_id
from employees
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id =(select department_id
from employees
where employee_id = 108)
)
)
可以优化成：
delete from employees e
where department_id = (select department_id
from employees
where employee_id = 108)
and salary = (select min(salary)
from employees
where department_id = e.department_id
)
使用约束not null和unique创建表：其中在有unique约束中给它赋值多个null，null之间是不冲突的
create table emp3(
--列级约束：
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20),
salary number(10),
--表级约束：
constraint emp3_email_uk unique(email)
)
主键约束：能够唯一的确定一条记录，同样也分表级约束和列级约束，primary key不仅是not null而且unique
create table emp4(
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)
)
或者
create table emp4(
id number(10),
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10),
constraint emp4_email_uk unique(email)，
constraint emp4_id_pk primary key（id）
)
外键约束：（注意：在emp6中插入数据的时候，不能够插入departments表中department_id没有的数据记录。另外，外键引用的列起码要有一个唯一的约束）
create table emp6(
id number(10),
name varchar2(20) constraint emp6_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp6_email_uk unique (email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
)
向表中插入departments表中存在的department_id(主键)数据
insert into emp6
values(1002,'AA',null,10000,20)

on delete set null:(级联置空：子表中相应的列置空)
on delete cascade:(级联删除：当父表中的列被删除时，子表中相对应的列也被删除)
create table emp7(
id number(10),
name varchar2(20) constraint emp7_name_nn not null,
email varchar2(20),
salary number(10),
department_id number(10),
constraint emp7_email_uk unique (email),
constraint emp7_id_pk primary key(id),
constraint emp7_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)
check约束：比如约束工资的范围
create table emp8(
id number(10),
name varchar2(20) constraint emp8_name_nn not null,
email varchar2(20),
salary number(10) constraint emp8_salary check(salary>1500 and salary<30000),
department_id number(10),
constraint emp8_email_uk unique (email),
constraint emp8_id_pk primary key(id),
constraint emp8_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)

修改约束：
添加not null约束
alter table emp5
modify (salary number(10,2) not null)
删除约束：
alter table emp5
drop constraint emp5_name_nn
添加unique约束
alter table emp5
add constraint emp5_name_uk unique(name)
无效化约束：
alter table emp3
disable constraint emp3_email_uk
激活约束：
alter table emp3
enable constraint emp3_email_uk
查询约束：（注意：其中条件里的表名要大写）
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name = 'EMPLOYEES'
查询定义有约束的列有哪些：
select constraint_name,column_name
from user_cons_columns
where table_name = 'EMPLOYEES'