This is a cheat sheet of MySQL for easier references. MySQL is an open-source relational database management system. It is a central component of the LAMP open-source web application software stack. MySQL is used by many database-driven web applications, including Drupal, Joomla, phpBB, and WordPress.
Installation
To get started with MySQL, you can download it from here or, simply use xampp, which uses maria-db, a drop in replacement of MySQL. To run MySQL, in linux environment or, docker/ podman container, I've a guide for you!
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;
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;
Select with condition
select ID,
case
when name=score < 40 then "F"
when name=score < 60 then "C"
when name=score < 80 then "B"
else "A"
end
from marks;
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
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
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;
