数据库之心：MySQL探索（三）DQL、多表设计、事务和索引

最新推荐文章于 2024-09-11 09:20:18 发布

程序猿零零漆

最新推荐文章于 2024-09-11 09:20:18 发布

阅读量636

点赞数 25

分类专栏： Mysql基础文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_63267251/article/details/141801254

版权

Mysql基础专栏收录该内容

3 篇文章 0 订阅

订阅专栏

主要介绍了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！！！

程序猿零零漆

关注

25
点赞
踩
17

收藏

觉得还不错? 一键收藏
10
评论
数据库之心：MySQL探索（三）DQL、多表设计、事务和索引

主要介绍了DQL、多表设计、事务的使用方法和索引的介绍和使用！！
复制链接

扫一扫

专栏目录