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 path/to/socket.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 < path/to/backup.sql
# Restore all databases from a backup (user will be prompted for a password)
mysql --user user --password < path/to/backup.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;