Oracle技术—1. DQL

oracle中应用的语言

DQL(Date Query Language)数据库查询语言：

语言中常见的关键字 ：

from ：用来获取查询的表单,可应用在在SQL语句的子查询中；
where ：增加查询条件，可应用在SQL语句的子查询中；
as ： 为列 重命名，可省略；
|| ：可用于多列内容的合并；
distinct ：查询结果去重；
select first_name || last_name as name from users;
where：有条件的查询；
having： 应用在 分组函数判断 中；

1. 比较查询：（<、>、=、!=）

2. 逻辑运算：（ and 、or、not）
   select first_name || last_name as name from users where not id='1';

3. 特殊谓词:

• 枚举（in、not in）

    select first_name  || last_name as name from users  where not id in (1,2,3,4);
    //查询id不是1、2、3、4的用户的姓名`  
  

• 闭区间（between…and …）

   select first_name  || last_name as name from users  where id between '1' and '3' ;
   //查询id不在1~3 之间的用户名
  

• 模糊查询（like）

     select first_name  || last_name as name from users  where name ;
     //查询姓张的用户
  

• 空值运算 （is （not) null）：

     select first_name  || last_name as name from users  where synopsis is not null ;
  

4. 分组排序（group by）

    //查询1997年各个月入职的的员工个数`
    select to_char(hire_date,'mm'),count(*) from employees
    where to_char(hire_date,'yyyy')=1997 
    group by to_char(hire_date,'mm')
   

   注意：
   · 只有应用了group by 的列才可写在select后否则报错弹出：
   ·组函数除外
   ·可使用组函数为分组条件，并可写在select后

5. 排序（order by 列名 desc\asc）

   • asc: 默认，升序排列；

   • desc: 降序排列；

       //查询1997年各个月入职的的员工个数,并从1~12按顺序输出`
        select to_char(hire_date,'mm'),count(*) from employees
        where to_char(hire_date,'yyyy')=1997 
        group by to_char(hire_date,'mm')
        order by to_char(hire_date,'mm');
     

6. 函数

   • 内置函数（sysdate）
     sysdate表示系统当前时间：

       //dual是虚表（哑表）单独是为了满足数据库中的语法要求
       select sysdate from dual
     

   • 日期转换格式：
     to_char(birthday,‘yyyy-MM-dd’)：将数据库中的日期以特定的格式查询出来

      // 查询1997年出生的员工姓名
      select first_name || last_name as name from employees
      where to_char(birthday,'yyyy')=1997 
     

     to_date(‘2000-10-23’,‘yyy-MM-dd’)：将字符类型数据转换成日期格式，一般用在DML语言中，用来向表中添加Date类型的数据

      insert into shop_order values(sq_id.nextval,to_date('1988-8-10','yyyy-mm-dd'),8000,'huxz');
     

7. 分组函数：

   • max(列名)：最大值
   • min(列名)：最小值
   • count(列名)：数量
   • sum(列名)：和
   • avg(列名)：平均数
     注意：primary key也可以使用

8. having 的判断：
   应用在group by分组后的再判断；

    //查询平均工资大于5000的部门以及平均工资
    select department_name ,avg(salary)
    from employees
    group by department_name
    having avg(salary)>5000
    注意：where  后面不可加 分组函数判断条件
   

SQL语句的子查询语句

1. where：where中的条件来源于另一条SQL语句中的执行结果

   • 单行（值）子查询

     //查询last_name 为Alice 的员工部门名（没有相同名）
//1、获取姓名为Alice的员工的 id

       select id
       from employees
       where last_name = 'Alice';
     

     //2、查询 i d 为80 的员工的部门名

       select department_name`
        from employees
       where id ='80';
     

     //3、合并

       select department_name
       from employees
       where id =select id
       	from employees
       	 where last_name = 'Alice';
     

   • 多行（值）子查询

      //查询last_name 为Link 的员工部门名（有相同名）
       select department_name
      	 from employees
      	 where id in (select id
        	from employees
       	 where last_name = 'Link');
     

   2. from 子查询
      select * from （完整的SQL查询语句）–>表示一个表单

       //所有工资高于平均工资(平均工资包括所有员工)的销售人员(销售人员：'SA_REP')(列名：job_id)
       select first_name from(select first_name,salary from employees where job_id='SA_REP') sa
       where sa.salary>(select avg(salary) as avg from employees);
      

伪列（Oracle中独有）

1. rowid一个特殊的列:
   每张表创建出来都会生成的，用来记录在表空间中的唯一位置的ID，数据插入时自动生成
   select rowid ,e.* from employees e

2. rownum
   对于数据的 查询结果进行编号，每一条SQL查询语句都会有自己对应的rownum存在
   rownum中存在的一个机制：在判断条件与当前对象的rownum不同时，数据库会自动将此对象（行）删除，此时下一个对象（行）的rownum将代替被删除的rownum。因此我们在使用时要借助from 子查询，获取结果；

    	//查询employees 表中员工工资排名3~7的员工姓名
    	select * from
    	(select rownum r1, e.*
    	from (select rownum r2 , t.* from employees t order by salary desc) e) e1
    	where  e1.r1 between 3 and 7 ;
   

此时我们有个疑问为什么不用r2 用r1 来判断------>因为每个SQL查询语句都对应有自己的rownum，在执行完条件语句后生成表单的rownum将不再是自然数排列

多链表连接

连接方式：

1. 内连接：inner join … on …

2. 外连接：

   1. 左连接： left ( outer ) join … on …
   2. 右连接：right（ outer ）join … on …

注意：左右只是一个相对的概念,连接为left则左边的表是完整的无论on 是否 成立都显示左边所有的信息，但不存在on 不显示 left 右边的表，right 同理

语法：
select 列名1，…
from table1 t1
(inner) join table2 t2
on t2.id = t2.id;