Upotreba drugih imena za tabele-alijasi (The use of other names in the table-aliases)

Privremeno nazvati kolonu drugim imenom su alijasi. Alijase mozete da zadate na pocetku teksta upita i da ih potom koristite u preostalom delu upita. Alijasi su cesto korisni i kao skracena imena. 

Upit iz prethodnog clanka se moze napisati

select k.ime 
from kupci as k, porudzbine as p, stvari_porudzbina as sp, 
motori as m
where k.sifrakupca = p.sifrakupca
and p.brojporudzbine = sp.brojporudzbine
and sp.brojmotora = m.brojmotora

and m.imemotora like '%KTM%';

Ovaj fajl sacuvajte kao alijasi.sql.

Objasnjenje:
U delu upita koji sadrzi deklaracije tabela koje nameravte da koristite, treba da dodate odredbu AS iza imena svake tabele za koju deklarisete alijas. 

Ako zelite da spojite tabelu samu sa sobom upotrebite alijase. Ovakvo spajanje korisno je ako zelite da nadjete sve redove u tabeli koji sadrze istu vrednost. Na primer, ako hocete da nadjete sve kupce koji zive u istom gradu - za istu tabelu (kupci) zadajte dva alijasa.

Primer 1:

select k1.ime, k2.ime, k1.grad
from kupci as k1, kupci as k2
where k1.grad = k2.grad
and k1.ime != k2.ime;

Objasnjenje:

Upit smo sastavili kao da umesto jedne tabele kupci, imamo dve razlicite tabele, c1 i c2, koje spajamo preko kolone grad. Ali nam je bio potreban jos jedan uslov k1.ime != k2.ime, da bismo iskljucili redove u kojima je kupac povezan sam sa sobom. 


Primer 2:

insert into kupci values

  (NULL, "Paja Pajic", "Pajina 99", "Beograd");

Dodali smo ovaj kod zato sto do sada svi nasi kupci su iz razlicitih gradova a za ovaj primer nam treba vise kupaca koji su iz istog grada.

1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.

3. Ubacite prvi kod, s pocetka ovog clanka i kliknite dugme "Go".

 4. Klik na karticu SQL, ubacite drugi kod i kliknite dugme "Go".

5. Klik na karticu SQL, ubacite kod iz primera 2 i kliknite dugme "Go".

6. Klik na karticu SQL, ponovo ubacite kod iz primera 1 i kliknite dugme "Go".

Sledeci clanak - Tipovi spoje u MySQL-u.

Temporarily called the column a different name are aliases. Aliases you can specify at the beginning of the text of the query and then use them in the rest of the query. Aliases are often useful as a shorthand name.

Query from the previous article can be written

SELECT c.name 

FROM customers AS c, orders as o, orders_items AS oi, motobikes AS m

WHERE c.customerid = o.customerid

AND o.ordersid = oi.ordersid

AND oi.motobikeid = m.motobikeid

AND m.motobike_name LIKE '%KTM%';

This file saved as aliases.sql.

Explanation:

In the wuery part, that contains tables declaration that you plan to use, you need to add a clause AS after the name of each table for which you declare an alias.

If you want to connect the table with itself use aliases. This connection is useful if you want to find all the rows in the table containing the same value. For example, if you want to find all customers who live in the same city - in the same table (customers), specify two aliases.

Example 1:

SELECT c1.name, c2.name, c1.city

FROM customers AS c1, customers AS c2

WHERE c1.city = c2.city

AND c1.name != c2.name;

Explanation:

Query was put together as if instead of a single table customers, we have two different tables, c1 and c2, that are connected via column city. But we needed another condition c1.name != c2.name, to exclude the rows in which the customer is connected to self.

Example 2:

INSERT INTO customers VALUES

  (NULL, "Paja Pajic", "Pajiana 99", "Beograd");

We've added this code because so far all of our customers are from different cities and for this example we need more customers who are from the same city.

1. Start XAMPP. Click on the Admin in line MySQL or type in the address bar 

http://localhost/phpmyadmin/

2. From the left select the database lovebikes, and on the right select the SQL tab.

3. Insert the first code, from the beginning of this article and click the "Go".

4. Click on the SQL tab, insert another code and click "Go".

5. Click on the SQL tab, insert the code from examples 2 and click "Go".

6. Click on the SQL tab, reinsert code in the examples 1 and click "Go".

.Next article - Types of joins in MySQL.