数据库oracle
概念理解
SQL 概述
SQL,一般发音为 sequel,SQL 的全称 Structured Query Language),SQL 用来和数据库打交道, 完成和数据库的通信,SQL 是一套标准。
什么是数据库
数据库(Data Base、DB),通常是一个或一组文件,保存了一些符合特定规格的数据 数据库软件称为数据库管理系统(DBMS),全称为 DataBase Management System,如:Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql
表
表是一种结构化的文件,可以用来存储特定类型的数据,如:学生信息,课程信息,都可以 放到表中。另外表都有特定的名称,而且不能重复。表中具有几个概念:列、行、主键。
学号(主键) | 姓名 | 性别 | 年龄 |
---|---|---|---|
001 | 张三 | 男 | 18 |
002 | 李四 | 女 | 18 |
主键:主键是由列构成 的,表中的每一行通常 都有一个标识,主键可 以由一个字段或多个 字段构成,一个字段构 成的主键称为单一主 键,多个字段构成的主 键称为复合主键,主键 通常是不能修改的
行:也叫记录,表中的数 据是按行(记录)存储的, 表里可以有 0 条或多条 记录
列:通常也叫字段,表是 由列构成的,列是具有类 型的
SQL 的分类
数据查询语言(DQL),只有一个 select
数据操纵语言(DML),主要包括:insert/update/delete
数据定义语言(DDL),主要包括:create/drop/alter
事务控制语言(TCLTransaction Control Language),主要包括:commit/rollback
数据控制语言(DCL),主要包括授权等等
演示数据的结构
获取表的信息:
select * from 表名; 获取全部
select 字段1,字段2,… from 表名; 获取部分
Oracle 中常见的数据类型
-
number
- number(3) 整数型:3表示长度,最大值是999
- number(3,2) 浮点型:3是有效数字的个数,2是小数位的个数
- 案例:
create table t_product(
price number(3,2)
);
insert into t_product(price) values(10); //ORA-01438: 值大于为此列指定的允许精度
insert into t_product(price) values(1); //可以
insert into t_product(price) values(1.2356); //可以- char 字符型【长度不可变】
- varchar2 字符型【长度可变】
- date 日期类型
- blob 二进制大对象【Binary Large OBject,存储图片、声音、视频…】
- clob 字符大对象【Character Large OBject,可以存储4G+的字符串】
在实际开发中,日期类型一般都会采用char类型这种字符串的形式表示。因为每一个数据库
在处理日期类型方面都是不同的。使用date类型会导致将来的数据库很难移植
简单查询及 SQLPLUS 常用命令
查询一个字段
查询员工姓名
select ename from emp;
Select 语句后面跟的是字段名称,select 是关键字,select 和字段名称之间采用空格隔开,from
表示将要查询的表,它和字段之间采用空格隔开
查询多个字段
查询员工的编号和姓名(SQL 语句不区分大小写)
select empno, ename from emp;
查询多个字段,需要放到 select 语句的后面,字段之间采用逗号隔开,最后一个字段和 from
不能加逗号
查询所有字段
select * from emp;
一般建议不使用号,使用号不明确,建议将相关的字段写到 select 语句的后面,
使用*****号的效率比较低
计算员工的年薪
列出员工的编号,姓名和年薪
select empno, ename, sal*12 from emp;
将查询出来的字段显示为中文(起别名)
select empno as 员工编号, ename as 员工姓名, sal*12 as 年薪 from emp;
如果字段重命名之后,新名字中含有空格,那么这个新名字需要用双引号括起来。这是
ORACLE 数据库唯一一个用双引号的地方。(Oracle 中所有的字符串都使用单引号括起来)
SQL Plus 常用命令
set linesize 200
set linesize 可以设置一行的字符数,默认为 80 个字符
set linesize 200,表示设置一行为 200 个字符
l(List)
可以显示缓存区中的最后执行的内容
run ,/, r
以上三个命令功能是一致的,重新运行缓存区中的语句
save
save 可以将最后一次在缓存区中执行的语句保存到文件
get
get 可以将文件中的 sql 语句放到缓存区中,采用/或 r 或 run,可以执行
ed(edit)
ed 可以采用记事本来编辑缓存区中的内容
条件查询
条件查询需要用到 where 语句,where 必须放到 from 语句表的后面
运算符 | 作用 |
---|---|
= | 等于 |
<>或!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
between…and…(等同于>= and <=) | 两个值之间 |
is null | 为null |
is not null | 不为null |
实例:
查询薪水为 5000 的员工
select empno, ename, sal from emp where sal=5000;
查询薪水不等于 5000 的员工
select empno, ename, sal from emp where sal <> 5000;
查询工作岗位不等于 MANAGER 的员工
select empno, ename, sal from emp where job <> 'MANAGER';
查询薪水为 1600 到 3000 的员工(第一种方式,采用>=和<=)
select empno, ename, sal from emp where sal >=1600 and sal <=3000;
select empno, ename, sal from emp where sal between 1600 and 3000;
查询津贴为空的员工
select * from emp where comm is null;
查询津贴不为空的员工
select * from emp where comm is not null;
工作岗位为 MANAGER,薪水大于 2500 的员工
select empno, ename, sal from emp where job='MANAGER' and sal>2500;
查询出 job 为 manager 和 job 为 salesman 的员工
select * from emp where job='MANAGER' or job='SALESMAN';
查询薪水大于 1800,并且部门代码为 20 或 30 的
select * from emp where sal>1800 and (deptno=20 or deptno=30);
查询出 job 为 manager 和 job 为 salesman 的员工
select * from emp where job in('MANAGER','SALESMAN');
查询 job 不等于 MANAGER 并且不等与 SALESMAN 的员工
select * from emp where job <> 'MANAGER' and job <> 'SALESMAN';
select * from emp where job not in('MANAGER','SALESMAN');
查询姓名以 M 开头所有的员工
select * from emp where ename like ' M %';
查询姓名以 T 结尾的所有的员工
select * from emp where ename like '%T';
查询姓名中包含 O 的所有的员工
select * from emp where ename like '%O%';
查询姓名中第二个字符为 A 的所有员工
select * from emp where ename like '_A%';
查询出系统中表名含有“下划线”的表
select 字段名 from 数据源 where 字段名 like '%@_%' escape '@';
escape的作用是自定义一个转义字符
按照薪水由小到大排序
select * from emp order by sal;
排序采用 order by 子句,order by 后面跟上排序字段,排序字段可以放多个,多个采用逗号
间隔,order by 默认采用升序,如果存在 where 子句那么 order by 必须放到 where 语句的后
面
select * from emp where sal >1500 order by sal;
按照薪水和姓名倒序
select * from emp order by sal desc ,ename desc;
如果采用多个字段排序,如果根据第一个字段排序重复了,会根据第二个字段排序
按照薪水升序
select * from emp order by 6;
不建议使用此种方式,采用数字含义不明确,程序不健壮
查询员工,将员工姓名全部转换成小写
select lower(ename) from emp;
查询 job 为 manager 的员工
select * from emp where job=upper('manager');
查询姓名以 M 开头所有的员工
select * from emp where substr(ename, 1,1)='M';
方法的第二个参数表示的是查询字符的位置,0,1 都表示第一个字符,负数表示从结尾
开始的位置,第三个参数表示截取字符串的长度。
Substr(‘被截取的字符串’,从哪一位开始截取,截取几位);
a) 从哪一位开始截取,有正数也有负数,正数表示从左边开始数。负数表示从右边开
始数。截取的时候一定是从左向右截取。
取得员工姓名的长度
select length(ename) from emp;
取得工作岗位为 MANAGER 的所有员工
select * from emp where job=trim('MANAGER ');
trim 会去首尾空格,不会去除中间的空格
查询 1981-02-20 入职的员工(第二种方法,将字符串转换成 date 类型)
select * from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
to_date 可以将字符串转换成日期,具体格式 to_date(字符串,匹配格式) 重点
控制符 | 说明 |
---|---|
yyyy | 年 |
mm | 月 |
dd | 日 |
hh12 hh24 | 12小时制 24小时制 |
mi | 分 |
ss | 秒 |
查询 1981-02-20 以后入职的员工,将入职日期格式化成 yyyy-mm-dd hh:mm:ss(to_char 重点)
select empno, ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp where
hiredate>to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
查询员工薪水加入千分位
select empno, ename, to_char(sal, '$999,999') from emp;
将字符串转换成数值
select * from emp where sal>to_number('1,500', '999,999');
采用 nvl 函数,取得员工的全部薪水,薪水+津贴
select empno, ename, sal, comm, sal+nvl(comm,0) from emp;
nvl(表达式 1,表达式 2),表达式 1:指的是字段名称;表达式 2:指的是将该字段的 null 转换成的
值
如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%
select empno, ename, job, sal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal from emp;
select empno, ename, job, sal, decode(job, 'MANAGER', SAL*1.1, 'SALESMAN', sal*1.5) as newsal from emp;
单行函数:一个输入对应一个输出
多行函数(聚合函数):多个输入对应一个输出
分组函数
count(字段) count(*) | 取得记录数 |
---|---|
sum() | 求和 |
avg() | 取平均 |
max() | 取最大的数 |
min() | 取最小的数 |
取得所有的员工数
select count(*) from emp;*
*Count(*)表示取得所有记录,忽略 null,为 null 值也会取得
取得津贴不为 null 员工数
select count(comm) from emp;
采用 count(字段名称),不会取得为 null 的纪录
取得工作岗位的个数
select count(distinct job) from emp; Distinct 可以去除重复的纪录
联合去重:
Select distinct job,deptno from emp; (job 和 deptno 联合去重) Distinct 关键字必须出现在所有字段的前面。
取得薪水的合计
select sum(sal) from emp;
取得薪水的合计(sal+comm)
select sum(sal+nvl(comm, 0)) from emp;
取得平均薪水
select avg(sal) from emp;
取得最高薪水
select max(sal) from emp;
取得最晚入职得员工
select max(to_char(hiredate, 'yyyy-mm-dd')) from emp;
取得最低薪水
select min(sal) from emp;
取得最早入职得员工
select min(hiredate) from emp;
分组查询
分组查询主要涉及到两个子句,分别是:group by 和 having
group by
取得每个工作岗位的工资合计,要求显示岗位名称和工资合计
select job, sum(sal) from emp group by job;
采用 group by,非聚合函数所使用的字段必须参与分组
在 select 语句中,如果有 group by 语句,那么 select 后面只能跟参加分组的字段和分组函
数。
如果使用了 order by,order by 必须放到 group by 后面
having(对分组之后的数据进行过滤)
取得每个岗位的平均工资大于 2000
select job, avg(sal) from emp group by job having avg(sal) >2000;
分组函数的执行顺序:
1、 根据条件查询数据
2、 分组
3、 采用 having 过滤,取得正确的数据
原则:可以在 where 语句中过滤的数据,不要使用 having 过滤。
完整的select语句格式
select 字段
from 表名
where …….
group by ……..
having …….
order by ……..
以上语句的执行顺序
首先执行 where 语句过滤原始数据
执行 group by 进行分组
执行 having 对分组数据进行操作
执行 select 选出数据
执行 order by 排序
连接查询
我们现在使用的数据库是关系型数据库,表和表之间存在关联关系, 通常的业务中要求我们多张表联合起来取得有效数据,这种多张表联合 查询被称作连接查询。(从单张表中取数据的情况比较少)
连接方式:
内连接:等值连接、非等值连接、自连接
内连接:a 和 b 两张表进行连接查询,只查询两张表能够完全匹配的记录,这种查
询叫做内连接
外连接:左(外)连接、右(外)连接
外连接:在内连接的(完全匹配的)基础之上,将其中一张表的记录完全展示,另一
张表肯定会有一些记录无法与其匹配,此时会自动模拟出空值与其匹配。这种连接查询叫做
外连接。
select e.ename,d.dname from emp e,dept d;
两张表连接查询如果没有条件限制,会进行任意匹配,查询结果条数是两张表记录条
数的乘积,这种现象叫做笛卡尔积现象。
查询每一个员工所在的部门名称。要求显示员工名和对应的部门名称。
SQL92 语法:(内连接中的等值连接)
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
SQL99 语法:(内连接中的等值连接)
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
**Sql92** **语法和** **sql99** **语法的区别:****99** **语法可以做到表的连接条件和查询条件分离,特别是多**
**个表进行连接的时候,会比** **sql92** **更清晰**
**查询每一个员工的工资等级****,****要求显示员工的薪水,以及对** **应的等级**
SQL92 语法:(内连接中的非等值连接)
select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
SQL99 语法:(内连接中的非等值连接)
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
//inner 可以省略
**查询出每一个员工的上级领导,要求显示员工姓名以及对** **应的领导名称。**
SQL92 语法:(内连接中的自连接)
select a.ename 员工,b.ename 领导 from emp a , emp b where a.mgr=b.empno;
SQL99 语法:(内连接中的自连接)
select a.ename 员工,b.ename 领导 from emp a inner join emp b on a.mgr=b.empno;
select a.ename 员工,b.ename 领导 from emp a join emp b on a.mgr=b.empno;
**查询员工所在的部门,要求显示员工名和对应的部门名称** **(****要求部门名称全部显示****)**
SQL92 语法:(外连接中的右(外)连接)
select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;
SQL92 语法:(外连接中的左(外)连接)
select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno(+);
SQL99 语法:(外连接中的右(外)连接)
select e.ename,d.dname from emp e right outer join dept d on e.deptno=d.deptno;
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno; //outer 可以省略
SQL99 语法:(外连接中的左(外)连接)
select e.ename,d.dname from dept d left outer join emp e on e.deptno=d.deptno;
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno;//outer 可以省略
任何一个左外连接都有对应的右外连接
**查询出哪个部门没有员工**
select * from dept where deptno not in(select distinct deptno from emp);
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno where e.ename
is null;
**查询出****"****所有****"****员工对应的上级领导名称**
select a.ename 员工,b.ename 领导 from emp a left join emp b on a.mgr=b.empno;
select a.ename 员工,nvl(b.ename,'这是老板') 领导 from emp a left join emp b on
a.mgr=b.empno;
三张表如何表连接 a join b join c a 先和 b **关联,**a 再和 c 关联
学生表 s t_stu
sid | sname |
---|---|
1 | 张三 |
2 | 李四 |
3 | 王五 |
课程表 c t_cour
cid | cname |
---|---|
1 | C++ |
2 | PHP |
3 | Java |
学生选课表 sc t_stu_cour
sid | cid(sid和cid是联合主键、复合主键) |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
2 | 2 |
2 | 3 |
3 | 1 |
3 | 3 |
查询出 2 号学生所选课程,要求显示学生姓名以及对应的课程名称
select
s.sname,c.cname
from
t_stu_cour sc
join
t_stu s
on
sc.sid=s.sid
join
t_cour c
on
sc.cid=c.cid
where
s.sid=2;
子查询
select 语句中嵌套 select 语句
查询员工信息,查询哪些人不是管理者,要求显示出其员工编号和员工姓名
select empno,ename from emp where empno not in(select distinct mgr from emp);
select empno,ename from emp where empno not in(select distinct mgr from emp where
mgr is not null);
子查询和连接查询的取舍:子查询管有多少,最终都是基于一个基表的展现,而连接查询可
以显示出多表的信息。