تغییر ساختار جدول، پاک‌سازی جدول، نما، HAVING، تراکنش و نویسه‌های جایگزین در SQL

دستور ALTER TABLE در SQL

دستور ALTER TABLE برای افزودن، حذف یا تغییر ستون‌های یک جدول موجود استفاده می‌شود. همچنین می‌توان از آن برای افزودن یا حذف انواع محدودیت‌ها روی جدول موجود بهره گرفت.

نحوهای اصلی

افزودن ستون:

ALTER TABLE table_name ADD column_name datatype;

حذف ستون:

ALTER TABLE table_name DROP COLUMN column_name;

تغییر نوع دادهٔ ستون:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

افزودن محدودیت NOT NULL:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

افزودن محدودیت UNIQUE:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

افزودن محدودیت CHECK:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

افزودن کلید اصلی:

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

حذف یک محدودیت:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

در MySQL، منبع برای حذف محدودیت یکتا این صورت را نشان می‌دهد:

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

حذف محدودیت کلید اصلی:

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

و در MySQL:

ALTER TABLE table_name
DROP PRIMARY KEY;

مثال ALTER TABLE

در جدول نمونهٔ CUSTOMERS، دستور زیر ستون جدیدی به نام SEX اضافه می‌کند:

ALTER TABLE CUSTOMERS ADD SEX char(1);

پس از افزودن ستون، مقدار آن برای رکوردهای موجود NULL است:

+----+---------+-----+-----------+----------+------+
| ID | NAME    | AGE | ADDRESS   | SALARY   | SEX  |
+----+---------+-----+-----------+----------+------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 | NULL |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 | NULL |
|  3 | kaushik |  23 | Kota      |  2000.00 | NULL |
|  4 | kaushik |  25 | Mumbai    |  6500.00 | NULL |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 | NULL |
|  6 | Komal   |  22 | MP        |  4500.00 | NULL |
|  7 | Muffy   |  24 | Indore    | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+

سپس منبع برای حذف این ستون مثال زیر را ارائه می‌کند:

ALTER TABLE CUSTOMERS DROP SEX;

پس از حذف، جدول دوباره شامل ستون‌های ID، NAME، AGE، ADDRESS و SALARY است.

TRUNCATE TABLE در SQL

دستور TRUNCATE TABLE برای حذف تمام داده‌های یک جدول موجود به‌کار می‌رود. می‌توان با DROP TABLE نیز کل جدول را حذف کرد، اما DROP TABLE ساختار جدول را نیز از پایگاه داده حذف می‌کند؛ در نتیجه برای ذخیرهٔ دوبارهٔ داده باید جدول را از نو ساخت.

TRUNCATE TABLE table_name;

مثال:

SQL> TRUNCATE TABLE CUSTOMERS;
SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)

پس از TRUNCATE، ساختار CUSTOMERS باقی است، اما هیچ رکوردی در آن وجود ندارد.

نما (View) در SQL

View (نما) چیزی بیش از یک دستور SQL ذخیره‌شده در پایگاه داده با یک نام مشخص نیست. یک نما در عمل ترکیبی از دادهٔ جدول است که به شکل یک پرس‌وجوی SQL از پیش تعریف‌شده ارائه می‌شود. نما می‌تواند همهٔ سطرهای جدول یا فقط برخی از آن‌ها را شامل شود و بسته به پرس‌وجوی سازندهٔ نما، از یک یا چند جدول ساخته شود.

نماها که نوعی جدول مجازی هستند، امکانات زیر را فراهم می‌کنند:

• ساختاربندی داده به روشی طبیعی و قابل‌درک برای یک کاربر یا گروهی از کاربران.
• محدودکردن دسترسی به داده تا کاربر فقط همان اطلاعات لازم را ببیند و در بعضی موارد تغییر دهد.
• خلاصه‌سازی دادهٔ چند جدول برای استفاده در گزارش‌ها.

ایجاد View

نما با CREATE VIEW ساخته می‌شود و می‌تواند از یک جدول، چند جدول یا حتی نمای دیگر ایجاد شود. کاربر باید مجوز سیستمی لازم را مطابق پیاده‌سازی DBMS داشته باشد.

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

نمونهٔ ساخت نمایی از نام و سن مشتریان:

SQL> CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;

نمای ساخته‌شده مانند یک جدول واقعی قابل پرس‌وجو است:

SQL> SELECT * FROM CUSTOMERS_VIEW;

+----------+-----+
| name     | age |
+----------+-----+
| Ramesh   |  32 |
| Khilan   |  25 |
| kaushik  |  23 |
| Chaitali |  25 |
| Hardik   |  27 |
| Komal    |  22 |
| Muffy    |  24 |
+----------+-----+

WITH CHECK OPTION

WITH CHECK OPTION گزینه‌ای برای CREATE VIEW است که تضمین می‌کند عملیات UPDATE و INSERT شرط‌های تعریف نما را رعایت کنند. در غیر این صورت عملیات با خطا مواجه می‌شود.

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

در این مثال، چون نما فقط داده‌هایی را می‌پذیرد که AGE در آن‌ها NULL نیست، ورود مقدار NULL برای ستون سن باید رد شود.

به‌روزرسانی View

متن منبع شرایط زیر را برای قابل‌به‌روزرسانی‌بودن یک نما فهرست می‌کند:

• بخش SELECT نباید شامل DISTINCT باشد.
• بخش SELECT نباید تابع‌های خلاصه‌ساز یا تابع‌های مجموعه‌ای داشته باشد.
• بخش SELECT نباید عملگرهای مجموعه‌ای داشته باشد.
• بخش SELECT نباید ORDER BY داشته باشد.
• بخش FROM نباید چند جدول داشته باشد.
• بخش WHERE نباید Subquery (زیرپرس‌وجو) داشته باشد.
• پرس‌وجو نباید GROUP BY یا HAVING داشته باشد.
• ستون‌های محاسبه‌شده قابل به‌روزرسانی نیستند.
• برای اینکه INSERT کار کند، همهٔ ستون‌های NOT NULL جدول پایه باید در نما حضور داشته باشند.

اگر نما این شرایط را داشته باشد، می‌توان آن را به‌روزرسانی کرد. مثال زیر سن Ramesh را به ۳۵ تغییر می‌دهد و این تغییر در جدول پایهٔ CUSTOMERS نیز اعمال می‌شود:

SQL> UPDATE CUSTOMERS_VIEW
     SET AGE = 35
     WHERE name='Ramesh';

درج سطر در View

می‌توان در یک نما سطر درج کرد و همان قواعد مربوط به UPDATE برای INSERT نیز اعمال می‌شوند. منبع توضیح می‌دهد که در نمونهٔ CUSTOMERS_VIEW امکان درج رکورد وجود ندارد، زیرا همهٔ ستون‌های NOT NULL جدول پایه در این نما قرار نگرفته‌اند. در نمایی که شرایط لازم را داشته باشد، درج سطر مانند درج در جدول انجام می‌شود.

حذف سطر از View

حذف رکورد از نما نیز همان قواعد UPDATE و INSERT را دارد. مثال زیر رکورد با سن ۲۲ را حذف می‌کند؛ در نتیجه سطر متناظر از جدول پایه نیز حذف شده و تغییر در نما دیده می‌شود:

SQL> DELETE FROM CUSTOMERS_VIEW
     WHERE age = 22;

حذف View

اگر دیگر به نما نیاز نباشد، می‌توان آن را با DROP VIEW حذف کرد:

DROP VIEW view_name;
DROP VIEW CUSTOMERS_VIEW;

HAVING در SQL

بند HAVING شرط‌هایی را تعیین می‌کند که مشخص می‌کنند کدام گروه‌ها در نتیجهٔ نهایی ظاهر شوند. WHERE روی سطرها یا ستون‌های انتخابی شرط می‌گذارد، در حالی که HAVING روی گروه‌هایی که با GROUP BY ساخته شده‌اند شرط اعمال می‌کند.

جایگاه HAVING در پرس‌وجو به این ترتیب است:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING باید پس از GROUP BY و، اگر ORDER BY وجود داشته باشد، پیش از آن قرار گیرد:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2;

مثال زیر گروه‌های سنی‌ای را نمایش می‌دهد که دست‌کم دو رکورد دارند:

SQL> SELECT *
     FROM CUSTOMERS
     GROUP BY age
     HAVING COUNT(age) >= 2;

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+
|  2 | Khilan |  25 | Delhi   | 1500.00 |
+----+--------+-----+---------+---------+

تراکنش (Transaction) در SQL

تراکنش یک واحد کاری است که روی پایگاه داده انجام می‌شود. تراکنش می‌تواند یک یا چند تغییر را به ترتیب منطقی دربر گیرد؛ این کار ممکن است دستی توسط کاربر یا خودکار توسط یک برنامهٔ پایگاه داده انجام شود. ایجاد، به‌روزرسانی یا حذف رکورد نمونه‌هایی از عملیات داخل تراکنش هستند. کنترل تراکنش برای حفظ یکپارچگی داده و مدیریت خطاهای پایگاه داده اهمیت دارد.

در عمل، چند پرس‌وجوی SQL در یک گروه قرار می‌گیرند و همه به‌عنوان بخشی از یک تراکنش اجرا می‌شوند.

ویژگی‌های ACID

• Atomicity (اتمی‌بودن): همهٔ عملیات واحد کاری باید با موفقیت کامل شوند؛ در غیر این صورت تراکنش در نقطهٔ خطا متوقف و عملیات پیشین به وضعیت قبل بازگردانده می‌شوند.
• Consistency (سازگاری): پس از ثبت موفق تراکنش، پایگاه داده باید به وضعیت معتبر و مناسب جدید منتقل شود.
• Isolation (انزوا): تراکنش‌ها بتوانند مستقل از یکدیگر و بدون ایجاد اثر نامناسب روی هم کار کنند.
• Durability (ماندگاری): نتیجه یا اثر تراکنش ثبت‌شده حتی در صورت خرابی سیستم باقی بماند.

دستورهای کنترل تراکنش

• COMMIT: ذخیرهٔ تغییرات.
• ROLLBACK: بازگرداندن تغییرات.
• SAVEPOINT: ایجاد نقاطی درون تراکنش که می‌توان تا آن نقطه بازگشت.
• SET TRANSACTION: تعیین ویژگی‌های تراکنش.

طبق متن منبع، دستورهای کنترل تراکنش برای دستورهای DML یعنی INSERT، UPDATE و DELETE به‌کار می‌روند و برای ایجاد یا حذف جدول استفاده نمی‌شوند، زیرا آن عملیات به‌طور خودکار ثبت می‌شوند.

COMMIT

COMMIT تغییراتی را که تراکنش در پایگاه داده ایجاد کرده ذخیره می‌کند و همهٔ تراکنش‌های انجام‌شده از آخرین COMMIT یا ROLLBACK را ثبت می‌کند.

COMMIT;

مثال منبع ابتدا مشتریان ۲۵ ساله را حذف و سپس تغییر را ثبت می‌کند:

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> COMMIT;

در نتیجه دو رکورد Khilan و Chaitali حذف می‌شوند و رکوردهای شناسهٔ ۱، ۳، ۵، ۶ و ۷ باقی می‌مانند.

ROLLBACK

ROLLBACK تراکنش‌هایی را که هنوز ذخیره نشده‌اند لغو می‌کند. این فرمان فقط تغییرات پس از آخرین COMMIT یا ROLLBACK را می‌تواند برگرداند.

ROLLBACK;

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> ROLLBACK;

در این مثال حذف روی جدول اثر نهایی ندارد و هر هفت رکورد اولیه باقی می‌مانند.

SAVEPOINT

SAVEPOINT نقطه‌ای درون تراکنش است که امکان بازگشت تا همان نقطه را بدون لغو کل تراکنش فراهم می‌کند.

SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;

نمونهٔ منبع سه نقطهٔ ذخیره پیش از حذف سه رکورد ایجاد می‌کند:

SQL> SAVEPOINT SP1;
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
SQL> SAVEPOINT SP2;
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
SQL> SAVEPOINT SP3;
SQL> DELETE FROM CUSTOMERS WHERE ID=3;

اگر پس از این عملیات دستور زیر اجرا شود:

SQL> ROLLBACK TO SP2;

دو حذف آخر لغو می‌شوند، زیرا SP2 پس از حذف نخست ایجاد شده بود. بنابراین فقط رکورد شناسهٔ ۱ حذف شده و شش سطر با شناسه‌های ۲ تا ۷ باقی می‌مانند.

RELEASE SAVEPOINT

برای حذف یک نقطهٔ ذخیره از دستور زیر استفاده می‌شود:

RELEASE SAVEPOINT SAVEPOINT_NAME;

پس از آزادکردن یک SAVEPOINT، دیگر نمی‌توان با ROLLBACK به آن نقطه برگشت.

SET TRANSACTION

SET TRANSACTION می‌تواند برای آغاز تراکنش و تعیین ویژگی‌های تراکنش بعدی استفاده شود. برای نمونه می‌توان تراکنش را فقط‌خواندنی یا خواندنی/نوشتنی تعیین کرد:

SET TRANSACTION [ READ WRITE | READ ONLY ];

نویسهٔ جایگزین (Wildcard) در SQL

عملگر LIKE برای مقایسهٔ یک مقدار با الگوهای مشابه از نویسه‌های جایگزین استفاده می‌کند. SQL در کنار LIKE دو نویسهٔ اصلی زیر را پشتیبانی می‌کند:

این نمادها را می‌توان با هم ترکیب کرد.

SELECT FROM table_name WHERE column LIKE 'XXXX%';
SELECT FROM table_name WHERE column LIKE '%XXXX%';
SELECT FROM table_name WHERE column LIKE 'XXXX_';
SELECT FROM table_name WHERE column LIKE '_XXXX';
SELECT FROM table_name WHERE column LIKE '_XXXX_';

می‌توان هر تعداد شرط را با AND یا OR ترکیب کرد و XXXX می‌تواند مقدار عددی یا رشته‌ای باشد.

مثال واقعی منبع، تمام مشتریانی را که مقدار SALARY آن‌ها با ۲۰۰ آغاز می‌شود انتخاب می‌کند:

SQL> SELECT * FROM CUSTOMERS
     WHERE SALARY LIKE '200%';

+----+---------+-----+-----------+----------+
| ID | NAME    | AGE | ADDRESS   | SALARY   |
+----+---------+-----+-----------+----------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik |  23 | Kota      |  2000.00 |
+----+---------+-----+-----------+----------+