oracle基本操作

    select * from hr.EMPLOYEES;
    
    select * from hr.countries;
    
    select * from hr.countries,hr.departments;
    
    select region_id,country_name from hr.countries;
    
    select employee_id, first_name, last_name, salary*(1+0.1) from hr.employees;
    
    select employee_id, first_name, last_name, salary, salary*(1+0.1) new_salary from hr.employees;
    
    select distinct department_id from hr.employees;
    
    -- 通配符 模糊查询 like
    select employee_id, first_name, last_name from hr.employees where first_name like 'B%';
    SELECT EMPLOYEE_ID,first_name,LAST_NAME from hr.EMPLOYEES WHERE first_name like 'A%';
    
    --连接运算符  and/or
    SELECT employee_id, first_name, LAST_NAME, SALARY from hr.EMPLOYEES WHERE DEPARTMENT_ID=60 and SALARY>2000;
    SELECT employee_id,first_name,LAST_NAME,DEPARTMENT_ID from hr.EMPLOYEES WHERE DEPARTMENT_ID=60 or DEPARTMENT_ID=30;
    
    --null值
    INSERT into hr.DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID) values (300,'数据库',NULL);
    SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID from hr.DEPARTMENTS WHERE MANAGER_ID is null;
    
    --order by 子句
    SELECT EMPLOYEE_ID,first_name,LAST_NAME,SALARY from hr.EMPLOYEES WHERE SALARY > 2000 ORDER BY SALARY;
    SELECT EMPLOYEE_ID,first_name,LAST_NAME,SALARY from hr.EMPLOYEES WHERE SALARY > 2000 ORDER BY SALARY DESC;
    
    --group by 子句
    SELECT JOB_ID,SALARY from hr.EMPLOYEES order by JOB_ID;
    SELECT JOB_ID, avg(salary),sum(SALARY),max(SALARY),count(JOB_ID) from hr.EMPLOYEES group by JOB_ID;
    SELECT JOB_ID, avg(salary),sum(SALARY),max(SALARY),count(JOB_ID) from hr.EMPLOYEES group by JOB_ID;
    SELECT JOB_ID,avg(SALARY),max(SALARY),min(SALARY),sum(SALARY),count(*) from hr.EMPLOYEES group by DEPARTMENT_ID, JOB_ID;
    --汇总信息
    SELECT JOB_ID,avg(SALARY),sum(SALARY),max(SALARY),count(*) from hr.EMPLOYEES group by rollup(JOB_ID);
    SELECT JOB_ID,avg(SALARY),sum(SALARY),max(SALARY),count(*) from hr.EMPLOYEES group by cube(JOB_ID);
    
    --having 子句
    SELECT JOB_ID,avg(SALARY),sum(SALARY),max(SALARY),count(*) from hr.EMPLOYEES group by JOB_ID having avg(SALARY) > 10000;
    
    --多表联合查询
    --简单链接 - 基本形式 笛卡尔积
    SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENT_NAME from hr.EMPLOYEES,hr.DEPARTMENTS;
    
    --简单链接 - 条件限定
    SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENT_NAME from hr.EMPLOYEES,hr.DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

    select EMPLOYEE_ID,LAST_NAME,DEPARTMENT_NAME from hr.EMPLOYEES,hr.DEPARTMENTS
    where EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID 
    and DEPARTMENTS.DEPARTMENT_NAME='Shipping';
  
    
    -- 表别名
    SELECT em.EMPLOYEE_ID,em.LAST_NAME,dep.DEPARTMENT_NAME from hr.EMPLOYEES em,hr.DEPARTMENTS dep WHERE em.DEPARTMENT_ID=dep.DEPARTMENT_ID and dep.DEPARTMENT_name='Shipping';
    
    -- 内连接
    
    SELECT em.employee_id,em.last_name,dep.DEPARTMENT_name from hr.EMPLOYEES em JOIN hr.DEPARTMENTS dep 
    on em.DEPARTMENT_ID=dep.DEPARTMENT_ID where em.JOB_ID='AD_ASST';
    
    
    -- 自然连接 --连接的各表之间必须有相同名称的列
    
    SELECT em.employee_id,em.first_name,em.last_name,dep.DEPARTMENT_name
    from hr.EMPLOYEES em natural join hr.DEPARTMENTS dep 
    WHERE dep.DEPARTMENT_NAME='Sales';
    
    
    -- 外连接
    insert into hr.EMPLOYEES(employee_id,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID)
    values(1000,'blaine','blaine@hotmail.com',to_date('2009-05-01','yyyy-mm-dd'),'IT_PROG',null);
    
    SELECT em.employee_id,em.last_name,dep.DEPARTMENT_name from hr.EMPLOYEES em inner join hr.DEPARTMENTS dep 
    on em.DEPARTMENT_ID=dep.DEPARTMENT_ID WHERE em.JOB_ID='IT_PROG';
    SELECT em.employee_id,em.last_name,dep.DEPARTMENT_name from hr.EMPLOYEES em left join hr.DEPARTMENTS dep
    on em.DEPARTMENT_ID=dep.DEPARTMENT_ID WHERE em.JOB_ID='IT_PROG';
    
    ELECT em.employee_id,em.last_name,dep.DEPARTMENT_name from hr.EMPLOYEES em RIGHT JOIN hr.DEPARTMENTS dep 
    on em.DEPARTMENT_ID=dep.DEPARTMENT_ID WHERE dep.LOCATION_ID=1700;
    
    -- 全连接
    SELECT em.employee_id,em.last_name,dep.DEPARTMENT_name from hr.EMPLOYEES em full join hr.DEPARTMENTS dep 
    on em.DEPARTMENT_ID=dep.DEPARTMENT_ID WHERE dep.LOCATION_ID=1700 or em.JOB_ID='IT_PROG';
    
    -- 自连接
    SELECT employee_id,LAST_NAME,JOB_ID,MANAGER_ID from hr.EMPLOYEES order by employee_id;
    SELECT em1.last_name 'manager',em2.last_name 'employee' from hr.EMPLOYEES em1 LEFT JOIN hr.EMPLOYEES em2
    on em1.EMPLOYEE_ID=em2.EMPLOYEE_ID order by EMPLOYEE_ID;
    
    -- 集合操作 
    --union/union all  并运算
    
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'C%' or LAST_NAME like 'S%'
    union 
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'S%' or LAST_NAME like 'T%';
    
    
    --union 与 union all  union all 操作符形成的结果集中包含两个子结果集中重复的行
    
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'C%' or LAST_NAME like 'S%'
    union all 
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'S%' or LAST_NAME like 'T%';
    
    
    -- intersect  交集运算
    
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'C%' or LAST_NAME like 'S%'
    intersect 
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'S%' or LAST_NAME like 'T%';
    
    
    --minus 差集
    --返回所有从第一个查询中返回的，但是没有在第二个查询中返回的记录
    
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'C%' or LAST_NAME like 'S%'
    minus
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES WHERE LAST_NAME like 'S%' or LAST_NAME like 'T%';
    
    
    /*
    在使用结合操作符编写复合查询时，规则包括： 
    1.在构成复合查询的各个查询中，各select语句指定的列必须在数量上和数据类型上相匹配；
    2.不允许在构成复合查询的各个查询的规定order by 子句，
    3.不允许在blob、long这样的大数据类型对象上使用集合操作符
     */
    
    -- 子查询
   
    SELECT EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID from hr.EMPLOYEES WHERE DEPARTMENT_ID in (
    SELECT DEPARTMENT_ID from hr.DEPARTMENTS where LOCATION_ID=1700);
   
   
    --EXISTS
   
    SELECT EMPLOYEE_ID,LAST_NAME from hr.EMPLOYEES em WHERE EXISTS(
    SELECT * from hr.DEPARTMENTS dep WHERE em.DEPARTMENT_ID=dep.DEPARTMENT_ID
    and LOCATION_ID=1700);
   
    
   
    SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY from hr.EMPLOYEES WHERE JOB_ID='PU_MAN' and
    SALARY>=(SELECT avg(SALARY) from hr.EMPLOYEES where JOB_ID='PU_MAN');