ОРГАНИЗАЦИЯ БАЗ ДАННЫХ И ЗНАНИЙ ТЕМА 5 СТРУКТУРИРОВАННЫЙ ЯЗЫК ЗАПРОСОВ SQL. - презентация

1 ОРГАНИЗАЦИЯ БАЗ ДАННЫХ И ЗНАНИЙ ТЕМА 5 СТРУКТУРИРОВАННЫЙ ЯЗЫК ЗАПРОСОВ SQL

2 СУБПОДРАЗДЕЛЕНИЯ SQL !!! Это не различные языки, а разделы команд SQL сгруппированных по их функциям. Операторы DDL (Data Definition Language) - операторы определения объектов базы данных CREATE SCHEMA - создать схему базы данных DROP SHEMA - удалить схему базы данных CREATE TABLE - создать таблицу ALTER TABLE - изменить таблицу DROP TABLE - удалить таблицу CREATE DOMAIN - создать домен ALTER DOMAIN - изменить домен DROP DOMAIN - удалить домен CREATE COLLATION - создать последовательность DROP COLLATION - удалить последовательность CREATE VIEW - создать представление DROP VIEW - удалить представление

3 Операторы DML (Data Manipulation Language) - операторы манипулирования данными SELECT - отобрать строки из таблиц INSERT - добавить строки в таблицу UPDATE - изменить строки в таблице DELETE - удалить строки в таблице COMMIT - зафиксировать внесенные изменения ROLLBACK - откатить внесенные изменения Операторы защиты и управления данными CREATE ASSERTION - создать ограничение DROP ASSERTION - удалить ограничение GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами REVOKE - отменить привилегии пользователя или приложения Кроме того, есть: группы операторов установки параметров сеанса; получения информации о базе данных; операторы статического SQL; операторы динамического SQL.

4 DDL: Операторы создания схемы базы данных. При описании команд предполагается, что: текст, набранный строчными буквами (например, CREATE TABLE) является обязательным текст, набранный прописными буквами и заключенный в угловые скобки (например, ) обозначает переменную, вводимую пользователем в квадратные скобки (например, [NOT NULL]) заключается необязательная часть команды взаимоисключающие элементы команды разделяются вертикальной чертой (например, [UNIQUE | PRIMARY KEY]). Операторы базы данных Команда Описание CREATE DATABASE Создание базы данных. DROP DATABASE Удаление базы данных.

5 Создание и удаление таблиц Создание таблицы: CREATE TABLE ( [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES [ ]],...) Пользователь обязан указать имя таблицы и список столбцов. Для каждого столбца обязательно указываются его имя и тип, а также опционально могут быть указаны параметры –NOT NULL - в этом случае элементы столбца всегда должны иметь определенное значение (не NULL) –один из взаимоисключающих параметров UNIQUE - значение каждого элемента столбца должно быть уникальным или PRIMARY KEY - столбец является первичным ключом. –REFERNECES [ ] - эта конструкция определяет, что данный столбец является внешним ключом и указывает на ключ какой мастер_таблицы он ссылается. Контроль за выполнением указанных условий осуществляет СУБД.

6 Пример: создание базы данных publications: CREATE DATABASE publications; CREATE TABLE authors (au_id INT PRIMARY KEY, author VARCHAR(25) NOT NULL); CREATE TABLE publishers (pub_id INT PRIMARY KEY, publisher VARCHAR(255) NOT NULL,url VARCHAR(255)); CREATE TABLE titles (title_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, yearpub INT, pub_id INT REFERENCES publishers(pub_id)); CREATE TABLE titleautors (au_id INT REFERENCES authors(au_id), title_id INT REFERENCES titles(title_id)); CREATE TABLE wwwsites (site_id INT PRIMARY KEY, site VARCHAR(255) NOT NULL, url VARCHAR(255)); CREATE TABLE wwwsiteauthors (au_id INT REFERENCES authors(au_id), site_id INT REFERENCES wwwsites(site_id));

7 Удаление таблицы: DROP TABLE Модификация таблицы: Добавить столбцы ALTER TABLE ADD ( [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES [ ]],...) Удалить столбцы ALTER TABLE DROP (,...) Модификация типа столбцов ALTER TABLE MODIFY ( [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES [ ]],...)

8 DDL: Операторы создания индексов. Создание индекса: CREATE [UNIQUE] INDEX ON (,...) Эта команда создает индекс с заданным именем для таблицы по столбцам, входящим в список, указанный в скобках. Индекс часто представляет из себя структуру типа B-дерева, но могут использоваться и другие структуры. Создание индексов значительно ускоряет работу с таблицами. В случае указания необязательного параметра UNIQUE СУБД будет проверять каждое значение индекса на уникальность. Обязательно надо строить индексы для первичных ключей, поскольку по их значениям осуществляется доступ к данным при операциях соединения двух и более таблиц. Какие поля нужно индексировать дает анализ наиболее частых запросов к базе данных. Первоначальное определение структуры индексов производится разработчиком на стадии создания прикладной системы. Уточнение структуры индексов производится администратором системы по результатам анализа ее работы, учета наиболее часто выполняющихся запросов и т.д. Удаление индекса: DROP INDEX

9 Например: Для БД publications можно ожидать, что одним из наиболее частых запросов будет выборка всех публикаций данного автора. Для минимизации времени этого запроса необходимо построить индекс для таблицы authors по именам авторов: CREATE INDEX au_names ON authors (author); Создание индексов для первичных ключей: CREATE INDEX au_index ON authors (au_id); CREATE INDEX title_index ON titles (title_id); CREATE INDEX pub_index ON publishers (pub_id); CREATE INDEX site_index ON wwwsites (site_id);

10 DDL: Операторы управления правами доступа. Для определения прав пользователей относительно объектов базы данных (таблицы, представления, индексы) в SQL определена пара команд GRANT и REVOKE. Синтаксис операции передачи прав на таблицу: GRANT ON [ ] TO Права пользователя на уровне таблицы определяются следующими ключевыми словами (эти ключевые слова совпадают с командами выборки и изменения данных): SELECT - получение информации из таблицы UPDATE - изменение информации в таблице INSERT - добавление записей в таблицу DELETE - удаление записей из таблицы INDEX - индексирование таблицы ALTER - изменение схемы определения таблицы ALL - все права

11 В поле может быть указано либо ключевое слово ALL или любая комбинация других ключевых слов. Отмена прав на таблицу осуществляется командой REVOKE: REVOKE ON [ ] FROM Все ключевые слова данной команды эквивалентны оператору GRANT. Большинство систем поддерживают также команду GRANT для назначения привилегий на базу данных в целом: GRANT ON TO Отмена прав на базу данных осуществляется командой: REVOKE FROM

12 Способы задания прав на базу данных различны для разных СУБД Пример. Список прав на БД, поддерживаемых СУБД Informix: CONNECT - права на доступ к данным и их модификацию, если это разрешено на уровне таблицы; RESOURCE - права на управление ресурсами. Все перечисленное выше плюс права на создание новых объектов (таблиц, индексов и т.д.) и удаление и изменение тех объектов, которыми данный пользователь владеет; DBA - права на администрирование. Все права на управление ресурсами плюс права на удаление базы данных, удаление любых объектов, назначение и отмена прав других пользователей.

13 Пример: предоставить все права на таблицу publishers пользователю andy: GRANT ALL ON publishers TO andy; Предоставить права на извлечение и добавление записей в эту же таблицу publishers пользователю peter : GRANT SELECT INSERT ON publishers TO peter; Предоставить сразу всем пользователям одинаковые права. Используется ключевое слово PUBLIC: GRANT SELECT ON publishers TO PUBLIC;

14 DML: Команды модификации данных. К этой группе относятся операторы добавления, изменения и удаления записей. Добавить новую запись в таблицу: INSERT INTO [ (,,...) ] VALUES (,,..) Список столбцов в данной команде не является обязательным параметром. В этом случае должны быть указаны значения для всех полей таблицы в том порядке, как эти столбцы были перечислены в команде CREATE TABLE, например: INSERT INTO publishers VALUES (16,"Microsoft Press", Пример с указанием списка столбцов: INSERT INTO publishers publisher,pub_id) VALUES ("Super Computer Publishing",17);

15 INSERT - вставка строк в таблицу (одной строки) INSERT INTO P (PNUM, PNAME) VALUES (4, "Иванов"); Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2): INSERT INTO TMP_TABLE (PNUM, PNAME) SELECT PNUM, PNAME FROM P WHERE P.PNUM>2; Номер поставщика PNUM Наименование поставщика PNAME 1Иванов 2Петров 3Сидоров 2; Номер поставщика PNUM Наименование поставщика PNAME 1Иванов 2Петров 3Сидоров">

16 Модификация записей: UPDATE SET =,... [WHERE ] Ключевое слово WHERE применяется только к тем записям, для которых выполняется условие или, если условие не задано, UPDATE применяется ко всем записям. В качестве условия используются логические выражения над константами и полями. В условиях допускаются: операции сравнения: >, =,, !=. В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( " " позже в алфавитном порядке) и датам ( " " позже в хронологическом порядке). операции проверки поля на значение NULL: IS NULL, IS NOT NULL операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN. операции проверки на вхождение в список: IN и NOT IN операции проверки на вхождение подстроки: LIKE и NOT LIKE отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.

17 Примеры: найти в таблице publishers все неопределенные значения столбца url и заменяет их строкой "url not defined". UPDATE publishers SET url="url not defined" WHERE url IS NULL; обновить строки в таблице P UPDATE P SET PNAME = "Пушников" WHERE P.PNUM = 1; Номер поставщика PNUM Наименование поставщика PNAME 1Иванов 2Петров 3Сидоров

18 Удаление записей DELETE FROM [ WHERE ] Удаляются все записи, удовлетворяющие указанному условию. Если ключевое слово WHERE и условие отсутствуют, из таблицы удаляются все записи. Примеры: удалить запись об издательстве Super Computer Publishing. DELETE FROM publishers WHERE publisher = "Super Computer Publishing"; удаление строк в таблице DELETE FROM P WHERE P.PNUM = 1; Удаление всех строк в таблице: DELETE FROM P;

19 DML: Выборка данных С помощью этой команды SQL- извлечения записей из таблиц осуществляется не только операция реляционной алгебры "выборка" (горизонтальное подмножество), но и предварительное соединение (join) двух и более таблиц. SELECT имеет вид: SELECT [ALL | DISTINCT] FROM,... [ WHERE ] [ GROUP BY,...] [ HAVING ] [ORDER BY [ASC | DESC],... ]

20 Сложность оператора SELECT определяется тем, что он содержит в себе все возможности реляционной алгебры, а также дополнительные возможности, которых в реляционной алгебре нет. Порядок предложений в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок. Этот оператор всегда начинается с ключевого слова SELECT. В конструкции определяется столбец или столбцы, включаемые в результат. Он может состоять из имен одного или нескольких столбцов, или из одного символа * (звездочка), определяющего все столбцы. Элементы списка разделяются запятыми.

21 Пример: Отбор данных из одной таблицы Получить список всех авторов SELECT author FROM authors; Получить список всех полей таблицы authors: SELECT * FROM authors; Выбрать все данные из таблицы поставщиков (ключевые слова SELECT… FROM…): SELECT * FROM P; Замечание. В результате получим новую таблицу, содержащую полную копию данных из исходной таблицы P. Номер поставщика Наименование поставщика Город поставщика 1Иванов Уфа 2Петров Москва 3Сидоров Москва 4Сидоров Челябинск Отношение P (Поставщики)

22 Выбрать некоторые колонки из исходной таблицы: SELECT P.NAME FROM P; Замечание. В результате получим таблицу с одной колонкой, содержащую все наименования поставщиков. Замечание. Если в исходной таблице присутствовало несколько поставщиков с разными номерами, но одинаковыми наименованиями, то в результатирующей таблице будут строки с повторениями - дубликаты строк автоматически не отбрасываются. Выбрать некоторые колонки из исходной таблицы, удалив из результата повторяющиеся строки (ключевое слово DISTINCT): SELECT DISTINCT P.NAME FROM P; Замечание. Использование ключевого слова DISTINCT приводит к тому, что в результатирующей таблице будут удалены все повторяющиеся строки. WHERE - предложение команды SELECT, которое позволяет устанавливать предикаты, условие которых может быть или верным или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы для которой такое утверждение верно.

23 Найти все книги, опубликованные после 1996 года: SELECT title FROM titles WHERE yearpub > 1996; Когда предложение WHERE представлено, СУБД просматривает всю таблицу по одной строке и исследует каждую строку чтобы определить верно ли утверждение. Основные булевские операторы AND, OR, и NOT также распознаются в SQL. В дополнении к реляционным и булевским операторам SQL использует специальные операторы IN, BETWEEN, LIKE, и IS NULL. Найти все публикации за интервал гг. Это условие можно записать в виде: SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997; Другой вариант - с использованием логической операции проверки на вхождение в интервал: SELECT title FROM titles WHERE yearpub BETWEEN 1995 AND 1997; При использовании конструкции NOT BETWEEN находятся все строки, не входящие в указанный диапазон. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.

24 Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию: SELECT * FROM P WHERE P.PNUM > 2; Оператор IN определяет набор значений в которое данное значение может или не может быть включено. Примеры: SELECT * FROM Salespeople WHERE city = 'Barcelona' OR city = 'London'; более простой способ получить ту же информацию: SELECT * FROM Salespeople WHERE city IN ( 'Barcelona', 'London' );

25 Использование логической операции проверки на вхождение в список: SELECT title FROM titles WHERE yearpub IN (1995,1996,1997); Список интересующих значений здесь задан в явном виде. Конструкция NOT IN позволяет найти строки, не удовлетворяющие условиям, перечисленным в списке. Преимущества ключевого слова IN наиболее полно проявляются во вложенных запросах, называемых подзапросами. Пример: Найти все издания, выпущенные компанией "Oracle Press". Наименования издательских компаний содержатся в таблице publishers, названия книг в таблице titles. Ключевое слово NOT IN позволяет объединить обе таблицы (без получения общего отношения) и извлечь при этом нужную информацию: SELECT title FROM titles WHERE pub_id IN (SELECT pub_id FROM publishers WHERE publisher='Oracle Press'); При выполнении этой команды СУБД вначале обрабатывает вложенный запрос по таблице publishers, а затем его результат передает на вход основного запроса по таблице titles.

26 Некоторые задачи нельзя решить с использованием только операторов сравнения, например, при осуществлении поиска информации, имея только ориентировочное название. Для решения этой задачи предназначено ключевое слово LIKE, его синтаксис имеет вид: WHERE LIKE [ ESCAPE ] Образец заключается в кавычки и должен содержать шаблон подстроки для поиска. Обычно в шаблонах используются два символа: % (знак процента) - заменяет любое количество символов _ (подчеркивание) - заменяет одиночный символ. Найти web-site издательтва "Wiley: SELECT publiser, url FROM publishers WHERE publisher LIKE '%Wiley%'; В соотвествии с шаблоном СУБД найдет все строки включающие в себя подстроку "Wiley". Замечание:LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки.

27 Найти все книги, название которых начинается со слова "SQL": SELECT title FROM titles WHERE title LIKE 'SQL%'; В том случае, когда надо найти значение, которое само содержит один из символов шаблона, используют ключевое слово ESCAPE и. Литерал, следующий в шаблоне после ключевого символа, рассматривается как обычный символ, все последующие символы имеют обычное значение. надо найти ссылку на web-страницу, о которой известно, что в ее url содержится подстрока "my_works": SELECT site, url FROM wwwsites WHERE url LIKE ESCAPE Замечание: при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT. Ключевое слово ALL указывает, что в результат необходимо включать все строки.

28 Часто бывает, что не для всех полей в записях определены конкретные значения. SQL учитывает такой вариант, позволяя вам вводить значение NULL в поле, вместо значения, что означает специальное маркирование поля, как не имеющее никакого значения для этой строки (или записи). SQL предоставляет специальный оператор IS, который используется с ключевым словом NULL, для размещения значения NULL. Найдем все записи в таблице Поставщиков с NULL значениями в PNAME столбце: SELECT * FROM Р WHERE PNAME IS NULL;