主要介绍了DQL、多表设计、事务的使用方法和索引的介绍和使用!!
目录
前言
主要介绍了DQL、多表设计、事务的使用方法和索引的介绍和使用!!
提示:以下是本篇文章正文内容,下面案例可供参考
DQL
DQL英文全称是Data Query Language(数据查询语言),用来查询数据库表中的记录。
关键字:SELECT
语法:
select 字段列表 from 表名列表 where 条件列表 gruop by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数
条件查询(where) 分组查询(group by) 排序查询(order by) 分页查询(limit)
基本查询
查询多个字段:
select 字段1, 字段2, 字段3 from 表名;
查询所有字段(通配符):
select * from 表名;
设置别名:
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
去除重复记录:
select distinct 字段列表 from 表名;
注意事项:
* 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
条件查询:
select 字段列表 from 表名 where 条件列表 ;
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ... and ... | 在某个范围之内(含最小、最大值) |
in(...) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
is null | 是null |
逻辑运算符 | 功能 |
and 或 && | 并且 (多个条件同时成立) |
or 或 || | 或者 (多个条件任意一个成立) |
not 或 ! | 非 , 不是 |
分组查询
介绍:将一列数据作为一个整体,进行纵向计算。
语法:
select 聚合函数(字段列表) from 表名 ;
函数 | 功能 |
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
注意事项:
- null值不参与所有聚合函数运算。
- 统计数量可以使用:count(*) count(字段) count(常量),推荐使用count(*)。
分组查询:
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
where与having区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项:
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having 。
排序查询语法:
select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段 ] order by 字段1 排序方式1 , 字段2 排序方式2 ...
排序方式:
ASC:升序(默认值)
DESC:降序
注意事项:
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
分页查询
语法:
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
注意事项:
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
if(表达式, tvalue, fvalue):当表达式为true时,取值tvalue;当表达式为false时,取值fvalue
在 SQL 中,IF
表达式的语法:
SELECT
IF(condition, true_value, false_value) AS Result
FROM table_name;
CASE
表达式在 SQL 中用于条件判断和返回不同的结果。
语法:
case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ...end
多表设计
概述
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多
一对多关系实现:在数据库表中多的一方,添加字段,来关联一的一方的主键。
部门数据可以直接删除,然而还有部分员工归属于该部门下,此时就出现了数据的不完整、不一致问题。
目前上述的两张表,在数据库层面,并未建立关联,所以是无法保证数据的一致性和完整性的。因此需要外键。
外键约束
外键语法:
- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
物理外键
概念:使用 foreign key 定义外键关联另外一张表。
缺点:
- 影响增、删、改的效率(需要检查外键关系)。
- 仅用于单节点数据库,不适用与分布式、集群场景。
- 容易引发数据库的死锁问题,消耗性能。
逻辑外键
概念:在业务层逻辑中,解决外键关联。
通过逻辑外键,就可以很方便的解决上述问题。
一对一
案例: 用户 与 身份证信息 的关系。
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率。
多对多
案例: 学生 与 课程的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少了包含两个外键,分别关联两方主键。
多表查询
- 多表查询: 指从多张表中查询数据
- 笛卡尔积: 笛卡尔乘积是指在数学中,两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)
连接查询
内连接:相当于查询A、B交集部分数据
外连接:
- 左外连接:查询左表所有数据(包括两张表交集部分数据)
- 右外连接:查询右表所有数据(包括两张表交集部分数据)
内连接:
隐式内连接:
select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接:
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
左外连接:
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
右外连接:
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
子查询
- 介绍:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
- 形式:
select * from t1 where column1 = ( select column1 from t2 … );
- 子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。
分类:
- 标量子查询:子查询返回的结果为单个值
- 列子查询:子查询返回的结果为一列
- 行子查询:子查询返回的结果为一行
- 表子查询:子查询返回的结果为多行多列
标量子查询:
- 子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式
- 常用的操作符:= <> > >= < <=
列子查询:
- 子查询返回的结果是一列(可以是多行)
- 常用的操作符:in 、not in等
行子查询:
- 子查询返回的结果是一行(可以是多列)。
- 常用的操作符:= 、<> 、in 、not in
表子查询:
- 子查询返回的结果是多行多列,常作为临时表
- 常用的操作符:in
事务
介绍
概念:事务 是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作 要么同时成功,要么同时失败。
注意事项:
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
操作
开启事务:
start transaction; / begin ;
提交事务:
commit;
回滚事务:
rollback;
四大特性(ACID)
- 原子性:事务是不可分割的最小单元,要么全部成功,要么全部失败
- 一致性:事务完成时,必须使所有的数据都保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
索引
介绍
索引(index)是帮助数据库 高效获取数据 的 数据结构 。
优缺点
优点:
- 提高数据查询的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。
缺点:
- 索引会占用存储空间。
- 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
结构
MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的 B+Tree 结构组织的索引。
大数据量情况下,层级深,检索速度慢
语法
创建索引:
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
查看索引:
show index from 表名;
删除索引:
drop index 索引名 on 表名;
注意事项:
- 主键字段,在建表时,会自动创建主键索引。
- 添加唯一约束时,数据库实际上会添加唯一索引。
总结
上述主要介绍了DQL、多表设计、事务的使用方法和索引的介绍和使用,希望能帮助大家更好的学习mysql!!!