Oracle笔记14——Oracle创建与维护表

Oracle中数据库对象命名原则

• 必须由字母开始,长度在1-30个字符之间。

• 名字中只能包含A-z, a-z，0-9，_(下划线)，$和#。

• 同一个Oracle服务器用户所拥有的对象名字不能重复。

• 名字不能为Oracle的保留字。

• 名字是大小写不敏感

 

一、数据类型

1、字符型

CHAR(size)：固定长度，例如手机号等VARCHAR2(size)：可变长度，例如备注等
CLOB：可变长度字符数据，最大可存储4G数据

2、数值类型（数值型）

NUMBER：数值型，既可以表示整数，也可以表示小数
NUMBER(n)：整数
NUMBER(P.S)：数值型，总长度p，小数位最大为s位

3、日期型

DATE：包括年月日时分秒
TIMESTAMP：精度比DATE高一些，可以准确到毫秒

4、图片类型

BLOB：可以存放声音、图片、文件，最大可存储4G二进制的数据

 

二、创建表

1.一般语法

CREATE TABLE 表名(列名 列类型[长度] [DEFAULT 默认值][,........]);

2.子查询创建表

CREATE TABLE 表名[列名1,列名2....] AS 子查询;

1.创建表person,并指定以下列的信息：
列名            列数据类型   长度   精度  默认值
id             number         4
cname          varchar2       20
birthday       date
height         number         3
weight         number         5     2
country_code   char           2             '01'   --一个中文占2个字节   英文与数字分别占1个字节

--创建表
create table person(
       id number(4),
       cname varchar2(20),
       birthday date,
       height number(3),
       weight number(5,2),
       country_code char(2) default '01'
);

--创建表
CREATE TABLE person(
       id NUMBER(4),
       cname VARCHAR2(20),
       birthday DATE,
       height NUMBER(3),
       weight NUMBER(5,2),
       country_code CHAR(2) DEFAULT '01'
);
--默认值使用
--插入默认值
INSERT INTO person(id,cname,birthday,height,weight) VALUES(1000,'剑法',SYSDATE,52,7);
SELECT * FROM person;

--使用默认值修改
INSERT INTO person(id,cname,birthday,height,weight,country_code) VALUES(2000,'宝宝',SYSDATE,52,7,'10');
UPDATE person SET country_code = DEFAULT WHERE id = 2000;


--用子查询语法创建表
--语法：CREATE TABLE 表名[(列名1, 列名2, 列名3)] AS 子查询;
1.创建表dept_10,用于保存部门10的员工的所有信息,表结构与emp表一致
CREATE TABLE dept_10 AS SELECT * FROM emp WHERE deptno = 10;                               --复制所有列

2.创建表dept_20,用于保存部门20的员工的雇员编号、雇员姓名、经理,表结构信息参照emp表
CREATE TABLE dept_20(empno,ename,mgr) AS SELECT empno,ename,mgr FROM emp WHERE deptno = 20;--复制指定列

--在sys用户下引用scott用户的表
SELECT * FROM scott.emp;

 

三、修改表（添加列、修改列、删除列）

1.添加列：

ALTER TABLE 表名 ADD (列名 列类型[长度] [default 默认值]);

2.修改列：

ALTER TABLE 表名 MODIFY (列名 列类型[长度] [default 默认值]);

3.删除列(删除单列)：

ALTER TABLE 表名 DROP COLUMN 列名;

4.删除列(删除多列)：

ALTER TABLE 表名 DROP (列名1,列名2.....);

5.删除表：

DROP TABLE 表名;

6.重命名表名：

RENAME 旧表名 TO 新表名;

7.截断表：

TRUNCATE TABLE 表名;

8.截断表TRUNCATE与删除表数据DELETE的区别

TRUNCATE属于DDL语言，删除表中所有的数据，释放存储空间，不可以撤回数据
DELETE属于DML语言，可以删除指定的数据，不释放存储空间，可以通过ROLLBACK回滚数据

9.修改列注意：

1）修改数据类型：已有的行数据必须为空。
2）修改长度原则：
数值型修改长度：当长度向小改时，已有行的数该列必须为空；
当长度向大改时，可以随意修改。
字符型修改长度：当长度向小改时，只要修改后的值能容纳下当前已有数据的最大值即可，
当长度向大改时，可以随意修改。
3） 修改列的默认值：
默认值的修改不会影响已经存在的行，只影响新增加的行
4）修改列名：
ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名;

10.删除列注意：

1）列可以有也可以没有数据。
2）表中至少保留一列。
3）列被删除后，不能再恢复。
4）被外键引用的列，不能被删除。

11.数据字典

查询当前用户下的所有表格：

SELECT * FROM user_tables;

--添加列
1.给person表添加性别列:sex char(2)，并添加一条数据
ALTER TABLE person ADD sex char(2); 
INSERT INTO person(id,cname,birthday,height,weight,sex) VALUES(3000,'宝宝3号',SYSDATE,52,7,'男');

--修改列
1.将person表的sex的长度修改为char(1)
ALTER TABLE person MODIFY sex char(2);--当长度向小改时，已有行的数该列必须为空
2.将person表的sex列的数据类型修改为number(2)
ALTER TABLE person MODIFY sex number(2);--修改数据类型：已有的行数据必须为空
3.将person表的country_code的默认值修改为'88'，并添加一条数据
ALTER TABLE person MODIFY country_code DEFAULT '88'; --默认值的修改不会影响已经存在的行，只影响新增加的行
INSERT INTO person(id,cname,birthday,height,weight,sex) VALUES(3001,'宝宝4号',SYSDATE,52,7,'男');
4.将person表的sex的列名修改为sexName
ALTER TABLE person RENAME COLUMN sex TO sexName;

--删除列
1.将person表的sexName列删除
ALTER TABLE person DROP COLUMN sexName;--要删除的列可以有也可以没有数据。
2.将person表的height、weight列删除
ALTER TABLE person DROP (height, weight);--删除多列
3.将dept表的deptno列删除
ALTER TABLE dept DROP COLUMN deptno;--被外键引用的列，不能被删除。

--删除表
1.删除表person
DROP TABLE person;

--重命名表
1.将person表表名修改为dossier
RENAME person TO dossier;

--截断表
1.截断表dossier
SELECT * FROM dossier;
INSERT INTO dossier VALUES(1001, 'NIHAO', TO_DATE('1999-9-4','YYYY-MM-DD'), 150, 25, 02);

TRUNCATE TABLE dossier;--切断表dossier，不可撤回数据
DELETE FROM dossier;--删除dossier表中所有数据，可以回滚数据


--数据字典
SELECT * FROM user_tables;--查询当前用户下的所有表格