Oracle学习笔记（一）

oracle学习笔记

一、创建表空间

​ 一个表空间可以与多个数据文件（物理结构）关联。一个数据库下可以建立多个表空间，一个表空间可以建立多个用户，一个用户下可以建立多个表。

​ 数据文件是数据库的物理存储单位。数据库的数据时存储在表空间中的，真正在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一单数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属的表空间才行。

**注：**表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或多个数据文件中。

create tablespace space
datafile 'D:\seven.dbf'
size 20m
autoextend on
next 5m
maxsize 30m;

二、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 子查询

​ 子查询在操作中有三类：

1. 单列子查询：返回的结果是一列的一个内容
2. 单行子查询：返回多个列，有可能是一个完整的记录
3. 多行子查询：返回多条记录

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)

举例：

查询出比雇员7654的工资高，同时从事和7788的工作一样的员工
select * from emp t1 where t1.sal > 
(select t.sal from emp t where t.empno = 7654) 
and t1.job = (select t2.job from emp t2 where t2.empno = 7788);

查询每个部门的最低工资和最低工资的雇员和部门名称
select d.dname,a.minsal,e.ename from dept d,
(seletc deptno,min(sal) minsal from emp group by deptno)
a,emp e where d.deptno = a.deptno and e.sal = a.minsal;

2.6 多表查询

​ 使用一张以上的表做查询就是多表查询

语法：select {distinct} *|列名..from 表名 别名，表名1 别名
{where 限制条件 order by 排序字段 asc|desc...}

举例：

查询员工表和部门表
select * form emp,dept

​ 产生的记录为56条，是emp表和dept表的记录数的乘积，称其为笛卡尔积。

​ 如果多张表进行一起查询而且每张表的数据很大的话，笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积就需要关联查询。两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

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

查询出雇员的编号，姓名，部门的编号和名称，地址
select e.empno,e.ename,d.deptno,d.dname,d.loc
from emp e,dept d where e.deptno = d.deptno;

注：where后跟的限制条件的个数所要查询的表的n-1个，如：三表关联查询则需要两个限制条件。

in运算符

​ 当查询条件较多时，使用or运算符编写非常繁琐，所以用in来进行查询。

举例：

查询地址编号为1、3、4的业主记录
select * from T_OWNERS where address in (1,3,4);

查询地址含有“花园”的业主的信息
select * from T_OWNERS where addressid in
(select id from t_address where name like '%花园%');

查询地址不含有“花园”的业主的信息
select * from T_OWNERS where addressid not in
(select id from t_address where name like '%花园%');