oracle SQL常用语句

一、数据查询

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;