פקודות SQL: רשימת פקודות SQL נפוצות עם דוגמאות

פקודות SQL במבט אחד

פקודות SQL מתחלקות לכמה קבוצות לפי מה שהן עושות. הטבלה מפרטת את אלה שתשתמשו בהן הכי הרבה, וכל אחד מהסעיפים שבהמשך מריץ אחת מהן.

DDL היא שפת הגדרת נתונים (data definition language), DML היא שפת טיפול בנתונים (data manipulation language), DQL היא שפת שאילתות (data query language) ו-TCL היא שפת בקרת טרנזקציות (transaction control language). השמות פחות חשובים מהחלוקה: DDL משנה את הצורה של מסד הנתונים, ו-DML משנה את מה שבתוכו.

CREATE TABLE: הגדרת טבלה

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

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    grade INTEGER
);

SELECT name, sql FROM sqlite_master WHERE type = 'table';

העמוד על CREATE TABLE מסביר כללים כמו NOT NULL, UNIQUE ו-DEFAULT.

INSERT: הוספת שורות

INSERT INTO מוסיפה שורה אחת או כמה. רשמו את העמודות, ואז את הערכים באותו סדר.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);

INSERT INTO students (name, grade) VALUES ('Ada', 91);
INSERT INTO students (name, grade) VALUES
    ('Boris', 78),
    ('Rosa', 85);

SELECT * FROM students;

עמודת ה-id התמלאה מעצמה: INTEGER PRIMARY KEY מקבל את המספר הבא כשמשמיטים אותו. עוד על זה ב-INSERT.

SELECT: קריאת שורות

SELECT היא הפקודה שתכתבו הכי הרבה. בחרו את העמודות, סננו שורות עם WHERE ומיינו אותן עם ORDER BY.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

SELECT name, grade
FROM students
WHERE grade >= 80
ORDER BY grade DESC;

רק שורות שעוברות את בדיקת ה-WHERE חוזרות, מהציון הגבוה לנמוך. ראו יסודות SELECT ו-WHERE לפרטים.

UPDATE: שינוי שורות

UPDATE קובעת ערכים חדשים בשורות שכבר קיימות. פסוקית ה-WHERE קובעת באילו שורות מדובר, ואם משמיטים אותה, כל שורה משתנה.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85);

UPDATE students
SET grade = 82
WHERE name = 'Boris';

SELECT * FROM students;

כשאתם לא בטוחים למה פסוקית WHERE מתאימה, הריצו אותה קודם כ-SELECT. העמוד על UPDATE מראה עוד דפוסים.

DELETE: הסרת שורות

DELETE FROM מסירה את השורות שמתאימות לפסוקית ה-WHERE שלה. כמו UPDATE, היא משפיעה על כל שורה אם הפסוקית חסרה.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

DELETE FROM students
WHERE grade < 70;

SELECT * FROM students;

השורה של Chen נעלמה והשאר לא נפגעו. ראו DELETE.

ALTER TABLE ו-DROP TABLE: שינוי או הסרה של טבלה

ALTER TABLE משנה טבלה קיימת בלי לאבד את השורות שלה. DROP TABLE מסירה את הטבלה לגמרי.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO students (name) VALUES ('Ada'), ('Boris');

ALTER TABLE students ADD COLUMN email TEXT;
UPDATE students SET email = lower(name) || '@school.test';
SELECT * FROM students;

CREATE TABLE old_notes (id INTEGER PRIMARY KEY, body TEXT);
DROP TABLE old_notes;
SELECT name FROM sqlite_master WHERE type = 'table';

עמודה חדשה מתחילה כ-NULL בשורות קיימות עד שממלאים אותה. ALTER ו-DROP TABLE מפרט מה SQLite מאפשרת.

JOIN: שילוב טבלאות

JOIN קורא שורות משתי טבלאות בבת אחת, לפי התאמה בעמודה משותפת.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE enrollments (student_id INTEGER, course TEXT);

INSERT INTO students (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO enrollments VALUES (1, 'Math'), (1, 'Physics'), (3, 'Art');

SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON enrollments.student_id = students.id
ORDER BY students.name;

ל-Boris אין הרשמות, ולכן INNER JOIN משאיר אותו בחוץ. LEFT JOIN היה שומר אותו, עם קורס ריק.

GROUP BY: סיכום שורות

GROUP BY מכווץ שורות שחולקות ערך לשורה אחת, ופונקציית צבירה כמו COUNT או AVG מסכמת כל קבוצה.

CREATE TABLE enrollments (student TEXT, course TEXT, score INTEGER);
INSERT INTO enrollments VALUES
    ('Ada', 'Math', 91), ('Boris', 'Math', 78),
    ('Ada', 'Art', 88), ('Rosa', 'Art', 95), ('Chen', 'Art', 70);

SELECT course, COUNT(*) AS students, AVG(score) AS average
FROM enrollments
GROUP BY course
ORDER BY course;

פונקציות צבירה ו-GROUP BY ו-HAVING מעמיקים יותר.

BEGIN, COMMIT ו-ROLLBACK: קיבוץ שינויים

טרנזקציה גורמת לכמה שינויים להיחשב כאחד. COMMIT שומרת את כולם, ו-ROLLBACK מבטלת את כולם.

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Boris', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Boris';
COMMIT;

BEGIN;
UPDATE accounts SET balance = 0;
ROLLBACK;

SELECT * FROM accounts;

ההעברה בוצעה, והשינוי השני בוטל, כך ש-Ada מסיימת עם 70 ו-Boris עם 80. טרנזקציות מסביר מתי להשתמש בהן.

במה SQLite שונה

הפקודות בעמוד הזה הן SQL תקני, ומה שלומדים כאן עובר גם ל-MySQL ול-PostgreSQL. הדוגמאות להרצה כתובות ל-SQLite, ושלושה פרטים משתנים במסדי הנתונים הגדולים יותר:

• רשימת הטבלאות. הדוגמאות קוראות את sqlite_master, טבלת הסכמה של SQLite עצמה. MySQL משתמשת ב-SHOW TABLES, ו-PostgreSQL משתמשת ב-view information_schema.tables (או ב-\dt ב-psql).
• מזהים אוטומטיים. עמודה שמוצהרת כ-INTEGER PRIMARY KEY ממספרת שורות חדשות בעצמה ב-SQLite, ולכן דוגמאות ה-INSERT משמיטות את id. MySQL צריכה AUTO_INCREMENT על העמודה, ו-PostgreSQL צריכה עמודת identity (GENERATED BY DEFAULT AS IDENTITY) או SERIAL.
• הרשאות ושינויי סכמה. ל-SQLite אין GRANT או REVOKE, כי מסד נתונים של SQLite הוא קובץ יחיד בלי חשבונות משתמשים, וה-ALTER TABLE שלה לא יכול לשנות טיפוס של עמודה.