(尚硅谷) Orcal sql ddl 数据库的 创建，修改，删除

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