DATABASEchw31

2020-03-01 161浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.3 Basic SQL Query Language    3.1 Introduction ANSI SQL-92 ANSI SQL-99 release in 1999  Zhejiang Sci-Tech University Lican Huang 2
  • 3.3 Basic SQL Query Language  3.1 Introduction      SQL (Structured Query Language) 􀂄 1974 Boyce and Chamberlin 􀂄 1975—1979,IBM implemented it in System R IBM developed SQL, and it became standard. Mos t queries (including updates, inserts, deletes) are very standard across relational products. Zhejiang Sci-Tech University Lican Huang 3
  • 4.3. Basic SQL Query Language  Zhejiang Sci-Tech University Lican Huang 4
  • 5.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.1 (AGENTS where city =“New York”)[aid, aname] Select aid, aname from agents where city = “New Yo rk”; Zhejiang Sci-Tech University Lican Huang 5
  • 6.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.2 Select * from customers; Retrieve all fields ( all columns) Zhejiang Sci-Tech University Lican Huang 6
  • 7.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.3 Select pid from orders; Duplicate rows; select all distinct from … Optinal all Zhejiang Sci-Tech University Lican Huang 7
  • 8.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.4 ((CUSTOMERS[cid,cname] ∞ ORDERS ) ∞AGENTS)[cname,aname] (((CUSTOMERS X ORDERS)XAGENTS) where CUSTOMERS.cid = ORDERS.cid and ORDERS.aid = AGENTS.aid)[cname, aname] Select distinct customers.cname, agents.aname from customer s, orders, agents where customers.cid= orders.cid and order s.aid=agents.aid; Zhejiang Sci-Tech University Lican Huang 8
  • 9.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.5 Select ordno,x.cid,x.aid,x.pid, .40*(x.qty*p.price)-0.1*(c.discnt + a.percent)*(x.qty*p.price) from orders as x, customers as c , agents as a, products as p where c.cid = x.cid and a.aid = x .aid and p.pid =x.pid; As alias profit Zhejiang Sci-Tech University Lican Huang 9
  • 10.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.6 Select c.cid, c2.cid from customers c1, customers c2 where c1.city = c2.city and c1.cid < c2.cid; Zhejiang Sci-Tech University Lican Huang 10
  • 11.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.7 Select distinct x1.pid from orders x1, orders x2 where x1.pid = x2.pid and x1.cid < x2.cid; Zhejiang Sci-Tech University Lican Huang 11
  • 12.3 Basic SQL Query Language 3.3 Simple Select Statements Example 3.3.8 Select distinct y.cid from orders x, ord ers y where y.pid = x.pid and x.aid = “a06”; Zhejiang Sci-Tech University Lican Huang 12
  • 13.3 Basic SQL Query Language  3.2 Setting Up the Database Create Table customers (cid char(4) NO T NULL, cname varchar(13), city varc har(20),discnt real, primary key(cid)); Zhejiang Sci-Tech University Lican Huang 13
  • 14.3 Basic SQL Query Language  3.2 Setting Up the Database Create Table tablename (colname datat ype [NOT NULL] {, colname datatype [not null]….} [, primary key (colname {,colname …})]); Zhejiang Sci-Tech University Lican Huang 14
  • 15.3 Basic SQL Query Language 3.2 Setting Up the Database A.3 datatypes (page 733) Char(n)  page 733 Varchar(n) Smallint Integer Real float  Zhejiang Sci-Tech University Lican Huang 15
  • 16.3 Basic SQL Query Language 3.10 Insert Example 3.10.1  Insert into orders (ordno, month, cid,aid ,pid) values(1107,’aug’,’c006’,’a04’,’p 01’); Zhejiang Sci-Tech University Lican Huang 16
  • 17.3 Basic SQL Query Language 3.10 Update Statement Example 3.10.3 Update agents set percent = 1.1*percent where city = ‘New York’;  Zhejiang Sci-Tech University Lican Huang 17
  • 18.3 Basic SQL Query Language   3.10 Delete Statement Delete from agents where city = ‘New York’ Zhejiang Sci-Tech University Lican Huang 18
  • 19.3 Basic SQL Query Language   3.10 drop drop table tablename; Zhejiang Sci-Tech University Lican Huang 19
  • 20.3 Basic SQL Query Language      3.10 Alter Table Page 745 Alter Table tablename ADD ( columnname d atatype. ..) Alter Table tablename drop( columnname d atatype. ..) Alter Table tablename modify( columnname datatype, columnname datatype) ORACLE Zhejiang Sci-Tech University Lican Huang 20
  • 21.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.1 Select distince cid from orders where ai d in (select aid from agents where cit y =“Duluth” or city = “ Dallas”);  Zhejiang Sci-Tech University Lican Huang 21
  • 22.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.2 select * from agents where city in(“Dul uth”,”Dallas”);  Zhejiang Sci-Tech University Lican Huang 22
  • 23.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.3 Select cname, discnt from customers w here cid in(select cid from orders whe re aid in (select aid from agents wher e city in (“Duluth”,”Dallas”)));  Zhejiang Sci-Tech University Lican Huang 23
  • 24.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.4 Select distinct cname from customers where ‘p05’ in (select pid from orders where cid = customers.cid);  Zhejiang Sci-Tech University Lican Huang 24
  • 25.3 Basic SQL Query Language 3.4 Subqueries The IN Predicate Example 3.4.6 Select ordno from orders where(cid,ai d) in (select cid, aid from customers c, agents a where c.city = “Duluth” and a.city =“New York”);  Zhejiang Sci-Tech University Lican Huang 25
  • 26.3 Basic SQL Query Language  3.4 Subqueries The IN Predicate expr NOT IN(Subquery) Expr[NOT] IN (Subquery) expr[NOT] IN (val {,val….}) Zhejiang Sci-Tech University Lican Huang 26
  • 27.3 Basic SQL Query Language 3.4 Subqueries The Quantified Comparison Predicate Expr theta {SOME ANY ALL}(Subquery)  Theta <,<=, =, <>,>,>= SOME ANY are same. Zhejiang Sci-Tech University Lican Huang 27
  • 28.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.7 Select aid from agents where percent < =all (select percent from agents);  Zhejiang Sci-Tech University Lican Huang 28
  • 29.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.8 select cid, cname from customers wher e discnt = some(select discnt from cu stomers where city = “Dallas” or city = “Boston”);  Zhejiang Sci-Tech University Lican Huang 29
  • 30.3 Basic SQL Query Language 3.4 Subqueries Example 3.4.9 select cid from customers where disc nt < all (select discnt from customers where city = “Duluth”);  Zhejiang Sci-Tech University Lican Huang 30
  • 31.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.10 select distinct c.aname from custom ers c where exists (select * from order s x where c.cid = x.cid and x.aid = “a 05”); Zhejiang Sci-Tech University Lican Huang 31
  • 32.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.11 select distinct cid from orders x whe re pid =“p01” and exists (select * fro m orders where cid = x.cid and pid = ‘p07’); Zhejiang Sci-Tech University Lican Huang 32
  • 33.3 Basic SQL Query Language  3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.12 select distinct c.cname from custom ers c where not exists(select * from or ders x where c.cid = x.cid and x.aid = ‘a05’); Zhejiang Sci-Tech University Lican Huang 33
  • 34.3 Basic SQL Query Language 3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.13 page 105  Zhejiang Sci-Tech University Lican Huang 34
  • 35.3 Basic SQL Query Language 3.4 The EXISTS Predicate [NOT] EXISTS (Subquery) Example 3.4.14 select distinct cid from orders x whe re no exists (select * from orders wher e cid = x.cid and aid = ‘a03’);  Zhejiang Sci-Tech University Lican Huang 35