MySQL基础语法 #

-- DDL: 数据定义
CREATE DATABASE mydb;
CREATE TABLE users (id INT, name VARCHAR(50));
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE users;

-- DML: 数据操作
INSERT INTO users VALUES (1, 'John');
UPDATE users SET name = 'Jane' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- DQL: 数据查询
SELECT * FROM users;

-- DCL: 数据控制
GRANT SELECT ON mydb.* TO 'user'@'localhost';
REVOKE SELECT ON mydb.* FROM 'user'@'localhost';

-- TCL: 事务控制
COMMIT;
ROLLBACK;

-- 这是单行注释（推荐）
SELECT * FROM users;

# 这也是单行注释（MySQL特有）
SELECT * FROM users;

/*
 * 这是多行注释
 * 可以跨越多行
 */
SELECT * FROM users;

/*
查询所有用户信息
包括ID、姓名、邮箱
*/
SELECT id, name, email FROM users;

-- 推荐：在复杂查询前添加说明
-- 查询2024年销售额前10的产品
SELECT 
    product_name,
    SUM(amount) AS total_sales
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY product_name
ORDER BY total_sales DESC
LIMIT 10;

-- 推荐：在关键步骤添加注释
SELECT 
    id,
    name,
    -- 计算折扣后价格
    price * 0.9 AS discount_price
FROM products;

-- 合法标识符
CREATE TABLE users (id INT);
CREATE TABLE user_info (id INT);
CREATE TABLE User123 (id INT);
CREATE TABLE _temp (id INT);

-- 非法标识符（会报错）
CREATE TABLE 123users (id INT);    -- 以数字开头
CREATE TABLE user-name (id INT);   -- 包含连字符
CREATE TABLE table (id INT);       -- 使用关键字

-- 使用反引号可以包含特殊字符或关键字
CREATE TABLE `table` (id INT);
CREATE TABLE `user-name` (id INT);
CREATE TABLE `123users` (id INT);

-- 查询时也需要使用反引号
SELECT * FROM `table`;

-- 数据定义
CREATE, ALTER, DROP, TRUNCATE, RENAME

-- 数据操作
INSERT, UPDATE, DELETE, SELECT

-- 数据控制
GRANT, REVOKE

-- 约束
PRIMARY, KEY, FOREIGN, UNIQUE, NOT, NULL, DEFAULT

-- 查询
FROM, WHERE, GROUP, BY, HAVING, ORDER, LIMIT

-- 连接
JOIN, INNER, LEFT, RIGHT, ON

-- 函数
COUNT, SUM, AVG, MAX, MIN

-- 不推荐：使用关键字作为表名或字段名
CREATE TABLE order (
    select INT,
    from VARCHAR(50)
);

-- 推荐：添加前缀或后缀
CREATE TABLE order_info (
    select_count INT,
    from_address VARCHAR(50)
);

-- 或使用反引号
CREATE TABLE `order` (
    `select` INT,
    `from` VARCHAR(50)
);

-- 推荐：小写字母，下划线分隔
CREATE DATABASE my_database;
CREATE DATABASE ecommerce_db;
CREATE DATABASE blog_system;

-- 不推荐
CREATE DATABASE MyDatabase;    -- 大小写混用
CREATE DATABASE my-database;   -- 使用连字符
CREATE DATABASE myDatabase;    -- 驼峰命名

-- 推荐：小写字母，下划线分隔，使用复数
CREATE TABLE users;
CREATE TABLE order_items;
CREATE TABLE product_categories;

-- 不推荐
CREATE TABLE User;            -- 单数
CREATE TABLE OrderItems;      -- 驼峰
CREATE TABLE order-items;     -- 连字符

-- 推荐：小写字母，下划线分隔
CREATE TABLE users (
    id INT PRIMARY KEY,
    user_name VARCHAR(50),
    created_at DATETIME,
    is_active TINYINT(1)
);

-- 不推荐
CREATE TABLE users (
    ID INT,                   -- 大写
    UserName VARCHAR(50),     -- 驼峰
    is-active TINYINT(1)      -- 连字符
);

-- 主键索引：pk_表名
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 唯一索引：uk_表名_字段名
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- 普通索引：idx_表名_字段名
CREATE INDEX idx_users_name ON users(name);

-- 组合索引：idx_表名_字段1_字段2
CREATE INDEX idx_users_name_age ON users(name, age);

-- 外键命名：fk_表名_关联表名
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_users 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 关键字不区分大小写（推荐大写）
SELECT * FROM users;
select * from users;
SELECT * FROM USERS;

-- 推荐写法
SELECT id, name FROM users WHERE age > 18;

-- 取决于操作系统和配置
-- Linux：区分大小写
-- Windows：不区分大小写

-- 查看大小写设置
SHOW VARIABLES LIKE 'lower_case_table_names';

-- 值说明：
-- 0: 区分大小写（Linux默认）
-- 1: 表名存储为小写，名称比较不区分大小写（Windows默认）
-- 2: 表名按原样存储，比较时不区分大小写（macOS默认）

-- 字符串数据区分大小写（取决于排序规则）
SELECT 'ABC' = 'abc';  -- 结果取决于collation

-- 区分大小写比较
SELECT BINARY 'ABC' = 'abc';  -- 返回 0（false）

-- 使用区分大小写的排序规则
CREATE TABLE users (
    name VARCHAR(50) COLLATE utf8mb4_bin
);

-- SQL语句以分号结尾
SELECT * FROM users;

-- 在命令行客户端中，分号表示语句结束
mysql> SELECT * FROM users
    -> ;

-- 推荐：每个子句单独一行
SELECT 
    id,
    name,
    email
FROM users
WHERE age > 18
ORDER BY created_at DESC
LIMIT 10;

-- 不推荐：所有内容在一行
SELECT id, name, email FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;

-- 推荐：使用4个空格或1个Tab缩进
SELECT
    id,
    name,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users
WHERE
    status = 1
    AND created_at > '2024-01-01';

-- 整数类型
TINYINT    -- 1字节，-128到127
SMALLINT   -- 2字节，-32768到32767
MEDIUMINT  -- 3字节
INT        -- 4字节，常用
BIGINT     -- 8字节，大整数

-- 浮点类型
FLOAT      -- 4字节，单精度
DOUBLE     -- 8字节，双精度

-- 定点类型
DECIMAL(M,D)  -- 精确小数，M总位数，D小数位

-- 示例
CREATE TABLE products (
    id INT,
    price DECIMAL(10,2),    -- 10位总长，2位小数
    weight FLOAT
);

-- 定长字符串
CHAR(N)     -- 固定N长度，最大255

-- 变长字符串
VARCHAR(N)  -- 可变长度，最大65535

-- 文本类型
TINYTEXT    -- 255字节
TEXT        -- 65535字节
MEDIUMTEXT  -- 16MB
LONGTEXT    -- 4GB

-- 示例
CREATE TABLE articles (
    title VARCHAR(200),
    content TEXT
);

-- 日期类型
DATE        -- 日期，格式：YYYY-MM-DD
TIME        -- 时间，格式：HH:MM:SS
DATETIME    -- 日期时间，格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP   -- 时间戳，自动更新
YEAR        -- 年份

-- 示例
CREATE TABLE events (
    event_date DATE,
    event_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 布尔类型
BOOLEAN     -- 实际是TINYINT(1)

-- 枚举类型
ENUM('A', 'B', 'C')

-- 集合类型
SET('a', 'b', 'c')

-- JSON类型
JSON

-- 示例
CREATE TABLE users (
    is_active BOOLEAN DEFAULT TRUE,
    status ENUM('active', 'inactive', 'banned'),
    tags SET('tag1', 'tag2', 'tag3'),
    profile JSON
);

SELECT 10 + 5;    -- 加法，结果：15
SELECT 10 - 5;    -- 减法，结果：5
SELECT 10 * 5;    -- 乘法，结果：50
SELECT 10 / 5;    -- 除法，结果：2.0000
SELECT 10 DIV 3;  -- 整除，结果：3
SELECT 10 % 3;    -- 取余，结果：1
SELECT 10 MOD 3;  -- 取余，结果：1

-- 在查询中使用
SELECT 
    product_name,
    price,
    price * 0.9 AS discount_price,
    price * quantity AS total
FROM products;

SELECT 10 = 10;    -- 等于，结果：1
SELECT 10 != 10;   -- 不等于，结果：0
SELECT 10 <> 10;   -- 不等于，结果：0
SELECT 10 > 5;     -- 大于，结果：1
SELECT 10 < 5;     -- 小于，结果：0
SELECT 10 >= 10;   -- 大于等于，结果：1
SELECT 10 <= 10;   -- 小于等于，结果：1

-- 安全等于（NULL值比较）
SELECT NULL <=> NULL;  -- 结果：1
SELECT NULL = NULL;    -- 结果：NULL

SELECT TRUE AND FALSE;   -- 与，结果：0
SELECT TRUE OR FALSE;    -- 或，结果：1
SELECT NOT TRUE;         -- 非，结果：0
SELECT TRUE XOR FALSE;   -- 异或，结果：1

-- 在WHERE中使用
SELECT * FROM users 
WHERE age >= 18 AND status = 1;

-- NULL表示"没有值"或"未知值"
-- NULL不等于0、空字符串或空格

SELECT NULL = NULL;      -- 结果：NULL
SELECT NULL != NULL;     -- 结果：NULL
SELECT NULL IS NULL;     -- 结果：1
SELECT NULL IS NOT NULL; -- 结果：0

-- 错误写法
SELECT * FROM users WHERE name = NULL;    -- 不会返回任何结果

-- 正确写法
SELECT * FROM users WHERE name IS NULL;
SELECT * FROM users WHERE name IS NOT NULL;

-- IFNULL：如果为NULL则返回默认值
SELECT IFNULL(name, 'Unknown') FROM users;

-- COALESCE：返回第一个非NULL值
SELECT COALESCE(phone, email, 'N/A') AS contact FROM users;

-- NULLIF：如果相等则返回NULL
SELECT NULLIF(a, b);