آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

آموزش مفهوم کلیدهای جانشین یا surrogate keys در sql

دوره های مرتبط با این مقاله

Identity column: معرفی مفهوم کلیدهای جانشین یا surrogate keys

Surrogate key یک ستون است که مقادیرش به صورت خودکار توسط موتور پایگاه داده (DB Engine) تولید و ارائه می شود. Surrogate key زمانی کاربرد دارد که هیچ راه موجه و مشخصی برای استفاده از مقادیر یک ستون (ایجاد شده در جدول جاری) به عنوان کلید اصلی وجود نداشته باشد. مقادیر ستون surrogate key معمولا اعداد صحیح (از نوع int) هستند که معنی و مفهوم خاصی برای کاربر ندارند.
ویرایش 2012 محیط مدیریت بانک های اطلاعاتی رابطه ای Microsoft SQL Server، روش های مختلفی برای ایجاد surrogate key ارائه می کند. دو روش برای ایجاد surrogate key، identity key ها و sequence ها را شامل می شود.


مقدمه ای بر identity column ها

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


Unit Price
Size
Item Name
Category
39.95
Large
Long-sleeve jersey dress
Women
39.95
S
Iron-Free Pleated Khaki Pants
Boys
59.60
Large
Striped long-sleeve shirt
Men
45.95
Large
Long-sleeve jersey dress
Women
45.00
Shoulder handbag
Girls
39.95
Petite
Continental skirt
Women

می خواهیم مقدار یک آیتم به نام "Long-sleeve jersey dress" را تغییر دهیم. از آنجایی که باید آیتم را با کدنویسی در جدول جستجو کنید، می توانید از اسم آیتم برای پیدا کردن آن استفاده نمایید. این جدول در واقع دو آیتم با این اسم دارد. پس از آن به جستجوی آیتم دیگری بر اساس category یا دسته آن می پردازیم. در ستون Category، تعداد زیادی آیتم با نام "Women" وجود دارد. به همین شکل، رکوردهای متعددی وجود دارند که مقدارشان در ستون Size، "Large" می باشد. در رابطه با ستون Unit Price نیز با همین مشکل مواجه می شویم. نتیجه می گیریم که معیار مناسبی برای جداسازی رکوردهایی که مقدار ستون Item Name آن ها shirt Long-sleeve می باشد نداریم.
برای حل معضل شناسایی رکوردها و تمییز آن ها از یکدیگر، می توان یک ستون ایجاد که وظیفه و نقش اصلی آن تشخیص دادن یک رکورد از رکورد دیگر است. برای این منظور، SQL به شما اجازه می دهد یک ستون ایجاد نموده که نوع داده ای آن int می باشد و همچنین کاربر مقداری را در آن وارد نمی کند، بلکه مقدار به صورت خودکار و زمانی که رکورد جدید ایجاد می شود در آن فیلد وارد می شود. به چنین ستونی در اصطلاح identity column گفته می شود.
برای ایجاد identity column بایستی یک جدول جدید ایجاد نمایید (امکان ایجاد آن در یک جدول از داده (سطر و ستون) پر شده وجود ندارد).
کار را با پایگاه داده ی university ادامه می دهیم. در این ورژن از پایگاه داده ی یاد شده، University6، تعدادی جدول جدید ایجاد می کنیم:


  1. جدول Departments: این جدول حاوی اطلاعاتی درباره ی بخش های مختلف است که برای اداره ی دانشگاه بکار می رود.
  2. جدول :Locations این جدول دربردارنده ی لیستی از ساختمان ها و بخش هایی است که در محوطه ی دانشگاه وجود دارد. تعدادی از ساختمان ها به دفتر آموزش تبدیل می شوند و تعدادی هم به کلاس تخصیص می یابند.
  3. Course Delivery Types: این جدول روش های مختلف برقراری دوره های آموزشی همچون حضوری، آنلاین و غیره .. را تشریح می کند.
  4. Undergraduate Courses: این جدول لیستی از دوره های آموزشی است توسط دانشگاه منتشر و در اختیار داشنجویان قرار می گیرد. دوره های آموزشی جدید به طور منظم اضافه شده و برخی از دوره های آموزشی هم که منسوخ شده اند، حذف می شوند. هر دوره ی آموزشی دارای یک کد، اسم، تعداد واحد، ملزومات و نیز توصیف مختصری درباره ی آن دوره ی آموزشی می باشد.
  5. Genders: این جدول برای مشخص کردن جنسیت دانشجو بکار می رود. جدول مذکور همچنین دربردارنده ی اطلاعاتی درباره ی افراد فعال در دانشگاه نظیر کارکنان، اساتید و دانشجویان خواهد بود.
  6. Teachers: این جدول فهرستی از اساتید دانشگاه را شامل می شود. تعدادی دانشیار و برخی استاد تمام هستند. در اطلاعات هر استاد یک شماره ی منحصربفرد (معادل شماره ی کارمند در جدول Employee)، اسم (نام و نام خانوادگی)، مدارک علمی استاد و غیره مشاهده می شود.
  7. شی Undergraduate Majors: major زمینه یا رشته ی تحصیلی دانشجو می باشد. برای اینکه زمینه ی کاری خود را گسترش دهد، دانشگاه حوزه های مطالعاتی فراوانی را پوشش داده است که به نحوی علت وجود تعداد بسیار زیاد رشته های تحصیلی می باشد. پایگاه داده ی جاری جدولی ویژه ی رشته های تحصیلی متعدد خواهد داشت.
  8. :Minors همان طور که از اسمش پیدا است، minor یک رشته ی تحصلی جانبی است که تمرکز اصلی دانشجو قرار نمی گیرد و نسبت به رشته تحصیلی اصلی دانشجو به زمان و توجه کمتری احتیاج دارد. پایگاه داده ی University دربردارنده ی یک جدول ویژه minor خواهد بود.

برخی از جداولی که قبلا ذکر شد، به این ورژن از پایگاه داده نیز اضافه می شوند. این جداول عبارتند از employees، semesters و schedules.


آموزش کاربردی: ایجاد ستون Identity

1. محیط Microsoft SQL Server را راه اندازی نموده و به سرور وصل شوید.
2. بر روی اسم سرور راست کلیک کرده و گزینه ی New Query را انتخاب نمایید.
3. به منظور ایجاد پایگاه داده ی جدید، دستور زیر را تایپ کنید:


USE master;
GO
DROP DATABASE University5
GO
CREATE DATABASE University6;
GO
USE University6;
GO
CREATE SCHEMA Academics;
GO
CREATE SCHEMA Administration;
GO
/* Instead of specified a deterministic date of birth, we will supply 
   a number of days to this function and, based on day this script is 
   run, the function will subtract the number of days from that date.
   That's how we will get the date of birth of a student. */
   
CREATE FUNCTION Administration.SetDateOfBirth(@days int)
RETURNS Date
AS
BEGIN
	RETURN DATEADD(d, @days, SYSDATETIME());
END
GO
CREATE TABLE Administration.Genders
(
    GenderLetter nvarchar(3) not null,
    GenderName nvarchar(50)
);
GO
CREATE TABLE Administration.Departments
(
    DepartmentCode nvarchar(4) not null,
    DepartmentName nvarchar(50) default N'N/A'
);
GO
CREATE TABLE Administration.Employees
(
    EmployeeNumber nvarchar(8) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20) not null,
    DepartmentCode nvarchar(4),
    Title nvarchar(100),
    Gender nvarchar(3) default N'N/A'
);
GO
CREATE TABLE Academics.CourseDeliveryTypes
(
    CourseDeliveryTypeCode nvarchar(1) not null,
    CourseDeliveryType nvarchar(25),
    Notes nvarchar(max)
);
GO
CREATE TABLE Academics.UndergraduateCourses
(
    CourseCode nvarchar(8) not null,
    CourseName nvarchar(100),
    Credits integer not null,
    CourseDescription nvarchar(max),
    Prerequisites nvarchar(100),
    Notes nvarchar(max)
);
GO
CREATE TABLE Administration.Locations
(
    LocationCode nvarchar(5) not null,
    LocationName nvarchar(50) not null,
    Notes nvarchar(max)
);
GO
INSERT INTO Administration.Genders
VALUES(N'M', N'Male'),
      (N'F', N'Female'),
      (N'U', N'Unknown');
GO
INSERT INTO Administration.Departments(DepartmentCode, DepartmentName)
VALUES(N'N/A', NULL),
      (N'ADMN', N'Administration, Admissions, and Students Affairs'),
      (N'HRMN', N'Human Resources and Management'),
      (N'WRTG', N'Wrighting'),
      (N'EDUC', N'Educational Studies'),
      (N'LNGS', N'Languages and Linguistics'),
      (N'PSOP', N'Psychology, Sociology, and Philosophy'),
      (N'ITEC', N'Information Technology'),
      (N'FINA', N'Finances'),
      (N'ACCT', N'Accounting'),
      (N'EBCM', N'Economics, Business, Commerce, and Marketing'),
      (N'CJLE', N'Criminal Justice and Law Enforcement'),
      (N'CHEM', N'Chemistry'),
      (N'CMSC', N'Computer Sciences'),
      (N'ELCE', N'Electrical and Computer Engineering'),
      (N'CMST', N'Computer Studies'),
      (N'HSGE', N'History and Geography'),
      (N'ANTH', N'Anthropology'),
      (N'BIOL', N'Biology and Biotechnology'),
      (N'HLTH', N'Health Care and Gerontology'),
      (N'MATH', N'Mathematics and Statistics'),
      (N'GVPS', N'Government and Political Sciences');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'279227', N'Donald',  N'Henry',  N'Leighton',  N'ADMN', N'President', N'M'),
      (N'502494', N'Anthony', N'Robert', N'Parrish',   N'ADMN', N'Provost', N'M'),
      (N'247591', N'Leonid',  N'George', N'Hawthorne', N'HSGE', N'Dean of History, Geography, and Political Sciences', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'400384', N'Jennifer',  N'Palermo', N'HRMN', N'Dean of Human Resources and Management Studies', N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'274039', N'Joyce',    N'Denise',   N'Blue',      N'CHEM', N'Dean of Chemistry Studies', N'F'),
      (N'409260', N'Edmond',   N'Gabriel',  N'Harrington', N'CJLE', N'Dean of Criminal Justice Studies', N'M'),
      (N'828347', N'Robert',   N'Elie',     N'Marsden',   N'MATH', N'Dean of Mathematics, statistics, and Physics', N'M'),
      (N'640207', N'Kimberly', N'Carlette', N'Edelman',   N'PSOP', N'Dean of Psychology, Sociology, and Philosophy', N'F'),
      (N'161138', N'Laura',    N'Fannie',   N'Joansen',   N'ADMN', N'Dean of Litterary Studies', N'F'),
      (N'605924', N'Phillipe', N'Ernest',   N'Portman',   N'BIOL', N'Dean of Biological and Biotechnology Studies', N'M'),
      (N'908047', N'Ann',      N'Laura',    N'Tenney',    N'FINA', N'Cashier',  N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'582007', N'Alexander', N'Nolan',  N'CMSC', N'Dean of Computer Sciences and Computer Engineering', N'M'),
      (N'697300', N'Albert',    N'Harney', N'FINA', N'Dean of Financial and Accounting Studies', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'702048', N'Laurentine', N'Felicité', N'Avrilien', N'EDUC', N'Dean of Educational Studies',  N'F'),
      (N'927486', N'Robert',     N'John',     N'Preston',  N'CMST', N'Dean of Computer Studies', N'M'),
      (N'930248', N'Jeannette',  N'Veronica', N'Holms',    N'ADMN', N'Vice President for Government Relations', N'F');
GO
INSERT INTO Academics.CourseDeliveryTypes
VALUES(N'F', N'Face-to-Face', N'The course will be taught in a classroom. Consult the schedule to find out how often the class will meet.'),
      (N'L', N'Online', N'The course will de delivered over the Web and other means of remote communications such as video and/or conference call.'),
      (N'H', N'Hybrid', N'The course will be taught face-to-face part-time and online part-time. For example, the teacher and students may meet face-to-face in one session and online for the next section. Consult the course schedule or the teacher communicate how the class will be conducted.');
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits)
VALUES(N'LBRS 100', N'Library and Research', 1),
      (N'EDPD 100', N'Education and Personal Development', 3),
      (N'CMSC 101', N'Introduction to Computer Programming With C++', 3),
      (N'WRTG 101', N'Introduction to Writing', 3);
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits, Prerequisites)
VALUES(N'MATH 106', N'College Algebra', 3, N'MATH 012'),
      (N'MATH 115', N'Pre-Calculus', 3, N'MATH 012');
GO
INSERT INTO Academics.UndergraduateCourses(CourseCode, CourseName, Credits)
VALUES(N'HIST 140', N'Technological Transformations', 3),
      (N'GVPS 140', N'National and Local Governments', 3);
GO
INSERT INTO Administration.Locations(LocationCode, LocationName)
VALUES(N'ADMAS', N'Administration and Auxiliary Services'),
      (N'HMNSS', N'Humanities and Social Sciences Building'),
      (N'FSOPR', N'Facilities Services and Operations'),
      (N'SEAMT', N'Science, Engineering, and Mathematics Building'),
      (N'NAASB', N'Natural and Applied Sciences Building'),
      (N'PACTR', N'Performing Arts Center'),
      (N'PEAHL', N'Physical Education and Health Building'),
      (N'LATAC', N'Literatrue and Academic Building'),
      (N'SPASC', N'Space Science Center'), 
      (N'SMALC', N'Sun, Moon, and Life Center'),
      (N'JPTRC', N'Jupiter Center'),
      (N'LIBRM', N'Libraries and Media Building'),
      (N'DSSSS', N'Disability Support Services'),
      (N'SECRB', N'Security Building'),
      (N'ONLNE', N'Online'),
      (N'HYBRD', N'Hybrid');
GO

4. به منظور اجرای دستورات بالا، کلید F5 را فشار دهید.
5. در پنجره ی Object Explorer، گره Databases را باز نمایید.
6. پایگاه داده ی University6 را باز کنید.
7. بر روی Tables راست کلیک نموده، سپس گزینه ی New Table... را انتخاب کنید.
8. جدول را انتخاب نموده و در پنجره ی Properties متعلق به آن، بر روی Schema کلیک نمایید و سپس Academics را انتخاب کنید.


ایجاد یک ستون Identity به صورت ویژوال

جهت ایجاد یک ستون identity، کافی است در بخش بالایی پنجره ی design جدول، اسم ستون را مشخص کنید. معمولا اسم این ستون هم نام با اسم مفرد جدول می باشد. همچنین اسم ستون عموما به _id, Id یا ID ختم می شود.
پس از مشخص کردن اسم ستون، نوع داده ای این ستون را بر روی نوع int تنظیم نمایید. نوع داده ای این ستون غالبا از نوع int یا عدد صحیح می باشد. در کادر پایین پنجره ی design، خصیصه ی Identity Specification را باز کرده و مقدار فیلد (Is Identity) را از No به Yes تغییر دهید.
پس از تنظیم مقدار خصیصه ی (Is Identity) بر روی Yes، زمانی که کاربر برای بار نخست داده وارد این جدول می کند، مقدار رکورد اول بر روی 1 تنظیم می شود. این ویژگی یا قابلیت توسط خصیصه ی Identity Seed کنترل می شود. چنانچه مایلید شمارش از عدد دیگری شروع شود، کافی است آن را در فیلد مزبور (Identity Seed) تنظیم نمایید.
پس از تنظیم مقدار (Is Identity) بر روی Yes، مفسر SQL مقدار هر سطر جدید را به اندازه ی یک واحد (1) افزایش می دهد که مقدار پیش فرض می باشد. پس نتیجه می گیریم که رکورد اول مقدار 1، رکورد دوم مقدار 2 و به همین ترتیب ادامه می یابد. این امکان توسط خصیصه ی Identity Increment مدیریت می شود. اگر می خواهید واحد افزایش تغییر یابد (بیشتر شود برای مثال 2 تا 2 تا بالا برود)، در آن صورت بایستی مقدار فیلد Identity Increment را در پنجره ی Properties تنظیم نمایید.


آموزش کاربردی: ایجاد یک ستون Identity به صورت ویژوال

1. بر روی کادر موجود در زیر سرستون Column Name کلیک نمایید.
2. TeacherID را وارد آن کرده و با زدن کلید Tab به کادر مجاور بروید.
3. نوع داده ای ستون را بر روی int تنظیم نمایید.
4. در کادر پایین پنجره ی design، بخش مربوط به Identity Specification را باز کرده و بر روی (Is Identity) دابل کلیک نمایید تا مقدارش از No به Yes تغییر یابد.
5. همان طور که مشاهده می کنید مقدار پیش فرض فیلد Identity Increment و Identity Seed به صورت پیش فرض بر روی 1 تنظیم می باشد.
6. بقیه ی جدول را به صورت زیر تکمیل نمایید:


Allow Nulls
Data Type
Column Name
TeacherID
nvarchar(10)
TeacherNumber
nvarchar(25)
FirstName
nvarchar(25)
MiddleName
Unchecked
nvarchar(25)
LastName
nvarchar(40)
[Degrees]
Unchecked
nvarchar(4)
DepartmentCode
Unchecked
nvarchar(3)
Gender

7. بر روی TeacherNumber راست کلیک کرده و گزینه ی Set Primary Key را انتخاب نمایید.
8. حال با بستن Query Editor، جدول جاری را بسته و تغییرات اعمال شده را save کنید.
9. اسم جدول را Teachers انتخاب نمایید و سپس دکمه ی OK را کلیک کنید.
10. در پنجره ی Object Explorer، بر روی Tables راست کلیک کرده و گزینه ی New Table... را انتخاب نمایید.
11. پس از انتخاب جدول، در پنجره ی Properties، بر روی Schema کلیک نموده و سپس Academics را انتخاب کنید.
12. بر روی کادر موجود در زیر سرستون Column Name کلیک نمایید و SemesterID را به عنوان اسم جدول درج کنید. کلید Tab را بزنید.
13. اکنون بر روی فیلد SemesterID راست کلیک کرده و با انتخاب گزینه ی Set Primary Key آن را به عنوان کلید اصلی جدول انتخاب نمایید.
14. نوع داده ای ستون ذکر شده را بر روی int تنظیم کنید.
15. حال در زبانه ی Column Properties، بخش Identity Specification را با کلیک بر روی آن باز نمایید. با دابل کلیک بر روی فیلد (Is Identity) مقدارش را به Yes تغییر دهید.
16. Identity Increment به طور پیش فرض بر روی مقدار 1 تنظیم می باشد. مقدار فیلد Identity Seed را بر روی 101 تنظیم کنید.


آموزش SQL

17. باقی جدول جاری را به ترتیب زیر تکمیل نمایید:


Allow Nulls
Data Type
Column Name
SemesterID
Unchecked
nvarchar(30)
Semester
Unchecked
date
SemesterStart
Unchecked
date
SemesterEnd
Unchecked
date
Session1Start
Unchecked
date
Session1End
Unchecked
date
Session2Start
Unchecked
date
Session2End
Unchecked
date
OnlineStart
Unchecked
date
OnlineEnd

18. برای بستن جدول و ذخیره ی اطلاعات آن، پنجره ی Query Editor را ببندید.
19. اسم جدول را Semesters انتخاب کنید.
20. کد موجود در پنجره ی Query Editor را با زدن کلیدهای Ctrl + A انتخاب کنید. سپس دستورات زیر را جایگزین آن ها نمایید:


USE University6;
GO
CREATE TABLE Academics.UndergraduateSchedules
(
    UndergraduateScheduleID int not null,
    SemesterID 	  int,
    StartDate  	  date,
    EndDate       date,
    StartTime  	  time,
    EndTime 	  time,
    Weekdays 	  nvarchar(32),
    TeacherNumber nvarchar(10),
    CourseCode 	  nvarchar(8),
    CourseDeliveryTypeCode nvarchar(1),
    LocationCode  nvarchar(5) not null,
    RoomNumber    nvarchar(40)
);
GO

21. برای اجرای مجموعه دستورات، کلید F5 را فشار دهید.


ایجاد ستون Identity با کدنویسی

برای ایجاد یک ستون Identity با استفاده از دستورات SQL، می بایست پس از اسم ستون و تعیین نوع داده ای آن، کلید واژه ی identity و به دنبال آن پرانتز را درج نمایید. داخل پرانتز، مقدار اولیه (seed value)، ویرگول و سپس واحد افزایش (increment value) را مشخص نمایید.


مثال:
CREATE TABLE StoreItems(
    ItemID int IDENTITY(1, 1) NOT NULL, 
    Category nvarchar(50),
    [Item Name] nvarchar(100) NOT NULL,
    Size varchar(20),
    [Unit Price] money);
GO

آموزش کاربردی

1. داخل Query Editor کلیک نمایید و کلیدهای Ctrl + A را بزنید تا تمامی دستورات جاری انتخاب شوند.
2. کد زیر را جایگزین دستورات نمایید:


USE University6;
GO
CREATE TABLE Academics.UndergraduateMajors
(
    MajorID int identity(1001, 1) not null,
    Major nvarchar(60) unique,
    Dean nvarchar(8) not null
);
GO

3. جهت اجرای کد، F5 را فشار دهید.


درج داده در جدول با ستون Identity

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


مثال:
USE Exercise;
GO
CREATE TABLE StoreItems
(
	ItemID int identity(1, 1) NOT NULL, 
	Category nvarchar(50),
	[Item Name] nvarchar(100) NOT NULL,
	Size nvarchar(20),
	[Unit Price] money
);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Men', N'Simplicity Leather Dress Oxfords', N'9', 65.85);
GO

آموزش SQL

اگر مقداری داخل ستون identity وارد کنید، قطعا با خطا مواجه خواهید شد:


آموزش SQL

آموزش کاربردی: ایجاد رکورد Identity

1. در Object Explorer، بر روی Tables راست کلیک کرده و گزینه ی Refresh را انتخاب نمایید.
2. حال گره Tables را باز کنید. بر روی Academics.Teachers راست کلیک کرده و گزینه ی Edit Top 200 Rows را انتخاب نمایید.
3. بر روی کادر موجود در زیر سرستون TeacherNumber کلیک کرده و مقدار 293804 را وارد نمایید.
4. جدول را با مقادیر زیر پر کنید:


Gender
Department Code
[Degrees]
Last Name
Middle Name
First Name
Teacher Number
M
WRTG
MA, PhD
Martins
Stephen
293804
F
LNGS
MS, MA
Yoder
Donna
603925
M
EDUC
BA, MA
Palmer
Justin
David
979384
M
HSGE
MA, MS
Fisher
Donald
283029

5. جدول را ببندید.
6. در Object Explorer، بر روی Academics.Semesters راست کلیک کرده و گزینه ی Edit Top 200 Rows را انتخاب نمایید.
7. جدول مورد نظر را با مقادیر زیر کامل کنید:


Online End
Online Start
Session2 End
Session2 Start
Session1 End
Session1 Start
Semester End
Semester Start
Semester
03/18/2012
01/09/2012
04/29/2012
02/27/2012
02/19/2012
01/09/2012
04/29/2012
01/09/2012
SPRING 2012
07/08/2012
05/14/2012
08/19/2012
07/16/2012
07/08/2012
05/14/2012
08/19/2012
05/14/2012
SUMMER 2012
10/14/2012
09/20/2012
12/16/2012
10/22/2012
10/14/2012
09/17/2012
12/16/2012
09/17/2012
FALL 2012

8. جدول را ببندید.
9. داخل Query Editor کلیک کرده و با زدن کلیدهای Ctrl + A تمامی دستورات را انتخاب نمایید.
10. دستورات زیر را جایگزین کد جاری نمایید:


USE University6;
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, DepartmentCode, Gender)
VALUES(N'820384', N'Marianne', N'Oslin', N'EDUC', N'F');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, MiddleName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'160205', N'Steve', N'Alxeander', N'Rosner', N'MA, PhD', N'CMSC', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'520203', N'Anne',    N'Wine',       N'MS, PhD',     N'MATH', N'F'),
      (N'297940', N'Thomas',  N'Phillips',   N'BS, MS, PhD', N'MATH', N'M'),
      (N'640840', N'Maryam',  N'Whittaker',  N'MA, MS, PhD', N'MATH', N'F'),
      (N'339429', N'Lisa',    N'Williamson', N'PhD',         N'EBCM', N'F'),
      (N'249382', N'Johanna', N'Possemato',  N'PhD',         N'GVPS', N'F');
GO
INSERT INTO Academics.UndergraduateMajors(Major, Dean)
VALUES(N'English',      N'161138'),
      (N'Linguistics',  N'161138'),
      (N'History',      N'247591'),
      (N'Geography',    N'247591'),
      (N'Finance',      N'697300'),
      (N'Sociology',    N'640207'),
      (N'Psychology',   N'640207'),
      (N'Economics',    N'908047'),
      (N'Marketing',    N'908047'),
      (N'Statistics',   N'828347'),
      (N'Accounting',   N'697300'),
      (N'Gerontology',  N'640207'),
      (N'Biology',      N'605924'),
      (N'Chemistry',    N'274039'),
      (N'Anthropology', N'247591'),
      (N'Political Science',	     N'247591'),
      (N'Criminal Justice and Law Enforcement', N'409260'),
      (N'Emergency Management',	     N'400384'),
      (N'Business Administration',   N'908047'),
      (N'Human Resource Management', N'400384'),
      (N'Computer Science',                 N'582007'),
      (N'Computer Networks and Security',   N'927486'),
      (N'Information Systems Management',   N'927486'),
      (N'Computer and Information Science', N'927486'),
      (N'Health Care Management and Policy', N'927486');
GO
INSERT INTO Academics.Semesters(Semester, SemesterStart, SemesterEnd, Session1Start, Session1End, 
				Session2Start, Session2End, OnlineStart, OnlineEnd)
VALUES(N'SPRING 2013', N'20130107', N'20130428', N'20130107', N'20130217', N'20130225', N'20130428', N'20130107', N'20130317'),
      (N'SUMMER 2013', N'20130513', N'20130818', N'20130513', N'20130707', N'20130715', N'20130818', N'20130513', N'20130707'),
      (N'FALL 2013',   N'20130916', N'20131215', N'20130916', N'20131103', N'20131021', N'20131215', N'20130919', N'20131013');
GO

11. برای اجرای مجموعه دستورات، کلید F5 را بزنید.
12. تب مجاور (SQLQuery1.sql) را باز کرده و کد داخل آن را انتخاب کنید.
13. دستورات زیر را در آن درج کنید:


SELECT Instructors.TeacherNumber [Teacher #],
       Instructors.FirstName [First Name],
       LEFT(Instructors.MiddleName, 1) MI,
       Instructors.LastName [Last Name],
       Instructors.[Degrees],
       Instructors.DepartmentCode [Dept Code],
       CASE Instructors.Gender
		WHEN N'M' THEN N'Male'
		WHEN N'F' THEN N'Female'
		ELSE N'Unknomn'
       END Gender
FROM Academics.Teachers Instructors
ORDER BY Instructors.LastName;
GO

14. داخل پنجره راست کلیک کرده و گزینه ی Execute SQL را انتخاب نمایید.
15. کد موجود در پنجره ی Query Editor را به صورت زیر ویرایش نمایید:


SELECT sems.Semester,
       FORMAT(sems.SemesterStart, N'yyyy') Year,
       FORMAT(sems.SemesterStart, N'MMMM') "Semester spans from",
       FORMAT(sems.SemesterEnd, N'MMMM') "Semester spans to",
       FORMAT(sems.SemesterStart, N'D') "Start of Semester",
       FORMAT(sems.SemesterEnd, N'D') "End of Semester",
       FORMAT(sems.Session1Start, N'D') "Start of Session 1", 
       FORMAT(sems.Session1End, N'D') "End of Session 1", 
       FORMAT(sems.Session2Start, N'D') "Start of Session 2", 
       FORMAT(sems.Session2End, N'D') "End of Session 2", 
       FORMAT(sems.OnlineStart, N'D') "End of Online Sessions"
FROM Academics.Semesters sems
ORDER BY sems.SemesterStart;
GO

16. داخل پنجره راست کلیک نموده و برای اجرای کوئری گزینه ی Execute SQL را انتخاب کنید.


درج مقدار در ستون Identity

برای فراهم آوردن امکان درج مقدار در ستون Identity، بایستی دستور SET IDENTITY_INSERT را به صورت زیر فراخوانی نمایید:


SET IDENTITY_INSERT [ database_name.[schema_name].] table { ON | OFF }

database_name، اسم پایگاه داده مالک جدول می باشد. در صورتی که دستور USE را قبل دستور ذکر شده بکار برده اید، می توانید این پارامتر را حذف کنید. schema_name نیز نام schema ای که است که پایگاه داده مورد نظر متعلق به آن می باشد. المان table اسم جدولی است که ستون identity به آن تعلق دارد. پس از ذکر اسم جدول، flag را بر روی ON تنظیم نمایید تا اجازه ی ورود مقدار در ستون Identity به شما داده شود.
پس از تنظیم flag در دستور فوق بر روی مقدار ON، لازم است مقدار ستون Identity را لیست مقادیر پس از اسم جدول، در دستور INSERT یا INSERT INTO ذکر کنید.


مثال:
USE Exercise1;
GO
SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(10, N'Girls', N'Girls 2-6X Short-Sleeved Mesh Polo Shirt', N'2T', 18.95);
GO

این بار در زمان درج داده دیگر با خطا مواجه نخواهید شد.
می توان پس از تنظیم IDENTITY_INSERT بر روی ON، آن را به راحتی به مقدار پیش فرض (OFF) برگردانید و سپس یک رکورد بدون مقداری برای درج در ستون identity به جدول اضافه کنید.


مثال:
USE Exercise1;
GO
SET IDENTITY_INSERT StoreItems ON;
GO
INSERT INTO StoreItems(ItemID, Category, [Item Name], Size, [Unit Price])
VALUES(50, N'Men', N'Simplicity Leather Dress Oxfords', N'10.5', 65.85);
GO
SET IDENTITY_INSERT StoreItems OFF;
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Fiona High Heel Boot', N'6.50', 295.00);
GO
INSERT INTO StoreItems(Category, [Item Name], Size, [Unit Price])
VALUES(N'Women', N'Saddle East/West Leather Hobo Bag', N'Medium', 345.50);
GO

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


انتخاب و استخراج مقادیر ستون Identity

همان طور که می دانید، برای بازیابی مقادیر یک ستون، می بایست دستور SELECT را درج نموده و بلافاصله پس از آن اسم ستون را ذکر کنید. در صورتی که جدول دارای ستون Identity باشد، می توان با بهره گیری از پرچم $IDENTITY مقادیر ستون ذکر شده را واکشی کرد:


USE Exercise;
GO
SELECT $IDENTITY FROM StoreItems;
GO

  • 1359
  •    1076
  • تاریخ ارسال :   1394/12/12

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .