SQL cheat sheet

Data Definition Language (DDL)

Create Database

create database cheatsheet;

Use Database

use cheatsheet;

Show Databases

show databases;

Create Table

create table student
(
    student_id int primary key,              -- Setting primary key(first method)
    first_name varchar(50),
    last_name varchar(50),
    class_number int,
    age int,
    salary real
);

create table class
(
    class_number int,
    class_name varchar(50),
    class_location varchar(50),
    st_id int,
    primary key(class_number)                -- Setting primary key(secund method)
);

Show Tables

show tables;

Describe Table

describe student;
desc student;
show columns in student;

Rename Table

# rename table student to student_table;
alter table student_table rename to student;

Renaming Column

alter table student change column student_id st_id int;

Add Constraint to Column

alter table student change column first_name first_name varchar(50) not null;

Add Column

alter table student add column salary real;

Drop Column

alter table student drop column salary;

Modify the Datatype of column

alter table student modify column salary int;

Truncate Table

truncate student;

Drop Table

drop table student;

Drop Database

drop database student;

Data Manipulation Language (DML)

Insertion (Complete)

insert into student (student_id, first_name, last_name, class_number, age, salary) values (1, "Refaat", "AL Ktifan", 1, 18, 100000.00);

insert into student values (2, "Mohammad", "Mohammad", 2, 20, 50000.22);

Insertion (Partial)

insert into student (student_id, first_name) values (3, "Khaled");

Updating all rows

update student set salary = 1.1 * salary;

Updating a specified row

update student set salary = 1.2 * salary where student_id = 1;

Delete a specified row

delete from student where student_id = 2;

Delete all rows

delete from student;

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

Select only specified columns

select student_id, first_name from student;

Select only few rows

select student_id, first_name from student where age > 25;

Greater than(>)

select * from student where salary > 3100;

Greater than equal to(>=)

select * from student where salary >= 3100;

Less than(<)

select * from student where salary < 4500;

Less than equal to(<=)

select * from student where salary <= 4350;

Range

select * from student where salary > 3000 and salary < 4000;

BETWEEN and AND

select * from student where salary between 3000 and 4000;

NOT BETWEEN

select * from student where salary not between 3000 and 4000;

NOW

select now();

ORDER BY

select * from  student order by salary;

# Explanation: orders the rows from ACS = alphabetic or numeric order, or DESC the reverse

select * from  student order by age DESC/ASC

Like Operator

select * from student where name like '%Re%';          -- Similar to *Re* in regrex

select * from student where name like 'Re_';           -- Similar to Re. in regrex

CASE

select first_name , age, (case when age > 20 then 1 else 0 end ) as boolean from student;

concat function

select concat(first_name, " ", last_name) as name from student;

COALESCE

select last_name , coalesce(age,class_number) as result from student;

FORMAT

select last_name , format(salary, 3) as salary from student;

IF

select  last_name , if(salary < 50, 'salary is < 50', salary) as salary from student limit 5;

ELSE

select first_name as name , (if age > 18 then "under 18" else "over") as age from student;

IFNULL

select first_name , ifnull(age, salary) from student limit 5;

Views

Create a view

create view personal_info as select first_name, last_name, age from student;

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 student as e inner join project as p on e.eid = p.eid;

-- or

select e.fname, p.pname from student as e join project as p on e.eid = p.eid;

Full outer join

select e.fname, p.pname from student as e left outer join project as p on e.eid = p.eid
union
select e.fname, p.pname from student as e right outer join project as p on e.eid = p.eid;

Left outer join

select e.fname, p.pname from student as e left outer join project as p on e.eid = p.eid;

Right outer join

select e.fname, p.pname from student as e right outer join project as p on e.eid = p.eid;

Left outer join - inner join

select e.fname, p.pname from student 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 student as e right outer join project as p on e.eid = p.eid where e.fname is null;

TRIGGER

THE TRIGGER STRUCTURE

    DELIMITER $$
    CREATE TRIGGER  trigger_name
    [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
    FOR EACH ROW [FOLLOWS|PRECEDES] existing_trigger_name
    BEGIN
    …
    END$$
    DELIMITER ;

AFTER INSERT

create table student (id serial, name varchar(100), account decimal(5,2));

insert into student (name,account) values ('Refaat',500.28);

create table orders (id serial, name varchar(40), amount decimal(5,2) );

delimeter $
	create trigger trigger_exchange
		after insert on orders
		 for each row
		 	begin
		 		update student
				set account = account - new.amount
				where cust_id = new.cust_id;
			end $

insert into student (name,amount) values ('Mohammed', 10.28);

Functions

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;

Format function

FORMAT(number, decimal_places)

UPPER

UPPER(name);

length function

LENGTH(string)

substr function

SUBSTR(string, start, length)

time function

select student_id, first_name, time(student_registered) from student;

date function

select id, first_name, date(student_registered) from student ;

MD5

select md5("hello new world");    -- result: 6dc422ea4e83e014c4456706c72730f6

MD5

select sha1("test");    -- result: a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

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

Duplicate a Table

create table emp_dup select * from student;

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

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

HOW TO