OCA/OCP Oracle 数据库12c考试指南读书笔记：第7章: Retrieving, Restricting, and Sorting Data Using SQL

2024-02-25 01:50
文章标签 sql oracle 数据库 读书笔记 data using database ocp sorting 12c retrieving oca 考试指南 restricting

本文主要是介绍OCA/OCP Oracle 数据库12c考试指南读书笔记：第7章: Retrieving, Restricting, and Sorting Data Using SQL，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

推荐

在篇首强烈推荐Oracle Blogs中的Technology / Database, SQL and PL/SQL专栏。

SQL SELECT的能力

SQL是声明式（declarative ）编程语言，因此简单，而且灵活。另一类是命令式（imperative ），如C语言。
SQL除了简单外，另一个好处是行业标准，因此比较成熟。

DESCRIBE table命令

To get the answers you seek, you must ask the correct questions.

希望问题得到解答，就必须正确的问问题。查询表之前就必须了解表结构。
表的结构存在数据字典中，称为元数据。实际存在行中的称为数据。
元数据可以通过describe命令获得。

DESC[RIBE] [schema.]tablename

例如：

SQL> desc sys.dual;Name                                      Null?    Type----------------------------------------- -------- ----------------------------DUMMY                                              VARCHAR2(1)

必须有值的列定义为NOT NULL， NULL表示值空缺。

SELECT语句的能力

专业术语tuple带属性，翻译成Oracle的语言就是表带行，行带列。
SELECT 语句的能力包括

  1. projection - 可以选择列
  2. selection - 可以选择行
  3. joining - 表之间的交互

数据规范化

在数据访问和数据存储之间取得平衡。

数据建模

UML，SQL Developer Data Modeler
逻辑模型：实体与关系
实体以圆角矩形表示，实体有属性，即列
实体的实例称为tuple或行
实体的集合称为表
实体的行称为cardinality（基数） of the tuples
唯一的属性称为唯一键，唯一键通常作为主键
实体间的关系称为外键
逻辑模型在关系型数据库中被实现为物理模型

冗余的数据不仅浪费空间，而且维护困难。反之则维护方便。
第一范式(1NF)：消除重复数据
第二范式(2NF)：消除不依赖于主键的属性（另外定义实体）
第三范式(3NF)：在第二范式基础上，消除相互依赖的属性
在数仓或决策支持系统中，可能通过冗余提高查询性能，但这是例外。在OLTP系统通常遵守。
主键唯一标识一行，用#*表示。
关系可以是1:N, N:1,1:1，M:N。后两者，一对一和多对多比较少见。
1对多表示一个可以关联多个，例如一种颜色可以关联多个车，或多种车可以有一个颜色。
这种关系用crow’s foot notation表示，参考。
参照一致性与外键：
主键端是唯一，外键端的值必须在主键端存在。这对表之间的Join较关键
表是二维的，包括行和列。每一行的列数必须相同
这里提到了在关系型之前的层级模型（hierarchical model），层级模型将数据存于一个文件中，数据查询效率高，但数据移动较麻烦。层级模型的一个例子是将部门和员工存于一行，这样每一行并不等宽。
总的来说，关系型模型是最优的模型。
关于建模，请参考这篇文章Get Your Information in Order和这篇文章：Modeling and Accessing Relational Data
老的示例schema是SCOTT，目前都是用HR和OE等：

SQL> select table_name from all_tables where owner='OE';TABLE_NAME
--------------------------------------------------------------------------------
CUSTOMERS
INVENTORIES
ORDERS
ORDER_ITEMS
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
PRODUCT_REF_LIST_NESTEDTAB
PROMOTIONS
SUBCATEGORY_REF_LIST_NESTEDTAB
WAREHOUSES10 rows selected.SQL> select table_name from all_tables where owner='HR';TABLE_NAME
--------------------------------------------------------------------------------
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
COUNTRIES7 rows selected.

用户是登录数据库用的，schema是用户名下所有的对象。schema是用来存对象的，包括数据对象和PL/SQL对象等。用户初始创建时，schema为空。用户和schema是一一对应的，因此经常同时使用。
用户是用来访问对象的，用户默认拥有自身schema对象的权限，而没有访问其它schema的权限。
常用的用户是某些用户只有登录权限，本身的schema中并没有对象，只是访问其它schema中的对象；而某些用户不允许登录，只是拥有对象供其他用户访问。
数据对象必须存在于schema中，例如HR.EMPLOYEES表示表EMPLOYEES存在于HR schema中。
访问时必须写全，除非时HR用户本身或使用同义词。
HR和OE用户默认是锁定的，即不允许登录，以下命令可以解锁：

alter user hr account unlock identiified by password;

示例schema可以在创建数据库时建立，或者之后下载安装，例如：

@?/demo/schema/human_resources/hr_main.sql

其中@表示执行，？表示ORACLE_HOME.
在生产系统中，不建议建立示例schema。

执行基础SELECT命令

SELECT * ：所有
SELECT DISTINCT：消除重复
SELECT email e FROM hr.employees：e是别名
select ‘GOOD’ || ’ MORNING’ from dual： 字符串拼接

DUAL表：

SQL> select systimestamp from dual;SYSTIMESTAMP
---------------------------------------------------------------------------
15-SEP-19 11.55.34.847737 AM +00:00

虽然不是必须，但SQL中的保留着建议大写，另，对象的元数据在数据字典中是大写存放的。

字符串常数（literal）是以单引号包围的。而不是双引号：

SQL> select "GOOD" from dual;
select "GOOD" from dual*
ERROR at line 1:
ORA-00904: "GOOD": invalid identifier

转义：

SQL> select 'I''m OK!' from dual;'I''MOK
-------
I'm OK!

或者用q转义，注意单引号前后的X，可以是任意字符，表示分隔符：

SQL> select q'XI'm OK!X' from dual;Q'XI'MO
-------
I'm OK!

NULL表示值未知或空缺。所有NULL参与的操作结果均为NULL。

SQL> select NULL + 5 from dual;NULL+5
----------

主键不允许NULL，但外键允许。
关于基本的SELECT用户，请参见文章Getting Answers with SELECT

限制查询返回的行

where子句。where是可选的。
a=1: 数值条件
a=‘OK’：字符串条件
a=‘01-JAN-99’：日期条件
比较符号：=, >=, <=, >, <, BETWEEN…AND…，!=或<>, IN, LIKE，IS NULL
LIKE中的通配符包括%（0或多次匹配）和_（单次匹配）
LIKE中的转义：

SQL> select job_id from hr.jobs where job_id like 'SA\_%' escape '\';JOB_ID
----------
SA_MAN
SA_REP

IS NULL示例：

SQL> select * from dual where 5+null is null;D
-
XSQL> select * from dual where 5+null is not null;no rows selected

Bool操作：AND， OR，NOT
where的示例请参见文章Why WHERE Matters

排序查询返回的行

order by 语法：
在这里插入图片描述
关于示例，请参见文章An Order of Sorts，非常好。

SQL Row-Limiting 子句

在这里插入图片描述
参考这篇文章On ROWNUM and Limiting Results和这篇文章，以及这篇文章。
示例：

SQL> select * from hr.countries order by country_name;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CN China                                             3
DK Denmark                                           1
EG Egypt                                             4
FR France                                            1
DE Germany                                           1
IN India                                             3
IL Israel                                            4
IT Italy                                             1
JP Japan                                             3
KW Kuwait                                            4
ML Malaysia                                          3
MX Mexico                                            2
NL Netherlands                                       1
NG Nigeria                                           4
SG Singapore                                         3
CH Switzerland                                       1
UK United Kingdom                                    1
US United States of America                          2
ZM Zambia                                            4
ZW Zimbabwe                                          425 rows selected.SQL> select * from hr.countries order by country_name fetch first 5 rows only;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            25 rows selected.
SQL> select * from hr.countries where rownum <= 5 order by country_name;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            25 rows selected.SQL> select * from hr.countries order by country_name offset 2 rows fetch first 5 rows only;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
BE Belgium                                           1
BR Brazil                                            2
CA Canada                                            2
CN China                                             3
DK Denmark                                           15 rows selected.
SQL> select * from hr.countries order by region_id fetch first 5 rows with ties;CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
BE Belgium                                           1
UK United Kingdom                                    1
NL Netherlands                                       1
IT Italy                                             1
FR France                                            1
DK Denmark                                           1
DE Germany                                           1
CH Switzerland                                       18 rows selected.

符号替换

&符号，每次出现都必须输入。
&&符号，相同的只需输入一次。因为存作了会话变量。
可以替代literal或列名或表达式。
对于SQL*Plus和SQL Developer均适用。对于字符串替代无需考虑转义。

SQL> select * from hr.employees where employee_id =&empid;
Enter value for empid: 101
old   1: select * from hr.employees where employee_id =&empid
new   1: select * from hr.employees where employee_id =101EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------101 Neena                Kochhar
NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000100            901 row selected.
SQL> select * from hr.employees where hire_date = '&d';
Enter value for d: 21-SEP-05
old   1: select * from hr.employees where hire_date = '&d'
new   1: select * from hr.employees where hire_date = '21-SEP-05'EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------101 Neena                Kochhar
NKOCHHAR                  515.123.4568         21-SEP-05 AD_VP           17000100            901 row selected.

注意以上的with ties，是指会根据order by列出并列排名的行。
以下是set verify和表达式替换的示例：

SQL> select &a;
Enter value for a: * from dual
old   1: select &a
new   1: select * from dualD
-
X1 row selected.SQL> set verify off
SQL> select &a;
Enter value for a: * from dualD
-
X1 row selected.

&&替代避免重复，是因为存为了会话变量。可以用undefine取消定义。特别是如果第一次输错时有用：

SQL> select &&a;
Enter value for a: * from dual;
old   1: select &&a
new   1: select * from dual;
select * from dual;*
ERROR at line 1:
ORA-00933: SQL command not properly endedSQL> undefine a;
SQL> select &&a;
Enter value for a: * from dual
old   1: select &&a
new   1: select * from dualD
-
X

查看会话变量，蛮多有用信息：

SQL> define
DEFINE _DATE           = "15-SEP-19" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1903000000" (CHAR)
DEFINE A               = "* from dual" (CHAR)

可直接定义或更改会话变量：

SQL> define b=1

set define on|off决定了&和&&是否生效：

SQL> select 'Coda & Sid' from dual;
Enter value for sid: a
old   1: select 'Coda & Sid' from dual
new   1: select 'Coda a' from dual'CODAA
------
Coda aSQL> set define off
SQL> select 'Coda & Sid' from dual;'CODA&SID'
----------
Coda & SidSQL> show define
define "&" (hex 26)
SQL> set define off
SQL> show define
define OFF

这篇关于OCA/OCP Oracle 数据库12c考试指南读书笔记：第7章: Retrieving, Restricting, and Sorting Data Using SQL的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/744074。

相关文章

详谈redis跟数据库的数据同步问题

详谈redis跟数据库的数据同步问题

《详谈redis跟数据库的数据同步问题》文章讨论了在Redis和数据库数据一致性问题上的解决方案,主要比较了先更新Redis缓存再更新数据库和先更新数据库再更新Redis缓存两种方案,文章指出,删除R... 目录一、Redis 数据库数据一致性的解决方案1.1、更新Redis缓存、删除Redis缓存的区别二
阅读更多...
oracle数据库索引失效的问题及解决

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
阅读更多...
Mysql 中的多表连接和连接类型详解

Mysql 中的多表连接和连接类型详解

《Mysql中的多表连接和连接类型详解》这篇文章详细介绍了MySQL中的多表连接及其各种类型,包括内连接、左连接、右连接、全外连接、自连接和交叉连接,通过这些连接方式,可以将分散在不同表中的相关数据... 目录什么是多表连接？1. 内连接（INNER JOIN）2. 左连接（LEFT JOIN 或 LEFT
阅读更多...
C#实现文件读写到SQLite数据库

C#实现文件读写到SQLite数据库

《C#实现文件读写到SQLite数据库》这篇文章主要为大家详细介绍了使用C#将文件读写到SQLite数据库的几种方法,文中的示例代码讲解详细,感兴趣的小伙伴可以参考一下... 目录1. 使用 BLOB 存储文件2. 存储文件路径3. 分块存储文件《文件读写到SQLite数据库China编程的方法》博客中，介绍了文
阅读更多...
Oracle Expdp按条件导出指定表数据的方法实例

Oracle Expdp按条件导出指定表数据的方法实例

《OracleExpdp按条件导出指定表数据的方法实例》：本文主要介绍Oracle的expdp数据泵方式导出特定机构和时间范围的数据,并通过parfile文件进行条件限制和配置,文中通过代码介绍... 目录1.场景描述 2.方案分析3.实验验证 3.1 parfile文件3.2 expdp命令导出4.总结
阅读更多...
Android数据库Room的实际使用过程总结

Android数据库Room的实际使用过程总结

《Android数据库Room的实际使用过程总结》这篇文章主要给大家介绍了关于Android数据库Room的实际使用过程,详细介绍了如何创建实体类、数据访问对象（DAO）和数据库抽象类,需要的朋友可以... 目录前言一、Room的基本使用1.项目配置2.创建实体类（Entity）3.创建数据访问对象（DAO
阅读更多...
mysql重置root密码的完整步骤(适用于5.7和8.0)

mysql重置root密码的完整步骤(适用于5.7和8.0)

《mysql重置root密码的完整步骤(适用于5.7和8.0)》：本文主要介绍mysql重置root密码的完整步骤,文中描述了如何停止MySQL服务、以管理员身份打开命令行、替换配置文件路径、修改... 目录第一步：先停止mysql服务，一定要停止！方式一：通过命令行关闭mysql服务方式二：通过服务项关闭
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
mysql主从及遇到的问题解决

mysql主从及遇到的问题解决

《mysql主从及遇到的问题解决》本文详细介绍了如何使用Docker配置MySQL主从复制,首先创建了两个文件夹并分别配置了`my.cnf`文件,通过执行脚本启动容器并配置好主从关系,文中还提到了一些... 目录mysql主从及遇到问题解决遇到的问题说明总结mysql主从及遇到问题解决1.基于mysql
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2