Шаг 5: SQL

SQL язык запросов, к базам данных (БД). В БД обычно хранятся все данные сайта, например логины и пароли пользователя, данные банковских карт, паспортные данные. Именно поэтому злоумышленники пытаются взломать и украсть таблицы. Данные можно хранить и в файле, но почти всегда удобней все же в БД. База данных состоит из таблиц, таблица — это привычное для всех отображение, например как в популярном excel. Таблица состоит из колонок и строк, строки в свою очередь состоят из ячеек. Все довольно просто.

Для удобства договоримся что команды языка будим писать заглавными буквами. Имена полей и колонок в ` ` кавычках (кнопка с буквой ё).

Для начала создадим БД с названием test:

CREATE DATABASE test;

Или удалим:

DROP DATABASE test;

Теперь для работы с БД ее нужно выбрать:

USE test;

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

  • целые числа int, bigint … 
  • строки varchar, text … 
  • данные из списка enum 
  • дата date, time, datetime  
  • и пр.

Создадим таблицу пользователей с названием user и полями:

CREATE TABLE user (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT ' ',
	`date_reg` DATETIME NOT NULL,
	`count` INT DEFAULT 0,
	PRIMARY KEY (`id`)
);

В данном примере использовалось:

  • not null — данное поле не может быть пустым;
  • значения в скобках рядом с varchar обозначают максимальную длину строки (не более 50 символов);
  • default — устанавливает значение по умолчанию;
  • int имеет допустимые пределы от -2 147 483 648 до 2 147 483 647, если нужно значение больше можно использовать bigint от -9 223 172 036 854 775 808 до 9 223 172 036 854 775 807;
  • auto_increment — если поле явно не указано то автоматически увеличит значение на 1;
  • primary key — первичный ключ, значения в данном полю должны быть уникальными и не могут повторятся в данной колонке данной таблицы, часто уникальные идентификаторы записи. В повседневной жизни примером может быть телефонный номер или номер квартиры.

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

INSERT INTO `user` VALUES ( null, 'Иван', GETDATE(), 0 ), ( 3, 'Федор', GETDATE(), 0 )
или
INSERT INTO `user` (`name`, `date_reg`) VALUES ( 'Петр', '2019-05-15 13:15:00 ' )

В данном примере использовалось:

  • getdate() — встроенная функция возвращает текущую дату в формате годмесяцчисло часы:минуты:секунды,миллисекунды например 2000-12-31 00:00:00,000;

Обратите внимание, т.к. мы указали «Федору» id=3, то «Петру» добавлен следующий по счету номер. Если мы удалим запись с id=4, то следующее добавление продолжит с 5. Это сделано для того, чтобы записи не заменялись. В результате выполнения этих 2х команд у нас получается таблица:

idnamedate_regcount
1Иван2019-05-15 13:15:000
3Федор2019-05-15 13:15:000
4Петр2019-05-15 13:15:000

Теперь можно изменить запись. Меняемые значения указываются через запятую после оператора set. После оператора where перечисляются условия которые должны выполнятся. Те поля которые попадут под данные условия будут изменены. В таких случаях и используется уникальный идентификатор (primary key), он однозначно определяет нужную строку. Если where не указан, то будут изменены все строки.

UPDATE `user` SET `count`=10, `name`='Василий' WHERE `id`=1 OR `id`=2
или
UPDATE `user` SET `count`=22 WHERE `id`>2 AND `count`<1

При выполнении первого запроса, изменится только первая запись, т.к. id=2 у нас в таблице нет. А вот выполнение второго запроса изменит 2 записи. Теперь наша таблица имеет вид:

idnamedate_regcount
1Василий2019-05-15 13:15:0010
3Федор2019-05-15 13:15:0022
4Петр2019-05-15 13:15:0022

Удалим строку с id=1:

DELETE FROM `user` WHERE `id` = 1

Удалим все записи:

DELETE FROM `user`

Самый популярный запрос — выборка. Выбрать из базы строки, чтобы нужным образом обработать, например вывести списком. После оператора SELECT можно перечислить какие именно колонки нужно вернуть, или указать символ * (звездочка), в этом случае вернутся все колонки:

Выберем все записи:

SELECT * FROM `user`

Выберем имя и дату регистрации у записи с номером меньше или равным 3:

SELECT `name`, `date_reg` FROM `user` WHERE `id` <= 3

При использовании where используются следующие логические операторы:

  • and — логическое «и»;
  • or — логическое «или»;
  • like — совпадение в строке по подстроке;
  • in — совпадение значение из указанного списка;

Все имена которые заканчиваются на букву «Р» или в середине есть буква «И»:

WHERE `name` LIKE "%р" OR `name` LIKE "%и%"

Все имена равные Федор, Василий и Иван:

WHERE `name` IN ( 'Федор', 'Василий', 'Иван' )
или
WHERE `name` =  'Федор' OR `name` =  'Василий' OR `name` =  'Иван' )

Объединение запросов, часто бывает нужно сделать выборку из 2х и более таблиц. Например у нас добавилась таблица `city`:

iduseridsname
13Москва
21Санкт-Петербург

Выбрать из таблицы `user` далее u и таблицы `city` далее c колонки: из `user` колонки `name` и `data_reg`, из `city` колонки `name`, где ячейка `userid` таблицы `city` равна `id` таблицы `user`:

SELECT user.`name`, user.`date_reg`, city.`name` as cityname FROM `user`, `city` WHERE city.`userid` = user.`id`
или
SELECT u.`name`, u.`date_reg`, c.`name` AS cityname FROM `user` u, `city` c WHERE c.`userid` = u.`id`

Во втором случае мы дали временные маркеры таблицам таблице `user` маркер u, а таблице `city` маркер c. Теперь мы можем обращаться к этим таблицам по маркерам. Внимательно, мы не можем получить таблицу с 2 одинаковыми колонками (у нас колонка name повторяется), поэтому должны также (временно) переименовать одну из колонок через оператор as (не обязательно, можно его не указывать). Результат:

namedate_regcityname 
Василий2019-05-15 13:15:00Санкт-Петербург
Федор2019-05-15 13:15:00Москва

Задание:

  • Найти типы данных колонок;
  • Разобраться с форматом даты и времени;
  • Найти все логические операторы where;
  • Прочитать про join;