Oracle
数据库
数据库(Database)是按照数据结构来组织、存储和管理数据的 仓库 ;随着信息技术和市场的发展,数据管理不再仅仅是存储和管理数据,而转变成用户所需要的各种数据管理的方式。数据库有很多种类型,从最简单的存储有各种数据的 表格 到能够进行海量数据存储的 大型数据库系统 都在各个方面得到了广泛的应用。
常用的数据库有:
DBMS
数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,简称 DBMS。它对数据库进行统一的管理和控制,以保证数据库的安全性和完整性。用户通过 DBMS 访问数据库中的数据,数据库管理员也通过 dbms 进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立,修改和询问数据库。大部分 DBMS 提供数据定义语言 DDL (Data Definition Language)和数据操作语言 DML ,供用户定义数据库的模式结构与权限约束,实现对数据的追加、删除等操作。
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说 Oracle 数据库系统是目前世界上流行的关系数据库管理系统,系统可移植性好、使用方便、功能强,适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的适应高吞吐量的数据库解决方案。
SQL 语言介绍
表
表(Table)
表结构
表由表名、字段(名称+类型+约束)、记录 组成。与 java 相对应
SCOTT 用户表
查看用户表
- dept:部门表
- emp:雇员表
- salgrade:工资等级表
- bonus:奖金表
oracle 命令不区分大小写(SELECT 与 select), 存储数据|内容 区分大小写
SELECT
SELECT [DISTINCT] {*,column alias,..} FROM table alias WHERE 条件表达式 ORDER BY 排序字段列表 [asc|desc]
解析步骤: from -> where -> select -> order by
查询列(字段)
- 查询部分字段,指定的字段名:
- 查询所有的字段 通配符 * ( 书写方便、可以检索未知列;但是降低检索的性能) ,数据的顺序跟定义表结构的顺序一致
- 去除重复:使用distinct去重,确保查询结果的唯一性 select distinct deptno from emp; --去重
- 别名:使用别名便于操作识别 、隐藏底层信息。存在字段别名和表别名 select ename as "雇员 姓名" from emp;
as: 字段别名可以使用as;表别名不能使用as "":原样输出,可以存在 空格与区分大小写
- 字符串:使用’’表示字符串(注意区分””) ,拼接使用 ||
select 'my' from emp;sql select ename||'a'||'-->' info from emp;
- 伪列:不存在的列,构建虚拟的列 select empno, 1*2 as count,'cmj' as name,deptno from emp;不存在的列,构建虚拟的列
- 虚表:用于计算表达式,显示单条记录的值 select 1+1 from dual;
- null: null 遇到数字参与运算的结果为 null,遇到字符串为空串
--nvl内置函数,判断是否为null,如果为空,取默认值0,否则取字段实际值 select ename,sal*12+nvl(comm,0) from emp;
查询行(记录)::where 过滤行记录条件 ,条件有
where 过滤行记录条件 ,条件有
a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and b)、and 、or、 not、 union、 union all、 intersect 、minus c)、null :is null、 is not null、 --not is null d)、like :模糊查询 % _ escape('单个字符') f)、in 、 exists(难点) 及子查询
- 比较条件: = 、>、 <、 >=、 <=、 !=、 <>
- 且 或 非:and、 or、 not
- null: null不能使用条件判断,只能使用is
--存在佣金的员工名称 select * from emp where comm is null; --不存在佣金的员工名称 select * from emp where comm is not null; select * from emp where not comm is null;
Union、Union All、Intersect、Minus
Union,并集(去重) 对两个结果集进行并集操作,不包括重复行同时进行默认规则的排序;Union All,全集(不去重) 对两个结果集进行并集操作,包括重复行,不进行排序 ;Intersect,交集(找出重复) 对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;Minus,差集(减去重复) 对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序
--查询工资大于1500 或 含有佣金的人员姓名 --union 去除重复行 select ename from emp where sal>1500 union select ename from emp where comm is not null; -- union all 不去除重复行 select ename from emp where sal>1500 union all select ename from emp where comm is not null; --查询显示不存在雇员的所有部门号。 select deptno from dept minus select distinct deptno from emp --查询工资大于1500 且 含有佣金的人员姓名 select ename,sal,comm from emp where sal>1500 intersect select ename,sal,comm from emp where comm is not null;
like :模糊查询
模糊查询,使用通配符:
- %:零个及以上(任意个数的)的字符
- _:一个字符
- 遇到内容中包含 % _ 使用escape('单个字符')指定转义符
--查询员工姓名中包含字符A的员工信息 select * from emp where ename like '%A%'; --查询员工姓名中包含第二个A的员工名称信息 select * from emp where ename like '_A%'; --数据中 员工姓名中 存在 _ % ,如何查找: --1)、编写测试数据 insert into emp(empno,ename,sal) values(1000,'t_%test',8989); insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000); --2)、查找 --查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号 select ename,job,sal,deptno from emp where ename like '%a%%' escape('a'); --查询员工姓名中包含第二个_的员工名称 岗位 工资 部门编号
in 与 exists
--exists (结果集) 存在即保留,存在即合理 -- 从 from后数据源中拿出每一条数据,判断是否满足where后的条件,如果是exists就判断exists()中的结果集中是否存在数据,存在当前判断的这条数据就满足跳进,不存在就过滤 --执行流程 : from --> where --> select --> order by
获取所有行的记录
select * from emp; select * from emp where 1=1 ; select * from emp where ename like '%';
排序
使用 ORDER BY 排序,排序不是真实改变存储结构的顺序,而是获取的集合的顺序。
- 顺序 :asc(默认) desc
- 多字段: 在前面字段相等时,使用后面的字段排序
- 空排序: 降序为 desc,注意 null 为最后
--按工资降序 select * from emp order by sal desc; --null问题 select * from emp order by nvl(comm,0),comm desc; select * from emp order by comm nulls first; --查询雇员姓名,年薪 按佣金排序 默认为升序(asc),降序为desc,注意null为最后 select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc; --查询雇员姓名,年薪 按佣金排序 默认为升序(asc),降序为desc,注意null为最后 select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
2023/3/18
函数
函数分为系统内置函数 自定义函数(后期学习的 plsql 中定义);了解系统内置函数(方法),重点掌握 to_date,to_char (字符和日期的转换)根据函数的返回结果,我们将函数分为单行函数和多行函数
- 单行函数:一条记录返回一个结果
- 多行函数 组函数 聚合函数 (重点) :多条记录 返回一个结果 (重点)
单行函数
日期函数
oracle以内部数字格式存储日期年月日小时分钟秒
- sysdate/current_date 以date类型返回当前的日期
- add_months(d,x) 返回加上x月后的日期d的值
- LAST_DAY(d) 返回的所在月份的最后一天
- months_between(date1,date2) 返回date1和date2之间月的数目
- next_day(sysdate,星期一) 下一个星期一
select sysdate from dual; select current_date from dual;
-- 2天以后是几号 select sysdate+2 from dual;
-- 查询当前月的最后一天 select ename,last_day(sysdate) from emp;
--雇佣日期 距离现在的 月份数 select ename, months_between(sysdate , hiredate) from emp;
--下一个星期二 select next_day(sysdate, '星期二') from dual;
转换函数(重点)
- to_date(c,m) -> 字符串以指定格式转换为日期
- to_char(d,m) -> 日期以指定格式转换为字符串
select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss') time from dual; select to_char(sysdate, 'yyyy-mm-dd') from dual; select to_char(sysdate, 'yyyy/mm/dd') from dual; select to_char(sysdate, 'yyyy\mm\dd') from dual; --注意中文的问题 --select to_char(sysdate,'yyyy年mm月dd日') from dual; select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;
查询82年入职员工的信息
select * from emp where hiredate between to_date('1982-1-1','yyyy-mm-dd')and to_date('1982-12-31','yyyy-mm-dd'); select * from emp where to_char(hiredate,'yyyy') = '1982';
其他函数
--nvl(值1,值2) 对null值判断 --判定函数 decode(判定字段,值1,结果1,值2,结果2....,默认值) 对判定字段的值进行判定,如果值为值1,函数的结果为结果1,与值2相等,函数的结果为结果2...最终如果以上都不相等,最终取默认值 --给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十... select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','无')from dept;
多行|聚合|组函数
avg 、sum、 min、max、count
- 注意:
-
- 组函数仅在选择列表和Having子句中有效
- 出现组函数,select 只能有组函数或分组字段
- 说明:
-
- 组信息 与单条记录不能同时查询
- 组函数 不能用在 where中,能使用的地方 select having
- null 不参与运算
count: 求和
--1、count统计所有的员工数 --1)、* --2)、主键 --3)、推荐 select ename,1 from emp; select count(1) from emp where 1=1; --2、null不参与运算 --存在佣金的员工数 --不推荐/不需要 select count(comm) from emp where comm is not null; --推荐 select count(comm) from emp; --统计 部门编号30的员工数 select count(1) from emp where deptno=30; --统计数量过程中 ,可能处理重复 --统计 存在员工的 部门数量 select count(distinct(deptno)) 有人的部门 from emp; --统计10和20部门一共有多少人 select distinct(count(1)) from emp where deptno in(10,20);
max min: 最大值 最小值
select max(sal),min(sal) from emp;
sum:求和
-- 查询10部门的所有员工的工资总和 select sum(sal) from emp where deptno=10;
avg: 平均
-- 查询工资低于整个公司平均工资的员工编号,姓名及工资 select empno,ename,sal from emp where sal < (select avg(sal) from emp);
分组
- 分组: group by , 将符合条件的记录 进一步的分组
select distinct * | 字段 | 表达式 | 函数 as 别名 from 表 表别名 where 过滤行记录条件 group by 分组字段列表 having 过滤组 order by 字段列表 asc | desc
解析步骤
1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by
- group by : 分组1)、select出现分组函数,就不能使用 非分组信息,可以使用group by 字段2)、group by字段 可以不出现 select 中 ,反之select 除组函数外的,其他字段必须出现在group by中
- having : 过滤组
-
- where : 过滤行记录,不能使用组函数
- having : 过滤组 可以使用组函数
-- 求出每个部门的平均工资 select deptno,avg(sal) from emp group by deptno; -- 找出20部门和30部门的最高工资 --先过滤后分组 select deptno,max(sal) from emp where deptno in(20,30) group by deptno; --先分组后过滤 select deptno,max(sal) from emp group by deptno having deptno in(20,30); -- 求出10和20部门部门的哪些工资高于1000的员工的平均工资 select avg(sal) from emp where sal>1000 and de