تعریف اشیای پایگاه داده در SQL

این فصل تعریف جدول‌ها، بارگذاری جدول‌ها، قیود جامعیت، تعریف ایندکس‌ها و تعریف Viewها را پوشش می‌دهد.

تعریف جدول‌ها

جدول‌های جدید با واردکردن دستور CREATE TABLE در پایگاه داده تعریف می‌شوند. در ساده‌ترین شکل، این دستور شامل نام جدول جدید و یک یا چند تعریف ستون است. جدول‌ها با دستور DROP TABLE حذف می‌شوند.

CREATE TABLE WRITERS
(
  NAME CHAR(20),
  BDATE DATE,
  GENDER CHAR(6),
  COUNTRY CHAR(20),
  SALES NUMERIC(9,2)
)

DROP TABLE WRITERS

جدول‌ها با CREATE TABLE اضافه می‌شوند

برای هر جدول یک دستور CREATE TABLE لازم است. در بیشتر گویش‌های SQL، نام جدول نباید بیش از ۱۸ نویسه باشد، باید با حرف شروع شود و نباید کلیدواژهٔ دیگری غیر از حروف، رقم‌ها و زیرخط داشته باشد.

ستون‌ها داخل پرانتز تعریف می‌شوند

تعریف ستون شامل نام، نوع داده و اندازهٔ اختیاری ــ داخل پرانتز ــ است. تعریف ستون‌ها با ویرگول جدا می‌شود. نوع‌های داده میان سیستم‌ها متفاوت است.

جدول‌ها با DROP TABLE حذف می‌شوند

دستور DROP TABLE جدول مشخص‌شده را برای همیشه از پایگاه داده حذف می‌کند. هر دو دستور CREATE و DROP هنگام اجرا به‌صورت خودکار Commit می‌شوند.

خلاصهٔ نحو CREATE TABLE
CLAUSE	PARAMETERS	EXAMPLE
CREATE	TABLE table	TABLE WRITERS
(	col type (size) list	NAME CHAR(20), BDATE DATE, SALES NUMERIC(9,2)
)

خلاصهٔ نحو DROP TABLE
CLAUSE	PARAMETERS	EXAMPLE
DROP	TABLE table	TABLE WRITERS

تمرین‌ها

جدولی به نام SCIENTISTS با چهار ستون NAME، BDATE، GENDER و COUNTRY ایجاد کنید. ستون‌های متنی را طوری بسازید که ۲۰ نویسه را نگه دارند.
چند ردیف به جدول جدید اضافه کنید و وجود جدول و ردیف‌ها را بررسی کنید.
جدول SCIENTISTS را Drop کنید و با یک پرس‌وجو مطمئن شوید دیگر وجود ندارد.

بارگذاری جدول‌ها

یک دسته ردیف را می‌توان با دستور SQL INSERT به جدول افزود. بند VALUES ــ که در هر بار فقط یک ردیف را پشتیبانی می‌کند ــ حذف می‌شود و به‌جای آن پرس‌وجویی قرار می‌گیرد که چندین ردیف برمی‌گرداند. پرس‌وجو داخل پرانتز نوشته می‌شود.

WRITERS — قبل از بارگذاری
NAME	BDATE	GENDER	COUNTRY	SALES

INSERT
INTO WRITERS ( NAME, BDATE, GENDER, COUNTRY )
( SELECT NAME, BDATE, GENDER, COUNTRY
  FROM PERSONS
  WHERE JOB = 'W' )

WRITERS — پس از بارگذاری
NAME	BDATE	GENDER	COUNTRY	SALES
Dickinson	1812-07-22	Male	England	-
Dickinson	1830-07-15	Female	USA	-
Holmes	1809-04-09	Male	USA	-
Moliere	1622-07-03	Male	France	-

چند ردیف را می‌توان با INSERT به جدول افزود.
به‌جای بند VALUES یک پرس‌وجو مشخص می‌شود.
پرس‌وجو داخل پرانتز قرار می‌گیرد.

خلاصهٔ نحو INSERT با پرس‌وجو
CLAUSE	PARAMETERS	EXAMPLE
INSERT
INTO	table ( col list )	COUNTRIES ( COUNTRY, POP )
VALUES	( value list )	( 'Beulah', 144000 )
	( query )	( SELECT ... )

تمرین‌ها

جدول SCIENTISTS را با چهار ستون NAME، BDATE، GENDER و COUNTRY دوباره بسازید. حداکثر طول ستون‌های متنی ۲۰ نویسه باشد.
جدول SCIENTISTS را فقط با دانشمندان زن بارگذاری کنید. چند نفر هستند؟
همهٔ دانشمندان مرد را در جدول SCIENTISTS بارگذاری کنید. اکنون چند نفر وجود دارد؟
همهٔ الهی‌دان‌ها را در جدول SCIENTISTS درج کنید، زیرا Systematic Theology بزرگ‌ترین علم است. اکنون چند ردیف در جدول وجود دارد؟
جدول SCIENTISTS را Drop کنید.

قیود جامعیت

مقادیر قابل‌قبول یک ستون را می‌توان با مشخص‌کردن قیدها (Constraints) در دستور CREATE محدود کرد. پایگاه داده این قیدها را هنگام INSERT، UPDATE و DELETE اعمال می‌کند. دو قید سادهٔ NOT NULL و DEFAULT به شکل زیر هستند.

CREATE TABLE WRITERS
(
  NAME CHAR(20) NOT NULL,
  BDATE DATE,
  GENDER CHAR(6) DEFAULT 'Male',
  COUNTRY CHAR(20),
  SALES NUMERIC(9,2) DEFAULT 0
)

NOT NULL از مقادیر Null جلوگیری می‌کند

از آن‌جا که مقدارهای Null معمولاً نامطلوب‌اند، قید NOT NULL برای جلوگیری از Null در یک ستون مشخص می‌شود. NOT NULL بلافاصله پس از تعریف نوع داده و اندازهٔ یک ستون می‌آید.

DEFAULT مقادیر اولیهٔ ردیف‌های جدید را فراهم می‌کند

نوشتن کلیدواژهٔ DEFAULT و سپس یک مقدار، پایگاه داده را ملزم می‌کند هرگاه ردیفی بدون مقدار مشخص برای آن ستون اضافه می‌شود، مقدار پیش‌فرض را در ستون قرار دهد.

CREATE TABLE WRITERS
(
  NAME CHAR(20) NOT NULL,
  BDATE DATE,
  GENDER CHAR(6) DEFAULT 'Male',
  COUNTRY CHAR(20),
  SALES NUMERIC(9,2) DEFAULT 0,
  UNIQUE ( NAME ),
  CHECK ( GENDER IN ('Male','Female') ),
  CHECK ( SALES >= 0 )
)

قیود UNIQUE و CHECK با تحمیل شرط‌های بیشتر به داده‌ها سطح بالاتری از جامعیت فراهم می‌کنند. هر دو می‌توانند روی یک ستون یا ترکیبی از ستون‌ها اعمال شوند.

UNIQUE از مقادیر تکراری جلوگیری می‌کند

کلیدواژهٔ UNIQUE و فهرست ستون‌ها در پرانتز از ذخیرهٔ مقادیر تکراری در ترکیب مشخص‌شده جلوگیری می‌کنند. قیود UNIQUE پس از تعریف ستون‌ها نوشته می‌شوند.

CHECK مقادیر معتبر را تضمین می‌کند

کلیدواژهٔ CHECK و یک مقایسه در پرانتز مانع ذخیرهٔ ردیف‌هایی می‌شود که معیار CHECK را برآورده نمی‌کنند. قیود CHECK زیر تعریف ستون‌ها نوشته می‌شوند.

CREATE TABLE WRITERS
(
  NAME CHAR(20) NOT NULL,
  BDATE DATE,
  GENDER CHAR(6) DEFAULT 'Male',
  COUNTRY CHAR(20),
  SALES NUMERIC(9,2) DEFAULT 0,
  CHECK ( GENDER IN ('Male','Female') ),
  CHECK ( SALES >= 0 ),
  PRIMARY KEY ( NAME ),
  FOREIGN KEY ( COUNTRY )
    REFERENCES COUNTRIES ( COUNTRY )
)

کلید اصلی (Primary Key) ستون یا مجموعه‌ای از ستون‌هاست که برای هر ردیف جدول همیشه یکتا است. کلید خارجی (Foreign Key) ستونی است که مقادیر آن به کلید اصلی جدول دیگری ارجاع می‌دهند.

PRIMARY KEY ردیف‌های یکتا را تضمین می‌کند

نوشتن PRIMARY KEY و سپس فهرست ستون‌ها در پرانتز به پایگاه داده می‌گوید برای ترکیب مشخص‌شده هم Null و هم مقدار تکراری را ممنوع کند.

FOREIGN KEY به مقادیر کلید اصلی ارجاع می‌دهد

کلید خارجی با نوشتن FOREIGN KEY، فهرست ستون‌ها، REFERENCES، نام جدول مرجع و کلید اصلی آن تعریف می‌شود. سیستم تضمین می‌کند مقادیر کلید خارجی در جدول مرجع وجود داشته باشند.

خلاصهٔ نحو CREATE TABLE با قیود
CLAUSE	PARAMETERS	EXAMPLE
CREATE	TABLE table	TABLE WRITERS
(	col type (size) list	NAME CHAR(20), BDATE DATE, SALES NUMERIC(9,2)
	NOT NULL	NOT NULL
	DEFAULT value	DEFAULT 0
UNIQUE	( col list )	( NAME )
CHECK	( comparison )	( GENDER IN ('Male','Female') )
PRIMARY KEY	( col list )	( NAME )
FOREIGN KEY	( col list ) REFERENCES table ( col list )	( COUNTRY ) REFERENCES COUNTRIES ( COUNTRY )
)

تمرین‌ها

جدولی به نام THEOLOGIANS با ستون‌های NAME، BDATE، GENDER و COUNTRY ایجاد کنید. ستون‌های متنی ۲۰ نویسه باشند. GENDER نباید Null باشد و باید Male یا Female باشد؛ NAME کلید اصلی و COUNTRY کلید خارجی ارجاع‌دهنده به COUNTRIES باشد. جدول را با ردیف‌های مناسب PERSONS بارگذاری کنید.
سعی کنید ردیفی با کشور نامعتبر مانند Russia اضافه کنید. سپس ردیفی با Null gender و یک ردیف با Null name اضافه کنید و خطاها را مشاهده کنید.
جدول THEOLOGIANS را Drop کنید.

تعریف ایندکس‌ها

ایندکس ساختار داده‌ای نامرئی است که دسترسی به جدول را سریع‌تر می‌کند. ایندکس‌ها با دستور CREATE INDEX ساخته می‌شوند، در صورت مناسب‌بودن به‌طور خودکار توسط SQL استفاده می‌شوند و با دستور DROP INDEX از پایگاه داده حذف می‌شوند.

CREATE INDEX GJX
ON PERSONS ( GENDER, JOB )

DROP INDEX GJX

ایندکس‌ها با CREATE INDEX اضافه می‌شوند

ایندکس با دستور CREATE INDEX تعریف می‌شود. نام ایندکس باید در پایگاه داده یکتا باشد و به‌طور داخلی توسط سیستم استفاده می‌شود. نام ایندکس هنگام حذف یک ایندکس موجود نیز به کار می‌رود.

ایندکس‌ها به‌طور خودکار استفاده می‌شوند

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

ایندکس‌ها با DROP INDEX حذف می‌شوند

برای حذف ایندکس از پایگاه داده از دستور DROP INDEX استفاده کنید. فضای دیسک جدول باقی می‌ماند و فقط فضای اشغال‌شده توسط ایندکس آزاد می‌شود. هر دو دستور CREATE و DROP هنگام اجرا به‌طور خودکار Commit می‌شوند.

خلاصهٔ نحو CREATE INDEX
CLAUSE	PARAMETERS	EXAMPLE
CREATE	INDEX index	INDEX GJX
ON	table ( col list )	PERSONS ( GENDER, JOB )

خلاصهٔ نحو DROP INDEX
CLAUSE	PARAMETERS	EXAMPLE
DROP	INDEX index	INDEX GJX

تمرین‌ها

جدولی به نام REGIONS با یک ستون CHAR(10) به نام NAME ایجاد کنید. چهار ردیف با مقادیر North، South، East و West درج کنید. سپس خود جدول را در خودش درج کنید تا به حدود ۶۵٬۵۳۶ ردیف برسید.
تعداد ردیف‌ها را به تفکیک نام Region بشمارید و زمان اجرای پرس‌وجو را یادداشت کنید.
ایندکسی به نام RX روی ستون NAME جدول REGIONS بسازید. پرس‌وجوی قبلی را دوباره اجرا کنید. سریع‌تر است، کندتر است یا تقریباً همان؟ چرا؟
ایندکس و سپس جدول را Drop کنید.

تعریف Viewها

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

CREATE VIEW PTV ( PERSON, NAME, TITLE )
AS
SELECT PERSON, NAME, TITLE
FROM PERSONS AS P, JOBS AS J
WHERE P.JOB = J.JOB

DROP VIEW PTV

Viewها با CREATE VIEW اضافه می‌شوند

View برای اطلاعاتی که زیاد به آن‌ها مراجعه می‌شود و به‌سختی به دست می‌آیند، مانند خلاصه‌های حاصل از چند جدول، مفید است. تقریباً هر دستور پرس‌وجو را می‌توان برای تعریف View به کار برد؛ با این استثنا که تعریف View نمی‌تواند بند ORDER BY داشته باشد.

Viewها ممکن است قابل به‌روزرسانی باشند

معمولاً Viewای که از یک جدول و با همهٔ ستون‌های non-null ساخته شده باشد قابل به‌روزرسانی است؛ Viewهای دیگر ممکن است قابل به‌روزرسانی نباشند. اطلاعاتی که از طریق View به‌روزرسانی می‌شود در جدول یا View زیرین تغییر می‌کند.

Viewها با DROP VIEW حذف می‌شوند

برای حذف View از پایگاه داده از دستور DROP VIEW استفاده می‌شود. حذف View داده‌های جدول‌های زیرین را حذف نمی‌کند. هر دو CREATE و DROP هنگام اجرا به‌طور خودکار Commit می‌شوند.

خلاصهٔ نحو CREATE VIEW
CLAUSE	PARAMETERS	EXAMPLE
CREATE	VIEW view ( col list )	VIEW PTV ( PERSON, NAME, TITLE )
AS	query	SELECT ...

خلاصهٔ نحو DROP VIEW
CLAUSE	PARAMETERS	EXAMPLE
DROP	VIEW view	VIEW PTV

تمرین‌ها

Viewای به نام IV ایجاد کنید که همان ستون‌های جدول PERSONS را داشته باشد، اما فقط افراد متولد Israel را دربرگیرد. برای بررسی View از آن Query بگیرید.
دوستی را با شمارهٔ شخص ۵۰۲ و کشور Israel در View IV درج کنید. سپس با شمارهٔ شخص ۵۰۳ و کشور USA دوستی را در View درج کنید. برای دوست گمشده چه اتفاقی افتاد؟
Viewای به نام IJV بر اساس View IV ایجاد کنید. ستون‌های NAME، BDATE و COUNTRY را همراه با ستون TITLE از جدول JOBS قرار دهید.
همهٔ Viewهای خود را Drop کنید. متشکرم. خداحافظ.

CREATE TABLE، قیود، Index و View در SQL