SQL ALTER TABLE

عبارت ALTER TABLE در sql برای تغییر و حذف کردن یا اضافه کردن ستون ها در جداول استفاده میشود.

دستور alter table  در  sql

ALTER TABLE table_name
ADD column_name datatype

این دستور برای اضافه کردن ستونی جدید به جدول استفاده میشود.

ALTER TABLE table_name
DROP COLUMN column_name

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

ALTER TABLE table_name
ALTER COLUMN column_name datatype

و در آخر دستور بالا برای تغییر در داده های یک ستون از جدول استفاده میشود.

مثال:

جدول  person را در نظر بگیرید

P_Id LastName FirstName Address City
۱ Hansen Ola Timoteivn 10 webfocus.ir Sandnes
۲ Svendson Tove Borgvn 23 Sandnes
۳ Pettersen Kari Storgt 20 – webfocus.ir Stavanger

حالا میخواهیم به جدول بالا یک ستون با نام DateOfBirth اضافه کنیم

از دستور زیر بهره میگیریم

ALTER TABLE Persons
ADD DateOfBirth date

توجه داشته باشید که این ستون جدید مقدار تاریخ را نگه میدارد یعنی در واقه میخواهم به این نکته اشاره کنم که dataType نوع داده ای را مشخص میکند که در آن ستون نگهداری میشود.

جدول بالا به شکل زیر میشود.

P_Id LastName FirstName Address City DateOfBirth
۱ Hansen Ola Timoteivn 10 Sandnes – webfocus.ir
۲ Svendson Tove Borgvn 23 – webfocus.ir Sandnes
۳ Pettersen Kari Storgt 20 Stavanger

تغییر در dataType را با استفاده از دستور زیر انجام میدهیم

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

حال این ستون از جدول یعنی DateOfBirth مقدار سال را در دو رقم یا رقم در خود نگهداری میکند.

و در آخر میخواهیم که این ستون از جدول را حذف کنیم .

ALTER TABLE Persons
DROP COLUMN DateOfBirth

با اجرای دستور بالا ستون DateOfBirth  از جدول حذف خواهد شد.

جدول به شکل اول خود بر میگردد

P_Id LastName FirstName Address City
۱ Hansen Ola Timoteivn 10 Sandnes
۲ Svendson Tove Borgvn 23 Sandnes
۳ Pettersen Kari Storgt 20 Stavanger

مترجم: علیرضا مسگری

SQL DROP

index ها ، جداول(table ها) و پایگاه های داده (databases) به راحتی با دستور drop قابل حذف هستند.

drop index در MS Access

DROP INDEX index_name ON table_name

drop index در MS sql server

DROP INDEX table_name.index_name

drop index برای DB2/oracle

DROP INDEX index_name

drop index برای MySQL

ALTER TABLE table_name DROP INDEX index_name

دستور drop table که برای حذف جدول از آن استفاده میشود

DROP TABLE table_name

drop database نیز دستوری است برای حذف یک پایگاه داده که از آن به شکل زیر استفاده میشود

DROP DATABASE database_name

و اما اگر بخواهیم که فقط اطلاعات جدول را پاک کنیم و خود جدول باقی بماند از دستور truncate  استفاده میکنیم.

TRUNCATE TABLE table_name

مترجم: علیرضا مسگری

SQL INDEX

  • دستور  index این امکان را میدهد تا پایگاه داده راحت و سریعتر به داده ها دسترسی پیدا کند و نیازی به خواندن تمام اطلاعات برای پیدا کردن داده ای خاص نباشد
  • کاربر index را نمیبیند و فقط سرعت عملکرد را متوجه میشود

برای فهم راحت تر فرض کنید در یک کتاب به دنبال مطلب خاصی هستید. یک راه این است که برای پیدا کردن مطلب مورد نظر از صفحه اول شروع کنیم و صفحه به صفحه بگردیم تا آن را پیدا کنیم ولی راه دیگر که معقول تر به نظر می رسد این است که ناشر وقت صرف کرده و تعدادی صفحه اضافی برای ساخت فهرست کتاب اختصاص داده است و شما با کمک فهرست کتاب خیلی سریع مطلب مورد نظر را خواهید یافت. ایندکس در پایگاه داده نیز مشابه این کار را انجام می دهد.

نکته:

update  شدن یک جدول با index زمان بیشتری نسبت به  update  شدن یک جدول بدون  index  میباشد زیرا  index ها نیز باید  update  شوند

دستور ساخت  index

CREATE INDEX index_name
ON table_name (column_name)

اما در دستور بالا تکرار برای عنصری که  index  است مجاز است و برای یکتا ساختن  index  از دستور زیر استفاده میکنیم.

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

دستور  index  در پایگاه داده های متفاوت ، مختلف است پس برای ساخت آن دقت کنید.

به مثال های زیر دقت کنید

CREATE INDEX PIndex
ON Persons (LastName)

این دستور PIndex را به عنوان  index برای جدول  person در ستون LastName  ایجاد میکند.

برای hndex  ساختن چندین ستون هم از فرم زیر کمک بگیرید

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

مترجم: علیرضا مسگری

SQL DEFAULT

این محدودیت موجب میشود که یک مقدار به صورت پیشفرض در ستون مورد نظر قرار بگیرد و اگر برای آن ستون مقدار دیگری وارد نشود همان مقدار به صورت پیشفرض وارد جدول گردد

دستور ساخت  default  هنگام ساخت یک جدول به صورت زیر میباشد.

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

همچنین این مقدار پیشفرض میتواند توسط توابع sql در نظر گرفته شود.

مثال

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

برای ایجاد default برای یک عنصر در جدولی که قبلا ساخته شده است با کمک alter از دستور زیر بهره میگیریم

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

و در اخر برای حذف یک default  از جدول به صورت زیر عمل میکنیم

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

مترجم: علیرضا مسگری

SQL CHECK

محدودیت check تعیین میکند که چه بازه ای از مقادیر و یا داده با چه مشخصاتی میتواند در ستون پایگاه داده قرار بگیرد.

همچنین میتوان برای جدول با توجه به ستونهایش محدودیت check ایجاد کنیم

میخواهیم محدودیت check  را هنگام ساخت جدول اعمال کنیم ، به دستورات زیر دقت کنید ، پس از ساخت جدول شما فقط میتوانید مقدار integer  را برای P_Id وارد کنید

My SQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

و برای محدود کردن چند ستون به طور همزمان هم از دستور زیر استفاده میکنیم

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

برای ایجاد دستور check در جدولی که قبلا ساخته شده است از دستور زیر بهره میگیریم

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

و همچنین این عمل برای محدودیت چندین ستون به صورت زیر اعمال میشود

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

برای حذف یک محدودیت check نیز به این صورت عمل میکنیم

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

مترجم: علیرضا مسگری

SQL FOREIGN KEY

محدودیت foreign key  در یک جدول به کلید اصلی یک جدول دیگر (primary key ) اشاره دارد .

بیاید برای درک بهتر به مثال زیر دقت کنیم:

جدول “person”

P_Id LastName FirstName Address City
۱ Hansen Ola webfocus.ir Sandnes
۲ Svendson Tove Borgvn 23 webfocus.ir
۳ Pettersen Kari Storgt 20 Stavanger

جدول “order”

O_Id OrderNo P_Id
۱ ۷۷۸۹۵ ۳
۲ ۴۴۶۷۸ ۳
۳ ۲۲۴۵۶ ۲
۴ ۲۴۵۶۲ ۱

دقت کنید که عنصر P_Id درجدول order به عنصر همنام خود یعنی P_Id در جدول person اشره دارد و حاوی همان اطلاعات میباشد P_Id در order یک  foreign key  و در person یک primary key  میباشد.

foreign key  در واقع از وارد شدن اطلاعات نا معتبر در جدول و همچنین هرچیز که ارتباط بین دو جدول را تخریب کند جلوگیری میکند زیرا که به مقدار یکتایی در جدول اصلی اشاره دارد.

ایجاد foreign key هنگام ساخت جدول

برای انجام عمل فوق از دستور زیر استفاده میکنیم

MySQL:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

و برای محدود کردن چند ستون در جدول به عنوان foreign key  از دستور زیر استفاده کنید

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

برای ساخت یک foreign key در جدولی که قبلا ساخته شده است باید از دستور زیر استفاده کرد.

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

و همچنین برای ساخت چند ستون به عنوان یک foreign key از دستور زیر استفاده کنید

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

و برای حذف یک foreign key از دستورات زیر بهره بگیرید

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

مترجم: علیرضا مسگری

SQL PRIMARY KEY

  • این محدودیت یک مشخصه یکتایی است که برای هر داده در جدول پایگاه داده تعریف میشود
  • primary key حتما باید مقدارش یکتا باشد
  • مقدار null را نمیتواند بپذیرد
  • هر جدول باید حتما یک primary key داشته باشد
  • و البته فقط یک primary key میتواند داشته باشد.

کلید اصلی یک مشخصه یکتا برای هر رکورد پایگاه داده است. برای درک بهتر کاربرد کلید اصلی می توانید کد ملی افراد را در نظر بگیرید. هر شخصی در کشور ما یک و فقط یک کد ملی دارد. به عبارتی وقتی یک کد ملی داریم آن کد فقط مشخص کننده هویت یک فرد خواهد بود. در پایگاه داده نیز کلید اصلی دقیقا همین نقش را ایفا می کند. کلید اصلی در پایگاه داده فقط یک رکورد از پایگاه داده را برای ما مشخص می کند. برای تعیین کلید اصلی در یک پایگاه داده یک یا مجموعه ای از چند فیلد اطلاعاتی را به عنوان کلید اصلی می توانیم در نظر بگیریم. مثلا فرض کنید در یک جدول بانک اطلاعاتی که حاوی مشخصات دانشجویان است نیاز است که کلید اصلی انتخاب کنیم. یک راه این است که مجموعه (نام و نام خانوادگی) را به عنوان کلید اصلی در نظر بگیریم. با دقت بیشتر مشخص می شود که ممکن است در یک کلاس دو شخص متفاوت ولی با نام و نام خانوادگی یکسان داشته باشیم. پس بهتر است از شماره دانشجویی به عنوان کلید اصلی استفاده کنیم که در این صورت حتی اگر نام و نام خانوادگی افراد با یکدیگر یکسان باشد ولی شماره دانشجویی این افراد متفاوت خواهد بود و شماره دانشجویی مشخص کننده شخص مورد نظر ما و کلید اصلی جدول دانشجویان خواهد بود.

ایجاد محدودیت primary key در هنگام تولید جدول

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

و اما برای اینکه یک محدودیت primary key را نامگذاری کنیم و اینکه برای یک جدول با چندین ستون یک primary key تعریف کنیم از دستور زیر استفاده می کنیم خوب به این دستور دقت کنید

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ایجاد primary key بعد از ساخت یک جدول

برای انجام عمل فوق از دستور زیر استفاده میکنیم و حتما دستور alter را بخاطر دارید ، دقت کنید

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

و همچنین این عمل برای یک جدول با داده های بسیار از دستور زیر بهره میگیریم

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

نکته:

دقت کنید زمانی که شما بعد از ساخت یک جدول اقدام به تولید primary key میکنید باید حتما انرا  not null تعریف کنید

حذف یک primary key

برای این کار از دستور زیر استفاده میکنیم

MySQL

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

مترجم: علیرضا مسگری

SQL UNIQUE

unique در دستورات SQL باعث می شود که فیلد مورد نظر در database یکتا شناخته شود.

primary key  و unique در واقع تضمین یکتا بودن فیلد مورد نظر در جدول پایگاه داده است.

در یک primary key  به طور خودکار دستور  unique  وجود دارد.

نکته:

در یک جدول پایگاه داده ای شما می توانید چندین فیلد unique داشته باشید اما فقط یک  primary key  می توانید داشته باشید.

دستور unique  در جدول زیر باعث یکتا شدن مقدار “P_Id” میشود.

MySQL:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

برای نام گذاری یک محدودیت UNIQUE و یا برای معین کردن UNIQUE های موجود در یک جدول می توان از دستور زیر استفاده نمود.

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ترکیب دستور های ALTER TABLE و UNIQUE یعنی UNIQUE کردن یک مقدار برای جدول ساخته شده.

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

DROP کردن فیلد UNIQE :

MySQL:

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

مترجم: علیرضا مسگری

SQL NOT NULL

عبارت not null  موجب می شود تا ستون مورد نظر نتواند مقدار null  را بپذیرد.

not null  باعث میشود تا فیلد مورد نظر همیشه دارای مقدار بوده به این معنا که شما نمی توانید یک record جدید بدون value یا یک record موجود را بدون مقدار(value) ,  به روز کنید(update).

عبارت SQL زیر موجب میشود تا ستون های “P_Id” و “LastName” مقدار null را نپذیرند(یعنی نمیتوانیم این دو فیلد را خالی بگذاریم).

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


مترجم: علیرضا مسگری

SQL Constraints

Constraints برای محدود ساختن آن داده هایی که می توانند در جدول ذخیره شوند استفاده میشود

میتواند زمانی که یک جدول ساخته میشود و یا بعد از ساخته شدن آن ( همراه دستور ALTER TABLE که بعدا توضیح خواهیم داد) مشخص شود.
ما بر روی CONSTRAINTS های زیر تمرکز میکنیم.

    *  NOT NULL
    * UNIQUE
    * PRIMARY KEY
    * FOREIGN KEY
    * CHECK
    * DEFAULT

در قسمت بعدی بیشتر توضیح خواهیم داد

مترجم : علیرضا مسگری