SQL زبان پرس و خٍي
: ضامن دو بخشSQL زبان
: زبان تعريف دادى
DDL:
Data Definition Language
: زبان مار مردن با دادى
DML:
Data Manipulation Language
1
داموي متغيرًا
:داموي ًاي همٍهي
Integer
Logical
Decimal (p, q) or Numerical (p, q)
Real
Date
Time
…
2
تعريف باهك اظالعات
: تعريف باهك اظالعات خديد با دستٍر
CREATE DATABASE db_name AUTHORIZATION dba_name
: مثال
Create database university authorization amini
3
تعريف خداول
در تعريف خدول، هام خدول ، هام ظفت ًا ، داموي اهٌا ، مليد ًاي خدول و موترل ًاي الزم روي
ظفت ًا مصخط مي ضٍد.
تعريف خدول با دستٍر زير اهحام مي گيرد:
4
(CREATE TABLE tbl_name
, ]attr domain [NOT NULL[
…
,] PRIMARY KEY (the key) [
,]UNIQUE (alternative key) [
]FORIGN KEY (key) REFERENCES (ref_tbl)[
]ON DELETE CASCADE [
,]ON UPDATE CASCADE [
…
,] FORIGN KEY […
]CHECK(condition)[);
5
را تعریف کوید؟crs مثال :خدول
Create table CRS ( c#,cname,unit,clg)#
,c# int NOT NULL(
,cname char(30) NOT NULL
,unit Smallint NOT NULL
,clg# Smallint
,primary key (c#)
,unique (cname)
forigen key (clg#) references clg
,on delete cascade
,on update cascade
check ( unit > 0 and unit insert into Goodstud select * from Stud where avg
51
حذف كزدن داده
DELETE FROM table-name WHERE condition
مثال : داهصحٍيان معدل زير 01 را از خدول Studحذف مويد؟
01 =19
UPDATE sec 2 روش
SET score=score+1
WHERE score=17 and city
91
مثال : شماره و نام دانشجوياني كه متولد قزوين يا همدان هستند و معدل
الف دارند؟
SELECT s#,sname FROM stud WHERE avg >=17 and
) ” همدان“=” or cityقزوين“=(city
موود؟ مثال : مصخعات داهصحٍياهي مي در داهصلدى ضمارى 01 يا51تحعين مي
51=#SELECT * FROM stud WHERE clg#=10 OR clg
مثال : اسامي شهر هايي كه دانشجويان متولد آن شهر ها هستند؟
SELECT city FROM stud
02
نكته : خروجي SELECTسطر تكراري دارد
نكته : در صورتي كه بخواهيم خروجي selectسطر تكراري نداشته باشد
از دستور distinctاستفاده مي كنيم .
مثال:
SELECT DISTINCT city FROM stud
12
مزتب ساسي:
ORDER BYبراي مرتب كردن خروجي از بخش SELECTاستفاده مي كنيم
كه آخرين بخش دستور است.
نكته :
با استفاده از قيد Ascمرتب سازي به صورت صعودي انجام مي گيرد.
با ستفاده از قيد Descمرتب سازي به صورت نزولي انجام مي گيرد.
پيش فرض ORDER BYمرتب سازي صعودي است.
22
مثال : شماره و نام دانشجويان از جدول studرا بر اساس نام مرتب کنيد؟
SELECT s#,sname FROM stud ORDER BY sname
نكته: مرتب سازي با بيش از يك صفت مي تواند انجام گيرد
مثال : شماره دانشجو , نام دانشجو ,شماره درس و ترمي كه در آن دانشجويان ثبت
نام كرده اند را نشان دهيدو بر اساس ترم به صورت نزولي و شماره دانشجوئي به
صورت صعودي مرتب سازي كنيد ؟
SELECT stud.s#,sname,c#,term FROM stud,sec
#WHERE stud.s# =sec.s
ORDER BY term DESC , stud.s# ASC
32
عملگر ًاي محمٍعي اي:
عملگر هاي مجموعه اي جبر رابطه اي عينا در جبر SQLوجود دارند.
Union………………………………………. U
∩...…………………………………… Intersect
- ……………………………………… Except
عالوه بر آنها دو عملگرعضويت با دستور Inو شامل بودن با
دستور Containsتعريف شده اند.
مثال: اسامي دانشجويان و اساتيد
SELECT sname FROM stud Union SELECT pname FROM prof
42
مثال: اسامي استادان و دانشجويان همنام
SELECT sname FROM stud Intersect SELECTpname FROM prof
مثال: اسامي دانشجوياني كه استاد همنام ندارند
SELECT sname FROM stud Except SELECT pname FROM prof
مثال: مشخصات دانشجوياني كه متولد يكي از شهر هاي تهران يا مشهد يا تبريز هستند؟
)"تبريز”,”مشهد ”,”تهران”( SELECT * FROM stud WHERE city IN
مثال : مشخصات دانشجوياني كه در دانشكده هاي قم تحصيل ميكنند؟
SELECT * FROM stud WHERE clg# IN(SELECT clg# FROM clg WHERE
)”قم“=city
52
Nested Query
:Nested Queryيك دستور selectدر داخل دستورselect
ديگر را مي ناميم.
پرس و جوي داخلي را پرس و جوي فرزند مي ناميم.
پرس و جوي خارجي را پرس و جوي مادر مي ناميم.
دو نوع پرس و جوي فرزندي داريم :
مستقل: زير پرس و جويي كه هيچ ارجاعي به پرس و جوي
مادر نداشته باشد.
وابسته: زير پرس و جويي كه حداقل يك مورد ارجاع به
پرس و جوي مادر داشته باشد.
62
حوسه اعتبار اسامي
اسامي پرس و خٍي مادر در زير پرس و خٍ معتبر است.
اسامي پرس و خٍي فرزهد در مادر فاقد اعتبار است.
ارخاعات و اسامي بي هزديك ترين محن تفسير مي ضٍد.
هحٍى اخراي زير پرس و خٍ ًا
اگر زير پرس و خٍ مستقن باضد، در اين ظٍرت زير پرس و خٍ فقط يك بار در ابتدا اخرا مي ضٍد و هتايج ان در
خدول مياهي ذخيرى مي ضٍد و ًر خا مي هتايج زير پرس و خٍ ًا الزم باضد از خدول مياهي استخراج مي ضٍد در اين
حالت زير پرس و خٍ فقط يك بار مي ايد.
اگر زير پرس و خٍ وابستي باضد در اين ظٍرت پرس و خٍي مادر اخرا مي ضٍد.
72
هحٍى اخراي زير پرس و خٍ ًا
مثال : مصخعات داهصحٍياهي مي در ضٌر محن تٍلد ضان تحعين مي موود؟
#SELECT * FROM stud WHERE clg# IN(SELECT clg
)FROM clg WHERE clg.city=stud.city
82
توابع حماسبايت
تٍابع محاسباتي عبارتود از:
COUNT,MIN,MAX,AVG, SUM
مقادير NULLقبن از اخراي اين تٍابع حذف مي ضٍهد.
در ظٍرت لزوم با استفادى از DISTINCTمقادير تلراري را حذف مي مويم.
مقادير تلراري براي MIN,MAXتاثير هدارد.
مثال : تعداد داهصحٍياهي مي معدل الف دارهد ؟
71=> select COUNT (s#) from stud where avg
هل تي : ًوگام ضمردن تعداد سعر ًا مليد اظلي را مي ضمريم.
92
مثال : تعداد من درس ًا ؟
Select count (*) from crs
مثال : تعداد دروسي مي در ترم 148 ارائي ضدى اهد ؟
Select count(distinct c#) from sec where term=841
مثال : معدل ضاگرد اول داهصلدى مامپيٍتر؟
Select max( avg ) from stud where clg# in(Select
clg# from clg where clgname=“ ("مامپيٍتر
30
مثال : معدل ضاگرد اول داهصلدى مامپيٍتر؟
Select max(avg) from stud,clg where
stud.clg#=clg.clg# and clgname=““ مامپيٍتر
مثال : هام ضاگرد اول داهصلدى ضمارى 01؟
Select sname from stud where clg#=10 and avg in (select
max (avg) from stud where clg#=10)
. غير محازاستWhere هل تي : استفادى از تٍابع محاسباتي در بخش
31
مثال: معدل ترم گذضتي داهصحٍئي بوام ًاضم هسب؟
Select sum(unit+score)/sum unit
from stud,sec,crs
where (stud.s#=sec.s#) and (term=841) and
(sec.c#=crs.c#) and (sname=“ ( “ ًاضم هسب
32
گزوه بنذي داده
دستٍر گروى بودي در SQL
GROUP BY
محن قرار گيري دستٍر گروى بودي
ORDER BYو قبن از WHEREبعد ازGROUP BY
33
گزوه بنذي
هلات:
1. ظفتي مي گروى بودي روي ان اهحام مي ضٍد بايد در خروخي سلکت بيايد.
تبعرى: بعضي از DBMSاخازى مي دًود مي ظفت گروى بودي ضدى در
خروخي ظاًر ضٍد.
1. غير از ظفت گروى بودي ضدى بقيي ظفات خروخي بايد با يلي از تٍابع محاسباتي ًمراى ضٍهد.
2. براي ًر مقدار از ظفت گروى بودي ضدى فقط يك مقدار در خروخي ظاًر مي ضٍد.
43
مثال : ضاگرد اول ًر يك از داهصلدى ًا؟
#Select clg#,max(avg) from stud group by clg
اهحام مي روي گروى ًا هيز مي تٍان ضرط گذاضت اين مار با استفادى ازضرطhaving
گيرد.
Having بعد از group byهٍضتي مي ضٍد .
عمللرد havingضبيي whereمي باضد.
مثال : ضمارى دروسي مي در ترم گذضتي بيش از 05 داهصحٍ در ان ثبت هام مردى است؟
#Select c# from sec where term=841 group by c
05>)#having count(s
53
عملگز ىاي ديگز
1. عملگر like
اين عملگر براي مقايسي و بررسي رضتي ًا بلار مي رود .
دو مارام تر ويژى دارد:
“بي خاي ًر مارام تر “ مارام تر ” _“
.“بي خاي ًر تعداد مارام تر “ مارام تر “%“
63
مثال های عملگر like
مثال : ًمي درس ًاي باهك اظالعات؟
باهك اظالعات%” %“ Select * From crs Where cname LIKe
مثال : اساتيدي مي هام اول اهٌا علي است؟
علي” %“ select pname from prof where pname like
مثال : دروسي مي مد اهٌا سي مارام تري است و با ” ” DBضروع میصٍهد ؟
”_select c#,cname from crs where c# like “DB
73
مثال : دروسي مي مد اهٌا با ” “OOضروع مي ضٍهد؟
خٌت حن تٍخي بي دو هل تي:
مملن است بي يلي از ظٍرت ًاي )O_Oيا O Oيا O-Oيا ( OO
در دادى ًا امدى باضود .
مملن است از حروف بزرگ يا مٍچك يا ًر دو استفادى مود.
Select c#,cname From crs Where UPPER(c#) like “OO%” OR
”%UPPER(c#) like “O_O
83
2 . سايز عملگز
EXISTS……………………………….IS NULL
NOT EXISTS……………………. NOT NULL
. هل تي : وخٍد يا عدم وخٍد يك سعر مامن را بررسي مي موود
مثال : داهصلدى ًاي ي مي درسي ارائي همي دًود؟
Select * From clg Where NOT EXISTS(select c#
from crs where clg.clg#=crs.clg#)
39
قوانني:
عملگر ANDمقدار ميويمم را بر مي گرداهد.
عملگر ORمقدار مامزيمم را بر مي گرداهد.
هتيحي عملگر NOTبرابر ( مقدار - 1)است .
عملگرALLبراي مقايسي ًمي مقادير .
عملگر ANYبراي مقايسي ًر يك از مقادير .
04
مثال : داهصحٍ ياهي مي معدل اهٌا از ًمي داهصحٍيان مامپيٍتر بيصتر است؟
select * from stud where avg > ALL(select avg from
)01=#stud where clg
مثال : داهصلدى ًاي ي مي مد اهٌا عددي فرد و يك رقمي است؟
)9 , 7 , 5 , 3 , 1(select * from clg where clg#=ANY
مثال : هام درس ًاي ي مي با تعداد واحد متفاوت تعريف ضدى اهد ؟
select C.name from crs C, crs where
C.name=crs.name AND Cunitcrs.unit
14
مثال : داهصحٍيان ممتاز و داهصلدى ًاي اهٌا؟
select V.sname,S.clg# from verygoodstud V,stud S
where V.s#=S.s#
42
امنيت در SQL
ًر ماربر ضواسي خاص خٍد را دارد و ًر ضواسي داراي امتيازات:
SELECT,INSERT,DELETE,UPDATEوREFERENCES
مي براي موترل و اعمال محدوديت ًاي خامعيتي است.
USAGE براي استفادى از املاهاتي از قبين VIEW
دادن ًريك از امتيازات بر ضمردى ضدى با دستٍر GRANاهحام می گیرد.
ضلن دستٍر:
> بخصي از باهك اظالعات ONليست امتيازات [WITH GRANT OPTIONليستي از ماربران <TO
34
جذول جماسي
اهٍاع خدول در : SQL
خدول اظلي ) (base tableبا دستٍر CREATE TABLEایحاد می ضٍد.
خدول مياهي ) (intermediate tableمي تٍسط سيستم ايحاد میصٍد.
خدول محازي) (viewsوخٍد خارخي و فيزيلي هدارد اما مي تٍان بي ان دسترسي داضت و ان را
تغيير داد.
ً oدف خداول محازي ايحاد خدول خالظي از اظالعات مٍخٍد ومحدود مردن ديد ماربران مي باضد.
44
:ضلن دستٍر
CREATE VIEW view_name[(attributes)] AS SELECT…
مثال: خدول محازي ضامن هام ،گروى ًا و استاد درس؟
Create view schedule as select cname,sec#,pname
from crs,sec where crs.c#=sec.c#
.هل تي: مي تٍان ستٍن ًاي خدول محازي را با هام ًاي خديد هامگذاري مرد
Create view schedule (course , section , professor) as
select cname,sec#,pname from crs,sec where
crs.c#=sec.c#
45