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.

Data Definition Language (DDL)

Data Definition Language (DDL) SQL is a language used to define the structure of a relational database, including creating, modifying, and deleting tables, views, indexes, and other database objects. It can also be used to define the relationships between these objects. DDL SQL is part of the Structured Query Language (SQL) that is used to manage data in a relational database management system (RDBMS).

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;