MySQL

on Mac

brew install mysql
brew services start mysql
mysql -u root # login with user root
# exit ; to exit from the MySQL 
brew services restart mysql # restart the service
brew services stop mysql # stop the service

CREATE DATABASE db_name; 
SHOW DATABASES; # show all databases
USE db_name
CREATE TABLE Users ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL UNIQUE, bio TEXT, country VARCHAR(2)) ;

INSERT INTO Users (email, bio, country) VALUES (
'hello@gmail.com',
'Mango is the best',
'US'
);

# multiple insert
INSERT INTO Users (email, bio, country) 
VALUES ('hola@gmail.com', 'Nice weather', 'BD'), 
('morning@gmail.com', 'Cold', 'JP');

SELECT id, email from Users 
WHERE country = 'US' 
AND id > -1 
AND email LIKE "hello%" 
ORDER BY id ASC LIMIT 2;

# index increase the cost of writing on the database
CREATE INDEX email_index on Users(email); # create index

# Relational tables
CREATE TABLE Rooms( 
id INT AUTO_INCREMENT, 
street VARCHAR(255), 
owner_id INT NOT NULL, 
PRIMARY KEY (id), 
FOREIGN KEY (owner_id) REFERENCES Users(id));

# insert related tables
INSERT INTO Rooms (owner_id, street) VALUES (1, 'san diego sailboat'), (1, 'nantucket cottage'), (1, 'vail cabin'), (1, 'sf cardcoard box');

# LEFT JOIN
SELECT Users.id as user_id, Users.email, 
Rooms.street 
FROM Users LEFT JOIN Rooms 
ON Rooms.owner_id = Users.id;

# Join table
CREATE TABLE Bookings(
    -> id INT AUTO_INCREMENT,
    -> guest_id INT NOT NULL,
    -> room_id INT NOT NULL,
    -> check_in DATETIME,
    -> PRIMARY KEY (id),
    -> FOREIGN KEY (guest_id) REFERENCES Users(id),
    -> FOREIGN KEY (room_id) REFERENCES Rooms(id));

# query on the join table
SELECT guest_id, street, check_in FROM Bookings INNER JOIN Rooms on Rooms.owner_id = guest_id WHERE guest_id = 1;

# delete the table and the database
DROP TABLE Users;
DROP DATABASE airbnb;

# Aggregate function
SELECT count(*) AS COUNT, country  FROM Users GROUP BY country HAVING COUNT(*) > 1;