SQL command cheatsheet

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 here to know more.

MySQL Component

Connecting to MySQL

Database

User

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

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

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

DCL

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

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 ‘citynamecapital’ 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);