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中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接（cross join）三、自然连接（naturalandroid join）四
阅读更多...
Mysql如何将数据按照年月分组的统计

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》：本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA
阅读更多...
Mysql表如何按照日期字段的年月分区

Mysql表如何按照日期字段的年月分区

《Mysql表如何按照日期字段的年月分区》：本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区
阅读更多...
mysql的基础语句和外键查询及其语句详解(推荐)

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》：本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键
阅读更多...
MySQL更新某个字段拼接固定字符串的实现

MySQL更新某个字段拼接固定字符串的实现

《MySQL更新某个字段拼接固定字符串的实现》在MySQL中,我们经常需要对数据库中的某个字段进行更新操作,本文就来介绍一下MySQL更新某个字段拼接固定字符串的实现,感兴趣的可以了解一下... 目录1. 查看字段当前值2. 更新字段拼接固定字符串3. 验证更新结果mysql更新某个字段拼接固定字符串 -
阅读更多...
python连接本地SQL server详细图文教程

python连接本地SQL server详细图文教程

《python连接本地SQLserver详细图文教程》在数据分析领域,经常需要从数据库中获取数据进行分析和处理,下面：本文主要介绍python连接本地SQLserver的相关资料,文中通过代码... 目录一.设置本地账号1.新建用户2.开启双重验证3,开启TCP/IP本地服务二js.python连接实例1.
阅读更多...
Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

Spring Boot项目中结合MyBatis实现MySQL的自动主从切换功能

《SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能》：本文主要介绍SpringBoot项目中结合MyBatis实现MySQL的自动主从切换功能,本文分步骤给大家介绍的... 目录原理解析1. mysql主从复制（Master-Slave Replication）2. 读写分离3.
阅读更多...
Spring Security基于数据库的ABAC属性权限模型实战开发教程

Spring Security基于数据库的ABAC属性权限模型实战开发教程

《SpringSecurity基于数据库的ABAC属性权限模型实战开发教程》：本文主要介绍SpringSecurity基于数据库的ABAC属性权限模型实战开发教程,本文给大家介绍的非常详细,对大... 目录1. 前言2. 权限决策依据RBACABAC综合对比3. 数据库表结构说明4. 实战开始5. MyBA
阅读更多...
Ubuntu中远程连接Mysql数据库的详细图文教程

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.
阅读更多...
Oracle数据库常见字段类型大全以及超详细解析

Oracle数据库常见字段类型大全以及超详细解析

《Oracle数据库常见字段类型大全以及超详细解析》在Oracle数据库中查询特定表的字段个数通常需要使用SQL语句来完成,：本文主要介绍Oracle数据库常见字段类型大全以及超详细解析,文中通过... 目录前言一、字符类型（Character）1、CHAR：定长字符数据类型2、VARCHAR2：变长字符数
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2