Sql и базы данных
 Скачать 1.44 Mb.
|
|
SQL и базы данных SQL является инструментом, предназначенным для организации, управления, выборки и обработки информации, содержащейся в реляционных базах данных. • Определение данных. SQL позволяет пользователю определить структуру и организацию хранимых данных и взаимоотношения между элементами сохраненных данных. • Выборка данных. SQL дает пользователю или приложению возможность извлекать из базы содержащиеся в ней данные и пользоваться ими. • Обработка данных. SQL позволяет пользователю или приложению изменять базу данных, т.е. добавлять в нее новые данные, а также удалять или обновлять уже имеющиеся в ней данные. • Управление доступом. С помощью SQL можно ограничить возможности пользователя по выборке, добавлению и изменению данных и защитить их от несанкционированного доступа. • Совместное использование данных. SQL применяется для координации совместного использования данных пользователями, работающими одновременно, с тем чтобы изменения, вносимые одним пользователем, не приводили к непреднамеренному уничтожению изменений, вносимых примерно в то же время иным пользователем. • Целостность данных. SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы. Сердцем СУБД является механизм базы данных (database engine, часто называемый просто движком); он отвечает за структурирование данных, сохранение и получение их из базы данных. Он принимает SQL-запросы от других компонентов СУБД (таких, как генератор отчетов или модуль запросов), от пользовательских приложений и даже от других вычислительных систем. Как видно из рисунка, SQL выполняет много различных функций. Вот некоторые основные свойства SQL, обеспечивающие такой небывалый его успех в течение последних десятилетий. • Независимость от конкретных СУБД • Межплатформенная переносимость • Наличие стандартов • Поддержка со стороны компании IВМ • Поддержка со стороны компании Microsoft • Построение на реляционной модели • Высокоуровневая структура, напоминающая естественный язык • Возможность выполнения специальных интерактивных запросов • Обеспечение программного доступа к базам данных • Возможность различного представления данных • Полноценность в качестве языка, предназначенного для работы с базами данных • Возможность динамического определения данных • Поддержка архитектуры клиент/ сервер • Поддержка приложений уровня предприятия • Расширяемость и поддержка объектно-ориентированных технологий • Возможность доступа к данным в Интернете • Интеграция с языком java (протокол JDВC) • Поддержка открытого кода • Промышленная инфраструктура Мейнфре́йм (также мэйнфрейм, от англ. mainframe) — большой универсальный высокопроизводительный отказоустойчивый сервер со значительными ресурсами ввода-вывода, большим объёмом оперативной и внешней памяти, предназначенный для использования в критически важных системах (англ. Реляционная база данных — база данных, основанная на реляционной модели данных. Реляционной называется база данных, в которой все данные, доступные пользователю, организованы в виде таблиц, а все oпepaции базы данных выполняются над этими таблицами. Столбцов не может быть 0, строк может быть 0. В правильно построенной реляционной базе данных в каждой таблице есть столбец (или комбинация столбцов), для которого значения во всех строках различны. Этот столбец (столбцы) называется первичным ключом (primary key). Таблица, в которой все строки отличаются друг от друга, в математических терминах называется отношением (relation) . Отношение " предок-потомок", существующее между офисами и работающими в них людьми, в реляционной модели не утеряно; просто оно реализовано в виде одинаковых значений данных, хранящихся в двух таблицах, а не в виде явного указателя. Таким способом реализуются все отношения, существующие между таблицами реляционной базы данных. Одним из главных преимуществ языка SQL является возможность извлекать связанные между собой данные, используя эти отношения. На рис. 4.9 столбец REP_O F F I C E представляет собой внешний ключ для таблицы OFF I CE S . Хотя столбец REP_OFF I CE и находится в таблице SALE SREPS, значения, содержащиеся в нем, представляют собой идентификаторы офисов. Эти значения соответствуют значениям в столбце OFF I CE, который является первичным ключом таблицы OFF I CE S . Первичный и внешний ключи создают между таблицами, в которых они содержатся, такое же отношение "предок потомок", как и в иерархической базе данных. Фактически внешний ключ всегда будет составным (состоящим из нескольких столбцов), если он ссылается на составной первичный ключ в другой таблице. Очевидно, что количество столбцов и их типы данных в первичном и внешнем ключах совпадают. двенадцать правил, которым должна соответствовать настоящая реляционная база данных 1 . Правило представления информации. Вся информация в реляционной базе данных должна быть представлена исключительно на логическом уровне и только одним способом - в виде значений, содержащихся в таблицах. 2. Правило гарантированного доступа. Логический доступ ко всем и каждому элементу данных (атомарному значению) в реляционной базе данных должен обеспечиваться путем использования комбинации имени таблицы, значения первичного ключа и имени столбца. 3. Систематическая трактовка значения NULL. В настоящей реляционной базе данных должна быть реализована полная поддержка значений NULL (которые отличаются от строки символов нулевой длины, строки пробельных символов, а также от ну ля или любого другого числа), которые используются для представления отсутствующей и неприменимой информации систематическим образом независимо от типа этих данных. 4. Правило динамического каталога, основанного на реляционной модели. Описание базы данных на логическом уровне должно быть представлено в том же виде, что и обычные данные, чтобы пользователи, обладающие соответствующими правами, могли работать с ним с помощью того же реляционного языка, который они применяют для работы с основными данными. 5. Правило исчерпывающего подъязыка данных. Реляционная система может поддерживать несколько языков и режимов взаимодействия с пользователем. Однако должен существовать по крайней мере один язык, инструкции которого можно представить в виде строк символов в соответствии с некоторым точно определенным синтаксисом и который в полной мере поддерживает все следующие элементы: • определение данных; • определение представлений; • обработку данных (интерактивную и программную); • ограничения целостности данных; • авторизацию; • границы транзакций (начало, фиксацию и откат) . 6. Правило обновления представлений. Все представления, которые теоретически можно обновить, должны быть доступны для обновления системой. 7. Правило высокоуровневого добавления, обновления и удаления. Операции вставки, обновления и удаления должны применяться к отношению в целом. 8. Правило физической независимости данных. Прикладные программы и утилиты для работы с данными на логическом уровне должны оставаться неизменными при любых изменениях способов хранения данных или методов доступа к ним. 9. Правило логической независимости данных. Прикладные программы и утилиты для работы с данными должны на логическом уровне оставаться нетронутыми при внесении в базовые таблицы любых изменений, которые теоретически позволяют сохран ить нетронутыми содержащиеся в этих таблицах данные. 10. Правило независимости контроля целостности. Должна существовать возможность определять условия целостности, специфичные для конкретной реляционной базы данных, на подъязыке этой базы данных и хранить их в каталоге, а не в прикладной программе. 11. Правило независимости распространения. Реляционная база данных должна быть переносима не только в пределах системы, но и по сети. 12. Правило согласования языковых уровней. Если в реляционной системе есть низкоуровневый язык (обрабатывающий одну запись за один раз), то он не должен иметь возможность обходить правила и условия целостности данных, выраженные на реляционном языке высокого уровня (обрабатывающем несколько записей за один раз). правило 12 предотвращает использование других средств работы с базой данных, помимо ее подъязыка, поскольку это может нарушить ее целостность Резюме SQL основан на реляционной модели данных, в которой данные организованы в виде коллекц ии таблиц. • Каждая таблица имеет уникальное имя. • В каждой таблице есть один или несколько именованных столбцов, расположенных в опреде ленном порядке слева направо. • В каждой таблице есть нуль или более строк, каждая из которых содержит одно значение дан ных в каждом столбце; строки в таблице не упорядочены. • Все значения данных в одном столбце имеют одинаковый тип данных и входят в набор допус тимых значений, который называется доменом столбца. Отношения между таблицами реализуются с помощью содержащихся в них данных. В реляцио нной модели данных для представления этих отношений используются первичные и внешние ключи. • Первичным ключом может быть столбец или комбинация столбцов таблицы, значения котор ых уникальным образом идентифицируют каждую строку таблицы. У таблицы есть только оди н первичный ключ. • Внешним ключом является столбец или группа столбцов таблицы, значения которых совпада ют со значениями первичного ключа другой таблицы. Таблица может содержать несколько внешних ключей, связывающих ее с одной или нескольк ими другими таблицами. • Пара "первичный ключ-внешний ключ" создает отношение "предокпотомок" между таблица ми, содержащими их. Строки результатов запроса, как и строки таблицы базы данных, не имеют определенного порядка. Но, включив в инструкцию SELECT предложение ORDER ВУ, можно отсортировать результаты запроса. Репликация — одна из техник масштабирования баз данных. Состоит эта техника в том, что данные с одного сервера базы данных постоянно копируются (реплицируются) на один или несколько других (называемые репликами). Для приложения появляется возможность использовать не один сервер для обработки всех запросов, а несколько. Таким образом появляется возможность распределить нагрузку с одного сервера на несколько. Большинство СУБД позволяет различным пользователям создавать таблицы с одинаковыми именами (например, и пользователь Joe, и пользователь Sam могут создать таблицу B I RTHDAYS). С УБД обращается к необходимой таблице в зависимости от того, кто из пользователей запрашивает данные. При наличии соответствующих прав можно обращаться к таблицам, владельцами которых являются другие пользователи, с помощью полного, или квалифицированного, имени таблицы. Оно состоит из имен владельца таблицы и собственно таблицы, разделенных точкой. Например, квалифицированное имя таблицы BIRTHDAYS, владельцем которой является пользователь SАМ, имеет такой вид. SA М.BIRTHDAYS Стандарт ANSl / ISO SQL еще больше обобщает понятие квалифицированного имени таблицы. Он разрешает создавать именованное множество таблиц, называемое схемой. Вы можете обращаться к таблице определенной схемы с использованием квалифицированного имени. Например, обращение к таблице BIRTHDAYS в схеме EMPLOYEE _INFO имеет следующий вид. EMPLOYEE INFO.BIRTHDAYS Полное имя столбца BIRTH_DATE в таблице BIRTHDAYS, владельцем которой является пользователь SАМ, имеет следующий вид. SA М.BIRTHDAYS.BIRTHDATE SQL поддерживает обработку отсутствующих, неизвестных или неприменимых данных с помощью концепции отсутствующего значения. NULL - это значение является индикатором, который показывает, что в конкретной строке определенный элемент данных отсутствует или что столбец вообще не подходит для этой строки. Резюме В настоящей главе были описаны основные элементы SQL. • Язык SQL включает около тридцати инструкций, каждая из которых состоит из команды и одного или нескольких предложений. Каждая инструкция выполняет одно конкретное действие. • В SQL-бaзax данных могут храниться значения различных типов, включая текстовые данные, целые и десятичные числа, числа с плавающей точкой и данные ряда других типов, введенных конкретным производителем. • Инструкции SQL могут включать в себя выражения, в которых над именами столбцов, константами и встроенными функциями выполняются арифметические и другие операции. • Разнообразие типов данных, констант и встроенных функций делает переносимость инструкций SQL более сложной задачей, чем это может показаться на первый взгляд. • Значения NULL используются для обработки отсутствующих или неприменимых элементов данных в SQL. XML ( англ. eXtensible Markup Language) — расширяемый язык разметки Язык называется расширяемым, поскольку он не фиксирует разметку, используемую в документах: разработчик волен создать разметку в соответствии с потребностями к конкретной области, будучи ограниченным лишь синтаксическими правилами языка. Расширение XML — это конкретная грамматика, созданная на базе XML и представленная словарём тегов и их атрибутов, а также набором правил, определяющих какие атрибуты и элементы могут входить в состав других элементов Резюме • Инструкция SELECT используется для формирования SQL-зaпpoca. Каждая инструкция SELECT во звращает таблицу результатов запроса, содержащую один или более столбцов и ну ль или бол ее строк. • Предложение FROM определяет таблицы, в которых содержатся выбираемые данные. • Предложение SELECT определяет столбцы данных, которые необходимо включить в результат ы запроса и которые могут представлять собой столбцы из базы данных или вычисляемые сто лбцы. • Предложение WHERE отбирает строки, которые необходимо включить в результаты запроса, пу тем применения условия отбора к строкам базы данных. • Условие отбора позволяет выбрать строки путем сравнения значений, проверки значений на принадлежность множеству или диапазону значений, проверки на соответствие шаблону и на равенство значению NULL. • Простые условия отбора могут объединяться в более сложные условия с помощью операторо в AND , OR и NOT. • Предложение ORDER BY указывает, что результаты запроса должны быть отсортированы по воз растанию или убыванию на основе значений одного или нескольких столбцов. • Для объединения результатов двух инструкций SELECT в одно множество можно использовать операцию UNION. В SQL-бaзe данных первичные и внешние ключи создают отношение "предок-потомок". Таблица, содержащая внешний ключ, является потомком, а таблица с первичным ключом - предком. Чтобы использовать в запросе отношение "предок-потомок", необходимо задать условие отбора, в котором первичный ключ сравнивается с внешним ключом. Таблица SALESREPS (потомок) содержит столбец REP _ OFFICE, который является внешним ключом для таблицы OFFICES (предок). Здесь отношение "предок-потомок" используется с целью поиска в таблице OFFICE для каждого служащего соответствующей строки, содержащей город и регион, и включения ее в резу ль таты запроса. 1. Что такое DDL? Какие операции в него входят? Рассказать про них. Какие существуют операторы SQL? операторы определения данных (Data Definition Language, DDL): ● CREATE создает объект БД (базу, таблицу, представление, пользователя и т. д.), CREATE DATABASE Test ● ALTER изменяет объект (ALTER TABLE - изменить структуру таблицы) ● DROP удаляет объект; Проконтролировать создание базы данных можно с помощью оператора SHOW DATABASES, SHOW TABLES кроме пользовательских таблиц отображает также и служебные таблицы. DESCRIBE table_name – показывает таблицу DELETE FROM Удаление всех данных из таблицы Проверка на уже существующие базы данных: CREATE DATABASE IF NOT EXIST имя_базы_данных; Перед созданием таблицы необходимо выбрать базу данных, в которую таблица будет записана. Это делается с помощью оператора USE: USE имя_базы_данных Для того, чтобы посмотреть описание созданной таблицы можно воспользоваться оператором DESCRIBE: DESCRIBE Users; 2. Что такое DML? Какие операции в него входят? Рассказать про них. операторы манипуляции данными (Data Manipulation Language, DML): ● SELECT выбирает данные, удовлетворяющие заданным условиям, SELECT [DISTINCT | ALL] поля_таблиц FROM список_таблиц [WHERE условия_на_ограничения_строк] [GROUP BY условия_группировки] [HAVING условия_на_ограничения_строк_после_группировки по агрегатным функциям] [ORDER BY порядок_сортировки [ASC | DESC]] [LIMIT ограничение_количества_записей] SQL- псевдонимы: SELECT good_type_id AS id FROM GoodTypes; Порядок выполнения инструкций (зависит от СУБД): FROM WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY В предложении WHERE не доступны псевдонимы столбцов, определяемых в предложении SELECT , потому что, согласно списку, оно выполняется до SELECT WHERE условия_на_ограничения_строк [логический_оператор другое_условия_на_ограничения_строк] - IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL - [NOT] BETWEEN min AND max - [NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений WHERE status IN ('father', 'mother'); - [NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка определённому шаблону Трафаретные символы: Шаблон Описание never% Сопоставляется любым строкам, начинающимся на «never». Шаблон Описание %ing Сопоставляется любым строкам, заканчивающимся на «ing». _ing Сопоставляется строкам, имеющим длину 4 символа, при этом 3 последних обязательно должны быть «ing». Например, слова «sing» и «wing». ESCAPE- символ используется для экранирования трафаретных символов WHERE progress LIKE '3!%' ESCAPE '!'; Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3. Логические операторы: NOT, OR (общее значение выражения истинно, если хотя бы одно из них истинно), AND, XOR (общее значение выражения истинно, если один и только один аргумент является истинным) WHERE plane = 'Boeing' AND NOT town_from = 'London'; ● INSERT добавляет новые данные Общая структура запроса с оператором INSERT INSERT INTO имя_таблицы [(поле_таблицы, ...)] VALUES (значение_поля_таблицы, ...) | SELECT поле_таблицы, ... FROM имя_таблицы ... INSERT INTO Goods ( good_id , good_name , type ) VALUES ( 5 , 'Table' , 2 ); INSERT INTO Goods VALUES ( 5 , 'Table' , 2 ); INSERT INTO Goods SELECT good_id , good_name , type FROM Goods where good_name = 2 ; ● UPDATE изменяет существующие данные UPDATE FamilyMembers SET member_name = "Andie Anthony" что делаем WHERE member_name = "Andie Quincey" куда делаем UPDATE Payments SET unit_price = unit_price * 2 ● DELETE удаляет данные; DELETE Reservations , Rooms FROM Reservations JOIN Rooms ON Reservations room_id = Rooms id WHERE Rooms has_kitchen = false ; TRUNCATE TABLE имя_таблицы ; у оператора TRUNCATE есть ряд отличий: • Не срабатывают триггеры (, в частности, триггер удаления • Удаляет все строки в таблице, не записывая при этом удаление отдельных строк данных в журнал транзакций • Сбрасывает счетчик идентификаторов до начального значения • Чтобы использовать, необходимы права на изменение таблицы Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE. Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину. CREATE TRIGGER имя_триггера ON {имя_таблицы | имя_представления} {AFTER | INSTEAD OF} [INSERT | UPDATE | DELETE] AS выражения_sql |