دنباله‌ها، داده‌های تکراری، تزریق SQL و توابع کاربردی SQL

استفاده از دنباله (Sequence) در SQL

Sequence (دنباله) مجموعه‌ای از اعداد صحیح مانند ۱، ۲، ۳ و ... است که هنگام نیاز به‌ترتیب تولید می‌شوند. دنباله‌ها در پایگاه داده کاربرد زیادی دارند، زیرا بسیاری از برنامه‌ها لازم دارند هر سطر جدول یک مقدار یکتا داشته باشد و دنباله راه ساده‌ای برای تولید این مقدارها فراهم می‌کند. این بخش روش استفاده از دنباله در MySQL را توضیح می‌دهد.

استفاده از ستون AUTO_INCREMENT

ساده‌ترین راه استفاده از دنباله در MySQL این است که یک ستون با ویژگی AUTO_INCREMENT تعریف شود و تولید مقدارهای بعدی به MySQL سپرده شود.

mysql> CREATE TABLE INSECT
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');

در این درج‌ها لازم نیست شناسهٔ رکورد به‌صورت دستی داده شود؛ MySQL مقدار آن را خودکار تولید می‌کند.

mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+

دریافت مقدار AUTO_INCREMENT

LAST_INSERT_ID() یک تابع SQL است و از هر کلاینتی که بتواند دستور SQL اجرا کند قابل استفاده است. متن منبع همچنین نمونه‌های اختصاصی Perl و PHP را برای دریافت مقدار خودافزایشی آخرین رکورد ارائه می‌کند.

نمونهٔ Perl

در Perl می‌توان از ویژگی mysql_insertid استفاده کرد. این ویژگی بسته به نحوهٔ اجرای پرس‌وجو از هندل پایگاه داده یا هندل دستور قابل دسترسی است:

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

نمونهٔ PHP

پس از پرس‌وجویی که مقدار AUTO_INCREMENT تولید می‌کند، متن منبع تابع mysql_insert_id() را برای دریافت آن نشان می‌دهد:

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

شماره‌گذاری مجدد یک دنبالهٔ موجود

ممکن است پس از حذف رکوردهای زیاد، نیاز به شماره‌گذاری مجدد رکوردها احساس شود. متن منبع هشدار می‌دهد که اگر جدول با جدول‌های دیگر JOIN (پیوند) دارد، این کار باید با احتیاط بسیار انجام شود. اگر شماره‌گذاری مجدد ستون AUTO_INCREMENT اجتناب‌ناپذیر باشد، روش ارائه‌شده حذف ستون و افزودن دوبارهٔ آن است:

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

شروع دنباله از مقدار مشخص

MySQL به‌طور پیش‌فرض دنباله را از ۱ شروع می‌کند، اما می‌توان هنگام ساخت جدول مقدار دیگری تعیین کرد. مثال منبع دنباله را از ۱۰۰ آغاز می‌کند:

mysql> CREATE TABLE INSECT
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL,
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
);

راه دیگر این است که پس از ساخت جدول مقدار آغازین با ALTER TABLE تنظیم شود:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

مدیریت داده‌های تکراری در SQL

ممکن است یک جدول چند رکورد تکراری داشته باشد. هنگام بازیابی، در بسیاری از موارد منطقی‌تر است فقط رکوردهای یکتا دریافت شوند. کلیدواژهٔ DISTINCT همراه SELECT رکوردهای تکراری را حذف و فقط مقدارهای یکتا را برمی‌گرداند.

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition];

در جدول CUSTOMERS، پرس‌وجوی زیر حقوق‌ها را مرتب می‌کند و مقدار 2000.00 دو بار ظاهر می‌شود:

SQL> SELECT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

با افزودن DISTINCT مقدار تکراری حذف می‌شود:

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

تزریق SQL (SQL Injection)

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

اصل مطرح‌شده در متن منبع این است که به دادهٔ ورودی کاربر اعتماد نشود و داده فقط پس از اعتبارسنجی پردازش شود. نمونهٔ زیر نام کاربری را به نویسه‌های حرفی/عددی و زیرخط با طول ۸ تا ۲۰ محدود می‌کند:

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM CUSTOMERS
                          WHERE name=$matches[0]");
}
else
{
   echo "user name not accepted";
}

برای نمایش مسئله، متن منبع ورودی مخرب زیر را مثال می‌زند:

// supposed input
$name = "Qadir'; DELETE FROM CUSTOMERS;";
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");

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

متن منبع توضیح می‌دهد که در MySQL تابع قدیمی mysql_query() اجازهٔ Query Stacking (اجرای چند پرس‌وجو در یک فراخوانی) را نمی‌دهد و چنین فراخوانی‌ای شکست می‌خورد، اما برخی افزونه‌های دیگر PHP مانند SQLite و PostgreSQL در نمونهٔ تاریخی منبع می‌توانند پرس‌وجوهای پشته‌شده را اجرا کنند و در نتیجه خطر جدی ایجاد شود.

پیشگیری از تزریق SQL

در زبان‌های اسکریپتی مانند Perl و PHP باید نویسه‌های ویژهٔ ورودی به‌درستی Escape شوند. متن منبع برای افزونهٔ قدیمی MySQL در PHP از mysql_real_escape_string() استفاده می‌کند:

if (get_magic_quotes_gpc())
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

مسئلهٔ LIKE

برای استفاده از ورودی کاربر در LIKE باید نویسه‌های جایگزین % و _ نیز به Literal تبدیل شوند. متن منبع تابع addcslashes() را برای تعیین مجموعهٔ نویسه‌هایی که باید Escape شوند نشان می‌دهد:

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == \%str\_
mysql_query("SELECT * FROM messages
             WHERE subject LIKE '{$sub}%'");

توابع کاربردی SQL

SQL توابع درون‌ساخت بسیاری برای پردازش داده‌های رشته‌ای و عددی دارد. متن منبع در این بخش ابتدا توابع زیر را معرفی می‌کند:

• COUNT: شمارش تعداد سطرها.
• MAX: انتخاب بیشترین مقدار یک ستون.
• MIN: انتخاب کمترین مقدار یک ستون.
• AVG: محاسبهٔ میانگین یک ستون.
• SUM: محاسبهٔ مجموع یک ستون عددی.
• SQRT: محاسبهٔ ریشهٔ دوم.
• RAND: تولید عدد تصادفی.
• CONCAT: به‌هم‌چسباندن رشته‌ها.
• توابع عددی SQL برای دستکاری و محاسبات عددی.
• توابع رشته‌ای SQL برای دستکاری رشته‌ها.

جدول نمونهٔ employee_tbl

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |
|    3 | Jack | 2007-05-06 |                170 |
|    3 | Jack | 2007-04-06 |                100 |
|    4 | Jill | 2007-04-06 |                220 |
|    5 | Zara | 2007-06-06 |                300 |
|    5 | Zara | 2007-02-06 |                350 |
+------+------+------------+--------------------+

COUNT

COUNT تعداد رکوردهایی را می‌شمارد که انتظار می‌رود یک SELECT برگرداند.

SQL> SELECT COUNT(*) FROM employee_tbl;
-- 7

SQL> SELECT COUNT(*) FROM employee_tbl
    -> WHERE name="Zara";
-- 2

MAX

MAX بیشترین مقدار را از یک مجموعه رکورد پیدا می‌کند.

SQL> SELECT MAX(daily_typing_pages)
    -> FROM employee_tbl;
-- 350

با GROUP BY می‌توان بیشترین مقدار هر شخص را محاسبه کرد:

SQL> SELECT id, name, MAX(daily_typing_pages)
    -> FROM employee_tbl GROUP BY name;

Jack  170
Jill  220
John  250
Ram   220
Zara  350

منبع همچنین ترکیب MIN و MAX را نشان می‌دهد:

SQL> SELECT MIN(daily_typing_pages) least,
    -> MAX(daily_typing_pages) max
    -> FROM employee_tbl;
-- least = 100, max = 350

MIN

MIN کمترین مقدار را در مجموعهٔ رکوردها پیدا می‌کند:

SQL> SELECT MIN(daily_typing_pages)
    -> FROM employee_tbl;
-- 100

کمترین مقدار برای هر نام با GROUP BY در نتیجهٔ منبع چنین است: Jack=100، Jill=220، John=250، Ram=220 و Zara=300.

AVG

AVG میانگین یک فیلد را در چند رکورد محاسبه می‌کند.

SQL> SELECT AVG(daily_typing_pages)
    -> FROM employee_tbl;
-- 230.0000

با گروه‌بندی بر اساس نام، میانگین صفحات تایپ‌شده برای هر فرد طبق نتیجهٔ منبع: Jack=135.0000، Jill=220.0000، John=250.0000، Ram=220.0000 و Zara=325.0000.

SUM

SUM مجموع یک فیلد را در چند رکورد محاسبه می‌کند:

SQL> SELECT SUM(daily_typing_pages)
    -> FROM employee_tbl;
-- 1610

مجموع برای هر نام با GROUP BY طبق نتیجهٔ منبع: Jack=270، Jill=220، John=250، Ram=220 و Zara=650.

SQRT

SQRT ریشهٔ دوم یک عدد را محاسبه می‌کند.

SQL> SELECT SQRT(16);
-- 4.000000

منبع توضیح می‌دهد که خروجی اعشاری است، زیرا SQL ریشهٔ دوم را در نوع دادهٔ شناور پردازش می‌کند. می‌توان تابع را روی رکوردها نیز اجرا کرد:

SQL> SELECT name, SQRT(daily_typing_pages)
    -> FROM employee_tbl;

John  15.811388
Ram   14.832397
Jack  13.038405
Jack  10.000000
Jill  14.832397
Zara  17.320508
Zara  18.708287

RAND

RAND() عدد تصادفی بین ۰ و ۱ تولید می‌کند:

SQL> SELECT RAND(), RAND(), RAND();
-- 0.45464584925645 | 0.1824410643265 | 0.54826780459682

اگر یک آرگومان صحیح داده شود، آن مقدار Seed (بذر) مولد عدد تصادفی است و استفادهٔ دوباره از همان بذر یک دنبالهٔ تکرارپذیر ایجاد می‌کند:

SQL> SELECT RAND(1), RAND(), RAND();
-- 0.18109050223705 | 0.75023211143001 | 0.20788908117254

برای تصادفی‌کردن ترتیب سطرها می‌توان از ORDER BY RAND() استفاده کرد. متن منبع دو اجرای پشت‌سرهم را نشان می‌دهد که ترتیب متفاوتی از همان هفت رکورد employee_tbl برمی‌گردانند:

SQL> SELECT * FROM employee_tbl ORDER BY RAND();

CONCAT

CONCAT دو یا چند رشته را به یک رشته تبدیل می‌کند:

SQL> SELECT CONCAT('FIRST ', 'SECOND');
-- FIRST SECOND

برای ترکیب شناسه، نام و تاریخ کاری هر کارمند:

SQL> SELECT CONCAT(id, name, work_date)
    -> FROM employee_tbl;

1John2007-01-24
2Ram2007-05-27
3Jack2007-05-06
3Jack2007-04-06
4Jill2007-04-06
5Zara2007-06-06
5Zara2007-02-06

فهرست توابع عددی SQL

توابع عددی عمدتاً برای دستکاری عددها و محاسبات ریاضی استفاده می‌شوند. متن منبع در پایان این بخش فهرست زیر را ارائه می‌کند؛ جزئیات و مثال‌های هر تابع در بخش بعدی مجموعه آمده است.