Learning SQL第二版：数据库管理与查询指南

SQL

需积分: 9 142 浏览量更新于2024-07-16 收藏 1.51MB PDF 举报

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

领优惠券(最高得80元）

"Learning SQL, Second Edition 是一本由 Alan Beaulieu 编著的书籍，由 O'Reilly Media, Inc. 出版，发行日期为2009年4月27日，专注于教授SQL基础知识和技术，适用于最新的数据库管理系统，包括MySQL 6.0、Oracle 11g和Microsoft的SQL Server 2008。这本书通过336页的篇幅，深入浅出地介绍了SQL的关键概念和技巧，并配有丰富的插图和注解示例。每章都包含一个独立的SQL课程，书末的练习题可以帮助读者巩固所学知识。" 在这本书中，你可以期待学习以下SQL知识点： 1. **SQL基础**：快速了解SQL语言的基本语法和结构，包括如何创建、更新和删除数据（CRUD）操作，以及如何在数据库中进行查询。 2. **数据陈述**：掌握如何使用SELECT语句来检索数据，包括选择、投影、排序、分组和过滤数据。书中会详细介绍如何使用WHERE子句、GROUP BY和HAVING子句，以及ORDER BY子句。 3. **数据库对象**：学习如何用SQL架构语句创建数据库对象，如表格、索引和约束。理解表格的设计，包括主键、外键和唯一性约束的概念，以及如何通过ALTER TABLE语句修改已有表的结构。 4. **高级特性**：进一步探讨更复杂的SQL功能，如联接（JOIN）操作，用于合并来自多个表的数据；子查询（Subqueries），它们可以在主查询中嵌套，用于复杂的数据比较和计算。 5. **数据交互与查询**：理解数据集之间的关系，以及如何利用这些关系来构建更复杂的查询。这可能包括自连接、外连接和交叉连接，以及如何使用UNION和INTERSECT操作。 6. **数据转换与操作**：学习如何使用SQL对数据进行转换和处理，比如类型转换、日期和时间操作、字符串函数以及数学运算。还会涉及到如何使用CASE语句进行条件判断和数据转换。通过这本书，无论是为了编写数据库应用程序，执行管理任务，还是生成报告，都可以帮助你快速上手并精通SQL。无论你是SQL新手还是有一定经验的开发者，都能从中受益。书中的实例和练习将使理论知识得以实践，从而提升你在实际工作中的SQL技能。

资源详情

资源推荐

(known as the primary key ), along with additional information needed to describe the entity

completely. Looking again at the customer table, the cust_id column holds a different number for each

customer; George Blake, for example, can be uniquely identified by customer ID #1. No other customer

will ever be assigned that identifier, and no other information is needed to locate George Blake's data in

the customer table.

NOTE

Every database server provides a mechanism for generating unique sets of numbers to use as

primary key values, so you won't need to worry about keeping track of what numbers have been

assigned.

While I might have chosen to use the combination of the fname and lname columns as the primary key

(a primary key consisting of two or more columns is known as a compound key ), there could easily be

two or more people with the same first and last names that have accounts at the bank. Therefore, I

chose to include the cust_id column in the customer table specifically for use as a primary key column.

NOTE

In this example, choosing fname /lname as the primary key would be referred to as a natural key

, whereas the choice of cust_id would be referred to as a surrogate key . The decision whether

to employ natural or surrogate keys is a topic of widespread debate, but in this particular case

the choice is clear, since a person's last name may change (such as when a person adopts a

spouse's last name), and primary key columns should never be allowed to change once a value

has been assigned.

Some of the tables also include information used to navigate to another table; this is where the

"redundant data" mentioned earlier comes in. For example, the account table includes a column called

cust_id , which contains the unique identifier of the customer who opened the account, along with a

column called product_cd , which contains the unique identifier of the product to which the account will

conform. These columns are known as foreign keys , and they serve the same purpose as the lines that

connect the entities in the hierarchical and network versions of the account information. If you are

looking at a particular account record and want to know more information about the customer who

opened the account, you would take the value of the cust_id column and use it to find the appropriate

row in the customer table (this process is known, in relational database lingo, as a join ; joins are

introduced in Chapter 3 and probed deeply in Chapters Chapter 5 and Chapter 10 ).

It might seem wasteful to store the same data many times, but the relational model is quite clear on

what redundant data may be stored. For example, it is proper for the account table to include a column

for the unique identifier of the customer who opened the account, but it is not proper to include the

customer's first and last names in the account table as well. If a customer were to change her name,

for example, you want to make sure that there is only one place in the database that holds the

customer's name; otherwise, the data might be changed in one place but not another, causing the data

in the database to be unreliable. The proper place for this data is the customer table, and only the

cust_id values should be included in other tables. It is also not proper for a single column to contain

multiple pieces of information, such as a name column that contains both a person's first and last

names, or an address column that contains street, city, state, and zip code information. The process of

refining a database design to ensure that each independent piece of information is in only one place

(except for foreign keys) is known as normalization .

Getting back to the four tables in Figure 1-3 , you may wonder how you would use these tables to find

George Blake's transactions against his checking account. First, you would find George Blake's unique

identifier in the customer table. Then, you would find the row in the account table whose cust_id

column contains George's unique identifier and whose product_cd column matches the row in the

product table whose name column equals "Checking." Finally, you would locate the rows in the

transaction table whose account_id column matches the unique identifier from the account table.

This might sound complicated, but you can do it in a single command, using the SQL language, as you

will see shortly.

SQL SQL Databases Programming Alan Beaulieu O'Reilly Media, Inc. Learning SQL, 2nd Edition

1.2. What Is SQL?

Along with Codd's definition of the relational model, he proposed a language called DSL/Alpha for

manipulating the data in relational tables. Shortly after Codd's paper was released, IBM

commissioned a group to build a prototype based on Codd's ideas. This group created a

simplified version of DSL/Alpha that they called SQUARE. Refinements to SQUARE led to a

language called SEQUEL, which was, finally, renamed SQL.

SQL is now entering middle age (as is this author, alas), and it has undergone a great deal of

change along the way. In the mid-1980s, the American National Standards Institute (ANSI)

began working on the first standard for the SQL language, which was published in 1986.

Subsequent refinements led to new releases of the SQL standard in 1989, 1992, 1999, 2003, and

2006. Along with refinements to the core language, new features have been added to the SQL

language to incorporate object-oriented functionality, among other things. The latest standard,

SQL:2006, focuses on the integration of SQL and XML and defines a language called XQuery

which is used to query data in XML documents.

SQL goes hand in hand with the relational model because the result of an SQL query is a table

(also called, in this context, a result set). Thus, a new permanent table can be created in a

relational database simply by storing the result set of a query. Similarly, a query can use both

permanent tables and the result sets from other queries as inputs (we explore this in detail in

Chapter 9).

One final note: SQL is not an acronym for anything (although many people will insist it stands for

"Structured Query Language"). When referring to the language, it is equally acceptable to say

the letters individually (i.e., S. Q. L.) or to use the word sequel.

1.2.1. SQL Statement Classes

The SQL language is divided into several distinct parts: the parts that we explore in this book

include SQL schema statements, which are used to define the data structures stored in the

database; SQL data statements, which are used to manipulate the data structures previously

defined using SQL schema statements; and SQL transaction statements, which are used to

begin, end, and roll back transactions (covered in Chapter 12). For example, to create a new

table in your database, you would use the SQL schema statement create table, whereas the

process of populating your new table with data would require the SQL data statement insert.

To give you a taste of what these statements look like, here's an SQL schema statement that

creates a table called corporation:

CREATE TABLE corporation

(corp_id SMALLINT,

name VARCHAR(30),

CONSTRAINT pk_corporation PRIMARY KEY (corp_id)

);

This statement creates a table with two columns, corp_id and name, with the corp_id column

identified as the primary key for the table. We probe the finer details of this statement, such as

the different data types available with MySQL, in Chapter 2. Next, here's an SQL data statement

that inserts a row into the corporation table for Acme Paper Corporation:

INSERT INTO corporation (corp_id, name)

VALUES (27, 'Acme Paper Corporation');

This statement adds a row to the corporation table with a value of 27 for the corp_id column

and a value of Acme Paper Corporation for the name column.

Finally, here's a simple select statement to retrieve the data that was just created:

mysql< SELECT name

-> FROM corporation

-> WHERE corp_id = 27;

+------------------------+

| name |

+------------------------+

| Acme Paper Corporation |

+------------------------+

All database elements created via SQL schema statements are stored in a special set of tables

called the data dictionary. This "data about the database" is known collectively as metadata and

is explored in Chapter 15. Just like tables that you create yourself, data dictionary tables can be

queried via a select statement, thereby allowing you to discover the current data structures

deployed in the database at runtime. For example, if you are asked to write a report showing the

new accounts created last month, you could either hardcode the names of the columns in the

account table that were known to you when you wrote the report, or query the data dictionary

to determine the current set of columns and dynamically generate the report each time it is

executed.

Most of this book is concerned with the data portion of the SQL language, which consists of the

select, update, insert, and delete commands. SQL schema statements is demonstrated in

Chapter 2, where the sample database used throughout this book is generated. In general, SQL

schema statements do not require much discussion apart from their syntax, whereas SQL data

statements, while few in number, offer numerous opportunities for detailed study. Therefore,

while I try to introduce you to many of the SQL schema statements, most chapters in this book

concentrate on the SQL data statements.

1.2.2. SQL: A Nonprocedural Language

If you have worked with programming languages in the past, you are used to defining variables

and data structures, using conditional logic (i.e., if-then-else) and looping constructs (i.e., do

while ... end), and breaking your code into small, reusable pieces (i.e., objects, functions,

procedures). Your code is handed to a compiler, and the executable that results does exactly

(well, not always exactly) what you programmed it to do. Whether you work with Java, C#, C,

Visual Basic, or some other procedural language, you are in complete control of what the

program does.

NOTE

A procedural language defines both the desired results and the mechanism, or process,

by which the results are generated. Nonprocedural languages also define the desired

results, but the process by which the results are generated is left to an external agent.

With SQL, however, you will need to give up some of the control you are used to, because SQL

statements define the necessary inputs and outputs, but the manner in which a statement is

executed is left to a component of your database engine known as the optimizer. The optimizer's

job is to look at your SQL statements and, taking into account how your tables are configured

and what indexes are available, decide the most efficient execution path (well, not always the

most efficient). Most database engines will allow you to influence the optimizer's decisions by

specifying optimizer hints, such as suggesting that a particular index be used; most SQL users,

however, will never get to this level of sophistication and will leave such tweaking to their

database administrator or performance expert.

With SQL, therefore, you will not be able to write complete applications. Unless you are writing a

simple script to manipulate certain data, you will need to integrate SQL with your favorite

programming language. Some database vendors have done this for you, such as Oracle's PL/SQL

language, MySQL's stored procedure language, and Microsoft's Transact-SQL language. With

these languages, the SQL data statements are part of the language's grammar, allowing you to

seamlessly integrate database queries with procedural commands. If you are using a non-

database-specific language such as Java, however, you will need to use a toolkit/API to execute

SQL statements from your code. Some of these toolkits are provided by your database vendor,

whereas others are created by third-party vendors or by open source providers. Table 1-2 shows

some of the available options for integrating SQL into a specific language.

Table 1-2. SQL integration toolkits

Language

Toolkit

Java

JDBC (Java Database Connectivity; JavaSoft)

C++

Rogue Wave SourcePro DB (third-party tool to connect to Oracle, SQL Server,

MySQL, Informix, DB2, Sybase, and PostgreSQL databases)

C/C++

Pro*C (Oracle), MySQL C API (open source), and DB2 Call Level Interface (IBM)

ADO.NET (Microsoft)

Perl

Perl DBI

Python

Python DB

Visual

Basic

ADO.NET (Microsoft)

If you only need to execute SQL commands interactively, every database vendor provides at

least a simple command-line tool for submitting SQL commands to the database engine and

inspecting the results. Most vendors provide a graphical tool as well that includes one window

showing your SQL commands and another window showing the results from your SQL

commands. Since the examples in this book are executed against a MySQL database, I use the

mysql command-line tool that is included as part of the MySQL installation to run the examples

and format the results.

1.2.3. SQL Examples

Earlier in this chapter, I promised to show you an SQL statement that would return all the

transactions against George Blake's checking account. Without further ado, here it is:

SELECT t.txn_id, t.txn_type_cd, t.txn_date, t.amount

FROM individual i

INNER JOIN account a ON i.cust_id = a.cust_id

INNER JOIN product p ON p.product_cd = a.product_cd

INNER JOIN transaction t ON t.account_id = a.account_id

WHERE i.fname = 'George' AND i.lname = 'Blake'

AND p.name = 'checking account';

+--------+-------------+---------------------+--------+

+--------+-------------+---------------------+--------+

| 11 | DBT | 2008-01-05 00:00:00 | 100.00 |

+--------+-------------+---------------------+--------+

1 row in set (0.00 sec)

Without going into too much detail at this point, this query identifies the row in the individual

table for George Blake and the row in the product table for the "checking" product, finds the row

in the account table for this individual/product combination, and returns four columns from the

transaction table for all transactions posted to this account. If you happen to know that George

Blake's customer ID is 8 and that checking accounts are designated by the code 'CHK', then you

can simply find George Blake's checking account in the account table based on the customer ID

剩余329页未读，继续阅读

Timothy_Ai

粉丝: 0
资源: 6

Learning SQL第二版：数据库管理与查询指南

Learning SQL, 2nd Edition

self.learning_rate = tf.train.exponential_decay( self.initial_learning_rate, self.global_step, self.decay_steps, self.decay_rate, self.staircase, name='learning_rate')是什么意思

tff.learning.build_federated_averaging_process与tff.learning.build_weighted_fed_avg区别

self.a_train_op = tf.train.AdamOptimizer(self.learning_rate).minimize(-self.a_loss)转换为pytorch

module 'keras._tf_keras.keras.backend' has no attribute 'learning_phase'

self.a_train_op = tf.train.AdamOptimizer(self.learning_rate).minimize(-self.a_loss)用pytorch实现

def __init__(self, learning_rate=0.5, num_iterations=1000, regularization=None, reg_strength=0.01): self.learning_rate = learning_rate self.num_iterations = num_iterations self.regularization = regularization self.reg_strength = reg_strength self.weights = None self.bias = None的含义

epoch_learning_rate = FLAGS.learning_rate

基于Springboot的租房管理系统+源代码+演示视频.zip

最新资源

def init(self, learning_rate=0.5, num_iterations=1000, regularization=None, reg_strength=0.01): self.learning_rate = learning_rate self.num_iterations = num_iterations self.regularization = regularization self.reg_strength = reg_strength self.weights = None self.bias = None的含义