当前位置：首页 > news >正文

数据库补充笔记2

news 来源：原创 2024/5/9 21:43:28

Oracle

命令分类操作：

分类	命令
DDL	create：创建；drop：删除；alter：修改；rename: 重命名; truncate：截断
DML	insert：插入；delete：删除；update：更新；select：查询
DCL	grant：授权；revoke：回收权利；commit:提交事务；rollback：回滚事务

查询列(字段)

select distinct *|字段|表达式 as 别名 from 表表别名

SELECT * FROM 表名; ->查询某个表中所有的记录的所有字段信息

SELECT 列名 FROM 表名; ->查询某个表中所有的记录的指定字段信息

SELECT 列名1,列名2 FROM 表名; -> 查询某个表中所有的记录的字段1 字段2

SELECT distinct 列名 FROM 表名; ->去除重复记录

SELECT 表达式 FROM 表名; ->查询表达式

SELECT xxx as 别名 FROM 表名表别名 ->使用别名

部分列

查询部分字段，指定的字段名:

--1)、检索单个列
select ename from emp; --查询雇员姓名
--2)、检索多个列
select deptno,dname,loc from dept; --查询部门表的deptno,dname, loc 字段的数据。
--以下查询的数据顺序不同(查询的字段顺序代表数据顺序)
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

所有列

--1)、检索所有列1
select * from dept; --查询部门的所有信息
--2)、检索所有列2
select deptno,dname,loc from dept; --查询部门的所有信息

去除重复

使用distinct去重，确保查询结果的唯一性

select distinct deptno from emp; --去重

别名

使用别名便于操作识别、隐藏底层信息。存在字段别名和表别名

select ename as "雇员 姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

as: 字段别名可以使用as;表别名不能使用as

"":原样输出，可以存在空格与区分大小写

null

null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;
select ename,sal*12+comm from emp;
--nvl内置函数，判断是否为null,如果为空，取默认值0，否则取字段实际值 select
ename,sal*12+nvl(comm,0) from emp;

比较条件

= 、>、 <、 >=、 <=、 !=、 <>

select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and 950; --between and是成对出现的
--查询 员工的年薪大于20000的 员工名称、岗位 年薪
--1)、nvl
select ename,job,12*(nvl(comm,0)+sal) income from emp;
--2)、年薪大于20000
--错误不能使用别名: select ename,job,12*(nvl(comm,0)+sal) income from emp where
income>2000;
--a)、嵌套一个: 查询在前 过滤在后
select ename,job,income from
(select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
--b)、不使用别名 (推荐) :过滤在前，查询在后
select ename,job,12*(nvl(comm,0)+sal) income from emp where 12*(nvl(comm,0)+sal)
>2000 ;
--了解 any some all
-- >=any(值列表) 大于最小值<=any(值列表)小于最大值
select * from emp where sal >=any(900,2000);
select * from emp where sal <=any(900,2000);
-- some与any 一样的效果
-- all 大于最大值 小于最小值
select * from emp where sal >=all(900,2000); select * from emp where sal
<=all(900,2000);
--查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
--检索 工资 大于 2000员工名称 岗位 工资
--检索 工资 小于 3000员工名称 岗位 工资
--检索 工资 2000, 3000员工名称 岗位 工资
--查询部门编号为20的员工名称

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

in相当于使用or的多个等值，定值集合 ,如果存在子查询，确保类型相同、字段数为1，如果记录多，效率不高，用于一些少量定值判断上

select * from emp where sal in(900,800);
--子查询(查询中再有查询) in 只能存在一个字段
select * from emp where sal in (select sal from emp e where deptno=10);
--10或30部门的雇员信息
select * from emp where deptno in(10,30);
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
select deptno from dept where dname in('SALES','ACCOUNTING'); SELECT *
FROM emp
WHERE deptno IN
(SELECT deptno FROM dept WHERE dname IN ('SALES', 'ACCOUNTING'));
/*
便于理解 使用java思维
while(外层结果集){
while(内层结果集){
	if(emp.deptno==10){
		syso("....");
	}
	if(emp.deptno==30){
		syso("....");
	}
}
}
==>in :如果记录多，效率不高，用于 一些 少量定值判断上
*/

exists条件为true,存在记录则返回结果，后续不再继续比较查询，与查询的字段无关，与记录有关

--exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
--exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)
/*
while(外层结果集){
while(内层结果集){
if(emp.deptno==dept.deptno||true){
syso("....");
break; //跳出本层
}
}
}
*/
--无关系列
/**
while(emp 的14条记录){
while(dept的2条记录){
if(true){
syso();
break;
}
}
}
*/
select *
from emp
where exists
(select deptno,dname from dept where dname in ('SALES', 'ACCOUNTING'));
--加入关系列
/**
while(emp 的14条记录){
while(dept的2条记录){
if(e.deptno=d.deptno){
syso();
break;
}
}
}
*/
select *
from emp e
where exists (select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno = d.deptno);
/**
while(emp 的14条记录){
while(dept的2条记录){
if(e.deptno!=d.deptno){
syso();
break;
}
}
}
*/
select *
from emp e
where exists (select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno != d.deptno);
--分析以下结果
select *
7.2.7. 获取所有行的记录
7.2.8. 排序
使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。
顺序 :asc（默认） desc
多字段: 在前面字段相等时，使用后面的字段排序
空排序: 降序为 desc，注意 null 为最后
from emp e
where not exists
(select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno = d.deptno);
select ename, sal
from emp
where sal in (select sal from emp e2 where e2.sal >= 2000);
select ename, sal
from emp
where exists (select ename, sal from emp e2 where e2.sal >=
2000); --exists
select empno, ename, sal
from emp e1
where exists (select empno, ename, sal, comm
from emp e2
where comm is not null
and e1.empno = e2.empno);
select empno, ename, sal
from emp e1
where exists (select empno, ename, sal, comm
from emp e2
where comm is not null
and e1.deptno = e2.deptno);

排序

使用 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;
7.3. 作业
1. 使用基本查询语句.
(1)查询DEPT表显示所有部门名称.
(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年
收入"。（NVL(comm,0) comm取空值时用0替代）
(3)查询显示不存在雇员的所有部门号。
2. 限制查询数据
(1)查询EMP表显示工资超过2850的雇员姓名和工资。
(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。
(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。
(6)查询EMP表显示补助非空的所有雇员名及其补助。
3. 排序数据
(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序
(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇
佣日期进行排序。
(3)查询表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。
8. 函数
函数分为系统内置函数 自定义函数（后期学习的 plsql 中定义）；了解系统内置函数（方法），重点掌
握 to_date，to_char （字符和日期的转换）根据函数的返回结果，我们将函数分为单行函数和多行函
数
--对部门编号为 20 或30的雇员，工资+佣金 进行升序排序，如果相同，则按姓名降序。
--1、查询20、30 雇员
select * from emp where deptno in(20,30);
--2、工资+佣金排序
select ename,sal,comm,sal+nvl(comm,0) c from emp where deptno in(20,30) order by
c;
--3、多个字段排序使用, 排序的字段可以使构建出来的虚拟的字段
select ename,sal,comm from emp where deptno in(20,30) order by
sal+nvl(comm,0),ename desc;

转换函数（重点）

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;

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);

分组

分组: group by , 将符合条件的记录进一步的分组

过滤组:having, 过滤组信息，表达式同where 一致

现在的结构如下

select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc

解析步骤

1)、from
2)、where
3)、group by
4)、having
5)、select
6)、order by

group by : 分组

1)、select出现分组函数,就不能使用非分组信息，可以使用group by 字段

2)*、group by字段可以不出现select中，反之select除组函数外的，其他字段必须出现在group by

中

having : 过滤组

where : 过滤行记录，不能使用组函数
having : 过滤组可以使用组函数

--按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
--按 部门岗位 查询 平均工资
select avg(sal) from emp group by deptno,job;
--按 部门 查询 平均工资，且平均工资大于2000的部门编号
--1、先分组 后过滤 (不推荐)
select *
from (select deptno, avg(sal) avsal from emp where 1 = 1 group by deptno)
where avsal > 2000;
--2、过滤组 ,分组同时 过滤
select avg(sal), deptno from emp group by deptno having avg(sal)>2000;
--查询 最低平均工资的部门编号
--1)、按部门求出平均薪水
select avg(sal) from emp group by deptno;
--2)、找出最低的平均薪水
select min(avg(sal)) from emp group by deptno;
--3)、过滤组
select deptno
from emp
where 1 = 1
group by deptno
having avg(sal) = (select min(avg(sal)) from emp where 1 = 1 group by deptno);
--查看 高于本部门平均薪水员工姓名
--1、按部门求出平均薪水
--2、关联子查询
select *
from emp e
where exists
(select deptno
from (select deptno, avg(sal) avgsal from emp group by deptno) e2
where e.deptno = e2.deptno
and e.sal > avgsal);
--另外一种 (推荐)
select *
from emp e1
where sal > (select avg(sal) from emp e2 where e2.deptno = e1.deptno);

表链接（99）

交叉连接 cross join --->笛卡尔积

自然连接(主外键、同名列) natural join -->等值连接

join using连接(同名列) -->等值连接

[inner]join on 连接 -->等值连接非等值自连接 (解决一切) 关系列必须区分

left|right [outer] join on|using -->外连接

full join on|using -->全连接满足直接匹配，不满足相互补充null ，确保所有表的记录都至少出

现一次

DDL

SQL语言结构:

DDL（Data Definition Language 数据定义语言）用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL 对这些对象和属性的管理和定义具体表现在 create、 drop 和 alter 上。特别注意：DDL 操作的“对象”的概念，”对象“包括对象及对象的属性，而且对象最小也比记录大个层次。以表举例：create 创建数据表，alter 可以更改该表的字段，drop 可以删除这个表，从这里我们可以看到，DDL 所站的高度，他不会对具体的数据进行操作。

语句	作用
create	可以创建数据库和数据库的一些对象
drop	可以删除数据表、索引、条件约束等
alter	修改数据表定义及属性

三范式

在设计数据库时，存在行业的标准，这个标准也称为条件，即范式 Normal Form。一般遵循三个条件即可，也就是”三范式”(3NF)。

1NF:是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。
2NF：是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。如 emp 表中加上了员工编号（empno）列，因为每个员工的员工编号是唯一的，因此每个员工可以被唯一区分。这个唯一属性列被称为主关键字或主键、主码。同时要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。即第二范式就是非**主属性非部分依赖于主键。
3NF:必须先满足第二范式（2NF）。3NF 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（deptno）、部门名称、地址等信息。那么员工信息表(emp)中列出部门编号后就不能再将部门名称、部门地址等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。即第三范式就是属性不依赖于其它非主属性。

简而言之，最终的目的避免数据重复冗余，1NF-->列不可再分最小原子（避免重复);2NF-->主键依赖(确定唯一；3NF-->消除传递依赖(建立主外键关联拆分表);

DML

DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

DML 的主要语句（操作）：

语句	作用
Insert	向数据表张插入一条记录
Delete	删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
Update	用于修改已存在表中的记录的内容