MySQL

MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

The MySQL command-line tool.

More information: https://www.mysql.com/.

# Connect to a database:
mysql database_name

# Connect to a database, user will be prompted for a password:
mysql -u user --password database_name

# Connect to a database on another host
mysql -h database_host database_name

# Connect to a database through a Unix socket
mysql --socket pasocket.sock

# Execute SQL statements in a script file (batch file)
mysql -e "source filename.sql" database_name

# Restore a database from a backup created with mysqldump (user will be prompted for a password)
mysql --user user --password database_name < pabackup.sql

# Restore all databases from a backup (user will be prompted for a password)
mysql --user user --password < pabackup.sql

Cheatsheets

Quick cheatsheat for doing mysql query for CRUD operation.

Create Database

CREATE DATABASE db_name;

List all databases

SHOW DATABASES;

Select Database

You need to select data base before you working on the data manipulation.

use db_name;

List all tables

SHOW TABLES;

Create Table

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Foreign key

Create table with foreign key

CREATE TABLE IF NOT EXISTS forum(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	created_by BIGINT NOT NULL,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	FOREIGN KEY (created_by) REFERENCES users(id)
);

Update table add foreign key

ALTER TABLE forum ADD FOREIGN KEY(created_by) REFERENCES users(id);

Delete foreign key

ALTER TABLE forum DROP FOREIGN KEY forum_ibfk_1;

To get foreign key.

SHOW CREATE TABLE forum;

Show create table query

SHOW CREATE TABLE users;

Edit table

Add column.

ALTER TABLE users ADD COLUMN verified boolean;

Delete column.

ALTER TABLE users DROP COLUMN verified;

Add primary key.

ALTER TABLE users ADD PRIMARY KEY (email);

Insert

INSERT INTO users(first_name, last_name, email) 
VALUES ("Ahmad","Rosid","sample@mail.com"), ("Duman","Doe","test@mail.com");

Update

UPDATE users SET email="test@mail.com" WHERE id = 1;

Delete

Delete row in table.

DELETE FROM users WHERE id = 1;

Delete table.

DROP TABLE users;

Delete database.

DROP DATABASE db_name;

Query Select Data

Get all data.

SELECT * FROM users;

Get data by id.

SELECT * FROM users WHERE id=1;

Get data by multiple id.

SELECT * FROM users WHERE id in(1,2);

Select unique row.

SELECT DISTINCT email from users;

Count unique field.

SELECT COUNT(DISTINCT email) as unique_email from users;