§ 24. Использование SQL для построения запросов
Сайт: | Профильное обучение |
Курс: | Інфарматыка. 10 клас (Павышаны ўзровень) |
Книга: | § 24. Использование SQL для построения запросов |
Напечатано:: | Гость |
Дата: | Четверг, 9 Май 2024, 03:09 |
24.1. Мова запытаў SQL
Майстар запытаў і Канструктар — усяго толькі візуальныя інструменты для стварэння запытаў. Любы запыт, створаны ў Access з дапамогай візуальных інструментаў, можна прагледзець у рэжыме SQL (прыклад 24.1). У гэтым рэжыме запыт адлюстроўваецца як запіс на мове SQL (англ. Structured Query Language — структураваная мова запытаў). SQL з’яўляецца найважнейшым кампанентам тэхналогіі рэляцыйных баз даных і падтрымліваецца практычна ўсімі сістэмамі кіравання БД. Любому прафесіяналу, як кваліфікаванаму карыстальніку баз даных, так і прыкладному праграмісту ці адміністратару БД, неабходна ведаць SQL. Некаторыя магчымасці мовы SQL:
SQL нельга залічыць да традыцыйных моў праграміравання. Ён з’яўляецца дэкларатыўнай мовай, прызначанай для апісання, змянення і вымання даных. У цяперашні час дзякуючы з’яўленню магчымасці захоўваць працэдуры, апрацоўваць іерархічныя тыпы даных і іншым расшырэнням SQL сталі называць мовай праграміравання. Перавагі мовы SQL:
Недахопы мовы SQL:
Сінтаксіс SQL блізкі да натуральнай англійскай мовы. Каманды SQL выглядаюць як звычайныя сказы англійскай мовы і простыя ў разуменні. Каманда SQL складаецца з аператараў (ключавых слоў) і ідэнтыфікатараў (прыклад 24.2). Калі ў запыце некалькі каманд, то ў канцы каманды ставіцца кропка з коскай (;). У Access адначасова можна выканаць толькі адну каманду SQL, таму кропку з коскай можна не ставіць. Паводле агульнапрынятага стылю для запісу ключавых слоў рэкамендуецца выкарыстоўваць вялікія літары. Калі ідэнтыфікатар змяшчае прабелы, то ён ставіцца ў квадратныя дужкі (прыклад 24.3). У Access выкарыстоўваецца інтэрактыўная версія мовы SQL — Microsoft Jet SQL. Каб стварыць запыт SQL у Access (прыклад 24.4), неабходна: 1) перайсці ў меню Создание; 2) выбраць Конструктор запросов; 3) закрыць акно Добавление таблицы; 4) выбраць Режим SQL; 5) у акне, якое адкрыецца, ўвесці тэкст запыту і пры неабходнасці захаваць запыт; 6) выканаць запыт, націснуўшы кнопку на Панели инструментов. У Access рэалізаваны наступныя складовыя часткі мовы SQL:
|
Прыклад 24.1. Напісанне запыту на мове SQL. Табліца БД: Запыт у рэжыме Канструктар, які дазваляе знайсці сеансы, кошт білетаў на якія не перавышае 9 р. 50 к.: Вынік запыту (рэжым табліцы): ЗапытSQL: Мова SQL была прапанавана кампаніяй IBM у пачатку 70-х гг. для праверкі магчымасцей рэляцыйнай мадэлі БД. Распрацоўшчыкі мовы запытаў SQL:
Першы міжнародны стандарт мовы SQL быў прыняты ў 1989 г. У цяперашні час дзейнічае стандарт, прыняты ў 2003 г. (SQL:2003), з некаторымі мадыфікацыямі і дапаўненнямі, занесенымі пазней (SQL:2008, SQL:2011, SQL:2016). SQL з’яўляецца непрацэдурнай мовай праграміравання. Каманды SQL могуць выконвацца непасрэдна ў інтэрактыўным рэжыме, а таксама ўбудоўваюцца ў базавую мову праграміравання, якой можа быць любая стандартная мова, напрыклад C++. Прыклад 24.2. У запыце SQL з 24.1: Аператары SELECT — вызначае палі, якія змяшчаюць запытаныя даныя. FROM — вызначае табліцу, якая змяшчае палі, паказаныя ў SELECT. WHERE — вызначае ўмовы адбору палёў, якім павінны адпавядаць усе запісы, што ўключаюцца ў вынікі. Ідэнтыфікатары палёў Сеансы. Кинотеатр, Сеансы. Фильм, Сеансы. Время, Сеансы. Стоимость — змяшчаюць адрас (імя аб’екта БД) і назва поля. Прыклад 24.3. Сінтаксіс ідэнтыфікатараў SQL. Запыт:
Результат: Прыклад 24.4. . Стварэнне запыту SQL у Access. Выбар рэжыму SQL: Акно для ўводу запыту SQL: |
24.2. Вызначэнне структуры базы даных
З дапамогай аператараў DDL мовы SQL вызначаецца структура БД, г. зн. ствараюцца аб’екты базы даных (прыклад 24.5) і задаюцца іх уласцівасці. Прызначэнне каманд DDL вызначаецца першым словам у запісе каманды:
Сінтаксіс каманды DDL: <OPERATOR><OBJECT> <NAME> [параметры] Сінтаксіс каманды на стварэнне табліцы базы даных: CREATE TABLE <імя_табліцы> Асноўныя тыпы даных, якія выкарыстоўваюцца ў DDL, у параўнанні з тыпамі даных у Access:
Асноўныя абмежаванні SQL для даных у палях табліцы БД:
Спіс абмежаванняў у аператары CREATE TABLE можа адсутнічаць, таму ва ўяўленні сінтаксісу ён ставіцца ў квадратныя дужкі. У прыкладзе 24.6 прыведзены каманды на стварэнне і выдаленне табліцы базы даных. У DDL рэалізавана магчымасць змянення аб’ектаў БД — аператар ALTER. З дапамогай гэтага аператара можна змяняць структуру табліцы, дабаўляючы ці выдаляючы палі, змяняць тып поля, дабаўляць ці выдаляць абмежаванні (прыклад 24.7). Сінтаксіс каманды на змяненне табліцы БД: ALTER TABLE <імя_табліцы> <дзеянні па змяненні> |
Прыклад 24.5. Некаторыя з аб’ектаў у DDL.
Прыклад 24.6. Стварэнне і выдаленне табліцы базы даных з дапамогай запытаў SQL. 1. Стварэнне:
Каманда стварае табліцу «Сеансы в мае» наступнай структуры: Палі табліцы:
2. Выдаленне: DROP TABLE [Сеансы в мае] Прыклад 24.7. Выкарыстанне аператара ALTER для змянення табліцы БД. 1. Дабаўленне поля:
У табліцу «Сеансы в мае» будзе дабаўлена поле «Наличие билетов» з тыпам даных «Короткий текст». Даўжыня тэксту абмежавана значэннем па змоўчанні (255 сімвалаў). 2. Абнаўленне поля:
Для поля «Наличие билетов» табліцы «Сеансы в мае» будзе зменена абмежаванне па даўжыні тэксту (устаноўлена абмежаванне 10 сімвалаў). 3. Выдаленне поля:
Поле «Наличие билетов» будзе выдалена з табліцы «Сеансы в мае». |
24.3. Маніпуліраванне данымі ў табліцы БД
Мова маніпулірявання данымі DML уключае аператары, якія змяняюць стан даных. Гэтымі аператарамі з’яўляюцца:
INSERT Сінтаксіс аператара INSERT: INSERT INTO <імя табліцы> У раздзеле VALUES значэнні палёў павінны быць паказаны ў тым парадку, у якім палі задаваліся пры стварэнні табліцы. Спіс слупкоў можа адсутнічаць. Калі спіс не паказаны ў аператары, то гэта азначае, што спіс значэнняў павінен быць поўным, г. зн. змяшчаць значэнні для ўсіх палёў табліцы (прыклад 24.8). У Access адначасова можна дабавіць у табліцу толькі адзін запіс. SELECT Аператар SELECT — адзін з найбольш важных і выкарыстоўваемых аператараў DML, які мае найбольш складаную структуру сярод усіх аператараў. Ён дазваляе выбраць даныя з БД і ператвараць да патрэбнага віду атрыманыя вынікі. Вынікам выканання аператара SELECT заўсёды з’яўляецца табліца. Сінтаксіс аператара SELECT:
У прыкладах 24.9—24.13 разгледжаны розныя варыянты выкарыстання аператара SELECT у адносінах да табліцы «Сеансы» (прыклад 24.1). У якасці ўмовы выбаркі ў раздзеле WHERE аператара SELECT можна выкарыстоўваць лагічныя выразы, якія выкарыстоўваюць палі табліц, канстанты, знакі параўнання, злучнікі AND і OR, адмаўленне NOT.
Выкарыстанне розных умоў для выбаркі даных разгледжана ў прыкладзе 24.10. У якасці крытэрыя сартавання ў раздзеле ORDER BY аператара SELECT паказваюцца імёны палёў, па якіх патрабуецца ўпарадкаваць запісы ў табліцы. Сартаванне можна выконваць па некалькіх палях. Прынцып работы такога сартавання аналагічны шматузроўневаму сартаванню ў Excel. Спачатку запісы размяшчаюцца ў тым парадку, які вызначаны для першага поля. Парадак, вызначаны для наступнага поля, упарадкоўвае толькі тыя запісы, якія маюць аднолькавыя значэнні ў першым полі. Далей сартаванне ўжываецца толькі да тых запісаў, якія маюць аднолькавыя значэнні як у першым полі, так і ў другім. Па змоўчанні запісы ў табліцы сартуюцца па ўзрастанні (ASC), для сартавання ў адваротным парадку неабходна дабавіць ключавое слова DESC (прыклад 24.11). Пры складанні запытаў пры дапамозе аператара SELECT для атрымання некаторай статыстычнай інфармацыі (сумы, колькасці, сярэдняга значэння і г. д.) выкарыстоўваюцца агрэгіруючыя функцыі. Гэта функцыі, якія працуюць з наборам даных і вяртаюць выніковае значэнне. Асноўныя з іх:
Усе гэтыя функцыі вяртаюць адзінае значэнне. Пры гэтым функцыі COUNT, MIN і MAX выкарыстоўваюцца ў дачыненні да даных любога тыпу, а SUM і AVG выкарыстоўваюцца толькі для даных лікавага тыпу. Ужываць агрэгіруючыя функцыі можна ў дачыненні да ўсіх радкоў табліцы (прыклад 24.12), радкоў, вызначаным у раздзеле WHERE (прыклад 24.13) ці да груп радкоў. У адным запыце можна паказваць некалькі агрэгіруючых функцый, запісаўшы іх праз коску. Сінтаксіс агрэгіруючых функцый: <Функцыя>(<выраз>) У якасці аргумента агрэгіруючай функцыі часцей за ўсё выкарыстоўваецца назва поля табліцы, над значэннямі якога праводзяцца вылічэнні. Таксама дапускаецца ў якасці аргумента выкарыстоўваць выразы, якія ўключаюць у сябе адвольную камбінацыю назваў палёў, канстант, функцый, аб’яднаных арыфметычнымі аперацыямі. У прыкладзе 24.14 разгледжана ўжыванне агрэгіруючых функцый COUNT, SUM і AVG у аператары SELECT у адносінах да табліцы «Сеансы в мае»: У раздзеле GROUP BY аператара SELECT запісваецца імя поля, па якім павінна ажыццяўляцца групоўка даных. У гэтым выпадку значэнне агрэгіруючай функцыі вылічваецца для кожнай групы (прыклад 24.15). Вынікі запытаў можна выкарыстоўваць у іншых запытах — атрымліваецца ўкладзены запыт (прыклад 24.16). UPDATE Сінтаксіс аператара UPDATE: З дапамогай аднаго аператара UPDATE могуць быць зададзены значэнні для любой колькасці слупкоў. Аднак у адным і тым жа аператары UPDATE можна ўносіць змяненні ў кожны слупок паказанай табліцы толькі адзін раз. Пры адсутнасці раздзела WHERE будуць абноўлены ўсе радкі табліцы. У прыкладзе 24.17 прыведзены запыт на абнаўленне з указаннем у раздзеле WHERE, якія даныя трэба абнавіць. DELETE Выкарыстоўваецца для выдалення запісаў з табліц БД (прыклад 24.18). Сінтаксіс аператара DELETE: Калі раздзел WHERE адсутнічае, выдаляюцца ўсе радкі з табліцы. |
Прыклад 24.8. Дабаўленне даных у табліцу.
Значэнне радка ў гэтым выпадку можна ўводзіць без двукосся, паколькі ў радку няма прабелаў: VALUES (1, Джентельмены, '21:00', 8.50) Вынік: Прыклад 24.9. Запыт на выбар усіх даных з табліцы. SELECT * FROM Сеансы Вынік: Прыклад 24.10. Запыты на выбар даных, якія задавальняюць некаторую ўмову. 1. Выбраць даныя з палёў «Фильм», «Время», «Стоимость» табліцы «Сеансы» для запісаў, у якіх кошт білета меншы за 10 р.
Вынік: 2. Выбраць даныя з палёў «Кинотеатр», «Фильм», «Время», «Стоимость» табліцы «Сеансы» для запісаў, у якіх кошт білета ад 9 да 10 10 р.
Вынік: 3. Выбраць даныя з палёў «Фильм», «Время», «Стоимость» табліцы «Сеансы» для запісаў, у якіх значэнне поля «Кинотеатр» супадае са значэннем 'Аврора'.
Вынік: 4. Выбраць даныя з палёў «Фильм», «Время», «Стоимость» табліцы «Сеансы» для запісаў, у якіх значэнне поля «Кинотеатр» заканчваецца на 'р'.
Вынік: Прыклад 24.11. Сартаванне даных у табліцы. 1. Выконваць сартаванне даных у табліцы «Сеансы» па полі «Время» ў парадку ўзрастання.
Вынік: 2. Выканаць сартаванне даных у табліцы «Сеансы» па полі «Кинотеатр» у парадку спадання, а па полі «Время» — у парадку ўзрастання.
Вынік: Прыклад 24.12. Ужыванне агрэгіруючых функцый да вызначаных радкоў табліцы ў запытах на выбарку. Знайсці ў табліцы «Сеансы» самы позні сеанс з коштам квітка ад 6 да 9 р.
Вынік: Прыклад 24.13. Ужыванне агрэгіруючых функцый да вызначаных радкоў табліцы ў запытах на выбарку. Знайсці ў табліцы «Сеансы» самы позні сеанс з коштам квітка ад 6 да 9 р.
Вынік: Прыклад 24.14. Выкарыстанне агрэгіруючых функцый у запытах на выбарку. 1. SUM. Знайсці ў табліцы «Сеансы в мае» сумарны кошт білетаў.
Вынік: 2. AVG. Знайсці ў табліцы «Сеансы в мае» сярэдні кошт білетаў з двума лічбамі пасля коскі.
Результат: Прыклад 24.15. Выкарыстанне агрэгіруючых функцый у дачыненні да груп радкоў у запытах на выбарку. Знайсці ў табліцы «Сеансы в мае» сумарную колькасць білетаў для кожнага фільма.
Вынік: Прыклад 24.16. Укладзены запыт на выбарку.
Запыт вяртае даныя з табліцы «Туры» пра самы танны тур у Нарвегію. Прыклад 24.17. Запыты на абнаўленне табліцы «Сеансы в мае».
У выніку выканання запыту колькасць білетаў на кожны з сеансаў у кінатэатры «Мир» паменшыцца на 5. Прыклад 24.18. Запыт на выдаленне даных з табліцы «Сеансы в мае».
|
Пытанні да параграфу
1. Як стварыць запыт у Access, не выкарыстоўваючы магчымасцей візуальных інструментаў стварэння запытаў? 2. Якія магчымасці ў рабоце з БД маюцца ў мове SQL? 3. Якія складовыя часткі можна вылучыць у мове SQL? 4. Які сінтаксіс напісання ідэнтыфікатараў, калі ў іх прысутнічаюць прабелы? 5. Для чаго прызначаны аператары мовы DDL? 6. Які аператар дазваляе ствараць табліцы БД? 7. З якімі тыпамі даных працуюць аператары мовы DDL? 8. У чым розніца паміж тыпам даных CHAR і VARCHAR? 9. Якія абмежаванні для даных у палях табліцы БД могуць быць запісаны ў запыце SQL? 10. Для чаго прызначаны аператары мовы БД? 11. З дапамогай якога аператара можна дабаўляць у табліцу БД запісы? 12. Выкарыстанне якога аператара дазваляе зрабіць выбарку даных з БД? 13. Які знак выкарыстоўваецца, калі ў запыце SQL мяркуецца вывад даных са ўсіх палёў табліцы? 14. Якая памылка дапушчана ў запыце? SELECT Дата, Имя from Заказы WHERE Имя = Никита; 15. Што будзе вынікам запыту? SELECT * FROM Заказы WHERE Дата BETWEEN '2019-01-01' AND '2019-12-31' 16. У якім раздзеле запыту SQL задаюцца крытэрыі сартавання запісаў? 17. Якім чынам сартуюцца даныя, калі крытэрый сартавання не вызначаны? 18. Як называюцца функцыі, якія працуюць з наборам даных і вяртаюць у запыце SELECT выніковыя значэнні? 19. Якім чынам паказваецца ў запыце SELECT набор даных, з якім працуюць агрэгіруючыя функцыі? 20. Ці магчыма выкарыстанне некалькіх агрэгіруючых функцый у адным запыце? |
Практыкаванні
1. Стварыце базу даных «Туры.accdb».
- Стварыце новую пустую базу даных і ўвядзіце яе імя.
- Стварыце табліцу базы даных з дапамогай SQL-запыту.
- Увядзіце наступную каманду для стварэння табліцы:
CREATE TABLE Туры (
Код INTEGER NOT NULL PRIMARY KEY,
Страна VARCHAR(50) NOT NULL,
Транспорт VARCHAR(20) NOT NULL,
Цена MONEY NOT NULL)
4. Выканайце гэту каманду. Праверце, што табліца «Туры» сапраўды створана.
2. Дабаўце ў табліцу «Туры» запісы (па адным):
INSERT INTO Туры
VALUES (1, 'Австрия', 'автобус', 800);
INSERT INTO Туры
VALUES (2, 'Норвегия', 'самолет', 1500);
Аналагічна дабаўце ў табліцу яшчэ некалькі запісаў:
Код |
Страна |
Транспорт |
Цена |
3 |
Швеция |
паром |
900 р. |
4 |
Германия |
автобус |
700 р. |
5 |
Греция |
самолет |
1200 р. |
6 |
Норвегия |
автобус |
800 р. |
7 |
Германия |
самолет |
1500 р. |
3. Выканайце і захавайце запыты на выбарку даных з табліцы «Туры»:
1. Адлюструйце ўсе даныя табліцы:
SELECT * FROM Туры
Паглядзіце на вынік.
2. Для ўсіх запісаў адлюструйце толькі палі «Страна» і «Цена»:
SELECT Страна, Цена FROM Туры
3. Адлюструйце запісы, якія змяшчаюць даныя пра туры ў Нарвегію:
SELECT * FROM Туры WHERE Страна LIKE Норвегия
4. Складзіце, выканайце і захавайце запыты на выбар з табліцы «Туры»:
1. Значэнне палёў «Страна», «Транспорт» і «Цена» для ўсіх аўтобусных тураў.
2. Складзіце і выканайце запыт, які выбірае з табліцы «Туры» значэнні ўсіх палёў для тураў з цаной менш за 1000 р.
5. Праверце работу запыту:
SELECT * FROM Туры ORDER BY Цена
Дабаўце ў канцы папярэдняга запыту ключавое слова DESC. Што змянілася? Захавайце вынік.
6. Складзіце, выканайце і захавайце запыты на выбар з табліцы «Туры»:
1. Значэнні ўсіх палёў для тураў c коштам больш за 1000 р. і сартаваннем вынікаў па спаданні цаны.
2. Мінімальнай цаны для тураў у Нарвегію.
3. Тура з максімальнай цаной на самалёт.
7. Складзіце, выканайце і захавайце запыты для табліцы «Туры»:
1. Павелічэнне цаны ўсіх тураў на 10 %.
2. Рэалізацыя зніжкі на білеты так, што цэны ўсіх тураў на самалётах складаюць 80 % ад зыходных.
8. Скапіруйце табліцу «Туры», назваўшы копію «Туры2». Складзіце і выканайце запыт на выдаленне ўсіх тураў у Германію. Праверце, што даныя ў табліцы «Туры2» сапраўды змяніліся. Выдаліце табліцу «Туры2», якая больш не патрэбна, з дапамогай запыту.
9. Адкрыйце базу даных «Арэнда аўтамабіляў.accdb». Складзіце, выканайце і захавайце запыты.
- Выбраць усе аўтамабілі ў парадку года выпуску. Для тых, у якіх год выпуску аднолькавы — па спаданні кошту арэнды.
- Знайсці аўтамабілі з аб’ёмам рухавіка >3.
- Знайсці аўтамабілі чырвонага колеру.
- Знайсці аўтамабілі з аб’ёмам рухавіка =2 і арэндай не больш за 300 р.
- Вывесці інфармацыю пра аўтамабілі, год выпуску якіх паміж 2000 і 2010.
- Вывесці ўсіх арандатараў, якія ужывуць на вуліцы Панамарова.
- Для кожнага аўтамабіля вызначыць кошт тэхагляду, які складае 70 % ад кошту арэнды.
- Выкарыстоўваючы групоўку, атрымаць выніковую суму арэнды для кожнай групы аўтамабіляў з аднолькавым відам паліва.
- Знайсці мінімальны, максімальны і сярэдні кошт арэнды для аўтамабіляў з аднолькавым тыпам кузава.
10. Выканайце запыт на пошук усіх узятых у арэнду аўтамабіляў.
SELECT *
FROM Автомобили
WHERE Код IN (SELECT ‘Автомобили_Код’ FROM Аренда);
11. Знайдзіце ўсіх арандатараў, якія не бралі ні адну машыну ў арэнду.