数据库开发

一、数据库操作-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）

  • 多对多

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

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