所有要求都是用sql sever语句实现 创建一个数据库，至少里面有5个表，每个表至少有十条数据，至少有两个表具有外键 ，使用模式匹配查询，使用top查询，in查询，降序查询，使用count集合函数查询，分组统计查询，使用连接条件的多表查询，比较运算符的子查询，使用in的子查询 创建视图并使用 创建索引 创建唯一性约束，检查约束，默认值约束，外键约束 创建一个存储过程并使用 在某个表上创建一个插入和更新的dml触发器

好的，以下是 SQL Server 语句实现：

1. 创建数据库：

CREATE DATABASE mydatabase;
USE mydatabase;

2. 创建表并插入数据：

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50)
);

INSERT INTO customers (id, name, age, city)
VALUES (1, 'John', 25, 'New York'),
       (2, 'Mary', 30, 'Los Angeles'),
       (3, 'David', 40, 'Chicago'),
       (4, 'Sarah', 35, 'Houston'),
       (5, 'Michael', 28, 'Miami'),
       (6, 'Jennifer', 32, 'San Francisco'),
       (7, 'Robert', 45, 'Atlanta'),
       (8, 'Lisa', 27, 'Seattle'),
       (9, 'Kevin', 38, 'Dallas'),
       (10, 'Jessica', 33, 'Boston');

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product VARCHAR(50),
  price DECIMAL(10, 2),
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO orders (id, customer_id, product, price, order_date)
VALUES (1, 1, 'Phone', 500, '2021-05-01'),
       (2, 1, 'Laptop', 1000, '2021-06-01'),
       (3, 2, 'Tablet', 700, '2021-05-15'),
       (4, 3, 'TV', 1200, '2021-05-20'),
       (5, 4, 'Headphones', 200, '2021-06-05'),
       (6, 5, 'Camera', 800, '2021-06-10'),
       (7, 6, 'Smartwatch', 300, '2021-06-15'),
       (8, 7, 'Speaker', 150, '2021-07-01'),
       (9, 8, 'Printer', 400, '2021-07-05'),
       (10, 9, 'Monitor', 600, '2021-07-10');

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

INSERT INTO products (id, name, category, price)
VALUES (1, 'Phone', 'Electronics', 500),
       (2, 'Laptop', 'Electronics', 1000),
       (3, 'Tablet', 'Electronics', 700),
       (4, 'TV', 'Electronics', 1200),
       (5, 'Headphones', 'Accessories', 200),
       (6, 'Camera', 'Electronics', 800),
       (7, 'Smartwatch', 'Accessories', 300),
       (8, 'Speaker', 'Electronics', 150),
       (9, 'Printer', 'Electronics', 400),
       (10, 'Monitor', 'Electronics', 600);

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO categories (id, name)
VALUES (1, 'Electronics'),
       (2, 'Accessories');

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John', 'Sales', 5000),
       (2, 'Mary', 'Marketing', 6000),
       (3, 'David', 'IT', 7000),
       (4, 'Sarah', 'HR', 4000),
       (5, 'Michael', 'Finance', 8000),
       (6, 'Jennifer', 'Operations', 5500),
       (7, 'Robert', 'Engineering', 9000),
       (8, 'Lisa', 'Legal', 4500),
       (9, 'Kevin', 'Customer Service', 3500),
       (10, 'Jessica', 'Management', 10000);

3. 使用模式匹配查询：

SELECT name, age, city
FROM customers
WHERE name LIKE 'J%';

4. 使用 TOP 查询：

SELECT TOP 5 name, age, city
FROM customers
ORDER BY age DESC;

5. 使用 IN 查询：

SELECT name, product, price
FROM orders
WHERE customer_id IN (1, 3, 5);

6. 使用降序查询：

SELECT name, salary
FROM employees
ORDER BY salary DESC;

7. 使用 COUNT 集合函数查询：

SELECT COUNT(*) AS num_customers
FROM customers;

8. 分组统计查询：

SELECT category, AVG(price) AS avg_price, COUNT(*) AS num_products
FROM products
GROUP BY category;

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

SELECT customers.name, orders.product, orders.price
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

10. 比较运算符的子查询：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

11. 使用 IN 的子查询：

SELECT name, department
FROM employees
WHERE department IN (SELECT name FROM categories WHERE name LIKE 'M%');

12. 创建视图并使用：

CREATE VIEW customer_orders AS
SELECT customers.name, orders.product, orders.price
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

SELECT * FROM customer_orders;

13. 创建索引：

CREATE INDEX idx_customer_age ON customers(age);

SELECT name, age
FROM customers
WHERE age BETWEEN 30 AND 40;

14. 创建约束：

ALTER TABLE customers
ADD CONSTRAINT uc_customer_name UNIQUE (name);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(id);

ALTER TABLE employees
ADD CONSTRAINT chk_employee_salary CHECK (salary >= 3000 AND salary <= 10000);

ALTER TABLE products
ADD CONSTRAINT df_product_price DEFAULT 0;

15. 创建存储过程并使用：

CREATE PROCEDURE get_customer_orders
@customer_id INT
AS
BEGIN
  SELECT product, price, order_date
  FROM orders
  WHERE customer_id = @customer_id;
END;

EXEC get_customer_orders 1;

16. 创建触发器：

CREATE TRIGGER tr_customer_orders
ON orders
AFTER INSERT, UPDATE
AS
BEGIN
  UPDATE customers
  SET last_order_date = GETDATE()
  WHERE customers.id = inserted.customer_id;
END;