没有合适的资源？快使用搜索试试~ 我知道了~

首页PostgreSQL编程进阶：构建强力数据库应用

PostgreSQL编程进阶：构建强力数据库应用

postgresql

需积分: 0 6 下载量 194 浏览量更新于2024-07-19 收藏 2.73MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

试读

205页

"《Packt.PostgreSQL.Development.Essentials》是一本专为有基础的PostgreSQL数据库开发者设计的教程书籍，旨在深入教授他们在使用PostgreSQL开发数据库应用时所需掌握的实践技巧和编程方法。作者Manpreet Kaur和Baji Shaik共同撰写了这本书，以帮助读者构建功能强大的数据库应用程序。该书的核心内容聚焦于发展程序性函数，即如何利用PostgreSQL的强大功能来设计和实现复杂的数据库操作。读者将学习到如何优化查询性能，处理并发访问，以及如何利用PostgreSQL的扩展和工具来增强应用程序的功能。书中涵盖了SQL语言的高级特性、数据类型管理、事务处理、视图与存储过程、索引优化、触发器和函数等核心主题。通过阅读本书，数据库开发者不仅能提升他们的技术技能，还能了解如何在实际项目中正确运用PostgreSQL，确保数据安全性和一致性。此外，书中的例子和实战项目将帮助读者巩固理论知识，以便在实际工作中游刃有余。尽管本书竭力提供准确的信息，但须注意，由于技术的快速变化，部分内容可能存在时效性，且所有版权和使用限制必须遵守，未经出版商（Packt Publishing）事先书面许可，不得复制、存储或以任何形式传播书中的内容，除非用于嵌入在批评性文章或评论中。《PostgreSQL Development Essentials》首次出版于2016年9月，作为一本权威指南，它不仅适合初学者进阶，也适合有一定经验的开发者深化对PostgreSQL的理解，是提升PostgreSQL开发能力的不可或缺的参考资料。然而，尽管出版商努力确保商标信息的准确性，但并不保证其完全正确，因此在使用时应自行核实。"

资源详情

资源推荐

Preface

[ 3 ]

A block of code is set as follows:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.sql.ResultSet;

import java.sql.SQLException;

Any command-line input or output is written as follows:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE [condition];

New terms and important words are shown in bold.

Warnings or important notes appear in a box like this.

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this

book—what you liked or disliked. Reader feedback is important for us as it helps us

develop titles that you will really get the most out of. To send us general feedback, simply

e-mail feedback@packtpub.com, and mention the book's title in the subject of your

message. If there is a topic that you have expertise in and you are interested in either

writing or contributing to a book, see our author guide at www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you

to get the most from your purchase.

Preface
[ 4 ]
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do
happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-
we would be grateful if you could report this to us. By doing so, you can save other readers
from frustration and help us improve subsequent versions of this book. If you find any
errata, please report them by visiting h t t p : / / w w w . p a c k t p u b . c o m / s u b m i t - e r r a t a, selecting
your book, clicking on the Errata Submission Form link, and entering the details of your
errata. Once your errata are verified, your submission will be accepted and the errata will
be uploaded to our website or added to any list of existing errata under the Errata section of
that title.
To view the previously submitted errata, go to h t t p s : / / w w w . p a c k t p u b . c o m / b o o k s / c o n t e n
t / s u p p o r t and enter the name of the book in the search field. The required information will
appear under the Errata section.
Piracy
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At
Packt, we take the protection of our copyright and licenses very seriously. If you come
across any illegal copies of our works in any form on the Internet, please provide us with
the location address or website name immediately so that we can pursue a remedy.
Please contact us at copyright@packtpub.com with a link to the suspected pirated
material.
We appreciate your help in protecting our authors and our ability to bring you valuable
content.
Questions
If you have a problem with any aspect of this book, you can contact us
at questions@packtpub.com, and we will do our best to address the problem.

Advanced SQL

This book is all about an open source software product, a relational database called

PostgreSQL. PostgreSQL is an advanced SQL database server, available on a wide range of

platforms. The purpose of this book is to teach database developers the fundamental

practices and techniques to program database applications with PostgreSQL.

In this chapter, we will discuss the following advanced SQL topics:

Creating views

Understanding materialized views

Creating cursors

Using the GROUP BY clause

Using the HAVING clause

Understanding complex topics such as subqueries and joins

Creating views

A view is a virtual table based on the result set of an SQL statement. Just like a real table, a

view consist of rows and columns. The fields in a view are from one or more real tables in

the database. Generally speaking, a table has a set of definitions that physically stores data.

A view also has a set of definitions built on top of table(s) or other view(s) that does not

physically store data. The purpose of creating views is to make sure that the user does not

have access to all the data and is being restricted through a view. Also, it's better to create a

view if we have a query based on multiple tables so that we can use it straightaway rather

than writing a whole PSQL again and again.

Database views are created using the CREATE VIEW statement. Views can be created from a

single table or multiple tables, or another view.

Advanced SQL

[ 6 ]

The basic CREATEVIEW syntax is as follows:

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE [condition];

Let's take a look at each of these commands:

CREATE VIEW: This command helps create the database's view.

SELECT: This command helps you select the physical and virtual columns that

you want as part of the view.

FROM: This command gives the table names with an alias from where we can fetch

the columns. This may include one or more table names, considering you have to

create a view at the top of multiple tables.

WHERE: This command provides a condition that will restrict the data for a view.

Also, if you include multiple tables in the FROM clause, you can provide the

joining condition under the WHERE clause.

You can then query this view as though it were a table. (In PostgreSQL, at the time of

writing, views are read-only by default.) You can SELECT data from a view just as you

would from a table and join it to other tables; you can also use WHERE clauses. Each time

you execute a SELECT query using the view, the data is rebuilt, so it is always up-to-date. It

is not a frozen copy stored at the time the view was created.

Let's create a view on supplier and order tables. But, before that, let's see what the structure

of the suppliers and orders table is:

CREATE TABLE suppliers

(supplier_id number primary key,

Supplier_name varchar(30),

Phone_number number);

CREATE TABLE orders

(order_number number primary key,

Supplier_id number references suppliers(supplier_id),

Quanity number,

Is_active varchar(10),

Price number);

CREATE VIEW active_supplier_orders AS

SELECT suppliers.supplier_id, suppliers.supplier_name orders.quantity,

orders.price

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id

WHERE suppliers.supplier_name = 'XYZ COMPANY'

Advanced SQL

[ 7 ]

And orders.active='TRUE';

The preceding example will create a virtual table based on the result set of the SELECT

statement. You can now query the PostgreSQL VIEW as follows:

SELECT * FROM active_supplier_orders;

Deleting and replacing views

To delete a view, simply use the DROP VIEW statement with view_name. The basic

DROPVIEW syntax is as follows:

DROP VIEW IF EXISTS view_name;

If you want to replace an existing view with one that has the same name and returns the

same set of columns, you can use a CREATE OR REPLACE command.

The following is the syntax to modify an existing view:

CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name(s)

WHERE condition;

Let's take a look at each of these commands:

CREATE OR REPLACE VIEW: This command helps modify the existing view.

SELECT: This command selects the columns that you want as part of the view.

FROM: This command gives the table name from where we can fetch the columns.

This may include one or more table names, since you have to create a view at the

top of multiple tables.

WHERE: This command provides the condition to restrict the data for a view. Also,

if you include multiple tables in the FROM clause, you can provide the joining

condition under the WHERE clause.

Let's modify a view, supplier_orders, by adding some more columns in the view. The

view was originally based on supplier and order tables having supplier_id,

supplier_name, quantity, and price. Let's also add order_number in the view.

CREATE OR REPLACE VIEW active_supplier_orders AS

SELECT suppliers.supplier_id, suppliers.supplier_name orders.quantity,

orders.price,order. order_number

FROM suppliers

剩余204页未读，继续阅读

粉丝: 0
资源: 1

上传资源快速赚钱

我的内容管理展开

我的资源快来上传第一个资源

我的收益

登录查看自己的收益

我的积分登录查看自己的积分

我的C币登录后查看C币余额

我的收藏

我的下载

下载帮助

PostgreSQL编程进阶：构建强力数据库应用

原版书 Packt.Unity Game Development Essentials.2009

Packt.Linux.Device.Drivers.Development.2017

Packt.Mastering.PostgreSQL10

关于Java的外文期刊参考文献

能不能找出那种专著类型的参考文献至少三个外文的，跟Java，JavaEE有关的并且是2018年以后的

有关liunx的英文参考文献

有关matplotlib库的文献

mastering elasticsearch[m]. 2nd edition. uk: packt publishing

关于vue的外文文献参考

给我列举几个关于SpringBoot的文献引用

kotlin .apply

关于java的外语文献_java英文参考文献（涵盖3年最新120个）

关于springboot的外文参考文献

custom tiles provider - humanitarian map style | packt courseware

关于MongoDB的十条参考文献

人工智能深度学习参考文献

springboot参考文献

有关python大数据分析技术的文献及其作者和出处

关于javaweb的外文参考文献书本

深度学习参考文献列表

最新资源