Oracle19C入门到熟练007-简单查询

学习要求

有一定关系型数据的操作功底，会SQL语句

教学目标

学习SQL查询操作

SELECT语法

完整语法

select [all | distinct
{* | expression | column1_name [,column2_name][,...]}
from {table1_name | (subquery)}[alias]
[,{table2_name | (subquery)}[alias],...]
[where condition]
[connect by condition [start with condition]]
[group by expression [,...]]
[having condition[,...]]
//分别标识联合，交和差操作
[{union | intersect | minus}]
[oredr by expression [asc|desc][,...]]
//在查询表时将其锁住，不允许其他用户对该表进行DML操作，直到解锁为止
[for update [of [schema.] table_name | view] column][nowait];

简化语法

select 列1,列2,列3.... from 表名  别名

注意点

• SQL 语言大小写不敏感。

• SQL 可以写在一行或者多行

• 使用缩进提高语句的可读性。

列投影

需求：查询所有员工信息

select * from emp;
select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;

需求：查询每个员工的编号、姓名、职位

select empno, ename, job from emp;

需求：查询所有部门信息

select * from dept;

消除重复

在表中，可能会包含重复值。查询时，如果希望列出不同的值，可以使用DISTINCT。DISTINCT 关键字可以用于一列，也可以用于多列。

需求：查询所有有员工的部门编号

select deptno from emp;  -- 没有排重前
select distinct deptno from emp;  -- 排除重复

需求：查询有员工的部门和职位

select deptno, job from emp;  -- 没有排重前
select distinct deptno,job from emp;  -- 排除重复

这里注意： 如果是多个列，排重比较就是多个列重复比较啦。

算术运算符

操作符	描述
+	加
-	减
*	乘
/	除

Oracle中，对 NUMBER 型数据可以使用算数操作符创建表达式（+ - * /）；

Oracle中，对 DATE 型数据可以使用算数操作符创建表达式（+ -）。

注意点

• 乘除的优先级高于加减。

• 同一优先级运算符从左向右执行。

• 括号内的运算先执行

需求：查询所有员工工资

select ename, sal from emp;

需求：临时给所有员工涨薪300

select ename, sal + 300 from emp;

需求：查询所有员工的年薪

select ename, sal * 12 from emp;

需求：查询所有员工工资涨了300块之后的年薪

select ename, (sal + 300) * 12 from emp;

需求：查询当前时间

select sysdate from dual;   -- dual 续表

需求：查询明天时间

select sysdate + 1 from dual;

需求：查询截止目前，所有员工工龄

select (sysdate - hiredate)/365 from emp;

注意：2个日期相减，得到是天数

更准确的写法：

select trunc(months_between(sysdate,hiredate)/12) from emp;

months_between ： 日期函数，计算2个时间间隔多少个月

trunc：number函数，取整数，将数字中小数部分截去，返回整数

别名

select语法中有2种别名

列别名

紧跟在列后面，使用as 语法，也可以省略，如果列别名比较特殊，使用“” 隔开

需求：查询所有员工的年薪(别名：annualSalary)

方式1：

select ename, sal * 12 as annualSalary from emp;

方式2：

select ename, sal * 12 annualSalary from emp;

方式3：比较特殊的列别名才使用， 比如：特殊字符， 空格， 区分大小写

select ename, sal * 12 "annual Salary" from emp;

表别名

紧跟在表后面，或者子查询后面

需求：查询每个员工的编号、姓名、职位(使用表别名)

select empno, ename, sal from emp e;

select e.empno, e.ename,e.sal from emp e;   -- 跟上面操作等价

需求：查询所有员工信息

select * from emp e;
select e.* from emp e;

列拼接

因为某种需求，需要把列与列，列与字符连接在一起，此时可以使用Oracle提供的 || 连接符。

需求：查询出这种效果：xxx员工工资是：xx元

select ename ||'员工工资是：' || sal || '元'  from emp;

注意

• oracle中列，字符串拼接是使用 || 符号，而不是 +

• oracle中的字符串/日期类型必须使用单引号， 双引号直接报错

select ename ||"员工工资是：" || sal || "元"  from emp;

排序

将通过select查询数据，按照指定列进行排序。

语法

select 列1,列2,列3.... from 表名  别名  order by 排序列  排序规则

排序规则有2种：

ASC ：正序排

DESC ：倒序排

需求：查询所有员工信息，按照工资升序排

select * from emp  order by sal asc;

需求：查询所有员工信息，按照工资升降序

select * from emp  order by sal desc;

需求：查询所有员工信息，按照部门编码降序排，如果编号一样，按照员工编号正序排

select * from emp order by deptno desc, empno asc;

需求：查询所有员工信息，按照年薪降序排序

select ename, sal * 12 annualSalary from emp order by  sal * 12 desc;

select ename, sal * 12 annualSalary from emp order by  annualSalary desc;

注意：先执行SELECT语句，后再执行ORDER BY 子句，所以ORDER BY 语句可以使用列别名作为排序列

需求：查询所有员工信息，按照部门和年薪降序排序

select ename, deptno, sal * 12 annualSalary  from emp order by deptno desc, annualSalary desc;

注意：排序列不能使用 '' 引号括起来

分页

将需要显示的数据分成相同的等份，然后一页，一页显示，此为分页。

MySQL中提供limit 语法：limit 偏移量, 每页显示条数

--- 前提： 每页显示3条
-- 第一页
select * from emp limit 0, 3;
-- 第二页
select * from emp limit 3, 3;
-- 第三页
select * from emp limit 6, 3;
-- 第n页
select * from emp limit (n-1)*3, 3;

-- 其中偏移量：显示当前页的数据需要跳过几条数据   每页显示条数：当前页要显示几条数据

而在Oracle中并没有limit 的语法，而是使用

offset 偏移量rows fetch next 每页显示条数 rows only 语法

--- 前提： 每页显示3条
-- 第一页
select * from emp  offset 0 rows fetch next 3 rows only;
-- 第二页
select * from emp  offset 3 rows fetch next 3 rows only;
-- 第三页
select * from emp  offset 6 rows fetch next 3 rows only;
-- 第n页
select * from emp  offset (n-1)*3 rows fetch next 3 rows only;

注意：offset....fetch next..only 语法是Oracle12c之后提供的语法。Oracle12c之前使用嵌套分页语法

--- 前提： 每页显示3条
-- 第一页
select  t.* from (select rownum r, e.* from emp e where rownum <=3) t where t.r >0;
-- 第二页
select  t.* from (select rownum r, e.* from emp e where rownum <=6) t where t.r >3;
-- 第三页
select  t.* from (select rownum r, e.* from emp e where rownum <=9) t where t.r >6;
-- 第n页
select  t.* from (select rownum r, e.* from emp e where rownum <=n*3) t where t.r >(n-1)*3;

注意：rownum是虚拟列，针对查询出来结果进行编号(或者说正序排)，只能查小于，不能查大于