Oracle学习

最新推荐文章于 2023-03-03 20:42:22 发布

置顶 Adopat

最新推荐文章于 2023-03-03 20:42:22 发布

阅读量253

点赞数

分类专栏：数据库文章标签： Oracle入门到精通

本文链接：https://blog.csdn.net/weixin_44446122/article/details/103328948

版权

数据库专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Oracle学习

Oracle数据库的配置

oracle的五个服务

OracleDbConsoleorcl(企业管理服务，B/S结构的管理界面)
OrcleJobScheduleorcl(日程调度，自动化任务)
oracleOraDb11g_home1iSQL*Plus(B/S结构的SQLPLUS工具)
OracleRemExecService(基本服务，一切服务的基础，必须启动) 快捷启动的命令：net start oracleserviceorcl；关闭的命令：net stop oracleserviceorcl
OracleOraDb11g_home1TNSListener(监听服务，负责监听客户端连接服务器，必须启动)快捷启动监听服务的命令：lsnrctl start;关闭的命令：lsnrctl stop

用户与表空间

登录SQLPLUS

sqlplus /nolog

系统用户：

sys:orcl
system:orcl
sysman:orcl
scott:tiger

使用系统用户登录

使用system用户登录：[username/password][@server][as sysdba|sysoper]

产看登录用户

show user;
dba_users:数据字典；
select name from dba_users;

启用scott用户

两种方法解锁：

安装时候在口令管理中解锁scott用户
使用启用用户的命令：alter user username account unlock;

表空间

表空间概述

表空间与数据库的关系：一个数据库可以由多个表空间组成
表空间与数据文件的关系;一个表空间由多个数据文件构成，数据文件的大小由用户自己定义
表空间的分类
1. 永久表空间：表，视图，存储过程
2. 临时表空间：中间执行的过程
3. UNDO表空间：被修改之前的数据，用于数据回滚

查看用户的表空间

dba_tablespaces(系统用户登录查看的字典),user_tablespaces（普通和用户登录查看的字典）
dba_users;user_users数据字典

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

ALTER USER username DEFAULT |TEMPORARY TABLESOPACE tablespace_name

创建，修改，删除表空间

创建永久表空间：

创建临时表空间：

创建永久表空间：
create tablespace test1_tablespace
datafile 'test1file.dbf' size 10m;

查看表空间：
desc dba_data_files;
查看创建永久表空间的文件路径：
select file_name from dba_data_files where tablespace_name=‘TEST1_TABLESPACE’;
创建临时表空间：
create temporary tablespace temptest1_tablespace
tempfile ‘tempfile1.dbf’ size 10m;
查看创建临时表空间的文件路径：
select file_name from dba_temp_files where tablespace_name=‘TEMPTEST1_TABLESPACE’


- 修改表空间的状态

1. 设置联机状态或者脱机状(默认情况下，创建的表空间市处于联机的状态)

   ```
   ALTER TABLESPACE tablespace_name
   ONLINE|OFFLINE;
   将test1_tablespace设置为脱机状态:
   ALTER TABLESPACE test1_tablespace
   offline;
   产看表空间的状态：
   select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
   ```

2. 设置表空间只读或者可读写的状态

   ```
   ALTER TABLESPACE tablespace_name
   READ ONLY|READ WRITE;
   产看表空间的读写状态(默认状态下的表空间的读写状态为 read write)：
   ALTER TABLESPACE test1_tablespace read only;
   产看表空间的读写状态：
   select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE';
   ```

3. 修改表空间的数据文件

   - 增加数据文件

     ```
     ALTER TABLESPACE tablespace_name
     ADD DATAFILE 'XX.dbf' size xx;
     向test1_tablespace表空间增加数据文件：
     ALTER TABLESPACE test1_tablespace
     ADD DATAFILE 'test2_file.dbf' size 10m;
     查看增加后的数据文件：
     select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE';
     ```

   - 删除数据文件

     ```
     ALTER TABLESPACE tablespace_name
     DROP  DATAFILE 'filename.dbf'
     ```

4. 删除表空间：

   ```
   DROP TABLESPACE tablespace_name[INCLUDING CONTENTS]
   ```

### 实例表空间schema用户的理解

- 实例：实例是访问oracle数据库所需要的一部分计算机内存和辅助处理的后台进程，是由这些进程和这些进程所使用的内存（SGA）所构成的一个集合，一个数据库可以有多个实例

- 表空间：一个数据库实例可以有多个表空间，一个表空间有N个表，我们也可以称作为租户

- schema:在物理结构来说表空间和schema是一样的，schema(对象)，存储着表，索引，视图，触发器等。

- 用户：用户我们在实际企业中就是schema，对表的独占性

用一个比喻来结合这些东西，方便理解：
解释数据库，表空间，数据文件，表，数据，用一个物流仓库来理解：
我们可以把数据库比作是一个大仓库，仓库这么大不方便管理，为了方便管理我们把仓库划分成一个一个小的空间，称作表空间，这每一个空间都需要人来管理，操作和使用这些表空间的人称为用户，有了这么大一个空间我们可以放任何东西，如表，视图，索引，触发器等，这些集合起来统称为对象(schema),我们平时要使用的都是这些对象，那么怎么区别这些不同表空间的对象呢？我们就以用户名来区分，所以才有在企业中schema就是用户的原因，




## 表的操作

### 认识表

- 基本存储单位
- 二维结构（行记录，列域或者字段）
- 约定：
- 每一列的数据必须具有相同的数据类型型
- 列名唯一
- 每一行的数据的唯一性

### 数据类型

- 字符型
- CHAR(n)n最大2000，NCHAR(n) n最大1000，一般用NCHAR(n)存储汉字;固定长度的类型
- VARCHAR2(n)4000;NVARCHAR2(n)2000;可变长度的字符的类型
- 数值型
- NUMBER(p,s):p代表有效的数字，s代表小数点后的位数
- FLOAT(n)
- 日期型
- DATE(DATE类型表示的范围公元前4712年1月1日到公元9999年12月31日)，精确到秒
- TIMESTAMP,精确到小数秒
- 其他类型
- BLOG(4GB,二进制)
- CLOG(4GB,字符串)

### 管理表

- 创建表

基本语法：
CREATE TABLE table_name(
column_name datatype,);
创建用户表：
create table userinfo(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
email varchar2(30),
regdate= date);


- 修改表

添加字段：
ALTER TABLE table_name
ADD column_name datatype;
更改字段的数据类型：
ALTER TABLE table_namae
MODIFY column_name datatype;
删除字段：
ALTER TABLE table_name
DROP COLUMN column_name;
修改字段名：
ALTER TABLE table_name
rename COLUMN column_name to new_column_name;
修改表名：
RENAME table_name to new_table_name;


- 删除表

TRUNCATE table table_name;
DROP TABLE table_name;


### 操作表中的数据

- 添加数据

INSERT INTO table_name(column1,column2) values(value1,value2);
向表中所有字段添加值：
insert into userinfo values(1,‘xxx’,‘123’,‘xxxx@126.com’,sysdate,‘xxxxx’);
向表中指定的字段添加值：
insert into userinfo (id,new_userpwd) values(values1,values2);
向表中添加默认值：
ALTER TABLE table_name
MODIFY column_name datatype default;


- 修改数据

- UPDATE语句修改数据

  ```
  UPDATE table_name
  SET column1=value1..
  WHERE conditions
  ```

- 删除数据

- DELETE语句进行删除数据

  ```
  DELETE FROM table_name
  [WHERE condition]
  ```

- 复制表数据

- 创建表的时候复制数据

  ```
  CREATE TABLE new_table
  as
  SELECT column1,.... from table_old
  ```

- 添加数据的时候复制数据也叫蠕虫复制

  ```
  INSERT INTO table_new
  column1...
  SELECT column1,... |* from table_old;
  注意不加条件是进行无条件的进行更新
  ```

## 约束

### 约束的作用

- 定义规则
- 确保完整性

### 非空约束

- 在创建表的时候添加非空约束

CREATE TABLE table_name(column_name datatype NOT NULL,…);
CREATE TABLE userinf0_1(id number(6,0),
username varchar2(20) not null,
userpwd varchar2(20) not null);


- 在修改表的时候添加非空约束

ALTER TABLE table_name
MODIFY column_name datatype NOT NULL;


- 在修改表的时候去除非空约束

ALTER TABLE table_name
MODIFY column_name datatype null;


### 主键约束

- 作用：确保表中每一行的数据的为一性，非空，唯一，一个表只能设计一个主键约束，但是主键约束可以由多个字段构成

- 在创建表时设置你主键约束

第一种方法：
CREATE TABLE table_name(column_name datatype PRIMARY KEY,…);
Exmple:
CREATE TABLE userinfo(id number(6,0) primary key,username varchar2(20),
userpwd varchar2(20));
第二种方法(表级约束)：
CONSTRAINT constraint_name
PRIMARY KEY(column_name1,…)
示例：
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 dba_constraintS where table_name=‘USERINFO’;


- 在修改时添加主键约束

ADD CONSTAINT constraint_name PRIMARY KEY(column_name1,…);
示例：
ALTER TABLE userinfo
ADD CONSTRAINT pk_id primary key(id);


- 更改约束的名称

ALTER TABLE table_name
RENAME CONSTRAINT old_name to new_name;
示例;
ALTER TANLE userinfo
RENAME CONSTRAINT pk_id to new_pk_id;
更改完查询：
SELECT CONSTRAINT FROM dba_constraints where table_name=‘USERINFO’;


- 删除主键约束

禁用约束：
ALTER TANLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name;
示例：
ALTER TABLE userinfo
disable constraint pk_id;
禁用后查看状态：
SELECT CONSTRAINT_NAME,STATUS FROM dba_constraints where table_name=‘USERINFO’;
删除约束:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
删除主键约束：
ALTER TABLE table_name
DROP PRIMARY KEY[CASCADE];


### 外键约束

- 在创建表的时候设置外键约束

列级设置外键约束：
CREATE TABLE table_name(column_name datatype REFERENCES table2(column_name),…);
注意主表中的字段必须要是主键，而且主从表中相应的字段必须要是同一数据类型；从表中外键字段的值必须来自主表中相对应的字段的值或者时NULL值
示例：
1,创建主表
create table typeinfo(typeid varchar2(10) primary key,
typename varchar2(20));
2.创建从表
create table userinfo(id varchar2(20) primary key,
username varchar2(20),
typeid_new varchar2(20) refrences typeinfo(typeid));
表级设置外键约束：
CONSTRAINT constraint_name FOREIGN KEY(column_name)
REFERENCES table_name(column_name)[ON DELETE CASCADE]
示例：
create table userinfo(id varchar2(20) primary key,
username varchar2(20),
typeid_new varchar2(20),
constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid) on delete cascade);
create table typeinfo(typeid varchar2(20) primary key,
username varchar2(20));


- 在修改表的时候添加外键约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY(column_name)
REFERENCES table_name(column_name) [ON DELETE CASCADE];
示例：
create table uerinfo(id varchar2(20) primary key,
username varchar2(20),
typeid_new varchar2(20));
ALTER TABLE userinfo
ADD CONSTRAINT fk_typeid_new foreign key(type_id_new) references typeinfo(typeid);


- 删除外键约束

禁用外键约束：
ALTER TABLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name;
查看约束的名字：
select constraint_name,status,constraint_type from dba_constraints
where table_name=‘USERINFO’;
删除外键约束：
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;


### 唯一约束

- 作用：设置了唯一性的字段，保证数据的唯一性

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

- 在创建表的时候设置唯一约束

列级设置唯一约束：
CREATE TABLE table_name(column_name datatype UNIQUE);
示例：
create table userinfo_u(
id varchar2(10) primary key,
username varchar2(20) unique,
userpwd varchar2(20));
表级设置唯一约束：
CONSTRAINT constraint_name UNIQUE(column_name);
注意如果要创建多个唯一性约束，要重复上面的语句，因为唯一性约束的名字不应该是相同的
示例：
creaate table userinfo_u1(
id varchaar2(10) primary key,
username varchar2(20),
constraint un_username unique(username));


- 在修改表的时候添加唯一约束

语法格式：
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column_name);
示例：
create table userinfo(
id varchar2(10) primary key,
username varchar2(20));
ALTER TABLE userinfo
ADD CONSTRAINT un_username unique(username);


- 删除唯一约束

禁用唯一约束：
ALTER TABLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name;
如果忘记约束的名字，可以通过以下语句查看约束的状态，类型，名字：
select constraint_name,constraint_type,status from dba_constraints
where table_name=‘USERINFO’;
彻底删除唯一性约束：
ALTER TABLE table_name
DROP COONSTRAINT constraint_name;


### 检查约束

- 作用：是表中的数据更具有实际意义

- 在创建表的时候设置检查约束

列级设置检查约束：
CREATE TABLE table_name(column_name datatype CHECK(expressions));
示例：
create table userinfo_c(
id varchar2(10) primary key,
username varchar2(20) unique,
salary number(5,0) check(salary>0));
表级设置检查约束：
CONSTRAINT constraint_name
CHECK(expression);
示例：
create table userinfo_c1(
id varchar2(10) primary key,
username varchar2(20) unque,
salary number(5,0),
constraint ck_salary check(salary>0));


- 在修改表的时候添加检查约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK(expression);
示例：
create table userinfo_c2(
id varchar2(10) primary key,
username varchar2(20) unique,
salary number(5,0));
alter table userinfo_c2
add constraint ck_salary check(salary>0);


- 删除检查约束

禁用检查约束：
ALTER TABLE table_name
DISABLE|ENABLE CONSTRAINT constraint_name;
彻底删除检查约束：
ALTER TABLE table_name
DROP CONSTRAINT constrant_name;


### 约束的总结：

- ```
非空约束
主键约束：每个表只能由一个主键约束，主键约束可以由多个字段组成
外键约束：涉及两个表的约束
唯一性约束
检查约束

Oracle查询

基本查询语句

基本格式

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

在SQL*PLUS中设置格式

在SQL*PLUS中格式的设置

列名的更改：
COLUMN column_name HEADING new_name;(注意COLUMN可以简写成COL)
示例：
col username heading 用户名；
select * from userinfo_c;
设置显示的格式：
COLUMN column_name FORMAT dataformat;(字符型用a表示，数值类型用9代表一个数字)
示例：
col usernmame format a10;//将username字段长度改为10
col salary format 9999.9;//保留4为整数一位小数
此外还可以设置美元符号
col salary format $9999.9;
清除格式设置：
COLUMN column_name CLEAR;

查询表中的所有字段及指定字段

查询所有字段和指定的字段

select * from table_name;
select column_name1,column_name2,.. from table_name

给字段设置别名

语法格式

SELECT column_name as new name
FROM table_name;
注意AS可以省略，用一个空格代替，但是不建议这样操作
示例：
select id as 编号,username as 用户名,salary 工资
from userinfo_c;
注意这里的中文字符不需要使用单引号，这里跟MySQL不同，在Oracle中单引号表示原样输出

运算符和表达式

语法格式

表达式=操作数+运算符
Oracle中的操作树可以有常量，变量和字段
算术运算符:(+,_,*,/)
比较运算符:(>,>=,<,<=,=,<>)用于where条件
逻辑运算符(and,or,not)，逻辑运算符的运行优先级not>and>or,注意比较运算符的优先级高于逻辑运算符

在SELECT 语句中使用运算符

语法格式

示例：
select id,username,salary+200
from userinfo_c;

带条件的查询

单一条件的查询
带多个条件的查询(使用逻辑运算符连接)

模糊查询

关键字:LIKE

通配符的使用(_,%)
一个_代表一个字符
%可以代表0到多个字符

范围查询

关键字：BETWEEN…AND(闭区间)

slect * from userinfo_c where salary between 800 and 2000;

关键字：IN/NOT IN

select * from userinfo_c where username in('aaa','bbb');

对查询后的结果排序

关键字ORDER BY

SELECT....FROM....[WHERE]
ORDER BY column1 DESC/ASC,...

case…when语句的使用

语法结构：

CASE...WHEN的第一种使用方法
CASE column_name WHEN value1 THEN result1,...
[ELSE result] END
示例：
select username,case username when 'aaa' then '计算机部门'
when 'bbb' then '市场部门'
else '其他部门' end as 部门
from users;
CASE...WHEN的第二种使用方法：
CASE
WHEN column_name=value1
THEN result1,...[ELSE result] END
示例：
select username,case when username='aaa' then '计算机部门'
when username='bbb' then '市场部门'
else '其他部门'
end as 部门
from users;

decode函数的使用

语法结构

decode(culumn_name,value1,result1,...,defaultvalue)
示例：
select username,decode(username,'aaa','计算机部门','bbb',市场部门','其他')as 部门
from users;

Adopat

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Oracle学习

Orcale学习Orcale数据库的配置oracle的五个服务OracleDbConsoleorcl(企业管理服务，B/S结构的管理界面)OrcleJobScheduleorcl(日程调度，自动化任务)oracleOraDb11g_home1iSQL*Plus(B/S结构的SQLPLUS工具)OracleRemExecService(基本服务，一切服务的基础，必须启动) 快捷启动的命...
复制链接

扫一扫