数据库系统概论05其他关系语言.ppt

* * * * * * Modification of the Database – Insertion Provide as a gift for all loan customers of the Perryridge branch, a new $200 savings account for every loan account they have, with the loan number serving as the account number for the new savings account. Modification of the Database – Updates Use the U. operator to change a value in a tuple without changing all values in the tuple. QBE does not allow users to update the primary key fields. Update the asset value of the Perryridge branch to $10,000,000. Increase all balances by 5 percent. An Example Query in Microsoft Access QBE Example query: Find the customer_name, account_number and balance for all accounts at the Perryridge branch An Aggregation Query in Access QBE Find the name, street and city of all customers who have more than one account at the bank Aggregation in Access QBE The row labeled Total specifies which attributes are group by attributes which attributes are to be aggregated upon (and the aggregate function). For attributes that are neither group by nor aggregated, we can still specify conditions by selecting where in the Total row and listing the conditions below As in SQL, if group by is used, only group by attributes and aggregate results can be output * * * * * * * * * * * * * * * * * * * * * * * * * * * 5.3 QBE QBE( Query-by-Example，基于例子的查询)既指一种数据操纵语言，也指一种早期的包含这种语言的数据库系统。 QBE数据操纵语言有两个显著的特点： 1．和大多数查询语言和编程语言不同，QBE是二维文法：查询看起来像表格。一维查询语言(如SQL)的一个查询语句可以在一行写完，而二维查询语言则需要两维才能表达一个查询。 2．QBE的查询是用“例子”表达的。用户不是写一个过程获得所需答案，而是举出所需答案的一个例子。系统会把这个例子一般化并计算出查询的答案。 5.3 QBE QBE有两种形式：原始的基于文本的版本和后来开发的图形版本，其中图形版本为Microsoft Access数据库系统所支持。 基于文本的QBE的特点，它相当于SQL中没有聚集和更新的select- from- where子句。 5.3.1 框架表 在QBE中用框架表表达查询 这些表表明了关系模式，如图5-2所示。 用户不是把所有的框架表都显示出来使屏幕混乱不堪，而是根据所给查询选择所需的框架表，并在其中填入示例行( example row)。示例行可以包含常量和示例元素（即域变量）。为了避免混淆，在QBE中，域变量前加下划线，而常量无需任何标志。 QBE Skeleton Tables for the Bank Example QBE Skeleton Tables 5.3.2 在单个关系上的查询 Find all loan numbers at the Perryridge branch