CREATE DATABASE mydb;
SHOW DATABASES;
USE mydb;

Copy

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100) UNIQUE,
  age INT
);

Copy

INSERT INTO users (name, email, age)
VALUES ("Alice", "alice@example.com", 25),
       ("Bob", "bob@example.com", 30);

Copy

SELECT * FROM users;
SELECT name, age FROM users;
SELECT * FROM users WHERE age > 25;
SELECT * FROM users ORDER BY age DESC;
SELECT COUNT(*) FROM users;

Copy

UPDATE users SET age = 26 WHERE name = "Alice";
DELETE FROM users WHERE id = 2;

Copy

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

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

Copy

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  salary DECIMAL(10,2) CHECK (salary > 0)
);

Copy

CREATE INDEX idx_name ON users(name);
SHOW INDEXES FROM users;

Copy

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age;

Copy

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

Copy

CREATE VIEW adult_users AS
SELECT * FROM users WHERE age >= 18;

SELECT * FROM adult_users;

Copy

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

CALL GetUsers();

Copy

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.name = UPPER(NEW.name);

Copy

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

Copy

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Copy