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

  • Reading time:11 mins read

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

Εισαγωγή

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

Εγκατάσταση MySQL

Αρχικά κάνουμε αναζήτηση στον browser μας το κείμενο ‘mysql community server και στο πρώτο που θα σας δείξει θα κάνετε κλικ.

Στην συνέχεια διαλέγουμε το λειτουργικό μας και πατάμε το πρώτο download.

Αφού το κατεβάσουμε το ανοίγουμε και ξεκινάμε την εγκατάσταση.
Στο ‘setup type‘ διαλέγουμε ‘custom‘.

Στο ‘select product‘ επιλέγουμε το ‘MySQL Server‘ και ‘MySQL Workbench‘.

Στα υπόλοιπα πατάμε Next και τα αφήνουμε όπως είναι.
Στο ‘account and roles‘ βάζουμε έναν κωδικό της αρεσκίας μας. Ο συγκεκριμένος κωδικός θα χρειάζεται κάθε φορά για να συνδεθούμε στην βάση μας.

Αν δεν ανοίξει αυτόματα το workbench, κάνουμε αναζήτηση στα windows το ‘mysql workbench‘ και το ανοίγουμε.

Όταν ανοίξουμε το workbench μας εμφανίζει στην αρχική σελίδα για να συνδεθούμε στον server που θέλουμε. Αν σας ανοίξει σε κάποιο άλλο παράθυρο κάνετε κλικ στο πρώτο εικονίδιο από τα αριστερά.
Κάτω από το κείμενο ‘MySQL Connections‘ πρέπει να σας εμφανίζει μία σύνδεση κάνετε κλικ πάνω της και βάζεται τον κωδικό που βάλατε κατα την εγκατάσταση.

Αν δεν σας εμφανίζει κάποια σύνδεση κάνετε κλικ στο κουμπί συν και φτιάχνετε μία νέα σύνδεση. Βάζετε ότι όνομα θέλετε και συμπληρώνετε τον κωδικό τα υπόλοιπα τα αφήνετε όπως είναι. Τέλος πατάμε ‘test connection‘ και αν είναι όλα εντάξει πατάμε OK και μετά συνδεόμαστε όπως είδαμε πριν.

Μόλις συνδεθούμε θα μας βγάλει σε αυτό το παράθυρο.

Για να δημιουργήσουμε την βάση δεδομένων μας γράφουμε ‘CREATE DATABASE <name> ;’ και βάζουμε όποιο όνομα θέλουμε να έχει η βάση μας.
Σε κάθε εντολή στο τέλος βάζουμε ερωτηματικό.
Για να την εκτελέσουμε κάνουμε κλικ μέσα στην εντολή που γράψαμε και μετά κάνουμε κλικ στον δεύτερο κεραυνό που φαίνεται και στην εικόνα από πάνω.
Επειδή μπορούμε να έχουμε πολλές βάσεις δεδομένων πρέπει να ορίσουμε ποια θα χρησιμοποιήσουμε. Για να το κάνουμε αυτό μπορούμε είτε να κάνουμε διπλο κλικ πάνω στο όνομα της βάσης στα αριστερά είτε να γράψουμε την εντολή ‘USE <name> ; ‘.
Εαν δεν μας εμφανίζεται το όνομα της βάσης στα αριστερά τότε μπορούμε να πατήσουμε το κουμπί για την ανανέωση.
Όταν συνδεόμαστε σε μία βάση αυτή αναγράφεται με έντονο μαύρο για να την ξεχωρίζουμε από τις υπόλοιπες.

Στο παράθυρο στο κάτω μέρος της οθόνης βλέπουμε το αποτέλεσμα των εντολών μας. Εάν είναι ένα πράσινο τικ σημαίνει ότι όλα εκτελέστηκαν σωστά. Εαν έχει ένα κόκκινο Χ σημαίνει ότι υπάρχει κάποιο πρόβλημα και η εντολή δεν εκτελέστηκε.
Κάτω από την στήλη message θα μας γράφει ποιο error βρέθηκε και δεν έτρεξε η εντολή μας. Μετά μπορούμε να ξανακοιτάξουμε την εντολή μας να δούμε που έχουμε το λάθος και εάν δεν το βρίσκουμε τότε μπορούμε να κάνουνε αναζήτηση στο ίντερνετ το error που μας έβγαλε για να βρούμε ποιο ήταν το πρόβλημα.

Δημιουργία και Διαγραφή Πίνακα

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

Για να δημιουργήσουμε έναν πίνακα γράφουμε CREATE TABLE, το όνομα του πίνακα και μέσα σε παρένθεση τις στήλες και τα χαρακτηριστικά τους.

CREATE TABLE test
(
    vendor_id		 INT				PRIMARY KEY		AUTO_INCREMENT,
    vendor_name	     VARCHAR(50)		NOT NULL		UNIQUE
); 

Το vendor_int είναι ένας φυσικός αριθμός, θα είναι το Primary Key και θα αυξάνεται κατά ένα σε κάθε νέα γραμμή. Το vendor_name είναι κείμενο μέχρι 50 χαρακτήρες, δεν μπορεί να είναι κενό και κάθε όνομα πρέπει να είναι και μοναδικό.

Για να διαγράψουμε έναν πίνακα.

DROP TABLE test; 

Για να διαγράψουμε μόνο τα περιεχόμενα του πίνακα αλλά όχι τον ίδιο τον πίνακα.

TRUNCATE TABLE test; 

Primary Key

Το Primary Key μπορούμε να το δηλώσουμε είτε δίπλα από την στήλη είτε στο τέλος ξεχωριστά.
Εάν θέλουμε να προσθέσουμε δύο ή παραπάνω στήλες στο Primary Key τότε πρέπει υποχρεωτικά να το δηλώσουμε στο τέλος.

CREATE TABLE test
(
	vendor_id		INT				AUTO_INCREMENT,
    vendor_name		VARCHAR(50)		NOT NULL,
    CONSTRAINT vendors_pk PRIMARY KEY (vendor_id, vendor_name)
); 

Foreign Key

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

CREATE TABLE branch_test (branch_id INT PRIMARY KEY);

CREATE TABLE test
(
	vendor_id		INT				AUTO_INCREMENT,
    vendor_name		VARCHAR(50)		NOT NULL,
    branch_id_f		INT,
    CONSTRAINT vendors_pk PRIMARY KEY (vendor_id, vendor_name),
    CONSTRAINT branch_fk FOREIGN KEY (branch_id_f) REFERENCES branch_test (branch_id)
); 

Index

Για να προσθέσουμε σε μία στήλη Index χρησιμοποιούμε την εντολή CREATE INDEX.
Εάν θέλουμε να είναι και μοναδικές οι τιμές γράφουμε  την εντολή CREATE UNIQUE INDEX.
Αρχικά δίνουμε ένα όνομα στο Index και μετά γράφουμε τον πίνακα και μέσα σε παρένθεση την στήλη.

CREATE UNIQUE INDEX vendors_email_ix 
ON test (vendors_email); 

** Η MySQL προσθέτει αυτόματα Index στις στήλες που είναι Primary και Foreign Key.

Επεξεργασία Πινάκων

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

Για να προσθέσουμε μία νέα στήλη.

ALTER TABLE test
ADD vendor_email VARCHAR(80); 

Για να μετανομάσουμε μία στήλη.

ALTER TABLE test
RENAME COLUMN vendor_email TO vendors_email; 

Για να επεξεργαστούμε τα χαρακτηρηστικά μίας στήλης.

ALTER TABLE test
MODIFY vendors_email VARCHAR(100); 

Για να αφαιρέσουμε το Foreign Key από μία στήλη.

ALTER TABLE test
DROP FOREIGN KEY branch_fk; 

Εισαγωγή Δεδομένων

Για να προσθέσουμε τιμές στους πίνακες χρησιμοποιούμε την εντολή INSERT INTO <table name> VALUES (values)
Οι τιμές πρέπει να είναι με την ίδια σειρά που είναι και οι στήλες του πίνακα.

INSERT INTO test VALUES
(1, 'Coca Cola', 4, 'info@cocacola.gr'),
(2, 'Vikos Cola', 2, 'info@vikos.gr'),
(3, 'Green Cola', 1, 'info@green.gr'); 

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

INSERT INTO test(vendor_name, branch_id_f, vendors_email)
VALUES ('Pepsi Cola', 3, 'info@pepsi.gr');

INSERT INTO test(vendor_name, vendors_email)
VALUES ('Fanta', 'info@fanta.gr'); 

Εάν θέλουμε να δούμε τις τιμές που έχει ένας πίνακας γράφουμε την εντολή
SELECT * FROM <table_name>
Ο αστερίσκος είναι για να επιλέξουμε όλες τις στήλες, εάν θέλουμε να δούμε μόνο μερικές στήλες γράφουμε τα ονόματα των στηλών χωρισμένα με κόμμα.

SELECT * FROM test; 

Επεξεργασία Δεδομένων

Εαν θέλουμε να αλλάξουμε την τιμή από ένα κελί χρησιμοποιούμε την εντολή
UPDATE <table_name> SET <column_name> = ‘value’ WHERE <column_name> = ‘value’
Την εντολή where την βάζουμε για να δείξουμε σε ποια γραμμή θέλουμε να αλλάξουμε την τιμή μιας στήλη.

UPDATE test
SET vendors_email = 'info@greencola.gr'
WHERE vendor_id = 3; 

Δημιουργία Πινάκων για την Βάση Δεδομένων μας

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

Αρχικά θα δημιουργήσουμε και θα εισάγουμε τα δεδομένα στους δύο μικρούς πίνακες, στον πίνακα branch και στον πίνακα support_topic.
Πρώτα θα φτιάξουμε όλους τους πίνακες και μετά θα εισάγουμε τα Foreign Keys σε κάθε πίνακα για να μην έχουμε κάποιο πρόβλημα, όπως το να υπάρχει το Foreign Key σε έναν πίνακα αλλά να μην υπάρχει ο άλλος ο πίνακας με τον οποίο θα συνδέεται το Foreign Key.

CREATE TABLE branch
(
	branch_id		INT				AUTO_INCREMENT		PRIMARY KEY,
    branch_city		VARCHAR(30)		NOT NULL,
    mng_id			INT				UNIQUE,
    mng_start		DATE
);

INSERT INTO branch (branch_city, mng_id, mng_start)
VALUES
	('Athens', 1, '2018-11-15'),
	('Thessaloniki', 3, '2019-04-02'),
	('Larissa', 8, '2021-09-19'),
	('Patra', 10, '2020-12-07')
; 
CREATE TABLE support_topic
(
	subject_id		INT				AUTO_INCREMENT		PRIMARY KEY,
    subject_name	VARCHAR(30)		NOT NULL			UNIQUE
);

INSERT INTO support_topic (subject_name)
VALUES
	('Transfers'),
    ('Adding Money'),
    ('Cards'),
    ('Card Payments & Withdrawals'),
    ('Account Management'),
    ('Credit Products'),
    ('Insurance'),
    ('Sign Up'),
    ('App Features'),
    ('Other')
; 

Μπορούμε να τρέξουμε και την εντολή SELECT για να δούμε αν δημιουργήθηκαν οι πίνακες όπως θέλαμε.

SELECT * FROM branch;
SELECT * FROM support_topic; 

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

Εάν θέλετε να μάθετε πώς δημιουργήσαμε τα δεδομένα για τους συγκεκριμένους πίνακες μπορείτε να δείτε το συγκεκριμένο μάθημα.

CREATE TABLE employees
(
	emp_id			INT				AUTO_INCREMENT		PRIMARY KEY,
    first_name		VARCHAR(50)		NOT NULL,
    last_name		VARCHAR(60)		NOT NULL,
    sex				CHAR(1),
    job				VARCHAR(80),
    birth_date		DATE			NOT NULL,
    phone_number	CHAR(10)		NOT NULL			UNIQUE,
    user_name		VARCHAR(50)							UNIQUE,
    company_email	VARCHAR(70)							UNIQUE,
    pass_word		VARCHAR(20),
    city			VARCHAR(50)		NOT NULL,
    postcode		CHAR(5)			NOT NULL,
    street_name		VARCHAR(50),
    street_number	INT,
    afm				CHAR(9)			NOT NULL			UNIQUE,
    amka			CHAR(11)		NOT NULL			UNIQUE,
    iban			CHAR(27)		NOT NULL			UNIQUE,
    salary_y		INT,
    vacation_days	INT,
    super_id		INT,
    branch_id		INT		
); 
CREATE TABLE customers
(
	cust_id				INT				AUTO_INCREMENT		PRIMARY KEY,
    first_name			VARCHAR(50)		NOT NULL,
    last_name			VARCHAR(60)		NOT NULL,
    sex					CHAR(1),
    birth_date			DATE			NOT NULL,
    phone_number		CHAR(10)		NOT NULL			UNIQUE,
    user_name			VARCHAR(50)		NOT NULL			UNIQUE,
    email				VARCHAR(70)		NOT NULL			UNIQUE,
    password_hash		VARCHAR(50)		NOT NULL,
    country				VARCHAR(60)		NOT NULL,
    city				VARCHAR(50)		NOT NULL,
    postcode			CHAR(5)			NOT NULL,
    street_name			VARCHAR(50),
    street_number		INT,
    ssn					CHAR(11)		NOT NULL			UNIQUE,
    cust_acc_created	DATE			NOT NULL
)AUTO_INCREMENT=1000; 
CREATE TABLE credit_cards
(
	card_id			INT 			AUTO_INCREMENT			PRIMARY KEY,
    card_number		CHAR(19)		NOT NULL				UNIQUE,
    cust_id			INT,
    card_date		DATE 			NOT NULL,
    card_cvc		CHAR(3)			NOT NULL,
    card_pin		CHAR(4)			NOT NULL
); 
CREATE TABLE support_history
(
	emp_id			INT,
    cust_id			INT,
    date_time		DATETIME,
    resolved		VARCHAR(3)		NOT NULL,
    min_took		INT				NOT NULL,
    subject_id		INT 			NOT NULL,
    CONSTRAINT support_pk PRIMARY KEY (emp_id, cust_id, date_time)
); 

Αφού έχουμε δημιουργήσει όλους του πίνακες τώρα πρέπει να εισάγουμε τα δεδομένα μας. Η MySQL από default τρέχει σε safe mode και δεν μας αφήνει να ανεβάσουμε από οποιονδήποτε φάκελο δεδομένα παρά μόνο από έναν ασφαλή φάκελο.
Για να δούμε ποιος είναι αυτός γράφουμε την εντολή:

SHOW VARIABLES LIKE "secure_file_priv"; 

Οπότε για να μπορέσουμε να φορτώσουμε τα δεδομένα από το αρχείο CSV θα πρέπει να το μετακινήσουμε σε αυτόν τον φάκελο.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employees_f.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 

Οι στήλες από το αρχείο θα πρέπει να είναι ακριβώς οι ίδιες με τον πίνακα που έχουμε δημιουργήσει για να μπορέσουμε να εισάγουμε τα δεδομένα.
Στην πρώτη σειρά βάζουμε την διαδρομή του αρχείου και το όνομά του, μετά γράφουμε το όνομα του πίνακα, στην συνέχεια γράφουμε πώς χωρίζονται τα δεδομένα, στην συγκεκριμένη περίπτωση είναι με κόμμα. Και στην τελευταία σειρά γράφουμε πόσες σειρές να παραλέιψει χωρίς να τις διαβάσει, εδώ βάζουμε μία σειρά γιατί στην πρώτη σειρά στο αρχείο CSV είναι οι κεφαλίδες αλλά ο πινακάς που έχουμε έχει ήδη κεφαλίδες.
Το ίδιο κάνουμε και για τους άλλους τρεις πίνακες.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customers_df.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/credit_card.csv'
INTO TABLE credit_cards
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/support_history.csv'
INTO TABLE support_history
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; 

** Επειδή μερικά CSV έχουν εκκατομύρια σειρές, ανάλογα και με τον υπολογιστή που το τρέχετε ενδέχεται να πάρει αρκετή ώρα.
      Εάν σας βγάλει timeout error μπορείτε να πάτε:
      Edit → Preferences → SQL Editor και να αλλάξετε την τιμή στο timeout interval.

Τέλος, αφού προσθέσαμε και τα δεδομένα σε όλους τους πίνακες θα προσθέσουμε και τα Foreign Keys για να δημιουργηθούν οι συνδέσεις μεταξύ των πινάκων.

ALTER TABLE branch
ADD CONSTRAINT manager_fk
FOREIGN KEY (mng_id) REFERENCES employees (emp_id); 

Χρησιμοποιούμε την εντολή ALTER TABLE και στην συνέχεια κάνουμε ADD το Foreign Key όπως είδαμε και προηγουμένος.

Έπειτα προσθέτουμε και τα υπόλοιπα Foreign Keys στους άλλους πίνακες.

ALTER TABLE employees
ADD CONSTRAINT branch_fk
FOREIGN KEY (branch_id) REFERENCES branch (branch_id),
ADD CONSTRAINT supervisor_fk
FOREIGN KEY (super_id) REFERENCES employees (emp_id);


ALTER TABLE support_history
ADD CONSTRAINT subject_fk
FOREIGN KEY (subject_id) REFERENCES support_topic (subject_id);


ALTER TABLE credit_cards
ADD CONSTRAINT customer_card_fk
FOREIGN KEY (cust_id) REFERENCES customers (cust_id);


ALTER TABLE support_history
ADD CONSTRAINT support_employee
FOREIGN KEY (emp_id) REFERENCES employees (emp_id),
ADD CONSTRAINT support_customer
FOREIGN KEY (cust_id) REFERENCES customers (cust_id); 

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

SELECT * FROM credit_cards; 

Επίσης αν θέλουμε μπορούμε να δούμε και το Database Schema της βάσης μας.
Πατάμε Database → Reverse Engineer → Next → Next, διαλέγουμε την βάση που θέλουμε και πατάμε execute.
Και μας εμφανίζει το Database Schema όπως μπορούμε να το δούμε και παρακάτω.

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

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

Buy Me a Coffee at ko-fi.com
Subscribe
Notify of
guest
0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments