SQL Cheat Sheet

login to database

mysql -u username -p'password' database_name

logout from database

Ctrl + D

list all database

SHOW DATABASES;

Create Database

CREATE DATABASE company;

enter database

USE database_name;

show current database (dual is dummy/virtual database provided by oracle)

SELECT DATABASE() FROM dual;

list all tables

SHOW TABLES;

list table pattern

SHOW TABLES LIKE '%table_substring%';

Create table

CREATE TABLE users(
    id INT,
    NAME VARCHAR(100) NOT NULL,
    age TINYINT NOT NULL,
    city VARCHAR(200) NOT NULL
);

Alter table

ALTER TABLE
    users MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE
    users ADD added_at DATETIME AFTER `city`,
    ADD updated_at DATETIME AFTER added_at;

show schema

DESC <table_name>;

SHOW CREATE TABLE <table_name>\G;

show running sql processes

SHOW FULL PROCESSLIST;

import database/table (RUN IN BASH TERMINAL)
NOTE: Make sure your database is present in mysql server if not create new one

mysql -u root -p'password' database_name < backup_file.sql

export database (all tables) (RUN IN BASH TERMINAL)

mysqldump -u root -p'password' database_name > backup_file.sql

export specific tables (RUN IN BASH TERMINAL)

mysqldump -u root -p'password' database_name tbl1 tbl2 tbl3 > backup_file.sql

export only schema without data

mysqldump -u root -p'password' database_name --no-data 

run sql command in terminal

mysql -u root -p'password' -e "SELECT COUNT(*) FROM database_name.table_name"

copy table

CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name;

copy only table structure

CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name WHERE 1 > 2;

Create new database user

CREATE USER 'user'@'hostname' IDENTIFIED BY 'PassWord';

To give remote access

GRANT ALL ON database_name.* to 'database_username'@'10.24.96.%' IDENTIFIED BY 'database_password';

CRUD SQL

SELECT * FROM users ORDER BY id DESC;

DELETE FROM users WHERE id = 3;

INSERT INTO users (id, name, age, city, added_at, updated_at) VALUES (NULL, 'sonam gupta', 18, 'gorakhpur', NOW(), NOW());

UPDATE users SET name = 'Sonam Gupta', age = 20, city = 'Gorakhpur', updated_at = NOW() WHERE id = 5;

Ref Links:

• https://www.tecmint.com/fix-error-1130-hy000-host-not-allowed-to-connect-mysql/