Узнать следующую и предыдущую запись в выборке по полю с неуникальными значениями

Опубликовано в Пнд, 06/08/2012 - 22:37
mysql sql
Есть таблица с данными:

+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2012-08-06 22:09:40 |
| 2 | 2012-08-06 22:09:44 |
| 3 | 2012-08-06 22:10:00 |
| 4 | 2012-08-06 22:10:00 |
| 5 | 2012-08-06 22:10:17 |
+----+---------------------+
Нужно, зная id и date определённой записи, узнать следующую и предыдущую запись в такой вот выборке:

SELECT *
FROM `table`
ORDER BY `date` ASC, `id` ASC

Следующая запись:

SELECT *
FROM `table`
WHERE `date` > $date OR (`date` = $date AND `id` > $id)
ORDER BY `date` ASC, `id` ASC
LIMIT 1

Предыдущая запись:

SELECT *
FROM `table`
WHERE `date` < $date OR (`date` = $date AND `id` < $id)
ORDER BY `date` DESC, `id` DESC
LIMIT 1

Добавлено: 30 Июля 2018 07:55:14 Добавил: Андрей Ковальчук

Вычислить возраст по дате рождения

Опубликовано в Втр, 17/01/2012 - 12:47
mysql
Более универсальный вариант SQL запроса из официальной документации:

SELECT
  birthday,
  (
    (YEAR(CURRENT_DATE) - YEAR(birthday)) -                             /* step 1 */
    (DATE_FORMAT(CURRENT_DATE, '%m%d') < DATE_FORMAT(birthday, '%m%d')) /* step 2 */
  ) AS age
FROM users

Смысл следующий — первым шагом вычитаем из текущего года, год рождения, вторым шагом вычитаем единичку если дня рождения в этом году ещё не было.

birthday это поле с датой в любом валидном формате (не timestamp), например 1989-07-22 или даже 1989-07-22T00:00:00.

Добавлено: 30 Июля 2018 07:28:00 Добавил: Андрей Ковальчук

Выборка постов с определёнными тегами

Есть таблицы posts (id, title, text) и tags (id, name), связанные отношением многие-ко-многим с помощью таблицы posts_tags (post_id, tag_id).

Задача — выбрать посты с определёнными тегами.

Вариант 1:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt1 ON pt1.post_id = p.id
INNER JOIN posts_tags pt2 ON pt2.post_id = p.id
WHERE
    pt1.tag_id = ID_ПЕРВОГО_ТЕГА AND
    pt2.tag_id = ID_ВТОРОГО_ТЕГА

Таблица posts_tags джойнится столько раз, сколько тегов участвует в выборке (в примере их два).

Вариант 2:

SELECT p.id, p.title, p.text
FROM posts p
WHERE p.id IN (
    SELECT pt.post_id
    FROM posts_tags pt
    INNER JOIN posts_tags pt1 ON pt.post_id = pt1.post_id AND pt1.tag_id = ID_ПЕРВОГО_ТЕГА
    INNER JOIN posts_tags pt2 ON pt.post_id = pt2.post_id AND pt2.tag_id = ID_ВТОРОГО_ТЕГА
)

Таким способом делает выборку модуль Views в Drupal.

Вариант 3:

SELECT p.id, p.title, p.text
FROM posts p
INNER JOIN posts_tags pt ON pt.post_id = p.id
WHERE pt.tag_id IN (ID_ПЕРВОГО_ТЕГА, ID_ВТОРОГО_ТЕГА)
GROUP BY p.id
HAVING COUNT(*) >= 2

В HAVING указывается количество тегов участвующих в выборке (в примере их два). Таким способом делает выборку модуль Search в Drupal.

Добавлено: 29 Июля 2018 22:18:40 Добавил: Андрей Ковальчук

Сортировка по времени дня

Задача
Вы хотите упорядочить записи по времени дня.

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

Обсуждение
В зависимости от типа столбца сортировку по времени дня можно проводить разными способами. Если значения хранятся в столбце TIME, вы можете непосредственно упорядочивать их. Чтобы расположить по времени дня значения DATETIME или TIMESTAMP, извлеките из них составляющие времени и отсортируйте их. Например, таблица mail содержит значения DATETIME, которые могут быть упорядочены по времени дня следующим образом:

mysql> SELECT * FROM mail ORDER BY HOUR(t), MINUTE(t), SECOND(t);


+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Вы также можете применить функцию TIME_TO_SEC() для отбрасывания составляющей даты и возврата составляющей времени, пересчитанной в секунды:mysql> SELECT * FROM mail ORDER BY TIME_TO_SEC(t);

+--------------------------+---------+---------+----------+---------+----------+
| t | srcuser | srchost | dstuser | dsthost | size |
+--------------------------+---------+---------+----------+---------+----------+
| 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2001-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2001-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
...

Добавлено: 22 Июля 2018 21:46:40 Добавил: Андрей Ковальчук

Сортировка результатов выражения

Задача
Вы хотите упорядочить результат запроса на основе вычисления значений некоторых выражений над столбцами.

Решение
Поместите выражение, вычисляющее значение, в инструкцию ORDER BY. Если вы работаете со старой версией MySQL, не поддерживающей выражения в ORDER BY, используйте обходной маневр.

Обсуждение
Один из столбцов таблицы mail содержит величину сообщения в байтах:

mysql> SELECT * FROM mail;


+--------------------------+---------+---------+----------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size
+--------------------------+---------+---------+----------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |

... Предположим, что вы хотите извлекать только «большие» сообщения (например, превышающие 50 000 байт), но при этом выводить и упорядочивать их в терминах килобайтов, а не байтов. Вы можете применить для сортировки результатов выражения инструкцию ORDER BY, если это допускает ваша версия MySQL.

Выражения в инструкции ORDER BY не разрешены в версиях MySQL, предшествующих 3.23.2. Чтобы обойти это ограничение, укажите выражение в списке столбцов вывода и сошлитесь на него либо позиционно, либо используя псевдоним:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) -> FROM mail WHERE size > 50000
-> ORDER BY 3;


+--------------------------+---------+--------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+--------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+--------------------------------+

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) AS kilobytes
-> FROM mail WHERE size > 50000
-> ORDER BY kilobytes;


+--------------------------+---------+-----------+
| t | srcuser | kilobytes |
+--------------------------+---------+-----------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+----------+----------+

Такой способ будет работать и для версий MySQL 3.23.2 и выше, но в них появляется дополнительная возможность размещения выражения непосредственно в инструкции ORDER BY:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024)
-> FROM mail WHERE size > 50000
-> ORDER BY FLOOR((size+1023)/1024);


+--------------------------+---------+---------------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+--------------------------+---------+---------------------------------+
| 2001-05-11 10:15:08 | barb | 57 |
| 2001-05-14 14:42:21 | barb | 96 |
| 2001-05-12 12:48:13 | tricia | 191 |
| 2001-05-15 10:25:52 | gene | 976 |
| 2001-05-14 17:03:01 | tricia | 2339 |
+--------------------------+---------+----------------------------------+

Однако даже если вы помещаете выражение в инструкцию ORDER BY, есть по крайней мере две причины для того, чтобы использовать для него псевдоним:

• Легче написать инструкцию ORDER BY, используя псевдоним, чем заново вводя выражение (которое может быть весьма громоздким).

• Псевдоним может быть удобен при выводе – столбец получает понятное и осмысленное название.

Такое же ограничение накладывается на выражения в инструкции GROUP BY), и обходить его следует так же. Не забудьтеоб этом, если ваша версия MySQL старше, чем 3.23.2. Многие запросы далее в книге используют выражения в инструкциях ORDER BY и GROUP BY. Чтобы они заработали на ранних версиях сервера MySQL, вам придется переписать их, применяя только что изученный прием.

Добавлено: 22 Июля 2018 21:40:33 Добавил: Андрей Ковальчук

Итоги по датам

Задача
Вы хотите выводить итоговую информацию для значений даты или времени.

Решение
Используйте инструкцию GROUP BY для разбиения значений времени на диапазоны нужного размера. Часто для извлечения значащих составляющих даты или времени требуется использовать выражения.

Обсуждение
Чтобы разместить записи во временном порядке, используйте инструкцию ORDER BY для сортировки столбца временного типа. Если вместо этого вы хотитевыводить итоговую информацию для записей, группируя их по интервалам времени, необходимо определить, как сопоставить запись интервалу, и использовать для группирования записей инструкцию GROUP BY.

Если временные значения разбиваются на категории естественным образом, можно работать с ними напрямую. Часто так обрабатываются таблицы, в которых составляющие даты или времени представлены в отдельных столбцах. Например, таблица лучших игроков baseball1.com приводит даты рождения, используя разные столбцы для года, месяца и дня. Чтобы узнать, сколько игроков родилось в каждый день года, подсчитайте записи по ка-
лендарной дате, используя значения месяца и дня, но игнорируя год:

mysql> SELECT birthmonth, birthday, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL AND birthday IS NOT NULL
-> GROUP BY birthmonth, birthday;


+-------------+-----------+------------+
| birthmonth | birthday | COUNT(*) |
+-------------+-----------+------------+
| 1 | 1 | 47 |
| 1 | 2 | 40 |
| 1 | 3 | 50 |
| 1 | 4 | 38 |
...
| 12 | 28 | 33 |
| 12 | 29 | 32 |
| 12 | 30 | 32 |
| 12 | 31 | 27 |
+-------------+-----------+------------+

Менее подробные итоги могут быть получены за счет использования только значений месяца:

mysql> SELECT birthmonth, COUNT(*)
-> FROM master
-> WHERE birthmonth IS NOT NULL
-> GROUP BY birthmonth;

+-------------+-------------+
| birthmonth | COUNT(*) |
+-------------+-------------+
| 1 | 1311 |
| 2 | 1144 |
| 3 | 1243 |
| 4 | 1179 |
| 5 | 1118 |
| 6 | 1105 |
| 7 | 1244 |
| 8 | 1438 |
| 9 | 1314 |
| 10 | 1438 |
| 11 | 1314 |
| 12 | 1269 |
+-------------+-------------+

Иногда даже не выделенные в отдельный столбец временные значения можно использовать напрямую. Чтобы определить, сколько водителей было в дороге каждый день и сколько миль проехал каждый из них, сгруппируйте записи driver_log по дате:

mysql> SELECT trav_date,
-> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
-> FROM driver_log GROUP BY trav_date;

+--------------+----------------------+---------------+
| trav_date | number of drivers | miles logged |
+--------------+----------------------+---------------+
| 2001-11-26 | 1 | 115 |
| 2001-11-27 | 1 | 96 |
| 2001-11-29 | 3 | 822 |
| 2001-11-30 | 2 | 355 |
| 2001-12-01 | 1 | 197 |
| 2001-12-02 | 2 | 581 |
+--------------+----------------------+----------------+

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

Если временной столбец содержит слишком много различных значений для того, чтобы их можно было группировать естественным образом, итоги обычно группируют при помощи выражений, которые отображают соответствующие составляющие даты или времени на небольшое количество категорий. Например, чтобы вывести итоги по времени дня для таблицы mail, сделайте следующее:

mysql> SELECT HOUR(t) AS hour,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY hour;

+------+-------------------------+--------------------------+
| hour | number of messages | number of bytes sent |
+------+------------------------+---------------------------+
| 7 | 1 | 3824 |
| 8 | 1 | 978 |
| 9 | 2 | 2904 |
| 10 | 2 | 1056806 |
| 11 | 1 | 5781 |
| 12 | 2 | 195798 |
| 13 | 1 | 271 |
| 14 | 1 | 98151 |
| 15 | 1 | 1048 |
| 17 | 2 | 2398338 |
| 22 | 1 | 23992 |
| 23 | 1 | 10294 |
+------+------------------------+---------------------------+

Чтобы вывести итоги по дню недели, используйте функцию DAYOFWEEK():

mysql> SELECT DAYOFWEEK(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY weekday;


+-----------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+-----------+-------------------------+--------------------------+
| 1 | 1 | 271 |
| 2 | 4 | 2500705 |
| 3 | 4 | 1007190 |
| 4 | 2 | 10907 |
| 5 | 1 | 873 |
| 6 | 1 | 58274 |
| 7 | 3 | 219965 |
+----------+---------------------------+--------------------------+

Чтобы сделать результат более понятным, можно использовать функцию DAYNAME() для вывода не номеров дней недели, а их названий. Однако названия дней недели сортируются в лексическом порядке (например, «Tuesday» будет стоять после «Friday»), так что применяйте функцию DAYNAME() только для отображения результатов. Продолжайте группировать по числовым значениям дней, чтобы строки вывода сортировались в таком порядке:

mysql> SELECT DAYNAME(t) AS weekday,
-> COUNT(*) AS 'number of messages',
-> SUM(size) AS 'number of bytes sent'
-> FROM mail
-> GROUP BY DAYOFWEEK(t);


+--------------+-------------------------+--------------------------+
| weekday | number of messages | number of bytes sent |
+--------------+-------------------------+---------------------------+
| Sunday | 1 | 271 |
| Monday | 4 | 2500705 |
| Tuesday | 4 | 1007190 |
| Wednesday | 2 | 10907 |
| Thursday | 1 | 873 |
| Friday | 1 | 58274 |
| Saturday | 3 | 219965 |
+--------------+--------------------------+--------------------------+

Аналогично можно суммировать значения по месяцам года, сортируя по числовому значению месяца и выводя его название.Есть множество возможностей ввода категорий для временных значений:

• Столбцы DATETIME и TIMESTAMP обычно содержат много уникальных значений. Для получения итогов за день отбросьте составляющую времени дня, чтобы привести все значения одного дня к единому значению. Любая из предложенных инструкций GROUP BY выполнит такую операцию, но последняя будет наиболее медленной:

GROUP BY FROM_DAYS(TO_DAYS(имя_столбца))
GROUP BY YEAR(имя_столбца), MONTH(имя_столбца), DAYOFMONTH(имя_столбца)
GROUP BY DATE_FORMAT(имя_столбца,'%Y-%m-%e')


• Чтобы вывести месячный или квартальный отчет о продажах, группируйте по MONTH(имя_столбца) или QUARTER(имя_столбца) для помещения дат в соответствующую часть года.

• Чтобы вывести итоговые данные об активности веб-сервера, поместите журналы сервера в MySQL и запускайте запросы, которые будут распределять записи по категориям. В главе 18 рассказано о том, как сделать это для Apache.

Добавлено: 17 Июля 2018 22:29:19 Добавил: Андрей Ковальчук

Выборка записей в случайном порядке.

В случае, когда из таблицы нужно выбрать записи и при этом отсортировать их случайным образом, крайне не желательно использовать запрос следующего вида:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1000

Нежелательной, с точки зрения производительности, является конструкция
ORDER BY RAND()
В таких случаях более производительным вариантом будет использование следующей хранимой процедуры с последующим запросом:
CREATE PROCEDURE get_rands(IN cnt INT, IN tbl CHAR(32))
BEGIN
SET @iQuery = CONCAT('DROP TEMPORARY TABLE IF EXISTS ', tbl,'_rands');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;
SET @iQuery = CONCAT('CREATE TEMPORARY TABLE ', tbl,'_rands ( rand_id INT )');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;

loop_me: LOOP
IF cnt < 1   THEN LEAVE loop_me; END IF; 
SET @iQuery = CONCAT('INSERT INTO ', tbl,'_rands SELECT r1.id FROM ', tbl ,  
' AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM ', tbl , ')) AS id) AS r2  
WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1');
PREPARE iExec FROM @iQuery;
EXECUTE iExec;
DROP PREPARE iExec;
SET cnt = cnt - 1;
END LOOP loop_me;
END

При вызове данной процедуры, нужно указать необходимое количество записей для генерации и имя таблицы, id из которой будут выбираться случайным образом:
CALL get_rands(1000, 'table_name');

В данном случае процедура создаст временную таблицу table_name_rands и запишет в нее 1000 случайных id из таблицы table_name.
Теперь можно производить выборку данных:
SELECT t.id, t.name
FROM table_name t
INNER JOIN table_name_rands r ON t.id = r.rand_id

В результате выполнения этого запроса получаем 1000 записей из таблицы table_name, отсортированных случайным образом.
При выборке большого количества записей львиную долю времени забирает выполнение процедуры, поскольку там в цикле производится вставка n-записей. Но, суммарно, время, затраченное на выполнение процедуры (генерация случайных id и запись их во временную таблицу) + последующая выборка, или обновление, или удаление, меньше, чем если бы выборка и сортировка выполнялась с использованием конструкции ORDER BY RAND(). Поскольку, при выполнении ORDER BY RAND(), происходит то же, что мы делали в процедуре, только для всей исходной таблицы! Т.е. создается временный столбец, в который записываются случайные id и потом происходит сортировка по этому столбцу.
А теперь представьте, что в исходной таблице, например, 3 млн. записей, а вам нужно выбрать 500.
Преимущество описанного способа – очевидно.
Будьте здоровы!

Добавлено: 12 Июля 2018 21:27:11 Добавил: Андрей Ковальчук

MySQL поиск и сортировка результатов по релевантности

SELECT name
FROM table_test
WHERE name LIKE '%строка_поиска%'
ORDER BY CASE 
WHEN name LIKE 'строка_поиска' THEN 0
WHEN name LIKE 'строка_поиска %' THEN 1
WHEN name LIKE 'строка_поиска%' THEN 2
WHEN name LIKE '% строка_поиска%' THEN 3
ELSE 4
END

Таким образом, результаты поиска будут отсортированы по релевантности:
строка_поиска
строка_поиска и затем какой-то текст
строка_поиска_как_часть_слова
какой-то текст строка_поиска_как_часть_слова
прочие результаты, содержащие в_конце_слова_строка_поиска
или в_середине_строка_поиска_как_часть_слова

Добавлено: 12 Июля 2018 21:25:22 Добавил: Андрей Ковальчук

MySQL одновременный SELECT и UPDATE при работе с одной таблицей

MySQL не позволит вам сделать выборку данных SELECT из таблицы в тот момент, когда одновременно делается обновление данных UPDATE той же таблицы.
Т.е. нижеприведенный пример – недопустимый:

UPDATE table_1 AS tbl_1
    SET tbl_1.cnt = (
        SELECT COUNT(*) FROM table_1 AS tbl_2
        WHERE tbl_1.type = tbl_2.type
    );

Error: You can't specify target table 'tbl_1' for update in FROM clause

В такой ситуации нужно использовать связывание таблицы с собой. Присоединяемая таблица будет реализована через временную таблицу, поэтому подзапрос откроет и закроет таблицу до того, как UPDATE откроет её. Таким образом данный запрос будет успешно выполнен:
UPDATE table_1 AS tbl_1
    INNER JOIN(
        SELECT type, COUNT(*) AS cnt
        FROM table_1
        GROUP BY type
    ) AS tbl_2 USING(type)
SET tbl_1.cnt = tbl_2.cnt;

Добавлено: 12 Июля 2018 21:23:23 Добавил: Андрей Ковальчук

Создание базы данных и тестовой таблицы

Задача
Вы хотите создать базу данных и таблицы в ней.

Решение
Применяйте предложение CREATE DATABASE для создания базы данных, предложение CREATE TABLE – для создания каждой из таблиц, которые предполагается использовать, и предложение INSERT – для добавления записей в таблицы.

Обсуждениеc
Предложение GRANT определяет права для работы с базой данных cookbook, но не создает ее. Прежде чем вы сможете работать с базой данных, необходимо ее явно создать. В данном разделе показано, как это сделать, а также как создать таблицу и наполнить ее тестовыми данными, которые будут использоваться в примерах следующих разделов.
После того как учетная запись cbuser создана, проверьте, удастся ли с ее помощью соединиться с сервером MySQL. Установив соединение, создайте базу данных. Выполните приведенные ниже команды на компьютере, имя которого было указано в предложении GRANT (за –h должно следовать имя хоста, на котором работает MySQL):

% mysql -h localhost -p -u cbuser
Enter password: cbpass
mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.08 sec)

Теперь у вас есть база данных, и можно создавать в ней таблицы. Следующие предложения указывают cookbook в качестве базы данных по умолчанию, создают простую таблицу и заполняют ее записями:

mysql> USE cookbook;
mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);


Таблица с именем limbs содержит три столбца для записи количества ног/ножек (legs) и рук/ручек (arms) различных одушевленных и неодушевленных объектов (thing). (Физиология инопланетянина из последней строки такова, что невозможно определить значения, соответствующие количеству рук и ног, поэтому использованы значения NULL – «неизвестная величина».) Чтобы проверить, содержит ли таблица предполагаемые данные, выполните предложение SELECT:

mysql> SELECT * FROM limbs;

+---------------+------+------+
| thing | legs |arms |
+---------------+------+------+
| human | 2 | 2 |
| insect | 6 | 0 |
| squid | 0 | 10 |
| octopus | 0 | 8 |
| fish | 0 | 0 |
| centipede | 100 | 0 |
| table | 4 | 0 |
| armchair | 4 | 2 |
| phonograph | 0 | 1 |
| tripod | 3 | 0 |
| Peg Leg Pete | 1 | 2 |
| space alien | NULL | NULL |
+----------------+------+------+

12 rows in set (0.00 sec)
Теперь у вас есть база данных и таблица, которые можно использовать для выполнения тестовых запросов.

Добавлено: 12 Июля 2018 08:24:51 Добавил: Андрей Ковальчук

Создание учетной записи пользователя MySQL

Задача
Вам нужно создать учетную запись для соединения с сервером MySQL, установленным на определенном хосте.

Решение
Используйте предложение (statement) GRANT для создания пользовательской учетной записи MySQL. Затем установите соединение с сервером, указав имя и пароль из созданной учетной записи.

Обсуждение
Для соединения с сервером MySQL требуются имя пользователя и пароль. Дополнительно можно указать имя хоста, на котором работает сервер. Если параметры соединения не заданы явно, mysql использует значения по умолчанию. Например, если не указано имя хоста, mysql обычно считает, что сервер работает на локальном компьютере.

Следующий пример показывает, как использовать программу mysql для соединения с сервером и выполнения предложения GRANT, которое создаст пользовательскую учетную запись с правами на доступ к базе данных cookbook.

Аргументами mysql являются: –hlocalhost (указывает на соединение с сервером MySQL, работающим на локальном компьютере), –p (сообщает программе mysql о необходимости запрашивать пароль) и –u root (соединение от имени MySQL-пользователя root). Текст, вводимый пользователем, выделен полужирным шрифтом, а вывод программы – светлым шрифтом:

% mysql -h localhost -p -u root
Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT
Bye


Если после ввода команды mysql (первая строка) вы получите сообщение, указывающее на то, что программа не найдена или введена неправильная команда, обратитесь к рецепту

1.7. Если же этого не произойдет, то в ответ на запрос пароля введите пароль пользователя root там, где в примере вы видите ******. (Если MySQL-пользователь root не имеет пароля, просто нажмите клавишу Return). Затем введите предложение GRANT, как показано выше. Если вы работаете с базой данных, отличной от cookbook, подставьте ее имя везде, где в предложении GRANT встречается cookbook. Обратите внимание на то, что даже если учетная запись пользователя уже заведена, выдача прав на доступ к базе данных все равно необходима. Однако в этом случае можно опустить часть IDENTIFIED BY 'cbpass', так как оставив ее, вы измените текущий пароль пользователя.

Часть 'cbuser'@'localhost' содержит информацию о хосте, с которого вы соединяетесь с сервером MySQL, чтобы получить доступ к базе данных cookbook. Чтобы создать пользователя для соединения с сервером, работающим на локальной машине, используйте localhost, как показано. Если же вы планируете устанавливать соединения с сервером с другого компьютера, подставьте его имя в предложение GRANT. Например, если вы как cbuser соединяетесь ссервером с хоста xyz.com, то предложение GRANT должно выглядеть так:

mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'xyz.com' IDENTIFIED BY 'cbpass';


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

Учетные записи MySQL – это не то же самое, что учетные записи для входа в вашу операционную систему. Например, MySQL-пользователь root не имеет ничего общего с UNIX-пользователем root, несмотря на то что их имена совпадают. То есть вполне вероятно, что у них разные пароли. Кроме того, это означает, что нельзя создать учетную запись MySQL, создав учетные записи для входа в систему; вместо этого необходимо использовать предложение GRANT. соединения с сервером MySQL, необходимо установить соединение с сервером с целью выполнить предложение GRANT.

Предполагается, что вы уже имеете возможность соединиться с сервером как MySQL-пользователь root, так как GRANT может применяться только таким пользователем, как root, – с правами администратора по созданию учетных записей. Если вы не можете соединиться с сервером от имени root, обратитесь к вашему администратору MySQL с тем, чтобы он выполнил для вас предложение GRANT. После того как это сделано, вы можете соединяться с сервером под собственным именем, создавать свою базу данных и вообще работать самостоятельно.

Добавлено: 12 Июля 2018 08:24:23 Добавил: Андрей Ковальчук

Поиск фразы при помощи индекса FULLTEXT

Задача
Вы хотите найти при помощи индекса FULLTEXT фразу, то есть набор смежных слов, расположенных в определенном порядке.

Решение
Используйте возможность поиска фразы, предоставляемую FULLTEXT поиском, или комбинируйте FULLTEXT поиск слов и обычный поиск по образцу.

Обсуждение
Чтобы найти записи, содержащие определенную фразу, недостаточно просто выполнить FULLTEXT поиск:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('still small voice');


+------------ +
| COUNT(*) |
+ ------------+
| 548 |
+------------ +

Запрос возвращает результат, но не тот, который хотелось бы получить. FULLTEXT поиск вычисляет релевантность по присутствию каждого отдельного слова, вне зависимости от того, где именно в столбце vtext оно встретилось. Величина релевантности будет ненулевой до тех пор, пока поиск будет обнаруживать хотя бы одно слово. Поэтому такие запросы обычно находят слишком много записей.

В MySQL версии 4.0.2 у FULLTEXT поиска появилась возможность поиска фраз в логическом режиме. Если вы хотите найти строки, содержащие какуюто фразу, просто заключите ее в двойные кавычки:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('"still small voice"' IN BOOLEAN MODE);


+------------ +
| COUNT(*) |
+ ------------+
| 1 |
+ ------------+

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

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext)
    > AGAINST('+still +small +voice' IN BOOLEAN MODE);


+------------ +
| COUNT(*) |
+ ------------+
| 3 |
+ ------------+

Если же использовать поиск по шаблону SQL, то будет возвращен правильный результат:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE vtext LIKE '%still small voice%';


+------------ +
| COUNT(*) |
+ ------------+
| 1 |
+ ------------+

Однако поиск по шаблону SQL обычно работает медленнее, чем FULLTEXT поиск. Похоже, вы оказались перед неприятным выбором: использовать быстрый способ, не выводящий желаемых результатов, или же корректно работающий, но медленный способ. К счастью, есть еще вариант: вы можете объединить оба способа в одном запросе:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('still small voice')
    > AND vtext LIKE '%still small voice%';


+------------ +
| COUNT(*) |
+ ------------+
| 1 |
+ ------------+

Берем лучшее из каждого способа:
• С помощью выражения MATCH() MySQL может выполнить FULLTEXT поиск для формирования множества строккандидатов, содержащих слова из фразы. Тем самым значительно сужается круг поиска.
• Используя сравнение с шаблоном SQL, MySQL просматривает строки кандидаты для вывода тех строк, в которых слова расположены в нужном порядке.

Данный прием не сработает, если все слова короче минимума, указанного для индексирования, или если слова встречаются более чем в половине записей. В подобных случаях FULLTEXT поиск не вернет ни одной строки, но вы все еще можете выполнить поиск по шаблону SQL.

Добавлено: 12 Июля 2018 08:23:59 Добавил: Андрей Ковальчук

Включение и исключение слов из FULLTEXT - поиска

Задача
Вы хотите специально указать слова, которые должны присутствовать или быть исключены из FULLTEXT поиска.

Решение
Используйте FULLTEXT поиск в логическом (Boolean) режиме.

Обсуждение
Обычно FULLTEXTпоиск возвращает записи, содержащие любое из слов строки поиска, даже если некоторые другие отсутствуют. Например, следующий запрос находит записи, которые содержат хотя бы одно из имен David или Goliath:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('David Goliath');


+------------ +
| COUNT(*) |
+------------ +
| 934 |
+ ------------+

Но что делать, если вам нужны только записи, содержащие оба слова? Можно переписать запрос так, чтобы искать слова по отдельности, и соединить результаты при помощи оператора AND:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('David')
    > AND MATCH(vtext) AGAINST('Goliath');


+ ------------+
| COUNT(*) |
+------------ +
| 2 |
+------------ +

Начиная с версии MySQL 4.0.1 есть еще одна возможность потребовать присутствия в выводе нескольких слов – поиск в логическом режиме. Для того чтобы показать, что слово строки поиска должно содержаться в каждой возвращенной строке, поставьте перед ним символ + и завершите строку словами IN BOOLEAN MODE:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('+David +Goliath' IN BOOLEAN MODE)


+ ------------ +
| COUNT(*) |
+ ------------+
| 2 |
+------------ +

Поиск в логическом режиме также позволяет исключать слова. Просто поставьте перед каждым нежелательным словом символ . Следующие запросы выбирают записи таблицы kjv, содержащие имя David, но не содержащие имя Goliath, или наоборот:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('+David - Goliath' IN BOOLEAN MODE)


+------------ +
| COUNT(*) |
+ ------------+
| 928 |
+ ------------+

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('David +Goliath' IN BOOLEAN MODE)


+ ------------+
| COUNT(*) |
+ ------------ +
| 4 |
+ ------------+

Еще одним специальным символом логического режима поиска является *, добавляемый в конец слова и действующий как групповой символ. Следующий запрос находит записи, которые содержат не только whirl, но и такие слова, как whirls, whirleth и whirlwind:

mysql> SELECT COUNT(*) FROM kjv
    > WHERE MATCH(vtext) AGAINST('whirl*' IN BOOLEAN MODE);


+------------ +
| COUNT(*) |
+ ------------+
| 28 |
+ ------------+

Добавлено: 12 Июля 2018 08:23:36 Добавил: Андрей Ковальчук

FULLTEXT поиск и короткие слова

Задача
FULLTEXT поиск по коротким словам не возвращает записей.

Решение
Измените значение параметра минимальной длины слова для механизма индексирования.

Обсуждение
В тексте, подобном KJV, некоторые слова имеют особое значение, например «Бог» или «грех». Но если вы, работая с сервером MySQL 3.23, выполните FULLTEXT поиск этих слов в таблице kjv, то обнаружите любопытный результат – ни того, ни другого слова как будто никогда и не было в тексте:

SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');


+------------ +
| COUNT(*) |
+ ------------+
| 0 |
+ ------------+

mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');

+ ------------+
| COUNT(*) |
+ ------------+
| 0 |
+ ------------+

Одно из свойств индексатора – игнорирование «слишком общих» слов (то есть слов, присутствующих более чем в половине записей). Так, из индекса удаляются слова типа «the» и «and», но в данном случае мы имеем дело с чемто иным. Давайте сосчитаем общее количество записей и (при помощи шаблонов SQL) количество записей, содержащих каждое из слов:1

SELECT COUNT(*) AS 'total verses',
    > COUNT(IF(vtext LIKE '%God%',1,NULL)) AS 'verses containing "God"',
    > COUNT(IF(vtext LIKE '%sin%',1,NULL)) AS 'verses containing "sin"'
    > FROM kjv;


+-------------- + -----------------------------+---------------------------- +
| total verses | verses containing "God" | verses containing "sin" |
+-------------- +----------------------------- + ----------------------------+
| 31102 | 4118 | 1292 |
+-------------- + -----------------------------+ ----------------------------+

Ни одно из слов не присутствует более чем в половине стихов, так что полно текстовый поиск не удался не изза частого употребления слов. Причина в том, что по умолчанию в индексы не включаются слова, длина которых меньше четырех символов. Если вы работаете с сервером MySQL 3.23, то вам ничего не удастся с этим поделать (по крайней мере, ничего более простого, чем обращение к исходным текстам MySQL с их повторной компиляцией).

Но начиная с версии MySQL 4.0 минимальная длина слова является настраиваемым параметром, который можно изменить, задав переменную сервера ft_min_word_len. Например, чтобы включать в индекс слова, содержащие три и более символов, добавьте строку set variable в группу
 файла /etc/my.cnf (или другого файла, в котором вы храните настройки сервера):

[mysqld]
setvariable = ft_min_word_len=3

Сохраните изменения, перезапустите сервер и пересоздайте индекс FULLTEXT, чтобы новое значение вступило в силу: 

[SQL]ALTER TABLE kjv DROP INDEX vtext;
ALTER TABLE kjv ADD FULLTEXT (vtext);


Давайте посмотрим, включает ли новый индекс короткие слова:

SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');


+------------ +
| COUNT(*) |
+ ------------+
| 3878 |
+ ------------+

mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('sin');

+ ------------+
| COUNT(*) |
+ ------------+
| 389 |
+ ------------+

Так то лучше!
Но почему запрос с MATCH() находит 3878 и 389 записей, в то время как при веденный ранее запрос с LIKE нашел 4118 и 1292 записей? Поиск по образцу с помощью LIKE ищет соответствующие подстроки, а поиск FULLTEXT, осуществляемый MATCH(), ищет только целые слова.

Добавлено: 12 Июля 2018 08:23:07 Добавил: Андрей Ковальчук