زبان SQL

1. زبان SQL

2. زبان بیانی SQL • زبان SQL پیاده سازی آزادی از جبر رابطه ای است • این زبان اولین بار در سال 1976 پدید آمد • ویک زبان بیانی است یعنی کاربر آنچه را لازم دارد با دستورات محدودی بیان می کندوسیستم آن را تفسیر می کند وروال کاررا تشخیص وانجام می دهد. • این زبان در حین اجرای دستورات کاربر،بسیاری از خطاهای اورا تشخیص می دهد وازبروز آنها جلوگیری می کند.

3. تعریف داده • زبان SQL از دو بخش تشکیل شده است : • DDL: زبان تعریف داده ها • DML:زبان کار کردن با داده ها • هر بخش به طور جداگانه بررسی می شود. • الف :دامنه متغیر • در تعریف داده باید نام،صفت ها،کلیدهای جدول وکنترل های لازم روی صفت ها مشخص شود.

4. تعریف بانک اطلاعات • برای تعریف بانک اطلاعات از دستور زیر استفاده می شود: CREATE DATABASE db_name AUTHORIZATION dba_name; مثال : بانک اطلاعات واجازه دستیابی به آن به صورت زیر است : CREATE DATABASE univer AUTHORIZATION yazdani;

5. تعریف جدول جدول درس زیر را در نظر گرفته و جدولی را تعریف می کنیم Crs(c#,cname,unit,clg#) C# :کددرس تا 5 کاراکتر Cname: نام درس تا 30 کاراکتر unit: تعداد واحددرس 1 تا 4 Clg#: کلید خارجی است که به جدول clg اشاره می کند که دو رقمی است .

6. تعریف جدول CREATE TABLE crs (c# char(5) NOT NULL, cname char(30) NOT NULL, unit Smallint NOT NULL, clg# Smallint, PRIMARY KEY(c#), UNIQUE(cname), FOREIGN KEY (clg#) REFERENCES clg ON DELETE CASCADE, ON UPDATE CASCADE, CHECK(unit>0 AND unit<5));

7. توضیحات • تعریف صفت ها مشخص است.تنها برای clg# قید NOT NULL نیامده به معنی این است که اگر درسی را وارد کنیم می توانیم دانشکده آن را قید نکنیم • دستور PRIMERY KEY کلید اصلی جدول است تا اینجا،تعریف جدول اجباری وادامه آن اختیاری است. • دستور UNIQUE کلید فرعی است نمی تواند تکراری باشد. • نکته :کلید فرعی برخی از کاربردها رادارد مثلا در جدول دانشجو شماره دانشجویی به عنوان کلید اصلی است ونام دانشجو همراه با صفت دیگری به عنوان کلید فرعی است مثلا برای تهیه لیست کلاس مورد استفاده قرار می گیرد می توانیم چندین کلید فرعی داشته باشیم.

8. توضیحات • دستور FOREIGN KRY کلید خارجی را مشخص می کندو با ذکر REFERENCES تعیین می کند که این کلید به کدام جدول ارجاع می شود. • قیدهای ON DELETE CASCADE و ON UPDATE CASCADE می گوید اگر زمانی این کلید در جدول اصلی خودش حذف شد یا تغییر کرد،در این جدول مشابه آن انجام شود .مثلا اگر کد از 00 به 30 تغییر کند تمام درسهایی که در جدول crs به این دانشکده مربوط می شوند نیز کددانشکده شان عوض شود • دستور CHECK برای بیان قواعد جامعیتی است .

9. تغییر شکل جداول • ممکن است جدولی که تعریف کرده ایم بخواهیم تغییر دهیم که با دستور ALTER TABLE امکان پذیر است. • تغییر نوع داده :بخواهیم نوع داده ستون ها یا ستونهایی را تغییر دهیم ALTER TABLE table-name MODIFY(<col-name><new type>); مثال : نام دروس را در جدول درس به 40 کاراکتر افزایش می دهد. ALTER TABLE crs MODIFY(cname CHAR(40)); نکته : اگر بخواهیم نوع داده را به طور کلی تغییر دهیم اکثر نسخه های SQL جلوگیری می کنند مگر آنکه ستونهای مربوطه فاقد داده باشند.

10. تغییر شکل جداول • افزودن صفت • می توان صفت (ستون) یا صفت هایی به جدول افزود. ALTER TABLE table-name ADD(<col-name><type>); مثال: ستون text را با فرمت Char(100) به جدول crs اضافه می کند. ALTER TABLE crs ADD(text Char(100)); حذف ستون: به طور مستقیم پیش بینی نشده زیرا ممکن است تاثیر جبران ناپذیری روی ارتباط جداول با یکدیگر بگذارد .می توان جدول جدیدی ایجاد کرد فاقد ستون های مورد نطر باشدوسپس ستونهایی را که باقی می مانند ازجدول قدیم به جدول جدید کپی کرد.

11. به روز درآوردن جدول • وارد کردن داده ها با دستورINSERT انجام می شود. INSERT INTO table-name VALUES(list of values) مثال: INSERT INTO crs VALUES(“10147”,”BANK”,3,10); INSERT INTO crs VALUES(“10152”,”BANK1”,3, ); کددانشگاه ثبت نشده اشکالی ندارد.

12. وارد کردن چندسطر با برداشتن از جدول دیگر • فرض کنید جدولی به صورت زیر داریم. CREATE TABLE profession (esg….. deg……); دو ستون ”تخصص“ و“مدرک تحصیلی“ را از جدول استاد برداریم ودر این جدول کپی کنیم. INSERT INTO profession SELECT esp,degree FROM prof;

13. حذف داده ها • شکل کلی دستور DELETE FROM table-name WHERE……; دستورWHERE برای شرط به کار می رود. مثال: DELETE FROM sec WHERE score IS NULL; تمامی اطلاعات مربوط به دروسی که برای آنها نمره مشخص نشده است را حذف می کند. کل جدول را نیز از بانک اطلاعات مربوط حذف می کند. DROP table-name

14. تغییر داده ها در جدول • تغییر داده ها در جدول با دستورUPDATE انجام می شود. UPDATE table-name SET attr1=value1,attr2=value2,….. WHERE….. مثال:تبدیل واحد درس“BANK1“ به 4 تبدیل می شود. UPDATE crs SET unit=4 WHERE cname=‘BANK1’ تبدیل شماره گروه به 10 ونمره به صفر برای موارد تهی : UPDATE sec SET sec#=10,score=0 WHERE SEC# IS NULL;

15. گزینش وپرتووپیوند • این سه دستور با هم ترکیب و دستور SELECT را درست می کنند. SELECT col1,……….coln FROM tab1,……….,tabm WHERE …… مثال:اطلاعات کامل دانشجویان تبریز در دانشکده مهندسی با کد 3 حل: برای گرفتن تمام ستونهای جدول از دستورSELECT * استفده می کنیم. SELECT * FROM stud WHERE city=‘tabriz’ AND clg#=3;

16. گزینش وپرتووپیوند • مثال:نام ونمره دانشجویان در دروس مختلف. • حل : اگر کددرس را می خواهیم دو جدول نیازداریمstud وsec که دو جدول با هم پیوند طبیعی می شوند که در SQL با شرط تساوی صفات مشترک (دراینجاS# )انجام می شود.اگر نام جدول را بخواهیم باید سه جدول را پیوند دهیم. شرط تساوی صفات مشترک دو به دو می آید. دستور1: فقط کددرس SELECT sname,c#,score FROM stud,sec WHERE stud.s#=sec.s#; • دستور 2:نام درس SELECT sname,cname,score FROM stud,crs,sec WHERE stud.s#=sec.s# AND sec.c#=crs.c#;

17. گزینش وپرتووپیوند • مثال : نام وشماره دروسی که از سال 1375 به بعد ارئه شده اند. حل :دراین مثال خروجی تکراری داریم.یعنی اگردرسی در نیمسالی در 5 گروه درسی ارائه شده باشد.اطلاعاتش عینا 5 باردرخروجی تکرار می شودوبرای جلوگیری ازاین تکرار بی موردازکلمه DISTINCT استفاده می شود.وچون c# در دو جدول آمده، لازم آن را با نقطه گذاری مشخص کنیم. SELECT DISTINCT cname,crs.c#,term FROM crs,sec WHERE crs.c#=sec.c# AND term>750;

18. مرتب کردن خروجی • برای مرتب کردن ازدستور ORDER BY استفاده می کنیم.ودر پایان می آید. اگر DESC را اضافه کنیم نزولی مرتب می کند.ولی اگر ASC را اضافه کنیم صعودی مرتب می کند. SELECT DISTINCT cname,crs.c#,term FROM crs,sec WHERE crs.c#=sec.c# AND term>750; ORDER BY cname DESC;

19. دستورUNION و INTERSECTوEXCEPT • دستورات اجتماع واشتراک وتفاضل مثال: نام وکددانشکده همه افراد اعم از دانشجو یا استاد SELECT sname,clg# FROM stud UNION SELECT pname,clg# FROM prof; دستورات بالا تمامی افراددانشکده ها اعم از دانشجوواستاد را همراه با کددانشکده می دهد.اگربه جای UNION از INTERSECT استفاده کنیم اسامی دانشجویان واساتید همنام را می آوردواگرEXCEPT به کار بریم اسامی دانشجویان بجز آنهایی که با اساتید همنام هستند را می دهد.دستورزیر اسامی ودانشکده اساتید،بجز آنهایی که با دانشجویی در دانشکده خودشان همنام هستند را می دهد. SELECT pname,clg# FROM prof EXCEPT SELECT sname,clg# FROM stud;

20. دستور IN • مثال:دانشکده هایی که در شهرهای تهران ،اصفهان،شیراز ورشت واقعند. SELECT * FROM clg WHERE city IN(‘رشت’,’شیراز’,’اصفهان’,’تهران’); دانشجویانی که شهرهای تولدشان محل های فوق نیست. SELECT * FROM clg WHERE city NOT IN(‘رشت’,’شیراز’,’اصفهان’,’تهران’); شماره دروسی که در سالهایی بجز 71و75 اعلام شده. SELECT * FROM sec WHERE term NOT IN(‘711’,’712’,’751’,’752’); اساتیدی بجز مولوی وفتوت که نمره 20 داده اند. SELECT * FROM prof WHERE pname IN(SELECT pname FROM sec WHERE score=20 AND pname NOT IN(‘مولوی’,’فتوت’) );

21. توابع محاسباتی • COUNT,MIN,MAX,AVG,SUM مثال: تعداد دانشجویان تهرانی. SELECT COUNT(s#) FROM stud WHERE city=‘تهران’; مثال: تعداد سطرهای جدول درس. تابع ویژه COUNT(*) برای شمارش سطرهای جداول تعریف شده است در این تابع نمی توان از DISTINCT استفاده کرد وسطرهای NULL را هم می شمارد SELECT COUNT(*) FROM crs;

22. توابع محاسباتی • مثال : حداقل میانگین نمره دانشجویان . SELECT MIN(avg) AS Lowest_Avg FROM stud دانشجویانی که میانگین آنها از میانگین همه میانگین ها بیشتر است . SELECT sname.avg FROM stud WHERE avg>(SELECT AVG(avg) FROM stud); ابتدا زیر دستور که درون پارانتز نوشته می شود اجرا وسپس دستور اصلی اجراء ومقدار avg هر سطر با این عدد صحیح مقایسه می شود. مثال : نام اساتید دانشجویانیکه میانگین آنها ازمیانگین همه میانگین ها بیشتر است. SELECT DISTINCT pname.s# FROM sec WHERE s# IN(SELECT s# FROM stud WHERE avg>(SELECT AVG(avg) FROM stud));

23. گروه بندی • تفکیک داده ها در SQL با دستور GROUP BY انجام می شود. بخش GROUP BY جزو آخرین بخشهای یک دستوراست. • مثال:ماکزیمم ومینمم میانگین دانشجویان در هر دانشکده. SELECT clg#,MAX(avg),MIN(avg) FROM stud GROUP BY clg#;

24. شرط درون گروه ها • در درون گروه ها هم می توان شرط گذاشت. این کار با قید HAVING انجام می شود که شبیه WHERE عمل می کند. در بخش HAVING ،یک صفت دارای یک مقدار نیست،بلکه دارای مجموعه ای از مقادیر است.چون بعد از گروه بندی شرط تست می شود،تمامی مقادیر مربوط به آن صفت تحت نام آن صفت قرار می گیرند مثلا وقتی c# بیان می شود تمامی مقادیر مربوط به c# در داخل گروه ها مدنطر است. • مثال : شماره دانشکده هایی که تعداد اساتید آنها بیش از بیست نفر می باشد. SELECT clg# FROM prof GROUP BY clg# HAVING COUNT(pname)>20;

25. شرط درون گروه ها مثال:دروس سه واحدی که در ترمی بیش از5 گروه ارائه شده اند. عملگرهای دیگر عملگر LINK : برای کار با رشته ها است.با استفاده از آن می توان رشته را بررسی کرد. • کاراکتر“_“ یعنی به جای هر کاراکتری • کاراکتر“%“ یعنی به جای هر تعداد کاراکنر

26. عملگرهای دیگر ثال : درس های بانک اطلاعات که سه واحدی یا چهار واحدی هستند؟ SELECT * FROM crs WHERE unit=3 OR unit=4 AND cname LINK ‘%بانک اطلاعات%’ مثال: اساتیدی که نام اول آنها ”علی“ است. SELECT pname FROM prof WHERE pname LINK ‘%علی’ مثال : دروسی که کد آنها سه کاراکتری است وبا DB شروع می شود مثلDB1 SELECT c#,cname FROM crs ; WHERE c# LINK ‘DB_‘ کدهای مثل DBOO در آن قرار ندارد زیرا کاراکتر’_’ برای جایگزینی یک کاراکتر است.

27. عملگرهای دیگر دانشجویانی که معدل آنها از همه دانشجویان کامپیوتر (کد10) بیشتر است. SELECT * FROM stud WHERE avg>ALL(SELECT avg FROM stud WHERE clg#=10); دانشکده هایی که دانشجو دارند. SELECT * FROM clg WHERE clg#=ANY (SELECT clg# FROM stud);

28. امنیت در SQL2 • هرچه بانک بزرگتر وکاربردهای آن بیشتر باشد حساسیت بیشتر وتامین امنیت بانک پیچیده تر می شود. وهر یک از کاربران فقط به بخشهایی از داده ها نیاز دارند • هر کاربر فقط دید خود راداردیعنی تنها به آنچه نیازدارد دسترسی می یابد واز مابقی بانک بی خبر ومحروم است . تنها مدیر بانک وبرخی از برنامه سازان می دانند در کل بانک چه می گذرد. • امتیازهای اصلی که برای دستیابی به جداول در نظر گرفته شده عبارتند از: UPDATE,DELETE,INSERT,SELECT • امتیاز REFERENCES برای کنترل واعمال محدودیت های جامعیتی است.مثلا در یک سازمان دولتی ،رئیس سازمان باید بتواند روی دستمزدکارمندان خود محدودیت بگذاردولی سایرین چنین حقی ندارند. • امتیاز USAGE برای استفاده از امکاناتی ازقبیل VIEW در نظر گرفته شده است. • امتیازهایی چون REFERENCES,UPDATEوINSERT می توانند برای بخشی از جدول داده شوند.

29. امنیت در SQL2 GRANT<لیست امتیازها> ON <بخشی از بانک اطلاعات> TO<لیستی از کاربران>[WITH GRANT OPTION] • اگر قید WITH GRANT OPTION ذکرشود،معنای آن این است که کاربر می تواند این امتیازها را به کاربران دیگر نیز واگذار کند(با دستورGRANT دیگری) مثال: GRANT INSERT,SELECT,UPDATE ON crs,sec TO karimi,zamani; GRANT UPDATE(degree,esp) ON prof TO naime; GRANT SELECT ON iust_db TO rahmani WITH GRANT OPTION;

30. امنیت در SQL2 • امتیازها را می توان با دستور REVOKE باز پس گرفت. REVOKE<لیست امتیازها> ON<بخشی از بانک اطلاعات> FROM<لیست کاربران>; • می توان حق واگذاری به غیر را با دستور REVOKE GRANT OPTION ON باز پس گرفت. REVOKE INSERT ON src,sec FROM karimi,zamani; REVOKE GRANT OPTION ON iust_db FROM rahmani;