Βάσεις Δεδομένων με την MySQL #3

Μέρος 1o: Theory, ER Diagram & Design the Database
Μέρος 2ο: Install MySQL, Create the Database, Tables & Import the Data
Μέρος 3ο: Basic SQL Queries, Functions, JOINs & VIEWs

Εισαγωγή

Στο προηγούμενο μάθημα είδαμε πώς μπορούμε να φτιάξουμε την βάση δεδομένων μας. Σε αυτό το μάθημα θα δούμε πώς μπορούμε να δούμε και να αναλύσουμε τα δεδομένα από τους πινακές μας αλλά και πώς μπορούμε να ενώσουμε δύο ή περισσότερους πίνακες.

SELECT

Αν θέλουμε να δούμε τα δεδομένα ενός πίνακα χρησιμοποιούμε την εντολή:
SELECT <columns_names> FROM <table_name>

SELECT company_email, job, birth_date, salary_y
FROM employees;

ORDER BY

Μπορούμε να δούμε τα δεδομένα ταξινομημένα με βάση μία στήλη προσθέτοντας την εντολή:
ORDER BY <column_name>, αν θέλουμε να είναι σε φθίνουσα ταξινόμηση προσθέτουμε και την εντολή DESC.

SELECT company_email, job, birth_date, salary_y
FROM employees
ORDER BY salary_y DESC;

LIMIT

Όταν έχουμε μεγάλους πίνακες το να επιστρέφουμε κάθε φορά όλες τις σειρές του είναι κάτι που μπορεί να πάρει αρκετό χρόνο αλλά και να καταναλώσει πολλούς πόρους από την βάση μας. Γι’ αυτό το λόγο μπορούμε να προσθέσουμε την εντολή LIMIT για να επιστρέψουμε μόνο όσες σειρές χρειαζόμαστε.

SELECT company_email, job, birth_date, salary_y
FROM employees
ORDER BY salary_y DESC
LIMIT 5;

WHERE

Μπορούμε αν θέλουμε να επιστρέψουμε μόνο κάποιες σειρές που ικανοποιούν κάποιες συνθήκες. Για παράδειγμα να επιστρέψει μόνο τους εργαζόμενους που δουλεύουν σαν ‘Στέλεχος Marketing’. Για να το κάνουμε αυτό προσθέτουμε την εντολή:
WHERE <column_name> = <condition>

SELECT company_email, job, birth_date, salary_y
FROM employees
WHERE job = 'Στέλεχος Marketing';

Μπορούμε και εδώ μετά το WHERE αν θέλουμε να προσθέσουμε το ORDER BY και το LIMIT.

AND

Ή μπορούμε να επιλέξουμε τους δέκα πιο καλό πληρωμένους εργαζόμενους που δουλεύουν σαν ‘Στέλεχος Marketing’ και είναι γεννηθείς μετά το 1990.

SELECT company_email, job, birth_date, salary_y
FROM employees
WHERE job = 'Στέλεχος Marketing' AND birth_date > '1990-01-01'
ORDER BY salary_y DESC
LIMIT 10;

OR

Ή μπορούμε να επιλέξουμε εργαζόμενους με συγκεκριμένο όνομα για να τους ευχηθούμε χρόνια πολλά για παράδειγμα για την γιορτή τους. Εδώ θα χρησιμοποιήσουμε την εντολή OR η οποία θα μας επιστρέψει τις σειρές στις οποίες ισχύει είτε ο ένας έλεγχος είτε ο άλλος.

SELECT first_name, phone_number
FROM employees
WHERE first_name = 'Αγγελής' OR first_name = 'Άγγελος';

IN

Μπορούμε επίσης να δούμε τους εργαζόμενους που είναι από συγκεκριμένες πόλεις. Αντί να γράφουμε 5-6 φορές την εντολή OR μπορούμε να χρησιμοποιήσουμε την εντολή IN και μέσα σε παρένθεση να βάλουμε τις τιμές που θέλουμε.

SELECT first_name, last_name, job, street_name
FROM employees
WHERE city IN ('Θεσσαλονίκη', 'Αθήνα', 'Λάρισα', 'Βόλος');

BETWEEN

Αν θέλουμε μπορούμε να δούμε και τις τιμές οι οποίες είναι ανάμεσα από δύο τιμές. Για παράδειγμα τους εργαζόμενους με ημερομηνία γέννησης από το 1990 μέχρι το 1999.

SELECT first_name, last_name, birth_date, job
FROM employees
WHERE birth_date BETWEEN '1990-01-01' AND '1999-12-31';

Ένωση στηλών και μετανομασία (CONCAT() … AS)

Όταν φτιάχναμε τις στήλες αναφέραμε ότι κάθε στήλη πρέπει να έχει μόνο μία τιμή αλλά όταν θέλουμε να δούμε τα δεδομένα πολλές φορές θέλουμε πολλές τιμές σε ένα κελί. Όπως για παράδειγμα το πλήρες όνομα ή η διεύθυνση. Για να ενώσουμε δύο ή παραπάνω στήλες χρησιμοποιούμε την συνάρτηση CONCAT() και μέσα στην παρένθεση γράφουμε τις στήλες που θέλουμε να ενώσουμε. Στην συνέχεια προσθέτουμε την εντολή AS και μετά το πώς θέλουμε να ονομάσουμε την νέα στήλη.

Η εντολή SELECT τραβάει τα δεδομένα από τους πίνακες οπότε ότι επεξεργασία κάνουμε μετά θα έχει εφαρμογή μόνο στο πίνακα των αποτελεσμάτων και δεν θα επηρεαστεί καθόλου ο αρχικός μας πίνακας.

SELECT CONCAT(first_name, ' ',last_name) AS Name,
	   CONCAT(street_number, ' ', street_name, ' ', postcode, ' ', city) AS Address
FROM employees;

Ανάμεσα από κάθε στήλη προσθέτουμε και ένα κενό γιατί δεν μπαίνει αυτόματα και αν δεν το βάλουμε τα κείμενα από τις δύο στήλες θα είναι κολλημένα.

Επίσης ένας άλλος χρηστικός λόγος που αποθηκεύουμε μία τιμή σε κάθε κελί ακόμα και αν μπορεί να την χρειαστούμε ενωμένη είναι ότι είναι πολύ πιο εύκολο να ενώσεις 2-3 στήλες πάρα να έχεις μία στήλη και να χρειαστεί μετά να την χωρίσεις.

Αριθμητικές Πράξεις

Με την SQL μπορούμε να κάνουμε όλες τις βασικές μαθηματικές πράξεις (*/+-). Ας δούμε ένα παράδειγμα, ας υποθέσουμε ότι θέλουμε να δώσουμε μία αύξηση 10% και θέλουμε να δούμε πόσο είναι αυτή η αύξηση 10% σε κάθε εργαζόμενο και πόσο θα είναι πλέον ο μισθός. Θα χρειαστούμε δηλαδή δύο νέες στήλες.

SELECT 	CONCAT(first_name, ' ',last_name) AS Name,
		job, salary_y,
        salary_y * 0.1 AS '10%',
        salary_y + salary_y * 0.1 AS Final_Salary
FROM employees;

Μπορούμε να κάνουμε μαθηματικές πράξεις και χωρίς να χρειάζεται να τραβήξουμε δεδομένα από έναν πίνακα.

SELECT 34.57 AS price,
	34.57 * 0.24 AS Tax,
    34.57 + 34.57 * 0.24 AS Final_Price;

ROUND

Η συνάρτηση ROUND() είναι για να στρογγυλοποιήσει έναν αριθμό. Μέσα στην παρένθεση βάζουμε την αριθμό και εάν θέλουμε πόσα δεκαδικά ψηφία να έχει, αν δεν βάλουμε νούμερο τότε by default είναι χωρίς δεκαδικά ψηφία. Ας δούμε πώς μπορούμε να υπολογίσουμε τον μηνιαίο μισθό.

SELECT 	CONCAT(first_name, ' ',last_name) AS Name,
		job, salary_y,
        salary_y / 12 AS monthly_salary,
        ROUND(salary_y / 12) AS rounded_monthly_salary
FROM employees;

DISTINCT

Αν δεν θέλουμε ένας πίνακας να περιέχει επαναλαμβανόμενες σειρές τότε βάζουμε την εντολή DISTINCT πριν γράψουμε τις στήλες.

SELECT DISTINCT city, job
FROM employees;

COUNT

Αν θέλουμε να μετρήσουμε πόσες σειρές μας επιστρέφει ένα ερώτημα χρησιμοποιούμε την συνάρτηση COUNT().
Για παράδειγμα πόσοι εργαζόμενοι μένουν στην Αθήνα.

SELECT COUNT(*) AS employess_from_athens
FROM employees
WHERE city = 'Αθήνα';

MAX

Για να βρούμε την μέγιστη τιμή από μία στήλη. Για παράδειγμα τον μεγαλύτερο μισθό.

SELECT MAX(salary_y) AS max_salary
FROM employees;

MIN

Για να βρούμε την ελάχιστη τιμή από μία στήλη. Για παράδειγμα τον ελάχιστο μισθό.

SELECT MIN(salary_y) AS min_salary
FROM employees;

AVG

Για να βρούμε τον μέσο όρο από μία στήλη. Για παράδειγμα τον μέσο μισθό της εταιρείας.

SELECT  AVG(salary_y) AS avg_salary,
		ROUND(AVG(salary_y)) AS rounded_avg_salary
FROM employees;

SUM

Για να βρούμε το άθροισμα από μία στήλη. Για παράδειγμα το σύνολο των μισθών που δίνει η εταιρεία σε ένα έτος.

SELECT SUM(salary_y) AS 'Total Salaries'
FROM employees;

Μαζί με τις προηγούμενες συναρτήσεις μπορούμε να χρησιμοποιήσουμε και την εντολή DISTINCT μαζί.
Για παράδειγμα σε πόσες πόλεις μένουν οι υπάλληλοί μας.

SELECT COUNT(city)          AS employees_with_cities,
	   COUNT(DISTINCT city) AS number_of_cities
FROM employees;

Μπορούμε επίσης να χρησιμοποιήσουμε παραπάνω από μία συνάρτηση. Για παράδειγμα θέλουμε να δούμε τον αριθμό των εργαζομένων μας, την ημερομηνία γέννησης του νεότερου εργαζόμενού μας, τον ελάχιστο μισθό και τον μέσο όρο των ημερών διακοπών.

SELECT  COUNT(emp_id) AS number_of_employees,
		MAX(birth_date) AS youngest_employee,
		MIN(salary_y) AS min_salary,
		ROUND(AVG(vacation_days)) AS avg_vacation_days
FROM employees;

GROUP BY

Όταν θέλουμε να βρούμε για παράδειγμα τον μέσο μισθό ανάλογα το φύλο ή ανάλογα την δουλειά χρησιμοποιούμε την εντολή GROUP BY. Αυτή η εντολή ομαδοποιεί όλες τις ίδιες τιμές από μία στήλη και μετά μπορούμε να χρησιμοποιήσουμε μία άλλη συνάρτηση για να υπολογίσουμε την μέγιστη τιμή, την μέση τιμή κλπ από μία άλλη στήλη για κάθε ομάδα που δημιουργήσαμε με την εντολή GROUP BY.

SELECT sex, AVG(salary_y) AS avg_salary_per_sex
FROM employees
GROUP BY sex;

Ένα άλλο παράδειγμα. Αν θέλουμε να υπολογίσουμε το σύνολο των μισθών που δίνουμε σε κάθε τμήμα. Θέλουμε να δούμε δηλαδή τα πέντε τμήματα που δίνουμε τα περισσότερα χρήματα σαν εταιρία.

SELECT job, SUM(salary_y) AS salary_per_job
FROM employees
GROUP BY job
ORDER BY salary_per_job DESC
LIMIT 5;

HAVING

Η εντολή HAVING λειτουργεί σαν την εντολή WHERE αλλά ελέγχει τα δεδομένα αφού έχουμε χρησιμοποιήσει την εντολή GROUP BY.

SELECT city, SUM(salary_y) AS salary_per_city
FROM employees
WHERE salary_y < 30000
GROUP BY city
HAVING salary_per_city < 60000
ORDER BY salary_per_city DESC;

Πρώτα γίνεται ο έλεγχος με το WHERE όπου κρατάμε μόνο τα εισοδήματα των εργαζομένων που είναι μικρότερα από 30.000€. Μετά γίνεται το GROUP BY και υπολογίζονται τα εισοδήματα ανά πόλη και τέλος με το HAVING κάνουμε έλεγχο στα εισοδήματα ανά πόλη και κρατάμε αυτά που είναι μικρότερα από 60.000€ ανά πόλη.

Ας δούμε ένα ακόμα παράδειγμα.
Ας υποθέσουμε ότι θέλουμε να δούμε τα τμήματα που έχουν πάνω από 50 άτομα και πόσα άτομα έχει το καθένα από αυτά.

SELECT job, count(*) AS number_of_employees
FROM employees
GROUP BY job
HAVING number_of_employees > 50
ORDER BY number_of_employees DESC;

IF

Την συνάρτηση IF την χρησιμοποιούμε εάν θέλουμε να κάνουμε έναν έλεγχο σε κάθε σειρά και ανάλογα εάν ισχύει ή όχι ο έλεγχος να κάνουμε κάτι διαφορετικό. Ας το δούμε σε ένα παράδειγμα για να το καταλάβουμε καλύτερα. Ας υποθέσουμε ότι θέλουμε να δώσουμε ένα μπόνους στους εργαζόμενούς μας το οποίο όμως θα είναι ανάλογα με την ηλικία τους. Εάν είναι πάνω από 50 θα πάρουν ένα μπόνους 10% του μισθού τους αλλίως θα πάρουν ένα μπόνους 5%.

IF ( <έλεγχος>, ΝΑΙ, ΟΧΙ )

SELECT first_name, last_name, salary_y,
		IF (birth_date < '1973-01-01' , salary_y * 0.10, salary_y * 0.05) AS Salary_Bonus
FROM employees;

CASE

Η συνάρτηση CASE είναι παρόμοια με την συνάρτηση IF αλλά με την CASE μπορούμε να κάνουμε παραπάνω από έναν έλεγχο. Ας υποθέσουμε ότι θέλουμε να δώσουμε ένα μπόνους ανάλογα με τον μισθό των υπαλλήλων μας. Όσοι έχουν μισθό πάνω από 30.000€ θα πάρουν μπόνους 5%, όσοι έχουν μισθό από 20.000€ μέχρι 30.000€ θα πάρουν μπόνους 10% και όσοι έχουν κάτω από 20.000€ θα πάρουν μπόνους 15%.

CASE
WHEN <έλεγχος> THEN <αποτέλεσμα>
WHEN <έλεγχος> THEN <αποτέλεσμα> …..
ELSE <αποτέλεσμα>
END

SELECT first_name, last_name, job, salary_y,
CASE
	WHEN salary_y > 30000 THEN ROUND(salary_y * 0.05)
    WHEN salary_y >= 20000 THEN ROUND(salary_y * 0.10)
    ELSE ROUND(salary_y * 0.15)
END AS yearly_bonus
FROM employees;

JOINS

Πολλές φορές θέλουμε να τραβήξουμε δεδομένα από διαφορετικούς πίνακες, για να το καταφέρουμε αυτό χρησιμοποιούμε την εντολή JOIN. Υπάρχουν 4 διαφορετικές επιλογές όταν θέλουμε να ενώσουμε δύο πίνακες. Για να δούμε και τις τέσσερις περιπτώσεις θα χρησιμοποιήσουμε δύο νέους πίνακες.

SELECT <column_names>
FROM <table_name> <table_alias>
(INNER-LEFT-RIGHT-CROSS) JOIN <table_name> <table_alias>
ON <table_alias>.<column_name> = <table_alias>.<column_name>;

INNER JOIN

Η πρώτη περίπτωση είναι όταν θέλουμε να κρατήσουμε μόνο τις σειρές που υπάρχουν και στους δύο πίνακες. Σε αυτό το παράδειγμα η ένωση θα γίνει με βάση την στήλη ‘customer_id’. Όποτε με το INNER JOIN θα κρατήσουμε τις σειρές που το ‘customer_id’ υπάρχει και στους δύο πίνακες. Αν δούμε τους πίνακες τα ‘customer_id’ που υπάρχουν και στους δύο πίνακες είναι το 2 και το 4 άρα θα κρατήσουμε μόνο αυτές τις δύο στήλες.

SELECT customer_name, order_id, order_date
FROM test_customers c
INNER JOIN test_orders o
ON o.customer_id = c.customer_id;

LEFT – RIGHT JOIN

Εδώ κρατάμε όλες τις σειρές από τον πρώτο (LEFT) ή δεύτερο (RIGHT) πίνακα και προσθέτουμε τις σειρές του άλλου πίνακα που ταιριάζουν. Συνήθως χρησιμοποιούμε μόνο το LEFT και αν θέλουμε να κρατήσουμε τα στοιχεία του δεύτερου πίνακα απλά γράφουμε αυτόν πρώτα.

SELECT customer_name, order_id, order_date
FROM test_customers c
LEFT JOIN test_orders o
ON o.customer_id = c.customer_id;

CROSS JOIN

Σε αυτή την περίπτωση επιστρέφει κάθε συνδυασμό των γραμμών ανάμεσα στους δύο πίνακες. Η συγκεκριμένη ένωση χρησιμοποιείτε σπάνια και επιστρέφει πάρα πολύ μεγάλους πίνακες.

SELECT c.customer_name, o.order_id, o.order_date
FROM test_customers c
CROSS JOIN test_orders o;

OUTER JOIN

Το OUTER JOIN επιστρέφει τις γραμμές και από τους δύο πίνακες. Δυστυχώς η MySQL δεν έχει την εντολή OUTER JOIN. Ο πίνακας που θα πέρναμε με το OUTER JOIN είναι ο παρακάτω.

Παραδείγματα με τα JOINs

Ας δούμε μερικά ακόμα παραδείγματα με τους πίνακες από την βάση δεδομένων μας.

Ας υποθέσουμε ότι θέλουμε να δούμε τα ονόματα των πελατών μας και τον αριθμό της χρεωστικής τους κάρτας.

SELECT first_name, last_name, card_number
FROM customers c
JOIN credit_cards cc
ON c.cust_id = cc.cust_id
LIMIT 100;

Ας δούμε ένα λίγο πιο δύσκολο ερώτημα. Ας υποθέσουμε ότι θέλουμε να δούμε το ιστορικό υποστήριξης αλλά θέλουμε σε μία στήλη το ονοματεπώνυμο του υπαλλήλου σε μία δεύτερη στήλη το ονοματεπώνυμο του πελάτη και άλλες δύο στήλες με το αν λύθηκε το θέμα και ποιο ήταν αυτό.
Για να το λύσουμε αυτό θα χρειαστεί να ενώσουμε παραπάνω από δύο πίνακες. Η διαδικασία όμως είναι ίδια ενώνουμε τον πρώτο πίνακα με τον δεύτερο και μετά ενώνουμε τον τρίτο πίνακα με τον πίνακα που έχουμε δημιουργήσει ήδη.

SELECT CONCAT(e.first_name, ' ', e.last_name) AS 'Όνομα Υπαλλήλου', 
	   CONCAT(c.first_name, ' ', c.last_name) AS 'Όνομα Πελάτη', 
       s.resolved, st.subject_name
FROM employees e
JOIN support_history s ON e.emp_id = s.emp_id
JOIN customers c ON c.cust_id = s.cust_id
JOIN support_topic st ON st.subject_id = s.subject_id
ORDER BY RAND()
LIMIT 30;*

Η συνάρτηση RAND() είναι προαιρετική και απλά μας επιστρέφει τυχαίες σειρές του πίνακα.

VIEW

Η εντολή VIEW δημιουργεί έναν εικονικό πίνακα ο οποίος αποτελείται από όποιες στήλες και όποιους πίνακες θέλουμε. Για παράδειγμα τον πίνακα που φτιάξαμε στο προηγούμενο παράδειγμα θα μπορούσαμε να τον προσθέσουμε σε ένα VIEW και κάθε φορά που θα θέλουμε να τον δούμε να μην χρειάζεται να γράφουμε όλες αυτές τις εντολές. Ένα VIEW είναι χρήσιμο και όταν θέλουμε σε κάποιους χρήστες να δώσουμε πρόσβαση σε συγκεκριμένες στήλες και όχι σε όλα τα στοιχεία του πίνακα. Οπότε φτιάχνουμε ένα VIEW και τους δίνουμε πρόσβαση σε αυτό το VIEW μόνο και όχι στους πίνακες.

CREATE VIEW <view_name> AS
SELECT … FROM …

CREATE VIEW support_summary AS
	SELECT CONCAT(e.first_name, ' ', e.last_name) AS 'Όνομα Υπαλλήλου', 
	       CONCAT(c.first_name, ' ', c.last_name) AS 'Όνομα Πελάτη', 
           s.resolved, st.subject_name
	FROM employees e
	JOIN support_history s ON e.emp_id = s.emp_id
	JOIN customers c ON c.cust_id = s.cust_id
	JOIN support_topic st ON st.subject_id = s.subject_id;

Μετά το μόνο πράγμα που έχουμε να κάνουμε κάθε φορά είναι να χρησιμοποιούμε την εντολή SELECT για να καλέσουμε τον πίνάκα.

SELECT * FROM support_summary;

Διάφορα Παραδείγματα

1) Βρείτε τον μέσο μισθό ανά κατάστημα.

SELECT branch_city, ROUND(AVG(salary_y)) AS avg_salary
FROM employees e
JOIN branch b
ON e.branch_id = b.branch_id
GROUP BY e.branch_id
ORDER BY avg_salary DESC;

2) Το ονοματεπώνυμο κάθε προηστάμενου και πόσους εργαζόμενους έχει υπό την επίβλεψη του.

SELECT CONCAT(e2.first_name, ' ', e2.last_name) AS 'Supervisor Name', 
	   COUNT(e.super_id) AS 'Number of Employees'
FROM employees e
JOIN employees e2
ON e.super_id = e2.emp_id
GROUP BY e.super_id
ORDER BY COUNT(e.super_id) DESC;

3) Τον αριθμό των υπαλλήλων ανά κατάστημα και το ονοματεπώνυμο του Μάνατζερ.

SELECT b.branch_city, 
	   CONCAT(e2.first_name, ' ', e2.last_name) AS 'Manager Name', 
	   COUNT(e.emp_id) AS number_of_employees
FROM employees e
JOIN branch b
ON e.branch_id = b.branch_id
JOIN employees e2
ON e2.emp_id = b.mng_id
GROUP BY e.branch_id
ORDER BY number_of_employees DESC;

4) Το ονοματεπώνυμο των πελατών που επικοινώνησαν τουλάχιστον 4 φορές με την υποστήριξη πελατών και πόση ώρα απασχόλησαν συνολικά τους υπαλλήλους μας.

SELECT CONCAT(c.first_name, ' ',c.last_name) AS 'Customer Name',
	   COUNT(sh.cust_id) AS number_supp,
	   ROUND((SUM(min_took)) / 60,1) AS hours_with_support
FROM customers c
JOIN support_history sh
ON c.cust_id = sh.cust_id
GROUP BY sh.cust_id
HAVING number_supp >= 4
ORDER BY hours_with_support DESC;