SQL语句基础用法大全(DML)

栏目: 数据库 · 发布时间: 5年前

内容简介:sql 结构查询语言,分以下几组:DML(Data Manipulation Language,数据操作语言),用于检索或者更新数据DDL(Data Definition Language,数据定义语言),用于定义数据的结构,如创建,修改或者删除数据库对象
编辑推荐:
本文来自于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伪列,这个在以后的开发当中必定要使用。


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

ANSI Common Lisp

ANSI Common Lisp

Paul Graham / Prentice Hall / 1995-11-12 / USD 116.40

For use as a core text supplement in any course covering common LISP such as Artificial Intelligence or Concepts of Programming Languages. Teaching students new and more powerful ways of thinking abo......一起来看看 《ANSI Common Lisp》 这本书的介绍吧!

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具