MariaDB基础语法 #

-- SQL语句基本结构
SELECT column_name
FROM table_name
WHERE condition;

-- 语句以分号结尾
-- 不区分大小写（关键字）
-- 空格和换行不影响执行

SQL语句分类
├── DDL（数据定义语言）
│   ├── CREATE - 创建
│   ├── ALTER - 修改
│   ├── DROP - 删除
│   └── TRUNCATE - 清空
├── DML（数据操作语言）
│   ├── SELECT - 查询
│   ├── INSERT - 插入
│   ├── UPDATE - 更新
│   └── DELETE - 删除
├── DCL（数据控制语言）
│   ├── GRANT - 授权
│   └── REVOKE - 撤销
└── TCL（事务控制语言）
    ├── START TRANSACTION - 开始事务
    ├── COMMIT - 提交
    ├── ROLLBACK - 回滚
    └── SAVEPOINT - 保存点

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

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

/*
 * 这是多行注释
 * 可以跨越多行
 * 用于详细说明
 */
SELECT * FROM users;

/* 单行块注释 */
SELECT * FROM users;

-- 文件头注释
/*
 * 文件名: user_queries.sql
 * 描述: 用户相关查询
 * 作者: developer
 * 日期: 2024-01-15
 * 版本: 1.0
 */

-- 函数注释
-- 获取用户总数
-- 参数: status - 用户状态（可选）
-- 返回: 用户数量
SELECT COUNT(*) FROM users WHERE status = 1;

-- 复杂查询注释
SELECT 
    u.id,
    u.name,
    -- 计算用户订单总数
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE 
    -- 只查询活跃用户
    u.status = 1
    -- 注册时间在一年内
    AND u.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 合法的标识符
CREATE TABLE users (
    id INT,
    user_name VARCHAR(50),
    user_email VARCHAR(100)
);

-- 以字母或下划线开头
CREATE TABLE _temp_table (id INT);
CREATE TABLE user_info (id INT);

-- 可以包含数字（不能以数字开头）
CREATE TABLE user2 (id INT);
CREATE TABLE table_2024 (id INT);

-- 区分大小写取决于操作系统
-- Linux: 区分大小写
-- Windows: 不区分大小写
-- macOS: 取决于文件系统

-- 使用反引号包含特殊字符
CREATE TABLE `user-info` (
    `user-id` INT,
    `user name` VARCHAR(50)
);

-- 包含关键字的标识符
CREATE TABLE `order` (
    `select` INT,
    `from` VARCHAR(50),
    `where` TEXT
);

-- 推荐做法：避免使用关键字和特殊字符
CREATE TABLE orders (
    select_id INT,
    from_address VARCHAR(50),
    where_clause TEXT
);

-- 推荐命名规范

-- 表名：小写，下划线分隔，复数形式
CREATE TABLE users (id INT);
CREATE TABLE order_items (id INT);
CREATE TABLE product_categories (id INT);

-- 列名：小写，下划线分隔
CREATE TABLE users (
    user_id INT,
    first_name VARCHAR(50),
    created_at DATETIME
);

-- 主键：id 或 表名_id
CREATE TABLE users (
    id INT PRIMARY KEY,
    -- 或
    user_id INT PRIMARY KEY
);

-- 外键：关联表名_id
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 布尔字段：is_xxx, has_xxx
CREATE TABLE users (
    is_active BOOLEAN,
    has_verified BOOLEAN
);

-- 日期时间：xxx_at, xxx_date
CREATE TABLE users (
    created_at DATETIME,
    updated_at DATETIME,
    birth_date DATE
);

-- MariaDB保留关键字（部分）
-- ADD, ALL, ALTER, AND, AS, ASC, BETWEEN, BY
-- CASE, CREATE, DATABASE, DEFAULT, DELETE, DESC
-- DISTINCT, DROP, ELSE, EXISTS, FROM, GROUP, HAVING
-- IN, INDEX, INSERT, INTO, IS, JOIN, KEY, LIKE
-- LIMIT, NOT, NULL, ON, OR, ORDER, SELECT, SET
-- TABLE, THEN, UNION, UPDATE, VALUES, WHERE

-- 使用保留关键字作为标识符需要反引号
CREATE TABLE `table` (
    `select` INT,
    `from` VARCHAR(50),
    `where` TEXT
);

-- 非保留关键字可以直接使用
CREATE TABLE status (
    name VARCHAR(50),
    value INT
);

-- 但建议避免使用
CREATE TABLE user_status (
    status_name VARCHAR(50),
    status_value INT
);

-- 关键字不区分大小写
SELECT * FROM users;
select * from users;
Select * From Users;

-- 标识符大小写取决于系统设置
-- 查看设置
SHOW VARIABLES LIKE 'lower_case_table_names';

-- lower_case_table_names = 0: 区分大小写（Linux默认）
-- lower_case_table_names = 1: 不区分，存储为小写（Windows默认）
-- lower_case_table_names = 2: 不区分，保留原样（macOS默认）

-- 推荐做法：统一使用小写
CREATE TABLE users (id INT);
SELECT * FROM users;

-- 整数类型
CREATE TABLE numbers (
    tiny_col TINYINT,        -- 1字节，-128到127
    small_col SMALLINT,      -- 2字节，-32768到32767
    medium_col MEDIUMINT,    -- 3字节
    int_col INT,             -- 4字节
    bigint_col BIGINT        -- 8字节
);

-- 无符号整数
CREATE TABLE unsigned_numbers (
    positive_int INT UNSIGNED
);

-- 浮点类型
CREATE TABLE floats (
    float_col FLOAT,         -- 单精度
    double_col DOUBLE        -- 双精度
);

-- 定点类型
CREATE TABLE decimals (
    price DECIMAL(10, 2)     -- 10位，2位小数
);

-- 定长字符串
CREATE TABLE fixed_strings (
    code CHAR(10),           -- 固定10字符
    status CHAR(1)           -- 固定1字符
);

-- 变长字符串
CREATE TABLE variable_strings (
    name VARCHAR(100),       -- 最多100字符
    description TEXT         -- 长文本
);

-- 二进制数据
CREATE TABLE binary_data (
    data BLOB
);

-- 日期时间类型
CREATE TABLE dates (
    birth_date DATE,                    -- 日期
    meeting_time TIME,                  -- 时间
    created_at DATETIME,                -- 日期时间
    updated_at TIMESTAMP,               -- 时间戳
    publish_year YEAR                   -- 年份
);

-- 自动更新
CREATE TABLE auto_dates (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 布尔类型
CREATE TABLE flags (
    is_active BOOLEAN,      -- TINYINT(1)别名
    is_deleted BOOL         -- 同上
);

-- 枚举类型
CREATE TABLE status_table (
    status ENUM('active', 'inactive', 'pending')
);

-- 集合类型
CREATE TABLE tags_table (
    tags SET('tag1', 'tag2', 'tag3', 'tag4')
);

-- JSON类型
CREATE TABLE json_data (
    data JSON
);

SELECT 10 + 5;    -- 加法：15
SELECT 10 - 5;    -- 减法：5
SELECT 10 * 5;    -- 乘法：50
SELECT 10 / 5;    -- 除法：2.0000
SELECT 10 DIV 5;  -- 整除：2
SELECT 10 % 3;    -- 取模：1
SELECT 10 MOD 3;  -- 取模：1
SELECT -5;        -- 负号：-5

-- 等于
SELECT 1 = 1;     -- 1 (true)
SELECT 1 <=> 1;   -- 1 (安全等于，支持NULL比较)

-- 不等于
SELECT 1 != 2;    -- 1
SELECT 1 <> 2;    -- 1

-- 大于、小于
SELECT 2 > 1;     -- 1
SELECT 1 < 2;     -- 1
SELECT 2 >= 2;    -- 1
SELECT 1 <= 1;    -- 1

-- NULL判断
SELECT NULL IS NULL;        -- 1
SELECT 1 IS NOT NULL;       -- 1
SELECT NULL <=> NULL;       -- 1

-- BETWEEN
SELECT 5 BETWEEN 1 AND 10;  -- 1

-- IN
SELECT 1 IN (1, 2, 3);      -- 1

-- LIKE
SELECT 'hello' LIKE 'h%';   -- 1

-- REGEXP
SELECT 'hello' REGEXP '^h'; -- 1

-- AND
SELECT 1 AND 1;   -- 1
SELECT 1 AND 0;   -- 0

-- OR
SELECT 1 OR 0;    -- 1
SELECT 0 OR 0;    -- 0

-- NOT
SELECT NOT 0;     -- 1
SELECT NOT 1;     -- 0

-- XOR
SELECT 1 XOR 0;   -- 1
SELECT 1 XOR 1;   -- 0

-- 按位与
SELECT 5 & 3;     -- 1 (0101 & 0011 = 0001)

-- 按位或
SELECT 5 | 3;     -- 7 (0101 | 0011 = 0111)

-- 按位异或
SELECT 5 ^ 3;     -- 6 (0101 ^ 0011 = 0110)

-- 按位取反
SELECT ~5;        -- -6

-- 左移
SELECT 1 << 2;    -- 4

-- 右移
SELECT 4 >> 1;    -- 2

SELECT 
    price,
    quantity,
    price * quantity AS total,
    price * quantity * 0.9 AS discounted_total
FROM products;

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World

-- 使用CONCAT_WS
SELECT CONCAT_WS('-', '2024', '01', '15');  -- 2024-01-15

-- 字符串函数
SELECT 
    UPPER('hello'),      -- HELLO
    LOWER('HELLO'),      -- hello
    LENGTH('hello'),     -- 5
    SUBSTRING('hello', 1, 3);  -- hel

-- CASE WHEN
SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

-- IF函数
SELECT 
    name,
    IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

-- IFNULL
SELECT IFNULL(NULL, 'default');  -- default

-- NULLIF
SELECT NULLIF(1, 1);  -- NULL
SELECT NULLIF(1, 2);  -- 1

-- 日期计算
SELECT 
    NOW(),
    DATE_ADD(NOW(), INTERVAL 7 DAY),
    DATE_SUB(NOW(), INTERVAL 1 MONTH),
    DATEDIFF('2024-12-31', '2024-01-01');

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 提取日期部分
SELECT 
    YEAR(NOW()),
    MONTH(NOW()),
    DAY(NOW()),
    HOUR(NOW()),
    MINUTE(NOW());

-- 单引号
SELECT 'Hello World';

-- 双引号（需要SQL模式支持）
SELECT "Hello World";

-- 转义字符
SELECT 'It\'s a test';
SELECT "He said \"Hello\"";

-- 转义序列
SELECT 'Line1\nLine2';     -- 换行
SELECT 'Tab\there';        -- 制表符

-- 十六进制
SELECT 0x48656C6C6F;       -- Hello
SELECT X'48656C6C6F';      -- Hello

-- 整数
SELECT 123;
SELECT -123;

-- 浮点数
SELECT 3.14;
SELECT -3.14;

-- 科学计数法
SELECT 1.5E2;    -- 150
SELECT 1.5E-2;   -- 0.015

-- 十六进制
SELECT 0xFF;     -- 255

-- 日期
SELECT DATE '2024-01-15';
SELECT '2024-01-15';

-- 时间
SELECT TIME '10:30:00';
SELECT '10:30:00';

-- 日期时间
SELECT TIMESTAMP '2024-01-15 10:30:00';
SELECT '2024-01-15 10:30:00';

SELECT TRUE, FALSE;     -- 1, 0
SELECT true, false;     -- 1, 0
SELECT TRUE IS TRUE;    -- 1
SELECT FALSE IS FALSE;  -- 1

-- NULL表示未知或缺失
SELECT NULL;

-- NULL运算结果都是NULL
SELECT NULL + 1;        -- NULL
SELECT NULL = NULL;     -- NULL
SELECT NULL <=> NULL;   -- 1

-- 判断NULL
SELECT NULL IS NULL;    -- 1
SELECT 1 IS NOT NULL;   -- 1

-- 定义用户变量（会话级别）
SET @name = 'John';
SET @age = 30;
SET @total = (SELECT COUNT(*) FROM users);

-- 使用变量
SELECT @name, @age;
SELECT * FROM users WHERE name = @name;

-- 计算并赋值
SELECT @total := COUNT(*) FROM users;
SELECT @rank := @rank + 1 AS rank, name FROM users, (SELECT @rank := 0) r;

-- 在存储过程中使用
DELIMITER //
CREATE PROCEDURE test_variables()
BEGIN
    DECLARE local_name VARCHAR(50) DEFAULT 'Default';
    DECLARE local_count INT;
    
    SET local_count = (SELECT COUNT(*) FROM users);
    
    SELECT local_name, local_count;
END //
DELIMITER ;

-- 查看系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'character%';

-- 会话级修改
SET SESSION sql_mode = 'STRICT_ALL_TABLES';

-- 全局级修改
SET GLOBAL max_connections = 500;

-- 查看状态
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';

-- 查看当前SQL模式
SELECT @@sql_mode;

-- 查看全局SQL模式
SELECT @@GLOBAL.sql_mode;

-- 查看会话SQL模式
SELECT @@SESSION.sql_mode;

-- 设置SQL模式
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 多个模式
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';

-- 常用模式说明
-- STRICT_TRANS_TABLES: 严格模式，非法值报错
-- NO_ZERO_IN_DATE: 不允许日期中有0
-- NO_ZERO_DATE: 不允许0000-00-00日期
-- ONLY_FULL_GROUP_BY: GROUP BY必须包含所有非聚合列
-- NO_ENGINE_SUBSTITUTION: 禁用引擎替换

-- 宽松模式
SET sql_mode = '';

-- 严格模式
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 示例对比
CREATE TABLE test_strict (
    id INT PRIMARY KEY,
    name VARCHAR(5)
);

-- 宽松模式：截断数据，警告
INSERT INTO test_strict VALUES (1, 'Hello World');
-- Query OK, 1 row affected, 1 warning

-- 严格模式：报错
SET sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO test_strict VALUES (2, 'Hello World');
-- ERROR: Data too long for column 'name'

-- 默认使用分号作为语句分隔符
SELECT * FROM users;
SELECT * FROM orders;

-- 修改分隔符（用于存储过程、触发器等）
DELIMITER //

CREATE PROCEDURE get_users()
BEGIN
    SELECT * FROM users;
    SELECT * FROM orders;
END //

DELIMITER ;

-- 调用存储过程
CALL get_users();