Получите бесплатно 4 курса для лёгкого старта работы в IT
Получить бесплатно
Главная БлогВыборка данных: как осуществляется и какие нюансы существуют
Выборка данных

Выборка данных: как осуществляется и какие нюансы существуют

Дата публикации: 18.10.2022
9 788
Время чтения: 16 минут
Дата обновления: 28.09.2023
В статье рассказывается:

О чем речь? Выборка данных – это, как следует из названия, отбор информации из базы данных по заданным критериям. За этот процесс отвечают определенные операторы, которые формируют тип запроса и необходимые критерии.

Как сделать? Данная задача является не самой простой, так как приходится разбираться в сложном синтаксисе. Однако уловив последовательность команд, дело остается за малым – получить и обработать необходимую информацию.

В статье рассказывается:

  1. Суть выборки данных
  2. Выборка данных через оператор SELECT
  3. Группировка данных при выборке
  4. Нюансы выборки данных из ORM систем
  5. Пройди тест и узнай, какая сфера тебе подходит:
    айти, дизайн или маркетинг.
    Бесплатно от Geekbrains

Суть выборки данных

Любая реляционная СУБД имеет такую функцию, как выборка данных (команда SELECT). Она является одной из самых востребованных, но при этом и сложнейших в плане синтаксиса. Однако, при всей сложности и объёмности предложений SQL, выборка данных из базы не представляет какой-то проблемы.

Суть выборки данных
Суть выборки данных

Чтобы успешно произвести выборку, необходимо чётко понимать, какая последовательность ключевых слов в запросе необходима и каким будет результат по каждому ключевому слову. Мы будем рассматривать примеры по мере усложнения. Начнём с самых простых случаев выборки данных из базы и пока не будем использовать какие-либо клаузулы или предикаты (уточняющие фразы) для определения условий, фильтрации данных в выборке и сортировке отфильтрованных значений.

Приступая к работе с выборками данных, всегда помните одно важное правило: команда SELECT в SQL-запросе всегда вернёт вам данные в формате таблицы. И неважно, насколько сложный у вас запрос. SQLite и любая другая РСУБД будет возвращать результат выборки данных в виде таблицы.

Кроме того, необходимо располагать ключевые слова в правильном порядке:

  • Начинаем с ключевого слова SELECT.
  • После него идут круглые скобки, где мы указываем колонки, из которых нам необходимо получить значения.
  • Затем следует ключевое слово FROM.
  • Пишем имя таблицы, к которой обращаемся за данными.
  • Прописываем остальные ключевые слова (тоже в строгой последовательности, но сейчас не будем останавливаться на этом подробно, дабы не запутаться).
Узнай, какие ИТ - профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов - исполнительный директор Geekbrains
Павел Симонов
Исполнительный директор Geekbrains
Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.
Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!

Скачивайте и используйте уже сегодня:

Павел Симонов - исполнительный директор Geekbrains
Павел Симонов
Исполнительный директор Geekbrains
pdf иконка

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

Поможет разобраться в актуальной ситуации на рынке труда

doc иконка

Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка

Только проверенные нейросети с доступом из России и свободным использованием

pdf иконка

ТОП-100 площадок для поиска работы от GeekBrains

Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽

pdf 3,7mb
doc 1,7mb
Уже скачали 32472 pdf иконка

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

Выборка данных через оператор SELECT

Элементами оператора SELECT в SQL являются блоки, определяющие параметры выражения.

Для MySQL обязательный блок — первый, сам SELECT.

Всего в SELECT есть три блока:

  • Собственно SELECT: те данные, которые мы хотим получить из базы. В каком-то смысле аналогичен переименованию и проекции в реляционной алгебре.
  • FROM: устанавливает диапазон данных в выборке (сообщает, откуда начинать выбирать). По аналогии с реляционной алгеброй это аргумент операции.
  • WHERE: обязательное условие выборки данных, которому они должны соответствовать. В реляционной алгебре подобное называется операцией выборки.

Блок SELECT

Наподобие проекции:

SELECT col1, col2, …

Помещает в выборку только данные из указанных столбцов. Чтобы выбрать все без исключения столбцы, применяем синтаксис SELECT *.

Наподобие переименования:

SELECT col1 as name1, col2 as name2, …

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

Это самые базовые варианты использования SELECT, но его возможности намного шире. Например, можно подставить значение или функцию (в том числе оператор). Если написать:

SELECT ‘Hello World!’ as Hello;

То получим следующую выборку:

Hello
Hello World!

При наличии в данных таблицы operands

a b
1 10
2 15
3 20

запрос к базе будет иметь вид:

SELECT a, b, a+b as c FROM operands

Результат:

a b c
1 10 11
2 15 17
3 20 23

В SQL имеется масса встроенных функций, которые могут работать с временны́ми данными, преобразовывать типы, обрабатывать статистику и т. п.

Блок FROM

Этот блок используется для того, чтобы уточнить аргумент SELECT. Если брать самые простые случаи, то во FROM указывают имя таблицы (отношения).

Блок FROM
Блок FROM

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

SELECT a+b FROM (SELECT 1 as a, 2 as b) as tbl1;

Дарим скидку от 60%
на обучение «Аналитик больших данных» до 22 декабря
Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей
Забронировать скидку

Кроме того, посредством блока FROM можно вычислять декартовы произведения и делать конкатенацию. В этом нам поможет JOIN, бинарный оператор.

Предположим, у нас есть таблица bin:

a
0
1

По запросу в базу

SELECT * FROM bin b1 JOIN bin b2 JOIN bin b3;

получим декартово произведение bin×bin×bin:

a a a
0 0 0
1 0 0
0 1 0
1 1 0
0 0 1
1 0 1
0 1 1
1 1 1

JOIN и является оператором декартова произведения. Есть несколько вариантов JOIN: INNER JOIN, используемый по умолчанию, NATURAL, OUTER RIGHT JOIN, OUTER LEFT JOIN, OUTER FULL JOIN.

Блок WHERE

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

К примеру, по запросу:

SELECT * FROM bin WHERE a>0;

вы получите:

a
1

Помните, что любое переименование осуществляется только после выборки. Поэтому, например, выражение.

Только до 26.12
Скачай подборку материалов, чтобы гарантированно найти работу в IT за 14 дней
Список документов:
ТОП-100 площадок для поиска работы от GeekBrains
20 профессий 2023 года, с доходом от 150 000 рублей
Чек-лист «Как успешно пройти собеседование»
Чтобы получить файл, укажите e-mail:
Введите e-mail, чтобы получить доступ к документам
Подтвердите, что вы не робот,
указав номер телефона:
Введите телефон, чтобы получить доступ к документам
Уже скачали 52300

SELECT a as b FROM bin WHERE b>0;

неправильное: здесь блок WHERE видит только аргумент FROM, а переименование — ещё не видит.

Исходя из этого правила, блок WHERE можно использовать только совместно с блоком FROM, и выражение вроде:

SELECT 1 WHERE TRUE;

просто не будет работать.

Но, если очень нужно, можно задействовать dual («пустую» таблицу, из которой напрямую ничего нельзя выбрать — вернётся ошибка):

SELECT 1 FROM dual WHERE TRUE;

Этот вариант вполне рабочий.

Dual можно указывать, если по синтаксису SQL требуется именно таблица.

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

Группировка данных при выборке

Чтобы сгруппировать данные в 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 и т. п.).

В MySQL5 есть только поддержка 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;

Нюансы выборки данных из ORM систем

При работе с моделями данных, содержащими только одну сущность, никаких сложностей с ORM не возникает. Разберём простой пример. Предположим, у нас есть сущность Пользователь (User) с двумя атрибутами — именем (Name) и ID.

public class User {

@Id

@GeneratedValue

private int id;

private String name;

//Getters and Setters here

}

Как же вытащить из базы данных экземпляр данной сущности? Очень просто: с помощью одного метода объекта EntityManager:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, id);

А вот в случае, когда есть отношение «один-ко-многим», всё становится намного интереснее:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany

private List<Address> addresses;

//Getters and Setters here

}

Наверное, вы уже задаётесь вопросом, а нужно ли делать выборку данных по адресам, извлекая экземпляр пользователя. Верный ответ — по-разному: если эти адреса нам нужны, то да, делаем, если нет — то нет. Как правило, в ORM доступны два способа выбрать зависимую запись: жадный и ленивый. Последний применяется по умолчанию во многих ORM. Однако если ваш код выглядит вот так:

EntityManager em = entityManagerFactory.createEntityManager();

User user = em.find(User.class, 1);

em.close();

System.out.println(user.getAddresses().get(0));

то вы получите исключение “LazyInitException”. Оно всегда вызывает недоумение у начинающих программистов, испытывающих недостаток опыта работы с ORM. Пора вводить новые понятия — сессия в транзакции, Detached и Attached экземпляры сущности.

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

С базами данных такое тоже, увы, возможно. Возрастание мелких транзакций создаёт проблемы с производительностью.

Но, как уже говорилось, адреса далеко не всегда требуются при получении данных о пользователе, и только бизнес-логика определяет, попадут адреса в запрос на выборку данных или нет. То есть, придётся прописывать дополнительные проверки. Как-то всё слишком сложно получается, не правда ли?

Но можно пойти иным путём и просто сменить тип выборки:

public class User {

@Id

@GeneratedValue

private int id;

private String name;

@OneToMany(fetch = FetchType.EAGER)

private List<Address> addresses;

//Getters and Setters here

}

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

Откройте для себя захватывающий мир IT! Обучайтесь со скидкой до 61% и получайте современную профессию с гарантией трудоустройства. Первый месяц – бесплатно. Выбирайте программу прямо сейчас и станьте востребованным специалистом.

Усталость от ORM и переход на другие фреймворки — нередкое явление среди бэкенд-разработчиков. Многие выбирают Spring JDBC, в котором реляционные данные можно преобразовать в объектные, причём в полуавтоматическом режиме. Необходимо писать запросы под каждую ситуацию, где требуется та или иная совокупность атрибутов. А если нужны одни и те же структуры данных, то код можно переиспользовать.

Это обеспечивает большую степень гибкости. К примеру, не нужно создавать новый объект-сущность, достаточно выбрать всего один атрибут:

String name = this.jdbcTemplate.queryForObject(

«select name from t_user where id = ?»,

new Object[]{1L}, String.class);

Хотя можно выбрать и объект, как обычно:

User user = this.jdbcTemplate.queryForObject(

«select id, name from t_user where id = ?»,

new Object[]{1L},

new RowMapper<User>() {

public User mapRow(ResultSet rs, int rowNum) throws SQLException {

User user = new User();

user.setName(rs.getString(«name»));

user.setId(rs.getInt(«id»));

return user;

}

});

Если дописать в этот код ещё несколько строк и грамотно составить запрос к SQL (так, чтобы исключить проблему n+1 запроса), то можно получить и список адресов, необходимых пользователю.

Подведём итог всего вышесказанного. Запросы к БД позволяют осуществлять операции выборки данных, их фильтрации, сортировки. Посредством запроса в базу можно делать расчёты, объединять данные из нескольких таблиц, удалять, редактировать, добавлять записи в таблицу. Типов запросов довольно много, и это делает запрос гибким мощным инструментом, подходящим для различных нужд (тип выбирается по назначению запроса).

Оцените статью:
5
Добавить комментарий

Сортировать:
По дате публикации
По рейтингу
  1. Аноним

    Круто

    0
    0
    Ответить
  2. Аноним

    !

    0
    0
    Ответить
Читайте также
prev
next
Бесплатные вебинары:
prev
next
Как работает дизайн-студия на примере одного кейса 

Как работает дизайн-студия на примере одного кейса 

Узнать подробнее
Инновационные подходы к обучению информационным технологиям

Инновационные подходы к обучению информационным технологиям

Узнать подробнее
Как стать Python-разработчиком

Как стать Python-разработчиком

Узнать подробнее
Что нужно знать разработчику

Что нужно знать разработчику

Узнать подробнее
Кто такой тестировщик и как им стать

Кто такой тестировщик и как им стать

Узнать подробнее
Чем занимается программист и как им стать

Чем занимается программист и как им стать

Узнать подробнее
Как искусственный интеллект помогает и мешает задачам кибербезопасности

Как искусственный интеллект помогает и мешает задачам кибербезопасности

Узнать подробнее
Бесплатный вебинар про внедрение искусственного интеллекта

Бесплатный вебинар про внедрение искусственного интеллекта

Узнать подробнее
Какие есть профессии в ИТ

Какие есть профессии в ИТ

Узнать подробнее
Смените профессию,
получите новые навыки,
запустите карьеру
Поможем подобрать обучение:
Забрать подарок

Получите подробную стратегию для новичков на 2023 год, как с нуля выйти на доход 200 000 ₽ за 7 месяцев

Подарки от Geekbrains из закрытой базы:
Осталось 17 мест

Поздравляем!
Вы выиграли 4 курса по IT-профессиям.
Дождитесь звонка нашего менеджера для уточнения деталей

Иван Степанин
Иван Степанин печатает ...