CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;

USE mydb;

DROP DATABASE mydb;

ALTER DATABASE mydb CHARACTER SET utf8mb4;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 18,
    status TINYINT DEFAULT 1 COMMENT '1:active 0:inactive',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESC users;
-- 或
DESCRIBE users;
-- 或
SHOW COLUMNS FROM users;

SHOW TABLES;

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
?
-- 刪除列
ALTER TABLE users DROP COLUMN phone;
?
-- 修改列類(lèi)型
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
?
-- 重命名列
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50);
?
-- 添加索引
ALTER TABLE users ADD INDEX idx_email (email);
?
-- 添加外鍵
ALTER TABLE orders ADD CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users(id);

DROP TABLE users;
-- 或
DROP TABLE IF EXISTS users;

TRUNCATE TABLE users;

RENAME TABLE old_name TO new_name;

-- 插入單條記錄
INSERT INTO users (username, email, password) VALUES ('zhangsan', 'zhangsan@example.com', '123456');
?
-- 插入多條記錄
INSERT INTO users (username, email, password) VALUES
    ('lisi', 'lisi@example.com', '123456'),
    ('wangwu', 'wangwu@example.com', '123456');
?
-- 從另一表插入數(shù)據(jù)
INSERT INTO users_backup SELECT * FROM users;

-- 更新單個(gè)字段
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
?
-- 更新多個(gè)字段
UPDATE users SET email = 'new@example.com', status = 0 WHERE id = 1;
?
-- 批量更新
UPDATE users SET status = 0 WHERE age < 18;

-- 刪除指定記錄
DELETE FROM users WHERE id = 1;
?
-- 批量刪除
DELETE FROM users WHERE status = 0;
?
-- 清空表（DELETE方式，會(huì)重置自增ID）
DELETE FROM users;

-- 查詢(xún)所有列
SELECT * FROM users;
?
-- 查詢(xún)指定列
SELECT id, username, email FROM users;
?
-- 去重查詢(xún)
SELECT DISTINCT status FROM users;

-- 等于
SELECT * FROM users WHERE id = 1;
?
-- 不等于
SELECT * FROM users WHERE status != 0;
?
-- 多條件 AND
SELECT * FROM users WHERE age >= 18 AND age <= 60;
?
-- 多條件 OR
SELECT * FROM users WHERE age < 18 OR age > 60;
?
-- IN 查詢(xún)
SELECT * FROM users WHERE id IN (1, 2, 3);
?
-- NOT IN 查詢(xún)
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
?
-- BETWEEN 范圍查詢(xún)
SELECT * FROM users WHERE age BETWEEN 18 AND 60;
?
-- LIKE 模糊查詢(xún)
SELECT * FROM users WHERE username LIKE 'zhang%';  -- 以zhang開(kāi)頭
SELECT * FROM users WHERE username LIKE '%san';     -- 以san結(jié)尾
SELECT * FROM users WHERE username LIKE '%an%';     -- 包含an
SELECT * FROM users WHERE username LIKE 'zh_ng';    -- _匹配單個(gè)字符
?
-- IS NULL 查詢(xún)
SELECT * FROM users WHERE phone IS NULL;
?
-- IS NOT NULL 查詢(xún)
SELECT * FROM users WHERE phone IS NOT NULL;

-- 升序
SELECT * FROM users ORDER BY age ASC;
?
-- 降序
SELECT * FROM users ORDER BY created_at DESC;
?
-- 多字段排序
SELECT * FROM users ORDER BY status ASC, age DESC;

-- 查詢(xún)前N條
SELECT * FROM users LIMIT 10;
?
-- 分頁(yè)查詢(xún)（跳過(guò)M條，取N條）
SELECT * FROM users LIMIT 0, 10;   -- 第1頁(yè)，每頁(yè)10條
SELECT * FROM users LIMIT 10, 10;  -- 第2頁(yè)，每頁(yè)10條

-- 按狀態(tài)分組統(tǒng)計(jì)
SELECT status, COUNT(*) as count FROM users GROUP BY status;
?
-- 多字段分組
SELECT status, age, COUNT(*) as count FROM users GROUP BY status, age;
?
-- 分組后過(guò)濾（HAVING）
SELECT status, COUNT(*) as count FROM users
GROUP BY status
HAVING count > 5;

-- COUNT 統(tǒng)計(jì)數(shù)量
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- SUM 求和
SELECT SUM(amount) FROM orders;

-- AVG 平均值
SELECT AVG(age) FROM users;

-- MAX 最大值
SELECT MAX(price) FROM products;

-- MIN 最小值
SELECT MIN(price) FROM products;

-- 組合使用
SELECT
    COUNT(*) as total,
    AVG(age) as avg_age,
    MAX(age) as max_age,
    MIN(age) as min_age
FROM users;

-- 只返回匹配的記錄
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 返回左表所有記錄，右表沒(méi)有匹配則為NULL
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 返回右表所有記錄，左表沒(méi)有匹配則為NULL
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

SELECT u.username, o.order_no, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 普通索引
CREATE INDEX idx_username ON users(username);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 組合索引
CREATE INDEX idx_status_age ON users(status, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

SHOW INDEX FROM users;

DROP INDEX idx_username ON users;

CREATE VIEW user_orders AS
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

SELECT * FROM user_orders;

DROP VIEW user_orders;

-- 開(kāi)啟事務(wù)
START TRANSACTION;
-- 或
BEGIN;

-- 執(zhí)行SQL
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 提交事務(wù)
COMMIT;

-- 回滾事務(wù)
ROLLBACK;

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'testuser'@'%' IDENTIFIED BY 'password123';  -- 允許遠(yuǎn)程連接

-- 授予所有權(quán)限
GRANT ALL PRIVILEGES ON mydb.* TO 'testuser'@'localhost';

-- 授予特定權(quán)限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'testuser'@'localhost';

-- 刷新權(quán)限
FLUSH PRIVILEGES;

SHOW GRANTS FOR 'testuser'@'localhost';

REVOKE ALL PRIVILEGES ON mydb.* FROM 'testuser'@'localhost';

DROP USER 'testuser'@'localhost';

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newpassword';

-- 注冊(cè)（插入用戶(hù)）
INSERT INTO users (username, email, password) VALUES ('newuser', 'user@example.com', MD5('password'));

-- 登錄驗(yàn)證
SELECT * FROM users WHERE username = 'newuser' AND password = MD5('password');

-- 通用分頁(yè)公式：LIMIT (page-1)*pageSize, pageSize
SELECT * FROM users ORDER BY created_at DESC LIMIT 0, 10;   -- 第1頁(yè)
SELECT * FROM users ORDER BY created_at DESC LIMIT 10, 10;  -- 第2頁(yè)

-- 每日新增用戶(hù)統(tǒng)計(jì)
SELECT DATE(created_at) as date, COUNT(*) as new_users
FROM users
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 各狀態(tài)用戶(hù)數(shù)量
SELECT
    CASE status
        WHEN 1 THEN '活躍'
        WHEN 0 THEN '禁用'
        ELSE '未知'
    END as status_name,
    COUNT(*) as count
FROM users
GROUP BY status;

-- 用戶(hù)消費(fèi)排行TOP10
SELECT u.username, SUM(o.amount) as total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 10;

-- 查找重復(fù)的郵箱
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 刪除重復(fù)數(shù)據(jù)（保留ID最小的）
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

-- 導(dǎo)出數(shù)據(jù)到CSV文件（命令行）
mysql -u root -p -e "SELECT * FROM users" mydb > users.csv;

-- 導(dǎo)入CSV文件
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- CASE WHEN 批量更新
UPDATE users
SET status = CASE
    WHEN age < 18 THEN 0
    WHEN age >= 18 AND age < 60 THEN 1
    ELSE 2
END;

-- 查詢(xún)最近7天的數(shù)據(jù)
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 查詢(xún)本月數(shù)據(jù)
SELECT * FROM orders
WHERE YEAR(created_at) = YEAR(NOW())
AND MONTH(created_at) = MONTH(NOW());

-- 查詢(xún)指定時(shí)間段
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 假設(shè)categories表有id, name, parent_id字段
-- 查找某分類(lèi)的所有子分類(lèi)（遞歸，MySQL 8.0+）
WITH RECURSIVE category_tree AS (
    SELECT * FROM categories WHERE id = 1
    UNION ALL
    SELECT c.* FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- 軟刪除（添加is_deleted字段）
UPDATE users SET is_deleted = 1, deleted_at = NOW() WHERE id = 1;
-- 查詢(xún)未刪除的數(shù)據(jù)
SELECT * FROM users WHERE is_deleted = 0;
-- 恢復(fù)數(shù)據(jù)
UPDATE users SET is_deleted = 0, deleted_at = NULL WHERE id = 1;

CONCAT(str1, str2)      -- 連接字符串
LENGTH(str)             -- 字符串長(zhǎng)度
UPPER(str)              -- 轉(zhuǎn)大寫(xiě)
LOWER(str)              -- 轉(zhuǎn)小寫(xiě)
TRIM(str)               -- 去除首尾空格
SUBSTRING(str, pos, len) -- 截取子串
REPLACE(str, old, new)  -- 替換

NOW()                   -- 當(dāng)前日期時(shí)間
CURDATE()               -- 當(dāng)前日期
CURTIME()               -- 當(dāng)前時(shí)間
DATE(datetime)          -- 提取日期部分
YEAR(date)              -- 提取年份
MONTH(date)             -- 提取月份
DAY(date)               -- 提提取日
DATE_FORMAT(date, format) -- 日期格式化
DATE_ADD(date, INTERVAL expr unit) -- 日期增加
DATE_SUB(date, INTERVAL expr unit) -- 日期減少

ABS(num)                -- 絕對(duì)值
ROUND(num, decimals)    -- 四舍五入
CEIL(num)               -- 向上取整
FLOOR(num)              -- 向下取整
RAND()                  -- 隨機(jī)數(shù)