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

first_name VARCHAR(50)
CP CHAR (5)

Tipus numèrics

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

• https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Date and Time types

DATE: '2020-12-31' 
TIME: '10:59:30.9999'
TIMESTAMP 'YYYY-MM-DD HH:MM:SS'

mysql> select now(); -> retorna un timestamp

Boolean

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

Taula amb tots els tipus

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
);

Evidentment si incomplim un dels tipus sortirà un error:

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

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;

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
);

I en fer la select podem també obtenir el format habitual:

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

T2: CREATE TABLE

De forma general:

CREATE TABLE table_name(
     column_name_1 data_type default value column_constraint,
     column_name_2 data_type default value column_constraint,
     ...,
     table_constraint
);

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)
);

SQL Identity

CREATE TABLE ranks (
    rank_id INT GENERATED ALWAYS AS IDENTITY,
    rank_name CHAR
);

Depèn de la versió dóna error:

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

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

CREATE TABLE ranks (
    rank_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    rank_name CHAR NOT NULL
); 

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:

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)
);

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 |
+------------+

T3: ALTER TABLE

mysql> desc courses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| course_id   | int         | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

o bé

mysql> SHOW CREATE TABLE courses;

Afegim i modifiquem camps:

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;

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    |                |
+--------------+---------------+------+-----+---------+----------------+

I ara eliminem 3 columnes:

ALTER TABLE courses DROP COLUMN fee;

ALTER TABLE courses 
DROP COLUMN max_limit,
DROP COLUMN credit_hours;

mysql> desc courses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| course_id   | int         | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

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
);

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);

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    |       |
+---------------+--------------+------+-----+---------+-------+

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    |       |
+------------+--------------+------+-----+---------+-------+

També podem fer un ALTER per modificar els tipus de dades dels camps:

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    |       |
+------------+--------------+------+-----+---------+-------+

Si volem modificar dues columnes, hem de posar dues vegades el MODIFY:

ALTER TABLE persons
MODIFY person_id SMALLINT,
MODIFY first_name varchar(100);

T4: DROP TABLE

Aquesta sentència no té massa secret:

DROP TABLE leave_requests;
DROP TABLE persons;
DROP TABLE courses;

Per mirar que efectivament s'han esborrat:

mysql> show tables;

---

creat per Joan Quintana Compte, desembre 2021