Oracle-第一章-单表查询

基本知识

SQL语言是一种非过程语言； SQL语句接受集合作为输入，返回集合作为输出； SOL的集合特性允许将一条SQL语句的结果作为另一条SQL语句的输入； SQL不要求用户指定对数据的存放方法，用户无需了解存取路径及物理地址，存取的路径由DBMS优化来完成； 查找可以是集合，插入、删除更新也可以是元组； 同一语法结构有两种使用方法，用户在终端命令提示符下输入SQL命令时数据库服务器立即执行的交互式应答使用，和把SQL命令嵌入到应用程序中执行的预编译SQL。

通过SQL语言可以完成以下操作 

1.建立数据库表格，并设置表格可以使用的空间；

2.改变数据库系统的环境设置；

3.针对某个数据库或表格授予用户存取权限；

4.对数据库表格建立索引值；

5.修改数据库表格结构（新建、删除、修改表格字段）； 6对数据库进行数据的新建、修改、删除、查询。

SQL语言分类

1数据查询语言-DQL-select 语句

2数据操作语言-DML-insert（插入数据） update（更新数据） delete（删除数据） 语句

3事务控制语言-TCL-commit（提交对数据库的更改） rollback（取消对数据库的更改） savepoint（设置保存点）

4数据定义语言-DDL-create table（创建表） alter table（修改表结构） drop table（删除表） 5数据控制语言-DCL-grant（授予用户或者角色权限） revoke（收回用户或者角色权限）

SQL语言编写规则

1关键字不区分大小写

2字符值区分大小写

 一、检索数据

本文代码基于PL/SQL Developer开发工具与Oracle进行交互

基本语法格式

select{[distinct|all]columns|*}--用于选择数据表、视图中的列【4】

[into table_name]--用于将原表中的结构和数据插入新表中

from{tables|views|other select}--用于指定数据来源，包括表、视图和其他select语句【1】 [where conditions]--用于对检索的数据进行筛选【2】

[group by columns]--用于对检索结果进行分组显示【3】

[having conditions]--用于从使用group by子句分组后的查询结果中筛选数据行【5】

[order by columns]--用来对结果进行排序【6】

以上语句的执行顺序：【4】【1】【2】【3】【5】【6】

1.简单查询

1.1检索所有列

select * from emp;--检索一个表的所有列
select * from emp,dept;--检索多个表的所有列，检索结果呈现在同一个窗口，且和表名的输入顺序有关

注：select子句中可跟具体字段（列名）、常量、*、函数
    常量-可用于使查询结果明晰
    select '姓名：',name from student;--查询结果如 姓名：张张
    select '张简简' from student;--suanzuo

1.2检索指定的列

（1）基础

select column1，column2 from table_name;--从单个表中查询多个制定的列
select column1，column2 from table_name1,table_name2;--从多个表中查询多个特定的列
select column2，column1 from table_name1,table_name2;--语句中不要求列名和表名顺序对应

（2）伪列

（2.1）ROWID

在oracle数据库中有的行标识符ROWID
是Oracle数据库中使用隐藏列，实际上并不是定义在表中
所以被称为伪列，长度为18个字符
包含该行数据在Oracle数据库中的物理地址，使用select语句可以查询到
select rowid from table_name;
--查询伪列
--与索引相关

（2.2）ROWNUM

非真实存在于表中
与查询结果相关
为每条记录赋序号
查询结果是一行一行出现的，查询一行赋一个号

select rownum,s.name
from student s
where rownum=1;
--可运行
select rownum,s.name
from student s
where rownum=2;
--不可运行
--因为行无法出现，无法跳过1赋值2
select rownum,s.name
from student s
where rownum<3;
--可运行,显示前两条记录
select rownum,s.name
from student s
where rownum>3;
--不可运行
--行无法出现

解决办法（用子查询）：
select *
from （select rownum r,s.name,s.age
      from student s）
where r>3;

ROWNUM通常用于分页：
例
--分页
--每一页有10行，分别查询页和第二页内容
--注意用公式的形式（如10*(1-1)+1 and 10*1）
select * from 
(select rownum r,s.name,s.age from student s)
where r between 10*(1-1)+1 and 10*1;
select * from 
(select rownum r,s.name,s.age from student s)
where r between 10*(2-1)+1 and 10*2;
--
方法一
select *
  from (select m.*, rownum r
          from (select * from emp e order by e.sal desc) m
         where rownum <= 10) mm
 where mm.r > 5
方法二
select * from
 (select row_number() over(order by sal desc) rownumber,e.* from emp e)
 where rownumber>=5 and rownumber<=10

1.3查询日期列

日期列指数据类型为DATA的列，默认显示格式为DD-MON-RR
------
（1）以简体中文显示日期结果
alter session set nls_date_language='SIMPLIFIED CHINESE';--报错‘ora-12705:无法访问NLS数据文件,或者指定的环境’
--原因是多次重复安装数据库--解决办法暂无，建议一开始一次性安装好数据库--原来是ＳＩＭＰＬＩＦＩＥＤ拼写错误（可恶）
（select * from V$NLS_PARAMETERS--可查看系统参数，如NLS_LANGUAGE  |  SIMPLIFIED CHINESE）
select ename,hiredate from emp;--即使步错误解决，仍无法显示‘17-12月-80’格式的结果
------
（2）以美国英语显示日期结果
alter session set nls_date_langeuage='AMERICAN';--但是这样的修改不会有ora-12705，可以修改
select ename,hiredate from emp;--仍然无法得到'17-DEC-80'格式的查询结果
-----
（3）以特定格式显示日期结果
alter session set nls_date_format='YYYY''年''MM''月''DD''日''';--显示‘a-01821日期格式无法识别’报错
select ename,hiredate from emp;--即使成功修改后，在PL/SQL中也无法显示自定义日期格式

alter session set nls_date_format='YY-DD-MM';--可运行
select ename,hiredate from emp;--无法显示自定义日期格式

alter session set nls_date_format='YY';--也可运行
select ename,hiredate from emp;--无法显示自定义日期格式
----
（4）TO_CHAR函数自定义日期（看函数部分）

1.4带有表达式的select语句

select sal*(1+.1),sal from emp;--查询结果是两列sal*(1+0.1),sal
在select语句中可以使用算术运算符+-*/和括号，不仅可以执行单独的数学运算，还可以执行单独的日期运算以及与列名关联的运算

1.5别名

（1）列别名
select empno as 员工编号，ename as '员工名称'，job 职务 from emp;--报错-原因'员工名称'外有引号
select empno as 员工编号,ename yuangongmingcheng from emp;--可以不需要as,别名不能被引号选中，别名可以是中文、字母
（2）表别名-可快捷选取列名（养成习惯）-另建student数据表
①select   from student s;
②select s.______[出现各列名] from student s;--选中需要的列名
③select s.birthday,tel from student s;--不必须用s.tel

1.6显示不重复记录

select distinct job from emp;--减少冗余
select distinct XO,gender from student s;--另建student数据表，对组合列去重，以每行每单位内容全重复的才算重复

1.7处理null值

null表示未知量，不是空格也不是，当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，则结果为null
select ename,sal,comm,sal+comm from emp;
--sal+comm会出现值为‘值+null=null’的错误
select ename,sal,comm,sal+nvl(comm,) from emp;
--nvl(comm,0)如果comm存在数值则返回数值，否则为0
####
含有null的条件查询
select * from student s where s.major like'%会计%' and (s.home not like '%济宁%' or s.home is null);
--查询学会计的家乡不是济宁的同学
####
nvl2(参数1，参数2，参数3)
--参数1不为空时，返回参数2，否则返回参数3

### 查询xx为空或者错误的XX 
select s.name,d.dormno
from student s
left join dorm d
on s.dormid=d.id
where d.id is null;
-- 用 is null 非=''或者=null

2.条件查询

2.1比较查询

语法select col1,col2... from tbname where 条件要求；

大于    >
大于等于    >=
小于    <
小于等于    <=
不等于    <>  或者 ！=

特殊
A {operator} any B 表示元素A与B中任何元素进行operator运算符的比较，只要有一个比较值为TRUE则返回数据行
例：select empno,ename,sal from emp where sal = any(840,1300,1155);
A {operator} all B 表示元素A与B中所有元素进行operator运算符的比较，全部比较值都为TRUE才返回数据行
例：select empno,ename,sal from emp where sal <> all(840,1300,1155);

2.2模糊查询（like关键字）

select * from student s where name like '张张%';--可得张张、张张张等人的信息，%可匹配0个及任意长度字符串

select * from student s where name like '张_';--可得张张、张海等名字为两个长度且姓张的人名，‘_’仅仅且必须匹配一个字符

select * from student s where s.bulidid not like '%楼%';--like关键字无法查询到空白记录null值
select * from student s where not s.bulidid like '%楼%';--‘非’操作，查询结果和上面一样

select * from student s where not s.bulidid like '小红楼';--则无结果，应当用‘=’号

转义字符（不是oracle自带的，需要自己定义）
select * from dept_temp where dname like 'IT\_%'escape'\';--自定义‘\’为转义符，例子中‘_’已经不是通配符了

2.3 in关键字查询

select * from student s where s.xo [not] in ('A','AB');
--查询血型[不为A和AB]为A或AB的同学的所有信息
in 后面要跟一个集合

2.4多条件查询（and,or,not--and优先级高于or）

考虑 与或非、交并补
select * 
from student s 
where s.name not like '孙%' and s.name not like '王%';
--查询不姓孙也不姓王的学生
select * 
from student s 
where not (s.name  like '孙%' or s.name  like '王%');--查询不姓孙也不姓王的学生

2.5 between函数

[not]between and 
select * from student s where s.age>=22 and s.age<=24;
等同
select * from student s where s.age between 22 and 24;

2.6对查询结果排序（order by总是放在select语句的后面）

select s.groupno,s.name,s.age from student s order by s.groupno,s.age desc;
--检索组号、姓名、年级并按组号升序排序（默认ASC）再按年级降序（DESC）

select ename,sal from emp order by (3+sal)*8;
--检索依照的条件可以是特定列或表达式

注意了解以下两种用法但不建议使用（不直观）：
select empno,ename,sal*12 AnnualSalary from emp order by 3 desc;
--用列名‘3’指代行名，‘Annual Salary’取别名时两个单词间不能有空格
select ename from emp order by sal;
--用非选择列表列进行排序

ASCII码：计算机中每一个字符都有一个数字与之对应
         10 \n 换行符  65 A 97 a
         排序时先按字符中个字符的ASCII码比较，若相同再比较第二个字符的ASCII码 

3.分组查询

3.1单列分组

（1）基本用法

select deptno,job from emp;
select deptno,job from emp group by deptno,job;
select ename,deptno,job from emp group by deptno,job;--报错
--对于含group by子句的select语句中仅允许出现统计函数、常量、group by子句中已经出现过的字段列
select ename,deptno,job from emp group by ename,deptno,job;

select avg(sal) from emp group by job;--group by 中的列可以不出现在select列表中

（2）常用的统计函数

聚合函数：
AVG|返回一个数字列或者计算列的平均值
COUNT|返回查询结果中的记录数（注意不统计null值）--count(1)可以用来计数
MAX|返回一个数字列或是计算列的大值
MIN|返回一个数字列或是计算列的小值
SUM|返回一个数字列或者计算列的总合
select sum(sal),sum(distinct sal),sum(all sal)
from emp;
--都可以加 distinct 或 all（默认为all）

select job,avg(sal),sum(sal),max(sal),min(sal),count(job) 
from emp 
group by job;
--分组的意义在于对分组后的内容进行处理
--查询各类job的平均工资、总工资、高工资、低工资、人数

####
补充：
①wm_concat
select wm_concat(s.name)
from student s;
--用逗号将所有的字符串连接在一起
②variance统计方差
③stddev标准方差

（3）对于 含group by子句的select语句中仅允许出现统计函数、常量、group by子句中已经出现过的字段列 的理解文章来源地址https://www.toymoban.com/news/detail-475670.html

select job,avg(sal) from emp;--error:不是单组分组函数
select job,avg(sal) from emp group by job;
条语句select子句中的列名称job告诉Oracle系统显示每行数据的职位，在emp表中有很多条数据
avg(sal)告诉Oracle显示emp表中所有数据行的平均工资，只能产生一个平均工资
两者矛盾，因此报错

3.2多列分组

select deptno,job,avg(sal),max(sal) from emp group by deptno,job;
--与单列分组的区别在于多列分组是把多个列中相同的组合列作为分组的标准
--查询同一部门中的同类工作的平均工资和高工资

3.3分组后排序

select deptno,job,avg(sal),max(sal) 
from emp
group by deptno,job  order by deptno desc;

3.4限制分组结果

用having子句对分组结果做进一步筛选
如果不使用group by子句，having子句和where子句一样都具有定义搜索条件的作用
having子句可以包括聚合函数（COUNT SUM AVG） where子句不能包含聚合函数

select子句中的处理顺序：
首先由from子句找到数据表，where子句则接收from子句输出的结果，having子句接收来自group by、where、from子句的输出

select deptno as 部门编号，avg(sal) from emp group by deptno having avg(sal)>2000;
--先通过分组计算得到每个部门的平均工资，然后通过having过滤得到平均工资大于2000的记录信息
--聚合函数的条件必须放在having之后

select deptno as 部门编号 from emp  where sal>2000;--此处的where并不能用having替代

3.5 ROLLUP 和 CUBE 操作符 （仅作了解）

（1）使用 ROLLUP 操作符执行数据统计
（直接用group by只能生成的简单的数据统计结果，用ROLLUP生成数据统计、横向小计、总计统计）
select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 
from emp
group by rollup(deptno,job); 
--可得到各部门全部岗位的平均工资

（2）使用 CUBE 操作符执行数据统计
select deptno 部门编号,job 岗位,avg(sal) 平均工资 
from emp 
group by cube(deptno,job);

思考一：
select deptno 部门编号,job 岗位,avg(sal) 平均工资 
from emp 
group by cube(job,deptno);

思考二：
select deptno 部门编号,job 岗位,mgr 上级,avg(sal) 平均工资 
from emp 
group by cube(job,deptno,mgr);
--出现多个复合组合的结果，复杂度提升，个人认为不建议使用


（3）使用 GROUPING 函数确定统计结果是否用到了特定列
select deptno 部门编号,job 岗位,mgr 上级,
avg(sal) 平均工资,grouping(deptno),grouping(job),grouping(mgr)
from emp
group by cube(job,deptno,mgr);
--此查询语句可能存在无意义的情况，本处只用做grouping示例

（4）在 RULLUP 操作符中使用复合列
复合列被看作一个逻辑单元的列组合，可以略过RULLUP的某些统计结果。
例如：group by rollup(a,b,c)的统计结果等同于group by(a,b,c) 、group by(a,b) 、group by a 、group by()的并集。
将（b,c）作为复合列，group by(a,(b,c))的结果等同于group by(a,b,c)、group by a、group by()的并集。

select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 
from emp 
group by rollup(deptno,job); 


select deptno as 部门编号，job as 岗位，avg(sal) as 平均工资 
from emp 
group by rollup((deptno,job)); 


（5）在 CUBE 操作符中使用复合列
例如：group by cube(a,b,c)的统计结果等同于group by(a,b,c) 、group by(a,b) 、group by(a,c)、group by(b,c)、group by a、group by b、group by c 、group by()的并集。
将（a,b）作为复合列，group by((a,b),c)的结果等同于group by(a,b,c)、group by(a,b)、group by c、group by()的并集。

3.6 GROUPING SETS 操作符（生成多种分组结果）

select deptno,job,avg(sal)
from emp
group by grouping sets(deptno,job);
--得到依部门和工资两个平均工资

到了这里，关于Oracle-第一章-单表查询的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！