search
HomeDatabaseMysql TutorialOracle基础第六章 查询

Oracle基础第六章 查询

Jun 07, 2016 pm 03:08 PM
dbaoracleBasecheckInquireTarget

本章目标 单表查询 算术运算 NULL 比较运算符 逻辑运算符 ORDER BY 子句 使用 SQL 运算符 多表查询 联结条件和联结类型 联结查询使用 SQL/92 语法 行的标识符 1. 行的标识符 a) 在 Oracle 数据库中的每一行都有一个唯一的行标识符,它用于 Oracle 数据库内部

本章目标

单表查询

算术运算

NULL

比较运算符

逻辑运算符

ORDER BY子句

使用SQL运算符

多表查询

联结条件和联结类型

联结查询使用SQL/92语法

 

行的标识符

1.        行的标识符

a)        在Oracle数据库中的每一行都有一个唯一的行标识符,它用于Oracle数据库内部存储行的物理位置。

b)        例如,查看customer表的信息

SELECT ROWID, customer_id

FROM customers;

c)        查询效果如下图所示:

 

Oracle基础第六章 查询

d)        需要注意的是ROWID并不出现在表的定义中

e)        例如,可以使用DESCRIBE命令查看customer表的信息

DESCRIBE customers

f)         执行效果如下图所示:

 

Oracle基础第六章 查询

 

2.       ROWNUM

a)       ROWNUM可以返回结果集的行号,查询返回的第一行编号为1,第二行编号为2,依此类推

b)        例如,查询customer表的相关信息

SELECT ROWNUM, customer_id, first_name, last_name

FROM customers;

c)        查询效果如下图所示:

 

Oracle基础第六章 查询

d)        可以将ROWNUM使用在WHERE子句中

e)        例如

SELECT ROWNUM, customer_id, first_name, last_name

FROM customers

WHERE customer_id = 3;

f)         查询效果如下图所示:

 

Oracle基础第六章 查询

3.        数据完整性-2

a)        实体完整性

b)        域完整性

c)        引用完整性

d)        自定义完整性

4.        实体完整性

a)        实体完整性

实体完整性要求每一个表中的主键字段都不能为空或者重复的值。

b)        实现方法

                        i.              唯一约束

                      ii.              主键约束

5.        域完整性

a)        域完整性

域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等

b)        实现方法

                        i.              限制数据类型

                      ii.              外键约束

                    iii.              默认值

                     iv.              非空约束

6.        引用完整性

a)        引用完整性

                        i.              也称之为参照完整性,当更新、删除、插入一个表中的数据时,通过参照引用相互关联的另一个表中的数据,来检查对表的数据操作是否正确。

                      ii.              引用完整性要求关系中不允许引用不存在的实体。

                    iii.              引用完整性与实体完整性是关系模型必须满足的完整性约束条件。

b)        实现方法

外键约束

7.        自定义完整性

a)        自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件,它反映某一具体应用所涉及的数据必须满足的语义要求。

b)        实现方法

                        i.              存储过程

                      ii.              触发器

8.        选择主键的原则

a)        最少性

尽量选择单个键作为主键

b)        稳定性

尽量选择数值更新少的列作为主键

9.        主外键关系

a)        注意事项

                        i.              当主表中没有对应的记录时,不能将记录添加到子表

                      ii.              不能更改主表中的值而导致子表中的记录孤立

                    iii.              子表存在与主表对应的记录,不能从主表中删除该记录

                     iv.              删除主表前,先删子表

10.    表约束

a)        表约束的目的:确保表中数据的完整性

b)        常用的约束类型:

                        i.              主键约束(PRIMARY KEY):要求主键列数据唯一,并且不允许为空

                      ii.              非空约束(NOT NULL):指定的列的值不允许为空

                    iii.              唯一键约束(UNIQUE):要求该列唯一,允许为空,但只能出现一个空值

                     iv.              检查约束(CHECK):指定表中一列或多列可以接受的数据值格式

                       v.              默认约束(DEFAULT):指定某列的默认值

                     vi.              外键约束(FOREIGN KEY):用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行

日期计算

1.        日期计算

a)        可以在日期上使用加法或减法运算符,加减操作的数字表示天数

b)        例如,在日期上加3

SELECT TO_DATE('16-5-2012')+3

FROM dual;

c)        在某日期上减3

SELECT TO_DATE('16-5-2012')-3

FROM dual;

d)        执行效果如右图所示:

 

Oracle基础第六章 查询

dual表

1.       dual

a)       dual表常用在没有查询目标的SELECT语句块中,它只包含一行数据

b)        可以使用DESCRIBE查看dual表结构

DESCRIBE dual

c)        查询dual表的数据

SELECT *

FROM dual;

d)        执行效果如下图所示:

 

Oracle基础第六章 查询

 

2.        列的别名

a)        通常情况,当数据行从表中查询出来时,Oracle会将列名转换为大写,并显示在数据上方

b)        如果查询语句中有表达式时,表达式的名称将会显示在列位值,可以为其提供别名;别名可以使用双引号,也可以在别名前加AS关键字

c)        例如

SELECT price * 2 AS "DOUBLE_PRICE"

FROM products;

d)        查询效果如下图所示:

 

Oracle基础第六章 查询

 

单表查询

1.        连接

a)        可以将一些列连接起来显示,连接使用||运算符

b)        例如,查询customer表的first_name列和last_name

c)       SELECT first_name || ' ' || last_name AS "Customer Name"

d)       FROM customers;

e)        查询效果如下图所示:

 

Oracle基础第六章 查询

2.       NULL

a)       NULL值表示未知的值。它是一个特殊的值,但并不是空字符串,NULL值表示该列是未知的。

b)        当某些查询语句在输出结果列上看不到值的时候,可能就是NULL

c)        例如

SELECT customer_id, first_name, last_name, dob

FROM customers

WHERE dob IS NULL;

d)        查询效果如下图所示:

 

Oracle基础第六章 查询

3.        处理NULL

a)       NULL值被查询出来的时候没有显示信息,如何告知用户这是空字符串还是NULL,这可以通过NVL()函数来进行处理

b)        例如

SELECT customer_id, first_name, last_name,

  NVL(phone, 'Unknown phone number') AS PHONE_NUMBER

FROM customers;

c)        查询效果如下图所示:

 

Oracle基础第六章 查询

4.       DISTINCT关键字

a)        在SELECT语句中,可以使用DISTINCT关键字删除重复的行

b)        例如,查询purchases表中,删除重复的customer_id

SELECT DISTINCT customer_id

FROM purchases;

c)        查询结果如下图所示:

 

Oracle基础第六章 查询

5.        比较运算符

a)        比较运算符

                        i.              =:等于

                      ii.              !=:不等于

                    iii.              :小于

                     iv.              >:大于

                       v.              :小于或等于

                     vi.              >=:大于或等于

                   vii.              ANY:比较值列表中的任何一个值

                 viii.              ALL:比较列表值中的所有值

b)       运算符

                        i.              例如,查询customer_id不等于2的客户信息

SELECT *

FROM customers

WHERE customer_id 2;

                      ii.              查询效果如下图所示:

 

Oracle基础第六章 查询

c)       >运算符

                        i.              例如,查询商品编号大于8的商品

SELECT product_id, name

FROM products

WHERE product_id > 8;

                      ii.              查询效果如下图所示:

 

Oracle基础第六章 查询

d)       运算符

                        i.              例如,查询ROWNUM伪列小于等于3,即查询前3件商品信息

SELECT ROWNUM, product_id, name

FROM products

WHERE ROWNUM

                      ii.              查询效果如下图所示:

 

Oracle基础第六章 查询

e)       ANY运算符

                        i.              在WHERE子句中使用ANY运算符与列表中的任意值进行比较

                      ii.              必须在使用ANY运算符之前使用= ,  ,  ,  或  >=运算符

                    iii.              例如,查询customer表,customer_id值只要大于列表(234)中的任意一个值即满足条件

SELECT *

FROM customers

WHERE customer_id > ANY (2, 3, 4);

                     iv.              查询效果如下图所示:

 

Oracle基础第六章 查询

f)        ALL运算符

                        i.              在WHERE子句中使用ALL运算符与值列表中的所有值进行比较

                      ii.              例如,查询customer

SELECT *

FROM customers

WHERE customer_id > ALL (2, 3, 4);

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

6.       SQL运算符

a)       SQL运算符可以进行模式匹配,像字符串、列表值、NULL值。

                        i.              LIKE:匹配字符串

                      ii.              IN:匹配列表值

                    iii.              BETWEEN AND:匹配范围值

                     iv.              IS NULL:匹配空值

                       v.              IS NAN:匹配非数字值

b)        同样可以在上述运算符前加NOT取反

                        i.              如:NOT LIKE ,NOT BETWEEN,IS NOT NULL,IS NOT NAN

c)       LIKE运算符

                        i.              可以在WHERE子句中使用LIKE运算符进行字符串的匹配

                      ii.              可以使用普通字符及通配符进行组合,通配符如下:

                    iii.              下划线(_):表示匹配某个位置的字符

                     iv.              百分号(%):表示在某个位置的任意个字符

                       v.              例如,匹配’_o%’,表示第一个字母任意,第二个是o%表示最后可以是任意个字母

                     vi.              SELECT *

                   vii.              FROM customers

                 viii.              WHERE first_name LIKE '_o%';

                     ix.              查询效果如下图所示:

 

Oracle基础第六章 查询

d)        转义字符

                        i.              如果要查询实际的下划线或百分号就需要使用ESCAPE选项区分通配符

                      ii.              ESCAPE选项告知数据库如何区分通配符和要匹配的字符,可以使用反斜杠(\),例如,

SELECT name

FROM promotions

WHERE name LIKE '%\%%' ESCAPE '\';

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

e)       IN运算符

                        i.              在WHERE子句中可以使用IN筛选某列的值是否在列表中

                      ii.              例如,查询customers表,检查customer_id是否在235

SELECT *

FROM customers

WHERE customer_id IN (2, 3, 5);

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

f)        BETWEEN运算符

                        i.              在WHERE子句中可以使用BETWEEN检查某列的值是否在一个指定的范围内。包含区间两端的值。

                      ii.              例如,查询customerscustomer_id13之间的客户

SELECT *

FROM customers

WHERE customer_id BETWEEN 1 AND 3;

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

7.        逻辑运算符

a)        逻辑运算符允许根据逻辑条件限制行

                        i.              x AND y

                      ii.              xy都必须是真时,返回真

                    iii.              x OR y

                     iv.              xy为真时,返回真

                       v.              NOT x

                     vi.              x为假时,返回真

b)       AND运算符

                        i.              例如

                      ii.              查询customer表中dob列大于’01-1-1970’年,并且customer_id大于3的客户信息

SELECT *

FROM customers

WHERE dob > '01-1-1970'

AND customer_id > 3;

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

c)       OR运算符

                        i.              例如

                      ii.              查询customer表中dob列大于’01-1-1970’年,或者customer_id大于3的客户信息

SELECT *

FROM customers

WHERE dob > '01-1-1970'

OR customer_id > 3;

                    iii.              查询效果如下图所示:

 

Oracle基础第六章 查询

8.       ORDER BY

a)        可以使用ORDER BY对查询的结果进行排序,可以对一列或多列进行排序,ORDER BY必须在FROM子句后或WHERE子句后(如果有WHERE子句)

b)        例如,查询customer表,并对last_name列进行排序

SELECT *

FROM customers

ORDER BY last_name;

c)        查询结果如下所示:

 

Oracle基础第六章 查询

d)        默认情况下,ORDER BY是按照升序(ASC)排序,也可以使用DESC指定为降序排序

e)        例如,查询customers表,先按first_name升序排列,再按last_name降序排列

SELECT *

FROM customers

ORDER BY first_name ASC, last_name DESC;

f)         查询结果如下所示:

 

Oracle基础第六章 查询

g)        同样可以是列的位置编号,指定哪列需要进行排序

h)        例如,查询customer表,要对查询的第2first_name列进行降序排列

SELECT customer_id, first_name, last_name

FROM customers

ORDER BY 2 DESC;

i)          查询结果如下所示:

 

Oracle基础第六章 查询

多表查询

1.        多表查询

a)        不同的数据存储在不同的表中,通常要查询多张表才能找到需要的数据

                        i.              例如,要查询product_id3的商品类型,需要两步骤

                      ii.              从products表查询出product_id3的商品的product_type_id类型

                    iii.              根据上一步骤查询出的product_type_id查询出product_types

                     iv.              查询顺序如图所示:

 

Oracle基础第六章 查询

b)        联结两张表的查询可以在FROM后面加两张表,然后再指定两张表的关联关系

                        i.              首先指定查询的两张表,如下

                      ii.              FROM products, product_types

                    iii.              再通过WHERE条件指定两表的关系

                     iv.              WHERE products.product_type_id = product_types.product_type_id

                       v.              AND products.product_id = 3;

                     vi.              说明

                   vii.              通常情况下,是一个表的主键与另外一个表的外键相匹配;再指定产品的编号为3,即products.product_id = 3

c)        两表查询语句如下

SELECT products.name, product_types.name

FROM products, product_types

WHERE products.product_type_id = product_types.product_type_id

AND products.product_id = 3;

查询结果如下图所示:

 

Oracle基础第六章 查询

2.        表的别名

a)       products表和product_types表相关字段会用在SELECT语句及WHERE子句上,可以给表起别名,提高代码可读性、降低书写难度

b)        例如,改写查询语句

SELECT p.name, pt.name

FROM products p, product_types pt

WHERE p.product_type_id = pt.product_type_id

AND p.product_id = 3

ORDER BY p.name;

c)        查询效果如下图所示:

 

Oracle基础第六章 查询

3.        笛卡尔乘积

a)        如果查询两张表,并且没有定义联结条件,那么查询的结果集是两表相乘的结果,这样的情况称之为笛卡尔乘积

b)        笛卡尔乘积会产生很多行结果。比如,第一张表的行数为50行,第二张表为100行,那么笛卡尔乘积产生的结果为5000行。因为第一张表的每条记录会对应第二张表的100条记录,即50*100=5000

c)        例如

SELECT pt.product_type_id, p.product_id

FROM product_types pt, products p;

d)        查询效果如下图所示:

 

Oracle基础第六章 查询

4.        多表查询

a)        多表查询,需要查询的信息包含

                        i.              购买商品信息(在purchases中)

                      ii.              顾客姓名信息(在customers表中)

                    iii.              购买的商品名称(在products表中)

                     iv.              购买的商品类型(在product_types表中)

b)        查询语句如下

SELECT c.first_name, c.last_name, p.name AS PRODUCT, pt.name AS TYPE

FROM customers c, purchases pr, products p, product_types pt

WHERE c.customer_id = pr.customer_id

AND p.product_id = pr.product_id

AND p.product_type_id = pt.product_type_id

ORDER BY p.name;

c)        总结

                        i.              多表查询WHERE时,联结次数=查询时联结表的数量-1

联结条件和联结类型

1.        联结条件和联结类型

a)        常见的有两类联结条件

                        i.              等值联结:一般使用等于号(=)操作符

                      ii.              非等值联结:使用除了等于以外的运算符,例如,>BETWEEN

b)        常见的三种联结类型

                        i.              内联结

1.        内联结返回的行只有满足联结条件才会返回。如果联结条件的列中有NULL值,那么该行则不会返回

                      ii.              外联结

1.        外联结返回的行满足联结条件,也包括在联结条件的列包含空值的行

                    iii.              自联结

1.        联结的表为同一张表

c)        非等值联结

                        i.              非等值联结一般使用>>=LIKEINBETWEEN等运算符

                      ii.              非等值联结一般使用比较少

                    iii.              例如,现有工资等级表(salary_grades)

1.        数据如下:

Oracle基础第六章 查询

 

2.        现需要查询出员工的工资等级

                     iv.              要查询出员工工资等级需要使用非等值联结,使用BETWEEN运算符

1.       SELECT e.first_name, e.last_name, e.title, e.salary, sg.salary_grade_id

2.       FROM employees e, salary_grades sg

3.       WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary

4.       ORDER BY salary_grade_id;

                       v.              查询结果如下图所示:

Oracle基础第六章 查询

 

2.        外联结

a)        外联结可以查询出即使列中包含NULL值的数据也可以查询出来

b)        外部联结运算符 (+)。在使用(+)的字段,可能包含NULL

c)        例如,

                        i.              SELECT p.name, pt.name

                      ii.              FROM products p, product_types pt

                    iii.              WHERE p.product_type_id = pt.product_type_id (+)

                     iv.              ORDER BY p.name;

d)        查询结果如右图所示:

                       

Oracle基础第六章 查询

3.        外联结分类

a)        外联结可以分为

                        i.              左外联结

                      ii.              右外联结

b)        左外联结右外联结

                        i.              现有有以下查询语句

1.       SELECT ...

2.       FROM table1, table2

3.       ...

4.        其中table1.column1table2.column2为联结条件

                      ii.              假设1table1表包含了某行的column1列的值为NULL

1.        左外联结的WHERE条件写作

2.       WHERE table1.column1 = table2.column2 (+);

                    iii.              假设2table2表包含了某行的column2列的值为NULL

1.        右外联结的WHERE条件写作

2.       WHERE table1.column1 (+) = table2.column2;

c)        左外联结

                        i.              例如,有查询语句如下

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),