一、数据查询
1、数据查询基础基本语法:select [all|distinct] column_name[,expression...]
from table1_name[,table2_name,view_name,...]
[where condition]
[group by column_name1[,column_name2,...] [having group_condition]]
[order by column_name2 [asc|desc] [,column_name2,...]];
2、基本查询
2.1 无条件查询
(1)查询所有列
sql> select * from emp;
(2)查询指定列
sql> select deptno,dname from dept;
(3)使用算数表达式
sql> select empno,sal*0.8 from emp;
(4)使用字符常量
sql> select empno,'name is:',ename from emp;
(5)使用函数
sql> select empno,UPPER(ename) from emp;
(6)改变列标题
sql> select ename,employeename,sal salary from emp;
(7)使用连接字符串(用‘||’运算符将查询目标连接起来)
sql> select '员工号:'||empno||'员工号'||ename from emp;
(8)消除重复行(用参数distinct)
sql> select distinct deptno from emp;
2.2 有条件查询
(1)关系运算(=、>、<、>=、<=、<>、!=)
在where条件中可以使用关系运算表达式
sql> select empno,ename,sal from emp where deptno!=10;
sql> select empno,ename,sal from emp where sal>1500;
(2)确定范围(between and、not between and)
sql> select * from emp where deptno between 10 and 20;
sql> select * from emp where sal not between 1000 and 2000;
(3)确定集合(in、not in)
查询条件涉及多个等于或不等于运算,可使用in或not in运算符
sql> select empno,ename,sal from emp where deptno in(10,20);
(4)字符匹配(like、not like)
进行模糊查询时,在where中使用like或not like。oracle中使用两个
通配符,“%”代表0个或多个字符,“_”代表单个字符。
例1:查询名字中含有“s”的员工信息。
sql> select * from emp where ename like '%s%';
例2:查询名字第二个字母为“A”的员工信息。
sql> select * from emp where ename like '_A%';
如果查询信息本身含有‘%’或‘_’,可使用escape定义一个转义字符。
例3:查询名字中含有“_”字符的员工信息。
sql> select * from emp where ename like '%x_%' escape 'x';
(5)空值判断(is null、is not null)
判断表达式的结果是否为空
sql> select * from emp where deptno is null;
sql> select * from emp where deptno is not null;
(6)逻辑操作(not、and、or)
not的优先级最高,or的优先级最低。
例1:查询10号部门中工资高于1500的员工信息。
sql> select * from emp where deptno=10 and sal>1500;
例2:查询工资高于1500的10号部门和20号部门的员工信息。
sql> select * from emp where (deptno=10 or deptno=20) and sal>1500;
2.3 查询排序(可用 order by 对查询结果排序)
(1)升序、降序排序
asc:升序 desc:降序 默认是升序
sql> select empno,ename,sal from emp order by sal;
sql> select empno,ename,sal from emp order by sal desc;
(2)多列排序
首先按第一个列进行排序,当第一个列数据相同时,按第二列排序,
以此类推。
例:查询员工信息,按员工所在部门号升序、工资降序排序。
sql> select * from emp order by deptno,sal desc;
(3)按表达式排序
例:查询员工信息,并按员工年工资排序。
sql> select empno,ename,sal from emp order by sal*12;
(4)使用别名排序
sql> select empno,sal*12 salary from emp order by salary;
(5)使用列位置编号排序
使用第二列排序
sql> select empno,sal*12 salary from emp order by 2;
2.4 查询统计
使用聚集函数进行查询统计,常用聚集函数:
(1)count:count([distinct|all]*) 返回结果集中记录个数
(2)count:count([distinct|all]column) 返回结果集中非空记录个数
(3)avg:avg([distinct|all]column) 返回列或表达式的平均值
(4)max:max([distinct|all]column) 返回列或表达式的最大值
(5)min:min([distinct|all]column) 返回列或表达式的最小值
(6)sum:sum([distinct|all]column) 返回列或表达式的总和
(7)stddev:stddev(column) 返回列或表达式的标准差
(8)variance:variance(column) 返回列或表达式的方差
·聚集函数只能出现在列表达式、order by子句、having子句中,不能出现在where
子句和group by 子句中。
·默认对所有返回进行统计,包括重复行;不要重复可用distinct。
例1:统计10号部门员工的人数、平均工资、最高工资、最低工资。
sql> select count(*),avg(sal),max(sal),min(sal) from emp where deptno=10;
例2:统计所有员工的平均奖金和奖金总额。
sql> select avg(comm),sum(comm) from emp;
例3:从员工表中查询所有的部门个数。
sql> select count(distinct deptno) from emp;
例4:统计员工工资的方差和标准差。
sql> select variance(sal),stddev(sal) from emp;
3、分组查询
基本语法:select column,group_function,...
from table
[where condition]
[group [by roolup|cube|grouping sets] group_by_expression]
[having group_condition]
[order by column[asc|desc]];
3.1 单列分组查询
将'查询出来的记录'按照某一指定的列进行分组。
例如:查询每个部门的部门号、人数和平均工资。
sql> select deptno,count(*),avg(sal) from emp group by deptno;
查询结果:
deptno count(*) avg(sal)
-------------------------
10 4 2583.33
20 5 1543
30 6 1133.22
3.2 多列分组查询
例如:查询各个部门中不同工种的员工人数和平均工资。
sql> select deptno,job,count(*),avg(sal) from emp group by deptno,job;
查询结果:
deptno job count(*) avg(sal)
----------------------------------------
10 clerk 1 900
10 manager 1 2169
20 clerk 2 3322
20 manager 1 2339
20 salesman 4 2999
3.3 使用having子句限制返回组
例如:查询部门平均工资高于1500的部门号、部门人数和部门平均工资。
sql> select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)>1500;
3.4 使用rollup和cube选项
在group by中使用rollup:可以生成横向统计和不分组统计;
在group by中使用cube:可以生成横向统计、纵向统计和不分组统计;
3.5 合并分组查询
group by中使用grouping sets可实现合并分组查询。
例:查询各个部门的平均工资和各个工种的平均工资。
sql> select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
4、连接查询
从多个表或视图中查询数据。
交叉连接是多有连接的超集,外连接是内连接的超集。
4.1 交叉连接
sql> select empno,ename,sal,dname,loc from emp,dept;
4.2 内连接
根据指定的连接条件进行连接查询,只有满足连接条件的数据才会出现在结果集中。
(1)相等连接
使用“=”指定连接条件的连接查询。
(2)不相等连接
连接条件的运算符不是等号。
(3)自身连接
在同一个表或视图中连接,相当于同一个表作为两个或多个使用。
4.3 外连接
在内连接的基础上,将某个连接表中不符合连接条件的记录加入到结果集中。
(1)左外连接
(2)右外连接
(3)全外连接
5、子查询
嵌套在其他SQL语句中的select语句,也成为嵌套查询。
5.1 单行单列子查询
返回一行数据,且只返回一列
例:查询比7934号员工工资高的员工信息。
sql> select empno,ename,sal from emp where sal>(select sal from emp where empno=7934);
5.2 多行单列子查询
返回多行数据,且只返回一列
使用多行单列子查询时,需要使用多行比较运算符:
in 与子查询返回结果中任何一个值相等
not in 与子查询返回结果中任何一个值都不等
>any 比子查询返回结果中某一个值大
=any 与子查询返回结果中某一个值相等
<any 比子查询返回结果中某一个值小
>all 比子查询返回结果中所有值都大
<all 比子查询返回结果中所有值都小
exists 子查询至少返回一行时条件为true
not exists 子查询不返回任何一行时条件为true
例1:查询10号部门某个员工工资相等的员工信息。
sql> select empno,ename,sal from emp where sal in (select sal from emp where deptno=10);
例2:查询比10号部门所有员工工资高的员工信息。
sql> select empno,ename,sal from emp where sal>all (select sal from emp where deptno=10);
例3:查询比10号部门某个员工工资高的员工信息。
sql> select empno,ename,sal from emp where sal>any (select sal from emp where deptno=10);
5.3 单行多列子查询
返回一行数据,但包含多列数据。
例1:查询与7888员工的工资、工种都相同的员工信息。
sql> select empno,ename,sal,job from emp where (sal,job)= (select sal,job from emp where empno=7888);
例2:查询与10号部门某个员工工资相同,工种也与10号部门的某个员工相同的员工信息
sql> select empno,ename,sal,job from emp where sal in (select sal from emp where deptno=10) and
job in (select job from emp where deptno=10);
5.4 多行多列子查询
返回多行数据,且是多列数据。
例:查询与10号部门某个员工工资相同,工种也相同的员工信息。
sql> select empno,ename,sal,job from emp where (sal,job) in (select sal,job from emp where deptno=10);
5.5 相关子查询
子查询在执行时需要引用外部父查询的信息,这种查询称为相关子查询。
常使用exists或not exists实现。如果子查询返回结果,则条件为true,如果没有返回
结果,则条件为false。
例:查询没有任何员工的部门号、部门名。
sql> select deptno,dname,loc from dept where not exists(select * from emp where emp.deptno=dept.deptno);
5.6 在from子句中使用子查询
当在from子句中使用子查询时,该子查询被作为视图对待,必须为该子查询指定别名。
例:查询各个员工的员工号、员工名及其所在部门的平均工资。
sql> select empno,ename,d.avgsal from emp, (select deptno,avg(sal) avgsal from emp group by deptno) d where emp.deptno=d.deptno;
5.7 在DDL语句中使用子查询
可以在create table和create view语句中使用子查询来创建表和视图。
例:sql> create talbe emp_subquery
as
select empno,ename,sal from emp;
sql> create view emp_view_subquery
as
select * from emp where sal>2000;
6、合并查询
select query_statement1
[union|union all|intersect|minus]
select query_statement2;
·当要合并几个查询的结果集时,这几个查询的结果集必须具有相同的列数与数据类型。
·如果要对最终结果集排序,只能在最后一个查询之后用order by子句指明排序列。
6.1 union
例:查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的
员工号、员工名、工资和部门号。
sql> select empno,ename,sal,deptno from emp where deptno=10
union
select empno,ename,sal,deptno from emp where sal>2000
order by deptno;
·union相同的信息不重复出现,如果要保留所有重复记录,则使用union all。
6.2 intersect
获取几个查询结果集的交集。只会返回同时存在于几个查询结果集中的记录。
6.3 minus
获取几个查询结果集的差集。返回在第一个结果集中存在,在第二个结果集中不存在。
二、数据操纵
1、插入数据
·如果在into子句中没有指明任何列名,则values子句中列值得个数、顺序、类型必须与表中列
的个数、顺序、类型相匹配。
·如果在into子句中指定了列名,则values子句中提供的列值得个数、顺序、类型必须与指定的
列的个数、顺序、类型按位置对应。
·向表或视图中插入的数据必须满足表的完整性约束。
·字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入,或使用
TO_DATE函数进行日期转换。
1.1 插入单行记录
insert into table_name|view_name [(column1[,column2...])]
values(value1[,value2,...]);例1:向dept表中插入一行记录。
sql> insert into dept values(50,'IM','dalian');
例2:向emp表中插入一行记录。
sql> insert into emp(empno,ename,sal,hiredate) values(1234,'JOAN',2500,
'20-4月-2007');
1.2 利用子查询插入数据
insert into table_name|view_name (column1[,column2,...])subquery;
例1:统计各个部门的部门号、部门最高工资和最低工资,并将统计结果写入表emp_salary(假设该表已经创建)中。
sql> insert into emp_salary select deptno,max(sal),min(sal) from emp group by deptno;
例2:向emp表中插入一行记录,其员工名为FAN,员工号为123,其他信息与员工名为SCOTT的员工的信息相同。
sql> insert into emp select 123,'FAN',job,mgr,hiredate,sal,comm,deptno
from emp where ename='SCOTT';
·如果要将大量数据插入到表中,可以利用子查询直接装载的方式进行。由于直接装载
数据的操作过程不写入日志文件,因此数据插入操作的速度大大提高。当利用子查询
装载数据时,需要在insert into语句中使用/*+APPEND*/关键字,语法为:
insert /*+append*/ into table_name|view_name (column1 [,column2,...])] subquery;
例:复制emp表中的empno、ename、sal、deptno四列的值,并插入到new_emp表中。
sql> insert /*+append*/ into new_emp(empno,ename,sal,deptno)
select empno,ename,sal,deptno from emp;
1.3 向多个表中插入数据
insert all|first
when condition1 then into table1
when condition1 then into table1
...
else into tablen
subquery;
·all表示一条记录可以同时插入到多个满足条件的表中;
·first表示一条记录只会插入到第一个满足条件的表中;
例:将emp表中的员工信息按部门号的不同分别复制到emp10、emp20、emp30和emp_other表中(假设表已经被创建)。
sql> insert first
when deptno=10 then into emp10
when deptno=20 then into emp20
when deptno=30 then into emp30
else into emp_other
select * from emp;
2、修改数据
update table_name|view_name
set column1=value1[,column2=value2...]
[where condition];
例1:将员工号为7899的员工工资增加100,奖金修改为200。
sql> update emp set sal=sal+100,comm=200 where empno=7899;
例2:将30号部门的员工工资设置为10号部门平均工资加300。
sql> update emp set sal=300+(select avg(sal) from emp where deptno=10) where
deptno=30;
3、删除数据
delete from table|view [where condition];
例1:删除员工号为7844的员工信息。
sql> delete from emp where empno=7844;
例2:删除10号部门所有员工的信息。
sql> delete from emp where deptno=10;
例3:删除比员工号为7900的员工工资高的员工信息。
sql> delete from emp where sal>(select sal from emp where empno=7900);
·利用delete删除数据,实际上是将数据标记为unused,并不释放空间,同时将操作过程写入
日志文件,因此delete操作可以进行回滚。但是,如果要删除的数据量非常大,则delete
操作效率较低。这时可以使用truncate语句,执行该语句时释放存储空间,而且不写入日志文件,因此执行效率高,但不可回滚。
删除表中数据:
truncate table table_name;