Oracle 数据库所有查询命令南京廖华答案网

Oracle 数据库所有查询命令下载本文

文章发布时间 : 2024/10/17 16:36:26星期四

1.单行查询........................................................................................................................................ 2 2.基本查询........................................................................................................................................ 3 3.限定返回的行 ................................................................................................................................ 4 4.逻辑运算........................................................................................................................................ 5 5.排序 ............................................................................................................................................... 6 6.函数 ............................................................................................................................................... 6 7.分组查询........................................................................................................................................ 9 8.多表查询...................................................................................................................................... 10 9.多表连接...................................................................................................................................... 13 10.子查询 ....................................................................................................................................... 14 11.创建和管理表............................................................................................................................ 20 12.约束 ........................................................................................................................................... 23 13.视图，序列，索引 .................................................................................................................... 25 14.其他数据库对象 ........................................................................................................................ 26 15.pl sql基础 .................................................................................................................................. 28

1.单行查询

--查询所有员工的姓 select e.last_name as 姓 from employees e

--消除重复的姓

select distinct e.last_name as 姓氏 from employees e

--计算员工的月收入（工资+佣金）

select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金 from employees

--计算员工的年收入

select salary*(nvl(commission_pct,0)+1)*12 as 年收入 from employees

--查询员工的姓名

select e.first_name||' '||e.last_name \from employees e

--查询位置为1700的部门名称（不重复） select distinct d.department_name as 部门名称 from departments d

where d.location_id=1700

--查询工资高于10000的员工 select *

from employees where salary>10000

--查询工资低于3000的员工 select *

from employees where salary<3000

--查询在1998年入职的员工 select *

from employees e

where to_char(e.hire_date,'yyyy')='1998'

--查询没有佣金的员工

select *

from employees e

where commission_pct is null

--查询姓以B开头的员工 select *

from employees e

where e.last_name like 'B%'

--查询部门号为10或者20或者30的员工 select *

from employees e

where e.department_id in(10,20,30)

2.基本查询

--查询所有员工的姓 select last_name from employees;

--消除重复的姓

select distinct last_name from employees;

--创建一个查询，以显示employees表中的唯一职务代码 select distinct t.job_id from employees t;

--创建一个查询，使其显示每位员工的姓氏、职务代码、聘用日期和员工编号，并且首先显示员工编号。为hire_date列提供一个别名：startdate

select t.employee_id,t.last_name,t.job_id,t.hire_date as startdate from employees t;

--计算员工的月收入（工资+佣金）

select t.salary+t.salary*nvl(t.commission_pct,0) from employees t; --计算员工的年收入

select 12*(t.salary+t.salary*nvl(t.commission_pct,0)) from employees t; --查询员工的姓名

select t.first_name||' '||t.last_name from employees t;

--显示与职务标识连接的姓氏，它们之间由逗号和空格分隔，这列数据命名为 Employee and Title

select t.job_id||', '||t.last_name as \from employees t;

--创建一个查询，使其显示employees表的所有数据，用逗号分隔各列，命名列为THE_OUTPUT select t.employee_id||','||t.first_name||','||t.last_name||','|| t.email||','||t.phone_number||','||t.hire_date||','||t.job_id||','||

t.salary||','||t.commission_pct||','||t.manager_id||','||t.department_id as \from employees t;

============================================================================

3.限定返回的行

--查询位置为1700的部门名称（不重复） select distinct t.department_name from departments t

where t.location_id=1700;

--创建一个查询，显示员工编号为176的员工的姓氏和部门编号 select t.last_name,t.department_id from employees t

where t.employee_id=176;

--查询工资高于10000的员工的姓氏和薪资 select t.last_name,t.salary from employees t where t.salary>10000;

--查询工资低于3000的员工 select t.*

from employees t where t.salary<3000;

--查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期

select t.last_name,t.job_id,t.hire_date from employees t where t.hire_date between to_date('19980220','yyyyMMdd') and to_date('19980501','yyyyMMdd');

--显示在1994年聘用的每位员工的姓氏和聘用日期 select t.last_name,t.hire_date from employees t where t.hire_date between to_date('19940101','yyyyMMdd') to_date('19950101','yyyyMMdd'); --*查询没有佣金的员工 select t.*

from employees t

and

where t.commission_pct is null; --查询姓以B开头的员工 select t.*

from employees t

where t.last_name like 'B%';

--查询部门号为10或者20或者30的员工 select t.*

from employees t

where t.department_id in (10,20,30); --查询没有经理的所有员工的姓氏和职称 select t.last_name,t.job_id from employees t

where t.manager_id is null;

--显示员工名字中的第三个字母为“a”的所有员工的姓氏 select t.last_name from employees t

where t.last_name like '__a%';

4.逻辑运算

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK） (需用子查询，暂不做)

--找出有佣金的员工的都做什么工作（无重复） select distinct t.job_id from employees t

where t.commission_pct is not null;

--找出不收取佣金或收取的佣金高于100的员工 select *

from employees t

where t.commission_pct is null or t.salary*t.commission_pct>100;

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（CLERK）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料(需用子查询，暂不做) --显示员工姓氏中有“a”和“e”的所有员工的姓氏 select *

from employees t

where t.last_name like '%a%' or t.last_name like '%e%' --显示职务为销售代表（SA_REP）或仓库管理员（ST_CLERK）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金 select t.last_name,t.job_id,t.salary from employees t

where (t.job_id='SA_REP' or t.job_id='ST_CLERK') and t.salary not in (2500,3500,7000);

--显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金 select t.last_name,t.salary from employees t

where t.salary<5000 or t.salary>1200;

5.排序

--查询员工资料，按薪资升序排列 select t.*

from employees t order by t.salary;

--查询员工资料，在部门号从大到小的情况下按按薪资升序排列 select t.*

from employees t

order by t.department_id desc ,t.salary;

--按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号 select t.last_name , t.department_id from employees t

where t.department_id in(20,50) order by t.last_name;

--显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序 select t.last_name , t.salary,t.commission_pct from employees t

where t.commission_pct is not null

order by t.salary desc,t.commission_pct desc;

6.函数（已看到此处）

--1、显示当前日期 select sysdate from dual;

--2、显示当前日期，格式为****年**月**日，别名为hday select to_char(sysdate,'yyyy\年\月\日\

--3、编写一个查询，显示姓名以J、A或M开始的所有员工的姓氏（第一个字母大写，其余字母小写）和姓氏的长度，给每列一个合适的标签； select initcap(t.last_name) lname,length(t.last_name) len from employees t

where substr(upper(last_name),0,1) in('J','A','M');

--4、计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字； select t.first_name,round((sysdate-t.hire_date)/7) as weeks_worked from employees t;

--5、计算每位员工截止到当前时间入职的月数，别名为months_worked。该星期数舍入到最接近的整数。同时显示员工的名字。

select t.first_name,round(months_between(sysdate,t.hire_date)) as months_worked from employees t;

--6、查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期

select t.last_name,t.job_id,t.hire_date from employees t where t.hire_date between to_date('19980220','yyyyMMdd') and to_date('19980501','yyyyMMdd');

--7、创建一个查询。显示所有员工的姓氏和薪金。将薪金格式规定为15个字符长，左边填充$

select lpad(t.salary,15,'$') sal from employees t;

/*8、显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。将该列标记为review。这一日期的显示格式类似于：“Monday,the Thirty-First of July,2000”*/

select t.last_name,t.hire_date,/*next_day(add_months(t.hire_date,6),2) from employees t*/ to_char(next_day(add_months(t.hire_date,6),2),'fmday,\\of \from employees t;

/*第八题to_char的第三个参数用于设置本次查询使用的国家和地区，ddspth中spth为后缀，表示Spelled, ordinal number*/

--9、显示员工的姓氏、聘用日期和该员工在星期几开始工作的。 select t.last_name,t.hire_date,to_char(t.hire_date,'DAY') as \开始\from employees t;

--10、计算员工的月收入（工资+佣金）

select t.salary+t.salary*nvl(t.commission_pct,0) from employees t;

--11、创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美元。按薪资降序排列数据。

select t.last_name||lpad(' ',trunc(t.salary/1000)+1,'*'),t.salary from employees t order by t.salary desc;

--12、创建一个查询。使其显示员工的姓氏和佣金额。如果某位员工不赚取佣金则显示“No Commission”，将该列标记为COMM

select t.last_name,nvl(to_char(t.salary*t.commission_pct,'999,999.99'),'No Commission') COMM from employees t

/*13、使用decode函数编写一个查询，使其按照以下数据根据JOB_ID列的值显示所有员工

的级别，同时显示员工的姓氏

job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 都不是 0 */

select t.last_name,t.job_id job, decode(t.job_id,'AD_PRES','A' ,'ST_MAN','B' ,'IT_PROG','C' ,'SA_REP','D' ,'ST_CLERK','E' ,'0'

) as grade from employees t;

--14、使用case语法重写一遍上题 select t.last_name,t.job_id job, case t.job_id

when 'AD_PRES'then 'A' when 'ST_MAN' then 'B' when 'IT_PROG' then 'C' when 'SA_REP' then 'D' when 'ST_CLERK' then 'E' else '0'

end

as grade from employees t;

************************************补充************************************ --1、显示当前日期，本月最后一天的日期，以及本月还剩多少天 SELECT SYSDATE,

LAST_DAY(SYSDATE) \

LAST_DAY(SYSDATE) - SYSDATE \ FROM DUAL;

--2、显示今年的第一天

select trunc(sysdate,'year') from dual; --3、显示本月的第一天

select trunc(sysdate,'month') from dual; --4、最近一个星期四是哪天(不含今日) SELECT NEXT_DAY(sysdate,5) FROM DUAL;

7.分组查询

--求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名 select avg(t.salary),max(t.salary),min(t.salary),sum(t.salary) from employees t;

--求每种工作的平均工资 select avg(t.salary) from employees t group by t.job_id;

--求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示 select t.department_id,avg(t.salary) from employees t

group by t.department_id,t.job_id order by t.department_id;

--查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。 select t.department_id,sum(t.salary),avg(t.salary) from employees t

group by t.department_id order by t.department_id;

--显示每种工作的人数 select t.job_id,count(*) from employees t group by t.job_id;

--显示员工最高工资超过10000的部门的id及其员工最高工资 select t.department_id,max(t.salary) m from employees t

group by t.department_id having max(t.salary)>10000;

--显示平均工资最高的部门id及其平均工资(要用到还未学习的知识) select * from(

select t.department_id,avg(t.salary) from employees t

group by t.department_id order by avg(t.salary) desc )

where rownum<2 ;

8.多表查询

多表查询，基本知识（预习自检）：什么是多表查询? 什么是笛卡尔积？怎样避免笛卡尔积？

要将n个表联结在一起，至少需要多少个联结条件？什么是等值联结？

如何在联结条件外附加限制条件？如何限定模糊的列名？如何进行非等值联结？什么是外联结？什么是自联结？

如何用sql1999标准表达：笛卡尔积、自然联结、等值联结、内联结、左外联结、右外联结和全外联结？ **/ /*

联结的分类：

Joins that are compliant with the SQL:1999 standard include the following: Natural joins:

NATURAL JOIN clause USING clause ON clause Outer joins:

LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN Cross joins

sql：1999联结语法

SELECT table1.column, table2.column FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] | [JOIN table2

ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

知识总结：

In this lesson, you should have learned how to use joins to display data from multiple tables by using: Equijoins Nonequijoins Outer joins Self-joins Cross joins Natural joins

Full (or two-sided) outer joins **/

/*1.Write a query for the HR department to produce the addresses of all the departments.

Use the LOCATIONS and COUNTRIES tables. Show the location ID, street address, city, state or province, and country in the output.

Use a NATURAL JOIN to produce the results.*/

select lct.location_id,lct.street_address,lct.state_province,cty.country_name from locations lct natural join countries cty;

/*2.The HR department needs a report of all employees.

Write a query to display the last name, department number, and department name for all the employees.*/

select emp.last_name,department_id,dpt.department_name from employees emp natural join departments dpt;

/*3.The HR department needs a report of employees in Toronto.

Display the last name, job, department number, and the department name for all employees who work in Toronto.*/

select emp.last_name,emp.job_id,dpt.department_id,dpt.department_name

from employees emp join departments dpt on emp.department_id=dpt.department_id join locations lct on dpt.location_id=lct.location_id where lct.city='Toronto';

/*4.Create a report to display employees’ last name and employee number along with their manager’s last name and manager number.

Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Save your SQL statement as lab_06_04.sql. Run the query.*/ --自联结 select emp.last_name Employee,emp.employee_id Emp#,mgr.last_name Manager,mgr.employee_id Mgr#

from employees emp join employees mgr on emp.manager_id=mgr.employee_id;

/*5.Modify lab_06_04.sql to display all employees including King, who has no manager.

Order the results by the employee number. Save your SQL statement as lab_06_05.sql. Run the

query in lab_06_05.sql.*/ select emp.last_name Employee,emp.employee_id Emp#,mgr.last_name Manager,mgr.employee_id Mgr#

from employees emp left outer join employees mgr on emp.manager_id=mgr.employee_id order by Emp#;

/*6.Create a report for the HR department that displays employee last names, department numbers,

and all the employees who work in the same department as a given employee.

Give each column an appropriate label. Save the script to a file named lab_06_06.sql.*/ select emp.last_name Employee,emp.department_id,colleague.last_name colleague

from employees emp join employees colleague on emp.department_id=colleague.department_id where emp.employee_id<>colleague.employee_id order by Employee;

/*7.The HR department needs a report on job grades and salaries.

To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table.

Then create a query that displays the name, job, department name, salary, and grade for all employees.*/

--如果没有job_grades表,执行下列语句 /*

--建表

create table JOB_GRADES (

LOWEST_SAL NUMBER(6), HIGHEST_SAL NUMBER(6), GRADE_LEVEL CHAR(1) )

--插入数据

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (30000, 40000, 'F');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (20000, 30000, 'E');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (15000, 20000, 'D');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (8500, 15000, 'C');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (5500, 8500, 'B');

insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL) values (2000, 5000, 'A'); commit; */

select emp.last_name Employee,emp.salary,g.grade_level

from employees emp join job_grades g on emp.salary between g.lowest_sal and g.highest_sal;

--附加练习

/*##8.The HR department wants to determine the names of all the employees who were hired after Davies.

Create a query to display the name and hire date of any employee hired after employee Davies.*/ select emp.last_name Employee,emp.hire_date

from employees emp join employees clg on emp.hire_date > clg.hire_date where clg.last_name='Davies' order by emp.hire_date;

/*##9.The HR department needs to find the names and hire dates of all the employees who were hired before their managers,

along with their managers’ names and hire dates. Save the script to a file named lab_06_09.sql.*/

select emp.last_name Employee,emp.hire_date,mgr.last_name Manager,mgr.hire_date mgr_hire_day

from employees emp join employees mgr on emp.manager_id=mgr.employee_id and emp.hire_date < mgr.hire_date;

9.多表连接

--using 子句

SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id);

--on子句

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id);

--使用ON子句创建多表连接

SELECT employee_id, city, department_name FROM employees e JOIN departments d

ON d.department_id = e.department_id JOIN locations l

ON d.location_id = l.location_id;

--左外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

--右外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

right outer join departments d

ON (e.department_id = d.department_id) ;

--满外连接

SELECT e.last_name, e.department_id, d.department_name FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

--增加条件连接

SELECT e.employee_id, e.last_name, e.department_id, d.department_id,d.location_id

FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149

10.子查询

预习自测:

什么是子查询?

子查询能解决什么类型的问题? 子查询可以用在什么位置? 子查询有哪些类型? */ /*

核心知识: 1.子查询概念

Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

子查询就是按顺序执行系列查询并将前一个查询的结果作为下一个查询使用的值; 2.子查询语法

SELECT select_list FROM table

WHERE expr operator (SELECT select_list

FROM table);

注:operator 包含比较表达式,如: >, =, IN ,等 2.1 子查询(内查询)先于主查询(外查询)执行 2.2 子查询的结果用于外查询 3.子查询可以用于什么位置? where/having/from子句

4.举例:查询工资比Abel高的员工的姓氏和工资 SELECT last_name, salary FROM employees WHERE salary >

(SELECT salary

FROM employees

WHERE last_name = 'Abel');

5.使用指南:

5.1 子查询用括号包含

5.2 将子查询放在比较运算符右边以增加可读性

5.3 单行子查询使用单行运算符,多行子查询使用多行运算符(IN,ANY,ALL) 6.区分单行与多行子查询

6.1 单行子查询:返回单行数据,适用的比较运算符为=,>,<,<>,>=,<= 6.2 多行子查询:返回多行数据,适用的比较运算符为IN,ANY,ALL SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY

(SELECT salary

FROM employees

WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

7.子查询中的空值:

in(...)的含义等价于:= any(...),所以子查询中是否有空值,对结果没有影响;

但是,not in(...)的含义等价于:<> all(...),如果子查询中出现空值,整个表达式为空

自然:任意比较 all(...),如果子查询中出现空值,整个表达式为空 */

/*--------------强大的课后练习-----------------*/ --以下语句是否有问题?有还是没有? /*

select *

from employees e

where e.department_id =

(select d.department_id from departments d where d.department_name in('Marketing','IT')); */

--查询部门名称为Marketing和IT的员工信息

select *

from employees e

where e.department_id in

(select d.department_id from departments d

where d.department_name in('Marketing','IT')); --查询不是经理的员工的信息 select *

from employees e

where e.employee_id not in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null); --查询出所有经理的信息

select e.last_name,e.department_id from employees e

where e.employee_id in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null); --查询工资比10号部门中其中一个员工低的员工信息 select *

from employees e where e.salary

where e1.department_id=10);

--查询工资比10号部门都要低的员工信息 select *

from employees e where e.salary<

(select min(e1.salary) from employees e1

where e1.department_id=10); --如果要显示这个最低工资 select e.last_name,e.salary,s1.ms from employees e,

(select min(e1.salary) ms from employees e1

where e1.department_id=10) s1 where e.salary

--列出与“Sewall”(指的是last_name)从事相同工作的所有员工及部门名称 select e.last_name,d.department_name from employees e,departments d

where e.department_id=d.department_id

and e.job_id=(select job_id from employees where last_name='Sewall') and e.last_name<>'Swall';

--显示和Austin同部门，工资低于Baer的雇员有哪些 select e.last_name from employees e

where e.department_id=(select department_id from employees where last_name='Austin') and e.salary<(select salary from employees where last_name='Baer');

--找出部门90中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK） select *from employees where department_id=90; select e.last_name,e.job_id from employees e where (

e.department_id=90 and

e.employee_id in

(select distinct e1.manager_id from employees e1

where e1.manager_id is not null) ) or(

e.department_id=20 and e.job_id like '%CLERK%' ) ;

--显示每个部门的名称、地点、员工人数以及该部门所有员工的平均薪资，将平均薪资舍入到小数点后两位。

SELECT d.department_name 部门, d.location_id 地点,

s.empnum 部门员工数, s.avgsal 部门平均工资 FROM departments d,

(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal FROM employees e1 GROUP BY e1.department_id) s WHERE d.department_id=s.dptid;

--列出薪金高于公司平均薪金的所有员工,薪资,所在部门名称,上级领导姓名,工资等级 SELECT d.department_name 部门, e.last_name 员工, mgr.last_name 主管, g.grade_level 工资等级 FROM employees e

join employees mgr on e.manager_id=mgr.employee_id join departments d on e.department_id=d.department_id

join job_grades g on e.salary between g.lowest_sal and g.highest_sal WHERE e.salary>(select avg(salary) from employees)

--查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资,及工资等级

--方法1

SELECT d.department_name 部门, s.empnum 部门员工数, s.avgsal 部门平均工资, e.salary 最低工资工资,

e.last_name 部门最低工资雇员, g.grade_level 工资等级 FROM employees e, departments d,

(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal,MIN(e1.salary) minsal

FROM employees e1 GROUP BY e1.department_id) s, job_grades g

WHERE e.department_id=d.department_id AND d.department_id=s.dptid AND e.salary=s.minsal

AND e.salary between g.lowest_sal AND g.highest_sal;

--方法2:

select d.department_name 部门, d.department_id 部门号, s1.empcount 部门员工数, s1.avgsal 部门平均工资, s1.minsal 部门最低工资,

e.last_name 部门最低工资雇员, g.grade_level 工资等级 from employees e

join departments d on e.department_id=d.department_id

join job_grades g on e.salary between g.lowest_sal and g.highest_sal join

(select e1.department_id dptid,COUNT(*) empcount,AVG(e1.salary) avgsal,MIN(e1.salary) minsal from employees e1

group by department_id) s1

on e.department_id=s1.dptid and e.salary=s1.minsal;

/*------------------英文练习题-------------*/

/*1.The HR department needs a query that prompts the user for an employee last name.

The query then displays the last name and hire date of any employee in the same department as the employee

whose name they supply (excluding that employee).

For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding

Zlotkey).*/

select e.last_name,e.hire_date,e.department_id from employees e

where e.department_id=( select e1.department_id from employees e1

where e1.last_name=&last_name) and e.last_name<>&last_name;

/*2.Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary.*/ SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE e.salary>(select avg(salary) from employees) order by e.salary;

/*3.Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains the letter “u.” Save your SQL statement as lab_07_03.sql. Run your query.*/ SELECT e.employee_id, e.last_name

FROM employees e

WHERE e.department_id in(select department_id from employees where last_name like '%u%');

/*4.The HR department needs a report that displays the last name, department number, and job ID of all employees

whose department location ID is 1700.*/ select e.last_name,e.department_id,e.job_id from employees e

where department_id in(select department_id from departments where location_id=1700)

--5.Create a report for HR that displays the last name and salary of every employee who reports to King.

select e.last_name,e.salary from employees e

where e.manager_id in(select employee_id from employees

where last_name='King')

/*6.Create a report for HR that displays the department number, last name, and job ID for every employee

in the Executive department.*/

select e.department_id,e.last_name,e.job_id from employees e

where e.department_id in(select department_id

from departments d

where d.department_name='Executive')

/*7.Modify the query in lab_07_03.sql to display the employee number, last name, and salary of all employees

who earn more than the average salary, and who work in a department with any employee whose last name contains a “u.”

Resave lab_07_03.sql as lab_07_07.sql. Run the statement in lab_07_07.sql. */

SELECT e.employee_id, e.last_name, e.salary

FROM employees e

WHERE e.department_id in(select department_id from employees where last_name like '%u%') and e.salary>(select avg(salary) from employees);

11.创建和管理表

创建和管理表,预习自检: 1.有哪些数据库对象? 表：用于存储数据

视图：一个或者多个表中的数据的子集序列：数字值生成器

索引：提高某些查询的性能同义词：给出对象的替代名称 2.建表是要指定哪些内容? 3.如何建表时为列指定默认值? 4.如何使用子查询语法创建表?

5.如何为已有表新增列,删除列,修改列,为新增列定义默认值? 6.如何标记列为\7.如何批量删除\列 8.如何删除表 9.如何更改表名?

10.如何舍去表中的内容? 11.如何为表,列添加注释?

12.oracle有哪些常用的数据类型? */ /*

使用sql语句完成以下练习: */

--1.显示当前用户拥有的表 select table_name from user_tables;

--2.显示当前用户拥有的表,视图,同义词和序列 select *

from user_catalog; --或者 select * from cat

/*3.创建dept表,结构如下:

列名 id name 数据类型 number varchar2 长度 7 25 */

create table dept2

( id number(7),

name varchar(25) );

--4.使用DEPARTMENTS表中的数据填充dept表,只包含所需列 insert into dept(id,name)

select department_id,department_name from departments;

/*5.创建emp表,结构如下:

列名 id last_name first_name dept_id 数据类型 number varchar2 varchar2 number 长度 7 25 25 7 */

create table emp

(id number(7),

last_name varchar2(25), first_name varchar2(25), det_id number(7) );

--6.修改emp表,从而允许更长的员工姓氏(50),并通过查询数据字典来确认 alter table emp

modify (last_name varchar2(50));

--7.确认dept和emp表都在数据字典中 select table_name from user_tables

where table_name='DEPT' or table_name='EMP';

/*8.根据EMPLOYEES表的结构创建EMPLOYEES2表,仅包含EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID列,

将新表中的各列分别命名为ID,FIRST_NAME,LAST_NAME,SALARY,DEPT_ID*/ create table employees2(id,first_name,last_name,salary,dept_id) as

select employee_id,first_name,last_name,salary,department_id from employees; --9.删除emp表 drop table emp;

--10.将employees2重命名为emp rename employees2 to emp;

--11.向emp表添加一列名为birthday,日期类型,并通过查询数据字典来确认 alter table emp

add(birthday date);

--12.向EMP表的列birthday添加备注(生日),并通过查询数据字典来确认 --要注意

comment on column emp.birthday is'生日'; select *

from user_col_comments

where COLUMN_NAME='BIRTHDAY';

--13.修改表emp,将salary的默认值设置为0] ALTER table emp

modify(salary default(0));

--14.向DEPT和EMP表定义中添加描述表的备注,并通过查询数据字典来确认 comment on table dept

is '部门表';

select *

from user_tab_comments where TABLE_NAME='DEPT' ;

comment on table emp is'员工表';

select *

from user_tab_comments where TABLE_NAME='EMP' ;

--15.删除emp表的first_name列 alter table emp drop (first_name);

--16.将表emp的dept_id列标记为不适用(unused)

alter table emp

set unused(dept_id);

--17.删除emp表中所有不使用(unused)的列 alter table emp

drop unused column;

select table_name,column_name,data_type,t.data_default from user_tab_cols t where table_name=upper('employees');

12.约束

约束,预习自检 1.什么是约束 2.有哪些约束

什么是主键, 什么是外键? 3.他们的含义分别是什么? 4.可以在什么时候创建约束? 5.如何在建表是创建约束? 6.如何在建表后为表添加约束? 7.如何删除约束? 8.如何禁用/启用约束

9.如何在删除列的时候删除其关联的约束? 10.如何查看用户的约束? 11.如何查看列上的约束? */

select * from user_constraints where table_name='JOB_HISTORY'; select * from user_cons_columns where table_name='JOB_HISTORY' /*

--------------练习-------------- */

--1.为emp表的id列添加主键约束,命名为my_emp_id_pk; alter table emp

add constraint my_emp_id_pk primary key(id);

--2.为dept表的id列添加主键约束,命名为my_dept_id_pk; alter table dept

add constraint my_dept_id_pk primary key(id);

--3.向表emp中添加一个dept_id列,它引用部门表,该约束命名为my_emp_dept_id_pk;

alter table emp

add(dept_id number(8) ,

constraint my_emp_dept_id_pk foreign key(dept_id)references dept(id));

--4.通过user_constraints视图,确认已经添加了这些约束. select *

from user_constraints

where constraint_name='MY_EMP_ID_PK' or constraint_name='MY_DEPT_ID_PK'

or constraint_name='MY_EMP_DEPT_ID_PK';

--5.修改emp表,添加一个commission列,其数据类型为number,精度为2,小数位数为2,为该佣金列添加一个确保佣金值大于0的约束. alter table emp

add(commission number(2,2) constraint check_cms check(commission>0)); --6.为emp表添加约束,保证last_name的唯一性 alter table emp

add constraint unique_n unique(last_name);

--7.为emp表添加约束,保证last_name和first_name均不能为空 alter table emp

add constraint name_is_not_null check(last_name is not null and first_name is not null); /*8.创建学生表Student(id,name,birthday,sex) 含义分别为:学生编号,学生姓名,出生年月日,学生性别;

在建表的同时指定id为主键,name不能为空,sex必须为男或者女; */

create table Student ( id number(8) not null,

name varchar2(25) not null, birthday date,

sex varchar2(10) check(sex='男'or sex='女'), constraint stu_id_pk primary key(id) );

--或者,让系统为逐渐约束命名 create table Student

( id number(8) primary key, name varchar2(25) not null, birthday date,

sex varchar2(10) check(sex='男'or sex='女') );

/* 此处用于检测emp中的约束有哪些 select *

from user_constraints where table_name='EMP' */

13.视图，序列，索引

1.The staff in the HR department wants to hide some of the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table.

They want the heading for the employee name to be EMPLOYEE(列名). */

create view EMPLOYEES_VU as

select employee_id,last_name as EMPLOYEE,department_id from employees ; /*

2.Confirm that the view works. Display the contents of the EMPLOYEES_VU view. */

select * from user_views

where view_name='EMPLOYEES_VU'; /*

3.Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers. */

select EMPLOYEE,department_id from EMPLOYEES_VU; /*

4.Department 50 needs access to its employee data.

Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50.

You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO.

For security purposes, do not allow an employee to be reassigned to another department through the view. */

create view DEPT50(EMPNO,EMPLOYEE,DEPTNO) as

select employee_id,last_name,department_id from employees

where department_id=50 with read only; /*

5.Display the structure and contents of the DEPT50 view. */

desc DEPT50;

select text from user_views where view_name='DEPT50'; /*

6.Test your view. Attempt to reassign Matos to department 80.

update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*

7.You need a sequence that can be used with the PRIMARY KEY column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. */

create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*

8.To test your sequence, write a script to insert two rows in the DEPT table.

Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. Run the commands in your script. */

insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Education'); insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Administration'); /*9.Create a nonunique index on the NAME column in the DEPT table.*/ create index on DEPT(name);

--查看索引

select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;

14.其他数据库对象

They want the heading for the employee name to be EMPLOYEE(列名). */

create view EMPLOYEES_VU as

select employee_id,last_name as EMPLOYEE,department_id from employees ; /*

2.Confirm that the view works. Display the contents of the EMPLOYEES_VU view. */

select * from user_views

where view_name='EMPLOYEES_VU'; /*

3.Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers. */

select EMPLOYEE,department_id from EMPLOYEES_VU; /*

4.Department 50 needs access to its employee data.

Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50.

You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO.

For security purposes, do not allow an employee to be reassigned to another department through the view. */

create view DEPT50(EMPNO,EMPLOYEE,DEPTNO) as

select employee_id,last_name,department_id from employees

where department_id=50 with read only; /*

5.Display the structure and contents of the DEPT50 view. */

desc DEPT50;

select text from user_views where view_name='DEPT50'; /*

6.Test your view. Attempt to reassign Matos to department 80. */

update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*

create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*

8.To test your sequence, write a script to insert two rows in the DEPT table.

Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration.

Confirm your additions. Run the commands in your script. */

select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;

15.pl sql基础

1.在声明部分声明各种标量变量,并尝试在程序体中使用select语句为某个标量变量赋值 */

declare

-- Local variables here

hire_date DATE;

first_name VARCHAR2(25):='zhangsan'; salary NUMBER(8,2); isLeader BOOLEAN; age pls_integer; blood_type CHAR DEFAULT 'O';

acct_id INTEGER(4) NOT NULL := 9999; begin

-- 直接赋值 isLeader:=true;

--常用方式,利用select对变量赋值 select sysdate into hire_date from dual;

select first_name into first_name from employees where employee_id=100; dbms_output.put_line(first_name);

dbms_output.put_line(to_char(hire_date)); end; /*

2.声明行级记录变量,并尝试在程序体中使用select语句为整个记录赋值,尝试为单个分量赋值 */

declare

emp_rec employees%ROWTYPE;--emp_rec的结构与emp表的结构一致

cursor c1 IS

SELECT dep.department_id,dep.department_name ,dep.location_id FROM departments dep;

dept_rec c1%ROWTYPE;--也可以用在游标上

Begin

select dep.department_id,dep.department_name ,dep.location_id into dept_rec

FROM departments dep

where dep.department_id=20;--这里不能返回多行 dbms_output.put_line(dept_rec.department_name);

emp_rec.first_name:='zhang'; emp_rec.salary:=3000;

end; /*

3.在声明部分定义游标,并尝试或获取游标中的值 */

declare

cursor c_emp is

select a.employee_id,a.first_name||' '||a.last_name as ename ,a.job_id ,a.salary from employees a

where a.department_id=20;

total_salary employees.salary%type :=0; begin

--依次提取游标中的行,赋值给c_emp_r，c_emp_r不用事先声明 for c_emp_r in c_emp loop

total_salary := total_salary+c_emp_r.salary; dbms_output.put_line(c_emp_r.ename); end loop;

dbms_output.put_line('工资sum:'||to_char(total_salary)); end; /*

4.根据指定id获得员工的工资,工资在1000-3000内的输出A,3000-5000之间的输出B,5000-8000之间的输出C,8000以上的输出D 使用if-else和case两种方式来完成 */

declare

-- Local variables here

id integer;

v_salary employees.salary%type; v_grade char(1); begin id:=:id;

select salary into v_salary from employees t where t.employee_id=id; if v_salary>=1000 and v_salary<3000 then v_grade:='A';

elsif v_salary>=3000 and v_salary<5000 then v_grade:='B';

elsif v_salary>=5000 and v_salary<8000 then v_grade:='C'; elsif v_salary>=8000 then v_grade:='D'; else null; end if;

dbms_output.put_line('工资级别：'||v_grade); end;

--5.使用FOR循环求1-100之间的素数 declare

-- Local variables here i integer;

v_flag boolean; begin

-- Test statements here for i in 2..100 loop v_flag:=true; for j in 2..i/2 loop if mod(i,j)=0 then begin

v_flag:=false; exit; end; end if; end loop; if v_flag then

dbms_output.put(rpad(to_char(i),8,' ')); end if; end loop;

dbms_output.new_line; end;

--6.(选做)使用LOOP循环求1-100之间的素数

--7.打印99乘法表 declare

-- Local variables here i integer; j integer; begin

for i in 1..9 loop for j in 1..i loop

dbms_output.put(i||'*'||j||'='||(i*j)||' '); end loop;

dbms_output.put_line(''); end loop; end;

--8.根据工资查询员工姓名。如果此员工不存在（发出NO_DATA_FOUND异常），打印相应的提示信息。 declare

cursor c_name(p_salary employees.salary%type) is

select last_name from employees t where t.salary=p_salary; begin

for names in c_name('&v_salary') loop

dbms_output.put_line(names.last_name); end loop; exception

when no_data_found then dbms_output.put_line('没有改工资对应的员工'); end;

--9.显示EMP中的第四条记录。游标%rowcount=4 declare

cursor c_emp is

select * from employees t ; begin

for rec in c_emp loop

if(c_emp%rowcount=4) then

dbms_output.put_line(rec.last_name); end if; end loop; exception

when no_data_found then dbms_output.put_line('没有改工资对应的员工'); end; /*

10.根据部门名称(由用户输入)，按以下格式打印各部门人员姓名：部门名称：RESEARCH

部门人员：SMITH,JONES,FORD */

declare

v_ename employees.last_name%type; v_ename_str varchar2(1000);

v_dname departments.department_name%type; --部门名称 cursor c_emp(dname varchar2) is SELECT e.last_name FROM employees e,departments e.department_id=d.department_id and department_name=dname; begin

v_dname := '&请输入部门名称：'; --v_dname := 'Marketing';

dbms_output.put_line('部门名称:'||v_dname); open c_emp(v_dname); loop

fetch c_emp into v_ename; exit when c_emp%notfound;

--dbms_output.put(v_ename||',');--put会放缓冲区 v_ename_str := v_ename_str||v_ename||','; end loop;

v_ename_str := substr(v_ename_str,1,length(v_ename_str)-1); dbms_output.put_line('部门人员:'||v_ename_str); --dbms_output.new_line;--刷新缓冲区 end; /*

11.针对所有部门，按以下格式打印各部门人员姓名：部门名称：RESEARCH 部门人员：SMITH,JONES,FORD

部门名称：ACCOUNTING 部门人员：CLARK,KING,MILLER,

如果该部门没有人员,则输出: 部门名称：Treasury

部门人员：该部门没有员工实现提示： 1）循环每个部门，用其部门号作条件去查员工表 2）用显示cursor完成 3）要求用FOR，会用到嵌套循环。 */

DECLARE

cursor c_department

d where

is select * from departments;

cursor c_emp(p_deptno employees.department_id%type)

is select * from employees where department_id=p_deptno; v_empname varchar2(1000); i integer:=0; begin

for dept in c_department loop

dbms_output.put_line('部门名称：'||dept.department_name); dbms_output.put('部门人员：'); v_empname:=''; i:=0;

for emp in c_emp(dept.department_id) loop

v_empname:=v_empname||emp.last_name||','; i:=i+1; end loop;

if(i=0) then

dbms_output.put_line('该部门没有员工'); else

dbms_output.put_line(substr(v_empname,1,length(v_empname)-1)); end if; end loop; end; /*

12.对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；如果该员工职位是CLERK，并且在NEW

YORK工作那么就给他薪金扣除5％;其他情况不作处理*/ declare

cursor c_emp is

select t.employee_id,t.job_id,l.city

from employees t ,departments d ,locations l

where t.department_id=d.department_id and d.location_id=l.location_id; begin

for rec in c_emp loop

if(REGEXP_LIKE (rec.job_id, '.*_MGR') and rec.city='DALLAS') then update employees t set t.salary=t.salary*(1.15) where t.employee_id=rec.employee_id; end if;

if (REGEXP_LIKE (rec.job_id, 'S[HT]_CLERK') and rec.city='NEW YORK') then update employees t set t.salary=t.salary*0.95 where t.employee_id=rec.employee_id; end if; end loop;

end; /*

13.对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪： 81年6月以前的加薪10％ 81年6月以后的加薪5％ */ declare

cursor c_emp is select t.*

from employees t

where t.manager_id in (select employee_id from employees where last_name = 'BLAKE'); begin

for rec in c_emp loop

if rec.hire_date

update employees t set t.salary=t.salary*1.1 where t.employee_id=rec.employee_id; end if;

if rec.hire_date>=to_date('19810601','yyyymmdd') then update employees t set t.salary=t.salary*1.05 where t.employee_id=rec.employee_id; end if; end loop; end;

Word文档下载：Oracle 数据库所有查询命令.doc

搜索更多:Oracle 数据库所有查询命令