oracle——单表查询

(基础)查询

1.1. 基本查询语法

基本查询是指最基本的select语句。

【语法】

 

【知识点】如何使用工具进行查询

在plsql developer中打开查询窗口（执行sql语句）：

执行语句的操作：

选中要执行的语句，点击执行按钮或者按快捷键F8，下方会显示查询的结果。

显示下一页记录和显示剩余的所有页的记录：

注：如果想学习工具的详情使用，可参阅文档《plsql developer中文手册.pdf》

 

 

1.2. 选择列

【语法】

选择全部列：

选择特定列：

【知识点】

两种语法效率是不同的，哪种效率高？

结论和原因：如果select * 会全字段扫描，效率低，因此，尽量用指定的字段。

 

 

1.3. 别名

别名涉及到列的别名和表的别名。需要注意引号和用法。

 

【示例】

--别名
--列的别名
SELECT ename AS 姓名, job AS 工作 FROM emp;  --省略了双引号

SELECT ename AS "姓   名", job AS "工作" FROM emp;--最标准的写法,在别名有空格的时候不能省略双引号

SELECT ename   "姓   名", job   "工作" FROM emp; --省略了as

SELECT ename 姓名, job  工作 FROM emp;--省略as和双引号

--表的别名
SELECT * FROM emp t; --给表起别名不能加as;

SELECT t.ename,t.job FROM emp t ;-- 表的别名引用字段

SELECT empQWERTYUIO.ename,empQWERTYUIO.job FROM empQWERTYUIO ;--使用表名去引用字段相对麻烦

SELECT emp.ename,emp.job FROM emp t ;--一旦给表起了别名, 那么就只能使用 别名去引用字段,原本的表名 不可用

【知识点】

1.引号的问题。别名最好使用双引号，也可以省略，而且还可以省略as。

2.表的别名一旦指定，列的引用中必须使用表的别名。

 

1.4. 书写SQL的注意事项

 

注意：sql语句对大小写不敏感，但数据内容区分大小写；表名和表字段都不区分大小写

【示例】

使用工具来格式化语句：

选中要格式化的语句，点击工具栏上的“美化”按钮，工具会自动将语句格式化：

格式化美化功能非常适合比较长的、复杂的语句的格式化。

 

1.5. 字符串连接符||  

 

【示例】

--需求1：查询出员工的名字，要求显示的员工名字前面加上“姓名：”的字符串，显示结果参考：姓名：scott
SELECT  ‘姓名：‘|| ename 姓名 FROM emp;
--需求2：将和员工的编号和员工的姓名都放在一个结果字段中显示。合成列
SELECT  empno||‘ ‘||ename FROM emp;

提示：单引号代表的是字符串。

【知识点】

引号的问题。Oracle中如何选择单引号和双引号呢？基本上，只要是别名或不需要Oracle解析（运算）的字符串，用双引号，剩下的都用单引号（比如字符串）

 

 

1.6. 伪表-dual

mysql查询当前系统时间：SELECT SYSDATE();

但在Oracle中会报错：

提示：sysdate代表系统时间函数。

报错原因：

Oracle和mysql的一个区别：

Oralce的查询语句必须是完整的，即必须满足语法select  from

 

【示例】

需求：查询显示当前的日期：
SELECT SYSDATE FROM dual;--sysdate代表当前日期的一个系统函数，dual是伪表，主要用来占位的，补充sql的。
SELECT ‘a‘||‘b‘ FROM dual;
SELECT 1+2 FROM dual;

DUAL 是一个‘伪表’（也称之为万能表），可以用来测试函数和表达式。也有人称之为万能表。使用的时候可以用来占个语法的位置，来补充完整的sql。

伪表也是一张表，只是做了一些特殊处理。我们来看看：

注意：大家不要手动来维护这张表，这个表是由Oracle自动维护的。

 

1.7. 空值运算问题

【示例】

--需求：查询所有员工的月薪（月薪=基本工资+奖金）
SELECT ename, sal+comm 月薪  FROM emp;--原因：与null运算的结果都是null

问题：

为何有的人月薪没值？但这些人明明有基本薪资（sal）。

原因：和null进行运算的都是null。

如何解决呢？我们会在单行函数中这一章节中进行解决。

提示：

后续内容中会有更多与null相关的例子。

 

 

1.8. SQL语句和SQL*Plus命令

目标：

了解什么是命令，什么是语句。

两者对比如下：

关键字可以缩写，比如显示表结构的命令：

 

【关于工具窗口使用选择】

  工具的命令行窗口下，既能执行命令，也能执行sql；但在sql窗口下只能运行sql，如果在sql窗口运行命令，会出现错误信息

【演示】

可视化工具sql窗口只能执行SQL语句，但不能执行命令

在可视化工具的命令窗口分别通过 完整命令、简写命令、SQL语句 显示emp表的结构：都成功

工具中的命令窗口的调用方法：

友情提示：

工具的命令窗口和sqlplus自带的命令窗口在有时候还是有少许区别的。后面会提到。

 

 

1.9. 导出报表-扩展-了解

报表是向上级报告情况的表格。简单的说：报表就是用表格、图表等格式来动态显示数据，可以用公式表示为：“报表 = 多样的格式 + 动态的数据”。

计算机提供的报表可以由数据库直接生成，也可以由专业的报表软件生成。

l 数据库软件：它们可以拥有动态变化的数据，但是这类软件一般只会提供，最简单的表格形式来显示数据。它们没有实现报表软件的"格式多样化"的特性。

l 报表软件：它们需要有专门的报表结构来动态的加载数据，同时也能够实现报表格式的多样化。（eclipse官方提供birt）

 

使用sqlplus导出报表，不太方便。

在没有专业的报表系统或报表工具的情况下，推荐直接使用SQLplus Developer工具进行简单报表的导出，导出步骤参考如下：

1. 执行期望的sql语句。

SELECT t.ename 员工编号, t.ename 员工姓名, t.hiredate 入职日期 FROM emp t;

2. 选中需要导出的报表数据（如果不选中就是导出所有的），在左侧的工具栏上点击右键，选择copy to excel...copy as xls

自动生成到execl表格：

提醒：

复杂的查询以及查询技巧主要是报表查询的时候要用的到！

 

2. 过滤子句where

2.1. 过滤语法

过滤就是使用where子句，将不满足条件的行过滤掉。

注意：

l Where子句紧跟from子句。

l where的过滤条件是对于每一行数据的。

 

2.2. 字符和日期

这里强调两个事情：

字符大小写的问题和默认日期格式的问题。

【示例】

--需求1：查询关于KING这个人的记录。
SELECT * FROM emp WHERE ename=‘king‘;--错误
SELECT * FROM emp WHERE ename=‘KING‘;--正确，具体数据库的值是区分大小写。

--需求2：查询入职日期是1987/4/19的员工的信息
SELECT * FROM emp WHERE hiredate =‘1987/4/19‘;--数据库默认是日期的格式不对。导致无法将字符串隐式转换为日期
SELECT * FROM emp WHERE hiredate =‘17-12月-80‘;--数据库默认的日期格式，字符串可以隐式转换为日期

--数据内容显示的1987/4/19格式是工具给你转的

【疑问】为什么工具显示的不是默认值（和sqlplus显示的不一样）？

数据库真实存储日期的格式：

原因是工具自己转换了！工具显示的日期格式配置如下：

【工具使用提示】

1．注意工具的错误提示方式：

2．异常会导致语句执行过程被卡住。见工具栏上的闪电图标。此时可以选择终止运行或排除异常后语句继续执行。

1.3. Escape-转义字符

准备测试数据。

添加一条ename的值为xiao_ming的测试数据，可以使用insert语句：

INSERT INTO emp(empno,ename) VALUES(1001,‘xiao_ming‘);
commit;

注意：oracle默认手动提交，增删改之后需要手动提交事务，而查询则不需要

【回顾】通配符：

【示例】

--需求1：查询名称是带有”x”字符的员工的记录信息。
SELECT * FROM emp WHERE ename LIKE ‘%x%‘;

--需求2：查询员工名称中含有下划线（“_”）的员工.
SELECT * FROM emp WHERE ename LIKE ‘%_%‘; --为什么全查出来：sql的通配符%(任意多个字符) _(任意一个)

SELECT * FROM emp WHERE ename LIKE ‘%\_%‘ ESCAPE ‘\‘;
--用ESCAPE来声明一个转义字符，语句中，该转义字符之后的字符，都作为普通字符来处理。

SELECT * FROM emp WHERE ename LIKE ‘%|_%‘ ESCAPE ‘|‘;
--除了“/”，别的符号也可以，但建议使用“/”

--需求3：查询姓名是4个字符的员工的信息。
SELECT * FROM emp WHERE ename LIKE ‘____‘;
/*
作用：假如你允许用户注册的时候带下划线，或者表单有个字段是备注，那么用户，在写备注的时候，可能会写下划线。
注册zhong_shi,此时，你想知道数据库中有多少人的用户名是带下划线的。
*/
SELECT COUNT(*) FROM emp WHERE ename LIKE ‘%/_%‘ ESCAPE ‘/‘;

【工具的使用提示】：

对于某个关键字不确定单词的编写的时候，可以使用工具的自动提示功能。

当输入前几个字符，则工具会自动提示相关关键字的列表。如果不小心关闭了提示或者未出现提示，则可以通过在单词上按F6。

2.4. 条件运算符

常见的条件运算符如下：

运算符的优先级：

括号最无敌 

 

2.5. in和not in过滤时的空值问题

 

【示例】要求使用in和not in

--需求1：查询10号部门和20号部门的员工；
SELECT * FROM emp WHERE deptno IN(10,20);

--需求2：查询10号和20号以及没有部门的员工部门的员工；
SELECT * FROM emp WHERE deptno IN(10,20,NULL);  --失败
--分析:首先要明白in的原理是什么?
--deptno = 10 OR deptno = 20 OR deptno IS NULL  
--或的关系 只要有一个结果的true 返回的就是true

--解决方法:
SELECT * FROM emp WHERE deptno  IN(10,20) OR deptno IS null;

--需求3：查询不是10号和20号以及没有部门的员工部门的员工；
SELECT * FROM emp WHERE deptno  NOT IN(10,20,NULL);--失败
-- deptno!=10 AND deptno!= 20 AND deptno IS NOT NULL 
-- 与的关系 ,只要有一个不满足 返回就是false
SELECT * FROM emp WHERE deptno  NOT IN(10,20) AND deptno IS NOT null;

2.6. 条件运算的优先级

思考：下面这条语句的两个条件的执行顺序是什么？（注：condition1和condition2是两个条件表达式）

答案：先执行2，再执行1。

原因是：where条件的解析顺序：从右到左

【知识点】

SQL优化：（where条件特别多的情况下，就有效果了）

对于and，应该尽量把假的放到右边。

对于or，应该尽量把真的放到右边。

 

oracle——单表查询

标签：单表查询   特定   大小   单行函数   命令行   ges   mbr   cli   单表