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;

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;

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;

Aggregation

Sum function

select sum(population) from city group by population;

Average function

select avg(population) from city group by population;

Count function

select count(name) from city group by name;

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