🏷️Mysql

The Basics

Installation

sudo apt install mysql-server -y

Connection

Local mysql

sudo mysql -u student -p

Remote mysql connection

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

1. find bind-address

2. change to bind-address = 0.0.0.0

sudo systemctl restart mysql

Common sql

User

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

CREATE DATABASE learn CHARACTER SET utf8;

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

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

FLUSH PRIVILEGES;

Check users and change passwords

SELECT USER(), CURRENT_USER();

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

Tables

SHOW FULL TABLES;

Foreign key

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

Joins

Joins

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

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

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

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;

Multiple Joins

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;

Filtering aggregated data with Joins

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;

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.