Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Воронежский государственный университет

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

SQL в вопросах и задачах

.pdf

Скачиваний:

Добавлен:

21.05.2015

Размер:

917.91 Кб

Скачать

☆

<<< < Предыдущая 1 2 3 4 5 6 7 8 910 / 1610 11 12 13 14 15 16 > Следующая >>>

оп исания столбца (з ап ятой). О гранич ения на таблицу раз мещ а ются в конце оп ред еления та блицы, п осле оп ред еления п ослед него столбца. К оманд а CREATE TABLE имеет след ующ ий синтаксис, расширенный в ключ ением огранич ений

CREATE TABLE <и м ят а бли цы >

(<и м яст о лбца > <т и п д а нны х> <о гр а ни чени яна ст о лбец>, <и м яст о лбца > <т и п д а нны х> <о гр а ни чени яна ст о лбец>,…

<о гр а ни чени яна т а бли цу> (<и м яст о лбца >[,<и м яст о лбца >… ])… );

Поля, з ад анныев круглых скобках п ослеоп исанияогранич ений таблицы

– это п оля, на которыеэ ти огранич енияра сп ространяются. О гранич ения на столбцы п рименяютсяк темстолбцам, з а которыми они оп иса ны.

4.5.1. О граничение NOT NULL

Ч тобы з ап ретитьв оз можностьисп ольз ов а ния в					п оле NULL-з нач ений,
можно п ри	соз д ании та блицы кома нд ой		CREATE		TABLE	указ а ть д ля
соотв етств ующ его столбца		ключ ев ое слов о	NOT NULL. Э то			огранич ение
п рименимо только к столбца м таблицы. К ак ужегов орилосьв ыше, NULL –
это сп ециальный маркер , обоз нач ающ ий тот факт,				ч то п оле п усто. Н о он
п олез ен не в сегд а. Пер в ич ные ключ и, на п ример,				в	п ринцип е не д олжны
сод ержать NULL-з нач ений (быть п устыми),			п оскольку э то нарушило				бы
требов ание	уникальности	п ер в ич ного	ключ а		(более	строго	–

функциональную з а в исимостьатрибутов таблицы от п ерв ич ного ключ а). Во

многих д ругих случ аях также необход имо, ч тобы п оля	о бяза т ельно
сод ержали оп р ед еленные з нач ения. Е сли ключ ев ое слов о	NOT NULL

раз мещ аетсянеп осред ств енно п ослетип а д анных (в ключ аяраз мер) столбца ,

то любые п оп ытки остав итьз на ч ение п оля п устым (в в ести в	п оле NULL-
з нач ение) буд ут отв ергнуты системой.
Н а п ример, д лятого, ч тобы в оп ред елении таблицы STUDENT з ап ретить
исп ольз ов ание NULL-з нач ений д ля столбцов STUDENT_ID,	SURNAME и
NAME, можно з ап исатьслед ующ ее:

CREATE TABLE STUDENT

(STUDENT_ID INTEGER NOT NULL,

	92
SURNAME	CHAR (25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER,
KURS	INTEGER,
CITY	CHAR (15),
BIRTHDAY	DATE,
UNIV_ID	INTEGER);

Важно п омнить, ч то,	если д ля столбца указ ано NOT NULL, то п ри
исп ольз ов ании команд ы	INSERT	обяз ательно	д олжно	быть ука з ано
конкретноез нач ение, в в од имоев это п оле. При отсутств ии огранич енияNOT
NULL в столбце з нач ение может отсутств ов ать, если только не ука з ано
з нач ение столбца п о умолч анию (DEFAULT). Если			п р и соз д ании таблицы
огранич ение NOT NULL не было ука з ано, то его			можно	указ ать п оз же,
исп ольз уя команд у ALTER TABLE.		О д на ко, д ля того, ч тобы д ля в нов ь
в в од имого с п омощ ью кома нд ы ALTER TABLE столбца можно было з а д ать
огранич ение NOT NULL, таблица, в		которую д обав ляется столбец, д олжна
бытьп устой.

4.5.2. Уникал ь ност ь как ограничение на ст ол бец

Иногд а тр ебуется, ч тобы в сез на ч ения, в в ед енныев столбец, отлич а лись

д руг от	д руга. Н а п р имер, э того		требуют	п ер в ич ные ключ и. Е сли		п ри
соз д а нии таблицы		д ля столбца указ ыв а ется огранич ение UNIQUE,			то	баз а
д анных	отв ерга ет	любую п оп ытку	в в ести в	это п оле какой-либо	строки

з нач ение, ужесод ержащ еесяв том жеп олед ругой строки. Э то огранич ение п рименимо только к тем п олям, которыебыли объяв лены NOT NULL. М ожно п ред ложить след ующ ее оп ред еление таблицы STUDENT, исп ольз ующ ее огранич ениеUNIQUE.

CREATE TABLE STUDENT

(STUDENT_ID	INTEGER NOT NULL UNIQUE,
SURNAME	CHAR (25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER,
KURS	INTEGER,
CITY	CHAR (15),

	93
BIRTHDAY	DATE,
UNIV_ID	INTEGER);
О бъяв ляяп олеSTUDENT_ID уника льным, можно бытьув еренным, ч то в
таблице не п ояв ится	з а п исей д ля д в ух студ ентов с од инаков ыми

ид ентификаторами. Столбцы, отлич ныеот п ерв ич ного ключ а, д ля которых требуется п од д ержать уника льность з нач ений, наз ыв а ются в оз можными ключ ами или уникальными ключ ами (CANDIDATE KEYS или UNIQUE KEYS).

4.5.3. Уникал ь ност ь как ограничение т абл иц ы

М ожно сд елатьуникальными груп п у п олей, указ а в UNIQUE в кач еств е

огранич ений т а бли цы . При объед инении п олей			груп п у в а жен п оряд ок, в
котором они ука з ыв а ются.		О гранич ение на	таблицу UNIQUE яв ляется
п олез ным, если	требуется	п од д ержив ать уника льность груп п ы п олей.
Н а п ример, если в	нашей баз ед а нных нед оп ускается, ч тобы студ ент сд а в ал в

од ин д еньбольшеод ного экз амена, то можно в таблицеобъяв итьуника льной комбина цию з нач ений п олей STUDENT_ID и EXAM_DATE. Д ляэтого след ует соз д а тьтаблицу EXAM_MARKS такимсп особом.

CREATE TABLE EXAM_MARKS

(EXAM_ID	INTEGER NOT NULL,
STUDENT_ID	INTEGER NOT NULL,
SUBJ_ID	INTEGER NOT NULL,
MARK	CHAR (1),
EXAM_DATE	DATE NOT NULL,

UNIQUE (STUDENT_ID, EXAM_DATE));

О братитев нима ние, ч то оба п оля в огра нич ении таблицы UNIQUE в се ещ еисп ольз уют огра нич ениестолбца – NOT NULL. Е сли бы исп ольз ов а лось огранич ениестолбца UNIQUE д ляп оля STUDENT_ID, то такоеогранич ение таблицы было бы необяз ательным.

Е сли з нач ения п оляSTUDENT_ID д олжно бытьраз лич ным д ля кажд ой строки в та блице EXAM_MARKS, это можно сд ела ть, объяв ив UNIQUE как огранич ениесамого п оляSTUDENT_ID. В этомслуч аенебуд ет и д в ух строк с ид ентич ной комбина цией з нач ений п олей STUDENT_ID, EXAM_DATE.

След ов а тельно, указ а ние п олез но исп ольз ов а ть в инд ив ид уальных п олей, рассма трив аемомп римере.

UNIQUE	как огра нич ение таблицы		наиболее
случ а ях,	когд а	не требуется уника льность
как это	имеет	место на самом	д еле в

4.5.4. П рисвоение им ен ограничения м

О гра нич ениям та блиц можно п рисв а ив ать уникальные имена.

Преимущ еств о яв ного з ад анияимени огранич ениясостоит в			том, ч то в этом
случ а е п ри в ыд ач е системой	сообщ ения о	нарушении	установ ленного
огранич ениябуд ет указ ано его имя, ч то уп рощ ает обнаружениеошибок.
Д ля п рисв оения имени	огранич ению	исп ольз уется несколько

из мененный синтаксис кома нд CREATE TABLE и ALTER TABLE.

Прив ед енный в ышеп ример з ап роса из меняетсяслед ующ имобраз ом:

CREATE TABLE EXAM_MARKS

(EXAM_ID	INTEGER NOT NULL,
STUDENT_ID	INTEGER NOT NULL,
SUBJ_ID	INTEGER NOT NULL,
MARK	CHAR (1),
EXAM_DATE	DATE NOT NULL,

CONSTRAINT STUD_SUBJ_CONSTR

UNIQUE (STUDENT_ID, EXAM_DATE);

В этом з а п росе STUD_SUBJ_CONSTR – это имя, п рисв оенное указ анному огранич ениюта блицы.

4.5.5. О граничение первичны х кл ю чей

	П ер ви чны е ключи	т а бли цы – это сп ециа льныеслуч аи комбиниров а ния
огранич ений UNIQUE и NOT NULL. Перв ич ные ключ и имеют след ующ ие
особенности:
∙	таблица может сод ержа тьтолько од ин п ер в ич ный ключ ;
∙	в нешниеключ и п о умолч а ниюссыла ютсяна п ерв ич ный ключ та блицы;
∙	п ерв ич ный ключ	яв ляется ид ентификатор ом строк таблицы (строки,

од нако, могут ид ентифицир ов атьсяи д ругими сп особами).

У луч шенный в ар иант соз д ания таблицы STUDENT1 с объяв ленным п ерв ич нымключ омимеет теп ер ьслед ующ ий в ид :

CREATE TABLE STUDENT

(STUDENT_ID	INTEGER PRIMARY KEY,
SURNAME	CHAR (25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER,
KURS	INTEGER,
CITY	CHAR (15),
BIRTHDAY	DATE,
UNIV_ID	INTEGER);

4.5.6. С ост авны е первичны е кл ю чи


О гра нич ение		PRIMARY KEY	может также быть п р именено		д ля
нескольких п олей,		состав ляющ их	уника льную комбинацию з нач ений –
со ст а вно й п ерв ич ный ключ . Рассмотрим таблицу EXAM_MARKS. О ч ев ид но,
ч то ни к п олю	ид ентификатора		студ ента (STUDENT_ID), ни	к	п олю
ид ентификатора	п ред мета обуч ения (EXAM_ID) п о отд ельности			нельз я

п ред ъяв итьтребов аниеуника льности. О д на ко, д лятого, ч тобы в таблицене могли п ояв итьсяраз ныез ап иси д ляод инаков ых комбина ций з на ч ений п олей STUDENT_ID и EXAM_ID (конкретный студ ент на конкр етном э кз аменене

может п олуч итьболее од ной оценки),		имеет смысл объяв итьуника льной
комбина цию этих п олей.	Д ля этого	мы можем п рименить огранич ение
таблицы PRIMARY KEY, объяв ив п ару EXAM_ID и STUDENT_ID п ерв ич ным
ключ омтаблицы:
CREATE TABLE NEW_EXAM_MARKS
(STUDENT_ID	INTEGER NOT NULL,
SUBJ_ID	INTEGER NOT NULL,
MARK	INTEGER,
DATA	DATE,

CONSTRAINT EX_PR_KEY PRIMARY KEY (EXAM_ID, STUDENT_ID));

4.5.7. П роверка з начений пол ей

О гра нич ение CHECK п оз в оляет оп р ед елятьуслов ие, котор ому д олжно уд ов летв орятьв в од имое в п оле та блицы з на ч ение, п режд е ч ем оно буд ет п ринято. Л юба яп оп ытка обнов итьили з а менитьз нач ениеп олятакими, д ля которых п ред икат, з а д ав аемый огранич ением CHECK, имеет з нач ениело ж ь, буд ет отв ергаться.

Рассмотрим таблицу STUDENT. Знач ение столбца STIPEND в этой таблице STUDENT в ыражается д есятич ным ч ислом. Н аложим на з на ч ения этого столбца след ующ ееогранич ение– в елич ина раз мера стип енд ии д олжна бытьменьше200.

Соотв етств ующ ий з ап рос имеет след ующ ий в ид .

CREATE TABLE STUDENT

(STUDENT_ID	INTEGER PRIMARY KEY,
SURNAME	CHAR (25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER CHECK (STIPEND < 200),
KURS	INTEGER,
CITY	CHAR (15),
BIRTHDAY	DATE,
UNIV_ID	INTEGER);

4.5.8. П роверка ограничиваю щ их усл овий с испол ь з ованием сост авны х пол ей

О гра нич ение CHECK можно исп ольз ов ать в кач еств е таблич ного огранич ения, то естьп ри необход имости в ключ итьболееод ного п олястроки в огранич ив а ющ ееуслов ие.

Пред п оложим, ч то огранич ение на раз мер стип енд ии	(меньше 200)
д олжно ра сп ространяться только на студ ентов , жив ущ их в	Воронеже. Э то

можно указ атьв з а п росесо след ующ имтаблич нымогранич ениемCHECK:

	97
CREATE TABLE STUDENT
(STUDENT_ID	INTEGER PRIMARY KEY,
SURNAME	CHAR(25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER,
KURS	INTEGER,
CITY	CHAR(15),
BIRTHDAY	DATE,
UNIV_ID	INTEGER UNIQUE,
CHECK(STIPEND < 200 AND CITY = ‘Во р о н еж ’));
или в несколько д ругой з ап иси
CREATE TABLE STUDENT
(STUDENT_ID	INTEGER PRIMARY KEY,
SURNAME	CHAR(25) NOT NULL,
NAME	CHAR (10) NOT NULL,
STIPEND	INTEGER,
KURS	INTEGER,
CITY	CHAR(15),
BIRTHDAY	DATE,
UNIV_ID	INTEGER UNIQUE,

CONSTRAINT STUD_CHECK CHECK (STIPEND < 200

AND CITY = ‘Во р о н еж ’));

4.5.9. Уст ановка з начений поум ол чанию

В SQL имеетсяв оз можностьп ри в ста в кев таблицу строки, неука з ыв ая з нач ений некоторого п оля, оп ред елятьз нач ениеэтого п оля п о умолч анию. Н а иболееч асто исп ольз уемымз на ч ением п о умолч аниюяв ляетсяNULL. Э то з нач ениеп ринимается п о умолч анию д ля любого столбца, д ля которого не было уста нов лено огранич ениеNOT NULL.

Зна ч ение п оля п о умолч анию ука з ыв ается в кома нд е CREATE TABLE темжесп особом, ч то и огра нич ениестолбца, с п омощ ьюключ ев ого слов а

DEFAULT <зна чени е по ум о лча ни ю>.

Строго гов оря, оп ция DEFAULT не имеет огранич ительного св ойств а, так как она не огра нич ив ает з нач ения, в в од имые в п оле, а п р осто

конкретиз ирует з нач ениеп оляв случ ае, если оно не бы ло за д а но .

Пред п оложим,

ч то основ на я масса

студ ентов , информация о которых

наход ится в таблицеSTUDENT, п рожив ает

Воронеже.

Ч тобы п ри з ад ании

атрибутов

не

в в од ить д ля большинств а студ ентов

наз в а ние город а

‘Во р о н еж ’,

можно установ итьего как з нач ениеп оля CITY п о умолч анию,

оп ред елив таблицу STUDENT след ующ имобраз ом:

CREATE TABLE STUDENT

( STUDENT_ID INTEGER PRIMARY KEY,

SURNAME

CHAR (25) NOT NULL,

NAME

CHAR (10) NOT NULL,

STIPEND

INTEGER CHECK (STIPEND < 200),

KURS

INTEGER,

CITY

CHAR (15) DEFAULT ‘Во р о н еж ’,

BIRTHDAY

DATE,

UNIV_ID

INTEGER);

Д ругаяцельп рактич еского п римененияз ад а нияз нач енияп о умолч а нию

– это исп ольз ов а ниеего как альтернатив ы д ля NULL.

К ак уже отмеч а лось

в ыше, п рисутств иеNULL в кач еств ев оз можных з нач ений п олясущ еств енно

усложняет

интерп ретацию

оп ераций

срав нения, в

которых

уч а ств уют

з нач ениятаких п олей, п оскольку NULL п ред став ляет собой п риз нак того, ч то

фактич ескоез нач ениеп оля неи звест но

или нео пр ед еленно . След ов ательно,

строго

гов ор я,

срав нение с ним любого

конкретного

з на ч ения в рамках

д в уз нач ной

булев ой

логики

яв ляется не корр ектным, з а

исключ ением

сп ециа льной оп ерации сра в нения IS NULL, котор аяоп ред еляет,

яв ляетсяли

сод ержимое

п оля

ка ким-либо

з нач ением

или

оно

отсутств ует.

Д ейств ительно,

каким образом в

рамках

д в уз нач ной

логики отв етить на

в оп рос

истинно

или

ложно

услов ие: CITY =

‘Во р о н еж ’,

если текущ ее

з нач ениеп оляCITY неиз в естно (сод ержит NULL).

NULL

Во

многих

случ аях

исп ольз ов ание

в место

з нач ения,

п од ста в ляемого

п оле п о

умолч анию,

может сущ еств енно

уп ростить

исп ольз ов аниез нач ений п оляв п ред икатах.

Н а п ример,

можно установ итьд ля столбца

оп цию NOT

NULL, а

д ля

неоп ред еленных

з нач ений

ч ислов ого

тип а

уста нов ить

з нач ение

п о

умолч анию“рав но нулю”, или д ляп олей тип а CHAR – п робел, исп ольз ов ание которых в оп ера циях сра в нениянев ыз ыв ает никаких п роблем.

При исп ольз ов а нии з нач ений п о умолч а нию в	п ринцип е д оп устимо
п рименятьогранич енияUNIQUE или PRIMARY KEY в	этом п оле. При этом,

од нако, след ует иметьв в ид у отсутств иев та комогранич ении п ра ктич еского смысла, п оскольку только од на стр ока в таблицесможет п ринятьз нач ение, сов п а д ающ ее с этим з нач ением п о умолч анию. Д ля та кого п рименения з ад а нияп о умолч анию необход имо (д о в ста в ки д ругой строки с уста нов кой п о умолч а нию) мод ифициров ать п ред ыд ущ ую строку, сод ержащ ую такое з нач ение.

УП Р АЖ НЕ НИ Я
1.	Соз д айтетаблицу EXAM_MARKS так, ч тобы нед оп уска лсяв в од в таблицу
	д в ух з а п исей об	оценка х од ного	студ ента п о конкретным экз амену и
	п ред мету обуч ения, а та кже, ч тобы не д оп уска лось п ров ед ение д в ух
	экз аменов п о любымп ред мета мв од ин д ень.
2.	Соз д айтетаблицу п ред метов обуч ения SUBJECT так, ч тобы колич еств о
	отв од имых на	п ред мет ч асов	п о умолч а нию было	рав но	36, не
	д оп уска лисьз ап иси с отсутств ующ им колич еств ом ч асов , п олеSUBJ_ID
	яв лялось п ер в ич ным ключ ом таблицы, и з нач ения семестров				(п оле
	SEMESTR) лежа ли в д иа п аз онеот1-го д о 12-ти.
3.	Соз д айте таблицу EXAM_MARKS та ким образ ом, ч тобы			з нач ения п оля
	EXAM_ID были	больше з на ч ений п оля SUBJ_ID, а		з нач ения п оля
	SUBJ_ID были большез нач ений п оля STUDENT_ID; п устьтакжебуд ут
	з ап рещ ены з нач енияNULL в любомиз этих трех п олей.

100

4.6. П оддерж ка ц ел ост ност и данны х

В та блица х рассма трив аемой баз ы д а нных з на ч ения некотор ых п олей св яз аны д руг с д ругом. Та к п олеSTUDENT_ID в таблицеSTUDENT и п оле STUDENT_ID в таблицеEXAM_MARKS св яз аны тем, ч то оп исыв а ют од ни и те же объекты, то есть сод ержат ид ентифика торы студ ентов , инфор ма ция о


которых хранится в		баз е. Б олеетого, з нач ения ид ентификатор ов студ ентов ,
которыед оп устимы		таблицеEXAM_MARKS,		д олжны в ыбираться только из
сп иска з нач ений STUDENT_ID, фактич ески				п р исутств ующ их в			таблице
STUDENT, то есть п ринад лежа щ их реально оп иса нным в ба з е студ ентам.
А налогич но,	з нач ения п оля UNIV_ID		таблицы			STUDENT	д олжны
соотв етств ов ать ид ентификаторам унив ерситетов UNIV_ID, фактич ески
п рисутств ующ им в		та блице UNIVERSITY,		а	з нач ения п оля SUBJ_ID
таблицы EXAM_MARKS д олжны соотв етств ов атьид ентификатора мп ред метов
обуч ения, фактич ески п рисутств ующ имв таблицеSYBJECT.
О гра нич ения,		наклад ыв а емые указ анным			тип ом	св яз и, наз ыв а ются
о гр а ни чени ям и	ссы ло чно й цело ст но ст и .		О ни		состав ляют в ажную ч а сть
оп исания характеристик п ред метной области,					обесп еч ения корректности

д анных, хранящ ихся в та блица х. К ома нд ы оп иса ния та блиц DML имеют сред ств а, п оз в оляющ ие оп исыв атьогра нич ения ссылоч ной целостности и обесп еч ив атьп од д ержаниетакой целостности п р и манип уляциях з на ч ениями п олей баз ы д анных.

4.6.1. В неш ние и родит ел ь ские кл ю чи

К огд а ка жд ое з нач ение, п р исутств ующ ее од ном п оле та блицы, п ред став лено в д ругомп олед ругой или э той жета блицы, гов орят, ч то п ер в ое п олессылаетсяна в торое. Э то указ ыв ает на п рямуюсв яз ьмежд у з на ч ениями д в ух п олей. Поле, котороессылается на д ругоеп оле, наз ыв ается в неш ним

к лю ч ом , а	п оле, на	которое ссылается д ругое п оле, наз ыв ается
р одит ельск им	к лю ч ом . Так ч то п оле UNIV_ID таблицы STUDENT – это
в нешний ключ	(оно ссылается на п олед ругой таблицы), а п оле UNIV_ID
таблицы UNIVERSITY,		на ко т о р о е ссылается этот в нешний ключ – это
род ительский ключ .

<<< < Предыдущая 1 2 3 4 5 6 7 8 910 / 1610 11 12 13 14 15 16 > Следующая >>>

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

#
22.11.201830.11 Кб6sotsiologia(2).docx
#
21.05.2015485.86 Кб9sotsiologia.rtf
#
19.03.2016267.64 Кб12speckurs.pdf
#
21.05.20155.14 Mб156spez_fiz_pr_zachita.pdf
#
20.05.2015137.2 Кб85SPSE2014.docx
#
21.05.2015917.91 Кб34SQL в вопросах и задачах.pdf
#
21.05.20155.12 Mб12srrf2014web.pdf
#
20.05.2015318.46 Кб8strany_i_stolitsy_blank_dlya_studentov.doc
#
20.05.2015120.59 Кб31Sudebnaya_sistema_v_RF (4).docx
#
20.05.2015126.73 Кб56Sudebnaya_sistema_v_RF+.docx
#
19.03.2016203.58 Кб39Sudebnaya_sistema_v_RF_5 (4).docx