oracle2(单行函数，多行函数，转换函数，其他函数，分组，增删改)

练习

select * from emp;
1.选择部门30中的所有员工.
select * from emp where deptno = 30 ;
2.列出所有办事员(CLERK)的姓名，编号和部门编号.
select empno,ename,deptno from emp where job = 'CLERK';
3.找出佣金高于薪金的员工.
select * from emp where comm > sal;
4.找出佣金高于薪金的60%的员工.
select * from emp where comm > sal*0.6;
5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job='CLERK';
6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job='CLERK' or job <>'MANAGER' and job<>'CLERK' and sal >=2000;
7.找出收取佣金的员工的不同工作.
select distinct job from emp where comm is not null ;
8.找出不收取佣金或收取的佣金低于100的员工.
select  ename from emp where comm is null or comm < 100;
10.找出早于12年前受雇的员工.
select * from emp  where hiredate < '01-1月-1982' order by hiredate ;
11.以首字母大写的方式显示所有员工的姓名.
13.显示不带有"R"的员工的姓名.
select ename from emp where ename not like '%R%';
17.显示员工的详细资料,按姓名排序.
select * from emp  order by ename desc;
18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename ,hiredate from emp order by hiredate asc;
19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select ename ,job,sal from emp  order by job desc,sal desc;
20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
select ename from emp where name like '%A%';

小括号

-- 查询工作为SALESMAN,MANAGER并且工资为2500的员工工资
   --and的执行等级高于or的执行等级
   --可以使用小括号将执行等级进行提升
   select * from emp where (job= 'SALESMAN' or job = 'MANAGER') and sal = 2975;

单行函数

--oracle  单行函数(这些函数是oracle的内置函数，在mysql中不一定能用)
   --不改变真实数据，对真实数据的进一步处理和显示
   --格式  select 函数名(字段名) from 表名 ；
   --字符函数
         select initcap(ename) from emp; --首字母大写 initcap(字段名)
         select lower(ename) from emp; --所有都是小写，lower(字段名)
         select upper(ename) from emp; --字段中所有的都是大写，upper(字段名)
         select lower('HH') from emp; --小写，里面也可以不是字段名，可以是字符
         select ltrim('abcdef','abc') from emp; --左裁剪 ltrim(char,set)
         select rtrim('abcdef','abc') from emp; --右裁剪，rtrim(char,set)
         select translate('jack','abcd','1234') from emp; --按字符翻译，translate(char,from,set);
         select replace("aaaccc",'a','b') from emp; --字符替换，replace(char,search_str,replace_str)
         select instr('abcdef','a') from emp; -- 查找字符，instr(char,sbustr[,pos])
         select substr('bcadef',3,2) from emp; --取出子字符串，substr(char,pos,len)
         select concat('aaa','bbb') from dual;  --连接字符串，concat(char1,char2)
   --数值函数
         --数据库中存在一张伪表，不是真实存在的，是为了方便进行数据验证而临时存在的表，表名是dual
         --abs(n) 取绝对值
         -- ceil(n) 向上取整 
         --sin(n) 正弦
         -- cos(n) 余弦
         -- sign(n) 取符号
         -- floor(n) 向下取整
         -- power(m,n) m的n次幂
         -- mod(m,n) 取余数
         -- round(m,n) 对于小数点的后几位的四舍五入
         -- trunc(m,n) 对于小数点的后几位的截断
         -- sqrt(n) 开平方根
         select abs(-22),ceil(2.3),sin(23),cos(56),sign(-23),floor(3.6),power(3,2),mod(9,4) from dual;
         select round(93.36,1) ,trunc(100.2365,2),sqrt(3) from dual;
   -- 日期函数
      --返回两个日期之间的月份
      select months_between('01-5月-09'，'01-2月-04') from dual;
      -- 将月份加到日期上形成新的日期
      select add_months('02-2月-03'，2) from dual;
      select add_months('02-2月-03'，-2) from dual;
      --返回指定日期后的星期对应的新日期
      select next_day('15-7月-2020','星期一') from dual;
      --返回当月的最后一天
      select last_day('15-7月-2020') from dual;
      -- 按指定的格式对日期进行四舍五入
      --年的四舍五入是按照月的大小，月的四舍五入是按照天的大小，天的四舍五入是按照所在的周的位置进行四舍五入
      --注意它的格式
      select round(to_date('15-7月-2020'),'YEAR') from dual;
      select round(to_date('18-6月-2020'),'MONTH') from dual;
      select round(to_date('15-7月-2020'),'DAY') from dual;

多行函数

--多行函数 (max,min,sum,count,avg)
-- select 函数名(字段名) ，函数名(字段名) from 表名
--注意，多行函数不可以跟单行函数以及字段名同时使用，除非是分组
--多行函数之间可以相互使用
   -- max(字段名) 返回当前字段的最大值
   --min(字段名) 返回当前字段的最小值
   --avg(字段名) 返回当前字段的平均值
   --sum(字段名) 返回当前字段的和
   --count
          --count(*) 返回当前受影响的行数
          --count(字段名) 返回当前不为空的字段值的数量
          --count(distinct 字段名) 返回当前重复的字段名的数量
   --查询当前员工的最高工资
   select max(sal) from emp; 
   -- 查看员工的最低工资
   select min(sal) from emp;
   --查看员工的平均工资
   select avg(sal) from emp;
   --查看员工的工资之和
   select sum(sal) from emp;
   --查看员工的数量
   select count(*) from emp; --返回的是当前受影响的行数
   --查看有津贴的员工的数量
   select count(comm) from emp; --返回当前查询字段的值的数量，null自动过滤
   --查看当前员工的工作种类
   select count( distinct job) from emp; --取出重复的字段的值的数量

转换函数和其他函数

to_number(),to_char(),to_date(),nvl(),nvl2(),decode()

--转换函数 to_number(),to_char(),to_date()
--数值可以于字符相互转换，字符和日期可以相互转换，日期和数值不可以相互转换
  --to_number('数值类型的字符')
  --字符转换数字 char->unmber
        select to_number('123') from dual;
  --数字转换字符 number->char
        --指示显示的格式：
           -- 9表示位置占位，例如999，999，999是将数字按每三个一组分开，
           --L表示人民币的符号，$表示美元符号，
           -- 0也可以表示位置占位，但真实数位不足时，会使用0进行不为，若个数比数字少也不可以。
           select to_char(123456789,'999,999,999') from dual;
           select to_char(123456789,'L999,999,999') from dual;
           select to_char(123456,'$999,999') from dual;
           select to_char(123456,'000,000,000') from dual;
  --数值和字符之间可以隐式转换，to_number可以省略
         --查找工资大于2000的员工信息
           select * from emp where sal > '2000';
           select * from emp where sal > to_number('2000');
  --字符转换日期 char->date
        --使用to-date(字段名,'格式')
           --字符必须符合你设置的格式，
           --oracle默认的日期格式是日月年
           --常用的日期格式：yyyy-mm-dd,yyyy/mm/dd;
           查找员工入职在82年之后的员工信息
           select * from emp where hiredate > to_date('1982-01-01','yyyy-mm-dd');
           select * from emp where hiredate > to_date('1982/01/01','yyyy/mm/dd');
  --日期转换字符 date->char 不指定转换格式，使用默认的转换格式，
        --常用的转换格式：yyyy-mm-dd,yyyy/mm/dd,yyyy"年"mm"月"dd"日"。
        --to_char('要转换的日期')
        select to_char(hiredate) from emp;  --使用默认的转换格式
        select to_char(hiredate,'yyyy-mm-dd') from emp;
        select to_char(hiredate,'yyyy/mm/dd') from emp;
        select to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;
        查找员工入职在82年之后的员工信息
        select * from emp where to_char(hiredate,'yyyy/mm/dd') > '1982/01/01';
------------------------------------------------------------------------------------------
--其他函数 nvl(),nvl2(),decode();
      --nvl(字段名，值) 如果字段值不为null,则返回字段的值，若字段为空，则返回字段设置的值，相当于if
      查找员工的薪资的详细信息(着个计算sal+comm，comm有为空，相加sal加空也就成空了，但我们是
      想让它有comm就加，没有就是sal的值，使用nvl()函数)
      select ename,sal+nvl(comm,0) from emp;
      --nvl2(字段名，处理值1，处理值2) 如果字段不为空，则执行处理1，如果字段为空，则执行处理2
      --相当于if，else,
       查找员工的薪资的详细信息
       select ename,nvl2(comm,comm+sal,sal) from emp; 
      --decode(字段名，值1，处理值1，值2，处理值2，...公共处理值)
      --若字段的值和decode中的条件值相同，则执行对象的处理，如果都没有则执行公共处理，相当于
      --if ,else if,else if,else.
      select ename,decode(job,'MANAGER','经理','PRESIDENT','董事长','SALESMAN','部长','职工') from emp;
     
      

分组

--分组
--关键字 group by 字段名，字段名1，...
  --注意：
      --select和from之间只允许出现多行函数和分组字段名；
      --如果是多字段分组，先按第一个字段进行分组，在按照第二个字段分组，依次往后。
      --where中不允许出现多行函数（where是对条件的筛选，而多行函数是对筛选后结果的执行）
--分组筛选：关键字 having
--group by 字段名 having 筛选条件；
  --注意：
    --对于筛选后的多行函数进行筛选
    --针对分组进行分组后的数据筛选，允许使用多行函数，必须于group by 一起使用
--where与having比较
  --where子句不允许出现多行函数，having中允许出现多行函数，
  --where和having都可以对普通字段进行筛选，但是where的执行效率比having的高
    --where的执行效率(通过看下面的的sql语句)
    -- from-> where->group by->select->order by
    select deptno,job from emp where deptno > 10 group by deptno,job order by deptno;
    --having的执行效率(通过看下面的的sql语句)
    --from->group by->select->having->order by
    select deptno,job from emp group by deptno,job having deptno > 10 order by deptno;
    --注意：
          --在分组语句中，使用where进行普通字段的筛选，使用having进行多行函数的筛选。
-----------------------------------------------------------------------------------
--练习
查找最高工资和员工数
select max(sal),count(*) from emp;
查找不同部门的最高工资
select deptno,max(sal) from emp group by deptno;
查找不同工作岗位的员工数
select job,count(*) from emp group by job;
查找不同部门不同工作的员工数
select deptno,job,count(*) from emp group by deptno,job order by deptno;
查找不同部门不同工作的并且人数大于1的
select deptno,job,count(*) from emp group by deptno,job having count(*) > 1 order by deptno;
查找部门号大于10的不同部门不同工作的人数
select deptno ,job,count(*) from emp where deptno > 10 group by deptno,job order by deptno;
select deptno ,job,count(*) from emp group by deptno,job having deptno > 10 order by deptno;

增删改和数据备份和整体插入

--数据的增删改及数据的备份
--注意：
  -- 增删改的数据SQL语句执行完毕后，不会立马进行数据的写入，而是保存在内存中，需要对数据进行手动的
  --的提交，如数据有问题可以进行回滚操作
  --主键：
     --在一张表中，某个字段的值是非空唯一的，将此字段设置为主键；
     --主键作用：唯一的标识一条数据
  select * from dept;
  --增加数据的格式：
     --当有主键是，主键必须给值，允许为空的字段可以不给值
     --插入语句表名后的字段名要和后面赋值的字段一一对应。
     --insert into 表名(字段名，字段名1...) values(值，值1...)
     --insert into 表名 values(值1，值2...) 全字段插入，可以省略字段名称；
              --增加数据部门信息(部门编号，名称，地点)
              insert into dept values(50,'LOL','北京');
              --增加一条数据只有部门编号，名称
              insert into dept(deptno,dname) values(60,'ASD');
  --删除数据
    --格式：
    delete from 表名 删除表中所有的数据
    delete from 表名 where 条件 删除指定条件的所有的数据
    truncate table 表名  删除表中所有数据，它比delete的效率高。
    drop from 表名 删除表。
  --更新数据
    --格式：
       --update 表名 set 字段名=值,字段名=值1； 改变的是表中对应所欲字段名下的数据
       --update 表名 set 字段名=值，字段名=值1 where 条件 ，改变的是当前筛选条件下的字段名
  --数据的备份（备份的只是表结构和表数据，约束不会备份（例如主键，此时可以添加主键相同的数据））
    --全部备份
    --格式：create table 新的表名 as select * from 表名；
    create table deptBak as select *  from dept;     
    select * from deptBak; 
    --部分备份
    --格式：create table 新的表名 as select 字段名，字段名 from 表名
    create table deptBak1 as select deptno,dname from dept;
    select * from deptBak1; 
    --数据的整体插入(查询语句结果的字段数据必须和插入表名的字段名数量一致，结构一致)
    --格式：insert into 新的表名 select * from 表名;
     delete from deptBak;
    insert into deptBak select * from dept;   
    select * from deptBak;     
     

练习

select * from emp;
--1.选择部门30中的所有员工.
select * from emp where deptno = 30 ;
--2.列出所有办事员(CLERK)的姓名，编号和部门编号.
select ename,empno,deptno from emp where job='CLERK';
--3.找出佣金高于薪金的员工.
select * from emp where comm > sal;
--4.找出佣金高于薪金的60%的员工.
select * from emp where comm > sal* 0.6;
--5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
--6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (job <>'MANAGER' and job = 'SALESMAN' and  sal >=2000);
--7.找出收取佣金的员工的不同工作.
select distinct job from emp where comm is not null;
--8.找出不收取佣金或收取的佣金低于100的员工.
select * from emp where (comm is null) or (comm < 100);
--9.找出各月倒数第3天受雇的所有员工.
select * from emp where hiredate = last_day(hiredate) -2;
--10.找出早于12年前受雇的员工.
select * from emp where hiredate < to_date('1982/01/01','yyyy/mm/dd');
select * from emp where to_char(hiredate,'yyyy/mm/dd') < '1982/01/01';
--11.以首字母大写的方式显示所有员工的姓名.
select initcap(ename) from emp;
--12.显示正好为5个字符的员工的姓名.
select ename from emp where length(ename) = 5;
--13.显示不带有"R"的员工的姓名.
select ename from emp where ename not like '%R%';
--14.显示所有员工姓名的前三个字符.
select substr(ename,1,3)  from emp  ;
--15.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') from emp;
--16.显示满10年服务年限的员工的姓名和受雇日期.
select ename ,hiredate from emp where months_between(sysdate,hiredate)/12>=10;
select ename,hiredate from emp where (sysdate-hiredate)/365>10;
--17.显示员工的详细资料,按姓名排序.
select * from emp  order by ename;
--18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename ,hiredate from emp order by hiredate ;
--19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select ename,job,sal from emp order by job desc,sal;
--20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
select ename,to_char(hiredate,'yyyy') years，to_char(hiredate,'mm') ms from emp order by ms,years ;
--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
select trunc(sal/30,0) dailSal from emp;
select floor((sal+nvl(comm,0))/30) from emp;
--22.找出在(任何年份的)2月受聘的所有员工。
select * from emp where to_char(hiredate,'mm')='02';
--23.对于每个员工,显示其加入公司的天数.
select ename,floor(sysdate-hiredate) 天数 from emp;
--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
select ename from emp where instr(ename,'A') > 0;
select ename from emp where ename like '%A%';
--25.以年月日的方式显示所有员工的服务年限.
select to_char(hiredate,'yyyy"年"mm"月"dd"日"') 服务年限 from emp;
select ename , hiredate ,floor((sysdate-hiredate)/365) ,floor(mod((sysdate-hiredate)/30,12)),floor(mod(sysdate-hiredate,30)) from emp;