1. Database and Table Creation

-- Single-line comment (similar to Python)
-- Create a database (if not exists) with UTF8 charset
CREATE DATABASE IF NOT EXISTS learning_mysql DEFAULT CHARACTER SET 'utf8';

USE learning_mysql;

SET NAMES GBK; -- Temporary client encoding for Chinese

-- Create a user table (first_table)
CREATE TABLE IF NOT EXISTS user_records (
    user_id SMALLINT,
    username TINYINT,
    age TINYINT,
    email VARCHAR(50),
    address VARCHAR(200),
    birth_year YEAR,
    salary FLOAT(8, 2),
    phone INT,
    married TINYINT(1) COMMENT '0 = unmarried, non-zero = married'
) ENGINE=InnoDB CHARSET=UTF8;

SHOW TABLES;

-- Create a course table
CREATE TABLE IF NOT EXISTS courses (
    course_id TINYINT,
    course_name VARCHAR(50),
    course_desc VARCHAR(200)
);

SHOW TABLES;

-- Test data overflow
CREATE TABLE test_numbers (
    num_tiny TINYINT,
    num_small SMALLINT,
    num_medium MEDIUMINT,
    num_int INT,
    num_big BIGINT
);

INSERT INTO test_numbers VALUES (-128, -32768, 0, -2147483648, 0);
SELECT * FROM test_numbers;

-- Test zero-filled data
CREATE TABLE test_zero_fill (
    num_tiny TINYINT ZEROFILL,
    num_small SMALLINT ZEROFILL,
    num_medium MEDIUMINT ZEROFILL,
    num_int INT ZEROFILL,
    num_big BIGINT ZEROFILL
);

INSERT INTO test_zero_fill VALUES (1, 1, 1, 1, 1);

-- Test floating-point types
CREATE TABLE test_floats (
    num_float FLOAT(6, 2),
    num_double DOUBLE(6, 2),
    num_decimal DECIMAL(6, 2)
);

INSERT INTO test_floats VALUES (3.1415, 3.1415, 3.1415);
SELECT * FROM test_floats;
SELECT * FROM test_floats WHERE num_double = 3.14;

### 2. Data Type Testing
```sql
-- Test character types (CHAR vs VARCHAR)
CREATE TABLE IF NOT EXISTS test_chars (
    char_col CHAR(5),
    varchar_col VARCHAR(5)
);

INSERT INTO test_chars VALUES ('1', '1');
INSERT INTO test_chars VALUES ('12345', '12345');
INSERT INTO test_chars VALUES ('123456', '123456'); -- Truncated

SELECT CONCAT('-', char_col), CONCAT('-', varchar_col) FROM test_chars;
SELECT LENGTH('A'); -- String length

-- Test ENUM
CREATE TABLE IF NOT EXISTS test_enum (
    gender ENUM('male', 'female', 'secret')
);

INSERT INTO test_enum VALUES ('male'), ('female'), ('secret');

-- Test SET
CREATE TABLE IF NOT EXISTS test_set (
    favorites SET('A', 'B', 'C', 'D')
);

INSERT INTO test_set VALUES ('A,C,D'), ('A,D,E'), (15); -- 15 = binary 1111 (A,B,C,D)
SELECT * FROM test_set;

-- Test YEAR
CREATE TABLE IF NOT EXISTS test_year (
    birth_year YEAR
);

INSERT INTO test_year VALUES (1901), ('2000'), (0), ('0');
SELECT * FROM test_year;

### 3. Integrity Constraints
```sql
-- Primary Key (single-column)
CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY,
    username VARCHAR(20)
);

-- Composite Primary Key
CREATE TABLE IF NOT EXISTS user_cards (
    user_id INT,
    card_number CHAR(18),
    PRIMARY KEY (user_id, card_number)
);

-- Auto-Increment
CREATE TABLE IF NOT EXISTS auto_users (
    user_id SMALLINT AUTO_INCREMENT,
    username VARCHAR(20),
    PRIMARY KEY (user_id)
);

-- Not Null + Default
CREATE TABLE IF NOT EXISTS user_details (
    user_id INT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password CHAR(32) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18,
    address VARCHAR(50) NOT NULL DEFAULT 'Beijing',
    PRIMARY KEY (user_id)
);

-- Unique Constraint
CREATE TABLE IF NOT EXISTS unique_users (
    user_id TINYINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    card CHAR(17) UNIQUE,
    PRIMARY KEY (user_id)
);

### 4. Table Structure Operations
```sql
-- Rename Table
ALTER TABLE user_details RENAME TO user_info;
RENAME TABLE user_info TO user_details;

-- Add Columns
ALTER TABLE user_details ADD card CHAR(18);
ALTER TABLE user_details ADD test_col VARCHAR(100) NOT NULL UNIQUE FIRST;
ALTER TABLE user_details ADD test_col2 VARCHAR(100) NOT NULL DEFAULT 'test' AFTER password;

-- Modify Column
ALTER TABLE user_details MODIFY email VARCHAR(200);
ALTER TABLE user_details CHANGE test_col test_col_new CHAR(32) NOT NULL DEFAULT '123' FIRST;

-- Add Primary Key
ALTER TABLE test_table ADD PRIMARY KEY (id);

-- Add Unique Index
ALTER TABLE user_table ADD UNIQUE (username);
ALTER TABLE user_table ADD CONSTRAINT unique_card UNIQUE KEY (card_number);

-- Modify Storage Engine
ALTER TABLE test_table ENGINE = MyISAM;

-- Drop Table
DROP TABLE IF EXISTS user_details, unique_users;

### 5. Data Manipulation
```sql
-- Insert Single Record
INSERT INTO users (user_id, username) VALUES (1, 'admin');

-- Insert Multiple Records
INSERT INTO users (user_id, username) VALUES (2, 'user1'), (3, 'user2');

-- Update Records
UPDATE users SET username = 'new_user' WHERE user_id = 1;
UPDATE users SET age = age + 1 WHERE age IS NOT NULL;

-- Delete Records
DELETE FROM users WHERE user_id = 3;
TRUNCATE TABLE users; -- Resets auto-increment

### 6. Querying Data
```sql
-- Basic SELECT
SELECT * FROM users;
SELECT username, age FROM users WHERE age > 18;

-- Filter Conditions
SELECT * FROM users WHERE user_id BETWEEN 1 AND 10;
SELECT * FROM users WHERE username IN ('admin', 'user1');
SELECT * FROM users WHERE username LIKE '%user%';

-- Logical Operators
SELECT * FROM users WHERE age > 20 AND username LIKE 'u%';
SELECT * FROM users WHERE age < 18 OR username = 'admin';

### 7. Advanced Queries
```sql
-- Group By
SELECT gender, COUNT(*) FROM users GROUP BY gender;

-- Aggregate Functions
SELECT AVG(age) AS avg_age, MAX(age) AS max_age, MIN(age) AS min_age FROM users;

-- Having (Filter Groups)
SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) > 5;

-- Order By
SELECT * FROM users ORDER BY age DESC, username ASC;

-- Limit Results
SELECT * FROM users LIMIT 5; -- First 5
SELECT * FROM users LIMIT 10, 5; -- Records 11-15