От CREATE до JOIN: введение в SQL + шпаргалка
Львиная доля мировой информации хранится в реляционных базах данных. Чтобы работать с ней, нужно владеть языком SQL-запросов.
Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:
Этими азами мы и займемся: разберем синтаксис SQL-запросов в теории и на реальных примерах. К счастью, язык баз данных очень похож на простые английские предложения, так что вы легко с ним справитесь.
Чтобы учиться эффективнее, сразу же закрепляйте новые знания практикой. Поиграть с SQL можно на этом замечательном ресурсе. В левой панели вы должны ввести весь код, относящийся к структуре базы данных. После этого начинайте экспериментировать с SELECT’ами в правом поле.
* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.
Терминология
База данных состоит из таблиц, а таблица – из колонок и строк.
Каждая колонка, или поле таблицы, представляет собой конкретный вид информации, например, имя студента (строка) или зарплата сотрудника (число).
Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.
Уровень: Новичок
Создание и редактирование таблиц
CREATE
Несложно догадаться, что оператор CREATE создает новую таблицу в базе. Ему нужно передать описания всех полей таблицы в формате:
Создадим таблицу с данными о собаках и их рационе питания:
ALTER
Не всегда получается создать идеальную таблицу с первого раза. Не бойтесь вносить изменения, добавлять, удалять или изменять существующие поля:
DROP и TRUNCATE
Оператор DROP удаляет таблицу из базы целиком:
Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:
Атрибуты и ограничения
Можно ограничить диапазон данных, которые попадают в поле, например, запретить устанавливать в качестве возраста или веса отрицательные числа.
Самые распространенные в SQL ограничения целостности (CONSTRAINTS):
Ограничения можно добавлять при создании таблицы, а затем при необходимости добавлять/изменять/удалять. Они могут действовать на одно поле или комбинацию полей.
Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.
Решим новую задачу – составление списка президентов:
Ограничение уникальности не позволит занести в таблицу одного и того же президента одной страны дважды. Кроме того, не попадут в список и слишком молодые политики.
Для добавления и удаления ограничений к существующим таблицам используйте оператор ALTER. Ограничениям можно давать имя, чтобы ссылаться на них впоследствии. Для этого предназначена конструкция CONSTRAINT.
Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.
Для примера возьмем базу данных организации с таблицами сотрудников и отделов:
Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.
Сохранение и обновление записей
INSERT
Добавить в таблицу новую запись (или даже сразу несколько) очень просто:
Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:
UPDATE
Оператор UPDATE используется для изменения существующих записей таблицы.
Вот так легким движением руки мы обнулили зарплату сразу у всех сотрудников.
Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.
С условиями мы подробно разберемся чуть позже, когда будем говорить о выборке данных из базы.
DELETE
Можно удалить из таблицы все записи сразу или только те, которые соответствуют некоторому условию:
Уровень: уверенный пользователь
Выборка и фильтрация данных
Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.
Вот небольшая демо-база, на которой вы можете попрактиковаться:
SELECT
Можно переименовывать поля для вывода:
Ограничение количества результатов:
Агрегатные функции и группировка
SQL позволяет привести несколько записей таблицы к некоторому единому значению:
Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:
Полученные группы тоже можно отфильтровывать: для этого предназначена конструкция HAVING. Например, не будем учитывать в выборке отделы, в которых работает меньше трех человек:
Объединение таблиц
Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.
В запросе, захватывающем несколько таблиц, нужно указать следующее:
Соединение бывает внутреннее (INNER) и внешнее (OUTER).
Внутреннее соединение
При внутреннем соединении вы получите в результате только те записи, для которых нашлось соответствие во всех таблицах.
SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.
Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.
Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.
Внешнее соединение
При внешнем соединении в результат попадают также записи без соответствий. При этом вы можете регулировать, из какой таблицы такие записи берутся, а из какой – нет.
Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:
Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.
Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:
И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.
Декартово произведение
Оператор CROSS JOIN позволяет получить все возможные комбинации записей из двух таблиц:
Автосоединение
Кроме того, таблицу можно соединять с самой собой. Это пригодится, чтобы найти босса для каждого сотрудника. Сейчас в поле boss находится идентификатор другого сотрудника, необходимо вывести его имя:
Благодаря использованию левого соединения мы можем вывести также сотрудников, не имеющих руководителей.
Объединение выборок
SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:
UNION
Объединить штатных и внештатных сотрудников
INTERSECT
Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе
MINUS
Найти всех сотрудников, которые не участвуют в сборной предприятия по спортивной ходьбе и заставить участвовать :
Уровень: SQL-мастер
Представления
Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.
У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.
Представление может извлекать данные из одной или нескольких таблиц. Кроме того, при соблюдении ряда условий представление может быть изменяемым, то есть совершая операции над ним, можно изменять базовые таблицы.
Если представление изменяемое, можно использовать при его создании CHECK OPTION для проверки изменений на соответствие некоторому предикату:
Представления могут основываться как на таблицах базы, так и на других представлениях, образуя несколько уровней вложенности. С учетом этого предложение WITH можно расширить:
Чтобы удалить представление, используйте уже знакомый оператор DROP:
Индексы
Индексы – это специальный таблицы, которые позволяют ускорить поиск по базе данных. Их можно представить как алфавитный указатель в большой книге.
Наличие индексов в базе ускоряет выполнение операций SELECT и вычисление условий WHERE. Но есть и обратная сторона медали: замедляются операции вставки и удаления данных, так как при этих изменениях необходимо пересчитывать индексы.
Триггеры
Триггеры в SQL – это процедуры, которые автоматически запускаются при выполнении определенной операции (INSERT/UPDATE/DELETE) – до (BEFORE) или после (AFTER) нее.
Удалить существующий триггер можно с помощью оператора DROP:
Create Table
Create Table SQL
Команда Create Table SQL предназначена для описания структуры таблицы. Команда SQL Create Table создает пустую таблицу (без строк).
Если вы знакомы с SQL Create Table, то наверняка знаете о пунктах, которые мы перечислим ниже, однако если всё-таки вы что-то подзабыли, то милости просим – как говорится, повторение мать учения. Итак, Create Table MySQL, прежде всего, поддерживает опцию определения локальных столбцов, которые, в свою очередь, определяют тип данных, а также имя данных в столбце. Также определяется опция вычисляемых столбцов, вычисление происходит в тех случаях, когда организуется доступ к таблице. Что касается столбцов, которые основаны на доменах, то для них определение может включать каждое значение по умолчанию. Оно также определяет порядок сортировки. В связи с этим определение домена отменяется автоматически. Есть ряд описаний, которые включают предложение CHARACTER SET, определяя кодировку, присущую одному столбцу. При неиспользовании описаний, кодировка остаётся прежней (базовой). Стоит отметить, что текущие столбцы никак не меняются. Что же касается Create Table Oracle, то оно определяет фрагментацию таблицы.
CREATE TABLE MySQL
Create Table. Пример №1.
CREATE TABLE Persons (
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Create Table. Пример №2.
CREATE TABLE `blocklist` (
`email` text NOT NULL,
`login` text NOT NULL,
`remote_addr` text NOT NULL,
`browser` text NOT NULL,
`payment_wmid` text NOT NULL,
`payment_account` text NOT NULL,
`payment_raccount` text NOT NULL
);
Oracle Create Table
Синтаксис команды Create Table
Синтаксис команды CREATE TABLE
Определение ограничений целостности таблицы SQL. Чтобы предохранить поле от разрешения в нем пустых (NULL) указателей для столбца задается ограничение NOT NULL.
Create Table. Пример №3.
При создании, с помощью CREATE TABLE, любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT, воздействующем на таблицу.
Вы должны войти, чтобы оставить комментарий.
Команда SQL для создания и удаления таблиц, добавления данных (CREATE, INSERT, SHOW COLUMNS, TRUNCATE, DROP)
Создание таблицы
Попробуем создать таблицу с названием USERS и этими полями. Разберём пример по строчкам. В первой строке можно увидеть команду с говорящим названием CREATE TABLE. Она делает запрос на создание таблицы. После неё стоит название таблицы USERS. В названии таблицы стоит ставить только латинские буквы (строчные и заглавные) и подчёркивания, но нельзя делать пробелы и иные символы.
Итак, выполним SQL запрос из примера. Теперь необходимо убедиться действительно ли таблица создалась. Для этого сделаем следующий запрос к базе: Эту команду можно перевести дословно с английского языка: «ПОКАЖИ СТОЛБЦЫ ИЗ». После этой команды стоит название таблицы, которую мы только что создавали. Если таблица создана успешно, то в результате выполнения запроса мы увидим список столбцов: По этому результату мы видим, что все столбцы создались успешно.
Добавление данных в таблицу
После успешного создания таблицы можно попробовать добавить первые данные. Не будем мелочиться и добавим сразу две строки данных: Команда «INSERT INTO» (можно перевести с английского как «ВСТАВИТЬ В») вставляет данные в таблицу, название которой идёт после неё. В нашем случае это «USERS». Затем идёт слово «SET» (переводится как «ЗАДАТЬ»), после которого через запятую перечисляются названия столбцов в таблице и их значения, который надо вставить.
В нашем примере первый столбец ID принимает максимальное уникальное значение, поэтому нет большого смысла передавать его при добавлении данных. Для разнообразия во втором добавлении передано не только значение NAME, но и значение DATE, хотя это необязательно, потому что при создании столбца DATE было сказано, что он принимает значение равное текущей дате и времени, если не передать ему что-то другое.
Базы данных могут быть настроены по-разному. Формат даты может отличаться, из-за чего значение DATETIME может несохраниться, если не угадать с форматом. Поэтому вместо строки, содержащей дату в формате «ГГГГ-ММ-ДД ЧЧ:ММ:СС» можно написать «NOW()» (переводится «сейчас»), тогда в таблицу будет вставлено значение текущей даты в нужном формате.
Существует несколько способов записи команды INSERT. Приведём ещё один пример без «SET», который подходит для добавления небольшого количества данных, когда столбцов мало: После названия таблицы в скобках идёт название полей, в которых будут вставлены данные. Затем идёт «VALUES» и в скобках через запятую значения, которые будут вставлены. Обратите внимание, что важен порядок названий столбцов и значений, которые находятся в круглых скобках.
Давайте проверим, что было записано в нашу таблицу. Для этого выполним запрос: Команда «SELECT» возвращает строки из таблицы. После команды стоит звёздочка *, она означает, что необходимо выбрать все столбы таблицы. В нашем случае звёздочку можно заменить на перечисление трёх столбцов через запятую: «ID, NAME, DATE». После звёздочки стоит слово «FROM», которое можно перевести как «из». И в конце стоит название таблицы в базе данных «USERS», из которых будут выводиться данные.
В результате выполнения этого запроса мы увидим такой результат: Всё добавилось как нужно.
Учебник по PHP 4
 |
Команды SQLВ этом разделе мы изучим основные команды языка SQL. Для этого на компьютере с установленным MySQL необходимо запустить клиента mysql, в окне которого можно вводить команды SQL. Команды SQL не чувствительны к регистру, но традиционно они набираются прописными буквами. Типы полей базы данныхК числовым типам относятся целые числа и числа с плавающей точкой. Для чисел с плавающей точкой, кроме максимальной ширины отображения можно также указывать число значащих цифр после запятой, далее обозначаемое символом P. Таблица 13.6. Числовые типы Средние целые числа. Обычные целые числа. Числа с плавающей точкой одинарной точности. Числа с плавающей точкой двойной точности. Числа с плавающей точкой, приведенные к типу char. Типы даты и времени приведены в таблице 13.7. Таблица 13.7. Типы даты и времени Дата в формате ГГГГ-ММ-ДД. | ||||||||
Основные строковые типы приведены в таблице 13.8.
Таблица 13.8. Строковые типы
Строки с длиной len, которое не превышает 255 символов. Ключевое слово BINARY указывает на то, что данные должны обрабатываться независимо от регистра.
Синоним CHAR(len)за исключением того, что строки могут быть произвольной длины.
Строки с максимальной длиной символов равной 65535. Данные этого типа чувствительны к регистру.
CREATE DATABASE
Эта команда создает новую базу данных:
Каждый запрос MySQL завершается точкой с запятой.
При успешном выполнении команды MySQL выдаст строку, в которой сообщается, что этот запрос выполнен успешно и показано время, затраченное на выполнение запроса:
Далее в тексте строку MySQL с результатом выполнения команды мы будем приводить непосредственно в соответствующем листинге.
Как видим, среди различных баз данных на компьютере автора присутствует и только что созданная база данных forum:
Команда SHOW DATABASES является внутренней командой MySQL, отсутствующей в стандарте SQL и неподдерживаемой другими базами данных.
Для того чтобы начать работу с таблицами, необходимо сообщить MySQL с какой базой данных вы намерены работать. Это осуществляется при помощи команды USE:
CREATE TABLE
Команда CREATE TABLE создает новую таблицу в выбранной базе данных и которая в простейшем случае имеет следующий синтаксис:
Давайте теперь аналогичным образом создадим другие таблицы.
Следующей по порядку идет таблица forums, в которой содержатся данные о разделах форума.
Для удобства на форуме может быть создано несколько различных разделов. К примеру, на форуме по языкам программирования для того, чтобы не смешивать темы, относящиеся к различным языкам, имеет смысл создать следующие разделы: С++, PHP, Java и т. д.
В таблице forums присутствуют следующие поля: первичный ключ ( id_forum ), название раздела ( name ), правила форума ( rule ), краткое описание форума ( logo ), порядковый номер ( pos ), флаг, принимающий значение 1, если форум скрытый и 0, если общедоступный ( hide ).
Вот SQL-запрос, создающий таблицу forums
DESCRIBE
Команда DESCRIBE показывает структуру созданных таблиц и имеет следующий синтаксис:
Команда DESCRIBE не входит в стандарт SQL и является внутренней командой СУБД MySQL.
После выполнения этой команды, интерпретатор mysql выведет следующую таблицу
ALTER TABLE
Команда ALTER TABLE позволяет изменить структуру таблицы. Эта команда позволяет добавлять и удалять столбцы, создавать и уничтожать индексы, переименовывать столбцы и саму таблицу. Команда имеет следующий синтаксис:
Параметр alter_spec имеет значения, представленные в таблице
ADD create_definition [FIRST|AFTER column_name]
Добавление нового столбца create_definition. create_definition представляет собой название нового столбца и его тип. Конструкция FIRST добавляет новый столбец перед столбцом column_name. Конструкция AFTER добавляет новый столбец после столбца column_name. Если место добавления не указано, по умолчанию столбец добавляется в конец таблицы.
ADD INDEX [index_name] (index_col_name. )
Добавление индекса index_name для столбца index_col_name. Если имя индекса index_name не указывается, ему присваивается имя совпадающее с именем столбца index_col_name.
ADD PRIMARY KEY (index_col_name. )
Делает столбец index_col_name или группу столбцов первичным ключом таблицы.
CHANGE old_col_name new_col_name type
Изменение столбца с именем old_col_name на столбец с именем new_col_name и типом type.
Удаление столбца с именем col_name.
Удаление первичного ключа таблицы.
DROP INDEX index_name
Удаление индекса index_name.
Давайте переименуем созданный столбец test в текстовый столбец new_test
Как видно из рисунка, столбец успешно переименован:
При изменении только типа столбца, а не его имени, указание имени все-равно необходимо, хотя в этом случае оно будет фактически повторяться.
Результат выполнения этого запроса приведен на рисунке:
Теперь удалим столбец new_test :
Как видно из рисунка, после удаления этого столбца таблица forums приобрела исходную структуру:
DROP TABLE
Команда DROP TABLE предназначена для удаления одной или нескольких таблиц:
К примеру, для удаления таблицы forums нужно выполнить следующий SQL-запрос:
DROP DATABASE
Команда DROP DATABASE удаляет базу данных со всеми таблицами входящими в её состав:
Удалим, например, базу данных forum :
INSERT INTO…VALUES
Команда INSERT…VALUES вставляет новые записи в существующую таблицу. Синтаксис команды:
После оператора VALUES в скобках через запятую перечисляются значения соответствующих полей таблицы в соответствии с их типами.
Давайте вставим в базу данных authors несколько записей, в которых расположена информация об зарегистрированных посетителях форума:
DELETE
Команда DELETE удаляет из таблицы table_name записи, удовлетворяющие заданным в definition условиям, и возвращает число удаленных записей.
Вот как можно удалить все записи из таблицы authors :
Условия отбора могут быть значительно сложнее, так в листинге 13.16 удаляются все авторы с паролем ‘123’ и первичный ключ которых превышает 10:
Оператор AND является логическим «и». В запросах можно так же применять логическое или «или».
SELECT
Команда SELECT предназначена для извлечения строк данных из одной или нескольких таблиц и имеет в общем случае следующий синтаксис:
Для того чтобы посмотреть всю таблицу forums выполняется следующий запрос:
Выбираем все столбцы из таблицы forums без ограничений. Результат показан на рисунке:
Можно выбрать не все столбцы таблицы, а лишь часть, для этого необходимо явно задать список выбираемых столбцов:
В этом случае MySQL выведет лишь два столбца с первичным ключом id_forum и названием форума name
В результате этого запроса будет выведено только первые 3 записи из 5
Оператор LIMIT может также принимать два числовых аргумента, которые должны быть целыми числами. В этом случае последний аргумент задает максимальное количество возвращаемых строк, а первый сообщает MySQL начиная с какой по счёту строки производить отсчёт
В этом случае будут возвращены строки 2, 3 и 4
Результат показан на слудующем рисунке:
Порядок сортировки выводимых записей можно задавать при помощи оператора ORDER BY:
В этом запросе выводятся все записи со значением поля id_forum не меньше двух, которые при этом сортируются по значению поля pos. Результат такого запроса показан на рисунке:
UPDATE
В следующим листинге разделу форума с первичным ключом 2 устанавливается новое название (PHP) и устанавливается атрибут hide равным 1, делая форум невидимым.
С этой командой мы уже встречались ранее, когда выполняли запросы вида show databases и show tables для получения списка баз данных и таблиц в выбранной базе данных. Рассмотрим еще несколько вариантов использования этой команды.
Вывести список всех столбцов выбранной таблицы можно при помощи следующего запроса:
Результат приведен на следующем рисунке:
Можно также отобразить информацию обо всех индексах конкретной таблицы:
Выполнив команду SHOW PROCESSLIST можно увидеть список всех выполняющихся в системе запросов:
 | ||||
 | Предыдущая | Следующая |  | |
 | ||||
Если Вам нужна частная профессиональная консультация от авторов многих книг Кузнецова М.В. и Симдянова И.В., добро пожаловать в наш Консультационный Центр SoftTime.
