Лекция 4 Язык SQL Основы SQL История SQL Архитектура SQL Создание и удаление таблиц Типы данных в SQL

Язык SQL Для чтения и записи в базах данных MySQL используется структурированный язык запросов (SQL). Используя SQL, можно осуществлять поиск, вводить новые данные или удалять данные. SQL является просто основополагающим инструментом, необходимым для взаимодействия с MySQL. Даже если для доступа к базе данных вы пользуетесь каким-то приложением или графическим интерфейсом пользователя, где- то в глубине это приложение генерирует SQL- команды.

Язык SQL SQL является разновидностью «естественного языка». Иными словами, команда SQL должна читаться, по крайней мере на первый взгляд, как предложение английского языка. У такого подхода есть как преимущества, так и недостатки, но факт заключается в том, что этот язык очень непохож на традиционные языки программирования, такие как С, Java или Perl. Здесь мы рассмотрим язык SQL, как он реализован в MySQL.

Основы SQL SQL «структурирован» в том отношении, что он следует определенному набору правил. Запрос (query) - это полностью заданная команда, посылаемая серверу баз данных, который выполняет запрошенное действие. Ниже приведен пример SQL-запроса: SELECT name FROM people WHERE name LIKE Stac%' Это предложение выглядит почти как фраза на ломаном английском языке: «Выбрать имена из список люди, где имена похожи на Stac».

История SQL В IBM изобрели SQL в начале 1970-х, вскоре после введения д-ром Е. Ф. Коддом (Е. F. Codd) понятия реляционной базы данных. В 1980-х компания Oracle выпустила первую в мире общедоступную коммерческую SQL- систему. Sybase, Informix, Microsoft и ряд других компаний вышли на рынок с собственными разработками реляционных систем управления базами данных (РСУБД), основанных на SQL.

История SQL В 1989 году комиссия по стандартам ANSI выпустила первый общедоступный стандарт SQL - SQL89. различные коммерческие реализации языка SQL делали задачу перехода с одной реализации на другую нетривиальной. Стандарт 1992 года (SQL92 или SQL2) включил в себя максимально возможное количество расширений, добавленных в коммерческих реализациях языка. Однако, из- за очень большой широты стандарта SQL2, реляционные базы, реализующие полный стандарт, очень сложные и ресурсоемкие.

История SQL В связи с ростом популярности объектно- ориентированных СУБД (ООСУБД) и объектно-реляционных СУБД (ОРСУБД) возрастает давление с целью принятия объектно-ориентированного доступа к базам данных в качестве стандарта SQL (SQL3). Он не является пока официальным стандартом, но в настоящее время вполне определился. С появлением MySQL проявился новый подход к разработке серверов баз данных. Были предложены небольшие и быстрые реализации наиболее часто используемых функций SQL.

Архитектура SQL SQL напоминает человеческий язык благодаря императивной структуре. Отдельные команды SQL (запросы), могут быть разбиты на части речи. Рассмотрим примеры: CREATETABLEpeople (name CHAR(10)) глаголдополнениерасширенное определение INSERTINTO peopleVALUES('me') глаголкосвенное дополнение прямое дополнение SELECTnameFROM peopleWHERE name LIKE '%e' глаголпрямое дополнение косвенное дополнение придаточное предложение

Архитектура SQL Большинство реализаций SQL, включая MySQL, нечувствительны к регистру ключевых слов SQL. Например, можно писать и так: cREatE ТАblЕ people (name cHaR(10)) В MySQL имена баз данных, таблиц и колонок чувствительны к регистру. Не следует использовать имена, различающиеся одним только регистром.

Создание и удаление таблиц Таблица, структурированное вместилище данных, является основным понятием реляционных баз. Прежде чем начать вводить данные в таблицу, вы должны определить ее структуру. Рассмотрим следующую раскладку: people namechar(10) not null addresstext(100) idint

Создание и удаление таблиц Таблица содержит имена колонок и тип каждого поля, а также возможные дополнительные сведения о полях. Тип данных поля определяет, какого рода данные могут в нем содержаться. Полный стандарт SQL допускает большое разнообразие типов данных. MySQL реализует большую их часть. Общий синтаксис для создания таблиц следующий: CREATE TABLE table_name (column_name1 type [modifiers] [, column_name2 type [modifiers]] )

Создание и удаление таблиц В MySQL длина идентификатора может быть до 64 символов, допустим символ «$», и первым символом может быть цифра. MySQL допускает использование любых символов из установленного в системе локального набора. Для хорошей переносимости SQL избегайте имен, начинающихся не с допустимой буквы. Колонка - это отдельная единица данных в таблице. В таблице может содержаться произвольное число колонок, но использование больших таблиц неэффективно.

Создание и удаление таблиц Следующая команда удаляет таблицу: DROP TABLE table_name Все данные удаленной таблицы будут уничтожены. Если не осталось резервной копии, действие данной операции отменить невозможно. Одной можно командой удалить несколько таблиц, разделяя их имена запятыми. Например, DROP TABLE people, animals, plants. Можно использовать модификатор IF EXISTS для подавления ошибки в случае отсутствия удаляемой таблицы. Прежде чем создавать таблицу, выполните команду DROP TABLE table_name IF EXISTS.

Типы данных в SQL INTЦелое число, может быть со знаком или без знака. REALЧисло с плавающей запятой. CHAR(length)Символьная величина фиксированной длины. Поля меньшей длины дополняются пробелами. TEXT(length)Символьная величина переменной длины. DATEСтандартное значение даты. TIMEСтандартное значение времени. Есть дополнительный тип DATETIME для совместного хранения даты и времени в одном поле.

Типы данных в SQL Числовые типы данных. Выясняйте примерный размер хранимых данных. Если это числовое поле, то каким окажется максимальное значение? Может ли оно измениться в будущем? Если минимальное значение всегда положительно, следует рассмотреть использование беззнакового типа. Всегда следует выбирать самый маленький числовой тип, способный хранить самое большое мыслимое значение.

Типы данных в SQL Символьные типы. Вы должны подумать не только о максимальной и минимальной длине строки, но также о среднем размере, частоте отклонения от него и необходимости в индексировании. Индексирование по символьным полям происходит быстрее, если они имеют фиксированную длину. Если длина строк не слишком колеблется или постоянна, то лучше выбрать для поля тип CHAR.

Типы данных в SQL Поля переменной длины. Они используют ровно столько места, сколько необходимо для хранения отдельной величины. В отличие от стандарта ANSI, в MySQL поля типа VARCHAR не дополняются пробелами. Перед записью из строки удаляются лишние пробелы. Хороший пример поля, для которого требуется тип данных с переменной длиной, - URL Интернет. Иногда встречаются адреса вида: es\ ?XvlI=&Xr5 =&Xvl =&type-region-search-code=&Xa14= flora+springs&Xv4=.

Типы данных в SQL В MySQL есть команда ALTER TABLE, позволяющая переопределить размер поля без потери данных. ALTER TABLE mytable MODIFY mycolumn LONGTEXT

Типы данных в SQL Двоичные типы данных. В MySQL есть CHAR BINARY, VARCHAR BINARY, TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB. Двоичные данные являются куском данных, которые MySQL не пытается интерпрети- ровать. Символьные данные - текстовые данные из используемых человеком алфавитов. Они кодируются и сортируются по правилам для рассматриваемого набора символов. Двоичные данные MySQL сортирует в порядке ASCII без учета регистра.

Типы данных в SQL Перечисления и множества. Тип ENUM позволяет при создании таблицы указать список возможных значений некоторого поля. Например: CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY, фрукт ENUM('яблоко', 'апельсин', 'киви', 'банан')) При записи значения в эту колонку оно должно быть одним из перечисленных. MySQL абстрагирует их каким-либо числовым типом. Тип MySQL SET позволяет одновременно хранить в поле несколько значений.

Индексы Индексы. Индексом называют поле или группу полей, в которых вы собираетесь осуществлять поиск: CREATE INDEX index_name ON tablename (column1, column2, columnN) MySQL позволяет создавать индекс одновременно с созданием таблицы: CREATE TABLE materials (id INT NOT NULL, name CHAR(50) NOT NULL, resistance INT, melting_pt REAL, INDEX index1 (id, name), UNIQUE INDEX index2 (name))

Индексы Когда индекс содержит более одной колонки, MySQL читает колонки в порядке слева направо. Благодаря используемой MySQL структуре индекса всякое подмножество колонок с левого края автоматически становится индексом внутри «главного» индекса.

Ключевые поля Первичный ключ. Это уникальный индекс с именем PRIMARY. Назначив при создании таблицы колонку первичным ключом, вы делаете ее уникальным индексом, который будет поддерживать объединения таблиц: CREATE TABLE cities (id INT NOT NULL PRIMARY KEY, name VARCHAR(100), pop MEDIUMINT, founded DATE) Прежде чем создавать таблицу, нужно решить, какие поля будут ключами (и будут ли вообще ключи). Любые поля, которые будут участвовать в объединении таблиц, являются кандидатами на роль первичного ключа.

Автоинкрементирование Автоинкрементирование. Лучше всего, когда первичный ключ не имеет в таблице никакого иного значения, кроме значения первичного ключа. Для достижения этого лучшим способом является создание числового первичного ключа, значение которого увеличивается при добавлении в таблицу новой строки.

Автоинкрементирование При создании таблицы в MySQL можно одну из колонок специфицировать как AUTO_INCREMENT. В этом случае, при добавлении новой строки, имеющей значение NULL или 0 в данной колонке, автоматически будет происходить замена на значение на единицу больше, чем наибольшее текущее значение в колонке. Колонка с модификатором AUTO_INCREMENT должна быть индексирована: CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), pop MEDIUMINT, founded DATE)

Автоинкрементирование Когда вы первый раз добавляете строку, поле id получает значение 1, если в команде INSERT для него используется значение NULL или 0: INSERT INTO cities (id, name, pop) VALUES (NULL, 'Houston', ) Если вы выполните эту команду, когда в таблице нет строк, поле id получит значение 1, а не NULL. В случае, когда в таблице уже есть строки, полю будет присвоено значение на 1 большее, чем наибольшее значение id в данный момент.

Автоинкрементирование Другим способом реализации последовательностей является использование значения, возвращаемого функцией LAST_INSERT_ID: UPDATE table SET id=LAST_INSERT_ID (id+1);