Ultimate MySQL Handbook

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
sudo mysql

CREATE USER 'suraj'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'suraj'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

mysql -u suraj -p

CREATE DATABASE startersql;
USE startersql;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  gender ENUM('Male', 'Female', 'Other'),
  date_of_birth DATE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP DATABASE startersql;

INSERT INTO users (name, email, gender, date_of_birth)
VALUES ('Alice', 'alice@example.com', 'Female', '1995-05-14');

INSERT INTO users (name, email, gender, date_of_birth)
VALUES
('Bob', 'bob@example.com', 'Male', '1990-11-23'),
('Charlie', 'charlie@example.com', 'Other', '1988-02-17');

SELECT * FROM users;
SELECT name, email FROM users;

SELECT * FROM users WHERE gender = 'Male';
SELECT * FROM users WHERE date_of_birth BETWEEN '1990-01-01' AND '2000-12-31';

UPDATE users SET name = 'Alicia' WHERE id = 1;
UPDATE users SET salary = salary + 10000 WHERE salary < 60000;

DELETE FROM users WHERE id = 3;
DELETE FROM users; -- caution!

SELECT COUNT(*) FROM users;
SELECT AVG(salary), MIN(salary), MAX(salary) FROM users;

SELECT LENGTH(name), UPPER(name), LOWER(name) FROM users;
SELECT CONCAT(name, ' <', email, '>') FROM users;

SELECT NOW(), YEAR(date_of_birth) FROM users;
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM users;

SELECT MOD(id, 2) AS is_even FROM users;
SELECT IF(gender = 'Female', 'Yes', 'No') AS is_female FROM users;

SET autocommit = 0;
UPDATE users SET salary = 80000 WHERE id = 5;
COMMIT; -- or ROLLBACK;
SET autocommit = 1;

CREATE TABLE addresses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  city VARCHAR(100),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- INNER JOIN
SELECT users.name, addresses.city
FROM users
INNER JOIN addresses ON users.id = addresses.user_id;

-- LEFT JOIN
SELECT users.name, addresses.city
FROM users
LEFT JOIN addresses ON users.id = addresses.user_id;

SELECT name FROM users
UNION
SELECT name FROM admin_users;

-- Self Join
SELECT a.name AS user, b.name AS referred_by
FROM users a
LEFT JOIN users b ON a.referred_by_id = b.id;

CREATE VIEW high_salary_users AS
SELECT id, name, salary FROM users WHERE salary > 70000;

SELECT * FROM high_salary_users;

DROP VIEW high_salary_users;

SHOW INDEXES FROM users;
CREATE INDEX idx_email ON users(email);
DROP INDEX idx_email ON users;

CREATE INDEX idx_gender_salary ON users(gender, salary);

SELECT name FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

SELECT name, salary,
(SELECT AVG(salary) FROM users) AS avg_salary
FROM users;

SELECT gender, AVG(salary) FROM users GROUP BY gender;
SELECT gender, AVG(salary) FROM users GROUP BY gender HAVING AVG(salary) > 75000;

DELIMITER $$
CREATE PROCEDURE AddUser(
  IN p_name VARCHAR(100),
  IN p_email VARCHAR(100),
  IN p_gender ENUM('Male','Female','Other'),
  IN p_dob DATE,
  IN p_salary INT
)
BEGIN
  INSERT INTO users (name, email, gender, date_of_birth, salary)
  VALUES (p_name, p_email, p_gender, p_dob, p_salary);
END$$
DELIMITER ;

CALL AddUser('Kiran Sharma', 'kiran@example.com', 'Female', '1994-06-15', 72000);

CREATE TABLE user_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  name VARCHAR(100),
  created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_log (user_id, name)
  VALUES (NEW.id, NEW.name);
END$$
DELIMITER ;

SELECT * FROM users WHERE name LIKE 'A%'; -- starts with A

SELECT DISTINCT gender FROM users;

TRUNCATE TABLE users;

ALTER TABLE users ADD COLUMN city VARCHAR(100);
ALTER TABLE users CHANGE COLUMN city location VARCHAR(150);