MySQL Cheat Sheet

MySQL Cheat sheet lists all the popular MySQL commands in one place. You can see MySQL commands in MySQL Cheat Sheet.

Show all databases

---

show databases;

Create Database

---

create Database If not exists EMPLOYEE;

Drop MySQL database (Delete Database)

---

Drop Database EMPLOYEE;

Rename MySQL Database

---

Rename Database EMP TO EMPLOYEE;

Select Database

---

Use EMPLOYEE;

Backup MySQL database (Export MySQL Database)

---

mysqldump -u username -p db_name > database_name.sql

import database

---

mysql -u username -p database_name < file.sql

Table

Show All Tables

---

SHOW Tables;

Create new table

---

CREATE TABLE Employee(EMP_ID INT(10), EMP_NAME Varchar(50),Designation Varchar(20),Location Varchar(20));

Insert a row in a table

---

INSERT INTO EMPLOYEE VALUES(101,"Steve Hilton","Analyst","USA");

Read all the data from the table

---

SELECT * FROM EMPLOYEE;

Read specific row data from table

---

SELECT * FROM EMPLOYEE WHERE EMP_ID="101"

Read specific columns from table

---

SELECT EMP_ID,EMP_NAME FROM EMPLOYEE;

Update Table Value

---

UPDATE EMPLOYEE SET DESIGNATION="SR. Analyst" where EMP_ID="101";

Delete Row from Table

---

DELETE FROM EMPLOYEE WHERE EMP_ID="101";

Delete All Data from Table

---

--OPTION 1
DELETE * FROM EMPLOYEE;

--OPTION 2
TRUNCATE EMPLOYEE;

Add new column

---

 ALTER TABLE EMPLOYEE ADD Department varchar(50);

Rename column

---

Alter table Employee rename column Department to Dept;

Delete column

---

Alter table Employee drop Dept;

Keys

Add Primary key to new table

---

CREATE TABLE salary(EmpID int primary key, salary varchar(20)); 

Add Primary key to existing table

---

ALTER TABLE Employee add primary key(EMP_ID);

Remove Primary key

---

ALTER TABLE SALARY drop primary key;

Show all users

---

SELECT USER FROM USER;

Create User

---

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password';

Grant All Privileges to user

---

GRANT ALL ON *. * TO 'testuser'@'localhost' WITH GRANT OPTION;

Grant specific Privileges to user

---

grant select databases on employee.* to 'testuser'@'localhost';

Revoke all privileges from user

---

REVOKE ALL,GRANT OPTION  from 'testuser'@'localhost';

Revoke specific privileges from user

---

Revoke select on employee.* from 'testuser'@'localhost';

Remove user from MySQL

---

drop user 'testuser'@'localhost';