I.7. Език за структурирани заявки SQL — Разширена писмена разработка

Разширена писмена разработка. I.7. Език за структурирани заявки SQL

Подробен авторски текст за подготовка по конспекта на СУ. Краткият вариант е запазен на основната страница на темата. За преговор използвай плана; за обосновка — определенията и разработените примери. Това не е официален изпитен образец.

Кратка разработка и уроци по темата · План за изпита · Покритие · Източници

Обхват от конспекта

Език за структурирани заявки SQL. SQL операции.

Покритие на конспекта

Всяка подточка води към съответния раздел на разработката.

  1. Език за структурирани заявки SQL — 1. Предназначение и характер на SQL
  2. SQL операции — 2. Дефиниране на данни и ограничения; 3. Добавяне, изменение и изтриване; 4. Извличане, съединения и подзаявки; 5. Групиране, агрегиране и логическа обработка; 6. Повторения, NULL, права и транзакции

Кратък план за изпита

  1. SQL като декларативен език; отношение към релационния модел.
  2. DDL: таблици, типове и ограничения.
  3. DML: добавяне, промяна и изтриване.
  4. SELECT, филтриране, съединения и подзаявки.
  5. Групиране, агрегиране, повторения, NULL и сортиране.
  6. Транзакции, права и заключение.

1. Предназначение и характер на SQL

SQL е език за дефиниране, извличане и изменение на данни в релационни системи, както и за управление на права и транзакции. При декларативна заявка описваме желания резултат, а не задължително реда на физическите операции. Оптимизаторът може да избере индекс, обхождане и различен ред на съединенията, ако запази семантиката.

SQL е свързан с релационната алгебра, но не съвпада с чистия множествен модел. Резултатите обичайно допускат повторени редове, NULL изисква тризначна логика, а ORDER BY добавя подредба. Има стандарт и множество диалекти; конкретните типове, автоматични идентификатори, процедури и някои транзакционни свойства зависят от СУБД. По-долу примерът използва широко разпространено ядро на SQL.

2. Дефиниране на данни и ограничения

DDL операциите създават, променят и премахват обекти на схемата: CREATE, ALTER и DROP. Типът описва допустимото представяне на стойностите, а ограниченията изразяват допълнителни правила. PRIMARY KEY идентифицира реда и не допуска NULL в ключа; UNIQUE задава уникалност с зависещо от системата отношение към NULL; NOT NULL изисква стойност; CHECK проверява условие; FOREIGN KEY задава референтна цялостност.

CREATE TABLE Course (
    Code VARCHAR(10) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Hours INTEGER NOT NULL CHECK (Hours BETWEEN 1 AND 120)
);
CREATE TABLE Learner (
    Id INTEGER PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);
CREATE TABLE Enrollment (
    LearnerId INTEGER NOT NULL REFERENCES Learner(Id),
    CourseCode VARCHAR(10) NOT NULL REFERENCES Course(Code),
    Grade INTEGER CHECK (Grade BETWEEN 2 AND 6),
    PRIMARY KEY (LearnerId, CourseCode)
);

Тук един курсист може да има няколко записвания, но не и две за същия курс. Оценката може да е неизвестна, затова Grade допуска NULL. CHECK сам по себе си обикновено не отхвърля UNKNOWN; необходимостта от задължителна стойност се задава отделно с NOT NULL. При изтриване на родителски ред поведението трябва да е избрано съзнателно — отказ, каскадно изтриване или друго разрешено действие.

3. Добавяне, изменение и изтриване

INSERT добавя редове, UPDATE променя избрани редове, а DELETE ги премахва. Условието WHERE определя кои редове се засягат; липсата му при UPDATE или DELETE означава всички редове. Примерът започва от празни таблици, създадени по-горе:

INSERT INTO Learner VALUES (1, 'Ana'), (2, 'Boris');
INSERT INTO Course VALUES ('K1', 'SQL', 30), ('K2', 'ER', 12);
INSERT INTO Enrollment VALUES (1, 'K1', 6), (1, 'K2', NULL), (2, 'K1', 4);
UPDATE Enrollment SET Grade = 5
WHERE LearnerId = 2 AND CourseCode = 'K1';

След UPDATE оценката на Boris по K1 е 5; останалите редове не се променят. Добавяне на записване с несъществуващ курс трябва да бъде отхвърлено при активно проверяван външен ключ. DELETE премахва данни, докато DROP TABLE премахва обекта на схемата. Тези операции не са взаимозаменяеми.

4. Извличане, съединения и подзаявки

SELECT задава изходните изрази, FROM — източниците, а WHERE — условието за редовете. INNER JOIN комбинира съвпадащи редове по зададено условие. LEFT JOIN пази всички редове от левия източник и допълва несъвпадащите с NULL за десните колони. Условие върху десен атрибут, поставено в WHERE, може да отстрани тези допълнени редове и да промени смисъла на външното съединение.

SELECT l.Name, c.Title, e.Grade
FROM Learner AS l
JOIN Enrollment AS e ON e.LearnerId = l.Id
JOIN Course AS c ON c.Code = e.CourseCode
WHERE e.Grade >= 5
ORDER BY l.Name, c.Code;

За състоянието след UPDATE резултатът съдържа Ana–SQL–6 и Boris–SQL–5. Записването за ER не минава условието, защото сравнението на NULL с 5 не е TRUE. Примерът показва съединяване по ключови препратки и филтриране по факт за конкретно записване.

Подзаявката е заявка, вложена в друга. EXISTS проверява дали тя връща поне един ред; корелирана подзаявка използва стойност от текущ ред на външната. Например курсистите с поне едно записване се намират с WHERE EXISTS (SELECT 1 FROM Enrollment e WHERE e.LearnerId=l.Id). За липса на записване използваме NOT EXISTS. NOT IN има допълнителни особености при NULL в подзаявката и не трябва механично да се замества с отрицание на очаквано двузначно сравнение.

5. Групиране, агрегиране и логическа обработка

Агрегатните функции обобщават редове: COUNT брои, SUM сумира, AVG намира средно, MIN и MAX — крайни стойности. COUNT(*) брои редовете, а COUNT(Grade) брои оценките, които не са NULL. SUM и AVG обичайно игнорират NULL. Ако всички стойности са NULL или входът е празен, резултатът за тези агрегати не е автоматично нула; COUNT има отделно поведение.

SELECT c.Code, COUNT(e.LearnerId) AS Learners,
       COUNT(e.Grade) AS Graded, AVG(CAST(e.Grade AS DECIMAL(10,2))) AS AverageGrade
FROM Course AS c
LEFT JOIN Enrollment AS e ON e.CourseCode = c.Code
GROUP BY c.Code
HAVING COUNT(e.LearnerId) >= 1
ORDER BY c.Code;

За K1 има двама записани и две оценки със средно 5.5; за K2 — един записан, нула известни оценки и NULL за средната. След LEFT JOIN използваме COUNT(e.LearnerId), за да не преброим допълнения ред като реално записване при курс без курсисти. WHERE филтрира преди групиране, а HAVING — групите след него.

Полезният логически ред е FROM/JOIN, WHERE, GROUP BY, HAVING, изходните изрази на SELECT, DISTINCT и ORDER BY. Това е модел за смисъла на заявката, не обещание за физическия план. Неключова колона, която не участва в групирането и не е агрегирана, обикновено не може произволно да се извежда; допустимите изключения зависят от поддържаните правила за функционални зависимости.

6. Повторения, NULL, права и транзакции

SELECT без DISTINCT запазва възможните повторения. UNION премахва дубликати, а UNION ALL ги пази; за множествените операции се изискват съвместими изходни колони. INTERSECT и EXCEPT имат аналогичен смисъл на сечение и разлика там, където диалектът ги поддържа. Редът на резултата не е гарантиран без ORDER BY; за устойчив ред при равенство трябва да се добави критерий, който разрешава равенствата.

NULL обозначава липсваща или неприложима информация според модела. Проверяваме го с IS NULL, а не с = NULL. Логическите резултати са TRUE, FALSE и UNKNOWN; WHERE запазва само TRUE. COALESCE избира първата стойност, която не е NULL, но замяната на неизвестна оценка с нула би променила смисъла на средното и не трябва да се прави само за удобство.

COMMIT потвърждава транзакция, а ROLLBACK отменя непотвърдените промени в поддържания обхват. GRANT и REVOKE управляват права. Ролите и минимално необходимите права ограничават достъпа; параметризираните заявки отделят данните от SQL текста и предотвратяват типични SQL инжекции. Това не замества проверката на бизнес правилата.

В заключение SQL обхваща целия жизнен цикъл на данните. Добрият изпитен отговор показва връзката между схема, ограничения, промени и заявки и обяснява резултата върху конкретни редове. Особено важни са разграниченията WHERE/HAVING, INNER/OUTER JOIN, COUNT(*)/COUNT(колона), NULL/стойност и множество/мултимножество.

Литературна опора и граници на сверяването

Официална библиография на СУ — номерата по-долу са от нея.

  • СУ 11 — Garcia-Molina, Ullman, Widom, Database Systems: The Complete Book, 2. изд., 2008, пълен текст в проекта: §2.3 (DDL), §6.1–6.6 (заявки, изменения и транзакции), §7.1–7.3 (ключове и ограничения), §10.1 (права). Основното четене по SQL е глава 6, печатни с. 244–306 / PDF 281–343. Диалектните уточнения са отделени в текста; не всички примери от книгата са универсален синтаксис за всяка СУБД.
  • СУ 7 (Азълов, 1991) и СУ 12 (Modern Database Management, 2018) са допълнителни официални библиографски ориентири, но техни пълни текстове не са сверявани. SQL примерите тук са авторски.

Текстът и примерите са авторска учебна разработка, а не официален отговор или указание на изпитна комисия на СУ. Номерацията на разделите в плана е авторска. Препратките показват опората за понятията, а не че примерите са преписани от учебник.

Обратно към кратката разработка и уроците