对表
常用的alter语句:
ALTER TABLE (表名) ADD (列名 数据类型);
ALTER TABLE (表名) MODIFY (列名 数据类型);
ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名);
ALTER TABLE (表名) DROP COLUMN (列名);
ALTER TABLE (当前表名) RENAME TO (新表名);
//建测试表
create table dept(
deptno number(3) primary key,
dname varchar2(10),
loc varchar2(13)
);
create table employee_info(
empno number(3),
deptno number(3),
ename varchar2(10),
sex char(1),
phone number(11),
address varchar2(50),
introduce varchar2(100)
);
–
//1.重命名
//1.1 表:
rename dept to dt;
rename dt to dept;
//1.2 列:
alter table dept rename column loc to location;
alter table dept rename column location to loc;
//2.添加约束
//2.1 primary key
alter table employee_info add constraint pk_emp_info primary key(empno);
//2.2 foreign key
alter table employee_info add constraint fk_emp_info foreign key(deptno)
references dept(deptno);
//2.3 check
alter table employee_info add constraint ck_emp_info check (sex in ('F','M'));
//2.4 not null
alter table employee_info modify phone constraint not_null_emp_info not null;
//1.5 unique
alter table employee_info add constraint uq_emp_info unique(phone);
//1.6 default
alter table employee_info modify sex char(2) default 'M';
//3.列
//3.1 添加列
alter table employee_info add id varchar2(18);
alter table employee_info add hiredate date default sysdate not null;
//3.2 删除列
alter table employee_info drop column introduce;
//3.3 修改列
//3.3.1 修改列的长度
alter table dept modify loc varchar2(50);
//3.3.2 修改列的精度
alter table employee_info modify empno number(2);
//3.3.3 修改列的数据类型
alter table employee_info modify sex char(2);
//3.3.4 修改默认值
alter table employee_info modify hiredate default sysdate+1;
//4. 约束
//41. 禁用约束
alter table employee_info disable constraint uq_emp_info;
//4.2 启用约束
alter table employee_info enable constraint uq_emp_info;
//4.3 延迟约束
alter table employee_info drop constraint fk_emp_info;
alter table employee_info add constraint fk_emp_info foreign key(deptno)
references dept(deptno) deferrable initially deferred;
//5. 注释
//5.1 向表中添加注释
comment on table employee_info is 'information of employees';
//5.2 向列添加注释
comment on column employee_info.ename is 'the name of employees';
comment on column dept.dname is 'the name of department';
//6 表
//6.1 清除表中所有数据
truncate table employee_info;
//6.2 删除表
drop table employee_info;
–
//下面来看看刚刚才我们对表dept和表employee_info所做的更改
//user_constraints视图里面包含了刚刚创建的所有约束,以及其它信息
//可以使用desc user_constraints 命令查看其详细说明
[oracle@HXQ-CMS-TEST ~]$ sqlplus amsshorcl_test
SQL*Plus: Release 11.2.0.4.0 Production on 1 17:34:31 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select constraint_name,constraint_type,status,deferrable,deferred from user_constraints where table_name = 'EMPLOYEE_INFO';
CONSTRAINT_NAME C STATUS DEFERRABLE DEFERRED
------------------------------ - -------- -------------- ---------
PK_EMP_INFO P ENABLED NOT DEFERRABLE IMMEDIATE
FK_EMP_INFO R ENABLED NOT DEFERRABLE IMMEDIATE
CK_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
NOT_NULL_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
UQ_EMP_INFO U ENABLED NOT DEFERRABLE IMMEDIATE
SQL>
//可以通过user_cons_columns视图查看有关列的约束信息;
column column_name format a15;
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'EMPLOYEE_INFO';
OWNER CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
AMSSHORCL_TEST PK_EMP_INFO
EMPLOYEE_INFO EMPNO
AMSSHORCL_TEST FK_EMP_INFO
EMPLOYEE_INFO DEPTNO
AMSSHORCL_TEST CK_EMP_INFO
EMPLOYEE_INFO SEX
OWNER CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
AMSSHORCL_TEST NOT_NULL_EMP_INFO
EMPLOYEE_INFO PHONE
AMSSHORCL_TEST UQ_EMP_INFO
EMPLOYEE_INFO PHONE
//将user_constraints视图与user_cons_columns视图连接起来
//查看约束都指向那些列
SQL> column column_name format a15;
SQL> select ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
2 from user_constraints uc, user_cons_columns ucc
3 where uc.table_name = ucc.table_name
4 and uc.constraint_name = ucc.constraint_name
5 and ucc.table_name = 'EMPLOYEE_INFO';
COLUMN_NAME CONSTRAINT_NAME C STATUS
--------------- ------------------------------ - --------
EMPNO PK_EMP_INFO P ENABLED
DEPTNO FK_EMP_INFO R ENABLED
SEX CK_EMP_INFO C ENABLED
PHONE NOT_NULL_EMP_INFO C ENABLED
PHONE UQ_EMP_INFO U ENABLED
SQL>
//这里有个constraint_type,它具体指下面几种类型:
//C:check,not null
//P:primari key
//R:foreign key
//U:unique
//V:check option
//O:read only
–
//可以通过user_tab_comments 视图获得对表的注释;
select * from user_tab_comments where table_name=‘EMPLOYEE_INFO’;
SQL> select * from user_tab_comments where table_name='EMPLOYEE_INFO';
TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO TABLE
information of employees
//可以通过user_col_commnets 视图获得对表列的注释;
SQL> select * from user_col_comments where table_name = 'EMPLOYEE_INFO';
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO EMPNO
EMPLOYEE_INFO DEPTNO
EMPLOYEE_INFO ENAME
the name of employees
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO SEX
EMPLOYEE_INFO PHONE
EMPLOYEE_INFO ADDRESS
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO INTRODUCE
7 rows selected.
SQL> select * from user_col_comments where table_name = 'EMPLOYEE_INFO' and comments is not null;
TABLE_NAME COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO ENAME
the name of employees
//最后看一下修改后的表:
SQL> desc employee_info;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(3)
DEPTNO NUMBER(3)
ENAME VARCHAR2(10)
SEX CHAR(2)
PHONE NOT NULL NUMBER(11)
ADDRESS VARCHAR2(50)
INTRODUCE VARCHAR2(100)
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(3)
DNAME VARCHAR2(10)
LOC VARCHAR2(13)
查询表字段
-- user_tab_cols 包含oracle创建的隐藏字段
SELECT column_name FROM user_tab_cols where table_name = upper('表名')
-- user_tab_columns 自定义的字段
SELECT column_name FROM user_tab_columns where table_name = upper('表名')
将表所有列名查出,并拼成字符串
```sql
select Listagg(column_name, ',') WITHIN GROUP(ORDER BY column_name)
from user_tab_columns
where table_name = upper('表名')
--不想查询的字段名
and column_name not in ('字段名','字段名');