数据库入门整理-Oracle篇

• 建表语句

create table STUDENT (
ID NUMBER(8) GENERATED ALWAYS AS IDENTITY not null,-- 自增编号
constraint "UQ_STUDENT_ID" unique (ID),-- 唯一约束
XH varchar2(20),
XM varchar2(30) not null,
SEX number(1),
MOBILE number(11),
SFZX number(1),
RXSJ date,
--PRIMARY KEY ("ID", "XH") -- 创建表时指定主键
);
alter table STUDENT add primary key(XH);-- 添加主键
comment on column STUDENT.ID is '自增编号';-- 添加字段备注
comment on column STUDENT.XH is '学号';
comment on column STUDENT.XM is '姓名';
comment on column STUDENT.SEX is '性别(1:男,2:女)';
comment on column STUDENT.MOBILE is '手机号';
comment on column STUDENT.SFZX is '是否在校(0:离校, 1:在校)';
comment on column STUDENT.RXSJ is '入学时间';
comment on table STUDENT is '学生表';-- 添加表备注
alter table STUDENT add constraint CK_STUDENT_SEX check(SEX in (1,2));-- 值约束
alter table STUDENT add constraint CK_STUDENT_SFZX check(SFZX in (0,1));-- 值约束

•  更新语句

-- 表重命名
alter table STUDENT rename to STUDENT2;

-- 删除主键
alter table STUDENT2 drop primary key;

-- 添加新的主键
alter table STUDENT2 add primary key(ID,XH);

-- 修改表备注
comment on table STUDENT2 is '学生表2';

-- 字段重命名
alter table STUDENT2 rename column SEX to XB;

-- 添加唯一约束
alter table STUDENT2 add constraint UQ_STUDENT2_XB unique(XB);

-- 添加非空约束
alter table STUDENT2 modify XB not null;

-- 修改字段类型
alter table STUDENT2 modify SFZX number(1);

-- 删除值约束
alter table SDS_STUDENT2 drop constraint CK_STUDENT_SEX;

-- 修改字段备注
comment on column STUDENT2.XB is '性别(1:男, 2:女)';

• 查询当前库表操作

-- 获取当前库存在哪些表
SELECT A.TABLE_NAME, B.COMMENTS
FROM USER_TABLES A, USER_TAB_COMMENTS B
WHERE A.TABLE_NAME=B.TABLE_NAME
ORDER BY A.TABLE_NAME

-- 查询当前库是否存在表
SELECT COUNT(*) hasTable
FROM USER_TABLES
WHERE TABLE_NAME=UPPER(#{tableName})

• 查询指定表字段信息 

SELECT A.TABLE_NAME, A.COMMENTS, B.COLUMN_NAME, B.COMMENTS COLUMN_COMMENTS,
C.DATA_LENGTH, C.DATA_PRECISION, C.DATA_SCALE,
-- 判断是否自增主键
CASE WHEN C.IDENTITY_COLUMN = 'YES' THEN 'GENERATED' ELSE C.DATA_TYPE END DATA_TYPE,
-- 是否主键
(select cu.constraint_name from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name = A.TABLE_NAME and
cu.COLUMN_NAME=B.COLUMN_NAME
and au.constraint_type = 'P') PRIMARY_KEY,
-- 是否唯一
(select cu.constraint_name from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name = A.TABLE_NAME and
cu.COLUMN_NAME=B.COLUMN_NAME
and au.constraint_type = 'U') UNIQUE_KEY,
-- 是否为空
(select cu.constraint_name from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name = A.TABLE_NAME and
cu.COLUMN_NAME=B.COLUMN_NAME and au.constraint_type = 'C'
and search_condition_vc like '% NULL') NULLABLE,
-- 查询检查约束键名
(select cu.constraint_name from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name = A.TABLE_NAME and
cu.COLUMN_NAME=B.COLUMN_NAME and au.constraint_type = 'C'
and search_condition_vc not like '% NULL' and rownum=1) CONDITION_KEY,
-- 查询检查约束
(select au.search_condition_vc from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.table_name = A.TABLE_NAME and
cu.COLUMN_NAME=B.COLUMN_NAME and au.constraint_type = 'C'
and au.search_condition_vc not like '% NULL' and rownum=1) CONDITION

FROM USER_TAB_COMMENTS A,USER_COL_COMMENTS B, USER_TAB_COLS C
WHERE A.TABLE_NAME=B.TABLE_NAME AND B.TABLE_NAME=C.TABLE_NAME
AND B.COLUMN_NAME=C.COLUMN_NAME AND A.TABLE_NAME=UPPER(#{tableName})
-- ORDER BY B.COLUMN_NAME

•  获取表详细信息

-- 查询表实际占用空间
SELECT CREATED AS CREATE_TIME,
-- 查询表占用空间大小MB
( SELECT SUM(BYTES)/(1024*1024)
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'
AND SEGMENT_NAME=object_name
GROUP BY SEGMENT_NAME ) TABLE_SIZE,
-- 统计表记录数
(SELECT COUNT(1) FROM ${tableName}) TABLE_COUNT
FROM user_objects
WHERE object_name=UPPER(#{tableName})

-- 仅查询表数据的大小，不含索引、分区、LOB类型
SELECT SEGMENT_NAME TABLE_NAME, sum(bytes)/1024/1024 TABLE_SIZE
From User_Extents
Group By Segment_Name 　　
having Segment_Name=UPPER(#{tableName})