Transcript Insert

More SQL
Database Modification
Defining a Database Schema
Views
1/46
Database Modifications
 A modification command does not return a
result (as a query does), but changes the
database in some way.
 Three kinds of modifications:
1. Insert a tuple or tuples.
2. Delete a tuple or tuples.
3. Update the value(s) of an existing tuple or tuples.
2/46
Insertion
To insert a single tuple:
INSERT INTO <relation>
VALUES ( <list of values> );
Example: add Sydney Greenstreet to the list of
stars of The Maltese Falcon.
INSERT INTO StarsIn
VALUES(‘The Maltese Falcon’, 1942,
’Sydney GreenStreet’);
3/46
Specifying Attributes in INSERT
 We may add to the relation name a list of
attributes.
 Two reasons to do so:
1. We forget the standard order of attributes for the
relation.
2. We don’t have values for all attributes, and we
want the system to fill in missing components
with NULL or a default value.
4/46
Example: Specifying Attributes
Another way to add Sydney Greenstreet to the
list of stars of The Maltese Falcon.
INSERT INTO StarsIn(movieTitle, movieYear, starName)
VALUES(’The Maltese Falcon’, 1942, ’Sydney GreenStreet’);
5/46
Inserting Many Tuples
We may insert the entire result of a query
into a relation, using the form:
INSERT INTO <relation>
( <subquery> );
6/46
Example: Insert a Subquery
Using Studio and Movie, add to the relation
Studio all movie studios that are mentioned
in the relation Movie, but don’t appear in
Studio.
7/46
Solution
INSERT INTO Studio(name)
(SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT name
FROM Studio));
8/46
Deletion
To delete tuples satisfying a condition from
some relation:
DELETE FROM <relation>
WHERE <condition>;
9/46
Example: Deletion
Delete from relation StarsIn the fact that
Sydney GreenStreet was a star in The Maltese
Falcon:
DELETE FROM StarsIn
WHERE movieTitle = ‘The Maltese Falcon’ AND
movieYear = 1942 AND
starName = ‘Sydney Greenstreet’;
10/46
Example: Delete all Tuples
Make the relation Likes empty:
DELETE FROM Likes;
Note no WHERE clause needed.
11/46
Example: Delete Many Tuples
Delete from MovieExec all movie
executives whose net worth is low-less than
ten million dollars.
DELETE FROM MovieExec
WHERE netWorth < 10000000;
12/46
Updates
To change certain attributes in certain
tuples of a relation:
UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;
13/46
Example: Update
Modify the relation MovieExec by prepending the
title Pres. In front of every movie executives who
is the president of a studio:
UPDATE MovieExec
SET name = ‘Pres. ’ || name
WHERE cert# IN
(SELECT presC# FROM Studio);
14/46
Defining a Database Schema
A database schema comprises declarations
for the relations (“tables”) of the database.
Several other kinds of elements also may
appear in the database schema, including
views, indexes, and triggers, which we’ll
introduce later.
15/46
Creating (Declaring) a Relation
Simplest form is:
CREATE TABLE <name> (
<list of elements>
);
To delete a relation:
DROP TABLE <name>;
16/46
Elements of Table Declarations
Most basic element: an attribute and its type.
The most common types are:
 INT or INTEGER
 REAL or FLOAT
 CHAR(n )
• fixed-length string of n characters.
 VARCHAR(n )
• variable-length string of up to n characters.
17/46
Example: Create Table
CREATE TABLE MovieStar (
name
CHAR(30),
address VARCHAR(255),
gender
CHAR(1),
birthdate DATE
);
18/46
Dates and Times
DATE and TIME are types in SQL.
The form of a date value is:
DATE ’yyyy-mm-dd’
 Example: DATE ’2004-09-30’
19/46
Times as Values
The form of a time value is:
TIME ‘hh:mm:ss’
with an optional decimal point and fractions
of a second following.
 Example: TIME ‘15:30:02.5’
20/46
Modifying relation schemas
We can use ALTER to modify a relation
schema. We have several options, the most
important of which are:
 ADD followed by a column name and its data
type;
 DROP followed by a column name;
21/46
Adding Attributes
We may add a new attribute (“column”) to a
relation schema by:
ALTER TABLE <name> ADD
<attribute declaration>;
Example:
ALTER TABLE MovieStar ADD phone CHAR(16);
22/46
Deleting Attributes
Remove an attribute from a relation schema
by:
ALTER TABLE <name>
DROP <attribute>;
Example: we don’t really need the license
attribute for bars:
ALTER TABLE MovieStar DROP birthdate;
23/46
Default values
When we create or modify tuples, we sometimes
don’t have values for all components.
To address this problem, SQL provides the NULL
value.
However, there are times when we would prefer to
use default value, the value that is placed in a
component if no other value is known.
24/46
Example
We might wish to use the character ‘?’ as the
default for an unknown gender, and we might also
wish to use the earliest possible date, DATE
‘0000-00-00’ for an unknown birthdate.
CREATE TABLE MovieStar (
name
CHAR(30),
address VARCHAR(255),
gender CHAR(1) DEFAULT ‘?’,
birthdate DATE DEFAULT DATE ‘0000-00-00’
);
25/46
Indexes
An index on an attribute A of a relation is a
data structure that makes it efficient to find
those tuples that have a fixed value for
attribute A.
26/46
To create a index
Create an index on attribute year for the
relation Movie
CREATE INDEX YearIndex ON Movie(year);
From Movie, create an index on title and
year
CREATE INDEX KeyIndex ON Movie(title,
year);
27/46
To delete a index
If we wish to delete the index, we simply
use its name in a statement like:
DROP INDEX YearIndex;
Selection of indexes requires a trade-off by
the database designer
 The existence of an index on an attribute
greatly speeds up queries in which a value for
that attribute is specified.
 On the other hand, ervery index built for an
attribute of some relation makes insertions,
deletion, and updates to that relation more
complex and time-consuming.
28/46
Views
A view is a “virtual table” = a relation
defined in terms of the contents of other
tables and views.
Declare by:
CREATE VIEW <name> AS <query>;
Antonym: a relation whose value is really
stored in the database is called a base table.
29/46
Example: View Definition
To define a view that is a part of the Movie relation,
specifically, the titles and years of the movies made
by Paramount Studio:
CREATE VIEW ParamountMovie AS
SELECT title, year
FROM Movie
WHERE studioName = ‘Paramount’;
30/46
Example: Accessing a View
Query a view as if it were a base table.
 Also: a limited ability to modify views if it
makes sense as a modification of one
underlying base table.
Example query:
SELECT title
FROM ParamountMovie
WHERE year = 1979;
31/46
What Happens When a View Is Used?
The SQL system will translate the query on the
view ParamountMovie into a query about the base
table Movie that has the same effect as our
original query.
SELECT title
FROM Movie
WHERE studioName = ‘Paramount’ AND year = 1979;
32/46
Define a query based on views and
base tables
Example:
SELECT DISTINCT starName
FROM ParamountMovie, StarsIn
WHERE title = movieTitle AND year = movieYear;
33/46
Renaming attributes
We can give a view’s attributes names of our
own choosing. For example:
CREATE VIEW MovieProd(movieTitle, prodName)
AS
SELECT title, name
FROM Movie, MovieExec
WHERE producerC# = cert#;
34/46
Delete a view
If a view becomes unuseful, we can delete
it. For instance:
DROP VIEW ParamountMovie;
35/46
视图的作用
 简化用户操作
 数据看起来简单、清晰。
 用户可以从多角度看待同一数据
 灵活共享数据库。
 对数据库重构提供了一定程度的逻辑独立性
 当数据库重构造(如增加表，或增加字段)时，用户和用户程序
不会受影响。只是由于视图的更新是有条件的，因此视图只
能在一定程度上提供数据的逻辑独立性。
 能够对机密数据提供安全保护
 对不同的用户定义不同的视图，实现重要数据的隐藏。
36/46
练习
职工-工资数据库
职工（工号，姓名，职务，部门）
工资表（工号，月份，基本工资，岗位津贴，交通补贴）
 建立职工工号和每月基本工资的视图
 建立每个职工的姓名和他当月总收入的视图
 建立本月总收入低于1000元的职工的视图
37/46
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.
38/46
Two important rules
When we operate on a NULL and any other value,
including another NULL, using an arithmetic
operator like × or +, the result is NULL.
When we compare a NULL value and any value,
including another NULL, using a comparison
operator like = or >, the result is UNKNOWN.
The value UNKNOWN is another truth-value, like
TRUE and FALSE.
39/46
To ask if x has the value NULL
x IS NULL, this expression have the value
TRUE if x has the value NULL and it has
FALSE otherwise.
x IS NOT NULL, this expression have the
value FALSE if x has the value NULL and
it has TRUE otherwise
40/46
Comparing NULL’s to Values
The logic of conditions in SQL is really 3valued logic: TRUE, FALSE, 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).
41/46
Join Expressions
SQL provides several versions of joins.
These expressions can be stand-alone
queries or used in place of relations in a
FROM clause.
42/46
Products and Natural Joins
Natural join:
R NATURAL JOIN S;
Product:
R CROSS JOIN S;
Example:
Likes NATURAL JOIN Serves;
Relations can be parenthesized subqueries, as well.
43/46
Theta Join
R JOIN S ON <condition>
Example: using Movie and StarIn:
Movie JOIN StarIn ON
title = movieTitle AND year = movieyear;
44/46
Outerjoins
 R OUTER JOIN S is the core of an outerjoin
expression. It is modified by:
1. Optional NATURAL in front of OUTER.
2. Optional ON <condition> after JOIN.
3. Optional LEFT, RIGHT, or FULL before OUTER.
 LEFT = pad dangling tuples of R only.
 RIGHT = pad dangling tuples of S only.
 FULL = pad both; this choice is the default.
45/46
Example
MovieStar NATURAL FULL OUTER
JOIN MovieExec;
 Where FULL can be replaced by LEFT or
RIGHT
Movie FULL OUTER JOIN StarIn ON title
= movieTitle AND year = movieYear;
 Where FULL can be replaced by LEFT or
RIGHT
46/46