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。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
SpringBoot基于注解实现数据库字段回填的完整方案

SpringBoot基于注解实现数据库字段回填的完整方案

《SpringBoot基于注解实现数据库字段回填的完整方案》这篇文章主要为大家详细介绍了SpringBoot如何基于注解实现数据库字段回填的相关方法,文中的示例代码讲解详细,感兴趣的小伙伴可以了解... 目录数据库表pom.XMLRelationFieldRelationFieldMapping基础的一些代
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
使用Node.js和PostgreSQL构建数据库应用

使用Node.js和PostgreSQL构建数据库应用

《使用Node.js和PostgreSQL构建数据库应用》PostgreSQL是一个功能强大的开源关系型数据库,而Node.js是构建高效网络应用的理想平台,结合这两个技术,我们可以创建出色的数据驱动... 目录初始化项目与安装依赖建立数据库连接执行CRUD操作查询数据插入数据更新数据删除数据完整示例与最佳
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2