第一章：基本的select查询&函数



--基本的select查询语句

--查询所有的部门
select *
  from departments;

--查询所有的部门,只看部门号和位置号
select department_id,location_id
  from departments;  

--在select子句中，可以使用算术表达式，对查找到的列
--做各种计算

--查看雇员薪水加上300以后的结果
select last_name,salary,salary+ 300
  from employees;

--使用小括号可以改变运算的优先级
--先把雇员的薪水加上100，然后计算年薪并显示
select last_name,salary,12*(salary+100)
  from employees;

--null值
--一些列具有空值（null）。null值是未知的值。
--null值是如何进入到表中的？当向表里插入行时，
--如果不给某个列明确地赋值，那么它将自动得到null值。
--（注意，null值和列的数据类型无关）

--在算术表达式中如果包含null值，那么整个表达式的结果就为null
select last_name,salary,commission_pct
  from employees;

select last_name,salary,commission_pct
      ,12*salary*commission_pct
  from employees;  


--使用列别名
--直接跟在列名后面来定义。可以选用as关键字也可以不用
--列别名会将查询返回的结果集的列标题改名字。

--以下3种情况，列别名必须加双引号：
--1）列别名中包含空格
--2）列别名中包含特殊字符
--3）列别名是大小写敏感的

select last_name as name,commission_pct comm
  from employees;

select last_name as "Name",12*salary"Annual Salary "
  from employees;  

--使用串联操作符：||
--该操作符将字符串首尾相连。

select last_name||job_id "Employees"
  from employees;

--在select语句中可以使用字面量（常量）：  
--定义：对于字符串和日期字面量，必须用单引号括起来。
--对于数字字面量，直接写。

--字面量用在select子句中，那么他会对查询返回的每一行输出一次

select last_name || ' is a ' || job_id "employees details"
  from employees;

--以下查询的结果集？
select 100
  from employees;

select last_name 姓名
  from employees;

--使用distinct关键字去掉查询结果集中的重复的行
--何谓重复的行？
--任意两行在列或者列的组合上有完全相同的值
--distinct影响其后所有的列

--查询所有雇员所分布的部门的部门号.有重复的
select department_id
  from employees;

--查询所有雇员所分布的部门的部门号.去掉重复的部门
select distinct department_id
  from employees;  

select distinct department_id,job_id
  from employees;   

--使用desc命令查看表的结构
desc departments;

--使用where子句限制查询返回的行
--where子句又叫做过滤条件，其后的条件表达式必须返回
--true或者false。
--工作过程：对于表中的每一行，都取出某个或某些特定列的值，
--带入到where条件中进行比较判断，如果比较结果为true，则
--这一行就出现在最终的结果集中；否则，这一行被抛弃。

--查询部门90 的所有雇员
select last_name,salary,department_id
  from employees
  where department_id=90;

--在where中使用字面量
--1）对于字符串和日期字面量，一定要用单引号括起来
--2）对于字符串字面量，特别注意：它是大小写敏感的

--查询雇员惠伦的信息
--返回0行
select last_name,salary
  from employees
  where last_name='whalen';

--查询雇员惠伦的信息
--返回1行
select last_name,salary
  from employees
  where last_name='Whalen';

--对于日期字面量，一定注意它是格式敏感的。
--日期值的格式一定要满足默认的日期显示格式。

--查询2007年2月7号入职的雇员
select last_name,hire_date
  from employees
  where hire_date='2007-02-07';

--查询2007年2月7号入职的雇员
--ORA-01858: 在要求输入数字处找到非数字字符
select last_name,hire_date
  from employees
  where hire_date='07-feb-07';

--查看数据字典得到客户端默认的日期显示格式
select NLS_DATE_FORMAT
  from nls_session_parameters;

--在where条件中使用各种操作符
--1）=、>,<,<=,>=,不等于：<>,!=,^=
--查询薪水小于等于3000的雇员
select last_name,salary
  from employees
  where salary <= 3000;

--2)between 下限值  and 上限值:
--测试某个列值在一个取值范围之内
--查询薪水在2500和3500之间的雇员
select last_name,salary
  from employees
  where salary between 2500 and 3500;

--3)in(值的列表)：
--测试某个列值是否等于列表中的任何一个值。如果相等则返回true
--in操作在数据库内部等同于or操作
--IN操作符可用于任何数据类型
--查询经理编号为101或者102或者201的雇员的信息
select last_name,salary,manager_id
  from employees
  where manager_id in (101,102,201);

--4)like'匹配模式'：
--like操作符后面必须跟一个字符串字面量，该字面量作为
--一个匹配模式来使用。like又叫做模糊查询或者通配符查询
--匹配模式中只有两个特殊字符：
-- %：可表示0个或任意多个字符
-- _：可表示任意一个字符

--查询姓名以大S开头的雇员的姓名
select first_name
  from employees
  where first_name like 'S%';

--查询姓名的第二个字母为o的雇员的姓名
select last_name
  from employees
  where last_name like '_o%';

--如果要搜索的字符串本身就包含_或%字符，那么
--需要使用escape选项先指定一个转义字符，然后把
--转义字符放在_或%字符前面即可

--查询工作编号包含字符“SA_”的雇员的信息
select last_name,job_id
  from employees
  where job_id like '%SA\_%' escape '\';

--5)isnull操作符:
--测试一个列值是否为null值

--查询经理编号为null的雇员
select last_name,manager_id
  from employees
  where manager_id is null;

--切记不能这么写：
select last_name,manager_id
  from employees
  where manager_id = null;  

--在单个的WHERE子句中，通过使用AND和OR操作符，
--可以把多个条件连接起来
--1）and：逻辑与操作
--只有当每个子条件都返回true时，and的结果才为true。否则，
--and的结果都是false

--查询月薪大于等于10000元并且工作编号包含
--字符串MAN的雇员的信息
select last_name,salary,job_id
  from employees
  where salary >= 10000
    and job_id like '%MAN%';

--2）or：逻辑或操作
--只要有一个子条件返回true，or的结果就为true。否则，
--or的结果都是false

--查询月薪大于等于10000元或者工作编号包含字符串MAN的雇员的信息
select last_name,salary,job_id
  from employees
  where salary >= 10000
    or job_id like '%MAN%';

----3）not：逻辑非操作
--如果子条件返回true，not的结果就为false。否则，
--not的结果是true

--not操作符一般不单独使用，它和其它操作符一起用。例如：
--notin,notlike,notbetween  and,isnot null,

--查询工作编号不是IT_PROG或者 ST_CLERK或者 SA_REP的雇员
--的姓和工作编号
select last_name,job_id
  from employees
  where job_id not in ('IT_PROG','ST_CLERK','SA_REP');


--当where子句中条件较多时，使用小括号来决定计算的优先级。
--这样写同时使得代码可读性很高
--选择雇员是总裁或者销售代表，并且月薪大于15000的行
select last_name,salary,job_id
  from employees
  where (job_id='SA_REP'
    or job_id='AD_PRES')
    and salary > 15000;

--使用orderby子句对查询返回的结果集进行排序
--语法：
--[ORDERBY    {column,expr,numeric_position} [ASC|DESC]];

--orderby子句是最后执行的子句
--asc：指明是升序排序，默认值
--desc：指明是降序排序

--将所有雇员按照入职日期做升序排序
--1)直接指定排序列
select last_name,salary,hire_date
  from employees
  order by hire_date;

-- --将所有雇员按照入职日期做降序排序 
select last_name,salary,hire_date
  from employees
  order by hire_date desc;  

--2）可以使用表达式或者列别名来排序
--按照雇员的年薪大小进行排序
select last_name,salary,12*salary annsal
  from employees
  order by annsal;

select last_name,salary,12*salary annsal
  from employees
  order by 12*salary;  

--3）指定一个数字位置来排序
--该数字表示select列表的的第几个列
----按照雇员的部门号进行排序
SELECT last_name,salary,department_id,job_id
  from employees
  order by 3;

--4）排序列可以有多个
--将雇员先按照部门排序，部门相同的雇员按照薪水做降序排序
select last_name,department_id,salary
  from employees 
  order by department_id,salary desc;

--在sql语句中，可以使用替代变量接收用户的输入
--替代变量有两种语法形式：
--带一个&符号的和带两个&&符号的

--使用替代变量输入数字时，不需要给替代变量加单引号

--根据用户输入的雇员编号，查找雇员的信息
select employee_id,last_name,salary
  from employees
  where employee_id = &emp_id;

--使用替代变量输入字符串或者日期时，必须给替代变量加单引号  

--根据用户输入的工作编号，查找相应的雇员
select last_name,salary,job_id
  from employees
  where job_id = '&job_title';

--根据用户输入的工作编号，查找相应的雇员
--假设输入SA_REP
--ORA-00904:"SA_REP": 标识符无效
select last_name,salary,job_id
  from employees
  where job_id = &job_title;

--以f5执行以上语句，可以看到替代变量的赋值过程
select last_name,salary,job_id
  from employees
  where job_id = &job_title;

--使用set verifyoff命令可以隐藏替代变量的替换过程
set verify off

--观察以下查询，有两个同名的替代变量：
--输入几次？
select employee_id,last_name,salary,&column_name
  from employees
  order by &column_name;

--使用带两个&&符号的替代变量，同名变量只需要输入一次
select employee_id,last_name,salary,&&column_name
  from employees
  order by &column_name;

--和替代变量有关的两条命令：
--define:定义一个替代变量
--undefine:销毁一个替代变量

--定义一个名为employee_num的替代变量。注意变量名前面没有&符号
define employee_num=200;

select employee_id,last_name,salary
  from employees
  where employee_id = &employee_num;

undefine employee_num;

--在select语句中使用函数
--单行函数：这种函数对查询返回的每一行会执行一次，并
--返回一个结果

--可以分为5种：
--字符串函数、数字函数、日期函数、通用函数和类型转换函数

--1、字符串函数
--1）大小写转换函数：lower、upper、initcap

--直接使用select语句来调用函数
select lower('SQL Course'),upper('SQL Course'),
     initcap('SQL Course')
  from dual;

--dual表是oracle中的一个特殊的系统表，它属于sys用户的。
--它永远只有一行一列。主要设计用来完成select语句的语法。

desc dual;

select * from dual;

--大小写转换函数可用在where条件中，对列值做转换
--查找雇员Higgins

select last_name,salary
  from employees
  where lower(last_name)='higgins';


--2)concat:将两个字符串参数首尾相连返回。等同于||操作符
select concat('Hello','World')
  from dual;

--3)substr:从一个源字符串中取出一个子串返回
select substr('Hello World',3,3)
  from dual;

select substr('Hello World',5)
  from dual;  

--4)instr:从一个源字符串中找到指定的子串出现的位置
select instr('Hello World','o',1)
  from dual;  

select instr('Hello World','o',1,2)
  from dual;  

--5)length：返回字符串的长度
select length('hello')
  from dual;

select length('你好')
  from dual;

--6）lpad和rpad：在一个源字符串的左边或者右边填充指定数量的字符并返回
select lpad('hello',10,'*') rs1,
       rpad('hello',10,'*') rs2
  from dual;

--7)trim:去掉字符串的首尾空格
select trim(' hello ') rs
  from dual;

--去掉字符串首部或者尾部的1个字符  
select trim(leading'h' from'hello') rs
  from dual;  

select trim(trailing'o' from'hello') rs
  from dual;

----8)ltrim/rtrim:去掉字符串的首部或者尾部空格
select ltrim(' hello ') rs1,rtrim(' hello ') rs2
  from dual;

--9)replace:在源字符串中查找一个子串，找到后把子串替换成
--另一个子串
select replace('Jack and Jue','J','Bl')
  from dual;

--数字函数：3个
--round：将数字四舍五入到指定的小数位
--trunc：将数字截断到指定的小数位（不四舍五入）
--mod：返回两个整数相除后的余数

--注意：小数位为负的意味着从小数点的左边进行四舍五入或截断
select round(45.923,2),round(45.923,0),
       round(45.923,-1),round(45.923,-2)
   from dual;

select trunc(45.923,2),trunc(45.923,0),
       trunc(45.923,-1),trunc(45.923,-2)
   from dual;

select mod(5,2)
  from dual;


--日期函数
--1）sysdate：返回数据库的当前日期和时间
select sysdate
  from dual;

--ORA-00923: 未找到要求的FROM 关键字
select sysdate()
  from dual;  


--2）current_date：返回用户会话（客户端）的当前日期和时间
select current_date
  from dual;  

--因为数据库把日期作为数字存储，因此可以对日期进行加减运算。
--给日期加3天
select sysdate+3
  from dual;

--给日期减3天
select sysdate-3
  from dual;  

----给日期加2个小时
select sysdate + 2/24
  from dual;

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
       to_char(sysdate + 2/24,'YYYY-MM-DD HH24:MI:SS')
  from dual;

--两个日期相减返回相差的天数
--查询雇员在公司一共工作了几周
select last_name,
      (sysdate - hire_date)/7  weeks
from employees;

--MONTHS_BETWEEN(date1, date2):  
--返回两个日期差几个月。结果的小数部分代表月的一部分
select months_between(sysdate,hire_date)
  from employees;

--ADD_MONTHS(date, n): 给日期加几个月。N是整数可以为负数
select add_months(sysdate, 5)
  from dual;

--NEXT_DAY(date, ‘char’): 找到从date开始的下一个星期几的日期。char表示星期几
--找到下一个星期三是几月几号
select next_day(sysdate,'星期三')
  from dual;

--ORA-01846: 周中的日无效  
select next_day(sysdate,'Wensday')
  from dual;

--ROUND(date[,'fmt']): 
--TRUNC(date[,'fmt']): 
--将日期按照给定的格式模型month或者year进行四舍五入或者截断

select round(sysdate,'month'),trunc(sysdate,'month')
  from dual;

select round(sysdate,'year'),trunc(sysdate,'year')
  from dual;

select trunc(sysdate)
  from dual;

--类型转换函数
--oracle中的类型转换有两种：
--隐式类型转换：oracle自动做的
--显式类型转换：使用函数来实现的

--尽量避免在sql语句中发生自动类型转换，因为
--它可能会对执行计划的生成带来负面影响

--类型转换函数：3个
--1）to_char(date，'fmt model'):将日期
--按照指定的日期格式模型转换成字符串
--常用的日期格式元素：YYYY、MM、MON、DD、DAY等等

select last_name,hire_date,
      to_char(hire_date,'YYYY/MM/DD')
  from employees;

select last_name,hire_date,
      to_char(hire_date,'fmDD MONTH YYYY')
  from employees;  

select last_name,hire_date,
      to_char(hire_date,'fmday month year')
  from employees;

--2）to_char(number，'fmt model'):将数字
--按照指定的数字格式模型转换成字符串
--常用的数字格式元素：9、0、$、L、. ,等等
--注意：9的个数决定了数字的宽度

--将salary列格式化显示
select last_name,salary,
       to_char(salary,'L99,999.00')
  from employees;

--数字宽度不够显示#号  
select last_name,salary,
       to_char(salary,'L9,999.00')
  from employees;

--3）to_numer(char[,'fmt model']):
--将字符串转成数字

select to_number('123')
  from dual;

select to_number('$1,123.78','$9,999.99')
  from dual;  

----4）to_date(char[,'fmt model']):
--将字符串转成日期

select to_date('2005/05/12','YYYY/MM/DD')
  from dual;

--考虑到转换的通用性，月份不要使用字母来表示，而是使用数字
--ORA-01843: 无效的月份
select to_date('2005/oct/12','YYYY/MON/DD')
  from dual;

--在where条件中，如果要对日期值做比较，一般都是用
--to_date函数提供一个真正的日期值来进行比较，避免
--隐式类型转换

--查询2005-01-01之前入职的雇员
select last_name,hire_date
  from employees
  where hire_date < to_date('2005 01 01','YYYY MM DD');

--函数的嵌套使用
--把一个函数调用作为参数直接传给另一个函数，叫做函数嵌套。
--单行函数可以嵌套任何深度

select last_name,
       upper(concat(substr(last_name,1,8),'_us'))
  from employees;

--通用函数
--都可以对null值做转换处理
--有：nvl、nvl2、nullif、coalesce

--nvl(列名，value):
--如果第一个参数的值不为null，则直接返回它；
--如果第一个参数的值为null，则返回第二个参数值value

--要求两个参数的类型必须相同

--计算每个雇员的年收入（年薪+年佣金）
select last_name,commission_pct,
       (12*salary)+(12*salary*nvl(commission_pct,0)) ann_sal
   from employees;

--NVL2(expr1, expr2, expr3)
--NVL2函数解释第一个表达式。如果它的值不为null，
--函数返回第二个表达式。如果第一个表达式的值为null，
--函数返回第三个表达式。 

--显示雇员的收入构成
select last_name,commission_pct,
       nvl2(commission_pct,'SAL+COMM','SAL') income
   from employees;

--3/NULLIF (expr1, expr2):
--NULLIF比较expr1and expr2。如果它们相等则函数
--返回null。如果它们不等，函数返回expr1。
--但是，你不能指定expr1是一个字面量null。

select nullif('hello','hello')
  from dual;

select nullif('hello1','hello')
  from dual;

--ORA-00932: 数据类型不一致: 应为-, 但却获得 CHAR
select nullif(null,'hello')
  from dual;  

--COALESCE (expr1, expr2, ... exprn)
--返回列表中第一个非null的表达式
--注意，所有的表达式必须是一样的类型

--ORA-00932: 数据类型不一致: 应为NUMBER, 但却获得 CHAR
select last_name,
       coalesce(commission_pct,manager_id,'no comm and  no manager')
from employees;

select last_name,
       coalesce(to_char(commission_pct),
         to_char(manager_id),'no comm and  no manager')
from employees;