# Mysql

## The Basics

<details>

<summary>Installation</summary>

```shell
sudo apt install mysql-server -y
```

</details>

### Connection

<details>

<summary>Local mysql </summary>

```shell
sudo mysql -u student -p
```

</details>

<details>

<summary>Remote mysql connection</summary>

```shell
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
```

1. find `bind-address`
2. change to `bind-address = 0.0.0.0`

```shell
sudo systemctl restart mysql
```

</details>

### Common sql

<details>

<summary>User</summary>

Db name is `learn`, and user is `student` with password `STUDENT`

```sql
CREATE DATABASE learn CHARACTER SET utf8;
```

```sql
CREATE USER 'student'@'%' IDENTIFIED BY 'STUDENT';
```

```sql
GRANT ALL PRIVILEGES ON learn.* TO 'student'@'%' WITH GRANT OPTION;
```

```sql
FLUSH PRIVILEGES;
```

Check users and change passwords

<pre class="language-sql"><code class="lang-sql"><strong>SELECT USER(), CURRENT_USER();
</strong></code></pre>

```sql
SET PASSWORD FOR 'student'@'%' = 'newpassword';
FLUSH PRIVILEGES;
```

</details>

<details>

<summary>Tables</summary>

```sql
SHOW FULL TABLES;
```

</details>

<details>

<summary>Foreign key</summary>

```sql
 ALTER TABLE comments 
 ADD FOREIGN KEY (post_id) REFERENCES posts (id) 
 ON DELETE CASCADE 
 ON UPDATE CASCADE;
```

</details>

### Joins

<details>

<summary>Joins</summary>

```sql
SELECT *
FROM store
JOIN address ON store.address_id = address.address_id;
```

```sql
SELECT *
FROM store
RIGHT JOIN address ON store.address_id = address.address_id;
```

```sql
SELECT *
FROM store
LEFT JOIN address ON store.address_id = address.address_id;
```

```sql
SELECT 
    customer.customer_id,
    customer.first_name,
    customer.last_name,
    COUNT(rental_id)
FROM customer
LEFT JOIN rental ON rental.customer_id = customer.customer_id
GROUP BY customer.customer_id;
```

</details>

<details>

<summary>Multiple Joins</summary>

```sql
SELECT c.customer_id,
       c.first_name,
       c.last_name,
       store.store_id,
       COUNT(rental_id) as rentals_checked_out,
       address.address as store_adress
FROM customer c
         LEFT JOIN rental ON rental.customer_id = c.customer_id
         LEFT JOIN store ON store.store_id = c.store_id
         LEFT JOIN address ON address.address_id = store.address_id
GROUP BY c.customer_id, address.address;
```

</details>

<details>

<summary>Filtering aggregated data with Joins</summary>

```sql
SELECT title, SUM(amount) sales, COUNT(*) rentals
FROM rental
         JOIN payment ON payment.rental_id = rental.rental_id
         JOIN inventory ON inventory.inventory_id = rental.inventory_id
         JOIN film ON film.film_id = inventory.film_id
GROUP BY title
HAVING sales > 200
ORDER BY sales DESC;
```

</details>

### Indexing

* Indexing can speed up your query when you have many records into your db.
* If you use `where user_id = 1729`, you can add index for `user_id`.
* If you use `published_at BETWEEN '2020-01-01 00:00:00' AND '2022-01-01 00:00:00'`, you can add index to `published_at` for reduce a query.
