Oracle学习笔记（二）

作者: dev_winner | 来源:发表于2019-09-18 21:57 被阅读0次

Oracle数据类型：

字符型：
- 固定长度的字符类型：CHAR(n)（n最大值为2000）、NCHAR(n)（支持Unicode格式存放数据，n最大值为1000）。
- 可变长度的字符类型：VARCHAR2(n)（n最大值为4000）、NVARCHAR2(n)（支持Unicode格式存放数据，n最大值为2000）
数值型
- NUMBER(p,s)：参数p表示有效数字的位数，s表示小数点后的位数。例如：NUMBER(5,2)：表示有效数字5位，保留2位小数，如123.45
- FLOAT(n)：主要用来存储二进制数据，其能表示的二进制位数为1~126位；若将这个二进制数转换成十进制数，则需要将其乘以0.30103才能得到结果！
日期型
- DATE类型可表示范围为：公元前4712年1月1日到公元9999年12月31日；其精确到秒(s)。
- TIMESTAMP（时间戳类型）：其能精确到小数秒。
其他类型
- BLOB（Binary Large Object）：以二进制形式能够存放4GB的大对象数据。
- CLOB（Character Large Object）：以字符串形式能够存放4GB的大对象数据。
创建数据表

CREATE TABLE table_name 
(
  column_name datatype,   
  ...
)
-- 例如：创建一张userinfo表
CREATE TABLE userinfo
(
  id number(6, 0),
  username varchar2(20),
  userpwd varchar2(30),
  regdate date
);
-- 查看创建的表结构信息
DESC userinfo;

添加数据表字段

ALTER TABLE table_name ADD column_name datatype;
-- 向表userinfo添加一个字段remarks
ALTER TABLE userinfo ADD remarks varchar2(500);
-- 查看表字段
DESC userinfo;

更改数据表字段数据类型

ALTER TABLE table_name MODIFY column_name datatype;
- 例如：修改remarks长度为400
ALTER TABLE userinfo MODIFY remarks varchar2(400);

删除字段

ALTER TABLE table_name DROP COLUMN column_name;
-- 例如删除remarks字段
ALTER TABLE userinfo DROP COLUMN remarks;

修改表中字段名

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
-- 例如将userpwd重命名为new_userpwd
ALTER TABLE userinfo RENAME COLUMN userpwd TO new_userpwd;

修改表名

RENAME table_name TO new_table_name;
-- 例如：重命名表名userinfo为new_userinfo
RENAME userinfo TO new_userinfo;

删除数据表

-- 清除表中所有记录，即表被截断，其效率比delete语句快
TRUNCATE TABLE table_name;
-- 删除整张表（结构）
DROP TABLE table_name;
-- 例如删除表new_userinfo
DROP table new_userinfo;

插入数据

INSERT INTO table_name(column1, column2, ...) VALUES(value1, value2, ...);
-- 例如：向userinfo表中添加一条记录，其中sysdate是获取当前系统的日期
INSERT INTO userinfo VALUES(1, 'zhangsan', '123', sysdate);
-- 给regdate字段添加默认值sysdate
ALTER TABLE userinfo MODIFY regdate DEFAULT sysdate;

（插入）复制表数据：①在建表时复制；②在添加时复制。

CREATE TABLE table_new AS SELECT column1, ... | * FROM table_old;
-- 例如：将数据表userinfo复制为一份新表：userinfo_new
CREATE TABLE userinfo_new AS SELECT * FROM userinfo;
-- 查看新表userinfo_new结构信息
desc userinfo_new;
-- 在添加时复制
INSERT INTO table_new [(column1, ...)] SELECT column1, ... | * FROM table_old;
-- 例如：向userinfo_new表再次添加userinfo表中的所有数据
INSERT INTO userinfo_new SELECT * FROM userinfo;
-- 查看userinfo_new表中的所有记录
SELECT * FROM userinfo_new;

UPDATE语句更新数据

UPDATE table_name SET column1 = value1, ... [WHERE conditions];
-- 例如：修改username='zhangsan'的ID为1
UPDATE userinfo SET ID = 1 WHERE username='zhangsan';
SELECT * FROM userinfo;

删除记录

DELETE FROM table_name [WHERE conditions];
-- 例如：删除表userinfo_new中的所有记录
DELETE FROM userinfo_new;

查看当前数据库实例

SHOW parameter instance_name;
select name from V$database;

在创建表时设置（只能在列级设置，并且非空约束没有名称）非空约束

CREATE TABLE table_name
(  
  column_name datatype NOT NULL,
  ...
)
-- 例如：创建一张新表
CREATE TABLE userinfo_1(
id NUMBER(6, 0),
username VARCHAR2(20) NOT NULL,
userpwd VARCHAR2(20) NOT NULL
);
DESC userinfo_1;

在修改表时添加非空约束

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
-- 例如将userinfo表中username字段设置为非空约束
ALTER TABLE userinfo MODIFY username varchar2(20) NOT NULL;
DESC userinfo;

在修改表时去除非空约束

ALTER TABLE table_name MODIFY column_name datatype NULL;
-- 例如将表userinfo中username字段去除非空约束
ALTER TABLE userinfo MODIFY username VARCHAR2(20) NULL;
DESC userinfo;

主键约束的作用：确保表当中每一行数据的唯一性（字段值非空、唯一），一张表只能设置一个主键约束，主键约束可以由多个字段构成（称为联合主键或复合主键）

CREATE TABLE table_name 
(
  column_name datatype PRIMARY KEY,
  ...
  -- 下面是设置表级约束，方括号表示选择性添加
  [CONSTRAINT constraint_name PRIMARY KEY(column_name1, ...)]
)
CREATE TABLE userinfo_p
(
  id NUMBER(6, 0) PRIMARY KEY,
  username varchar2(20),
  userpwd varchar2(20)
);
desc userinfo_p;
-- 例如设置表级约束pk_id_username 
CREATE TABLE userinfo_p1
(
  id NUMBER(6, 0),
  username VARCHAR2(20),
  userpwd varchar2(20),
  -- 添加表级约束
  constraint pk_id_username primary key(id, username)
);
desc userinfo_p1;
-- 查找表创建的约束名，注意表名要大写
desc user_constraints;
SELECT CONSTRAINT_NAME FROM user_constraints WHERE TABLE_NAME='USERINFO_P1';

在修改表时添加主键约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1, ...)
-- 例如给表userinfo中id字段添加主键约束，注意此时表中要么没数据，要么记录值唯一
ALTER TABLE userinfo ADD CONSTRAINT pk_id PRIMARY KEY(id);
DESC user_constraints;
SELECT CONSTRAINT_NAME FROM user_constraints WHERE TABLE_NAME='USERINFO';

更改表中约束的名称

ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;
-- 例如：修改userinfo表中主键约束名为new_pk_id
ALTER TABLE userinfo RENAME CONSTRAINT pk_id TO new_pk_id;
SELECT CONSTRAINT_NAME FROM user_constraints WHERE TABLE_NAME='USERINFO';

禁用或者删除主键约束

-- 禁用 | 放行约束
ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name;
-- 例如禁用userinfo表中主键约束
ALTER TABLE userinfo DISABLE CONSTRAINT new_pk_id;
DESC user_constraints;
-- 查看主键约束名及其状态
SELECT CONSTRAINT_NAME, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO';
-- 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
-- 例如：删除userinfo表中的主键约束
ALTER TABLE userinfo DROP CONSTRAINT new_pk_id;
SELECT CONSTRAINT_NAME, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO';
-- 或者以这种方式直接删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY[CASCADE];
-- 例如删除userinfo_p表中的主键约束
ALTER TABLE userinfo_p DROP PRIMARY KEY;
desc userinfo_p;

在创建表时设置外键约束（也叫主从表），设置外键约束时，主表的字段必须是主键，主从表中相应的字段必须是同一数据类型，从表中外键字段的值必须来自主表中相应字段的值，或者为null值，其它值是不允许的！

-- table1为从表，table2为主表
CREATE TABLE table1
(
  column_name datatype REFERENCES table2(column_name),
  ...
);
-- 例如：创建主表typeinfo，创建从表userinfo_f，创建列级外键约束
CREATE TABLE typeinfo
(
  typeid VARCHAR2(10) primary key,
  typename VARCHAR2(20)
);
CREATE TABLE userinfo_f
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  typeid_new VARCHAR2(10) REFERENCES typeinfo(typeid)
);
INSERT INTO typeinfo values(1, 1);
INSERT INTO userinfo_f values(2, 'sam', 1);
INSERT INTO userinfo_f values(1, 'zhangsan', null);
SELECT * FROM userinfo_f;

在创建表时设置表级外键约束

CREATE TABLE table_name1
(
  ...
  CONSTRAINT constraint_name FOREIGN KEY(column_name1) REFERENCES table_name2(column_name2) [ON DELETE CASCADE];
);
-- 例如：创建userinfo_f2表级外键约束fk_typeid_new 
CREATE TABLE userinfo_f2
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  typeid_new VARCHAR2(20),
  CONSTRAINT fk_typeid_new FOREIGN KEY(typeid_new) REFERENCES typeinfo(typeid) ON DELETE CASCADE
);

在修改表时添加（表级）外键约束

ALTER TABLE table_name1 ADD CONSTRAINT constraint_name FOREIGN KEY(column_name1) REFERENCES table_name2(column_name2) [ON DELETE CASCADE];
-- 例如：先创建一张表userinfo_f4，再修改表添加外键约束fk_typeid_alter 
 CREATE TABLE userinfo_f4
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  typeid_new VARCHAR2(10)
);
ALTER TABLE userinfo_f4 ADD constraint fk_typeid_alter foreign key(typeid_new) REFERENCES typeinfo(typeid);

禁用或者删除外键约束

DESC user_constraints;
-- 注意：表名必须大写
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_F4';
-- 禁用外键约束FK_TYPEID_ALTER
ALTER TABLE userinfo_f4 disable constraint FK_TYPEID_ALTER;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_F4';
-- 删除userinfo_f4表中外键约束FK_TYPEID_ALTER
ALTER TABLE userinfo_f4 DROP constraint FK_TYPEID_ALTER;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_F4';

唯一约束的作用：保证字段值的唯一性。
唯一约束与主键约束的区别：主键字段值必须是非空的；唯一约束允许仅有一个空值；主键在每张表中只能有一个，唯一约束则可以有多个。
在列级设置唯一约束

CREATE TABLE table_name
(
  column_name datatype UNIQUE,
  ...
 )
-- 例如：创建一张新表userinfo_u，同时在创建列级唯一约束
CREATE TABLE userinfo_u
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20) UNIQUE,
  userpwd VARCHAR2(20)
);

在创建表时创建表级唯一约束，注意：一条表级约束只能创建一个字段的唯一约束，若要创建多个，则也要编写多条语句，即每一条唯一约束都只有一个名字。

CREATE TABLE table_name
(
  ...
  CONSTRAINT constraint_name UNIQUE(column_name)
)
-- 例如：创建一张新表userinfo_u1并创建一条表级约束un_username
CREATE TABLE userinfo_u1
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  constraint un_username UNIQUE(username)
);

在修改表时添加唯一约束

ALTER CONSTRAINT constraint_name UNIQUE(column_name);
-- 例如：创建一张新表userinfo_u2，然后修改表添加唯一约束
CREATE TABLE userinfo_u2
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20)
);
ALTER TABLE userinfo_u2 ADD CONSTRAINT un_username_new UNIQUE(username);

删除表中的唯一约束

-- 禁用userinfo_u2表中的唯一约束UN_USERNAME_NEW
ALTER TABLE userinfo_u2 disable constraint UN_USERNAME_NEW;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_U2';
-- 删除userinfo_u2 表中的唯一约束UN_USERNAME_NEW
ALTER TABLE userinfo_u2 DROP CONSTRAINT UN_USERNAME_NEW;

检查约束的作用：使得表中的值更具有实际意义。

CREATE TABLE table_name
(
  column_name datatype CHECK(expressions),
  ...
);
-- 例如：创建一张新表userinfo_c，并定义列级约束
CREATE TABLE userinfo_c
(
  id VARCHAR2(10) PRIMARY KEY,
  username varchar2(20),
  salary NUMBER(5, 0) CHECK(salary > 0)
);
INSERT INTO userinfo_c VALUES(1, 'AAA', -55);  -- 报错，违反检查约束
-- 设置表级检查约束
CREATE TABLE table_name
(
  column_name1 datatype1,
  ...,
  CONSTRAINT constraint_name CHECK(expressions);
);
-- 例如：创建新表userinfo_c1，并对字段salary添加表级约束ck_salary
create table userinfo_c1
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  salary NUMBER(5, 0),
  CONSTRAINT ck_salary CHECK(salary > 0)
);

在修改表时添加检查约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(experssions);
-- 例如：创建新表userinfo_c3，并修改表添加检查约束ck_salary_new
create table userinfo_c3
(
  id VARCHAR2(10) PRIMARY KEY,
  username VARCHAR2(20),
  salary NUMBER(5, 0)
);
ALTER TABLE userinfo_c3 ADD CONSTRAINT ck_salary_new CHECK(salary > 0);

禁用或删除检查约束

-- 例如：禁用userinfo_c3表中的检查约束CK_SALARY_NEW
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_C3';
ALTER TABLE userinfo_c3 DISABLE CONSTRAINT CK_SALARY_NEW;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM user_constraints WHERE TABLE_NAME='USERINFO_C3';
-- 删除userinfo_c3表中的检查约束CK_SALARY_NEW
ALTER TABLE userinfo_c3 DROP CONSTRAINT CK_SALARY_NEW;

Oracle学习笔记（二）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读