SQL в вопросах и задачах
.pdf11
Из этих св ойств отношенияв ытекаютслед ующ иев ажныеслед ств ия.
∙ Из уникальности кортежей след ует, ч то в отношении всегд а имеется атр ибут или на бор атрибутов , п оз в оляющ их и д ент и ф и ци р о ва т ь кортеж, д ругими слов ами отношении в сегда естьп ер в ич ный ключ .
∙Из неуп оряд оч енности кортежей след ует, в о-п ерв ых, ч то в отношении не сущ еств ует д ругого сп особа ад ресации кортежей, кроме ад ресации по
ключу, в о-в торых, ч то в отношении не сущ еств ует таких п онятий как п ерв ый кортеж, п ослед ний, п ред ыд ущ ий, след ующ ий и т.д .
∙ Из неуп оряд оч енности атрибутов след ует, ч то ед инств енным сп особом их ад ресации в з ап роса х яв ляетсяисп ольз ов а ниена именов а нияатр ибута.
О тносительно св ойств а реляционного отношения, каса ющ егося отсутств иякортежей-д убликатов , след ует сд елатьв а жноез а меч ание. В этом п унктеSQL неп олностью соотв етств ует реляционной мод ели. А именно, в отношениях, яв ляющ ихся рез ульта тами з ап росов , SQL д о пуска ет на лич ие од инаков ых строк. Д ля их устранения в з а п росе исп ольз уется ключ ев ое слов о DISTINCT (см. ниже).
Информация в реляционных баз а х д а нных, ка к п ра в ило, хранится нев
од ной таблице-отношении, а в |
нескольких. При соз д ании нескольких таблиц |
в з аимосв яз а нной информации |
п ояв ляется в оз можность в ып олнения более |
сложных оп ераций с д анными, то естьболеесложной обработки д анных. Д ля работы со св яз анными д анными из нескольких таблиц в ажным яв ляется п онятиетак наз ыв аемых внеш ни х ключей.
Внеш ни м ключо м таблицы наз ыв аетсяатрибут или набор атрибутов этой таблицы, кажд ое з нач ение которых в текущ ем состоянии таблицы в сегд а сов п а д ает со з нач ением атрибутов , яв ляющ ихся ключ ом, в д ругой таблице. Внешниеключ и исп ольз уютсяд лясв яз ыв а нияз нач ений атрибутов из раз ных
таблиц. С п омощ ью в нешних ключ ей |
обесп еч ив ается |
так наз ыв аемая |
ссылоч на я целостность баз ы д анных, то |
есть согласов |
а нность д а нных, |
оп исыв а ющ их од ни и тежеобъекты, но хр анящ ихсяв раз ных таблица х.
12
1.2.О т л ичие SQL от проц едурны х я зы ков програм м ирования
Я з ык SQL |
относится к |
кла ссу |
неп роцед урных |
яз ыков |
п рогра ммиров ания. |
В отлич ие от |
унив ерсальных п роцед урных |
яз ыков , |
которыетакжемогут бытьисп ольз ов аны д ляработы с ба з ами д анных, яз ык SQL ориентиров а н нена за пи си , а на м но ж ест в а .
Э то оз нач ает |
след ующ ее. |
В кач еств е |
в ход ной информации |
д ля |
формулируемого на |
яз ыке SQL |
з а п роса к |
баз е д анных исп ольз |
уется |
м но ж ест в о ко р т еж ей-за пи сей од ной или нескольких таблиц-отношений. В рез ультате в ып олнения з ап роса также образ уется м но ж ест во ко р т еж ей рез ультирующ ей таблицы-отношения. Д ругими слов а ми, в SQL р ез ультатом любой оп ера ции на д отношениями такжеяв ляется отношение. Зап рос SQL з ад а ет неп р оцед уру, то естьп ослед ов ательностьд ейств ий, необход имых д ля п олуч ения рез ульта та, а услов ия, которым д олжны уд ов летв орятькортежи рез ультирующ его отношения, сформулиров а нныев термина х в ход ного (или в ход ных) отношений.
1.3. И нт еракт ивны й и вст роенны й SQL
Сущ еств уют и исп ольз уются д в е формы яз ыка SQL: и нт ер а кт и вны й
SQL и вст р о енны йSQL.
Инт ер а кт и вный SQL исп ольз уется д ля неп осред ств енного в в од а SQL- з ап росов п ольз ов ателеми п олуч ениярез ультата в интер актив номрежиме.
Вст р о енны й SQL состоит из команд SQL, в стр оенных в нутрьп рограмм, которыеобыч но на п иса ны на некотором д ругом яз ыке(Па ска ль, С, С++ и д р.). Э то д елает п рогра ммы, на п иса нныена та ких яз ыках, болеемощ ными,
гибкими и эффектив ными, обесп еч ив а я их |
п р именение д ля работы с |
|
д анными, хранящ имися в реляционных баз ах. |
Пр и этом, од нако, |
требуются |
д оп олнительныесред ств а обесп еч енияинтер фейса SQL с яз ыком, в который |
||
он в страив ается. |
|
|
Д анна я книга п осв ящ ена интера ктив ному SQL, п оэ тому |
ней не |
обсужд аютсяв оп росы п остроенияинтерфейса, п оз в оляющ его св яз атьSQL с д ругими яз ыка ми п рограммиров а ния.
13
1.4.С оставны е част и SQL
Иинтерактив ный, и в строенный SQL п од раз д еляются на след ующ ие соста в ныеч асти.
Язы к О пр ед елени я Д а нны х – |
DDL (Data Definition |
Language), |
д ает |
|
в оз можность соз д ания, из менения и уд аления раз лич ных |
объектов |
баз ы |
||
д анных (та блиц, инд ексов , п ольз ов а телей, п рив илегий и т.д .). |
|
|
||
К |
ч ислу д оп олнительных функций яз ыка оп ред еления д а нных |
DDL |
||
могут |
быть в ключ ены сред ств а |
оп ред еления огранич ений целостности |
д анных, |
оп ред еления |
п оряд ка |
структур |
хранения |
д анных, |
оп исания |
|
элементов физ ич еского уров няхра ненияд анных. |
|
|
|
||||
Язы к |
О бр а бо т ки |
Д а нны х |
– DML |
(Data |
Manipulation |
Language), |
|
п ред остав ляет в оз можность в ыборки информации |
из |
ба з ы д а нных и ее |
|||||
п реобра зов а ния. |
|
|
|
|
|
|
Темнеменее, э то нед в а р аз лич ных яз ыка , а комп оненты ед иного SQL.
14
1.5.Т ипы данны х SQL
Вяз ыке SQL имеются сред ств а, п оз в оляющ ие д ля кажд ого атрибута указ ыв атьтип д а нных, которому д олжны соотв етств ов атьв сез нач енияэ того атрибута.
След ует отметить, ч то оп ред елениетип ов д а нных яв ляетсятой ч а стью, в которой коммерч еские реализ ации яз ыка не п олностью согла суются с требов аниями официального ста нд арта SQL. Э то объясняется, в ч а стности, желаниемсд елатьSQL сов местимымс д ругими яз ыка ми п рогра ммиров ания.
1.5.1. Т ип данны х “ст рока сим вол ов”
Станд арт п од д ержив ает только од ин тип д ля п ред ста в ления текста :
CHARACTER(CHAR). Э тот |
тип д анных п ред став ляет собой симв ольные |
строки фиксиров а нной д лины. Е го синтаксис имеет в ид : |
|
CHARACTER [(д ли на )] |
или |
CHAR [(д ли на )].
Текстов ыез нач ения п оля таблицы, д ля которого оп ред елен тип CHAR, имеют ф и кси р о ва нную д лину, котораяоп ред еляетсяп араметромд ли на . Э тот п араметр может п риниматьз нач ения от 1 д о 255, то естьстрока может сод ержатьд о 255 симв олов . Е сли в о в в од имой в п олетекстов ой константе фактич еское ч исло симв олов меньше ч исла, оп ред еленного п ар аметром
д ли на , то эта константа ав томатич ески д оп олняется сп ра в а |
п робелами д о |
з ад а нного ч исла симв олов . |
|
Н екоторые реализ ации яз ыка SQL п од д ер жив а ют |
кач еств е тип а |
д анных строки п еременной д лины. Э тот тип может обоз нач а тьсяключ ев ыми слов а ми VARCHAR(), CHARACTER VARYING или CHAR VARYING(). О н оп исыв ает текстов ую строку, которая может иметьпр о и зво льную д лину д о
оп ред еленного конкретной реализ а цией SQL максимума (в |
Oracle д о 2000 |
симв олов ). В отлич ие от тип а CHAR в этом случ ае п ри |
в в од е текстов ой |
конста нты, фактич ескаяд лина которой меньшез ад анной, неп роиз в од итсяее д оп олненияп робелами д о з ад а нного максима льного з на ч ения.
К онстанты, имеющ иетип CHARACTER и VARCHAR, в в ыр ажениях SQL з аключ а ютсяв од иноч ныека в ыч ки, на п ример ‘т екст ’.
15
След ующ иеп ред ложенияэ кв ив алентны:
VARCHAR[(д ли на )], CHAR VARYING[(д ли на )],
CHARACTER VARYING[(д ли на )]
Е сли |
д лина строки не указ ана |
яв но, она п ола гается рав ной од ному |
|
симв олу в о в сех случ аях. |
|
|
|
По ср ав нению с тип ом CHAR тип д а нных VARCHAR п оз в оляет более |
|||
экономно |
исп ольз ов ать п амять, в ыд еляемую д ля хранения |
текстов ых |
|
з нач ений, |
и оказ ыв ается более |
уд обным п ри в ып олнении |
оп ераций |
св яз анных со срав нениемтекстов ых констант.
1.5.2.Числ овы е т ипы данны х
|
Станд артными ч ислов ыми тип ами д анных SQL яв ляются: |
|
∙ |
INTEGER – исп ольз уетсяд ляп ред став ленияцелых ч исел |
д иап аз онеот |
|
–231 д о +231. |
|
∙ |
SMOLLINT – исп ольз уется д ля п р ед ста в ления целых ч исел |
д иа п а з оне |
|
меньшем, ч емд ляINTEGER, а именно – от –215 д о +215. |
|
∙DECIMAL (т о чно ст ь[,м а сш т а б]) – д есятич ноеч исло с фиксиров а нной
точ кой, точ ность ука з ыв ает, сколько з на ч ащ их цифр имеет ч исло. М а сштаб указ ыв ает максима льноеч исло цифр сп рав а от точ ки
∙NUMERIC (т о чно ст ь[,м а сш т а б]) – д есятич ноеч исло с фиксир ов а нной точ кой, такоеже, как и DECIMAL.
∙ FLOAT [(т о чно ст ь)] – ч исло с п ла в а ющ ей точ кой и указ анной минимальной точ ностью.
∙REAL – ч исло такое же, как п р и тип еFLOAT, з а исключ ением того, ч то
точ ностьустана в лив ается п о умолч а нию в |
з ав исимости от конкр етной |
|||
реа лиз ации SQL. |
|
|
|
|
∙ DOUBLE PRECISION – ч исло та кое же, ка к и REAL, но точ ностьв |
д в а |
|||
раз а п рев ышает точ ностьд ляREAL. |
|
д а нных NUMBER |
|
|
СУ Б Д Oracle исп ольз ует |
д оп олнительно |
тип |
д ля |
|
п ред став ления в сех ч ислов ых |
д анных, целых, |
с фиксиров а нной |
или |
16
п ла в а ющ ей точ кой. Е го синтаксис:
NUMBER [(т о чно ст ь[, м а сш т а б])].
Е сли з нач ение п ара метра т о чно ст ь не указ ано яв но, |
оно |
п олагается |
||||||
рав ным 38. |
Знач ение п ара метра |
м а сш т а б п о умолч анию п ред п олагается |
||||||
рав ным 0. |
Знач ение п ара метра |
т о чно ст ь |
может из меняться от 1 д о 38; |
|||||
з нач ение п а раметра |
м а сш т а б |
может |
из меняться |
от |
–84 |
д о |
128. |
|
Исп ольз ов а ние отрицательных |
з нач ений ма сштаба |
оз нач ает |
сд в иг |
|||||
д есятич ной |
точ ки в |
сторону старших раз ряд ов . Н ап р имер, оп ред еление |
||||||
NUMBER (7, –3) оз на ч ает округлениед о тысяч . |
|
|
|
|
Тип ы DECIMAL и NUMERIC п олностьюэкв ив а лентны тип у NUMBER.
Синта ксис: DECIMAL [(т о чно ст ь[, м а сш т а б])], DEC [(т о чно ст ь[, м а сш т а б])], NUMERIC [(т о чно ст ь[, м а сш т а б])].
1.5.3. |
Дат а и врем я |
Тип |
д а нных, п ред наз нач енный д ля п ред став ления д а т ы и вр ем ени , |
такжеяв ляется неста нд артным, хотяи ч рез в ыч а йно п олез ным. Поэтому д ля точ ного в ыяснения того, какие тип ы д а нных п од д ержив ает конкретная СУ Б Д , след ует обра щ а тьсяк еед окумента ции.
В СУ Б Д Oracle имеетсятип DATE, исп ольз уемый д ляхра ненияд аты и в ремени. Под д ержив аютсяд аты, на ч инаяот 1 янв а ря4712 г. д о н.э. и д о 31 д екабря4712 г. При оп ред елении д аты без уточ ненияв ремени п о умолч а нию п ринимаетсяв ремяп олуноч и.
Н а лич ие тип а |
д а нных |
д ля |
хр анения д аты |
и в ремени |
п оз в оляет |
|
п од д ержив ать сп ециа льную |
а рифметику |
д ат и |
в ремен. Д оба в ление к |
|||
п еременной тип а |
DATE целого |
ч исла |
оз нач ает ув елич ение |
д аты на |
соотв етств ующ ееч исло д ней, а в ыч ита ниесоотв етств ует оп ред елениюболее ранней д аты.
К онстанты тип а DATE з ап исыв а ются в з ав исимости от формата , п ринятого оп ера ционной системе. Н а п ример ‘03.05.1999’ или ‘12/06/1989’,
или ‘03-nov-1999’, или ‘03-apr-99’.
17
1.5.4. Неопредел енны е ил и пропущ енны е данны е (NULL)
Д ля |
обоз на ч ения отсутств ующ их, п роп ущ енных или неиз в естных |
||
з нач ений |
атрибута в SQL исп ольз уется ключ ев ое слов о |
NULL. Д ов ольно |
|
ч асто можно в стретитьслов осоч ета ние “а т р и бут и м еет |
зна чени е NULL”. |
||
Строго гов оря, NULL не яв ляется з нач ением |
обыч ном п онимании, а |
||
исп ольз уется именно д ля обоз нач ения того |
факта, ч то |
д ейств ительное |
з нач ениеатрибута на са момд елеп роп ущ ено или неиз в естно. Э то п рив од ит к ряд у особенностей, ч то след ует уч итыв ать п ри исп ольз ов ании з нач ений атрибутов , которыемогут наход итьсяв состоянии NULL.
∙ |
В агрегирующ их функциях, п оз в оляющ их п олуч атьсв од нуюинформацию |
||||||
|
п о множеств у |
з нач ений атрибута , на п ример, сумма р ное или сред нее |
|||||
|
з нач ение, |
д ля |
обесп еч ения точ ности и |
од ноз на ч ности |
толков а ния |
||
|
р ез ультатов отсутств ующ иеили NULL-з на ч енияатрибутов игнорируются. |
||||||
∙ |
У слов ные оп ераторы от булев ой д в уз на ч ной логики true/false |
||||||
|
р асширяютсяд о трехз нач ной логики true/false/unknown. |
|
|||||
∙ |
Все оп ераторы, |
з а |
исключ ением оп ера тора |
конкатенации стр ок “ || ”, |
|||
|
в оз в ра щ а ют п устоез нач ение(NULL), если з нач ениелюбого из |
оп ер анд ов |
|||||
|
отсутств ует (имеет “з на ч ениеNULL”). |
|
|
||||
∙ |
Д ля п ров ерки |
на |
п устое з нач ение след ует исп ольз ов ать оп ераторы |
||||
|
IS NULL |
и IS |
NOT NULL |
(исп ольз ов ание д ля этого |
оп ератора |
||
|
срав нения“= ” яв ляетсяошибкой). |
|
|
||||
∙ |
Функции |
п реобра з ов ания тип ов , |
имеющ ие NULL в кач еств е а ргумента , |
||||
|
в оз в ра щ ают п устоез нач ение(NULL). |
|
|
1.6. И спол ь з уем ы е т ерм ины и обоз начения
Ключев ые сло ва – это исп ольз уемыев в ыражениях SQL слов а, имеющ ие сп ециа льное наз нач ение (на п ример, они могут обоз на ч ать конкретные
команд ы SQL). К люч ев ые слов а нельз я исп ольз ов а тьд ля д ругих |
целей, к |
|
п римеру, в кач еств еимен объектов |
баз ы д а нных. В книгеони в ыд еляются |
|
шрифтом: КЛЮЧЕВОЕСЛОВО. |
|
|
Ко м а нд ы , или пр ед ло ж ени я, |
яв ляются инструкциями, с |
п омощ ью |
18
которых SQL обращ ается к баз е д анных. К оманд ы состоят из од ной или более логич еских ч астей, наз ыв аемых п ред ложениями. Пред ложения нач ина ютсяключ ев ымслов оми состоят из ключ ев ых слов и аргументов .
О бъекты баз ы д а нных, имеющ ие имена (таблицы, атрибуты и д р.), в книгета кжев ыд еляютсяособымобраз ом: ТАБЛИЦА1, АТРИБУТ_2.
В оп исании синтаксиса кома нд |
SQL оп ератор оп ред еления “::=” |
||
раз д еляет оп ред еляемый элемент (слев а от оп ератора) |
и собств енно его |
||
оп ред еление (сп ра в а от оп ера тора); |
кв а д ратные скобки |
“[ ]” указ ыв ают |
|
нео бяза т ельны й элемент |
синта ксич еской констр укции; |
многоточ ие “...” |
|
указ ыв ает, ч то в ыражение, |
п ред шеств ующ ееему, может п ов тор яться любое |
ч исло раз ; фигурныескобки “{ }”объед иняют п ослед ов ательностьэлементов
в ло ги ческую |
гр уппу, од ин из э лементов котор ой д олжно бытьобяз ательно |
|
исп ольз ов ан; |
в ертикальна я ч ерта “|” ука з ыв ает, |
ч то ч а сть оп ред еления, |
след ующ а я з а |
этим симв олом, яв ляется од ним из |
в оз можных в ариантов ; в |
углов ыескобки “< >” з аключ а ютсяэ лементы, которыеобъясняютсяп о мере того, как в в од ятся.
19
1.7. Учебная баз а данны х
В п рив од имых в п особии п римера х контрольных уп ражнениеях исп ольз уется след ующ их таблиц.
Таблица 1.1. STUDENT (Студент)
п остроения SQL-з ап росов |
и |
баз а д анных, состоящ ая |
из |
STUDENT_ID |
SURNAME |
NAME |
STIPEND |
KURS |
CITY |
BIRTHDAY |
UNIV_ID |
1 |
Ив а нов |
Ив а н |
150 |
1 |
О рел |
3/12/1982 |
10 |
3 |
Петров |
Петр |
200 |
3 |
К урск |
1/12/1980 |
10 |
6 |
Сид оров |
Ва дим |
150 |
4 |
Москв а |
7/06/1979 |
22 |
10 |
К уз нецов |
Б орис |
0 |
2 |
Б рянск |
8/12/1981 |
10 |
12 |
Зайцев а |
О льга |
250 |
2 |
Л ип ецк |
1/05/1981 |
10 |
265 |
Пав лов |
А нд рей |
0 |
3 |
Воронеж |
5/11/1979 |
10 |
32 |
К отов |
Па в ел |
150 |
5 |
Б елгор од |
NULL |
14 |
654 |
Л укин |
А ртем |
200 |
3 |
Воронеж |
1/12/1981 |
10 |
276 |
Петров |
А нтон |
200 |
4 |
NULL |
5/08/1981 |
22 |
55 |
Б елкин |
Ва дим |
250 |
5 |
Воронеж |
7/01/1980 |
10 |
… … |
… … |
… … |
… … |
… … |
… … |
… … |
… … |
STUDENT_ID – числовой код, идентифицирующий студента,
SURNAME – фамилия студента, NAME – имя студента,
STIPEND – стипендия, которую получает студент, KURS – курс, на котором учится студент,
CITY – город, в котором живет студент, BIRTHDAY – дата рождения студента,
UNIV_ID – числовой код, идентифицирующий университет, в
котором учится студент.
Таблица 1.2. LECTURER (Преподаватель)
LECTURER_ID |
SURNAME |
|
NAME |
CITY |
UNIV_ID |
|
24 |
К олесников |
|
Б орис |
Вор онеж |
10 |
|
46 |
Н иконов |
|
Ив ан |
Вор онеж |
10 |
|
74 |
Л агутин |
|
Пав ел |
М оскв а |
22 |
|
108 |
Струков |
|
Н иколай |
М оскв а |
22 |
|
276 |
Н иколае |
|
Виктор |
Вор онеж |
10 |
|
328 |
Сорокин |
|
А нд рей |
О рел |
10 |
|
… … |
… … |
|
… … |
… … |
… … |
|
LECTURER_ID |
– |
числовой |
код, |
идентифицирующий |
преподавателя,
20
SURNAME – фамилия преподавателя, NAME – имя преподавателя,
CITY – город, в котором живет преподаватель,
UNIV_ID – идентификатор университета, в котором работает
преподаватель.
Таблица 1.3. |
SUBJECT (Предмет обучения) |
|||
SUBJ_ID |
|
SUBJ_NAME |
HOUR |
SEMESTER |
10 |
|
Информатика |
56 |
1 |
22 |
|
Физ ика |
34 |
1 |
43 |
|
М атематика |
56 |
2 |
56 |
|
История |
34 |
4 |
94 |
|
А нглийский |
56 |
3 |
73 |
|
Физ культура |
34 |
5 |
… … |
|
… … |
… … |
… … |
SUBJ_ID – идентификатор предмета обучения,
SUBJ_NAME – наименование предмета обучения,
HOUR – количество часов, отводимых на изучение предмета, SEMESTER – семестр, в котором изучается данный предмет.
Таблица 1.4. UNIVERSITY (Университеты)
UNIV_ID |
UNIV_NAME |
RATING |
CITY |
22 |
М Г У |
606 |
М оскв а |
10 |
ВГ У |
296 |
Воронеж |
11 |
Н Г У |
345 |
Н ов осибирск |
32 |
РГ У |
416 |
Ростов |
14 |
Б Г У |
326 |
Б елгор од |
15 |
ТГ У |
368 |
Томск |
18 |
ВГ МА |
327 |
Воронеж |
… … |
… … |
… … |
… … |
UNIV_ID – идентификатор университета, UNIV_NAME – название университета,
RATING – рейтинг университета,
CITY – город, в котором расположен университет.