编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

-- 1. 创建表示例
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 修改表示例
-- 添加字段
ALTER TABLE users ADD COLUMN last_login DATETIME;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) UNIQUE;

-- 删除字段
ALTER TABLE users DROP COLUMN last_login;

-- 3. 创建带索引的表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT, -- 添加description字段
    INDEX idx_name (product_name),
    INDEX idx_price (price DESC),
    FULLTEXT ft_description (description) -- 全文索引
);

-- 4. 用命令方式建立索引
CREATE INDEX idx_category ON products(category);

-- 复合索引
CREATE INDEX idx_price_cat ON products(price, category);

-- 5. 删除索引
DROP INDEX idx_price_cat ON products;

-- 6. 插入测试数据
INSERT INTO users (username, email, age) VALUES
('user1', 'user1@example.com', 25),
('user2', 'user2@example.com', 30),
('user3', 'user3@example.com', 22);

INSERT INTO products (product_name, category, price, stock) VALUES
('Laptop', 'Electronics', 899.99, 100),
('Mouse', 'Accessories', 29.99, 500),
('Keyboard', 'Accessories', 49.99, 300);

-- 7. 查询示例
SELECT * FROM users WHERE age > 23;
SELECT * FROM products WHERE category = 'Accessories' ORDER BY price;

-- 8. 查看索引信息
SHOW INDEX FROM products;

-- 9. 删除表
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS products;