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,
    PRIMARY KEY (employee_id , course_id)
);

T3: ALTER TABLE

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;

ALTER TABLE courses DROP COLUMN fee;

ALTER TABLE courses 
DROP COLUMN max_limit,
DROP COLUMN credit_hours;

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

ALTER TABLE persons
DROP COLUMN email;

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;

T4: DROP TABLE

(TBD)

---

creat per Joan Quintana Compte, desembre 2021