数据库查询-Day16(2019/5/14)-DATABASE

数据库--查询

一、 概念

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库；随着信息 技术和市场的发展，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各 种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够 进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。

image.png

数据库管理系统(Database Management System)是一种操纵和管理数据库的大 型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和 控制，以保证数据库的安全性和完整性。用户通过 DBMS 访问数据库中的数据，数据 库管理员也通过 dbms 进行数据库的维护工作。它可使多个应用程序和用户用不同的 方法在同时或不同时刻去建立，修改和询问数据库。大部分 DBMS 提供数据定义语言DDL（****Data Definition Language****）和数据操作语言DML（****Data ManipulationLanguage****）*，供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除 等操作。 根据存储模型可将数据库划分为关系型数据库和非关系型数据库。 关系型数据库， 是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库 中的数据。 简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

标准数据查询语言 SQL就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。 当前主流的关系型数据库有 Oracle、 DB2、 Microsoft SQL Server、 MicrosoftAccess、 MySQL 等。

Oracle Database，又名 Oracle RDBMS，或简称 Oracle。是甲骨文公司的一款 关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说 Oracle 数 据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功 能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的 适应高吞吐 量的数据库解决方案。

二、SQL 语言

SQL(Structured Query Language)为数据库的语言，在 1974 年由 Boyce【博伊 斯】 和 Chamberlin【钱伯林】 提出的一种介于关系代数与关系演算之间的结构化查询 语言，是一个通用的、功能极强的关系型数据库语言。 它包含三部分:

image.png
image.png

1. 表

表是逻辑表(概念表)，不是物理表。

块(8k) --->区（连续块）-->段(连续区) -->表(多个段) ，数据段不全是表，表一定是数据段。还 有其他段:如索引段。

image.png

2、 表结构

表由表名、字段(名称+类型+约束)、记录 组成。与 java 相对应：


image.png

3、三范式

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

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

简而言之，最终的目的避免数据重复冗余，

  • 1NFà列不可再分,最小原子 （避免重复）

  • 2NF** à**主键依赖(确定唯一)；

  • 3NFà消除传递依赖(建立主外键关联 拆分表*)

四、 SELECT

1、查询列(字段)

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

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

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

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

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

SELECT 表达式 FROM 表名;à查询表达式

SELECT xxx as 别名 FROM 表名 表别名à使用别名

1)、部分列

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


| 

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

 |

2)、所有列

查询所有的字段 通配符 ( 书写方便、可以检索未知列;但是降低检索的性能)* ,数 据的顺序跟定义表结构的顺序一致:


| 

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

 |

3)、去除重复

|

select distinct deptno from emp; --去重

|

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

l as: 字段别名可以使用as;表别名不能使用as l "":原样输出，可以存在 空格与区分大小写


| 

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

 |

5)、字符串

使用’’表示字符串(注意区分””) ，拼接使用 ||


select 'my' from emp; 
select ename||'a'||'-->' info from emp;

6)、虚表

用于计算表达式，显示单条记录的值

select 1+1 from dual;

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

2、查询行(记录)

where 过滤行记录条件 ,条件有

image.png

1)、比较条件


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*的员工名称

2)、且 或 非 and、 or、 not


select * from emp where sal>=900 and sal<=950; 
*--*查询 岗位 为 *CLERK* 且部门编号为 *20*的员工名称 部门编号，工资 
*--*查询 岗位 为 *CLERK* 或部门编号为 *20*的员工名称 部门编号，工资 
*--*查询 岗位 不是 *CLERK* 员工名称 部门编号，工资

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

4)、集合操作

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;

5)、 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');
 *--*查询员工姓名中包含第二个*_*的员工名称 岗位 工资 部门编号

6)、 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 * 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 );

3、排序

使用 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;



*--*对部门编号为 *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;
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 221,198评论 6赞 514
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 94,334评论 3赞 398
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 167,643评论 0赞 360
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 59,495评论 1赞 296
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 68,502评论 6赞 397
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 52,156评论 1赞 308
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,743评论 3赞 421
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,659评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 46,200评论 1赞 319
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 38,282评论 3赞 340
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,424评论 1赞 352
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 36,107评论 5赞 349
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,789评论 3赞 333
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,264评论 0赞 23
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,390评论 1赞 271
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,798评论 3赞 376
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,435评论 2赞 359

推荐阅读更多精彩内容

  • 数据库开发规范
    数据库开发规范1. 数据库命名规范前缀对象前缀命名: 前缀命名一般用小写表的前缀: 业务模块组名前缀存储过程前缀:...
    PowerYangSoft阅读 2,463评论 0赞 8
  • MySQL数据库学习day3
    回顾 字段类型(列类型)：数值型，时间日期型和字符串类型 数值型：整型和小数型(浮点型和定点型) 时间日期型：da...
    翊溪阅读 949评论 0赞 0
  • Python64_MySQL数据库
    安装与配置 安装xampp（为了使用mysql数据库）：点击MySQL的start以启动mysql 配置环境变量（...
    jxvl假装阅读 419评论 0赞 0
  • 四、数据库建模设计
    文/Bruce.Liu1 1.建模简介 范式：英文名称是 Normal Form，它是英国人 E.F.Codd（埃...
    BruceLiu1阅读 5,607评论 0赞 9
  • 【诗】你毕业了（给所有毕业的你）
    鲜花铺就的路 没有掌声 一幕幕，一程程 用尽了力气 努力地、挤出一个笑容 转身的人 看到了“晓风残月” 来时的路没...
    拣爱阅读 161评论 1赞 2