Installation

If a previous MySQL installation failed or needs to be reset, remove the existing service cautiously:

sc delete mysql

For MySQL 5.7:

1. Extract the archive to a path without spaces or non-ASCII characters.
2. Add the bin directory to your system's PATH.
3. Create a my.ini configuration file:

[client]
port=3306
default-character-set=utf8

[mysqld]
basedir=D:\program\mysql-5.7.19-winx64
datadir=D:\program\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
skip-grant-tables

4. Install as a Windows service (run CMD as Administrator):

mysqld --install

5. Initialize the data directory insecurely:

mysqld --initialize-insecure --user=mysql

6. Start the service:

net start mysql

7. Log in and set the root password:

USE mysql;
UPDATE user SET authentication_string = PASSWORD('yb0os1') 
WHERE user = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

8. Comment out skip-grant-tables in my.ini and restart the service to enforce authentication.

Note: MySQL tables are stored as files on disk.

---

Database Management

Create a database with specific character set and collation:

CREATE DATABASE IF NOT EXISTS test1 
CHARACTER SET utf8 
COLLATE utf8_bin;

List and inspect databasse:

SHOW DATABASES;
SHOW CREATE DATABASE test1;

Drop a database:

DROP DATABASE IF EXISTS test1;

Backup and restore:

# Backup
mysqldump -u root -p -B test1 > backup.sql

# Restore (inside MySQL client)
SOURCE backup.sql;

---

Data Types

Prefer smaller types that meet requirements.

• Integer types: TINYINT (1B), INT (4B), BIGINT (8B). Signed by default unless UNSIGNED is specified.
• BIT(M): Stores M bits (1–64).
• Floating-point: FLOAT, DOUBLE, or DECIMAL(M,D) for exact precision.
• Strings:
  • CHAR(N): Fixed-length (up to 255 chars).
  • VARCHAR(N): Variable-length (max ~65KB total; actual character limit depends on encoding).
  • Use TEXT for large text.
• Date/time: DATE, TIME, DATETIME, TIMESTAMP.

---

Table Operations

Create a table:

CREATE TABLE `user` (
  `id` INT,
  `username` VARCHAR(255),
  `password` VARCHAR(255),
  `birthday` DATE
) CHARACTER SET utf8 
  COLLATE utf8_bin 
  ENGINE=InnoDB;

Modify structure:

ALTER TABLE emp ADD image VARCHAR(255);
ALTER TABLE emp MODIFY job VARCHAR(60);
ALTER TABLE emp DROP COLUMN sex;
ALTER TABLE emp RENAME TO employee;
ALTER TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT '';

Inspect schema:

DESCRIBE employee;

---

CRUD Operations

-- Insert
INSERT INTO user (id, username) VALUES (1, 'alice');

-- Update
UPDATE user SET password = 'newpass' WHERE id = 1;

-- Delete
DELETE FROM user WHERE id = 1;

-- Select
SELECT DISTINCT username FROM user WHERE username LIKE 'a%';

Sorting and aggregation:

SELECT deptno, AVG(sal) AS avg_sal 
FROM emp 
GROUP BY deptno 
HAVING avg_sal < 2000
ORDER BY avg_sal DESC
LIMIT 0, 10;

Date literals can be compared directly if formatted consistently.

---

Joins and Subqueries

Cartesian product (avoid without join conditions):

SELECT * FROM emp, dept;

Self-join:

SELECT worker.ename AS employee, boss.ename AS manager
FROM emp worker
JOIN emp boss ON worker.mgr = boss.empno;

Subqueries:

-- Single-row
SELECT * FROM emp 
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

-- Multi-row
SELECT * FROM emp 
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10)
  AND deptno != 10;

-- Multi-column
SELECT * FROM emp 
WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'ALLEN')
  AND ename != 'ALLEN';

Outer joins:

-- Left join: all students, with grades if available
SELECT s.id, s.name, IFNULL(e.grade, 0)
FROM student s
LEFT JOIN exam e ON s.id = e.id;

---

Constraints

• Primary Key: Unique, non-null. Composite keys allowed.
• UNIQUE: Allows multiple NULLs unless combined with NOT NULL.
• Foreign Key: Requires InnoDB. Referenced column must be PK or UNIQUE.

CREATE TABLE class (id INT PRIMARY KEY, name VARCHAR(32));
CREATE TABLE students (
  stu_id INT PRIMARY KEY,
  name VARCHAR(32),
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES class(id)
);

MySQL 5.7 parses but ignores CHECK constraints.

Auto-increment:

CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (NULL); -- inserts 1

---

Built-in Functions

String:

SELECT CONCAT(ename, ' works as ', job) FROM emp;
SELECT SUBSTRING('example', 1, 3); -- 'exa'

Numeric:

SELECT CEILING(1.2), FLOOR(1.8), MOD(10, 3);

Date/Time:

SELECT NOW(), DATEDIFF('2023-01-01', '2020-01-01');
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d');

Control flow:

SELECT ename, IFNULL(comm, 0) FROM emp;
SELECT 
  CASE job
    WHEN 'CLERK' THEN 'Staff'
    WHEN 'MANAGER' THEN 'Manager'
    ELSE job
  END AS role
FROM emp;

---

Indexing

Improves query speed at the cost of storage and write performance.

-- Create index
CREATE INDEX idx_empno ON emp(empno);

-- View indexes
SHOW INDEX FROM emp;

-- Drop index
DROP INDEX idx_empno ON emp;

Guidelines:

• Index columns frequent used in WHERE.
• Avoid indexing low-cardinality columns (e.g., gender).
• Avoid indexing rapidly changing columns.

---

Transactions

Ensure atomicity of related DML operations.

START TRANSACTION;
SAVEPOINT sp1;
INSERT INTO accounts VALUES ('A', -100);
INSERT INTO accounts VALUES ('B', +100);
COMMIT; -- or ROLLBACK TO sp1;

Isolation levels:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL defaults to REPEATABLE READ. Levels address:

• Dirty reads
• Non-repeatable reads
• Phantom reads

Only InnoDB supports transactions.

---

Storage Engines

• InnoDB: Default. Supports transactions, row-level locking, foriegn keys.
• MyISAM: No transactions. Table-level locking. Faster for read-heavy workloads.
• Memory: Data stored in RAM. Lost on restart.

Change engine:

ALTER TABLE t ENGINE = InnoDB;

---

Views

Virtual tables based on queries:

CREATE VIEW user_summary AS
SELECT id, username FROM user WHERE active = 1;

-- Use like a table
SELECT * FROM user_summary;

Views simplify access control and query logic.

---

User and Privilege Management

Users are stored in mysql.user.

Create user:

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'securepass';

Grant privileges:

GRANT SELECT, INSERT ON app_db.* TO 'app_user'@'192.168.1.%';

Revoke:

REVOKE INSERT ON app_db.* FROM 'app_user'@'192.168.1.%';
FLUSH PRIVILEGES;

Omitting host defaults to '%' (any host).