04-4 4.4 Structured Query Language.pptx

第1页：4.4 生词表

第2页：4.4 生词表（续）

第3页：4.4 生词表（续）

第4页：4.4 缩略语表

第5页：2所示的数据库表

考虑图4.2所示的数据库表，该表包含有关影片的信息。表中的每一行对应一个记录，表中的每个记录由相同的存储特定值的字段组成。也就是说，每个影片记录有影片标识、名称、流派、分级，这些字段包含每个记录的特定数据。每个数据库表应该有一个名字，这样将该表命名为Movie。

Consider the database table shown in Figure 4.2, which contains information about movies. Eachrowinthe table corresponds to a record. Eachrecordin the table is made up of the same fields in which particular values a restored. Thatis, each movie record has a Movie Id, a Title, a Genreanda Rating that contain the specific data for each record. A database table is given a name, such as Movieinthis case. 4.4 Structured Query Language

第6页：假设我们想建立一个影片租赁业务

假设我们想建立一个影片租赁业务。除了要出租的影片列表之外，我们还必须建立一个数据库表保存我们的客户信息。图4.3中的Customer表可以表示这方面的资料。

我们可以使用SQL对这些表格进行选择性数据检索、更新和改变数据、删除数据等。

Supposewe wanted to create a movie rental business. In addition to the list of movies for rent, we must create a database table to hold information about our customers. The Customer table in Figure 4.3 could represent this information.

Wecanuse SQLto retrieve selective data, update and change data, deleted ataetc. from these tables.

第7页：结构化查询语言（SQL）

语言点分析：复合句。句中非限制性定语从句asitsnameimplies作插入语，as指代整个主句内容。

结构化查询语言（SQL）是一个用于管理关系数据库的全面的数据库语言。它包括描述数据库方案的语句以及添加、修改和删除数据库内容的语句。顾名思义，它还包括能够查询数据库来检索特定数据的功能。

The Structured Query Language (SQL)is a comprehensive database language for managing relational databases. It includes statements that specify database schemas as well as statements that add, modify, and delete database content.[1]It also includes, as its name implies, the ability to query the database to retrieve specific data.

第8页：SQL的最初版本是Sequal

SQL的最初版本是Sequal，20世纪70年代初由IBM开发。1986年，美国国家标准学会（ANSI）公布SQL标准，SQL标准是访问关系数据库的商业数据库语言的基础。

SQL是不区分大小写的，因此，关键字、表名和属性名称可以大写、小写或大小写混合。空格被用作为语句中的分隔符。

The original version of SQLwas Sequal, developed by IBMinthe early 1970 s. In 1986, the American National Standards Institute (ANSI)published the SQLstandard, the basis for commercial database languages for accessing relational databases.

SQLisnotcase sensitive, so keywords, table names, and attribute names can be uppercase, lowercase, or mixed. Spacesare used as separators in a statement.

第9页：让我们首先关注简单查询

查询

让我们首先关注简单查询。select语句是实现这一目的的主要手段。基本的select语句包含一个select子句、一个from子句和一个where子句：

Queries

Let’s first focus on simple queries. The select statement is the primary tool for this purpose. The basic select statement includes a select clause, a from clause, and a where clause:

第10页：select属性列表from表名列表

select属性列表from表名列表where条件

select子句确定返回的属性，from子句确定查询用到哪些表，where子句限制返回的数据。例如：

select attribute-list from table-list where condition

The select clause determines what attributes are returned. The from clause determines what tables are used in the query. The where clause restricts the data that is returned. Forexample:

第11页：这个查询的结果

select Title from Movie where Rating=‘PG’

这个查询的结果是Movie数据表中的分级是PG的所有名称的列表。如果没有必需的特殊限制，where子句可以省略：

select Title from Movie where Rating=‘PG’

The result of this query is a list of all titles from the Movietable that have a rating of PG. The where clause can be eliminated if no special restrictions are necessary:

第12页：此查询返回客户表中所有客户的名称和地址

select Name, Address from Customer

此查询返回客户表中所有客户的名称和地址。星号（*）可用于select子句中表示需要返回选中记录的所有属性：

select Name, Address from Customer

Thisquery returns the name and address of all customers in the Customer table. Anasterisk (*)can be used in the select clause to denote that all attributes in the selected records should be returned:

第13页：此查询将返回Movie表中的Genre属

select*from Movie where Genrelike‘%action%’

此查询将返回Movie表中的Genre属性包含单词action的记录的所有属性。SQL的like操作符对字符串执行一些简单的模式匹配，％符号匹配任何字符串。

select*from Movie where Genrelike‘%action%’

Thisquery returns all attributes of records from the Movietablein which the Genre attribute contains the word‘action’. The like operator in SQLperforms some simple pattern matching on strings, andthe%symbol matches any string.

第14页：使用orderby子句

使用orderby子句，select语句也可以决定查询的结果应该如何排序：

select*from Movie where Rating=’R’order by Title

此查询将返回按影片名排序的R级电影的所有属性。

Select statements can also dictate how the results of the query should be sorted using the order by clause:

select*from Movie where Rating=‘R’order by Title

Thisquery returns all attributes of R-rated movies sorted by the movie title.

第15页：相对于已经列出来的，SQL支持的

相对于已经列出来的，SQL支持的select语句还有更多的变化形式。请记住，我们的目标是给你介绍数据库的概念。为了真正精通SQL查询，您将需要更多的细节。

There are many more variations on select statements supported by SQLthanthose we’veshown here. Remember that our goal is to introduce the database concepts to you. You would require much more detail to truly become proficient at SQLqueries.

第16页：SQL的insert

修改数据库内容

SQL的insert、update和delete语句允许改变数据表中的数据。insert语句向表中增加一个新的记录。每个insert语句指定新纪录的属性的值。例如：

Modifying Database Content

The insert, update, and delete statements in SQLallowthe data in a table to be changed. The insert statement adds a new record to a table. Eachinsert statement specifies the values of the attributes for the new record. Forexample:

第17页：该语句插入一个具有指定属性的新记录到

insert into Customer values (9876,‘John Smith’,‘602 Greenbriar Court’,‘2938321234020299’)

该语句插入一个具有指定属性的新记录到Customer表。

insert into Customer values (9876,‘John Smith’,‘602 Greenbriar Court’,‘2938321234020299’)

This statement inserts a new record into the Customer table with the specified attributes.

第18页：update语句改变表的一个或多个记录的

update语句改变表的一个或多个记录的值。例如：

update Movieset Genre=‘thriller drama’where title=‘Unbreakable’

该语句将Movie表中的Title（名称）为'Unbreakable'（《坚不可摧》）的Genre（流派）属性改为‘thrillerdrama’（惊险剧）

The update statement changes the values in one or more records of a table. Forexample:

update Movieset Genre=‘thriller drama’where title=‘Unbreakable’

This statement changes the Genreofthe movie Unbreakable to‘thriller drama’.

第19页：句中aswith

The delete statement removes all records from a table matching the specified condition. Forexample, if we wanted to remove all R-rated movies from the Movietable, we could use the following delete statement:

delete from Movie where Rating=‘R’

[2]As with the select statement,there are many variations of the insert,update,and delete statements as well.分析：简单句。句中aswith:与……一样，例如：Oneday,as with all tyrannies,Myanmar's will fall.终有一天，就像所有的专制政权一样，缅甸军人政权会崩溃。

Delete语句删除数据库中所有符合特定条件的记录。例如，如果想从Movie表中删除所有R级影片，可以使用下面delete语句：

delete from Movie where Rating=‘R’

与select语句一样，insert、update和delete语句也有许多不同形式。

第20页：4.4 结构化查询语言 课文要点

Exercises

Ⅰ. Write true or false for the following statements according to the passage.

1. ( ) The Structured Query Language (SQL) is a comprehensive database language for managing relational databases.

2. ( )The SQL includes statements that add, modify, and delete database content.

3. ( ) The original version of SQL was Sequal, developed by IBM in 1986.

4. ( ) SQL is case sensitive, so keywords, table names, and attribute names must be lowercase.

5. ( ) The basic select statement includes a select clause, a from clause, and a where clause.

6. ( ) The where clause determines what tables are used in the query.

7. ( ) The from clause can be eliminated if no special restrictions are necessary.

8. ( ) The like operator in SQL performs some simple pattern matching on strings.

第21页：习题4.4 二 填空

习题4.4 二 填空：根据课文内容在横线处填入恰当的词。

Fill in the blanks according to the passage..

1. The Structured Query Language (SQL) is a comprehensive database language for ____________________________________.
2. In 1986, the ________________________________ (ANSI)published the SQL standard.
3. The ___________________is the primary tool for query.
4. The ___________________ clause determines what attributes are returned.
5. The ______________clause determines what tables are used in the query.
6. The ______________clause restricts the data that is returned.
7. Select statements can also dictate how the results of the query should be sorted using the ________________ clause
8. The __________ statement adds a new record to a table.
9. The update statement changes the ____________ in one or more records of a table.
10. The _____ statement removes all records from a table matching the specified condition.

第22页：习题4.4 三 翻译

习题4.4 三 翻译：将下列单词和短语译成中文。

Translate the following words and expressions into Chinese..

1. Structured Query Language
2. relational database
3. American National Standards Institute
4. case sensitive
5. clause
6. variation
7. update
8. specify
9. restrict
10. query