DML

name: portada
layout: true
class: portada-slide, middle, right
---
# Data Manipulation Language (DML)
## 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)]]

---
# Data Manipulation Language (DML)
__Data Manipulation Language__ is a sublanguage of a database language such as __SQL__,
used for adding (insert), deleting and modifying (updating) data in a database.

The main statements are:
- .blue[INSERT]: Adds data to the database.
- .blue[SELECT]: Retrieves data from the database.
- .blue[DELETE]: Removes data to the database.
- .blue[UPDATE]: Modifies data from the database.

---
# INSERT
Insert elements into a table.

```sql
INSERT INTO database.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, ...);
```
.center[[INSERT MariaDB](https://mariadb.com/kb/en/insert/)]
.center[[Insert into W3](https://www.w3schools.com/sql/sql_insert.asp)]

```sql
USE highschool;

INSERT INTO teacher (`name`, `surname`) VALUES
  ("Joan", "Puigcerver");

INSERT INTO subject (`acronym`, `title`, `number_students`) VALUES
  ("DBA", "Database Management", 45),
  ("PRG", "Programing", 30);
```

---
# INSERT
- If no colums are specified, all columns from the table will
  be used in the insert statement.
- If a column is skipped, its value will be set to:
  - __DEFAULT__, if specified.
  - Next available value if __AUTO_INCREMENT__ is set.
  - __NULL__, if null values are allowed.
  - An error will occur.

---
# SELECT
Retrieves data from the database.

```sql
SELECT 
  [ALL | DISTINCT | DISTINCTROW]
  column_expr [, column2_expr ...]
  FROM table
    [WHERE condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING aggregate_condition]
```
.center[[SELECT MariaDB](https://mariadb.com/kb/en/select/)]

---
# SELECT: Order
The order that the DBMS exevutes the SELECT statement is:
- Looks at the tables where information is to be extracted.
- Iterates row by row evaluating the desired condition, 
  keeping the rows that evaluate to true and discarding the rest.
- Sorts the result.
- Selects the desired elements (or aggregate functions)

---
# SELECT: Column selction

- Columns to be selected can be specified in the SELECT statement, in the wanted order.
- An asterisk `*` can be used to select all the columns from a table.

---
# SELECT: FROM

__FROM__ specifies the table or tables which the data is retrieved from.

```sql
SELECT `acronym`, `title`
FROM subject;
```
```text
+---------+---------------------+
| acronym | title               |
+---------+---------------------+
| DBA     | Database Management |
| PRG     | Programing          |
+---------+---------------------+
```

---
# SELECT: WHERE
.center[[Operands MariaDB](https://mariadb.com/kb/en/built-in-functions/)]

__WHERE__ retrieves the data that met the specified condition.
- Expressions can be a costant, aritmetic expression, null values, ...
```
=, !=, <>, <, <=, >, >=
IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE
```
- Mutiple expressions can be combined with boolean operators:
```
AND, OR and NOT
```
- Parenthesis can be used to force evaluation order.

---
# SELECT: WHERE
```sql
SELECT `acronym`, `title`
FROM subject
WHERE `acronym` = "DBA";
```
```text
+---------+---------------------+
| acronym | title               |
+---------+---------------------+
| DBA     | Database Management |
+---------+---------------------+
```

---
# SELECT: ORDER BY
__ORDER BY__ specifies the ordering criteria of the result.
- __ASC__: Ascending order. Default.
- __DESC__: Descending order .

Multiple columns can be used. First specified column will be used to sort the data; 
if they are the same, then the second specified column will be used, an so on.

```sql
ORDER BY column_expr [ASC | DESC] [, column2_expr [ASC | DESC] ...]
```

---
# SELECT: ORDER BY

```sql
SELECT `name`, `age`, `height`
FROM person,
ORDER BY `age` ASC, `height` DESC;
```
```text
+-------+-----+--------+
| name  | age | height |
+-------+-----+--------+
| Pere  |  18 |    182 |
| Carla |  18 |    171 |
| Martí |  19 |    170 |
| Carme |  20 |    168 |
| Laura |  20 |    157 |
+-------+-----+--------+
```

---
# SELECT: ALL | DISTINCT
- __ALL__: Duplicate rows can appear in the select statement. Default value.
- __DISTINCT__: Removes duplicate rows.

```

---
# Column and table alias
Aliases can be set to columns or tables.

```
-- AS is optional
SELECT avg(e.grade) AS average_grade
FROM enrolls AS e
WHERE e.DNI_student = "00000000A";
```

```text
+----------------+
| average_grade  |
+----------------+
|          8.17  |
+----------------+
```

---
# Arithmetic operators
Arithmetic operator can be used anywhere in the SELECT statement, specially in the SELECT or WHERE clause.

The basic arithmetic operators are:
```
+ -- Addition
- -- Substract
* -- Multiplication
/ -- Division
DIV -- Integer division
% or MOD -- Modulus
```

.center[[Arithmetic Operators MariaDB](https://mariadb.com/kb/en/arithmetic-operators/)]

---
# Comparison operators
Comparison operator can be used to check conditions in the WHERE statement

```
A = B -- Equal
A != B -- Not equal
A < -- Less than
A <= B -- Less or equal
A > B -- Greater than
A >= B -- Greater or equal
A BETWEEN B AND C -- B <= A <= C
A NOT BETWEEN B AND C -- Not between
A IS NULL -- Checks if null
A IS NOT NULL -- Checks if not null
A IN (B, C, ...) -- Checks if A is in the set of values
A NOT IN (B, C, ...) -- Checks if A is not in the set of values
```

.center[[Comparison Operators MariaDB](https://mariadb.com/kb/en/comparison-operators/)]

---
# Logical operators
Logical operators can be used to combine multiple conditions

```
NOT A -- Negates the A condition FALSE <=> TRUE
! A   -- Negates the A condition FALSE <=> TRUE
A AND B -- Both A and B must be TRUE
A && B  -- Both A and B must be TRUE
A OR B -- At least A or B has to be TRUE
A || B -- At least A or B has to be TRUE
A XOR B -- A or B has to be TRUE, not any nor both
```

.center[[Logical Operators MariaDB](https://mariadb.com/kb/en/logical-operators/)]

---
# String comparison operators
Previous comparison operators can be used in Strings too, but there are others.

.center[[String funtions MariaDB](https://mariadb.com/kb/en/string-functions/)]
.center[[LIKE MariaDB](https://mariadb.com/kb/en/like/)]

- __LIKE__: Is used to check if a string matches a specific pattern.
  - __%__: Is used to match any sequence of characters. It can be empty.
  - __\___: Marches a single character.

```
SELECT name FROM student WHERE name LIKE "_a%";
```