MySQL cheatsheet
I have even uploaded the .sql file which you can download and directly run them in the sql prompt.
General Commands
To run sql files
source <filename>.sql;
Data Types
Integers
INT
TINYINT
SMALLINT
MEDIUMINT
BIGINT
Float
FLOAT(M,D)
Double
DOUBLE(M,D)
Decimal
DECIMAL(M,D)
Date
DATE -- Format - (YYYY-MM-DD)
Date Time
DATETIME -- Format - (YYYY-MM-DD HH:MM:SS)
Time
TIME -- Format - (HH:MM:SS)
String
CHAR(M)
VARCHAR(M)
BLOB or TEXT
Comments
/* Multi
line
comment */
# Single Line Comment
-- Single Line Comment
Data Definition Language (DDL)
Create Database
create database cheatsheet;
Use Database
use cheatsheet;
Show Databases
show databases;
Create Table
create table employee
(
employee_id int primary key, -- Setting primary key(1st method)
first_name varchar(50),
last_name varchar(50),
dept_number int,
age int,
salary real
);
create table department
(
dept_number int,
dept_name varchar(50),
dept_location varchar(50),
emp_id int,
primary key(dept_number) -- Setting primary key(2nd method)
);
Show Tables
show tables;
Describe Table
describe employee;
desc employee;
show columns in employee;
Rename Table
rename table employee to employee_table;
alter table employee_table rename to employee;
Renaming Column
alter table employee change column employee_id emp_id int;
Add Constraint to Column
alter table employee change column first_name first_name varchar(50) not null;
Add Column
alter table employee add column salary real;
Drop Column
alter table employee drop column salary;
Modify the Datatype of column
alter table employee modify column salary int;
Truncate Table
truncate employee;
Drop Table
drop table department;
Drop Database
drop database cheatsheet;
Data Manipulation Language (DML)
Insertion (Complete)
insert into employee (employee_id, first_name, last_name, dept_number, age, salary) values (1, "Anurag", "Peddi", 1, 20, 93425.63);
insert into employee values (2, "Anuhya", "Peddi", 2, 20, 83425.63);
Insertion (Partial)
insert into employee (employee_id, first_name) values (3, "Vageesh");
Updating all rows
update employee set salary = 1.1 * salary;
Updating a specified row
update employee set salary = 1.2 * salary where employee_id = 1;
Delete a specified row
delete from employee where employee_id = 2;
Delete all rows
delete from employee;
Enabling foreign key checks
set foreign_key_checks = 1;
Disabling foreign key checks
set foreign_key_checks = 0;
Data Query Language (DQL)
Display Table
select * from employee;
Select only specified columns
select employee_id, first_name from employee;
Select only few rows
select employee_id, first_name from employee where age > 25;
Where Clause
Greater than(>)
select * from employee where salary > 3100;
Greater than equal to(>=)
select * from employee where salary >= 3100;
Less than(<)
select * from employee where salary < 4500;
Less than equal to(<=)
select * from employee where salary <= 4350;
Range
select * from employee where salary > 3000 and salary < 4000;
BETWEEN and AND
select * from employee where salary between 3000 and 4000;
OR
select * from employee where salary = 3000 or salary = 4000;
Null
select * from employee where salary is NULL;
Not null
select * from employee where salary is NOT NULL;
ORDER BY Clause
select * from employee ORDER BY salary DESC;
Like Operator
select * from employee where name like '%Jo%'; -- Similar to *Jo* in regrex
select * from employee where name like 'Jo_'; -- Similar to Jo. in regrex
Views
Create a view
create view personal_info as select first_name, last_name, age from employees;
Displaying view
select * from personal_info;
Updating in view
update personal_info set salary = 1.1 * salary;
Deleting record from view
delete from personal_info where age < 40;
Droping a view
drop view personal_info;
Joins
Inner join
select e.fname, p.pname from employees as e inner join project as p on e.eid = p.eid;
-- or
select e.fname, p.pname from employees as e join project as p on e.eid = p.eid;
Full outer join
select e.fname, p.pname from employees as e left outer join project as p on e.eid = p.eid
union
select e.fname, p.pname from employees as e right outer join project as p on e.eid = p.eid;
Left outer join
select e.fname, p.pname from employees as e left outer join project as p on e.eid = p.eid;
Right outer join
select e.fname, p.pname from employees as e right outer join project as p on e.eid = p.eid;
Left outer join - inner join
select e.fname, p.pname from employees as e left outer join project as p on e.eid = p.eid where p.pname is null;
Right outer join - inner join
select e.fname, p.pname from employees as e right outer join project as p on e.eid = p.eid where e.fname is null;
Aggregation
Sum function
select sum(population) from city group by population;
Average function
select avg(population) from city group by population;
Count function
select district, count(district) from city group by district;
Maximum function
select max(population) from city group by population;
Minimum function
select min(population) from city group by population;
Standard deviation function
select stddev(population) from city group by population;
Group concat function
select group_concat(population) from city group by population;
Only COUNT function considers NULL values
Procedure
Creating procedure
create procedure display_dbs()
show databases;
Calling procedure
call display_dbs();
Drop procedure
drop procedure display_dbs;
Transaction
Begin transaction
start transaction;
Create savepoint
savepoint sv_pt;
delete from city; -- changing data in table
Rollback
rollback to sv_pt;
Releasing savepoint
release savepoint sv_pt;
Commiting changes
commit;
Constraints
Not Null
alter table Employee
change
Age
Age int NOT NULL;
Unique
alter table Employee
add constraint u_q unique(ID);
alter table Employee -- drop the constraint
drop constraint u_q;
Primary Key
alter table Employee
add constraint p_k primary key(ID);
alter table Employee -- drop the constraint
drop constraint p_k;
Check
alter table Employee
add constraint Age check (age>=30);
alter table Employee -- drop the constraint
drop check Age;
Default
alter table Employee
alter Age set default 10;
alter table Employee -- drop the constraint
alter Age drop default;
Cloning
Duplicate a Table Schema
create table emp_dup like employee;
Duplicate a Table
create table emp_dup select * from employee;
Access Controls
Creating New User
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
the hostname part is set to localhost
, so the user will be able to connect to the MySQL server only from the localhost.
To grant access from another host, change the hostname part with the remote machine IP.
CREATE USER 'username'@'172.8.10.5' IDENTIFIED BY 'user_password';
To create a user that can connect from any host, '%' is used in the hostname part:
CREATE USER 'username'@'%' IDENTIFIED BY 'user_password';
Grant All Permissions
GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';
Asterisks(*) refers to the database and table names respectively.
By using asterisks we can give access of all the databases or tables to the user.
Flush Privileges
FLUSH PRIVILEGES
All the changes won't be in effect unless this query is fired.
Specific User Permissions
GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';
type_of_permission
may have one of these value:
- ALL PRIVILEGES - Allows user full access to a designated database (or if no database is selected, global access across the system).
- CREATE - allows them to create new tables or databases.
- DROP - allows them to them to delete tables or databases.
- DELETE - allows them to delete rows from tables.
- INSERT - allows them to insert rows into tables.
- SELECT - allows them to use the
SELECT
command to read through databases. - UPDATE - allow them to update table rows.
- GRANT OPTION - allows them to grant or remove other users’ privileges.
Multiple permissions are given with commas.
Revoking permissions
REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';
Show User's Current Permissions
SHOW GRANTS FOR 'username'@'localhost';
Delete a User
DROP USER 'username'@'localhost';
Set new password to a user
use mysql;
update user set authentication_string=PASSWORD("<new2-password>") where User='<user>';
flush privileges;
Reset Root Password
Stop MySQL service
sudo systemctl stop mysql
Restart MySQL service without loading grant tables
sudo mysqld_safe --skip-grant-tables &
The apersand (&) will cause the program to run in the background and --skip-grant-tables
enables everyone to to connect to the database server without a password and with all privileges granted.
Login to shell
mysql -u root
Set new password for root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;
Stop and start the server once again
mysqladmin -u root -p shutdown
sudo systemctl start mysql
Programming
Declare variables
set @num = 10;
set @name = 'Anurag';
Print them
select @name;
For loop
set @n = 21;
select repeat("* ", @n := @n - 1) from information_schema.tables where @n > 0;
Miscellaneous
Round
select round(3.141596, 3);
Repeated concatenation
select repeat("* ", 20);
Random float
select rand();
Typecast to Int
select cast(23.01245 as signed);
Concatenation
select concat("Mahesh", " ", "Chandra", " ", "Duddu", "!");
Extract Month
select month("1998-12-30");
Extract Year
select year("1998-12-30");