Schema Definition Language
Update Operations on Views
◇ Advantages
Reduced Training Costs
Application Portability
Application Longevity
Cross-system Communication
Customer Choice
A standard can Stifle Creativity
◇ Problems
Not Quite Relational
Some Deficiencies
incomplete in table definition management lack of implementation standard <!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw standard.htm, Thu Jun 14 08:06:50 CST 2001
◇ Reference Book
C. J. Date, "A Guide to The SQL Standard", 2nd Ed., Addison-Wesley, 1989
00 Invoke SQL
Data manipulation functions can be invoked either from interactively or from an application program
Data Definition functions can only be invoked interactively
礶
00 Data Manipulation Language
◇ SELECT, INSERT, UPDATE, DELETE
INSERT
INTO SP (SNO, PNO, QTY)
VALUES ( 'S5', 'P1', 100)
UPDATE S
SET STATUS = 2 * STATUS
WHERE S.CITY = 'LONDON'
DELETE
FROM P
WHERE P.WEIGHT > 15
◇ Options
DISTINCT, ALL
ORDER BY (DESC, ASC)
◇ Comments:
single-row SELECT only (singleton SELECT)
need to use CURSOR for multiple-row SELECT UPDATE and DELETE can work on multiple-rows possible confusion on the word "update" used in generic meaning, and the "UPDATE" operation in SQL. "update" may means INSERT, UPDATE, and DELETE.
礶
00 Aggregate Functions
◇ COUNT, SUM, AVG, MAX, MIN, COUNT(*).
00 CURSOR Operation
A cursor is a SQL object that is associated with a specific SELECT operation. ◇ OPEN
open the cursor, which causes the SELECT to be executed ◇ FETCH
fetch rows repeatly on the opened cursor ◇ CLOSE
Cursor Operation Details 礶
00 Views
CREATE VIEW LS (SNO, SNAME, STATUS)
AS SELECT S.SNO, S.SNAME, S.STATUS
FROM S
WHERE S.CITY = 'London'
View definition must not contain subqueries
Full SELECTION operation
Restricted update operations
applicable only if the target is a simple row-and-column subset of a single underlying base table. Detailed View Description 礶
00 Data Control
◇ Security
◇ Integrity
◇ Transaction Processing
Detailed Data Control Description 礶
00 ANSI-SQL Keywords
ANSI-SQL Keywords Details
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw overview.htm, Thu Jun 14 08:06:52 CST 2001
Schema Definition Language
◇ Define a database
CREATE SCHEMA
AUTHORIZATION user
schema-element-list
◇ Schema Elements
tables views grand-operations
Comments: "SCHEMA" is confusion, may be better to use "DATABASE"
◇ Data Types
CHARACTER NUMERIC INTEGER SMALLINT FLOAT DOUBLE REAL DECIMAL
Comments: Need other data types:
DATE TIMES MONEY VALUES BINARIES BIT STRINGS BOOLEAN VALUES etc.
◇ Constraints .h2
Primary Key Foreign Key Candidate Key UNIQUE (column-commalist)
each column must be defined as "NOT NULL" Check Condition
CREATE TABLE S
( SNO ....
STATUS ....
CHECK ( S.STATUS BETWEEN 10 and 100 ))
^
|---------------(search-condition)
Check conditions is a "restricted predicate"
a condition that can be evaluated for a given row ◇ Privileges
CREATE, SELECT, INSERT, UPDATE, DELETE
no special privilege is needed to create a schema
to create a base table, no special privilege is needed
(unless it references another table) to create a view V, the SELECT privilege is needed on every table mentioned in the definition of V
for other operations, corresponding privilege is needed
need SELECT privilege on all tables mentioned in WHERE clauses
◇ Grant
GRANT privileges ON table TO grantee-commalist
Privilege can be either "ALL" or a commalist of specific operations. grantee is either an authorization identifier or "PUBLIC" ◇ USER
a system variable specifying the user's identifier <!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw schema.htm, Thu Jun 14 08:06:54 CST 2001
◇ Can Define Base Tables and Views
CREATE TABLE
CREATE VIEW
Comment: only avbiable to DBA in interactive mode ◇ AUTHORIZATION name
◇ Examples
CREATE SCHEMA AUTHORIZATION TED
CREATE TABLE S (
SNO CHAR(5), NOT NULL,
SNAME CHAR(20),
STATUS DECIMAL(3),
CITY CHAR(15),
PRIMARY KEY (SNO))
CREATE TABLE P (
PNO CHAR(6), NOT NULL,
PNAME CHAR(20),
COLOR CHAR(6),
WEIGHT DECIMAL(3),
CITY CHAR(15),
PRIMARY KEY (PNO))
CREATE TABLE SP (
SNO CHAR(5), NOT NULL,
PNO CHAR(6), NOT NULL,
QTY DECIMAL(3),
PRIMARY KEY ( SNO, PNO ))
FOREIGN KEY ( SNO ) REFERENCE S,
FOREIGN KEY ( PNO ) REFERENCE P )
CREATE VIEW LS (
SNO, SNAME, STATUS)
AS SELECT S.SNO, S.SNAME, S.STATUS
FROM S
WHERE S.CITY = 'London'
Relation: S (Supplier) SNO SNAME STATUS CITY char(5) char(20) decimal(3) char(15) NOT NULL primary key
Relation: P (Parts) PNO PNAME COLOR WEIGHT CITY char(6) char(20) char(6) decimal(3) char(15) NOT NULL primary key
Relation: SP (Shipment) SNO PNO QTY char(5) char(6) decimal(3) primary Key foreign key(S) foreign key(P)
<!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw datadef.htm, Thu Jun 14 08:06:56 CST 2001
◇ Primary Key
Semantically identical to
each table can have at most one primary key
primary key receives special treatment in any corresponding foreign key definitions
Comments
primary key is optional for compatibility, but is strongly recommended ◇ Foreign Keys
FOREIGN KEY (ColumnList)
REFERENCES BaseTable [(ColumnList)]
a column (or combination of columns) in one base table T2 whose values are required to match values of the primary key (or cadidate key: in ANSI standard) in some base table T1 Comment: better to reference to primary keys only T1 : referenced table, T2 : referencing table A foreign key and its matching candidate key must have the same number of columns and the data types of corresponding columns must be identical T1 and T2 can be the same table null values are allowed in a foreign key
a table definition is invalid if any referenced table does not exist
(permit the definition, but not usable) DBMS will reject any operation that violates any referential constraint
Comment: it is better to provide cascading delete, instead of reject it. need REFERENCE privilege on every component columns of the matching candidate key.
Comment: not clear how cycle referential integrity is treated <!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw pkey.htm, Thu Jun 14 08:06:58 CST 2001
◇ Cursor Operations
OPEN FETCH CLOSE UPDATE .. CURRENT (positioned UPDATE) DELETE .. CURRENT (positioned DELETE)
◇ NonCursor Operations
SELECT INSERT UPDATE (searched UPDATE) DELETE (searched DELETE)
◇ Transaction termination operations
COMMIT WORK ROLLBACK WORK
<!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw manipu.htm, Thu Jun 14 08:07:00 CST 2001
OPEN
FETCH
UPDATE .. CURRENT (positioned UPDATE)
DELETE .. CURRENT (positioned DELETE)
CLOSE
.fi
a pointer that can be used to run thru an ordered collection of rows, pointing to each of the rows in that collection in turn ◇ Cursor Definition
SYNTAX EXAMPLE DECLARE cursor CURSOR DECLARE X CURSOR FOR query-expression FOR SELECT SP.SNO, SP.QTY FROM SP WHERE SP.PNO = PNO_PARAM [order-by-clause] ORDER BY SP.SNO
define a set of row for cursor to access
conditions for update:
if it is a view, must be an updatable view cursor definition does not include ORDER BY clause will be executed when X is OPENed
parameter PNO_PARAM must be specified parameter values are fixed at the OPEN time ORDER BY clause
ORDER BY ordering-spec-list [ASC|DESC]
column name can be replaced by column position ◇ OPEN/CLOSE
◇ Positions
on \fRa row\fP,\h'1'before \fRa row\fP,\h'1'after \fRthe last row
OPEN put the cursor before the first row ◇ FETCH (\s-2 fetch next\s0)
SYNTAX EXAMPLE FETCH cursor FETCH X INTO target-list INTO SNO_P, QTY_P INDICATOR QTY_I
will put the cursor on next row or after the last row ◇ UPDATE (positioned)
SYNTAX EXAMPLE UPDATE table UPDATE SP SET assignment-commalist SET QTY=SP.QTY+INCR_PARAM WHERE CURRENT OF cursor WHERE CURRENT OF X
Cursor X must be currently open and be positioned on a row row (table) must be updatable must not reference to any of the aggregate builtin function Comments: \s-4 don't know what happen if the update operation changed the position of the updated row w.r.t. to order ◇ DELETE (positioned)
SYNTAX EXAMPLE DELETE DELETE FROM table FROM SP WHERE CURRENT OF cursor WHERE CURRENT OF X
Cursor X will be positioned before next row or after the last row if no next row <!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw cursor.htm, Thu Jun 14 08:07:01 CST 2001
◇ SELECT
Singleton SELECT, not a full SELECT
retrieve at most one row
it's an error if multiple rows are selected SYNTAX EXAMPLE SELECT [ALL|DISTINCT] selection SELECT P.WEIGHT, P.COLOR INTO target-list WEIGHT_PARAM, COLOR_PARAM table-expression FROM P WHERE P.PNO = 'P4'
"selection" can be either a commalist of scalar expression or a "*", but not both.
it can be both in DB2
at most one DISTINCT can appear in SELECT such as:
illegal: SELECT SUM(DISTINCT QTY), AVG(DISTINCT QTY) ◇ INSERT
SYNTAX EXAMPLE INSERT INSERT INTO table [(column-list)] INTO TEMP (SNO, CITY) source SELECT S.NO, S.CITY FROM S WHERE S.STATUS > STATUS_PARAM
"source" can be a query or a VALUE caluse
VALUES clause must not be an operational expression such as X+1 ◇ SEARCHED UPDATE
SYNTAX EXAMPLE UPDATE table UPDATE table SET assignment-commalist SET STATUS = 2 * STATUS WHERE search-condition WHERE S.CITY = CITY_PARAM
◇ SEARCHED DELETE
SYNTAX EXAMPLE DELETE DELETE FROM table FROM P [WHERE search-condition] WHERE P.WEIGHT > WEIGHT_PARAM
◇ Limitation of INSERT, UPDATE, DELETE
If use a query specification, the target table must not be referred in any FROM clause, such as:
INSERT
INTO T
SELECT *
FROM T
<!-- This document is transformed using htxp.pl -->
瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw noncursr.htm, Thu Jun 14 08:07:03 CST 2001
◇ View Definition
Syntax
CREATE VIEW [(column-commalist)]
AS query-specification
[WITH CHECK OPTION]
.Code
Example
CREATE VIEW GOODSUPPS (SNO, STATUS, CITY)
AS SELECT S.SNO, S.STATUS, S.CITY
FROM S
WHERE S.STATUS > 15
.Code
cannot include any parameter since it is at schema level ◇ View Processing
ORIGINAL QUERY TRANSLATED QUERY SELECT * SELECT S.SNO, S.STATUS, S.CITY FROM GOODSUPPS FROM S WHERE CITY <> 'London' WHERE S.STATUS > 15 AND S.CITY <> 'London'
no specification on how view should be processed
materialization of view is not specified
Retrieval Operations on Views
◇ almost no restriction (except some surprises)
◇ Anomalies
grouped views, DISTINCT, derived columns
A FROM clause that references a grouped view cannot have an associated WHERE clause, GROUP BY clause, or HAVING clause.
Example
CREATE VIEW PQ ( PNO, TOTQTY )
AS SELECT SP.PNO, SUM (SP.QTY)
FROM SP
GROUP BY SP.SNO
.Code
ORIGINAL QUERY TRANSLATED QUERY SELECT PQ.PNO SELECT SP.PNO FROM PQ FROM SP WHERE TOTQTY > 5 WHERE SUM (SP.QTY) > 5 GROUP BY SP.PNO Correct Translation SELECT SP.PNO FROM SP GROUP BY SP.PNO HAVING SUM ( SP.QTY ) > 5
ORIGINAL QUERY TRANSLATED QUERY SELECT AVE(PQ.TOTQTY) SELECT AVE(SUM(SP.QTY)) FROM PQ FROM SP GROUP BY SP.PNO
Rule of Thumb:
The translated query must be a valid SQL SELECT-expression 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw view.htm, Thu Jun 14 08:07:05 CST 2001
Update Operations on Views
◇ Limited Update
CREATE VIEW CITYPAIRS (SCITY, PCITY)
AS SELECT S.CITY, P.CITY
FROM S, SP, P
WHERE S.SNO = SP.SNO
AND SP.PNO = P.PNO
.Code
◇ Conditions of Updatable View Definition
does not include the key word DISTINCT every item in the SELECT clause is a simple reference to a column of the underlying table not a constant, an operational expression, or a function FORM clause identifies exactly one updatable table WHERE clause does not include a subquery Comments
these conditions are too restrictive in DB2, if SELECTION contains operational exp. (e.g. X+1), UPDATE is allowable on other columns ◇ Check Option what if an update make a tuple disqualified? add WITH CHECK OPTION to the view definition
all INSERTs and UPDATEs against that view will be checked to ensure that the newly INSERTed or UPDATEd row does satisfy the view-definition condition Comments
in DB2, check option can only be specified for updatable view check option is not inherentable 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw vwupdate.htm, Thu Jun 14 08:07:07 CST 2001
◇ Limitations can only be used in cursor definition (query exp.) a binary operation
illegal example: x UNION y UNION z Syntax A UNION [ ALL ] B
redundant duplicate rows are retained iff ALL is used for all i, the ith column of A and the ith column of B have exactly the same description (the same data type and length) no name inherent rule -- resulting table is unnamed 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw union.htm, Thu Jun 14 08:07:09 CST 2001
◇ COUNT, SUM, MAX, MIN
argument of SUM and AVG must be numeric DISTINCT or ALL can be applied on the argument must specify DISTINCT when COUNT is used if DISTINCT is used, argument must be a simple cloumn no nested function reference nulls in argument are eliminated before a function is applied (except COUNT) if argument is empty, COUNT returns a zero; others return null unless a GROUP BY is involved, a SELECT clause must consists of function references only, no mixture is allowed 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw function.htm, Thu Jun 14 08:07:10 CST 2001
◇ Security View (use view to hide data) GRANT operation
SELECT, UPDATE, DELETE, INSERT, REFERENCE privileges. table creator has the full privilege any user holding a privilege "with the grant option" can grant the privilege to another user with or without grant option GRANT INSERT, DELETE, UPDATE ON SP TO JOE
GRANT SELECT ON SP TO ALICE WITH GRANT OPTION
GRANT UPDATE (STATUS) ON S TO JUDY
GRANT DELETE ON SP TO SANDY, CLYDE
◇ Integrity NOT NULL CHECK
check for any column or combination of columns (In DB2, non-updatable views can not have CHECK) UNIQUE
no duplicated row is allowed must have NOT NULL constraint PRIMARY KEY (a special case of UNIQUE) FOREIGN KEY (an alternative way to specify a foreign key) REFERENCES Comments
Need to support wider range of integrity constraints ◇ Transaction Processing transaction: a sequence of SQL operations that is guaranteed to be atomic no nested transaction terminated by
COMMIT WORK (normal termination) ROLLBACK WORK (rollback termination) No specification what to do on failure a database update by a transaction is not visible by other transactions before termination comments:
no cascade rollback is possible, but concurrency is lower 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw control.htm, Thu Jun 14 08:07:12 CST 2001
◇ Range Variables
SELECT FIRST.SNO, SECOND.SNO
FROM S FIRST, S SECOND
WHERE FIRST.CITY = SECOND.CITY
FIRST and SECOND are range variables ◇ GROUP BY
rearrange the table into min. number of groups such that within each group all rows have the same value for the GROUP BY column ◇ HAVING
a WHERE clause for groups expression in a HAVING clause must be single-valued per group must be used with GROUP BY together SELECT PNO, SUM (SP.QTY)
FROM SP
GROUP BY SP.PNO
HAVING COUNT(*) > 1
.Code
◇ Search Conditions
used in WHERE and HAVING to qualify or disqualify specific rows Boolean Operators:
comparison predicate:
BETWEEN
ScalarExp [ NOT ] BETWEEN ScalarExp AND ScalarExp
LIKE test for null IN All-or-Any test for existence <!-- This document is transformed using htxp.pl --> 瓣ミ現獀厩戈癟厩╰ 硈模玭 lien@cherry.cs.nccu.edu.tw misc.htm, Thu Jun 14 08:07:14 CST 2001
◇ Comparison Predicate:
one argument can be a subquery that returns a single scalar value if no value is returned, the predicate will evaluated to unknown the subquery must be at RHS of the comparison operator cannot compare two subqueries cannot include GROUP BY or HAVING the FROM clause in the subquery must not identify a grouped view correctness of a program depends on data ◇ BETWEEN
ScalarExp [ NOT ] BETWEEN ScalarExp AND ScalarExp y BETWEEN x AND z x <= y AND y <= z
◇ LIKE used for simple pattern-matching Column-ref [ NOT ] LIKE atom [ ESCAPE atom ]
SELECT P.PNO, P.PNAME
FROM P
WHERE P.PNAME LIKE 'C%'
.Code
"part number and names for parts whose names begin with the letter C" Symbol meaning "_" any single character "%" any sequence of n characters Example meaning X LIKE %Berkeley% X contains the string "Berkeley" X LIKE S__ X is char(2) and begins with an S X LIKE _% X begins with a '_'
◇ Test for Existence
◇ Test for Null Null means unknown Null compared with any value --> unknown
NOT AND T ? F OR T ? F T F T T ? F T T T T ? ? ? ? ? F ? T ? ? F T F F F F F T ? T
SELECT S.SNO
FROM S
WHERE S.CITY IS NULL
◇ IN
ScalarExp [ NOT ] IN ( atom-commalist )
ScalarExp [ NOT ] IN subquery
SELECT P.PNO
FROM P
WHERE P.WEIGHT IN (12, 16, 17 )
◇ All-or-Any
ScalarExp quantified-comparison subquery quantified-comparison is the scalar comparison (=, <>, <, > <=, >=) followed by ALL, ANY, or SOME SELECT DISTINCT SP.SNO
FROM SP
WHERE SP.QTY >ALL
(SEL
9480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
