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