SQL(四)

最新推荐文章于 2023-12-17 23:57:27 发布

欧麦噶没有昵称

最新推荐文章于 2023-12-17 23:57:27 发布

阅读量426

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/iostream992/article/details/106056572

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SQL(四)

1.子查询

概念

子查询简单的来说就是一条sql语句里面嵌套了另外一条或者多条sql语句。

一般使用在比较的值不确定，需要另外一个select语句执行以后才能得到。

子查询(第一种格式，子句出现在where或者having后面)

select ...
from ...
where col_name = (select语句)
group by ...
having col_name = (select语句)
order by ...;

查询工资比Ngao工资高的员工信息

因为我们不知道Ngao薪水，所以先查询出Ngao工资，再去比较(1450.00)
```
select salary 
from s_emp
where last_name = 'Ngao';
```

然后再去查询比940高的员工信息

select *
from s_emp
where salary > 1450

将两条sql合并到一块

select *
from s_emp
where salary > (
    select salary 
    from s_emp
    where last_name = 'Ngao'
);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jn5j4s7z-1589184396489)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200511141622450.png)$

查询以 N 开头，名字由四个字符组成，该员工所在部门员工的基本信息

查询以 N 开头，名字由四个字符组成员工所在的部门编号(41)
```
select dept_id
from s_emp
where last_name like 'N___';
```

查询41号部门的员工信息

select *
from s_emp
where dept_id in (41);

合并两条sql

select *
from s_emp
where dept_id in (
    select dept_id
    from s_emp
    where last_name like 'N___'
);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cWYr8KnA-1589184396494)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200511141955499.png)$

查询平均工资比 41 号部门的平均工资高的部门中员工的信息

查询41号部门平均工资(1247.5)

select avg(salary)
from s_emp
where dept_id = 41;

查询部门平均工资比1247.5高的部门(31,32,35,50,33,10)

select dept_id
from s_emp
group  by dept_id
having avg(salary) > 1247.5;

查询上述部门的员工信息

select *
from s_emp
where dept_id in (31,32,35,50,33,10);

组合

select *
from s_emp
where dept_id in (
	select dept_id
    from s_emp
    group  by dept_id
    having avg(salary) > (
        select dept_id
	    from s_emp
    	group  by dept_id
	    having avg(salary) > 1247.5
    )

);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qbiRr7Nc-1589184396500)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200511142722631.png)$

查询比 Smith 所在部门最高工资高的员工的信息

求部门(41)

select dept_id 
from s_emp
where last_name='Smith';

求最高工资(1450)

select max(salary) 
from s_emp 
where dept_id=41;

提収员工信息

select id,last_name,salary 
from s_emp 
where salary > 1450;

合并

select id,last_name,salary 
from s_emp 
where salary > (
	select max(salary) 
	from s_emp 
	where dept_id= (
		select dept_id 
		from s_emp
		where last_name='Smith'
	)
)

在这里插入图片描述

子查询第二种形式

在查询过程中需要用来一张不存在的表，这张表需要用户通过 select 子句查询的结果进行构建，然后再利用这张构建的表实现最终的查询。
```
select ...
from table_name,(select语句)
where col_name = (select语句)
group by ...
having col_name = (select语句)
order by ...;
```

查询平均工资比 41 号部门的平均工资高的部门中员工的信息,并且显示出当前部门的平均工资

查询41号部门的平均工资(1247.5)

select avg(salary)
from s_emp
where dept_id = 41;

查询平均工资比1247.5高的的部门(31,32,35,50,33,10))

select dept_id
from s_emp
group by dept_id
having avg(salary) > 1247.5

查询该部门的员工信息

select *
from s_emp
where dept_id in (31,32,35,50,33,10);

查询部门平均薪水

select avg(salary),dept_id
from s_emp
group by dept_id

查询员工所在部门的平均薪水

select emp.*,gg.avgs
from s_emp emp,(
  select avg (salary) avgs,dept_id deptid
  from s_emp
  group by dept_id
)  gg
where emp.dept_id = gg.deptid 
and emp.dept_id in (31,32,35,50,33,10);

拼接sql

select emp.*,gg.avgs
from s_emp emp,(
    select avg (salary) avgs,dept_id deptid
    from s_emp
    group by dept_id
)  gg
where emp.dept_id = gg.deptid 
and emp.dept_id in (
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary) > (
        select avg(salary)
        from s_emp
        where dept_id = 41
    )
);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1w3BZoSH-1589184396507)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200511144954216.png)$

2.子查询总结

两种形式
- where 或 having 里面用到值，通过 select 获取到的
- select 时需要一张额外的表，select 获取到的

3.运行时参数(了解)

概念

sql 语句中的值,我们可以使用一个参数来代替,然后每次运行的时候都可以重新输入这个值
格式

&参数名;

参数名可以任意。运行时参数可以是数字也可以是字符串，也可以是sql子句，一条sql语句可以拥有多个运行时参数。
参数是数字
```
select * 
from s_emp
where id = &id;
```

参数是字符串(加单引号)

select *
from s_emp
where last_name = &name;

参数是字符串(不加单引号)

select *
from s_emp
where last_name = '&name';

参数出现多个

select *
from s_emp
where salary > &a; and salary < &b;

参数为子句

select *
from s_emp
where &a;

select *
&a;

4.数据建模

4.1 软件开发流程

软件开发流程大致分为以下步骤

可行性分析（老总）

分析软件经济可行性，法律可行性
需求分析（美工）

对软件进行详细的需求分析原型图
系统设计（架构师，Teamleader）

数据库设计，技术选型（确定开发架构以及前后端所使用的技术）…
编码（码农）
测试（测试人员）
上线
运维（运维人员）

4.2 数据建模介绍

系统设计中一个重要的环节就是觀库设计，数据库设计的时候需要先进行数据建模(实体关系图E-R图) ,数据建模的依据就是前期所做的需求分析.

数据建模大致分为三个阶段:

概念建模阶段
逻辑建模阶段
物理建模阶段

其中概念建模和逻辑建模阶段与数据库厂商毫无关系，换言之，与MySQL,
SQLServer, Oracle没有关系。

物理建模阶段和数据库厂商存在很大的联系, 因为不同厂商对同一功能的支持方式不同，如高可用性，读写分离，甚至是索引, 分区等。

4.2.1 概念建模

在概念建模阶段，主要做三件事：

客户交流
理解需求
形成实体

也是一个迭代，如果先有需求，尽置去理解需求，明白当前项目或者软件需要完成什么，不明白或者不确定的地方和客户及时交流，和客户 double confirm过的需求，落实到实体;但是好多时候我们需要通过先和客户交流，进而将交流结果落实到需求，之后进一步具体到实体

注意：实体类似于java中的类。后面会细讲实体

在一个B2C电子商务网站中，这样的需求再普通不过了：客户可以在该网站上自由进行购物！

我们就以这个简单例子，对其进行细分，来讲解整个数据建模的过程，通过上面这句话，我们可以得出最起码的三个实体：

客户
网站
商品

4.2.1 逻辑建模

对概念建模阶段的实体进行细化，细化成具体的表同时丰富表结构

这个阶段的产物是，形成一张实体关系图。这张关系图中丰富了实体结构，同时展示了实体与实体之间的关系。在实际开发过程中，数据库的设计核心就是实体关系图的设计。

4.2.2 物件建模

将在逻辑建模阶段创建的实体关系图转为相应的 SQL代码，运行来创建相应具体数据库对象。但是这个阶段们不仅仅创建数据库对象，针对业务需求，我们也可能做如数据拆分水平或垂直拆分)。

如B2B网站，我们可以将商家和一般用户放在同一张表中，但是针表现考虑，我们可以将其分为两张表；随业务量的上升，表数据越来越大，整个系统越来越慢，这个时我们可以考虑数据拆分，甚至是读写分离，这个阶段
也会涉及到集群的事情。

如果你是架构师或者数据建模师，这个时候你可以跟 DBA 说，Alright,! am done with it,now is your show time。

注意：物理建模根不同类型的数据哭有很大的关系，不同类型的数据库sql代码不一样。

4.3 实体关系图

在逻辑建模阶段,最终的产物就是实体关系图。

实体关系图(Entity Relationship Diagram) 也叫 ER图,用来描述现实世界的概念模型。

构成ER图的三大要素: 实体, 属性, 关系。

4.3.1 实体

概念

用来表示据有相同特征和性质的事物。(类似于Java中的类)

实体由实体名和实体属性组成。

4.3.2 属性

概念

实体所具有的某一特性，一个实体可以拥有多个属性

4.3.3 关系

概念

实体彼此之间的连接方式
类型

1:1 (丈夫实体和妻子实体)

1:N(班主任实体和班级学生实体)

N:M(学生和老师)
may-be & must-be

在实体与实体之间的关系中，都会存在着may-be和must-be

例如：系统中有顾客和订单俩个实体(1 :N关系)。

一个顾客对应多个订单,一个订单对应一个顾客。

一个顾客可以(may be)没有订单和他对应，一个订单一定(must be)会有顾客和它对应.
在这里插入图片描述

# 唯一
* 非空
#* 非空唯一，将来要用来做主键
o 代表可有可无
虚线代表 mybe 表示顾客可能没有订单
实现 表示 must be 订单一定有着顾客

4.3.4 构建ER图

数据建模软件
- PowerDesigner
- StarUML
- …
StartUML http://staruml.io/
- 介绍
练习画出一个B2C 的电商网站ER图
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-krUrD0po-1589271277142)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512111116480.png)$

4.4 物理建模

物理建模就是将ER图转换为 SQL语句，在之前我们先要了解两个概念主外键。

4.4.1 主键概念

主键就是用来唯一标识一行数据的列。例如一个人可能就是一个数据。身份证号码唯一标识一个人的数据。那么这个号码就是主键。

能做主键的列必要满足非空唯一的特点
只要满足非空唯一的任何列都可以做主键
可以让表中一个有意义的列做主键,比如说学号,它既表示学生学号又作为表中的主键,因为这个列满足非空唯一的条件
可以找一个没有意义的列做主键,就是用来唯一标识一行记录的，大部分情况下都是用没有意义的列去坐主键
可以让多个列联合在一起做表中的主键,那么它就是联合主键,要求这几个列的值联合在一起是非空唯一的

4.4.2 外键概念

表中的某一个列声明为外键列,一般这个外键列都会引用于另外一张表的主键列的值(有唯一约束的列就可以,不一定非要引用主键列）
一张表的主键列中出现过的值都可以在外键列中使用,没有出现过的值,都可以能使用
外键列值也可以为空的,提前是这个外键列在另一张表中不做主键
如果把B表中的联合主键的值引用到A表中做外键因为是俩个列在B表中做联合主键,那么A表引用过来的时候也要把俩个列的值都引用过来，那么它们在A表中就会作为联合外键出现

4.4.3 转换

实体的名字转换为表的名字（人实体）
实体的属性转换为表中的列（姓名，身份证号）
具有唯一特点的属性设置为表中的主键(身份证号码)
根据实体之间的关系设置表的主外键关系

4.4.4 范式

经过上面的例子我们基本上可以去创建表了，但是设计数据哭时需要满足一定的规范，这种规范称为范式，不同的范式层级递增。范式越高，数据库冗余越小。

第一范式(1NF)

列不可再分。一张表中每个列的值是不可再分的。

例如设计一张表，表中有一列是部门。

这个列的值可能会存储部门编号,名字。也就是说这个列是可分割的。因此这个设计不满足第一范式。

注意：设计表时最低满足第一范式。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PRWDrkfm-1589271277157)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512105331613.png)$

第二范式(2NF)

在满足第一范式基础上,表中所有非主键列都必须依赖于主键列。

订单表 : 订单编号(主键)

订单编号订单名称订单日期订单中产品的生产地

这几个非主键列中,产品生产地是不依赖于订单编号的，所以这种设计是不满足第二范式

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6loOriNu-1589271277163)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512105634244.png)$

第三范式(3NF)

在满足第二范式的基础上,表中的非主键列都必须直接依赖于主键列，而不能间接的依赖.(不能产生依赖传递）
例如
订单表：订单编号 (主键）

订单编号订单名称顾客编号顾客姓名

顾客编号依赖于订单编号，顾客姓名依赖于顾客编号。也就是说顾客姓名间接依赖订单编号。这种设计不符合第三范式。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Re3Xk5f-1589271277165)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512105916995.png)$

一般来说数据库只需要满足第三范式就基本上可以了。

巴斯-科德范式(BCNF)

关系模式中每一个决定因素都包含候选键，也就是说，只要属性或属性组A能够决定任何一个属性B，则A的子集中必须有候选键。BCNF范式排除了任何属性(不光是非主属性，2NF和3NF所限制的都是非主属性)对候选键的传递依赖与部分依赖。

比如我们有一个学生导师表，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ML7ynsqc-1589271277166)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512110249487.png)$

这个表的设计满足三范式，有主键，不存在主键的部分依赖，不存在非主键的传递依赖。但是这里存在另一个依赖关系，“专业”函数依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。

所以这个表的部分主键依赖于非主键部分，那么我们可以进行以下的调整，拆分成2个表：

学生导师表：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eqfDeWRG-1589271277168)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512110319975.png)$
导师表：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cp2YrAUx-1589271277169)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512110342194.png)$

第四范式(4NF)
第五范式(5NF)

5.建表语句

有了上面的基础，我们就可以根据ER图去建表了。

语法

-- 格式1
create table 表名(
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型]
);

-- 格式2
create table 表名(
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	字段名 数据类型  [列约束类型],
	[表级约束],
	[表级约束]
);

表名
- 必须是字母开头
- 必须是1-30个字符之间的长度
- 表名中只能出现A - Z, a - z, 0 - #
- 不能和数据库中己有对象的名字重复
- 不能是数据库中的关键字
数据类型
- char varchar varchar2 都是存储字符串
  
  char 存储数据的长度是固定的。
  
  varchar2 存储数据的长度是可变的。
  
  char 效率比 varchar2效率高。
  varchar是数据库标准类型，varchar2 oracle所特有
  
  varchar2不能存空字符串，可以存空。varchar可以存空字符串
  
  oracle 建议使用 varchar2
- number(p,s) 可以存整数和浮点数 p 表示最大位数， s表示小数位，也可以为负数。
  
  number(5,2) 整数 15.22
- date 日期类型，存储日期
- blob 存二进制对象，例如视频，音频图片等。
  
  视频转化为二进制数据 —> 表
  
  特别消耗资源
  
  一般存储在分布式文件系统上，视频路径存储在数据库里面
- clob 存储大文本，例如 xml 等
  
  注意 blob & clob 不会在实际项目中使用。因为特别消耗资源。
  
  mongodb:非关系型数据库，存储大文本
约束
- 主键约束 primary key
- 外键约束 foreign key
- 唯一约束 unique
- 非空约束 not null
- check 约束 check
列级约束写在列后面，表示约束写在所有列后面。

普通建表例子(列级约束)

create table student(
	id number primary key,
	name varchar2(20) not null,
	age number,
	birthday date
);

drop table student;

create table student(
	id number primary key,
	name varchar2(20) not null,
	email varchar2(20) unique,
	gender varchar2(20) check(gender in ('f','m')),
	birthday date
);

drop table student;

普通建表(表级约束)

create table student( 
    id number,
    name varchar2(20) not null,
    age number,
    email varchar2(100),
    gender char,
    primary key(id),
    unique(email),
    check(gender in('f','m'))
);

注意 not null 没有表级约束

建表(主外键关系列级约束)

create table t_customer(
	id number primary key,
	name varchar2(255)
);


create table t_order(
	id number primary key,
	content varchar2(2) not null,
	t_customer_id number references t_customer(id)
)

-- 必须新建t_customer 然后再建 t_order

-- 删除表(必须先删除有外键的表)
drop table t_order;
drop table t_customer;

--如果非要直接删除t_customer
drop table t_customerr cascade constraints;

建表(表级约束)

create table t_customer(
	id number primary key,
	name varchar2(255)
);


create table t_order(
	id numner primary key,
	content varchar2(2) not null,
	t_customer_id number,
	foreign key(t_customer_id)	references t_customer(id)
)


-- 必须新建t_customer 然后再建 t_order

-- 删除表(必须先删除有外键的表)
drop table t_order;
drop table t_customer;

--如果非要直接删除t_customer
drop table t_customer cascade constraints;

建立联合主键

-- 联合主键必须使用表级约束
create table student(
	id number,
	name varchar2(20),
	email varchar2(20) unique,
	gender varchar2(20) check(gender in ('f','m')),
	birthday date,
    primary key(id,name)
);

联合唯一约束

-- 联合唯一约束必须使用表级约束
create table student(
	id number,
	name varchar2(20),
	email varchar2(20) unique
	gender varchar2(20) check(gender in ('f','m')),
	birthday date,
    unique(id,name)
);

联合外键

create table t_customer(
	id number,
	name varchar2(255),
	primary key(id,name)
);


create table t_order(
	id number,
	content varchar2(2) not null,
	t_customer_id number,
	t_customer_name varchar(255),
	foreign key(t_customer_id,t_customer_name)	references t_customer(id,name)
)

约束总结：
- 表级约束与列级所处约束位置不一样
- not null 没有表级约束
- 表级约束和列级约束语法稍有不同
- 只要是联合约束就必须使用表级约束进行构建。

6.给约束起别名

contraint
- 建表是可以给约束给一个名字，如果不起默认就会给一个名字，只不过这个名字对我们来水不友好
- 起名字规律表名_列名_约束类型名
  - 约束类型：
  - pk 主键
  - nn 不为空
  - fk 外键
  - ck check
  - un 唯一
- 将来我们就可以根据这个名字去修改表的约束
- 列级约束名
- 表级约束名

7.特殊建表

建立一张和s_dept一摸一样的表，并且表里面的数据也要样

create table 表名
as
select 语句;

create table s_dept_backup
as
select * from s_dept;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tmljhF55-1589271277173)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512151113503.png)$

建立一张和s_dept一摸一样的表,只需要表结构，不需要数据

create table s_dept_backup
as
select * from s_dept
where 1 = 2;

建立一张和s_emp一摸一样的表,只需要表 id，last_name结构，同时只要前10行数据
```
create table s_emp_backup
as
select id,last_name from s_emp
where rownum <= 10;
```
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kVUG6RIw-1589271277178)(C:\Users\zzb\Desktop\笔记\oracle(4)] .assets\image-20200512151908944.png)$