oracle基础笔记-常用命令

登录

• 登录名/密码（不区分大小写）
• connect 用户/用户名

数据字典：

dba_tablespaces 针对系统管理员级别
user_tablespaces 针对普通用户查看
dba_users 针对系统管理员
user_users 针对普通用户

---

常用命令

-系统命令

• 获取当前系统日期
  sysdate

• 显示当前用户
  show users

• 显示数据字典的数据字段
  desc dba_users

• 启用用户的语句
  ALTER USER username ACCOUNT unlock；

• 设置用户的默认或临时表空间
  ALTER USER username
  default|temporary TABLESPACE tablespace_name

• 创建表空间 括号内表示临时表空间
  CREATE [temporary] TABLESPACE tablespace_name tempfile|datafile ‘xx.dbf’ size xx

• 删除表空间(包括数据文件)
  DROP TABLESPACE tablespace_name [Including contents]

• 设置联机或脱机状态
  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 ‘xx.dbf’

---

-表操作

• 创建表

  CREATE TABLE table_name
  (
  column_name datatype,…
  )

• 修改表结构

  ALTER TABLE table_name 要对任何表进行操作前需要先加上这句
  ADD column_name datatype; 添加字段
  MODIFY column_name datatype; 更改字段类型
  DROP COLUMN column_name ; 删除字段
  RENAME COLUMN column_name TO new_column_name; 修改字段名字
  RENAME table_name TO new_table_name;修改表名

• 删除表

  TRUNCAT TABLE table_name 只删除表的数据
  DROP TABLE table_name 连表结构一起删除

• 添加数据

  INSERT INTO table_name (column1,column2,…) VALUES(values1,values2,…)

• 为字段添加默认值

  • 在创建字段的时候添加即在该字段后加上 default 默认值
  • 表已经创建则用
    ALTER table 表名
    MODIFY 字段名 default 默认值

• 在建表时复制数据
  CREATE TABLE table_newname
  AS
  SELECT column1,…|* FROM table_old

• 在对表添加时复制

  INSERT INTO table_new
  [(column1,…)]
  SELECT column1,…|*FROM table_old

• 修改表数据(where 表示条件)

  UPDATE table_name
  SET column1=value1,…[WHERE connditions]

• 删除数据（以行删除）没有截断表的效率高

  DELETE FROM table_name [WHERE conditions]

---

-约束

• 查询已创建的约束
  desc user_constraints

非空约束

• 在创建表时设置：
  在对应字段类型后加上 NOT NULL

• 表已创建，修改表（但是要在表中没有空值时才能成功）：

  alter table table_name
  modify column_name datatype not null;

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

  ALTER TABLE table_name
  MODIFY column_name datatype NULL;

主键约束(唯一，非空)

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

  • 在字段类型后加上(一个) PRIMARY KEY
  • 多个，放在所有字段设置之后
    CONSTRAINT constraint_name PRIMARY KEY(column_name1,…)

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

  alter table table_name
  ADD CONSTRAINT constraint_name
  PRIMARY KEY(column_name1,…);

• 更改约束的名称

  alter table table_name
  RENAME CONSTRAINT old_name TO new_name

• 删除主键约束

  • 启用/禁用：
    alter table table_name
    DISABLE|ENABLE CONSTRAINT constraint_name
  • 删除 :
    ①alter table table_name
    DROP CONSTRAINT constraint_name
    ②([]内代表级联删除)
    alter table table_name
    DROP PRIMARY KEY[CASCADE]

---

外键约束

• 在创建表时设置外键约束(后者是主表且主表的字段必须是主键，且在从表插入数据时外键的值要跟主表一样或为空值)

  • 在字段类型后加上 REFERENCES table2(column_name)
    -在所有字段创建完之后
    CONSTRAINT constraint_name FOREIGN
    KEY(column_name) REFERENCES
    table_name(column_name)[ON DELETE CASCADE]

• 在修改表时添加外键约束([]内表示级联删除)

  alter table table_name
  ADD CONSTRAINT constraint_name FOREIGN
  KEY(column_name) REFERENCES
  table_name(column_name)[ON DELETE CASCADE]

• 删除外键约束

  • 启用/禁用：
    alter table table_name
    DISABLE|ENABLE CONSTRAINT constraint_name
  • 删除 :
    alter table table_name
    DROP CONSTRAINT constraint_name

---

唯一约束(允许有空值且可以有多个)

• 在创建表时设置唯一约束

  • 在字段类型后加上 UNIQUE
  • 在所有字段创建完后
    CONSTRAINT constraint_name UNIQUE(column_name)

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

  alter table table_name
  ADD CONSTRAINT constraint_name
  UNIQUE(column_name);

• 删除唯一约束
  同外键约束一样

---

检查约束

• 在创建表时设置检查约束

  • 在字段类型后加上 CHECK(expressions)
  • 在所有字段创建完后
    CONSTRAINT constraint_name
    CHECK(expressions)

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

  alter table table_name
  ADD CONSTRAINT constraint_name
  CHECK(expressions);

• 删除检查约束
  同上

---

-查询

• 在SQL*PLUS中设置格式(可简写COL)

  • COLUMN column_name HEADING new_name
  • COLUMN column_name FORMAT dataformat
    例：（字符类型）col username format a10
    （数值类型）col salary format $999.9
    (将原长度很长的用户名设置成在显示的时候只显示10个字节的长度)

• 清除之前设置的格式

  COLUMN column_name CLEAR

• 对查询结果排序

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

• CASE…WHEN 语句

  CASE column_name
  WHEN value1 THEN result1,…
  [ELSE rusult]
  END

• decode函数

  decode(column_name,value1,result1,…,defaultvalue)

---