База данных
- это хранилище информации. С ней можно работать через приложение с помощью API запросов, проверяя заодно согласованность приложения с базой данных и напрямую через SQL, если backend не готов.
Реляционные базы данных состоят из
таблиц и связей между ними.
Таблица - совокупность строк(записи) и
столбцов(полей). В пересечении значений
ячейка(данные).
Первичный ключ - это поле с неповторимыми значениями
в таблице, нужное для индификации, бывает составной из нескольких
полей.
СУБД -система управления базами данных
Список популярных СУБД:
Postgre SQL
Oracle
My SQL
Microsoft SQL Server
Комментарий - это поясняющий текст, который вставляют
в запрос.
-- однострочный комментарий SQL
/* многострочный комментарий SQL */
Запрос - команда к базе данных.
Оператор - спецслово или символ (пример: SELECT).
Бесплатные программы для установки локального сервера:
MAMP - для Windows, Mac
XAMPP - для Linux
Типы данных:
1. Числовые
INT - числа(integer) целочисленный тип
REAL - число с плавающей точкой(до 6 десятичных
разрядов)
2. Строковые, значения в кавычках пример: "Значения"
VARCHAR(n) - небольшая строка до 255 символов, n -
ограничение. Заводит любую строку любой длины короче n.
TEXT - для большого объема любой длинны
3. Дата и время, значения заключают в кавычки.
timestamp - дата и время
DATE - дата
4. Логический
boolean - логический тип данных
TRUE - истина
FALSE - ложь
NULL - неизвестно
Базы данных и консоль
psql - это клиент, который помогает работать с
Postgre SQL
Подключиться к удаленному серверу команда:
psql -Uимя пользователя ввести пароль и нажать
ENTER
- Ключ U указывает, что для получения используется
имя пользователя
команды в psql:
\? - справочник команд
q - выйти из справочника команд
\q - выйти из psql
\l - покажет список баз данных
\c name - подключит к базе данных с названием name
\dt - список таблиц в базе данных
ДАМП базы - это файл набором SQL команд, который
позволяет воссоздать базу данных, таблицы и восстановить информацию
- создание дампа:
pg_dump имя базы данных > путь и имя файла дампа.
pg_dump - встроенная СУБД PostgreSQL - вводить
снаружи без подключения к СУБД
имя базы данных - имя нужной БД
путь и имя файла дампа - имя и путь куда сохранить
дамп.
!!! У пользователя под которым вводиться команда должны быть права
на это !!!
- восстановление дампа:
psql имя базы данных < путь и имя дампа.
- создать базу данных:
createdb -T templateO имя базы данных templateO -
шаблон, который создаст чистую БД
Связи с таблицами
ER-диаграммы - визуализирует структуру баз данных
PK - первичный ключ
FK - внешний(вторичный)ключ
Базы данных и таблицы
CREATE DATABASE название базы; - команда на создание
базы данных.
DROP DATABASE название базы; - команда на удаление
базы данных.
CREATE TABLE название базы (id INT NOT NULL AUTO_INCREMENT, name
VARCHAR (30), email VARCHAR (40), bio TEXT, birth DATE, PRIMARY
KEY(id));
- создание таблицы.
Где:
id, name, email, bio, birth - придуманные названия
полей.
INT, VARCHAR, TEXT, DATE - типы данных.
NOT NULL - не может быть пустым.
(30), (40) - количество символов в поле.
AUTO_INCREMENT - автозаполнение следующим по счету
значением.
PRIMARY KEY(id) - назначает поле id первичным ключом
должно быть уникальное не повторяемое значение.
DROP TABLE название таблицы; - команда на удаление
таблицы.
ALTER TABLE название таблицы ADD название поля тип данных;
- добавить поле в таблицу.
ALTER TABLE название таблицы DROP COLUMN название поля;
- удалить поле из таблицы.
INSERT INTO название таблицы (name, email, bio, birth) VALUES
("ВАНЯ", "vano@yandex.com", "Родился в Самаре, живёт в Москве",
1970-03-02);
- добавление данных в таблицу.
- названия полей в свободном порядке через запятую
- значения через запятую в том же порядке в каком записаны названия
полей
- поле первичного ключа вписывать не нужно у него само заполнение
- в те поля что не вписали значение присвоится NULL
ALTER TABLE название таблицы CHANGE название поля которое меняем
название нового поля DATE NOT NULL;
- изменение поля в таблице.
- если название не меняем пишем старое
DATE - типы данных
NOT NULL - не может быть пустым можно использовать
для обязательных полей
INSERT INTO название таблицы (названия полей через запятую) VALUES
(значения через запятую), (значения через запятую), (значения через
запятую);
- множественные добавления полей.
UPDATE название таблицы SET название поля = новое значение WHERE
условие;
- обновление значений в поле.
- пример UPDATE man SET name = "Саша" WHERE id = 5;
UPDATE название таблицы SET название поля = новое значение,
название поля = новое значение, название поля = новое значение WHERE
условие AND условие_2;
- обновление сразу нескольких полей.
DELETE FROM название таблицы WHERE условие; или
DELETE FROM название таблицы; - удаление записей из
таблицы.
TRUNCATE название таблицы; - очистка таблицы.
Выборка из таблицы
SELECT * FROM название таблицы; - выбрать все
значения из таблицы.
* - обозначает выбрать всё.
SELECT названия полей через запятую если их несколько FROM название
таблицы;
- выбрать определенные поля.
SELECT * FROM название таблицы WHERE условие AND условие_2 OR
условие_3;
- выбрать с несколькими условиями.
Операторы
> - больше
< - меньше
= - равно
<> или != - не равно
>= - больше либо равно
<= - меньше либо равно
IS- является
NOT - не является или IS NOT к примеру IS NOT NULL
AND - все условия соответствуют
OR - хотя бы одно условие соответствует
- условий может быть сколько угодно
- WHERE условие AND NOT условие_2 когда соответствует
первому условию и не соответствует второму
CASE - в случае когда
CASE WHEN условие THEN результат (если условие
верное) ELSE результат_2 (если условие не верное)
END;
SELECT DISTINCT название поля FROM название таблицы;
- чтоб исключить повторяющиеся значения.
SELECT * FROM название таблицы LIMIT 2; - задать
лимит 2 строки.
SELECT * FROM название таблицы LIMIT 2,3; - пропустит
2 строчки, а затем 3 выведет 3-5 строчку.
SELECT * FROM название таблицы ORDER BY id LIMIT 2; -
отсортировать по полю.
- LIMIT всегда в конце,
ORDER BY после WHERE сортирует без
операторов в порядке возрастания.
SELECT * FROM название таблицы ORDER BY id DESC LIMIT 2;
- отсортировать в порядке убывания.
SELECT * FROM название таблицы WHERE id BETWEEN 2 AND 6 AND id <>
4;
- выбрать где id от 2 до 6 включительно, без 4 id.
SELECT * FROM название таблицы WHERE id IN (2, 8); -
выберет id 2 и 8.
SELECT * FROM название таблицы WHERE id NOT IN (2, 8);
- исключит из выбора id 2 и 8.
SELECT * FROM название таблицы WHERE name LIKE "И%";
- выбрать все name начинающиеся на И.
'%ИВА%' - выберет все где в середине ИВА
_ - заменяет одно значение
% - заменяет любое количество символов и даже
пустоту
Если нужно найти _ или % символы их нужно экранировать. Экранирующий
символ любой, главное чтоб не встречался в сочетании со значениями _ и
% например / вот пример с ним:
WHERE название столбца LIKE '/%' ESCAPE '/' или c ! WHERE название
столбца LIKE '!%' ESCAPE '!'
Индексы нужны для ускорения поиска по полю
CREATE INDEX название индекса ON название таблицы (поле к которому
добавляем индекс название);
- добавить к полю индекс.
ROP INDEX название индекса ON название таблицы; -
удалить индекс.
PRIMARY KEY(id) - создать первичный ключ для поля id сделать его
ключом
FOREIGN KEY(название вторичного ключа) REFERENCES название таблицы
куда ссылается ключ(название поля из таблицы для сращивания); -
создаем вторичный ключ. -
ПИШУТСЯ В КОНЦЕ ЗАПРОСА ПРИ СОЗДАНИИ И РЕДАКТИРОВАНИИ
ТАБЛИЦЫ
FROM название таблицы сращивания
INNER JOIN название таблицы которую сращивают
ON название таблицы сращивания.название поля
в этой таблице для склейки = название таблицы которую
сращивают.название поля в этой таблице для
склейки;
пример:
FROM people INNER JOIN orders ON people.id =
orders.personid;
- команда для сращивания.
SELECT имя таблицы.название поля - при сращивании
указывают имя таблицы к полю при выводе
Данные которые он берет - это строки из выведенных полей
Псевдонимы - переименование полей во время выборки
пример: SELECT name AS ИМЯ
SELECT CONCAT('Имя: ', name, ', День рождения: ', birth) AS
Информация
- объединит несколько полей и фраз в поле информация и выведет:
Информация
Имя: Алексей, День рождения: 03.04.2010
SELECT p.id, p.name FROM people AS p; - псевдонимы
можно использовать для названий таблиц.
COUNT(название столбца) - возвращает число строк в
столбце.
COUNT(*) - возвращает общее количество строк в
таблице.
COUNT(DISTINCT название столбца) - возвращает
количество уникальных строк в столбце.
SELECT COUNT(id) - вызывает количество id
SELECT MIN(price) - выведет минимальное значение
price
SELECT MAX(price) - выведет максимальное значение
price
SELECT AVG(price) - выведет средне арифметическое
значение price
SELECT SUM(price) - выведет сумму значений price
SELECT UCASE(title) AS title - выведет все значения
title в верхнем регистре
SELECT LCASE(title) AS title - выведет все значения
title в нижнем регистре
GROUP BY - группирует по полям при использовании
функции, вписывается поле которое не использовало функцию, но функция
работала на него
пример:
SELECT price, COUNT(price) FROM shop GROUP BY price HAVING
COUNT(price) > 1;
- HAVING это условие для функций