Master MySQL Easily: Complete Analysis of 30 Basic Operations Statements

1. Create a database

CREATE DATABASE mydatabase;

1. Drop a database

DROP DATABASE mydatabase;

1. Create a table

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);

1. Drop a table

DROP TABLE users;

1. Insert a record into a table

INSERT INTO users (username, email) VALUES (‘john_doe’, ‘[e-mail]’);

1. Update records in a table

UPDATE users SET email = ‘[e-mail]’ WHERE username = ‘john_doe’;

1. Delete records from a table

DELETE FROM users WHERE username = ‘john_doe’;

1. Select all records from a table

SELECT * FROM users;

1. Select specific columns from a table

SELECT username, email FROM users;

1. Select records with a condition

SELECT * FROM users WHERE id = 1;

1. Select records with multiple conditions

SELECT * FROM users WHERE username = ‘john_doe’ AND email = ‘[e-mail]’;

1. Select records with pattern matching

SELECT * FROM users WHERE username LIKE ‘john%’;

1. Order records in ascending order

SELECT * FROM users ORDER BY username ASC;

1. Order records in descending order

SELECT * FROM users ORDER BY username DESC;

1. Limit the number of records returned

SELECT * FROM users LIMIT 10;

1. Offset the start of records returned

SELECT * FROM users LIMIT 10 OFFSET 20;

1. Count the number of records in a table

SELECT COUNT(*) FROM users;

1. Sum of values in a column

SELECT SUM(sales) FROM transactions;

1. Average value in a column

SELECT AVG(price) FROM products;

1. Maximum value in a column

SELECT MAX(score) FROM exam_results;

1. Minimum value in a column

SELECT MIN(age) FROM employees;

1. Group records by a column

SELECT department, COUNT(*) FROM employees GROUP BY department;

1. Join two tables

SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

1. Left join two tables

SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

1. Right join two tables

SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

1. Full outer join two tables

SELECT users.username, orders.order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

1. Create an index on a table

CREATE INDEX idx_username ON users (username);

1. Drop an index from a table

DROP INDEX idx_username ON users;

1. Grant privileges to a user

GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;

1. Revoke privileges from a user

REVOKE SELECT, INSERT, UPDATE ON mydatabase.* FROM ‘username’@’localhost’;