1 / 20

Unit 3 SQL

Unit 3 SQL. 英文版： Chap 3 “SQL” 中文版：第 4 章 “結構化查詢語言”. 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)

onofre
Download Presentation

Unit 3 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related