oracle报错写表与查表,oracle——单表查询

1. 基本(基础)查询

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 姓名 FROMemp;--需求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‘ FROMdual;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，应该尽量把真的放到右边。

原文：http://www.cnblogs.com/dongfangshenhua/p/6901833.html