?

/////////////////基础知识//////////////////

?

应用场景178：最简单的select语句

SELECT * FROM Employees;

应用场景179：指定要查询的列

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees;

应用场景180：使用DISTINCT关键字

SELECT Title FROM HRMAN.Employees;

SELECT DISTINCT Title FROM HRMAN.Employees;

应用场景181：使用ROWNUM

COL EMP_NAME FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title FROM HRMAN.Employees
WHERE ROWNUM<=3;

应用场景182：改变显示的列标题

COL 姓名 FORMAT A20
COL 性别 FORMAT A4
COL 职务 FORMAT A10
COL 身份证 FORMAT A20
SELECT EMP_NAME AS 姓名, SEX AS 性别, TITLE AS 职务, WAGE AS 工资, IDCARD AS 身份证 FROM HRMAN.Employees;

应用场景183：设置查询条件

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage > 3000 AND Wage < 4000;

应用场景184：在查询条件中使用BITWEEN 关键字

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage FROM HRMAN.Employees WHERE Wage BETWEEN 3000 AND 4000;

应用场景185：在查询条件中使用IN关键

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, WAGE FROM HRMAN.Employees WHERE Emp_name IN ('张三', '李四', '王五');

应用场景186：实现模糊查询

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '%ddd%';

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
WHERE IDCard LIKE '110123_adx_';

应用场景187：排序结果集

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
COL IDCard FORMAT A20
SELECT EMP_NAME, TITLE, IDCard FROM HRMAN.Employees
ORDER BY Emp_name;

COL EMP_NAME FORMAT A20
COL TITLE FORMAT A20
SELECT EMP_NAME, TITLE, Wage FROM HRMAN.Employees
ORDER BY Wage DESC;

应用场景188：对多列进行排序

COL EMP_NAME FORMAT A20
COL SEX FORMAT A20
SELECT EMP_NAME, Sex, Wage FROM HRMAN.Employees
ORDER BY Sex, Wage;

应用场景189：使用分组统计

COL 职务 FORMAT A10
SELECT Title AS 职务, AVG(Wage) AS 平均公资 FROM HRMAN.Employees GROUP BY Title;

COL Sex FORMAT A10
COL Title FORMAT A10
SELECT Sex, Title, AVG(Wage) FROM HRMAN.Employees GROUP BY Title;

SELECT Dep_Id, AVG(Wage) FROM HRMAN.Employees
GROUP BY Dep_Id HAVING AVG(Wage) > 4000;

应用场景190：连接查询

内连接
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1, HRMAN.Employees t2
WHERE t1.Dep_id=t2.Dep_id;

外链接：
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 INNER JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 RIGHT JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 FULL JOIN HRMAN.Departments t1
ON t1.Dep_id=t2.Dep_id;

交叉连接
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Employees t2 CROSS JOIN HRMAN.Departments t1;

应用场景191：在连接查询中对空值中对空值的判断

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id;

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep_name, t2.Emp_name FROM HRMAN.Departments t1 LEFT JOIN HRMAN.Employees t2
ON t1.Dep_id=t2.Dep_id
WHERE t2.Emp_id IS NULL;

应用场景192：一个简单地子查询