oracle基础知识及命令

数据字典

dba_users 用户信息
dba_tablespaces  系统表空间
dba_data_files  存放表空间文件的相关信息
user_constraints  约束的相关信息

管理员

sys:超级管理员        sys/123456 as sysdba
system:管理员        system/123456
sysman:企业级管理员
scott:用户级        scott/tiger -> 修改密码为123456

常用命令

show user:     查看登录用户
desc table_name:    查看表（数据字典）
alter user scott account [un]lock; :    锁定用户

创建表

create table table_name(
id number(6,0),
name varchar2(20),
birthday date
);

数据类型

字符型

• char(n), :定长字符串，最大长度2000；

• nchar(n), :以unicode编码的定长字符串，最长1000；

• varchar2(n), :变长字符串，最大4000；

  1.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；
  2.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；
  3.VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，
  大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

• nvarchar2(n), :以unicode编码的变长字符串，最长2000；

数值型

 number(p,s) :有效数字p位，保留s位。当s<0时,表示对小数点前s位数字进行舍入。
 float(n), :存储二进制的数值的，表示二进制位数0-126位，若是转化为十进制通常是乘以0.30103得到。  
> 一般使用number

日期型

date : 表示公元前4712年1月1日到公元9999年12月31日
timestamp：精确到小数位秒

其他类型

 blob ： 以二进制，4GB 存储
 clob ： 以字符串，4GB 存储
> 可存储音频、视频等

修改表

向表中添加字段

alter table table_name add column_name datatype;

修改字段类型

alter table talbe_name modify column_name datatype;

删除字段

alter table talbe_name drop column column_name;

修改字段名

alter table talbe_name rename column column_name to new_name;

修改表名

rename talbe_name to new_table_name;

删除表

truncate table table_name;    //删除表的内容，保留表结构，不能删除单条数据
drop talbe table_name;        //删除表结构和表的内容
delete from table_name;      //删除一条或多条表中数据

操作表中数据

添加数据

insert into table_name(column_name,...) values (...);
insert into table_name values (全部列...);

删除数据

delete from table_name [where 条件]

修改数据

update table_name set column_name = 值 [where 条件]

查询数据

select column,column from table_name;

在Oracle里执行增删改时需要进行事务的提交或回滚(回滚到上一次提交的地方)

表空间

• 概念：表空间是数据库的逻辑存储空间。可以理解为，在数据库当中开辟的一个空间，用于存放数据库的对象。

   永久表空间：数据库中要永久化存储的一些对象，如：表、视图、存储过程  
  临时表空间：数据库操作当中中间执行的过程，执行结束后，存放的内容会被自动释放  
  UNDO表空间：用于保存事务所修改数据的旧值，可以进行数据的回滚。  

查看用户下面的默认表空间

select default_tablespace,temporary_tablespace 
from dba_users where username='SYSTEM';

创建表空间

create [temporary] tablespace 表空间的名称 tempfile | datafile '.dbf' size xx M

修改用户下表空间

alter USER 用户名称  DEFAULT丨TEMPORARY TABLESPACE 表空间的名称;

查询表空间文件信息

select file_name from dba_data_files;

注意:一个用户只能有一个默认表空间，一个表空间可以有多个表空间文件。

添加数据文件

alter tablespace 表空间名 add datafile '数据文件名.dbf' size 数据文件大小

删除数据文件

alter tablespace 表空间名 drop datafile '数据文件名.dbf';

删除表空间

drop tablespace 表空间名 [including contents and datafiles];

约束

• 定义规则

• 确保完整性

  非空约束

• 在创建表时设置

  create table talbe_name(column_name datatype NOT NULL,...);

• 在修改表时添加

  //添加非空约束的字段不能有null数据存在
  ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

• 在修改表时去除

  ALTER TABLE table_name MODIFY column_name datatype NULL;

主键约束

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

• 创建表时设置

  CREATE TABLE table_name(column_name datatype PRIMARY KEY,...);

  • 联合约束:

    CONSTRAINT constraint_name PRIMARY KEY (column_name1,...) 

    示例：

    CREATE TABLE table_name(
        id number(6,0),
        username varchar2(20),
        CONSTRAINT pk_id_username PRIMARY KEY (id,username)//名字任意
    );

    如果忘记约束名字，可在数据字段中查找：

    desc user_constraints;
    //查询约束名称和状态
    select constraint_name,status from user_constraints where table_name=’table_name’;
    //结果为 pk_id_username

• 修改时添加

  ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1,...);

• 更改约束名称

  ALTER TABLE table_name RENAME CONSTRAINT old_constraint_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]; //CASCADE在级联操作中使用

  外键约束

• 在创建表时设置外键约束（列级）

  CREATE TABLE table1(column_name datatype REFERENCES table2(column_name),...);
  //table2为主表，table1为从表

  1. 设置外键约束时，主表字段必须为该表的主键字段
  2. 主从表中相应的字段必须时同一个数据类型
  3. 从表中外键字段的值必须来自主表中相应字段的值，或为null

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

  CREATE TABLE table_name(
      column_name datatype,...,
      CONSTRAINT constraint_name FOREIGN KEY (column_name)
      REFERENCES table_name2(column_name)[ON DELETE CASCADE]);
  // table_name2 为主表名
  // [ON DELETE CASCADE]表示级联删除
  // 约束的名字也是唯一的

• 在修改表时

  ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name(column_name)[ON DELETE CASCADE]

唯一约束

与主键约束的区别：

• 主键约束的字段为非空的，而唯一约束只允许有一个值为null

• 单一的唯一约束中Null是未知，比较的结果依旧是未知的，顾可以存在多个null值

• 一张表只能有一个主键约束，但可以有多个唯一约束

• 创建表时设置（列级）

  CREATE TABLE table_name(column_name datatype UNIQUE,...);

• 创建表时设置（表级）

  CREATE TABLE table_name(column_name datatype,...,
      CONSTRAINT u_name UNIQUE (column_name)
  );
      //若想设置多个唯一约束字段，可重复 CONSTRAINT 语句

• 修改表时添加

  ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

检查约束

• 在创建表时（列级）

  CREATE TABLE table_name(column_name datatype CHEC(expressions),...);

• 在创建表时（表级）

  CREATE TABLE table_name(
      column_name datatype,...,
      CONSTRAINT c_name CHECK(expressions)
  );

• 在修改表时

  ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expressions);

  约束总结

• 1.在修改表的时候修改约束，只有非空约束不同，使用的是修改字段的语句：

  ALTER TABLE table_name MODIFY column_name datatype NOT NULL;  

  其他都是使用

  ALTER TABLE table_name ADD CONSTTRAINT constraint_name CHECK/PRIMARY KEY /UNIQUE 
  //外键
  ADD CONSTRAINT FOREIGN KEY (column_name) REFERENCES 主表名(主表字段);

• 2.非空约束没有名字，其他都可以改名字

  ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name TO new_name;

  查询的时候使用数据字典：user_constraints

  select constraint_name,constraint_type,status from user_constraints  
     where table_name='AAA';

• 3.删除约束，非空约束也是使用修改字段的语句，其他都是使用下面的：

  1.禁用约束：ALTER TABLE table_name DISABLE | ENABLE CONSTRAINT constraint_name;
  2.直接删除：ALTER TABLE table_name DROP CONSTRAINT constraint_name;

  主键约束有一种特殊的删除方法：

  ALTER TABLE table_name DROP PRIMARY KEY;

查询

查询表中所有字段及指定字段：
1.查询所有：

select * from table_name;

2.查询指定字段：

select column,... from table_name;

给字段设置别名：

select coulumn 别名,column 别名 from table_naem;\

对查询结果进行排序

• 在分组函数查询语句中进行排序，可以用order by后跟 分组函数，或跟别名，或用列数字

  select coulumn... from table_name order by column_name ASC|DESC;

sqlplus的另一个小技能：
a命令(单词append的缩写，表示追加)
当我们想在一个语句之后加上一个语句时，就好像图中我想在最后加上一个desc，以便降序排列
那么我们可以直接在输入界面输入
a desc（注意必须得是两个空格或两个空格以上）
然后输入/再按回车执行就可以了

连接查询

内连接：

select * from emp,dept where emp.deptno=dept.deptno;

select * from emp INNER JOIN dept ON emp.deptno=dept.deptno;

外连接：

// + 在左边为右外连接，在右边为左外连接
select * from emp,dept where emp.deptno+=dept.deptno;

select * from emp LEFT JOIN dept ON emp.deptno=dept.deptno;

运算和表达式

逻辑运算的优先级：  
    按not、and、or的顺序依次递减；  
    比较运算符的优先级高于逻辑运算符。  

字符串连接操作符：||
NULL操作：记录中缺少的数据值就是NULL，是指未赋值、未知或不可用的值。不等于0或空格。查询条件中使用 IS [NOT] NULL

继续查询

模糊查询：

通配符：_ , %  一个_只能代表一个字符，一个%可以代表0到多个任意字符
使用LIKE查询 

范围查询

between...and：闭合区间的范围
IN/NOT IN：具体的值

集合运算：

INTERSECT(交集):返回两个查询共有的记录
UNION ALL(并集)：返回各个查询的所有记录，包括重复记录
UNION(并集)：返回各个查询的所有记录，不包括重复记录
MINUS(补集)：返回第一个查询检索出的记录减去第二个查询检索出的记录之后的剩余记录

注意：使用集合操作时，查询所返回的列数以及列的类型必须匹配，列名可以不同

case…when语句

CASE column_name WHEN value1 THEN result1,...[ELSE result] END
CASE WHEN column_name=value1 THEN result1,...[ELSE result] END
case...when 语句的使用([when后面跟的是判断的条件，then是条件为真是显示出来的东西])

decode:与case...when类似
decode(列名，判断条件，条件为真的值，默认值)
decode(col_name,value1,result1,...,defaulvalue)

分组查询

分组函数

• 分组函数作用于一组数据，并对一组数据返回一个值
• 常用分组函数：AVG、SUM、MAX、MIN、COUNT(计数)、WM_CONCAT(行转列)

分组函数和空值:分组函数会不计算空值，想要计算空值的办法就是使用
nvl()函数 ：NVL(表达式1，表达式2)

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
该函数的目的是把一个空值（null）转换成一个实际的值。
其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

• 分组函数会自动忽略空值
• NVL函数：NVL函数使分组函数无法忽略空值

分组数据

group by 子句：可将表中数据分成若干组

注意：

• 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中

过滤分组：having子句的使用

where与having的区别：

having是先分组再过滤 。where是先过滤再分组

• 不能在where子句中使用组函数
• 可以在having子句中使用组函数
• 从SQL优化角度，尽量使用where，会使得分组记录数大大降低，从而提高效率

group by语句增强

select deptno,job,sum(sal) from emp group by rollup(deptno,job)  

意思是先以deptno job分组计算薪资
再以dept分组计算薪资，再无条件分组计算薪资
等价于

select deptno,job,sum(sal) from emp group by deptno,job  

select deptno,sum(sal) from emp group by deptno  

select sum(sal) from emp group by null  

break on deptno skip 2   
//表示不同的部门号只显示一次  并且空两行  
set pagesize  30  设置页面大小