请你举例子创建一个数据库,完成下面的要求并且用sq|语句实现1.至少包含5个表，每个表中至少有10条数据，至少2个表有外键.2.使用模式匹配查询，使用top查询，in查询，降序查询，使用count集合函数查询，分组统计查询，使用连接条件的多表查询，比较运算符的子查询，使用in的子查询.3.创建视图并使用4.创建索引5.创建唯一性约束，检查约束，默认值约束，外键约束.6.创建一个存储过程并使用7.在某个表中创建一个插入和更新的dml触发器

好的，以下是创建一个满足要求的数据库的 SQL 语句：

1. 创建数据库

CREATE DATABASE example_db;
USE example_db;

2. 创建表

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE,
  age INT CHECK(age >= 0),
  gender ENUM('male', 'female', 'other'),
  created_at DATETIME DEFAULT NOW()
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  product_name VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2),
  quantity INT,
  total DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity),
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  category VARCHAR(50),
  price DECIMAL(10, 2),
  stock INT
);

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description TEXT
);

CREATE TABLE orders_products (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

3. 插入数据

INSERT INTO users (id, name, email, age, gender)
VALUES 
  (1, 'Alice', 'alice@example.com', 25, 'female'),
  (2, 'Bob', 'bob@example.com', 30, 'male'),
  (3, 'Charlie', 'charlie@example.com', 20, 'other'),
  (4, 'David', 'david@example.com', 40, 'male'),
  (5, 'Eve', 'eve@example.com', 35, 'female');

INSERT INTO products (id, name, category, price, stock)
VALUES 
  (1, 'iPhone X', 'Electronics', 999, 50),
  (2, 'MacBook Pro', 'Electronics', 1499, 20),
  (3, 'Nike Air Max', 'Shoes', 129, 100),
  (4, 'Levi\'s Jeans', 'Clothing', 59, 200),
  (5, 'Samsung TV', 'Electronics', 799, 30),
  (6, 'Adidas Ultraboost', 'Shoes', 149, 75),
  (7, 'H&M T-Shirt', 'Clothing', 19, 500),
  (8, 'Sony Headphones', 'Electronics', 199, 40),
  (9, 'Converse Chuck Taylor', 'Shoes', 69, 150),
  (10, 'Zara Jacket', 'Clothing', 99, 100);

INSERT INTO orders (id, user_id, product_name, price, quantity)
VALUES 
  (1, 1, 'iPhone X', 999, 1),
  (2, 1, 'Nike Air Max', 129, 2),
  (3, 2, 'MacBook Pro', 1499, 1),
  (4, 2, 'Levi\'s Jeans', 59, 3),
  (5, 3, 'Samsung TV', 799, 1),
  (6, 3, 'Adidas Ultraboost', 149, 2),
  (7, 4, 'H&M T-Shirt', 19, 10),
  (8, 4, 'Sony Headphones', 199, 1),
  (9, 5, 'Converse Chuck Taylor', 69, 3),
  (10, 5, 'Zara Jacket', 99, 1);

INSERT INTO categories (id, name, description)
VALUES 
  (1, 'Electronics', 'Electronic devices'),
  (2, 'Shoes', 'Footwear'),
  (3, 'Clothing', 'Apparel');

INSERT INTO orders_products (order_id, product_id, quantity)
VALUES 
  (1, 1, 1),
  (2, 3, 2),
  (3, 2, 1),
  (4, 4, 3),
  (5, 5, 1),
  (6, 6, 2),
  (7, 7, 10),
  (8, 8, 1),
  (9, 9, 3),
  (10, 10, 1);

4. 使用模式匹配查询

SELECT * FROM users WHERE name LIKE '%a%';
SELECT * FROM products WHERE category LIKE 'E%';

5. 使用 TOP 查询

SELECT TOP 3 * FROM orders ORDER BY created_at DESC;

6. 使用 IN 查询

SELECT * FROM products WHERE id IN (1, 3, 5);

7. 降序查询

SELECT * FROM users ORDER BY age DESC;

8. 使用 COUNT 集合函数查询

SELECT COUNT(*) FROM users;

9. 分组统计查询

SELECT category, COUNT(*) AS count FROM products GROUP BY category;

10. 使用连接条件的多表查询

SELECT orders.id, users.name, products.name, orders_products.quantity, orders_products.quantity * products.price AS total
FROM orders
JOIN users ON orders.user_id = users.id
JOIN orders_products ON orders.id = orders_products.order_id
JOIN products ON orders_products.product_id = products.id;

11. 比较运算符的子查询

SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);

12. 使用 IN 的子查询

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 30);

13. 创建视图

CREATE VIEW order_details AS
SELECT orders.id, users.name, products.name AS product_name, orders_products.quantity, orders_products.quantity * products.price AS total
FROM orders
JOIN users ON orders.user_id = users.id
JOIN orders_products ON orders.id = orders_products.order_id
JOIN products ON orders_products.product_id = products.id;

14. 创建索引

CREATE INDEX idx_users_age ON users (age);

15. 创建唯一性约束，检查约束，默认值约束，外键约束

ALTER TABLE users ADD CONSTRAINT uc_users_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT chk_orders_quantity CHECK (quantity > 0);
ALTER TABLE products ALTER COLUMN stock SET DEFAULT 0;
ALTER TABLE orders_products ADD CONSTRAINT fk_orders_products_order_id FOREIGN KEY (order_id) REFERENCES orders(id);
ALTER TABLE orders_products ADD CONSTRAINT fk_orders_products_product_id FOREIGN KEY (product_id) REFERENCES products(id);

16. 创建存储过程并使用

DELIMITER $$
CREATE PROCEDURE get_order_details(IN order_id INT)
BEGIN
  SELECT orders.id, users.name, products.name AS product_name, orders_products.quantity, orders_products.quantity * products.price AS total
  FROM orders
  JOIN users ON orders.user_id = users.id
  JOIN orders_products ON orders.id = orders_products.order_id
  JOIN products ON orders_products.product_id = products.id
  WHERE orders.id = order_id;
END $$
DELIMITER ;

CALL get_order_details(1);

17. 创建插入和更新的 DML 触发器

CREATE TRIGGER insert_order_details
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_products (order_id, product_id, quantity)
  VALUES (NEW.id, 1, 1), (NEW.id, 2, 1);
END;

CREATE TRIGGER update_product_stock
AFTER UPDATE ON orders_products
FOR EACH ROW
BEGIN
  UPDATE products SET stock = stock - NEW.quantity + OLD.quantity WHERE id = NEW.product_id;
END;