oracle基本操作语句

where e.department_id = d.department_id and e.department_id = 11;
create or replace view emp_13_dept_temp as select d.department_name, d.manager_id, e.employee_name,
alter table employee_13_temp add constraint emp_13_pri primary key(employee_id);
alter table Байду номын сангаасmployee_13_temp modify(salary not null);
--删除约束
alter table employee_13_temp drop constaint emp_13_pri;
update department_13 set manager_id=130 where department_id > 14;
savepoint undo1;
delete from department_13 where department_id > 14;
savepoint undo2;
constraint emp_13_foreign foreign key(department_id)
references department_13(department_id));
--check约束
varchar(25),
manager_id number(6), location_id number(4));
--创建唯一性约束(同时也是表级约束)
create table department_13(department_id number(6), department_name varchar(25),
constraint dep_id_13_pri primary key(department_id));
--创建外键约束
create table employee_13(employee_id number(6), employee_name varchar(25), email varchar(28), hire_date
when matched then
update set
a.department_name = b.department_name,
a.manager_id = b.manager_id,
a.location_id = b.location_id
when not matched then
date,
job_id varchar(20), salary number(8,2),commission_pct number(2,2),
manager_id number(6), department_id number(6),
--创建序列
create sequence hospital_id
minvalue 1
maxvalue 999999999999
start with 11
increment by 1
cache 10;
--创建视图
create or replace view emp_13_11 as select employee_id, employee_name, salary, job_id from employee_13
manager_id number(6), location_id number(4),
constraint dep_id_13_uni unique(department_id));
--创建主键约束
--提交事务
update department_13 set manager_id = 120 where department_id = 14;
commit;
--察看自动提交环境变量
show autocommit;
--打开自动提交
set autocommit on;
--savepoint
--删除被外键参照的主键约束
alter table department_13 drop primary key cascade;
--手工创建索引
create Index emp_13_sal on employee_13(salary);
--删除索引
drop index emp_13_sal;
--创建一个同义词
create synonym ct from System.emp_13_dept_temp;
--删除同义词
drop synonym ct
/*
*数据操作语句(操作表的数据)
*/
--Insert语句
insert into department_13 values(13,'测试部',120,119);
oracle基本操作语句 收藏
/*
*数据定义语句(只操作表的结构)
*/
--创建表
--创建部门表
create table department_13(
department_id number(6),
department_name varchar(25),
manager_id number(6),
select rownum, employee_name, salary from (select employee_name, salary from employee_13 order by
salary desc) where rownum <=3;
select * from (select employee_name, salary from employee_13 order by salary desc) where rownum <=3;
insert(a.department_id, a.department_name, a.manager_id, a.location_id)
values(b.department_id, b.department_name, b.manager_id, b.location_id);
--对空值的引用
select employee_name, salary, salary*(1+commission_pct) "奖金" from employee_13;
create table employee_13_temp(employee_id number(6), employee_name varchar(25), email varchar(28),
hire_date date,
job_id varchar(20), salary number(8,2),commission_pct number(2,2),
manager_id number(6), department_id number(6),
constraint emp_sal_min check(salary > 8888));
--增加约束
e.salary from employee_13 e, department_13 d
where e.department_id = d.department_id;
--删除视图
drop view emp_13_11;
--查找出薪水最高的三个员工的信息(Top-N分析法):使用到了行内视图
location_id number(4)
);
--使用子查询创建表
create table department_13_temp as select department_id, department_name from department_13;
--修改表
--增加字段
alter table department_13_temp add(manager_id number(6));
where department_id = 11;
create or replace view emp_13_dept as select d.department_name, d.manager_id, e.employee_name, e.salary
from employee_13 e, department_13 d
--修改字段大小
alter table department_13_temp modify(manager_id number(4));
--删除表
drop table department_13_temp;
--数据字典表
select table_name from dba_tables;
--删除字段
alter table department_13_temp drop column manager_id;
--修改字段名称
alter table 表名 rename column 原列名 to 新列名;
--修改字段类型
alter table department_13_temp modify(manager_id varchar(6));
rollback to undo1;
--SELECT语句
--带算书表达式的select语句
select employee_id, employee_name, salary, salary*12 from employee_13;