SQL для начинающих

() translation by (you can also view the original English article)

Большинство современных веб-приложений сегодня взаимодействуют с базами данных, как правило, с языком SQL. К счастью для нас, этот язык довольно легко изучить. В этой статье мы собираемся начать с некоторых базовых SQL-запросов и использовать их для взаимодействия с базой данных MySQL.

Что вам потребуется

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

Следующие приложения упрощают установку MySQL и phpMyAdmin на локальный компьютер:

WAMP для Windows
MAMP для Mac

Мы будем использовать командную строку для запросов. WAMP уже поставляется с консолью MySQL. Для MAMP вы можете прочитать эту статью.

CREATE DATABASE: Создание базы данных

Вот наш самый первый запрос. Мы собираемся создать базу данных для работы.

Сначала откройте консоль MySQL и войдите в систему. Для WAMP пароль по умолчанию пуст. Для MAMP пароль по умолчанию должен быть «root».

После входа введите этот запрос и нажмите enter:

1
2	CREATE DATABASE my_first_db;

Обратите внимание, что в конце запроса добавляется точка с запятой (;), как в конце строк кода.

Кроме того, специальные слова "CREATE DATABASE" не зависят от регистра, а также все специальные слова в SQL. Но ради удобочитаемости мы будем писать их в верхнем регистре.

Необязательно: Кодировка (Character Set) и Сравнение (Collation)

Если вы хотите установить кодировку и сравнение по умолчанию, вы можете написать тот же запрос, как это:

1
2	CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Здесь приведен список поддерживаемых наборов кодировок и сравнений в MySQL.

SHOW DATABASES: Список всех баз данных

Этот запрос используется для получения списка всех имеющихся у вас баз данных.

DROP DATABASE: удаление базы данных

Вы можете удалить существующую базу данных этим запросом.

Будьте осторожны с этим запросом, потому что он не дает вам никаких предупреждений. Если у вас есть таблицы и данные в базе данных, все они будут удалены мгновенно.

USE: выбор базы данных

Это технически не запрос. Это «утверждение» и не требует точки с запятой в конце.

Он сообщает MySQL о выборе базы данных по умолчанию для работы с текущим сеансом. Теперь мы готовы создавать таблицы и делать другие вещи в этой базе данных.

Что такое таблица базы данных?

Вы можете представить таблицу базы данных, такую как электронная таблица или файл csv, который содержит структурированные данные.

Как и в этом примере, таблицы имеют имена столбцов и строки данных. С SQL-запросами мы можем создавать эти таблицы. Мы также можем добавлять, читать, обновлять и удалять данные.

CREATE TABLE: создание таблицы

С помощью этого запроса мы можем создавать таблицы в базе данных. К сожалению, документация MySQL не очень дружелюбна для новичков. Структура этого типа запросов может быть очень сложной, но мы начнем с простой.

Следующий запрос создаст таблицу с двумя столбцами.


CREATE TABLE users (
  username VARCHAR(20),
	create_date DATE
);

Обратите внимание, что мы можем написать запрос в нескольких строках и даже использовать табуляцию для отступов.

Первая строка проста. Мы просто создаем таблицу с именем «users». После этого в круглых скобках у нас находятся столбцы таблицы списков, разделенные запятыми. После каждого имени столбца у нас есть тип данных, например VARCHAR или DATE.

VARCHAR(20) означает, что столбец является строковым типом и может содержать не более 20 символов. DATE также является типом данных, который специально используется для хранения дат в этом формате: «ГГГГ-ММ-ДД».

Первичный ключ - PRIMARY KEY

Перед тем, как запустить этот запрос, мы должны также включить столбец для user_id, который будет PRIMARY KEY. Не вдаваясь в подробности, вы можете думать о PRIMARY KEY как о способе определения каждой строки данных в таблице.

Теперь запрос будет выглядеть следующим образом:


CREATE TABLE users (
	user_id INT AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(20),
	create_date DATE
);

INT делает его 32-битным целым типом (т.е. числовым). AUTO_INCREMENT автоматически генерирует новый номер идентификатора каждый раз, когда мы добавляем новые строки данных. Это не требуется, но делает добавление гораздо более удобным.

Этот столбец не должен быть целым числом, но это наиболее часто используемый тип. Наличие колонки PRIMARY KEY также не требуется, но настоятельно рекомендуется для хорошего проектирования и производительности базы данных.

Давайте запустим запрос:

SHOW TABLES: Список всех таблиц

Этот запрос позволяет получить список таблиц, которые в настоящее время находятся в базе данных.

EXPLAIN: Показать структуру таблицы

Чтобы просмотреть структуру существующей таблицы, вы можете использовать этот запрос.

Поля (столбцы) перечислены в результатах с их свойствами.

DROP TABLE: удаление таблицы

Подобно DROP DATABASES, этот запрос удаляет таблицу и ее содержимое без предупреждения.

ALTER TABLE: изменение таблицы

Этот запрос также может иметь довольно сложную структуру из-за множества изменений, которые он может выполнять на таблице. Давайте рассмотрим несколько простых примеров.

(Обязательно заново создайте таблицу, которую мы только что удалили, или следующие запросы, очевидно, не будут работать.)

Добавление столбца

1
2	ALTER TABLE users
3	ADD email VARCHAR(100)
4	AFTER username;

Благодаря читабельности SQL, я не думаю, что запрос даже нуждается в объяснении.

Удаление столбца

Это было очень просто. Но используйте его с осторожностью, поскольку он удаляет данные без предупреждения.

Повторно добавьте столбец электронной почты, потому что мы будем использовать его позже:

1
2	ALTER TABLE users
3	ADD email VARCHAR(100)
4	AFTER username;

Изменение столбца

Иногда вы можете захотеть изменить свойства столбца, поэтому вам не нужно удалять и заново создавать его.

Это переименовало столбец имени пользователя в «user_name» и изменило тип с VARCHAR(20) на VARCHAR(30). Подобное изменение не должно нарушать какие-либо существующие данные в таблице.

INSERT: Добавление данных в таблицу

Давайте добавим некоторые данные в таблицу с помощью этого запроса.

Как вы можете видеть, VALUES() содержит список значений полей, разделенных запятыми. Строковые значения заключены в одинарные кавычки. И значения должны быть в порядке столбцов, которые были определены при создании таблицы.

Обратите внимание, что первое значение равно NULL для поля первичного ключа, которое мы назвали «user_id». Мы делаем это таким образом, чтобы автоматически генерировался идентификатор, так как столбец AUTO_INCREMENT. При вставке строки данных в первый раз, идентификатор будет 1. Следующая вставленная строка будет 2 и так далее...

Альтернативный синтаксис

Вот еще один синтаксис для вставки строк.

На этот раз мы используем ключевое слово SET вместо VALUES, и за ним не следуют скобки. Здесь есть несколько моментов:

Столбец можно опустить. Например, мы не назначили значение user_id, которое по умолчанию будет использовать функцию AUTO_INCREMENT. Если вы опустите столбец VARCHAR, по умолчанию будет пустая строка (если другое значение по умолчанию не было указано во время создания таблицы).
В каждом столбце должно быть указано его имя. Из-за этого они могут быть в любом порядке, в отличие от предыдущего синтаксиса.

Альтернативный синтаксис 2

Вот еще другой синтаксис.

Опять же, поскольку каждый столбец имеет имя, они могут быть в любом порядке.

LAST_INSERT_ID()

Вы можете использовать этот запрос для получения идентификатора AUTO_INCREMENT для последней вставленной строки в текущем сеансе.

NOW()

Я думаю, что самое время продемонстрировать, как вы можете использовать функцию MySQL внутри своих запросов.

Функция NOW() возвращает текущую дату. Таким образом, вы можете использовать ее для автоматической установки столбца DATE в текущий день при вставке новой строки.

Обратите внимание, что мы получили предупреждение от MySQL, но это не имеет большого значения. Причина в том, что NOW() фактически возвращает информацию о времени.

Но созданный столбец create_date содержит только дату, а не время, в результате чего возвращаемые данные были обрезаны. Вместо этого мы могли бы использовать функцию CURDATE(), которая возвращает только дату, но данные, хранящиеся в конце, будут одинаковыми в любом случае.

SELECT: чтение данных из таблицы

Очевидно, что данные, которые мы добавили, были бы бесполезны, если мы не сможем их прочитать. Здесь вызывается запрос SELECT.

Вот простейший возможный запрос SELECT для чтения из таблицы:

В этом случае звездочка (*) означает, что мы попросили извлечь все столбцы из таблицы. Если вам нужны только определенные столбцы, запрос будет выглядеть так:

WHERE

Чаще всего нас интересуют только некоторые строки, а не все. Например, допустим, нам нужен адрес электронной почты для пользователя "nettuts".

Думайте об этом, как конструкции IF. WHERE позволяет задавать условия в запросе для получения результатов, которые вы ищете.

Обратите внимание, что для условия равенства используется только один знак равенства (=), а не двойной (==), которым вы могли бы воспользоваться при программировании.

Вы также можете использовать другие условия сравнения:

AND и OR могут использоваться для объединения условий:

Обратите внимание, что числовые значения не обязательно должны быть внутри кавычек.

IN()

Это полезно для сравнения нескольких значений.

LIKE

Это позволяет выполнять поиск с подстановками (wildcard).

В качестве подстановочного знака используется знак процента (%).

ORDER BY

Если вы хотите, чтобы результаты были возвращены в определенном порядке, используйте этот пункт:

Порядок по умолчанию - ASC (то есть восходящий). Вы можете добавить DESC для сортировки в обратном порядке.

LIMIT ... OFFSET ...

Вы можете ограничить количество возвращенных результатов.

LIMIT 2 просто получает первые 2 результата. LIMIT 1 OFFSET 2 получает 1 результат после первых 2 результатов. LIMIT 2, 1 означает то же самое, но обратите внимание, что первое число - это смещение, а второе - предел.

UPDATE: обновление данных в таблице

Этот запрос используется для обновления данных в таблице.

В большинстве случаев он используется с условием WHERE, потому что вам нужно будет обновлять только определенные строки. Если условие WHERE не предусмотрено, все строки будут обновлены с теми же изменениями.

Вы также можете использовать LIMIT, чтобы ограничить количество обновляемых строк.

DELETE: удаление данных из таблицы

Подобно UPDATE, этот запрос также обычно используется с предложением WHERE.

TRUNCATE TABLE

Чтобы удалить все содержимое таблицы, вы можете просто сделать это:

1
2	DELETE FROM users;

Но обычно лучше использовать TRUNCATE.

TRUNCATE также сбрасывает номера AUTO_INCREMENT, чтобы новая строка снова имела идентификатор 1. Но этого не происходит с запросом DELETE, и счетчик продолжает расти.

Экранирование строковых значений и специальных слов

Строковые значения

Некоторые символы должны быть экранированы, иначе у вас могут быть проблемы.

Для экранирования используется обратная косая черта (\).

Это также очень важно по соображениям безопасности. Любой ввод данных пользователя, поступающий в базу данных, должен быть надлежащим образом экранирован. В PHP вы используете функцию mysql_real_escape_string() или используете подготовленные инструкции, так как они автоматически экранируются.

Специальные слова

Поскольку MySQL имеет много специальных слов, таких как SELECT или UPDATE, вы можете предотвратить конфликт, помещая кавычки вокруг имен таблиц и столбцов. Но это не обычные кавычки; вам нужно использовать символ (`).

Предположим, вы хотите по какой-то причине добавить столбец с именем «delete»:

Заключение

Благодарю вас за чтение статьи. Надеюсь, я смог показать вам, что язык SQL прост в изучении, но при этом очень мощный.

Пожалуйста, оставьте ваши комментарии и вопросы, и всем хорошего дня!

Следуйте за нами в Твиттере или подпишитесь на Nettuts+ RSS Feed для лучших учебных пособий по веб-разработке в Интернете. Готов

Готовы ли вы перевести свои навыки на следующий уровень и начать получать прибыль от ваших скриптов и компонентов? Посмотрите наш рынок, CodeCanyon.