9 - تعریف اشیای پایگاه داده

• تعریف جدول‌ها
• بارگذاری جدول‌ها
• قیود یکپارچگی
• تعریف ایندکس‌ها
• تعریف 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، نام جدول نمی‌تواند بیش از 18 نویسه باشد، باید با حرف آغاز شود و فقط می‌تواند شامل حروف، ارقام و زیرخط باشد.

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

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

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

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

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

نحو عمومی CREATE TABLE و DROP TABLE:

CREATE TABLE table
(
    col type(size) [, ...]
);

-- Example
CREATE TABLE WRITERS
(
    NAME CHAR(20),
    BDATE DATE,
    SALES NUMERIC(9, 2)
);

DROP TABLE table;
-- Example
DROP TABLE WRITERS;

تمرین‌ها

1. جدولی به نام SCIENTISTS با چهار ستون NAME، BDATE، GENDER و COUNTRY ایجاد کنید. ستون‌های متنی ظرفیت 20 نویسه داشته باشند.
2. چند سطر به جدول جدید اضافه کنید و وجود جدول و سطرها را تأیید کنید.
3. جدول SCIENTISTS را Drop کنید. Query اجرا کنید تا مطمئن شوید دیگر وجود ندارد.

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

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

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

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

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

نحو بازبینی‌شده INSERT پارامتر Query را نیز می‌پذیرد؛ Query و VALUES با یکدیگر انحصاری‌اند و هم‌زمان استفاده نمی‌شوند.

INSERT
INTO table (col list)
VALUES (value list)

-- or
INSERT
INTO table (col list)
(SELECT ...);

تمرین‌ها

1. جدول SCIENTISTS را دوباره با چهار ستون NAME، BDATE، GENDER و COUNTRY ایجاد کنید. ستون‌های متنی 20 نویسه ظرفیت داشته باشند.
2. فقط دانشمندان زن را در جدول SCIENTISTS بارگذاری کنید. چند نفرند؟
3. همه دانشمندان مرد را نیز وارد جدول SCIENTISTS کنید. حالا چند سطر وجود دارد؟
4. همه الهی‌دانان را هم وارد SCIENTISTS کنید، زیرا مطابق شوخی متن منبع «الهیات نظام‌مند بزرگ‌ترین همه علوم است». اکنون چند سطر در جدول وجود دارد؟
5. جدول SCIENTISTS را Drop کنید.

قیود یکپارچگی

با مشخص‌کردن Constraintها در دستور 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 در ستون می‌شود. این قید پس از نوع داده و اندازه ستون نوشته می‌شود.

DEFAULT مقدار اولیه سطرهای جدید را تعیین می‌کند

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

قیود یکپارچگی - ادامه

قیدهای UNIQUE و 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,
    UNIQUE (NAME),
    CHECK (GENDER IN ('Male', 'Female')),
    CHECK (SALES >= 0)
);

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

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

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

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

قیود یکپارچگی - ادامه

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

CREATE TABLE WRITERS
(
    NAME    CHAR(20),
    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 یکتایی سطرها را تضمین می‌کند

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

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

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

قیود یکپارچگی - ادامه

نحو بازبینی‌شده CREATE TABLE شامل قیدهای NOT NULL، DEFAULT، UNIQUE، CHECK، PRIMARY KEY و FOREIGN KEY است.

CREATE TABLE table
(
    col type(size) [NOT NULL] [DEFAULT value],
    ...,
    UNIQUE (col list),
    CHECK (comparison),
    PRIMARY KEY (col list),
    FOREIGN KEY (col list)
        REFERENCES table (col list)
);

تمرین‌ها

1. جدول THEOLOGIANS را با چهار ستون NAME، BDATE، GENDER و COUNTRY ایجاد کنید. ستون‌های متنی 20 نویسه ظرفیت داشته باشند. GENDER نباید Null باشد، مقدار پیش‌فرض آن 'male' باشد و فقط 'male' یا 'female' را بپذیرد؛ NAME کلید اصلی باشد و COUNTRY کلید خارجی‌ای باشد که به COUNTRIES ارجاع می‌دهد. سپس سطرهای مناسب را از PERSONS بارگذاری کنید.
2. یک سطر با کشور نامعتبر مانند Prussia وارد کنید. سپس سطری با Gender برابر Null وارد کنید؛ Null به‌صورت NULL و بدون کوتیشن نوشته می‌شود.
3. جدول THEOLOGIANS را Drop کنید.