This note is help for manage your database.

4/10/2020 - 9:02 PM
This note is help for manage your database.

//Start MySql in terminal
mysql-ctl cli

//Create Database
CREATE DATABASE test_db;

//List available databases
show databases;

//Delete Or Drop a database
DROP DATABASE <database_name>;
DROP DATABASE IF EXISTS book_shop;

//User Database for work
USE <database name>;

// See which Database we are using
SELECT database();

//Create Table
CREATE TABLE tablename(
	column_name data_type,
	........
);

//Show all table
SHOW TABLES;

//show all colums in table
SHOW COLUMNS FROM <tablename>;
	Or
DESC tablename;

//Dropping Tables
DROP TABLE <tablename>; 

//Insert data into table
INSERT INTO table_name(collumn_name) VALUES (data);
Ex: INSERT INTO cats(name, age) VALUES ('Jetson', 7);

//View table data
SELECT * FROM <table_name>;

//Insert Multiple data into table
INSERT INTO table_name 
            (column_name, column_name) 
VALUES      (value, value), 
            (value, value), 
            (value, value);

//Using quota in a stirng value
"This text has \"quotes\" in it" 
			or 
'This text has \'quotes\' in it'

//see your warning
SHOW WARNINGS;

//If we insert blank value in table then the table set the default value is NULL(if we doesn't use NOT NULL)
// If use NOT NULL the set default value 0 or empty
CREATE TABLE cats2
  (
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
  );

//set default value in table
CREATE TABLE cats3
  (
    name VARCHAR(20) DEFAULT 'no name provided',
    age INT DEFAULT 99
  );

//If we want don't set Null value in table
CREATE TABLE cats4
  (
    name VARCHAR(20) NOT NULL DEFAULT 'unnamed',
    age INT NOT NULL DEFAULT 99
  );

//Set Primary Key in table
CREATE TABLE unique_cats
  (
    cat_id INT NOT NULL,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
  );

//Set primary key and auto increment
CREATE TABLE unique_cats2 (
    cat_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);

//set primary key in the same line
CREATE TABLE employees (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    current_status VARCHAR(255) NOT NULL DEFAULT 'employed'
);

//ON UPDATE
CREATE TABLE comments2 (
    content VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);
UPDATE comments2 SET content='THIS IS NOT GIBBERISH' WHERE content='dasdasdasd';

//see specific column of a table
//it will show data depend on command order
SELECT age FROM cats;
SELECT age, breed, name, cat_id FROM cats;

//WHERE is use for show or find a specific data
SELECT age FROM cats;
SELECT cat_id, age FROM cats WHERE cat_id=age;

//Aliases - show table name using another name
SELECT name AS 'cat name', breed AS 'kitty breed' FROM cats;

// Update table data
UPDATE < table_name > SET breed='Shorthair' WHERE breed='Tabby';
UPDATE shirts SET color='off white', shirt_size='XS' WHERE color='white';

//Delete all data in a table
DELETE FROM < table_name >;

//Delete specific data in a table
DELETE FROM cats WHERE name='egg';

******************
//String Function//

//CONCAT
SELECT CONCAT(author_fname, ' ', author_lname) FROM books;
SELECT CONCAT(author_fname, ' ', author_lname) AS 'full name' FROM books;
SELECT author_fname AS first, author_lname AS last, CONCAT(author_fname, ', ', author_lname) AS full FROM books;

//CONCAT_WS -> it will join all table element with first stirng
SELECT CONCAT_WS(' - ', title, author_fname, author_lname) FROM books;

//SUBSTRING or SUBSTR
SELECT SUBSTRING('Hello World', 1, 4);  //Hell
SELECT SUBSTRING('Hello World', 7);     //World
SELECT SUBSTRING('Hello World', -3);    //rld
SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
SELECT CONCAT ( SUBSTRING(title, 1, 10), '...' ) AS 'short title' FROM books;

//REPLACE -> it is case sensitive
SELECT REPLACE(title, 'e ', '3') FROM books;  //titl3
SELECT
    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string'
FROM books;

//REVERSE
SELECT REVERSE(author_fname) FROM books;
SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

//CHAR_LENGTH
SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;
SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;

//UPPER() and LOWER() -> it takes one argument
SELECT LOWER(title) FROM books;
SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
SELECT CONCAT(UPPER(author_fname), ' ', UPPER(author_lname)) AS "full name in caps" 
FROM books;

***********
// DISTINCT
SELECT DISTINCT author_lname FROM books;
SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;
SELECT DISTINCT author_fname, author_lname FROM books;

//ORDER -> Sorting data
SELECT author_lname FROM books ORDER BY author_lname;
SELECT author_lname FROM books ORDER BY author_lname DESC;
SELECT released_year FROM books ORDER BY released_year ASC;
SELECT title, author_fname, author_lname FROM books ORDER BY 2; //-> 2 means author_fname
SELECT title, author_fname, author_lname FROM books ORDER BY 1 DESC; //-> 1 means title
SELECT author_fname, author_lname FROM books ORDER BY author_lname, author_fname; // Fisrt sort by author_lname then author_fname

//LIMIT
SELECT title FROM books LIMIT 10;
SELECT title, released_year FROM books 
ORDER BY released_year DESC LIMIT 5;
SELECT title, released_year FROM books 
ORDER BY released_year DESC LIMIT 0,5; //start 0 then 5 row shows

//LIKE -> searching. it not case sensitive
SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%'
SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '__'; //Last 2 underscore means stock quantity has 2 digit.
SELECT title FROM books WHERE title LIKE '%\%%'
SELECT title FROM books WHERE title LIKE '%\_%'
(235)234-0987 LIKE '(___)___-____'
SELECT title FROM books WHERE title LIKE 'W%'; start with 'W';
SELECT title FROM books WHERE title NOT LIKE 'W%';

//COUNT
SELECT COUNT(*) FROM books;
SELECT COUNT(DISTINCT author_lname, author_fname) FROM books;
SELECT title FROM books WHERE title LIKE '%the%';
SELECT COUNT(author_fname) FROM books;

//GROUP
SELECT title, author_lname FROM books GROUP BY author_lname;
SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;
SELECT author_fname, author_lname, COUNT(*) FROM books GROUP BY author_lname, author_fname;
SELECT CONCAT('In ', released_year, ' ', COUNT(*), ' book(s) released') AS year FROM books GROUP BY released_year;

//MIN & MAX
SELECT MIN(released_year) FROM books;
SELECT title, pages FROM books WHERE pages = (SELECT Min(pages) FROM books); 
SELECT CONCAT(author_fname, ' ', author_lname) AS author,
  MAX(pages) AS 'longest book' FROM books
GROUP BY author_lname, author_fname;

//SUM
SELECT SUM(released_year) FROM books;
SELECT author_fname, author_lname, Sum(pages)
FROM books GROUP BY author_lname, author_fname;

//AVG
SELECT AVG(pages) FROM books;
SELECT released_year, AVG(stock_quantity) FROM books GROUP BY released_year;

//Data Types
CHAR
VARCHAR
DECIMAL(5,2) -> 15 digits
FLOAT        -> 07 digits
DATE         -> YYYY-MM-DD
TIME         -> HH:MM:SS
DATETIME     -> YYYY-MM-DD HH:MM:SS
TIMESTAMP

// Timing Function
CURDATE(), CURTIME(), NOW()
SELECT name, birthdate FROM people;
SELECT name, DAY(birthdate) FROM people;
SELECT name, birthdate, DAY(birthdate) FROM people;
SELECT name, birthdate, DAYNAME(birthdate) FROM people;
SELECT name, birthdate, DAYOFWEEK(birthdate) FROM people;
SELECT name, birthdate, DAYOFYEAR(birthdate) FROM people;
SELECT name, birthtime, DAYOFYEAR(birthtime) FROM people;
SELECT name, birthdt, DAYOFYEAR(birthdt) FROM people;
SELECT name, birthdt, MONTH(birthdt) FROM people;
SELECT name, birthdt, MONTHNAME(birthdt) FROM people;
SELECT name, birthtime, HOUR(birthtime) FROM people;
SELECT name, birthtime, MINUTE(birthtime) FROM people;
SELECT CONCAT(MONTHNAME(birthdate), ' ', DAY(birthdate), ' ', YEAR(birthdate)) FROM people;
SELECT DATE_FORMAT(birthdt, 'Was born on a %W') FROM people;
SELECT DATE_FORMAT(birthdt, '%m/%d/%Y') FROM people;
SELECT DATE_FORMAT(birthdt, '%m/%d/%Y at %h:%m') FROM people;

SELECT DATEDIFF(NOW(), birthdate) FROM people;
SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people;
SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people;
SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people;
SELECT birthdt, birthdt + INTERVAL 15 MONTH + INTERVAL 10 HOUR FROM people;

//AND and OR
SELECT title, author_lname, released_year FROM books
WHERE author_lname='Eggers' AND released_year > 2010;
SELECT * FROM books WHERE author_lname='Eggers' 
    AND released_year > 2010 AND title LIKE '%novel%';
SELECT title, author_lname, released_year, stock_quantity 
FROM books WHERE author_lname = 'Eggers' || released_year > 2010 
	OR stock_quantity > 100;

//BETWEEN
SELECT title, released_year FROM books WHERE released_year BETWEEN 2004 AND 2015;
SELECT name, birthdt FROM people WHERE birthdt BETWEEN CAST('1980-01-01' AS DATETIME)
    AND CAST('2000-01-01' AS DATETIME);

//IN and NOT In -> it works as OR operator
SELECT title, author_lname FROM books WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');
SELECT title, released_year FROM books WHERE released_year NOT IN 
	(2000,2002,2004,2006,2008,2010,2012,2014,2016);

//CASE
SELECT title, released_year, CASE 
	WHEN released_year >= 2000 THEN 'Modern Lit'
         ELSE '20th Century Lit'
       END AS GENRE
FROM books;

SELECT title, stock_quantity,
    CASE 
        WHEN stock_quantity BETWEEN 0 AND 50 THEN '*'
        WHEN stock_quantity BETWEEN 51 AND 100 THEN '**'
        WHEN stock_quantity BETWEEN 101 AND 150 THEN '***'
        ELSE '****'
    END AS STOCK
FROM books;

SELECT author_fname, author_lname,
    CASE 
        WHEN COUNT(*) = 1 THEN '1 book'
        ELSE CONCAT(COUNT(*), ' books')
    END AS COUNT
FROM books 
GROUP BY author_lname, author_fname;

// Foreign Key
CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(id)
);

//Inner Join
--IMPLICIT INNER JOIN
SELECT first_name, last_name, order_date, amount
FROM customers, orders 
    WHERE customers.id = orders.customer_id;

SELECT genre, 
       Round(Avg(rating), 2) AS avg_rating 
FROM   series 
       INNER JOIN reviews 
               ON series.id = reviews.series_id 
GROUP  BY genre; 
	
--EXPLICIT INNER JOIN
SELECT first_name, last_name, order_date, amount 
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
ORDER BY amount;

//Left Join
SELECT 
    first_name, 
    last_name,
    IFNULL(SUM(amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
    ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent;

//Right Join
SELECT 
    IFNULL(first_name,'MISSING') AS first, 
    IFNULL(last_name,'USER') as last, 
    order_date, 
    amount, 
    SUM(amount)
FROM customers
RIGHT JOIN orders
    ON customers.id = orders.customer_id
GROUP BY first_name, last_name;

//ON DELETE CASCADE -> when we delete user then delete order
CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY(customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE
);

//Join 
SELECT first_name, 
       last_name, 
       Count(rating)                    AS COUNT, 
       Ifnull(Min(rating), 0)           AS MIN, 
       Ifnull(Max(rating), 0)           AS MAX, 
       Round(Ifnull(Avg(rating), 0), 2) AS AVG, 
       CASE 
         WHEN Count(rating) >= 10 THEN 'POWER USER' 
         WHEN Count(rating) > 0 THEN 'ACTIVE' 
         ELSE 'INACTIVE' 
       end                              AS STATUS 
FROM   reviewers 
       LEFT JOIN reviews 
              ON reviewers.id = reviews.reviewer_id 
GROUP  BY reviewers.id; 

//3 table to 1 table
SELECT 
    title,
    rating,
    CONCAT(first_name,' ', last_name) AS reviewer
FROM reviewers
INNER JOIN reviews 
    ON reviewers.id = reviews.reviewer_id
INNER JOIN series
    ON series.id = reviews.series_id
ORDER BY title;

//Clone Instragram Database

CREATE DATABASE ig_clone;
USE ig_clone;

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photos (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    image_url VARCHAR(255) NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE comments (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    comment_text VARCHAR(255) NOT NULL,
    photo_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE likes (
    user_id INTEGER NOT NULL,
    photo_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    PRIMARY KEY(user_id, photo_id)
);

CREATE TABLE follows (
    follower_id INTEGER NOT NULL,
    followee_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

CREATE TABLE tags (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  tag_name VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photo_tags (
    photo_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(tag_id) REFERENCES tags(id),
    PRIMARY KEY(photo_id, tag_id)
);

//ig_clone query
SELECT 
    DAYNAME(created_at) AS day,
    COUNT(*) AS total
FROM users GROUP BY day ORDER BY total DESC LIMIT 2;

//Identify which users with no photos upload
SELECT username FROM users LEFT 
JOIN photos
    ON users.id = photos.user_id 
WHERE photos.id IS NULL;

//Indetify users who's photo get most like's
SELECT 
    username, photos.id, photos.image_url, COUNT(*) AS total
FROM photos
INNER JOIN likes
    ON likes.photo_id = photos.id
INNER JOIN users
    ON photos.user_id = users.id
GROUP BY photos.id ORDER BY total DESC LIMIT 1;

//Calculate average number of photos per user
SELECT (SELECT Count(*) FROM photos) / (SELECT Count(*) FROM users) AS avg; 

//Find the five most popular hashtags
SELECT tags.tag_name, Count(*) AS total 
FROM   photo_tags 
       JOIN tags 
         ON photo_tags.tag_id = tags.id 
GROUP  BY tags.id ORDER  BY total DESC LIMIT  5; 

//Finding the users who have liked every single photo
SELECT username, Count(*) AS num_likes 
FROM   users 
       INNER JOIN likes 
               ON users.id = likes.user_id 
GROUP  BY likes.user_id 
HAVING num_likes = (SELECT Count(*) FROM   photos);
Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

This note is help for manage your database.
