-- 创建"users"表 | |
CREATE TABLE users ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
email VARCHAR(100), | |
password VARCHAR(100), | |
created_at TIMESTAMP | |
); | |
-- 插入数据 | |
INSERT INTO users (id, name, email, password, created_at) | |
VALUES (1, 'John Doe', 'john@example.com', 'password123', '2023-01-01 12:00:00'); | |
INSERT INTO users (id, name, email, password, created_at) | |
VALUES (2, 'Jane Smith', 'jane@example.com', 'qwerty456', '2023-02-01 12:00:00'); | |
-- 查询创建时间早于7天,且邮箱包含 "@example.com" 的用户 | |
SELECT * FROM users | |
WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY) | |
AND email LIKE '%@example.com'; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。WHERE
:用于过滤查询结果。AND
:在WHERE
子句中,用于联合多个条件。DATE_SUB
:用于进行日期计算。NOW()
:用于获取当前日期和时间。INTERVAL
:用于指定时间间隔。LIKE
:在WHERE
子句中,用于匹配字符串模式。上述示例中,LIKE '%@example.com'
表示匹配任何包含 "@example.com" 的邮箱地址。%
是一个通配符,表示任意数量的任意字符。
sql复制代码
-- 创建"products"表 | |
CREATE TABLE products ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
price DECIMAL(10,2) | |
); | |
-- 插入数据 | |
INSERT INTO products (id, name, price) | |
VALUES (1, 'Product A', 10.99); | |
INSERT INTO products (id, name, price) | |
VALUES (2, 'Product B', 5.99); | |
INSERT INTO products (id, name, price) | |
VALUES (3, 'Product C', 2.99); | |
-- 创建"orders"表 | |
CREATE TABLE orders ( | |
id INT PRIMARY KEY, | |
customer_name VARCHAR(100), | |
order_date DATE, | |
product_id INT, | |
quantity INT, | |
FOREIGN KEY (product_id) REFERENCES products(id) | |
); | |
-- 插入订单数据 | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (1, 'John Doe', '2023-03-01', 1, 2); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (2, 'Jane Smith', '2023-03-05', 2, 5); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (3, 'Mike Johnson', '2023-02-28', 3, 3); | |
-- 查询每个产品的销售数量和总销售额 | |
SELECT products.name, SUM(orders.quantity) as total_sales | |
FROM products | |
JOIN orders ON products.id = orders.product_id | |
GROUP BY products.name; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。GROUP BY
:用于对查询结果进行分组。SUM
:用于计算列的总和。JOIN
:用于将两个或多个表进行连接。ON
:在JOIN
中,用于指定连接条件。FOREIGN KEY
:用于定义外键。在上述示例中,orders
表中的product_id
列被定义为一个外键,参照了products
表中的id
列。这表明了orders
表中的每一条订单记录都对应着products
表中的一条产品记录。通过这种方式,我们可以将订单数据和产品数据进行关联。
-- 创建"orders"表 | |
CREATE TABLE orders ( | |
id INT PRIMARY KEY, | |
customer_id INT, | |
order_date DATE, | |
total_amount DECIMAL(10,2) | |
); | |
-- 插入订单数据 | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (1, 1, '2023-03-01', 150.99); | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (2, 2, '2023-03-02', 75.99); | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (3, 1, '2023-03-03', 200.99); | |
-- 查询总销售额大于500的客户及其总销售额 | |
SELECT customer_id, SUM(total_amount) as total_sales | |
FROM orders | |
GROUP BY customer_id | |
HAVING SUM(total_amount) > 500; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。GROUP BY
:用于对查询结果进行分组。HAVING
:用于过滤聚合查询结果。
-- 创建"users"表 | |
CREATE TABLE users ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
email VARCHAR(100), | |
password VARCHAR(100), | |
created_at TIMESTAMP | |
); | |
-- 插入数据 | |
INSERT INTO users (id, name, email, password, created_at) | |
VALUES (1, 'John Doe', 'john@example.com', 'password123', '2023-01-01 12:00:00'); | |
INSERT INTO users (id, name, email, password, created_at) | |
VALUES (2, 'Jane Smith', 'jane@example.com', 'qwerty456', '2023-02-01 12:00:00'); | |
-- 查询在2023年1月1日之后创建的用户 | |
SELECT * FROM users WHERE created_at > '2023-01-01 12:00:00'; | |
-- 查询在2023年1月1日和2023年2月1日之间创建的用户 | |
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01 12:00:00' AND '2023-02-01 12:00:00'; | |
-- 查询创建时间早于7天的用户 | |
SELECT * FROM users WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY); |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。WHERE
:用于过滤查询结果。BETWEEN
:用于在一个范围中筛选数据。DATE_SUB
:用于进行日期计算。NOW()
:用于获取当前日期和时间。INTERVAL
:用于指定时间间隔。
-- 创建"products"表 | |
CREATE TABLE products ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
price DECIMAL(10,2) | |
); | |
-- 插入数据 | |
INSERT INTO products (id, name, price) | |
VALUES (1, 'Product A', 10.99); | |
INSERT INTO products (id, name, price) | |
VALUES (2, 'Product B', 5.99); | |
INSERT INTO products (id, name, price) | |
VALUES (3, 'Product C', 2.99); | |
-- 创建"orders"表 | |
CREATE TABLE orders ( | |
id INT PRIMARY KEY, | |
customer_name VARCHAR(100), | |
order_date DATE, | |
product_id INT, | |
quantity INT, | |
FOREIGN KEY (product_id) REFERENCES products(id) | |
); | |
-- 插入订单数据 | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (1, 'John Doe', '2023-03-01', 1, 2); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (2, 'Jane Smith', '2023-03-05', 2, 5); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (3, 'Mike Johnson', '2023-02-28', 3, 3); | |
-- 查询每个产品的销售总额和平均价格 | |
SELECT products.name, SUM(orders.quantity) as total_sales, AVG(products.price) as avg_price | |
FROM products | |
JOIN orders ON products.id = orders.product_id | |
GROUP BY products.name; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。GROUP BY
:用于对查询结果进行分组。SUM
:用于计算列的总和。AVG
:用于计算列的平均值。JOIN
:用于将两个或多个表进行连接。ON
:在JOIN
中,用于指定连接条件。FOREIGN KEY
:用于定义外键。
-- 创建"orders"表 | |
CREATE TABLE orders ( | |
id INT PRIMARY KEY, | |
customer_id INT, | |
order_date DATE, | |
total_amount DECIMAL(10,2) | |
); | |
-- 插入订单数据 | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (1, 1, '2023-03-01', 150.99); | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (2, 2, '2023-03-02', 75.99); | |
INSERT INTO orders (id, customer_id, order_date, total_amount) | |
VALUES (3, 1, '2023-03-03', 200.99); | |
-- 查询每个客户的订单总金额 | |
SELECT customer_id, SUM(total_amount) as total_spent FROM orders | |
GROUP BY customer_id; | |
-- 查询总订单金额大于500的客户 | |
SELECT customer_id FROM orders | |
GROUP BY customer_id HAVING SUM(total_amount) > 500; | |
-- 查询最近7天内下单的客户及其总金额 | |
SELECT customer_id, SUM(total_amount) as total_spent | |
FROM orders | |
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) | |
GROUP BY customer_id; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。GROUP BY
:用于对查询结果进行分组。SUM
:用于计算列的总和。HAVING
:用于过滤聚合查询结果。WHERE
:用于过滤查询结果。DATE_SUB
:用于进行日期计算。
-- 创建"users"表 | |
CREATE TABLE users ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
email VARCHAR(100), | |
password VARCHAR(100) | |
); | |
-- 插入数据 | |
INSERT INTO users (id, name, email, password) | |
VALUES (1, 'John Doe', 'john@example.com', 'password123'); | |
INSERT INTO users (id, name, email, password) | |
VALUES (2, 'Jane Smith', 'jane@example.com', 'qwerty456'); | |
-- 查询年龄大于25岁的用户 | |
SELECT * FROM users WHERE age > 25; | |
-- 查询年龄大于25岁且名字以'J'开头的用户 | |
SELECT * FROM users WHERE age > 25 AND name LIKE 'J%'; | |
-- 查询年龄大于25岁且邮箱包含'@example.com'的用户 | |
SELECT * FROM users WHERE age > 25 AND email LIKE '%@example.com'; | |
-- 查询名字是'John Doe'或者邮箱是'[jsmith@]example.[com]'的用户 | |
SELECT * FROM users WHERE name = 'John Doe' OR email = '[jsmith@]example.[com]'; | |
-- 查询名字不是'John Doe'且邮箱不是'[jsmith@]example.[com]'的用户 | |
SELECT * FROM users WHERE NOT (name = 'John Doe' AND email = '[jsmith@]example.[com]'); |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。WHERE
:用于过滤查询结果。LIKE
:用于模糊匹配。AND
、OR
和NOT
:用于组合条件。%
:在LIKE
语句中,表示任意字符的任意数量。_
:在LIKE
语句中,表示任意单个字符。
-- 创建"products"表 | |
CREATE TABLE products ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
price DECIMAL(10,2), | |
category VARCHAR(50) | |
); | |
-- 插入数据 | |
INSERT INTO products (id, name, price, category) | |
VALUES (1, 'Product A', 10.99, 'Electronics'); | |
INSERT INTO products (id, name, price, category) | |
VALUES (2, 'Product B', 5.99, 'Groceries'); | |
INSERT INTO products (id, name, price, category) | |
VALUES (3, 'Product C', 2.99, 'Books'); | |
-- 创建"orders"表 | |
CREATE TABLE orders ( | |
id INT PRIMARY KEY, | |
customer_name VARCHAR(100), | |
order_date DATE, | |
product_id INT, | |
quantity INT, | |
FOREIGN KEY (product_id) REFERENCES products(id) | |
); | |
-- 插入订单数据 | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (1, 'John Doe', '2023-03-01', 1, 2); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (2, 'Jane Smith', '2023-03-05', 2, 5); | |
INSERT INTO orders (id, customer_name, order_date, product_id, quantity) | |
VALUES (3, 'Mike Johnson', '2023-02-28', 3, 3); | |
-- 查询每个产品的销售总额 | |
SELECT products.name, SUM(orders.quantity) as total_sales FROM products | |
JOIN orders ON products.id = orders.product_id | |
GROUP BY products.name; | |
-- 查询每个产品的销售数量和销售额的总和 | |
SELECT products.name, SUM(orders.quantity) as total_sales, SUM(orders.quantity * products.price) as total_price FROM products | |
JOIN orders ON products.id = orders.product_id | |
GROUP BY products.name; | |
-- 查询每个产品的销售数量大于1的总销售额 | |
SELECT products.name, SUM(orders.quantity * products.price) as total_sales FROM products | |
JOIN orders ON products.id = orders.product_id | |
WHERE orders.quantity > 1 | |
GROUP BY products.name; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。JOIN
:用于将两个或多个表进行连接。GROUP BY
:用于对查询结果进行分组。SUM
:用于计算列的总和。*
:用于选择所有列。FOREIGN KEY
:用于定义外键。WHERE
:用于过滤查询结果。HAVING
:用于过滤聚合查询结果。
-- 创建"employees"表 | |
CREATE TABLE employees ( | |
id INT PRIMARY KEY, | |
name VARCHAR(100), | |
age INT, | |
salary DECIMAL(10,2), | |
department VARCHAR(50) | |
); | |
-- 插入数据 | |
INSERT INTO employees (id, name, age, salary, department) | |
VALUES (1, 'John Doe', 30, 5000.00, 'Sales'); | |
INSERT INTO employees (id, name, age, salary, department) | |
VALUES (2, 'Jane Smith', 28, 4500.00, 'Marketing'); | |
INSERT INTO employees (id, name, age, salary, department) | |
VALUES (3, 'Mike Johnson', 35, 6000.00, 'Sales'); | |
INSERT INTO employees (id, name, age, salary, department) | |
VALUES (4, 'Sarah Brown', 40, 7000.00, 'HR'); | |
-- 查询所有员工及其薪资 | |
SELECT name, salary FROM employees; | |
-- 查询每个部门的员工数量 | |
SELECT department, COUNT(*) as num_employees FROM employees GROUP BY department; | |
-- 查询薪资最高的员工及其部门 | |
SELECT name, department, salary FROM employees ORDER BY salary DESC LIMIT 1; | |
-- 更新员工信息 | |
UPDATE employees SET salary = 5500.00 WHERE id = 1; | |
-- 删除员工 | |
DELETE FROM employees WHERE id = 4; |
上述示例展示了以下SQL命令和功能:
CREATE TABLE
:用于创建表。INSERT INTO
:用于向表中插入数据。SELECT
:用于从表中查询数据。GROUP BY
:用于对查询结果进行分组。ORDER BY
:用于对查询结果进行排序。LIMIT
:用于限制查询结果的行数。UPDATE
:用于更新表中的数据。DELETE FROM
:用于从表中删除数据。