Sysleaf

SQL command cheatsheet

. .
SQL command cheatsheet

Introduction

MySQL is an open-source relational database where data is stored in a table format. It offers ACID properties for the transaction and ensures data integrity through strict rules and constraints. This post is all about common MySQL command/query for quick reference.

Install, Start & Stop

Installation step will vary from system to system, see this link to know more.

MySQL Component

Description Command
MySQL server mysqld
MySQL client mysql
MySQL client for admin related work mysqladmin
MySQL client for backup related work mysqldump
MySQL client for import related work mysqlimport

Connecting to MySQL

Description Command
connect to mysq server with ‘root’ user, default localhost:3306 mysql -u root
connect to mysq server to ‘test’ database with ‘root’ user mysql -u root test
connect to mysq server if password enabled for ‘root’ user mysql -u root -p
connect to mysql server long version
mysql --host=127.0.0.1 --port=3306 
--user=root --password=123
connect to mysql server short version mysql -h 127.0.0.1 -P 3306 -u root -p
To exit from mysql shell type quit

Database

Description Command
show all database SHOW DATABASES;
show current database SELECT DATABASE();
switch to database ‘test’ USE test;
create database ‘test’ CREATE DATABSE test;
drop database ‘test’ DROP DATABSE test;

User

Description Command
show all the user created in mysql database
SELECT User FROM mysql.user;
create user ‘john’ & ‘vikash’ with password ‘pwd123’
CREATE USER
john@localhost IDENTIFIED BY 'pwd123',
vikash@localhost IDENTIFIED BY 'pwd123';
drop user ‘john’ from mysql database
DROP USER john@localhost;
rename user ‘john’ to ‘vikash’ in mysql database
RENAME USER
johan@localhost TO vikash@localhost;
show currently logged in user details
SELECT USER, HOST, DB
FROM information_schema.processlist;

DDL

Description Command
show all the tables under current database
SHOW TABLES;
show details of table ‘city’
DESCRIBE city;
create table ‘country’, by default ‘MyISAM’ db engine is used
CREATE TABLE country (
  Code CHAR(3) NOT NULL DEFAULT '',
  Name CHAR(52) NOT NULL DEFAULT '',
  Capital INT(11) DEFAULT NULL,
  PRIMARY KEY (Code)
)
create table ‘city’ where column ‘CountryCode’ refer column ‘Code’ of table ‘country’, use InnoDB for foreign key constraint, key is synonym for Index in mysql
CREATE TABLE city (
  ID INT(11) NOT NULL AUTO_INCREMENT,
  Name CHAR(35) NOT NULL DEFAULT '',
  CountryCode CHAR(3) NOT NULL DEFAULT '',
  PRIMARY KEY (ID),
  KEY CountryCode (CountryCode),
  FOREIGN KEY (CountryCode) REFERENCES country (Code)
) ENGINE=InnoDB;
add new column ‘Population’ & ‘GDP’ to table ‘country’
ALTER TABLE country
  ADD Population INT(11) NOT NULL,
  ADD GDP FLOAT(10,2) DEFAULT NULL;
modify column ‘Population’ & ‘GDP’ of table ‘country’
ALTER TABLE country
  MODIFY Population INT(12) NOT NULL,
  MODIFY GDP FLOAT(8,2) DEFAULT NULL;
drop column ‘Population’ of table ‘country’
ALTER TABLE country
  DROP Population;
rename column ‘Population’ to ‘people’ of table ‘country’
ALTER TABLE country
  CHANGE COLUMN Population people;
rename table ‘country’ to ‘nation’
ALTER TABLE country
  RENAME TO nation;
truncate table ‘city’
TRUNCATE TABLE city;
drop table ‘city’
DROP TABLE city;

DML

Description Command
basic select syntax & execution sequence are…
FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT->SELECT
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ... ASC|DESC;
basic update syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
basic delete syntax
DELETE FROM table_name
WHERE condition;
select user form table ‘users’ whose age is greater than 19 but less than 35
SELECT *
FROM users
WHERE age > 19 AND age < 35;
some of the basic WHERE operator i.e
age>=19
age BETWEEN 19 AND 29
email LIKE ‘_@%com’
age IN (18,19,20)
age IS NULL
age IS NOT NULL
=, <>, >, <, >=, <=, 
BETWEEN, 
LIKE, 
IN
IS NULL
IS NOT NULL
combine multiple conditon using
AND, OR, NOT
show average age of user
SELECT AVG(age) as average_age
FROM users;
some of the basic aggregate function
COUNT, SUM, MIN, MAX, AVG
show only 5 record
SELECT * FROM City LIMIT 5; //MySQL
SELECT TOP 5 * FROM City; //MSSQL
SELECT * FROM City WHERE ROWNUM >= 5; //ORACLE
select distinct(unique) value
SELECT DISTINCT Country FROM Customers;
show number of customer from each country
SELECT country, COUNT(customer_id) AS no_of_cust
FROM customers
GROUP BY country
basic syntax of join…
(INNER) JOIN- present in both
LEFT (OUTER) JOIN- present in left and both
RIGHT (OUTER) JOIN- present in right and both
FULL (OUTER) JOIN- present in left or right
SELECT column_name(s)
FROM table1
[INNER/LEFT/RIGHT/FULL] JOIN table2 
ON table1.column_name = table2.column_name;

TCL

Description Command
commit transaction i.e save it in database commit;
rollback all transaction that has not been saved i.e since last commit rollback;
create savepoint with name ‘deletedUser’ to be used for rollback savepoint deletedUser;
restore database to savepoint ‘deletedUser’ rollback to deletedUser;

DCL

Description Command
show the privileges for all users
SHOW GRANTS;
show the privileges for user root
SHOW GRANTS FOR root;
show the privileges for user root@localhost
SHOW GRANTS FOR root@localhost;
basic syntax for grant
GRANT privileges 
ON object 
TO user;
basic syntax for revoke
REVOKE privileges 
ON object 
FROM user;
list of privileges
SELECT, INSERT, UPDATE, DELETE, INDEX,
CREATE, ALTER, DROP, GRANT OPTION, ALL
grant select & delete privilege to user root on table users
GRANT SELECT,DELETE 
ON users 
TO root@localhost;
revoke select & delete privilege from user root on table users
REVOKE SELECT,DELETE 
ON users 
FROM root@localhost;

Index

Description Command
show all index created on table city
SHOW INDEX FROM city;
create index ‘city_name’ on table ‘city’ and column ‘name’
CREATE INDEX city_name
ON city(name)
create index ‘city_name_capital’ on table ‘city’ and columns ‘name’ & ‘capital’
CREATE UNIQUE INDEX city_name_capital
ON city(name, capital)
drop index ‘city_name’ from table ‘city’
DROP INDEX city_name
ON city
create index while creating table
CREATE TABLE city
(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  name CHAR(52) NOT NULL,
  INDEX city_name (name)
)
rename index using alter table command
ALTER TABLE city
RENAME INDEX city_name TO cityName
drop index using alter table command
ALTER TABLE city
DROP INDEX city_name
create index on column ‘name’ using alter table command
ALTER TABLE city
ADD INDEX city_name (name);