{"id":1064,"date":"2021-04-07T17:48:58","date_gmt":"2021-04-07T17:48:58","guid":{"rendered":"https:\/\/codeinsightacademy.com\/blog\/?p=1064"},"modified":"2022-08-03T09:30:29","modified_gmt":"2022-08-03T09:30:29","slug":"sql-cheat-sheet","status":"publish","type":"post","link":"https:\/\/codeinsightacademy.com\/blog\/database\/sql-cheat-sheet\/","title":{"rendered":"SQL Cheat Sheet"},"content":{"rendered":"\n

login to database<\/p>\n\n\n\n

mysql -u username -p'password' database_name<\/code><\/pre>\n\n\n\n
logout from database<\/p>\n\n\n\n
Ctrl + D<\/code><\/pre>\n\n\n\n
list all database<\/p>\n\n\n\n
SHOW DATABASES;<\/code><\/pre>\n\n\n\n
Create Database<\/p>\n\n\n\n
CREATE DATABASE company;<\/code><\/pre>\n\n\n\n
enter database<\/p>\n\n\n\n
USE database_name;<\/code><\/pre>\n\n\n\n
show current database (dual is dummy\/virtual database provided by oracle) <\/p>\n\n\n\n
SELECT DATABASE() FROM dual;<\/code><\/pre>\n\n\n\n
list all tables<\/p>\n\n\n\n
SHOW TABLES;<\/code><\/pre>\n\n\n\n
list table pattern<\/p>\n\n\n\n
SHOW TABLES LIKE '%table_substring%';<\/code><\/pre>\n\n\n\n
Create table<\/p>\n\n\n\n
CREATE TABLE users(\n    id INT,\n    NAME VARCHAR(100) NOT NULL,\n    age TINYINT NOT NULL,\n    city VARCHAR(200) NOT NULL\n);<\/code><\/pre>\n\n\n\n
Alter table<\/p>\n\n\n\n
ALTER TABLE\n    users MODIFY id INT PRIMARY KEY AUTO_INCREMENT;<\/code><\/pre>\n\n\n\n
ALTER TABLE\n    users ADD added_at DATETIME AFTER `city`,\n    ADD updated_at DATETIME AFTER added_at;<\/code><\/pre>\n\n\n\n
show schema<\/p>\n\n\n\n
DESC <table_name>;<\/code><\/pre>\n\n\n\n
SHOW CREATE TABLE <table_name>\\G;<\/code><\/pre>\n\n\n\n
show running sql processes<\/p>\n\n\n\n
SHOW FULL PROCESSLIST;<\/code><\/pre>\n\n\n\n
import database\/table (RUN IN BASH TERMINAL)
NOTE: Make sure your database is present in mysql server if not create new one<\/strong><\/p>\n\n\n\n
mysql -u root -p'password' database_name < backup_file.sql<\/code><\/pre>\n\n\n\n
export database (all tables) (RUN IN BASH TERMINAL)<\/p>\n\n\n\n
mysqldump -u root -p'password' database_name > backup_file.sql<\/code><\/pre>\n\n\n\n
export specific tables (RUN IN BASH TERMINAL)<\/p>\n\n\n\n
mysqldump -u root -p'password' database_name tbl1 tbl2 tbl3 > backup_file.sql<\/code><\/pre>\n\n\n\n
export only schema without data<\/p>\n\n\n\n
mysqldump -u root -p'password' database_name --no-data<\/strong> <\/code><\/pre>\n\n\n\n
run sql command in terminal<\/p>\n\n\n\n
mysql -u root -p'password' -e \"SELECT COUNT(*) FROM database_name.table_name\"<\/code><\/pre>\n\n\n\n
copy table<\/p>\n\n\n\n
CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name;<\/code><\/pre>\n\n\n\n
copy only table structure<\/p>\n\n\n\n
CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name WHERE 1 > 2;<\/code><\/pre>\n\n\n\n
Create new database user<\/p>\n\n\n\n
CREATE USER 'user'@'hostname' IDENTIFIED BY 'PassWord';<\/code><\/pre>\n\n\n\n
To give remote access <\/p>\n\n\n\n
GRANT ALL ON database_name.* to 'database_username'@'10.24.96.%' IDENTIFIED BY 'database_password';<\/code><\/pre>\n\n\n\n
CRUD SQL<\/p>\n\n\n\n
SELECT * FROM users ORDER BY id DESC;\n\nDELETE FROM users WHERE id = 3;\n\nINSERT INTO users (id, name, age, city, added_at, updated_at) VALUES (NULL, 'sonam gupta', 18, 'gorakhpur', NOW(), NOW());\n\nUPDATE users SET name = 'Sonam Gupta', age = 20, city = 'Gorakhpur', updated_at = NOW() WHERE id = 5;<\/code><\/pre>\n\n\n\n
Ref Links:<\/p>\n\n\n\n