ДЕРЕКТЕР БАЗАЛАРЫНЫҢ ЖҮЙЕСІ

Коммерциялық емес акционерлік қоғам

Алматы энергетика және байланыс университеті

Компьютерлік технологиялар кафедрасы

ДЕРЕКТЕР БАЗАЛАРЫНЫҢ ЖҮЙЕСІ

5В070400 - Есептеу техникасы және бағдарламалық қамтамасыз ету

және 5В070300 – Ақпараттық жүйелер мамандықтары бойынша зертханалық жұмыстарды орындауға арналған әдістемелік нұсқаулар

Алматы 2013

Құрастырушылар: А. А. Аманбаев, Е.Г. Сатимова және Н. Т. Карымсакова. Деректер базасының жүйесі. 5В070400 – «Есептеу техникасы және бағдарламалық қамтамасыз ету» және 5В070300 – «Ақпараттық жүйелер» мамандықтары бойынша зертханалық жұмыстарды орындауға арналған әдістемелік нұсқаулар. – Алматы: АЭжБУ, 2013. – 47 б.

Әдістемелік нұсқаулар «Деректер қорларын жобалау және қорғау», «Деректер қорларын жобалау» және «Деректер базасының жүйесі» пәнінен зертханалық жұмыстарды орындауға арналған. Әдістемелік нұсқауларда Microsoft SQL Server 2008 деректер базасын басқару жүйесінде, зертханалық жұмыстарға сәйкес әртүрлі мысалдар қалай шешілетіні көрсетілген.

Әрбір зертханалық жұмыс бойынша қосымшаның программалық листингі көрсетілген және Microsoft SQL Server 2008 деректер базасын басқару жүйесін оқып үйренуге арналған.

Без. 15, әдебиет көрсеткіші – 10 атау.

Пікір беруші: аға оқытушы Темырканова Э.К.

“Алматы энергетика және байланыс университеті” коммерциялық емес акционерлік қоғамның 2011 жылға арналған басылым бойынша шығарылады.

2011 ж. Жинақтық жоспары, реті 253

Мазмұны

Кіріспе	4
1 Зертханалық жұмыс. Деректер базасы моделін ДББЖ MS SQL Server ортасында жобалау және іске қосу	5
1.1 Зертханалық жұмыс тапсырмалары	5
1.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	5
1.2.1 Microsoft SQL SERVER 2008 орнату және баптау	5
1.2.2 ДББЖ MS SQL Server ортасы туралы жалпы мәліметтер	7
1.2.3 Деректер базасы мен кестелерді құру	7
1.2.4 Құрылымның өзгеруі және кестелерді жою	11
2 Зертханалық жұмыс. Деректермен манипуляция	12
2.1 Зертханалық жұмысқа тапсырмалар	12
2.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	12
2.2.1 Деректерді манипуляциялау командалары	12
3 Зертханалық жұмыс. Деректер базасына арналған қарапайым сұраныстар	16
3.1 Зертханалық жұмысқа тапсырмалар	16
3.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	16
3.2.1 Ақпараттарды кестеден алу	16
3.2.2 Стандартты функцияларды қолдану	26
4 Зертханалық жұмыс. Күрделі және динамикалық сұраныстарды құру	30
4.1 Зертханалық жұмысқа тапсырмалар	30
4.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	30
4.2.1 Сұраныстарда кестелерді біріктіру	30
4.2.2 Сұранысшаларды қолдану. Байланысқан сұранысшалар	31
4.2.3 Динамикалық сұраныстарды сақталынатын процедура көмегімен құру	34
5 Зертханалық жұмыс. Көріністер	37
5.1 Зертханалық жұмысқа тапсырмалар	38
5.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	38
5.2.1 Көріністер туралы жалпы ақпарат	38
5.2.2 Бірнеше кесте негізінде көрініс	39
5.2.3 Көріністер базасындағы көрініс	41
6 Зертханалық жұмыс. Функциялар мен триггерлер	42
6.1 Зертханалық жұмысқа тапсырмалар	42
6.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар	43
6.2.1 Функциялар	43
6.2.2 Триггерлер	44
Әдебиеттер тізімі	47

Кіріспе

"Деректер базасының жүйесі" курсының мақсаты қандай да бір ДҚБЖ-ның ерекшеліктерін зерттеу емес, деректер базасын құру және олармен жұмыс істеу технологиясын оқып үйрену болып табылады. Ақпараттық жүйелерді жобалау жобаның мақсатын анықтаудан басталады.

Әдістемелік нұсқаудың мақсаты, күрделілігі әр-түрлі деңгейдегі деректер базасын Microsoft SQL Server 2008 ортасында жобалау мен құрудың негізгі принциптерін меңгеруге арналған зертханалық жұмыстар енгізілген.

Зертханалық жұмыстың орындалу сатылары: теориялық бөлімді өңдеу, жұмыс тапсырмасын орындау, есеп беруді құру және оны оқытушыға қорғау. Жұмыс тапсырмасы қарастырылатын тақырыпқа байланысты жұмыстардан тұрады. Тәжирбиелік тапсырмаларды орындау студенттерге білім мен қажетті тәжірибе алуға мүмкіндік береді. Әдістемелік нұсқауларда Microsoft SQL Server 2008 деректер базасын басқару жүйесінде, зертханалық жұмыстарға сәйкес әр-түрлі мысалдар қалай шешілетіні көрсетілген. Студенттерге берілген зертханалық жұмыстарды орындау үшін осы мысалдарды қолдануға болады. Есеп беруде студент өзі таңдаған дистрибутивте зертханалық жұмыстардың орындалуын көрсетуге міндетті.

Әрбір зертханалық жұмыстың орындалуы есеп беруді дайындаумен аяқталуы тиіс. Есеп берулер АЭжБУ фирмалық стандартына сәйкес дайындалуы және жасалған жұмыс бойынша 20-дан кем емес принтскриннан тұруы тиіс:

- титулды бет;

- жұмыстың мақсаты мен тапсырмасы;

- мазмұны;

- кіріспе;

- зертханалық жұмыстың сипаттамасы;

- жұмыстың орындалуына арналған тапсырма;

- бақылау сұрақтарына жауаптар (теориялық дайындықтың нәтижелері);

- жасалған жұмыс бойынша нәтижелер (жұмыс тапсырмасының әрбір пункті бойынша принтскриндер);

- өз сөзімен жазылған қорытынды;

- қорытынды;

- пайдаланылған әдебиеттер тізімі.

1 Зертханалық жұмыс. Деректер базасы моделін ДББЖ MS SQL Server ортасында жобалау және іске қосу

Жұмыстың мақсаты:

- ДББЖ Microsoft SQL SERVER 2008-ді орнату және баптау;

- ДББЖ MS SQL Server 2008 ортасында деректер базасын құру;

- ДББЖ MS SQL Server 2008 ортасында деректер базасының кестелерін құруды машықтау.

1.1 Зертханалық жұмыс тапсырмалары

ДББЖ MS SQL Server 2008 ортасында жобаланған модельдің бөлігін жүзеге асырыңыз:

«Оқытушылар», «Кафедралар», «Топтар» және «Студенттер» кестелерін құрыңыз.

Зертханалық жұмыста қарастырылатын деректер базасының объектілеріне ағылшын тіліндегі атаулар қолданылады. Бұл пайдаланылатын бағдарламалық қамтаманың талаптарымен байланысты. Сондықтан төменде келтірілген мысалдарда деректер базасы “Education” деп аталсын. SQL Server-дің кез келген объектісін құру үшін қандай да бір команданың орындалуына негізделген бірнеше әдістерді қолдануға болады.

Қолданушыларға арналған базалардың орналасуы SQL нұсқасына және Program Files орналасуына байланысты өзгеріп отыруы мүмкін. Базаның орналасқан жерін бағдарламаның негізгі терезесіндегі мәзірден Қасиеттерін (Properties) таңдау арқылы анықтауға болады. Сондай-ақ қасиеттер терезесінде TransactionLog қосымшасында транзакция журналының орналасуын қараңыз. Резервті көшіру және деректер базасын қалпына келтіру үшін, керекті командалары да осы контекстті мәзірде: Барлық тапсырмалар жолы (All Tasks), Backup Databases және Restore Databases командалары арқылы таңдалып қолдануға болады.

1. MS SQL Server 2008-де lab_study деректер базасын құру

2. lab_study деректер базасында кестелерді келесі жоспармен құру:

a. Кафедралар, Оқытушылар кестелері – SQL Server Management Studio-да графикалық әдіспен

b. Топтар, Студенттер кестелері - SQL Server Management Studio-ның Database Diagrams бөлігінде

c. Пәндер, Оқу жоспары, Үлгерім кестелері - Object Explorer-де скрипттар арқылы

1.2 Зертханалық жұмысты орындауға арналған әдістемелік нұсқаулар

1.2.1 Microsoft SQL SERVER 2008 орнату және баптау.

Құрылу тарихы. SQL Server сериясының бастауы және оның негізі SQL сұраныстар тілі болып табылады. Бұл тілді 1970 жылдардың басында IBM компаниясы жазып шығарған. Алғашқыда ол SEQVEL (Structured English Query Language) деген атауға ие болды. SQL Server-де қолданылатын SQL тілінің негізін T-SQL (Transact – SQL) тілі құрады.

80 жылдардың басында IBM фирмасы және оның мердігерлері Microsoft пен Sybase желілік ДББЖ-ның алғашқы нұсқасын жасап шығарады. Ол SQL Server нұсқа 1.0 деп аталды және IBM OS/2 операционды жүйелеріне арналды. Осыдан кейін бұл операционды жүйеге арналып тағы SQL Server-дің 3 нұсқасы шығарылды. 80-жылдардың ортасында Microsoft және Sybase компаниясы IBM фирмасынан бөлініп шығады, сондай-ақ Microsoft өзінің Windows операционды жүйесімен және Sybase компаниясымен бірге SQL Server-ді дамыту жұмысын бастайды.

90-жылдардың ортасында (әсіресе 1995ж) Microsoft Windows NT операционды жүйесін жасап шығарады және де Sybase компаниясымен бірге 4.1 нұсқадағы Windows-қа арналған SQL Server-дің бірінші нұсқасын жасап шығарады.

Осыдан кейін Sybase компаниясы Microsoft-пен қарым-қатынасын үзеді де, Microsoft компаниясы Microsoft SQL Server 6.0-ді жасап шығарады. Берілген нұсқа Windows NT, 95 және 98 операционды жүйелерінде жұмыс істеуге арналған болатын. 1999 ж. Microsoft SQL Server 7.0 нұсқасы жарияланады. Ол әлемдегі ең атақты серверлі ДББЖ-нің біріне айналады. 2000 ж. Micrsoft SQL Server 2000 8-ші нұсқасы енгізілді. 2005 жылы сервердің NET технологиясына негізделген жаңа нұсқасы, ал 2008 жылы оның жақсартылған Microsoft SQL Server 2008 нұсқасы шығарылады.

Аппараттық қамтамаға талаптар

Минималды:

- процессор: Intel Pentium III 1000 МГц немесе жоғары;

- жады: 512 МБ немесе жоғары;

- қатқыл диск: 20 ГБ немесе жоғары.

Ұсынылатындар:

- процессор: Intel Pentium4 3000 МГц немесе жоғары;

- жады: 2 ГБ немесе жоғары;

- қатқыл диск: 100 ГБ немесе жоғары.

Бағдарламалық қамтамаға талаптар

Microsoft dotNET Framework 3.5 SP1, Windows Installer 4.5 және Windows PowerShell 1.0 орнату пакеттерінің болуы қажет.

Операционды жүйе: MS Windows 2003 Server SP2 (Standard Edition, Enterprise Edition, Data Center Edition), MS Windows 2003 Small Business Server SP2 (Standard Edition, Premium Edition), MS Windows 2008 Server (Standard Edition, Enterprise Edition, Data Center Edition, Web Edition).
сонымен қатар, SQL Server 2008 кейбір редакциялары, соның ішінде тегін редакцияны (Express Edition), келесі ОЖ-лерге орнатуға болады: MS Windows XP SP2 (Home Edition, Professional Edition, Media Center Edition, Tablet Edition), MS Windows Vista (Home Basic Edition, Home Premium Edition, Business Edition, Enterprise Edition, Ultimate Edition).

Ескертпе: SQL Server 2008 64-разрядты редакциялары аппаратты-бағдарламалық қамтамаға қоятын талаптары өзгеше.

MS SQL Server 2008 орнату. Берілген компьютерде администратор құқы арқылы орнатушы-бағдарламаны (Express Edition тегін нұсқасында әдетте SQLEXPRADV_x86_RUS.exe деп аталады) іске қосу. Экранда орнатуға арналған мәзір пайда болады. Мәзірдің режімдерін пайдалана отырып MS SQL Server 2008 баптап орнату.

1.2.2 ДББЖ MS SQL Server ортасы туралы жалпы мәліметтер.

Microsoft SQL Server – деректер базасын басқарудың реляционды жүйесі (ДББЖ). Реляционды деректер базасында мәліметтер кестелерде сақталады. Өзара байланысты мәліметтер кестелерге топталуы мүмкін, сонымен қатар кестелер арасында да байланыс орнатылуы мүмкін. Осыдан реляционды деген атаудың негізі шығады, ағылшын сөзі relational (туыстық, байланысқан). Қолданушылар деректерге қосымшалар арқылы қол жеткізеді, ал администраторлар серверге тікелей қол жеткізеді.

SQL Server масштабталатын деректер базасы болып табылады, яғни ол үлкен көлемдегі деректерді сақтай алады, бірнеше қолданушының жұмысын қамтамасыз ете алады.

ДББЖ SQL Server алғаш 1989 жылы пайда болды және содан бері біраз өзгеріске ұшырады. Өнімнің масштабталуы, оның тұтастығы, өнімділігі және функционалды мүмкіндіктері өзгерді.

SQL Server жүйесі клиент-серверлі жүйе түрінде немесе автономды «үстелдік» жүйе түрінде жүзеге асырылуы мүмкін. Сіз жобалайтын жүйенің типі деректер базасына бір уақытта рұқсат алуға міндетті пайдаланушылардың санына және орындалуы тиіс жұмыстың сипатына байланысты.

1.2.3 Деректер базасы мен кестелерді құру.

Физикалық түрде деректер базасы операционды жүйенің бір немесе бірнеше файлдарында орналасады. Операционды жүйенің бір файлында бірнеше деректер базасы болуы мүмкін емес. Бұл файлда кестелер мен индекстер секілді объектілер сақталады. Транзакция журналы – SQL Server транзакцияның орындалуына дейінгі және кейінгі ақпаратты жазуға қолданатын жұмыс аймақтары. Бұл ақпарат қажет болған жағдайда транзакцияны орындамауға немесе деректер базасын қалпына келтіруге пайдаланылуы мүмкін. MS SQL Server 2008 транзакциялар журналы бөлек файлда сақталады. Transact-SQL көмегімен деректер базасын құру үшін CREATE DATABASE командасы пайдаланылады.

CREATE DATABASE lab_study

ON PRIMARY

(NAME = education_data, FILENAME='C:\Program Files\Microsoft SQLServer\MSSQL\Data\education_data.mdf', size = 4, maxsize =25, filegrowth = 1 mb)

LOG ON

(NAME = education_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\education_log.ldf', size = 4, maxsize = 20,

filegrowth =1 mb);

Назар аударыңыз: базаның және журналының орналасуы - 'С:\…’ –.SQL нұсқасы мен Program Files орналасуына сәйкес өзгеруі мүмкін.

Мұндағы:

- education құрылатын деректер базасының атауы.

- ON деректер базасының ақпараты сақталатын дисктегі файлдар тізімін анықтайды.

- PRIMARY деректер базасының логикалық бастамасы және жүйелік кестелерден құралатын файлды анықтайды. Деректер базасында тек қана бір біріншілік файл (PRIMARY) болуы мүмкін. Егер бұл параметрді өткізіп жіберсе, біріншілік болып тізімдегі бірінші файл саналады.

- LOG ON транзакция журналы сақталатын дисктегі файлдар тізімін анықтайды. Егер бұл параметр анықталмаған болса, онда транзакция журналының көлемі жалпы деректер көлемінің 25% құрайтын болады.

- education_data SQL Server файлға қаратпа ретінде қолданатын логикалық атты анықтайды.

- FILENAME операционды жүйе файлының параметрлерін береді (SQL Server орнатылған серверде орналасуға тиісті файлдың аты, алғашқы және максималды деректер базасының көлемі).

Берілген деректер базасын келесі команданың көмегімен анықтаңыз:

USE lab_study;

Енді барлық келесі командалар осы деректер базасында орындалады. Кестені SQL тілінің CREATE TABLE операторы арқылы да құруға болады.

Жазылу түрі (синтаксис)

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ] )

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

] } ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ] }

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) } ]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

Ескертпелер:

1) Аргументтер мен шектеулер CREATE TABLE операторының анықтамасында қарастырылады.

2) Бағанды анықтау: кестені құру кезінде ең болмағанда бағанның бір анықталуын беру қажет.

Кесте құру кезінде нөлдік мән берудің ережелері: бағанға нөл беру мүмкіндігі нөлдік мән (NULL) берілген жағдайда оның дерек ретінде қабылданатындығын немесе қабылданбайтындығын көрсетеді. NULL — бұл нөл де бос орын да емес. Ол жазылым жасалмаған, немесе берілген мән белгісіз не қолданылмайды деген мағынаны білдіреді.

Зертханалық жұмыстың креативті бөлімі. Берілген деректер базасында жұмыс істеу үшін мына команданы қолдану қажет:

USE lab_study;

Кафедралар кестесін құру:

CREATE TABLE Chair (

Chair_ID int PRIMARY KEY,

Chair_NAME varchar(20) NOT NULL,

Chair_PHONE varchar (10) ,

Chair_CHIEF varchar (15)) ;

Оқытушылар кестесін құру:

CREATE TABLE Teacher (

Teach_ID bigint not null PRIMARY KEY ,

Teach_FAM varchar (20) NOT NULL,

Teach_IMYA varchar (10),

Teach_OTCH varchar (15),

Teach_POSITION varchar (18),

Teach_STEPEN varchar (12),

Chair_ID int NOT NULL) ;

Топтар кестесін құру:

CREATE TABLE Grup (

Grup_ID int identity (1,1) PRIMARY KEY,

Grup_NAME varchar (9) NOT NULL,

Grup_COURSE int NOT NULL ) ;

GROUP сөзі резервтелген (GROUP BY конструкциясының бөлігі) болып табылатындықтан оны атау ретінде қолдану үшін үнемі тік жақшаға [ ] алып отыру қажет немесе бұл қызметтік сөзді ДБ элементтерінің атауларында қолданбау қажет. Сол себепті біздің кестеміз GRUP деп аталады.

Студенттер кестесін құру (мұнда кілттердің сипаттамалары берілген):

CREATE TABLE People (

Peop_ID bigint,

Peop_FAM char(20) NOT NULL,

Peop_NAME char(10),

Peop_OTCH char(15),

Peop_DATE datetime,

Peop_ADDRESS char(25),

Group_ID int NOT NULL REFERENCES [Grup] (Grup_ID ),

Peop_MAN bigint,

CONSTRAINT PK_People PRIMARY KEY (Peop_ID),

CONSTRAINT FK_People_People FOREIGN KEY (Peop_MAN) REFERENCES People (Peop_ID));

Пәндер кестесін құру:

CREATE TABLE Subject (

Subj_ID int PRIMARY KEY ,

Subj_NAME varchar(20) NOT NULL,

Total_Hours int ,

Lection_Hours int,

Practice_Hours int,

Labor_Hours int) ;

Оқу жоспары кестесін құру:

CREATE TABLE Study (

Grup_ID int NOT NULL REFERENCES Grup (Grup_ID ) ON DELETE CASCADE,

Subj_ID int NOT NULL REFERENCES Subject (Subj_ID) ,

Teach_ID bigint NOT NULL REFERENCES Teacher (Teach_ID),

Kredit_count int,

Lesson_Hours int not null,

CONSTRAINT PK_Study PRIMARY KEY (Grup_ID, Subj_ID, Teach_ID, ) ) ;

Үлгерім кестесін құру:

CREATE TABLE Evolution (

Peop_ID bigint not null FOREIGN KEY REFERENCES People (Peop_ID),

Grup_ID int not null,

Subj_ID int not null,

Teach_ID bigint not null,

Kredit_count int ,

Pr_DATE datetime null,

OCENKA integer CHECK ( OCENKA in (0,1,2,3,4,5,6,7,8,9)) DEFAULT(0),

CONSTRAINT FK_Evolution_Study FOREIGN KEY (Grup_ID, Subj_ID, Teach_ID ) REFERENCES Study (Grup_ID, Subj_ID, Teach_ID ),

CONSTRAINT PK_Evolution PRIMARY KEY (Peop_ID, Grup_ID, Subj_ID, Teach_ID ));

1.2.4 Құрылымның өзгеруі және кестелерді жою.

Кестелердің құрылымын ALTER TABLE командасымен өзгертуге болады.

Өрістерді қосу. Chair кестесіне Chair_Cab өрісін қосу:

ALTER TABLE Chair ADD Chair_Cab char(10) not null;

Кестеден өрістерді жою. Енгізілген өрісті кестеден жойыңыз:

ALTER TABLE Chair

DROP COLUMN Chair_Cab ;

People кестесіне студенттердің шәкіртақысы туралы ақпарат сақталатын өрісті қосыңыз:

ALTER TABLE People ADD Peop_STIP int ;

Шектеулерді енгізу. Егер кестеде біріншілік немесе сыртқы кілттер анықталмаған болса, мұны да ALTER TABLE көмегімен жөндеуге болады.

Chair кестесінде біріншілік кілт анықталмаған делік:

ALTER TABLE Chair

ADD CONSTRAINT PK_Chair PRIMARY KEY (Chair_ID)

Teacher (Оқытушылар) кестесінде сыртқы кілттердің бірі анықталма-ған, оны қосамыз:

ALTER TABLE Teacher

ADD CONSTRAINT PK_Teacher_Chair FOREIGN KEY (Chair_ID) REFERENCES Chair (Chair_ID);

Кестелерді жоюға арналған команда DROP.

Мысалы:

DROP Teacher

DROP командасын қолдану кезінде сақ болыңыз!

Бақылау сұрақтары.

1. SQL аббревиатурасы қандай мағына береді?

2. Клиент/сервер технологиясының, мэйнфреймді қолданатын технол-огиядан, қандай негізгі айырмашылықтары бар?

3. Клиент/сервер технологиясының аясында, дербес компьютер клиент болып табыла ма, әлде сервер ме?

4. Кестелерді құруда мәліметтердің қандай типтеріне рұқсат бар?

5. MS SQL Server 2008 бағдарламасының мәзір құралдары арқылы кестені қалай құруға болады?

6. SQL тілінің құралдары арқылы кестені қалай құруға болады?

7. SQL құралдары арқылы кестеге мәліметтер жолдарын енгізу секілді қарапайым операцияларды қалай орындайды?

8. SQL құралдары арқылы кестенің жолдарын модификациялаудың қарапайым операцияларын қалай орындайды?

2 Зертханалық жұмыс. Деректермен манипуляция

Жұмыстың мақсаты:

- студенттерге деректер базасын скрипт арқылы және ДББЖ MS SQL Server 2008 графикалық ортасы арқылы толтыруды үйрету;

- студенттерге ДББЖ MS SQL Server 2008 ортасында скрипт арқылы деректер базасының кестесінен жолдарды жоюды үйрету;

- студенттерге өрістің мәнін скрипт арқылы өзгертуді үйрету.

2.1 Зертханалық жұмысқа тапсырмалар

1. Деректер базасының барлық кестелерін толтырыңыз (INSERT командасы арқылы).

Келесі ережелерді ұстаныңыз:

а) аз байланысы бар кестелер (анықтамалар) бірінші болып толтырылады;

б) категориялық бүтіндік ережесін сақтаңыз: жолдың ешқандай кілттік атрибуты бос болмауы керек;

в) сілтемелер деңгейінде бүтіндіктің ережесін ұстаныңыз: әрбір сыртқы кілттің мәні не бос, не басқа кестенің кілтінің мәніне тең болуы тиіс.

2. People кестесінің Peop_STIP өрісін әр түрлі деректермен толтырыңыз. (UPDATE командасын қолданыңыз).

3. Өз ыңғайыңызға қарай деректер базасының кестелерін командаларын орындаңыз.

2.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар

2.2.1 Деректерді манипуляциялау командалары.

SQL Server 2008-де кестелерді толтыру келесі команданың көмегімен орындалады:

INSERT [INTO]

table_name [ ( column_list ) ]

{ VALUES

( { DEFAULT | NULL | expression } [ ,...n] )

| derived_table}

немесе

INSERT <Кесте аты> [(<Өрістер саны>)]

VALUES (<Өріс мәндері>),

мұндағы <Кесте аты> - деректер енгізілетін кесте, (<Өрістер саны>) – деректер енгізілетін өрістер тізімі, егер көрсетпесек, онда барлық өрістерді үтір арқылы толтыру деп түсініледі, (<Өріс мәндері>) – үтір арқылы жазылған өріс мәндері.

Мән ретінде Default константасын беруге болады, яғни үнсіз келісім бойынша берілген мән болады, немесе Select операторын қоюға болады. Бұл жерде ол формулаларды есептеу құралы ретінде қолданылады.

Мысалы: өрісінің мәндері ФАТ = Иванов, Мекенжай = Мәскеу, Мамандық коды = 5 болып табылатын «Студенттер» кестесіне мәлімет енгізу.

INSERT Студенттер (Фамилия, Аты, Тегі, Адрес, [Мамандық коды])

VALUES (‘Иванов’, ‘Антон’, ‘Антонович’, ‘Астана’, 5)

Ескерту: аргументтер мен шектеулер INSERT операторының анықтамасында қарастырылады (CTRL+ALT+F3).

Ескертпе:

- кестедегі деректерді ауыстыру үшін INSERT инструкциясының көмегімен жаңа деректерді енгізудің алдында, бар деректерді жоюға арналған DELETE инструкциясын пайдаланамыз. Берілген жолдағы бағанның мәнін өзгерту үшін UPDATE инструкциясын пайдаланыңыз;

- егер column_list тасталынып кеткен болса, кестенің барлық бағандары өсу ретімен көрсетілген бағандарды енгізу тізімі айқын емес болады;

- column_list-те кестенің бағанын бір рет қана анықтауға болады. Егер Column_list-те баған болмаса, SQL Server мәнді бағанның анықталуына негізделіп беруі тиіс; қарсы жағдайда жолды жүктеу мүмкін емес болады.

Идентификаторлар бағанына айқын мәндер енгізуде бағандар тізімін және VALUES тізімін пайдалану қажет. Егер VALUES тізімінде мәндер кестенің бағандарына сәйкес орналаспаған болса немесе кестенің әрбір бағанына сәйкес мән болмаса, column_list-ті бағанды айқын көрсетуге пайдалану қажет.

Егер бағанның мәні ретінде DEFAULT пайдаланылатын болса, онда бұл баған үшін стандартты мән енгізіледі. Егер бағанның стандартты мәндері болмаса және бағанда Null мәні болуы мүмкін болса, онда NULL мәні енгізіледі. DEFAULT мәнін идентификаторлар бағанына қоюға болмайды.

Кодтың мысалы:

INSERT INTO Chair (Chair_ID,Chair_NAME,Chair_PHONE, Chair_CHIEF)

VALUES (50763,'Инж. Кибернетики','123456','Хисаров Б.’);

«Оқытушылар» жазбасын құру:

INSERT INTO Teacher (Teach_FAM, Teach_IMYA, Teach_OTCH,

Teach_POSITION, Teach_STEPEN, Chair_ID,Teach_ID)

VALUES ('Ахметова', 'Галия ', 'Сериковна', ' ', ' ',50763,77512);

Кестедегі жекелеген бағандар мен жолдарды жою. Кестедегі барлық бағандарды немесе жекелеген бағандарды жоюға болады. Бұл мына команда арқылы жүзеге асырылады:

DELETE <Кесте аты>

[WHERE <Шарт>] ,

мұндағы <Шарт> - жойылатын жазбалардың сәйкес келетін шарты, егер шарт көрсетілмесе кестенің барлық жолдары жойылады. Шарт көрсетілген болса, шартқа сәйкес өріс жазбалары өшіріледі.

Мысалы: «Студенттер» кестесінен Адрес = Астана сәйкес келетін жазбаларды жою.

DELETE Студенттер WHERE Адрес = ‘Астана’

Кестедегі деректерді өзгерту. Бұл үшін келесі команда қолданылады:

UPDATE <Кесте аты>

SET

< Өріс аты 1> = <Өрнек1>,[<Өріс аты 2> = <Өрнек2>,]

…

[WHERE <Шарт>],

мұндағы <Өріс аты1>, < Өріс аты2> - өзгертілетін өріс аттары, <Өрнек1>, <Өрнек2> - нақты мәндер немесе NULL, болмаса SELECT операторлары. Мұнда SELECT функция ретінде пайдаланылады.

<Шарт> - өрістері өзгертіліп жатқан жазбаның сәйкес келу шарты.

Мысалы: «Студенттер» кестесінде Иванов атты студенттің мекенжайын Астанадан Таразға, ал мамандық кодын 5-тен 3-ке ауыстыру қажет.

UPDATE Студенттер

SET Адрес = ‘Тараз’,

[Мамандық коды] = 3

WHERE ФАТ = ‘Иванов’

Ескертпе: өрнек ретінде математикалық формулаларды қолдануға болады.

Мысалы: SET [Орташа балл]= (Баға1+ Баға2+ Баға3)/3) «Орташа балл» өрісін «Баға1», «Баға2» және «Баға3» өрістерінің орташасы ретінде есептейді. Ол үшін «Баға1», «Баға2» және «Баға3» өрістері бар болуы тиіс және «Орташа балл» өрісінің деректер типі жылжымалы нүктемен болуы керек (Мысалы Real).

Ескертпе: егер кестедегі барлық деректерді жойып, бірақ оның құрылымын сақтап қалу керек болса, TRUNCATE TABLE <Кесте аты> командасын пайдаланыңыз.

Өрістің мәнін өзгерту. Кесте бағандарының ағымдағы мәндерін өзгерту үшін UPDATE инструкциясы пайдаланылады.

Синтаксис

UPDATE table_name

[ WITH ( < table_hint > ) ]

SET { column_name = { expression | DEFAULT | NULL }} [ ,...n ]

[ WHERE < search_condition > ]

Ескерту: аргументтер мен шектеулер UPDATE операторының анықтамасында қарастырылады.

Ескертпе:

- идентификаторлар бағандарын жаңартуға болмайды ;

- WHERE қызметтік сөзі көрсетілмесе, кестедегі барлық бағандар жаңартылады;

- жолдарды жаңартудың алдында WHERE қызметтік сөзінде кестенің әрбір жолы үшін іздеу шартының бағалауы жүргізіледі;

- егер жолдарды жаңарту ережені немесе шектеуді, баған үшін NULL мәнінің баптауын бұзатын болса, сондай-ақ жаңадан енгізілетін мән сәйкес келмейтін деректер типіне жататын болса, инструкциядан қайту орындалады және жазбаларды жаңартусыз қате қайтарылады;

- char барлық бағандары оң жақ шеттен белгілі бір шекараға ие;

- varchar-ға енгізілген деректерден тек қана бос орыннан тұратын жазбалардан басқа барлық тұйықталған бос орындар жойылады. Мұндай жолдар бір бос жолға дейін қысқартылады.

Мысалы: келесі мысалда реттік нөмірі 2-ге тең оқыту дәрежесін орнату арқылы оқытушылар кестесін жаңарту көрсетілген:

UPDATE Teacher

SET Teach_Stepen = 'Профессор' WHERE Teach_ID = 2;

Бақылау сұрақтары.

1. Деректерді манипуляциялаудың қандай командаларын білесіз?

2. Деректердің бүтіндігі дегеніміз не?

3. Сілтемелер деңгейінде бүтіндіктің ережесін құрыңыз.

4. Өрістің NOT NULL деген мәні нені білдіреді?

5. Егер өрісте NULL деп көрсетілген болса, бұл өріске міндетті түрде бір нәрсе енгізілуі тиіс дегенді білдіре ме?

6. Өрістің identity мәні нені білдіреді?

3 Зертханалық жұмыс. Деректер базасына арналған қарапайым сұраныстар

Жұмыстың мақсаты: студенттерге деректер базасына қарапайым сұраныстарды скрипт және ДББЖ MS SQL Server 2008 графикалық ортасы арқылы құруды үйрету.

3.1 Зертханалық жұмысқа тапсырма

Басқа кестелердегі ақпараттарды іздеуді орыңдаңыз.

1. Әліпби (алфавит) тәртібі бойынша, қызметтерін көрсете отырып оқытушылар тізімін құру қажет.

2. Менгеруші аты-жөні мен (фамилия) кафедра аттарының тізімін құрыңыз.

3. Бірінші топта оқитын фамилиялары әртүрлі студенттер тізімі (осы топта фамилиясы бірдей студенттер бар деп есептелінеді).

4. Стипендиясы 2000−нан жоғары студенттер тізімі.

5. Астана мен Қарағандыда тұратын студенттер тізімі.

6. Стипендиясы жоқ екінші топ студенттерінің тізімі.

7. Фамилиясы «А» әрпінен басталатын үшінші топ студенттері тізімі.

8. 1988 жылы дүниеге келген студенттер тізімі.

9. Үшінші топтың студенттерінің стипендияларының қосындысын есептеңіз.

10. Барлық пәндер бойынша дәрістер сағатының орташасын есептеңіз.

11. Деректер базасына қанша студент енгізілді?

12. Пәндер туралы барлық ақпараттарды шығарыңыз.

13. Алматыда тұрмайтын студенттер тізімі.

14. Мамыр айында туған студенттер тізімі.

15. Үлгерім парағындағы минималды бағалы студенттер нөмірі.

16. Үлгерім парағындағы максималды бағалы студенттер нөмірі.

17. Студенттер тізімін және олардың жасын шығарыңыз.

3.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар

3.2.1 Ақпараттарды кестеден алу.

Сұраныстар бір немесе бірнеше кестені байланыстыруға арналған, сондай-ақ олар кестеден жекелеген өрістерді таңдауға, шартқа сәйкес деректердің фильтрациясын жүргізуге мүмкіндік береді. Мұндай сұраныстарды фильтрлер деп атайды.

Сұраныстарды орындау үшін арнайы SQL (Standard Query Language) сұраныстар тілін пайдаланады.

АЖ-де сұраныстар клиенттік қосымша жағында да, сервер жағында да болуы мүмкін. Егер сұраныс клиент жағында болса, онда ол байланыс объектісінің ішіне жазылады. Бұл жағдайда клиенттің қосымша деректер файлына тәуелді емес. Деректер файлы тек қана кестелерден тұрады, сондықтан біз деректер файлын қозғамай-ақ, клиенттік қосымшаны оңай модификациялай аламыз, бірақ бұл жағдайда сұраныс серверге желі арқылы жіберіледі, ал бұл қауіпсіздік жағынан қиындықтар туғызуы мүмкін.

Егер сұраныс серверде сақталып немесе орындалатын болса, онда сұраныстың өзі ДБ компоненті болып табылады. Барлық ақпараттарды жіберу деректер файлының ішінде жүреді, яғни сервердің ішінде, клиенттік қосымша тек сұраныс нәтижесін көрсетеді. Бұл жағдайда деректерге жоғары дәрежедегі қауіпсіздік қамтамасыз етіледі, бірақ сұранысты өзгерткен жағдайда деректер файлын өзгертуге тура келеді.

Барлық сұраныстар екіге бөлінеді:

1) статикалық;

2) динамикалық.

Статикалық сұраныстардың құрылымы бағдарламамен жұмыс істеу кезінде өзгермейді, ал динамикалық сұраныстар жағдайға байланысты өзгеруі мүмкін.

Ескерту: әдетте динамикалық сұраныстар тек клиент жағынан орындалатын сұраныстардың көмегімен орындалады. Егер сервер жағынан орындалатын динамикалық сұранысты орындау қажет болса, онда сақталынатын процедураларды қолдану қажет.

Сақталынатын процедулар – сервер жағында сақталатын SQL сұраныс. Бұл сұраныс SQL коды ішіне жазылатын параметрлерге ие. Сақталынатын процедураны шақырған кезде оның ішіне параметр мәндерін беру қажет.

Негізінен сұраныс немесе сақталынатын процедура не кестелердің арасында байланыс орнатады не деректердің фильтрацясын жүргізеді, кейбір SQL сұраныстар есептеулерді орындайды.

Кестелердің арасындағы байланыс жағдайында әрқашан бір кесте біріншілік, басқасы екіншілік болып табылады. Байланыстың өзі байланыс өрісі арқылы жүзеге асырылады. Байланыс өрістерінде бірдей мәндері бар жазбалар теңестіріледі. Біріншілік кесте әрқашан бірінші толтырылады, ал оның байланыс өрісі автоматтты түрде толтырылады (дерек типі – санауыш). Екіншілік кесте біріншілік кесте толтырылғаннан кейін толтырылады, оның байланыс өрісінің мәні біріншілік кестеден алынады. Байланыс өрістерінің дерек типтері бірдей болуы тиіс.

Деректер базасына сұраныс есептелетін ақпараттың диапазонын қысқартатын және оны берілген критерийлерге сай бағандар мен жолдар арқылы шектейтін таңдау операциясы болып табылады.

Transact-SQL-де деректерді таңдау үшін SELECT инструкциясы пайдаланылады. SQL нақты сұраныстарының көпшілігі кестенің барлық емес, тек белігілі бір жолдары мен бағандарын таңдауға арналған.

SELECT инструкциясы.

Деректер базасынан жолдарды алады және бір немесе бірнеше кестеден бір немесе бірнеше жолдар не бағандарды таңдауға мүмкіндік береді. Бұл сұраныстарды жіберуге қолданылатын SQL-дің негізгі құрылымдық компоненті. SELECT инструкциясы деректерді өзгертпейді, енгізбейді және жоймайды.

Синтаксис

SELECT select_list

[ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Ескерту:

SELECT инструкциясы жүйеге сұранысты сипаттайды. Сұраныстың нәтижесі ұқсас құрылымды жолдардан тұратын кесте болып табылады. SELECT инструкциясы осы нәтиже кестесінде қандай бағандар болатындығын және оларды қандай жолдар толтыратындығын анықтайды. SELECT инструкциясы сұраныстың орындалу ретін жүйеге хабарламайды; жүйенің өзі сұранысты берілген жағдайда ең оңтайлы тәсілмен орындайды. Нәтиже төменде сипатталған канондық орындау стратегиясына сай келуі тиіс. Бір ғана айырмашылық кестедегі жолдардың орналасу ретінде болуым мүмкін, бірақ басқа ретті де ORDER BY қызмет сөзімен беруге болады.

Орындау стратегиясы.

1. Кестелердің бірігуін FROM қызметтік сөзінде құрыңыз. JOIN айқын синтакисін қолдану барысында JOIN нәтижесі де айқын болады. Егер FROM қызметтік сөзінде үтір арқылы жазылған кестелердің тізімі болса, бұл кестелердің айқын емес бірігуі векторлық туынды болып табылады.

2. WHERE қызметтік сөзі бар болған жағдайда берілген шарттарды бірінші қадамнан кейін қолданған жөн және тек шартқа сай жолдарды ғана сақтау керек.

3. Егер SELECT қызметтік сөзінде бірігулер, сондай-ақ GROUP BY қызмет сөзі болмаса, жетінші қадамға өтіңіз.

4. Егер қызмет сөзі бар болса, екінші қадамнан кейін алынған жолдарды бірнеше топтарға бөліңіз, әрбір топтың барлық жолында топталу бағандарының мәндері бірдей болуы тиіс. Егер GROUP BY қызмет сөзі болмаса, барлық жолдарды бір топқа біріктіріңіз.

5. Төртінші қадамнан кейінгі алынған әр топқа HAVING қызмет сөзін қолданыңыз, егер ондай көрсетілген болса. Тек HAVING қызмет сөзіне сәйкес келетін топтар ғана қалады. Бесінші қадамнан кейін алынған әр топ үшін берілген топтағы SELECT қызмет сөзімен таңдау тізімін бағалау арқылы бір жолды құрыңыз.

6. Егер SELECT қызметтік сөзінде DISTINCT кілттік сөзі бар боса, алтыншы қадамның нәтижесінде алынған барлық қайталанатын жолдарды жойыңыз.

7. Егер ORDER BY қызметтік сөзі бар болса сұрыптауды реті көрсетілгендей орындаңыз.

SELECT _LIST

Сұраныс қайтаратын бағандарды көрсетеді.

Синтаксис

SELECT [ ALL | DISTINCT ] TOP (expression) < select_list >

< select_list > ::=

{ *

| { table_name | table_alias }.*

| { column_name | expression } [ [ AS ] column_alias ]

} [ ,...n ]

Ескерту: аргументтер мен шектеулер SELECT операторының анықтамасында қарастырылады.

Тек column_alias аргументін қарастырайық – сұраныс нәтижелерінің жиынында баған атын ауыстыруға арналған альтернативті ат. Мысалы, «quantity» бағаны үшін «Quantity», «Quantity to Date» немесе «Qty» псевдонимі көрсетілуі мүмкін. Сонымен қатар псевдонимдер өрнектер нәтижелерінің аттарын көрсету үшін қолданылады, мысалы:

SELECT AVG(Stud_STIP) AS Орташа_шәкіртақы

FROM Students

Ескертпе: column_alias-ты ORDER BY қызмет сөзінде қолдануға болады, бірақ WHERE, GROUP BY және HAVING қызмет сөздерінде қолдануға болмайды.

FROM қызмет сөзі жолдарды таңдау үшін кестелерді нұсқауға қолданылады. FROM қызмет сөзі міндетті емес болып табылады.

Ескертпе: функцияның қайтарылатын кестелік мәндері қолданыл-майды.

Синтаксис және аргументтер команданың анықтамасында қарастыры-лады.

[ FROM { < table_source > } [ ,...n ]

< table_source > ::=

table_name [ [ AS ] table_alias ]

| < joined_table >

| <derived_table> [ [ AS ] table_alias ]

< joined_table > ::=

< table_source > < join_type > < table_source > ON <search_condition >

| <table_source> CROSS JOIN <table_source>

| <left_table_source> { CROSS | OUTER } APPLY <right_table_source>

| ( < joined_table > )

< join_type > ::=

[ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN ]

left_table_source::= table_source

right_table_source::=table_source

JOIN аргументін қолданудың ерекшеліктерін қарастырайық:

CROSS JOIN екі кестенің векторлық туындысын көрсетеді.

INNER барлық сәйкес жол жұптары қайтарылғанын көрсетеді. Екі кестедегі сәйкес емес жолдарды болдырмау. Егер біріктіру типі берілмесе, үнсіздік келісімі бойынша қойылады.

LEFT [ OUTER ] сол жақтағы кестенің берілген шартқа сәйкес келмейтін барлық жолдары нәтижелік жинаққа барлық жолдарға қосымша ретінде қосылатындығын көрсетеді. Сол жақ кестенің шығыс бағандары үшін NULL мәні берілген.

RIGHT [ OUTER ] оң жақтағы кестенің берілген шартқа сәйкес келмейтін барлық жолдары нәтижелік жинаққа барлық жолдарға қосымша ретінде қосылатындығын көрсетеді. Оң жақ кестенің шығыс бағандары үшін NULL мәні берілген.

JOIN берілген кестелерді біріктіру қажеттігін көрсетеді.

ON < search_condition > бірігу негізделетін шарттарды көрсетеді. Шартты кез келген рұқсат етілген предикат көрсете алады, бірақ көбіне салыстыру операторлары мен бағандары пайдаланылады.

Ескерту:

1) Егер бір сұраныс JOIN операторымен де, APPLY кілттік сөзімен де жазыла алатын болса, JOIN қолданған сұраныс жылдамырақ болуы мүмкін.

2) Егер right_table_source тізімдегі кестелерге байланыспаған кестелерге сілтеме бар болса, онда left_table_source-тегі кестенің атын немесе псевдонимін сәйкес келтіру қажет, болмаса сыртқы FROM қызмет сөзіндегі (егер WHERE қызмет сөзінің ішкі сұранысында немесе SELECT тізімінде APPLY операторы көрсетілген болса) кестенің атын немесе лақап атын сәйкес келтіру қажет. Егер бірінші және екінші жағдайларда сәйкес келетін сілтемелер табылса, бірінші жағдайдың тиімділігі жоғарырақ болып есептеледі.

3) APPLY операторлары JOIN операторларындай артылықшылықтарға ие. Жақша болмаған жағдайда JOIN және APPLY операторларының тізбектілігі солдан оңға қарай жүргізіледі.

Қосымша мәліметтерді SQL Server электронды құжатынан қараңыз.

Мысалдар: Келесі мысал FROM қызмет сөзін қолдану туралы көп мәлімет береді.

SELECT Teach_ID, Teach_FAM, Teach_IMYA FROM Teacher

FROM қарапайым қызмет сөзін пайдалану.

SELECT Teach_ID, Teach_FAM, Teach_IMYA

FROM Teacher ORDER BY Teach_ID

CROSS JOIN пайдалану. Келесі мысалда People және Group кестелерінің қиылысу туындылары қайтарылады. Peop_ID және Group_ID жолдарының барлық мүмкін сәйкестіктері қайтарылады:

SELECT S.Peop_ID, G.Grup_Id

FROM People S

CROSS JOIN Grup G

ORDER BY S.Peop_ID, G.Grup_ID

LEFT OUTER JOIN пайдалану. Келесі мысал екі кестені Group_ID бағаны бойынша байланыстырады және сол жақтағы кестеден сәйкестіктері жоқ жолдарды сақтайды. Group кестесі People кестесімен Group_ID бағаны бойынша салыстырылады. Нәтижесінде барлық топтар көрсетіледі (студенттері бары да, жоғы да):

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

LEFT OUTER JOIN Grup G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

INNER JOIN пайдалану. Келесі мысалда тек топқа жазылған студенттердің ғана аттары қайтарылады:

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

INNER JOIN Grup G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

RIGHT OUTER JOIN пайдалану. Келесі мысал екі кестені Group_ID бағаны бойынша байланыстырады және оң жақтағы кестеден сәйкестіктері жоқ жолдарды сақтайды. Group кестесі People кестесімен Group_ID бағаны бойынша салыстырылады. Нәтижесінде барлық топтар көрсетіледі (студенттері бары да, жоғы да):

SELECT S.Peop_FAM ,S.Peop_NAME, G.GRUP_NAME

FROM People S

RIGHT OUTER JOIN Group G ON S.Grup_ID = G.Grup_ID

ORDER BY G.Grup_NAME

DISTINCT - SELECT командасында екеулік мәндерді алып тастауды қамтамасыз ететін аргумент. Мысал үшін, қазіргі уақытта қандай студенттердің тым болмағанда бір бағасы бар екенін анықтау қажет делік: (бағанның өзі емес, тек студенттердің код тізімі қажет):

SELECT Peop_ID

FROM Evolution

Бұл команданы орындау барысында кодтар екі еселенеді. Дубликатсыз тізімді алу үшін былай енгізу қажет:

SELECT DISTINCT Peop_ID

FROM Evolution

DISTINCT SELECT командасында бір рет қана көрсетілуі мүмкін. Егер бірнеше бағандар таңдалатын болса, онда DISTINCT таңдалған өрістер ұқсас жолдарды қалдырып кетеді. Кейбір мәндері бірдей, кейбір мәндері әртүрлі жолдар сақталынып қалады.

Егер DISTINCT орнына ALL деп көрсететін болсақ, онда керісінше нәтиже беріледі, жолдардың екі еселенуі сақталады. Бұл да ешқандай аргумент көрсетілмейтін жағдай болғандықтан, ALL да тек түсіндіру аргументі болып табылады.

Кестенің жолдарын шектеу. Кестелер үлкейіп кету қасиетіне ие. Сізді қазіргі уақытта олардың тек кейбіреуі қызықтыратын болғандықтан, қай жолдарды таңдау керектігін анықтау үшін критерийлер орнату мүмкіндігі бар. Бұл үшін SELECT қызмет сөзінің WHERE кілттік сөзі қолданылады. Ол кестенің кез келген жолы үшін дұрыс немес бұрыс болатын шартты қоюға мүмкіндік береді. Команда кестеден тек шартты қанағаттандыратын жолдарды ғана алады. WHERE қызмет сөзінің синтаксисі:

WHERE баған_аты салыстыру_операторы мән

WHERE қызметтік сөзі бар SELECT инструкциясы сұраныстың нәтижесін қысқартады. Ол тек бір ғана жолды қайтаруы мүмкін немесе берілген критерийге сәйкес келетін жолдар табылмаса мүлде қайтармауы мүмкін.

Тегі “Николаевич” болатын барлық студенттердің аттары мен фамилияларын алу керек делік:

SELECT Peop_FAM , Stud_IMA

FROM People

WHERE Peop_OTCH = 'Николаевич'

Сұраныста WHERE қызметтік сөзі бар болғанда, SQL Server тұжырымдаманың дұрыстығын анықтау үшін кестені толығымен, әрбір жолын қарап шығады.

Ескертпе: 'Москва' типті жолдық константалар Transact-SQLде апострофпен' немесе тырнақшамен "шектеледі.

Енді WHERE қызметтік сөзінде сандық өріспен сұраныс құрастырып көрейік. Шәкіртақысы 1200 болатын барлық студенттерді таңдаймыз:

SELECT *

FROM People

WHERE Peop_STIP = 1200

Келесі мысалда WHERE қызметтік сөзін нөмірі 5 болатын топтың жалпы шәкіртақысының суммасын алуға қолдану келтірілген:

SELECT SUM(Peop_STIP) AS ' Шәкіртақы суммасы'

FROM People WHERE Group_ID=5

Шығаруды сұрыптау. Әдетте белгілі бір мәлімтеттердің реттелгені талап етіледі. Реттелу ORDER BY кілттік сөзі (үнсіз келісім бойынша өсу ретімен) арқылы жасалады. ORDER BY қызметтік сөзінің синтаксисі:

ORDER BY Баған1 | Бүтін_мән [ACS | DESC]

Мысалы, студенттердің тізімін әліпбилік ретпен шығарайық: SELECT Peop_ID, Peop_FAM

FROM People

ORDER BY Peop_FAM

Егер бағанның атынан кейін тағы бір бағанның атын көрсететін болсақ, онда екінші бағанның жолдары мәндері бойынша бірінші кестемен тең жолдары реттеледі. ORDER BY кілттік сөзінің тізімінде көрсетілген бағанды тізімдегі баған нөміріне сәйкес санмен (Бүтін_мән параметрі) алмастыруға болады:

SELECT Grup_ID, Peop_ID, Peop_FAM

FROM People

ORDER BY 1, 3 DESC

ASC(ascending) параметлері сұрыптау ретін өсу бойынша, DESC (descending) – кему реті бойынша береді.

Ескертпе:

- дерек типі text және image болып табылатын бағандарды ORDER BY қызмет сөзінде қолдануға болмайды;

- ASC таңдалған бағандардағы мәндерді сұрыптауды өсу реті бойынша, астынан үстіне қарай жүргізу керек екенін көрсетеді;

- DESC таңдалған бағандардағы мәндерді сұрыптауды кему реті бойынша, үстінен астына қарай жүргізу керек екенін көрсетеді. Нөлдік мәндер рұқсат етілген мәндердің ең төменгісі болып саналады.

Ескерту: ORDER BY өрнегінде элементтер санына шектеу жоқ.

ORDER BY қызмет сөзін UNION инструкциясымен бірге пайдаланған жағдайда сұрыпталатын бағандар SELECT бірінші инструкциясында көрсетілген бағандардың аттары немесе лақапаттары болуы тиіс. Мысалы, келесі SELECT инструкцияларының біріншісі сәтті орындалады, ал екіншісінде қате болады:

Create t1 (col1 int, col2 int);

Create t2 (col3 int, col4 int);

SELECT * from t1

UNION

SELECT * from t2

ORDER BY col1;

Берілген инструкция сәтті орындалады, себебі «col1» аты бірінші кестеге (t1) жатады.

SELECT * from t1

UNION

SELECT * from t2

ORDER BY col3;

Берілген инструкция орындалмайды, себебі «col3» аты бірінші кестеге (t1) жатпайды.

Деректерді таңдаудың шарт операциялары.

Transact-SQL күрделі шарттар арқылы жазбаларды таңдауға мүмкіндік береді және ол үшін қатынас операторларын, логикалық операторларды және арнайы операторларды пайдаланады.

Қатынас операторлары:

1) = Тең

2) > Артық

3) < Кем

4) >= Артық не тең

5) <= Кем не тең

6) <> Тең емес

Олар символдық мәндер және дата үшін стандартты мәндерге ие емес. Символдық мәндер олардың кодтарының терминдерінде салыстырылады.

Шәкіртақысы 1200-ден жоғары студенттердің тізімін көргіміз келді делік:

SELECT *

FROM People

WHERE Peop_STIP > 1200

Логикалық операторлар:

- AND логикалық "ЖӘНЕ"

- OR логикалық "НЕМЕСЕ"

- NOT логикалық келіспеу

AND операторы екі логикалық мәнді салыстырып, егер екі мән шындық (яғни TRUE), болған жағдайда TRUE (ақиқат) қайтарады, басқа жағдайларда - FALSE (жалған). OR операторы аргументтердің тым болмаса біреуі TRUE болса, TRUE қайтарады. NOT операторы егер аргумент FALSE болса, TRUE қайтарады және керісінше.

Логикалық операторларды қолдану SELECT командасының мүмкіндіктерін айтарлықтай көбейтеді.

AND логикалық операторы (логикалық ЖӘНЕ) егер сұраныс нәтижесінде қайтарылатын жолдар WHERE қызмет сөзінде берілген шарттарды қанағаттандыратын болса, орынды болады:

SELECT * FROM Students

WHERE Stud_IMYA = 'Алексей' AND

Stud_ADDRESS = 'Таугуль'

Егер кестеде Stud_IMA бағаны 'Алексей'-ге тең екі жол бар болатын болса, онда AND логикалық операторы Stud_ADDRESS бағаны «Таугуль»-ге тең жолды таңдауға мүмкіндік береді (яғни Таугульде тұратын Алексейді).

OR операторының көмегімен бір бағаннан бірнеше мән таңдауға болады :

SELECT * FROM People WHERE Peop_NAME = 'Алексей'

AND Peop_ADDRESS = 'Таугуль'

NOT операторы салыстыру операцияларында баған алдында көрсетіледі:

SELECT * FROM People

WHERE NOT Peop_ADDRESS = 'Таугуль'

Бұл сұраныс Students кестесінен мекенжайы Таугуль емес болатын барлық студенттер туралы ақпарат сақталған жолдарды алуға мүмкіндік береді.

Өрнектерді топтау үшін Transact-SQL дөңгелек жақшаларды ( ) қолдануға мүмкіндік береді.

Мысалы:

SELECT * FROM People

WHERE NOT (Peop_IMYA = 'Алексей' OR

Peop_IMYA = 'Николай')

Арнайы операторлар: IN, BETWEEN, LIKE, IS NULL. IN операторы өрістің мәні кіруі тиіс мәндердің тізімін анықтайды. Мысалы, егер есімі 'Алексей' және 'Николай' болатын барлық студенттерді табу керек болса, мынадай сұранысты пайдаланамыз:

SELECT *

FROM People

WHERE Peop_IMYA IN ('Алексей' , 'Николай')

IN операторы үшін мәндер жиыны дөңгелек жақшаға алынады, мәндер үтірмен бөлінеді.

BETWEEN операторы IN операторына ұқсас. BETWEEN−нің мүмкін мәндер тізімінен айырмашылығы, ол мәндер аралығын анықтайды. Сұраныста алдымен BETWEEN сөзін, кейін бастапқы мәнін, AND кілттік сөзін және соңғы мәнін көрсету қажет. Бірінші мәні екіншісінен кіші болуы қажет. Келесі сұраныс бағасы 3 пен 5−тің аралығындағы студенттерді таңдайды:

SELECT *

FROM Evolution

WHERE Ocenka BETWEEN 3 AND 5

LIKE операторы жолшаны (подстроки) табу үшін өзі бірге қолданылатын символдық өрістерге ғана қолданылады. Яғни, ол жолдың бөлігінің шартпен сәйкестігін көру үшін символдың өрісін іздейді. Шарт ретінде ол арнайы символдарды қолданады:

- '_' символы кезкелген бірлік символды ауыстыра алады. Мысалы, 'к_т' 'кот' немесе 'кит' сөздеріне сәйкес келе алады, бірақ 'крот'-қа емес;

- % процент белгісі тізбектелген кезкелген санды символды ауыстыра алады. Мысалы, '%м%р' 'компьютер' немесе 'омар' сөздеріне сәйкес келеді.

Аттары 'О' әрпінен басталатын студенттерді таңдайық:

SELECT *

FROM People

WHERE Peop_NAME LIKE 'О%'

3.2.2 Стандартты функцияларды қолдану.

Математикалық функциялар:

-ABC (numeric) – сан модулі;

- ACOS/ASIN/ATAN (Float) – арккосинус, арксинус, арктангенс радианда;

- COS/SIN/TAN/COT (Float) – косинус, синус, тангенс, котангенс;

- CEILING (Numeric) – ең кіші бүтін сан, жақшадағы параметрден үлкен немесе оған тең;

- DEGREES (Numeric) – радианды градусқа айналдырады;

- EXP(Float) – экспонента, е^х;

- FLOOR(Numeric) – ең кіші бүтін сан, numeric өрнегіне тең немесе кіші;

- LOG(Float) – натурал логарифм ln;

- LOG10(Float) – ондық логарифм log₁₀;

- PI () –пи саны;

- POWER (Numeric,y) –Numeric өрнегін у дәрежесіне шығарады;

- RADIANS (Numeric) – градусты радианға айналдырады;

- RAND () – нөл мен бірдің аралығында орналасқан Float деректер типіндегі кездейсоқ сандарды генерирует;

- ROUND (Numeric, Длина) –Numeric өрнегін берілген Ұзындыққа дейін дөңгелектейді (үтірден кейінгі таңба санын);

- SIGN (Numeric) – санның таңбасын+/- немесе нөлды шығарады;

- SQUARE (Float) –Float санының квадратын есептейді;

- SQRT (Float) –Float санының түбірін есептейді.

Математикалық функцияларды қолданудың мысалдары:

- SELECT ABC(-10) нәтижесі 10;

- SELECT SQRT (16) нәтижесі 4;

- SELECT ROUND (125.85,0) нәтижесі 126;

- SELECT POWER (2,4) нәтижесі 16.

Ескерту: функция параметрі ретінде оларға сәйкес деректер типін көрсетеміз.

Жолдық функциялар. Жолдық функциялар бір немесе бірнеше жолдармен амалдарды орындауға мүмкіндік береді:

- 'Строка1'+ 'Строка2' Строка1- ді Строка2 - ге қосады;

- ASCII(Char) Char өрнегінің ең сол жақтағы символынан ASCII кодын қайтарады;

- CHAR(Int) Int өрнегіндегі ASCII кодқа сәйкес символды шығарады;

- CHARINDEX(Образец, Выражение) Өрнектің мысалының позициясын шығарады, яғни мысалдың өрнектің қай жерінде орналасқанын;

- DIFFERENCE(Выражение1, Выражение2) екі өрнекті салыстырады, 0 ден 4 ке дейін сандарды шығарады: 0 – өрнектер абсолют әртүрлі; 4 – өрнектер абсолют бірдей. Екі өрнек те Char деректер типі;

- LEFT(Char, Int) Char Int жолынан символдарды солға шығарады;

- RIGHT(Char, Int) Char Int жолынан символдарды оңға шығарады;

- LTRIM(Char) Char жолынан сол жақтағы бос орындарды (пробел) өшіреді;

- RTRIM(Char) жолынан оң жақтағы бос орындарды (пробел) өшіреді;

- WCHAR(Int) Unicode форматында Int өрнегін шығарады;

- REPLACE(Строка1, Строка2, Строка3) Строка1 де Строка2 нің барлық элементтерін Строка3 тің элементтеріне ауыстырады;

- REPLICATE(Char, Int) Char Int жолын бір рет қайталайды;

- REVERSE(Сhar) Char жолын инверсиясын жасайды, яғни символдарды кері тәртіппен орналастырады;

- SPACE(Int) Int бос орнын (пробел) шығарады;

- STR(Float) Float санын жолға айналдырады;

- STUFF(Выражение1, Начало, Длина, Выражение2) Выражения1 ден Начало символының позициясынан бастап Длина параметріне тең символ санын өшіреді, оның орнына Выражение2 ні қояды;

- SUBSTRING(Выражение, Начало, Длина)Өрнектен(выражение) бастапқы (Начало) позициясынан бастап берілген ұзындықта(длина) жол шығарылады;

- UNICODE(Char) Char жолындағы бірінші символының кодын Unicode форматында шығарады;

- LOWERC(Char) Char жолын кіші әріптерге айналдырады;

- UPPER(Char) Char жолын бас әріптерге айналдырады.

Жолдық функциялардың қолданылу мысалдары:

- SELECT ASCII('G') - нәтижесі 71;

- SELECT LOWER(‘ABC’) - нәтижесі abc;

- SELECT Right(‘ABCDE,3’) - нәтижесі CDE;

- SELECT REVERCE('МИР') - нәтижесі РИМ.

Ескерту. Барлық жол функцияларында Char типті өрнек мәндері бірлік тырнақшаға алынады.

Мерзім(дата) функциялары. Кейбір мерзім функцияларында арнайы символдармен кодталатын мерзімнің бөлігі қолданылады:

- dd – ай күні (1 ден 31 - ге дейін);

- dy – жыл күні (1 ден 366 - ға дейінгі сан);

- hh –сағат мәні (0-23);

- ms –секунд мәні (0 ден 999);

- mi –минут мәні (0-59);

- qq – мән (1-4);

- mm – айлар мәні (1-12);

- ss – секунд мәні (0-59);

- wk – жылдағы апта нөмірінің мәні;

- dw – апта күні мәні, апта жексенбіден басталады (1-7);

- yy – жыл мәні (1753 -999).

Мерзім функциялары мерзіммен немесе уақытпен жұмыс істеуге арналған. Келесі бірнеше мерзім функциялары бар:

- DATEADD(часть, число, date) date мерзіміне санға(число) үлкейтілген мерзім бөлігін(часть) қосады;

- DATEDIFF(часть, date1, date2) date1 және date2 арасындағы мерзім бөлігінің санын шығарады;

- DATENAME(часть, date) Берілген мерзімге мерзім бөліктерінің символдық мәнін шығарады (апта күні аты);

- DATEPART(часть, date) Берілген мерзімге мерзім бөліктерінің сандық мәнін шығарады (ай нөмірі);

- DAY(date) Берілген мерзімде күн санын шығарады;

- MONTH (date) Берілген мерзімде ай санын шығарады;

- YEAR(date) Берілген мерзімде жыл санын шығарады;

- GETDATE() компьютерде қойылған ағымдағы мерзімді шығарады.

Ескерту: мерзімдер Америкалық форматта көрсетіледі: ай/күн/жыл (месяц/день/год).

Мерзімдермен жұмыстың функцияларының мысалдары:

- SELECT DATEADD(dd,5,11/20/07) - нәтижесі Nov/25/2007;

- SELECT DATEDIFF(dd,11/20/07, 11/25/07) – нәтижесі 5 күн;

- SELECT DATENAME(mm, 11/20/07) - нәтижесі November;

- SELECT DATEPART(mm, 11/20/07) - нәтижесі 11.

Ескерту: SELECT операторы өрнегінде салыстыру амалдарын қолдануға болады. Нәтижесінде не ақиқат TRUE, не жалған FALSE. Келесі операторларды қолданса болады: =, <, >, >=, <=, <>, !<(кіші емес), !>(үлкен емес), !=(тең емес). Амалдардың приоритеті дөңгелек жақшаға алынады.

Жүйелік функциялар. Жүйелік функциялар деректер базасы және оның мазмұны туралы ақпарат алу үшін арналған. SQL серверде келесі жүйелік функциялар бар:

- COL_LENGTH(таблица, поле) өріс енін шығарады;

- DATALENGTH(выражение) өрнек ұзындығын шығарады;

- GETAHSINULL(имя БД) ДҚ−да NULL мәнін қолдануға болатынын не болмайтынын шығарады;

- IDENTINCR(таблица) кестедегі санағыш өрісінің көбею қадамын шығарады;

- IDENT_SEED(таблица) кестедегі санағыштардың бастапқы мәнін шығарады;

- ISDATE(выражение) Егер өрнек мерзім болса, бірді шығарады, ал болмаса нөлді шығарады;

- ISNUMERIC(выражение) Егер өрнек сандық болса, бірді шығарады, ал болмаса нөлді шығарады;

- NULIFF(выражение1, выражение2) Егер выражение1 выражение 2 ге тең болса нөлді шығарады.

Агрегаттық функциялар. Агрегаттық функциялар кестенің өрістері бойынша нәтижелік мәндерді есептеуге мүмкіндік береді:

- AVG(поле) –өрістің орта мәнін шығарады;

- COUNT(*) – кестедегі жазба санын шығарады;

- COUNT(поле) – өрістің барлық мәндерінің санын шығарады;

- MAX(поле) – өрістің максималды мәнін шығарады;

- MIN(поле) – өрістің минималды мәнін шығарады;

- STDEV(поле) – өрістің барлық мәндерінің орта квадраттық ауытқуын шығарады;

- STDEVP(поле) – өрістің әртүрлі мәндерінің орта квадраттық ауытқуын шығарады;

- SUM(поле) – өрістің барлық мәндерін қосады;

- TOP n [Percent] – Кестеден n бірінші жазбаларды шығарады;

- VAR(поле) – өрістің барлық мәндерінің дисперсиясын шығарады;

- VARP(поле) – өрістің әртүрлі мәндерінің дисперсиясын шығарады.

Агрегаттық функцияларды қолданудың мысалдары:

- SELECT AVG(возраст) FROM Студенты –«Студенты» кестесіндегі студенттің орта жасын шығарады;

- SELECT COUNT(ФИО) FROM Студенты – «Студенты» кестесінен әртүрлі ФИО санын шығарады;

- SELECT Top 100 * FROM Студенты – «Студенты» кестесіндегі бастапқы 100 студентті шығарады .

Бақылау сұрақтары.

1. SELECT операторының міндетті құрауышын айтыңыз.

2. WHERE кілттік сөзінің өрнегіндегі барлық мәліметтері үшін тырнақшаны қолдану міндетті ма?

3. SELECT операторы SQL дің қай бөлігіне жатады?

4. WHERE кілттік сөзі үшін арналған өрнекке бірнеше шарт қойса бола ма?

5. Сандық өріс мәндері үшін тырнақшалардың болуы рұқсат па?

6. COUNT функциясын қолданғанда деректер типі рөл ойнай ма?

7. Деректерді баған бойынша топтау үшін, осы баған SELECT кілттік сөзінің тізімінде көрсетілуі қажет па?

8. SELECT операторында ORDER BY кілттік сөзін қолданған кезде GROUP BY кілттік сөзін қолдану міндетті ма?

4 Зертханалық жұмыс. Күрделі және динамикалық сұраныстарды құру

Жұмыстың мақсаты:

- Стуенттерге скриптпен және СУБД MS SQL Server 2008 графикалық ортасында деректер базасына күрделі сұраныстарды құрғызып үйрету;

- Сақталынатын процедура көмегімен динамикалық сұраныстардың құрылу процесін зерттеу.

4.1 Зертханалық жұмысқа тапсырмалар

Деректер базасынан келесі мәліметтерді алыңыз:

1) «Информатика» және «Физика» пәндерін жүргізетін оқытушылар тізімі.

2)Қанағаттанарлықсыз бағалары бар студенттердің тізімі.

3)Жоғары математика бойынша емтихан тапсырмаған студенттердің тізімі.

4) «Информатика» кафедрасының оқытушылар тізімі.

5) Кафедра жетекшілерінің фамилияларымен кафедралар тізімі.

6) Осы топ старосталарының фамилиясымен бірге топ аттары тізімі.

7) Барлық пәндер бойынша бағаларымен бірге әрбір топтың студенттерінің тізімі.

8) «Информатика» пәні бойынша қай топтарда сабақ жүргізіледі?

9) Бірінші топта Информатика бойынша қандай сабақ түрлері жүргізіледі?

10) Бір семестрде әрбір топта әр пән бойынша қанша сағат сабақ жүргізіледі?

11) Студенттер тізімін, орташа бағасын және оның оқу бағасын («озат», «үздік», «қанағаттанарлық»)

11) Үш санның орташасын шығаратын процедура құру.

12) «ФИО» бойынша студенттер кестесінен студенттерді таңдау үшін сақталынатын процедура құру.

4.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар

4.2.1 Сұраныстарда кестелерді біріктіру.

Кестелердің атына лақап ат қолдану. Кестелерге лақап аттар басыу көлемін кішірейту мақсатымен беріледі, сонымен қатар кестелерді рекурсивті байланыстыруда қолдану үшін.

Мысал:

SELECT A.Teach_ID, B.Chair_NAME FROM Teacher A

INNER JOIN Chair B ON A.Chair_ID=B.Chair_ID

Рекурсивті байланысты керекті деректердің барлығы бір кестеде орналасқан кезде, бірақ белгілі бір түрде кестенің бір жазбаларын екіншісімен салыстыру қажет болған кезде қолдану ыңғайлы. Біздің базада People кестесінде студенттердің фамилиялары мен осы студенттердің старосталарының нөмірлері сақталған. Студенттердің тізімін олардың старосталарының фамилиясымен бірге алайық:

SELECT A.Peop_FAM , B.Peop_FAM FROM People A

LEFT JOIN People B ON A.Peop_MAN = B.Peop_ID

Бірнеше кілтпен байланыстыру. Мысалға, сіз қандай жұмыс түрі бойынша және де қай пән бойынша қай студенттер қандай баға алғанын көргіңіз келеді делік (ол үшін төрт кестені байланыстыру қажет болады):

SELECT People.Peop_FAM , Subject.Subj_NAME , Evolution.OCENKA

FROM People

JOIN Evolution ON Stud.Peop_ID = Evolution.Peop_ID

JOIN Subject ON Subject.subj_id = Evolution.subj_id

4.2.2 Сұранысшаларды қолдану. Байланысқан сұранысшалар.

Сұраныстар басқа сұраныстарды басқаруы мүмкін. Ол сұранысты басқа сұраныстың шартының ішіне орналастыру және дұрыс не дұрыс емес шартта ішкі сұранысты шығаруды қолдану арқылы жасалынады.

Әдетте ішкі сұраныс, дұрыс не дұрыс еместікке, сыртқы сұраныс шартында тексерілетін мәндерді генерирлейді. Мысалы, біз студенттің фамилиясын білеміз - Сидоров, бірақ оның кодын білмейміз (Peop_ID), және Evolution кестесінен оның барлық бағаларын алғымыз келеді:

SELECT * FROM Evolution

WHERE Peop_ID = (

SELECT Peop_ID FROM People

WHERE Peop_FAM = 'Сидоров' )

Сыртқы сұранысты (негізгі сұраныс) орындау үшін, алдымен WHERE сөйлемі ішінде ішкі сұраныс (сұранысша) орындалады. Сұранысшаны орындаған кезде Students кестесі қаралады, одан Peop_FAM өрісі 'Сидоров' қа тең жолдар таңдалынады да, кейін Peop_ID өрісінің мәні шығарылады. Мысалға жалғыз жол Peop_ID = 30104 болсын. Кейін алынған мән ішкі сұраныстың өзіне орналасудың орнына негізгі сұраныстың шартына орналастырылады, сондықтан шарт мына түрде болады:

WHERE Peop_ID = 301004

Шарттарда салыстыру амалдарына (үлкен, кіші, тең, тең емес т.б.) негізделген ішкі сұраныстарды қолданған кезде, алдымен ішкі сұраныстың бір тек бір ғана мәнді қайтаратынына көз жеткізіп алу керек. Егер сіздің ішкі сұранысыңыз ешқандай мән қайтармаса, онда негізгі сұранысыңыз да ешқандай мәнді шығармайды.

Егер сіз бірнеше жолды қайтаратын ішкі сұранысты қолданғыңыз келсе, онда IN операторын қолдану қажет. Мысалы, егер базада Сидоров фамилиясымен бірнеше студент және барлығында да бағалары бар болса:

SELECT * FROM Evolution

WHERE Peop_ID IN (

SELECT Peop_ID

FROM People WHERE Peop_FAM = 'Сидоров'

ЭТТ (ТЭЦ) пәні үшін барлық бағаларды табайық:

SELECT Evolution.OCENKA FROM Evolution

JOIN subject ON Evolution.subj_id= subject.subj_id

WHERE Evolution.Subj_ID IN (

SELECT Subj_ID FROM Subject

WHERE Subj_NAME = 'ТЭЦ')

Осы нәтижені қосылу көмегімен де алуға болады:

SELECT Evolution.OCENKA FROM subject

JOIN progress ON subject.subj_id=progress.subj_id

WHERE Subj_NAME = 'ТЭЦ'

Алайда осы сұраныс алдыңғысына эквивалентті болса да, SQL Server екі кестеден жолдардың әрбір мүмкін комбинацияларын қарастыруға және олардың шартқа келетінін тексеруге міндетті.

Жоғарыда келтірілген ішкі сұраныстардың барлығын біріктіретіні, олардың барлығының тек бір жалғыз бағанды таңдауы. Ол міндетті, себебі олардың нәтижесі жалғыз мәнмен салыстырылады. SELECT * типті бұйрықтар ішкі сұраныстарда тыйым салынған.

Сонымен қатар ішкі сұраныстарды HAVING сөйлемінде қолдануға да болады. Осы ішкі сұраныстар өзінің GROUP BY немесе HAVING сөйлемдерін қолдана алады. Келесі сұраныс оған мысал бола алады:

SELECT Ocenka, COUNT (DISTINCT Stud_ID )

FROM Evolution GROUP BY Ocenka

HAVING Ocenka > (

SELECT AVG(ocenka ) FROM Evolution

WHERE Pr_DATE >01/09/05)

Бұл бұйрық 01.09.05. кейін емтихан тапсырған, бағасы ортадан жоғары студенттерді санайды.

Байланысқан ішкі сұраныстар. Ішкі сұраныстарды қолданғанда салынған ішкі сұраныста сыртқы сұраныс кестелеріне қараса болады. Мысалы, 01.03(1 наурыз) күні емтихан тапсырған барлық студенттерді қалай тапса болады:

SELECT * FROM Stud C

WHERE '2008-03-01' IN (

SELECT Pr_DATE FROM Evolution O

WHERE O.Peop_ID = C.Peop_ID )

Байланысқан сұраныстарды кестені өзімен салыстыруға пайдалануға болады. Мысалы, баллы ортадан жоғары барлық студенттерді тапса болады:

SELECT Peop_ID FROM Evolution O

WHERE ocenka > (

SELECT AVG(ocenka ) FROM Evolution O1

WHERE O1.Peop_ID = O.Peop_ID )

EXISTS операторын қолдану. EXISTS операторы ішкі сұранысты аргумент ретінде алады, және оны егер ішкі сұраныс қандай да бір жазбаны қайтарса дұрыс деп, ал оны жасамаса дұрыс емес деп бағалайды. Кем дегенде бір студент оқитын барлық топтарды іздеу осылай орындалады:

SELECT Grup_ID FROM [Gruppa] G

WHERE EXISTS (

SELECT * FROM People

WHERE Stud.Grup_ID = G.Grup_ID)

Ішкі сұраныс топта оқитын барлық студенттерді таңдайды. Оператор EXISTS операторы сыртқы шартта ішкі сұраныстың белгілі мәнді қайтарғанын белгілейді, осыдан шарттың дұрыстығы шығады. Ішкі сұраныс барлық сыртқы сұраныс үшін бір рет қана орындалады және барлық жағдайда бір мәнге иемденеді. Сондықтан EXISTS, осындай түрде қолданғанда, шартты барлық жолға бірден дұрыс немесе дұрыс емес қылады.

ANY, ALL операторларын қолдану. Емтиханды тапсырған студентті іздеудің жаңа тәсілін қарастырайық:

SELECT * FROM People

WHERE Peop_ID = ANY (

SELECT Peop_ID FROM Evolution)

ANY операторы сұраныспен шығарылған кезкелген мәнді алады да, және оларды дұрыс деп бағалайды, егер олардың кезкелгені сыртқы сұраныстың ағымдағы мәніне тең болса. Бұл сұраныстың негізгі шартта салыстырылатын мәндердің типіндей мәндерді алуы керек дегенді білдіреді.

Жоғарыда келтірілген сұраныста IN операторын қолданса болатын еді. Алайда ANY операторын тек теңдік операторымен ғана емес қолдануға болады.

ALL операторы шартты дұрыс деп санайды, егер сұранысшаның таңдаған әрбір мәні сыртқы сұраныстың шарттарын қанағаттандырса; Шәкіртақысы 1500 нан жоғары студенттерді таңдайық:

SELECT * FROM People

WHERE Peop_STIP > ALL(

SELECT Peop_STIP FROM People

WHERE Peop_STIP = 1500)

UNION бұйрығын қолдану. UNION бұйрығы қайталанылатын жолдардан басқа бірнеше сұраныстың шығарылуын біріктіреді. Мысалы, төменде келтірілген сұраныс фамилиялары К мен С тің арасындағы барлық студенттер мен оқытушыларды шығарады:

SELECT Peop_FAM FROM People

WHERE Peop_FAM BETWEEN 'К' AND 'С'

UNION

SELECT Teach_FAM FROM Teacher

WHERE Teach_FAM BETWEEN 'К' AND 'С'

UNION бұйрығын қолданудың екі ережесі бар:

- Бағандардың (колонки) саны мен жалғасу реті барлық сұраныстарда бірдей болуы тиіс;

- Деректер типі сәйкес болуы тиіс.

UNION автоматты түрде шығарудан жолдардың көшірмесін алып тастайды. Егер сіз сұраныстан барлық жолдардың нәтижеге шыққанын тілесеңіз, онда UNION ALL қолдаңыныз:

SELECT Peop_FAM FROM People

UNION ALL

SELECT Teach_FAM FROM Teacher

CASE операторын қолдану. CASE функциясы, мүмкін, SQL да ең сирек қолданылатындардың бірі шығар. Жиірек оны таңдамалардағы өріс мәндері нұсқаларын генерациялауға қолданады, бірақ, бұл функцияда тағы да көп пайдалы қолданулары бар. Мысалы, төменде келтірілген сұраныс барлық студенттерді статусқа шығарады:

SELECT Peop_FAM as 'Фамилия' +' ' + Stud_Ima as 'Имя',

case

when Peop_STIP is NULL then 'Платник'

else 'Грантник' end as 'Статус' FROM People

COALESCE операторын қолдану. Аргументтер тізімінен NULL ге тең емес алғашқы мәнді қайтарады. Берілген сұраныс барлық студенттер мен олардың шәкіртақыларын шығарады. Егер стипендия өрісі бос болса, яғни NULL бар болса, онда ол басқа аргументпен ауыстырылады :

SELECT Peop_FAM as 'Фамилия', Stud_Ima as 'Имя',

COALESCE (cast(Peop_STIP as char(10)), 'Платник') as 'Стипендия' FROM People

4.2.3 Динамикалық сұраныстарды сақталынатын процедура көмегімен құру.

Сақталынатын процедура – параметрлері бар SQL сұраныс, яғни ол жай процедура ретінде орындалады (біз оның атын береміз және параметрлердің мәнін сақталынатын процедураға жібереміз.) Сақталынатын процедура параметрлерінің мәнінен тәуелді біз анау не мынау нәтижені аламыз.

Ескерту: SQL серверде сақталынатын процедураларға сервердің жағында орындалатын динамикалық сұраныстар жүзеге асырылады.

SQL тілі бұйрықтары көмегімен сақталынатын процедураның құрылуын қарастырайық. Сақталынатын процедураны көрсету үшін жұмысшы ДҚ «Object Explorer» панелінен «Stored Procedures» пунктін бөліп алу керек. SQL тілі бұйрықтары көмегімен жаңа процедураны құру үшін құралдар панеліндегі пернені тышқанның сол жақ батырмасымен шерту керек.

Сервер терезесінің жұмыс аймағында SQLQuery1.sql қосымшасы (вкладка) пайда болады, мұнда жаңа процедура кодын теру қажет, оның синтаксисі келесідей:

CREATE PROCEDURE <Имя процедуры>

[@<Параметр1> <Тип1>[=<Значение1>],

@<Параметр2> <Тип2>[=<Значение2>], …]

[WITH ENCRYPTION]

AS <Команды SQL>

Мұндағы:

- Процедура аты – құрылып жатқан сақталынатын процедура аты.

- Параметр1, Параметр2, … - процедураға жіберілетін параметрлер.

- мән1, мән2, … - параметрлердің үнсіздік бойынша мәні.

- Тип1, Тип2, … - параметрлердің деректер типі.

- WITH ENCRYPTION – деректерді шифрлауды қосады.

- SQL бұйрықтары– SQL сұраныс, процедураны жібергенде орындалады.

Ескерту: SQL сұраныс өзіне параметрлерді қосады, егер параметрлер қандай да бір өрістермен немесе өрнектермен салыстырылса, онда олардың деректер типі дәл осы өрістердің немесе өрнектердің деректер типімен бірдей болуы қажет.

Ескерту: Құрылған соң процедура ағымдағы ДҚ «Object Explorer» панеліндегі Stored Procedures бөлімінде орналасады. Егер екі рет тышқанның сол жақ батырмасымен процедураны шертсек, онда ол өзгерту үшін «SQLQuery» қосымшасында ашылады.

Сақталынатын процедура туралы ақпаратты көріп шығу үшін мына бұйрықты орындау қажет:

EXEC SP_HELPTEXT <процедура аты>

Сақталынатын процедуралар келесі бұйрықпен жіберіле алады

EXEC < процедура аты > [<Параметр1>, <Параметр2>, …]

Мұндағы:

- < процедура аты > - орындалып жатқан процедура аты;

- Параметр1, Параметр2, … - параметрлер мәні.

Мысал: орташа баллдары берілген мәннен жоғары студенттердің атын шығарып беретін сақталынатын процедура құру:

CREATE PROCEDURE СрБАЛЛ

@X Real

SELECT * FROM Студенты

WHERE

(Оценка1+ Оценка2+ Оценка3)/3>@X

Жоғарыда терілген процедураны шақыру бұйрығы келесідей:

EXEC СрБАЛЛ 4

Бұйрық орташа баллдары 4-тен жоғары студенттердің барлығын шығарады.

Сақталынатын процедураның құрылымы келесідей:

1) Процедураның құрылым параметрлерін баптау аймағы. Процедура кодын терген кезде қолданылатын кейбір құрылымдық ережелерді баптауға мүмкіндік береді. Біздің жағдайда бұл:

- SET ANSI_NULLS ON ANSI кодировкасында NULL (Бос) мәнін қолдануды қосады;

- SET QUOTED_IDENTIFIER ON идентификаторларды анықтау үшін қос тырнақшаларды қолдану мүмкіндігін қосады;

2) процедура атын анықтау аймағы (Procedure_Name) және процедураға жіберілетін параметрлердің (@Param1, @Param2).

Параметрлерді анықтаудың синтаксисі келесідей:

@<параметр аты> <деректер типі> = <үнсіздік бойынша мән>

Параметрлер өзара үтірмен бөлінеді;

3) процедура денесінің басы «BEGIN» қызметтік сөзімен белгіленеді;

4) процедура денесінде T-SQL сұраныстарды программалау тілі бұйрықтары бар;

5) процедура денесінің соны «END» қызметтік сөзімен белгіленеді.

Ескерту: кодта жасыл түспен түсініктемелер бөлінеді. Олар сервермен өңделмейді және кодқа түсініктеме функциясын орындайды. Түсініктеме жолы “–“ жолшасынан басталады. Ары қарай кодта біз түсініктемелерді көрсетпейміз, олар оралып тұрады. Түсініктемелер бөлімінің сол жағында “+” таңбасы тұрады, оны басып түсініктемелерді ашып алса болады.

Үш санның орташасын есептейтін процедура кодын келесі мысалда берілгендей тереміз.

SET ANSI_NULLS ON

GO [Команданы жіберу]

SET QUOTED_IDENTIFIER ON

GO [Команданы жіберу]

CREATE PROCEDURE [Үш санның орташасын есептеу]

@Value1 Real=0,

@Value2 Real = 0,

@Value3 Real = 0

BEGIN

SET NOCOUT ON;

SELECT 'Орташа сан'=(@Value1+@Value2+@Value3)/3

END

GO [Команданыжұмыс істеуге жіберу]

Берілген процедура кодын нақтырақ қарастырайық:

1) CREATE PROCEDURE [Среднее трех величин] құрылатын процедура атын «Среднее трех величин» ретінде анықтайды;

2) @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 процедураның үш параметрін анықтайды Value1, Value2 и Value3. Берілген параметрлерге бөлшек сандарды берсе болады (Real деректер типі), үнсіздік бойынша мән 0;

3) SELECT 'Орташа мән'=(@Value1+@Value2+@Value3)/3 орташа мәнді шығарады және «орташа мән» жазуымен нәтижені шығарады.

Бақылау сұрақтары.

1. WHERE кілттік сөзімен берілген өрнекте бірнеше AND кілттік сөзінің болуы мүмкін ба?

2. Рекурсивті байланысу дегеніміз не?

3. Кестелерді байланыстырғанда FROM кілттік сөзі өрнегінде тұрған тәртіп бойынша байланысуы міндетті ме?

4. Операторда кесте-байланыс сұранысын қолданғанда, сұраныста оның бағандарын қолдану міндетті ме?

5. Сұраныста кестенің бір емес бірнеше бағанын байланыстырса бола ма?

6. SQL операторының қай бөлігі кестелерді байланыстырудың шартын береді?

7. Егер сұраныста екі кестеден құрылған таңдаманы көрсетіп, бірақ оларды байланыстырмаса не болады?

8. Кестелердің лақап аты не үшін қолданылады?

9. Ішкі сұраныс дегеніміз не? Ол қалай жұмыс жасайды?

5 Зертханалық жұмыс. Көріністер

Жұмыстың мақсаты: студенттерге деректер базасына көріністерді скриптпен және СУБД MS SQL Server 2008 графикалық ортасында құруды үйрету.

5.1 Зертханалық жұмысқа тапсырмалар

Келесі көріністі құрыңыз:

1) Топтың тізімін және осы топтың старосталарын көрсететін.

2) Пән бойынша емтихан тапсырмаған студенттер тізімін көрсететін.

3) Пайдаланушыға ыңғайлы түрде «Успеваемость» кестесінің мазмұнын көрсететін (идентификаторларсыз).

4) Жатақханада тұратын студенттердің тізімін көрсететін.

5) Бір өрісте форматталған студенттердің тізімін көрсететін (формат: Сидоров А.С. (топ)).

6) Оқытушылардің тізімін, пәндерді және осы пәндер жүргізілетін топтарды көрсететін.

5.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар

5.2.1 Көріністер туралы жалпы ақпарат.

Көрініс (view) негізінен бір немесе бірнеше кестеден құрылған, кейбір деректердің жиыншасына пайдаланушылардың қол жетімділігіне мүмкіндік беруші виртуалды көрініс болып табылады. Пайдаланушы үшін көрініс дегеніміз жай кесте сияқты, бірақ сонымен қатар өзінен өзінде оның мәліметтері жоқ. Физикалық түрде көрініс SQL-сұраныс түрінде жүзеге асырылған, оның негізінде мәліметтерді таңдау бір немесе бірнеше кестеден немесе көріністерден жүргізіледі. Негізінде көрініс құрылатын кестелер базалық (base table) деп аталады. Ең жеңіл жағдайда көріністі бастапқы кесте сияқты деректер жиынынан тұратын бір кестенің негізінде құруға болады. Тәжірибеде ондай көріністер сирек қолданылады. Одан күрделірек көріністер бірнеше кестедегі бағандарды біріктіруі мүмкін. Сонымен қатар, таңдама үшін шарт бере отырып, осы кестелерден тек шектелген жолдардың жиынын көріністен қолжетімді қыламыз. Көріністен негізгі кестенің бір немесе бірнеше бағандары шығарылса, онда кестеге вертикальді фильтр салынған делінеді. Егер көріністің анықтамасында жолдарды таңдауға бір немесе бірнеше шарт қойылса, онда кестеге горизонтальді фильтр салынған делінеді.

Көріністердің механизмі кестелердегі конфиденциалды деректерге пайдаланушының қолжетімділігін шектеуге мүмкіндік береді. Пайдаланушы көрініске қараған сайын, сервер осы көріністің анықтамасында бар сұранысқа сай, деректердің таңдамаларын жүзеге асырады. Сонымен қатар сұраныстағы барлық сілтемелердің актуалдығы орындалуға қажетті объекттің бар жоқтығына тексеріледі. Егер сұранысқа қосылған кестелердің бірі қол жетімсіз болып қалса, онда бүтін көріністі қолдану мүмкін болмай қалады. Көріністер CREATE VIEW операторы көмегімен құрылады. CREATE TABLE операторына аналогты, CREATE VIEW операторын осыған дейін болмаған көріністі құру үшін ғана қолдануға болады.

CREATE VIEW көрініс_аты

SELECT таңдау_тізімі

[ FROM кесие_көзі ]

[ WHERE таңдау_шарты ]

Көріністің не екенін түсінудің ең жақсы әдісі - нақты мысалды қарастыру. Көріністі бір немесе бірнеше кестенің деректері негізінде де, сонымен қатар басқа да көріністер негізінде де құруға болады.

Бір кесте базасы негізіндегі көрініс. People кестесінен StudAddress көрінісін құрайық:

CREATE VIEW StudAddress

SELECT Peop_ID, Peop_FAM , Stud_IMA,Peop_ADDRESS

FROM People

Students кестесінен StudStip көрінісін құрайық:

CREATE VIEW StudStip

SELECT distinct Peop_FAM ,Peop_STIP

FROM stud

Бір қарағанда, бір кесте негізінде көрініс құру мағынасыз сияқты. Алайда кестенің кейбір бағандарындағы деректерге, бөлек категориядағы пайдаланушыға рұқсат қажетсіз не керісінше болуы мүмкін (мысалы, студент шәкіртақы мөлшері). Немесе жайша ана не мына кестедегі деректерде қажеттілік жоқ (мысалға, бізге студенттің тек фамилиясы, аты және мекенжай керек делік). Бұл жағдайда кейбір кестенің негізінде көрініс құрылады, одан конфиденциалды немесе қажет емес мәліметтер алынып тасталады.

Көріністі деректер базасынан жою үшін DROP VIEW көрініс_аты бұйрығы қолданылады. Бұл бұйрықтың екі опциясы бар – RESTRICT және CASCADE. Егер RESTRICT қолданылса және шартта тәуелді көріністер бар болса, DROP VIEW операторы қатені қайтарады. CASCADE опциясы қолданылса және тәуелді көріністер бар болса, DROP VIEW операторы жақсы (успешно) бітеді және барлық көріністер жойылады.

Көріністі жою

Көріністер DROP VIEW операторы көмегімен жойылады <көрініс_аты >:

DROP VIEW StudAddress

5.2.2 Бірнеше кесте негізінде көрініс.

Жиі көріністер SQL мен жұмысты жеңілдету үшін қолданылады, көбіне бұл біріктірулерге де қатысты.

teacher және chair деген екі кестенің негізінде PrepodChair көрінісін құрайық, мұнда кафедраларда жұмыс істейтін оқытушылардың фамилиялары болсын:

CREATE VIEW PrepodChair

SELECT teacher.teach_fam, chair.chair_name

FROM teacher, chair

WHERE teacher.chair_id=chair.chair_id

People, Subject және Evolution кестелерінен Studoсenka көрінісін құрайық, мұнда пәндер бойынша студенттер тізімі және олардың бағалары көрсетіледі:

CREATE VIEW Studocenka

SELECT stud.Peop_FAM , subject.subj_name, Evolution.ocenka

FROM stud,subject,Evolution

WHERE (Evolution.Peop_ID=stud.Peop_ID) and (Evolution.subj_id=subject.subj_id)

Көріністер есептелінетін өрістерді қолдануды жеңілдету үшін пайдалы. Otli4n көрінісін құрайық, мұнда топтағы пәндер бойынша озаттардың саны көрсетіледі:

CREATE VIEW Otli4n

SELECT g.grup_name as [Группа], s.subj_name as [Предмет],

count(p.ocenka) as[Колличество отличников]

FROM Evolution p join gruppa g on g.grup_id = p.grup_id

JOIN subject s on s.subj_id = p.subj_id where p.ocenka in (8,9)

GROUP BY g.grup_name, s.subj_name

Осылайша, көріністер SQL-дің күрделі операторларын бірталай жеңілдете алады. Көріністерді қолдана отырып, SQL кодын бір рет жазып, кейін қажет жағдайда қайта қолданса болады.

Басқа көріністерді қолданудың жиі жағдайы болып таңдалған деректерді қайтаформаттау болып табылады. Мысалға, нәтижелер әрдайым белгілі форматта қажет делік. Әрбір қажет болған сайын біріктіру орындағанша, оның орнына көрініс құрып қолдануға болады.

Деректерді қайтаформаттау үшін көріністерді қолдануға болады. Белгілі форматта, кафедрада жұмыс істейтін оқытушыларді көрсететін көріністі құрайық:

CREATE VIEW PrepodChair2

SELECT left(Teach_IMA,1) +'.'+left(Teach_OTCH,1) +'.'+ left(Teach_FAM,15)+'('+left(teach_position,10)+')' AS [Teacher] , chair_name AS [Chair]

FROM Teacher, chair

WHERE chair.chair_id=teacher.chair_id

5.2.3 Көріністер базасындағы көрініс.

Көріністер ретінде виртуалды кестелерді айтатын болғасын, онда олардың базасында басқа да көріністерді құруға болады. Мысалы, бізге мамандықтардағы грантта және ақылы түрде оқитындардың санын көрсететін көрініс құру қажет делік. Берілген көріністі, әрине, күрделі сұраныстардың негізінде құруға да болады, бірақ, оны екі көрініс негізінде құрайық Grantn (гранттықтар) және Platn (ақылы түрде оқитындар).

Алдымен Speciality (Мамандықтар) кестесін құрып толтыру қажет:

CREATE TABLE Speciality (

Spec_ID int not null PRIMARY KEY,

Spec_NAME char (20) not null,)

Кейін People кестесіне spec_id атрибутын қосу қажет, Spec кестесінде spec_id сыртқы кілтін құру қажет:

ALTER TABLE stud

ADD spec_id int foreign key references Spec (spec_id)

Grantn көрінісін құрамыз, ол мамандық пен осы мамандықтағы грант санын көрсетеді:

CREATE VIEW Grantn

SELECT spec.spec_name, count (distinct Peop_ID) as 'grant'

FROM People, Spec

WHERE stud.spec_id=spec.spec_id and Peop_STIP is not null

GROUP BY spec_name

Plat көрінісін құрамыз, ол мамандық пен осы мамандықтағы ақылы түрде оқитындардың санын көрсетеді:

CREATE VIEW plat

SELECT spec.spec_name, count (distinct Peop_ID) as 'plat'

FROM stud, Spec

WHERE stud.spec_id=speciality.spec_id and Peop_STIP is null

GROUP BY spec_name

Енді StudSpecGP көріністің өзін құрамыз, ол Grantn және Plat көріністер базасында мамандық пен гранттықтар, ақша төлеп оқитындардың санын көрсетеді:

CREATE VIEW plat

SELECT p.spec_name AS ' Speciality' ,

(SELECT [grant] FROM Grantn WHERE grantn.spec_name= g.spec_name) AS 'Gratniki',

(SELECT [plat] FROM plat WHERE plat.spec_name= p.spec_name) AS 'Platniki'

FROM grantn g, plat p

WHERE g.spec_name=p.spec_name

GROUP BY g.spec_name, p.spec_name

Бақылау сұрақтары.

1. Көрініс дегеніміз не?

2. Негізінде көрініс құрылатын кестелер қалай аталады?

3. «Вертикальный», «Горизонтальный» фильтрлер түсініктері нені білдіреді?

4. Егер көрініс негізінде құрылған кесте жойылса не болады?

5. Деректерді қорғау үшін көріністі қалай пайдаланса болады?

6. Көрініс құрамында деректер бола ма?

7. Қандай мақсатқа қол жеткізу үшін көріністер қолданылады?

6 Зертханалық жұмыс. Функциялар мен триггерлер

Жұмыстың мақсаты: студенттерді деректер базасына функциялар мен триггерлерді скриптпен және СУБД MS SQL Server 2008 графикалық ортасында құруды үйрету.

6.1 Зертханалық жұмысқа тапсырмалар

Келесі әрекеттерді жасайтын процедураларды құрыңыз:

1) Студентті қабылдау/шығару;

2) Барлық студенттердің шәкіртақыларының қосындысын 15%-ке көбейту;

3) Студенттерге әртүрлі жұмыс түрі үшін баға қою (практика, бақылау, семестрлік, курстық проект, емтихан);

4) Үлгермейтін студенттерді табу;

5) Екі топты біреуге біріктіру;

6) Мұғалімді пән бойынша сабақ беретін топтарға тіркеу (Study кестесіне ақпаратты енгізу);

7) ЖОО-на күндізгі оқу түріне жасы 35 тен кіші талапкерлер қабылданады. Студентті қабылдаған кезде (Students кестесіне мәліметтерді енгізгенде) жасын бақылауға мүмкіндік беретін триггерді құрыңыз;

8) Теориялық түрде ДҚ на студентке қателесіп оқылмайтын сабағы бойынша бағаны енгізіп қоюға болады. Тапсырма: Тапсырылған пәннің санын (қосындысын) (ол студенттің тобымен оқылатын пәндердің санынан аспауы керек(Progress және Subject кестелері), студенттермен оқылатын пәндер туралы ақпаратпен бірге (Study кестесі)) бақылайтын триггер құру;

9) Белгілі бір уақыт аралығында қандай болса да кестеге жүргізілген ДҚ пайдаланушысының әрекеттерін журналға тіркеп тұратын триггер құру;

10) Теориялық түрде ДҚ на сессияны қанағаттанарлық бағаға жапқан студентке қателесіп шәкіртақыны енгізіп қоюға болады. Тапсырма: Студенттің алынған бағалары мен оның шәкіртақысының бар жоғын бақылайтын триггер құру;

11) Белгілі бір топтағы студенттердің санын қайтаратын функцияны құрыңыз;

12) Белгілі бір мамандықтағы гранттықтардың санын қайтаратын функцияны құрыңыз.

6.2 Зертханалық жұмысты орындауға әдістемелік нұсқаулар

6.2.1 Функциялар.

SQL Server де деректерді өңдеуге Transact-SQL мүмкіндіктерін кеңейту үшін ішкі функциялар тобы іске асырылған. Шартты түрде SQL Server де бар әртүрлі ішкі функцияларды бірнеше категорияға бөлуге болады:

- математикалық функции;

- жолдық функции;

- мерзіммен жұмыс жасауға арналған функциялар;

- статикалық функциялар;

- криптографиялық функциялар;

- ранжирлейтін функциялар;

- типтерді келтіретін функциялар;

- жүйелік функциялар.

Әрбір функция белгілі бір қасиеттердің тобын иеленеді, олар есептелінетін бағандарда оның қолдану мүмкіндігін анықтайды. Функция деп орындалу нәтижесі бойынша кейбір мәнді қайтаратын аталған код блогы десек болады. Қайтарылатын мән шама болуы да, жолдардың жиыны да болуы мүмкін. Функциялар өрнектерде қатысуы, сақталынатын процедуралардың денесінде шақырылуы мүмкін, сонымен қатар басқа функциялардың денесінде де шақырылуы мүмкін.

Жүйеде үнсіздік бойынша бар ішкі функциялардан басқа, пайдаланушылар жеке өзінің функцияларын құра алады. Пайдаланушылық функциялар деректерді өңдеудің ыңғайлы әрі икемді механизмі болып табылады, себебі оларды кезкелген өрнекке қосса болады.

Функцияны құру үшін келесі форматтағы CREATE FUNCTION операторын қолдану қажет:

CREATE FUNCTION имя_функции

([ < @имя_параметра> AS <тип_данных> [=<значение_по_умолчанию>]])

RETURNS <возвращаемый_тип_данных>

AS операторы SQL

Студенттің жасын анықтайтын функция (мысалы, біз id_stud бойынша студенттің жасын білгіміз келеді):

CREATE FUNCTION GetPeopleAge(@uid int)

RETURNS varchar(200)

BEGIN declare @age datetime

SELECT @age=Peop_DATE FROM People WHERE Peop_ID=@uid

SET @age=GETDATE()-@age;

RETURN convert(varchar(20),YEAR(@age)-1900)+' лет, '+convert(varchar(20),MONTH(@age)-1)+' месяцев и '+convert(varchar(20),DAY(@age)-1)+' дней'

END

Берілген пайдаланушылық функцияны келесі сұраныста қолдануға болады:

SELECT Peop_FAM as [Фамилия], Stud_IMA as [Имя], Peop_DATE as [Дата рождения], dbo.GetPeopleAge(Peop_ID) as [Возраст]

FROM People ORDER BY Peop_DATE ASC

Яғни, пайдаланушылық функцияны құра отырып сұраныстардың күрделілігін көп есе жеңілдетсе болады.

6.2.2 Триггерлер.

Триггер – бұл компиляцияланған процедура, деректер базасында болатын жағдайлармен иницияланатын әрекеттерді жасауға қолданылады Триггер деректер базасында сақталған процедура болып табылады, ол кестеге қатысты әрекеттер жасалынған кезде жіберіледі. Триггер INSERT, UPDATE немесе DELETE операторларына дейін немесе кейін орындалуы мүмкін. Триггерлердің ең таралған қолданысы – бұл деректер базасындағы күрделі критерийлерді тексеруі. Триггер - SQL-сервердің ерекше құрылғысы, ДҚ да деректердің бүтіндігін демеуші.

Қарапайым мысалды қарастырайық. Студенттің шәкіртақысы 15000 нан аса алмайды. Шәкіртақының сомасын 15000-нан асырып енгізуге мүмкіндік бермейтін триггерді анықтайық. SQL Server Enterprise Manager утилитасы көмегімен триггерді құру үшін Progress кестесін деректер базасының объектілер тізімінен таңдау қажет, одан кейін контексттік мәзір бұйрығын орындау қажет Аll tasks (Барлық есептер) - Manage Triggers. Бұл әрекеттер триггер қасиеттерінің диалогтық терезесінің ашылуына әкеледі.

CREATE TRIGGER tri_stip

ON stud FOR INSERT, UPDATE

/*описываются локальные переменные*/

DECLARE @stip smallmoney

/* определяется информация о вставляемых записях*/

SELECT @stip = U.Peop_STIP

FROM Inserted U

IF @stip >15000

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Стипендия студента не может превышать 15000', 16,10)

END

Progress кестесі үшін tri_ins_progress триггерін анықтайық, ол Progress кестесіне жазба қойылған сайын немесе модификацияланған сайын жіберіле-тін болады. Егер емтихан немесе зачет уақытында тапсырылмаса (мысалы, айдың 15- күнінен кейін), онда жазба қабылданбайды:

CREATE TRIGGER tri_ins_Evolution

ON Evolution FOR INSERT, UPDATE

/*описываются локальные переменные*/

DECLARE @nDayOfMonth TINYINT

/* определяется информация о вставляемых записях*/

SELECT @nDayOfMonth = DATEPART (Day, I.Pr_DATE)

FROM Evolution P, Inserted I

WHERE P.Peop_ID = I.Peop_ID AND P.Ocenka = I.Ocenka

/*проверяется условие вставки записи*/

/*и при необходимости сообщается об ошибке*/

IF @ nDayOfMonth >15

BEGIN

/*Команда ROLLBACK используется для того, чтобы отменить

модификацию данных в случае, если база заблокирована*/

ROLLBACK TRAN

RAISERROR ('Вводить оценки, полученные до 15-го числа', 16,10)

END

Progress кестесіне студенттермен 15-нен кейін алынған бағалары туралы мәліметтерді енгізіп көріңіз.

Триггерді келесі түрмен жоюға болады: DROP TRIGGER имя_триггера

Немесе Аll tasks (барлық есептер) контексттік мәзірінен- Manage Triggers триггердің атын тізімнен таңдау арқылы.

Студентті қосуға триггер:

CREATE TRIGGER tr_ins_stud

ON People FOR INSERT

DECLARE @grup integer

SELECT @grup=I.Grup_ID FROM Inserted I UPDATE Gruppa

SET Grup_KOLSTUD = Grup_KOLSTUD+1

WHERE Gruppa.Grup_ID=@grup

Мысалы, бізге кімнің және қашан студенттердің бағаларын өзгерткенін білу қажет делік. Ол үшін журналдың кестесі мен триггер құру қажет.

Өзгерістерді журналдауға арналған кестені құру

CREATE TABLE journ (

mod_oper CHAR(20), /* Тип производимой операции */

mod_datetime DATEtime, /*Дата изменеия */

mod_user VARCHAR(30), /*Пользователь БД */

mod_id INTEGEr, /* id студента чья оценка была изменена*/

mod_ocen integer /* Измененая оценка*/

old_ocen integer )

Бағалардың өзгеруін тіркейтін тригер:

CREATE TRIGGER treg

ON Evolution

FOR Update

DECLARE @id int, @ocen int, @old_ocen int

SELECT @old_ocen=P.ocenka,@id = P.Peop_ID , @ocen = U.ocenka

FROM Evolution P, Inserted U

INSERT INTO journ VALUES('Обновлена',Current_timestamp,Current_USER,@id, @ocen,@old_ocen);

Берілген триггер студенттердің бағаларының өзгеруі туралы ақпараттарды енгізіп тұрады.

Бақылау сұрақтары.

1. Функцияларды қолданудың артықшылығы неде?

2. Процедуралар мен функциялардың айырмашылығы қандай?

3. Триггерлер қашан орындалады – INSERT, UPDATE және DELETE бұйрықтарын орындағанға дейін, әлде кейінбе?

4. Триггерді өзгертуге бола ма?

Әдебиеттер тізімі

1. Роберт У., Майкл К., Роберт Р., Фабио Ф., Фармер Д. SQL Server 2008. Ускоренный курс для профессионалов. Вильямс – Москва – Санкт Петербург – Киев, 2008. – 768 с.

2. Стивенс Р, Программирование баз данных. - М.: ООО «Бином-Пресс», 2007 – 384 с.

3. Найт Б., Пэтел К., Снайдер В., Лофорт Р., Уорт С. Microsoft SQL Server 2008: руководство администратора для профессионалов. Вильямс – Москва – Санкт Петербург – Киев, 2008. – 855 c.

4. Жилинский А. Самоучитель Microsoft SQL Server 2008. БХВ-Петербург, 2009. – 240 с.

5. Хансен Г., Хансен Д. Базы данных: разработка и управление. – М.: ЗАО «Издательство БИНОМ», 1999.

6. Питер Роб, Карлос Коронел Системы баз данных: проектирование, реализация и управление, 5-е издание, – BHV Санкт-Петербург, 2004. -1040 с.

7. Кренке Д. Теория и практика построения баз данных. Изд.9 – Питер, 2005.

8. MICROSOFT SQL SERVER 2005. Реализация и обслуживание. Учебный курс Microsoft/ Пер. с английского – М.: «Русская редакция», Спб.: «Питер», 2007. – 768 стр. ил.

9. Мамаев Е. MS SQL Server 2000. Проектирование и реализация баз данных. Сертификационный экзамен. - BHV, СПб., 2004.- 416 с.

10. Плю Р., Стефенс Р., Райан К. Освой самостоятельно SQL за 24 часа. – М.: Издательский дом «Вильямс», 2000.