/mysql-db

mysql run on terminal

Note: Add this line on ~/.zshrc OR ~/.bash_profile File

By Running

open ~/.zshrc

open ~/.bash_profile
export PATH=${PATH}:/usr/local/mysql/bin

mysql -u root -p

After that enter password

Start databse CLI

mysql-ctl cli

Create a database

CREATE DATABASE database_name;

Show database

SHOW DATABASES;

Delete database

DROP DATABASE database_name;

Use databae

USE database_name;

Check current database

SELECT DATABASE();

Create a table

CREATE TABLE table_name (column_name data_type, column_name data_type);
CREATE TABLE books (first_name VARCHAR(10), quantity INT);

Show tables

SHOW TABLES;

Show columns from tables OR Descibe tables

SHOW COLUMNS FROM table_name;
DESC table_name;

Delete table

DROP TABLE table_name;

Insert data into table

INSERT INTO table_name(column_name1, column_name2) VALUES(value1, value2);

INSERT INTO table_name(name, column_name2) VALUES('mario\'s', value2);

View data from table

SELECT * FROM database_name.table_name;
SELECT * FROM table_name;

View data from table particular column

SELECT column_name1, column_name2 FROM table_name;
SELECT name, age FROM cats;

WHERE clause (Get data from table with condition)

SELECT * FROM table_name WHERE column_name1 = value1;
SELECT * FROM cats WHERE age = 4;

Aliases (Give a name to a column)

SELECT column_name1 AS alias_name1, column_name2 AS alias_name2 FROM table_name;
SELECT cat_id AS id, name AS cat_name FROM cats;

Multiple insert data into table

INSERT INTO table_name(column_name1, column_name2) VALUES(value1, value2), (value1, value2);

Show warnings

SHOW WARNINGS;

Prevent null values insert into table

CREATE TABLE table_name (column_name1 data_type1 NOT NULL, column_name2 data_type2 NOT NULL);

Default value insert into table

CREATE TABLE table_name (column_name1 data_type1 DEFAULT default_value1, column_name2 data_type2 DEFAULT default_value2);

Prevent null and default value insert into table

CREATE TABLE table_name (column_name1 data_type1 NOT NULL DEFAULT default_value1, column_name2 data_type2 NOT NULL DEFAULT default_value2);

Primer on Primary Key

CREATE TABLE table_name (column_name1 data_type1 PRIMARY KEY, column_name2 data_type2);
CREATE TABLE person (person_id INT NOT NULL, name VARCHAR(50), age INT, PRIMARY KEY (person_id));

Auto increment primary key

CREATE TABLE table_name (column_name1 data_type1 AUTO_INCREMENT, column_name2 data_type2);
CREATE TABLE person (person_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), age INT, PRIMARY KEY (person_id));
INSERT INTO person (name, age) VALUES ('John', 20);

UPDATE data in table

UPDATE table_name SET column_name1 = value1 WHERE column_name2 = value2;
UPDATE cats SET breed = 'shorthair' WHERE breed = 'Tabby';

DELETE data from table

DELETE FROM table_name WHERE column_name1 = value1;
DELETE FROM cats WHERE breed = 'Tabby';

CONCAT String in SQL

SELECT CONCAT(column_name1, column_name2) FROM table_name AS alias_name;
SELECT CONCAT(firstname, lastname) FROM authors AS fullname;

CONCAT_WS in SQL

Note: First paramete is seprator that is seprate between column

SELECT CONCAT_WS(' - ', column_name1, column_name2, column_name3) FROM table_name;

SUBSTRING in SQL

Note: SUBSTR() is synonyms of SUBSTRING

SELECT SUBSTRING(column_name1, 1, 2) FROM table_name;
SELECT SUBSTRING('Hello World', 1, 4) FROM table_name;           // Output: Hell
SELECT SUBSTRING('Hello World', 5) FROM table_name;           // Output: o World
SELECT SUBSTRING('Hello World', 7, 2) FROM table_name;           // Output: Wo
SELECT SUBSTRING('Hello World', -3) FROM table_name;           // Output: rld

REPLACE in SQL

SELECT REPLACE(column_name1, 'old', 'new') FROM table_name;
SELECT REPLACE('Hello World', 'Hello', 'Hey') FROM table_name;

REVERSE in SQL

SELECT REVERSE(column_name1) FROM table_name;
SELECT REVERSE('Hello World') FROM table_name;

SELECT REVERSE('Hello World');

CHAR Length in SQL

SELECT CHAR_LENGHT(column_name);

UPPERCASE in SQL

SELECT UPPER(column_name);

LOWERCASE in SQL

SELECT LOWER(column_name);

Insert within character in SQL

SELECT INSERT('Hello World', 6, 0, ' there ');           // Output: Hell0 there World
SELECT INSERT('Hello World', 4, 4, ' there ');           // Output: Hell there orld

Return Left Most character in SQL

SELECT LEFT('foobar', 5);               // Output: fooba

Return Right Most character in SQL

SELECT RIGHT('foobar', 5);               // Output: oobar

Same string repeat in number of times in SQL

SELECT REPEAT('Hello ', 3);               // Output: Hello Hello Hello

TRIM in SQL

Note: Remove all remstr from prefix or suffix in string

SELECT TRIM('  Hello  ');               // Output: Hello
SELECT TRIM(LEADING '.' FROM '.....Hello...');               // Output: Hello...
SELECT TRIM(TRAILING '.' FROM '.....Hello...');               // Output: .....Hello
SELECT TRIM(BOTH '.' FROM '.....Hello...');               // Output: Hello

REMOVE duplicate using DISTINCT in SQL

SELECT DISTINCT first_name, last_name FROM books;
SELECT DISTINCT CONCAT(first_name, ' ', last_name) FROM books;

Sorting Data with ORDER BY in SQL

SELECT first_name, last_name FROM books ORDER BY first_name;
SELECT first_name, last_name FROM books ORDER BY first_name DESC;
SELECT first_name, last_name FROM books ORDER BY 2;

NOTE: 2 is refering last_name

SELECT first_name, last_name FROM books ORDER BY last_name, first_name;
SELECT CONCAT(first_name, ' ', last_name) as username  FROM books ORDER BY username;

LIMIT in SQL

SELECT first_name, last_name FROM books LIMIT 3;
SELECT first_name, last_name FROM books ORDER BY first_name LIMIT 3;
SELECT first_name, last_name FROM books LIMIT 0,5;

Better Search LIKE in SQL

SELECT first_name FROM books WHERE last_name LIKE '%Anm%';

NOTE: \ repersents character

SELECT book_name FROM books WHERE stock_quantity LIKE '__';
SELECT book_name FROM books WHERE stock_quantity LIKE '(___)____-___';

NOTE: search mobile like (123)6545-654

SELECT book_name FROM books WHERE stock_quantity LIKE '%\_%';
SELECT book_name FROM books WHERE stock_quantity LIKE '%\%%';

NOTE: search underscore or percentage

COUNT in SQL

SELECT COUNT(*) FROM books;
SELECT COUNT(first_name) FROM books;
SELECT COUNT(DISTINCT first_name, last_name) FROM books;

NOTE: Count unique name

SELECT COUNT(first_name) FROM books WHERE title LIKE '%the%';

Group By in SQL (Remove duplicate)

SELECT first_name, last_name FROM books GROUP BY author_lname, last_name;
SELECT first_name, COUNT(*) FROM books GROUP BY last_name;

MIN and MAX in SQL

SELECT MIN(released_year) FROM books;
SELECT MAX(released_year) FROM books;

Sub Query

SELECT title FROM books WHERE pages = 632;

Note: Subquery for get MIN number of Pages from books.

SELECT title FROM books WHERE pages = (SELECT Min(pages) FROM books);

NOTE: Find min pages with title

------- OR -------

SELECT title FROM books ORDER BY pages ASC LIMIT 1;
SELECT author_fname, author_lname, COUNT(*) as books_written, Min(released_year) FROM   books GROUP  BY author_lname, author_fname;

NOTE: MIN, MAX with GROUP BY

Sum Function in SQL

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

AVG Function in SQL

SELECT AVG(pages) FROM books;
SELECT author_fname, author_lname, AVG(pages) FROM books GROUP BY author_lname, author_fname;