خانه --> مبانی --> مروری بر TSQL و دستورات پایگاه داده های SQL Server ، MySQL و Oracel

مروری بر TSQL و دستورات پایگاه داده های SQL Server ، MySQL و Oracel

SQL یا (Structured Query Language) یک زبان ساخت یافته برای ثبت، بازیابی و بهنگام سازی داده از یک پایگاه داده رابطه ای و حتی پایگاه داده های غیر رابطه ای است. دستوراتی برای ایجاد، حذف و تغییر اشیای مختلف در پایگاه داده دارد. پرکاربرد ترین دستور آن برای اجرای پرس و جوهای مختلف روی پایگاه داده استفاده می شود.  SQL یک زبان برنامه نویسی تعاملی استاندارد برای بازیابی و بهنگام سازی پایگاه داده رابطه ای است. SQL امکان ایجاد جدول، اضافه و حذف داده، اصلاح داده و اجرای پرس و جوی روی داده به شکل یک زبان فرمانی فراهم می کند. SQL به عنوان یک استاندارد توسط ANSI در سال ۱۹۸۶ و توسط ISO در سال ۱۹۸۷ پذیرفته شد. در این بخش فرامینی از SQL که در اکثر گونه های DBMS وجود دارد شرح داده خواهد شد. و از آنجایی که در این کتاب برای یک دست شدن مثال ها از محصولات میکروسافت استفاده می کنیم لذا برای بیشتر مثال های این بخش هم پایگاه داده MS SQL Server را ملاک کار خود قرار می دهیم. حال به تعریف اولیه به چند واژۀ پر کاربرد در SQL می پردازیم :

Query: به معنای درخواست است و در sql به کد جستجویی می گویند که شما برای بدست آوردن نتیجه ای آن را مینویسید و اجرا می کنید .

Resault set: نتیجه بدست آمده از یک Query را میگویند.

نکته: فضاهای خالی در عبارات SQL ندیده گرفته می شوند و برای خوانائی کدهای SQL استفاده می شوند و سمیکولن (;) به عنوان پایان دهنده عبارت است.

برای ساخت جدول باید ابتدا فیلدها یا همان ستون‌های جدول تعریف شود. فیلد‌های دارای DataType های مختلفی هستند که آنها را به اختصار توضیح می دهیم.

انواع داده ها

شرح داده

نوع داده

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

Binary Data

این نوع فیلد‌ها، از ۱ تا ۸۰۰۰ بایت را در خود جای می‌دهند

Binary

این نوع فیلد‌ها هم از ۱ تا ۸۰۰۰ بایت را در خود جای می‌دهند. (متغیر)

Var Binary

این نوع فیلد‌ها از ۱ تا حداکثر ۲ گیگابایت را می‌توانند ذخیره کنند. فرق این نوع داده‌ها با دو نوع قبلی این است که در دو نوع قبلی، اطلاعات در خود رکورد ثبت می‌شوند ولی در این نوع داده‌ها، اطلاعات در یک Page ذخیره می‌شود و به جایش در رکورد، یک پوینتر ۱۶ بایتی ذخیره می‌شود. این نوع فیلد‌ها در SQL 10 حذف شده و به جایش باید از VarBinary استفاده کرد.

Image

این نوع Datatype در SQL 2005 معرفی شده و تقریباً همانند داده‌های Image هستند

VarBinary(Max)

این نوع فیلد برای نگهداری عبارات و یا حروف ASCII می‌باشد. در این نوع فیلد‌ها، برای نگهداری هر حرف، یک بایت اشغال می‌شود و لذا نیاز به Collation برای تعیین زبان اطلاعات می‌باشد. این نوع فیلد‌ها هم چهار نوع هستند

Character Data

این نوع فیلد‌ها، اطلاعات متنی (رشته کاراکتری ASCII) با طول ثابت از ۱ تا حداکثر ۸۰۰۰ حرف را در خود ذخیره می‌کنند.

Char

این نوع فیلد‌ها اطلاعات متنی (رشته کاراکتری Unicode)  با طول متغیر از ۱ تا حداکثر ۸۰۰۰ حرف را در خود ذخیره می‌کنند. فرق بین Char و VarChar در این است که در Char، طول رشته ثابت است. یعنی اگر یک فیلد را از نوع Char(20) معرفی کرده و در آن کلمه Orion را قرار دهیم، عین ۲۰ حرف استفاده خواهد شد. یعنی ۵ کاراکتر اول را کلمه مربوطه اشغال کرده و ۱۵ کاراکتر باقی‌مانده، Blank  خواهند بود. اما در VarChar اینگونه نیست.

VarChar

: در این نوع داده‌ها، اطلاعات از ۱ تا ۲ مگا حرف ذخیره می‌شود. این نوع داده همانند Image و VarBinary(MAX) در خود رکورد ذخیره نمی‌شوند. بلکه توسط یک پوینتر به جای دیگری اشاره می‌کنند. این نوع داده در SQL 10 حذف شده و بجای آن‌ها از VarChar(MAX) استفاده می‌شود

Text

در این نوع داده‌ها، اطلاعات از ۱ تا ۲ مگا حرف ذخیره می‌شود و مکانیزم آن هم بصورت پوینتری می‌باشد.

VarChar(Max)

این نوع فیلد‌ها برای نگهداری متون Unicode بوده و برای نگهداری هر حرف، از دو بایت استفاده می‌شود. پس مسلماً نسبت به نوع داده‌های کاراکتری، حافظه بیشتری را به خود اختصاص می‌دهد و در ضمن کمی هم کندتر است. این نوع فیلد‌ها، احتیاج به Collation  ندارند.

Unicode Data

در این نوع داده، اطلاعات از ۱ تا حداکثر ۴۰۰۰ حرف با طول ثابت ذخیره می‌شود

nChar

در این نوع داده، اطلاعات از ۱ تا حداکثر ۴۰۰۰ بایت با طول متغیر ذخیره می‌شود

nVarChar

در این نوع داده، از ۱ تا ۱ مگا حرف ذخیره می‌شود. مکانیزم آن هم بصورت Pointer می‌باشد

nText

در این نوع داده از ۱ تا ۱ مگا حرف ذخیره می‌شود. مکانیزم آن هم بصورت Pointer می‌باشد

nVarChar(MAX)

این نوع فیلد برای نگهداری اعداد صحیح و بدون اعشار استفاده می‌گردد و دارای ۴ نوع به شرح زیر است. در ضمن این نوع فیلد‌ها رتبه یک سرعت در نوع فیلد‌های عددی را دارد.

Integer Data

یک بایت را اشغال می‌کند و می‌تواند از ۰ تا ۲۵۵ را در خود ذخیره کند

TinyInt

یک عدد دو بایتی است و می‌تواند از ۳۲۷۶۷ منفی تا ۳۲۷۶۷ مثبت را در خود ذخیره کند

SmallInt

یک عدد چهار بایتی است که می‌تواند اعداد بین مثبت و منفی ۲ میلیارد را در خود ذخیره کند

Int

یک عدد ۸ بایتی است که می‌تواند اعداد بین مثبت و منفی ۴ میلیارد را در خود ذخیره کند

BigInt

این نوع فیلد برای نگهداری اعداد غیر صحیح با تعداد ارقام اعشار ثابت ۴ رقمی استفاده می‌شود. این نوع فیلد‌ها، دارای رتبه دوم سرعت در بین انواع داده‌ی عددی است.

Money Data

یک عدد ۴ بایتی است که می‌تواند ۶ رقم صحیح و ۴ رقم اعشار را در خود ذخیره کند

SmallMoney

: یک عدد ۸ بایتی است که می‌تواند ۱۵ رقم صحیح و ۴ رقم اعشار را در خود ذخیره کند

Money

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

Approximate Data

یک عدد ۴ بایتی است که اعداد بصورت توانی از ۱۰ نگهداری می‌شوند. (تعداد اعشار مشخص نیست

Real

یک عدد ۸ بایتی که اعداد بصورت توانی از ۱۰ نگهداری می‌شوند. (تعداد اعشار مشخص نیست

Float

این نوع فیلد برای نگهداری اعداد اعشاری با تعداد اعشار مشخص استفاده می‌گردد. این نوع فیلد‌ها بسیار کند بوده و استفاده از آنها توصیه نمی‌گردد. فرمت اینگونه داده‌ها به این شکل است: Decimal (Precision,Scale)که در آن Precision به معنای تعداد کل رقم‌های عدد و Scale تعداد ارقام اعشار را مشخص می‌کند. مثلاً اگر فیلدری بصورت Deciaml(6,2) تعریف شود، حداکثر آن برابر ۹۹۹۹٫۹۹ می‌باشد.

Decimal

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

Date/Time

این نوع فیلد، ۴ بایتی است و از سال ۱۹۰۰ تا ۲۰۷۹ را با دقت هزارم ثانیه ذخیره می‌کند

SmallDateTime

: این نوع فیلد، ۸ بایتی است و از سال ۱۷۰۰ تا ۹۹۹۹ را با دقت هزارم ثانیه ذخیره می‌کند

DateTime

برخی از انواع فیلد‌های خاص را در این قسمت به اختصار توضیح می دهیم

Other DataTypes

یک فیلد دو بیتی است و می‌تواند ۰ و ۱ و Null را ذخیره کند.کاربرد آن در زمان‌هایی است که دو حالت وجود داشته باشد. مانند جنسیت زن و مرد

Bit

در این فیلد ۸ بایتی، تایم لحظه‌ای اجرای دستور نگهداری می‌شود و کاربرد آن کنترل بروزرسانی همزمان (Concurrency) اطلاعات توسط چند کاربر است. البته در تعداد رکوردهای پایین به کار نمی‌آید و بیشتر زمانی مورد نیاز است که تعداد رکوردها خیلی زیاد باشد مثلاً ۱۰۰ میلیون رکورد!

TimeStamp

این فیلد ۱۶ بایتی، به ما کدی Unique یا تک می‌دهد که به اصطلاح GUID می‌گویند. یکی از کاربردهای آن در Replication است

UniqueIdentifier

: این نوع فیلد برای نگهداری انواع داده استفاده می‌شود و نوع آن با توجه به اولین مقداری که در آن قرار می‌گیرد تعیین خواهد شد. چون نوع و حجم فیلد مشخص نیست، لذا تنها یک اشاره‌گر ۱۶ بایتی در آن قرار گرفته و داده اصلی در فایل جداگانه نگهداری می‌شود. استفاده از این نوع فیلد، توصیه نمی‌گردد

SQL_Variant

این فیلد مربوط به کنترل Cursor است

‍Cursor

این فیلد بیشتر برای انتقال اطلاعات و دستورات تحت web استفاده می‌شود و شامل انواع MetaData های مختلف است. این فیلد در SQL 2005 معرفی گردید

XML

 

خواص تکمیلی فیلد ها

PRIMARY KEY : یعنی این فیلد از نوع کلید اصلی است.

NOT NULL: یعنی این فیلد باید مقداری داشته باشد و نمی تواند تهی باشد.

:REFERENCES othertable (primarykeyname) یعنی این فیلد یک کلید خارجی است که در جدول دیگری کلید اصلی است.

در انتهای تعریف می توان اطلاعات اضافی دیگری را داشت. برای نمونه:

PRIMARY KEY (column1,column2,…): اگر جدول کلید اصلی ترکیبی دارد باید آنرا در انتهای تعریف مشخص کنید.
FOREIGN KEY (column1,column2,…) :REFERENCES othertable اگر جدول ارتباطی با جدول دیگر دارد که یک کلید ترکیبی دارد بنابراین ستون های این جدول که کلید های خارجی هستند باید به این صورت لیست شوند.

 CREATE TABLE

عبارت Create یک فرمان [۱]DDL در SQL است که برای ایجاد یک شیء در پایگاه داده استفاده می شود. احتمالا معمول ترین فرمان Create فرمان CREATE TABLE است. این  اجازه ایجاد شمای یک جدول را می دهد. فرم کلی آن به صورت زیر است:

CREATE TABLE tablename (
colname  datatype  coloptions
,colname  datatype  coloptions
,colname  datatype  coloptions
,additionalinfo
);

هر سطر یک فیلد جدول را مشخص می کند. تعریف هر فیلد شامل نام، نوع داده و اطلاعات اضافی مربوطبه آن می تواند باشد. سطرها با کاما (,) از هم جدا می شوند.

مثال: تعریف جداول Car و Driver با چند فیلد نمونه می تواند به صورت زیر باشد:

CREATE TABLE driver (
name varchar(30)
,dob DATE NOT NULL
,PRIMARY KEY (name)
);


 

CREATE TABLE car (
regno VARCHAR(8)
,make VARCHAR(20)
,colour VARCHAR(30)
,price DECIMAL(8,2)
,owner VARCHAR(30)
,PRIMARY KEY(regno)
,FOREIGN KEY(owner) REFERENCES driver
);

 DROP TABLE

عبارت DROP برای از بین بردن یک شیء در پایگاه داده است. فرمان DROP TABLE زمانی بکار می رود که بخواهید جدول را حذف کنید. فرم کلی آن به صورت زیر است:

DROP TABLE tablename;

 مثال. فرمان زیر جدول car را حذف می کند.

DROP TABLE car;

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

مثال. چون جدول car توسط کلید خارجی با جدول driver در ارتباط است می توانید ابتدا جدول Car و سپس Driver را حذف کنید ولی عکس آن نمی شود.

DROP TABLE car;
DROP TABLE driver;

SELECT

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

SELECT DISTINCT columns AS columns
FROM table
WHERE rule
GROUP BY columns
HAVING rule
ORDER BY columns;

دستور SELECT دارای چند عبارت اختیاری به شرح زیر است:

FROM جدول یا جداولی را که از آنها داده بازیابی می شود را مشخص می کند. برای الحاق جداول بر اساس ضابطه خاصی می تواند همراه با عبارت JOIN بیاید.
WHERE همراه با یک گزاره شرطی برای محدود کردن سطرهای برگردانده شده استفاده می شود.
GROUP BY اغلب همراه با توابع تجمعی(AVE، SUM، MAX، MIN و COUNT) برای ترکیب یا گروه بندی سطرها یا حذف سطرهای تکراری در مجموعه نتیجه استفاده می شود.
HAVING همراه با یک گزاره شرطی روی نتیجه GROUP BY کار می کند. توابع تجمعی می توانند در گزاره شرطی HAVING هم استفاده شوند.
ORDER BY برای تعیین ستون های که بر اساس آنها داده نتیجه مرتب می شود (صعودی و نزولی) .

FROM

در ساده ترین دستور SELECT کلیه سطرهای یک جدول که بعد از عبارت FROM ذکر شده است را بازیابی می کند. لیستی از فیلدهای موردنظر در مقابل عبارت SELECT قرار می گیرد. نام فیلدها با کاما (,) از هم جدا می شوند. علامت ستاره (*) برای بیان کلیه فیلدهای یک جدول (یا چند جدول) می تواند استفاده شود.

مثال. اسامی کلیه شعبه ها در loan را پیدا کن

SELECT branch_name
FROM loan;

مثال. مشخصات کلیه مشتریان را پیدا کن.

SELECT *
FROM customer;

عبارات محاسباتی +، – ، * و / روی یک فیلد در لیست فیلدها می توانند بکاربرده شوند.

مثال. دستور زیر جدولی مشابه loan را بر می گرداند که مقدار صفت خاصه amount آن ۱۰ برابر شده است.

SELECT loan_number, branch_name, amount * 10
FROM loan;

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

مثال. اسامی و مقدار وام کلیه مشتریانی را که وامی از شعبه Perryridge گرفته اند را پیدا کن.

SELECT customer_name, borrower.loan_number, amount
FROM borrower, loan
WHERE borrower.loan_number = loan.loan_number AND
     branch_name = ‘Perryridge’;

مثال. دقت کنید که اگر شرطی ذکر نشود ضرب دکارتی دو جدول حاصل می شود.

SELECT *
FROM borrower, loan;

 

 DISTINCT

SQL اجازه تکرار در نتیجه SELECT را می دهد. بنابراین جدول حاصل ممکن است دارای سطرهای مشابه باشد. اگر این موضوع موردنظر نباشد عبارت DISTINCT را استفاده می کنیم. در این صورت کلیه سطرهای جدول حاصل منحصر بفرد خواهند بود. و سطرهای تکراری حذف می شوند.

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

SELECT DISTINCT branch_name
FROM loan;

عبارت all مشخص می کند که تکراری ها حذف نشوند.

SELECT ALL branch_name
FROM loan;

 

 WHERE

عبارت WHERE برای انتخاب سطرهای برگردانده شده از دستور SELECT بر طبق شرط خاصی بکار می رود.

برای بیان شرط می توان عملگرهای مقایسه ای (=، !=، <>، >، <، >= و <=) را استفاده کرد.

نتایج مقایسه را می توان توسط عملگرهای منطقی (NOT، AND و OR ) و پرانتز با هم ترکیب کرد. اجرای عملگرهای منطقی به ترتیب الویت آنها است. NOT الویت بالاتر و OR الویت کمتر را دارد. پرانتز می تواند برای تعیین ترتیب انجام عملیات استفاده شود. عمل داخل پرانتز همیشه اول انجام می گیرد.

مثال. کلیه شماره وام هائی که مقدار وام آنها از ۱۲۰۰ بیشتر است را پیدا کن.

SELECT loan_number
FROM loan
WHERE amount > 1200;

مثال. کلیه شماره وام های شعبه Perryridge که مقدار وام آنها از ۱۲۰۰ بیشتر است را پیدا کن.

SELECT loan_number
FROM loan
WHERE amount > 1200 AND branch_name = ‘Perryridge’;

 

توجه کنید که هنگام مقایسه با رشته باید آنرا در کوتیشن (‘ ‘) قرار داد.

مثال.شماره وام هائی که مقدار وام آنها بین ۹۰,۰۰۰ و ۱۰۰,۰۰۰ می باشد را پیدا کن.

SELECT loan_number
FROM loan
WHERE amount BETWEEN 90000 AND 100000;

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

مثال. مقدار وامهایی که از شعب Perryridge، Downtown یا Redwood گرفته شده اند را پیدا کن.

SELECT amount
FROM loan
WHERE Branch_name IN (‘ Perryridge’ , ‘Downtown’ , ‘Redwood’);

هنگام کارکردن با رشته ها وقتی خواهان مطابقت کامل رشته ها نیستیم بلکه بخشی از رشته یا الگوی خاصی از آن بیشتر موردنظر است، می توان از عبارت LIKE به جای علامت (=) استفاده کرد. دو کاراکتر ‘%’ و ‘_’ به ترتیب به معنی یک کاراکتر و بیشتر از یک کاراکتر را برای تطابق می توان بکار برد.

مثال. اسامی کلیه مشتریانی که آدرس آنها شامل کلمه Main است را پیدا کن.

SELECT customer_name
FROM customer
WHERE customer_street LIKE ‘%Main%’;

 

AS

SQL اجازه تغییر نام جدول را توسط عبارت AS می دهد.

مثال. تعداد وام و مقدار وام کلیه وام ها را پیدا کرده، نام ستون loan_number به loan_id تغییر بده.

SELECT loan_number AS loan_id, amount
FROM loan;

مثال. نام و تعداد وام کلیه مشتریانی که وامی در یک شعبه دارند را پیدا کن.

SELECT customer_name, T.loan_number, S.amount
FROM borrower AS T, loan AS S
WHERE T.loan_number = S.loan_numbe;

 

توابع تجمعی

توابع تجمعی (aggregation function) عملگرهایی هستند که محاسبه آماری روی گروهی از مقادیر داده ای را انجام می دهند. این توابع روی مقادیر یک ستون از یک جدول عمل می کند و یک مقدار را به عنوان نتیجه بر می گردانند. این توابع شامل AVG، SUM، MAX، MIN و COUNT هستند.

نتیجه تجمع نامی ندارد می تواند از AS برای نامگذاری آن استفاده کرد.

مثال. میانگین موجودی حساب ها در شعبه Perryridge را پیدا کن.

SELECT AVG (balance)
FROM account
WHERE branch_name = ‘Perryridge’;

COUNT تعداد سطرهای موجود در جواب که حاوی NULL نیستند را می دهد. برای اینکه تعداد مستقل از NULL باشد COUNT(*) را استفاده کنید.

مثال. تعداد مشتریان بانک را محاسبه کن.

SELECT COUNT (*)
FROM customer;

گاهی در جواب تعدادی سطرها مشابه می شوند، اگر می خواهید تعداد سطرهای متمایز را بدست آورید از COUNT DISTINCT استفاده کنید.

مثال. تعداد افرادی که در بانک پول دارند را پیدا کن.

SELECT COUNT (DISTINCT customer_name)
FROM depositor;

مثال. تعداد افرادی که در هر شعبه بانک حساب دارند را پیدا کن.

SELECT branch_name, COUNT (DISTINCT customer_name)
FROM depositor, account
WHERE depositor.account_number = account.account_number
GROUP BY branch_name;

 

GROUP BY

در بسیاری موارد تحلیل آماری روی گروهی از داده ها موردنیاز است. برای گروه بندی از عبارت GROUP BY استفاده کنید.

مثال. اسامی کلیه شعب و میانگین موجودی حساب آنها را پیدا کن

SELECT branch_name,AVG (balance)
FROM account
GROUP BY branch_name;

 

HAVING

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

مثال. اسامی کلیه شعب را که میانگین حساب آنها بیشتر از ۱۲۰۰ است را پیدا کن

SELECT branch_name,AVG (balance)
FROM account
GROUP BY branch_name
HAVING AVG (balance) > 1200;

 

ORDER BY

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

مثال. اسامی کلیه مشتریانی که وامی در شعبه Perryridge دارند را به ترتیب حروف الفبا لیست کن.

SELECT DISTINCT customer_name
FROM borrower, loan
WHERE borrower loan_number = loan.loan_number AND
     branch_name = ‘Perryridge’
ORDER BY customer_nam;

NULL

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

SELECT loan_number
FROM loan
WHERE amount IS NULL;

 

پرس و جوهای تودرتو

در SQL مکانیسمی برای پرس و جوهای تودرتو فراهم شده است. به عبارت دیگر یک عبارت SELECT می تواند درون دیگری قرار بگیرد تا نتیجه اجرای آن در شرط WHERE عبارت SELECT دیگر استفاده شود. عبارت SELECT دوم را یک پرس و جوی فرعی می نامند و حتما باید یک فیلد را برگرداند یعنی فقط یک صفت خاصه در دستور SELECT آن باید باشد. وقتی حاصل پرس و جوی فرعی بیشتر از یک سطر باشد از عملگرهای ALL، ANY، IN، NOT IN، EXISTS و NOT EXISTS برای گرفتن نتیجه مطلوب باید استفاده کرد.

مثال. اسامی کلیه مشتریانی که هم حساب وهم وام در بانک دارند را پیدا کن.

SELECT DISTINCT customer_name
FROM borrower
WHERE customer_name IN (SELECT customer_name FROM depositor );

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

SELECT DISTINCT customer_name
FROM borrower
WHERE customer_name NOT IN (SELECT customer_name FROM depositor );

مثال. اسامی کلیه مشتریانی که هم حساب وهم وام در شعبه Perryridge دارند را پیدا کن.

SELECT DISTINCT customer_name
FROM borrower, loan
WHERE borrower.loan_number = loan.loan_number AND branch_name = ‘Perryridge’ AND
branch_name, customer_name IN (SELECT branch_name, customer_name
FROM depositor, account WHERE depositor.account_number = account.account_number );

توجه. پرس و جوهای بالا ساده تر هم می تواند نوشته شود.

ترکیب پرس و جوها

گاهی می خواهیم نتیجه دو پرس و جو را با هم به نحوی ترکیب کنیم و یک جدول را بدست بیاوریم. عملگرهای UNION، INTERSECT و EXCEPT برای ترکیب نتیجه دو پرس و جو می توانند استفاده شوند که به ترتیب مشابه عملگرهای اجتماع، اشتراک و تفاضل در جبر رابطه ای عمل می کنند. مجموعه فیلدهای دو پرس و جوئی که با هم ترکب می شوند باید از نظر تعداد و نوع مطابق هم باشند. عملگر UNION جدولی شامل کلیه سطرهای هردو پرس و جو را می دهد. سطرهای تکراری حذف می شوند مگر اینکه از عبارت UNION ALL استفاده شود.

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

عملگر EXCEPT سطرهائی از نتیجه پرس و جوی اول که در نتیجه پرس و جوی دوم ظاهر نشده است را بر می گرداند. EXCEPT ALL سطرهای تکراری را حذف نمی کند.

مثال. اسامی کلیه مشتریانی که هم حساب وهم وام در بانک دارند را پیدا کن.

SELECT customer_nameFROM borrower
UNION
SELECT customer_name FROM depositor;

 

JOIN

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

دستورات پایگاه داده
دستورات پایگاه داده

Natural Join

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

مثال. اسامی وام گیرنده ها به همراه وام های گرفته شده از بانک را پیدا کنید.

SELECT *
FROM borrower NATURAL JOIN loan;

 

Outer Join

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

LEFT OUTER JOIN کلیه مقادیر جدول سمت چپ خود را بعلاوه مقادیری از جدول سمت راست که مطابقت دارند می دهد. RIGHT OUTER JOIN کلیه مقادیر جدول سمت راست خود را می دهد بعلاوه مقادیری از جدول سمت چپ که رکوردهایش جور هستند. FULL OUTER JOIN نتیجه الحاق خارجی چپ و راست را با هم ترکیب می کند. الحاق خارجی داده های مفقود را، برای سطرهائی که شرط الحاق در آنها برقرار نبوده، با NULL پر می کند.

مثال. اسامی کلیه وام گیرنده ها به همراه میزان وامی که گرفته اند را پیدا کنید.

SELECT *
FROM borrower LEFT OUTER JOIN loan
ON borrower.loan_no = loan.loan_no;

مثال. مقدار کلیه وام های گرفته شده از شعبه Perryridge را به همراه نام وام گیرنده ها پیدا کنید.

SELECT *
FROM borrower RIGHT OUTER JOIN loan
ON borrower.loan_no = loan.loan_no
WHERE loan.branch_name = ‘Perryridge’;

 

DELETE

فرمان DELETE اجازه حذف سطرهائی از یک جدول را می دهد. فرم کلی دستور به شکل زیر است:

DELETE FROM table_name WHERE condition;

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

دستور DELETE هیچ رکوردی را به عنوان خروجی بر نمی گرداند.

مثال. کلیه رکوردهای وام گیرندگان را حذف کن.

DELETE FROM borrower;

مثال. کلیه رکوردهایی که حسابی در شعبه Perryridge دارند را حذف کن.

DELETE FROM account
WHERE branch_name = ‘Perryridge’;

 

INSERT

دستور INSERT اجازه اضافه کردن رکوردی به یک جدول را می دهد. فرم کلی آن به صورت زیر است:

INSERT INTO table_name
(column_list)
VALUES (value_list);

Column_list لیست فیلدهائی است که مقادیر به آنها نسبت داده خواهد شد و اگر برای همه فیلدها مقداری درنظرگرفته شود می تواند حذف شود. value_list مجموعه ای از مقادیر است که برای هر فیلد در لیست column_list یا فیلدهای جدول که در دستور CREATE TABLE تعریف شده اند مقداری دارد. تعداد ستون ها و مقادیر آنها باید یکسان باشد. اگر فیلدی ذکر نشود مقدار پیش فرض آن درنظر گرفته می شود. به فیلدهائی که در دستور CREATE TABLE به عنوان PRIMARY KEY یا با محدودیت NOT NULL تعریف شده اند باید مقداری نسبت داده شود. مثال. حساب جدیدی را با شماره A9732 و با موجودی ۱۲۰۰ در شعبه Perryridge اضافه کن.

INSERT INTO account
VALUES (‘A-9732’, ‘Perryridge’,1200);
یا
INSERT INTO account
(branch_name, balance, account_number)
VALUES (‘A-9732’, ‘Perryridge’,1200);

مثال: اضافه کردن رکورد جدیدی در جدول account با مقدار موجودی null به صورت زیر انجام می شود.

INSERT INTO account
VALUES (‘A-777′,’Perryridge’, null );

 

UPDATE

دستور UPDATE اجازه تغییر داده های درون یک جدول را می دهد. این دستور هیچ رکوردی را اضافه یا حذف نمی کند. شکل کلی آن به صورت زیر است:

UPDATE table_name
SET column_name = value, column_name=value, …
WHERE condition;

در کلیه رکوردهائی که شرط در آنها برقرار بوده است مقدار فیلدی که نامش در عبارت SET تعیین برابر با مقدار جدید می شود. دستور UPADTE ممکن است روی یک یا چند رکورد در یک جدول تاثیر بگذارد. مثال. حساب هائی که موجودی آنها بیشتر از ۱۰۰۰۰ است را به میزان ۶% افزایش بده.

UPDATE account
SET balance = balance * 1.06
WHERE balance > 10000;

منابع:

http://www.hpkclasses.ir/Courses/DataBase/db0800.html

آدرس کانال تلگرام سایت بیگ دیتا:

t.me/bigdata_channel

آدرس کانال سروش ما:
https://sapp.ir/bigdata_channel

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

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *