Псевдоними на колони и на таблици:

Често ни се налага да даваме по-описателни имена на колоните на резултатните таблици, особено ако са обединения от две таблици и в двете например има колона name.

В такъв случай можем да „прекръстим“ колоните от резултатната таблица с други имена, с които да ги различаваме. От гледна точка на програмиста това как ще се казва дадена колона няма никакво значение. Но преименуването внася определена подреденост в крайния резултат. Понякога се налага да се преименува дори цяла таблица с цел да се избегне дълго име на таблицата и т.н. Можете да преименувате дори резултат от селект заявка. Всичко това става като се използва ключовата дума AS и се зададе новото име на колоната/таблицата.

Нека разгледаме следната заявка:

SELECT coaches.name,sports.name
from coaches JOIN sports
ON coaches.id IN(
SELECT coach_id
FROM sportgroups
WHERE sportgroups.sport_id = sports.id);

Тя връща като резултат следното:

Тук се вижда, че и двете колони се казват по един и същи начин. Можем да им зададем псевдоними.

SELECT coaches.name as CoachName,sports.name as Sport
from coaches JOIN sports
ON coaches.id IN(
SELECT coach_id
FROM sportgroups
WHERE sportgroups.sport_id = sports.id);

И резултатът е доста по-четим:

Забележка: Псевдонимите важат само по време на изпълнение на заявката и не влияят върху истинските имена на колоните/таблиците.

Нека дадем псевдоними и на колоните, и на таблиците:

SELECT sg.location as locationOfGroup,
sg.dayOfWeek as trainingDay,
sg.hourOfTraining as trainingHour,
sp.name as sportName
FROM sportgroups as sg JOIN sports as sp
ON sg.sport_id = sp.id;

Резултатът е:

Важно: Ако преименуваме съществуваща таблица във FROM, то навсякъде(в рамките на заявката) трябва задължително да се обръщаме към нея чрез нейния псевдоним.

SELECT sofiaSg.sg_id as SportGroupNumber,
       sp.name as Sport,
       sofiaSg.loc as SportsGroupLocation
FROM sports as sp JOIN  (SELECT id as sg_id,
								location as loc,
								sport_id
						FROM sportgroups
						WHERE location LIKE '%Sofia%'
						) as sofiaSg     /**sportgroups from Sofia**/
ON sp.id = sofiaSg.sport_id;

Тук правим JOIN на всички спортове със спортни групи само от София (НЕ с всички спортни групи) и извеждаме като резултат номер на група(id), име на спорта и локация на групата. Дали сме псевдоним sofiaSg на резултата от селект заявката, която извежда всички групи от София.

SELF JOIN:

Класически пример за SELF JOIN  е, когато имаме self-referencing table. Това е такава таблица, която има външен ключ към колона от самата нея. Пример: Нека имаме програмисти. Някои от програмистите са тийм-лидери, други са си просто програмисти, но си имат тийм лидери. Сещате се, че би било безсмислено да пазим на две места информация за програмистите и за тийм лидерите. Това е перфектен пример за самореференцираща се таблица. Нека създадем таблицата и да вмъкнем данни:

use school_sport_clubs;

drop table if exists programmers;
CREATE TABLE programmers(
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    startWorkingDate DATE, /** YYYY-MM-DD**/
    teamLead_id INT NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
);




INSERT INTO `programmers` (`name`, `address`, `startWorkingDate`, `teamLead_id`) 
VALUES ('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
('Georgi Petkov Todorov', 'Bulgaria- Sofia Nadezhda, bl. 35', '2002-12-01', '1'),
('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
('Iliya Stoynov Todorov', 'Sofia - Nadezhda, bl. 28', '2000-02-01', 5),
('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);

Сега нека изведем списък с всички програмисти и до тях имената на техните тийм лидери: Ще трябва да направим JOIN на таблицата programmers със същата таблица programmers.

SELECT progr.name as ProgrammerName, progr.address as ProgrammerAddres, teamLeads.name as TeamLeadName
FROM programmers as progr JOIN programmers as teamLeads
WHERE progr.teamLead_id = teamLeads.id;

Ето и резултата:

Как да излязат всички програмисти, дори и самите тийм лидери? Като направим LEFT JOIN:

SELECT progr.name as ProgrammerName, progr.address as ProgrammerAddres, teamLeads.name as TeamLeadName
FROM programmers as progr LEFT JOIN programmers as teamLeads
ON progr.teamLead_id = teamLeads.id;

Нека да разгледаме и друг случай на употреба на SELF JOIN:

Използваме познатата база за спортни клубове. Искаме да сформираме двойки ученици на базата на спортна група, в която тренират, като двойките не се повтарят. Например Иван Петков, Петко Иванов и Мария Иванова тренират в групата по футбол. Тогава ще ни трябват двойките:

Иван Петков – Петко Иванов;

Иван Петков – Мария Иванова;

Петко Иванов – Мария Иванова.

Ако обаче първата двойка тренира и в друга група, то тогава съвсем въжможно е тя да бъде дублирана – един път от групата по футбол и един път от групата по волейбол например. Имената на тези ученици се пазят в таблицата students. За да разберем обаче дали тренират в една група, ще трябва да потърсим кой в коя група е и да съединим двете едни и същи таблици (students) на базата на това кой в коя група тренира.

USE school_sport_clubs;
SELECT firstpl.name as firstPlayer, secondpl.name as secondPlayer, sports.name as sportName
FROM students as firstpl JOIN students as secondpl
ON firstpl.id > secondpl.id 
JOIN sports ON (
		secondpl.id IN(
		SELECT student_id 
                FROM student_sport
		WHERE sportGroup_id IN(
		SELECT id 
                FROM sportgroups
		WHERE sport_id = sports.id
		) 
		)	
AND (firstPl.id IN( SELECT student_id 
FROM student_sport
WHERE sportGroup_id IN(
SELECT id 
FROM sportgroups							WHERE sport_id = sports.id							) 
				)
    )
)
WHERE firstPL.id IN(
	  SELECT student_id
	  FROM student_sport
	  WHERE sportGroup_id IN(
			SELECT sportGroup_id
			FROM student_sport
			WHERE student_id = secondPl.id
		)
)
ORDER BY sportName;

Order by – подрежда резултата по азбучен ред на текстовата колона sportName.
Резултат:

programmersDbScript

REcreateDbScript

Даниел Джолев