SQL

us.content Export mysqldump -h 127.0.0.1 -u USER DATABASE -p > dump.sql Import wget https://site.ru/dump.sql или curl https://site.ru/dump.sql -O mysql -h 127.0.0.1 -u USER DATABASE -p < dump.sql SELECT * FROM users; Получить SELECT * FROM users WHERE id IN (3, 5, 7); Получить конкретные id можно использовать NOT IN обратное SELECT u.name, u.tel FROM users u; Получить используя псевдоним SELECT * FROM users LEFT JOIN img ON users.id_img = img.id; Получить связанные поля из другой таблицы SELECT * FROM users WHERE id_img = (SELECT id FROM img WHERE name = 'test'); Получить с вложенным запросом INSERT INTO users VALURES (NULL, name, email, tel); Вставить UPDATE users SET name = 'Ivan' where id = 1; Обновить поле name где id 1 DELETE FROM users WHERE id = 1; Удалить id 1 CONCAT(поля) объединяет поля SPACE(1) пробел, в скобках кол-во пробелов RIGHT(поле, 2) кол-во символо справо или слево LEFT lower(поле) к нижнему регистру UPPER(поле) к верхнему регистру LTRIM(поле) удаляет пробелы слева или справо RTRIM REPLACE(поле, 'что ищем', 'на что меняем') заменить символы COUNT(поля) кол-во AVG(поля) среднее SUM(поля) сумма MIN(поля) минимальное значение MAX(поля) максимальное значение AND логическое и BETWEEN диапазон значений SELECT * FROM users WHERE age BETWEEN 10 AND 20; LIKE как SELECT * FROM users WHERE name LIKE 'o%'; начинается с буквы o UNION объединяет в одну колонку SELECT name FROM users UNION SELECT title FROM product; GROUP BY группировка одинаковых полей, т.е. объединяет DISTINCT игнорует дублирующие поля ORDER BY сортировка по полю и порядку id DESC(обратно) ASC LIMIT ограничение выборки Соединение таблиц LEFT JOIN соединяет таблицы первая таблица слева, с ON, если в правой таблице не хватает значений то NULL RIGHT JOIN соединяет таблицы первая таблица справа, с ON JOIN (NATURAL JOIN) простой способ без команды ON INNER JOIN простой способ и без команды ON и с ON mysql -u -p войти в mysql, далее ввести пароль SHOW DATABASES показать все базы данных SHOW TABLES показать все таблицы DESC users структура таблицы users Создать таблицу CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) not null, email VARCHAR(255) not null, tel VARCHAR(255) null, id_img INT, CONSTRAINT users_email_unique UNIQUE (email) ) ENGINE = InnoDB COLLATE = utf8mb4_unicode_ci; CREATE INDEX users_tel_index ON users (tel); Добавить колонку AFTER TABLE users ADD COLUMN surname VARCHAR (255) AFTER name; Изменить колонку AFTER TABLE users CHANGE COLUMN surname newname VARCHAR (255); Удалить колонку AFTER TABLE users DROP COLUMN surname Добавить связь с таблицей img AFTER TABLE users ADD FOREIGN KEY (id_img) REFERENCES img (id) mysql_pconnect() (link is external) работает аналогично mysql_connect() (link is external) с двумя важными отличиями. Во-первых, при соединении функция пытается найти уже открытый (постоянный) указатель на тот же сервер с тем же пользователем и паролем. Если он найден, возвращён функцией будет именно он, вместо открытия нового соединения. Во-вторых, соединение с SQL сервером не будет закрыто, когда работа скрипта закончится. Вместо этого, оно останется рабочим для будущего использования ( mysql_close() также не закрывает постоянные соединения, открытые mysql_pconnect()). PDO PHP Data Objects) — расширение PHP, которое реализует взаимодействие с базами данных при помощи объектов. Профит в том, что отсутствует привязка к конкретной системе управления базами данных. Транзакция представляет собой последовательность операторов языка SQL, которая рассматривается как некоторое неделимое действие над базой данных. Нормализация — это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости. Денормализация (англ. denormalization) — намеренное приведение структуры базы данных в состояние, не соответствующее критериям нормализации, обычно проводимое с целью ускорения операций чтения из базы за счет добавления избыточных данных. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key. Внешний ключ (Foreign key, кратко FK). Обеспечивает однозначную логическую связь, между таблицами одной БД. Первичный ключ используется для уникальной идентификации записей в таблице, а внешний ключ используется для соединения двух таблиц вместе. Связь "один-ко-многим" Рассмотрим базу данных, в которой учитываются заказы, включающую таблицы "Клиенты" и "Заказы" в качестве примера. Связь "многие-ко-многим" Рассмотрим связь между таблицами "Товары" и "Заказы". Связь "один-к-одному" Форма имеет одного пользователя. Задача поддержки, или обеспечения ссылочной целостности заключается в том, чтобы предотвращать или устранять нарушение корректности значений внешних ключей при внесении изменений в базу данных путём коррекции значений внешних ключей, если это возможно, или отмены операции редактирования, если такая коррекция невозможна. Агрегатные функции - для того, что бы обобщить полученные данные. SUM, AVG, MIN, MAX. GROUP BY - Группировка данных позволяет объединить одинаковые значения в заданных полях в группы, а затем выполнять подсчеты для каждой группы. WHERE и HAVING - Главное отличие HAVING от WHERE в том, что в HAVING можно наложить условия на результаты группировки, потому что порядок исполнения запроса устроен таким образом, что на этапе, когда выполняется WHERE, ещё нет групп, а HAVING выполняется уже после формирования групп. DISTINCT и GROUP BY - Distinct - возвращает уникальные значения, GroupBy - группирует ВСЕ значения. UNION, INTERSECT и EXCEPT - Операторы используются для объединения, пересечения и вычитания данных из двух или более таблиц. MyISAM - Движок по умолчанию. Не поддерживает транзакций, средняя надежность хранения данных. Превосходная производительность чтения данных для интенсивных приложений. Большинство веб сервисов и хранилищ данных активно используют MyISAM. InnoDB - Транзакционный тип движка, применяемый при интенсивных операциях записи, спасибо возможности блокировки уровня строк таблицы. Великолепная восстанавливаемость и высокая надежность хранения данных. Движок InnoDB был приобретен Oracle в 2005 году. Оптимизации производительности баз данных Существует множество способов оптимизации производительности баз данных. Вот некоторые из них: Индексирование: Создание индексов на часто используемых столбцах позволяет ускорить поиск и сортировку данных. Оптимизация запросов: Проверьте и оптимизируйте свои SQL запросы, чтобы улучшить их производительность. Используйте правильные ключи, избегайте использования медленных операций, таких как операции с плавающей запятой или использование LIKE с шаблонами, начинающимися с символа "%". Нормализация базы данных: Структурируйте данные таким образом, чтобы уменьшить дублирование и избежать аномалий при обновлении. Хорошая нормализация помогает ускорить выполнение запросов и обеспечить целостность данных. Кэширование: Используйте кэширование для временного хранения часто запрашиваемых данных, чтобы избежать излишних обращений к базе данных. Это может быть реализовано с помощью инструментов кэширования на уровне приложения или с помощью кэшей на уровне базы данных, таких как Redis или Memcached. Партиционирование: Разделение больших таблиц на небольшие физические разделы (партиции) может улучшить производительность запросов и упростить управление данными. Оптимизация структуры таблиц: Избегайте хранения больших объемов данных в одной таблице. Разделите таблицы на более мелкие, чтобы ускорить запросы и облегчить администрирование. Настройка сервера базы данных: Изучите и настройте параметры конфигурации сервера базы данных, такие как размер буферного пула, количество одновременных подключений и другие параметры, чтобы достичь наилучшей производительности в зависимости от вашего конкретного сценария использования. Предварительное вычисление и агрегирование: Если у вас есть сложные запросы, которые требуют вычислений или агрегации большого объема данных, рассмотрите возможность предварительного вычисления и кэширования результатов, чтобы ускорить выполнение запросов. Масштабирование: Если ваша база данных продолжает расти и нагрузка на нее увеличивается, рассмотрите возможность горизонтального или вертикального масштабирования, чтобы улучшить производительность. Горизонтальное масштабирование включает распределение данных по нескольким серверам, а вертикальное масштабирование - увеличение ресурсов сервера базы данных. Мониторинг и профилирование: Постоянно отслеживайте производительность базы данных, используя инструменты мониторинга и профилирования. Это позволит вам идентифицировать узкие места и проблемы производительности, что поможет вам принять соответствующие меры для их устранения. Партиционирование — это разбиение таблиц, содержащих большое количество записей. Репликация — это синхронное или асинхронное копирование данных между несколькими серверами. Шардинг — это прием, который позволяет распределять данные между разными физическими серверами. NoSQL например MongoDB. Индекс представляет из себя структуру, в которой хранятся значения одного или нескольких столбца таблицы и ссылок на строки, где эти значения расположены. Для хранения индексов чаще всего используются "бинарные деревья". Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций. Триггер представляет собой хранимую процедуру, которая активизируется при наступлении определенного события. Например, можно задать хранимую процедуру, которая срабатывает каждый раз при удалении записи из транзакционной таблицы - таким образом, обеспечивается автоматическое удаление соответствующего заказчика из таблицы заказчиков, когда все его транзакции удаляются. Функции - это примерно тоже самое, что и процедуры, за исключением того, что в функции мы всегда делаем return.