Структура языка sql

См. содержание работы ...

Введение 2
1. История развития SQL 3
2. Язык структурированных запросов SQL 5
2.1 Таблицы SQL 6
2.2 Структура языка SQL 7
2.3 Структура запросов SQL 8
2.3 Оператор SELECT 10
2.4 Операторы модификации данных 20
2.4.1 Оператор INSERT 20
2.4.2 Оператор UPDATE 25
2.4.3 Оператор DELETE 27
2.5 Транзакции в SQL 28
2.6 Защита данных 30
2.7 Обработка ошибок 32
Заключение 34
Список использованных источников 36
Приложение А 37

В современном мире объемы информации, которые необходимы для ведения практически любой деятельности, все время возрастают. Поэтому для работой с большими объемами информации необходимы инструменты, специально разработанные не только для хранения данных, но также и обладающие возможностями быстрого поиска необходимых данных, возможностями увеличения объемов хранимой информации и изменения уже хранимой. Наиболее простым, удобным и рациональным способом хранения информации, на основе опыта последних десятилетий бурного развития информационных технологий, был признан способ хранения информации в виде баз данных.
База данных – это, прежде всего, хранилище объектов определенных данных, т.е. набора всевозможных понятий или событий, которые описывает данная база данных (БД). Вместе с этим в основ ные функции баз данных входит систематизация информации, а также возможность связывания между собой объектов БД, имеющих логические связи.
Строго говоря, данные любой структуры можно путем несложных манипуляций представить в виде простой двумерной таблицы. Основная идея реляционного подхода как раз и состоит в этом. То есть в том, чтобы представить произвольную структуру данных в виде простой двумерной таблицы или, как принято говорить, нормализовать структуру. И, как следствие развития реляционного подхода к базам данных, возникла острая необходимость использования универсального языка для доступа и управления данными в различных БД.

Рис. 4 — Результат выполнения запроса (3)Следует отметить, что вертикальная выборка, не содержащая потенциального ключа, однозначное определяющего запись таблицы, может содержать дубликаты строк. В таблице PC таким потенциальным ключом является поле code, которое назначено в качестве первичного ключа данной таблицы. Так как это поле не было включено в запрос, то в результирующем наборе, который приведен на рисунке 4, имеются дубликаты строк (например, первая и третья строки). Если требуется получить уникальные, неповторяющиеся строки (например, необходимо получить только различные комбинации цен и объемов жесткого диска компьютера, а не данные характеристики абсолютно всех компьютеров, которые хранятся в базе данных), то можно использовать ключевое слово DISTINCT:SELECT DISTINCT price, hdFROM Pc; (4)Результат запроса представлен на рисунке 5.Рис. 5 — Результат выполнения запроса (4)Также, помимо DISTINCT иногда применяется ключевое слово ALL (все строки). Значение ALL является значением по умолчанию.Для упорядочивания строк результирующего набора, можно выполнить сортировку выводимых записей по одному или нескольким полям, указанным в предложении оператора SELECT. Для сортировки результирующего набора используется предложение ORDER BY , в операторе SELECT оно всегда указывается в конце запроса. При этом в списке полей, по которым следует сделать сортировку, могут указываться не только имена полей, но и номер их порядковой позиции в списке предложения SELECT. Например, если нужно упорядочить результирующий набор данных по объему жесткого диска в порядке убывания, то запрос можно записать двумя способами:SELECT DISTINCT price, hdFROM PcORDER BY hd DESC; (5)ИлиSELECT DISTINCT price, hdFROM PcORDER BY 2 DESC; (6)Результат выполнения каждого из этих запросов будет одни и тем же, он представлен на рисунке 6.Рис. 6 — Результат выполнения запросов (5) или (6)Кроме того, сортировку результирующей таблицы можно производить и по возрастанию (параметр ASC, принимается по умолчанию), и по убыванию (параметр DESC), если в операторе ORDER BY указать 2 поля. Например, можно упорядочить результирующую таблицу по убыванию размера жесткого диска, а в случае совпадения значений – по возрастанию цены компьютера.SELECT DISTINCT price, hdFROM PcORDER BY hd DESC, price ASC; (7)Результат выполнения запроса представлен на рисунке 7.Рис. 7 — Результат выполнения запроса (7)Горизонтальная выборка записей реализуется с помощью предложения WHERE, которое записывается после предложения FROM оператора SELECT. После ключевого слова WHERE идет логическое выражение, и в выборку попадут те записи таблицы, для которых значение логического выражения TRUE.Логические выражения могут состоять как из одного выражения, так из неограниченного количества выражений, соединенных между собой булевскими операторами AND (и), OR (или), NOT (не). Кроме того, в комбинациях логических выражений может также использоваться SQL-оператор IS (есть), а также круглые скобки для более конкретного определения порядка выполнения операций логических выражений [3, с.209].Логическое выражение в языке SQL может принимать только одно из трех возможных значений: TRUE (истина), FALSE (ложь) или UNKNOWN (неизвестно). Исключением являются следующие предикаты: NULL (отсутствие значения), EXISTS (существование), UNIQUE (уникальность) и MATCH (совпадение), которые могут принимать только значения TRUE и FALSE.Чтобы было легче запомнить правила комбинирования всех трех истинностных значений, обозначают TRUE как 1, FALSE как 0 и UNKNOWN как 1/2 (где то между истинным и ложным). Тогда: AND, соединяющий два истинностных значения, дает минимум этих значений. Например, логическое выражение TRUE AND UNKNOWN будет равно UNKNOWN.OR, соединяющий два истинностных значения, дает максимум этих значений. Например, логическое выражение FALSE OR UNKNOWN будет равно UNKNOWN.Отрицание какого-либо истинностного значения равно 1 минус данное истинностное значение. Например, логическое выражение NOT UNKNOWN будет равно UNKNOWN.Помимо этого, в SQL используются предикаты сравнения.Предикат сравнения представляет собой два выражения, которые соединены оператором сравнения. В SQL, как и во многих язках программирования, применяются шесть традиционных операторов сравнения: =, >, <, >=, <=, <>.Данные типа NUMERIC, DECIMAL, INT (дробные, целые, положительные и отрицательные числа) сравниваются в соответствии с их алгебраическим значением.Данные типа VARCHAR (символьные строки) сравниваются в соответствии с их последовательностью в алфавите (чем ближе к началу алфавита расположен символ, тем он меньше, т.е. в русском алфавите буква «а» меньше всех остальных букв). Строки, которые состоят из нескольких символах сравниваются по следующим правилам. Например, если a1a2…an и b1b2…bn - две последовательности произвольных символов, то первая будет "меньше" второй, если а1<b1, или а1=b1 и а2<b2 и т.д. Считается также, что а1а2…аn будет "меньше" b1b2…bm, если n<m и а1а2…аn=b1b2…bn, то есть если первая строка – это префикс второй. Например, 'brought'>'bred', потому что первых два символа этих строк совпадают, а третий символ строки ''bred' предшествует третьему символу строки 'brought'. Также будет справедливым неравенство 'bar' < 'barber', потому что первая строка - это префикс второй.Данные временного типа DATETIME (дата/время) сравниваются в хронологическом порядке.Предположим, что необходимо получить информацию о компьютерах, имеющих объем оперативной памяти не менее 64 МБ и цену ниже $600:SELECT * FROM PcWHERE ram >= 64 AND price < 600; (8)Данным параметрам соответствует только одна модель компьютера (рисунок 8).Рис. 8 — Результат выполнения запроса (8)Кроме описанных выше, в SQL существуют и другие предикаты, например BETWEEN (Между) – для проверки условия вхождения значение какого-либо столба в заданный диапазон значений, IN (в) – определяет, будет ли значение проверяемого выражения обнаружено в заданном наборе значений, LIKE (как) - сравнивает значение строкового столбца с образцом. В образце разрешается использовать два трафаретных символа:символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении;символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении..Имена столбцов, указанные в предложении SELECT, можно изменять. Это сделано для повышения читабельности результатов запроса, т.к. часто имена полей в таблицах сокращают с целью упрощения набора запросов. Для этой цели применяется ключевое слово AS, которое по стандартам SQL можно и опустить (в запросах 1-8 это ключевое слово опущено).Например, запрос:SELECT ram AS “RAM, Mb”, hd AS “HardDisk, Gb”, price AS “Price,$”FROM PcWHERE cd = '24x'; (9)в результирующей таблице переименует столбец ram в «RAM, Mb» (Оперативная память, мегабайты), столбец hd в «HardDisk, Gb» (Жесткий диск, гигабайты), а столбец price в «Price,$». Этот запрос после выполнения возвращает объемы оперативной памяти и жесткого диска, а также цену для тех компьютеров, которые имеют 24-скоростной CD-ROM (рисунок 9).Рис. 9 — Результат выполнения запроса (9)Также с помощью предложения SELECT можно получить значения итоговых (агрегатных) функций. В языке SQL предусмотрено шесть агрегатных функций:Таблица 1 – Описание итоговых (агрегатных) функцииФункцияОписаниеCOUNT(*)Результатом является количество строк источника записей.COUNT()Результатом является количество значений в указанном в круглых скобках столбце.SUM()Результатом является сумма значений указанного в круглых скобках столбца.AVG()Результатом является среднее арифметическое значений указанного в круглых скобках столбца.MIN()Результатом является минимальное из значений указанного в круглых скобках столбца.MAX()Результатом является максимальное из значений указанного в круглых скобках столбца.Результатом работы функций, описанных в таблице 1, является одно число. Следует отметить, что функции SUM и AVG можно использовать только для полей, содержащих данные числового типа, в то время как COUNT, MIN и MAX применимы к любым типам данных. Принципиальное отличие функций COUNT(*) и COUNT() заключается в том, что первая при подсчете учитывает NULL поля, а вторая - нет.Например, необходимо найти максимальную, минимальную и среднюю цену на компьютеры. SELECT MAX(price) AS Max_price, MIN(price) AS Min_price, AVG(price) AS Avg_priceFROM PC; (10)Результатом выполнения запроса будет одна строка, содержащая агрегатные (итоговые) значения (рисунок 10).Рис. 10 — Результат выполнения запроса (10)2.4 Операторы модификации данныхСледующая группа операторов включает операторы, служащие для модификации (изменения) данных, содержащихся в таблицах БД. Этими операторами являются:INSERT – служит для добавления записей (строк) в таблицу БДUPDATE – служит для обновления данных в столбце таблицы БДDELETE – служит для удаление записей из таблицы БД2.4.1 Оператор INSERTНазначение оператора INSERT – вставка новых строк в таблицу базы данных. При этом значения, которые необходимо вставить в поля таблицы, могут быть как прописанными заранее константы, так и являться результатом выполнения подзапроса (вложенного запроса). В первом варианте для каждой строки данных формируется отдельное предложение оператора INSERT, а во втором – в таблицу будет добавлено столько строк, сколько возвратит подзапрос.Общий синтаксис оператора:INSERT INTO <имя таблицы>[(<имя столбца>,...)]{VALUES (< значение столбца>,…)}| <выражение запроса>| {DEFAULT VALUES};Как видно из общего синтаксиса оператора, список столбцов – не обязательный параметр. Тогда, в случае отсутствия списка столбцов, список добавляемых значений должен быть полным, то есть должен включать в себя значения для каждого столбца изменяемой таблицы БД. При этом добавляемые значения должны идти в то же порядке, что и порядок столбцов, заданный в операторе CREATE TABLE (оператор создания таблицы БД). Кроме того, тип данных каждого из добавляемых значений должно совпадать (или приводиться к нему) с типом данных, определенным для соответствующего столбца в операторе CREATE TABLE.В качестве примера рассмотрим добавление строки в таблицу Product, созданную с помощью следующего оператора CREATE TABLE:CREATE TABLE IF NOT EXISTS `CompFirm`.`Product` (`Model` VARCHAR(50) NOT NULL ,`maker` VARCHAR(10) NULL ,`type` VARCHAR(50) NULL ,PRIMARY KEY (`Model`) ); (11)Допустим в эту таблицу необходимо добавить данные о модели ПК 1158 производителя С. Это можно сделать с помощью следующего оператора SQL:INSERT INTO Product VALUES ('1158', 'C', 'PC'); (12)То есть порядок значений, предназначенных для вставки в таблицу (запрос 12), соответствует порядку соответствующих столбцов в запросе 11.Если же задать список столбцов (после указания таблицы для вставки), то можно изменить изначальный порядок их следования. Например:INSERT INTO Product (type, model, maker) VALUES ('PC', 1158, 'B'); (13)Результат выполнения запросов 12 и 13 будет одни и тем же (Рисунок 11).Рис. 10 — Результат выполнения запроса (12) или (13)На первый взгляд, кажется, что указывание столбцов для вставки только делает запрос более громоздким. Однако, эта возможность становится более выигрышной, если для столбцов указаны значения по умолчанию. Рассмотрим таблицу следующей структуры:CREATE TABLE IF NOT EXISTS `CompFirm`.`Product_D` (`Model` VARCHAR(50) NULL ,`maker` VARCHAR(10) NULL ,`type` VARCHAR(50) NOT NULL DEFAULT 'PC'); (14)Следует отметить, что здесь для каждого столбца задано значение по умолчанию: для первых двух столбцов - NULL, а для последнего (type) - 'PC'. Теперь можно написать:INSERT INTO Product_D (model, maker) VALUES (1158, 'B'); (15)При выполнении запроса 15 отсутствующее значение столбца type при вставке строки будет заменено значением, заданным по умолчанию - 'PC'. Следует заметить, что если в операторе CREATE TABLE для какого-либо столбца не указаны значение по умолчанию и ограничение NOT NULL, тогда значением по умолчанию будет NULL.Также следует обратить внимание, что можно, не указывая список столбцов воспользоваться значениями по умолчанию. Для этого необходимо вместо явного указания добавляемого значения написать в запросе зарезервированное слово DEFAULT (Умолчание):INSERT INTO Product_D VALUES ('B', 1159, DEFAULT); (16)Так как в таблице Product_D для каждого столбца задано значение по умолчанию, то чтобы вставить строку, состоящую из значений по умолчаний, можно было бы написать:INSERT INTO Product_D VALUES (DEFAULT, DEFAULT, DEFAULT); (17)Но для такого случая в SQL существует специальная конструкция DEFAULT VALUES, после применения этой онструкции запрос 7 приобретает следующий вид:INSERT INTO Product_D DEFAULT VALUES; (18)В результате выполнения запросов 15-18 в таблицу Product_d добавится 4 стоки (Рисунок 11).Рис. 11 — Результат выполнения запросов (15)-(18)Следует заметить, что в случае добавления строки в таблицу SQL проверяет все ограничения, которые наложены как на столбцы таблицы, так и на саму таблицу. Это могут быть, например, ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK или ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки не будет произведена.Допустим, необходимо добавить в таблицу Product_D строки, аналогичные строкам таблицы Product, которые относятся к персональным компьютерам. Формирование запросов для вставки каждой строчки вручную является не эффективным (т.к. необходимые нам значение уже содержатся в некоторой таблице), кроме того, построчное заполнение таблицы записями может привести к ошибкам, связанным с невнимательностью при вводе данных с клавиатуры. Данная проблема легко разрешается, если в операторе INSERT использовать вложенный запрос на выборку данных SELECT:INSERT INTO Product_D SELECT * FROM Product WHERE type = 'PC'; (19)SQL обрабатывает данный запрос, начиная с вложенного, то есть сначала будет выполнен оператор SELECT, и из таблицы Product будут выбраны абсолютно все записи. Затем с помощью оператора INSERT выбранные записи будут добавлены в таблицу Product_D. Т.к. порядок следования столбцов в обеих таблицах одинаков, то можно в операторе SELECT использовать символ «*» - столбцы будут идти с порядке, указанном в операторе CREATE TABLE при создании таблицы Product. Если же порядок следования столбцов был различен, то в операторе SELECT было бы необходимым указать столбцы в порядке, соответствующем порядку столбцов таблицы Product_D.Если же необходимо вставить сразу несколько срок в таблицу с помощью оператора INSERT при использовании VALUES, то можно прибегнуть к искусственному приему использования подзапроса, формирующего строку с предложением UNION ALL. Так если требуется вставить несколько строк при помощи одного оператора INSERT, можно написать:INSERT INTO Product_DSELECT 'B' AS maker, 1158 AS model, 'PC' AS typeUNION ALLSELECT 'C', 2190, 'Laptop'UNION ALLSELECT 'D', 3219, 'Printer'; (20)Результат выполнения запроса представлен на рисунке 12.Рис. 12 — Результат выполнения запроса (20)Использование UNION ALL предпочтительней UNION даже в том случае, если гарантировано отсутствие строк-дубликатов, потому что в этом случае не будет выполняться проверка для исключения дубликатов.2.4.2 Оператор UPDATEОператор UPDATE предназначен для изменения имеющихся данных в таблице. Общий синтаксис команды:UPDATESET {имя столбца = {выражение для вычисления значения столбца| NULL| DEFAULT},...}[ {WHERE }];В отличие от оператора INSERT оператор UPDATE позволяет работать с несколькими столбцами любого количества таблиц. Однако, в одном и том же предложении оператора UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. Также следует отметить, что при использовании UPDATE без предложения WHERE обновятся сразу все строки таблицы.Если столбец допускает NULL-значение (пустое значение), то его можно указывать в явном виде. Кроме того, можно заменить имеющееся значение столбца на значение, присваиваемое по умолчанию (DEFAULT), для данного столбца.Ссылка на "выражение для вычисления столбца" может относиться и к текущим значениям в изменяемой таблице. Например, для увеличение всех цен на ПК-блокноты на 5 процентов можно воспользоваться оператором UPDATE:UPDATE Laptops SET price=price*1.1 (21)Разрешается также значения одних столбцов любой таблицы присваивать значения другим столбцам, при условии совпадении их типов. Пусть, например, требуется в ПК-блокнотах заменить жесткие диски менее 10 Гб на жесткие диски, емкость которых должна составлять половину объема RAM, имеющейся в данных устройствах. Эта задача решается следующим образом:UPDATE Laptops SET hd=ram/2; (22)Естественно, типы данных столбцов hd и ram должны быть совместимы. Для приведения некоторых типов данных может использоваться выражение CAST.Если необходимо изменить данные в зависимости от содержимого некоторого столбца, то следует воспользоваться выражением CASE.Пусть, например, необходимо установить жесткие диски объемом 15 Гб на ПК-блокноты, у которых объем оперативной памяти менее 128 Мб и 40 гигабайтные - на остальные ПК-блокноты, тогда запрос UPDATE примет следующий вид:UPDATE Laptops SET hd = CASE WHEN ram<128 THEN 20 ELSE 40 END; (23)Аналогично с оператором INSERT оператор UPDATE позволяет использовать подзапросы для вычисления значений столбцов. Например, требуется установить на все ПК-блокноты самые быстрые процессоры из имеющихся. В таком случае необходимо написать такой запрос:UPDATE LaptopsSET speed = (SELECT MAX(speed) FROM Laptops); (24)2.4.3 Оператор DELETE Оператор DELETE предназначен для удаления записей из таблицы базы данных. Оператор имеет достаточно простой синтаксис:DELETE FROM <имя таблицы > [WHERE <условие>];В случае, если оператор WHERE опущен, то удаляются все записи из указанной таблицы. Пусть, например, требуется удалить из таблицы Laptops все портативные компьютеры с размером экрана менее 12 дюймов. Эту задачу можно выполнить при помощи следующего запроса: DELETE FROM Laptops WHERE screen < 12; (23)Если же оператора WHERE не было, тогда удалились бы все записи из таблицы Laptops:DELETE FROM Laptops; (24)Оператор DELETE также позволяет использовать вложенные запросы. Пусть, например, необходимо удалить те подели ПК из таблицы Product, для которых нет соответствующих строк в таблице PC. Запрос, выполняющий данную задачу, выглядит следующим образом: DELETE FROM ProductWHERE type='pc' AND model NOT IN (SELECT model FROM PC); (25)В запросе 25 используется вложенный запрос, на основе которого формируются условия для оператора WHERE.