SQL манипулирование данными

Создать БД
CREATE DATABASE имя_бд;
CREATE DATABASE mybase01;
-------------------------

Выбираем и используем БД
USE имя_бд;
USE mybase01;
-------------------------

Создать таблицу
Синтаксис:
CREATE TABLE имя_таблицы(
    имя_поля1 тип_данных_поля01,
    имя_поля2 тип_данных_поля02
);

Пример:
CREATE TABLE fg(
    atr01 CHAR(3),
    atr02 CHAR(4)
);
CREATE TABLE fg(
    atr01 CHAR(3) NOT NULL, -(значение не равно NULL)
    atr02 CHAR(4),
    atr03 INT (2) NOT NULL DEFAULT 10, -(по умолчанию равно '10')
);
-------------------------

Создание id в таблице
Пример 1: Создание id после создания таблицы
ALTER TABLE email_list ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
Пример 2:
CREATE TABLE guitarwars (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        DATA DATETIME,
        name CHAR (20),
        score CHAR (9)
);
-----------------------------------------------------

Удалить таблицу
DROP TABLE имя_таблицы;
DROP TABLE my_table;
-------------------------------------------------------------------------

Вставить значения в таблицу:
Синтаксис:
INSERT INTO Имя таблицы (имя_колонки1, имя_колонки2, ...) VALUES ('значение1', 'значение2', ...);
INSERT INTO 'rates' ('name','value') VALUES ('руб', 12);
INSERT INTO 'users' ('firstname','lastname') VALUES ('vlad', 'ivanoff'), ('ivan','petroff');
-------------------------------------------------

Извлечь значения из таблицы
Синтаксис:
SELECT перечень_колонок FROM имя_таблицы;
Пример:
SELECT first_name, last_name FROM people;
SELECT * FROM people; -(выбрать все)
-------------------------

Извлечь значения из таблицы c определенным значением
Синтаксис:
SELECT перечень_колонок FROM имя_таблицы WHERE имя_колонки="нужное_значение";

Пример:
SELECT * FROM student WHERE age=21;
SELECT first_name, last_name FROM student WHERE age=21 AND study="IT";
SELECT * FROM student WHERE info IS NULL; -- поиск NULL

Извлечение последних символов
SELECT какой_край(название_столбца, кол-во_символов) FROM имя_таблицы
SELECT RIGHT(YEAR, 2) FROM car_table;
SELECT LEFT(sex,1) FROM car_table;

Извлечение символов до запятой
SELECT SUBSTRING_INDEX(имя_столбца, символ_который ищем , до_какого_симв) FROM имя_таблицы
SELECT SUBSTRING_INDEX(location, ',',1) FROM people; -- ищем до первой запятой

Извлечь часть строки
SELECT SUBSTRING(строка,начало,длина);
SELECT SUBSTRING('МОРеутов',3,6); -- Реутов
-----------------------------------------------------

Извлечь значения из таблицы с упорядочевыванию данных
SELECT * FROM имя_табицы ORDER BY имя_столбца тип_порядка
Пример:
SELECT * FROM guitarwars ORDER BY score DESC - по убыванию (первый самый большой)
SELECT * FROM guitarwars ORDER BY score ASC  по возрастанию (первый самый маленький)
-----------------------------------------------------

Операторы для условия
= -- равно
> -- больше
< -- меньше
>= -- больше или равно (name>='Г' - начинается с буквы Г и следующих букв)
<= -- меньше или равно
<> -- не равно
LIKE -- подобный (location LIKE '%CA') % - произвольный; _ - один произвольный символ
        name LIKE '_им'
Логические операторы:
AND -- и
OR -- или
BETWEEN AND -- между (calories BETWEEN 30 AND 60) (отменьшего числа к большему)
IN -- выбор из списка, заменяет несколько OR
    WHERE rating IN ('Оригинально','Восхитительно','Потрясно');
NOT IN -- -- выбор значений исключающий списка, заменяет несколько OR
    WHERE rating NOT IN ('Скучно','Банально','Плохо');
WHERE NOT имя_колонки (BETWEEN|LIKE)
-----------------------------------------------------

Нестрогие запросы LIKE
SELECT * FROM people WHERE Sunname LIKE '%Mc%'; --MacDonalds, MacQunne
LIKE '%л' -- строка заканчивается на букву Л
LIKE '% для%' -- пробел перед и после ДЛЯ
LIKE '%ор %'  -- строка заканчивается на ОР
LIKE 'та%' -- слово начинается на ТА
LIKE '%ли%' -- все строки с ЛИ
-----------------------------------------------------

Ограничение на кол-во результатов по запросу
LIMIT
Пример:
LIMIT 5 -- вывод первых пяти записей
LIMIT 10, 5 -- вывод с 10 по 15 записей
-----------------------------------------------------

Вывести описание таблицы
DESC имя_таблицы;
DESC database01;
SHOW CREATE TABLE имя_таблицы; -- показывает как была создана таблица
SHOW COLUMNS FROM имя_таблицы; -- показать все столбцы
-----------------------------------------------------

Обновить значение
UPDATE имя_таблицы SET колонка_01='значение_01', колонка_02='значение_02' WHERE условие
UPDATE rates SET VALUE='$new_rate' WHERE name='USD'
UPDATE drinks SET cost=cost+1 WHERE name="Blues" OR name="Clubs"
UPDATE my_contact SET state=RIGHT(location,2); --два последних символа из location
-----------------------------------------------------

Удалить данные из таблицы
DELETE FROM имя_таблицы; -в этом случае таблица полностью очиститься
DELETE FROM имя_таблицы WHERE имя_столбца='его_значение'; -в этом случае удалиться только некоторые строки
Пример:
DELETE FROM email_list WHERE email='[email protected]';
DELETE FROM guitarwars WHERE screenshot IS NULL;
-----------------------------------------------------

Очистить всю таблицу
DELETE FROM имя_таблицы
DELETE FROM clown_info
-----------------------------------------------------

Изменение структуры таблицы

ALTER TABLE имя_таблицы КОМАНДА_SQL имя_колонки тип_колонки;

Команды_SQL:
DROP COLUMN - удалить колонку
ADD COLUMN - добавить колонку
CHANGE COLUMN - изменить колонку (имя и тип данных)
MODIFY COLUMN - модифицировать колонку (тип данных и позиция)
RENAME TO - переименовать таблицу

Примеры:
ALTER TABLE guitarwars DROP COLUMN age;
ALTER TABLE guitarwars ADD COLUMN age INT FIRST; - добавить этот столбец в начало таблицы
ALTER TABLE guitarwars ADD COLUMN age INT after name; - добавить этот столбец после столбца name
ALTER TABLE guitarwars CHANGE COLUMN score high_score INT;
ALTER TABLE guitarwars MODIFY COLUMN DATE DATETIME AFTER age;
ALTER TABLE guitarwars MODIFY COLUMN description CHAR (120); -- увеличить объем строки
ALTER TABLE my_contacts ADD COLUMN id INT(3) AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
ALTER TABLE contact RENAME TO my_contacts;
-----------------------------------------------------

Условие
CASE
    WHEN стобец1=значение1
    THEN новое_значение1
    WHEN  столбец2=значение2 (AND/WHERE столбец3=значение3)
    THEN новое_значение2
    ELSE новое_значение3
END;
//Используется с UPDATE SELECT INSERT DELETE
//как только нашлось одно значение, код переходит к END
//ELSE можно не включать в код

Пример:
UPDATE movie
SET category=
CASE
    WHEN DRAMA=TRUE THEN 'drama'
    WHEN  COMEDY=TRUE THEN 'comedy'
    ELSE 'other'
END;
-----------------------------------------------------
Арифметические операции
SUM(имя_столбца) - сумма
AVG(имя_столбца) - среднее значение
MIN(имя_столбца) - минимальное значение
MAX(имя_столбца) - максимальное значение
COUNT(имя_столбца) - посчитать кол-во полученных записей
DISTINCT имя_столбца - показать те значения, которые не повторяются

SELECT SUM(sales) FROM sales WHERE name="anne";
SELECT DISTINCT sale FROM sales WHERE name="anne";
-----------------------------------------------------
AS - псевдоним
-----------------------------------------------------

Одновременное выполнение команд
CREATE SELECT INSERT
Пример 01:
CREATE TABLE profession(
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    prof VARCHAR(10)
);
INSERT INTO profession(prof)
    SELECT profession FROM my_contacts;

Пример 02:
CREATE TABLE profession(
    id INT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    prof VARCHAR(10)
)AS
    SELECT profession FROM my_contacts;
-----------------------------------------------------

Создание внешнего ключа
Пример01:
CREATE TABLE my_contacts( // первая таблица
    contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ....
);

CREATE TABLE interest(
    int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, //первичный ключ
    interest VARCHAR (50) NOT NULL,
    contact_id_fk INT NOT NULL, //заготовка для внешнего ключа
    CONSTRAINT my_contacts_contact_id_fk //ограничению присваивается имя из какой таблицы ключ
    FOREIGN KEY (contact_id_fk) //имя внешнего ключа
    REFERENCES my_contacts (contact_id)  //указываем из какой таблицы ключ и как он там называется
);

Пример 02:
CREATE TABLE prof(
    prof_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    prof CHAR(25)
);

CREATE TABLE people(
    id_people INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (20) NOT NULL,
    last_name VARCHAR (20) NOT NULL,
    prof_id_fk INT NOT NULL,
    CONSTRAINT prof_prof_id_fk
    FOREIGN KEY (prof_id_fk)
    REFERENCES prof (prof_id)
);
-----------------------------------------------------

Запросы с использованием нескольких таблиц
    Соединение двух таблиц

имя_таблицы.имя_столбца
SELECT my_contacts.profession FROM my_contacts;

CROSS JOIN -- перекрестное соединение
SELECT t.toy, b.boy FROM toys AS t
    CROSS JOIN boys AS b;
или
SELECT toys.toy, boys.boy
    FROM toys, boys;
--к каждому из таблицы boys подставляются все toys
--обычно этими соединениями проверяют скорость СУБД

INNER JOIN -- внутреннее соединение
--вывести имена людей и профессии
SELECT mc.last_name, mc.first_name, p.profession
FROM my_contacts AS mc
    INNER JOIN profession AS p
ON mc.prof_id_fk=p.prof_id;

--
ON mc.prof_id_fk=p.prof_id; -- это эквивалентное соединение
ON mc.prof_id_fk<>p.prof_id; -- это не эквивалентное соединение
--

Запрос к верхнему примеру №2:
SELECT pp.last_name, pp.first_name, p.prof
FROM people AS pp
    INNER JOIN prof AS p
ON pp.prof_id_fk=p.prof_id;

NATURAL JOIN -- естественное соединение
--используются если имена столбов одинаковые
CREATE TABLE toys(
    toy_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    toy VARCHAR(10) NOT NULL
);

CREATE TABLE boy(
    boy_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    boy VARCHAR (10) NOT NULL,
    toy_id INT NOT NULL,
    CONSTRAINT toys_toy_id
    FOREIGN KEY (toy_id)
    REFERENCES toys (toy_id)
);

SELECT boys.boy, toys.toy
FROM boys
    NATURAL JOIN toys (WHERE);


Внешние соединения
Левое внешнее соединение
LEFT OUTER JOIN //перепибаем все записи из левой таблицы и ищем соответсвия среди записей правой таблицы
//то есть выбираются все данные которые пересекаются в таблицах + остальные записи из "левой" таблицы
//Таблица следующая после FROM, но ДО JOIN считается левой, а ПОСЛЕ JOIN - правой

Пример 01:
SELECT g.girl, t.toy
FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id=t.toy_id;
//Это выводит не только какие игрушки какой девочки принадлежат но и также выведет NULL у игрушке, которая не принадлежит никакой девочке

Правое внешнее соединение
RIGHT OUTER JOIN
//ищет в левой таблице соответсвия правой

Пример 01:
SELECT g.girl, t.toy
FROM toys t
RIGHT OUTER JOIN girls g
ON g.toy_id=t.toy_id;

    Соединение трех и более таблиц
????
-----------------------------------------------------

Запросы EXISTS и NOT EXISTS
SELECT mc.first_name, firstname, mc.last_name lastname, mc.email email //назначаем псевдонимы
FROM my_contacts mc
WHERE NOT EXISTS //Находит тех людей из таблицы mc которых нет в таблице jc
(SELECT * FROM job_current jc
WHERE mc.contact_id = jc.contact_id);

SELECT mc.first_name, firstname, mc.last_name lastname, mc.email email
FROM my_contacts mc
WHERE EXISTS //Находит тех людей из таблицы mc которе есть в таблице ci
(SELECT * FROM contact_interest ci
WHERE mc.contact_id = ci.contact_id);
-----------------------------------------------------

Союзы
UNION -- нужны когда нужно вывести одинаковые значения из 2-х и более таблиц

Пример 01:
SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listing
ORDER BY title;
-----------------------------------------------------

CREATE VIEW -- представления
CREATE VIEW web_desingers AS
SELECT mc.first_name, mc.last_name, mc.phone, mc.email
FROM my_contacts mc
NATURAL JOIN job_desired id
WHERE jd.title='Веб-дизайнер';

//Теперь вместо запроса поиска веб-дизайнера достаточно написать:
SELECT * FROM web_desingers;

Удалить представление
DROP VIEW web_desingers;
-----------------------------------------------------

Транзакция

01)Создаем БД которая поддерживает транзакции
CREATE TABLE(
)ENGINE=BDB DEFAULT CHARSET=utf-8;
или
CREATE TABLE(
)ENGINE=InnoDB DEFAULT CHARSET=utf-8;

или просто меняем ядро
ALTER TABLE имя_таблицы TYPE = InnoDB;

02)Создаем транзакцию

START TRANSACTION; //начало транзакции
.... //SQL-код
ROLLBACK; //передемали - откатили БД к состоянию до транзакции

START TRANSACTION; //начало транзакции
.... //SQL-код
COMMIT; //закрепили транзакцию. Все операции выполнены.
-----------------------------------------------------