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

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;

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;

Cloning

Duplicate a Table Schema

create table emp_dup like employee;

Duplicate a Table

create table emp_dup select * from employee;

Access Controls

Set new password to a user

use mysql;
update user set authentication_string=PASSWORD("<new2-password>") where User='<user>';
flush privileges;

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