Основные команды SQL. Шпаргалка.

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе баз данных.  В этой статье будет рассказано о часто используемых командах SQL. Идеально подойдёт для тех, кто хочет освежить свои знания об SQL.

Для примеров мы будем использовать OC Debian, MariaDB и консоль. Поехали…

Настройка базы данных для примеров.

Если у вас не стоит база данных, ставим ее.

apt-get install mariadb-server

Подключаемся к серверу :

mysql -u root -p

• -u : User.
• -p : Password.
• -h : Host.

Создаем новую базу данных testdb :

CREATE DATABASE testdb;

Показать доступные базы данных :

SHOW DATABASES;

Выбрать базу данных testdb для использования :

USE testdb;

Удаление базы данных :

DROP DATABASE database_name;

Работа с таблицами.

Создадим таблицу со столбцами id, user, pass, data. Причем id будет автоматически увеличивать свое значение :

CREATE TABLE test_tbl(
   id INT NOT NULL AUTO_INCREMENT,
   user VARCHAR(100) NOT NULL,
   pass VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY ( id )
   );

• INT : тип столбца среднее целое число. Подписанный диапазон составляет от -2147483648 до 2147483647 .
• VARCHAR : тип строка переменной длины ,может содержать буквы, цифры и специальные символы(100 , максимально сто символов).
• NOT NULL : столбец не может не содержать значение ( не может быть пустым).
• AUTO_INCREMENT : создает уникальный идентификатор при вставке новой записи  в таблицу.
• PRIMARY KEY ( id ) : данное ограничение позволяет однозначно идентифицировать каждую запись в таблице. Первичный ключ должен содержать уникальные значения. Первичный ключ не может содержать NULL значений. Каждая таблица должна иметь первичный ключ, и каждая таблица может иметь только один первичный ключ.
• DATA : тип дата. Формат: гггг-ММ-ДД.

Просмотр таблиц в базе :

SHOW TABLES;

Просмотра сведений о таблице :

DESCRIBE table_name;

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

INSERT INTO test_tbl (user, pass, data)
  VALUES ('root', '12345', '2019-11-24');

Если заполняем все столбцы, можно просто перечислить значения :

INSERT INTO test_tbl
  VALUES ('2','test', 'test123', '2019.11.24');

Обновление данных в таблице. Скажем заменим поля user и pass для id 1 :

UPDATE test_tbl
  SET user = 'test', pass = 'root'
  WHERE id = 1 ;

WHERE это условие при котором будет произведена замена.

Удаление всех данных из таблице :

DELETE FROM table_name;

Удаление таблицы :

DROP TABLE table_name; 

Создание запросов.

SELECT

SELECT используется для получение данных. Давайте получим значения столбца user и pass .

SELECT user, pass
  FROM test_tbl;

Или получим всю таблицу :

SELECT * FROM test_tbl;

SELECT DISTINCT

В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.

SELECT DISTINCT col_name1, col_name2, …
  FROM table_name;

WHERE

В запросе мы можем использовать всяко разные условия. Выведем например все данные где user = ‘test’ :

SELECT * FROM test_tbl
  WHERE user = 'test';

• сравнение текста;
• сравнение численных значений;
• логические операции AND (и), OR (или) и NOT (отрицание).

ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

SELECT * FROM test_tbl ORDER BY id DESC;

BETWEEN

С помощью BETWEEN мы можем выбрать определенный промежуток. Могут использованы числовые и текстовые значения, а также даты. Например с 2 по 4 запись :

SELECT * FROM test_tbl
  WHERE id BETWEEN 2 AND 4;

LIKE

Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.

• _ — Подчеркнутый символ представляет собой один символ .
• % — Знак процента представляет нулевой, один или несколько символов.
• WHERE name LIKE ‘text%’ : Находит любые значения, начинающиеся с «text» .
• WHERE name LIKE ‘%text’ : Находит любые значения, заканчивающиеся на «text» .
• WHERE name LIKE ‘%text%’ : Находит любые значения, которые имеют «text» в любой позиции .
• WHERE name LIKE ‘_text%’ : Находит любые значения, которые имеют «text» во второй позиции .
• WHERE name LIKE ‘text_%_%’ : Находит любые значения, начинающиеся с «text» и длиной не менее 3 символов .
• WHERE name LIKE ‘text%data’ : Находит любые значения, начинающиеся с «text» и заканчивающиеся на «data» .

IN

С помощью оператора IN можно задать несколько значений в WHERE.

SELECT <столбец 1>, <столбец 2>, …
  FROM <table_name>
  WHERE <имя столбца> IN (<значение 1>, <значение 2>, …); 

Агрегатные функции

• COUNT (Имя столбца) — возвращает количество строк
• SUM ( Имя столбца ) — возвращает сумму значений в данном столбце
• AVG ( Имя столбца ) — возвращает среднее значение данного столбца
• MIN ( Имя столбца ) — возвращает наименьшее значение данного столбца
• MAX ( Имя столбца ) — возвращает наибольшее значение данного столбца

SELECT SUM(id) FROM test_tbl ;

SELECT MAX(id) FROM test_tbl ;

SELECT COUNT(user) FROM test_tbl
WHERE id = 1 ;

Резервное копирование базы и восстановление.

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

mysqldump -uroot -p testdb > /tmp/test_copy.sql

• -u : User
• -p : Password
• -h : Host
• testdb : Name data base.
• test_copy.sql : Файл дампа.

Если нужно создать копию нескольких баз, то указываем их после параметра -B

mysqldump -uroot -p -B testdb testdb1 testdb2 > /tmp/test_copy.sql

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

mysqldump -uroot -p -A > /tmp/all_base.sql

Восстановление дампа базы :

mysql -uroot -p testdb < /tmp/testdb_copy.sql

Или через mysql-console:

mysql> use testdb;
mysql> source /tmp/testdb_copy.sql

На этом мы закончим с базовыми командами SQL )))