SQL(四)

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

    

• 查询以 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___'
    );
    

    

• 查询平均工资比 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
        )
    
    );
    

    

• 查询比 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
        )
    );
    

    

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图

• 

4.4 物理建模

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

4.4.1 主键概念

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

• 能做主键的列必要满足非空唯一的特点

• 只要满足非空唯一的任何列都可以做主键

• 可以让表中一个有意义的列做主键,比如说学号,它既表示学 生学号又作为表中的主键,因为这个列满足非空唯一的条件

• 可以找一个没有意义的列做主键,就是用来唯一标识一行 记录的，大部分情况下都是用没有意义的列去坐主键

• 可以让多个列联合在一起做表中的主键,那么它就是联 合主键,要求这几个列的值联合在一起是非空唯一的

  

4.4.2 外键概念

• 表中的某一个列声明为外键列,一般这个外键列都会引 用于另外一张表的主键列的值(有唯一约束的列就可以,不一定非要引用主键列）

• 一张表的主键列中出现过的值都可以在外键列中使用,没有出现过的值,都可以能使用

• 外键列值也可以为空的,提前是这个外键列在另一张表中不做主键

• 如果把B表中的联合主键的值引用到A表中做外键因为是 俩个列在B表中做联合主键,那么A表引用过来的时候也要把俩个列的值都引用过来，那么它们在A表中就会作为联合外键出现
  

4.4.3 转换

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

4.4.4 范式

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

• 第一范式(1NF)

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

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

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

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

• 第二范式(2NF)

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

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

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

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

• 第三范式(3NF)

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

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

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

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

• 巴斯-科德范式(BCNF)

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

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

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

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

学生导师表：

导师表：

• 第四范式(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;
  

  

• 建立一张和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;