מבוא ל-MySQL

כתריאל טראום

katriel@penguin.org.il

היסטוריית גירסאות
גירסה 1.0 05-04-2007 כתריאל טראום
הומר מפורמאט ישן לוויקי

למי מיודע המדריך

מדריך זה מיועד לכל מי שרוצה ללמוד קצת על שפת ה SQL (כן, זו שפה בפני עצמה), ועל היישום שלה במערכת ה- MySQL.
המדריך יהיה מבוא לשימוש ב MySQL, לפקודות בסיסית והתחביר שלהם (הוספה/עדכון/מחיקה של נתונים וטבלאות)

זכויות יוצרים ורשיון

כל הזכויות שמורות © 2007, כתריאל טראום, הרשות ניתנת להעתיק, לשנות ולהפיץ מדריך זה תחת התנאים של רשיון ה-GFDL Linux הוא סימן מסחרי רשום של Linus Torvalds.

הסרת אחריות

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

משוב

תגובות, תלונות, הערות והארות לכתובת: xxx@xxx.xxx.

אז מה זה SQL בכלל?

SQL, קיצור ל Structured Query Language, היא שפה תיקנית (תקן ANSI ו ISO) לשליפה ועדכון נתונים ממסדי נתונים. תוכנות מסד נתונים רבות מיישמות את תקן ה SQL, ומוסיפות עליו הרחבות ושיפורים משלהן. שפת ה SQL מגדירה את המבנה של משאילתות, שמאפשרות להוסיף, ולשנות ולמחוק מידע ממסד הנתונים.

סקירה קצרה על MySQL

MySQL היא תוכנת מסד נתונים, שמפותחת תחת רשיון ה- GPL, ומופצת חינם באתר http://www.mysql.com. בנוסף לגירסא החינמית, מוכרת חברת MysqlAB גם גרסאות מתקדמות יותר, שלהן נדרש רשיון. מסד הנתונים מיישם חלק רחב (והולך וגדל) של תקן ה- ANSI SQL-99 (לא שזה ממש משנה לנו הרבה, אבל זה נחמד לדעת, לא?)

עבודה עם MySQL

התחברות לשרת

כדי לעבוד בצורה טקסטואלית על מסד נתונים מסויים שמנוהל ע”י mysql, נצטרך להתחבר לשרת, או בצורה מקומית, או בצורה מרוחקת, דרך הרשת.

שלב ראשון, בהנחה שזהו שרת שרק הותקן, וסיסמאת ה - root (של mysql, לא של המ”ה) עוד לא נקבעה, נצטרך לקבוע אותה. נשתמש ב- mysqladmin:

'# mysqadmin -u root password 'qwerty123'

כמובן שתשתמשו בסיסמא טובה יותר משלי :-).
הכלי שבעזרתו מנהלים את מסדי הנתונים, הטבלאות והמידע שהן מכילות, הוא mysql. על מנת להתחבר לשרת, ולקבל שורת פקודה שממנה נוכל לנהל את המסד הרצוי, נריץ את הפקודה:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

בזמן התקנת המערכת, נוצר מסד נתונים נסיוני ונגיש לכל בשם test, אם נרצה להתחבר ישירות למסד נתונים מסויים, למשל test, משורת הפקודה, נשתמש בפקודה:

# mysql -u root -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

עוד אין לנו הכלים לוודא שאני מחוברים באמת למסד test, אז תצטרכו להאמין לי בנתיים.
עכשיו שאנו מחוברים לשרת ה- MySQL, נוכל לעבור לשלב הבא, יצירת מסד נתונים, שנוכל לעבוד עליו במשך המדריך.

כמה מוסכמות לפני שמתחילים לעבוד

כאשר עובדים עם SQL בכלל ו- MySQL בפרט, ישנן כמה מוסכמות:

  • פקודה אינה נגמרת עד שמוקלד התו ”;”, דוגמא:
mysql> SELECT *
-> FROM demo
-> WHERE id=1;

בדוגמא הנ”ל, אפשר לראות שלחצתי על “Enter” בין כל חלק של הפקודה, ורק כאשר הכנסתי את התו ”;”, בוצעה הפקודה. כמובן שזה לא חובה. אבל זה עוזר בזמן הכנסת פקודות ארוכות כך שיהיו מחולקות על כמה שורות ויהיו יותר פשוטות להבנה ולקריאה.

  • מקובל, בזמן הרצת פקודות בשורת הפקודה, להפריד את פקודות ה SQL משמות הטבלאות והשדות ע”י שימוש באותיות גדולות בשביל פקודות ה- SQL. ניתן להתסתכל על הפקודה הנ”ל כדי לראות מה הכוונה. הפקודה SELECT (שליפת נתונים, תפורט בהמשך) נכתבת באותיות גדולות, ואילו שם ה טבלה (demo), נכתב באותיות קטנות. שוב, לא הכרחי, אבל עוזר להפריד בין חלקי הפקודה ששייכים ל SQL ובין חלקי הפקודה שמשתנים (למשל שם טבלה או עמודה)

יצירת מסד נתונים חדש

עכשיו שאנו מחוברים, בעזרת משתמש root הכל יכול (גם ב- MySQL, לא רק במערכת ההפעלה), נוכל ליצור את מסד הנתונים ולהתחבר אליו, כדי שנוכל לעבוד עליו. כדי ליצור את המסד נשתמש בפקודה CREATE ובפרמטר שלה DATABASE. הפקודה CREATE תשמש אותנו פעמים נוספות במהלך המדריך ליצירת טבלאות.

פקודת היצירה:

mysql> CREATE DATABASE demo;
Query OK, 1 row affected (0.00 sec)

כדי לראות את רשימת המסדים, נשתמש בפקודה SHOW:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| demo |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)

שימו לב שבנוסף למסד שזה עתה יצרנו, ישנם עוד 2 מסדים, mysql ו test. המסד test נוצר אוטומאטית בזמן התקנה ומאפשר גישה חופשית לביצוע ניסויים ללא צורך בהגדרות נוספות. המסד mysql מכיל נתונים על המערכת, למשל הרשאות גישה למסדים וטבלאות השונים במערכת.

להתחברות למסד שיצרנו, מתוך שורת הפקודה של MySQL, נשתמש בפקודה USE, שמאפשרת לנו להתחבר למסדי נתונים, על מנת לנהל אותם. כל פקודה שתורץ אח”כ, תשפיע על המסד שאליו אנו מחוברים בלבד. אם נרצה להריץ פקודה על מסד אחר, נהיה חייבים להשתמש ב USE על מנת להתחבר אליו קודם. נתחבר למסד החדש שיצרנו:

mysql> USE demo;
Database changed
mysql>

אנחנו עכשיו מוכנים לעבוד על המסד החדש.

קישור לפקודות המופיעות בפרק: CREATE - http://www.mysql.com/doc/en/CREATE_DATABASE.html

מבוא למערכת ההרשאות של MySQL

לפני שנמשיך לשאר פקודות הניהול , נערוך סקירה קצרה של מערכת ההרשאות, על מנת שנוכל להבין כיצד לאפשר ולמנוע ממשתמשים להריץ פקודות שהם לא אמורים.
מערכת ההרשאות של MySQL מבוססת על הפקודות GRANT ו REVOKE:

  • הפקודה GRANT משמשת לנתינת הרשאה, והפקודה REVOKE לשלילת הרשאה.
  • הפקודה GRANT מחולקת ל- 4 חלקים: א) מהות ההרשאה (כלומר הפעולות שיוכל המשתמש לבצע), ב) על איזה טבלאות ומסדים ההרשאה, ג) שם המשתמש ומיקומו (חיבור מקומי או דרך הרשת) וד) הסיסמא שאיתה צריך המשתמש להזדהות בזמן גישה משורת הפקודה או דרך אפליקצית צד שלישי (למשל התחברות למסד דרך PHP).

נחזור לדוגמא שלנו. אנחנו מחוברים בעזרת משתמש root של MySQL, ורוצים לתת הרשאה למשתמש demouser, לעשות שימוש במסד demo שיצרנו. מכיוון שלא למדנו עדיין את כל הפעולות שניתן לבצע, ניתן לו הרשאה כוללת לבצע את כל הפעולות, נגביל אותו לחיבור מהמחשב המקומי ונאפשר לו גישה לכל הטבלאות במסד (הקיימות והעתידיות):

[root@crypt root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE demo;
Database changed
mysql> GRANT all
-> ON demo.*
-> TO demouser@localhost
-> IDENTIFIED BY 'demopasswd';
Query OK, 0 rows affected (0.00 sec)

mysql>

הסבר:

  • אפשר גישה לכל פעולות:


GRANT all

  • אפשר גישה לכל הטבלות במסד demo:
ON demo.*
  • למשתמש demouser שמתחבר מקומית (localhost). שימו לב, אין פקודה שיוצרת משתמשים, עצם מתן ההרשאה, יוצר אותו:
TO demouser@localhost
  • אפשר התחברות בעזרת הסיסמא המצויינת:
IDENTIFIED BY 'demopasswd';

אחרי שהרצנו את הפקודה הנ”ל, ננסה להתחבר עם המשתמש החדש (demouser), ונראה איך (בניגוד ל root), אין לו גישה למסד אחר בשם demo2 שיצרתי בשביל הדוגמא:

# mysql -u demouser -p demo
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE demo2;
ERROR 1044: Access denied for user: 'demouser@localhost' to database 'demo2'
mysql>

עוד כמה דוגמאות לנתינת הרשאות שונות. מתן הרשאת SELECT בלבד (שליפת נתונים), טוב למשל כאשר רוצים לאפשר הצגת נתונים בלבד מתוך PHP.

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT SELECT
-> ON demo.*
-> TO demouser@localhost
-> IDENTIFIED BY 'demopasswd';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
[root@crypt root]# mysql -u demouser -p demo
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test1 (id int);
ERROR 1044: Access denied for user: 'demouser@localhost' to database 'demo'
mysql>

ניתן לראות כי אחרי שנתנו למשתמש רק הרשאת SELECT, הוא אינו יכול ליצור טבלאה (CREATE TABLE, יוסבר בהמשך).

דוגמא למתן הרשאה להתחברות משרת מרוחק:

mysql> GRANT ALL
-> ON demo.*
-> TO demouser@example.com
-> IDENTIFIED BY 'demopasswd';
Query OK, 0 rows affected (0.00 sec)

שימו לב לשורה “TO demouser@example.com” שמאפשרת גישה מהדומיין example.com למשתמש יחיד. לרוב אין סיבה לתת הרשאה למשתמש מרוחק. שימוש באופציה זו נעשה לרוב כאשר שרת האפליקציה (שוב, למשל PHP שמציג נתונים ממסד) יושב על מחשב נפרד מהמחשב שמריץ את מסד הנתונים.

נעבור לפקודה REVOKE. הפקודה דומה בתחביר לפקודה GRANT. במקום להעניק הרשאה, היא שוללת אותה. דוגמא לשלילת כל ההרשאות (בעצם חסימתו) של המשתמש demouser על מסד הנתונים demo:

[root@crypt root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> REVOKE ALL
-> ON demo.*
-> FROM demouser@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>

לא ארחיב עוד על הפקודה. היא מספיק דומה ל GRANT כדי ששאר הדוגמאות יחולו עליה בשינוי קטן. כאשר אסביר יותר על פקודות נוספות, תוכלו לתצר את הפקודה כך שתתאים לכם (למשל נתינת הרשאה שליפה ועדכון נתונים בלבד למשתמש מסויים, והרשאת מחיקה והוספה של טבלאות לאחר)

קישור לפקודות שצוינו בפרק: GRANT & REVOKE - http://www.mysql.com/doc/en/GRANT.html

יצירה ושינוי של טבלה

עכשיו שיש לנו מסד נתונים ולמדנו איך לתת הרשאה למשתמשים לנהלו, ולפני שנוכל להזין אליו נתונים בדרך זו או אחרת, נצטרך לבנות טבלאות כדי לשמור את הנתונים.
בפרק זה אסביר על 2 פקודות, הראשונה היא תת-פקודה של CREATE, ונקראת CREATE TABLE, והשניה, נקראת ALTER TABLE, תעזור לנו לבצע שינויים בטבלה (בשמות וסוג השדות, לא בתוכן).
קצת על מבנה הטבלאות ב- MySQL: לכל עמודה בטבלה יש סוג תוכן (int, text, varchar), שעליהם ניתן למצוא תיעוד רחב יותר בכתובת http://www.mysql.com/doc/en/Column_types.html

כל סוג מצביע לנו על איזה מידע יהיה בעמודה, ואיזה כמות של מידע ניתן להכניס. מומלץ מאוד להיכנס לקישור הנ”ל וללמוד קצת על סוגי העמודות האפשריים, שכן המדריך אינו מרחיב יותר בנושא.
ישנם כמה מאפיינים נוספים לכל עמודה, שבעזרתם ניתן לציין האם העמודה תקבל ערכי NULL, מה יהיה ערך בררת המחדל במקרה שלא יוזן כלום, האם העמודה היא עמודת המפתח של הטבלאה (Primary Key), והאם העמודה היא עמודה בעלת מספר רץ (auto_incremet). לפני יצירת הטבלה, רצוי לתכנן קודם למה היא תשמש ומה יהיו שמות וסוגי העמודות, רצוי שהשמות יהיו הגיוניים למען שימוש עתידי קל יותר. לדוגמא, לא מומלץ לבנות טבלה בעלת עמודות עם שמות כמו “1”, “2” ו- “3”, עדיף יהיה להשתמש בשמות כמו “id”, “name” ו “email”.
לצורך הדוגמא שלנו, נבנה טבלה שתכיל שמות וכתובות אימייל, ותהיה בעלת עמודת אינדקס עם מספר רץ, שיהיה מפתח הטבלה. עמודת השמות תהיה מסוג tinytext ועמודת הכתובות, תהיה varchar, עד 40 תוים. שורת ה- SQL התואמת תהיה:

# mysql -u root -p demo
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE demo (id INT NOT NULL AUTO_INCREMENT, name TEXT, email VARCHAR(40) NOT NULL , PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)

כדי לראות שהטבלה אכן קיימת, ולראות את המבנה שלה, נשתמש בשתי פקודות הצגה חדשות, SHOW ו DESCRIBE:

mysql> SHOW TABLES;
+----------------+
| Tables_in_demo |
+----------------+
| demo |
+----------------+
1 row in set (0.00 sec)

mysql> DESCRIBE demo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| email | varchar(40) | | | | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

אנחנו יכולים לראות שהטבלה נמצאת ברשימת הטבלאות, ואח”כ את מבנה הטבלה, כל עמודה והסוג שלה.

אחרי שיצרנו את הטבלה, ניתן להשתמש בפקודה ALTER, על מנת לשנות מאפייני עמודות. הנה כמה דוגמאות. שינוי שדה ה- id, מ int ל tinyint:

mysql> ALTER TABLE demo MODIFY id tinyint auto_increment;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE demo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| email | varchar(40) | | | | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.27 sec)

אפשר לראות שסוג השדה הוחלף ל TINYINT. שימו לב, בזמן שינוי סוג שדה, יש לציין את כל המאפיינים שרוצים שיהיו לו, אחרת הם ימחקו (אפשר להסתכל על השינוי כמחיקה של עמודה ויצירה מחדש, אם היה לי ערך ברירת מחדל, ולא ציינתי אותו בשינוי, הוא לא יהיה בעמודה החדשה). דוגמא נוספת, הספת עמודה אחרי עמודה name, שתכיל תוכן מסוג תאריך:

mysql> ALTER TABLE demo ADD date DATETIME AFTER name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE demo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| date | datetime | YES | | NULL | |
| email | varchar(40) | | | | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

דוגמא אחרונה, מחיקת עמודה, נמחק את העמודה החדשה שרק הוספנו:

mysql> ALTER TABLE demo DROP date;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE demo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| email | varchar(40) | | | | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

קישורים לתחביר מלא של הפקודות בפרק:

CREATE TABLE - http://www.mysql.com/doc/en/CREATE_TABLE.html ALTER - http://www.mysql.com/doc/en/ALTER_TABLE.html

הכנסה ושליפה של נתונים

עכשיו שיש לנו טבלה, עם כל השדות שאנו צריכים, ניגש למלאכה של הזנת מידע לטבלה. נשתמש בפקודה INSERT.
בזמן ההזנה, יש לשים לב שטקסט מוזן ע”י תחימה עם גרש (דוגמא: 'text') ואלו מספרים, לא חייבים. בנוסף ישנם תוים מיוחדים שבשימוש MySQL, שנצטרך להגיד ל- MySQL שאנו רוצים להכניס אותם למסד כטקסט, ולא כתו מיוחד (לדוגמא התו ' שמשמש לתחימת תוכן, נצטרך להכניסו כ '\, פעולה שנקראת character escaping). נעבור לכמה דוגמאות. בכלליות, בפקודה INSERT מציינים איזה שדות נרצה להזין, ואז את הערכים שנרצה להכניס.

mysql> DESCRIBE demo;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | tinyint(4) | | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| email | varchar(40) | | | | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO demo (id,name,email) VALUES ('','demo name','demo@email.com');
Query OK, 1 row affected (0.01 sec)

שימו לב שלשדה id לא הכנסתי ערך, בגלל שזהו שדה auto_increment, כלומר מספר רץ, הוא מתמלא אוטומאטית בזמן יצירת רשומה חדשה. אם נוותר על שדה ה- id, עדיין יווצר id חדש: ,

mysql> INSERT INTO demo (name,email) VALUES ('demo name','demo@email.com');
Query OK, 1 row affected (0.00 sec)

אם מזינים נתונים לכל השדות, ניתן לוותר על שמות השדות שנזין:

mysql> INSERT INTO demo VALUES ('','demo name','demo@email.com');
Query OK, 1 row affected (0.00 sec)

דומא נוספת, שמראה הכנסה של נתונים עם תו מיוחד:

mysql> INSERT INTO demo (name,email) VALUES ('demo name','demo@e'mail.com');
'>

ניתן לראות כי הפקודה אינה מבוצעת כראוי, ואנו מתבקשים להזין את המשך הפקודה, למרות שסיימנו ב ”;”, זאת מכיוון ש- MySQL אינו מזהה כי התו - ' בתוך נתוני האימייל אינו תו מיוחד. על מנת לפתור את הבעיה נשתמש, בתו \ כדי שיזהה כי זהו מידע (character escapint):

mysql> INSERT INTO demo (name,email) VALUES ('demo name','demo@e\'mail.com');
Query OK, 1 row affected (0.00 sec)

הפעם הפעולה הצליחה.

כדי לראות את הנתונים שהזנו, נשלוף אותם בעזרת הפקודה SELECT. הפקודה SELECT היא פקודה גמישה שמאפשרת שליפה ע”י הצלבה בין כמה טבלאות וחיבור הנתונים, שליפה של כמות מסויימת של רשומות, שליפת רשומה ראנדומאלית, שליפה לפי ערך מסויים מהטבלאה ועוד ועוד… אנחנו נסקור רק כמה פשוטות יותר. הדוגמא הפשוטה ביותר, היא שליפת כל הנתונים מהטבלה:

mysql> SELECT * FROM demo;
+----+-----------+-----------------+
| id | name | email |
+----+-----------+-----------------+
| 1 | demo name | demo@email.com |
| 2 | demo name | demo@email.com |
| 3 | demo name | demo@email.com |
| 4 | demo name | demo@e'mail.com |
+----+-----------+-----------------+
4 rows in set (0.00 sec)

אפשר לראות שקיבלו את כל המידע שמכילה הטבלה, בלי יוצא מן הכלל. אם נרצה למשל לשלוף רק את עמודת ה- email, נשתמש בפקודה:

mysql> SELECT email FROM demo;
+-----------------+
| email |
+-----------------+
| demo@email.com |
| demo@email.com |
| demo@email.com |
| demo@e'mail.com |
+-----------------+
4 rows in set (0.00 sec)

עוד דוגמא, יהיה שליפת מידע לפי קריטריון, למשל שליפת הרשומה שה- id שלה הוא 2:

mysql> SELECT * FROM demo WHERE id=2;
+----+-----------+----------------+
| id | name | email |
+----+-----------+----------------+
| 2 | demo name | demo@email.com |
+----+-----------+----------------+
1 row in set (0.01 sec)

התוספת “WHERE” היא התנאי שלנו. שימו לב, שבדומה ל INSERT, מספרים אינם חייבים להיות תחומים בגרש, לעומת טקסט שכן צריך:

mysql> SELECT * FROM demo WHERE email=demo@email.com;
ERROR 1064: You have an error in your SQL syntax near '@email.com' at line 1
mysql> SELECT * FROM demo WHERE email='demo@email.com';
+----+-----------+----------------+
| id | name | email |
+----+-----------+----------------+
| 1 | demo name | demo@email.com |
| 2 | demo name | demo@email.com |
| 3 | demo name | demo@email.com |
+----+-----------+----------------+
3 rows in set (0.00 sec)

דוגמא נוספת לשליפה עם קריטריונים, תהיה שליפה של 2 רשומות בלבד (מתוך ה- 4):

mysql> SELECT * FROM demo limit 0,2;
+----+-----------+----------------+
| id | name | email |
+----+-----------+----------------+
| 1 | demo name | demo@email.com |
| 2 | demo name | demo@email.com |
+----+-----------+----------------+
2 rows in set (0.00 sec)

קישורים לתחביר מלא של הפקודות בפרק:

INSERT - http://www.mysql.com/doc/en/INSERT.html UPDATE - http://www.mysql.com/doc/en/UPDATE.html

שינוי ומחיקה של נתונים בטבלה

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

mysql> SELECT * FROM demo;
+----+-----------+-----------------+
| id | name | email |
+----+-----------+-----------------+
| 1 | demo name | demo@email.com |
| 2 | demo name | demo@email.com |
| 3 | demo name | demo@email.com |
| 4 | demo name | demo@e'mail.com |
+----+-----------+-----------------+
4 rows in set (0.03 sec)

mysql> UPDATE demo SET name='updated named';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> SELECT * FROM demo;
+----+---------------+-----------------+
| id | name | email |
+----+---------------+-----------------+
| 1 | updated named | demo@email.com |
| 2 | updated named | demo@email.com |
| 3 | updated named | demo@email.com |
| 4 | updated named | demo@e'mail.com |
+----+---------------+-----------------+
4 rows in set (0.00 sec)

mysql>

ניתן לראות, כי באמת כל השדות עודכנו. דוגמא לעידכון של רשומה מסויימת:

mysql> SELECT * FROM demo;
+----+---------------+-----------------+
| id | name | email |
+----+---------------+-----------------+
| 1 | updated named | demo@email.com |
| 2 | updated named | demo@email.com |
| 3 | updated named | demo@email.com |
| 4 | updated named | demo@e'mail.com |
+----+---------------+-----------------+
4 rows in set (0.00 sec)

mysql> UPDATE demo SET email='updated@email.com' WHERE id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM demo;
+----+---------------+-------------------+
| id | name | email |
+----+---------------+-------------------+
| 1 | updated named | demo@email.com |
| 2 | updated named | demo@email.com |
| 3 | updated named | demo@email.com |
| 4 | updated named | updated@email.com |
+----+---------------+-------------------+
4 rows in set (0.00 sec)

ובאמת ניתן לראות כי רק רשומה בעלת id=4 עודכנה. ניתן לעדכן גם כמה שדות באותה הרשומה במקביל:

mysql> SELECT * FROM demo WHERE id=4;
+----+---------------+-------------------+
| id | name | email |
+----+---------------+-------------------+
| 4 | updated named | updated@email.com |
+----+---------------+-------------------+
1 row in set (0.00 sec)

mysql> UPDATE demo SET name='new name',email='new@email.com' WHERE id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM demo WHERE id=4;
+----+----------+---------------+
| id | name | email |
+----+----------+---------------+
| 4 | new name | new@email.com |
+----+----------+---------------+
1 row in set (0.00 sec)

נעבור למחיקת רשומות. בדומה לעידכון רשומות, אם לא נציין שום תנאי WHERE, ימחקו כל הרשומות בטבלה (כי בעצם כולן עונות לקריטריון, זה שווה ערך לשימוש ב “WHERE 1”, תנאי שתמיד מתקיים).
דוגמא למחיקת טבלה שלמה (רק את התוכן, לא את הטבלה עצמה):

mysql> DELETE FROM demo;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM demo;
Empty set (0.00 sec)

שיטה מאוד לא מומלצת (אלא אם אתם רוצים למחוק תוכן של טבלאה שלמה כמובן :-))/ לרוב, נרצה למחוק רק רשומה אחת (או יותר) שעונה לקריטריון שנציב:

mysql> SELECT * FROM demo;
+----+---------------+----------------+
| id | name | email |
+----+---------------+----------------+
| 1 | updated named | demo@email.com |
| 2 | updated named | demo@email.com |
| 3 | updated named | demo@email.com |
| 4 | new name | new@email.com |
+----+---------------+----------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM demo WHERE id=2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM demo;
+----+---------------+----------------+
| id | name | email |
+----+---------------+----------------+
| 1 | updated named | demo@email.com |
| 3 | updated named | demo@email.com |
| 4 | new name | new@email.com |
+----+---------------+----------------+
3 rows in set (0.00 sec)

ניתן לראות כי רק רשומה ש id=2 נמחקה. ניתן לתת תנאי יותר כולל, ואז ימחקו כמה רשומות:

mysql> DELETE FROM demo WHERE email='demo@email.com';
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM demo;
+----+----------+---------------+
| id | name | email |
+----+----------+---------------+
| 4 | new name | new@email.com |
+----+----------+---------------+
1 row in set (0.00 sec)

עוד משהו לשים לב אליו, הוא הפלט של פעולת המחיקה, שמציין כי מספר הרשומות שהושפעו מהפקודה (במקרה זה נמחקו) הוא 2 (“2 rows affected”, למי שלא שם לב :-)).

קישורים לפקודות שמופיעות בפרק: UPDATE - http://www.mysql.com/doc/en/UPDATE.html DELETE - http://www.mysql.com/doc/en/DELETE.html

מחיקת טבלה ומסד נתונים

בפרק האחרון של שימוש בשורת הפקודה של MySQL, אראה כיצד מוחקים את הטבלה שיצרנו ואחריה את מסד הנתונים כולו (שימו לב: מחיקת מסד הנתונים, תמחוק כמובן את כל הטבלאות שבתוכו).
כדי למחוק טבלה או מסד נתונים שלם, נשתמש בפקודה DROP. הפקודה היא פקודה חזקה, ויש להיזהר בשימוש בה.

דוגמא למחיקת טבלה שלמה. כדי למחוק, נשתמש במאפיין TABLE של DROP:

mysql> SHOW TABLES;
+----------------+
| Tables_in_demo |
+----------------+
| demo |
+----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE demo;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

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

כדאי למחוק מסד נתונים שלם, נשתמש בפקודה DROP שוב, עם תוספת DATABASE:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| demo |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
mysql> DROP DATABASE demo;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)

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

קישורים לפקודות מפרק זה: DROP TABLE - http://www.mysql.com/doc/en/DROP_TABLE.html
DROP DATABASE - http://www.mysql.com/doc/en/DROP_DATABASE.html

כלי ניהול

ייצוא ויבוא של נתונים

אחרי שלמדנו לעבוד עם MySQL, ולמדנו כיצד ליצור מסד ולהזין אליו נתונים, מגיע הרגע שנרצה לגבות את המסד, למקרה של מחיקה בשוגג של המידע או כל צרה אחרת שלא תבוא.
לשם כך, נשתמש בכלי מוסף שבא עם חבילת MySQL, שנקרא mysqldump. הכלי קורא את כל התוכן של מסד שנבחר (או אפילו רק טבלה מתוך מסד), ושולח את כל תוכן הטבלה למסך, וכך מאפשר לנו בעזרת הפניית פלט, לשמור אותו לקובץ שנרצה. התחביר הכללי הוא:

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

הכלי, כמו שמראה התחביר, מאפשר לבצע dump של מסד מסויים, של טבלה בתוך מסד, או אפילו של כל המסדים שקיימים במערכת. דוגמא של הרצת mysqldump על המסד שלנו, demo:

# mysqldump -u root -p demo
Enter password:
-- MySQL dump 8.22
-- 
-- Host: localhost Database: demo
---------------------------------------------------------
-- Server version 3.23.54

-- 
-- Table structure for table 'demo'
-- 

CREATE TABLE demo (
id int(11) NOT NULL auto_increment,
name text,
email varchar(40) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

-- 
-- Dumping data for table 'demo'
-- 


INSERT INTO demo VALUES (1,'demo name','demo@email.com');
INSERT INTO demo VALUES (2,'demo name','demo@email.com');
INSERT INTO demo VALUES (3,'demo name','demo@email.com');
INSERT INTO demo VALUES (4,'demo name','demo@email.com');

ניתן לראות שלמסך נשלח פקודת היצירה של הטבלה היחידה שיש בתוך המסד demo, הטבלה demo (איזה הפתעה), ופקודות ה INSERT לכל המידע הקיים במסד כרגע. ברור לעין, שהרצה של סדרת פקודות אלו מתוך שורת הפקודה של MySQL, תביא את מסד הנתונים שלו למצב שבוא הוא נמצא ברגע הרצת mysqldump. אופציות נוספות ורלוונטיות לפקודה mysqldump הן:

--add-drop-tables

שיוסיף את הפקודה:

DROP TABLE <table name>

אופציה זו יכולה לשמש אותנו כאשר רוצים לשחזר מעל מסד קיים, ולא רוצה שתהיה התנגשות עם מידע קיים. קודם תמחק הטבלה, ואז תיווצר מחדש ע”י הפלט של mysqldump.

-A or --all-databases

גיבוי של כל מסדי הנתונים במערכת. טוב בשביל גיבוי כולל של המערכת.

--complete-insert

בונה את שורת ה INSERT באופן מפורט יותר:

INSERT INTO demo (id, name, email) VALUES (1,'demo name','demo@email.com');

את הפירוט המלא ניתן כמובן למצוא הדף ה- man של mysqldump, ע”י הרצת הפקודה man mysqldump.

על מנת לשמור את הפלט לתוך קובץ, נשתמש בהפניית פלט, תכונה של המעטפת (shell) שמאפשר הפניית פלט של תוכנה לקובץ, במקום למסך. ניתן לקרוא על כלי זה במדריך על צינורות והפניית פלט

נפנה את הפלט לקובץ שנקרא backup.sql:

# mysqldump -u root -p --add-drop-table demo > backup.sql
Enter password:

כדי להיווכח שהמידע אכן נשמר, ניתן לבצע את הפקודה:

# cat backup.sql

עכשיו שיש לנו קובץ גיבוי, כיצד נשחזר אותו בשעת הצורך? הכלי mysql מאפשר לנו זאת. הוא יודע לקרוא את קבצי ה- dump שמייצר mysqldump, שוב בעזרת הפניית פלט (או יותר נכון קלט במקרה שלנו). התחביר הוא פשוט:

# mysql -u root -p [db name] < <dump file>

או במקרה שלנו:

# mysql -u root -p demo < backup.sql

דוגמא לשחזור, אחרי מחיקת הטבלה demo:

# mysql -u root -p demo
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE demo;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> quit
Bye
# mysql -u root -p demo < backup.sql
Enter password:
# mysql -u root -p demo
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES;
+----------------+
| Tables_in_demo |
+----------------+
| demo |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo;
+----+-----------+----------------+
| id | name | email |
+----+-----------+----------------+
| 1 | demo name | demo@email.com |
| 2 | demo name | demo@email.com |
| 3 | demo name | demo@email.com |
| 4 | demo name | demo@email.com |
+----+-----------+----------------+
4 rows in set (0.00 sec)

ניתן לראות שלאחר מחיקת הנתונים, וייבוא שלהם מהקובץ backup.sql, המצב הוחזר לקדמותו.

זה היה על קצה המזלג גיבוי ושחזור של נתונים בעזרת הכלי mysqldump. הכלי גמיש מאוד, ומשתלב טוב עם תסריטי גיבוי (Backup scripts).

תוכנת ניהול גראפית - phpMyAdmin

phpMyAmin היא תוכנת ניהול, בעלת ממשק web לניהול מסדי נתונים וטבלאות בשביל MySQL. התוכנה נכתבה ב- php, ויודעת לנהל מספר מסדי נתונים במקביל (באותה המערכת). התוכנה יודעת לנהל בצורה גראפית מסד נתונים, כאשר זה כולל, בין השאר, הוספה ומחיקה של טבלות/מסדי נתונים, שינוי ועידכון של נתונים לטבלות, גיבוי, שיחזור ועוד הרבה. פרק זה לא ידון בהתקנת phpMyAdmin או בקינפוגה, כל המידע הזה זמין בתיעוד של phpMyAdmin. מצורפות כמה תמונות להדגמה (התמונות הן של גירסא 2.3.0, כיום כבר זמינה 2.3.3):

הטבלה המוצגת היא טבלאת ההרשאות של MySQL, הטבלה בה נשמרים הנתונים על המשתמשים וההרשאות שלהם (ראו פרק 2.4). מומלץ להשתמש בכלי זה (או אחר דומה) כאשר יש לכם הרבה טבלאות ליצור, או מסד נתונים לנהל באופן יומיומי. הכלי טוב גם כאשר יש לך מסד נתונים לנהל, ואין לכם גישה לשורת הפקודה (למשל במקרה של אתר שיושב אצל ספק).

אפשר למצוא את phpMyAdmin בכתובת http://www.phpmyadmin.net/

מידע נוסף

את התיעוד הרשמי של MySQL ניתן למצוא באתר http://dev.mysql.com/doc/

מדריכים/מבוא_ל-mysql.txt · שונה לאחרונה ב: 2008/06/19 18:34 (עריכה חיצונית)
chimeric.de = chi`s home Creative Commons License Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0