learning-oracle-how2j

learning-oracle-how2j

?

-- 别名示例
SELECT salary*12 AS "年薪" FROM hr.employees;

-- 字符串拼接示例
SELECT first_name || ‘ ‘ || last_name AS "姓名" FROM hr.employees;

-- 消除重复行
SELECT DISTINCT department_id AS "部门号" FROM hr.employees;

-- 条件限定语句
SELECT * FROM hr.employees WHERE salary >= 20000;
SELECT * FROM hr.employees WHERE salary BETWEEN 3000 AND 4000;
SELECT * FROM hr.employees WHERE salary IN (3000,5000);
SELECT * FROM hr.employees WHERE first_name LIKE ‘%a_‘;
SELECT * FROM hr.employees WHERE department_id IS NULL;

-- 逻辑条件
SELECT * FROM hr.employees WHERE salary>=3000 AND salary<=5000;
SELECT * FROM hr.employees WHERE first_name LIKE ‘_a%‘ OR first_name LIKE ‘%a_‘;
SELECT * FROM hr.employees WHERE first_name NOT LIKE ‘%a_‘;

-- 排序查询
SELECT
e.first_name || ‘ ‘ || e.last_name AS "姓名",
e.salary AS "年薪"
FROM hr.employees e
ORDER BY e.salary DESC;

-- 关联查询
SELECT
e.first_name || ‘ ‘ || e.last_name AS "姓名",
d.department_name AS "部门名称"
FROM hr.employees e LEFT JOIN hr.departments d
ON e.department_id = d.department_id;

-- 统计查询
SELECT
AVG(salary)
FROM hr.employees;

-- 分页查询SELECT * FROM ( SELECT ROWNUM r,e1.* FROM( SELECT * FROM hr.employees e ORDER BY e.salary DESC )e1 )WHERE r > 4 AND r < 11;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!