- -基本的 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) is null操作符:
- -测试一个列值是否为 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操作符一般不单独使用，它和其它操作符一起用。例如：
- - not in, not like, not between   and, is not 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;

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

- - order by子句是最后执行的子句
- - 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 verify off命令可以隐藏替代变量的替换过程
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比较expr1 and 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