Oracle 入门

发表于 | 更新于 | 分类于 | 阅读次数：

Oracle 入门学习

Oracle 官网

官网下载网址 https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Database Control URL

https://localhost:1158/em

系统用户

  • sys,system
  • sysman
  • scott - 默认密码是 tiger
使用系统用户登录
1
2
3
[username/password][@server][as sysdba|sysoper]

system/root @orcl as sysdba (orcl 就是自己设置的服务名)

使用 system 用户登录

1
2
3
请输入用户名：system/root

connect sys/root as sysdba;
查看登录用户
1
show user
启用 scott 用户
1
2
3
ALTER user username account unlock;

connect scott/tiger

数据字典

dba_users 系统用户
user_users 普通用户
dba_tablespaces 系统管理员级别的用户对应的表空间（表）

  • SYSTEM（存储 system 用户的表、视图以及存储过程等数据库对象，称为系统表空间）
  • SYSAUX（EXAMPLE 的辅助表空间）
  • UNDOTBS1（存储撤销信息的，属于 UNDO 类型的表空间）
  • TEMP（存储 SQL 语句处理的表和索引信息，属于临时表空间）
  • USERS（存储数据库用户创建的数据库对象，永久性表空间）
  • EXAMPLE（用于安装 Oracle 11g 数据库示例来使用的表空间）
    user_tablespaces 普通用户对应的表空间（表）
    dba_data_files 表空间文件数据字典
    dba_temp_files 临时表空间文件数据字典
    user_constraints 约束的数据字典

表空间

数据库的逻辑存储空间，在数据库中开辟的空间用于存储数据库中的对象。
表空间是由一个或多个数据文件构成的。

分类

  • 永久表空间（存储表、视图等）
  • 临时表空间（存储数据库操作过程中，中间执行的过程，执行结束后会自动释放）
  • UNDO 表空间（用于保存事务所修改数据的旧值，即被修改之前的数据）

设置用户的默认/临时表空间

1
2
3
4
ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name;

eg:
ALTER USER system DEFAULT TABLESPACE system;

创建表空间/临时表空间

1
2
3
4
5
CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xxx.dbf' SIZE xx;

eg:
CREATE TABLESPACE test1_tablespace DATAFILE 'test1file.dbf' SIZE 10m;
CREATE TEMPORARY TABLESPACE temp1_tablespace TEMPFILE 'temp1file.dbf' SIZE 10m;

查看表空间文件具体路径

1
2
3
SELECT file_name,tablespace_name FROM dba_data_files;

SELECT file_name FROM dba_data_files WHERE tablespace_name = 'TEST1_TABLESPACE';

修改表空间状态

设置联机或脱机状态
1
2
3
4
5
6
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;

eg:
ALTER TABLESPACE test1_tablespace OFFLINE;
ALTER TABLESPACE test1_tablespace ONLINE;
SELECT status FROM dba_tablespace WHERE tablespace_name = 'TEST1_TABLESPACE';
设置只读或读写状态

不管是修改成只读还是读写状态，表空间的状态必须是联机状态，即 status = ONLINE。

1
2
3
4
5
6
ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE;

eg:
ALTER TABLESPACE test1_tablespace READ ONLY;
SELECT status FROM dba_tablespace WHERE tablespace_name = 'TEST1_TABLESPACE';
ALTER TABLESPACE test1_tablespace READ WRITE;

表空间数据文件

增加数据文件
1
2
3
4
5
ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx;

eg:
ALTER TABLESPACE test1_tablespace ADD DATAFILE 'test2file.dbf' SIZE 10m;
SELECT file_name FROM dba_data_files WHERE tablespace_name = 'TEST1_TABLESPACE';
删除数据文件

不能删除表空间创建时第一个数据文件，如果要删除需要把整个表空间删除。

1
2
3
4
5
ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf';

eg:
ALTER TABLESPACE test1_tablespace DROP DATAFILE 'test2file.dbf';
SELECT file_name FROM dba_data_files WHERE tablespace_name = 'TEST1_TABLESPACE';

删除表空间

1
2
3
4
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS];

eg:
DROP TABLESPACE test1_tablespace INCLUDING CONTENTS;

表

数据类型

字符型
  • CHAR(N)
    MAX(N) = 2000
  • NCHAR(N)
    MAX(N) = 1000
    支持按照 unicode 编码方式来存放数据的，一般情况下用 NCHAR 来存储汉字比较多的
  • VARCHAR2(N)
    MAX(N) = 4000
    可变长度的数据类型
  • NVARCHAR2(N)
    MAX(N) = 2000
    支持按照 unicode 编码方式来存放数据的
数值型
  • NUMBER(p,s)
    p 有效数字；s 小数点后的位数
    eg: NUMBER(5,2) 有效数字5位，保留2位小数，如123.45
  • FLOAT(N)
    主要用来存储二进制数据的，能表示的二进制 1-126位
日期型
  • DATE
    DATE 类型表示范围：
    公元前4712年1月1日到公元9999年12月31日 可以精确到秒
  • TIMESTAMP
    时间戳类型，可以精确到小数秒
其他类型
  • BLOB
    可以存放4G的数据，以二进制的形式来存放
  • CLOB
    可以存放4G的数据，以字符串的形式来存放

管理表

创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE table_name
{
	column_name datatype, ...
};

eg:
CREATE TABLE userinfo
(
	id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    email varchar2(30),
    regdate date
);

eg:
CREATE TABLE userinfo2
(
	id number(6,0),
    regdate date default sysdate
);

desc userinfo;
添加字段
1
2
3
4
ALTER TABLE table_name ADD column_name datatype;

eg:
ALTER TABLE userinfo ADD remark varchar2(500);
更改字段数据类型
1
2
3
4
5
ALTER TABLE table_name MODIFY column_name datatype;

eg:
ALTER TABLE userinfo MODIFY remark varchar2(400);
ALTER TABLE userinfo MODIFY userpwd number(6,0);
删除字段
1
2
3
4
ALTER TABLE table_name DROP COLUMN column_name;

eg:
ALTER TABLE userinfo DROP COLUMN remark;
修改字段名
1
2
3
4
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

eg:
ALTER TABLE userinfo RENAME COLUMN email TO usermail;
修改表名
1
2
3
4
RENAME table_name TO new_table_name;

eg:
RENAME userinfo TO user_info;
删除表
删除表中数据
1
2
3
4
TRUNCATE TABLE table_name;

eg:
TRUNCATE TABLE user_info;
删除表结构和表中数据
1
2
3
4
DROP TABLE table_name;

eg:
DROP TABLE user_info;

操作表数据

添加数据

如果把表中所有字段都插入数据，那么 table_name 后的列名可以省略，同时 value 值需要与每个字段的数据类型一一匹配。

1
2
3
4
5
INSERT INTO table_name(column1,column2,...) VALUES(value1,value2,...);

eg:
INSERT INTO userinfo VALUES(1,'zhangshengjian','root','2695118008@qq.com',sysdate);
INSERT INTO userinfo(id,username) VALUES(2,'zsj');
复制数据
在创建表时复制表数据
1
2
3
4
5
CREATE TABLE table_new AS SELECT column1,... | * FROM table_old;

eg:
CREATE TABLE userinfo_new AS SELECT * FROM userinfo;
CREATE TABLE userinfo_new1 AS SELECT id,username FROM userinfo;
在添加数据时复制表数据
1
2
3
4
5
INSERT INTO table_new [(column1,...)] SELECT column1,... | * FROM table_old;

eg:
INSERT INTO userinfo_new SELECT * FROM userinfo;
INSERT INTO userinfo_new(id,username) SELECT id,username FROM userinfo;
修改数据
1
2
3
4
5
6
UPDATE table_name SET column1=value1,... [WHERE conditions];

eg:
UPDATE userinfo SET userpwd = '111111';
UPDATE userinfo SET userpwd = '222222',email = '111@163.com';
UPDATE userinfo SET userpwd = '333333',email = '333@163.com' WHERE id = 1;
删除数据
1
2
3
4
5
DELETE FROM table_name [WHERE conditions];

eg:
DELETE FROM userinfo;
DELETE FROM userinfo WHERE id = 1;

约束

非空约束

在创建表时设置非空约束
1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name(
	column_name datatype NOT NULL,...
);

eg:
CREATE TABLE userinfo(
	id number(6,0),
    username varchar2(20) not null,
    userpwd varchar2(20) not null
);
在修改表时添加非空约束
1
2
3
4
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

eg:
ALTER TABLE userinfo MODIFY username varchar2(20) NOT NULL;
在修改表时去除非空约束
1
2
3
4
ALTER TABLE table_name MODIFY column_name varchar2(20) NULL;

eg:
ALTER TABLE userinfo MODIFY username varchar2(20) NULL;

主键约束

在创建表时设置主键约束

一张表只能设计一个主键约束。
主键约束可以由多个字段构成（联合主键或复合主键）。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name(
	column_name datatype PRIMARY KEY,...
);

eg:
CREATE TABLE userinfo(
	id number(6,0) primary key,
    username varchar2(20),
    userpwd varchar2(20)
);
1
2
3
4
5
6
7
8
9
10
11
CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);

eg:
CREATE TABLE userinfo(
	id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    CONSTRAINT pk_id_username PRIMARY KEY(id,username)
);

SELECT constraint_name FROM user_constraints WHERE table_name = 'USERINFO';
在修改表时添加主键约束
1
2
3
4
5
6
ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);

eg:
ALTER TABLE userinfo ADD CONSTRAINT pk_id PRIMARY KEY(id);

SELECT constraint_name FROM user_constraints WHERE table_name = 'USERINFO';
更改约束的名称
1
2
3
4
5
6
RENAME CONSTRAINT old_name TO new_name;

eg:
ALTER TABLE userinfo RENAME CONSTRAINT pk_id TO new_pk_id;

SELECT constraint_name FROM user_constraints WHERE table_name = 'USERINFO';
删除主键约束
禁用主键约束
1
2
3
4
5
6
DISABLE|ENABLE CONSTRAINT constraint_name;

eg:
ALTER TABLE userinfo DISABLE CONSTRAINT new_pk_id;

SELECT constraint_name,status FROM user_constraints WHERE table_name = 'USERINFO';
删除主键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP CONSTRAINT constraint_name;

OR

DROP PRIMARY KEY [CASCADE];

eg:
ALTER TABLE userinfo DROP CONSTRAINT new_pk_id;

SELECT constraint_name,status FROM user_constraints WHERE table_name = 'USERINFO';

OR

ALTER TABLE userinfo DROP PRIMARY KEY;

外键约束

在创建表时设置外键约束
列级外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE table1(
	column_name datatype REFERENCES table2(column_name),...
);

eg:
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(id,typeid_new) VALUES(1,1);
INSERT INTO userinfo_f(id,typeid_new) VALUES(1,NULL);

// 违反完整约束条件 - 未找到父项关键字
INSERT INTO userinfo_f(id,typeid_new) VALUES(1,2);

table1 一般称为从表，table2 一般称为主表，外键约束一般称为主从表的关系。
设置外键约束时，主表的字段必须是主键。
主从表中相应的字段必须是同一个数据类型。
从表中外键字段的值必须来自主表中的相应字段的值，或者为 null 值。

表级外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table_name(column_name) [ON DELETE CASCADE];

eg:
CREATE TABLE userinfo_f2(
	id varchar2(10) primary key,
    username varchar2(20),
    typeid_new varchar2(10),
    CONSTRAINT fk_typeid_new FOREIGN KEY(typeid_new) REFERENCES typeinfo(typeid)
);

CREATE TABLE userinfo_f3(
	id varchar2(10) primary key,
    username varchar2(20),
    typeid_new varchar2(10),
    CONSTRAINT fk_typeid_new1 FOREIGN KEY(typeid_new) REFERENCES typeinfo(typeid)
    ON DELETE CASCADE
);
在修改表时设置外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name(column_name) [ON DELETE CASCADE];

eg:
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);

ALTER TABLE userinfo_f4
ADD CONSTRAINT fk_typeid_alter FOREIGN KEY(typeid_new) REFERENCES typeinfo(typeid)
ON DELETE CASCADE;
删除外键约束
禁用外键约束
1
2
3
4
5
6
DISABLE|ENABLE CONSTRAINT constraint_name;

eg:
SELECT constraint_name,constraint_type,status FROM user_constraints WHERE table_name = 'USERINFO_F4';

ALTER TABLE userinfo_f4 DISABLE CONSTRAINT FK_TYPEID_ALTER;
删除外键约束
1
2
3
4
5
6
DROP CONSTRAINT constraint_name;

eg:
ALTER TABLE userinfo_f4 DROP CONSTRAINT FK_TYPEID_ALTER;

SELECT constraint_name,constraint_type,status FROM user_constraints WHERE table_name = 'USERINFO_F4';

唯一约束

在创建表时设置唯一约束

唯一约束和主键约束的区别：
主键字段值必须是非空的，唯一约束允许有一个空值。
主键在每张表中只能有一个，唯一约束在每张表中可以有多个。

列级唯一约束
1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name(
	column_name datatype UNIQUE,...
);

eg:
CREATE TABLE userinfo_u(
	id varchar2(10) PRIMARY KEY,
    username varchar2(20) UNIQUE,
    userpwd varchar2(20)
);
表级唯一约束
1
2
3
4
5
6
7
8
CONSTRAINT constraint_name UNIQUE(column_name);

eg:
CREATE TABLE userinfo_u1(
	id varchar2(10) PRIMARY KEY,
    username varchar2(20),
    CONSTRAINT un_username UNIQUE(username)
);
在修改表时设置唯一约束
1
2
3
4
5
6
7
8
9
ADD CONSTRAINT constraint_name UNIQUE(column_name);

eg:
CREATE TABLE userinfo_u2(
	id varchar2(10) primary key,
    username varchar2(20)
);

ALTER TABLE userinfo_u2 ADD CONSTRAINT un_username_new UNIQUE(username);
删除唯一约束
禁用唯一约束
1
2
3
4
5
DISABLE|ENABLE CONSTRAINT constraint_name;

eg:
ALTER TABLE userinfo_u2 DISABLE CONSTRAINT UN_USERNAME_NEW;
SELECT constraint_name,constraint_type,status FROM user_constraints WHERE table_name='USERINFO_U2';
删除唯一约束
1
DROP CONSTRAINT constraint_name;

检查约束

在创建表时设置检查约束
列级检查约束
1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name(
	column_name datatype CHECK(expressions),...
);

eg:
CREATE TABLE userinfo_c(
	id varchar2(10) PRIMARY KEY,
    username varchar2(20),
    salary number(5,0) CHECK(salary>0)
);
表级检查约束
1
2
3
4
5
6
7
8
9
CONSTRAINT constraint_name CHECK(expressions);

eg:
CREATE TABLE userinfo_c1(
	id varchar2(10) PRIMARY KEY,
    username varchar2(20),
    salary number(5,0),
    CONSTRAINT ck_salary CHECK(salary>0)
);
在修改表时设置检查约束
1
2
3
4
5
6
7
8
9
10
ADD CONSTRAINT constraint_name CHECK(expressions);

eg:
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);
删除检查约束
禁用检查约束
1
2
3
4
DISABLE|ENABLE CONSTRAINT constraint_name;

eg:
ALTER TABLE userinfo_c3 DISABLE CONSTRAINT CK_SALARY_NEW;
删除检查约束
1
2
3
4
DROP CONSTRAINT constraint_name;

eg:
ALTER TABLE userinfo_c3 DROP CONSTRAINT CK_SALARY_NEW;

查询语句

基本查询语句

1
SELECT [DISTINCT] column_name1,...|* FROM table_name [WHERE conditions];

SQL*PLUS 设置

设置用户名
1
2
3
4
5
6
COLUMN column_name HEADING new_name;

eg:
COL username HEADING 用户名;
COLUMN username HEADING 用户名;
SELECT * FROM users;
设置结果格式
1
2
3
4
5
6
7
8
9
COLUMN column_name FORMAT dataformat;

eg:
// 设置字符型格式用 "a"
COL username FORMAT a10;
SELECT * FROM users;
// 设置数字格式用 "9"，如果设置的长度超过了数据的长度用 "#" 代替
COL salary FORMAT 9999.9;
COL salary FORMAT $9999.9;

注：字符类型只能设置显示的长度。

清除格式
1
2
COLUMN column_name CLEAR;
COL column_name CLEAR;

给字段设置别名

1
SELECT column_name AS new_name,... FROM table_name;

注：AS 可以省略，有空格隔开原来的字段名和新字段名即可。

运算符和表达式

算术运算符 +, -, *, /
1
SELECT id,username,salary+200 FROM users;
比较运算符 >, >=, <, <=, =, <>
1
SELECT username FROM users WHERE salary>800;

比较运算符的优先级高于逻辑运算符

逻辑运算符 and, or, not
1
2
SELECT username FROM users WHERE salary>800 AND salary<>1888.5;
SELECT * FROM users WHERE NOT(username='aaa');

逻辑运算符的优先级：按 not、and、or 的顺序依次递减

模糊查询

通配符的使用（_ , %）

一个 _ 只能代表一个字符
% 可以代表0到多个任意字符

1
2
3
SELECT * FROM users WHERE username like 'a%';
SELECT * FROM users WHERE username like '_a%';
SELECT * FROM users WHERE username like '%a%';

范围查询

1
2
3
4
5
6
7
8
9
SELECT * FROM users WHERE salary>=800 AND salary<=2000;

SELECT * FROM users WHERE salary BETWEEN 800 AND 2000;

SELECT * FROM users WHERE salary NOT BETWEEN 800 AND 2000;

SELECT * FROM users WHERE username IN('AAA','BBB');

SELECT * FROM users WHERE username NOT IN('AAA','BBB');

结果排序

1
2
3
4
5
SELECT ... FROM ... [WHERE ...] ORDER BY column1 DESC/ASC, ...

eg:
SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users ORDER BY username DESC, salary ASC;

CASE … WHEN 语句的使用

1
2
3
4
CASE column_name WHEN value1 THEN result1, ... [ELSE result] END

eg:
SELECT username, CASE username WHEN 'aaa' THEN '计算机部门' WHEN 'bbb' THEN '市场部门' ELSE '其他部门' END AS 部门 FROM users;
1
2
3
4
5
6
CASE WHEN column_name=value1 THEN result1, ... [ELSE result] END

eg:
SELECT username, CASE WHEN username='aaa' THEN '计算机部门' WHEN username='bbb' THEN '市场部门' ELSE '其他部门' END AS 部门 FROM users;

SELECT username, CASE WHEN salary<800 THEN '工资低' WHEN salary>5000 THEN '工资高' END AS 工资水平 FROM users;

decode 函数的使用

1
2
3
4
decode(column_name, value1, result1, ..., defaultvalue)

eg:
SELECT username,decode(username,'aaa','计算机部门','bbb','市场部门','其他') AS 部门 FROM users;
本文结束啦 感谢您阅读
如果你觉得这篇文章对你有用，欢迎赞赏哦~
0%