sql
/*空值不同于0，凡是空值参与的运算，结果都为空（null）
DML:Data Manipulation Language 数据操作语言
DDL:Data Definition Language 数据定义语言
DCL:Data Control Language 数据控制语言
select employee_id,salary,commission_pct,salary*(1+commission_pct)
from employees
--别名
select employee_id as id,last_name name,12*salary annual_sal
from employees
select employee_id as "id",last_name "Name",12*salary annual_sal
from employees
--“||”连接符
select last_name||'`s job is '||job_id as "detail"
from employees
select last_name||'`s hire_date is '||hire_date as "details"
from employees
--“distinct”去重
select distinct department_id from employees
select employee_id,last_name,salary
from employees
where employee_id > 200
and salary > 500
select employee_id,last_name,salary
from employees
where last_name = 'Higgins'
select last_name,hire_date
from employees
where hire_date = '7-6月-1994'
select last_name,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') = '1994-06-07'
--between包含边界
select last_name,hire_date,salary
from employees
--where salary >= 4000 and salary <=7000
where salary between 4000 and 7000
--in的使用
select last_name,department_id,salary
from employees
--where department_id = 90 and department_id = 80 and department_id = 70
where department_id in(70,80,90)
--like的使用,'%'表示任意字符，'_'表示任何一个字符
select last_name,department_id,salary
from employees
--员工名字中末尾含有a的员工有哪些
--where last_name like '%a'
--员工名字中含有a的员工有哪些
--where last_name like '%a%'
--员工名字中第二位含有a的员工有哪些
--where last_name like '_a%'
--员工名字中第三位含有a的员工有哪些
where last_name like '__a%'
--escape后的“\”表示转义字符，“\”可以是任意字符
select last_name,department_id,salary
from employees
where last_name like '%\_%' escape '\'
select last_name,department_id,salary,commission_pct
from employees
where commission_pct is null
优先级：
算术运算符>连接符>比较符>is [not] null,like,[not] in>[not] between>not>and>or
--排序：desc从大到小，asc从小到大（缺省排序）
select last_name,department_id,salary
from employees
where department_id = 80
order by salary desc
select last_name,department_id,salary
from employees
where department_id = 80
order by salary asc
select last_name,department_id,salary
from employees
where department_id = 80
order by salary
select last_name,department_id,salary
from employees
where department_id = 80
order by salary asc,last_name asc
--按照别名来排序
select last_name,department_id,salary,12*salary "annual_sal"
from employees
order by "annual_sal"
--查询5000和一万的工资的员工
select last_name,salary
from employees
--where salary >= 5000 and salary <= 10000
where salary between 5000 and 10000
--查询工资等于6000，7000，8000，9000，10000的员工信息
select * from employees
--where salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary =10000;
where salary in(6000,7000,8000,9000,10000)
--lower 小写，upper 大写，initcap 首字母大写
select lower('ATGUIGUJAVA'),UPPER('AtGuiGu Java'),initcap('ATGUIGU java')
from dual
--select * from employees where lower(last_name) = 'king'
select * from employees where upper(last_name) = 'KING'
--concat 连接字符串，substr 截取，length 字符串长度
select concat('Hello','World!'),substr('HelloWorld',2,4),length('HelloWorld!')
from dual;
select instr('HelloWorld!','d') from dual;
select instr('HelloWorld!','c') from dual;
--左填充，右填充：
select employee_id,last_name,lpad(salary,10,'*'),rpad(salary,10,'*')
from employees;
select employee_id,last_name,lpad(salary,10,' '),rpad(salary,'10',' ')
from employees
--trim删除（只删除前后）
select trim('h' from 'hhhellohhWroldhhh') from dual;
--替换：replace
select replace('abcdab','b','m') from dual;
--round 保留位数,缺省保留整数。
select round(435.45,1),round(435.45),round(435.45,-1) from dual;
--trunc截断
select trunc(435.45,1),trunc(435.45),trunc(435.45,-1) from dual;
--mod :余数
select mod(1100,300) from dual
--日期可以做加减
select sysdate,sysdate + 1,sysdate - 3 from dual;
select employee_id,last_name,trunc(sysdate-hire_date) worked_days
from employees
order by worked_days;
select employee_id,last_name,(sysdate-hire_date)/30 month_days
from employees
order by month_days;
--months_between 两个日期之间有几个月
select employee_id,last_name,(sysdate-hire_date)/30 month_days,months_between(sysdate,hire_date)
from employees
order by month_days;
--向指定日期中加上若干月数 add_months
--指定日期的下一个星期“对应的日期” next_day
星期：
一:monday
二：tuesday
三：wednesday
四：thursday
五：friday
六；saturday
日：sunday
select add_months(sysdate,3),add_months(sysdate,-1),next_day(sysdate,'sunday') from dual;
--本月的最后一天 last_day
select last_name,hire_date
from employees
where hire_date = last_day(hire_date) - 1;
select last_day(sysdate) from dual;
--日期四舍五入 round
select round(sysdate,'year'),round(sysdate,'month'),round(sysdate,'day'),round(sysdate,'hh') from dual;
--日期截断 trunc
select trunc(sysdate,'year'),trunc(sysdate,'month'),trunc(sysdate,'day'),trunc(sysdate,'hh') from dual;
--数据类型转换: date <===> varchar2 <===> number (to_date,to_number,to_char)
select sysdate + 2 from dual;
select employee_id,hire_date from employees
--where hire_date = '7-6月-94'
--where to_char(hire_date,'yyyy-mm-dd')='1994-06-07'
where to_date('1994-06-07','yyyy-mm-dd') = hire_date
select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"') from employees
where to_char(hire_date,'yyyy-mm-dd')='1994-06-07';
--to_char函数中经常使用的几种格式（9：数字，0：零，$美元符，L：本地货币符号，.：小数点，，：千位符）
select to_char(1234334432432.7979,'999,999,999,999,999.9999') from dual;
select to_char(1234334432432.7979,'000,000,000,000,000.0000') from dual;
select to_char(1234334432432.7979,'$000,000,000,000,000.0000') from dual;
select to_char(1234334432432.7979,'L000,000,000,000,000.0000') from dual;
select to_number('￥001,234,567.89','L000,000,999.99') + 1 from dual;
select to_number('$001,234,567.89','$000,000,999.99') from dual;
select employee_id,last_name,salary*12*(1 + nvl(commission_pct,0)) "annual sal"
from employees;
select last_name,nvl(to_char(department_id,'99999999999'),'没有部门') from employees;
-- 不为空返回 为空返回
select last_name,commission_pct,nvl2(commission_pct,commission_pct + 0.015,0.01) from employees;
--nullif(expr1,expr2);相等返回null，不相等返回expr1
coalesce(expr1,expr2,expr3,……):一个值为空返回下一个值
--根据条件处理数据
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
else salary * 1.3 end
as nwe_sal
from employees
where department_id in(10,20,30);
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
else salary end
as nwe_sal
from employees;
select employee_id,last_name,department_id,decode(department_id,10,salary * 1.1,
20,salary * 1.2,
salary) as new_sal
from employees
where department_id in(10,20,30);
--练习
--打印输出格式为"2009年10月14日 9:25:40" 格式的当前系统的日期和时间。
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss')
from dual
--等值连接,内连接，都满足条件
select employee_id,employees.department_id,department_name
from employees,departments
where employees.department_id=departments.department_id
select e.employee_id,e.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id
select employee_id,e.department_id,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
--非等值连接，内连接，都满足条件
select employee_id,last_name,salary,lpad(grade_level,10,' ') "nwe grade_level"
from employees e,job_grades j
where salary between lowest_sal and highest_sal
--外连接(左外连接)，返回左边不满足条件得行
select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+)
--外连接(右外连接)，返回右表中不满足条件得行
select last_name,e.department_id,department_name
from employees e,departments d
where e.department_id(+)=d.department_id
--使用SQL：1999语法连接
--默认以相同字段作为连接条件，缺点：如果有多个连接条件时，默认以多个条件作为连接条件，无法指定用哪个字段作为连接条件
select employee_id,department_id,department_name
from employees natural join departments
--可以指定以哪个字段作为连接条件。缺点：字段名和数据类型要一样
select employee_id,department_id,department_name
from employees join departments
using(department_id)
select employee_id,e.department_id,department_name
from employees e join departments d
on e.department_id=d.department_id
select employee_id,e.department_id,department_name,city
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
--左外连接
select employee_id,e.department_id,department_name
from employees e left outer join departments d
on e.department_id=d.department_id
--右外连接
select employee_id,e.department_id,department_name
from employees e right outer join departments d
on e.department_id=d.department_id
--满外连接
select employee_id,e.department_id,department_name
from employees e full outer join departments d
on e.employee_id = d.department_id
--自连接
--查询公司中员工'Chen'的manager的信息
select emp.last_name,manager.last_name,manager.salary,manager.email
from employees emp,employees manager
where emp.manager_id=manager.employee_id and lower(emp.last_name)='chen'
练习题：
1、显示所有员工的姓名，部门号和部门名称
select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id=d.department_id
select last_name,e.department_id,department_name
from employees e , departments d
where e.department_id=d.department_id(+)
2、查询90号部门员工的job_id和90号部门的location_id
select last_name,m.department_id,job_id,location_id
from employees m,departments d
where m.department_id = d.department_id and m.department_id=90
select last_name,d.department_id,job_id,location_id
from departments d join employees e
on d.department_id=e.department_id
where e.department_id=90
select department_id,job_id,location_id
from departments join employees
using(department_id)
where department_id=90
select d.department_id,job_id,location_id
from departments d left outer join employees e
on d.department_id=e.department_id
where e.department_id=90
select d.department_id,job_id,location_id
from departments d right outer join employees e
on d.department_id=e.department_id
where e.department_id=90
select d.department_id,job_id,location_id
from departments d,employees e
where d.department_id=e.department_id(+)
and e.department_id=90
3、选择所有有奖金的员工的 last_name,department_name,location_id,city
select last_name,department_name,d.location_id,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and commission_pct is null
select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where commission_pct is null
4、选择city在Toronto工作的员工的 last_name,job_id,department_id,department_name
select last_name,job_id,e.department_id,department_name
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where lower(city)='toronto'
select last_name,job_id,e.department_id,department_name
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and lower(city)='toronto'
5、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
select e1.last_name "employees",e2.employee_id "Emp#",e1.last_name "manager",e2.employee_id Mgr#
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+)
select e1.last_name,e1.employee_id,e2.last_name,e2.employee_id
from employees e1 join employees e2
on e1.manager_id=e2.employee_id(+)
--select中有的字段，group by 中必须有（除了组函数中的字段arg()）
--group by 中有的字段select中不必有 即非组行数的列必须放在group by 中
select department_id,job_id,avg(salary)
from employees
group by department_id,job_id
select department_id,avg(salary)
from employees
--where department_id in (40,60,70)
--where avg(salary) > 6000 --where和组函数不能同时使用
having avg(salary) > 6000
group by department_id
order by department_id asc
--组函数可以互相嵌套
select max(lpad(round(avg(salary),2),10,' ')),min(trunc(avg(salary),2))
from employees
group by department_id
--有多少个部门
select count(distinct department_id) from employees
--计算全公司的奖金的值
select avg(nvl(commission_pct,0)) from employees
--查询各个部门的平均工资
select department_id,avg(salary) from employees
group by department_id
order by department_id asc
--Toronto这个城市的平均工资
select avg(salary),city
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
having lower(city)='toronto'
group by city
select 'Toronto',avg(salary)
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
where lower(city)='toronto'
select 'Toronto',avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.city='Toronto'
--(有员工的城市)各个城市的平均工资
select city,avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
group by city
--平均工资高于8000的部门id和它的平均工资
select e.department_id,avg(salary)
from employees e
having avg(salary) > 8000
group by e.department_id
--查询平均工资高于6000的job_title 有哪些
select job_title
from employees e natural join jobs
having avg(salary) > 6000
group by job_title
--查询所有部门的名字，location_id,员工数量和工资平均值
select department_name,location_id,count(employee_id),avg(salary)
from employees e right outer join departments d
on e.department_id=d.department_id
group by department_name,location_id
--查询公司在1995~1998年之间，每年雇佣的人数，结果类似下面的格式
total 1995 1996 1997 1998
20 3 4 6 7
答案：
select count(*) "total",
count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees e
where to_char(HIRE_DATE,'yyyy') in ('1995','1996','1997','1998')
--谁的工资比Abel高
select last_name,salary
from employees where salary >
(select salary from employees where lower(last_name)='abel')
--返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
select last_name,job_id,salary
from employees
where job_id=(select job_id from employees where employee_id = 141)
and salary>(select salary from employees where employee_id = 143)
--返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees
where salary = (select min(salary) from employees)
--查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary) > (
select min(salary)
from employees
where department_id=50
)
多行子查询：
操作符 含义
in 等于列表中的任意一个
any 和子查询返回的某一个值比较
all 和子查询返回的所有值比较
--返回其它部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
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'
--返回其它部门中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where salary < all
(select salary
from employees
where job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
--查询工资最低的员工信息：last_name,salary
select last_name,salary
from employees
where salary=(select min(salary) from employees)
select min(salary)
from employees
--查询平均工资最低的部门信息
select * from departments where department_id=(select department_id
from employees
having avg(salary)=(select min(avg(salary))
from employees
group by department_id)
group by department_id)
--查询平均工资最低的部门信息和该部门的平均工资
select d.*,(select avg(salary) from employees where department_id=d.department_id)
from departments d where department_id=(
select department_id from employees having avg(salary)=(
select min(avg(salary))
from employees
group by department_id)
group by department_id)
--查询平均工资最高的job信息
select * from jobs where job_id=(
select job_id from employees having avg(salary)=(
select max(avg(salary))
from employees e
group by job_id)
group by job_id)
--查询平均工资高于公司平均工资的部门有哪些?
select department_id,avg(salary) from employees
having avg(salary) > (select avg(salary) from employees)
group by department_id
--查询出公司中所有manager的详细信息。
select * from employees
where employee_id in (select manager_id from employees)
--各个部门中最高工资中最低的那个部门的最低工资是多少？
select min(salary) from employees where department_id=(
select department_id from employees having max(salary)=(
select min(max(salary)) from employees
group by department_id)
group by department_id)
--查询平均工资最高的部门的manager的详细信息: last_name,department_id,email,salary
select last_name,department_id,email,salary,manager_id from employees where manager_id in (
select distinct manager_id
from employees
where department_id=(
select department_id from employees
having avg(salary)=(
select max(avg(salary)) from employees
group by department_id)
group by department_id))
--查询1999年来公司的员工中最高工资的那个员工的信息
select * from employees
where salary = (
select max(salary) from employees where to_char(hire_date,'yyyy') = '1999'
)
and to_char(hire_date,'yyyy') = '1999'
--查询和Zlotkey相同部门的员工姓名和雇佣日期
--查询工资比公司平均工资高的员工的员工号，姓名和工资。
--查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
--查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
--查询在部门的location_id为1700的部门工作的员工的员工号
1.查询和Zlotkey相同部门的员工姓名和雇佣周期
select last_name,hire_date
from employees
where department_id=(select department_id from employees where lower(last_name)='zlotkey')
and lower(last_name)<>'zlotkey'
2.查询工资比公司平均工资高的员工的员工号，姓名和工资
select last_name,employee_id,salary
from employees
where salary>(select avg(salary) from employees)
3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary from employees e1
where salary > (select avg(salary)
from employees e2
where e1.department_id=e2.department_id
group by department_id)
4.查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id,last_name
from employees where department_id in
(select department_id from employees
where last_name like '%u%')
and last_name not like '%u%'
5.查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees
where department_id in(
select department_id
from departments
where location_id =1700)
6.查询管理者是king的员工姓名和工资
select last_name,salary
from employees
where manager_id in(
select employee_id
from employees
where lower(last_name)= 'king')
通过本章的学习，您将可以：
1.描述主要的数据库对象
2.创建表
3.描述各种数据类型
4.修改表的定义
5.删除，重命名和清空表
常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询的效率
同义词 给对象起别名
select * from user_tables
select * from user_catalog
select distinct object_type
from user_objects
命名规则
必须以字母开头
必须在 1 - 30 个字符之间
必须只能包含 A-Z,a-z,0-9,_,$和#
必须不能和用户定义的其他对象重名
必须不能是Oracle的保留字
$abc,2dbc,_abc,a-b
a#b
--创建表的第一种方式（白手起家）
create table emp1(
ID NUMBER(10),
NAME VARCHAR2(20),
SALARY NUMBER(10,2),
HIRE_DATE DATE
)
--创建表的第二种方式（依托于现有的表）
create table emp2
as
select employee_id id,last_name name ,hire_date,salary
from employees
create table emp3
as
select employee_id id,last_name name ,hire_date,salary
from employees where department_id = 80
--如果只需要表结构，就用where过滤数据。
create table emp3
as
select employee_id id,last_name name ,hire_date,salary
from employees where department_id = 800
或
create table emp3
as
select employee_id id,last_name name ,hire_date,salary
from employees where 1=2
数据类型
数据类型 描述
VARCHAR2(SIZE) 可变长字符数据
CHAR(SIZE) 定长字符数据
NUMBER(P,S) 可变长数值数据
DATE 日期型数据
LONG 可变长字符数据，最大可达到2G
CLOB 字符数据，最大可达到4G
RAW(LONG RAW) 原始的二进制数据
BLOB 二进制数据，最大可达到4G
BFILE 存储外部文件的二进制数据，最大可达到4G
ROWID 行地址
ALTER TABLE语句
使用ALTER TABLE 语句可以：
追加新的列
修改现有的列
为新追加的列定义默认值
删除一个列
重命名表的一个列名
使用ALTER TABLE 语句追加，修改，或者删除列的语法
ALTER TABLE table_name
ADD (column datatype [DEFAULT expr])
[,column datatype]……);
ALTER TABLE table_name
MODIFY (column datatype [DEFAULT expr])
[,column datatype]……);
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN old_column_name
TO new_column_name;
truncate table table_name --清空表，不能回滚
--删除表种的所有数据
--释放表的存储空间
delete from table_name --删除数据，可以回滚
truncate 不可以rollback
delete 可以rollback
改变对象的名称：（表，视图，序列，同义词……）
RENAME dept TO detail_dept; --必须是对象的拥有者。
drop table table_name --删除表
DDL语句
语句 描述
CREATE TABLE 创建表
ALTER TABLE 修改表结构
DROP TABLE 删除表
RENAME TO 重命名
TRUNCATE TABLE 删除表种的所有数据，并释放存储空间
以上的DDL命令，操作除外，皆不可回滚！
使用DML语句
向表种插入数据
更新表中数据
从表中删除数据
控制事务
事务是由完成若干项工作的DML语句组成的
INSERT INTO table [(column [,column [,column……]])]
VALUES (value [,value……]);
create table emp1 (
employee_id number(6),
last_name varchar2(25),
hire_date date,
salary number(8,2)
)
insert into emp1
values(1001,'AA',sysdate,10000)
insert into emp1
values(1002,'BB',to_date('1992-4-10','yyyy-mm-dd'),20000)
insert into emp1
values(1003,'CC',to_date('1999-4-10','yyyy-mm-dd'),null)
insert into emp1(last_name,employee_id,hire_date)
values('DD',1004,to_date('1993-10-10','yyyy-mm-dd'))
insert into emp1(employee_id,last_name,hire_date,salary)
select employee_id,last_name,hire_date,salary
from employees
where employee_id=80
insert into emp1(employee_id,last_name,salary,hire_date)
values(1005,'FF',3400,'21-3月-1995')
insert into emp1(employee_id,last_name,salary,hire_date)
values(&id,'&last_name',&salary,'&hire_date')
--update后要commit一下。在commit前可以rollback
update emp1
set salary = 12000
where employee_id=1001;
--更新114号员工的工作和工资使其与205号员工的相同
select employee_id,job_id,salary
from employees
where employee_id in(114,205)
update employees
set job_id=(
select job_id from employees where employee_id=205
)
,salary=(
select salary from employees where employee_id=205
)
where employee_id=114
delete from table_name
where condition;
数据库事务
事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成：
一个或多个DML语句
一个DDL语句
一个DCL语句
以下面的其中之一作为结束：
commit 或 rollback 语句
DDL语句（自动提交）
用户会话正常结束
系统异常终止
savepoint A --保存点
rollback savepoint A; --可以回滚到保存点。
约束是表级的强制规定
有以下五种约束：
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
如果不指定约束名，Oracle server 自动按照SYS_Cn 的格式指定约束名
创建和修改约束：
创建表的同时创建约束
建表之后创建约束
可以在表级或列级定义约束
可以通过数据字典视图查看约束
作用范围：
列级约束只能作用在一个列上
表级约束可以作用在多个列上（表级约束也可以作用在一个列上）
定义方式：列约束必须跟在列的定义后面，表级约束不与列一起，而是单独定义。
非空约束只能定义在列上。
--非空(not null)
create table emp2(
id number(10) constraint emp2_id_nn not null,
name varchar2(20) not null,
salary number(10,2)
)
--唯一键（unique）
create table emp3(
--列级约束
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20),
salary number(10,2),
--表级约束
constraint emp3_email_uk unique(email)
)
insert into emp3
values(1001,'AA','AA@126.com',2000);
insert into emp3
values(1002,'BB',null,3000);
insert into emp3
values(1003,'CC',null,4000);
--主键（promary key）(非空，不能重复)
create table emp4(
--列级约束
id number(10) constraint emp4_id_pk primary key,
name varchar2(20) constraint emp4_name_nn not null,
email varchar2(20),
salary number(10,2),
--表级约束
constraint emp4_email_uk unique(email)
)
insert into emp4
values(1001,'AA',null,2000);
insert into emp4
values(1001,'BB',null,3000);
create table emp5(
--列级约束
id number(10),
name varchar2(20) constraint emp5_name_nn not null,
email varchar2(20),
salary number(10,2),
--表级约束
constraint emp5_email_uk unique(email),
constraint emp5_id_pk primary key(id)
)
--外键(foreign key)
create table emp6(
--列级约束
id number(10),
name varchar2(20) constraint emp6_name_nn not null,
email varchar2(20),
salary number(10,2),
department_id number(10),
--表级约束
constraint emp6_email_uk unique(email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id) on delete cascade --级联删除
或者
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null --级联置空
)
--check
create table emp8(
--列级约束
id number(10),
name varchar2(20) constraint emp8_name_nn not null,
email varchar2(20),
salary number(10,2) constraint emp8_salary_ck check(salary > 1500 and salary < 30000),
department_id number(10),
--表级约束
constraint emp8_email_uk unique(email),
constraint emp8_id_pk primary key(id),
constraint emp8_dept_dept_id_fk foreign key(department_id) references departments(department_id) on delete set null
)
使用 ALTER TABLE 语句：
添加或删除约束，但是不能修改约束
有效化或无效化约束
添加 NOT NULL 约束要使用 MODIFY 语句
--修改约束
alter table emp4
modify(salary number(10,2) constraint emp4_salary_nn not null)
--删除约束
alter table emp4
drop constraint emp4_name_nn
alter table emp4
drop constraint sys_c0028540
--添加约束
alter table emp4
add constraint emp4_name_uk unique(name);
有效化和有效化
在 ALTER TABLE 语句中使用 DISABLE 子句将约束无效化
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
--disable 使约束失效
alter table emp3
disable constraint emp3_email_uk;
--使约束生效或激活约束
alter table emp3
enable constraint emp3_email_uk;
从数据字典中查询约束：
查询数据字典视图：USER_CONSTRAINTS
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name='employees';
查询定义约束的列
查询数据字典视图：USER_CONS_COLUMNS
SELECT column_name,constraint_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
create table emp2 as select employee_id id,last_name name,salary from employees;
create table dept2 as select department_id id,department_name dept_name from departments;
1、向表emp2的id列中添加PRIMARY KEY 约束（my_emp2_id_pk）
alter table emp2
add constraint my_emp2_id_pk PRIMARY KEY(id);
2、向表dept2的id列中添加PRIMARY KEY 约束(my_dept2_id_pk)
alter table dept2
add constraint my_dept2_id_pk PRIMARY KEY(ID);
3、向表emp2中添加列dept_id,并在其中定义FORIEGN KEY约束，
与之相关联的列是dept2表中的id列。
alter table emp2 add(dept_id number(10) constraint emp2_dept_id_fk references dept2(id) on delete set null);
视图
视图是一种虚表
视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句。
视图向用户提供基表数据的另一种表现形式
创建视图语法：
create view view_name
as
select column_name
from table_name;
目标：
描述视图
创建和修改视图的定义，删除视图
从视图中查询数据
通过视图插入，修改和删除数据
使用“Top-N”分析
为什么使用视图：
- 控制数据访问
- 简化查询
- 避免重复访问相同的数据
--重置字段
create view empview1
as
select employee_id id,last_name name,salary
from employees
where department_id=80
--基于多表创建视图
create view empview2
as
select employee_id id,last_name name,salary,department_name
from employees e,departments d
where d.department_id = e.department_id
--删除
delete from empview
where employee_id = 176
--修改视图
使用create or replace view 子句修改视图
create or replace view empview2
as
select employee_id id,last_name name,salary
from employees e,departments d
where d.department_id = e.department_id
create view 子句中各列的别名应和子查询中各列相对应
屏蔽DML操作
可以使用 WITH READ ONLY 选项屏蔽对视图的DML操作
任何 DML 操作都会返回一个Oracle server错误
create or replace view empview2
as
select employee_id id,last_name name,salary
from employees e,departments d
where d.department_id = e.department_id
with read only
简单视图和复杂视图
特性 简单视图 复杂视图
表的数量 一个 一个或多个
函数 没有 有
分组 没有 有
DML操作 可以 有时可以
--复杂视图
create or replace view empview3
as
select department_name dept_name,avg(salary) avg_sal
from employees e ,departments d
where e.department_id = d.department_id
group by department_name
视图中使用DML的规定
可以在简单视图中执行 DML 操作
当视图定义中包含以下元素之一时不能使用delete：
- 组函数
- GROUP BY子句
- DISTINCT 关键字
- ROWNUM 伪列
--删除视图
drop view empview;
Top-N分析
Top-N 分析查询一个列中最大或最小的 n 个值：
- 销售量最高的十种产品是什么？
- 销售量最差的十种产品是什么？
最大和最小的值的集合是 Top-N 分析所关心的
select rownum,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc
)
where rownum<=10
注意：
对 ROWNUM 只能使用 < 或 <=, 而用 =，>,>= 都将不能返回任何数据。
select rn,employee_id,last_name,salary
from (
select rownum rn,employee_id,last_name,salary
from (select employee_id,last_name,salary
from employees
order by salary desc
))
where rn<=50 and rn >40
练习：
--查询员工表中salary前10的员工信息。
/*
select rn,employee_id,last_name,salary from (
select rownum rn,employee_id,last_name,salary
from employees
order by salary desc)
where rownum <= 10
--查询员工表中 salary 10 ~ 20 的员工信息。
select rn,employee_id,last_name,salary from (
select rownum rn,employee_id,last_name,salary
from employees
order by salary desc)
where rn <= 20 and rn >= 10
--1.使用表 employees 创建视图 employee_vu,其中包括姓名（LAST_NAME）,员工号（EMPLOYEE_ID）,部门号(DEPARTMENT_ID)
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
--将视图中的数据限定在部门号是80的范围内
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80
--将视图改变成只读视图
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80
with read only
什么是序列
序列：可供多个用户用来产生唯一数值的数据库对象
- 自动提供唯一的数值
- 共享对象
- 主要用户提供主键值
- 将序列值装入内存可以提高访问效率
create sequence 语句
定义序列：
create sequence sequence_name
[INCREMENT BY n] --每次增长的数值
[START WITH n] --从哪个值开始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMAXVALUE}]
[{CYCLE | NOCYCLE}] --是否需要循环
[{CACHE n | NOCACHE}]; --是否缓存登录
create sequence empseq
increment by 10 --每次增长10
start with 10 --从10开始增长
maxvalue 100 --最大值100
cycle --需要循环
nocache --不需要缓存
修改序列的注意事项
- 必须是序列的拥有者或对序列有 alter 权限
- 只有将来的序列值会被改变
- 改变序列的初始值只能通过删除序列之后重建序列的方法实现
alter sequence empseq
increment by 1
nocycle;
--查看下一个序列
select empseq.nextval from dual;
--查看当前序列
select empseq.currval from dual;
使用序列
- 将序列值装入内存可提高访问效率
- 序列在下列情况下出现裂缝：
- 回滚
- 系统异常
- 多个表同时使用同一序列
- 如果不将序列的值装入内存(NOCACHE),可使用表 USER_SEQUENCES 查看序列当前的有效值
--查看用户中的序列
select sequence_name,min_value,max_value,
increment_by,last_number
from user_sequences;
--删除序列
drop sequence empseq;
索引
索引：
- 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
- 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
- 索引一旦建立，Oracle 管理系统会对其进行自动维护，而且由 Oracle 管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
- 在删除一个表时，所有基于该表的索引会自动被删除
- 通过指针加速 Oracle 服务器的查询速度
- 通过快速定位数据的方法，减少磁盘 I/O
创建索引
自动创建：在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
手动创建：用户可以在其它列上创建非唯一的索引，以加速查询。
--在一个或多个列上创建索引
create index index_name
on table (column[,column]... )
create index emp01_id_ix
on emp01(employee_id);
--在表 EMPLOYEES的列 LAST_NAME 上创建索引
CREATE INDEX emp_last_name_idx
ON employees(last_name);
--删除索引
drop index emp01_id_ix;
什么时候创建索引
以下情况可以创建索引：
- 列中数据值分布范围很广(存的数据很长)
- 列经常在 WHERE 子句或连接条件中出现
- 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%~4%
什么时候不要创建索引
下列情况不要创建索引：
- 表很小
- 列不经常作为连接条件或出现在WHERE子句中
- 查询的数据大于2%~4%
- 表经常更新
同义词-synonym
使用同义词访问相同的对象：
- 方便访问其它用户的对象
- 缩短对象名字的长度(即给数据库对象重命名)
CREATE [PUBLIC] SYNONYM synonym
FOR objext;
CREATE SYNONYM e FOR employees;
学习过程：
what-----why-----how
创建序列：
create sequence hs
increment by 10
start with 10
netvalue 应在 currval 之前指定，二者应同时有效
--1.创建序列dept_id_seq,开始值为200，每次增长10，最大值为10000
create sequence dept_id_seq
increment by 10
start with 200
maxvalue 10000
create table dept01 as
select department_id id,department_name name
from departments
where 1=2
--使用序列向表dept中插入数据
insert into dept01
values(dept_id_seq.nextval,'account')
通过本章学习，您将可以：
· 书写多例子查询
· 在 FROM 子句中使用子查询
· 在SQL中使用单例子查询
· 书写相关子查询
· 使用 EXISTS 和 NOT EXISTS 操作符
· 使用子查询更新和删除数据
· 使用 WITH 子句
子查询是嵌套在SQL语句中的另一个SELECT语句
SELECT .... --主查询（外查询）
FROM ...
WHERE ... (SELECT ...
FROM ...
WHERE ...) --子查询（内查询）
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table;)
· 子查询（内查询）在主查询执行之前执行
· 主查询（外查询）使用子查询的结果
一、多列子查询
Main query
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
Subquery
100 90
102 60
124 50
主查询与子查询返回的多个列进行比较
不成对比较举例
select employee_id,manager_id,department_id
from employees
where manager_id in
(select manager_id
from employees
where employee_id in (174,141))
and department_id in
(select department_id
from employees
where employee_id in (171,141))
成对比较举例
问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id
select employee_id,manager_id,department_id
from employees
where (manager_id,department_id) in
(select manager_id,department_id
from employees
where employee_id in (141,174))
and employee_id not in (141,174);
二、在FROM子句中使用子查询
问题：返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资
select last_name,department_id,salary,(select avg(salary) from employees e3
where e1.department_id = e3.department_id
group by department_id) avg_salaty
from employees e1
where salary > (select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id)
或
select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select department_id,avg(salary) avg_sal from employees group by department_id) e2
where e1.department_id = e2.department_id
and salary>e2.avg_sal
--显示员工的employee_id,last_name或location.
--其中，若员工department_id与location_id为1800的department_id相同，则location为'Canada',其余则为'USA'.
SELECT employee_id,last_name,
(case department_id when (select department_id from departments where location_id = 1800)then 'Canada'
else 'USA' end) location
from employees;
单列子查询应用举例
在 ORDER BY 子句中使用单列子查询
问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id,last_name
FROM employees e
ORDER BY (select department_name
from departments d
where e.department_id = d.department_id
);
四、相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
GET
-------->从主查询中过去候选列
| |
| EXECUTE
| 子查询使用主查询的数据
| |
| USE
|-------如果满足内查询的条件则返回该行
SELECT columnl,column2,....
FROM table1 outer
WHERE column1 operator
(SELECT colum1,column2
FROM table2
WHERE expr1 =
outer.expr2
);
子查询中使用主查询中的列
--若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
select employee_id,last_name,job_id
from employees e1
where 2 <= (
select count(1)
from job_history
where employee_id = e1.employee_id
)
五、EXISTS操作符
· EXISTS 操作符检查在子查询中是否存在满足条件的行
· 如果在子查询中存在满足条件的行：
- 不在子查询中继续找
- 条件返回TRUE
· 如果在子查询中不存在满足条件的行：
- 条件返回FALSE
- 继续在子查询中查找
--查询公司管理者的employee_id,last_name,job_id,department_id信息
select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
select manager_id
from employees e2
where e1.employee_id = e2.manager_id
)
或
select employee_id,last_name,job_id,department_id
from employees e1
where exists (
select 'A'
from employees e2
where e1.employee_id = e2.manager_id
)
NOT EXITSTS 操作符应用举例
--查询departments表中，不存在于employees表中的部门的department_id和department_name
select department_id,department_name
from departments d
where not exists(
select 'C'
from employees
where department_id = d.department_id
)
六、相关更新
update table1 alias1
set column = (select expression
from table2 alias2
where alias1.column =
alias2.column);
使用相关子查询依据一个表中的数据更新另一个表的数据
相关更新应用举例
1)
alter table employees
add(department_name varchar2(14));
2)
update employees e
set department_name =
(select department_name
from departments d
where e.department_id = d.department_id);
相关删除应用举例
--删除表employees中，其与emp_history表皆有的数据
delete from employees e
where employee_id =
(select employee_id
from emp_history
where employee_id = e.employee_id);
七、WITH子句
· 使用WITH子句，可以避免在SELECT语句中重复书写相同的语句块
· WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中
· 使用WITH子句可以提高查询效率
WITH子句应用举例
--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH
dept_costs AS (
select d.department_name,sum(e.salary) as dept_total
from employees e,departments d
where e.department_id = d.department_id
group by d.department_name
),
avg_cost as (
select sum(dept_total)/count(*) as dept_avg
from dept_costs
)
select *
from dept_costs
where dept_total >
(select dept_avg
from avg_cost)
order by department_name;
2022.3.30
练习题：
1.查询员工的last_name,department_id,salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可
select last_name,department_id,salary from employees where (salary,department_id) in (select salary,department_id from employees where commission_pct is not null)
2.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name,job_id,salary
select last_name,job_id,salary
from employees
where salary > all(select salary
from employees
where job_id = 'SA_MAN')
或
select last_name,job_id,salary
from employees
where salary > (select max(salary)
from employees
where job_id = 'SA_MAN')
3.选择所有没有管理者的员工的last_name
select last_name
from employees
where manager_id is null
或
select last_name
from employees e1
where not exists(select 'A'
from employees e2
where e1.manager_id=e2.employee_id)
PL/SQL语言
PL/SQL块
PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分
PL/SQL块的结构如下：
DECLARE
/*声明部分：在此声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数*/
BEGIN
/*执行部分：过程及SQL语句，即程序的主要部分*/
EXCEPTION
/*执行异常部分：错误处理*/
END;
其中执行部分是必须的。
set serveroutput on
最简单的PL/SQL
begin
dbms_output.put_line('Helloworld');
end;
declare
--声明变量
v_sal number(20);
begin
--sql语句的操作；select ... into ... from ... where ...;
select salary into v_sal from employees where employee_id = 100;
--打印
dbms_output.put_line(v_sal);
end;
declare
v_sal number(10,2);
v_email varchar2(20);
v_hire_date date;
begin
select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;
dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;
declare
v_sal employees.salary%type; --从源表表中获取数据类型
v_email employees.email%type;--从源表表中获取数据类型
v_hire_date employees.hire_date%type;--从源表表中获取数据类型
begin
--用into存入变量中
select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;
dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;
*/
浙公网安备 33010602011771号