Oracle 学习语法记录

语法记录

系统用户权限

• sys
• system
• sysman 用于操作企业管理器
• scott Oracle 创始人的名字,默认密码是: tiger

用户登陆语法

# 如果是管理员登陆，使用 as sysdba 或者 sysoper
[username/password] [@server] [as sysdba|sysoper]

使用其他用户登陆

如果 username 是 sys，必须使用 as 子句
CONNECT username/password [AS sysdba|syoper]

查看登陆的用户

show user

数据字典：相当于系统表

dba_users

查看所有用户

SELECT USERNAME FROM dba_users;

解锁用户

ALTER user username account UNLOCK

[例]:启动 scott 用户

ALTER USER scott ACCOUNT UNLOCK

锁住用户

alter user username account lock

使用指定用户登陆数据库，默认密码为 : tiger

connect username/password

表空间

理解表空间

数据库和表空间的关系

表空间是数据库的逻辑存储空间，可以理解为在数据库中开辟的一个空间用于存放数据库对象。一个数据库中有多个表空间。很多优化都是基于表空间。

表空间和数据文件

表空间实际上是由一个或多个数据文件构成的，数据文件的位置和大小由用户定义，数据库，表都是存储在数据文件中的。

表空间的分类

• 永久表空间 存储表，视图，存储过程
• 临时表空间 存放数据库操作当中，执行的过程，不进行永久的保存
• UNDO表空间 用于保存事务所修改的旧数据，也就是被修改之前的数据，用于对数据进行回滚

查看表空间字段

# dba_tablespaces  系统管理员使用的表空间数据字典
# user_tablespaces 普通用户表空间数据字典
desc {dba_tablespaces|user_tablespaces}

查看用户

# dba_users  系统管理员使用的用户数据字典
# user_users 普通用户用户数据字典
DESC dba_users | user_users
SELECT * FROM dbs_users

查看数据文件的字段

desc dba_data_files

创建表空间

CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SiZE xx

修改表空间

• 修改 system 用户默认表空间的语句

ALTER USER SYSTEM DEFAULT TABLESPACE xxx

• 修改表空间的状态

  • 设置表空间的脱机或联机状态

  ALTER TABLESPACE tablespace_name ONLINE|OFFLINE
  

  • 设置只读或可读写状态(修改此属性，必须是联机状态)(默认读写状态)

  ALTER TABLESPACE tablespace_name READ ONLY|READ WRITE
  

• 修改数据文件

  • 向表空间添加数据文件

  ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx
  

  • 向表空间删除数据文件

  ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf'
  

  [注意]：只能删除表空间中非第一个创建的数据文件，如果想删除全部的数据文件，只能删除表空间

• 删除表空间

DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]

[注意]：如果只删除表空间而不删除数据文件，不要加上[INCLUDING CONTENTS]

Orable 数据类型

• 字符型
  • CHAR(n)
  • NCHAR(n)
  • VARCHAR2(n)
  • NVARCHAR2(n) NVARCHAR2用于存储Unicode格式的数据，更适合存储中文数据
• 日期型
  • DATE 表示范围：公元前4712年1月1日到公元9999年12月31日，精确到秒
  • TIMESTAMP
• 数值型
  • NUMBER(n, s)
    • n：有效数字位数
    • s：小数点后的位数
  • FLOAT(n) 存放的是二进制的数据，n 表示二进制的位数为 1-126位，如果将其转化位十进制需要乘于 0.30103
• 其他数据类型
  • BLOB 最大：4G，以字节存放
  • CLOB 最大：4G，以字符存放

修改表

• 添加字段

ALTER TABLE table_name ADD column_name datatype

• 修改字段

ALTER TABLE table_name 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

• sysdate 系统当前时间

• 复制表中的数据

  • 在建表时复制

  CREATE TABLE table_name AS SELECT * FROM table_old
  

  • 在插入数据时复制

  INSERT INTO table_new[(column1, column2, ...)] SELECT * FROM table_old
  

约束

约束数据字典

user_constraints

• 非空约束(只能在列级添加约束)

  • 在创建表时设置非空约束

  CREATE TABLE table_name (column_name DATATYPE NOT NULL)
  

  • 在修改表时添加非空约束

  ALTER TABLE MODIFY column_name NOT NULL
  

  • 在修改表时去除非空约束

  ALTER TABLE MODIFY column_name NULL
  

• 主键约束(确保表当中每一行数据是唯一的)

  • 查询约束的名字
    利用到 users_constraint

  SELECT constraint_name FROM users_constraint WHERE table_name='[要大写]'
  

  • 在创建表时设置主键约束

  CREATE TABLE table_name (column_name DATATYPE PRIMARY KEY,...)
  

  • 置于所有的字段创建完毕后，添加主键约束

  CONSTRAINT primary_key_name PRIMARY KEY(column_name1, column_name2,...)
  

  • 在修改表时添加主键约束

  ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1, ...)
  

  • 更改约束的名称

  RENAME CONSTRAINT old_name TO new_name
  

  • 禁用或启用约束

  DIABLE|ENABLE CONSTRAINT constraint_name
  

  • 删除约束

  DROP CONSTRAINT constraint_name
  

  • 通过删除主键的方式来删除主键约束

  # CASCADE 级联的意思
  DROP PRIMARY KEY[CASCADE]
  

• 外键约束

  • 创建表的时候添加外键约束(列级)

  column_name datatype REFERENCES tablename(column_name)
  

  • 创建表的时候添加外键约束(表级)

  CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES tablename(column_name)[ON DELETE CASCADE]
  

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

  ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES tablename(column_name)[ON DELETE CASCADE]
  

  • 更改约束的名称

  RENAME CONSTRAINT old_name TO new_name
  

  • 禁用或启用约束

  DIABLE|ENABLE CONSTRAINT constraint_name
  

  • 外键约束

  DROP CONSTRAINT constraint_name
  

• 唯一约束(允许有一个 null 值)

  • 在创建表的时候创建唯一约束(列级)

  DATATYPE UNIQUE
  

  • 在创建表的时候创建唯一约束(表级)

  CONSTRAINT constraint_name UNIQUE(column_name)
  

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

  ADD CONSTRAINT constraint_name UNIQUE(column_name)
  

  • 更改约束的名称

  RENAME CONSTRAINT old_name TO new_name
  

  • 禁用或启用约束

  DIABLE|ENABLE CONSTRAINT constraint_name
  

  • 外键约束

  DROP CONSTRAINT constraint_name
  

• 检查约束

  • 在创建表的时候创建检查约束(列级)

  CHECK(expression)
  

  • 在创建表的时候创建检查约束(列级)

  CONSTRAINT constraint_name CHECK(expression)
  

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

  ADD CONSTRAINT connect_name CHECK(expression)
  

  • 更改约束的名称

  RENAME CONSTRAINT old_name TO new_name
  

  • 禁用或启用约束

  DIABLE|ENABLE CONSTRAINT constraint_name
  

  • 外键约束

  DROP CONSTRAINT constraint_name
  

查询

• 基本查询语句

SELECT [DISTINCT] cplumn_name...
FROM table_name
[WHERE conditions]

• 在 SQL*PLUS 中设置格式

# 改变显示的名字
COLUMN column_name heading new_name
# 改变显示的格式
COLUMN column_name FORMAT dataformat
# 清除格式
COLUMN column_name CLEAR

• 例:讲字符串补充为指定长度的字符穿

COLUMN column_name FORMAT a10;

• 例:数字格式 一个 9 表示一个数字，如果不足位数，显示 #

COLUMN column_name FORMAT a10;

• 查询表中的所有字段及特定字段

# 查询全部字段
SELECT * FROM table_name
# 查询指定字段
SELECT username, password FROM table_name

• 给字段设置别名

SELECT column_name AS new_name FROM table_name

• 运算符和表达式

• 在 SELECT 中使用运算符
• 带条件的查询
• 模糊查询

# _ 单个通配符
# * 任意通配符
SELECT column_name1, column_name2 FROM table_name WHERE column_name LIKE '_%'

• 范围查询

• 对查询结果排序

• case…when 语句的使用

  • 方法一

  CASE column_name
  WHEN vlaue THEN result,...
  [ELSE result] END
  

  • 方法二

  CASE
  WHEN column_name = value THEN result, ...
  [ELSE result] END
  
  

• decode 函数的使用

DECODE(column_name, value1, result1, value2, result2, ..., default_value)