SQL Cheat Sheet

by

April 7, 2021

5:48 pm

176 Comments on SQL Cheat Sheet

Database

,

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:

176 Replies to “SQL Cheat Sheet”

  4. the best adult generator adult fantasy ai chat create erotic videos, images, and virtual characters. flexible settings, high quality, instant results, and easy operation right in your browser. the best features for porn generation.

    Reply

  5. the best adult generator pornjourney nsfw create erotic videos, images, and virtual characters. flexible settings, high quality, instant results, and easy operation right in your browser. the best features for porn generation.

    Reply

  15. Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

    Reply

  103. Услуги по настройке https://sysadmin.guru и администрированию серверов и компьютеров. Установка систем, настройка сетей, обслуживание серверной инфраструктуры, защита данных и техническая поддержка. Помогаем обеспечить стабильную работу IT-систем.

    Reply

Leave a Reply to Buster Bendall Cancel reply

Your email address will not be published.