دستور Group By در sql

از دستور Group By در اسکیوال برای دسته بندی یک ستون بر حسب مقادیر مشابه فیلدهای یک ستون دیگر استفاده می شود .

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

ساختار این دستور به صورت زیر است :

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

دستور Group By در sql وقتی استفاده میشود که ما در حال انتخاب چند ستون هستیم و حداقل یک عملگر محاسباتی در دستور select داریم . در این زمان ما باید تمام ستونهای دیگر را گروه کنیم.

مثال : در مثال زیر مجموع نمرات هر دانش آموز را بصورت گروه بندی شده بر اساس فیلد نام دانش آموز به خروجی داده می شود : 

Select Name ,Sum ( Grade ) As مجموع نمرات
From Class
Group By Name ;

نکته : دستور group by در اسکیوال از جمله ی پرکاربردترین دستورات است که یادگیری کامل این دستور ، میتواند تفاوت اسکریپت نویسی حرفه ای در sql باشد.

عملگر های And و Or در SQL

عملگرهای And و Or برای ترکیب شرط ها در دستور Where در sql استفاده می شود .
گاهی اوقات خروجی که ما میخواهیم در اس کیوال بایستی چند شرط مختلف داشته باشد . به طور مثال افرادی را میخواهیم که سن بالای 23 سال و مدرک تحصیلی بالای لیسانس داشته باشند . در این حالت بایستی هر کدام از شرط ها را جداگانه تعریف کرده و سپس آنها را با هم ترکیب کنیم . برنامه هر کدام از شرط ها را بررسی میکند و خروجی را نمایش میدهد.

عملگر And برای اجرای دستور نیاز دارد تا تمام شرط های تعیین شده برای آن درست باشد .

SELECT * FROM Persons
WHERE FirstName=' saeed '
AND LastName=' rajabi '

عمگر Or فقط نیاز دارد که حداقل یکی از شرط ها درست باشد .

SELECT * FROM Persons
WHERE FirstName='saeed'
OR FirstName='rajabi'

ترکیب عملگرهای And و Or

Select * From Persons
Where ( ( Grade = 16 AND Major = 'Hard Ware' ) OR ( Garde = 12 AND Major = 'SoftWare' ) ) " ;

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

برای تغییر نام فیلد در جدول از تغییر کد باید به روش زیر و با کمک stored procedure های خود اسکیوال ، عمل کرد :

sp_RENAME 'table_name.old_name' , 'new_name', 'COLUMN'

مثال از تغییر نام ستون و فیلد TerritoryID به TerrID از جدول Sales از پایگاه داده ی Db_name :

USE Db_name;
GO
EXEC sp_rename 'Sales.TerritoryID', 'TerrID', 'COLUMN';
GO

Field چیست ؟

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

هر فیلد در بر گیرنده یک صفت و ویژگی برای موجودیت می باشد ، که دارای 2 جزء اصلی است :

1.اسم صفت خاصه : نام صفت مورد نظر را تعیین می کند . برای مثال فیلد نام ، نام خانوادگی ،  و ... در جدول اطلاعات مربوط به شخص.
2.مقدار صفت خاصه : در برگیرنده مقدار برای صفت مورد نظر است . برای مثال مقدار " سعید " به عنوان مقدار برای فیلد نام .

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

مثال درباره فیلد ها : اگر شما یک جدول با نام “Students” داشته باشید، ممکن است زمینه های زیر مورد نیاز باشند:
Name – برای ذخیره نام و نام خانوادگی دانش آموز
Address – برای ذخیره آدرس
DateofBirth - برای ذخیره تاریخ تولد دانش آموز
RegistrationDate – برای ذخیره تاریخ ثبت نام دانش آموز
و …
اگر شما فیلدی را به جدول اضافه کنید، این فیلد به تمام رکوردهای موجود آن جدول اضافه خواهد شد. در مثال فوق، تمامی رکوردهای جدول در “Students” همان ۴ فیلد را خواهند داشت.

تابع lcase در sql

تابع lcase در اسکیوال مقادیر فیلد را به حروف کوچک تبدیل میکند.

ساختار دستور LCase در sql به صورت زیر است :

SELECT LCASE(column_name) FROM table_name

دستور مرتب سازی Order By در sql

دستور Order By در sql

اطلاعاتی که در دستور select در اسکیوال به عنوان خروجی نمایش داده میشود ی بی نظم و یا بهتر بگوییم بدون نظم مد نظر ما است. مقادیر خروجی در ستون های جدول بر اساس مقدار هیج ستونی مرتب نمیشوند . با دستور دستور Order By میتوان اطلاعات جدول را بر اساس مقادیر یک یا چند ستون برحسب شاخص هایی مثل ترتیب حروف الفبا ، بزرگتر یا کوچکتر بودن اعداد و ...مرتب کرد.

ساختار دستور Order By

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) [ASC|DESC]

علامات [] در اطراف where بدین معناست که میتوانید where را بکار نبرید. اما اگر بکار بردید حتما باید قبل از order باشد . ASC به معنای صعودی بودن (a to z)و DESC به معنای نزولی بودن است (z to a).پیش فرضش ACS است.

نکته : همچنین این نیز امکان پذیرست که مرتب سازی را بر مبنای بیش از یک ستون انجام دهید .برای این منظور بخش مرتب سازی کد بالا بصورت زیر در میاید :

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

مثال :

Select Name , Family From Person
Where idnumber="11"
Order By Family

خروجی دستور بالا ، اشخاص با شرط ذکر شده در دستور where را بر اساس نام فامیلی مرتب صعودی میکند.

تابع Min در sql

تابع Min در sql کمترین مقدار موجود در بین مقادیر فیلدهای یک ستون را بر می گرداند . 

تابع min در اس کیوال با ستون هایی که داده آنها از نوع عددی یا حروفی باشد ، می تواند به کار رود . در فیلدها با مقادیر عددی تابع کوچکترین عدد و در فیلدها با مقادیر متنی ، تابع کلمه ای که به ترتیب حروف الفبا از آ تا ی در فارسی و A تا Z در انگلِسی در بالاترین رده باشد ، را بر می گرداند .

ساختار دستور min در sql به صورت زیر است :

SELECT MIN(column_name) FROM table_name

محدودیت Unique در sql

محدودیت Unique , SQL UNIQUE Constraint در sql

از محدودیت Unique زمانی استفاده میکنیم که بخواهیم مقادیر بعضی از فیلدها تکراری نباشند.

این فیلدها ممکن است کلید نباشند ، اما بنابر لزوم برنامه بایستی غیر تکراری باشند. به عنوان مثال فیلد آدرس ایمیل،  کلید نیست اما باید Unique یا واحد باشد. برای این کار از Unique Index ها استفاده می‌کنیم.

• محدودیت PRIMARY KEY به صورت خودکار، محدودیت Unique را نیز دارد.
  
• در هر جدول بیش از یک فیلد نیز میتواند از محدودیت Unique استفاده کند.

محدودیت UNIQUE در هنگام ساخت TABLE

برای تعریف محدودیت Unique در دستور create table در sql ، روی یک ستون از کلمه کلیدی UNIQUE بصورت زیر استفاده می کنیم:

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

برای تعریف محدودیت Unique روی چند ستون از دستور SQL زیر استفاده می کنیم:

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)
)

محدودیت UNIQUE پس از ساخت جدول

برای تغییر دادن محدودیت Unique روی یک ستون از دستور SQL زیر استفاده می کنیم:

ALTER TABLE Persons
ADD UNIQUE (P_Id)

برای تغییر دادن محدودیت Unique روی چند ستون از دستور SQL زیر استفاده می کنیم:

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

از بین بردن یک محدودیت UNIQUE

برای از بین بردن محدودیت Unique از دستور SQL زیر استفاده می کنیم:

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

دستور SELECT INTO در sql

از دستور Select Into در موارد زیر استفاده می شود :

1. ایجاد یک Back Up ( نسخه پشتیبان ) از یک جدول .
2. ایجاد یک آرشیو از رکوردهای یک جدول .
3. قرار دادن برخی از رکوردها یا فیلدهای مورد نظر از یک جدول در یک جدول جدید .
4. ایجاد یک نسخه پشتیبان از کل یک پایگاه داده در یک پایگاه داده جدید .

 تمامی مثالهای زیر نحوه استفاده از دستور select into را نشان میدهد:

• در این مثال (ساختار) ما می میخواهیم برخی از رکوردها یا فیلدهای مورد نظر از یک جدول در یک جدول جدید قرار دهیم:

We can select all columns into the new table:
SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

•  در این مثال (ساختار) ما تنها  می توانیم ستون ها را برای ایجاد یک جدول جدید انتخاب کنیم :

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

•  در این مثال ما میخواهیم یک Back Up ( نسخه پشتیبان ) از یک جدول Persons ایجاد کنیم:

SELECT *
INTO Persons_Backup
FROM Persons

•  در این مثال ما میخواهیم  یک کپی از جدول Persons را به یک پایگاه داده دیگر (Backup.mdb) اضافه کنیم :

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

•  در این ساختار ما میتوانیم  یک تعدادی از فیلدها را به جدول جدید کپی کنیم:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

•   در این مثال ما میخواهیم  با داشتن یک دستور شرطی فیلدهایی را از جدول Persons به جدول Persons_Backup اضافه  کنیم :

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

• انتخاب داده از بیش از یک جدول هم در دستور select into امکان پذیر است . در مثال زیر جدول "Persons_Order_Backup"  شامل داده ها از دو جدول "Persons" و "Orders" میباشد.

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

ذکر نام یک پایگاه داده جدید در دستور Select Into اختیاری است . چنانچه نامی در این قسمت ذکر نشود ، برنامه نسخه پشتیبان را در همان پایگاه داده موجود ایجاد می کند و چنانچه نامی ذکر شود ، برنامه نسخه پشتیبان را در پایگاه داده ذکر شده ایجاد می کند .

دستور SELECT در sql

دستور SELECT در اسکیوال برای انتخاب و استخراج اطلاعات مورد نظر از یک یا چند جدول و سپس مشاهده نتایج در یک جدول موقت استفاده می شود  :

SELECT column_name(s)
FROM table_name

SELECT در اینجا به مفهوم فعل است و مشخص میکند که میخواهیم اطلاعاتی را بخوانیم .

• SELECT فهرست ستونها مشخص میکند که قصد خواندن کدام ستونها را از بانک داریم در صورتی که در این فهرست * قرار دهیم کلیه ستونهای جدول نمایش داده میشود .
• با استفاده از کلمه FROM مشخص میکنیم که از کدام جدول یا جداول این ستونها باید انتخاب شوند .

دستور SELECT * 

دستور SELECT *  همه اطلاعات موجود در جدول را انتخاب میکند.برای انتخاب کلیه ستون های جدول  در قسمت نام ستون باید علامت * را گذاشت استفاده کرد . 

SELECT * FROM table_name

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

دستور CREATE DATABASE در sql

برای ایجاد یک پایگاه داده جدید از دستور CREATE DATABASE استفاده می شود.

ساختار دستور create database در اسکیوال به صورت زیر است:

CREATE DATABASE database_name

 برای ایجاد یک دیتابیس با نام mySite_db به صورت زیر عمل میکنیم :

CREATE DATABASE mySite_db

آموزش view در sql

view در sql در واقع همان جداول مجازی هستند که توسط آن می توانید عملیات خاصی رو که شامل دستورات sql میشه رو انجام بدهید و به صورت خروجی داشته باشید .

کاربرد view در اسکیوال

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

ساختار دستور View در sql به صورت زیر است :

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Record چیست ؟

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

موجودیت

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

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

مثال از رکورد در پایگاه داده : اگر شما جدول “Students” برای ذخیره اطلاعات دانش آموزان داشته باشید، در این جدول یک رکورد نشان دهنده یک دانش آموز خواهد بود. برای اضافه کردن دانش آموز به برنامه، باید یک رکورد به جدول “Students” اضافه کنید. برای پاک کردن یا ویرایش اطلاعات دانش آموز هم باید شما یک رکورد را از این جدول حذف

علامت های شرطی در Like

یادآوری از دستور Like :  همانطور که در مقاله ی قبلی در مورد دستور like در sql گفته شد ، هنگام کارکردن با رشته ها همیشه خواهان مطابقت کامل رشته ها نیستیم ، بلکه بخشی از رشته یا الگوی خاصی از آن بیشتر موردنظر است. در این هنگام می توان از دستور LIKE در شرط های اسکیوال ، به جای علامت = استفاده کرد.

در دستور like ، دو کاراکتر % و _ به ترتیب به معنی چند کاراکتر و یک کاراکتر را برای تطابق می توان بکار برد.

در آموزش sql این علامت ها کاربرد بسیاری دارند.

مثال : اسامی کلیه مشتریانی که آدرس آنها شامل کلمه Main است بطوریکه قبل Main هر چند کاراکتری که خواست بیاید ولی بعد از Main فقط یک کاراکتر بیاید را پیدا کن :

SELECT customer_name
FROM customer
WHERE customer_street LIKE '%Main_';

دستور Delete در sql

دستور Delete در sql برای حذف اطلاعات یک رکورد در جدول بانک اسکیوال استفاده می شود. از دستور delete باید با دقت استفاده شود زیرا امکان بازگرداندن رکورد های حذف شده به این راحتی ها نیست !

DELETE FROM table_name
WHERE some_column=some_value

برای پاک کردن یک رکورد با شناسه مشخص از دستور زیر استفاده میکنیم.

Delete From Person
Where ID = "16 "

دستور Delete * 

این امکان وجود دارد که با دستور Delete * بدون حذف یک جدول ، کلیه رکوردهای درون آن را پاک کرد.

Delete From Person

فیلد identity در sql

فیلد identity در sql به این معناست که به ازای هر رکوردی که در جدول وارد می کنیم ، فیلد identity شده ، به صورت اتوماتیک مقدار دهی خواهد شد و نمی توانیم این فیلد را مقدار دهی کنیم .

فیلد identity دو خصوصیت دارد:

Identity Increment : که مشخص می کند مقدار فیلد چند تایی افزایش پیدا کند، به طور پیش فرض مقدار آن یک است و می تواند اعداد مثبت و همین طور منفی نیز بگیرد.

Identity Seed : مشخص می کند مقدار این فیلد از چه عددی شروع شود، به طور پیش فرض از یک شروع می شود ولی می تواند از اعداد منفی و  هر عدد مثبتی برای شروع فیلد Identity استفاده کرد.

برای استفاده از فیلد identity در sql از ساختار زیر استفاده مکنیم :

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

نکته مهم : اگر رکوردهای جدول را با Delete حذف کنیم مقدار آخرین Identity حفظ خواهد شد و درج با عدد بعدی آن صورت می گیرد، اگر بخواهیم بعد از حذف داده ها درج با مقدار Seed تعریف شده صورت گیرد به جای Delete از دستور TRUNCATE table_name استفاده می کنیم، به این ترتیب لاگ هم ذخیره نخواهد شد ، ضمن اینکه سرعت حذف رکوردها بسیار بیشتر است.

 

تابع Format در اسکیوال

تابع format در sql چگونگی نمایش یک فیلد را مشخص میکند.

ساختار دستور format در sql به صورت زیر است :

SELECT FORMAT(column_name,format) FROM table_name

مثال چگونگی کار تابع format در اسکیوال را توضیح میدهد :

SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products

محدودیت Check در sql

محدودیت Check ,  SQL CHECK Constraint یا محدودیت های خاص در sql

از محدودیت Check یا محدودیت خاص در اس کیو ال زمانی استفاده میکنیم که بخواهیم برای یک فیلد، یک سری محدودیت خاص روی فیلد تعریف کنیم.

برای مثال : مقادیر داخل فیلد بایستی حتما بزرگتر از 10 باشد در غیر اینصورت در هنگام ورود اطلاعات، کاربر با خطا مواجه گردد.

نکته : محدودیت Check را میتوان برای ستون و جدول تعریف کرد.

محدودیت CHECK هنگامی که جدول ساخته می شود

  برای تعریف یک محدودیت Check در دستور create table ، روی یک ستون از دستور SQL زیر استفاده می کنیم:

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)
)

 برای تعریف یک محدودیت Check  روی چند ستون از دستور SQL زیر استفاده می کنیم: 

 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 پس از ساخته شدن جدول

برای تغییر دادن یک محدودیت Check  روی یک ستون از دستور SQL زیر استفاده می کنیم:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

 برای تغییر دادن یک محدودیت Check  روی چند ستون از دستور SQL زیر استفاده می کنیم:

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

از بین بردن محدودیت CHECK

برای از بین بردن محدودیت Check یا محدودیت خاص ، از دستور زیر استفاده می کنیم:

ALTER TABLE Persons
DROP CHECK chk_Person

دستور Insert در sql

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

دو روش استفاده از این دستور وجود دارد :

INSERT INTO table_name
VALUES (value1, value2, value3,...)

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

نکته : می توان در دستور Insert در اسکیوال تعیین کرد که مقادیر مورد نظر به ترتیب به کدام ستون های جدول وارد شوند . برای این منظور ابتدا نام ستون های مورد نظر را به ترتیب جلوی نام جدول در یک پرانتز وارد کرده و با کاما از هم جدا می کنیم . سپس مقادیر متناظر را به همان ترتیب پس از واژه Vaues در پرانتز وارد می کنیم .
نکته : چنانچه برای فیلد یا فیلدهایی مقداری در دستور insert در نظر گرفته نشود ، مقادیر پیش فرض تعیین شده و در صورت تعیین نکردن مقدار پیش فرض جای آنها در جدول خالی می ماند . فقط باید به ترتیب نام ستون ها و مقادیر دقت کرد .

Insert into Student ( Name , Family ) Values ( "Saeed" , "Rajabi" )

در مثال بالا نام Saeed در فیلد Name و نام Rajabi در فیلد Family میرود. بقیه ی فیلد ها در صورت تعیین کردن مقدار پیش فرض آن مقدار را میگیرند و در صورت تعیین نکردن آن ، مقدار خالی یا Null میگیرند.

تابع Now در sql

تابع now در اسکیوال تاریخ و ساعت جاری سیستم را برمیگرداند.

ساختار دستور now در sql به صورت زیر است :

SELECT NOW() FROM table_name