DDL

name: portada
layout: true
class: portada-slide, middle, right
---
# Data Definition Language (DDL)
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Databases in MariaDB
[MariaDB](https://mariadb.com/kb/en/data-definition/) allows to create muiltiple databases. It provides with the following features and statements:

- Create a database:

```
CREATE DATABASE database_name;
```

- Delete a database:

```
DROP DATABASE database_name;
```

- List databases:

```
SHOW DATABASES;
```

---
# Databases in MariaDB
Tables are stored instide a unique database. If we want to work on a single database, we can sepecify which database
to use with the sentence:

```
USE database_name;
```

---
# Tables in MariaDB
- Create a table:
  - IF NOT EXISTTS: It's optional and will only create the table if not exists.
  - Each field is defined with a name and its data type. We can also provide different options.

```
CREATE TABLE [IF NOT EXISTS] table_name (
    field1_name FIELD1_TYPE [COSTRAINTS],
    ...
);
```
- Delete a table:

```
DROP TABLE table_name;
```

- List tables:

```
SHOW TABLES;
```

---
# Data types
.center[[MariaDB data types](https://mariadb.com/kb/en/data-types/)]
.center[[Numeric MariaDB data types](https://mariadb.com/kb/en/numeric-data-type-overview/)]

- .blue[INT]: A integer number.
- .blue[FLOAT] or .blue[DOUBLE]: Single or double precision decimal number.
- .blue[BOOLEAN]: Boolean value that can store TRUE or FALSE values.
- .blue[VARCHAR(N)]: String value with variable length. N represents the max string length.
- .blue[DATE]: Date in YYYY-MM-DD format
- .blue[TIME]: Time in HH:MM:SS.ssssss format
- .blue[DATETIME]: Date and time in YYYY-MM-DD HH:MM:SS.ssssss format

```
CREATE TABLE person (
    name VARCHAR(30),
    birth_date DATE,
    height INT,
    weight FLOAT
);
```

---
# Column definition
.center[[MariaDB column definitions](https://mariadb.com/kb/en/create-table/#column-definitions)]

- .blue[NULL] or .blue[NOT NULL]: Specifies if the field can contain NULL values. If not specified, NULL values are allowed.
- .blue[DEFAULT]: Specifies the default field value if not specified.
- .blue[AUTO_INCREMENT]: Value can can be set automatically from a simple counter. Only allowed in INT types.
- .blue[CHECK]: Only certain values for this column.
- .blue[PRIMARY KEY]: Specify that this field is a primary key. Its UNIQUE and NOT NULL implicitly.
- .blue[UNIQUE KEY]: Specify that this field is unique and all values in the column must be distinct from each other.
  Unless the column is NOT NULL, there may be multiple rows with NULL in the column.
- .blue[COMMENT]: Column comment or description.

---
# Column definition

```
CREATE TABLE person (
    DNI VARCHAR(9) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE NOT NULL,
    height INT NULL CHECK (height >= 0)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_DNI varchar(9) NOT NULL REFERENCES person(DNI),
    price FLOAT NOT NULL
);
```
---
# Foreign keys
.center[[Foreign keys MariaDB](https://mariadb.com/kb/en/foreign-keys/)]
.center[[Foreign keys W3](https://www.w3schools.com/sql/sql_foreignkey.asp)]
- .blue[FOREIGN KEY ... REFERENCES]: Specifies that this attribute is a FOREIGN KEY and references a PRIMARY or UNIQUE KEY in another table. 
  Datatypes __must__ be the same in both tables.

```sql
FOREIGN KEY (attribute) REFERENCES table(table_attribute)
```

---
# ON DELETE/UPDATE
Guarantee that the data integrity is mantained, changes or deletions in elements references in other tables must be taken into account.

```sql
FOREIGN KEY (attribute) REFERENCES table(table_attribute)
[ON DELETE reference_option]
[ON UPDATE reference_option]
```
`reference_option` values are:
- .b[RESTRICT] or .b[NO ACTION]: Default value. The change in the parent table is prevented.
- .b[CASCADE]: The change is allowed and propagates to the child table. 
  For example, if a parent row is deleted, the child row is also deleted; if a parent row's ID changes, the child row's ID will also change.
- .b[SET NULL]: The change is allowed, and the child row's foreign key columns are set to NULL.
- .b[SET DEFAULT]: The change is allowed, and the child row's foreign key columns are set to the DEFAULT value.

---
# Costraints
.center[[Costraints W3](https://www.w3schools.com/sql/sql_constraints.asp)]

Costraints can be defined inline in the column definition or in a separate statement at the end of the table creation. 
Inline constraints will only apply to the column, while separate constraints can affect multiple columns.

Costraints can also be named with `CONSTRAINT costraint_name`.

```
CREATE TABLE person (
    DNI VARCHAR(9) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    birth_date DATE NOT NULL,
    height INT NULL CHECK (height >= 0)
);

CREATE TABLE enrolled (
  student_id INT NOT NULL REFERENCES student(student_id),
  subject_id INT NOT NULL REFERENCES subject(subject_id),
  UNIQUE(student_id, subject_id)
);
```

---
# Alter table
.center[[Alter table W3](https://www.w3schools.com/sql/sql_alter.asp)]
Modify a exisiting table.

- Add column:

```sql
ALTER TABLE table_name
ADD column_name datatype [OPTIONS];
```
- Delete column:

```sql
ALTER TABLE table_name
DROP column_name;
```

- Modify column:

```sql
ALTER TABLE table_name
MODIFY column_name datatype [OPTIONS];
```

---
# Insert values
.center[[Insert into W3](https://www.w3schools.com/sql/sql_insert.asp)]
Insert elements into a table.

```sql
INSERT INTO table_name (column_1, column_2, column_3, ...) VALUES
(value_11, value_12, value_13, ...),
(value_21, value_22, value_23, ...),
...
(value_N1, value_N2, value_N3, ...);
```

---
# Select values
.center[[Select W3](https://www.w3schools.com/sql/sql_select.asp)]
Select elements from a table.

```sql
SELECT column1, column2, ...
FROM table_name;
```
```sql
SELECT * FROM table_name;
```