Mostra la font per a SQL Tutorial: create, alter i drop tables

=Introducció=
Abans de fer ''create tables'' hem de saber quins són els tipus de dades que podem fer servir a MySQL:
*https://www.sqltutorial.org/sql-data-types/

I després podrem estudiar ja com es creen les taules (i també esborrar i modificar):
Section 12: Working with table structures
*CREATE TABLE – create a new table in the database.
*ALTER TABLE – modify the structure of an existing table.
*DROP TABLE – remove the tables permanently.
*TRUNCATE TABLE – delete all data in a big table fast and efficiently.
*https://www.sqltutorial.org/sql-create-table/

=T1: Tipus de dades al MySQL=
*https://www.sqltutorial.org/sql-data-types/
En la referència oficial de MySQL trobem la informació oficial:
*https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Tipus més importants que farem servir habitualment:
===caràcters===
<pre>
first_name VARCHAR(50)
CP CHAR (5)
</pre>
===Tipus numèrics===
<pre>
DECIMAL (p,s)
#p is the precision that represents the number of significant digits. És el número total de dígits (precisió)
#s is the scale which represents the number of digits after the decimal point. És el número de posicions decimals
INTEGER
TINYINT: de -128 a 127 (o UNSIGNED SMALLINT: de 0 a 255)
SMALLINT: de -32768 a 32767 (o UNSIGNED SMALLINT: de 0 a 65535)
INT: valors molt més grans (de -2147483648 a 2147483647; unsigned: de 0 a 4294967295)
BIGINT: no l'utilitzarem
</pre>
*https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
===Date and Time types===
<pre>
DATE: '2020-12-31' 
TIME: '10:59:30.9999'
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'

mysql> select now(); -> retorna un timestamp
</pre>

===Boolean===
Es pot utilitzar BOOL o BOOLEAN, que de fet és un àlies de tinyint(1):

===Taula amb tots els tipus===
<pre>
CREATE TABLE TOTS_ELS_TIPUS (
   nom VARCHAR(50),
   CP CHAR (5),
   preu DECIMAL (5,2),
   edat TINYINT UNSIGNED,
   salari_pobre SMALLINT,
   salari_ric INT,
   data_naix DATE,
   hora TIME,
   data_actual TIMESTAMP,
   es_major_edat BOOLEAN
);

INSERT INTO TOTS_ELS_TIPUS VALUES(
   'MARIA',
   '08012',
   34.23,
   16,
   20000,
   120000,
   '2005-11-19',
   '02:24:32',
   NOW(),
   TRUE
);
</pre>
Evidentment si incomplim un dels tipus sortirà un error:
<pre>
INSERT INTO TOTS_ELS_TIPUS VALUES(
   'MARIA',
   '080124',
   34.23,
   16,
   20000,
   120000,
   '2005-11-19',
   '02:24:32',
   NOW(),
   TRUE
);

ERROR 1406 (22001): Data too long for column 'CP' at row 1
</pre>
'''Notes''':
*farem servir cometes simples en comptes de cometes dobles
*si volem introduir un apòstrof, recorda utilitzar ''doble cometa simple'', o bé la contrabarra com a caràcter d'escapament (\)
*els valors numèrics van sense cometes simples,  utlitzem el punt decimal en comptes de la coma decimal.
*boolean: FALSE equival al valor 0; TRUE equival a quasevol valor != 0.
*Les dates les utilitzem de moment amb notació anglesa. Evidentment, hi ha maneres d'utilitzar un altre format.

UPDATE personal set birthday = STR_TO_DATE('1-01-2012', '%d-%m-%Y') where birthday IS NULL;

<pre>
INSERT INTO TOTS_ELS_TIPUS VALUES(
   'MARIA',
   '08012',
   34.23,
   16,
   20000,
   120000,
   STR_TO_DATE('19-11-2005', '%d-%m-%Y'),
   '02:24:32',
   NOW(),
   TRUE
);
</pre>
I en fer la select podem també obtenir el format habitual:
<pre>
SELECT DATE_FORMAT(data_naix,'%d %M %Y') from TOTS_ELS_TIPUS;
19 November 2005

SELECT DATE_FORMAT(data_naix,'%d-%m-%Y') from TOTS_ELS_TIPUS;
19-11-2005

SELECT DATE_FORMAT(data_naix,'%d/%m/%Y') from TOTS_ELS_TIPUS;
19/11/2005
</pre>

=T2: CREATE TABLE=
De forma general:
<pre>
CREATE TABLE table_name(
     column_name_1 data_type default value column_constraint,
     column_name_2 data_type default value column_constraint,
     ...,
     table_constraint
);
</pre>
<pre>
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
	FOREIGN KEY course_id REFERENCES courses(course_id),
    PRIMARY KEY (employee_id , course_id)
);
</pre>
==SQL Identity==
<pre>
CREATE TABLE ranks (
    rank_id INT GENERATED ALWAYS AS IDENTITY,
    rank_name CHAR
);
</pre>
Depèn de la versió dóna error:
<pre>
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY,
    rank_name CHAR
)' at line 2
</pre>
I és que hem de tenir en compte que el MySQL evoluciona, i a vegades hem de tenir en compte la versió que estem utilitzant.

==SEQUENCE==
<pre>
CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    rank_name CHAR NOT NULL
); 
</pre>
'''nota''': error degut a la versió

==Autoincrement==
Aquesta és l'opció que utilitzarem habitualment quan vulguem generar un autonumèric sense haver de preocupar-nos pel valor:
<pre>
CREATE TABLE leave_requests (
    request_id INT AUTO_INCREMENT,
    employee_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    leave_type INT NOT NULL,
    PRIMARY KEY(request_id)
);
</pre>

<pre>
insert into leave_requests (employee_id, start_date, end_date, leave_type) values (1,now(),now(),1);
insert into leave_requests (employee_id, start_date, end_date, leave_type) values (1,now(),now(),1);
insert into leave_requests (employee_id, start_date, end_date, leave_type) values (1,now(),now(),1);
insert into leave_requests (employee_id, start_date, end_date, leave_type) values (1,now(),now(),1);

mysql> select request_id from leave_requests;
+------------+
| request_id |
+------------+
|          1 |
|          2 |
|          3 |
|          4 |
+------------+
</pre>

=T3: ALTER TABLE=
<pre>
mysql> desc courses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| course_id   | int         | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
</pre>
o bé
<pre>
mysql> SHOW CREATE TABLE courses;
</pre>


Afegim i modifiquem camps:
<pre>
ALTER TABLE courses ADD credit_hours INT NOT NULL;

ALTER TABLE courses 
ADD fee NUMERIC (10, 2) AFTER course_name,
ADD max_limit INT AFTER course_name;

ALTER TABLE courses MODIFY fee NUMERIC (10, 2) NOT NULL;
</pre>
<pre>
mysql> desc courses;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| course_id    | int           | NO   | PRI | NULL    | auto_increment |
| course_name  | varchar(50)   | NO   |     | NULL    |                |
| max_limit    | int           | YES  |     | NULL    |                |
| fee          | decimal(10,2) | NO   |     | NULL    |                |
| credit_hours | int           | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
</pre>
I ara eliminem 3 columnes:
<pre>
ALTER TABLE courses DROP COLUMN fee;

ALTER TABLE courses 
DROP COLUMN max_limit,
DROP COLUMN credit_hours;
</pre>
<pre>
mysql> desc courses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| course_id   | int         | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
</pre>

<pre>
CREATE TABLE candidates (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
</pre>
<pre>
ALTER TABLE candidates
ADD COLUMN phone VARCHAR(50);

ALTER TABLE candidates
ADD COLUMN home_address VARCHAR(255),
ADD COLUMN dob DATE,
ADD COLUMN linkedin_account VARCHAR(255);
</pre>

<pre>
CREATE TABLE persons (
    person_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);

mysql> desc persons;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| person_id     | int          | NO   | PRI | NULL    |       |
| first_name    | varchar(255) | NO   |     | NULL    |       |
| last_name     | varchar(255) | NO   |     | NULL    |       |
| date_of_birth | date         | NO   |     | NULL    |       |
| phone         | varchar(25)  | YES  |     | NULL    |       |
| email         | varchar(255) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

</pre>
<pre>
ALTER TABLE persons
DROP COLUMN email;

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;

mysql> desc persons;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| person_id  | int          | NO   | PRI | NULL    |       |
| first_name | varchar(255) | NO   |     | NULL    |       |
| last_name  | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
</pre>
També podem fer un ''ALTER'' per modificar els tipus de dades dels camps:
<pre>
ALTER TABLE persons
MODIFY person_id SMALLINT;

mysql> desc persons;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| person_id  | smallint     | NO   | PRI | NULL    |       |
| first_name | varchar(255) | NO   |     | NULL    |       |
| last_name  | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
</pre>
Si volem modificar dues columnes, hem de posar dues vegades el MODIFY:
<pre>
ALTER TABLE persons
MODIFY person_id SMALLINT,
MODIFY first_name varchar(100);
</pre>

=T4: DROP TABLE=
Aquesta sentència no té massa secret:
<pre>
DROP TABLE leave_requests;
DROP TABLE persons;
DROP TABLE courses;
</pre>
Per mirar que efectivament s'han esborrat:
<pre>
mysql> show tables;
</pre>

{{Autor}}, desembre 2021