DM8数据查询语言(DQL)

基本SELECT查询

SELECT语句是SQL中最常用的语句，用于从数据库中检索数据。

-- 基本SELECT语法结构
/*
SELECT [ALL|DISTINCT] <column_list>
FROM <table_name>
[WHERE <condition>]
[GROUP BY <group_by_expression>]
[HAVING <group_condition>]
[ORDER BY <order_by_list> [ASC|DESC]]
[LIMIT <row_count>];
*/

-- 实际示例
SELECT
employee_id AS "员工编号", -- 列别名使用双引号可以包含空格或保留字
name "员工姓名", -- AS关键字可选
salary * 12 "年薪", -- 可以使用表达式
department_id
FROM
employees
WHERE
department_id IN (10, 20, 30) -- 部门ID在10,20,30中
AND salary > 5000 -- 且薪资大于5000
ORDER BY
salary DESC, -- 首先按薪资降序
name ASC -- 然后按姓名升序
LIMIT 10; -- 只返回前10条记录

条件查询与运算符

-- 条件查询综合示例
SELECT
product_id,
product_name,
category,
price,
stock_quantity
FROM
products
WHERE
(category = '电子产品' OR category = '办公用品') -- 类别为电子产品或办公用品
AND price BETWEEN 100 AND 1000 -- 价格在100到1000之间
AND product_name LIKE '%联想%' -- 产品名称包含"联想"
AND stock_quantity > 0 -- 库存大于0
AND discontinued_flag IS NOT NULL -- 停产标志不为空
ORDER BY
price DESC;

分组与聚合函数

DM8提供丰富的聚合函数进行数据统计：

-- 分组聚合示例
SELECT
department_id AS "部门ID",
COUNT(*) AS "员工人数",
ROUND(AVG(salary), 2) AS "平均薪资",
MAX(salary) AS "最高薪资",
MIN(salary) AS "最低薪资",
SUM(salary) AS "薪资总额",
LISTAGG(name, '; ') WITHIN GROUP (ORDER BY salary DESC) AS "员工列表"
FROM
employees
WHERE
hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD') -- 2020年后入职
GROUP BY
department_id
HAVING
COUNT(*) > 5 -- 只显示员工数大于5的部门
ORDER BY
"平均薪资" DESC;

多表连接查询

DM8支持多种表连接方式：

-- 内连接(INNER JOIN)：只返回两表中匹配的行
SELECT
e.employee_id,
e.name,
d.department_name,
j.job_title
FROM
employees e
INNER JOIN
departments d ON e.department_id = d.department_id
INNER JOIN
jobs j ON e.job_id = j.job_id
WHERE
d.location_id = 1700;

-- 左外连接(LEFT JOIN)：返回左表所有行，右表不匹配则为NULL
SELECT
d.department_name,
e.name
FROM
departments d
LEFT JOIN
employees e ON d.department_id = e.department_id
ORDER BY
d.department_name;

-- 右外连接(RIGHT JOIN)：返回右表所有行，左表不匹配则为NULL
SELECT
e.name,
d.department_name
FROM
employees e
RIGHT JOIN
departments d ON e.department_id = d.department_id;

-- 全外连接(FULL JOIN)：返回两表所有行，不匹配则为NULL
SELECT
e.name,
d.department_name
FROM
employees e
FULL JOIN
departments d ON e.department_id = d.department_id;

-- 交叉连接(CROSS JOIN)：笛卡尔积
SELECT
e.name,
d.department_name
FROM
employees e
CROSS JOIN
departments d;

-- 自连接：表与自身连接
SELECT
e1.name AS "员工姓名",
e2.name AS "上级姓名"
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.employee_id;