Sheet 1
— ﺷ ﺮ و ع ﺳ ﺮ ﯾ ﻊSQL
ﻣﺮور اﺑﺰارﻫﺎ+ دادٔه آزﻣﺎﯾﺸﯽ+ ﺗﻌﺎر ﯾﻒ ﺳﺎده
ﭼ ﯿ ﺴ ﺖ ؟SQL
: زﺑﺎن اﺳﺘﺎﻧﺪارد ﺑﺮای ﮐﺎر ﺑﺎ دﯾﺘﺎﺑﯿﺲﻫﺎی راﺑﻄﻪای اﺳﺖSQL
:دﺳﺘﻮرات ﮐﻠﯿﺪی
،( )ﻣﺮﺗﺐﺳﺎز یORDER BY ،( )ﺧﻼﺻﻪﺳﺎز یGROUP BY ،( )ﻓﯿﻠﺘﺮWHERE ،( )ﺧﻮاﻧﺪنSELECT
،( )ﻣﺤﺪودﺳﺎز یLIMIT
.(DML) ﺑﺮای ﻣﺪﯾﺮ ﯾﺖ دادهINSERT / UPDATE / DELETE و ﻫﻤﭽﻨﯿﻦ
د ا د ٔه ﻧ ﻤ ﻮ ﻧ ﻪ
country
area population name id
640680 65000000 France 1
357000 80700000 Germany 2
city
rating population country_id name id
5 2244000 1 Paris 1
3 3460000 2 Berlin 2
4 1700000 4 Warsaw 3
[Link] @ : • اﯾﻨﺴﺘﺎﮔﺮام[Link] 2025 ©
Sheet 2
— ﻓ ﯿ ﻠ ﺘ ﺮ ﮐ ﺮ د نWHERE
ﻣﻘﺎﯾﺴﻪای و ﻣﺘﻨﯽ ﺑﺎ ﺧﺮوﺟﯽ ﻧﻤﻮﻧﻪ
ﺷﺮط ﻋﺪدی
SELECT name FROM city WHERE rating > 3;
name
Paris
Warsaw
. ﺑﺮﮔﺮداﻧﺪه ﻣﯽﺷﻮﻧﺪ۳ ﻓﻘﻂ ردﯾﻒﻫﺎی ﺑﺎ اﻣﺘﯿﺎز ﺑﺎﻻﺗﺮ از
( LIKE ) ا ﻟ ﮕ ﻮ ﻫ ﺎ ی ﻣ ﺘ ﻨ ﯽ
SELECT name FROM city WHERE name LIKE 'P%';
name
Paris
. ﺷﺮوع ﻣﯽﺷﻮﻧﺪP ' ﯾﻌﻨﯽ ﻧﺎمﻫﺎﯾﯽ ﮐﻪ ﺑﺎ ﺣﺮف%P'
[Link] @ • [Link] 2025 © WHERE • LIKE
Sheet 3
IN وBETWEEN
اﻧﺘﺨﺎب ﺑﺎزه ﯾﺎ ﻟﯿﺴﺖ ﻣﺸﺨﺺ
BETWEEN
SELECT name FROM city WHERE population BETWEEN 500000 AND 5000000;
name
Berlin
Warsaw
IN
SELECT name FROM city WHERE country_id IN (1,2);
name
Paris
Berlin
[Link] @ • [Link] 2025 © BETWEEN • IN
Sheet 4
Aggregate
ﺑﺎ ﻧﻤﻮﻧﻪ ﺧﺮوﺟﯽAVG وCOUNT
COUNT
SELECT COUNT(*) FROM city;
count
.ﺗﻌﺪاد ﺗﻤﺎم ردﯾﻒﻫﺎی ﺟﺪول
AVG
SELECT AVG(rating) FROM city;
avg
4.0
[Link] @ • [Link] 2025 © COUNT • AVG
Sheet 5
GROUP BY + HAVING
(JOIN ﺧﻼﺻﻪﺳﺎز ی روی ﮔﺮوهﻫﺎ )ﺑﺪون
ﺗﻌﺪاد ﺷﻬﺮﻫﺎی ﻫﺮ ﮐﺸﻮر
SELECT country_id, COUNT(*) AS city_count
FROM city
GROUP BY country_id;
city_count country_id
1 1
1 2
1 4
.ﮔﺮوهﺑﻨﺪی ﺑﺮ اﺳﺎس ﺷﻨﺎﺳٔﻪ ﮐﺸﻮر و ﺷﻤﺎرش ﺷﻬﺮﻫﺎ
( )ﻓ ﯿ ﻠ ﺘ ﺮ ر و ی ﮔ ﺮ و ه ﻫ ﺎHAVING
SELECT country_id, AVG(rating) AS avg_rating
FROM city
GROUP BY country_id
HAVING AVG(rating) > 3;
avg_rating country_id
5 1
4 4
. اﺳﺘﻔﺎده ﮐﻨﯿﺪWHERE ﺑﻌﺪ از ﮔﺮوهﺑﻨﺪی اﻋﻤﺎل ﻣﯽﺷﻮد؛ ﺑﺮای ﻗﺒﻞ از ﮔﺮوهﺑﻨﺪی ازHAVING
[Link] @ • [Link] 2025 © GROUP BY • HAVING
Sheet 6
INNER JOIN
ﻧﻤﺎﯾﺶ ﻓﻘﻂ ردﯾﻒﻫﺎی دارای ﺗﻄﺒﯿﻖ
ﻧﻤﻮﻧﻪ
SELECT [Link], [Link] AS country
FROM city INNER JOIN country ON city.country_id = [Link];
country city
France Paris
Germany Berlin
.ﻓﻘﻂ ردﯾﻒﻫﺎﯾﯽ ﮐﻪ در ﻫﺮ دو ﺟﺪول ﺑﺎ ﺷﺮط اﺗﺼﺎل ﺗﻄﺒﯿﻖ دارﻧﺪ ﺑﺮﻣﯽﮔﺮدﻧﺪ
[Link] @ • [Link] 2025 © INNER JOIN
Sheet 7
LEFT JOIN
ﺣﻔﻆ ﻫﻤٔﻪ ردﯾﻒﻫﺎی ﺟﺪول ﺳﻤﺖ ﭼﭗ
ﻧﻤﻮﻧﻪ
SELECT [Link], [Link] AS country
FROM city LEFT JOIN country ON city.country_id = [Link];
country city
France Paris
Germany Berlin
NULL Warsaw
. ﻣﯽﺷﻮدNULL ﺳﻄﺮ ﺷﻬﺮ ﺑﺎﻗﯽ ﻣﯽﻣﺎﻧﺪ و ﺳﺘﻮنﻫﺎی ﮐﺸﻮر،ﺣﺘﯽ اﮔﺮ ﮐﺸﻮر ﭘﯿﺪا ﻧﺸﻮد
[Link] @ • [Link] 2025 © LEFT JOIN
Sheet 8
ORDER BY + LIMIT
ﻣﺮﺗﺐﺳﺎز ی و ﻣﺤﺪودﺳﺎز ی ﻧﺘﺎﯾﺞ
ﻣﺮﺗﺐﺳﺎز ی
SELECT name, rating FROM city ORDER BY rating DESC;
rating name
5 Paris
4 Warsaw
3 Berlin
Top-N
SELECT name FROM city ORDER BY rating DESC LIMIT 2;
name
Paris
Warsaw
[Link] @ • [Link] 2025 © ORDER • LIMIT
Sheet 9
ﻧﮑﺎت ﻣﻬﻢ
اﺷﺘﺒﺎﻫﺎت راﯾﺞ +ﺗﻮﺻﯿﻪﻫﺎی ﻋﻤﻠﯽ
ﭼﮏﻟﯿﺴﺖ
• ﺑﻪﺟﺎی * SELECTﺳﺘﻮنﻫﺎی ﻻزم را ﺑﻨﻮﯾﺴﯿﺪ.
• ﺷﺮط اﺗﺼﺎل را ﻫﻤﯿﺸﻪ ﺑﻌﺪ از ONﺑﻨﻮﯾﺴﯿﺪ.
• ﻗﺒﻞ از ﮔﺮوهﺑﻨﺪی از WHEREو ﺑﻌﺪ از آن از HAVINGاﺳﺘﻔﺎده ﮐﻨﯿﺪ.
• ﺑﺮای ﻧﺘﺎﯾﺞ ﻗﺎﺑﻞ ﺗﮑﺮار ،ﻫﻤﯿﺸﻪ ORDER BYﺑﻨﻮﯾﺴﯿﺪ.
© [Link] @ • [Link] 2025 Best Practices
Sheet 10
— ا ﺿ ﺎ ﻓ ﻪ ﮐ ﺮ د ن د ا د هINSERT
ﻧﮑﺎت ﮐﺎرﺑﺮدی+ ﭼﻨﺪﺳﻄﺮه+ ﺳﺎدهﺗﺮ ﯾﻦ ﺷﮑﻞ
. ﺧﻮدﮐﺎر اﺳﺖ ﻻزم ﻧﯿﺴﺖ ﺑﯿﺎور یid اﮔﺮ. ﻣﻘﺪار ﺑﺪه،ﺳﺘﻮنﻫﺎ را ﺑﻨﻮﯾﺲ
INSERT INTO city (name, country_id, population, rating)
VALUES ('Rome', 2, 2873000, 4);
( INSERT ) ﭘ ﺲ ا زcity
rating population country_id name
5 2244000 1 Paris
3 3460000 2 Berlin
4 1700000 4 Warsaw
4 2873000 2 Rome
(Bulk Insert ) ﭼ ﻨ ﺪ ﺳ ﻄ ﺮ ه
INSERT INTO city (name, country_id, population, rating)
VALUES
('Lyon', 1, 522000, 4),
('Hamburg', 2, 1841000, 4);
. ﺳﺮ ﯾﻊ و ﺗﻤﯿﺰ: ﭼﻨﺪ ردﯾﻒ،ﯾﮏ دﺳﺘﻮر
. روی ﺳﺘﻮنﻫﺎ اﺳﺘﻔﺎده ﮐﻨﯿﺪUNIQUE • ﺑﺮای ﺟﻠﻮﮔﯿﺮ ی از رﮐﻮرد ﺗﮑﺮار ی از
PostgreSQL:) اﺳﺘﻔﺎده ﮐﻨﯿﺪUPSERT از اﻟﮕﻮﻫﺎی،• ﺑﺮای ﻣﺪﯾﺮ ﯾﺖ ﺗﻌﺎرض
INSERT ... ON CONFLICT ... DO UPDATE، MySQL: INSERT ... ON
.(DUPLICATE KEY UPDATE
[Link] @ • [Link] 2025 © DML • INSERT
Sheet 11
— UPDATEﺗ ﻐ ﯿ ﯿ ﺮ د ا د ٔه ﻣ ﻮ ﺟ ﻮ د
ﺑﺎ ﺷﺮط اﯾﻤﻦ +ﻋﻤﻠﮕﺮﻫﺎی ﻋﺪدی +ﭼﻨﺪﺳﺘﻮﻧﻪ
ﺑﻪروزرﺳﺎﻧﯽ ﺑﺎ ﺷﺮط
ﻫﻤﯿﺸﻪ WHEREﺑﻨﻮﯾﺲ ﺗﺎ ﻓﻘﻂ ردﯾﻒﻫﺎی ﻣﻮردﻧﻈﺮ ﺗﻐ�ﺮ ﮐﻨﻨﺪ.
UPDATE city
SET rating = 5
;'WHERE name = 'Berlin
) ratingﻗ ﺒ ﻞ → ﺑ ﻌ ﺪ ( name
5→3 Berlin
ﭼﻨﺪ ﺳﺘﻮن ﺑﺎﻫﻢ
UPDATE city
SET rating = 5, population = population + 10000
;'WHERE name = 'Paris
ﻣﯽﺗﻮاﻧﯿﺪ ﭼﻨﺪ ﺳﺘﻮن را ﯾﮑﺠﺎ ﺗﻐ�ﺮ دﻫﯿﺪ.
• ا و ل ، SELECTﺑ ﻌ ﺪ : UPDATEﻫﻤﯿﻦ ﺷﺮط را ﺑﺎ SELECTﺗﺴﺖ ﮐﻦ.
• در ﺻﻮرت اﻫﻤﯿﺖ از TRANSACTIONاﺳﺘﻔﺎده ﮐﻦBEGIN → UPDATE → :
) COMMITو در ﺻﻮرت ﺧﻄﺎ .(ROLLBACK
• ﺑﺪون WHEREﻫﻤٔﻪ ردﯾﻒﻫﺎ ﺗﻐ�ﺮ ﻣﯽﮐﻨﻨﺪ—ﺧﻄﺮﻧﺎک!
© [Link] @ • [Link] 2025 DML • UPDATE
Sheet 12
— DELETEﺣ ﺬ ف د ا د ه
ﺣﺬف اﻣﻦ ﺑﺎ + WHEREﭘﺎکﺳﺎز ی ﮐﺎﻣﻞ )ﺑﺎ اﺣﺘﯿﺎط!(
ﺣﺬف ﯾﮏ ردﯾﻒ ﻣﺸﺨﺺ
ﺷﺮط دﻗﯿﻖ ﺑﺪه ﺗﺎ ﻓﻘﻂ ﻫﻤﺎن رﮐﻮرد ﺣﺬف ﺷﻮد.
DELETE FROM city
;'WHERE name = 'Warsaw
cityﺑ ﺎ ﻗ ﯽ ﻣ ﺎ ﻧ ﺪ ه
Paris
Berlin
Rome
ﺣﺬف ﮔﺮوﻫﯽ
DELETE FROM city
;WHERE rating < 4
ﺑﺎ ﯾﮏ ﺷﺮط ،ﭼﻨﺪ ردﯾﻒ ﻫﺪفدار ﺣﺬف ﻣﯽﺷﻮﻧﺪ.
ﺧﺎﻟﯽﮐﺮدن ﮐﻞ ﺟﺪول )ﺑﺎ اﺣﺘﯿﺎط ﺷﺪﯾﺪ(
;DELETE FROM city ﻫﻣٔﻪ ﺭﺩﻳﻑﻫﺎ ﺣﺫﻑ ﻣﯽﺷﻭﻧﺩ --
• اول ﺷﺮط DELETEرا ﺑﺎ SELECTآزﻣﺎﯾﺶ ﮐﻦ.
• ﻣﺤﺪودﯾﺖﻫﺎی FOREIGN KEYرا در ﻧﻈﺮ ﺑﮕﯿﺮ )ﻣﻤﮑﻦ اﺳﺖ ﺣﺬف ﺑﻪدﻟﯿﻞ
واﺑﺴﺘﮕﯽﻫﺎ ﺧﻄﺎ ﺑﺪﻫﺪ(.
© [Link] @ • [Link] 2025 DML • DELETE