2. Complete SQL Tianqing Zhang School of Computer (School of Software) SCU 2006.
-
Upload
shauna-woods -
Category
Documents
-
view
227 -
download
4
Transcript of 2. Complete SQL Tianqing Zhang School of Computer (School of Software) SCU 2006.
2. Complete SQL
Tianqing Zhang
School of Computer
(School of Software)
SCU 2006
Tianqing Zhang SCU 2
2.1 Basic SQL
• SQL (Structured Query Language) – The de facto standard query language for relational D
BMSs 事实标准– both DDL (Data Definition Language) and DML (Data
Manipulation Language)– A historical note
• SQL1 (ANSI 1989)• SQL2 or SQL-92 (ANSI 1992)• SQL3 (SQL99)
– 学习方法:背命令,实践,理解
Tianqing Zhang SCU 3
2.1.1 SQL Data Definition Language
• Creating a Database – Understanding SQL Data Types – Numeric Data – Character Strings – Bit Strings
• Temporal Data • Date and Time • Intervals • Operations on Dates
• Creating a Domain • Creating a Table • Destroying a Table • Modifying a Table Schema
Tianqing Zhang SCU 4
Backus Naur Form notation
• upper-case reserved words• lower-case user-defined words• | choice• { } a required element • [ ] an optional element• [ ,… ] optional repetitions
Tianqing Zhang SCU 5
Creating a Schema 1
• Why?– 允许多个用户使用一个数据库而不会干扰其它用户。 – 把数据库对象组织成逻辑组,让它们更便于管理。 – 第三方的应用可以放在不同的模式中, 这样它们就不
会和其它对象的名字冲突。
Tianqing Zhang SCU 6
Creating a Schema 2
• How?– CREATE SCHEMA database-name [AUTHORIZA
TION user-identifier]; – 访问在模式中的对象 : schema.table
• Public Schema: [public.]table– DROP SCHEMA database-name [RESTRICT | CASCA
DE]; • RESTRICT 受限 有对象,不能删• CASCADE 级联 有对象,一起删
Tianqing Zhang SCU 7
SQL Data Types 1
• Boolean Data– BOOLEAN TURE/FALSE
• Numeric Data– Exact Numbers
• INTEGER (INT , INT4) 2,147,483,648 to 2,147,483,647 • SMALLINT -32768 to 32767• BIGINT (INT8) 8 位整数
– Approximate Numbers• FLOAT[precision]• REAL• DOUBLE PRECISION
– Formatted Numbers• DECIMAL(i,j), DEC(i,j) or NUMERIC(i,j)
Tianqing Zhang SCU 8
SQL Data Types 2
• Character Strings– Fixed length n
• CHAR(n) or CHARACTER(n) 定长串 不足充空 space
– Varying length of maximum n• VARCHAR(n) or CHAR VARYING (n) 变长串 • VARCHAR2(n) ORACLE 用
• Bit Strings– Fixed length n: BIT(n) 定长位串– Varying length of maximum n: VARBIT(n) or BIT VARYIN
G (n) 变长位串
Tianqing Zhang SCU 9
SQL Data Types 3
• Temporal Data 日期时间– Date and Time
• DATE stores calendar values representing YEAR, MONTH, and DAY.
– ‘2000-02-29’ 推荐 to_date('2000/02/29' , ‘yyyy/mm/dd' )
• TIME defines HOURS, MINUTES, and SECONDS in a twenty-four-hour notation: HH:MM:SS
• TIME(i) defines i additional decimal fractions of seconds: HH:MM:SS:ddd...d.
– Example, in TIME(4), a time could be 22:20:01:2345
• TIME WITH TIME ZONE includes the displacement [+13:00 to -12:59] from standard universal time zone: HH:MM:SS+/-hh:mm.
• TIMESTAMP represents a complete date and time with six fractions of seconds and optional time zone. 时间戳 postgreSQL now( ) 函数
Tianqing Zhang SCU 10
SQL Data Types 4
• Intervals 间隔 – An interval results when two dates are subtracted.
• Example: OrderDate – ShipDate• INTERVAL YEAR (3) to MONTH could be an interval between 0-0
(0 years and 0 months) and 999-11 (999 years and 11 months). SQL 标准
• postgreSQL 形式 ‘ 999 YEAR 11 MONTH’• INTERVAL DAY(2) to MINUTE could be an interval between 00:00:
00 (0 days. 0 hours, 0 minutes) to 99:23:59 (99 days, 23 hours, and 59 minutes).
• postgreSQL ‘99 day 23 hour 59 minute’
– A date plus or minus an interval yields a new date.• CURRENT_DATE + INTERVAL '1' MONTH• postgreSQL CURRENT_DATA +’ 1 MONTH’
Tianqing Zhang SCU 11
SQL Data Types 5
• Valid combinations of DATETIME and intervals : – Datetime - Datetime = Interval of year/month or day/ti
me – Datetime (+ or -) Interval = Datetime – Interval (* or /) Number = Interval – Interval (+ or -) Interval = Interval
Tianqing Zhang SCU 12
An Example
Tianqing Zhang SCU 13
Creating a Domain
• Domain 域– SQL-92 allows the definition of new data types, more precisely d
ata type macros, for columns expressed in terms of the basic data types but not in terms of other domains. 用基本类型定义
– Syntax• CREATE DOMAIN domainName [AS] dataType
[DEFAULT defaultOption] [CHECK (searchCondition)]
– Example• CREATE DOMAIN hour_dom AS INTEGER DEFAULT 0 CHECK (VALUE >=
0); • CREATE DOMAIN genderDom AS CHAR
DEFAULT ‘M’CHECK (VALUE IN (‘F’, ‘f’,‘M’ ,‘m’)); -- 定义
• gender genderDom NOT NULL -- 使用
Tianqing Zhang SCU 14
Creating a Table 1
• SyntaxCREATE TABLE tableName ({columName dataType [NOT NULL] [UNIQUE] [DEFAULT defaultOption] [CHECK (searchCondition)] [,…] } [PRIMARY KEY (listOfColumns),] {[UNIQUE (listOfColumns),] [,…]} {[FOREIGN KEY (listOfFireignKeyColumns) REFERENCES parentTableName [ (listOfCandidateKeyColumn
s)], [MATCH {PARTIAL | FULL} [ON UPDATE referentialAction] [ON DELETE referentialAction]] [,…]} {[CHECK (searchCondition)] [,…] } )
Tianqing Zhang SCU 15
Creating a Table 2
• An Example:CREATE TABLE BOOK ( Book_Id NUMERIC(6) NOT NULL,
Edition NUMERIC(3) NOT NULL, BorrowerMemNo NUMERIC(4), BorrowDueDate DATE, CallNumber VARCHAR(8) NOT NULL, LibCheck ssn_dom,PRIMARY KEY (Book_Id), FOREIGN KEY (BorrowerMemNo) REFERENCES MEMBER(MemNo), FOREIGN KEY (CallNumber) REFERENCES TITLE(CallNumber), FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN)
);
Tianqing Zhang SCU 16
Creating a Table 3
• Primary and foreign keys can be specified at the same level as the definition of the column, as long as they are simple atomic ones. 如果单值, 主外码也可定义为列级完整性约束
CREATE TABLE LIBRARIAN ( SSN ssn_dom NOT NULL PRIMARY KEY,
Name name_dom NOT NULL,Address address_dom,Salary DEC(4.2) DEFAULT 0.0 CHECK(Salary >= 0),Gender gender_dom, Birthday DATE, SUPERSSN ssn_dom FOREIGN KEY REFERENCES LIBRARIAN(SSN), Section sectno_dom FOREIGN KEY REFERENCES SECTION(SectionId)
);
Tianqing Zhang SCU 17
Creating a Table 4
• Primary keys composed of more than one column can be specified.
CREATE TABLE DEPENDENT( LIBSSN ssn_dom,
Name name_dom NOT NULL, Birthday DATE, Kinship CHAR(5) DEFAULT 'none', PRIMARY KEY (LIBSSN, Name), FOREIGN KEY (LIBSSN) REFERENCES LIBRARIAN(SSN)
);
Tianqing Zhang SCU 18
Creating a Table 5
• The unique key word specifies each row must have unique values -- specify that a set of columns may serve as an alternate key. 备选码
CREATE TABLE BOOK ( Book_Id NUMERIC(6) NOT NULL,
Edition NUMERIC(3) NOT NULL, BorrowerMemNo NUMERIC(4), BorrowDueDate DATE, CallNumber VARCHAR(8) NOT NULL UNQUE, LibCheck ssn_dom,Primary Key (Book_Id), FOREIGN KEY (BorrowerMemNo) REFERENCES MEMBER(MemNo), FOREIGN KEY (CallNumber) REFERENCES TITLE(CallNumber), FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN)
);
Tianqing Zhang SCU 19
Destroying a Table
• SyntaxDROP TABLE tableName [ RESTRICT | CASCADE ]
– With the CASCADE option, the table and all references to it are removed.
– With the RESTRICT option, the table is removed if it is not referenced. 默认
• Example– DROP TABLE MEMBER CASCADE;– DROP TABLE MEMBER RESTRICT;
Tianqing Zhang SCU 20
Modifying a Table Schema 1
• Syntax
ALTER TABLE tableName [ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE][DEFAULT defaultOption] [CHECK (searchCondition)]][DROP [COLUMN] columnName [ RESTRICT | CASCADE ]][ADD [CONSTRAINT [constraintName]]tableConstraintDefinition][DROP CONSTRAINT constraintName [ RESTRICT | CASCADE ]][ALTER [COLUMN] SET DEFAULT defaultOption][ALTER [COLUMN] DROP DEFAULT]
Tianqing Zhang SCU 21
Modifying a Table Schema 2
• ALTER TABLE table-name ALTER – 修改字段数据类型
• ALTER TABLE LIBRARIAN ALTER Salary NUMBER(6,2);
• ALTER TABLE LIBRARIAN ALTER COLUMN Salary TYPE NUMBER(6,2); --p
ostgreSQL– 改变字段的缺省值
• ALTER TABLE DEPENDENT ALTER COLUMN kinship DROP DEFAULT;
• ALTER TABLE BOOK ALTER COLUMN BorrowerMemNo SET DEFAULT NULL;
– 增加一个不能写成表约束的非空约束• ALTER TABLE products ALTER COLUMN product_no SET NOT NUL
L;
Tianqing Zhang SCU 22
Modifying a Table Schema 3
• ALTER TABLE table-name ADD – 增加字段
• ALTER TABLE products ADD COLUMN description text ; – 增加约束
• ALTER TABLE products ADD CHECK (name <> ''); • ALTER TABLE products
ADD CONSTRAINT some_name UNIQUE (product_no);
• ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
Tianqing Zhang SCU 23
Modifying a Table Schema 4
• ALTER TABLE table-name DROP– 删除字段
• ALTER TABLE products DROP COLUMN description RESTRICT; -- 默认受限删除
• ALTER TABLE products DROP COLUMN description CASCADE; --CASCADE 授权删除任何依赖该字段的东西
– 删除约束 • ALTER TABLE products DROP CONSTRAINT constraint_na
me; • ALTER TABLE products ALTER COLUMN product_no DROP
NOT NULL;
Tianqing Zhang SCU 24
2.1.2 SQL Data Manipulation Language
• The Complete SELECT Statement– Syntax
SELECT [DISTINCT | ALL] { * | [columnExpression [AS newName]] [,…] }FROM tableName [alias] [,…][WHERE condition] -- 元组条件[GROUP BY columnList] -- 分组
[HAVING condition] -- 分组条件[ORDER BY columnList] -- 排序
– Example 全表查询
SELECT * FROM BOOK;
Tianqing Zhang SCU 25
SELECT Statement 1
• Resulting Tables as Sets– If duplicate elimination is desired, it can be achieved u
sing the DISTINCT keyword in the SELECT clause. 消重
SELECT DISTINCT CallNumber FROM BOOK;
Tianqing Zhang SCU 26
SELECT Statement 2
• Aliasing in SQL: The AS Operator 别名– Renaming attributes in the result of a query: 结果属性
别名SELECT Name AS Librarian_Name
FROM LIBRARIANWHERE Salary > 18000;
– Table alias can be achieved with the AS operator in the FROM-clause 表别名
SELECT * FROM LIBRARIAN AS L WHERE L.name = 'Ruchi Jones';
Tianqing Zhang SCU 27
SELECT Statement 3
• Calculated fields– Example:Produce a list of monthly salaries for all libra
rians, showing the name and the monthly salary details.
SELECT staffNo,fName,lName,salary/12FROM Staff;
SELECT staffNo,fName,lName,salary/12 AS monthlySalary
FROM Staff;
Tianqing Zhang SCU 28
SELECT Statement 4
• Comparing NULL Values– NULL can appear in a column 不能用 = <> 来比较– SQL provides the IS NULL and IS NOT NULL operato
rs to test for NULL values 用 IS NULL 和 IS NOT NULL 来比较
SELECT Book_id FROM BOOK WHERE BorrowerMemNo IS NULL;
Tianqing Zhang SCU 29
SELECT Statement 5
• NULL 引起的三值逻辑问题– 三值
• True• False• Unknown
– WHERE 子句为真的才被选出• 引入 IS NOT FALSE• Example: 区别?SELECT Name
FROM LibarianWHERE Salary < 29000 AND Gender = 'M‘;
SELECT Name FROM LibarianWHERE (Salary < 29000 AND Gender = 'M') IS NOT FALSE;
Tianqing Zhang SCU 30
SELECT Statement 6
• Range Conditions– Range search conditions, like the condition "retrieve all
the librarians whose salary is between 25000 and 35000“, will be expressed with a conjunction
SELECT * FROM LIBRARIAN WHERE Salary >= 25000 AND Salary <= 35000;
– Using the BETWEEN operator
SELECT * FROM LIBRARIAN WHERE Salary BETWEEN 25000 AND 35000;
Tianqing Zhang SCU 31
SELECT Statement 7
• Pattern Matching and String Concatenation– A percent sign `%‘ indicates a match with an arbitrary
number of characters including spaces. % 任意个字符– An underscore sign ‘_’ matches a single arbitrary char
acter. _ 一个字符– Example 1: Retrieve all the Librarians whose first nam
e is Nick.
SELECT SSN, Name
FROM LIBRARIAN
WHERE Name LIKE 'Nick%';
Tianqing Zhang SCU 32
SELECT Statement 8
• Pattern Matching and String Concatenation– A percent sign `%‘ indicates a match with an arbitrary
number of characters including spaces. % 任意个字符– An underscore sign ‘_’ matches a single arbitrary char
acter. _ 一个字符– Example 2: Display in a single column the full name of
all members in Pittsburgh (area code number 412) who have phone numbers, the first digit of which is a 6 and the third a 3. || 连接串
SELECT Fname || MI || Lname AS Name, PhoneNumber FROM MEMBER WHERE PhoneNumber LIKE '(412) 6_3%';
Tianqing Zhang SCU 33
SELECT Statement 9
• Pattern Matching and String Concatenation– In the event that the search pattern includes a percent
sign, we can use an escape character to cause the SQL interpreter to interpret the percent sign as itself. 如何表示 & 和 _ 本身? 转义字
– Example: Display the name and call number of all the book titles that contain '10%' as part of the title name.
SELECT Name, CallNumber FROM TITLES WHERE Name LIKE '%10&%%' ESCAPE '&';
Tianqing Zhang SCU 34
SELECT Statement 10
• Set membership search condition (IN/NOT IN)– Example: List all managers and supervisors.
SELECT staffNo,fName,lName,positionFROM StaffWHERE position IN (‘Manager’, ‘Supervisor’);
– Example: List all librarians who are not managers and supervisors.
SELECT staffNo,fName,lName,positionFROM StaffWHERE position NOT IN (‘Manager’, ‘Supervisor’);
SELECT staffNo,fName,lName,positionFROM StaffWHERE position NOT IN (‘Manager’, ‘Supervisor’) is not fal
se;
Tianqing Zhang SCU 35
SELECT Statement 11
• Aggregate Functions– Five aggregate functions:SUM, MAX, MIN, AVG, and COUNT
五个基本集函数– Example: Display all the statistics about librarian salaries.
SELECT SUM (Salary) AS TotalSalaries, -- 求和MAX (Salary) AS MaxSalary, --最大值MIN (Salary) AS MinSalary, --最小值AVG (Salary) AS AvgSalary, --平均值COUNT (*) AS Cardinality, -- 元组计数COUNT (DISTINCT Salary) AS Salarylevels –不重复非空值计数
FROM LIBRARIAN;
Tianqing Zhang SCU 36
SELECT Statement 12
• Aggregate Functions– Distinguish between count(*) and count(column). cou
nt(*) 对所有选出元组计数, count(column)只对 column 非 NULL 的原组计数。
– Example:
SELECT count(*)
FROM librarian;
SELECT count(salary)
FROM librarian;
Tianqing Zhang SCU 37
SELECT Statement 13
• Grouping in SQL: The GROUP BY and HAVING Clauses 分组 分组限制– In SQL, we can group rows together based on the val
ue of some attributes that we call the grouping attributes.
– Example: List the call number of the books that have more than two editions.
SELECT CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber;
Tianqing Zhang SCU 38
SELECT Statement 14
• Grouping in SQL: The GROUP BY and HAVING Clauses 分组 分组限制– The HAVING clause to select only those groups that we are inter
ested in. HAVING短语对分组进行限制– Example: assume that we are interested in finding out the numb
er of copies of the popular books (for example, with Edition >=3), for which at least five copies exist in the library.
SELECT CallNumber, COUNT(*) AS NoPopularBooks FROM BOOK WHERE Edition >= 3 -- 元组限制GROUP BY CallNumber -- 分组HAVING COUNT (*) >= 5; -- 分组限制
Tianqing Zhang SCU 39
SELECT Statement 15
• Grouping in SQL: The GROUP BY and HAVING Clauses 分组 分组限制– A wrong example:SELECT Book_Id, CallNumber, COUNT(*) AS NoPopul
arBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber;
– It’s wrong, why?
Tianqing Zhang SCU 40
SELECT Statement 16
• Grouping in SQL: The GROUP BY and HAVING Clauses 分组 分组限制– A wrong example:SELECT Book_Id, CallNumber, COUNT(*) AS NoPopul
arBooks FROM BOOK WHERE Edition >= 3 GROUP BY CallNumber;
– It’s wrong, why?– SELECT后一般只能出现集函数和 GROUP BY后的分
组列
Tianqing Zhang SCU 41
Nested Queries and Set Comparisons
• In some queries, the selection conditions can be expressed more easily in terms of set comparisons. 查询中使用集合– Explicit definition : (1, 2, 3) , ('Science', 'Art', 'Children'). – Implicit definition : use an inner SELECT statement (subquery) w
hose output table (set) is used by the selection condition of the outer SELECT statement in order to produce the final result. nested queries 嵌套查询
• SELECT MemNo, Fname, MI, Lname FROM MEMBER WHERE MemNo = - -必须保证子
查询单值( SELECT BorrowerMemNo
FROM BOOK WHERE Book_id = 999);
Tianqing Zhang SCU 42
Set Membership 1
• The IN and NOT IN operators check for simple membership.– Example : List each head librarian's SSN, along with
their section, except those of the Science, Art, and Children sections.
– SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children');
Tianqing Zhang SCU 43
Set Membership 2
• The IN and NOT IN operators check for simple membership.– Example : List each head librarian's SSN, along with
their section, except those of the Science, Art, and Children sections.
– SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children');
– 思考:如果 Name 为 NULL ,情况怎样?
Tianqing Zhang SCU 44
Set Membership 3
• IS NOT FALSE:– SELECT HeadSSN, Name
FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children')
IS NOT FALSE; - - 允许 NULL• IS TRUE:
– SELECT HeadSSN, Name FROM SECTION WHERE Name NOT IN ('Science', 'Art', 'Children')
IS TRUE; - - 明确不允许 NULL ,可省略
Tianqing Zhang SCU 45
Set Membership 4
• An example using the IN operator :– List the last names of all librarians who are not head l
ibrarians.– SELECT LName
FROM LIBRARIAN WHERE SSN NOT IN
(SELECT LIBSSN FROM SECTION) IS NOT FALSE;
Tianqing Zhang SCU 46
Quantified Set Comparisons 1
• The test for membership, which is an equality comparison, can be generalized to other comparisons ( =, <>, >, >=, <, <= ) and can be quantified using ANY (that is, SOME) or ALL.– Comparisons ( =, <>, >, >=, <, <= ) 要求子查询单值,类型可比,
子查询只能在比较运算符右边。• Example : List the SSN of all librarians whose salary is lower than t
hat of the librarian whose SSN is ‘S3-07-021’.• SELECT SSN
FROM LibrarianWHERE salary <
(SELECT salary FROM Librarian WHERE SSN = ‘S3-07-021’);
Tianqing Zhang SCU 47
Quantified Set Comparisons 2
– Comparisons ( =, <>, >, >=, <, <= ) + (ANY or ALL) 实现各种复杂语义
• > ANY 不是最小, >= ALL 最大,…• Example 1 : List the SSN of all librarians whose salary is th
e lowest in the library.• SELECT SSN
FROM Librarian
WHERE salary <= ALL
(SELECT salary
FROM Librarian);
Tianqing Zhang SCU 48
Quantified Set Comparisons 3
– Comparisons ( =, <>, >, >=, <, <= ) + (ANY or ALL) 实现各种复杂语义
• Example 2 : List the name and SSN of all head librarians whose salary is lower than that of any librarian who is not a head librarian.
• SELECT Name, SSN FROM LIBRARIAN, SECTION WHERE SSN = HeadSSN AND salary < AN
Y (SELECT Salary FROM LIBRARIAN L WHERE L.SSN NOT IN ( SELECT HeadSSN FROM SECTI
ON ) IS NOT FALSE);
Tianqing Zhang SCU 49
Set Comparisons: EMPTY and UNIQUE 1
• The EXISTS (not empty) and NOT EXISTS (empty) operators test for emptiness of a result. 存在测试– Example 1: List the names and SSNs of all librarians who are no
t head librarians.– SELECT L.SSN, L.Name
FROM LIBRARIAN L
WHERE NOT EXISTS
(SELECT *
FROM SECTION
WHERE L.SSN = HeadSSN); - -内外层相关– 相关子查询:计算时,类似两重循环。外层带值入内层,测试是否有值。
Tianqing Zhang SCU 50
Set Comparisons: EMPTY and UNIQUE 2
• The EXISTS (not empty) and NOT EXISTS (empty) operators test for emptiness of a result.– Example 2: List the member numbers of all library me
mber who haven’t borrowed any book.– SELECT MemNo
FROM Member M
WHERE NOT EXISTS
(SELECT *
FROM Book
WHERE M.MemNo = BorrowerMemNo); - -内外层相关
Tianqing Zhang SCU 51
Set Comparisons: EMPTY and UNIQUE 3
• NOT EXISTS 可实现除操作– Example 3: List the member numbers of all library mem
ber who likes all books written by ‘Thomas’.– SELECT MemNo
FROM Member MWHERE NOT EXISTS (SELECT * FROM Book B WHERE Author = ‘Thomas’ AND NOT EXISTS(SELECT * FROM LIKE L WHERE M.MemNo = L.MemNoAND L.CallNumber=B. CallNumber));
Tianqing Zhang SCU 52
Set Comparisons: EMPTY and UNIQUE 4
• The UNIQUE and NOT UNIQUE operators test for duplicates in a result. 唯一测试– Example: List the names and SSNs of all librarians wit
h more than one dependent.– SELECT Name,SSN
FROM Library L
WHERE NOT UNIQUE
(SELECT * FROM DEPENDENT WHERE LIBSSN = L.SSN);
Tianqing Zhang SCU 53
Joining Tables in SQL2 1
• Wher Join 传统方式– Example 1 : List the last names of the library members and his
borrowed book Id.SELECT Lname, Book_Id
FROM Member,BookWHER MemNo=BorrowerMemNo; -- 连接条件
– Example 2 : List the last names of the library members who likes books written by ‘Thomas’.
SELCT LnameFROM Member,Like,Book -- 三表连接WHERE Member.MemNo=Like.MemNo AND Like.Callnumber=Book.Callnumber
AND Book.Author=‘Thomas’;
Tianqing Zhang SCU 54
Joining Tables in SQL2 2
• SQL2 introduced JOIN in order to separate the selection and join conditions, leaving the WHERE clause for selection only.– Example 1 : List the last names of the library members and his
borrowed book Id.SELECT Lname, Book_Id
FROM Member JOIN Book ON MemNo = BorrowerMemNo;– Example 2 : List the last names of the library members who like
s books written by ‘Thomas’.SELCT Lname
FROM (Member M JOIN Like L ON M.MenNo= L.MenNo) JOIN Book B ON L.Callnumber=B. Callnumber
WHERE Book.Author=‘Thomas’;
Tianqing Zhang SCU 55
Joining Tables in SQL2 3
– Example 3 : list only once the last name and address of all the members with a borrowed book and whose first name is Andreas or Rebecca.
– SELECT DISTINCT Lname, Address
FROM (MEMBER JOIN BOOK ON MemNo = BorrowerMemNo)
WHERE Fname = 'Andreas' or Fname = 'Rebecca';
Tianqing Zhang SCU 56
Joining Tables in SQL2 4
• Four types of join: NATURAL JOIN (自然连接 ) and outer joins (LEFT OUTER JOIN 左外连接 , RIGHT OUTER JOIN 右外连接 , and FULL OUTER JOIN 全外连接 ). – NATURAL JOIN
• SELECT * FROM Member NATURAL JOIN Book WHERE Fname LIKE ‘%be’;
– LEFT OUTER JION (左外连接 相当于左表扩展右表属性)• SELECT *
FROM Member LEFT OUTER JOIN Book ON MemNo=BorrowerMemNO WHERE Fname LIKE ‘%be’;
• 即使 Member 表(左表)的某元组匹配不到 Book 表(右表)的元组,该元组也将出现在结果中,对应的 Book 表的属性为 NULL 。
Tianqing Zhang SCU 57
Joining Tables in SQL2 5
– RIGHT OUTER JION 右外连接 右表扩展• SELECT *
FROM Member RIGHT OUTER JOIN Book ON MemNo=BorrowerMemNO WHERE Fname LIKE ‘%be’;
• 即使 Book 表(右表) 的某元组匹配不到Member 表(左表)的元组,该元组也将出现在结果中,对应的 Book 表的属性为 NULL 。
– FULL OUTER JION 全外连接 双向扩展• SELECT *
FROM Member FULL OUTER JOIN Book ON MemNo=BorrowerMemNO WHERE Fname LIKE ‘%be’;
• 即使 Member 表(左表)的某元组匹配不到 Book 表(右表)的元组,该元组也将出现在结果中,对应的 Book 表的属性为 NULL;同时,即使 Book 表(右表) 的某元组匹配不到Member 表(左表)的元组,该元组也将出现在结果中,对应的 Book 表的属性为 NULL 。
Tianqing Zhang SCU 58
Joining Tables in SQL2 6
• AS phrase is supported in FROM clause.– SELECT Name
FROM (LIBRARIAN NATURAL JOIN SECTION) AS S
WHERE S.Name = 'Children';
Tianqing Zhang SCU 59
More on the Insert Command 1
• The implicit form. INSERT with implicit specification of attributes 不指定属性– INSERT INTO BOOK VALUES (999, 2, NULL, NULL, 'Q
A7.56B7', NULL); • The explicit form. INSERT with implicit specificati
on of attributes 指定属性– INSERT INTO BOOK (Book_id, Edition, CallNumber)
VALUES (999, 2, 'QA7.56B7'); – INSERT INTO BOOK (Edition, CallNumber, Book_id)
VALUES (2, 'QA7.56B7', 999);
Tianqing Zhang SCU 60
More on the Insert Command 2
• The tuples to be inserted into a table would be derived from the database using the SELECT statement 插入查询结果– CREATE TABLE Section_Info
(SectCode section_dom primary key,
NumLibrarian INT
);– INSERT INTO Section_Info (SectCode, NumLibrarian)
SELECT SectNo, Count(*)
FROM LIBRARIAN, SECTION
WHERE Section = SectNo
GROUP BY SectNo;
Tianqing Zhang SCU 61
Modifying data in the database (UPDATE) 1
• Syntax– UPDATE TableName
SET columnName1=value [, columnName1=value…]
[WHERE searchCondition]• Examples
– Example 1: Give all librarians 5% pay increase.– UPDATE librarian
SET salary = salary*1.05;
Tianqing Zhang SCU 62
Modifying data in the database (UPDATE) 2
• Examples– Example 2: Give all librarians works in ‘Art’ section (say sectio
n 3) 5% pay increase. – UPDATE librarian
SET salary = salary*1.05WHERE section = 3;
– UPDATE librarianSET salary = salary*1.05WHERE section =
(SELECT sectno FROM sction WHERE name = ‘Art’);
Tianqing Zhang SCU 63
Modifying data in the database (UPDATE) 3
• Examples– Example 3: Change Ashoka Savasere’s section From
‘History’ to ‘Art’ Section and give him 5% pay increase. – UPDATE librarian
SET section=(SELECT Sectno FROM Section
WHERE Name='Art'),
salary=salary*1.05
WHERE name='Ashoka Savasere‘ AND section =
(SELECT SectNo
FROM Section
WHERE name=‘History’);
Tianqing Zhang SCU 64
Deleting data from the database (DELETE)
• Syntax– DELETE FROM TableName
[WHERE searchCondition]
• Examples– Example 1: Delete all librarians. (Delete all rows.)– DELETE FROM librarian;– Example 2: Delete all librarians works in ‘checkout’ section. (Del
ete specific rows.) – DELETE FROM librarian
WHERE section =
(SELECT sectno FROM section
WHERE name = ‘checkout’);
Tianqing Zhang SCU 65
更新与参照完整性约束 1
• 当对两个有引用关系的表数据更新时,可能引起完整性约束被破坏– Example 1: 更新外码值
create table t1
( a int primary key,
b int
);
create table t2
( c char(10) primary key,
d int references t1
);
Tianqing Zhang SCU 66
更新与参照完整性约束 2
insert into t1(a,b) values(1,1);
insert into t1(a,b) values(2,2);
insert into t2(c,d) values('aa',1);
insert into t2(c,d) values('bb',2);
update t2 set d=3 where c='bb';
ERROR: insert or update on table "t2" violates foreign key constraint "t2_d_fkey"
DETAIL: Key (d)=(3) is not present in table "t1".
Tianqing Zhang SCU 67
更新与参照完整性约束 3
– Example 2 :删除被引用的码值delete from t1 where a=1;ERROR: update or delete on "t1" violates foreign key constraint "t2
_d_fkey" on "t2"DETAIL: Key (a)=(1) is still referenced from table "t2".– 增加删除外码约束的 action : set null 置空值
alter table t2 drop constraint t2_d_fkey;alter table t2 add constraint t2_d_fkey foreign key (d) references t1 on
delete set null;postgres=# delete from t1 where a=1;DELETE 1postgres=# select * from t2; bb | 2 aa |
Tianqing Zhang SCU 68
更新与参照完整性约束 4
– 增加删除外码约束的 action : cascade 级联alter table t2 drop constraint t2_d_fkey;
alter table t2 add constraint t2_d_fkey foreign key (d) references t1 on delete cascade;
postgres=# delete from t1 where a=1;
DELETE 1
postgres=# select * from t2;
bb | 2
Tianqing Zhang SCU 69
更新与参照完整性约束 5
– Example 3 :修改被引用的码值postgres=# update t1 set a=3 where a=1;ERROR: update or delete on "t1" violates foreign key constraint "t2
_d_fkey" on "t2"DETAIL: Key (a)=(1) is still referenced from table "t2".– 增加修改外码约束的 action : set null 置空值
alter table t2 drop constraint t2_d_fkey;alter table t2 add constraint t2_d_fkey foreign key (d) references t1 on
delete set null on update set null;postgres=# update t1 set a=3 where a=1;UPDATE 1postgres=# select * from t2; bb | 2 aa |
Tianqing Zhang SCU 70
更新与参照完整性约束 6
– 增加修改外码约束的 action : cascade 级联alter table t2 drop constraint t2_d_fkey;
alter table t2 add constraint t2_d_fkey foreign key (d) references t1 on delete cascade on update cascade;
postgres=# update t1 set a=3 where a=1;
UPDATE 1
postgres=# select * from t2;
bb | 2
aa | 3