SQL — урок 4. Выборка данных из базы

Базы данных SQL — урок 4. Выборка данных из базы

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 8 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5183
      @admin

      Выборка записей

      Выбор данных выполняется командой SELECT.
      Ниже приведен примерный список используемых ею конструкций и ключевых слов, полный же список зависит от реализации СУБД:

      • AS — определяет временный синоним источника данных или столбца;
      • FROM — указывает источники данных как таблицы, представления, другие выборки. По необходимости здесь можно указать соединение источников — каким образом запись одного источника сопоставляется с записью другого;
      • WHERE — позволяет указать условия по которым нужно производить отбор данных. Если хоть одно из перечисленных условий не выполняется, запись не попадает в выборку. Здесь также можно указать соединение источников;
      • ORDER BY — позволяет отсортировать выборку по указанным полям;
      • ASC, DESC — задают направление сортировки;
      • GROUP BY — позволяет разбить выборку на группы по указанному полю. Все записи, имеющие одно и то же значение в указанном поле, будут принадлежать одной группе;
      • HAVING — позволяет задать условие включения группы в выборку. Набор возможных условий как у WHERE плюс возможность использования агрегатных функций;
      • FOR UPDATE — позволяет заблокировать выбранные данные для изменения;
      • DISTINCT — позволяет включить в выборку только уникальные записи. Конечно это замедляет запрос, но бывает необходимо при использовании агрегатных функций.

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

      -- создание таблицы
      create table tblname (
      id integer,
      num double precision, 
      
      constraint pk_tblname primary key (id)
      );
      
      -- вставка данных
      insert into tblname values(1,2.0);
      insert into tblname values(2,3.3);
      insert into tblname values(3,6.6);
      insert into tblname values(4,2.0);
      insert into tblname values(5,3.3);
      insert into tblname values(6,6.6);
      commit;

      выборка констант

      Для выбора констант может указываться любой источник. Однако, если мы хотим просто
      подсчитать значение какого-то выражения, то указывать источник имеющий сотню тысяч записей затратно по ресурсам. Поэтому многие СУБД позволяют выбрать константы без указания источника. Oracle не поддерживает синтаксис SELECT без FROM, а для выбора констант используется специальная системная таблица dual.

      -- вычисляем три простых выражения
      SELECT 2+2, 2*2 , 2/2;
      SELECT 2+2, 2*2 , 2/2 FROM dual; -- для Oracle
      
      -- в выборке будет 6 одинаковых записей
      SELECT 2+2, 2*2 , 2/2 FROM tblname;

      выборка по столбцам таблиц

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

      -- перечисляем столбцы
      SELECT tblname.id, tblname.num FROM tblname;
      SELECT tblname.num FROM tblname;
      SELECT tblname.num*2 FROM tblname;
      
      -- все столбцы указанного источника
      SELECT tblname.* FROM tblname;
      
      -- все столбцы всех источников
      SELECT * FROM tblname;

      синонимы (алиасы)

      При выборе данных можно назначать временные синонимы источникам данных и используемым столбцам. А в некоторых случаях необходимо. Например, когда источник подзапрос соединяется с другим источником, именование подзапроса обязательно. Другой пример, это объединение нескольких выборок, имена столбцов которых должны совпадать. Ключевое слово AS как правило необязательно, а в Oracle разрешено только для столбцов.

      -- t синоним таблицы tblname
      SELECT t.id, t.num 
          FROM tblname AS t; -- не для Oracle
      SELECT t.id, t.num 
          FROM tblname t;  -- для всех
      
      -- именование подзапроса, используемого 
      -- как источник данных
      SELECT * FROM (select num from tblname ) t
      
      -- источник подзапрос соединяется 
      -- с другим источником
      SELECT t.tid, t2.num 
          FROM (
               select id as tid, num as tnum 
               from tblname 
               ) t, 
               tblname t2 
          WHERE t.tid=t2.id 
      
      -- синоним для столбца
      SELECT t.num*2 AS num2 FROM tblname t;
      
      -- "двойное число" русский синоним для столбца
      SELECT t.num*2 AS "двойное число" 
          FROM tblname t;
      SELECT t.id "ид", t.num*2 AS "двойное число" 
          FROM tblname t;
      
      -- `` расширение MySQL
      SELECT t.num*2 AS `двойное число` FROM tblname t; 

      уникальные записи

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

      -- есть одинаковые записи
      SELECT t.num FROM tblname t;
      
      -- только уникальные записи
      SELECT DISTINCT t.num FROM tblname t;

      Конструкция ORDER BY позволяет последовательно отсортировать сразу по нескольким столбцам. Столбцы, по которым происходит сортировка, желательно проиндексировать.

      -- сортировка записей по полю num
      SELECT * FROM tblname ORDER BY num
      
      -- восходящая сортировка
      SELECT * FROM tblname t 
        ORDER BY t.num ASC
      
      -- нисходящая сортировка
      SELECT * FROM tblname 
        ORDER BY t.num DESC

      выборка по условию

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

      SELECT * FROM tblname t WHERE t.id > 3
      
      -- следующие примеры возвращают одни и те же записи
      SELECT * FROM tblname t WHERE t.id=2 or t.id=4;
      
      SELECT * FROM tblname t WHERE t.id in (2,4);
      
      SELECT * FROM tblname t 
        WHERE t.id in(
            -- для Oracle: select 2 as c from dual
            select 2 as c 
            union
             -- для Oracle: select 4 as c from dual
            select 4 as c 
            ); -- для Oracle
            
      SELECT t.* FROM
          tblname t,
          (
           -- для Oracle: select 2 as c from dual
           select 2 as c 
           union
           -- для Oracle: select 4 as c from dual
           select 4 as c 
          ) t2
      WHERE t.id=t2.c;  

      выборка по группам

      И напоследок пару примеров группировки данных.

      -- группировка по полям num и id
      SELECT t.num, t.id FROM tblname t 
        GROUP BY num, id; 
      
      -- первый столбец будет отсортирован по возрастанию
      -- второй столбец каждой группы по убыванию 
      SELECT t.num, t.id FROM tblname t 
        GROUP BY num, id  
        ORDER BY num ASC, id DESC
        
      -- исключаем из выборки группу 3.3
      SELECT t.num, t.id FROM tblname t 
        GROUP BY num, id  
        HAVING num!=3.3
        ORDER BY num ASC, id DESC    

      Соединения

      Пусть имеются следующие две таблицы с данными.

      -- таблица цветов
      create table colors(
      id integer,
      cname varchar(45), -- название цвета
      
      constraint pk_colors primary key (id)
      );
      
      insert into colors values(1,'красный');
      insert into colors values(2,'синий');
      insert into colors values(3,'зеленый');
      insert into colors values(4,'белый');
      insert into colors values(5,'черный');
      insert into colors values(6,'бурмалиновый');
      
      -- таблица объектов мира
      create table objs (
      id integer,
      oname varchar(45), -- название объекта
      fk integer, -- ссылка на цвет объекта
      
      constraint pk_objs primary key (id),
      constraint fk_objs foreign key (fk) 
         references colors(id) 
      );
      
      insert into objs values(1,'небо',2);
      insert into objs values(2,'уголь',5);
      insert into objs values(3,'лист',3);
      insert into objs values(4,'водка',null );
      insert into objs values(5,'ягода',1);
      
      commit;

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

      select o.oname, c.cname from objs o, colors c;

      Для присоединения к каждой записи первой таблицы, только ту запись из второй таблицы, на которую происходит ссылка, необходимо указать условие.
      Это можно сделать в конструкции where или в конструкции from.
      Ниже приведен пример запроса с внутренним соединением.

      -- условие в where
      select o.oname, c.cname 
        from objs o, colors c 
        where o.fk=c.id;
      
      -- условие в from с помощью
      -- конструкции JOIN ON   
      select o.oname, c.cname from 
         objs o INNER JOIN colors c ON (o.fk=c.id );

      К одному источнику данных можно последовательно присоединять сколько угодно других
      источников.

      select o.oname, c.cname, o2.oname as oname2 
         from 
         (objs o INNER JOIN colors c ON (o.fk=c.id ))
         INNER JOIN (select * from objs ) o2 ON
         (o.id=o2.id) 

      В нашем случае внутреннего соединения не достаточно, так как из выборки выпадает запись с пустым значением внешнего ключа. Если же поставить условие
      «o.fk=c.id or o.fk is null», то вновь получим лишние записи. Данная задача решается
      с помощью внешних соединений. Если по условию для записи одной таблицы не найдено ни одной записи из другой таблицы, то присоединится пустая запись. Слова одной и другой использованы здесь не случайно. При внешних соединениях явно указывается к какой таблице происходит присоединение. В ниже приведенном примере ключевое слово OUTER в большинстве СУБД можно опустить.

      -- присоединяем к левой таблице
      -- в результате в выборке будет 5 записей
      -- в столбце cname будет одно пустое поле
      select o.oname, c.cname from 
         (objs o LEFT OUTER JOIN colors c ON (o.fk=c.id ))
      
      -- присоединяем к правой таблице
      -- в результате в выборке будет 6 записей
      -- в столбце oname будет два пустых поля
      select o.oname, c.cname from 
         (objs o RIGHT OUTER JOIN colors c 
          ON (o.fk=c.id ))
      
      -- в расширении Oracle можно указать (+)
      -- в условии для той таблицы, для которой
      -- будут генерироваться пустые записи
      select o.oname, c.cname 
         from objs o, colors c
         -- присоединяем к левой таблице   
         where o.fk=c.id(+) 
         
      select o.oname, c.cname 
         from objs o, colors c
         -- присоединяем к правой таблице
         where o.fk(+)=c.id 

      Пустое значение в выборке можно заменить функцией coalesce.

      -- в MySQL между coalesce и скобкой не должно 
      -- быть пробелов. 
      select o.oname, coalesce(c.cname,'цвет не задан')
         from (objs o LEFT OUTER JOIN colors c 
               ON (o.fk=c.id ));

      Агрегатные функции, группировка данных

      Для группировки данных в запросе select используется конструкция group by,
      в которой должны быть перечислены те же столбцы, что и после select. Ниже приведен
      пример вывода данных по группам для таблицы bills.

      -- таблица счетов
      create table bills(
      id integer,
      d date,  -- дата счета
      summ double precision ,-- сумма счета
      constraint pk_bills primary key (id)
      );
      
      -- вставка данных
      insert into bills 
          values(1, date '2008-01-01', 5.5);
      insert into bills 
          values(2, date '2008-02-01', 3.14);
      insert into bills 
          values(3, date '2008-03-01', 10.14);
      insert into bills 
          values(4, date '2008-01-01', 7.2);
      insert into bills 
          values(5, date '2008-02-01', 6.4);
      insert into bills 
          values(6, date '2008-03-01', 2.5);
      commit;
      
      -- вывод данных по группам
      select t.d, t.summ from bills t
         group by t.d, t.summ

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

      • avg([DISTINCT|ALL] column) — среднее значение по указанному столбцу;
      • count(*|[DISTINCT|ALL] соlumn) — количество элементов в выборке
        или в группе определяемой указанным столбцом;
      • sum([DISTINCT | ALL] соlumn) — сумма значений указанного столбца;
      • max(соlumn) — максимальное значение в столбце;
      • min(соlumn) — минимальное значение в столбце.

      Ключевое слово DISTINCT позволяет игнорировать повторные значения в столбце, ALL
      обрабатывает все значения в столбце (по умолчанию), * позволяет включить в обработку поля с null значением.
      В MySQL между именем функции и скобкой не должно быть пробелов.
      Ниже приведен пример использования агрегатных функций в качестве выбираемых данных. Если
      агрегатная функция используется в выборке без group by, то она применяется ко всем записям
      выборки, иначе для каждой группы в отдельности. И в любом случае в перечислении select нельзя
      смешивать групповые столбцы с не групповыми.

      -- статистические данные по всем месяцам
      select count(*) as "число записей",
             max(t.summ) as "макс. сумма",
             min(t.summ) as "мин. сумма",
             avg(t.summ) as "средняя сумма",
             sum(t.summ) as "общая сумма" 
      from bills t; 
      
      -- статистические данные по каждому месяцу
      select t.d as "месяц", count(1) as "число записей",
             max(t.summ) as "макс. сумма",
             min(t.summ) as "мин. сумма",
             avg(t.summ) as "средняя сумма",
             sum(t.summ) as "общая сумма" 
      from bills t 
      group by t.d

      Агрегатные функции можно использовать в выражениях условия в конструкции having для
      отбора группы.

      -- отбираем группы у которых общая сумма больше 12
      select t.d as "месяц", count(*) as "число записей",
             max(t.summ) as "макс. сумма",
             min(t.summ) as "мин. сумма",
             avg(t.summ) as "средняя сумма",
             sum(t.summ) as "общая сумма" 
      from bills t 
      group by t.d
      having sum(t.summ)>12

      Операции над выборками

      Так как выборка по сути является множеством, то и доступные операции над ними
      соответствующие:

      • UNION — объединение, в конечной выборке записи из обоих запросов;
      • INTERSECT — пересечение, в конечной выборке записи входящие в оба запроса;
      • EXCEPT — исключение, в конечной выборке записи входящие только в первый запрос.

      Запросы участвующие в таких операциях должны следовать нескольким условиям.
      Иметь одинаковое число столбцов, соответствующие столбцы должны быть одного типа.
      Тип данных столбца должен быть простым, т.е. не разрешаются типы подобные blob.
      MySQL 5 поддерживает только UNION, в Oracle EXCEPT для других целей,
      а для исключения используется MINUS.

      -- from dual только для Oracle
      -- в MySQL нельзя заключить 
      -- запросы в круглые скобки
      select 1 as i  from dual
        UNION
      select 2 as i  from dual
      
      UNION -- попробуйте также INTERSECT и EXCEPT
      
      select 2 as i from dual
        UNION
      select 3 as i  from dual;

      По умолчанию в результирующую выборку попадают только уникальные записи.
      Для включения всех записей используется ключевое слово ALL после имени операции.
      Например, в следующем примере будет две записи со значением 2.

      select 1 as i  from dual
        UNION
      select 2 as i  from dual
      
      UNION ALL
      
      select 2 as i from dual
        UNION
      select 3 as i  from dual;

      Добавление итогов в SQL

      Еще раз рассмотрим таблицу bills созданную в пункте об агрегатных функциях.
      Предположим мы хотим вывести все суммы, а в конце выборки добавить итоговую сумму.
      Наиболее универсальным способом является объединение двух запросов.

      -- в MySQL между cast и ( не должно быть пробелов
      select cast(t.d as char(12)) as d, t.summ
        from bills t
      
      union
      
      select 'ИТОГ', sum(t.summ)
        from bills t;

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

      -- для Oracle, столбцы группировки перешли в ROLLUP
      select 
         coalesce(cast(t.d as char(12)),'ИТОГ') as d, 
         sum(t.summ) as summ 
       from bills t
       group by ROLLUP(t.d, t.summ)
      
      -- для MySQL
      select
         coalesce(cast(t.d as char(12)),'ИТОГ') as d,
         sum(t.summ) as summ
       from bills t
       group by t.d, t.summ WITH ROLLUP;

      А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки
      общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в
      конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение
      null.

      select t.d1, t.summ from (
      
      select
          cast(t.d as char(12)) as d1,
          t.d,
          t.summ
        from bills t
      
      union
      
      select -- выборка итогов по группам
          'итого' as d1,
          t.d,
          sum(t.summ) as summ
        from bills t
        group by t.d
      
      union
      
      select -- выборка общего итога
          'ИТОГО' as d1,
          DATE '9999-01-01' as d,
          sum(t.summ) as summ
        from bills t
      ) t
      order by d,summ

      Подобную задачу можно решить с помощью стандартной конструкции CUBE, если она уже
      реализована в СУБД. Куб генерирует не только общий итог, но и все возможные под итоги.
      Ниже приведен пример использования куба. Для упрощения кода пустые значения не заменяются.

      select * from (
         select 
             t.d, 
             t.summ, 
             sum(t.summ) as itog 
           from bills t
           -- для MySQL: group by t.d, t.summ WITH CUBE
           group by cube(t.d, t.summ) -- для Oracle
           order by d
         ) t
         where -- убираем не нужные под итоги
           not(t.summ=itog and d is null) or
           (d is null and summ is null)    

      Нумерация записей

      В стандарт SQL2003 уже добавлена функция row_number(), если она еще не реализована
      в вашей версии БД, используйте следующие методы.

      Oracle

      В Oracle для нумерации записей введен псевдостолбец rownum.

      select rownum, t.* 
          from tblname t 
          order by id desc
      
      -- rownum можно использовать в условиях
      -- попробуйте операции <> и > 
      select rownum, t.* 
          from tb t 
          where rownum < 4 
          order by id desc;

      MySQL

      В MySQL для этого надо воспользоваться переменной. Чтобы увидеть результат следующего
      примера в MySQLQueryBrowser, необходимо начать транзакцию (на панели кнопка после слова
      Transaction). Далее выполняем приведенные в примере команды и затем завершаем транзакцию
      (соседняя кнопка с галочкой).

      -- устанавливаем значение локальной переменной в 0
      -- ее можете назвать как хотите
      set @rownum:=0;
      
      -- выборка с нумерацией
      select @rownum:=@rownum+1, t.* 
          from tblname t
          order by id desc;

      PostgreSQL

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

      -- создаем временную (для текущей сессии) 
      -- последовательность seqrownum для нумерации записей
      create temp sequence seqrownum;
      
      -- сбрасываем последовательность
      select setval('seqrownum',1);
      
      -- выборка с нумерацией
      select nextval('seqrownum')-1, t.* 
          from tblseq t 
          order by id desc;

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×