很重要！MySQL的复杂数据类型，学会使用再也不怕复杂场景了、枚举(ENUM)、集合(SET)、JSON、空间数据类型

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    status ENUM('active', 'inactive', 'suspended') NOT NULL
);

CREATE TABLE test (
    name VARCHAR(40),
    sex ENUM('male', 'female')  -- 定义sex列为ENUM类型，只能存储'male'或'female'
);

INSERT INTO test (name, sex) VALUES ('a', 'male'), ('b', 'female'), ('c', 'male');

INSERT INTO test (name, sex) VALUES ('d', 'other');  -- 会报错，因为'other'不在ENUM列表中

SELECT * FROM test WHERE sex = 'male';  -- 查询sex为'male'的记录

UPDATE test SET sex = 'female' WHERE name = 'a';  -- 将name为'a'的记录的sex更新为'female'

CREATE TABLE test2 (
    numbers ENUM('0', '1', '2')
);

INSERT INTO test2 (numbers) VALUES (2), ('2'), ('3');  -- '3'不是合法值，会尝试使用索引值，结果可能不是预期的

CREATE TABLE test3 (
    status ENUM('active', 'inactive', 'banned') NULL  -- 允许NULL值
);

INSERT INTO test3 (status) VALUES (NULL), (''), ('active');  -- 插入NULL、空字符串和合法枚举值

SELECT * FROM test ORDER BY CAST(sex AS CHAR);  -- 按照sex列的字母顺序排序

ALTER TABLE test MODIFY COLUMN sex ENUM('male', 'female', 'other');  -- 向sex列添加新值'other'

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    status ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active'  -- 定义status列为ENUM类型，默认值为'active'
);

-- 插入数据
INSERT INTO users (username, status) VALUES ('alice', 'active'), ('bob', 'inactive'), ('charlie', 'banned');

-- 查询数据
SELECT * FROM users WHERE status = 'active';  -- 查询status为'active'的用户

-- 更新数据
UPDATE users SET status = 'inactive' WHERE username = 'alice';  -- 将username为'alice'的用户的status更新为'inactive'

-- 添加新枚举值（注意：在实际应用中，应谨慎操作，避免数据不一致）
ALTER TABLE users MODIFY COLUMN status ENUM('active', 'inactive', 'banned', 'pending');  -- 向status列添加新值'pending'

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hobbies SET('reading', 'swimming', 'traveling', 'cooking')
);

-- 插入单个值
INSERT INTO users (name, hobbies) VALUES ('Alice', 'reading');

-- 插入多个值
INSERT INTO users (name, hobbies) VALUES ('Bob', 'swimming,traveling');

-- 插入空集合（即不指定任何值）
INSERT INTO users (name, hobbies) VALUES ('Charlie', '');

SELECT * FROM users;

-- 添加新的值（如果值不存在则添加，存在则忽略）
UPDATE users SET hobbies = CONCAT(hobbies, ',cooking') WHERE name = 'Alice';

-- 删除特定的值（如果值存在则删除，不存在则忽略）
UPDATE users SET hobbies = REPLACE(hobbies, 'swimming,', '') WHERE name = 'Bob';
UPDATE users SET hobbies = TRIM(BOTH ',' FROM REPLACE(hobbies, ',swimming', '')) WHERE name = 'Bob' AND hobbies LIKE '%,swimming,%' OR hobbies = 'swimming';

-- 替换为新的值集合（完全替换原有的值）
UPDATE users SET hobbies = 'reading,cooking' WHERE name = 'Charlie';

-- 查找喜欢阅读的用户
SELECT * FROM users WHERE FIND_IN_SET('reading', hobbies) > 0;

-- 查找喜欢游泳或旅行的用户（注意：FIND_IN_SET 不支持 OR 逻辑，需要分别查询然后合并结果或使用 LIKE）
SELECT * FROM users WHERE FIND_IN_SET('swimming', hobbies) > 0 OR FIND_IN_SET('traveling', hobbies) > 0;
-- 或者使用 LIKE（但这种方法效率较低，且不如 FIND_IN_SET 准确）
SELECT * FROM users WHERE hobbies LIKE '%swimming%' OR hobbies LIKE '%traveling%';

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hobbies SET('reading', 'swimming', 'traveling', 'cooking')
);

-- 插入数据
INSERT INTO users (name, hobbies) VALUES ('Alice', 'reading');
INSERT INTO users (name, hobbies) VALUES ('Bob', 'swimming,traveling');
INSERT INTO users (name, hobbies) VALUES ('Charlie', '');

-- 查询数据
SELECT * FROM users;

-- 更新数据
UPDATE users SET hobbies = CONCAT(hobbies, ',cooking') WHERE name = 'Alice';
UPDATE users SET hobbies = REPLACE(hobbies, 'swimming,', '') WHERE name = 'Bob';
UPDATE users SET hobbies = TRIM(BOTH ',' FROM REPLACE(hobbies, ',swimming', '')) WHERE name = 'Bob' AND hobbies LIKE '%,swimming,%' OR hobbies = 'swimming';
UPDATE users SET hobbies = 'reading,cooking' WHERE name = 'Charlie';

-- 再次查询数据
SELECT * FROM users;

-- 查找喜欢阅读的用户
SELECT * FROM users WHERE FIND_IN_SET('reading', hobbies) > 0;

CREATE TABLE mytest (
    id BIGINT,
    other JSON
);

INSERT INTO mytest VALUES (1, '[1, "abc", null, true, "00:00:00.000000"]');

SELECT JSON_EXTRACT(other, '$[0]') AS val FROM mytest;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    details JSON  -- 定义details列为JSON类型
);

INSERT INTO employees (name, details) VALUES 
('Alice', '{"age": 30, "department": "HR", "skills": ["communication", "organization"]}'),
('Bob', '{"age": 25, "department": "Engineering", "skills": ["coding", "debugging", "designing"]}');

-- 查询所有员工
SELECT * FROM employees;

-- 查询年龄大于25岁的员工
SELECT * FROM employees WHERE JSON_EXTRACT(details, '$.age') > 25;

-- 查询属于HR部门的员工
SELECT * FROM employees WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$.department')) = 'HR';

-- 为Alice添加一个新的技能
UPDATE employees SET details = JSON_SET(details, '$.skills', JSON_ARRAY_APPEND(JSON_EXTRACT(details, '$.skills'), '$', 'teaching')) WHERE name = 'Alice';

-- 将Bob的部门更改为Sales
UPDATE employees SET details = JSON_REPLACE(details, '$.department', 'Sales') WHERE name = 'Bob';

-- 删除Alice的年龄信息
UPDATE employees SET details = JSON_REMOVE(details, '$.age') WHERE name = 'Alice';

-- 创建表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_details JSON  -- 定义order_details列为JSON类型
);

-- 插入数据
INSERT INTO orders (customer_name, order_details) VALUES 
('John Doe', '{"products": [{"product_id": 1, "quantity": 2, "price": 100}, {"product_id": 2, "quantity": 1, "price": 200}], "total_amount": 400}'),
('Jane Smith', '{"products": [{"product_id": 3, "quantity": 3, "price": 50}], "total_amount": 150}');

-- 查询所有订单
SELECT * FROM orders;

-- 查询总金额大于200的订单
SELECT * FROM orders WHERE JSON_EXTRACT(order_details, '$.total_amount') > 200;

-- 查询包含特定产品的订单
SELECT * FROM orders WHERE JSON_CONTAINS(order_details->'$.products', '{"product_id": 2}', '$');

-- 更新订单详情（为John Doe的订单添加一个新产品）
UPDATE orders SET order_details = JSON_SET(order_details, '$.products', JSON_ARRAY_APPEND(JSON_EXTRACT(order_details, '$.products'), '$', '{"product_id": 4, "quantity": 1, "price": 150}')) WHERE customer_name = 'John Doe';

-- 删除订单中的某个产品（删除Jane Smith订单中的产品ID为3的产品）
SET @json_path = '$.products[* ? (@.product_id == 3)]';  -- JSON路径表达式，匹配product_id为3的产品
UPDATE orders SET order_details = JSON_REMOVE(order_details, @json_path) WHERE customer_name = 'Jane Smith';

CREATE TABLE spatial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    geometry_column GEOMETRY
);

INSERT INTO spatial_data (geometry_column) VALUES (ST_GeomFromText('POINT(1 1)'));

SELECT * FROM spatial_data WHERE ST_Contains(geometry_column, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT,
    path LINESTRING,
    area POLYGON,
    spots MULTIPOINT,
    routes MULTILINESTRING,
    regions MULTIPOLYGON,
    geo_collection GEOMETRYCOLLECTION
);

-- 插入一个点
INSERT INTO locations (name, location) VALUES ('Central Park', ST_GeomFromText('POINT(40.7829 -73.9654)'));

-- 插入一条线
INSERT INTO locations (name, path) VALUES ('Fifth Avenue', ST_GeomFromText('LINESTRING(40.7128 -74.0060, 40.7829 -73.9654)'));

-- 插入一个多边形
INSERT INTO locations (name, area) VALUES ('Central Park Polygon', ST_GeomFromText('POLYGON((40.786 -73.965, 40.786 -73.964, 40.785 -73.964, 40.785 -73.965, 40.786 -73.965))'));

-- 查询与 Central Park 距离小于 1 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 1000;

-- 查询包含某个点的多边形
SELECT name FROM locations
WHERE ST_Contains(area, ST_GeomFromText('POINT(40.785 -73.965)'));

-- 将 Central Park 的位置更新为一个新的点
UPDATE locations SET location = ST_GeomFromText('POINT(40.7830 -73.9653)') WHERE name = 'Central Park';

-- 删除名为 Fifth Avenue 的路径
DELETE FROM locations WHERE name = 'Fifth Avenue';

-- 创建表
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT,
    SPATIAL INDEX(location)  -- 创建空间索引
);

-- 插入数据
INSERT INTO locations (name, location) VALUES ('Central Park', ST_GeomFromText('POINT(40.7829 -73.9654)'));
INSERT INTO locations (name, location) VALUES ('Times Square', ST_GeomFromText('POINT(40.758 -73.9855)'));

-- 查询与 Central Park 距离小于 2 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 2000;

-- 更新 Times Square 的位置
UPDATE locations SET location = ST_GeomFromText('POINT(40.7581 -73.9854)') WHERE name = 'Times Square';

-- 再次查询与 Central Park 距离小于 2 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 2000;