Oracle数据库体系结构

系统

实例

表空间

数据表

数据文件

用户

    用户的分类：

        系统管理员 sys 相当于mysql的root

        一般管理员  system

        普通用户

总结：一个Oracle数据库系统可以创建多个数据库，每个数据库称为实例，在实例中可以分配多个表空间，每个表空间里可以创建多个数据表，而每个表空间里的表数据库都存储在一个数据文件里，要查询表空间里的表数据就需要在本实例下给表空间指定个用户，由用户去访问表空间的表及表的数据。

简单查询

SQL分组：

DML（data Manipulation Language）数据库操作语言  用于检索或者修改数据

DDL（data  Definition Language）数据定义语言，用于定义数据的结构，创建，或者修改是哪出数据的对象

DCL（data  control  Language）数据控制语言 用于定义数据库的权限

基本查询

语法：select [distinct] * | 字段  [别名] [字段 [别名]]  from 表名成 [别名];

位置：打开Oracle ---文件下方的小太阳-SQL窗口

查询所有员工的所有数据

select  * from emp；

位置：选中查询语句，点击齿轮，执行即可

修改显示别名（中文编号，姓名，职位）

select  empno,ename,job from emp;

select empno as "员工编号" ，ename "员工姓名”，job “职位” from ep;

查询乱码

执行select userenv("language") from  dual；---复制编码，---设置环境变量（变量名：NLS _LANG ，变量值：复制过来的编码）

去重复行（职位）

查询公司的所有职位

select distinct job from emp;

列拼接（编号，姓名，职位）

要求输入结果为：编号是7369的雇员，姓名是Smith，工作是clerk

select concat(concat(concat(concat(concat('编号是：7',empno)，’的雇员，姓名是:’，ename,'工作是:'),job) from emp;

=====select '编号是' || empno ||  '的雇员，姓名是：'||' , 工是：' || job   from emp;

统计每人的年薪，年薪等于月薪*12+年终奖(sal月薪，comm年中奖，

没有年终奖的没有统计出来，我们的第一个bug

语法：select sal*12+comm from emp；

使用nvl(v1,v2)处理空值V1不为空返回V1，为空返回V2

select a.empno,a.sal*12+vvl( comm，0)  from  emp  a;

单行单列虚拟表dual

dual是一张只有一个字段，一行记录的表，一个单单列的虚拟表，用来测试一些数据值最方便

查询当时时间   

得到guid

运算

select sysdate from dual;

select sys_guid() from dual;

select 1+1 from dual;   -----计算1+1

查询

3.4.1关系运算

>,<,>=,<=,!=(<>),=

3.4.2逻辑运算

逻辑运算符:and  or  not 

3.4.3其他运算

范围运算：between and ,like,in,not in, is null,is not null 

查询出基本工资大于2000的所有雇员信息

select   *  from  emp  where sal >2000;

查询出所有职位是办事员的雇员信息

select  * from emp where job='办事员';

查询工资在2000-3000之间全部雇员信息（不包含）

select *  from emp where sal >2000 and sal <3000;

查询职位是办事员或者销售人员的全部信息

select  *  from   emp  where job='办事员'  or  job=“销售人员”

select   *  from   emp  where  job in ("办事员"，"销售人员"）;

查询出所有不是办事员的雇员信息

select *  from   emp  where job  not  in "办事员";

select  *  from  emp   where job !="办事员"

selecet  *  from  emp  where  job  <>"办事员"；

查询入职时间在1981/4/2-1999/1/1的员工信息

select *  from   emp  where hirdate between  头 data("1981/4/2",yyyy/mm/dd)  and  to data("1999/1/1",yyyy/mm/dd);

查询出所有没有奖金的雇员信息

select *  from emp where  comm is  null;

查询出雇员编号是1,2,3 的雇员信息

select  *  from  emp   where emno in (1,2,3,);

select  *  from  emp  where emno =1 or emno =2 or emno=3;

查询出员工编号不是1,2,3的雇员信息

select   *  from   emp  where  emno  not  in  (1,2,3);

查询出雇员姓名以字母A开头的全部雇员信息

select  *  form  emp where ename like "A %";

查询出雇员姓名第二个字母是A的全部雇员信息

select  *  from   emp   where ename like "_A%";

查询出所有带有A 的全部雇员信息

select  *  from emp where ename like "%A%";

排序

order by 卸载所有SQL语句最后，可以指定多个排序字段

升序（ASC）：默认，不写也是升序

降序（DESC）：用户需要指定，由大到小排序

查询所有员工的雇员信息，要求按照工资升序排序

select   *  from  emp   order  by  sal  【asc】；

按照工资由高到低排序，如果工资相同，则按照雇佣日期由早到晚排序

select *   from   emp  order by  sal  desc,hiredate;

select   *  from  emp  order by sal desc,hiredate desc;

排序中的空值问题

当排序的字段有可能存在null，时就会产生问题，我们可以用null  first ,nulls  last 来指定null 值显示的位置。

查询雇员的奖金 从低到高

select  * from  emp  order  by  comm；

select  *  from  emp order by comm first  nulls;

原始数据需要加工才能使用

Oracle  给我们提供了常用的处理数据的函数，就像JAvA的类库，不需要所有的东西都要我们自己实现。

Oracle常用函数

/*

  一、基本查询

  语法：

    select 字段

    from 表名

*/

--查询所有员工的信息

select * from emp;

--查询显示指定列（编号、姓名、职位）

select empno,ename,job from emp;

--修改显示别名（中文编号、姓名、职位）

select empno as "员工编号",ename "员工姓名",job 职位 from emp;

select userenv('language') from dual;

--所有职位

select distinct job from emp;

--编号是：7369 的雇员， 姓名是：smith，工作是：clerk

select concat(concat(concat(concat(concat('编号是：7',empno),' 的雇员， 姓名是：'),ename),'，工作是：'),job) from emp;

select '编号是：' || empno || ' 的雇员， 姓名是：' || '，工作是：' || job from emp;

--年薪

--select a.empno,a.sal,a.comm,sal*12+comm "年薪" from emp a;

select a.empno,a.sal,a.comm,sal*12+nvl(comm,0) "年薪" from emp a;

select sal*12+decode(comm,null,0,comm) from emp;

--单行单列虚拟表dual

select 1+1 from emp where empno=7369;

/*

      1、查询当时时间

      2、得到guid

      3、运算

*/

常用函数

单行函数

影响每行记录的值，影响多少行返回多少行

多行函数多条记录参与运算，而返回一条记录，

单行函数主要分为以下五类：字符函数，数字函数，日期函数，转换函数，通用函数

l返回字符串并将字符串的第一个字母变为大写，其他字母小写

select   ename,initcap(ename)  from  emp;

select   ename,lower(ename)  from  emp;

length(ename)  求长度

replace（"A","B"）  A替换B

nvl(expr1,expr2):expr1 是否为空，如果为空， expr2，如果不为空，expr1的值。

使用coalesce函数

    与nvl函数相比优点是：同时处理交替的多个值，如果第一个表达式为空，返回下个表达式，对其他的参数进行coalesce

条件表达式：

在SQL中使用if-then-else逻辑

使用两种方法：

case表达式

decode函数

case表达式在需要使用if-then-else逻辑时，

case 带匹配的值  when  comparison_expr1 then return_expr1 

[when comparson_expr2 then return_expr2 

when comparison_exprn then return_exprn  

else  else_expr]

end

练习：查询部门号为10,20,30的员工信息，若部门号为10，则打印其工资的1.1倍，20号部门，则打印其工资的1.2倍，30号部门打印其工资的1.3倍

    select ename,deptno,

    case deptno when 10 then sal * 1.1

    when 20 then sal * 1..2

    when 30 then sal * 1.3

    else sal 

    end 

from emp;

decode函数

在需要使用if-then-else逻辑时

decode（col | expression,search1,result1,

[,search2,resut2,....,]

[,default]）

练习：查询部门号为10,20,30的员工信息，若部门号为10，则打印其工资的1.1倍，20号部门，则打印其工资的1.2倍，30号部门打印其工资的1.3倍

select enamel， deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;

多表查询

主键：oid 唯一能标识一行的字段，非空，唯一特点。

外键：可以在一张表中通过一个外键定位到另外一张表的主键

1，笛卡尔集合：

世界杯小组赛，采用笛卡尔集合的方式布局

中国对  日本对  伊朗队，各对一场。

第一个表的数据，与第二个表的数据一一匹配一次。

select *  from emp，dept;

多表查询，一定要避免笛卡尔集合，在一些表中，可能会存在一个外键，关联另一个表的主键，（或者是两张表当中有些）