Лекция 4

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

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

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

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

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

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

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

8. Архитектура SQL • SQL напоминает человеческийязыкблагодаря императивной структуре. Отдельные команды SQL(запросы), могут быть разбиты на части речи. Рассмотрим примеры:

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

10. Создание и удаление таблиц • Таблица, структурированное вместилище данных, является основным понятием реляционных баз. Прежде чем начать вводить данные в таблицу, вы должны определить ее структуру. Рассмотрим следующую раскладку:

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

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

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

14. Типы данных в SQL

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

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

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

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

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

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

21. Типы данных в SQL • Индексы. Индексом называютполе или группу полей, в которых вы собираетесь осуществлять поиск: 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))

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

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

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

25. Типы данных в SQL • При создании таблицы в 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)

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

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