SQL Лекция 3. DDL (Data definition language). Язык определения данных (DDL) является частью SQL, дающей пользователю возможность создавать различные объекты. - презентация

1 SQL Лекция 3

2 DDL (Data definition language). Язык определения данных (DDL) является частью SQL, дающей пользователю возможность создавать различные объекты базы данных и переопределять их структуру, например, создавать или удалять таблицы. CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX ALTER INDEX DROP INDEX CREATE VIEW ALTER VIEW DROP VIEW CREATE PROCEDURE DROP PROCEDURE

3 Пример (Таблица создание): Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу таблицу без строк: CREATE TABLE ( [( )], [( )] ….); Порядок столбцов в таблице определяется порядком, в котором они указаны. Имя столбца не должно разделяться при переносе строки, но отделяется запятыми. CREATE TABLE PEOPLE (ID NUMBER, NM VARCHAR2(50), FAMIL VARCHAR2(50), OTCH VARCHAR2(50), DROG DATE ) /

4 Пример (Таблица заполнение): Заполняем ее тремя записями: INSERT INTO PEOPLE(ID, NM, FAMIL, OTCH, DROG) VALUES(1, 'John', 'Godwin', 'Petrovich', TO_DATE(' ','DD-MM-YYYY')) / INSERT INTO PEOPLE(ID, NM, FAMIL, OTCH, DROG) VALUES(2, 'Bob', 'Doris', 'Martovich', TO_DATE(' ','DD-MM-YYYY')) / INSERT INTO PEOPLE(ID, NM, FAMIL, OTCH, DROG) VALUES(3, 'Frank', 'Black', 'Milleniumich', TO_DATE(' ','DD-MM-YYYY')) / COMMIT /

5 Пример(таблица просмотр): CREATE TABLE NEWPEOPLE ( NM VARCHAR2(50), FAMIL VARCHAR2(50), OTCH VARCHAR2(50) ) / COMMIT / Итак, новая табличка создана с применением оператора DDL - CREATE TABLE. Убедиться можно с помощью запроса: SELECT a.OBJECT_NAME, a.OBJECT_TYPE, a.STATUS FROM USER_OBJECTS a WHERE a.OBJECT_TYPE = 'TABLE' / Получаем: OBJECT_NAME OBJECT_TYPE STATUS CUSTOMERS TABLE VALID NEWPEOPLE TABLE VALID OFFICES TABLE VALID ORDERS TABLE VALID PEOPLE TABLE VALID PRODUCTS TABLE VALID SALESREPS TABLE VALID

6 Пример(таблица изменение): Команда ALTER TABLE не часть стандарта ANSI; но это широко доступная, и довольно содержательная форма. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис, чтобы добавить столбец к таблице: ALTER TABLE ADD/DROP ; ALTER TABLE "NEWPEOPLE" ADD "BloodGroup" number;

7 Пример(таблица удаление): Для удаления таблиц воспользуемся оператором языка DDL - DROP TABLE: DROP TABLE PEOPLE / DROP TABLE NEWPEOPLE / COMMIT / После ввода в SQL*Plus получаем: SQL> DROP TABLE PEOPLE 2 / Таблица удалена. SQL> DROP TABLE NEWPEOPLE 2 / Таблица удалена. SQL> COMMIT 2 / Фиксация обновлений завершена. С оператором DROP TABLE необходимо обращаться осторожно, так же помнить о возможности воспользоваться откатом транзакции, оператор ROLLBACK

8 Пример Индексы: Индекс это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое количество строк, а, так как строки не находятся в каком-нибудь определенном порядке, их поиск по указанному значению может потребовать значительного времени. Когда вы создаете индекс в поле, ваша база данных запоминает соответствующий порядок всех значений этого поля в области памяти. CREATE INDEX ON ( [, ]...); Таблица, конечно, должна уже быть создана и должна содержать имя столбца. Имя индекса не может быть использовано для чего-то другого в базе данных (любым пользователем). Однажды созданный, индекс будет невидим пользователю. Сервер SQL сам решает, когда он необходим, чтобы ссылаться на него, и делает это автоматически. CREATE INDEX Clientgroup ON PEOPLE (ID); Этот синтаксис используется для удаления индекса: DROP INDEX ;

9 DML (Data Manipulation Language). DML - Data Manipulation Language. Язык манипулирования данными. Используется для работы с информацией, хранимой в базе данных. Основными командами этой группы являются: Select - вычитка информации. Insert - добавление информации. Update - обновление информации. Delete - удаление информации.

10 Пример INSERT : INSERT INTO [(,,...) VALUES (,,..) Список столбцов в данной команде не является обязательным параметром. В этом случае должны быть указаны значения для всех полей таблицы в том порядке, как эти столбцы были перечислены в команде CREATE TABLE, например: INSERT INTO PEOPLE VALUES(1, 'John', 'Godwin', 'Petrovich', TO_DATE(' ','DD-MM-YYYY')) / С перечислением необходимых столбцов: INSERT INTO PEOPLE(ID, NM, FAMIL, DROG) VALUES(2, 'Bob', 'Doris', TO_DATE(' ','DD-MM- YYYY')) /

11 Пример UPDATE : UPDATE SET =,... [WHERE ] Если задано ключевое слово WHERE и условие, то команда UPDATE применяется только к тем записям, для которых оно выполняется. Если условие не задано, UPDATE применяется ко всем записям. Пример: UPDATE PEOPLE SET DROG = SYSDATE - 1 WHERE ID < 17; В качестве условия используются логические выражения над константами и полями. В условиях допускаются: операции сравнения: >, =,, !=. В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( " " позже в алфавитном порядке) и датам ( " " позже в хронологическом порядке). оперции проверки поля на значение NULL: IS NULL, IS NOT NULL операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN. операции проверки на вхождение в список: IN и NOT IN операции проверки на вхождение подстроки: LIKE и NOT LIKE отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.

12 Пример DELETE : DELETE FROM [ WHERE ] Удаляются все записи, удовлетворяющие указанному условию. Если ключевое слово WHERE и условие отстутствуют, из таблицы удаляются все записи. Пример: DELETE FROM PEOPLE WHERE ID < 17 and DROG = SYSDATE - 1 ;

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

14 Пример SELECT (продолжение): SELECT * FROM authors; SELECT title FROM titles WHERE yearpub > 1996; SELECT title FROM titles WHERE yearpub>=1995 AND yearpub

15 SELECT из нескольких таблиц. Очень часто возникает ситуация, когда выборку данных надо производить из отношения, которое является результатом слияния (join) двух других отношений. Например, нам нужно получить из базы данных publications информацию о всех печатных изданиях в виде следующей таблицы: |название_книги | год_выпуска | издательство | Для этого СУБД предварительно должна выполнить слияние таблиц titles и publishers, а только затем произвести выборку из полученного отношения. Для выполнения операции такого рода в операторе SELECT после ключевого слова FROM указывается список таблиц, по которым произвоится поиск данных. После ключевого слова WHERE указывается условие, по которому производится слияние. Для того, чтобы выполнить данный запрос, нужно дать команду: SELECT titles.title,titles.yearpub,publishers.publisher FROM titles,publishers WHERE titles.pub_id=publishers.pub_id;

16 SELECT из нескольких таблиц. А вот пример, где одновременно задаются условия и слияния, и выборки (результат предыдущего запроса ограничивается изданиями после 1996 года): SELECT titles.title,titles.yearpub,publishers.publisher FROM titles,publishers WHERE titles.pub_id=publishers.pub_id AND titles.yearpub>1996 Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка).

17 Вычисления внутри SELECT.. SQL позволяет выполнять различные арифметические операции над столбцами результирующего отношения. В конструкции можно использовать константы, функции и их комбинации с арифметическими операциями и скобками. Например, чтобы узнать сколько лет прошло с 1992 года (год принятия стандарта SQL-92) до публикации той или иной книги можно выполнить команду: SELECT title, yearpub-1992 FROM titles WHERE yearpub > 1992; В арифметических вражения допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS - абсолютное значение и т.д.). Также в запрос можно добавить строковую константу: SELECT 'the title of the book is', title, yearpub-1992 FROM titles WHERE yearpub > 1992;

18 Вычисления внутри SELECT.. В SQL также определены так называемые агрегатные функции, которые совершают действия над совокупностью одинаковых полей в группе записей. Среди них: AVG( ) - среднее по всем значениям данного поля COUNT( ) или COUNT (*) - число записей MAX( ) - максимальное из всех значений данного поля MIN( ) - минимальное из всех значений данного поля SUM( ) - сумма всех значений данного поля Следует учитывать, что каждая агрегирующая функция возвращает единственное значение. Примеры: определить дату публикации самой "древней" книги в нашей базе данных SELECT MIN(yearpub) FROM titles; подсчитать количество книг в нашей базе данных: SELECT COUNT(*) FROM titles;

19 Групировка данных. Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы. GROUP BY неразрывно связано с агрегирующими функциями, без них оно практически не используется. GROUP BY разделяет таблицу на группы, а агрегирующая функция вычисляет для каждой из них итоговое значение. Определим для примера количество книг каждего издательства в нашей базе данных: SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher; Kлючевое слово HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING. Например, устраним из предыдущего запроса те издательства, которые имеют только одну книгу: SELECT publishers.publisher, count(titles.title) FROM titles,publishers WHERE titles.pub_id=publishers.pub_id GROUP BY publisher HAVING COUNT(*)>1;

20 Cортировка данных. Для сортировки данных, получаемых при помощи оператора SELECT служит ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в. Данные могут быть упорядочены как по возрастанию, так и по убыванию. Пример: сортировать список авторов по алфавиту: SELECT author FROM authors ORDER BY author; Более сложный пример: получить список авторов, отсортированный по алфавиту, и список их публикаций, причем для каждого автора список книг сортируется по времени издания в обратном порядке (т.е. сначала более "свежие" книги, затем все более "древние"): SELECT authors.author,titles.title,titles.yearpub,publishers.publisher FROM authors,titles,publishers,titleauthors WHERE titleauthors.au_id=authors.au_id AND titleauthors.title_id=titles.title_id AND titles.pub_id=publishers.pub_id ORDER BY authors.author ASC, titles.yearpub DESC; Ключевое слово DESC задает здесь обратный порядок сортировки по полю yearpub, ключевое слов ASC (его можно опускать) - прямой порядок сортировки по полю author.

21