SQL Tutorial: create, alter i drop tables
Salta a la navegació
Salta a la cerca
Contingut
Introducció
Abans de fer create tables hem de saber quins són els tipus de dades que podem fer servir a MySQL:
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
En la referència oficial de MySQL trobem la informació oficial:
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
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
