标准SQL

一、SQL语句分类

select语句 查询语句
select
DDL 数据定义语言
create table 建立表
drop table 删除表
alter table 修改表
DML 数据操作语言
insert 增加数据
delete 删除数据
update 修改数据
TCL 事务控制语句
commit 提交
rollback 回滚
savepoint 保存点

二、查询语句

1.SQL中的数学运算

//一个月按照22 计算 求s_emp表中每个人的每天的平均工资
   select  salary,salary/22 from s_emp;
//   一年按照16个月 计算 显示s_emp每个人的月薪和年薪
   select  salary,salary*16 from s_emp;

2.字段或者表达式的别名

   select  salary sal,salary*16 yearsal from s_emp;

1. SQL中字符串的表达
   使用单引号表达
   ” ’ ’ ‘a’ ‘hello world’
   || 字符串拼接符

   select  first_name,last_name from s_emp;   
//   把first_name 和 last_name 拼接起来
   select  first_name||last_name from s_emp; 

4.NULL 值处理
NULL值和任何值做运算结果都是NULL

处理方式：nvl(par1,par2) 可以处理任何类型 但要求par1和par2类型一致。当par1为NULL 时会返回par2对应的值 par1不为NULL就返回par1。

 select salary,
       nvl(salary*12*(1+commission_pct/100),0)
         from s_emp;  

5.数据的排重 distinct
单字段排重:select distinct salary from s_emp;
多字段排重： 当多个字段的值都相同时 才认为是重复的数据

   SELECT  distinct  id,TITLE, SALARY 
       FROM S_EMP;   

6.常见的条件运算符
= != > < >= <=
SQL 提供的条件运算符

//   表达一个闭区间 [a,b]
       where  字段 between a  and b;
//表达一个字段在一个指定的列表中取值
       where  字段  in (值1,值2,值3);

7.null值的判断 : is null

where  字段 is null; 

8.模糊查询like
SQL中的通配符 ： % 0-n个任意字符
_ 1个任意字符

//查询s_emp 表找出 first_name 中第二个字符是a的
        select first_name 
            from  s_emp 
                where first_name like '_a%'; 

特殊的模糊查询

//找出以S_开头的表名
        select table_name from user_tables
            where table_name like 'S\_%' escape '\';

9.，逻辑条件连接符
and
or
not : ca —>not ca
= != ^= <>

 <=
               <      >=
               between a and b  not between a and b
               in        not in  (注意空值)
               like      not like 
               is null   is not null 

10.排序子句
语法:select 字段名
from 表名
where 条件
order by 排序标准 排序方式; 排序方式:

   升序  默认的  asc   自然顺序  字典顺序

//   按照salary 降序排序显示s_emp 表中的id first_name 和 salary
   select id,first_name,salary
       from  s_emp  
           order by salary desc; 

多字段排序:如果第一排序字段的值相同时 就可以启用第二排序字段对数据进行排列。

//按照salary 降序排序显示s_emp 表中的id first_name 和 salary,如果salary相同则使用id升序排。
   select id,first_name,salary
       from  s_emp  
           order by salary desc,id asc; 

tips:NULL 值在排序中的处理作为最大值处理.

11.单行函数和组函数
单行函数:针对sql语句处理的每一行都返回一个结果。sql语句影响多少行就返回多少个结果。
组函数:针对sql语句影响的这一组数据 综合处理之后得到一个结果。无论sql语句影响多少行 只返回一个结果。

 //  upper(par1) 把字符串处理成大写
     select first_name,upper(first_name)
         from s_emp where id=1;

常见组函数:
count(par1) 统计数据的个数
max(par1) 统计最大值
min(par1) 统计最小值
avg(par1) 统计平均值
sum(par1) 统计数据的和

 //  统计s_emp 表中的人数  工资的最大值 和 最小值
   select count(id),max(salary),min(salary)
       from  s_emp;   
 //  统计s_emp 工资的平均值 和 工资的和
   select avg(salary),sum(salary) from s_emp; 

 //特殊的用法
    select avg(distinct salary),
       sum(distinct salary) from s_emp; 

tips：组函数 对NULL值的处理方式 忽略NULL。
11.多表查询
select 需要的字段名
from 表名1,表名2
where 表之间的连接条件;

//等值连接
//显示s_dept 表中的部门名  和 部门对应的地区名。
    select d.name,r.name
        from  s_dept d,s_region r
            where region_id=r.id; 
 //非等值连接
 //  显示s_emp 表中每个员工的工资 以及对应的工资级别
  select salary,grade
      from  s_emp,salgrade
          where salary  between losal and hisal; 

12.特殊的连接——自连接：一张物理表中存储了两层业务含义的数据
要把其中的一层数据查询出来。

  如何找出所有的领导? 8 个领导
     /* 这个找出的是自己管自己的人 */
     select id,first_name
         from  s_emp 
             where  manager_id=id;   
     /* 把一张物理表 通过起别名看成两张逻辑表 */
     select distinct m.id,m.first_name
         from  s_emp e,s_emp m
             where e.manager_id=m.id;

13.外链接: 外连接的结果集 等于内连接的结果集 加上匹配不上
的记录。(一个也不能少) (+)字段对面的表的数据全部被匹配出来
本质上使用的是NULL 记录匹配

//写一个查询显示每个部门的名字 和 部门对应的地区名  :s_dept    s_region 
       select  d.name,r.name 
           from  s_dept d,s_region r
               where d.region_id=r.id;
  //   现在公司的业务扩展了 增加了新的部门
       insert into s_dept values(100,'test100',NULL);
       commit;  
       insert into s_dept values(200,'test200',NULL);
       commit; 
  //   如何显示所有的部门信息?
       select  d.name,r.name 
           from  s_dept d,s_region r
               where d.region_id=r.id(+)

非等值外链接

     //把s_emp 表中每个员工的id  salary 以及工资对应的
       工资级别显示出来。
    select id,salary,grade 
       from  s_emp,salgrade 
           where salary between losal and hisal;
     //给11号涨工资 涨成15000
     update  s_emp set salary=15000 where id=11;
     commit;
     update  s_emp set salary=150 where id=21;
     commit;   
     /* 显示所有的员工的id 和 salary 超出统计范围的
        也要显示 */
     select id,salary,grade 
         from  s_emp,salgrade 
             where salary between losal(+) 
             and hisal(+); 

14.SQL99中的内连接
from a表 join b表 on 表的连接条件
where 过滤条件;
from a表 inner join b表 on 表的连接条件
where 过滤条件;
显示每个部门的名字 和 对应的地区的名字
s_dept s_region
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id;
select d.name,r.name
from s_dept d join s_region r
on d.region_id=r.id;
select d.name,r.name
from s_dept d inner join s_region r
on d.region_id=r.id;
15.SQL99中的外连接
1)左外连接 :意思就是a表发起连接代表把a表的数据全部匹配出来。依然使用的是
NULL 记录匹配。
from a表 left outer join b表
on 表的连接条件 where 过滤条件;
2) 右外连接
from a表 right outer join b表
on 表的连接条件 where 过滤条件;

   //   找出新增加的部门?
     select  d.name,r.name    
         from  s_dept d,s_region r
             where d.region_id=r.id(+); 
     select  d.name,r.name    
         from  s_dept d left outer join s_region r
             on d.region_id=r.id; 

//    改成右外连接
 select  d.name,r.name    
         from   s_region r right outer join s_dept d
             on d.region_id=r.id; 

3)全外连接： 全外连接只是一个逻辑概念,全外连接的结果集
等译左外连接的结果集 加上右外连接的结果
集 然后减去重复的记录。

    SQL99语法
      from  a表 full  outer join  b表  
          on 表的连接条件 where 过滤条件; 
     Oracle中的实现
     union  合并两个结果集并排重
            select id from  s_emp  union 
              select id from s_emp;
            union all  合并两个结果集不排重
            select id from  s_emp  union  all
              select id from s_emp;