【数据库开发】DQL操作和多表设计

数据库开发

一、数据库操作-DQL

1.概述

用来查询数据库表中的记录，查询操作分为两部分，单表操作和多表操作，针对于查询而言（相较于增删改更加的灵活）基于目标分析条件转换为SQL语句

2.语法

SELECT 
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

3.基本查询（不带任何条件查询）

• 查询多个字段

   select 字段1,字段2,字段3 from 表名;
  

• 查询所有字段

  select * from 表名;
  

• 设置别名

  select 字段1 [as 别名1],字段2 [as 别名2] from 表名;
  

• 去除重复记录

  select distinct 字段列表 from 表名;
  

4.条件查询

条件列表：可以有多个条件

select 字段列表 from 表名 where 条件列表;

SQL语句中构建条件的运算符有两类：比较运算符和逻辑运算符

比较运算符中特殊的

比较运算符	功能
<>或!=	不等于
in(…)	在in范围之内的值，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配人任意个字符）
is null	是null

常用的逻辑运算符

逻辑运算符	功能
and或&&	并且（多个条件同时成立）
or或||	或者（多个条件任意一个成立）
not或！	非，不是

5.聚合函数

之前的查询都是横向查询，根据条件一行一行进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，返回一个结果值（将一列数据作为一个整体，进行纵向计算）

语法：

select 聚合函数（字段列表） from 表名;

聚合函数会忽略空值，对null值不作为统计

常用聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

sum聚合函数计算指定列的数值和时，如果不是数值类型，那么计算结果为0

案例：统计该企业最早入职的员工

select min(entrydate) from tb_emp;

6.分组查询

分组查询是按照某一列或者某几列，把相同的数据进行合并输出，分组查询通常会使用聚合函数进行计算

语法：

select 字段列表 from 表名 [wehere 条件] group by 分组字段 [having 分组后过滤条件];

案例：根据性别分组 , 统计男性和女性员工的数量

select gender, count(*) from tb_emp group by gender; 

7.排序查询

排序查询分为升序排序和降序排序

语法：

select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1,字段2，排序方式2...;

排序方式：

• ASC：升序（默认值）
• DESC：降序

案例：查询所有男生违纪扣分分数的数据(降序排列)如果分数相同按照扣分次数继续降序排列

SELECT * FROM tb_students WHERE gender = '男' ORDER BY break_law_score DESC,break_law_count DESC;

8.分页查询

语法：

select 字段列表 from 表名 limit 起始索引,查询记录数;

起始索引：从0开始

查询记录数：基于起始索引查询的条数

前台传递到后台只有两个数据（要查询第几页）（每页查询多少条）要基于公式进行换算

公式：起始索引=（要查询的页数-1）*每页显示的条数

案例：查询tb_students 查询第3页 每页展示2条

SELECT * FROM tb_students LIMIT 4 , 2;

二、多表设计

在实际开发中，由于业务之间相互关联，所以各个表结构之间存在着各种联系，分为三种：一对多（多对一）、多对多和一对一

• 一对多（多对一）

  问题分析：两张表（员工表、部门表），如果删除部门表中的一个部门，那么在这个部门的员工应该也被删除，在数据库层面并未建立关联，造成部门表不存在而在该部门的员工仍然存在，所以无法保证数据的一致性

  问题解决：通过数据库中外键约束来解决

  外键约束：让两张表数据建立连接，保证数据的一致性和完整性

  关键字：foreign key

  语法：

-- 创建表时指定
create table 表名(
    字段名  数据类型, 
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
    
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（主表列名）

• 一对一

  一对一关系表通常用来做单表的拆分，将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表中，将其他的字段放在另一张表中，以此来提高数据的操作效率

  应用场景：用户表（基本信息+身份信息）

  基本信息：用户ID额，姓名、性别、手机号、学历

  身份信息：民族、生日、身份证号、身份签发机关、身份证的有效时间

  一对一添加外键：在任意一方添加外键，关联另一方的主键，并且设置外键唯一（UNIQUE）

• 多对多

  学生与课程的关系属于多对多，一个学生可以选修多门课程，一门课程也可以供多个学生选择

  实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键