Download
unit 3 sql n.
Skip this Video
Loading SlideShow in 5 Seconds..
Unit 3 SQL PowerPoint Presentation

Unit 3 SQL

140 Vues Download Presentation
Télécharger la présentation

Unit 3 SQL

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Unit 3SQL 英文版:Chap 3 “SQL” 中文版:第4章 “結構化查詢語言”

  2. Chapter 3: SQL • Data Definition • Basic Query Structure • Set Operations (union, intersect, except/differentiate) • Aggregate Functions (count, sum, average, min, max – group by) • Null Values (is [not] null) • Nested Subqueries (in, exist, empty) • Complex Queries (as, with) • Views (create view X as/ select … into cursor) • Modification of the Database (delete, insert, update) • Joined Relations**

  3. History • IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory • Renamed Structured Query Language (SQL) • ANSI and ISO standard SQL: • SQL-86 • SQL-89 • SQL-92 • SQL:1999 (language name became Y2K compliant!) • SQL:2003 • Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. • Not all examples here may work on your particular system.

  4. Domain Types in SQL • char(n). Fixed length character string, with user-specified length n. • varchar(n). Variable length character strings, with user-specified maximum length n. • int.Integer (a finite subset of the integers that is machine-dependent). • smallint. Small integer (a machine-dependent subset of the integer domain type). • numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. • real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. • float(n). Floating point number, with user-specified precision of at least n digits. • More are covered in Chapter 4.

  5. Application-Dep. Data Type (VFP) • Visual FoxPro SQL Server • Abbreviation Data Type Data Type • ----------------------------------------- • C Character char • Y Currency money • D Date datetime • T DateTime datetime • B Double float • F Float float • G General image • I Integer int • L Logical bit • M Memo text • N Numeric float • P Picture image

  6. SQL for Relational Database • Data Definition Language (DDL) • Create table S … • Create view V … • Alter table S… • Drop table S • Data Manipulation Language (DML) • Insert into S … • Delete from S … • Update S set … • Query Language • Select – from – where

  7. DDL & DML (#1) create, insert • create database mydata; // mkdir d:\test (Visual Foxpro Command) • use mydata; //cd d:\test (Visual Foxpro Command) • create table S (SN char(5), sname char(20), status int, city char(15)); // create table S (SN char(5), sname char(20), status int, city char(15), primary key (`SN`), check status>0 ); • insert into S values('S1','Smith', 10, 'London'); • insert into S values('S2','Jones', 20, 'Paris'); • insert into S values('S3','Blake', 30, 'Paris'); • insert into S values ('S4','Clark', 40, 'London'),('S5','Adams', 50, 'Tokyo'); 藍色:Foxpro 指令 綠色:其他相關指令 MySQL - ; 表示提交

  8. DDL & DML (#2) update/delete, alter/drop • update S set city='Taipei' where SN='S5‘; • update S set city='Taipei' where city='Tokyo‘; • delete from S where SN='S1‘; • delete from S where where city='Tokyo‘; • alter table S add rec char(10); • alter table S modify rec integer; • alter table S drop rec; • drop table S;

  9. DDL & DML (#3) create view/ drop • create view temp as (select SN, sname, city from S where SN <> 'S4') ; • drop view temp; • Visual Foxpro Commands: • // select SN, sname, city from S where SN <> 'S4' into cursor temp • // select SN, sname, city from S where SN <> 'S4' into table temp • // drop table temp • // select SN, sname, city from S where SN <> 'S4' into array temp

  10. DDL & DML (#4) e.g. table P • create table P (PN char(5), pname char(20), weight int, color char(10)); //create table P (PN char(5), pname char(20), weight int, color char(10), primary key (`PN`)); • insert into P values('P1','PnameA', 151, ‘red'); • insert into P values('P2','PnameB', 122, ‘red'); • insert into P values('P3','PnameC', 143, ‘green'); • insert into P values('P4','PnameD', 114, ‘white');

  11. DDL & DML (#5) e.g. table SP • create table SP (SN char(5), PN char(5), price float(5,2), qty int); // create table SP (SN char(5), PN char(5), price float(5,2), qty int, primary key (`SN`,`PN`), foreign key (SN) references S(SN) on delete restrict on update cascade, foreign key (`PN`) references P(PN) on delete restrict on update cascade); • insert into SP values('S4','P2', 9.99, 420); • insert into SP values('S4','P3', 6.66, 430); • insert into SP values('S2','P2', 9.88, 220); • insert into SP values('S2','P4', 7.77, 240); • insert into SP values('S3','P3', 6.55, 330);

  12. Query Language (#1, set operators) • ------"Project" and "Restrict" ----------- • select * from S; • select SN, sname, city from S where SN <> 'S4' ; • select * from S where city like '%on%' or sname like 'B____‘; • select * from S where city is null and sname is not null; • ------"Union"----------- • select * from S union select * from S; • select * from S where status >= 30 union select * from S where status <=10; • select * from S where status >= 30 or status <=10; • ------ for "intersection" and "difference"----------- • select * from S where SN in (Select SN from SP); // 一般用法(有實際供貨) • select * from S where SN not in (Select SN from SP); // 一般用法(無實際供貨) • // select * from SA where SN in (select SN from SB); //用於交集(子公司A,B共同供貨商) • MySQL Ver 5. 不支援 Intersect, except 這兩個指令

  13. Query Language (#2, set operators) • ------"Cartesian Product" and "Join" ----------- • select * from S, P; • select SN, PN from S, P; • // ex: 紫微斗數資料窮舉, • // (命宮,星宿)是類似 S x P 的全部列表 • select * from S, SP; • select S.SN, sname, city, PN, price, qty from S, SP where S.SN=SP.SN; • // select SN, PN from S, SP; <--- why not? 因 SN名稱重複 • select S.SN, P.PN, Price, qty, weight from S, P, SP where S.SN=SP.SN and SP.PN=P.PN; • // 來自三個表格合組的大表格 S P SP

  14. Query Language (#3, qualifiers) • ------ qualifier: “union all”, “select distinct” ----------- • select * from S union all select * from S; //例如摸彩卷 // (Error) create view test as (select * from S union all select * from S); • select PN from SP; // P# 重複出現 因為不止一家賣 • select distinct PN from SP; • select PN, city from S, SP where S.SN=SP.SN; //調查P#可什麼地方有賣 • select distinct PN, city from S, SP where S.SN=SP.SN; • ------ qualifier: order by [asc/desc], limit ----------- • select * from SP order by PN; • select * from S,SP where S.SN=SP.SN and city= 'Taipei' order by Pricelimit 5; • select S.SN, sname, city, PN, price, qty from S, SP where S.SN=SP.SN order by PN asc, price desc; // P#遞增 P#同則 price 遞減

  15. Query Language (#4, adv. commands) • ------ advanced examples: as ----------- • select SN, PN, price, qty as boxes,price*qty as total from SP; // 欄位變名 • select S.SN, sname, city, PN, price, qty, price*qty as total from S, SP where S.SN=SP.SN; // 即使進行 Join 亦可使用欄位變名 • select First.SN, Second.SN from S as First, S as Second where First.city=Second.city and First.SN < Second.SN; // 表格變名 • ------ advanced examples: having ----------- • select S.SN, sname, city, PN, price, qty, price*qty as total from S, SP where S.SN=SP.SN having total > 2000; // having 是全部指令執行之後,用新完成的表單做後處理 // 上面指令在執行 having 條件之前變名已完成, 才可以引用 total 欄位 // 以下兩個指令結果相同,過程可能不相同 // select * from S where status >=20; // select * from S having status >=20;

  16. Query Language (#5, aggregation) • Aggregation Function (avg/min/max/sum/count, group by) • select * from SP where PN='P3'; • select count(*) from SP where PN='P3'; • select max(price) from SP; • select max(price) from SP where PN='P3'; • select PN, max(price) from SP group by PN; • select SN,PN, price, qty, (price*qty) as total from SP; • select SN, sum(price*qty) as fortune from SP group by SN; • select SN, sum(price*qty) as fortune from SP group by SN having fortune >5000 order by fortune desc; • 注意,group by 不保證「非其所指定的屬性」之值(例如PN之外的SN), 該值將會是 group 起來的所有tuples中的任何值。 • select PN, max(price), min(price) from SP group by PN; (保證PN) • select SN, PN, max(price) from SP group by PN;(不保證SN之值) • select * from SP where PN='P2'; (比較上例,查看SN之值) *如果 A B 查 group by A/B’s primary key, 則 A/B 各欄之值亦可保證。 • select PN, color, max(price) from SP, P where SP.PN=P.PN group by PN; Aggregation +group by 和 having 一樣是後處理指令 此例SN, #6, #7 示範各種 解決之道

  17. Query Language (#6, subquery) • Subquery (in, exists, some/all) • select SN, PN, price as max_price_of_PN from SP where (PN, price) in (select PN, max(price) from SP group by PN); • select SN, PN, price as max_price_of_PN from SP where (PN, price) = some (select PN, max(price) from SP group by PN); • select SN, PN, price as max_price_of_PN from SP where price >= all (select price from SP as SP2 where SP.PN=SP2.PN); • select SN, PN, price as max_price_of_PN from SP where price= (select max(price) from SP as SP2 where SP.PN=SP2.PN); • select * from S where SN in(select SN from SP); // 有實際供貨的廠商 • select * from S where exists (select * from SP where SP.SN=S.SN); // select * from S where status > all(select // 比全部有賣P6的廠商評價都高 // status from S,SP where S.SN=SP.SN and PN='P6'); // select * from S where status <= some(select // status from S,SP where S.SN=SP.SN and PN='P6');

  18. Query Language (#7, adv. SQL 1) • MySQL 5 不支援 “with” Clause • 但可用 “create view” 或 “from (…) as” 兩種方式代替 • with MAXP(tpn, tprice) as (select PN, max(price) from SP group by PN) select SN, PN, Price from SP, MAXP where SP.PN=MAXP.tpn and SP.price=MAXP.tprice; • create view MAXP as (select PN as tpn, max(price) as tprice from SP group by PN); select SN, PN, Price from SP, MAXP where SP.PN=MAXP.tpn and SP.price=MAXP.tprice; drop view MAXP; • select SN, PN, Price from SP, (select PN as tpn, max(price) as tprice from SP group by PN) as MAXP where SP.PN=MAXP.tpn and SP.price=MAXP.tprice;

  19. Query Language (#8, adv. SQL 2) • MySQL 5 不支援 “with” Clause • 但可用 “create view” 或 “from (…) as” 兩種方式代替 • with STA(PN, sprice, sqty) as (select PN, sum(price*qty), sum(qty) from SP group by PN) select PN, sprice/sqty as avg_price from STA; • create view STA as (select PN, sum(price*qty) as sprice, sum(qty) as sqty from SP group by PN); select PN, sprice/sqty as avg_price from STA; drop view STA; • select PN, sprice/sqty as avg_price from (select PN, sum(price*qty) as sprice, sum(qty) as sqty from SP group by PN) as STA;

  20. SQL for Relational Database • Data Definition Language (DDL) • Create table S … • Create view V … • Alter table S… • Drop table S • Data Manipulation Language (DML) • Insert into S … • Delete from S … • Update S set … • Query Language • Select – from – where