Data Management Technology(3) -- SQL

SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++.

What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.

Core of SQL

(1) DDL(Data Definition Language), 数据定义语言, used to define database schema

(2) Query Language 数据查询语言，fetch data from database according to user requirements

(3) DML(Data Manipulation language), 数据操纵语言，modify database instance, i.e., insert, update or delete data

(4) DCL(Data Control language)，数据控制语言, includes database constraints, user authentication, etc.

1.Data Definition Language (DDL)

Defining a Database Schema

A database schema comprises declarations for the relations (“tables”) of the database.

Many other kinds of elements may also appear in the database schema, including views, indexes, and triggers, which we’ll introduce later.

Declaring a Relation

Simplest form is:

CREATE TABLE (

);

And you may remove a relation from the database schema by:

DROP TABLE ;

Elements of Table Declarations

The principal element is a pair consisting of an attribute and a type.

The most common types are:

INT or INTEGER (synonyms).

REAL or FLOAT (synonyms).

CHAR(n ) = fixed-length string of n characters.

VARCHAR(n ) = variable-length string of up to n characters.

DATE and TIME are types in SQL.

The form of a date value is:

DATE ‘yyyy-mm-dd’

The form of a time value is:

TIME ‘hh:mm:ss’

with an optional decimal point and fractions of a second following.

Declaring Keys

An attribute or list of attributes may be declared PRIMARY KEY or UNIQUE.

These attribute(s) functionally determine all the attributes of the relation schema.

There are a few distinctions to be mentioned later.

Declaring Single-Attribute Keys

Place PRIMARY KEY or UNIQUE after the type in the declaration of the attribute.

Example:

CREATE TABLE Beers (

name CHAR(20) UNIQUE,

manf CHAR(20)

);

Declaring Multiattribute Keys

A key declaration can also be another element in the list of elements of a CREATE TABLE statement.

This form is essential if the key consists of more than one attribute.

May be used even for one-attribute keys.

Example:

CREATE TABLE Sells (

bar CHAR(20),

beer VARCHAR(20),

price REAL,

PRIMARY KEY (bar, beer)

);

PRIMARY KEY Versus UNIQUE

There exist these distinctions:

1.There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.

2.No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.

Other Declarations for Attributes

Two other declarations we can make for an attribute are:

1.NOT NULL means that the value for this attribute may never be NULL.

2.DEFAULT says that if there is no specific value known for this attribute’s component in some tuple, use the stated .

Example:

CREATE TABLE Drinkers (

name CHAR(30) PRIMARY KEY,

addr CHAR(50)

DEFAULT ‘123 Sesame St.’,

phone CHAR(16)

);

Adding Attributes

We may change a relation schema by adding a new attribute (“column”) by:

ALTER TABLE ADD ;

Deleting Attributes

Remove an attribute from a relation schema by:

ALTER TABLE DROP ;

2.Queries (Query Language)

Select-From-Where Statements

The principal form of a query is:

SELECT desired attributes

FROM one or more tables

WHERE condition about tuples of the tables

SELECT [ALL|DISTINCT] <目标列表达式>

​ [，<目标列表达式>] …

FROM <表名或视图名>[， <表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ]；

Single-relation queries

Begin with the relation in the FROM clause.

Apply the selection indicated by the WHERE clause.

Apply the extended projection indicated by the SELECT clause.

Operational Semantics

• To implement this algorithm think of a tuple variable ranging over each tuple of the relation mentioned in FROM.
• Check if the “current” tuple satisfies the WHERE clause.
• If so, compute the attributes or expressions of the SELECT clause using the components of this tuple.

* In SELECT clauses

• When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.”

Renaming Attributes

• If you want the result to have different attribute names, use “AS ” to rename an attribute.

Handle Duplicates

• SQL allows duplicates in relations as well as in query results.

• To force the elimination of duplicates, insert the keyword distinct after select**.**

• Find the names of all branches in the loan relations, and remove duplicates

  ​ SELECT DISTINCT branch_name FROM loan

• The keyword all specifies that duplicates not be removed.
  SELECT ALL branch_name FROM loan

Expressions in SELECT Clauses

• Any expression that makes sense can appear as an element of a SELECT clause.

  SELECT bar, beer, price * 6 AS priceInYuan FROM Sells;

• expressions/constants/functions/Attribute alias/…

  constants: SELECT drinker, ‘likes Bud’ AS whoLikesBud FROM Likes WHERE beer = ‘Bud’;

  functions: SELECT Sname，2006-Sage AS 'Year of Birth: ’ ，LOWER(Sdept) FROM Student；

  expressions: SELECT Sname AS NAME，'Year of Birth: ’ AS BIRTH， 2006-Sage AS BIRTHDAY，LOWER(Sdept) AS DEPARTMENT FROM Student；

Complex Conditions in WHERE Clause

• attribute names of the relation(s) used in the FROM.

• comparison operators: =, <>, <, >, <=, >=, between, in

• apply arithmetic operations: stockprice*2

• operations on strings (e.g., “||” for concatenation).

• Lexicographic order on strings.

• Pattern matching: s LIKE p

  • WHERE clauses can have conditions in which a string is compared with a pattern, to see if it matches.

  • General form:

    LIKE

    NOT LIKE

  • Pattern is a quoted string with % = “any string”; _ = “any character.”

  • ESCAPE character

    When the string contains ‘%’ or ‘_’, you need to use ESCAPE character‘\’

• Special stuff for comparing dates and times.

• Range comparison: between

  • BETWEEN … AND …
  • NOT BETWEEN … AND …

• Set operator: in

  • IN <值表>, NOT IN <值表>

    <值表>：用逗号分隔的一组取值

    SELECT Sname，Ssex FROM Student WHERE Sdept IN ( ‘IS’，‘MA’，‘CS’ );

• Important Points

  • Two single quotes inside a string represent the single-quote (apostrophe).
  • Conditions in the WHERE clause can use AND, OR, NOT, and parentheses in the usual way boolean conditions are built.
  • SQL is case-insensitive. In general, upper and lower case characters are the same, except inside quoted strings.

Ordering the Display of Tuples

• Use ‘Order by’ clause to specify the alphabetic order of the query result
  • SELECT DISTINCT customer_name FROM borrower ORDER BY customer_name
• We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default.
• Note: Order by can only be used as the last part of select statement

Null Values

• Tuples in SQL relations can have NULL as a value for one or more components.
• Meaning depends on context. Two common cases:
  • Missing value : e.g., we know Joe’s Bar has some address, but we don’t know what it is.
  • Inapplicable : e.g., the value of attribute spouse for an unmarried person.
• Comparing NULL’s to Values
  • The logic of conditions in SQL is really 3-valued logic: TRUE, FALSE, UNKNOWN.
  • When any value is compared with NULL, the truth value is UNKNOWN.
  • But a query only produces a tuple in the answer if its truth value for the WHERE clause is TRUE (not FALSE or UNKNOWN).
• Three-Valued Logic
  • To understand how AND, OR, and NOT work in 3-valued logic, think of TRUE = 1, FALSE = 0, and UNKNOWN = ½.
  • AND = MIN; OR = MAX, NOT(x) = 1-x.

Aggregations

• SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.

• Also, COUNT(*) counts the number of tuples.

• 计数

  • COUNT（[DISTINCT|ALL] *）
  • COUNT（[DISTINCT|ALL] <列名>）

• 计算总和

  • SUM（[DISTINCT|ALL] <列名>）

• 计算平均值

  • AVG（[DISTINCT|ALL] <列名>）

• 求最大值

  • MAX（[DISTINCT|ALL] <列名>）

• 求最小值

  • MIN（[DISTINCT|ALL] <列名>）

• DISTINCT短语：在计算时要取消指定列中的重复值

  • DISTINCT inside an aggregation causes duplicates to be eliminated before the aggregation.

• ALL短语：不取消重复值

• ALL为缺省值

• NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column.

  But if there are no non-NULL values in a column, then the result of the aggregation is NULL.

• Grouping

  • We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of attributes.
  • The relation that results from the SELECT-FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group.
  • SELECT beer, AVG(price) FROM Sells GROUP BY beer;
  • Usually, we want aggregations on certain parts of the relation.

• If any aggregation is used, then each element of the SELECT list must be either:

  1.Aggregated, or

  2.An attribute on the GROUP BY list.

HAVING Clauses

• HAVING may follow a GROUP BY clause.
• If so, the condition applies to each group, and groups not satisfying the condition are eliminated.
• find the average price of a bar which sells more than 20 beers
  • Select avg(price) from sells group by bar having count(distinct beer) >20

Extra Materials on Aggregation & Grouping

• SQL supports several aggregation operations: SUM, MIN, MAX, AVG, COUNT
• Except COUNT, all aggregations apply to a single attribute:SELECT Count(*) FROM

不要忘写COUNT (SELECT只能是键)

Multi-relation queries

Multirelation Queries

• Interesting queries often combine data from more than one relation.
• We can address several relations in one query by listing them all in the FROM clause.
• Distinguish attributes of the same name by “.”

Formal Semantics

Almost the same as for single-relation queries:

1.Start with the product of all the relations in the FROM clause.

2.Apply the selection condition from the WHERE clause.