Oracle02

SQL> desc s_emp;

Name                                      Null?    Type

----------------------------------------- -------- --------------

ID  员工编号                                                                NOT NULL NUMBER(7)

LAST_NAME 姓                                                                NOT NULL VARCHAR2(25)

FIRST_NAME 名                                                                                VARCHAR2(25)

USERID    用户编号                                                                        VARCHAR2(8)

START_DATE 入职日期                                                                      DATE

COMMENTS  备注                                                                            VARCHAR2(255)

MANAGER_ID  直接领导的员工编号                                              NUMBER(7)

TITLE        职位                                                                        VARCHAR2(25)

DEPT_ID      部门编号                                                                  NUMBER(7)

SALARY      月薪                                                                          NUMBER(11,2)

COMMISSION_PCT  提成                                                                    NUMBER(4,2)

字段的名字 

字段能不能为空 (可不可以没有值)    not null  代表必须有值

字段的类型    number  代表数字类型 (整数 长整型  浮点 )  varchar2(n) 代表变长字符串

Date  代表日期  （代表java中的Date  Calendar  Timestamp）

1.显示 s_emp  表中的 id  salary  和 部门编号

2.显示员工的名字  名字的构成是 first_name 和 last_name 拼接起来的  在first_name 和

last_name 之间 拼接一个下划线字符串

3.显示s_emp 表中的  月薪  和  年薪    年薪的计算方式是 月薪 乘以 13

4.显示s_emp 表中的  id  manager_id  如果manager_id  为空 则显示成 -1 如果不为空则显示

manager_id  本身

select  id,nvl(manager_id,-1) from s_emp;

5.显示 s_emp 表中的  id  first_name  和 年薪  这次年薪计算方式 是  salary*12 加上

salary*12乘以 提成/100  注意提成可能为空  如果提成为空 则把提成处理成 0 同时给年薪

起个别名 叫 yearsal

select  id,first_name,salary*12+salary*12*(nvl(commission_pct,0)/100)  yearsal

    from  s_emp;

select  id,first_name,nvl(salary*12+salary*12*(commission_pct/100),salary*12)

    from  s_emp;

--------------------------------------------------------------------------------

1.from 子句中数据排重  distinct

  select  salary from  s_emp;

  select  distinct  salary from  s_emp;

    排重只能写在字段的前面    下面的写法错误

  select  salary  distinct  from  s_emp;

  sql 语句 前后数据必须保持 一对一的一致

  select  id,distinct salary  from  s_emp;

  下面的写法是可以的  代表当 id  salary都相同时 就认为是重复的数据

  select  distinct  id,salary from  s_emp;

  select  distinct  title,salary from s_emp;

2.where  子句

  2.1 where子句的作用

    where 子句又叫条件子句,限制表中的行数据返回的  满足条件的数据被选中

                不满足条件的数据被过滤掉。

  2.2  语法

    select  字段名  from  表名  where  条件;

  2.3  两个极限条件               

      1 != 1    恒假  永假

      1 = 1    恒等  永真

      select  id,first_name  from  s_emp;

      select  id,first_name  from  s_emp  where  1 != 1;

      select  id,first_name  from  s_emp  where  1 = 1;

  2.4  数字类型的条件表达

              查询s_emp 表中  显示  id  first_name salary  要求 salary  等于 1400

      select  id,first_name,salary from s_emp where salary=1400;

              查询s_emp 表中  显示  id  first_name salary  要求 salary  不等于 1400

      select  id,first_name,salary from s_emp where salary!=1400;

              查询s_emp 表中  显示  id  first_name salary  要求 salary  小于 1400

      select  id,first_name,salary from s_emp where salary<1400;

  2.5  字符串类型的条件表达

              找出s_emp 表中 first_name 叫 Carmen  的 员工 显示  id  first_name salary

      select  id,first_name,salary from  s_emp where first_name='Carmen';

              找出s_emp 表中 first_name 叫 carmen  的 员工 显示  id  first_name salary 

      select  id,first_name,salary from  s_emp where first_name='carmen';

      SQL 不区分大小写

              字符串的值要严格区分大小写  并且字符串的值 要使用单引号 包起来

  2.6  sql中数学运算符有哪些

      =  !=    >    <  >=  <=

  2.7 sql 提供的运算符

      a.sql中表达一个闭区间 比如 [a,b]

            字段  between  a  and  b

            显示 s_emp 表中的  id  first_name salary  要求salary 在 [1400,2500] 之间

      select  id,first_name,salary  from s_emp

          where  salary between  1400  and  2500;                         

      b.表达一个值 处在一个列表范围内

            字段  in 列表 

            列表的表达是  (a值,b值,c值)

            找出 员工是  31部门  或者是 41部门  或者是 50 部门的员工

      select  id,first_name,dept_id from  s_emp where dept_id in (31,41,50);                       

            找出部门 要求 属于 1号地区  要么3号地区  要么  5 地区的         

      select id,name,region_id from s_dept where region_id in(1,3,5);       

      c.空值的判断运算符

              为什么要有NULL值的判断    除了  is  null 其它运算符 对NULL值判断无效

            找出 提成commission_pct 是 10的员工  显示  id  first_name  commission_pct

      select  id,first_name,commission_pct from s_emp where commission_pct=10;

      select  id,first_name,commission_pct from s_emp where commission_pct!=10;       

      select  id,first_name,commission_pct from s_emp where

          commission_pct is null;

            找出manager_id 是NULL的员工  显示 s_emp 表中的  first_name salary manager_id

      select  first_name,salary,manager_id from s_emp where manager_id is null;

      d.模糊查询  运算符  like

            找出所有姓  李的人?

                    李四  李靖  李世民  李嘉诚   

            找出所有带 龙的人               

                    成龙  龙王爷  龙骑    小龙女 

            核心语法:

        where 字段  like  '统配串';

                  统配串中最重要的两个字符是 统配符:             

        _  代表一个任意字符

        %  代表0-n个任意字符

          练习: 

        找出所有的s_emp 表中 first_name 带  a  的

    select  first_name from  s_emp where first_name like '%a%';   

        找出所有的s_emp 表中 first_name 第二个字符是  a  的

    select  first_name from  s_emp where first_name like '_a%';     

        找出所有的s_emp 表中 first_name 倒数第二个字符是  a 的         

    select  first_name from  s_emp where first_name like '%a_';     

        特殊情况:

      一张表 叫  user_tables  这里面放了所有的表的信息 其中有一个字段  table_name 代表

      所有的表名.

  select  table_name  from  user_tables;

      在上面查询的基础上 找出 所有的 以 S_开头的表名.

  escape 表达的字符后的字符 需要进行转义

  select table_name from user_tables where table_name like 'S\_%' escape '\';

  select table_name from user_tables where table_name like 'S\_\_%' escape '\';                   

  2.8 逻辑运算符

  与        &&      a&&b    数据库中表达 是  a  and  b

    举例:  找出 s_emp 表中  salary  在 [1400,2500] 之间的

    where  salary >=1400 and  salary <= 2500

    找出 s_emp 表中  salary  在 (1400,2500) 之间的

    where  salary > 1400 and  salary < 2500

  或        ||      a||b    数据库中 表达是  a  or  b

    找出 部门编号 在 31 或者 41  或者 50 部门的 员工 显示 id  first_name dept_id

    select  id,first_name,dept_id  from  s_emp  where dept_id=31 or

        dept_id=41  or dept_id = 50;

  非        对立面问题

    =                !=  ^=  <>

    >                <= 

    <                >=

    between a and b    not between a  and b

    in                not  in

    like              not  like   

    is null          is not null  除了这一组 上面的都要注意NULL值

    练习: 找出s_emp 表中 所有提成是NULL的人 显示 id first_name  commission_pct

    select  id,first_name,commission_pct  from s_emp

        where  commission_pct is null;

                找出s_emp 表中 所有提成不是NULL的人 显示 id first_name  commission_pct

    select  id,first_name,commission_pct  from s_emp

        where  commission_pct is  not null;

  2.9 条件的优先级    使用小括号解决

    select  first_name,salary,dept_id

        from  s_emp  where salary > 1000 and dept_id=41 or dept_id=42;

    select  first_name,salary,dept_id

        from  s_emp  where (salary > 1000 and dept_id=41) or dept_id=42;               

    select  first_name,salary,dept_id

        from  s_emp  where salary > 1000 and (dept_id=41 or dept_id=42);           

  3.排序

    3.1 概念

        按照一定的标准  把数据按照一定的顺序(升序 和 降序)排列

    3.2 语法

    select  字段名    from  表名  where 条件    order  by  排序标准  排序方式;

    排序方式:

    升序    默认的顺序  关键字是  asc  (自然顺序  字典顺序)

    降序      关键字是  desc  (反自然顺序  反字典顺序)

    3.3  练习

        按照工资排序  显示 s_emp 表中的  id  first_name  salary

    select  id,first_name,salary  from  s_emp where 1=1 order by salary;     

          按照工资降序排序  显示 s_emp 表中的  id  first_name  salary     

    select  id,first_name,salary  from  s_emp where 1=1 order by salary desc;     

    3.4 按照manager_id 排序 显示 s_emp 表中的 id  first_name  manager_id

    select  id,first_name,manager_id  from  s_emp  order by manager_id;     

    select  id,first_name,manager_id  from  s_emp  order by manager_id desc; 

    NULL 值在排序中作为最大值

    3.5 如果值相同 则可以启用第二排序字段    (多字段排序)

    120kg    60.5   

    120kg    61.0 

    select  id,first_name,manager_id  from  s_emp  order by manager_id,id;                   

    select  id,first_name,manager_id  from  s_emp  order by manager_id,

    first_name desc;

  4.单行函数

    4.1 概念

      单行函数:

            针对sql语句影响的每一行  都返回一个结果  sql语句影响多少行 就返回多少个结果。

      组函数:(统计函数) 

            无论sql语句影响多少行 只返回一个结果 

    4.2 举例

    upper  单行函数

    select  first_name,upper(first_name) uname from  s_emp where id=1;

    select  first_name,upper(first_name) uname from  s_emp where id>1;

    select  first_name,upper(first_name) uname from  s_emp where id<1;

    max    组函数

    select    max(first_name) uname from  s_emp where id=1;

    select    max(first_name) uname from  s_emp where id>1;

    select    max(first_name) uname from  s_emp where id<1;

    4.3 为了学习单行函数  引入了一个单行单列的表  dual

    select  * from  dual;

    select  upper('hello')  from  dual;

    select  lower('HELLO WORLD')  from  dual;           

    4.4 处理字符串的单行函数

    upper(par1)  把字符串变大写

    lower(par1)  把字符串变小写

    length(par1) 求字符串长度

      select  length('hello')  from dual;

    initcap(par1)  把每个单词的首字母变大写

      select initcap('one world one dream')  from dual; 

    concat(par1,par2)  连接两个字符串 并返回

      select concat('aa','bb') from  dual;

      select concat(first_name,last_name) from s_emp;     

      select  'aa' || 'bb' ||'cc' ||'dd'  from dual;

    substr(par1,par2,par3)  截取字符串

      par1 要截取的字符串   

      par2 代表截取的位置  编号从1开始 也可以是负数 -1代表最后一个字符

      par3 截取的长度

      select  substr('hello',0,2) from dual;

      select  substr('hello',1,2) from dual;

      select  substr('hello',2,2) from dual;

            显示s_emp 表中的first_name 以及first_name 对应的前三个字符

        select  first_name,substr(first_name,1,3) from s_emp;     

            显示s_emp 表中的first_name 以及first_name 对应的后三个字符 

        select  first_name,substr(first_name,-3,3) from s_emp;   

    nvl(par1,par2)  要求 par1 和 par2 的类型保持一致 

    replace(par1,par2,par3)  把par1 中出现的par2内容 替换成par3

        select  replace('one world one dream','one','two') from dual; 

    4.5 格式化显示函数 

      to_char(par1,par2)

        par1 是要处理的数据  今天主要用来处理数字类型的数据

        par2 是要显示的格式  可以省略  省略代表把数据变成字符串类型

                          如果par2不省略 则 按照指定的格式显示数据。

        fm  代表格式字符串 的开始 是format的缩写 可以省略

        $  美元符号

        L  本地货币符号    ￥  RMB  和本地语言有关

        9  小数点前代表  0-9 的任意数字

                            小数点后代表 1-9 的任意数字 

        0  小数点前代表 强制显示 前导零

            12345  012,345  9999  009,999 

                            小数点后代表 0-9 的任意数字               

        ,  分割符                   

        .  小数点

    select  to_char(12345.86,'fm$099,999.99') from  dual;     

    select  to_char(salary,'fm$099,999.99') from  s_emp;

    select  to_char(salary,'fm$099,999.00') from  s_emp;

    select  to_char(salary,'fmL099,999.00') from  s_emp;

    AMERICAN_AMERICA.UTF8

    4.6 处理数字的函数

    round(par1,par2)  四舍五入函数  (以后还可以处理日期)

        par1是要处理的数字 

        par2是处理的精度  如果省略代表取整

    select round(9.58) from dual;

    /* 精度是保留小数点后 1 位 对 第二位四舍五入 */   

    select round(9.58,1) from dual;

    select round(9.584,2) from dual;

    select round(12.88,-1) from dual;

    trunc(par1,par2)  截取函数  (以后还可以处理日期)

        par1是要处理的数字 

        par2是处理的精度  如果省略代表切掉小数点后的

    select trunc(9.58) from dual;

    select trunc(9.99) from dual;

    /* 精度是保留小数点后 1 位 对 第二位截取 */   

    select trunc(9.58,1) from dual;

    select trunc(9.584,2) from dual;

    /* 对小数点前的一位进行截取 */

    select trunc(19.13,-1) from dual;    

    4.7 函数嵌套

        把一个函数的返回值 作为另一个函数的参数 

      显示 s_emp 表中的first_name  和 first_name 的前三个字符 并把这前三个字符 变大写

    Carmen    CAR

    test      TES

    select  first_name,upper(substr(first_name,1,3)) from s_emp;                

      显示 s_emp 表中的first_name  和 first_name 的后三个字符 并把这前三个字符 变大写

            不允许使用负数编号

  select  first_name,upper(substr(first_name,-3,3)) from s_emp;  

  select  first_name,upper(substr(first_name,length(first_name)-2,3)) from s_emp;