Ответ 1
Часть 1 - Соединения и союзы
Этот ответ охватывает:
- Часть 1
- Объединение двух или более таблиц с использованием внутреннего соединения (см. запись в википедии для дополнительной информации)
- Как использовать запрос объединения
- Левое и правое внешние соединения (этот fooobar.com/questions/19258/... отлично описывает типы объединений)
- Запросы Intersect (и способы их воспроизведения, если ваша база данных не поддерживает их) - это функция SQL-Server (см. информацию) и часть причина, по которой я написал всю эту вещь.
- Часть 2
- Подзапросы - то, что они есть, где они могут использоваться и что нужно отслеживать
- Декартовы объединения AKA - О, страдание!
Существует несколько способов извлечения данных из нескольких таблиц в базе данных. В этом ответе я буду использовать синтаксис объединения ANSI-92. Это может отличаться от ряда других обучающих программ, которые используют более старый синтаксис ANSI-89 (и если вы привыкли к 89, может показаться гораздо менее интуитивным), но все, что я могу сказать, это попробовать), поскольку это намного проще чтобы понять, когда запросы начинают усложняться. Зачем использовать его? Есть ли выигрыш в производительности? короткий ответ - нет, но его легче читать, как только вы привыкнете к нему. Легче читать запросы, написанные другими людьми, используя этот синтаксис.
Я также собираюсь использовать концепцию небольшого caryard, в котором есть база данных, чтобы отслеживать, какие автомобили доступны. Владелец нанял вас в качестве своего компьютерного компьютера и ожидает, что вы сможете бросить ему данные, которые он просит при капле шляпы.
Я сделал несколько таблиц поиска, которые будут использоваться финальной таблицей. Это даст нам разумную модель для работы. Чтобы начать, я буду запускать свои запросы против базы данных примеров, которая имеет следующую структуру. Я попытаюсь подумать о распространенных ошибках, которые возникают при запуске и объяснении того, что с ними не так, - и, конечно же, показывает, как их исправить.
Первая таблица - это просто список цветов, чтобы мы знали, какие цвета у нас есть на автомобильном дворе.
mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)
В таблице брендов указаны различные марки автомобилей, из которых может продаваться caryard.
mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)
Таблица моделей будет охватывать разные типы автомобилей, для этого будет проще использовать разные типы автомобилей, а не фактические модели автомобилей.
mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)
И наконец, чтобы связать все эти другие таблицы, таблицу, которая связывает все вместе. Поле идентификатора - фактически уникальный номер партии, используемый для идентификации автомобилей.
mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)
Это даст нам достаточное количество данных (я надеюсь), чтобы скрыть приведенные ниже примеры разных типов объединений, а также предоставить достаточное количество данных, чтобы сделать их полезными.
Таким образом, попадая в это зерно, босс хочет знать идентификаторы всех спортивных автомобилей, которые у него есть.
Это простое объединение двух таблиц. У нас есть таблица, которая идентифицирует модель и таблицу с доступным запасом в ней. Как вы можете видеть, данные в столбце model таблицы cars относятся к столбцу models таблицы cars, которую мы имеем. Теперь мы знаем, что таблица моделей имеет идентификатор 1 для Sports, поэтому давайте напишем соединение.
select
ID,
model
from
cars
join models
on model=ID
Итак, этот запрос выглядит хорошо? Мы идентифицировали две таблицы и содержали необходимую информацию и использовали соединение, которое правильно идентифицирует, к каким столбцам присоединиться.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
Ой! Ошибка в нашем первом запросе! Да, и это слива. Вы видите, что запрос действительно получил правильные столбцы, но некоторые из них существуют в обеих таблицах, поэтому база данных путается о том, что означает фактический столбец и где. Для решения этой проблемы есть два решения. Первое хорошо и просто, мы можем использовать tableName.columnName, чтобы сообщить базе данных точно, что мы имеем в виду, например:
select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)
Другой, вероятно, чаще используется и называется наложением таблиц. Таблицы в этом примере имеют приятные и короткие простые имена, но написание чего-то типа KPI_DAILY_SALES_BY_DEPARTMENT, вероятно, быстро стареет, поэтому простой способ - прозвать таблицу следующим образом:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Теперь вернемся к запросу. Как вы можете видеть, у нас есть информация, которая нам нужна, но у нас также есть информация, которая не запрашивалась, поэтому нам нужно включить предложение where в выражение, чтобы получить только спортивные автомобили, как было задано. Поскольку я предпочитаю метод псевдонима таблиц вместо использования имен таблиц снова и снова, я буду придерживаться его с этого момента.
Очевидно, нам нужно добавить предложение where к нашему запросу. Мы можем идентифицировать спортивные автомобили либо ID=1, либо model='Sports'. По мере индексирования идентификатора и первичного ключа (а это, как правило, меньше ввода), используйте его в нашем запросе.
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Бинго! Босс счастлив. Конечно, будучи боссом и никогда не доволен тем, что он просил, он просматривает информацию, затем говорит, что мне нужны и цвета.
Хорошо, поэтому у нас есть большая часть нашего уже написанного запроса, но нам нужно использовать третью таблицу, которая является цветами. Теперь в нашей основной информационной таблице cars хранится идентификатор цвета автомобиля, и это связано с столбцом идентификаторов цветов. Итак, аналогично оригиналу, мы можем присоединиться к третьей таблице:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Черт, хотя таблица была правильно соединена и связанные столбцы были связаны, мы забыли извлечь фактическую информацию из новой таблицы, которую мы только что связали.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)
Правильно, что босс с нашей спины на мгновение. Теперь, чтобы немного объяснить это. Как вы можете видеть, предложение from в нашем заявлении связывает нашу основную таблицу (я часто использую таблицу, которая содержит информацию, а не таблицу поиска или измерения. Запрос будет работать так же хорошо, как и все таблицы, все переключаемые, но меньше смысла, когда мы возвращаемся к этому запросу, чтобы прочитать его через несколько месяцев, поэтому часто лучше всего попытаться написать запрос, который будет приятным и понятным - заложите его интуитивно, используйте хороший отступ, чтобы все было насколько это возможно. Если вы продолжите учить других, попробуйте привить эти характеристики в своих запросах, особенно если вы будете их устранять.
Вполне возможно сохранить связь между таблицами и таблицами таким образом.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
Хотя я забыл включить таблицу, в которой мы могли бы присоединиться к более чем одному столбцу в инструкции join, вот пример. Если таблица models имела брендовые модели и поэтому также имела столбец с именем brand, который привязан к таблице brands в поле ID, это можно сделать следующим образом:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1
Вы можете видеть, что запрос выше не только связывает объединенные таблицы с основной таблицей cars, но также указывает соединения между уже присоединенными таблицами. Если это не было сделано, результат называется декартовым соединением - это дба говорит плохо. Декартовое объединение - это тот, где строки возвращаются, потому что информация не сообщает базе данных о том, как ограничить результаты, поэтому запрос возвращает все строки, соответствующие критериям.
Итак, чтобы привести пример декартового объединения, запустите следующий запрос:
select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)
Добрый бог, это уродливо. Однако, что касается базы данных, это именно то, о чем просили. В запросе мы запросили для ID от cars и model от models. Однако, поскольку мы не указали, как присоединиться к таблицам, база данных сопоставила каждую строку из первой таблицы с каждой строкой из второй таблицы.
Хорошо, поэтому босс вернулся, и он снова хочет получить дополнительную информацию. Я хочу тот же список, но также включать в него 4WD.
Это, однако, дает нам отличный повод взглянуть на два разных способа достижения этого. Мы могли бы добавить еще одно условие в предложение where, подобное этому:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3
В то время, как выше будет работать отлично, давайте посмотрим на это по-другому, это отличный повод показать, как будет работать запрос union.
Мы знаем, что следующее возвращение всех спортивных автомобилей:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
И следующее вернет все 4WD:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
Итак, добавив предложение union all между ними, результаты второго запроса будут добавлены к результатам первого запроса.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)
Как вы можете видеть, сначала возвращаются результаты первого запроса, а затем результаты второго запроса.
В этом примере было бы, конечно, гораздо проще просто использовать первый запрос, но union запросы могут быть полезны для конкретных случаев. Они - отличный способ вернуть конкретные результаты из таблиц из таблиц, которые нелегко объединяются - или, если на то пошло, абсолютно несвязанные таблицы. Однако есть несколько правил.
- Типы столбцов из первого запроса должны соответствовать типам столбцов из каждого другого запроса ниже.
- Имена столбцов из первого запроса будут использоваться для идентификации всего набора результатов.
- Количество столбцов в каждом запросе должно быть одинаковым.
Теперь вы можете задаваться вопросом, что именноразница заключается в использовании union и union all. Запрос union будет удалять дубликаты, а union all - нет. Это означает, что при использовании union over union all наблюдается небольшое повышение производительности, но результаты могут быть полезны - я не буду спекулировать на таких вещах в этом, хотя.
В этой заметке, возможно, стоит отметить некоторые дополнительные примечания.
- Если мы хотим заказать результаты, мы можем использовать
order by, но вы больше не сможете использовать этот псевдоним. В вышеприведенном запросе добавлениеorder by a.IDприведет к ошибке - насколько это касается результатов, столбец называетсяID, а неa.ID- хотя в обоих запросах использовался один и тот же псевдоним. - У нас может быть только один оператор
order by, и он должен быть последним.
В следующих примерах я добавляю несколько дополнительных строк в наши таблицы.
Я добавил Holden в таблицу брендов.
Я также добавил строку в cars, которая имеет значение color 12 - которое не имеет ссылки в таблице цветов.
Хорошо, босс снова вернулся, лая запросы - * Я хочу подсчет каждой марки, которую мы носим, и количество автомобилей в ней! "- Типично, мы просто добираемся до интересной части нашего обсуждения и босса хочет больше работы.
Rightyo, так что первое, что нам нужно сделать, это получить полный список возможных брендов.
select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Теперь, когда мы присоединяемся к этой таблице наших автомобилей, мы получаем следующий результат:
select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)
Что, конечно, проблема - мы не видим упоминания о прекрасном бренде Holden, который я добавил.
Это связано с тем, что соединение ищет сопоставимые строки в обеих таблицах. Поскольку в автомобилях нет данных типа Holden, он не возвращается. Здесь мы можем использовать соединение outer. Это вернет все результаты из одной таблицы, будут ли они сопоставлены в другой таблице или нет:
select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)
Теперь, когда у нас есть это, мы можем добавить прекрасную совокупную функцию, чтобы получить счет, и на мгновение отпустить босса от спины.
select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)
И с этим, прочь босс скроется.
Теперь, чтобы объяснить это более подробно, внешние соединения могут быть типа left или right. Левая или правая определяет, какая таблица полностью включена. A left outer join будет включать в себя все строки из таблицы слева, а (как вы догадались) a right outer join выводит все результаты из таблицы справа в результаты.
Некоторые базы данных позволят full outer join, которые возвращают результаты (независимо от того, соответствует ли они или нет) из обеих таблиц, но это не поддерживается во всех базах данных.
Теперь, вероятно, я думаю, что в этот момент вам интересно, можете ли вы объединить типы соединений в запросе - и ответ да, вы абсолютно можете.
select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)
Итак, почему это не ожидаемые результаты? Это связано с тем, что, хотя мы выбрали внешнее соединение с автомобилями для брендов, оно не было указано в соединении с цветами - так что конкретное соединение приведет к возврату результатов, которые соответствуют в обеих таблицах.
Вот запрос, который будет работать для получения ожидаемых результатов:
select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)
Как мы видим, у нас есть два внешних объединения в запросе, и результаты идут, как ожидалось.
Теперь, как насчет тех других типов объединений, которые вы задаете? Что относительно пересечений?
Ну, не все базы данных поддерживают intersection, но почти все базы данных позволят вам создать пересечение через объединение (или хорошо структурированный оператор where).
Пересечение - это тип объединения, несколько похожий на a union, как описано выше, но разница в том, что он возвращает только строки данных, которые идентичны (и я имею в виду одинаковые) между различными индивидуальными запросами, союз. Вернутся только строки, идентичные во всех отношениях.
Простой пример будет таким:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
В то время как обычный запрос union возвращает все строки таблицы (первый запрос возвращает что-либо по ID>2, а второе - ID<4), что приведет к полному набору, запрос на пересечение будет только верните строку, соответствующую id=3, поскольку она соответствует обоим критериям.
Теперь, если ваша база данных не поддерживает запрос intersect, вышесказанное может быть легко выполнено со следующим запросом:
select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)
Если вы хотите выполнить перекресток между двумя разными таблицами, используя базу данных, которая по сути не поддерживает запрос пересечения, вам нужно создать соединение в каждом столбце таблиц.