بنام خدا SQL

1. بنام خدا SQL

2. SQL : Structured Query Language DDL : Data Definition Language DML : Data Manipulation Language DCL : Data Control Language

3. 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 : First formalized by ANSI • SQL-89 : Minor revision • SQL-92 (SQL2) : Major revision • Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features.

4. SQL:1999 (SQL3 ): Added regular expression matching, recursive queries, triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features • SQL:2003 • SQL:2006 • SQL:2008 • SQL:2008R2 • SQL:2012 • Not all examples here may work on your particular system.

5. انواع داده ها • char(n) • varchar(n) • int (a finite subset of the integers that is machine-dependent). • smallint. • numeric(p,d) • Date • Time • Timestamp

6. (table) جدول An SQL relation is defined using the create table command: create table r (A1D1, A2D2, ..., An Dn,(integrity-constraint1), ..., (integrity-constraintk)) r is the name of the relation each Ai is an attribute name in the schema of relation r Di is the data type of values in the domain of attribute Ai

7. not null • primary key (A1, ..., An ) CREATE TABLE s (s# char(15), sname char(30), city char(30), status integer, primary key (s#));

8. CREATE TABLE SP (S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL, PRIMARY KEY (S# , P#), FOREIGN KEY (S#) REFERENCES S ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (P#) REFERENCES P ON DELETE CASCADE ON UPDATE CASCADE, CHECK (QTY>0 AND QTY<5001) ); Option : NO ACTION,CASCADE,SET DEFAULT,SET NULL

9. The drop table • The alter table alter table r add A D where A is the name of the attribute to be added to relation r and D is the domain of A. • All tuples in the relation are assigned null as the value for the new attribute. • The alter table command can also be used to drop attributes of a relation: alter table r drop A where A is the name of an attribute of relation r • Dropping of attributes not supported by many databases

10. بازيابي داده ها SELECT [DISTINCT] items FROM table(s) [WHERE condition(s)] [ORDER BY] [GROUP BY] [HAVING ]

11. مثال : مشخصات تهيه کنندگان ساکن شهر C2 را بدهيد ؟ SELECT S# , Sname , Status , City FROM S WHERE City =‘c2’ ; S# SNAME STATUS CITY S1 Sn1 20 C2 S4 Sn4 20 C2 SELECT * تمامي مشخصات را مي آورد و مخصوصا در مواردي که تعداد صفات خاصه زياد است مفيد است .

12. SELECT S#, City FROM S ; مثال : دو ستون از جدول را مي دهد . S# CITY S1 C2 S2 C3 S3 C3 S4 C2 S5 C1

13. SELECT P# FROM SP ; مثال : آيا اين يک Project است ؟ خير- زيرا تمام اپراتورهاي جبر رابطه اي حاصشان يک رابطه است و رابطه عنصر تکراري ندارد برای حذف رديف های تکراری بايد از گزينه DISTINCT استفاده کرد. SELECT DISTINCT P# FROM SP;

14. Qualifier ستون و يا Qualifier صفت خاصه ، قيد ستون مثال : SELECT S.S# , S.Status FROM S WHERE City=‘C2’ or City=‘C3’ ; در اين مثال نيازي به تصريخ قيد نيست ، مواردي وجود دارد که در آنها استفاده از Qualifier توصيه مي شود و گاه الزامي است .(توصيه در مواردي است که بخواهيد به Query وضوح ببخشيد. الزام وقتي است که ستونهاي همنام در جداول مختلف داشته باشيم .مثل : City در S,SP )

15. ابازيابيبا جدول جواب منظم Q : شماره تهيه کنندگان و وضعيت آنها را بدهيد. جدول جواب به نظم صعودي مقاديرstatus مرتب شده باشد. SELECT S# ,Status FROM S ORDER BY Status ORDER BY Status DESC ORDER BY2شماره ستون درجدول جواب ORDER BY 2 DESC ASCENDING صعودي default است و احتياج به ذکر نيست. ASCENDING نياز به تصريح ندارد.

16. بازيابي مقدار محاسبه شده • ًQ: شماره هر قطعه و وزن آنرا به گرم بدهيد. فرض کنيد DBA وزن را به واحد کيلو گرم ذخيره نموده . SELECT P# , WEIGHT*1000AS ‘WEIGHT IN GRAMS’ FROM P عبارت محاسبه شدني P# WEIGHT IN GRAMS P1 12000 P2 17000 P3 17000 P4 14000 p5 12000 p6 19000

17. SELECT P# , ‘WEIGHT IN GRAMS’ , WEIGHT*1000 FROM P عبارت محاسبه شدني P# P1 WEIGHT IN GRAMS 12000 P2 WEIGHT IN GRAMS 17000 P3 WEIGHT IN GRAMS 17000 P4 WEIGHT IN GRAMS 14000 p5 WEIGHT IN GRAMS 12000 p6 WEIGHT IN GRAMS 19000 اين دو ستون بي نامند.

18. پياده سازي اپراتور join: بازيابي از چند جدول Query: مشخصات قطعات و تهيه کنندگان از يک شهررا بدهيد. SELECT S.* , P.* FROM S,P WHERE S.CITY = P.CITY سيستم ابتدا ضرب کارترين دو رابطه را ايجاد مي کند. سپس tuple هاي حائز شرايط را استخراج مي کند. مي توان در عمل join شرايطي را نيز مطرح کرد عمل join (Natural Join) Equi Join) )

21. Q: مشخصات قطعات و تهيه کنندگان از يک شهر که قطعه آبي باشد. SELECT S.S# , P.P# FROM S,P WHERE S.CITY = P.CITY AND COLOR = ‘ BLUE ‘ ;

22. نکته : چندين جدول را مي توان Join کرد ولي توجه داشته باشيد که عمل Join زمان گير است . نکته : SQL ، ناروشمند است (Nonprocedural) . شما فقط مي گوييد چه مي خواهيد از چه جدولي اينکه به چه ترتيبي انجام شود را نمي گوييد. اما بطور ضمني روشمند است . Q : نام زوج شهرهايي را بدهيد که تهيه کننده شهر اول قطعه اي انبار شده در شهر دوم را توليد کند. SELECT DISTINCT S.CITY , P.CITY FROM S , SP , P WHERE S.S#=SP.S# AND P.P#=SP.P#

23. Joinيک جدول را با خودش • مثال : شماره جفت تهيه کنندگان همشهری را بدهيد S S# Sname Status City S# S# S1 . . C2 S2 . . C3 S3 . . C3 S4 . . C2 S5 . . C1 S1 S4 S2 S3 جدول جواب SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY = SECOND.CITY

24. از يک تکنيک دگر نامي استفاده شده است S را به نام First و به نام Second ناميده ايم . بعد از FROM هرگاه نام يک رابطه بيابيد و بعد از حداقل يک Blank ، يک نام بيايد ، سيستم آن نام را ، نام ديگر براي آن رابطه در نظر مي گيرد. S# S# حاصل SELECT : S1 S1 S1 S4 S2 S2 S2 S3 S3 S2 S3 S3 S4 S1 S4 S4 سيستم چه مي کند ؟ 2 تا CURSOR مي گيرد و دومي را MOVE مي دهد براي حذف بديهات و تکراريها شرط زير را اضافه کنيد : AND FIRST.S# < SECOND.S# بديهات تکراري

25. در نتيجه خواهيم داشت SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY = SECOND.CITY AND FIRST.S# < SECOND.S#

26. مثال : شماره جفت تهيه کنندگاني را بدهيد که از يک شهر نباشد SELECT FIRST.S# , SECOND.S# FROM S FIRST , S SECOND WHERE FIRST.CITY != SECOND.CITY AND FIRST.S# < SECOND.S#

27. ❑ INNER JOIN ❑ OUTER JOIN (both LEFT and RIGHT) ❑ FULL JOIN ❑ CROSS JOIN

28. INNER JOIN SELECT <select list> FROM <first_table> <join_type> <second_table> [ON <join_condition>] SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

29. SELECT a.au_lname + ‘, ‘ + a.au_fname AS “Author”, t.title FROM authors a JOIN titleauthor ta ON a.au_id = ta.au_id JOIN titles t ON t.title_id = ta.title_id

30. OUTER JOIN SELECT <SELECT list> FROM <the table you want to be the “LEFT” table> <LEFT|RIGHT> [OUTER] JOIN <table you want to be the “RIGHT” table> ON <join condition>

31. USE Northwind SELECT c.CustomerID, CompanyName FROM Customers c LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.CustomerID IS NULL SELECT a.Address, va.AddressID, v.VendorID, v.VendorName FROM VendorAddress va FULL JOIN Address a ON va.AddressID = a.AddressID FULL JOIN Vendors v ON va.VendorID = v.VendorID

32. CROSS JOINs SELECT v.VendorName, a.Address FROM Vendors v CROSS JOIN Address a

33. An Alternative INNER JOIN SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID SELECT * FROM Products, Suppliers WHERE Products.SupplierID = Suppliers.SupplierID

34. An Alternative OUTER JOIN SELECT discounttype, discount, s.stor_name FROM discounts d LEFT OUTER JOIN stores s ON d.stor_id = s.stor_id SELECT discounttype, discount, s.stor_name FROM discounts d, stores s WHERE d.stor_id *= s.stor_id

35. توابع تجمعي ( گروهي) Aggregate Functions COUNT (DISTINCT ( ... COUNT(*) MAX MIN SUM AVG COUNT(*)تمام سطرها را – حتي تکراري - مي شمارد.

36. مثال : تعداد محمولات را بدهيد .( هر سطر SP يک محموله است) (SHIPMENT) SELECT COUNT(*) FROM SP ; مثال : چند نوع قطعه تهيه شده است . SELECT COUNT (DISTINCT P#) FROM SP ; مثال : در کل چند نوع قطعه وجود دارد SELECT COUNT(*) FROM P ; مثال : SELECT MAX(STATUS) FROM S ;

37. استفاده از GROUP BY : Query : شماره هر قطعه تهيه شده و کل تعداد تهيه شده از هر قطعه را بدهيد . عبارت GROUP BY جدول داده شده بعد از FROM را منطقا گروه بندي مي کند به نحوي که در هر گروه مقدار ستون يا ستونهاي داده شده پس از GROUP BY يکسان است .آنگاه تابع SUM عمل مي شود. )ستونهايي که حاصل اعمال توابع هستند بي نام مي باشند( SELECT P# ,SUM(QTY) FROM SP GROUP BY P# ; S# P# QTY S1 P1 300 S2 P1 300 S1 P2 200 S2 P2 400 S3 P2 200 S4 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 P# P1 600 P2 1000 P3 400 P4 200 P5 100

38. Q: شماره قطعاتي را بدهيد که توسط بيش از يک تهيه کنندهتهيه شده باشد. نکته 1 : HAVING هميشه با GROUP BY مي آيد و مستقل معني ندارد. نکته 2: استفاده از GROUP BY ناروشمندي SQL را کمي تضعيف مي کند. ( زيرا مي گوييد گروهبندي کن )و آنرا تا حدي روشمند ميکند. نقش having در گروه همان نقش where در tuple را دارد. Where براي بيان شرط يا شرايط ناظر به سطر است. having براي بيان شرط يا شرايط ناظر به گروه است . SELECT P# FROM SP GROUP BY P# HAVING COUNT ( * ) > 1 ;

39. شماره هر تهيه کننده و تعداد کل قطعاتي را که تهيه کرده بدهيدمثال : select s#, sum(QTY) from sp group by s# مثال :شماره تهيه کنندگاني را بدهيد که دو نوع قطعه تهيه کرده اند select s# from sp group by s# having count(*)=2

40. شماره هر تهيه کننده و تعداد انواع قطعاتي که تهيه کرده را در مورد تهيه کنندگاني که بيش از يک قطعه تهيه کرده اند ،بدهيد select s#, count(*) from sp group by s# having count(*)>1 در مورد قطعاتي که توسط سه تهيه کننده تهيه شده اند شماره قطعه و تعداد کل تهيه شده از قطعه را بدهيد select p#, sum(qty) from sp group by p# having count(*)=3

41. پرس و جو هاي فرعي (SUBQUERY) Q : اسامي تهيه کنندگان قطعه P2 را بدهيد . روش اول : با استفاده از مکانيزم Join : SELECT Sname FROM S , SP WHERE SP.S# = S.S# AND SP.P# = ‘P2’ ;

42. SUBQUERY همان ساختار SELECT – FROM – WHERE را دارد با اين تفاوت که در درون يک SELECT ديگر نوشته مي شود. SELECT Sname FROM S WHERE S# IN (S# = ANY (يا ( SELECT S# FROM SP WHERE P# = ‘ P2 ‘ ) OUTER QUERY S1 , S2 , S3 , S4 INNER QUERY پرس و جوي فرعي سيستم از دروني ترين SELECT شروع ميکند.

43. نکته : در بعضي از نسخه هاي SQL اپراتوري به نام ANY بصورت کلي Theta ANY پيش بيني شده است که Theta يکي از اپراتورهاي متعارف مقايسه است. ANY به معناي SOMEONE است. • عملکرد ANY = با عملکرد IN يکسان است. • مثال : اسامي تهيه کنندگاني که حداقل يک قطعه آبي رنگ تهيه مي کنند. SELECT Sname FROM S WHERE S# IN ( SELECT S# FROM SP WHERE P# IN ( SELECT P# FROM P WHERE P.COLOR = ‘BLUE ‘ ) ( ;

44. با استفاده از JOIN SELECT S.SNAME FROM S , SP , P WHERE S.S# = SP.S# AND SP.P# = P.P# AND P.COLOR = ‘BLUE’

45. نکته : مي توان بجاي اپراتور IN يا =ANY در شرايط خاصي از همان اپراتورهاي متعارف مقايسه اي استفاده کرد. SELECT COL1 FROM table WHERE COL2 = ( SELECT ….. > < مثال : شماره تهيه کنندگان هم شهر با ‘S1’ را بنويسيد . SELECT S# FROM S WHERE CITY = ( SELECT CITY FROM S WHERE S# = ‘ S1‘ ) * شرط: وقتي که پاسخ SELECT دروني يک مقدار باشد يا بعبارت ديگر مجموعه جواب SELECT دروني تک عضوي باشد .بنابراين SELECT S# FROM S WHERE CITY = ‘ C2 ‘

46. مثال: شماره تهيه کنندگاني را بدهيد که وضعيت آنها ماکزيمم نباشد. SELECT S# FROM S WHERE STATUS < ( SELECT MAX ( STATUS ) FROM S ) ; نکته : استفاده از توابع جمعي در SUBQUERY ها مجاز است.

47. راه حل ديگر در بعضي نسخه ها ي SQL که Anyدارند (مانند SQL SERVER) SELECT S# FROM S WHERE STATUS < ANY (SELECT STATUS FROM S) جواب اين قسمت خود يک ستون STATUS است . پس : SELECT S# FROM S WHERE STATUS < ANY {20,10,30} اگر وضعيت از يکي از اينها کمتر باشد جواب است ( زيرا ديگر ماکزيميم نيست ) مقادير ستون STATUS

48. Like • برای مقایسه بر روی رشته ها • percent (%). The % character matches any substring. • underscore (_). The _ character matches any character. Q : شماره قطعاتي را بدهيد که اسم آنها با کاراکتر C شروع شده باشد . يعني آغاز شونده با C LIKE ‘%C’ يعني مختوم به حرف C يعني اين String وجود داشته باشد مهم نيست کجا ‘CAB--’ يعني 5 حرف باشد و 3 حرف اولش CAB باشد . SELECT P# FROM P WHERE PNAME LIKE ‘C%’; WHERE PNAME LIKE ‘%CAB%’

49. Match the name “Main%” like‘Main\%’escape ‘\’ • SQL supports a variety of string operations such as • concatenation (using “||”) • converting from upper to lower case (and vice versa) • finding string length, extracting substrings, etc.

50. SELECT S# FROM S WHERE STATUS > 13 ; جدول جواب SQL ،NULL را به عنوان يک عملوند مقايسه در عمل مربوط دخالت نمي دهد . مانند اين است که مقايسه صورت نگرفته است . اما اگر بنويسيم : SELECT S# FROM S WHERE STATUS IS NULL جدول جواب