آموزش جامع SQL Server (جلسه یازدهم: تغییر داده‌ها)
1398/03/04 14:31 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه یازدهم: تغییر داده‌ها)

در این جلسه، خواهید آموخت که چگونه می‌توانید محتوای جداول را در پایگاه داده SQL Server تغییر دهید. دستورات SQL برای تغییر داده‌ها (مانند DELETE، INSERT و UPDATE) به زبان‌های دست‌کاری داده (DML) معروف هستند.

  • INSERT: وارد کردن یک ردیف به یک جدول

  • INSERT multiple rows: با استفاده از یک دستور INSERT، چندین ردیف به یک جدول اضافه می‌کند.

  • INSERT INTO SELECT: داده‌های به دست آمده از یک query را در یک جدول وارد می‌کند.

  • UPDATE: مقادیر فعلی در یک جدول را تغییر می‌دهد.


  • UPDATE JOIN: با استفاده از دستورهای JOIN، مقادیر یک جدول را بر اساس مقادیر یک جدول دیگر تغییر می‌دهد.

  • DELETE: یک یا تعداد بیشتری از ردیف‌های یک جدول را حذف می‌کند.

  • MERGE: در ایجاد ترکیبی از insertها، updateها و deleteها با استفاده از یک دستور تنها به شما کمک می‌کند.

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

۱- نصب و آشنایی با SQL Server

۱۱-تغییر داده‌ها بخش ۱

۲۱-indexها بخش۱

۳۱-توابع رشته‌ای بخش۱

۲-نوشتن query

۱۲-تغییر داده‌ها بخش ۲

۲۲-indexها بخش۲

۳۲- توابع رشته‌ای بخش۲

۳-محدود کردن ردیف‌ها

۱۳-تعریف داده‌ها بخش۱

۲۳-stored procedureها بخش۱

۳۳- توابع رشته‌ای بخش۳

۴-فیلتر کردن داده‌ها بخش ۱

۱۴-تعریف داده‌ها بخش۲

۲۴- stored procedureها بخش۲

۳۴-توابع سیستمی بخش ۱

۵-فیلتر کردن داده‌ها بخش۲

۱۵-تعریف داده‌ها بخش۳

۲۵- stored procedureها بخش۳

۳۵-توابع سیستمی بخش۲

۶-اتصال جداول بخش۱

۱۶-data typeهای بخش۱

۲۶-توابع تعریف شده توسط کاربر

۳۶-window functionها بخش۱

۷-اتصال جداول بخش۲

۱۷-data typeها بخش۲

۲۷-triggerها

۳۷-window functionها بخش ۲

۸-گروه‌بندی داده‌ها

۱۸-ویژگی‌ها

۲۸-توابع تجمعی

 

۹-subquery

۱۹-expressionها

۲۹-توابع تاریخ یا date بخش۱

 

۱۰-عملگرهای SET وCTEوPIVOT

۲۰-viewها

۳۰-توابع تاریخ یا date بخش۲

 

 

دستور INSERT در SQL Server

برای اضافه کردن یک یا تعداد بیشتری ردیف به یک جدول، از دستور INSERT استفاده می‌شود. کد زیر ساده‌ترین شکل استفاده از عبارت INSERT را نشان می‌دهد:

INSERT INTO table_name (column_list)
VALUES (value_list);

اجازه دهید این syntax را دقیق‌تر بررسی کنیم.

ابتدا، باید نام جدولی که می‌خواهید به آن ردیف اضافه کنید را مشخص کنید. معمولاً با استفاده از نام شما به جدول اشاره می‌کنید (مانند production.products که در آن production نام شما و products نام جدول است.

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

اگر یک ستون از جدول در لیست ستون‌ها قرار نگیرد، SQL Server باید بتواند برای وارد کردن‌ داده‌ها یک مقدار فراهم کند، در غیر این صورت عملیات insert انجام نمی‌شود.

SQL Server به صورت خودکار از مقدار زیر برای ستونی که در جدول قرار دارد اما در لیست ستون‌های عبارت INSERT درج نشده است، استفاده می‌کند:

  • در صورتی که ستون دارای ویژگی IDENTITY باشد، عدد افزایشی بعدی برای آن ستون درج می‌شود.
  • اگر ستون دارای یک مقدار پیش‌فرض مشخص شده باشد، از مقدار پیش‌فرض استفاده می‌شود.
  • اگر نوع داده ستون یک نوع داده timestamp است، از مقدار timestamp فعلی استفاده می‌شود.
  • اگر ستون می‌تواند مقدار Null‌ بگیرد، از NULL استفاده می‌شود.
  • اگر ستون یک ستون محاسبه‌ای است، از مقدار محاسب شده استفاده می‌شود.

در آخر، یک لیست از مقادیر فراهم می‌کنید که در دستور VALUES قرار می‌گیرند. هر ستون در لیست ستون‌ها باید یک مقدار مشابه در لیست value داشته باشد. همچنین، باید لیست value را در بین پرانتز قرار دهید.

مثال‌های دستور INSERT در SQL Server

اجازه دهید برای مثال‌ها یک جدل جدید با نام promotions ایجاد کنیم:

CREATE TABLE sales.promotions (
    promotion_id INT PRIMARY KEY IDENTITY (1, 1),
    promotion_name VARCHAR (255) NOT NULL,
    discount NUMERIC (3, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
); 

در این کد، ما یک جدول جدید به نام promotions در شمای sales ایجاد کردیم. جدول promotions دارای ۵ ستون است که عبارت‌اند از start date، discount، name، number و expired date.

ستون identification number یک ستون شناسایی است که وقتی یک ردیف جدید به جدول اضافه می‌کنید، مقدار آن به طور خودکار توسط SQL Server تعیین می‌شود.

۱)مثال ساده از INSERT

کد زیر یک ردیف جدید در جدول promotions ایجاد می‌کند:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
    (
        '2018 Summer Promotion',
        0.15,
        '20180601',
        '20180901'
    );

در این مثال، ما برای چهار ستون در جدول promotions مقدار مشخص کرده‌ایم. برای ستون promotion_id مقداری مشخص نکرده‌ایم، چون SQL Server این مقدار را به طور خودکار برای آن ستون مشخص می‌کند.

اگر دستور INSERT با موفقیت اجرا شود، رقم ردیف‌ها خود به خود وارد می‌شوند. در این مثال، SQL Server پیغام زیر را می‌دهد:

(1 row affected)

برای تائید صحیح بودن این عملیات، می‌توانید از query زیر استفاده کنید:

SELECT
    *
FROM
    sales.promotions;

همان‌طور که انتظار می‌رفت، نتایج به شرح زیر هستند:

نتایج

۲)وارد کردن و بازگرداندن مقادیر وارد شده

برای به دست آوردن مقادیر وارد شده، از دستور OUTPUT استفاده می‌کنیم. برای مثال، کد زیر یک ردیف جدید به جدول promptions اضافه کرده و مقدار وارد شده در ستون promotion_id را بازمی‌گرداند:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id
VALUES
    (
        '2018 Fall Promotion',
        0.15,
        '20181001',
        '20181101'
    );

نتایج

برای به دست آوردن مقادیر وارد شده از چندین ستون، باید همان‌طور که در کد زیر مشاهده می‌کنید، ستون‌ها را در خروجی مشخص کنید:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id,
 inserted.promotion_name,
 inserted.discount,
 inserted.start_date,
 inserted.expired_date
VALUES
    (
        '2018 Winter Promotion',
        0.2,
        '20181201',
        '20190101'
    );

تصویر زیر خروجی را نشان می‌دهد:

نتایج

۳)وارد کردن مقادیر ضمنی در یک ستون identity

معمولاً، برای ستون‌های identity مقدار مشخص نمی‌شود، چون SQL Server مقدار را به صورت خودکار وارد می‌کند.

به هر حال، در برخی موقعیت‌ها، شاید بخواهید یک مقدار به یک ستون identity وارد کنید، برای مثال در هنگام انتقال داده‌ها.

دستور INSERT در کد زیر را مشاهده کنید:

INSERT INTO sales.promotions (
    promotion_id,
    promotion_name,
    discount,
    start_date,
    expired_date
) OUTPUT inserted.promotion_id
VALUES
    (
        4,
        '2019 Spring Promotion',
        0.25,
        '20190201',
        '20190301'
    );

SQL Server برای کد بالا ارور زیر را می‌دهد:

Cannot insert explicit value for identity column in table 'promotions' when IDENTITY_INSERT is set to OFF.

برای وارد کردن مقدار ضمنی به یک ستون identity، باید ابتدا کد زیر را اجرا کنید:

SET IDENTITY_INSERT table_name ON;

برای غیر فعال کردن Identity_insert از یک دستور مشابه استفاده می‌شود:

SET IDENTITY_INSERT table_name OFF;

اجازه دهید دستورات زیر را اجرا کنیم تا یک مقدار به یک ستون identity در جدول promotions وارد شود:

SET IDENTITY_INSERT sales.promotions ON;
 
INSERT INTO sales.promotions (
    promotion_id,
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
    (
        4,
        '2019 Spring Promotion',
        0.25,
        '20190201',
        '20190301'
    );
 
 
SET IDENTITY_INSERT sales.promotions OFF;

در این مثال، ابتدا، edentity_insert را فعال کردیم، سپس یک ردیف با یک مقدار ضمنی برای ستون identity وارد کردیم و در آخر identity_insert را غیر فعال کردیم.

کد زیر داده‌های جدول Promotions پس از وارد سازی اطلاعات را نشان می‌دهد:

SELECT
    *
FROM
    sales.promotions;

نتایج

در نتیجه، به صورت کامل چگونگی استفاده از دستور INSERT در SQL Server برای اضافه کردن یک ردیف جدید به یک جدول را آموختید.

دستور INSERT Multiple Rows در SQL Server

در بخش بالا، چگونگی اضافه کردن یک ردیف به یک جدول با استفاده از دستور INSERT بررسی شد.

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

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

در این syntax، به جای استفاده از یک لیست مقادیر یا value، باید از لیست‌های مقادیر چندتایی مجزا شده با ویرگول برای ورود داده استفاده کنید.

تعداد ردیف‌هایی که هر دفعه با استفاده از این دستور INSERT می‌توانید وارد کنید ۱۰۰۰ ردیف  است. اگر می‌خواهید ردیف‌های بیشتر از این تعداد وارد کنید، باید استفاده از چندین دستور INSERT، دستور BULK INSERT یا یک جدول مشتق گرفته شده را در نظر بگیرید.

توجه کنید که این syntx باری وارد کردن چندین ردیف با INSERT فقط در SQL Server 2008 و بالاتر پشتیبانی می‌شود.

برای وارد کردن چندین ردیف که توسط یک دستور SELECT بازگردانده شده‌اند، باید از دستور INSERT INTO SELECT استفاده کنید.

مثال‌های INSERT Multiple Rows در SQL Server

از جدول sales.prompotions که در بخش قبلی ایجاد شد برای این مثال‌ها استفاده می‌کنیم.

اگر هنوز جدول Sales.promotions را ایجاد نکرده‌اید، می‌توانید از کد CREATE TABLE زیر استفاده کنید:

CREATE TABLE sales.promotions (
    promotion_id INT PRIMARY KEY IDENTITY (1, 1),
    promotion_name VARCHAR (255) NOT NULL,
    discount NUMERIC (3, 2) DEFAULT 0,
    start_date DATE NOT NULL,
    expired_date DATE NOT NULL
); 
۱)مثال وارد کردن چندین ردیف

کد زیر چندین ردیف را در جدول sales.promotions وارد می‌کند:

INSERT INTO sales.promotions (
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
    (
        '2019 Summer Promotion',
        0.15,
        '20190601',
        '20190901'
    ),
    (
        '2019 Fall Promotion',
        0.20,
        '20191001',
        '20191101'
    ),
    (
        '2019 Winter Promotion',
        0.25,
        '20191201',
        '20200101'
    );

برای کد بالا، SQL Server پیغام زیر را نشان می‌دهد که بیان می‌کند ۳ ردیف با موفقیت به جدول اضافه شدند.

(3 rows affected)

می‌توانیم با استفاده از query زیر درستی کدهای بالا را امتحان کنیم:

SELECT
    *
FROM
    sales.promotions;

خروجی به این شکل است:

نتایج

۲)مثال وارد کردن چندین ردیف و بازگرداندن لیست id

این مثال سه ردیف به جدول sales.promotions اضافه می‌کند و لیست promotion identity را بازمی‌گرداند:

INSERT INTO 
 sales.promotions ( 
 promotion_name, discount, start_date, expired_date
 )
OUTPUT inserted.promotion_id
VALUES
 ('2020 Summer Promotion',0.25,'20200601','20200901'),
 ('2020 Fall Promotion',0.10,'20201001','20201101'),
 ('2020 Winter Promotion', 0.25,'20201201','20210101');

نتایج

در این مثال، ما دستور OUTPUT را با استفاده از دستور insdrted.column_name برای ستونی که می‌خواستیم بازگردانده شود به کار بردیم. اگر می‌خواهید مقادیر چندین ستون را بازگردانید، می‌توانید از دستور زیر استفاده کنید:

OUTPUT inserted.column1, inserted.column2...

بسیار خب، به صورت کامل و مثال محور، چگونگی استفاده از شکل دیگر دستور INSERT در SQL Server برای وارد کردن چندین ستون در یک جدول را آموختید.

دستور INSERT INTO SELECT در SQL Server

برای وارد کردن داده از جداول دیگر به داخل یک جدول،  می‌توانید از دستور INSERT INTO SELECT به شکل زیر استفاده کنید:

INSERT  [ TOP ( expression ) [ PERCENT ] ] 
INTO target_table (column_list)
query

در این syntax، ردیف‌های بازگردانده شده توسط query درون جدول targer_table اضافه می‌شوند.

Query می‌تواند هر گونه دستور SELECT قابل‌قبولی که داده‌ها را از دیگر جدول‌ها به دست می‌آورد، باشد. این query باید مقادیری که مشابه ستون‌های مشخص شده در column list هستند را بازگرداند.

دستور TOP اجباری نیست. این دستور به شما امکان می‌دهد تا تعداد ردیف‌های بازگردانده شده توسط query را درون جدول هدف (target_table) وارد کنید. اگر از آپشن PERCENT استفاده کنید، آنگاه برنامه درصد ردیف‌های وارد شده را وارد می‌کند.

توجه کنید که تمرین خیلی خوبی است که همیشه از دستور TOP همراه با دستور ORDER BY استفاده کنید.

مثال‌های INSERT INTO SELECT در SQL Server

اجازه دهید برای مثال‌ها جدولی به نام addresses ایجاد کنیم:

CREATE TABLE sales.addresses (
    address_id INT IDENTITY PRIMARY KEY,
    street VARCHAR (255) NOT NULL,
    city VARCHAR (50),
    state VARCHAR (25),
    zip_code VARCHAR (5)
);   
۱)مثال وارد کردن تمام ردیف‌ها از یک جدول دیگر

کد زیر تمام آدرس‌ها را از جدول customers به جدول Addresses وارد می‌کند:

INSERT INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name; 

برای بررسی درستی کد بالا، می‌توانید از query زیر استفاده کنید:

SELECT
    *
FROM
    sales.addresses;

نتایج به شکل زیر هستند:

نتایج

۲)مثال وارد کردن چند ردیف از یک جدول دیگر

گاهی، فقط لازم است برخی از ردیف‌های یک جدول دیگر را به جدول مورد نظرتان اضافه کنید. در این مورد، باید تعداد ردیف‌های بازگردانده شده از query را با استفاده از شروط موجود در دستور WHERE محدود کنید.

کد زیر آدرس‌های فروشگاه‌های واقع در santa cruz و Baldwin را به جدول Addresses اضافه می‌کند:

INSERT INTO 
    sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.stores
WHERE
    city IN ('Santa Cruz', 'Baldwin')

SQL Server پیغام زیر را بازمی‌گرداند که نشان می‌دهد دو ردیف با موفقیت اضافه شده‌اند.

(2 rows affected)
۳)وارد کردن N ردیف بالایی

ابتدا، از دستور زیر برای حذف کردن تمام ردیف‌ها از جدول addresses استفاده می‌کنیم:

TRUNCATE TABLE sales.addresses;

سپس، برای وارد کردن ۱۰ مشتری بالایی که بر اساس نام و نام‌ خانوادگی مرتب شده‌اند، از دستور INSERT TOP INTO SELECT به شکل زیر استفاده می‌کنیم:

INSERT TOP (10) 
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

SQL Server پیغام زیر را بازمی‌گرداند که بیان می‌کند ۱۰ ردیف با موفقیت اضافه شدند.

(10 rows affected)
۴)وارد کردن درصد ردیف‌های بالایی

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

ابتدا، تمام ردیف‌های جدول addresses را truncate کنید:

TRUNCATE TABLE sales.addresses;

سپس، ۱۰ درصد از ردیف‌های بالایی جدول customers که بر اساس نام و نام خانوادگی مرتب شده‌اند را به جدول Addresses وارد کنید:

INSERT TOP (10) PERCENT  
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

SQL Server پیغام زیر را نمایش می‌دهد که بیان می‌کند ۱۴۵ ردیف با موفقیت اضافه شدند.

(145 rows affected)

به این شکل، چگونگی استفاده از دستور INSERT INTO SELECT برای وارد کردن ردیف‌ها از جداول دیگر به یک جدول را آموختید.

 

خلاصه

در این جلسه مبحث تغییر داده‌ها به صورت کامل همراه با مثال و حل مسئله توضیح داده شد.

دستورات و روش‌های دخیل در تغییر داده‌ها عبارت بودند از : DELETE ،UPDATE JOIN، UPDATE، INSERT INTO SELECT، INSERT multiple rows، INSERT و MERGE که هر کدام در بخش‌های مجزا مورد بررسی قرار گرفتند و توضیح داده شدند.

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

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

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

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

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

فرم ارسال نظرات