Haluaisin tietää seuraavaa:
Aion käyttää tätä (esimerkiksi - PHP) -sovelluksessani, mutta en halua suorittaa useita kyselyjä tietokantaan, mitä vaihtoehtoja minulla on saada tietoja useista taulukoista yhdellä kyselyllä?
Huomautus: Kirjoitan tämän, koska haluaisin linkittää hyvin kirjoitettuun oppaaseen, joka koskee lukuisia kysymyksiä, joihin törmään jatkuvasti PHP-jonossa, jotta voin linkittää tähän tarkempia tietoja, kun lähetän vastauksen.
Vastaukset kattavat seuraavat asiat:
Tämä vastaus kattaa:
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)
Merkkejä koskeva taulukko yksilöi eri automerkit, joita autopihalla voitaisiin mahdollisesti myydä.
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)
Malli-taulukko kattaa eri autotyypit, on yksinkertaisempaa käyttää eri autotyyppejä kuin varsinaisia automalleja.
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)
Ja lopuksi, kaikkien muiden taulukoiden yhdistämiseksi, taulukko, joka sitoo kaiken yhteen. ID-kenttä on itse asiassa yksilöllinen eränumero, jota käytetään autojen tunnistamiseen.
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)
Näin saamme (toivottavasti) tarpeeksi tietoa, jotta voimme kattaa alla olevat esimerkit erityyppisistä yhdistämisistä, ja myös tarpeeksi tietoa, jotta ne olisivat kannattavia.
Pomo haluaa siis tietää kaikkien omistamiensa urheiluautojen tunnukset.
Tämä on yksinkertainen kahden taulukon yhdistäminen. Meillä on taulukko, joka yksilöi mallin, ja taulukko, jossa on käytettävissä oleva varasto. Kuten näet, taulun "autot" sarakkeessa "malli" olevat tiedot liittyvät taulun "autot" sarakkeeseen "mallit". Nyt tiedämme, että mallit-taulussa on Sports
-taulukon tunnus 1
, joten kirjoitetaan liitos.
select
ID,
model
from
cars
join models
on model=ID
Tämä kysely näyttää siis hyvältä, eikö niin? Olemme tunnistaneet kaksi taulukkoa, jotka sisältävät tarvitsemamme tiedot, ja käytämme liitosta, joka tunnistaa oikein, mihin sarakkeisiin liitetään.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
Voi ei! Virhe ensimmäisessä kyselyssämme! Kyllä, ja se on luumu. Kyselyssä on nimittäin oikeat sarakkeet, mutta osa niistä on molemmissa taulukoissa, joten tietokanta menee sekaisin siitä, mitä saraketta tarkoitamme ja missä. Tämän ratkaisemiseksi on kaksi ratkaisua. Ensimmäinen on mukava ja yksinkertainen, voimme käyttää taulunNimi.sarakkeenNimi
kertoaksemme tietokannalle tarkalleen, mitä tarkoitamme, esimerkiksi näin:
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)
Toista vaihtoehtoa käytetään luultavasti useammin, ja sitä kutsutaan nimellä table aliasing. Tämän esimerkin taulukoilla on mukavat ja lyhyet yksinkertaiset nimet, mutta esimerkiksi KPI_DAILY_SALES_BY_DEPARTMENT
-taulukon kirjoittaminen vanhenee luultavasti nopeasti, joten yksinkertaisempi tapa on antaa taulukolle lempinimi näin:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Nyt takaisin pyyntöön. Kuten näet, meillä on tarvitsemamme tiedot, mutta meillä on myös tietoja, joita ei pyydetty, joten meidän on sisällytettävä lausekkeeseen where-lauseke, jotta saamme vain kysytyt urheiluautot. Koska käytän mieluummin taulukon alias-menetelmää kuin taulukon nimiä kerta toisensa jälkeen, noudatan sitä tästä eteenpäin.
On selvää, että kyselyyn on lisättävä where-lauseke. Voimme tunnistaa urheiluautot joko ID=1
tai model='Sports'
perusteella. Koska ID on indeksoitu ja ensisijainen avain (ja koska se on vähemmän kirjoitettu), käytämme sitä kyselyssämme.
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)
Bingo! Pomo on tyytyväinen. Tietenkin, koska hän on pomo eikä koskaan ole tyytyväinen siihen, mitä hän pyysi, hän katsoo tietoja ja sanoo sitten Haluan myös värit.
Okei, meillä on siis suuri osa kyselystämme jo kirjoitettuna, mutta meidän on käytettävä kolmatta taulukkoa, joka on värit. Päätietotaulukkomme cars
tallentaa auton väritunnuksen, ja tämä linkittyy takaisin colors ID -sarakkeeseen. Voimme siis liittää kolmannen taulukon samaan tapaan kuin alkuperäisessä:
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)
Hitto, vaikka taulukko liitettiin oikein ja siihen liittyvät sarakkeet linkitettiin, unohdimme vetää varsinaiset tiedot uudesta taulukosta, jonka juuri linkitimme.
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)
No niin, pomo on nyt hetkeksi poissa pelistä. Nyt selitän tämän hieman yksityiskohtaisemmin. Kuten näet, lausekkeemme from
-lauseke linkittää päätaulukkomme (käytän usein taulukkoa, joka sisältää tietoa, enkä haku- tai dimensiotaulukkoa. Kysely toimisi aivan yhtä hyvin, jos taulukot olisi vaihdettu, mutta siinä ei olisi yhtä paljon järkeä, kun palaamme takaisin tähän kyselyyn lukemaan sitä muutaman kuukauden kuluttua, joten usein on parasta yrittää kirjoittaa kysely, joka on mukava ja helppo ymmärtää - aseta se intuitiivisesti ja käytä hienoja sisennyksiä, jotta kaikki on mahdollisimman selkeää. Jos jatkat muiden opettamista, yritä sisällyttää nämä ominaisuudet heidän kyselyihinsä - varsinkin jos olet itse korjaamassa vikoja.
On täysin mahdollista linkittää yhä useampia taulukoita tällä tavalla.
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
Vaikka unohdin sisällyttää taulukon, jossa saatetaan haluta yhdistää useampi kuin yksi sarake join
-lausekkeeseen, tässä on esimerkki. Jos models
-taulussa olisi merkkikohtaisia malleja ja siten myös sarake nimeltä brand
, joka linkittyisi takaisin brands
-tauluun ID
-kentällä, se voitaisiin tehdä näin:
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
Yllä oleva kysely ei ainoastaan yhdistä yhdistettyjä taulukoita päätaulukkoon cars
, vaan se myös määrittää jo yhdistettyjen taulukoiden väliset yhteydet. Jos tätä ei tehtäisi, tulosta kutsuttaisiin kartesiittiseksi liitokseksi, joka on dba-kielellä huono. Cartesian join on sellainen, jossa rivit palautetaan, koska tiedot eivät kerro tietokannalle, miten tuloksia rajoitetaan, joten kysely palauttaa kaikki kriteerit täyttävät rivit.
Esimerkkinä kartesiittisesta liitoksesta voidaan suorittaa seuraava kysely:
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)
Hyvä luoja, miten rumaa. Tietokannan kannalta se on kuitenkin täsmälleen sitä, mitä pyydettiin. Kyselyssä pyysimme ID
:tä autoista
ja mallia
malleista
. Koska emme kuitenkaan määritelleet miten taulukot yhdistetään, tietokanta on yhdistänyt kaikkia ensimmäisen taulukon rivejä kaikkiin toisen taulukon riveihin.
Okei, pomo on palannut, ja hän haluaa taas lisää tietoa. Haluan saman luettelon, mutta siihen on sisällytettävä myös nelivetoautot.
Tämä antaa meille kuitenkin loistavan tekosyyn tarkastella kahta eri tapaa toteuttaa tämä. Voisimme lisätä toisen ehdon where-lausekkeeseen seuraavasti:
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
Vaikka edellä mainittu toimii täysin hyvin, tarkastellaan asiaa eri tavalla, tämä on hyvä tekosyy näyttää, miten union
-kysely toimii.
Tiedämme, että seuraava palauttaa kaikki urheiluautot:
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
Ja seuraava palauttaisi kaikki nelivetoautot:
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
Lisäämällä niiden väliin union all
-lausekkeen, toisen kyselyn tulokset liitetään ensimmäisen kyselyn tuloksiin.
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)
Kuten näet, ensimmäisen kyselyn tulokset palautetaan ensin ja sitten toisen kyselyn tulokset.
Tässä esimerkissä olisi tietysti ollut paljon helpompaa käyttää vain ensimmäistä kyselyä, mutta union
-kyselyt voivat olla loistavia erityistapauksissa. Ne ovat loistava tapa palauttaa tiettyjä tuloksia taulukoista, joita ei ole helppo yhdistää toisiinsa - tai muuten täysin toisistaan riippumattomista taulukoista. On kuitenkin noudatettava muutamia sääntöjä.
union
:n ja union all
:n käytön välillä. union
-kysely poistaa kaksoiskappaleet, kun taas union all
ei. Tämä tarkoittaa, että suorituskyky kärsii hieman, kun käytetään union
:ia union all
:iin verrattuna, mutta tulokset voivat olla sen arvoisia - en kuitenkaan spekuloi tuollaisilla asioilla tässä.
Tässä yhteydessä kannattaa ehkä huomioida joitakin lisähuomautuksia.order by
, mutta et voi enää käyttää aliasta. Yllä olevassa kyselyssä order by a.ID
:n liittäminen johtaisi virheeseen - tulosten osalta sarakkeen nimi on ID
eikä a.ID
- vaikka molemmissa kyselyissä on käytetty samaa aliasta.order by
-lause, ja sen on oltava viimeinen lauseke.
Seuraavissa esimerkeissä lisään taulukkoihin muutamia ylimääräisiä rivejä.
Olen lisännyt Holden
taulukkoon brands.
Olen myös lisännyt cars
-taulukkoon rivin, jonka color
-arvo on 12
- jolla ei ole viittausta colors-taulukossa.
Okei, pomo on taas täällä ja haukkuu pyyntöjä - *Tahdon laskennan jokaisesta tuotemerkistä, jota meillä on, ja niiden autojen lukumäärän!` - Tyypillistä, olemme juuri päässeet mielenkiintoiseen kohtaan keskustelussamme ja pomo haluaa lisää työtä.
Selvä, meidän on siis ensin hankittava täydellinen luettelo mahdollisista merkeistä.select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Kun yhdistämme tämän autoja koskevaan taulukkoon, saamme seuraavan tuloksen:
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)
Tämä on tietysti ongelma - emme näe mitään mainintaa lisäämästäni ihanasta Holden
-merkistä.
Tämä johtuu siitä, että liitos etsii kummastakin taulukosta yhteensopivia rivejä. Koska autoissa ei ole tietoja, jotka ovat tyyppiä Holden
, niitä ei palauteta. Tässä tilanteessa voimme käyttää ulkoista
liitosta. Tämä palauttaa kaikki tulokset toisesta taulukosta riippumatta siitä, onko ne täsmäävät toisessa taulukossa vai ei:
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)
Nyt kun tämä on selvillä, voimme lisätä ihanan aggregaattifunktion, jolla saamme laskennan ja saamme pomon hetkeksi pois niskastamme.
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)
Ja näin pomo lähtee pois. Selittääkseni tämän hieman tarkemmin, ulommat liitokset voivat olla tyyppiä "vasen" tai "oikea". Vasen tai oikea määrittelee, mikä taulukko on täysin mukana. Vasemmanpuoleinen ulompi liitos sisältää kaikki vasemmanpuoleisen taulukon rivit, kun taas (arvasit sen) oikeanpuoleinen ulompi liitos tuo kaikki oikeanpuoleisen taulukon tulokset tuloksiin. Jotkin tietokannat sallivat täyden ulomman liitoksen, joka tuo tulokset (riippumatta siitä, ovatko ne yhteensopivia vai eivät) kummastakin taulukosta, mutta tämä ei ole tuettu kaikissa tietokannoissa. Luultavasti mietit tässä vaiheessa, voiko kyselyssä yhdistää join-tyyppejä - ja vastaus on kyllä, ehdottomasti voi.
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)
Miksi tämä ei siis tuota odotettua tulosta? Se johtuu siitä, että vaikka olemme valinneet ulomman liitoksen autoista tuotemerkkeihin, sitä ei ollut määritetty liitoksessa väreihin - joten kyseinen liitos tuo takaisin vain tulokset, jotka vastaavat molempia taulukoita. Tässä on kysely, joka toimisi odotettujen tulosten saamiseksi:
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)
Kuten näemme, kyselyssä on kaksi ulompaa joinia, ja tulokset tulevat odotetusti.
Kysytkö nyt, entäpä ne muunlaiset joinit? Entä Intersections?
Kaikki tietokannat eivät tue "risteystä", mutta lähes kaikki tietokannat sallivat risteyksen luomisen liitoksen avulla (tai ainakin hyvin jäsennellyn where-lauseen avulla).
Risteymä on liittymätyyppi, joka muistuttaa jonkin verran edellä kuvattua unionia
, mutta erona on se, että se palauttaa vain tietorivit, jotka ovat identtisiä (ja tarkoitan identtisiä) eri yksittäisten kyselyjen välillä, jotka on yhdistetty unionilla. Vain kaikilta osin identtiset rivit palautetaan.
Yksinkertainen esimerkki olisi seuraava:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
Vaikka tavallinen union
-kysely palauttaisi kaikki taulukon rivit (ensimmäinen kysely palauttaisi kaikki yli ID>2
ja toinen kaikki ID<4
), mikä johtaisi täydelliseen joukkoon, intersect-kysely palauttaisi vain rivin, joka vastaa id=3
, koska se täyttää molemmat kriteerit.
Jos tietokantasi ei tue intersect
-kyselyä, edellä mainittu voidaan helposti toteuttaa seuraavalla kyselyllä:
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)
Jos haluat suorittaa leikkauksen kahden eri taulukon välillä käyttäen tietokantaa, joka ei luonnostaan tue leikkauskyselyä, sinun on luotava liitos taulukoiden jokaiselle sarakkeelle.
Okei, nyt pomo on taas rynnistänyt sisään - Tahdon listan kaikista autoista, joissa on merkki ja kuinka monta kyseistä merkkiä meillä on yhteensä! Tämä on loistava tilaisuus käyttää seuraavaa kikkaa SQL:n herkkupussissa - alakyselyä. Jos termi ei ole sinulle tuttu, alakysely on kysely, joka suoritetaan toisen kyselyn sisällä. Niitä voidaan käyttää monin eri tavoin. Laaditaan ensin yksinkertainen kysely, jossa luetellaan jokainen auto ja sen merkki:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
Jos haluaisimme yksinkertaisesti saada autojen lukumäärän lajiteltuna merkin mukaan, voisimme tietysti kirjoittaa näin:
select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand
+--------+-----------+
| brand | countCars |
+--------+-----------+
| BMW | 2 |
| Ford | 2 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+-----------+
Meidän pitäisi siis pystyä yksinkertaisesti lisäämään count-funktio alkuperäiseen kyselyymme, eikö niin?
select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 1 |
| 2 | Ford | 1 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 1 |
| 6 | BMW | 1 |
| 7 | Ford | 1 |
| 8 | Toyota | 1 |
| 9 | Toyota | 1 |
| 10 | BMW | 1 |
| 11 | Toyota | 1 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Valitettavasti emme voi tehdä sitä. Syynä on se, että kun lisäämme auton tunnuksen (sarake a.ID), meidän on lisättävä se ryhmittelyyn - joten nyt, kun laskentatoiminto toimii, jokaista tunnusta kohden on vain yksi vastaavuus.
Tässä voimme kuitenkin käyttää alikyselyä - itse asiassa voimme tehdä kaksi täysin erityyppistä alikyselyä, jotka palauttavat samat tulokset, joita tarvitsemme tätä varten. Ensimmäinen on yksinkertaisesti laittaa alakysely select
-lausekkeeseen. Tämä tarkoittaa, että aina kun saamme tietorivin, alakysely lähtee hakemaan sarakkeen tietoja ja lisää ne sitten tietorivillemme.
select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 2 | Ford | 2 |
| 7 | Ford | 2 |
| 1 | Toyota | 5 |
| 5 | Toyota | 5 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 11 | Toyota | 5 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 6 | BMW | 2 |
| 10 | BMW | 2 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Ja bam!, tämä riittää meille. Jos kuitenkin huomasit, tämä alikysely on suoritettava jokaiselle palauttamallemme tietoriville. Jopa tässä pienessä esimerkissä meillä on vain viisi eri automerkkiä, mutta alakysely suoritettiin yksitoista kertaa, koska meillä on yksitoista tietoriviä, jotka palautamme. Tässä tapauksessa se ei siis vaikuta tehokkaimmalta tavalta kirjoittaa koodia. Toisenlainen lähestymistapa on, että suoritetaan alakysely ja kuvitellaan, että se on taulukko:
select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 5 |
| 2 | Ford | 2 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 5 |
| 6 | BMW | 2 |
| 7 | Ford | 2 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 10 | BMW | 2 |
| 11 | Toyota | 5 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Okei, meillä on siis samat tulokset (järjestettynä hieman eri tavalla - näyttää siltä, että tietokanta halusi palauttaa tulokset tällä kertaa ensimmäisen valitsemamme sarakkeen mukaan järjestettyinä) - mutta samat oikeat numerot.
Mitä eroa näillä kahdella on - ja milloin meidän pitäisi käyttää kumpaakin alakyselytyyppiä? Varmista ensin, että ymmärrämme, miten tuo toinen kysely toimii. Valitsimme kyselymme from
-lausekkeessa kaksi taulukkoa ja kirjoitimme sitten kyselyn ja kerroimme tietokannalle, että se oli itse asiassa taulukko sen sijaan - mihin tietokanta on täysin tyytyväinen. Tämän menetelmän käyttämisessä voi olla joitakin etuja (ja myös joitakin rajoituksia). Tärkeintä on, että tämä alakysely suoritetaan kerran. Jos tietokantamme sisältäisi suuren määrän tietoja, tämä voisi hyvinkin olla valtava parannus ensimmäiseen menetelmään verrattuna. Koska kuitenkin käytämme tätä taulukkona, meidän on tuotava ylimääräisiä tietorivejä, jotta ne voidaan yhdistää takaisin tietoriveihimme. Meidän on myös varmistettava, että tietorivejä on todella paljon, jos aiomme käyttää yksinkertaista yhdistämistä, kuten yllä olevassa kyselyssä. Jos muistat, liitos vetää takaisin vain rivejä, joiden tiedot ovat samat liitoksen kummallakin puolella. Jos emme ole varovaisia, tämä voi johtaa siihen, että autoja koskevasta taulukosta ei palauteta kelvollisia tietoja, jos tässä alakyselyssä ei ole vastaavaa riviä.
Kun tarkastelemme ensimmäistä alakyselyä, myös siinä on joitakin rajoituksia. koska vedämme tiedot takaisin yhdelle riville, voimme VARMAASTI vetää takaisin yhden rivin tietoja. Tiedustelun select
-lausekkeessa käytetyt alakyselyt käyttävät hyvin usein vain aggregaattifunktiota, kuten sum
, count
, max
tai muuta vastaavaa aggregaattifunktiota. Niiden ei pitäisi olla, mutta usein ne kirjoitetaan juuri näin.
Ennen kuin siirrymme eteenpäin, katsotaanpa nopeasti, missä muualla voimme käyttää alakyselyä. Voimme käyttää sitä where
-lausekkeessa - tämä esimerkki on hieman keksitty, sillä tietokannassamme on parempia tapoja saada seuraavat tiedot, mutta koska kyseessä on vain esimerkki, katsotaanpa sitä:
select
ID,
brand
from
brands
where
brand like '%o%'
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)
Tämä palauttaa meille luettelon tuotemerkkien tunnuksista ja tuotemerkkien nimistä (toinen sarake on lisätty vain näyttämään tuotemerkit), joiden nimessä on kirjain o
.
Nyt voisimme käyttää tämän kyselyn tuloksia where-lausekkeessa seuraavasti:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like '%o%'
)
+----+--------+
| ID | brand |
+----+--------+
| 2 | Ford |
| 7 | Ford |
| 1 | Toyota |
| 5 | Toyota |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Kuten näet, vaikka alakysely palautti kolme merkkitunnusta, autoja-taulukossamme oli merkintöjä vain kahdelle niistä. Tässä tapauksessa tarkemmin sanottuna alakysely toimii ikään kuin olisimme kirjoittaneet seuraavan koodin:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)
+----+--------+
| ID | brand |
+----+--------+
| 1 | Toyota |
| 2 | Ford |
| 5 | Toyota |
| 7 | Ford |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Jälleen voit nähdä, miten alakysely vs. manuaaliset syötteet on muuttanut rivien järjestystä tietokannasta palautettaessa. Kun keskustelemme alikyselyistä, katsotaanpa, mitä muuta voimme tehdä alikyselyllä:
select
-lausekkeeseen, muutaman from
-lausekkeeseen ja pari muuta where
-lausekkeeseen - muista vain, että jokainen lisäämäsi alakysely tekee kyselystäsi monimutkaisemman ja sen suorittaminen kestää todennäköisesti kauemmin.
Jos sinun on kirjoitettava tehokasta koodia, voi olla hyödyllistä kirjoittaa kysely useilla eri tavoilla ja katsoa (joko ajoittamalla se tai käyttämällä selityssuunnitelmaa), mikä on optimaalinen kysely tulosten saamiseksi. Ensimmäinen toimiva tapa ei välttämättä ole aina paras tapa.Ajattelin lisätä joitakin ylimääräisiä osia, vinkkejä ja temppuja, jotka ovat tulleet esiin.
Yksi kysymys näen tulla melko vähän, on Miten saan ei-matching rivejä kahdesta taulukosta ja näen vastauksen yleisimmin hyväksytty jotain seuraavaa (perustuu meidän autot ja tuotemerkit taulukko - joka on Holden lueteltu tuotemerkkinä, mutta ei näy autojen taulukossa):
select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)
Ja Kyllä se toimii.
+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)
Se ei kuitenkaan ole ei tehokas joissakin tietokannoissa. Tässä on linkki Stack Overflow -kysymykseen, jossa kysytään siitä, ja tässä on erinomainen perusteellinen artikkeli, jos haluat perehtyä asiaan.
Lyhyt vastaus on, että jos optimoija ei käsittele sitä tehokkaasti, voi olla paljon parempi käyttää seuraavan kaltaista kyselyä, jolla saadaan sopimattomat rivit:
select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null
+--------+
| brand |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)
Ahhh, toinen vanha mutta hyvä - vanha Et voi määrittää kohdetaulukkoa 'tuotemerkit' päivitystä varten FROM-lausekkeessa.
MySQL ei anna sinun suorittaa päivitä...
-kyselyä, jossa on samaan taulukkoon kohdistuva alivalinta. Nyt saatat ajatella, että miksi et vain laita sitä where-lausekkeeseen? Mutta entä jos haluat päivittää vain rivin, jolla on max()
-päivämäärä, joukon muiden rivien joukossa? Sitä ei voi tehdä where-lausekkeessa.
update
brands
set
brand='Holden'
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can't specify target table 'brands'
for update in FROM clause
Emme siis voi tehdä sitä, vai mitä? No, ei aivan. On olemassa ovela kiertotapa, josta yllättävän moni käyttäjä ei tiedä - vaikka se sisältääkin jonkin verran hakkerointia, johon sinun on kiinnitettävä huomiota.
Voit liittää alikyselyn toisen alikyselyn sisälle, jolloin kahden kyselyn väliin jää riittävästi tilaa, jotta se toimii. Huomaa kuitenkin, että on ehkä turvallisinta liittää kysely transaktion sisään - tämä estää muiden muutosten tekemisen taulukoihin kyselyn suorittamisen aikana.
update
brands
set
brand='Holden'
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0