-- SQL Statements for MySQL Database Management
-- DDL (Data Definition Language) - Defining Database Structures
-- 1. Create a Database
CREATE DATABASE your_database_name;
-- 2. Use a Database (Important in MySQL to specify which database to work in)
USE your_database_name;
-- 3. Create a Table (Example: Employees)
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT for auto-
generating IDs
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
DepartmentID INT,
Email VARCHAR(100) UNIQUE
);
-- 4. Create another Table (Example: Departments)
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
DepartmentName VARCHAR(100) NOT NULL
);
-- 5. Alter Table - Add a Foreign Key Constraint
ALTER TABLE Employees
ADD CONSTRAINT FK_Department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
-- 6. Alter Table - Add a new column
ALTER TABLE Employees
ADD Phone VARCHAR(20);
-- 7. Alter Table - Modify a column (e.g., change data type)
ALTER TABLE Employees
MODIFY COLUMN DateOfBirth DATETIME; -- MySQL uses MODIFY COLUMN
-- 8. Alter Table - Drop a column
ALTER TABLE Employees
DROP COLUMN Phone;
-- 9. Rename a Table (MySQL specific syntax)
RENAME TABLE Employees TO Staff; -- Or ALTER TABLE Employees RENAME TO Staff;
-- 10. Drop a Table
-- DROP TABLE Staff; -- Use with extreme caution!
-- 11. Truncate a Table (Removes all data, keeps structure)
-- TRUNCATE TABLE Staff; -- Use with extreme caution!
-- 12. Drop a Database
-- DROP DATABASE your_database_name; -- Use with extreme caution!
-- DML (Data Manipulation Language) - Manipulating Data
-- 1. Insert Data into Departments Table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (101, 'Human Resources');
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (102, 'Marketing'),
(103, 'Sales');
-- 2. Insert Data into Employees Table
-- For AUTO_INCREMENT columns, you can omit the column in the INSERT statement, or
use NULL
INSERT INTO Employees (FirstName, LastName, DateOfBirth, DepartmentID, Email)
VALUES ('Alice', 'Smith', '1990-01-15', 101, 'alice.s@[Link]'); -- EmployeeID
will auto-increment
INSERT INTO Employees (FirstName, LastName, DateOfBirth, DepartmentID, Email)
VALUES ('Bob', 'Johnson', '1988-05-20', 102, 'bob.j@[Link]'),
('Charlie', 'Brown', '1992-11-10', 101, 'charlie.b@[Link]'),
('Diana', 'Prince', '1980-07-01', 103, 'diana.p@[Link]');
-- 3. Select Data - All columns, all rows
SELECT * FROM Employees;
-- 4. Select Data - Specific columns
SELECT FirstName, LastName, Email FROM Employees;
-- 5. Select Data - With WHERE clause (filtering rows)
SELECT * FROM Employees WHERE DepartmentID = 101;
SELECT FirstName, LastName FROM Employees WHERE DateOfBirth < '1990-01-01';
-- 6. Select Data - With ORDER BY (sorting results)
SELECT * FROM Employees ORDER BY LastName ASC, FirstName DESC;
-- 7. Select Data - With LIMIT (restricting number of rows - standard in MySQL)
SELECT * FROM Employees ORDER BY EmployeeID LIMIT 2;
SELECT * FROM Employees ORDER BY EmployeeID LIMIT 1, 2; -- Starting from offset 1,
retrieve 2 rows
-- 8. Select Data - With JOIN (combining tables)
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];
-- 9. Select Data - With GROUP BY and Aggregate Functions (e.g., COUNT, SUM, AVG,
MIN, MAX)
SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID;
-- MySQL specific for calculating age: TIMESTAMPDIFF()
SELECT DepartmentID, AVG(TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE())) AS AverageAge
FROM Employees
GROUP BY DepartmentID;
-- 10. Select Data - With HAVING (filtering groups)
SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 2;
-- 11. Update Data
UPDATE Employees
SET Email = '[Link]@[Link]', DepartmentID = 103
WHERE EmployeeID = 1;
UPDATE Employees
SET DateOfBirth = '1989-06-25'
WHERE LastName = 'Johnson';
-- 12. Delete Data
DELETE FROM Employees
WHERE EmployeeID = 4;
-- DELETE FROM Employees; -- Deletes all rows in the table! Use with caution!
-- DCL (Data Control Language) - Managing Permissions
-- 1. Grant Permissions to a user (replace 'your_user', 'your_password',
'your_database_name')
-- Creating a user and granting privileges
CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT SELECT, INSERT ON your_database_name.Employees TO 'your_user'@'localhost';
GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES; -- Always run FLUSH PRIVILEGES after GRANT/REVOKE
-- 2. Revoke Permissions from a user
REVOKE DELETE ON your_database_name.Employees FROM 'your_user'@'localhost';
FLUSH PRIVILEGES;
-- 3. Drop a user
-- DROP USER 'your_user'@'localhost';
-- TCL (Transaction Control Language) - Managing Transactions
-- Start a transaction
START TRANSACTION; -- or BEGIN;
-- Perform some DML operations within the transaction
INSERT INTO Employees (FirstName, LastName, DepartmentID, Email)
VALUES ('Eve', 'Adams', 102, 'eve.a@[Link]');
UPDATE Employees
SET Email = '[Link]@[Link]'
WHERE EmployeeID = 2;
-- Create a savepoint (optional, for partial rollback)
SAVEPOINT after_updates;
-- Another operation that might fail
-- This would fail if EmployeeID 5 already exists due to AUTO_INCREMENT or a direct
insert
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Email)
VALUES (5, 'Frank', 'Green', 103, 'frank.g@[Link]');
-- If the last insert failed, you could rollback to the savepoint:
-- ROLLBACK TO after_updates;
-- If everything went well, commit the changes permanently
COMMIT;
-- If something went wrong and you want to undo all changes since START
TRANSACTION:
-- ROLLBACK;