Oracle 学习(2)

2023-12-26 05:57:21

过滤和排序数据

where条件过滤?

日期格式

查询10号部门的员工信息:SQL> select *?from emp where deptno=10 ?

查询”KING”的信息:SQL> select * from emp where ename=?'KiNg?' ? 未选定行。

注意:字符串大小写敏感

??SQL> select * from emp where ENAME='KING'; 则正确

查询入职日期为1981年11月17日的员工:

SQL> select * from emp where hiredate=?'1981-11-17?' ? 可以吗?

参看:SQL> select sysdate from dual?? 查看系统当前的日期?(注意其格式)。

SQL> select * from emp where hiredate=?'17-11月-81?' ?

获取系统当前日期格式: SQL> select *?from v$nls_parameters (数据字典,类似于tab)

设置列宽度:SQL> col parameter for a30 ?

修改日期格式:SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd?' ?

再次查询:SQL> select * from emp where hiredate=?'1981-11-17?' ?

SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss' 显示带有时间的日期

SQL> select sysdate from dual 再次查看系统时间

改回系统默认格式:SQL> alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

  1. 字符和日期要包含在单引号中
  2. 字符大小写敏感,日期格式敏感
  3. 默认的日期格式是?DD-MON-RR

比较运算

普通比较运算符:

=? 等于(不是==) > 大于

>= 大于等于 < 小于

<= 小于等于 <> 不等于(也可以是!=)

查询薪水不等于1250的员工信息:SQL> select * from emp where sal <> 1250;

BETWEEN…AND: 介于两值之间。

查询工资在1000-2000之间的员工:

使用比较运算符:SQL> select * from emp where sal >=1000 and sal<2000 ?(注意第二个sal不能省)

用between?and:SQL> select *?from emp where sal between 1000 and 2000 ?

注意1.包含边界??2. 小值在前,大值在后。?(对于日期也是如此)

查询81年2月至82年2月入职的员工信息:

SQL> select * from emp where hiredate between '1-2月-81' and '30-1月-82'?

IN:在集合中。(not in 不在集合中)

查询部门号为10和20的员工信息:

(1)SQL> select * from emp where deptno=10 or deptno=20

(2)SQL> select *?from?emp where deptno in (10, 20) ?

SQL> select *?from?emp where deptno not in (10, 20) ? (30号部门的员工信息)

使用比较运算符该怎么写呢?

但是:如果是?….. not in (10, 20, NULL) 可不可以呢?

☆NULL空值如果结果中含有NULL不能使用not in 操作符, 但可以使用in操作符。

课后思考为什么???

like:模糊查询‘%’匹配任意多个字符。‘_’匹配一个字符。

查询名字以S开头的员工:SQL> select * from emp where ename like 'S%?' ?? (注意:S小写、大写不同)

查询名字是4个字的员工:SQL> select * from emp where ename like '_ _ _ _' ?

增加测试例子:向表中插入员工:

SQL> insert into emp(empno, ename, sal, deptno)?values(1001, ' TOM_ABC ', 3000, 10) ?

SQL> delete from emp where empno=8888;

查询名字中包含_的员工:

SQL> select * from emp where ename like '%?_ %?' 正确吗?

转义字符:

SQL> select * from emp where ename like '%\_% ' escape?'\' ?

转义单引号本身:

SQL> select 'hello '' world' from dual ? 使用两个单引号来完成转义

逻辑运算

AND ? 逻辑并

OR 逻辑或

NOT ? 逻辑非

如果…..where 表达式1 and?表达式2;

…..where 表达式2?and?表达式1; 这两句SQL语句功能一样吗?效率一样吗?

※SQL优化

SQL在解析where的时候是从右至左解析的。 所以: and时应该将易假的值放在右侧

or时应该将易真的值放在右侧

order by 排序

  1. 使用?ORDER BY 子句排序
    • ASC(ascend): 升序。默认采用升序方式。
    • DESC(descend): 降序
  2. ORDER BY 子句在SELECT语句的结尾。

SELECT ??last_name, job_id, department_id, hire_date

FROM ????employees

ORDER BY hire_date ;

查询员工信息,按月薪排序:SQL> ?select?*?from emp order by sal ? 结尾加desc (descending)降序

order by 之后可以跟那些内容呢?

? ?order by + 列名表达式别名序号。 注意:语法要求order?by 子句应放在select的结尾。

SQL> select ?ename, sal, sal*12 ?from emp order by sal * 12 desc ?

序号:默认:ename→1, sal→2,sal*12→3

SQL> select ?ename, sal, sal*12, from emp order by 2 desc ? 按月薪进行排序。

如果:SQL> select * from emp order by deptno, sal 会怎么样排序呢?

order by后有多列时,列名之间用逗号隔分,order?by会同时作用于多列。上例的运行结果会在同一部门内升序,部门间再升序。

SQL> select * from emp order by deptno, sal?desc 逆序呢?

desc 只作用于最近的一列两列都要降序排则需要两个desc。即:

SQL> select * from emp order by deptno desc, sal?desc ??

查询员工信息, 按奖金由高到低排序:

SQL> select * from emp order by comm desc

结果前面的值为NULL, 数据在后面,如果是一个100页的报表,这样显示肯定不正确。较为人性化的显示应该将空值放在最后, 即:

SQL> select * from emp order by comm desc nulls last (注意:是nulls?而不是null)

  1. 排序的规则
    • 可以按照select语句中的列名排序
    • 可以按照别名列名排序
    • 可以按照select语句中的列名的顺序值排序
    • 如果要按照多列进行排序,则规则是先按照第一列排序,如果相同,则按照第二列排序;以此类推

单行函数

单行函数:只对一行进行变换,产生一个结果。函数可以没有参数,但必须要有返回值。如:concat、nvl

  1. 操作数据对象
  2. 接受参数返回一个结果
  3. 只对一行进行变换
  4. 每行返回一个结果
  5. 可以转换数据类型
  6. 可以嵌套
  7. 参数可以是一列或一个值

字符函数

操作对象是字符串。

大致可分为两大类:一类是大小写控制函数,主要有lower、upper、initcap:

SQL> select lower('HeLlo, WORld')?转小写, upper('HellO, woRld')?转大写, initcap('hello, world')?首字母大写 ??from dual ???

另一类是字符控制函数:有CONCAT、SUBSTR、LENGTH/LENGTHB、INSTR、LPAD | RPAD、TRIM、REPLACE

substr(a, b):从a中,第b位开始取(计数从1开始),取到结尾

SQL> select substr('helloworld', 3) from dual ????

substr(a, b, c):从a中,第b位开始,向右取c位。 ??

SQL> select substr('hello world', 3, 5) from dual ??

length:字符数, lengthb:字节数:

SQL> select length('hello world') 字符数, lengthb('hello world')?字节数?from?dual;

select length('hello world') "字符数", lengthb('hello world') "字节数" from dual;

注意中英文差异。

instr:在母串中查找子串, 找到返回下标,计数从1开始。没有返回0

SQL> select instr('hello world', 'llo') from dual ??

lpad:左填充,参1:待填充的字符串,参2:填充后字符串的总长度(字节), 参3:填充什么 ?rpad:右填充。

SQL> select lpad('abcd', 10, '*') 左, rpad('abcd', 10, '#')?右?from?dual ?

SQL> select lpad('abcd', 15, '你')左填充, rpad('abcd', 16, '我') 右填充 from dual????

trim:去掉前后指定的字符

SQL> select trim('H' from 'Hello worldH')?from dual ?

注意语法期间含有from关键字

replace:替换

SQL> select replace('hello world', 'l', '*') from dual ?

删除字符串'hello world'中的字符'l':

SQL> select replace('hello world', 'l', '') from dual ??

数值函数

  1. ROUND: 四舍五入

ROUND(45.926, 2) 45.93

  1. TRUNC: ?截断

TRUNC(45.926, 2) 45.92

  1. MOD: 求余

MOD(1600, 300) 100

round(45.926, 2)??2表达的含义是保留两位小数,第二个参数如果是0可以省略不写。

SQL> select round(45.926, 2)?一, round(45.926, 1)?二, round(45.926, 0)?三, ?round(45.926, -1)?四, round(45.926, -2)?五?from dual ????

将上例中的所有round??替换为?trunc

正、负表示小数点之后,或小数点以前的位数。

SQL> select mod(1600, 600) from dual ??

时间函数

在Oracle中日期型的数据,既有日期部分,也有时间部分。

SQL> select sysdate from dual; (这里没有时间部分,因为系统默认的格式中不显示时间)

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual ?

SQL> select to_char(sysdate, 'day') from dual ?可以显示当前日期星期几

日期加减数字得到的结果仍为日期单位

显示?昨天、今天、明天:

SQL> select (sysdate-1) 昨天, (sysdate)?今天, (sysdate?+?1)?明天?from dual??

SQL> select to_char(sysdate-1, 'yyyy-mm-dd') 昨天, to_char(sysdate, 'yyyy-mm-dd') 今天, to_char(sysdate+1, 'yyyy-mm-dd') 明天 from dual????

既然一个日期型的数据加上或者减去一个数字得到的结果仍为日期,两个日期相减,得到的就是相差的天数。

计算员工的工龄:

SQL> select ename, hiredate, (sysdate - hiredate)?天, (sysdate - hiredate)/7 星期, (sysdate - hiredate)/30 月, (sysdate - hiredate)/365 年 from emp??

注意:日期和日期可以相减,但是不允许相加。 ?日期只能和数字相加!

SQL> select?sysdate+hiredate from emp ?

日期函数

上面求取员工工龄的结果不精确,如果想将其算准确,可以使用日期函数来做。 ?

months_between:两个日期值相差的月数(精确值) 跟between…and无关

SQL> select ename, hiredate, (sysdate-hiredate)/30 一, months_between(sysdate, hiredate)?二?from emp?

add_months:在某个日期值上,加上多少的月,正数向后计算,负数向前计算。

计算95个月以后是哪年、哪月、那天:

SQL> select add_months(sysdate, 95) 哪一天?from dual ?

last_day:日期所在月的最后一天。

SQL> select last_day(sysdate) from dual ?

next_day:指定日期的下一个日期

SQL> select next_day(sysdate, '星期一') from dual 从当前时间算起,下一个星期一

round、trunc 对日期型数据进行四舍五入和截断

SQL> select round(sysdate, 'month'), round(sysdate, 'year') from dual ?

SQL> select trunc (sysdate, 'month'), round(sysdate, 'year') from dual ?

转换函数

在不同的数据类型之间完成转换。将“123” 转换为 123。有隐式转换和显示转换之分。

隐式转换:

SQL> select * from emp where hiredate = '17-11月-81'? 由Oracle数据库来做

显示转换:

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual 通过转换函数来完成。

隐式转换,前提条件是:被转换的对象是可以转换的。 (ABC→625 可以吗?)

显示转换:借助to_char(数据,格式)、to_number、to_date函数来完成转换。?

?

如果隐式转换和显示转换都可以使用,应该首选哪个呢?

※SQL优化:如果隐式、显示都可以使用,应该首选显示,这样可以省去Oracle的解析过程。

练习:在屏幕上显示如下字符串:

?2015-05-11 16:17:06?今天是?星期一

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss?"今天是"?day') from dual ?

在固定的格式里加入自定义的格式,是可以的,必须要加“”

反向操作:已知字符串“2015-05-11 15:17:06 今天是 星期一”转化成日期。 to_date函数

SQL> select to_date('2015-05-11 15:17:06 今天是 星期一', 'yyyy-mm-dd hh24:mi:ss?"今天是"?day') from dual

?

查询员工的薪水:2位小数, 本地货币代码, 千位符

SQL> select to_char(sal, 'L9,999.99') from emp ?? 'L9,999.99'之间没有空格

将¥2,975.00转化成数字:

SQL> select to_number('¥2,975.00', 'L9,999.99') 转成数字?from dual ?

通用函数(了解)

这些函数适用于任何数据类型,同时也适用于空值

  1. NVL (expr1, expr2)
  2. NVL2 (expr1, expr2, expr3)
  3. NULLIF (expr1, expr2)
  4. COALESCE (expr1, expr2, ..., exprn)

nvl2: 是nvl函数的增强版。 nvl2(a, b, c)???当a =?null 返回?c, 否则返回b

使用nvl2求员工的年收入:

SQL> select empno, ename, sal, sal*12, sal * 12 + nvl2(comm, comm, 0) 年薪 from emp???

nullif: nullif(a, b)?当?a = b 时返回null, 不相等的时候返回a值。

SQL> select nullif('L9,999.99', 'L9,999.99') from dual ?

coalesce: coalesce(a, b, c, …, n) 从左向右找参数中第一个不为空的值。

SQL> select comm, sal, coalesce(comm, sal) 结果值 from emp???

条件表达式

例子:老板打算给员工涨工资, 要求:

总裁(PRESIDENT)涨1000,经理(MANAGER)涨800,其他人涨400.?请将涨前,涨后的薪水列出。

select ename, job, sal 涨前薪水, 涨后薪水?from emp 涨后的薪水是根据job来判断的

思路: if 是总裁('PRESIDENT')??then +1000

else if 是经理('MANAGER')??then +800

else +400

但是在SQL中无法实现if else 逻辑。当有这种需求的时候,可以使用case?或者?decode

case 是一个表达式,其语法为:

CASE expr??WHEN comparison_expr1?THEN return_expr1

????????????[WHEN?comparison_expr2 THEN?return_expr2

????????????WHEN?comparison_exprn THEN?return_exprn

????????????ELSE else_expr]

END

SQL> select ename, job, sal 涨前薪水, case?job when?'PRESIDENT' then?sal+1000

????when?'MANAGER' then?sal+800

????else?sal + 400

end?涨后薪水?

??from emp ?

注意语法:when then 与下一个when then以及end之间没有“,”分割符。

decode:是一个函数,其语法为:

DECODE(col|expression, search1, result1

?????? ???[, search2, result2,...,]

?????? ???[, default])

除第一个和最后一个参数之外,中间的参数都是成对呈现的?(参1, 条件,?值,?条件,?值, …, 条件, 值, 尾参)

SQL> select ename, job, sal 涨前薪水, decode(job, 'PRESIDENT', sal + 1000, ?

????'MANAGER', sal +?800,??

????sal + 400)??as 涨后薪水

??from emp ?

?

文章来源:https://blog.csdn.net/cat_fish_rain/article/details/135196346
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。