פרק קודם:

פרק 2 - שפת השאילתות SQL

מה נלמד בפרק הזה?

• עוד על SELECT FROM
• WHERE - סוגים ושילובים
• UPDATE - עדכון ערכים
• DELETE FROM - מחיקת ערכים
• DROP TABLE - מחיקת טבלאות

SELECT FROM

בפרק הקודם הכרנו את התחביר לבחור את הכל מטבלה:

1

SELECT * FROM MY_TABLE

הורידו את קובץ ה-csv ובעזרת קוד הפייתון הבא נטען אותו ל-db מסוג - sqllite.

הקובץ מכיל מידע על חיות לאימוץ.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

import sqlite3
import csv
csv_file = 'pets.csv'
conn = sqlite3.connect('pets_database.db')
cursor = conn.cursor()
with open(csv_file, 'r') as file:
    reader = csv.reader(file)
    columns = next(reader)  # Get the first row which is the header
    column_definitions = ', '.join([f"{col} TEXT" for col in columns])
    table_name = 'Pets'
    create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({column_definitions})"
    cursor.execute(create_table_query)
    insert_query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?' for _ in columns])})"
    for row in reader:
        cursor.execute(insert_query, row)
conn.commit()
conn.close()

כדי לראות שזה עובד ונבסס את קוד הבסיס שלנו לפרוייקט נריץ את הקוד הבא:

1
2
3
4
5
6
7
8

import sqlite3

con = sqlite3.connect("example.db")
cur = con.cursor()

selectCur = cur.execute("""SELECT * FROM pets""")
for pet in selectCur:
    print(pet)

בחירת עמודות ספציפיות

בשאילתת ה-select ניתן להציב עמודות במקום כוכבית כדי לבחור רק את המידע הספציפי שאנחנו רוצים.

1
2
3

selectCur = cur.execute("""SELECT pet_id,name FROM pets""")
for pet in selectCur:
    print(pet)

תרגיל - בחרו רק את העמודות name ו-adoption_status.

1
2
3

selectCur = cur.execute("""SELECT name,adoption_status FROM pets""")
for pet in selectCur:
    print(pet)

אותיות גדולות או קטנות?

זה לא משנה כל כך, שפת ה-SQL יודעת להתמודד גם עם אותיות גדולות וגם עם קטנות.
אולם קונבנציה עולמית נוחה זה לכתוב את כל המילים של שפת ה-sql באותיות גדולות וכל מה ששלכם באותיות קטנות.
למשל:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT * from pets
```  
במקום:  
```sql
SELECT * FROM PETS
```  
  
ככה נבדיל בין השאילתא למידע שלנו!  

## מתן שם לעמודות
ניתן לבחור עמודות ולתת להן שם אחר:  
```sql
SELECT name AS pet_name, age FROM pets

בעזרת המילה AS ניתן שם שונה לעמודה שבחרנו.
נראה בעתיד כי זה עוזר לנו להבדלה בין טבלאות שונות כאשר אנחנו בוחרים יותר מטבלה אחת!

באותו האופן ניתן לבחור גם בעזרת שם הטבלה לפני שם העמודה:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT pets.name as pet_name, age FROM pets
```  

כאן בחרנו את ``pets.name``.  

# תנאים - WHERE
לעיתים לא נבצע שאילתות גדולות שבוחרות את כלל הטבלה.  
בדרך כלל נרוץ באיטרציה על כמויות מידע גדולות אך לשם שנוכל לברור את המידע שלנו נוכל לשים עליו פילטר.  
הפילטר הזה מתבצע בעזרת המילה - ``WHERE``.  
  
```sql
SELECT * FROM pets
WHERE id = 6

התחביר:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT <....> `` ``WHERE`` ``<column>`` ``[operator]`` ``<filter value>
```  

## תנאי השוואה

### = שווה ל
זה עובד עם כל סוג מידע:  

```sql
SELECT * FROM pets WHERE species = 'Bird'
```  

```sql
SELECT * FROM pets WHERE age = 5

<> או != לא שווה

1
2
3
4
5
6
7

SELECT * FROM pets WHERE species != 'Bird'
```  

הפכנו את התנאי ל"תביאו את כל מה שהוא לא ציפור".  

```sql
SELECT * FROM pets WHERE age != 5

וכאן התנאי הפך ל- תביאו את כלל החיות שהן לא בנות - 5.

> גדול מ

1
2
3
4
5
6
7
8

SELECT * FROM pets WHERE age > 1
```  
ניתן לומר שכאן אנו בוחרים את כלל החיות הבוגרות.  
תנאים מייצגים בדרך כלל רעיון ולא סתם בפני עצמו.  

### < קטן מ
```sql
SELECT * FROM pets WHERE age < 1

תביאו את כלל החיות הצעירות

>= גדול שווה

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT * FROM pets WHERE species >= 1
```  

### <= קטן שווה
```sql
SELECT * FROM pets WHERE species <= 1
```  

## תנאים לוגיים
ניתן לצרף את המילים ``AND``, ``OR`` או ``NOT`` בשילוב תנאים.  

אני רוצה לבחור את כלל החתולים הצעירים.  
כאן מסתתרים שני תנאים - סוג החיה והגיל שלה.  
ננסח את שני התנאים:  
1. ``age <= 1``  
2. ``species = 'cat``  

ונשלב אותם בעזרת ה-``AND``

```sql
SELECT * FROM pets WHERE species = 'Cat' AND age <=1

שימו לב שה-sql לא מבדיל בין אותיות קטנות לגדולות אך כאשר מדובר במחרוזות הוא מבדיל!
ולכן צריך לכתוב - Cat.

תרגיל, כתבו תנאים למשפטים הבאים:

1. הביאו את כלל האוגרים והחתולים
2. הביאו את כלל החיות שאימצו אותן והן מסוג ציפור
3. תביאו את כלל החיות שהן לא חתול
4. תביאו את כלל החיות שהן לא חתול, והן מבוגרות וגם לא אומצו

BETWEEN בין מספרים

1

SELECT * FROM pets WHERE species = 'Cat' AND age BETWEEN 1 AND 4

LIKE דומה ל

בשביל לבדוק מחרוזות בצורה דינאמית יותר, שפת השאילתות מאפשרת לנו לתשאל מחרוזות על פי תבנית.

1. % - אפס או יותר תווים
2. _ - תו אחד כלשהו

בחרו את כלל החיות שהאות הראשונה שלהן מתחילה ב-Z:

1

SELECT * FROM pets WHERE name LIKE 'Z%'

נרחיב את התנאי ל -
בחרו את כלל החיות שהאות הראשונה מתחילה ב-Z והשם הוא בגודל 3 אותיות.
במקום להתחיל לספור תווים נוכל לבצע את הדבר הבא:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT * FROM pets WHERE name LIKE 'Z__'
```  
שמנו שני קווים תחתוניים אשר סופרים 2 תווים.  

שני התנאים יביאו לנו במקרה הזה אותה תוצאה על המידע שישנו.  

## IS NULL / IS NOT NULL
ישנו מידע טבלאי אשר יכול להיות "ריק".  
זאת אומרת לא מדובר במחרוזת ריקה בגודל 0 תווים אלה שלא קיים ערך כלל!  
נניח והמידע שלנו לא היה שמור באופן וודאי או שיש לנו רק מידע חלקי:  

```sql
SELECT * FROM pets WHERE name IS NOT NULL

IN - תנאי מערכים

כדי לבדוק אם ערך נמצא בסט ערכים מסוים נוכל להשתמש ב-IN:

1

SELECT * FROM pets WHERE species IN ('Dog','Cat','Bird','Goat')

כאן אנו בודקים אם אחת מהחיות היא אחת מהסוגים שבחרנו.

בניית שאילתות משאילתות משניות

כמו שזה נשמע - שאילתה בתוך שאילתה!
בשביל התרגול ניצור טבלה נוספת בשם - feeding_schedules:

זה הולך להיות מעט ארוך ואני לא אסביר מה קורה כאן, העתיקו את הקוד והריצו אותו.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

import sqlite3
con = sqlite3.connect("pets_database.db")
cur = con.cursor()
cur.execute("DROP TABLE feeding_schedule")
cur.execute("""CREATE TABLE feeding_schedule (
    schedule_id INTEGER PRIMARY KEY AUTOINCREMENT,
    pet_id INT NOT NULL,
    feeding_time DATETIME NOT NULL,
    feeding_notes TEXT,
    type_of_food TEXT
)""")
cur.execute("""-- Insert feeding schedule data for pet_ids 1-100
WITH RECURSIVE
cte_pet_ids(pet_id) AS (
    SELECT 1
    UNION ALL
    SELECT pet_id + 1
    FROM cte_pet_ids
    WHERE pet_id < 100
),
cte_feed_times(pet_id, feeding_time, feeding_notes, type_of_food, feeding_num) AS (
    SELECT
        pet_id,
        datetime('now', '+' || abs(random() % 10) || ' hours'),
        'Regular feeding schedule',
        CASE WHEN random() % 2 = 0 THEN 'Dry Food' ELSE 'Wet Food' END,
        1
    FROM cte_pet_ids
    UNION ALL
    SELECT
        pet_id,
        datetime('now', '+' || abs(random() % 10 + 1) || ' hours'),
        'Regular feeding schedule',
        CASE WHEN random() % 2 = 0 THEN 'Dry Food' ELSE 'Wet Food' END,
        feeding_num + 1
    FROM cte_feed_times
    WHERE feeding_num < 3
)
INSERT INTO feeding_schedule (pet_id, feeding_time, feeding_notes, type_of_food)
SELECT pet_id, feeding_time, feeding_notes, type_of_food
FROM cte_feed_times
ORDER BY pet_id, feeding_time""")
cur.execute("""
SELECT
    pet_id,
    feeding_time,
    feeding_notes,
    type_of_food
FROM (
    SELECT
        pet_id,
        feeding_time,
        feeding_notes,
        type_of_food,
        row_number() OVER (PARTITION BY pet_id ORDER BY feeding_time) AS row_num
    FROM feeding_schedule
) AS subquery
WHERE row_num <= 3""")

עכשיו יש לנו 2 טבלאות: pets ו-feeding_schedules.
ביצעו SELECT * FROM כדי לראות שהכל תקין.

שאילתא בתוך שאילתא

1
2

SELECT * FROM pets WHERE pet_id IN 
    (SELECT pet_id FROM feeding_schedule WHERE type_of_food = 'Dry Food')

השאילתא הזו בוחרת את כלל החיות אשר סוג האוכל שלהן זה אוכל יבש - שימו לב שהמידע הזה נלקח מטבלה אחרת!

1. זה בוחר קודם כל את החיות שאוכלות אוכל יבש, ולוקח רק את העמודה pet_id.
2. לאחר מכן זה רץ על טבלת pets ובוחר את כל החיות אשר ה-pet_id נמצא בשאילתא הפנימית.

אם נשנה את שם העמודה בשאילתא הפנימית מ -pet_id ל - id.
מה ייקרה?

1
2

SELECT * FROM pets WHERE pet_id IN 
    (SELECT pet_id as id FROM feeding_schedule WHERE type_of_food = 'Dry Food')

1
2

SELECT * FROM pets WHERE pet_id IN 
    (SELECT * FROM feeding_schedule WHERE type_of_food = 'Dry Food')

EXISTS - בדיקת אם השאילתא בפנים החזירה משהו

בודק אם שאילתא פנימית מחזירה תוצאה אחת או יותר.

1

SELECT * FROM pets WHERE EXISTS (SELECT 1 FROM feeding_schedule WHERE pets.pet_id = feeding_schedule.pet_id)

בדוגמא הזו השאילתא תחזיר את הכל כי עבור כל חיה יש לה זמן האכלה במסד נתונים.

1

SELECT * FROM pets WHERE EXISTS (SELECT 1 FROM feeding_schedule WHERE type_of_food = 'Special Food')

בשאילתא הזו אנו בודקים סוג של אוכל שלא קיים - אז במקרה הזה השאילתא הפנימית תחזיר תמיד 0 עמודות ולכן גם השאילתא הראשית לא תחזור כי תנאי ה-Exist לא מתקיים.

ANY או ALL

בודק במערך אם יש ערך אחד או כולם לפי התנאי.

1. ANY - בודק אם יש תנאי אחד לפחות שמתקיים
2. ALL - בודק אם הכל מקיים את התנאי.

בטכנולוגיה שאנו משתמשים בה - sqlite עם פייתון המילים לא קיימות ולכן נוכל לבצע בדיקה שונה בעזרת אופרטורים שונים שלמדנו עליהם.

למשל אנחנו רוצים למצוא את כל החיות שזמני האכלה שלהם בבוקר לפני 10:

1
2
3
4
5
6
7

SELECT *
FROM pets
WHERE pet_id IN (
    SELECT pet_id
    FROM feeding_schedule
    WHERE strftime('%H:%M:%S', feeding_time) < '10:00:00'
)

ואם נרצה לבדוק את הכל נוכל לבצע NOT EXIST:

1
2
3
4
5
6
7
8

SELECT *
FROM pets
WHERE NOT EXISTS (
    SELECT 1
    FROM feeding_schedule fs
    WHERE fs.pet_id = pets.pet_id
    AND strftime('%H:%M:%S', fs.feeding_time) <= '08:00:00'
)

Update - עדכון מידע

כדי לעדכן את הערכים בתוך הטבלה, ניתן להשתמש בשאילתת העדכון:
תחביר:
UPDATE <table>
SET <col>=val , col2 = val2
WHERE [Condition]

שימו לב - צריך תנאי אחרת תוכלו בטעות לעדכן את כל הטבלה בבת ואחת ולגרום לשגיאות במידע!

במידע הטבלאי שיש לנו מס 94 זה תוכי בשם אוליבר.
בטעות רשמנו שזה תוכי אבל זה חתול!
איך נשנה את זה?

1
2
3

UPDATE pets
SET species='Cat'
WHERE pet_id=94

DELETE - מחיקת עמודות

מחיקה זה קלי קלות - כמו עדכון:
DELETE FROM <table>
WHERE [Condition]

שימו לב - אם לא תשימו תנאי אתם תמחקו הכל!
למשל נרצה למחוק רק את עמודה 100:

1
2

DELETE FROM pets
WHERE pet_id = 100

DROP TABLE - מחיקת טבלאות

בפרק הקודם ראינו איך אנחנו יוצרים טבלאות.
כדי למחוק אותן כל מה שצריך לעשות זה למחוק!

1

DROP TABLE pets

כדי לסיים את הפרק מחקו את 2 הטבלאות שעבדו איתן ולבסוף מחקו את קובץ ה-DB.

בפרק הזה הרחבנו את הידע שלנו על פילטורים ב-SQL,
ואף נכנסו לנושא המתקדם של שאילתות משניות.
האם ניתן לעשות שאילתא בתוך שאילתא בתוך שאילתא?

בפרק הבא נרחיב את הידע על השפה עם JOIN, GROUP BY וסוגי מידע בעמודות.
תודה על הקריאה!