MIDOONY Logo

Sql (بخش اول - تاریخچه، نحوه ایجاد پایگاه داده، تعریف انواع داده ستون ها Data Definition Language)

1400/04/27
Sql (بخش اول - تاریخچه، نحوه ایجاد پایگاه داده، تعریف انواع داده ستون ها Data Definition Language)

زبان پرس و جوی ساخت یافته ( SQL - #Structured_Query_Language ) برای مدیریت داده ها در #پایگاه_های_داده_رابطه_ای طراحی شده است. #SQL ابتدا توسط IBM در اوایل دهه 1970 یعنی سال 1986 توسعه یافت. نسخه اولیه بنام (#SEQUEL - Structured English Query Language) برای تغییر و بازیافت داده های ذخیره شده در پایگاه داده رابطه ای IBM موسوم به سیستم R طراحی گردید، سپس در اواخر دهه 1970 شرکت Relational Software که در حال حاضر Oracle است اولین پیاده سازی تجاری SQL بنام Oracle V2 را برای کامپیوترهای VAX معرفی نمود.

بیشتر پایگاه های داده رابطه ای امروزی از قبیل Oracle، Sql Server، MySql، DB2، Informix و Access از SQL استفاده می نمایند.

در یک پایگاه داده رابطه ای زبان SQL برای موارد زیر استفاده می شود :
  • ایجاد پایگاه داده و ساختار جداول
  • عملیات پایه مدیریت داده شامل اضافه، حذف و اصلاح
  • اجرای پرس و جوهای پیچیده برای دسترسی به اطلاعات مفید

ایجاد پایگاه داده

دستورات اصلی زبان SQL عبارتند از Create Database و Create/Alter/Drop Table. دستور Create برای ایجاد پایگاه داده و ساختار جداول استفاده می گردد.
مثال : ایجاد پایگاه داده SW
با دستور

Create Database SW;
یک پایگاه داده بنام SW ایجاد می شود. پس از ایجاد پایگاه داده باید جداول آنرا ایجاد نماییم.

قالب کلی دستور ایجاد جدول بصورت زیر است:
CREATE TABLE <tablename>
        (
        ColumnName1, DataType, Optional Column Constraint,
        ColumnName2, DataType, Optional Column Constraint,
        ....
        ColumnNameN, DataType, Optional Column Constraint,
        Optional table Constraints
        );

Tablename

اسم جدول می باشد مانند Employee. هر فیلد اطلاعاتی در این دستور شامل سه بخش است:

  1. نام ستون ( ColumnName )
  2. نوع داده ( Datatype )
  3. شرایط و محدودیت های اختیاری (Optional Column Constraints)

ColumnName

نام ستون در جدول باید منحصربفرد باشد، نام و نام خانوادگی از مصادیق نام ستون هستند.

DataType

نوع داده همانطور که در زیر شرح داده شده است می تواند از نوع سیستمی یا تعریف شده توسط کاربر باشد و نوع اطلاعات نگهداری شده در آن ستون را مشخص می نماید.
بیشتر انواع داده دارای اندازه هستند مانند: Char(35) و Numeric(8,2)

نوع داده شرح محدوده فضای ذخیره سازی
Bit
داده صحیح :
موتور پایگاه داده SQL Server ذخیره سازی ستون های bit را بهینه می کند. اگر 8 ستون bit یا کمتر در جدول وجود داشته باشد، ستون ها به صورت 1 بایت ذخیره می شوند. اگر از 9 تا 16 ستون bit وجود داشته باشد، ستون ها به صورت 2 بایت ذخیره می شوند و غیره
0 or 1 1 byte
Int داده صحیح -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 bytes
SmallInt داده صحیح -2^15 (-32,768) to 2^15-1 (32,767) 2 bytes
Tinyint داده صحیح 0 to 255 1 byte
Decimal[(p[,s])]
داده عددی با اعشار ثابت:
مقدار p حداکثر تعداد ارقام قابل ذخیره می باشد که در هر دوطرف چپ و راست نقطه اعشار می باشند. مقدار s حداکثر تعداد ارقام قابل ذخیره در سمت اعشار می باشد
- 10^38 +1 to 10^38 - 1
حداکثر ارقام فضا
1 - 9 5 Bytes
10-19 9 Bytes
20-28 13 Bytes
29-38 17 Bytes
Numeric مترادفی برای Decimal
Timestamp مقدار عددی منحصر بفرد در سطح پایگاه داده
Uniqueidentifier شناسه منحصربفرد غیرمحلی (GUID) قالب xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx، که در آن هر x یک رقم هگزادسیمال در محدوده 0-9 یا a-f است. 16 bytes
Money مقدار داده پولی
  • -922,337,203,685,477.5808 to 922,337,203,685,477.5807
  • -922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica.
Informatica از 2 رقم اعشار پشتیبانی میکند نه 4 رقم.
8 bytes
Smallmoney مقدار داده پولی با دقت یک ده هزارم واحد پولی - 214,748.3648 to 214,748.3647 4 bytes
Float[(n)]
داده عددی با اعشار شناور:
  • n تعداد بیت هایی است که برای ذخیره مانتیس عدد float در نماد علمی استفاده می شود، بنابراین دقت و اندازه ذخیره سازی را دیکته می کند. اگر n مشخص شده باشد، باید مقداری بین 1 تا 53 باشد. مقدار پیش فرض n برابر 53 است.
  • SQL Server مقدار n را به عنوان یکی از دو مقدار ممکن در نظر می گیرد. اگر 1<=n<=24،مقدار n را به عنوان 24 در نظر خواهد گرفت. اگر 25<=n<=53، مقدار n را به عنوان 53 در نظر میگیرد.
- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
مقدار n ارقام فضا
1-24 7 4 bytes
25-53 15 8 bytes
Real داد ه عددی با اعشار شناور - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes
Datetime داده زمان، ساعت و تاریخ با دقت 0.000، 0.003 یا 0.007 ثانیه
  • تاریخ: January 1, 1753, to December 31, 9999
  • ساعت: 00:00:00 to 23:59:59.997
8 bytes
Smalldatatime داده زمان، ساعت و تاریخ با دقت یک دقیقه
  • تاریخ: 1900-01-01 to 2079-06-06
  • ساعت: 00:00:00 to 23:59:59
4 bytes
Char[(n)]
داده حرفی با طول ثابت:
  • مقدار n اندازه رشته بر حسب بایت را مشخص می کند.
  • برای کاراکترهایی که بصورت single-byte encoding تنظیم شده اند، بعنوان مثال Latin، فضای ذخیره برابر n بایت و طول رشته نیز برابر n حرف می باشد.
  • برای کاراکترهایی که بصورت multibyte encoding یا Double-byte تنظیم شده اند، بعنوان مثال زبان های آسیای شرقی یا استفاده از UTF-8 code page، فضای ذخیره همچنان برابر n بایت است اما تعداد حروف ای که می توانند ذخیره شوند ممکن است کمتر از n باشد.
برای اطلاعات بیشتر Single-Byte و Multibyte Character Sets را ببینید.
1 to 8000 n byte
Varchar[(n|max)]
داده حرفی با طول متغیر:
مقدار n اندازه رشته بر حسب بایت را مشخص می کند.
1 to 8000 or "max"
  • n byte + 2 byte
  • "max" = 2^31-1 bytes (2 GB)
Text داده حرفی با طول متغیر non-Unicode 2^31-1 (2,147,483,647 byte)
Binary[(n)] داده دودویی با طول ثابت 1 to 8000 n bytes
Varbinary[(n|max)] داده دودویی با طول متغیر 1 to 8000 or "max"
  • 1 to 8000
  • "max"=2^31-1 bytes
Image داده دودویی با طول متغیر 0 to 2^31-1 bytes 2^31-1 (2,147,483,647) bytes

شرایط و محدودیت های اختیاری (Optional Column Constraints)

این شرایط عبارتند از NULL, NOT NULL, UNIQUE, PRIMARY KEY و DEFAULT که برای تعریف مقادیر رکوردها استفاده می شوند. شرط NULL نشان می دهد که مقدار خالی برای این ستون مجاز می باشد و شرط NOT NULL نشان می دهد که هنگام ایجاد رکورد جدید، مقدار این ستون نمی تواند خالی باشد.

پس از اینکه تمام ستون های جدول تعریف شدند، یک محدودیت یا شرط با ذکر کلمه CONSTRAINT تعریف می گردد. در مثال بالا برای تعریف کلید اصلی عبارت زیر استفاده می شود:

CONSTRAINT EmployeePK PRIMARY KEY(EmployeeNo)
دستور کامل ایجاد جدول Employees در پایگاه داده SW به این صورت می باشد :
USE SW
CREATE TABLE EMPLOYEES (
        EmployeeNo CHAR(10) NOT NULL UNIQUE,
        DepartmentName CHAR(30) NOT NULL DEFAULT “Human Resources”,
        FirstName CHAR(25) NOT NULL,
        LastName CHAR(25) NOT NULL,
        Category CHAR(20) NOT NULL,
        HourlyRate CURRENCY NOT NULL,
        TimeCard LOGICAL NOT NULL,
        HourlySalaried CHAR(1) NOT NULL,
        EmpType CHAR(1) NOT NULL,
        Terminated LOGICAL NOT NULL,
        ExemptCode CHAR(2) NOT NULL,
        Supervisor LOGICAL NOT NULL,
        SupervisorName CHAR(50) NOT NULL,
        BirthDate DATE NOT NULL,
        CollegeDegree CHAR(5) NOT NULL,
        CONSTRAINT Employee_PK PRIMARY KEY(EmployeeNo));
به همین صورت جداول Department، Project و Assignment تعریف می گردند :
USE SW
CREATE TABLE DEPARTMENT (
        DepartmentName Char(35) NOT NULL,
        BudgetCode Char(30) NOT NULL,
        OfficeNumber Char(15) NOT NULL,
        Phone Char(15) NOT NULL,
        CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName));
USE SW
CREATE TABLE PROJECT (
        ProjectID Int NOT NULL IDENTITY (1000,100),
        ProjectName Char(50) NOT NULL,
        Department Char(35) NOT NULL,
        MaxHours Numeric(8,2) NOT NULL DEFAULT 100,
        StartDate DateTime NULL,
        EndDate DateTime NULL,
        CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(ProjectID));
USE SW
CREATE TABLE ASSIGNMENT (
        ProjectID Int NOT NULL,
        EmployeeNumber Int NOT NULL,
        HoursWorked Numeric(6,2) NULL);
سایر محدودیت های جدول به این شرح تعریف می گردند :

IDENTITY constraint

این محدودیت برای تعریق مقادیر منحصربفرد افزایشی استفاده می شود. این محدودیت اغلب با محدودیت PRIMARY KEY بکار می رود. این ویژگی به ستون هایی با نوع داده tinyint، smallint، int، decimal و numeric اختصاص می یابد.

  • ایجاد اعداد متوالی
  • عدم اجرای یکپارچگی موجودیت ها
  • تنها یک ستون می تواند این ویژگی را داشته باشد
  • نوع داده باید integer، numeric یا decimal باشد
  • ستون با این ویژگی قابل تغییر نیست
  • ستون نمی تواند شامل مقدار NULL یا تهی باشد
برای مثال به تعریف جدول هتل در پایگاه داده HOTEL توجه نمایید:
CREATE TABLE tblHotel (
        HotelNo Int IDENTITY (1,1),
        HotelName Char(50) NOT NULL,
        Address Char(50) NULL,
        City Char(25) NULL);

UNIQUE constraint

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

  • هر دو محدودیت PK و UNIQUE برای اجرای یکپارچگی موجودیت ها استفاده می شوند.
  • محدودیت های چندگانه از این نوع می تواند در یک جدول تعریف شوند.
  • وقتی این محدودیت به یک جدول اضافه شود، داده های موجود همیشه معتبر می باشند.
  • این محدودیت روی ستون هایی با مقادیر NULL می تواند اعمال شود
شکل کلی نگارش این محدودیت بصورت زیر است:
        [CONSTRAINT constraint_name]
        UNIQUE [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2 […, col_name16]])
        [ON segment_name]
برای مثال به این دستور توجه نمایید:
CREATE TABLE EMPLOYEES (
        EmployeeNo CHAR(10) NOT NULL UNIQUE)

FOREIGN KEY constraint

این محدودیت ستون یا ترکیب چند ستون را مشخص می کند که مقادیرشان با ستون PRIMARY KEY از جدول دیگر مطابقت داشته باشد.

  • مقدار FK بطور خودکار با تغییر مقدار ستون PK در جدول وابسته بروزرسانی می گردد
  • محدودیت FK باید به محدودیت PK یا UNIQUE در جدول دیگر اشاره کند
  • تعداد ستون های FK باید با PK یا UNIQUE در جدول دیگر برابر باشد
  • درصورت استفاده از WITH NOCHECK وجود مقادیر در جدول دیگر بررسی نمی گردد
  • هیچ ایندکسی روی ستون های FK ساخته نمی شود
شکل کلی محدودیت بصورت زیر است:
        [CONSTRAINT constraint_name]
        [FOREIGN KEY (col_name [, col_name2 […, col_name16]])]
        REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]
در مثال زیر فیلد HotelNo در جدول tblRoom یک FK به فیلد HotelNo در جدول tblHotel می باشد:
USE HOTEL
GO
CREATE TABLE tblRoom (
        HotelNo Int NOT NULL ,
        RoomNo Int NOT NULL,
        Type Char(50) NULL,
        Price Money NULL,
        PRIMARY KEY (HotelNo, RoomNo),
        FOREIGN KEY (HotelNo) REFERENCES tblHotel)

CHECK constraint

این شرط مقادیر وارد شده در جدول را محدود می کند از قبیل شرط های استفاده شده برای جستجوها به جزء Where.

شکل کلی این محدودیت به این صورت است:
        [CONSTRAINT constraint_name]
        CHECK [NOT FOR REPLICATION] (expression)
در این مثال فیلد Type به مقادیر ‘Single’، ‘Double’، ‘Suite’ ،  ‘Executive’ محدود می شود:
USE HOTEL
GO
CREATE TABLE tblRoom (
        HotelNo Int NOT NULL,
        RoomNo Int NOT NULL,
        Type Char(50) NULL,
        Price Money NULL,
        PRIMARY KEY (HotelNo, RoomNo),
        FOREIGN KEY (HotelNo) REFERENCES tblHotel
        CONSTRAINT Valid_Type
        CHECK (Type IN (‘Single’, ‘Double’, ‘Suite’, ‘Executive’)))
دومین مثال روی تاریخ استخدام یا مقدار حقوق می باشد:
GO
CREATE TABLE SALESREPS (
        Empl_num Int Not Null
        CHECK (Empl_num BETWEEN 101 and 199),
        Name Char (15),
        Age Int CHECK (Age >= 21),
        Quota Money CHECK (Quota >= 0.0),
        HireDate DateTime,
        CONSTRAINT QuotaCap CHECK ((HireDate < “01-01-2004”) OR (Quota <=300000)))

DEFAULT constraint

اگر برای ستون مقدار وارد نشده باشد بطور خودکار با این مقدار پیش فرض تکمیل می گردد. هر ستون فقط یک DEFAULT دارد.

شکل کلی این محدودیت به این صورت است:
        [CONSTRAINT constraint_name]
        DEFAULT {constant_expression | niladic-function | NULL}
        [FOR col_name]
مثلا برای مقدار پیش فرض ستون شهر به ‘Vancouver’ از دستور زیر استفاده می کنیم:
USE HOTEL
ALTER TABLE tblHotel
        Add CONSTRAINT df_city DEFAULT ‘Vancouver’ FOR City

ALTER TABLE

از این دستور برای افزودن یا حذف محدودیت ها استفاده می گردد همچنین می توان ستونی از جدول حذف یا به آن اضافه نمود. با افزودن محدودیت جدید تمام داده های موجود اعتبار سنجی می شوند.

به مثال زیر توجه نمایید:
USE HOTEL
GO
ALTER TABLE tblHotel
        ADD CONSTRAINT unqName UNIQUE (Name)
        ADD
        ColumnName int IDENTITY(seed, increment);

DROP TABLE

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

DROP TABLE tblHotel
0 935
دیدگاه کاربران
0 دیدگاه
شما هم دیدگاه خود را ارسال کنید