Как реализовать хранение друзей в БД?
Как хранить связи вида пользователь-друг — понятно. Создаем таблицу friend в которой два столбца — user и friend. Делаем ключ по двум полям. Соответственно, пользователь А, добавил в друзья пользователя B — появилась соответствующая запись. Когда пользователь В подтвердил заявку в друзья — создается симметричная запись.
Далее, если хотим вывести друзей пользователя, пишем что-то вроде
select f1.friend from friends f1 join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f1.user=:user_id
Но что делать если мы хотим вывести не только т.н «взаимных друзей» но и заявки в друзья и не подтвержденные заявки в друзья.
т.е например:
Пользователь user1 добавил в друзья пользователей user2, user3. user2 подтвердил заявку. user4 добавил в друзья user1. И в профиле user1 должно выводиться:
user2 (удалить из друзей)
user3 (отозвать заявку)
user4 (принять заявку)
Как правильно написать запрос? Или одним запросом не обойтись?
- Вопрос задан более трёх лет назад
- 11221 просмотр
Комментировать
Решения вопроса 1
В параллельной вселенной я космонавт
А что если хранить немного по другому?
Например не создавать дублирующую запись в обратную сторону, а изначально использовать еще одно поле в троичной системе счисления: ± 1 когда один пользователь добавил другого (знак указывает направление заявки) и 0 когда заявка подтверждена.
Тогда запрос будет один на выборку пары, а состояния отозвать/принять заявку и удалить из друзей будут определяться знаком числа в дополнительном поле.
Из очевидных плюсов. Места занимать будет примерно в два раза меньше — мелочь, а приятно.
Минусов не сразу не соображу.
Ответ написан более трёх лет назад
Нравится 17 3 комментария
При нажатии на кнопку отозвать/принять заявку и удалить из друзей — не удаляется запись, а просто меняется значение поля, что, на мой взгляд, намного лучше постоянного добавления/удаления записей (знатоки БД — поправьте, если я не прав).
Никита Колосов @Anexroid Автор вопроса
Просто тогда непонятно как выбирать друзей пользователя. Если делать что-то вроде select * from friend where user = :user_id or friend = :user_id, то не совсем понятно становится, кто кого добавил friend юзера в друзья или юзер френда. Ну и соответственно запись вида
user friend type 1 2 0
user friend type 2 1 0
будет означать одно и то же. Понятно, что добавляться будет только одна из них, но мороки, имхо, слишком много
Записи не идентичны.
если 1 добавил 2, то будет запись 1 2 0
если 2 добавил 1, то будет запись 2 1 0
Тут по записи видно кто инициировал добавление, кроме того, можно даже для дополнительного поля использовать bool, характеризующий подтвержденность дружбы. А направление запроса брать из полей user и friend.
Все что выше — мое видение направления решения. Идти в этом направлении или нет — решать вам.
Ответы на вопрос 3
Добавить ещё одну колонку — тип связи. Будет — user, friend, relation_type, где relation_type, например, friend/not_approved_friend/declined_friend/follower. А в выборку добавиться просто ограничение на тип связи.
Ответ написан более трёх лет назад
Комментировать
Нравится 5 Комментировать
Так хранить друзей это не очень хорошая идея. Если на сайте будет 1000 пользователей и у каждого по 100 друзей, то у вас будет таблица на 200000 записей, и довольно медленные запросы для такой простой штуки как список друзей.
Я бы сделал денормализацию, то есть просто хранил бы список друзей строкой в поле модели user 🙂
Быстрые запросы, так как пропарсить строку в несколько сотен символов будет быстрее, чем делать запрос к таблице в несколько сотен тысяч записей, да и в разработке такой способ проще.
Хотя возможно я ошибаюсь и разница в производительности будет не такой большой.
Собственно, отвечаю на ваш вопрос. Нужно просто получить список всех записей, где user1 есть в поле user или friend, затем уже в коде определить, взаимные это друзья (есть запись где user1 в поле friend, и запись где user1 в поле user, второй пользователь одинаковый в обоих запросах) или кто-то из них только отправил запрос. Запрос будет чем-то вроде:
select user, friend from friends where user=:user_id or friend=:user_id
Ответ написан более трёх лет назад
Нравится 2 2 комментария
Никита Колосов @Anexroid Автор вопроса
Не, ну хранить друзей в поле таблицы users это что-то вообще странное. А если у пользователя 10 000 друзей, а id-шник — это число порядка миллиона? Какой длины строка будет? Ужас же
> у вас будет таблица на 200000 записей
И что? И 50 лямов — не проблема для тупого индекс-скана даже на убитой виртуалке.
А вот хранить массив в поле таблицы — ветвь тупиковая. Чуть только понадобится найти «кто добавил в друзья меня», а не кого добавил этот пользователь — сразу получается full-scan, который быстро выполняться не может в принципе.
теоретически вам надо два множества — друзья юзера, и обратное — те кто позвал в друзья вашего юзера.
1. select f1.friend from friends f1 where f1.user=:user_id 2. select f1.user from friends f1 where f1.friend=:user_id пересечение будет давать взаимных френдов, вычитания — невзимных с одной и с другой стороны.
синтаксис SQL говорит что это легко можно сделать полным join, который обычно не имплементируется но эмулируется обьединением left и right. То бишь:
select * from friends f1 LEFT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f1.user=:user_id UNION select * from friends f1 RIGHT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f2.friend=:user_id и будет 3 варианта — обе f1 и f2 не null — взаимные друзья или один из них null
Но это не самый эффективный способ, я бы согласился с serso и посоветовал иметь доп колонку с типом ( заодно можно их иметь несколько — друзья, супруги/любовники, коллеги ) и при добавлении проверять обратное отношение и сразу заполнять колонку. На нагруженной системе это можно делать в отложенном режиме.
Проектируем базу данных: система друзей а-ля Facebook
Мы уже публиковали несколько уроков, посвящённых проектированию различных модулей, подобных тем, что используются на Facebook. Сегодня мы продолжим эту практику и рассмотрим как спроектировать БД для создания системы запросов в друзья.
Проект БД
Для реализации поставленной задачи нам понадобится три таблицы:Users, Updates и Friends.
Таблица Users
Таблица пользователей будет содержать данные, которые юзеры оставляют при регистрации.
CREATE TABLE `users` ( `user_id` INT(11) NOT NULL AUTO_INCREMENT , `username` VARCHAR(45) , `password` VARCHAR(100) , `email` VARCHAR(45) , `friend_count` INT(11) , `profile_pic` VARCHAR(150), PRIMARY KEY (`user_id`));
Пароль будем хранить после хеширования функцией MD5.
Таблица Updates
Данная таблица будет хранить статусы пользователей. В данном случае user_id_fk это внешний ключ, который относится к users.user_id
CREATE TABLE `updates` ( `update_id` INT(11) AUTO_INCREMENT , `update` VARCHAR(45), `user_id_fk` VARCHAR(45), `created` INT(11) , `ip` VARCHAR(45), PRIMARY KEY (`update_id`), FOREIGN KEY (user_id_fk) REFERENCES users(user_id));
Таблица Friends
Данная таблица будет хранить связи пользователей. В данном контексте friend_one и friend_two это внешние ключи к users.user_id
CREATE TABLE `friends` ( `friend_one` INT(11) , `friend_two` INT(11) , `status` ENUM('0','1','2') DEFAULT '0', PRIMARY KEY (`friend_one`,`friend_two`), FOREIGN KEY (friend_one) REFERENCES users(user_id), FOREIGN KEY (friend_two) REFERENCES users(user_id));
0, 1 и 2 — это статусы: “В ожидании”, “Запрос дружбы”, “Подтверждение дружбы”.
friendsTable.png
Добавление в друзья
Для того, чтобы отправить запрос в друзья, воспользуйтесь следующим insert запросом:
INSERT INTO friends (friend_one,friend_two) VALUES ('$user_id','$friend_id');
Подтверждение дружбы
Для подтверждения дружбы или игнорирования запроса, выставите статус в 0 или 1:
UPDATE friends SET status="1" WHERE (friend_one="$user_id" OR friend_two="$user_id") AND (friend_one="$friend_id" OR friend_two="$friend_id");
Проверка дружат ли пользователи или нет
В данном случае, friend_one это инициатор запроса:
SELECT 'friend_one','friend_two','status' FROM friends WHERE (friend_one="$user_id" OR friend_two="$user_id") AND (friend_one="$friend_id" OR friend_two="$friend_id")
PHP
Вот небольшой PHP код, который поможет вам вывести соответствующие кнопки на страницах пользователей.
> else
Обновление списка друзей
Выборка данных согласно связям таблиц users, friends и updates.
SELECT U.username, U.email, D.update_id, D.update, D.created FROM users U, updates D, friends F WHERE D.user_id_fk = U.user_id AND CASE WHEN F.friend_one = '$user_id' THEN F.friend_two = D.user_id_fk WHEN F.friend_two= '$user_id' THEN F.friend_one= D.user_id_fk END AND F.status > '0' ORDER BY D.update_id DESC;
Списки друзей
В данном запросе мы отобразим связь между таблицами users и friends, для отображения списка друзей.
SELECT F.status, U.username, U.email FROM users U, friends F WHERE CASE WHEN F.friend_one = '$user_id' THEN F.friend_two = U.user_id WHEN F.friend_two= '$user_id' THEN F.friend_one= U.user_id END AND F.status='1';
db.php
Напоследок приведём пример файла конфигурации базы данных.
define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'username'); define('DB_PASSWORD', 'password'); define('DB_DATABASE', 'database'); $connection = @mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: http://www.9lessons.info/2014/03/facebook-style-friend-request-system.html
Перевел: Станислав Протасевич
Урок создан: 24 Марта 2014
Просмотров: 28647
Правила перепечатки5 последних уроков рубрики "PHP"
Фильтрация данных с помощью zend-filter
Когда речь идёт о безопасности веб-сайта, то фраза "фильтруйте всё, экранируйте всё" всегда будет актуальна. Сегодня поговорим о фильтрации данных.
Контекстное экранирование с помощью zend-escaper
Обеспечение безопасности веб-сайта — это не только защита от SQL инъекций, но и протекция от межсайтового скриптинга (XSS), межсайтовой подделки запросов (CSRF) и от других видов атак. В частности, вам нужно очень осторожно подходить к формированию HTML, CSS и JavaScript кода.
Подключение Zend модулей к Expressive
Expressive 2 поддерживает возможность подключения других ZF компонент по специальной схеме. Не всем нравится данное решение. В этой статье мы расскажем как улучшили процесс подключение нескольких модулей.
Совет: отправка информации в Google Analytics через API
Предположим, что вам необходимо отправить какую-то информацию в Google Analytics из серверного скрипта. Как это сделать. Ответ в этой заметке.
Подборка PHP песочниц
Подборка из нескольких видов PHP песочниц. На некоторых вы в режиме online сможете потестить свой код, но есть так же решения, которые можно внедрить на свой сайт.
Работа с запросами в MySQL
Базы данных являются ключевым компонентом многих веб-сайтов и приложений и лежат в основе хранения и обмена данными в Интернете. Одним из наиболее важных аспектов управления базой данных является практика извлечения данных из базы данных, будь то на разовой основе или частью процесса, который был закодирован в приложении. Существует несколько способов получения информации из базы данных, но один из наиболее часто используемых методов выполняется путем отправки запросов через командную строку.
В системах управления реляционными базами данных запрос - это любая команда, используемая для извлечения данных из таблицы. В языке структурированных запросов (SQL) запросы почти всегда выполняются с помощью оператора SELECT.
В этом руководстве мы обсудим основной синтаксис SQL-запросов, а также некоторые из наиболее часто используемых функций и операторов. Мы также будем практиковаться в создании запросов SQL с использованием некоторых примеров данных в базе данных MySQL.
MySQL - это система управления реляционными базами данных с открытым исходным кодом. MySQL, одна из наиболее распространенных баз данных SQL, отдает приоритет скорости, надежности и удобству использования. Как правило, она соответствует стандарту ANSI SQL, хотя в некоторых случаях MySQL выполняет операции не так, как признанный стандарт.
Подготовка окружения
В целом, команды и концепции, представленные в этом руководстве, могут использоваться в любой операционной системе на базе Linux, на которой работает любое программное обеспечение базы данных SQL. Тем не менее, он был написан специально для сервера Ubuntu 18.04. Для настройки вам понадобится следующее:
- Сервер с Ubuntu 18.04
- Установленный на сервере MySQL
Если сервер создается в NetAngels, то при создании сервера мы рекомендуем выбрать Ubuntu 18.04 Bionic LAMP
Создание образца базы данных
Прежде чем мы сможем начать делать запросы в SQL, мы сначала создадим базу данных и пару таблиц, а затем заполним эти таблицы некоторыми примерами данных. Это позволит вам получить практический опыт, когда вы начнете делать запросы позже.
Для примера базы данных, которую мы будем использовать в этом руководстве, представьте следующий сценарий:
Вы и несколько ваших друзей празднуете свои дни рождения друг с другом. В каждом случае члены группы направляются в местный боулинг, участвуют в дружеском турнире, а затем все направляются к вам, где вы готовите любимое блюдо для именинника.
Теперь, что эта традиция продолжается некоторое время, вы решили начать отслеживать записи с этих турниров. Кроме того, чтобы упростить планирование обедов, вы решаете создать запись о днях рождения ваших друзей и их любимых блюдах, сторонах и десертах. Вместо того чтобы хранить эту информацию в физической книге, вы решаете использовать свои навыки работы с базами данных, записав ее в базу данных MySQL.
Если вы создали сервер в NetAngels на основе образа Ubuntu 18.04 Bionic LAMP, то откройте приглашение MySQL выполнив от пользователя root команду:
Примечание: Если зайти в MySQL таким образом не удается, то для аутентификации с использованием пароля используйте команду:
Затем создайте базу данных, запустив:
CREATE DATABASE `birthdays`; Затем выберите эту базу данных, набрав:
USE birthdays; Затем создайте две таблицы в этой базе данных. Мы будем использовать первую таблицу, чтобы отслеживать записи ваших друзей в боулинге. Следующая команда создаст таблицу под названием «tourneys» со столбцами для «name» каждого из ваших друзей, количества турниров, которые они выиграли («wins»), их лучший результат за все время и каков размер обувь для боулинга, которую они носят ( размер ):
CREATE TABLE tourneys ( name varchar(30), wins real, best real, size real ); Как только вы запустите команду CREATE TABLE и заполните ее заголовками столбцов, вы получите следующий вывод:
Query OK, 0 rows affected (0.00 sec) Заполните таблицу ‘tourneys’ некоторыми примерами данных:
INSERT INTO tourneys (name, wins, best, size) VALUES ('Dolly', '7', '245', '8.5'), ('Etta', '4', '283', '9'), ('Irma', '9', '266', '7'), ('Barbara', '2', '197', '7.5'), ('Gladys', '13', '273', '8'); Вы получите такой вывод:
Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 После этого создайте еще одну таблицу в той же базе данных, которую мы будем использовать для хранения информации о любимых блюдах ваших друзей на день рождения. Следующая команда создает таблицу с именем dinners и столбцами для«имя» каждого из ваших друзей, их «дата рождения», их любимое «блюдо», их любимое «гарнир» и их любимый «десерт»:
CREATE TABLE dinners ( name varchar(30), birthdate date, entree varchar(30), side varchar(30), dessert varchar(30) ); Аналогично для этой таблицы вы получите отзыв, подтверждающий успешное выполнение команды:
Query OK, 0 rows affected (0.01 sec) Заполните эту таблицу также некоторыми примерами данных:
INSERT INTO dinners (name, birthdate, entree, side, dessert) VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'), ('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'), ('Irma', '1941-02-18', 'tofu', 'fries', 'cake'), ('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'), ('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 Как только эта команда завершится успешно, вы закончили настройку базы данных. Далее мы рассмотрим основную структуру команд запросов SELECT.
Понимание операторов SELECT
Как упоминалось во введении, SQL-запросы почти всегда начинаются с оператора SELECT . SELECT используется в запросах, чтобы указать, какие столбцы из таблицы должны быть возвращены в наборе результатов. Запросы также почти всегда включают FROM , который используется для указания таблицы, к которой будет обращаться оператор.
Как правило, SQL-запросы следуют этому синтаксису:
SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply; Например, следующий оператор вернет весь столбец name из таблицы dinners :
SELECT name FROM dinners; +---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | +---------+ 5 rows in set (0.00 sec) Вы можете выбрать несколько столбцов из одной таблицы, разделяя их имена запятыми, например:
SELECT name, birthdate FROM dinners; +---------+------------+ | name | birthdate | +---------+------------+ | Dolly | 1946-01-19 | | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Barbara | 1948-12-25 | | Gladys | 1944-05-28 | +---------+------------+ 5 rows in set (0.00 sec) Вместо того, чтобы называть конкретный столбец или набор столбцов, вы можете следовать за оператором SELECT со звездочкой ( * ), которая служит заполнителем, представляющим все столбцы в таблице. Следующая команда возвращает каждый столбец из таблицы tourneys :
SELECT * FROM tourneys; +---------+------+------+------+ | name | wins | best | size | +---------+------+------+------+ | Dolly | 7 | 245 | 8.5 | | Etta | 4 | 283 | 9 | | Irma | 9 | 266 | 7 | | Barbara | 2 | 197 | 7.5 | | Gladys | 13 | 273 | 8 | +---------+------+------+------+ 5 rows in set (0.00 sec) WHERE используется в запросах для фильтрации записей, которые удовлетворяют указанному условию, и любые строки, которые не удовлетворяют этому условию, исключаются из результата. Предложение WHERE обычно соответствует следующему синтаксису:
. . . WHERE column_name comparison_operator value Оператор сравнения в предложении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:
| Оператор | Что он делает |
|---|---|
| = | тесты для равенства |
| != | тесты для неравенства |
| тесты для больше | |
| = | тесты для больше чем или равный к |
| BETWEEN | проверяет лежит ли в заданном диапазоне |
| IN | проверяет содержатся ли строки в наборе значений |
| EXISTS | тесты на соответствие строки существует при заданных условиях |
| LIKE | проверяет совпадает ли значение с указанной строкой |
| IS NULL | тесты для `NULL` значения |
| IS NOT NULL | тесты для всех других значений, чем `NULL` |
Например, если вы хотите найти размер обуви Ирмы, вы можете использовать следующий запрос:
SELECT size FROM tourneys WHERE name = 'Irma'; +------+ | size | +------+ | 7 | +------+ 1 row in set (0.00 sec) SQL допускает использование подстановочных знаков, и это особенно удобно при использовании в предложениях WHERE. Знаки процента ( % ) представляют ноль или более неизвестных символов, а подчеркивания ( _ ) представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не уверены, что эта запись. Чтобы проиллюстрировать это, скажем, что вы забыли любимое блюдо нескольких своих друзей, но вы уверены, что это конкретное блюдо начинается с буквы “t”. Вы можете найти его имя, выполнив следующий запрос:
SELECT entree FROM dinners WHERE entree LIKE 't%'; +--------+ | entree | +--------+ | tofu | | tofu | +--------+ 2 rows in set (0.00 sec) Основываясь на вышеприведенном выводе, мы видим, что блюдо - это тофу.
Могут быть случаи, когда вы работаете с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми именами. В этих случаях вы можете сделать эти имена более читабельными, создав псевдоним с ключевым словом AS . Псевдонимы, созданные с помощью AS , являются временными и существуют только на время запроса, для которого они созданы:
SELECT name AS n, birthdate AS b, dessert AS d FROM dinners; +---------+------------+-----------+ | n | b | d | +---------+------------+-----------+ | Dolly | 1946-01-19 | cake | | Etta | 1938-01-25 | ice cream | | Irma | 1941-02-18 | cake | | Barbara | 1948-12-25 | ice cream | | Gladys | 1944-05-28 | ice cream | +---------+------------+-----------+ 5 rows in set (0.00 sec) Здесь мы сказали SQL отображать столбец name как n , столбец birthdate как b , а столбец sert как d .
Примеры, которые мы рассмотрели до этого момента, включают в себя некоторые из наиболее часто используемых ключевых слов и предложений в запросах SQL. Они полезны для базовых запросов, но они бесполезны, если вы пытаетесь выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Это где агрегатные функции вступают в игру.
Агрегатные функции
Часто при работе с данными необязательно просматривать сами данные. Скорее, вам нужна информация о данных. Синтаксис SQL включает в себя ряд функций, которые позволяют интерпретировать или выполнять вычисления для ваших данных, просто выполнив запрос «SELECT». Они известны как aggregate functions.
Функция COUNT считает и возвращает количество строк, соответствующих определенным критериям. Например, если вы хотите узнать, сколько ваших друзей предпочитают тофу для своего дня рождения, вы можете выполнить этот запрос:
SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu'; +---------------+ | COUNT(entree) | +---------------+ | 2 | +---------------+ 1 row in set (0.00 sec) Функция AVG возвращает среднее (среднее) значение столбца. Используя наш пример таблицы, вы можете найти средний лучший результат среди ваших друзей с помощью этого запроса:
SELECT AVG(best) FROM tourneys; +-----------+ | AVG(best) | +-----------+ | 252.8 | +-----------+ 1 row in set (0.00 sec) SUM используется для поиска общей суммы данного столбца. Например, если вы хотите посмотреть, сколько игр вы и ваши друзья играли в боулинг за эти годы, вы можете выполнить этот запрос:
SELECT SUM(wins) FROM tourneys; +-----------+ | SUM(wins) | +-----------+ | 35 | +-----------+ 1 row in set (0.00 sec) Обратите внимание, что функции AVG и SUM будут работать правильно только при использовании с числовыми данными. Если вы попытаетесь использовать их для нечисловых данных, это приведет к ошибке или просто к «0», в зависимости от того, какую СУБД вы используете:
SELECT SUM(entree) FROM dinners; +-------------+ | SUM(entree) | +-------------+ | 0 | +-------------+ 1 row in set, 5 warnings (0.00 sec) MIN используется для поиска наименьшего значения в указанном столбце. Вы можете использовать этот запрос, чтобы увидеть, какой худший общий рекорд в боулинге (с точки зрения количества побед):
SELECT MIN(wins) FROM tourneys; +-----------+ | MIN(wins) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) Аналогично, MAX используется для поиска наибольшего числового значения в данном столбце. Следующий запрос покажет лучший общий результат в боулинге:
SELECT MAX(wins) FROM tourneys; +-----------+ | MAX(wins) | +-----------+ | 13 | +-----------+ 1 row in set (0.00 sec) В отличие от SUM и AVG , функции MIN и MAX могут использоваться как для числовых, так и для буквенных типов данных. При запуске в столбце, содержащем строковые значения, функция MIN будет отображать первое значение в алфавитном порядке:
SELECT MIN(name) FROM dinners; +-----------+ | MIN(name) | +-----------+ | Barbara | +-----------+ 1 row in set (0.00 sec) Аналогично, при запуске в столбце, содержащем строковые значения, функция MAX покажет последнее значение в алфавитном порядке:
SELECT MAX(name) FROM dinners; +-----------+ | MAX(name) | +-----------+ | Irma | +-----------+ 1 row in set (0.00 sec) Агрегатные функции умеют больше того, что было описано в этом разделе. Они особенно полезны при использовании с предложением GROUP BY , которое рассматривается в следующем разделе, а также с несколькими другими предложениями запроса, которые влияют на сортировку наборов результатов.
Управление запросами
В дополнение к предложениям FROM и WHERE , есть несколько других предложений, которые используются для манипулирования результатами запроса SELECT . В этом разделе мы объясним и предоставим примеры для некоторых из наиболее часто используемых предложений запросов.
Одним из наиболее часто используемых предложений запроса, помимо FROM и WHERE , является предложение GROUP BY . Обычно он используется, когда вы выполняете статистическую функцию для одного столбца, но в отношении сопоставления значений в другом.
Например, скажем, вы хотели знать, сколько ваших друзей предпочитают каждый из трех блюд, которые вы делаете. Вы можете найти эту информацию с помощью следующего запроса:
SELECT COUNT(name), entree FROM dinners GROUP BY entree; +-------------+---------+ | COUNT(name) | entree | +-------------+---------+ | 1 | chicken | | 2 | steak | | 2 | tofu | +-------------+---------+ 3 rows in set (0.00 sec) Предложение ORDER BY используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Чтобы проиллюстрировать это, следующий запрос перечисляет столбцы name и birthdate , но сортирует результаты по дате рождения:
SELECT name, birthdate FROM dinners ORDER BY birthdate; +---------+------------+ | name | birthdate | +---------+------------+ | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Gladys | 1944-05-28 | | Dolly | 1946-01-19 | | Barbara | 1948-12-25 | +---------+------------+ 5 rows in set (0.00 sec) Обратите внимание, что поведение по умолчанию ORDER BY состоит в сортировке набора результатов в порядке возрастания. Чтобы изменить это и отсортировать набор результатов в порядке убывания, закройте запрос с помощью DESC :
SELECT name, birthdate FROM dinners ORDER BY birthdate DESC; +---------+------------+ | name | birthdate | +---------+------------+ | Barbara | 1948-12-25 | | Dolly | 1946-01-19 | | Gladys | 1944-05-28 | | Irma | 1941-02-18 | | Etta | 1938-01-25 | +---------+------------+ 5 rows in set (0.00 sec) Как уже упоминалось ранее, предложение WHERE используется для фильтрации результатов на основе определенных условий. Однако, если вы используете предложение WHERE с агрегатной функцией, оно вернет ошибку, как в случае со следующей попыткой выяснить, какие стороны являются фаворитами по крайней мере трех ваших друзей:
SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3; ERROR 1111 (HY000): Invalid use of group function Предложение HAVING было добавлено в SQL для обеспечения функциональности, аналогичной функциональности предложения WHERE, а также совместимости с агрегатными функциями. Полезно думать о разнице между этими двумя пунктами как о том, что WHERE применяется к отдельным записям, в то время как HAVING применяется к групповым записям. С этой целью каждый раз, когда вы вводите предложение HAVING , также должно присутствовать предложение GROUP BY .
Следующий пример - еще одна попытка найти, какие гарниры являются фаворитами как минимум трех ваших друзей, хотя этот вернет результат без ошибок:
SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3; +-------------+-------+ | COUNT(name) | side | +-------------+-------+ | 3 | fries | +-------------+-------+ 1 row in set (0.00 sec) Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое более чем одной таблицы. Мы рассмотрим несколько способов сделать это в следующем разделе.
Запрос нескольких таблиц
Чаще всего база данных содержит несколько таблиц, каждая из которых содержит разные наборы данных. SQL предоставляет несколько разных способов выполнения одного запроса для нескольких таблиц.
Предложение JOIN может использоваться для объединения строк из двух или более таблиц в результате запроса. Это достигается путем нахождения связанного столбца между таблицами и соответствующей сортировки результатов в выходных данных.
Операторы SELECT , которые включают предложение JOIN , обычно следуют этому синтаксису:
SELECT table1.column1, table2.column2 FROM table1 JOIN table2 ON table1.related_column=table2.related_column; Обратите внимание, что поскольку предложения JOIN сравнивают содержимое нескольких таблиц, в предыдущем примере указывается, из какой таблицы выбрать каждый столбец, предшествуя имени столбца с именем таблицы и точкой. Вы можете указать, из какой таблицы должен быть выбран столбец, например, для любого запроса, хотя это не обязательно при выборе из одной таблицы, как мы делали в предыдущих разделах. Давайте рассмотрим пример, используя наш образец Dата.
Представьте, что вы хотите купить каждому из ваших друзей пару ботинок для боулинга в качестве подарка на день рождения. Поскольку информация о датах рождения и размерах обуви ваших друзей хранится в отдельных таблицах, вы можете запросить обе таблицы по отдельности, а затем сравнить результаты для каждой из них. Тем не менее, с помощью предложения JOIN вы можете найти всю необходимую информацию с помощью одного запроса:
SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys JOIN dinners ON tourneys.name=dinners.name; +---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+------+------------+ 5 rows in set (0.00 sec) Предложение JOIN , используемое в этом примере, без каких-либо других аргументов, является предложением inner JOIN . Это означает, что он выбирает все записи, которые имеют совпадающие значения в обеих таблицах, и печатает их в наборе результатов, в то время как все несоответствующие записи исключаются. Чтобы проиллюстрировать эту идею, давайте добавим новую строку в каждую таблицу, у которой нет соответствующей записи в другой:
INSERT INTO tourneys (name, wins, best, size) VALUES ('Bettye', '0', '193', '9'); INSERT INTO dinners (name, birthdate, entree, side, dessert) VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream'); Затем повторно запустите предыдущий оператор SELECT с предложением JOIN:
SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys JOIN dinners ON tourneys.name=dinners.name; +---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+------+------------+ 5 rows in set (0.00 sec) Обратите внимание, что, поскольку в таблице «tourneys» нет записи для Лесли, а в таблице «dinners» нет записи для Бетти, эти записи отсутствуют в этих выходных данных.
Тем не менее, можно вернуть все записи из одной из таблиц, используя предложение outer JOIN . В MySQL предложения JOIN записываются как LEFT JOIN или RIGHT JOIN .
Предложение LEFT JOIN возвращает все записи из« левой »таблицы и только совпадающие записи из правой таблицы. В контексте внешних объединений левая таблица - это таблица, на которую ссылается условие FROM , а правая таблица - любая другая таблица, на которую ссылается после оператора JOIN .
Выполните предыдущий запрос еще раз, но на этот раз используйте предложение LEFT JOIN :
SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys LEFT JOIN dinners ON tourneys.name=dinners.name; Эта команда будет возвращать каждую запись из левой таблицы (в данном случае, «турниры»), даже если в правой таблице нет соответствующей записи. Каждый раз, когда в правой таблице нет подходящей записи, она возвращается как NULL или просто пустое значение, в зависимости от вашей RDBMS:
+---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | Bettye | 9 | NULL | +---------+------+------------+ 6 rows in set (0.00 sec) Теперь выполните запрос еще раз, на этот раз с предложением RIGHT JOIN :
SELECT tourneys.name, tourneys.size, dinners.birthdate FROM tourneys RIGHT JOIN dinners ON tourneys.name=dinners.name; Это вернет все записи из правой таблицы ( dinners ). Поскольку дата рождения Лесли записана в правой таблице, но для нее нет соответствующей строки в левой таблице, столбцы name и size вернутся как значения NULL в этой строке:
+---------+------+------------+ | name | size | birthdate | +---------+------+------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | NULL | NULL | 1946-05-02 | +---------+------+------------+ 6 rows in set (0.00 sec) Обратите внимание, что левые и правые объединения могут быть записаны как LEFT OUTER JOIN или RIGHT OUTER JOIN , хотя подразумевается часть «OUTER» в предложении. Аналогично, указание INNER JOIN даст тот же результат, что и простое написание JOIN .
В качестве альтернативы использованию JOIN для запроса записей из нескольких таблиц, вы можете использовать предложение UNION .
Оператор UNION работает немного иначе, чем предложение JOIN : вместо того, чтобы печатать результаты из нескольких таблиц в виде уникальных столбцов с использованием одного оператора SELECT , UNION объединяет результаты двух операторов SELECT в один столбец.
Чтобы проиллюстрировать, запустите следующий запрос:
SELECT name FROM tourneys UNION SELECT name FROM dinners; Этот запрос удалит все повторяющиеся записи, что является поведением по умолчанию оператора UNION :
+---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Lesley | +---------+ 7 rows in set (0.00 sec) Чтобы вернуть все записи (включая дубликаты), используйте оператор UNION ALL :
SELECT name FROM tourneys UNION ALL SELECT name FROM dinners; +---------+ | name | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Lesley | +---------+ 12 rows in set (0.00 sec) Имена и количество столбцов в таблице результатов отражают имя и количество столбцов, запрошенных первым оператором SELECT. Обратите внимание, что при использовании UNION для запроса нескольких столбцов из более чем одной таблицы каждый оператор SELECT должен запрашивать одинаковое количество столбцов, соответствующие столбцы должны иметь одинаковые типы данных, а столбцы в каждом операторе SELECT должны быть в том же порядке. В следующем примере показано, что может произойти, если вы используете предложение UNION для двух операторов SELECT , которые запрашивают разное количество столбцов:
SELECT name FROM dinners UNION SELECT name, wins FROM tourneys; ERROR 1222 (21000): The used SELECT statements have a different number of columns Другой способ запроса нескольких таблиц - использование subqueries. Подзапросы (также известные как inner или nested query) - это запросы, заключенные в другой запрос. Это полезно в тех случаях, когда вы пытаетесь отфильтровать результаты запроса по сравнению с результатами отдельной агрегатной функции.
Чтобы проиллюстрировать эту идею, скажем, вы хотите знать, кто из ваших друзей выиграл больше матчей, чем Барбара. Вместо того, чтобы узнать, сколько матчей выиграла Барбара, а затем выполнить другой запрос, чтобы узнать, кто выиграл больше игр, вы можете рассчитать обе с помощью одного запроса:
SELECT name, wins FROM tourneys WHERE wins > ( SELECT wins FROM tourneys WHERE name = 'Barbara' ); +--------+------+ | name | wins | +--------+------+ | Dolly | 7 | | Etta | 4 | | Irma | 9 | | Gladys | 13 | +--------+------+ 4 rows in set (0.00 sec) Подзапрос в этом операторе был выполнен только один раз; нужно было только найти значение из столбца wins в той же строке, что и Barbara в столбце name , а данные, возвращаемые подзапросом и внешним запросом, не зависят друг от друга. Однако существуют случаи, когда внешний запрос должен сначала прочитать каждую строку в таблице и сравнить эти значения с данными, возвращенными подзапросом, чтобы получить требуемые данные. В этом случае подзапрос называется коррелированным подзапросом.
Следующее утверждение является примером коррелированного подзапроса. Этот запрос пытается выяснить, кто из ваших друзей выиграл больше игр, чем в среднем для тех, у кого одинаковый размер обуви:
SELECT name, size FROM tourneys AS t WHERE wins > ( SELECT AVG(wins) FROM tourneys WHERE size = t.size ); Чтобы запрос завершился, он должен сначала собрать столбцы name и size из внешнего запроса. Затем он сравнивает каждую строку из этого набора результатов с результатами внутреннего запроса, который определяет среднее количество побед для людей с одинаковыми размерами обуви. Поскольку у вас есть только два друга с одинаковым размером обуви, в наборе результатов может быть только одна строка:
+------+------+ | name | size | +------+------+ | Etta | 9 | +------+------+ 1 row in set (0.00 sec) Как упоминалось ранее, подзапросы могут использоваться для запроса результатов из нескольких таблиц. Чтобы проиллюстрировать это одним последним примером, скажем, вы хотели устроить неожиданный ужин для лучшего боулера группы. Вы можете узнать, кто из ваших друзей имеет лучший рекорд в боулинге, и вернуть любимое блюдо по следующему запросу:
SELECT name, entree, side, dessert FROM dinners WHERE name = (SELECT name FROM tourneys WHERE wins = (SELECT MAX(wins) FROM tourneys)); +--------+--------+-------+-----------+ | name | entree | side | dessert | +--------+--------+-------+-----------+ | Gladys | steak | fries | ice cream | +--------+--------+-------+-----------+ 1 row in set (0.00 sec) Обратите внимание, что этот оператор не только включает подзапрос, но также содержит подзапрос в этом подзапросе.
Заключение
Выдача запросов является одной из наиболее часто выполняемых задач в области управления базами данных. Существует ряд инструментов администрирования баз данных, таких как phpMyAdmin или pgAdmin, которые позволяют выполнять запросы и визуализировать результаты, но с помощью операторов SELECT это все еще широко распространенный рабочий процесс, который также может предоставить вам больший контроль.
Рекомендуемые статьи:
- Оптимальные настройки mysql для Битрикс
- PhpMyAdmin — как создать пользователя и базу данных
- Резервное копирование Mysql
- Перенос пользователей в MySQL между серверами
- Как сбросить пароль MariaDB 10 на сервере с Debian
Как хранить друзей в базе данных
15 февраля 2015
Время от времени мне прилетают в почту занятные вопросы. Последний про хранение упорядоченного списка в базе данных.
Имеется возможность загрузки фото в альбомы. Нужно реализовать возможность упорядочивать фото в пределах альбома. Автор уже хранит порядок в поле order , но его напрягает, что при перетаскивании какой-либо из картинок нужно обновлять order у фотографий всего альбома.
Для начала, стоит оценить частоту изменений порядка фото. Если операция не очень частая и выполняется, например, только админом раз в день и в альбомах не очень много фото, можно смело хранить order как integer и обновлять, как автор и делает.
Если же операция достаточно частая, можно схитрить и хранить order как decimal . В этом случае при вставке или перемещении фото между двумя другими необходимо обновить только order непосредственно перемещаемой записи.
Если мы перемещаем фото C и засовываем его между фото A и фото B , то значение order для него вычисляется как
C.order = A.order + (B.order - A.order) / 2;
UPD: из за ограничений точности следует проверять, влезет ли в базу очередное значение. Если нет — пересчитывать order . Даже несмотря на то, что от пересчётов мы не избавились, их частота сократилась для худшего случая на порядок.
Комментарии RSS по email OK
MT 15 февр. 2015 г., 18:43:23
Как вариант, в случае использования целых весов элементов можно воспользоваться школьным BASIC’овским трюком — вес элементов инкрементировать десятками: 10, 20, 30 и т. д. Тогда пересчёт весов других элементов будет требоваться нечасто (только если «места» между конкретными двумя элементами не осталось), и даже если потребуется — то не обязательно всех.
oWeRQ 15 февр. 2015 г., 19:56:17
Стоит учитывать, что если order = id и между элементами нет пробелов, B.order - A.order == 1, а 1 очень плохо несколько раз делится пополам, даже decimal(n, 6) быстро закончится, чтобы потом не возиться с дробями можно использовать integer order = id * 1024(хватит на 10 перемещений "во внутрь"), временами пресчитывать order.
Sam 15 февр. 2015 г., 20:10:47
oWeRQ, хорошо подмечено. В decimal влезает, в случае того же MySQL, 13 знаков после запятой. То есть хватит на 13 делений. MT, это усложнит алгоритм, хотя и с decimal мы тоже рано или поздно упрёмся.