oracle学习

//字符串的合并  使用 ||或者count(r1,r2)
select first_name||last_name from employees;


select concat(first_name,last_name) from employees;
//查询当前的系统日期
select sysdate from dual;
//别名的 使用,对于中文必须使用""


select salary*12 "年薪" from employees;
//空字符串的使用  is  null
select *  from employees where manager_id is null;


//去除重复的记录；
select distinct  first_name  from employees;
//查询Neena曾经从事过的工作----带有限制条件的查询
select *  from employees  where first_name='Neena' or  last_name = 'Neena';


--比较运算符 注意: <>   不等于
//查询薪资在8000到15000之间?  ----between 900  and 500
select *  from employees where salary between 8000  and 15000;
// in 的使用
select *  from employees where salary in (8000,9000);
select *  from employees;
//like  的使用  _  代表一个字符  %  代表多个字符
select *  from employees where first_name like '_on%';


insert into username  values (1);


select *  from  username;


/--order by的使用.默认是升序desc,降序是asc
select * from employees order by salary;
select  *  from employees order by salary asc; 
--多字段的排序


select *  from employees order by salary,first_name desc;


姓名中有e或者a的员工?  使用多行函数 concat
select *  from employees where concat(first_name,last_name) like '%a%' or first_name||last_name like '%e%'
--大小写转换函数 Lower  upper  initcap(首字母大写)
select lower(first_name)  from employees;
select upper(first_name),initcap (first_name)  from employees;
--字符串截取  sub


select substr(first_name,1,3) from employees;
--返回字符串的长度
select lengthb(first_name) from employees where first_name  like '%on%';
--lpad 在指定 的字符串 的左边添加字符  7代表中长度
select lpad('hellow',7,'*')  from dual;
--在右边添加指定的字符串
select rpad ('hellow',8,'*')  from dual;
--字符串的截取  trim
select trim ('s' from 'string') from dual;
--数字字符 --四舍五入  3 表示留3位小数
select round(45.55555,3) from dual;
--trunc  对数字进行截取
select trunc(23.77777777,3) from dual;
--mod  相除返回其余数
select mod(4,3) from dual;
---隐式转换
select '6666'||77 from dual;
select '6666'+77 from dual;
--显示转换
select to_char(110.110,'999.9900') from dual;
select to_char(salary,'$9999999.9') from employees;
select to_char(salary,'l999999.9') from employees;
--案例一：把￥3100.00 转回成数字
select to_number('￥3100.00','L99999.99') from dual;
--案例三：打印出"xxxx年xx月xx日 xx:xx:xx"格式的日期和时间
select to_char(sysdate,'yy"年"mm"月"dd"日" hh"小数" mm"秒"')  from  dual;
--增加日志
select add_months(sysdate,12) "明年今日" from dual;
select current_date from dual;//返回当前对话框的时间
--找出日期或间隔值的字段值
select extract(month  from sysdate) from dual;
--返回月份中最后一天
select last_day(sysdate) from dual; 
--next_day  返回下周的对应日期
select next_day(sysdate,'星期一')  from dual;
--日期f和s间相差月数
select months_between(sysdate,'2017-02-02') from dual;
select months_between(sysdate,'04-5月-09')from dual;
--nvl将null值替换成什么
select NVL(1,'01') from dual;
  如果这个人有奖金点，那就他的工资就是工资+奖金
   如果这个人没有奖金点，那就他的工资就没有奖金
select nvl2(commission_pct,'sal+com','sal') from employees;
--nullif(f,t) 比较两个表达式 如果相等 返回空  不然返回第一个表达式
select nullif(3+3,3+2) from dual;
--返回表达式列表中第一个非空表达式的值
select coalesce(null,null,2,3) from dual;
--case表达式
SELECT   last_name,
             case job_id
                  when '总经理'     then  1.15*salary
                  when '副总经理'   then  10*salary
                  else    salary
                  end    工资
          FROM     employees where last_name='OConnell';


--打印公司员工工资等级：5000以下D；5000-10000C;10000-15000B;15000以上A
--其中:nvl(x,y)用于将x为空的改为y值
--trunc用于除法
when  0  then  'DDDDD'  表示当结果值为0 的时候用DDDDD替换
select first_name ,salary ,
       case trunc (nvl(salary,0)/5000)
         when 0 then 'DDDDD'
         else 'AAAAAAAA'
           end "等级"
           from employees;  
select first_name,salary ,
       case trunc(nvl(salary,0)/5000) 
         when 0 then 'AA'
           when 1 then 'BB'
             else 'CC'
               end "等级"
               from employees;


-- decode函数
select first_name,salary,
      decode(trunc(nvl(salary,0)/5000),
               0,'a',
               1,'b',
               'c' )
               from employees;
--常见的分组函数               
 select avg(commission_pct),sum(commission_pct)/count(employee_id) from employees;               
select avg(commission_pct) from employees;--平均奖金
select sum(commission_pct)  from employees;
select count(salary) from employees;
select max(salary) from employees;
--先分组再统计：查询出每一个部门的最高工资
select max(salary) ,department_id  from employees group by department_id;
select max(salary), department_id  from employees group by department_id
--多表连接
--隐式连接
select *  from employees ,department;
Select * from product, productdir;
--内连接
select * from employees e 
       left join employees em on(e.manager_id=em.employee_id)
--查询大于公司平均工资的员工姓名
--两部:先查询出平均工资
select avg(salary) from employees;
--将上一个查询条件当成下一个语句的条件
select first_name ,salary from employees where salary>(select avg(salary) from employees);
--请查询David与James的所有部门的所有员工
select first_name ,salary from employees
       where department_id 
       in (select department_id from employees where first_name in ('David','James'));
--案例题一：查询1995，1996，1997，1998年各个部门新进员工数
--先查询出几个年份新进的员工


select to_char(hire_date,'yyyy') "年份" ,department_id,count(employee_id) 
       from employees where to_char(hire_date,'yyyy') in (1995,1996,1997,1998)            
            group by to_char(hire_date,'yyyy'),department_id;
--查询出 人员最多的三个部门
--分页需要使用top-n表达式,但是top-n并非关键字,只是一种语法 的称呼
select t.department_id,t."人数",rownum  from (
select department_id,count(employee_id) "人数" from employees 
       group by department_id     
             order by  "人数")t
             where rownum<=5
        
 //查询出每个部门的员工人数  并且安装人数进行排序         
 select department_id did,count(employee_id) nm from employees  group by department_id order by nm        
//取出前5的人员
select * from (
      select t.did,t.nm ,rownum rm from (
          select department_id did,count(employee_id) nm from employees  group by department_id order by nm   
           )t where rownum <=10
      ) t2 where rm>5


Create table CJ(name char(10),subject char(10),result int);
insert into CJ(name,subject,result) values('张三','语文',99);
insert into CJ(name,subject,result) values('张三','数学',86);
insert into CJ(name,subject,result) values('张三','英语',75);
insert into CJ(name,subject,result) values('李四','语文',78);
insert into CJ(name,subject,result) values('李四','数学',85);
insert into CJ(name,subject,result) values('李四','英语',78);
---交叉表
select t.name,
       sum(decode(trim(subject),'语文',t.result,0)) as "语文",
        sum(decode(trim(subject),'数学',t.result,0)) as "数学",
         sum(decode(trim(subject),'英语',t.result,0)) as "英语"
from cj t
     group by name
---拷贝表
insert into employees2   select * from employees ;
create table employees3 as select * from employees;
create table cj_bak as select * from cj where 1<>1  //只拷贝表结构
--修改
update employees3 set salary=salary+1000; 
     
--删除
delete employees3 where employee_id=2;     
--合并
select * from order2014 
union all
select * from order2015
---事务
insert into product_new(id,name) values (10,'音响');
SAVEPOINT undo1;
insert into product_new(id,name) values (11,'音响');
rollback to undo1;
--主键就可以直接这样写：
insert into employees(employees_id,last_name….) 
       values(emp_seq.nextval,’zhang’……)