<span style="font-size:18px;">-----------
创建和管理表
select * from user_tables;
select table_name from user_tables
select * from user_objects
select * from user_catalog;
--创建表(create table 表名)
--第一种方式:白手起家
create table emp1(
id number(5),
name varchar(20),
salary number(8,2),
hire_date Date
)
select * from emp1;
--第二种方法:依托于已经存在的类
create table emp2
as
select employee_id , last_name ,
hire_date , salary
from employees;
select * from emp2;
create table emp3
as
select employee_id , last_name , hire_date , salary
from employees
--where department_id = 90 ;
where 1=2
select * from emp3
create table emp4
as
select employee_id id , last_name , hire_date , salary sal
from employees
where department_id = 90;
select * from emp4;
--4对employee这个表进行一个复制,但是数据不需要
create table emp5
as
select * from employees
where 1=2;
select * from emp5;
--修改表
--添加一个列
create table emp1(
id number(5),
name varchar(20),
salary number(8,2),
hire_date Date
)
select * from emp1;
alter table emp1
add(department_id number(5));
alter table emp1
add(email varchar2(20))
--2.2修改现有的列
alter table emp1
modify(salary number(10 ,2 )default 200)
--2.3重命名现有的列
alter table emp1
rename column salary to sal
--2.4删除现有列
alter table emp1
drop column department_id
--3重命名一个表(rename to )
rename emp1 to emp;
--4清空表
create table emp2
as
select * from employees
select * from emp2
truncate table emp2
--删除数据
create table emp2
as
select * from employees
delete from emp2
rollback
truncate table emp2
--5删除表
drop table emp
-------------测 试--------------
--创建dept
create table dept(
id number(7),
name varchar2(25)
)
--将表departments中的数据插入表dept1中
create table dept1
as
select * from departments
select * from dept1
--将列Last_name的长度增加到50
alter table dept
modify(id number(5))
--根据表employees创建employees2
create table employees2
as
select * from employes;
--删除表emp
drop table dept1
--将表employees2重命名为emp
rename dept1 to emp
--在表dept和emp中添加新列id,
--并检查所作的操作
alter table dept
add(test_coumn varchar2(10))
--在表dept和emp中将列test_column设置成不可用,
--之后删除
alter table dept
set unused column id
alter table dept
Drop unused columns
--直接删除表emp中的列 dept_id
alter table emp
drop column dept_id
</span>
(尚硅谷) Orcal sql ddl 数据库的 创建,修改,删除
最新推荐文章于 2021-03-10 17:03:14 发布