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);
|