Ответы пользователя по тегу SQL
  • Update case или два запроса?

    @Joysi75
    Простые запросы проще сопровождать (нежели гроздья WHEN-ов).
    По скорости зависит от наличия одновременных запросов к этой же таблице, с точки зрения блокировок - кол-во их будет одинаковое.
    www.mysql.ru/docs/man/InnoDB_Locks_set.html и т.д.

    P.S. Подвопрос:
    ELSE text <--дополнительный вопрос-- это же не обязательно, зачем тогда так делать?
    Это во многих SQL серверах так(например, в Oracle) - анализатор не знает значений полей, участвующих в условиях When, потому ветка ELSE хотя и не обязательна, но может возникнуть RUNTIME ошибка при оптимизации запроса для выполнения.
    Ответ написан
  • Как сделать INSERT OR UPDATE?

    @Joysi75
    Выполнять с 2мя командами в СУБД.
    1) перед вашей операцией выполнить select * from table where age=X and name=Y
    2) Далее, если вернет пустой набор - делать insert
    если есть записи(ь) (не знаю, по логике возможны несколько записей или нет по одному возрасту и имени) -делать разбор возвращенного type и далее выполнить или нет необходимый update.

    Я не знаю возможностей sqlite - может в нем можно хранимую процедуру сделать, где всю логику реализовать внутри и вызывать ее с параметрами.
    Ответ написан
  • SELECT с неизвестной заранее вложенностью?

    @Joysi75
    Критично применение mysql ? В некоторых СУБД есть типы для хранения древовидных структур.
    Например MSSQL (express версия free) - https://msdn.microsoft.com/en-us/library/bb677290.aspx
    или обзорно тут https://habrahabr.ru/post/27774/
    Ответ написан
  • Как правильнее сделать выборку и построения графа рефераллов?

    @Joysi75
    MySQL обязательно использовать? Нет возможности сменить его на Postgres (или другие СУБД где есть рекурсивные запросы - Oracle/MSSQL/DB2)?

    Есть или нет готовые библиотеки для отрисовки из СУБД не знаю. Но возможно получить из таблицы пути для построения дерева. Если устроит то следующим образом.
    (К сожалению под рукой нет postgresql,) запрос примерно такой:
    WITH 
        RECURSIVE search_graph(id, ref_id) AS
        (
            SELECT id, ref_id
            FROM Users
            UNION ALL
            SELECT o.id, o.ref_id
            FROM Users o
        JOIN search_graph p ON p.ref_id = o.id
        )
        SELECT array_agg(id || ' -> ' ) AS "path"
        FROM search_graph
        GROUP BY id
        ORDER BY id
    ;

    Должен выдать:
    1 ->
    2 -> 1 ->
    3 -> 1 ->
    4 -> 1 ->
    5 -> 4 -> 1 ->

    Последний '->' можно при необходимости убрать через substring(str, from 1 for (length(str)-4))

    P.S. С MySQL форками и последними версиями не работал - может и появились рекурсивные запросы - не знаю. Или возможно через хранимую процедуру/функцию сделать аналог.
    Ответ написан
  • Насколько допустимы запросы без LIMIT?

    @Joysi75
    Плюсы использования limit:
    1) Ограничение и предсказуемость кол-во результирующих строк со стороны сервера,
    2) Меньше нагрузка на сеть,
    3) Проще проектировать GUI выдавая результат запроса "пачками" с определенным количеством строк.
    4) Подсказка для оптимизатора запросов, что приведет к увеличению производительности выборки со стороны сервера

    Минусы использования limit:
    1) Можно сломать логику и/или пропустить ошибку. Допустим в таблице справочника отсутствует primary key и задублировалась запись. Применяя выборки с limit 1 - не поймаете данную ошибку.
    2) Отсутствует в стандарте SQL до 2011.
    3) Не стандартизирован в SQL92. Многие СУБД имеют аналоги (top - MSSQL, Offset - вроде в Postgres; или, еще хуже - конструкции с where rownum = в Oracle ). То есть меняя версию СУБД Вам, возможно, придется переписать все подобного рода запросы.

    P.S. Такого рода споры о целесообразности применения конструкций СУБД необходимо решать с 2х точек зрения СУБД и Приложения. Уверены, что у Вас 99.999999% нет ошибок в реализации объектов СУБД и не планируете переходить на другие СУБД - применяйте limit, иначе задумайтесь.
    P.S.S. Аналогичен спор и о необходимости Order by. Серверные люди скажут, что order by - баловство, не стоит нагружать сервер задачами сортировки полученных данных - это "крестьянское" дело должно ложиться на плечи приложения :)
    Ответ написан
  • Множественные объединения c условием?

    @Joysi75
    По моему все можно :-) ибо
    Я бы делал через subquery (удобно для выборок по разным условиям из составленного "набора" пересечений)
    select BSproduct from
      ( -- Список продуктов имеющих черный цвет и малый размер
        select product as BSProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where color = "black" and size = "S"  
      ) as BlackAndSmallSize, 
      ( -- Список продуктов имеющих черный цвет и средний размер
        select product as BMProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where color = "black" and size = "M"  
      ) as BlackAndMidSize, 
      ( -- Список продуктов имеющих большой размер
        select product as LProduct from
          (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          ) 
        where size = "L"  
      ) as LargeSize
    where BSProduct=BMProduct and BMProduct=LProduct


    Не знаю как насчет MySQL и его форков, в некоторых SQL серверах можно заранее задавать подзапросы типа
    (
            SELECT  t1.id,  t1.name AS product,  t3.name AS color,  t5.name AS size
              FROM  `product` AS t1
              JOIN `product_color` AS t2 ON t1.id = t2.product_id
              JOIN `color` AS t3 ON t2.color_id = t3.id
              JOIN `product_size` AS t4 ON t1.id = t4.product_id
              JOIN `size` AS t5 ON t4.size_id = t5.id
          )

    с помощью команды WITH - тогда объем текста выборки сократится в разы.
    Ответ написан
  • Можно ли выполнить UPDATE + INSERT одним запросом?

    @Joysi75
    Если нужно 2 эти операции делать в связке, то можно закрепить это поведение в самой БД через триггер, который после выполнения UPDATE сам инициирует необходимый INSERT. Логику insert-а (в вопросе уже готовые значения) пропишите в теле триггера (для Update возможен доступ к значениям до выполнения команды через псевдозапись (или как правильно в Maria они называются) OLD, к новым значениям - через псевдозапись NEW.

    CREATE TRIGGER `update_users` AFTER UPDATE ON `users`
    FOR EACH ROW 
    BEGIN
       INSERT INTO payments(status_id, user_id, description, currency_id, 
           amount, bonus_amount, sum) 
        values('2' ,new.id, 'test', 643, 
           old.balance, old.balance+new.balance, new.balance);
    END;


    тут свои + и -.
    +) БД управляет сама поведением записей (на случай, если кто 'забудет' прописать вторую команду или оформить транзакцию, или кто вручную добавляет данные из PhpMySQLAdmin и т.п.) согласно заданной логике.
    -) Нужно хорошо знать БД (где и что она хранит и т.п. ;) )
    Вопрос производительности не имеет однозначного ответа, так как зависит от многих факторов (ресурсы и текущая нагрузка сервера, частота изменений ...)
    Ответ написан
  • Как организовать сложный запрос в SQL?

    @Joysi75
    select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb
    where p.product_id = pa.product_id and p.category_id=8 and
    pa.product_id  not in (
      select pa2.product_id from product_attribute pa2
         where (pa2.attribute_id=9 and pa2.text=92) or (pa2.attribute_id=8 and pa2.text=55)
    ) and
    not((pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and  pa.text=55))

    Выражения по типу (pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and pa.text=55) надо формировать динамически или

    Создать третью таблицу filtr_sess (sid, attribute_id, text) куда перед вызовом закидывать текущую сессию + выбранные атрибуты. Тогда вызов можно осуществлять только по Sid-у и Category_Id.
    Примерно так (пишу без доступа к СУБД с чужого ПК, прошу не винить в синтаксисе):
    select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb, filtr_sess fs
    where p.product_id = pa.product_id and p.category_id=8 and
    pa.product_id  not in (
    select pa2.product_id from product_attribute pa2, filtr_sess s
         where (pa2.attribute_id=s.id and pa2.text=s.text and s.sid=X)
    ) and fs.sid=X and pa.attribute_id <> fs.id and pa.text <> fs.text
    Ответ написан
  • Как лучше сформировать Access List в SQL?

    @Joysi75
    Если ваше приложение не должно работать с многими разнообразными СУБД, то может лучше переформулировать вопрос на:
    "Какая СУБД позволяет ограничивать доступ на чтение-модификацию-удаление в таблицах с точностью до выдачи таких разрешений на уровень отдельных полей таблиц?".
    Например, MSSQL (и другие) давно делает это стандартными средствами.
    Можете вытаскивать эти данные из служебных таблиц или через встроенные процедуры.
    И СУБД, естественно сама контролирует введенные вами же правила.

    Если хочется самому реализовывать. То смоделируйте в таблицах и далее нормализуйте:
    Пользователи
    Группы
    Роли (Администратор, Read-only, Guest ...)

    Справочник объектов БД (справочник: таблица, хранимая процедура, поле таблицы ...)
    Объекты БД ( название таблицы, название поля таблицы со ссылкой на эту же таблицу, параметр хран процедуры...)
    Свойства объектов (поля, параметры храним процедур)
    Справочник операций над объектами (запуск хран процедур, удаление записи в таблице, модификация ПОЛЯ таблицы ...)
    Операции над объектами(к какой роли привязана какая операция из справочника + над каким объектом)
    Журнал аудита(Кто, Что, Когда - ссылки на Пользователя, Объект и Операцию + дата).
    и т.п.
    Ответ написан
  • Почему использование триггера в mysql/oracle/mssql ... в web-программирование (и не только) считается признаком говнокода?

    @Joysi75 Автор вопроса
    прошу прощения за неправильную терминологию, но:
    Если взять "слои" Web-приложения, то почему нельзя сделать следующее распределение и вынести в СУБД
    Model - (СУБД - table, view + возможно stored_procedures, triggers)
    Controller - (СУБД - stored_procedures,functions,triggers)
    И оставить для веб-программирования "слои" Route и View.

    P.S. Ведь и freeware (и т.п. схемы лицензирования) СУБД (mysql ...) имеют эти механизмы и грех ими не пользоваться...
    Ответ написан
  • Как правильно составить выборку из таблиц по связи многие-ко-многим и сделать CONCAT в MySQL?

    @Joysi75
    Примерно так (пишу с левого компа, где нет mysql - если что отредактируйте немного)
    select product_id as id, products.name,
      group_concat(sizes.value separator ';') as sizes 
    from product_sizes, sizes, products 
    where product_id=products.id and size_id=sizes.id 
    group by product_id


    Добавлено после комментария автора топика:
    Если необходимо выводить и те товары , у которых не указан размер, тогда:
    select products.id, products.name, 
      group_concat(sizes.value separator ';') as sizes 
    from product_sizes 
      inner join sizes on (size_id=sizes.id) 
      right join products on (product_id=products.id) 
    group by product_id order by id;
    Ответ написан
  • Как ускорить поиск по таблице?

    @Joysi75
    `Phone` varchar(15) NOT NULL
    Если позволяет место на сервере лучше смените на
    `Phone` char(15) NOT NULL или (если не хранятся в значениях нецифровые символы - скобки, например)`Phone` int NOT NULL
    Так как в этом случае будет индексироваться по ключу фиксированной длины (в случае с int еще лучше, будет индексироваться по 4/8 байтам вместо 15) - соответственно поиск будет быстрее.
    Ответ написан
  • Как написать sql запрос в котором возможно придется транспонировать данные в таблице?

    @Joysi75
    В таком виде не подойдет ?
    mysql> select email, group_concat(concat(character_name, ':', 
    class_name, ' ',character_level,' lvl') separator ', ') as Chars 
    from characters,players,classes 
    where player_id=players.id and class_id=classes.id group by player_id;
    +--------+--------------------------------------------+
    | email  | Chars                                      |
    +--------+--------------------------------------------+
    | a@a.ru | Merlin:wizard 70 lvl, Conan:warrior 80 lvl |
    | b@b.ru | Azariel:warrior 50 lvl, Bilbo:thief 20 lvl |
    +--------+--------------------------------------------+
    2 rows in set (0.00 sec)

    в ORacle вместо group_concat используйте LISTAGG

    Если надо отдельно в каждой колонке выводить по отдельному классу, то измените select добавив через запятую выражение аналогично выводу для Сhars, но с использованием, например , CASE где сравниваете через WHEN с требуемым классом.
    Ответ написан
  • MySQL запрос, как отбросить в строке последнее слово?

    @Joysi75
    Можно и другим "монстром" :-)
    select substring_index(st1, ' ', length(st1) - length(replace(st1,' ',''))) as alt ....
    Ответ написан