oracle11g宝典 光盘源代码

sqlplus scott/tiger ------3.1.1示例数据库概述 DESC dept DESC emp DESC bonus DESC salgrade SELECT * FROM dept; SELECT * FROM emp; SELECT * FROM bonus; SELECT * FROM salgrade; CONNECT sys/password AS sysdba SELECT username, account_status FROM dba_users WHERE username in ('SCOTT','HR'); ALTER USER hr IDENTIFIED BY pswd ACCOUNT UNLOCK; CONNECT hr/pswd CONNECT sys/password AS sysdba ALTER USER hr PASSWORD EXPIRE ACCOUNT LOCK; sqlplus hr/pswd ------3.1.3HR示例方案中的表结构 DESC regions DESC countries DESC locations DESC departments DESC employees DESC job_history DESC jobs ------3.2.1.1查询所有列 SELECT * FROM regions; SELECT * FROM countries; COLUMN street_address FORMAT A28 COLUMN city FORMAT A22 COLUMN state_province FORMAT A20 SELECT * FROM locations; CLEAR columns SELECT * FROM departments; SELECT * FROM job_history; SELECT * FROM jobs; SELECT * FROM employees; SELECT department_id, employee_id, first_name, last_name, hire_date FROM employees; ------3.2.1.2查询指定的列 SELECT employee_id, job_id 岗位, start_date, end_date FROM job_history; SELECT DISTINCT country_id FROM locations; ------3.2.1.4使用WHERE子句指定查询条件 --使用默认的日期格式20-3月-00 SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE hire_date > '20-3月-00'; --不使用默认的日期格式2000-3-20 SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE hire_date > TO_DATE('2000-3-20', 'YYYY-MM-DD'); SELECT employee_id, first_name, salary, department_id FROM employees WHERE department_id = 30; SELECT employee_id, first_name, salary, department_id FROM employees WHERE department_id = 30 AND salary > 3000; SELECT first_name "First（姓）", last_name "Last（名）", salary "工 资", commission_pct "奖金比例%" FROM employees WHERE salary >= 11000 AND commission_pct IS NOT NULL; SELECT postal_code, city, country_id FROM locations WHERE country_id IN('JP', 'US'); SELECT postal_code, city, country_id FROM locations WHERE country_id = 'JP' OR country_id = 'US'; SELECT first_name, last_name, salary, hire_date FROM employees WHERE salary BETWEEN 11000 AND 13000; SELECT first_name, last_name, salary, hire_date FROM employees WHERE salary BETWEEN 11000 AND 13000 AND hire_date BETWEEN '17-2月-96' AND '10-3月-97'; ------3.2.1.5使用通配符指定字符串的模糊查询条件 SELECT first_name, last_name FROM employees WHERE last_name LIKE 'Ma%'; SELECT first_name, last_name FROM employees WHERE last_name LIKE '%ma%'; ------3.2.1.6使用表达式 SELECT region_id || '是指' || region_name AS 地区编码的意思 FROM regions; SELECT region_id, region_name, CASE region_id WHEN 1 THEN '欧洲' WHEN 2 THEN '美洲' WHEN 3 THEN '亚洲' WHEN 4 THEN '中东和非洲' ELSE '未定义' END 中文地区名 FROM regions; SELECT employee_id, first_name, job_id, salary, department_id, CASE WHEN salary > 20000 THEN '高工资' WHEN salary > 10000 AND salary <= 20000 THEN '中等工资' ELSE '低工资' END 工资级别 FROM employees WHERE department_id = 90; ------3.2.1.7使用ORDER BY子句对查询结果排序 SELECT rowid, rownum, first_name, salary, hire_date FROM employees WHERE salary BETWEEN 11000 AND 13000; SELECT rownum, first_name, salary, hire_date FROM employees WHERE salary BETWEEN 11000 AND 13000 ORDER BY salary DESC, hire_date; SELECT rownum, first_name, salary "工 资", hire_date FROM employees WHERE salary BETWEEN 11000 AND 13000 ORDER BY "工 资" DESC, 4; --不好的语句 SELECT employee_id, department_id FROM job_history ORDER BY start_date; --错误的语句 SELECT DISTINCT employee_id, department_id FROM job_history ORDER BY start_date; ------3.2.2分组查询 查询雇员的最高工资、最低工资、平均工资、总工资 SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees; SELECT count(department_id), count(*), count(manager_id) FROM departments; SELECT count(country_id), count(DISTINCT country_id) FROM locations; ------3.2.2.2使用GROUP BY子句 单列分组 SELECT department_id, MAX(hire_date), MIN(hire_date) FROM employees GROUP BY department_id; SELECT employee_id 雇员编码, count(*) 更换岗位次数 FROM job_history GROUP BY employee_id ORDER BY employee_id DESC; SELECT employee_id 雇员编码, count(*) 更换岗位次数 FROM job_history GROUP BY employee_id ORDER BY 更换岗位次数 DESC; 按多列分组 SELECT department_id, job_id, COUNT(*), MAX(salary) FROM employees WHERE department_id <= 50 GROUP BY department_id, job_id ORDER BY department_id, job_id; ------3.2.2.3使用HAVING子句 SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees WHERE department_id <= 50 GROUP BY department_id; SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees WHERE department_id <= 50 GROUP BY department_id HAVING AVG(salary) >= 6000; --错误的语句 SELECT department_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees; SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE hire_date > '20-3月-00'; SELECT employee_id, first_name, last_name, hire_date FROM employees HAVING hire_date > '20-3月-00'; --错误的语句，在HAVING子句中使用了别名 SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees GROUP BY department_id HAVING avg_sal >= 10000; --错误的语句，在WHEREG子句中使用了聚合函数 SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees WHERE AVG(salary) >= 10000 GROUP BY department_id; --正确的语句 SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees WHERE department_id <= 50 HAVING AVG(salary) >= 3000 GROUP BY department_id; --错误的语句 SELECT department_id, MAX(salary), AVG(salary) avg_sal FROM employees HAVING AVG(salary) >= 3000 GROUP BY department_id WHERE department_id <= 50; ------3.2.3连接查询 SELECT region_name FROM regions; SELECT city FROM locations WHERE country_id = 'UK'; SELECT region_name, city FROM regions, locations WHERE country_id = 'UK'; 相等连接 SELECT first_name, department_id FROM employees WHERE employee_id = 177; SELECT department_name FROM departments WHERE department_id = 80; SELECT first_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND employee_id = 177; 等价 SELECT employees.first_name, departments.department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND employees.employee_id = 177; SELECT e.first_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND e.employee_id = 177; 三个表的等值连接 SELECT first_name, department_name, job_title FROM employees e, departments d, jobs j WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND employee_id = 177; 不相等连接 SELECT e.first_name, e.salary, j.job_title, j.min_salary, j.max_salary FROM employees e, jobs j WHERE e.salary BETWEEN j.min_salary AND j.max_salary AND e.employee_id = 177; SELECT e.first_name, e.salary, j.job_title, j.min_salary, j.max_salary FROM employees e, jobs j WHERE e.salary > j.max_salary AND e.employee_id = 177; 自身连接 SELECT a.employee_id, a.first_name, a.manager_id, b.first_name FROM employees a, employees b WHERE a.manager_id = b.employee_id AND a.employee_id = 177; SELECT employee_id, first_name, manager_id FROM employees WHERE employee_id = 177; SELECT employee_id, first_name, manager_id FROM employees WHERE employee_id = 149; SELECT a.employee_id, a.first_name, a.manager_id 上司, b.manager_id 上司的上司 FROM employees a, employees b WHERE a.manager_id = b.employee_id AND a.employee_id = 177; ------3.2.4使用SQL92语法的连接查询 内连接 SELECT e.first_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND e.emplo