您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码:  验证码,看不清楚?请点击刷新验证码 必填



  求知 文章 文库 Lib 视频 Code iProcess 课程 角色 咨询 工具 火云堂 讲座吧   建模者  
会员   
 
   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
     
   
 订阅
  捐助
SQL语句基础用法大全(DML)
 
90 次浏览     评价:  
 2019-5-17
   
   
 
编辑推荐:
本文来自于csdn,本文主要介绍了sql语句中的、事务处理、子查询、多表查询等相关的知识,希望对您能有所帮助。

sql 结构查询语言,分以下几组:

DML(Data Manipulation Language,数据操作语言),用于检索或者更新数据

DDL(Data Definition Language,数据定义语言),用于定义数据的结构,如创建,修改或者删除数据库对象

DCL(Data Control Language,数据控制语言),定义数据库用户的权限

查看当前数据库的名称和状态

SQL> select instance_name,status from v$instance;

查看当前的open状态

SQL> select open_mode from v$database;

创建用户

SQL> create user c##scott identified by oracle;

给权限

SQL> grant connect,resource to c##scott;
SQL> alter user c##scott quota unlimited on users;

使用scott用户连接

SQL> conn c##scott/oracle

执行脚本,,,

SQL> @/home/oracle/scott.sql

*我在这用的是oracle12c,oracle11g可直接解锁scott用户来练习SQL语句*

用sys用户解锁并给密码:

alter user scott account unclock;
alter user scott identified by oracle;

查看当前用户

SQL> show user;

查看当前用户有哪些表

SQL> select table_name from user_tables;

查看用户的表

SQL> select * from tab;

查看emp表的表结构

SQL> desc emp

列出dept表的所有列

SQL> select * from dept;

查看指定列

SQL> select deptno,loc from dept;

特性:

字母左对齐,数字右对齐

SQL> select ename,sal,job,hiredate from emp;

支持运算符

SQL> select ename,sal,job,sal+100 from emp;

运算符优先级

SQL> select ename,sal,job,(sal+100)*12 from emp;

查看空值

SQL> select ename,job,sal,comm from emp;

列别名,可加双引号保持原样

SQL> select ename,sal,sal+100 as newsal from emp;

连接运算符

SQL> select ename||sal from emp;

使用字面字符串

SQL> select ename||”’s sal is ‘||sal from emp;
SQL> select ename||q’[’s sal is ]’ ||sal from emp;!

去除重复的行

select DISTINCT job from emp;

描述,显示表结构

desc emp;

在sqlplus中执行操作系统命令

SQL> !clear
SQL> host clear

简单查询

语法:

SELECT [DISTINCT]*| 字段 [别名],[字段 [别名]]

FROM 表名称 [表别名]

查询dept表的全部记录

select * from dept;

查询每个雇员的编号,姓名和基本工资

select job from emp;

查询每个雇员的职位

select distinct job from emp;

注意:查询出来的job内容有重复数据,使用distinct删除重复

select distinct job from emp;

查询每个雇员的姓名,职位

select distinct ename,job from emp;

简单查询中,可以使用四则运算符

查询每一个雇员的姓名,职位和基本年薪

select ename,job,sal*12 from emp;

注意:列的名称不方便浏览,可以使用列别名

select ename,job,sal*12 income from emp;
select ename,job,sal*12 as income from emp;

每个月每个人有200的饭补和100的车补,计算年薪

select ename,job,(sal+300)*12 income from emp;

年底多发一个月的基本工资

select ename,job,(sal+300)*12+sal income from emp;

使用||连接符

select empno||’,’||ename from emp;

 

限定查询

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [表别名]

[WHERE 条件(s)]

条件:

,>=,<,<=,!=(<>),

BETWEEN…AND…,LIKE,IN,IS NULL,AND,OR,NOT

1、关系运算

要求查询出基本工资高于1500的所有雇员信息

select * from emp where sal>1500;

查询出所有职位是办事员的雇员信息

select * from emp where job=’CLERK’;

注意:在oracle数据库,数据区分大小写

查询工资在1500-3000之间的全部雇员信息

select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;

查询职位是办事员,或者是销售员的全部信息

select * from emp where job=’CLERK’ or job=’SALESMAN’;

查询职位是办事员,或者是销售员的全部信息,并要求这些雇员的工资大于1200

select * from emp
where (job=’CLERK’ or job=’SALESMAN’) and sal>1200;

查询所有不是办事员的雇员信息

select * from emp where job<>’CLERK’;
select * from emp where job!=’CLERK’;
select * from emp where NOT job=’CLERK’;

 

2、范围判断

BETWEEN 最小值 AND 最大值

查询基本工资在1500-3000的雇员信息

select * from emp where sal between 1500 and 3000;

求反

select * from emp where not sal between 1500 and 3000;

3、判断是否为空

IS(NOT) NULL,空值不是数字0或者空字符串

查询出所有领取奖金的雇员信息

select * from emp where comm is not null;
select * from emp where not comm is null;

查询所有不领取奖金的雇员

select * from emp where comm is null;

4、指定范围的判断

IN操作符表示指定一个范围

查询雇员编号是7369,7566,7799的雇员信息

select * from emp
where empno=7369 or empno=7566 or empno=7799;

使用IN

select * from emp where empno in (7369,7566,7799);

使用NOT IN表示不在指定范围内

select * from emp where empno not in (7369,7566,7799);

注意:关于NOT IN的问题

如果使用IN操作符,查询的范围之中存在null,不影响查询

select * from emp where empno in (7369,7566,null);

如果使用NOT IN操作符,如果查询范围中有null,则不会有任何查询结果

select * from emp where empno not in (7369,7566,null);

如果NOT IN中出现了null,则表示查询全部数据

为什么呢?

5、模糊查询

LIKE子句

_: 匹配单个字符

%: 匹配任意多个字符

查询雇员姓名中以字母A开头的全部雇员信息

select * from emp where ename like ‘A%’;

查询雇员姓名中第二个字母是A的全部雇员信息

select * from emp where ename not like ‘%A%’;

求反

Hello World!select * from emp where ename not like ‘%A%’;

%%表示查询全部信息

数据排序

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [别名]

[WHERE 条件(S)]

[ORDER BY 字段 [ASC|DESC][字段 [ASC|DESC],…]]

说明:

该子句在所有的SQL语句最后

可以指定多个排序的字段

默认升序

降序需要手工指定

有需要的时候才进行排序

查询所有雇员的信息,按工资排序

select * from emp order by sal;
select * from emp order by sal asc;

降序排列

select * from emp order by sal desc;

查询所有雇员信息,按照工资降序排列,工资相同,则按雇佣日期从早到晚排列

select * from emp order by sal desc,hiredate asc;

单行函数

分类:

字符函数

数字函数

日期函数

转换函数

通用函数

字符函数:主要是进行字符串数据的操作

UPPER(字符串|列) 将输入的字符串变为大写返回

LOWER(字符串|列) 将输入的字符串变为小写返回

INITCAP(字符串|列) 开头首字母大写

LENGTH(字符串|列) 求出字符串长度

REPLACE(字符串|列) 进行替换

SUBSTR(字符串|列) 开始点[结束点],字符串截取

oracle提供一个虚拟表dual

转大写

select upper(‘hello’) from dual;
select * from emp where ename=upper(‘&str’);

转小写

将所有雇员姓名按照小写字母返回

select lower(ename) from emp;

将每个雇员姓名的开头首字母大写

select initcap(ename) from emp;

查询出每个雇员姓名的长度

select ename,length(ename) from emp;

查询出雇员姓名长度刚好是5的雇员信息

select ename,length(ename) from emp where length(ename)=5;

使用字符“_”替换雇员姓名中的所有字母“A”

select replace(ename,’A’,’_’) from emp;

字符串截取有两种语法:

1 SUBSTR(字符串|列,开始点),表示从开始点一直截取到结尾

select ename,substr(ename,3) from emp;

2 SUBSTR(字符串|列,开始点,截取多少位),表示从开始点截取多少位

select ename,substr(ename,0,3) from emp;
select ename,substr(ename,1,3) from emp;

要求截取每个雇员名字的后三个字母

通过长度-2确定开始点

select ename,substr(ename,length(ename)-2) from emp;

设置负数,表示从后指定截取位置

select ename,substr(ename,-3) from emp;

数字函数

ROUND(数字|列[,保留小数的范围]):四舍五入的操作

TRUNC(数字|列[,保留小数的范围]): 舍弃指定位置的内容

MOD(数字1,数字2):取模,取余数

ROUND

select round(903.5) from dual;
select round(903.53567) from dual;
select round(903.53567),round(-903.53567) from dual;
select round(903.53567),round(-903.53567),
round(903.53567,-1) from dual;
select round(903.53567),round(-903.53567),round(903.53567,-1),
round(903.53567,2) from dual;

 

TRUNC trunc

select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),
trunc(903.53567,2) from dual;

MOD

select mod(3,10) from dual;

日期函数

取得今天的日期,可以使用“SYSDATE”

select sysdate from dual;

 

日期的计算有以下几种计算:

日期+数字=日期,表示若干天之后的日期

日期-数字=日期,表示若干天之前的日期

日期-日期=数字,表示两个日期之间的天数,但是必须大日期减小日期

求出每个雇员到今天为止的雇佣天数

select ename,hiredate,sysdate-hiredate from emp;

日期除了以上的三个计算公式,还有四个操作函数

LAST_DAY(日期):求出指定日期当月的最后一天
#求出本月的最后一天日期
select last_day(sysdate) from dual;
NEXT_DAY(日期,星期数):求出下一个指定星期几的日期
#下一个周六是几号
select next_day(sysdate,'sat') from dual;
#下一个周日是几号
select next_day(sysdate,'sun') from dual;
ADD_MONTHS(日期,数字):求出若干月之后的日期
#四个月后是几号
select add_months(sysdate,4) from dual;
MOUNTHS_BETWEEN(日期1,日期2):求出两个日期之间所经历的月份
#求出每个雇员到今天为止的雇佣月份
select ename,hiredate,trunc(months_between(sysdate,hiredate)) from emp;

注意:在所有开发之中,如果是日期的操作,建议使用以上的函数,可以避免闰年的问题。

转换函数

之前我们学习了字符函数,数字函数和日期函数,而转换函数的主要功能是完成这几种数据之间的互相转换的操作,一共有三种转换函数。

TO_CHAR(字符串|列,格式字符串):将日期或者数字变为字符串显示;

TO_DATE(字符串,格式字符串):将字符串转变为DATE数据显示;

TO_NUMBER(字符串):将字符串变为数字显示.

TO_CHAR()函数

格式:年(yyyy),月(mm),日(dd)

#拆分年月日
select to_char(sysdate,’yyyy-mm-dd’),to_char(sysdate,’yyyy’) year,to_char(sysdate,’mm’) month,to_char(sysdate,’dd’) day from dual;

#干掉日期中的前导零
select to_char(sysdate,'fmyyyy-mm-dd') day from dual;

 

在oracle之中,sysdate函数中是包含了时间的,要显示时间需要增加标记

#使用sysdate显示时间
select to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) haha from dual;
select to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual;
#使用24小时制
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual; MI

 

使用TO_CHAR函数之后,所有内容为字符串,不再是date型数据,TO_CHAR同样可以用在数字上用来格式化数字

#格式化数字22222222222;
select to_char(22222222222,’999,999,999,999,999,999,999’) shuzi from dual;

这时,格式化字符串的数字9表示格式,而不是数字9。

#格式化数字为货币记录格式,使用“L”标记表示转换为当前语言环境下的货币符号
select to_char(22222222222,'L999,999,999,999,999,999,999') huobi from dual;

TO_DATE(): (熟悉用法)

使用方法如下;

select to_date(‘1989-09-12’,’yyyy-mm-dd’) from dual;

TO_NUMBER(): (基本不用,会用即可)

使用方法如下;

select to_date(‘1989-09-12’,’yyyy-mm-dd’) from dual;

在oracle中,不使用该函数也可以完成该功能,如下:

select ‘2’+’3’ from dual;

通用函数

通用函数主要有NVL(),DECODE()两个,这两个函数算是oracle的特色函数。

NVL(): 处理null

#要求查询所有雇员的全部年薪
select ename,sal,comm,(sal+comm)*12 from emp;

查询的结果出现问题,解决办法是将comm中的null值变成0,方法如下:

#查看转换过程
select ename,sal,comm,nvl(comm,0) from emp;
#解决问题
select ename,sal,comm,(sal+nvl(comm,0))*12 from emp;

DECODE():多数值判断

判断的是数值而不是条件,类似编程中的if…else…

说明:这种判断肯定是进行逐行判断,所以这个时候就必须采用DECODE(),而此函数的用法如下:

DECODE(数值|列,判断值1,显示值1,判断值2,显示值2,…)

 

#例如:要求显示全部雇员的职位,但是这些职位要求显示成pinyin。
CLERK: 办事员
SALESMAN: 销售员
MANAGER: 经理
ANALYST: 分析员
PRESIDENT: 总裁
#实现:
select empno,ename,job,decode(job,
'CLERK','banshiyuan',
'SALESMAN','xiaoshouyuan',
'MANAGER','jingli',
'ANALYST','fenxiyuan',
'PRESIDENT','zongcai')
from emp;

总结:

1:以上列出的SQL语句只是最基本的语法,明白SELECT,FROM,WHERE,ORDER BY之间的关系;

2:熟悉SCOTT用户的4张表的内容,包括列的名称,作用和类型;

3:精通以上所有SQL语句和函数的使用。

多表查询

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]

FROM 表名称 [别名],[表名称 [别名],表名称 [别名]…]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

注意:多表查询之前,首先必须查询各个表中的数据量,这个操作可以通过COUNT()函数来完成。切记!切记!切记!

注意:在遇到一个新的数据库,有两种做法:

1、直接执行以下命令

select * from 表名称

导致的结果一是无法浏览数据,二是可能造成死机,如:

介绍:

sh用户是oracle数据库示例方案的用户,该示例方案是大数据方案

做法:

1、给sh用户解锁和改密码

alter user sh identified by sh account unlock;

2、使用sh用户登录

conn sh/sh

3、查询有多少表

select * from tab;

4、查询sales表数据

select * from sales;

5、等待,然后没有然后了

2、先查询数据量

select count(*) from 表名称

=====

1、select count(*) from sales;

结论:遇到新数据库首先查询的是数据量,而不是直接select * from 表名称。

查询emp表中的数据量

select count(*) from emp;

确认数据量后,执行多表查询语句:

select * from emp,dept;

语句语法没有问题,但是因为数据库的机制导致产生了笛卡儿积,所以具体用法不这么用。

如何去掉笛卡儿积呢?

采用关联字段的形式,emp表和dept表之间存在了deptno的关联字段,从关联字段下手,如下:

select * from emp,dept where deptno=deptno; #执行错误
select * from emp,dept where emp.deptno=dept.deptno; #显示上笛卡儿积已经消除,实际上笛卡儿积还在,并没有消除。

举例:

1、使用sh大用户账号连接

conn sh/sh

2、查询sales表的数据量

select count(*) from sales;

3、查询还有其他的哪些表?

select * from tab;

4、查询costs表的数据量

select count(*) from costs;

5、查询sales表的表结构

desc sales;

6、查询costs表的表结构

desc costs;

7、根据查询结果发现PROD_ID可以作为关联字段进行查询

select count(*) from sales,costs where sales.prod_id=costs.prod_id;

结论:这两张表即便消除了笛卡儿积的显示,但是本身还存在笛卡儿积的问题,所以多表查询的性能是很差的,当然也主要看数据量。

如果表的名称比较长,那么这样的方式很不方便使用,解决办法就是使用表别名,如下:

select * from emp e,dept d where e.deptno=d.deptno;

要求查询每一位雇员的编号,姓名,职位,部门名称,部门位置

思路:

1、首先确认需要的表;

emp表可以查询雇员的编号,姓名,职位;

dept表可以查询部门名称和位置;

2、确定表的关联字段;

emp.deptno=dept.deptno

做法:

1、查询出每一位雇员的编号,姓名和职位;

select e.empno,e.ename,e.job from emp e;

2、为查询中引入部门表,同时增加消除笛卡儿积的条件;

select e.empno,e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;

要求查询出每一位雇员的姓名,职位和领导姓名

思路:

1、确认需要的表;

emp表可以查询雇员的姓名,职位和领导编号

emp表可以查询领导的姓名

2、确定关联字段;

emp.mgr=emp.empno,雇员的领导编号=领导的雇员编号

做法:

1、查询每一位雇员的姓名和职位

select e.ename,e.job from emp e;

2、查询领导信息,加入自身关联

select e.ename,e.job,m.ename from emp e,emp m
where e.mgr=m.empno;

注意:查询结果少了一行,缺少KING的领导姓名,因为他没有领导,后面左右链接解释。

要求查询出每个雇员的编号,姓名,基本工资,职位,领导的姓名,部门名称及位置

思路:

1、确认需要的表;

emp表查询每个雇员的编号,姓名,基本工资,职位

emp表查询领导的姓名

dept表查询部门的名称及位置

2、确定已知的关联字段

雇员和部门: e.deptno=d.deptno

雇员和领导: e.mgr=m.empno

做法:

1、查询出每个雇员的编号,姓名,基本工资,职位

select empno,ename,sal,job from emp;

2、加入领导的信息,引入自身关联,同时增加消除笛卡儿积的条件

select e.empno,e.ename,e.sal,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;

3、加入部门的信息,引入dept表,有新表,则要继续加入消除笛卡儿积的条件

select e.empno,e.ename,e.sal,e.job,m.ename,d.dname,d.loc
from emp e,emp m,dept d
where e.mgr=m.empno
and e.deptno=d.deptno;

思考练习题一:

要求查询出每一个雇员的编号,姓名,工资,领导的姓名,部门名称及位置,工资所在公司的工资等级

select e.empno,e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

左、右连接

左右连接可以改变查询判断条件的参考方向,例如,有以下查询

select * from emp e,dept d where e.deptno=d.deptno;

注意:一共有四个部门,但是这里只有三个,缺少40部门的部门信息,原因就是现在的查询以emp表为参考进行查询,要想显示40部门,

就必须改变参考的方向,这时就需要左右连接

用法:

select * from emp e,dept d where e.deptno(+)=d.deptno;

(+)用于左右连接的更改,这种符号有以下两种使用情况:

(+)=:表示右连接

=(+):表示左连接

不用刻意区分左还是右,根据查询结果来定,如果发现有些需要的数据没有显示出来,就使用此符号来改变连接方向,该符号为oracle独有。

如之前的查询领导姓名的范例:

select e.ename,e.job,m.ename from emp e,emp m
where e.mgr=m.empno(+);

补充:

查询字段时,需要多表关联,需要用到表别名,内连接,外连接,自然连接,自连接

一些关键连接方法。

1.表别名

查询员工编号 员工姓名 部门名称

select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;

2.内连接 A(inner) join B on 连接条件

select e.empno,e.ename,d.dname
from emp e inner join dept d
on e.deptno=d.deptno;

内连接必须满足连接条件,不满足的信息无法显示

3.外连接

1》左外连接

关键字 A left join B on 连接条件

首先添加一条没有部门的员工信息

insert into myemp(empno,ename,job) values(1100,’rose’,’artist’);

emp表里面rose没有员工编号,dept表不能找到rose的部门信息

select e.empno,e.ename,d.dname
from myemp e left join dept d
on e.deptno=d.deptno;

条件查询 = (+) 左连接

select e.empno,e.ename,d.dname
from myemp e,dept d
where e.deptno=d.deptno(+);

2》右外连接

关键字 A right join B on 连接条件

dept表的40号部门没有员工信息,使用内连接和条件查询都无法显示,

我们用右连接使40号部门显示出来。

select e.empno,e.ename,d.dname
from emp e right join dept d
on e.deptno=d.deptno;

条件查询左右连接

(+)= 右连接

= (+) 左连接

select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);

3》完全外连接 full join

select e.empno,e.ename,d.dname
from myemp e full join dept d
on e.deptno=d.deptno;

4》自然连接 natural join

不用区分关联条件,oracle自动为我们区分

查询工资大于2000的员工编号,员工姓名和部门名称。

select empno,ename,dname from emp natural join dept where sal>2000;

缺点

如果表名不确定,不能查询

5》自连接

查询员工姓名,员工职称以及员工所属领导。

select e.ename,e.job,m.ename
from emp e left join emp m
on e.mgr=m.empno;

条件查询

select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;

统计函数

分类:

COUNT():查询表中的数据记录

AVG(): 求出平均值

SUM(): 求和

MAX(): 求出最大值

MIN(): 求出最小值

统计出公司的所有雇员每个月支付的平均工资及总工资

select count(empno),sum(sal),avg(sal) from emp;

统计雇员中的最高和最低工资

select max(sal),min(sal) from emp;

注意:

COUNT()函数的主要功能是进行数据的统计,但是在进行数据统计的时候,如果一张表中没有统计记录,那么COUNT()也会返回数据,只是这个数据是“0”.

如:select count(ename) from bonus; #返回数字0

select sum(sal) from bonus; #返回NULL

如果使用其他函数,则有可能返回NULL值,但是COUNT()永远都会返回一个具体的数字,记住了,记住了,记住了。

分组统计

什么情况下需要分组统计?

1、男的分一组,女的分一组

2、年龄分组,成年和未成年

3、地区分组,上海和北京,

这些信息如果都保存在数据库中,肯定在数据库的某一列上存在重复数据,例如按照性别分组的时候,有男和女,按照年龄分组,有一个范围的重复,按照地区的话有一个地区的信息重复

注意:当数据重复的时候分组才有意义,一个人也可以单独分一组,但是么有意义。

如果需要分组,可以使用GROUP BY子句,语法如下:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

按照部门编号分组,求出每个部门的人数和平均工资

select deptno,count(empno),avg(sal) from emp
group by deptno;

按照职位分组,求出每个职位的最高和最低工资

select job,max(sal),min(sal) from emp
group by job;

注意:分组函数有以下要求:

1、分组函数可以在没有分组的时候单独使用,可是不能出现其他的查询字段,如:

正确使用:select count(empno) from emp;

错误使用,出现其他字段:select empno,count(empno) from emp;

2、如果要进行分组,则select子句之后,只能出现分组的字段和统计函数,其他字段不能出现,如:

正确使用:select job,count(empno),avg(sal) from emp group by job;

错误用法:select empno,job,count(empno),avg(sal) from emp group by job;

3、分组函数允许嵌套,但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段,如:

#按照职位分组,统计平均工资最高的工资

先统计出各个职位的平均工资

select job,avg(sal)
from emp
group by job;

查询平均工资最高的工资

错误使用:select job,max(avg(sal)) from emp group by job;

正确使用:select max(avg(sal)) from emp group by job;

查询出每个部门的名称,部门的人数,平均工资

思路:

1、确定所需要的数据表

dept表:每个部门的名称

emp表: 统计出部门的人数,平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno

做法:

1、将dept表和emp表的数据关联

select d.dname,e.ename,e.sal
from dept d,emp e
where d.deptno=e.deptno;

2、查看上面语句执行的结果,有重复值,就可以进行分组查询,但是此时与之前的分组不太一样,之前的分组是针对一张实体表,

但是对于以上的数据是通过查询结果显示的,所以是一张临时的虚拟表,但是不管是实体表和虚拟表,只要是有重复,那么就直接进行分组

select d.dname,count(e.empno),avg(e.sal) from dept d,emp e
where d.deptno=e.deptno
group by d.dname;

通过结果可以看到,这个分组并不合适,部门一共有四个,因为已经引入了dept表,该表存在了四个部门,所以应该通过左右连接来改变查询的结果

select d.dname,count(e.empno),avg(e.sal) from dept d,emp e
where d.deptno=e.deptno(+)
group by d.dname;

查询结果发现OPERATIONS部门的平均工资为NULL值,并不符合结果预期,所以可以通过NVL函数来进行处理,如下:

select d.dname,count(e.empno),trunc(nvl(avg(e.sal),0)) avg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.dname;

多字段分组

要求显示每个部门的编号,名称,位置,部门的人数和平均工资

思路:

1、确定所需要的数据表

dept表:每个部门的编号,名称,位置

emp表: 统计出部门的人数,平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno

做法:

1、将emp表和dept表关联

select d.deptno,d.dname,d.loc,e.empno,e.sal
from dept d,emp e
where d.deptno=e.deptno(+);

2、查看以上语句执行的结果,发现有三个列存在重复数据(deptno,dname,loc),所以在分组上的GROUP BY子句中就可以写上三个字段:

select d.deptno,d.dname,d.loc,count(e.empno),
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc;

以上就是分组查询,不管是单字段还是多字段,一定要有一个前提就是存在了重复数据。

在上个范例的基础上,要求统计出每个部门的详细信息,并且要求这些部门的平均工资高于2000

使用where子句

select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+)
and avg(e.sal)>2000
group by d.deptno,d.dname,d.loc;

该语句会报错,如下:

where d.deptno=e.deptno(+) and avg(e.sal)>2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here

意思是说在where子句中不能使用统计函数,这和where子句的功能有关。

如果要对分组后的数据再次进行过滤,需要使用HAVING子句,语法格式如下:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用分组函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

下面使用HAVING进行过滤

select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
having avg(sal)>2000;

注意:WHERE和HAVING的区别

WHERE:在执行GROUP BY操作之前进行的过滤,表示从全部数据中进行过滤,不能使用统计函数;

HAVING: 在GROUP BY分组之后的再次过滤,可以使用统计函数。

思考练习题二:

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于5000,输出结果按照月工资的合计升序排列。

子查询(核心重点)

子查询=简单查询+限定查询+多表查询+统计查询的综合体

在之前说多表查询不建议使用,因为性能差,但是多表查询最有利的替代者就是子查询,在实际的开发中使用最多的就是子查询。

语法:

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数,(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

FROM 表名称 [别名],[表名称 [别名],…],(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[WHERE 条件(s)](

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用统计函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

WHERE:子查询一般只返回单行单列,多行单列,单行多列的数据

FROM:子查询返回的一般是多行多列的数据,当作一张临时表出现。

要求查出工资比SMITH还要高的全部信息

思路:

1、首先要知道SMITH的工资是多少

select sal from emp where ename=’SMITH’;

2、由于此时返回的是单行单列的数据,所以这个子查询可以在WHERE中出现

select * from emp where sal>(
select sal
from emp
where ename=’SMITH’);

要求查询出高于公司平均工资的全部雇员信息

思路:

1、公司的平均工资是多少?

select avg(sal) from emp;

2、由于此时返回的是单行单列的数据,在WHERE之中出现

select * from emp where sal>(
select avg(sal)
from emp);

以上返回的是单行单列,但是在子查询中,也可以返回单行多列的数据,只是这种子查询很少出现,如:

select * from emp where(job,sal)=(
select job,sal
from emp
where ename=’ALLEN’);

如果子查询返回的是多行单列数据的话,这个时候就需要使用三种判断符来进行判断:IN,ANY,ALL

1、IN操作符:用于指定一个子查询的判断范围

 

select * from emp
where sal in(
select sal
from emp
where job=’MANAGER’);

在使用IN的时候还要注意NOT IN的问题,如果使用NOT IN操作,在子查询中,如果有一个内容是NULL,则不会有任何查询结果,

因为如果有NULL,则会查询所有数据,如果数据量太大就会导致有漏洞产生,所以加入限制。

select * from emp
where sal not in(
select sal
from emp
where job=’MANAGER’);

2、ANY操作符,与每一个内容相匹配,有三种匹配形式

1、=ANY:功能与IN操作符是完全一样;

select * from emp
where sal=any(
select sal
from emp
where job=’MANAGER’);

2、>ANY:比子查询中返回记录最小的还要大的数据

select * from emp
where sal>any(
select sal
from emp
where job=’MANAGER’);

3、

查询出每个部门的编号,名称,位置和部门人数,平均工资

之前使用的是多字段分组统计完成的,如:

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;

这时候实际产生了笛卡儿积,共56条记录。

下面使用子查询来完成,所有的统计查询只能在GROUP BY中出现,所以在子查询之中负责统计数据,而在外部的查询之中,负责将统计数据和dept表数据相统一。

select d.deptno,d.dname,d.loc,nvl(temp.count,0),nvl(temp.avg,0)
from dept d,(
select deptno dno,count(empno) count,avg(sal) avg
from emp
group by deptno) temp
where d.deptno=temp.dno(+);

这个子查询和之前的两种方法的比较:

1、子查询中统计的记录是14条,最终统计的显示结果是3条结果

2、dept表之中一共有4条记录

3、如果现在产生笛卡儿积的话只有12条,在加上雇员的14条,一共才26条

在开发中,使用子查询可以提高效率和节省性能,大部分情况下:如果最终查询结果中出现了select语句,但是又不能直接使用统计函数时,就在子查询中统计信息。

数据更新(重点)

增删改 查

数据更新包括数据的增加、修改、删除。

为了做实验,我们将emp表复制一份,使用以下指令:

create table myemp as select * from emp;

这种语法是oracle支持的,其他的数据库不一样

数据的增加

语法:

INSERT INTO 表名称 [(字段1,字段2,字段3,…)] VALUES(值1,值2,值3,…)

注意:如果需要进行增加数据的话,则以下的几种数据类型要分别处理:

增加数字:直接编写数字,如:123;

增加字符串:字符串应该使用”’”声明;

增加DATE数据:

第一种:可以按照已有的字符串的格式编写字符串,如:‘20-6月-06’;

第二种:利用TO_DATE函数将字符串变为DATE型数据;

第三种:如果设置的时间为当前系统时间,则使用SYSDATE;

对于数据的增加有两种操作格式:

完整型:

1.

insert into myemp (empno,ename,hiredate,sal,mgr,job,comm)
values (8888,’zhangsan’,TO_DATE(‘1960-08-17’,’yyyy-mm-dd’),8000,7369,’daza’,1000);

2.

insert into myemp (empno,ename,hiredate,sal,mgr,job,deptno)
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);

简便型:不写列名称

错误:

insert into myemp
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);

正确:需要按照列名称的顺序来写,必须符合字段的要求,一般开发中不使用

insert into myemp values (8890,’wangwu’,’daza’,7369,sysdate,3000,null,30);

数据的修改

语法:

UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,…[WHERE 更新条件(s)];

示例;

更新员工编号为7369的员工的工资为5000,奖金为2000,职位升级为’MANAGER’,职位更新时间为当前时间

update myemp set sal=5000,comm=2000,job=’MANAGER’,hiredate=sysdate where empno=7369;

所有员工的工资统一改为7500

update myemp set sal=7500;

注意:如果更新的时候不加上更新条件,则意味着更新全部数据,一定要注意,当数据量达到一定程度时,这种方式不可取。

为了后面做实验,我们需要回滚数据表

SQL> ROLLBACK;

数据的删除

语法:

DELETE FROM 表名称 [WHERE 删除条件(s)];

示例:

删除1987年入职的员工的信息

delete from myemp where TO_CHAR(hiredate,’yyyy’)=1987;

注意:如果删除的时候没有相应匹配条件的时候,则更新记录为0,更新操作也一样。

删除表中的所有数据

 

delete from myemp;

注意:对于删除操作,尽可能少使用,因为删除操作对于查询操作要危险许多。

提示:对于删除操作,在开发时对于所有的删除操作之前先给出一个提示框,以防止误删除。

事务处理

对于数据表的操作,查询要比更新操作更安全,因为更新操作有可能会出现错误,导致没有按照既定的要求正确的完成更新操作。

在很多时候更新可能由多条语句共同完成,如银行转账:

-判断A的账户上是否有5000W select yue+shouxufei>5000+sxf from zhanghu where id=a

-判断B的账户状态是否正常 select id,status from zhanghu where id=b

-从A的账户上移走5000W update zhanghu set yue-5000 where id=a

-向B的账户上增加5000W update zhanghu set yue+5000 where id=b

-向银行支付手续费5W update zhanghu set yue+shouxufei where id=yinhang

以上五个数据操作是一个整体,可以理解为一个完整的业务,如果其中第三点出错,其他操作该怎么办?

如果有操作出现错误,那么其他操作应该不再继续执行,并且都回归到最原始的状态,而这一个流程的操作实际上就是事务的操作。

 

回滚之前的操作

rollback;

再次查看myemp表,确认其中的数据情况

select * from myemp;

所有的事务处理都是针对每一个会话进行的,在oracle中,把每一个连接到数据库的用户都称为一个会话,每一个会话之间彼此独立,互不通信,每一个会话独享自己的事务控制,而事务控制之中主要使用两个命令:

事务的回滚:ROLLBACK,更新操作回到原点

事务的提交:COMMIT,真正的发出更新请求,一旦提交后无法回滚

示例:

在会话1中删除一条数据,从另一个会话来查询数据

会话1 delete from myemp where empno=7369;

会话1 select * from myemp;

会话2 select * from myemp;

在会话1中回滚之前的更新操作,再次删除数据,然后提交,再在两个会话中查询数据,在会话1中进行回滚发现已经无法回滚了。

会话1 delete from myemp where empno=7369;

会话1 commit;

会话1 select * from myemp;

会话2 select * from myemp;

注意:这种事务控制会出现一些问题,例如,某一个会话在更新数据表的时候还没有提交事务,其他会话是无法进行更新的,必须等待之前的会话提交后才可以。

示例:

死锁,会话1执行更新后,在没有提交之前,会话2进行更新会出现等待

会话1 update myemp set sal=9000 where empno=7839;

会话2 update myemp set sal=8000 where empno=7839;

这种问题从大的方面来讲可以称作死锁,但是在oracle之中死锁有很多种类.

所有的数据更新一定都会受到事务的控制。

数据伪列

ROWNUM(重点)

ROWNUM为每一个显示的记录都会自动的随着查询生成的行号。

示例:

查询emp表的rownum,empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp;

该行号不是永久的,会随着查询的显示变化而变化

查询emp表的rownum,empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp where deptno=30;

查询前五条记录

select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;

查询6-10行记录

错误做法:

select rownum,empno,ename,job,hiredate,sal from emp where rownum between 6 and 10;

执行完成后并没有返回任何数据,因为rownum不是真实列,正确的思路是先查询前10条记录,然后再查询后5条记录,需要使用子查询

select * from (
select rownum rn,empno,ename,job,hiredate,sal from emp
where rownum<=15) temp
where temp.rn>10;

 

ROWID

ROWID表示的是每一行数据保存的物理地址的编号。

查看ROWID

select rowid,deptno,dname,loc from dept;

查看完成后,保存该结果,下面的案例要和这个结果做比较

每一条记录的ROWID都不会重复,所以即使所有列的内容重复,ROWID也不会重复.

格式为:AAAL+XAAEAAAAANAAA

其中: 数据对象号:AAAL+X

相对文件号:AAE

数据块号: AAAAAN

数据行号: AAA

总结:

1、多表查询:在进行查询语句编写的时候,一定要确定所需要关联的数据表,而且只要是表的关联查询,就一定会存在笛卡儿积的问题,使用关联字段消除此问题。在使用多表查询的时候要考虑到左右连接的问题,oracle之外的数据库可以使用SQL1999语法控制左右连接。

2、所有的统计函数是用于进行数据统计操作的,而统计要在分组中进行/或者是单独使用,分组使用GROUP BY子句,是在某一列上存在重复数据的时候才会使用分组操作,而分组后的过滤使用HAVING子句完成,所有的分组函数可以嵌套,但是嵌套之后的分组函数之中不能再有其他的查询字段,包括分组字段。

3、子查询:结合限定查询、多表查询、分组统计查询完成各个复杂查询的操作,子查询一般在WHERE和FROM之后出现较多。

4、数据库的更新操作一定要受到事务的控制,事务的两个命令:COMMIT,ROLLBACK,每一个连接到数据库上的用户都用一个会话来表示。

5、数据表的分页查询显示依靠ROWNUM伪列,这个在以后的开发当中必定要使用。

 
   
90 次浏览  评价: 差  订阅 捐助
相关文章

我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
 
相关文档

数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
相关课程

高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号