oracle学习笔记

一、约束

​ 数据库中通过约束来对每个字段中的数据的合法性进行规范。

• 主键约束（primary key）
• 唯一性约束（unique）
• 非空约束（not null）
• 外键约束（foreign key）
• 检查约束（check）

主键：

1. 键列必须具有唯一性，且不能为空，相当于unique + not null
2. 一个表只允许有一个主键
3. 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

唯一键：

​ 唯一性约束可作用在单列或多列上，唯一性约束保证每一行的唯一性。unique允许null值，unique约束的列可存在多个null。

非空约束：

​ 非空约束作用的列也叫强制列，强制键列中必须有值。若建表时使用default关键字指定了默认值，则不可输入。

外键约束：

​ 外键约束定义在具有父子关系的子表中，外键约束能使得子表的列对应父表的主键列，用于维护数据库的完整性。

1. 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同。
2. 对应的父表列必须存在主键约束（primary key）或唯一约束（unique）
3. 外键约束列允许null值，对应的行就成了孤行了

检查约束：

​ 检查约束可用来实施一些简单的规则，如列值必须在某个范围内。

二、select语法

2.1 select基本结构和简单查询

2.1.1 简单查询

1. 查询所有

   查询员工的所有信息

   select * from emp

   查询部门的所有信息

   select * from dept

   查询工资等级信息

   select * from salgrade

2. 查询部分字段

   查询员工的姓名

   查询员工的姓名和年龄

   查询员工的名称、工资以及部门编号

   select ename, sal, deptno from emp

3. 按顺序查询

   查询部门的所有信息并按照部门编号、地址、部门名称的顺序来显示(按字段顺序依次展示)

2.2 去重、别名和排序

2.2.1 去重

​ 去除重复记录

​ 结构：

select distinct colName from tableName;

​ distinct 是去除整条记录

​ 查询部门编号

​ select distinct deptno from emp

​ 查询所有的员工名称，员工所在部门编号

​ select distinct ename, deptno from emp

2.2.2 列别名

给列取名字

结构：

select colName n from tableName;

select colName as n from tableName;

查询员工姓名，工资

select ename 姓名，sal 工资 from emp

select ename as 姓名，sal 工资 from emp

2.2.3 排序

​ 将查询出来的结果按照指定顺序排序

结构：

select colName1,colName2 from tableName order by colName;

查询出所有员工的姓名、工资，按照工资降序排序

select ename, sal from emp order by sal desc

select ename, sal from emp order by sal asc

查询所有的员工姓名、工资、所在的部门编号，按照部门编号升序排序，同一部门的员工，按照工资降序排序

select ename , sal , deptno from emp order by deptno asc , sal desc

2.3 伪列和虚表

2.3.1 伪列和表达式

​ 查询不存在的列即为伪列，当需要的结果不能直接从表中得到，需要经过计算来展示则可以使用伪列+表达式实现。

select ename , 1 , from emp;

查询员工的名称、月工资以及年薪

select ename , sal , sal * 12 from emp

使用别名：

select ename , sal , sal * 12 as 年薪 from emp

1. null处理

   nvl(exp1,res) exp1：不为空时 res:为空时
   当某个值为空时给出一个特定的值，否则该值就为它自己
   nulls first:将空值放到最前面
   nulls last:将空值放到最后面
   

   查询员工的姓名，工资，提成，月收入（月收入=工资+将近）

   select ename , sal , comm , sal + nvl(comm,0) 月收入 from emp

2. 字符串拼接

   ||

   查询员工的姓名以及将员工姓名和字符’a’进行拼接后的字段（员工姓名-a）

   select ename , ename || ‘a’ 别名 from emp

2.3.2 虚表

​ dual 是一个虚表，虚拟表，是用来构成 select 的语法规则，oracle保证dual里面永远只有一条记录。该表只有一行一列，和其他表一样，可以执行插入、更新、删除操作，还可以执行drop操作。但不要去执行drop操作，否则会使系统不能用，起不了数据库。

​ dual主要用来选择系统变量或是求一个表达式的值。如果不需要从具体的表来取得表中数据，而是单纯的得到一些想要得到的信息，并要通过select完成时，就需要借助一个对象——dual。

计算999*666

select 999*666 from dual

2.4 条件查询

select 查询内容 from 数据来源 where 行记录条件

2.4.1 比较运算

=、>、<、>=、<=、**<>、!=、^=（均代表不等于）、**between…and(两端均包含在内)、in

查询10部门的员工信息

select * from emp where deptno = 10

查询所有的工资在2000以上的员工信息

select * from emp where sal > 2000

查询工资在2000-4000之间的员工信息

select * from emp where sal between 2000 and 4000

查询在10部门或在20部门工作的员工信息

select * from emp where deptno in(10,20)

2.4.2 条件连接运算

and、or、not

​ 当查询条件有多个时，可能要同时满足，或者只满足其中一个，或者不满足某个条件，则需要用到或、且、非。

查询工资大于1500并且在20部门的员工信息

select * from emp where sal > 1500 and deptno = 20

查询在20部门的员工或工资大于1500的员工信息

select * from emp where sal > 1500 or deptno = 20

查询不在20部门的员工信息

select * from emp where deptno not deptno = 20

2.4.3 null运算nvl( )

​ null 比较特殊，需要单独处理

is null、is not null、not…is null

查询出所有可能获得奖金的员工信息（奖金不为空的）

select * from emp where comm is not null

select * from emp where not comm is null

查询出所有不可能获得将近的员工信息（奖金为空）

select * from emp where comm is null

2.4.4 模糊查询like

% :代表任意一个包含

_:代表单个字符

​ 当查询的条件比较模糊时，可以使用模糊查询

查询名称当中包含有'S'的员工信息

select * from emp where ename like '%S%'

查询名称以'A'开头的员工信息

select * from emp where ename like 'A%'

查询名称中第二个字母为'A'的员工信息

select * from emp where ename like '_A%'

查询名称中倒数第二个字母为'I'的员工信息

select * from emp where ename like '%I_'

2.4.5 exists

​ in 是把外表和内表做hash join，而exists 是对外表做loop，每次loop再对内表进行查询。这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。

查询员工名称当中包含 % 的员工信息

select * form emp where ename like '%a%%' escape('a')

查询员工名称当中包含 'a%' 的员工信息

select * from emp where ename like '%aaa%%' escape('a')

查询员工名称当中包含 '%' , '_' 的员工信息

select * from emp where ename like '%a%%a_%' escape('a')

2.5 子查询

2.5.1 from 子句…

​ 数据来源是经过过滤的

​ from子句指定select语句查询及与查询相关的表或视图。在from子句中最多可指定256个表或视图，他们之间用逗号分割。在from子句同时指定多个表或视图时，如果选择列表中存在同名列，这是应使用对象名限定这些列所属的表或视图。

select * from （select）

2.5.2 where子句

​ 判断条件不能直接得到，需要经过计算和过滤

查询销售部（SALES）的员工信息
select * from emp where deptno = (select dept where dname = 'SALES')
查询工资等级在2的员工信息
select * from emp where sal between (select losal from salgrade
where grade = 2) and (select hisal from salgrade where grade = 2)