MySql的聚合函数、select语法总结、事务、索引的详解

一名没脱发的WEB攻城狮

已于 2023-10-30 13:54:05 修改

阅读量149

点赞数

文章标签： java mysql

于 2023-10-30 13:53:35 首次发布

本文链接：https://blog.csdn.net/m0_38135735/article/details/134117494

版权

MySql的聚合函数、select语法总结、事务、索引的详解

聚合函数

count(计数) 想查询一个表中有多少个记录就用count
- Select count() // Select count(指定列名)会忽略所有的null值
- Select count(*) // 不会忽略所有的null值，本质计算行数
- Select count(1) // 不会忽略所有的null值，本质计算行数
以上三种方法都是查询某个表中的数据
sum(求和)

Select sum(‘需要求和列名’) as 求和 from 表名
avg(平均值)

Select avg(‘需要计算的列名’) as 求和 from 表名
max(最大值)

Select max(‘需要计算的列名’) as 求和 from 表名
min(最小值)

Select min(‘需要计算的列名’) as 求和 from 表名

select语法总结

select [All | distinct]
{* | table.* | [table.field1 as alias1][table.field2 as alias2][...]}
from table_name [as table_alias]
[left | right | inner jion table_name2] -- 联表查询
[where ...] -- 指定结果需要满足的条件
[group by] -- 指定结果需要按照哪些字段来分组
[having] -- 过滤分组的记录必须要满足的次要条件
[order by...] -- 指定查询结果按一个或者多个条件排序
[limit {[offset]row_count | row_countOffset offset}]-- 指定查询的记录从哪条到哪条

注意⚠️：[]代表可选择，{}代表必选

解释一下：

顺序很重要：

select 去重要查询的字段 from 表（注意：表和字段可以取别名）

XXX jion 要联接的表 on 等值判断

where （具体的值，自查询语句）

group by （通过哪个字段来分组）

having （过滤分组后的信息，条件和where是一样的）

order by（通过哪个字段排序，升序/降序）

limit startindex,pagesize

事务

什么是事务？

要么都成功，要么都失败

SQL执行 A给B转账 A 1000 ------->200 B 200
SQL执行 B收到A的钱 A 800 ------->B 400

将一组SQL放在一个批次中去执行

事务的原则：（ACID原则）原子性、一致性、持久性、隔离性

原子性：（Atomicity）

要么都成功，要么都失败

一致性：（Consistency）

事务前后的数据完整性要保持一致，1000

持久性：（Durability）

事务一旦提交则不可逆，被持久化道数据库中

隔离性：（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。

隔离所导致的一些问题

脏读：指一个事务读取了另外一个事务未提交的数据

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读（幻读）：

是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致

执行事务：

mysql是默认开启事务自动提交的

set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启

------------------
-- 手动处理事务
set autocommit = 0 -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
insert XX
insert CC

-- 提交：持久化（提交成功！）
commit
-- 回滚：回到原来的样子（提交失败！）
rollback

-- 事务结束
set autocommit = 1 -- 开启自动提交

-- 了解
savepoint -- 保存点 -- 设置一个事务的保存点
rollback to savepoint -- 回滚到指定保存点
release savepoint -- 删除保存点

模拟事务

CREATE DATABASE shop character SET utf8 COLLATE utf8_general_ci
use shop
CREATE TABLE `account`(
	`id` int(3) not null auto_increment,
	`name` varchar(30) not null,
	`money` decimal(9,2) not null,
	primary key (`id`)
)engine=innodb default charset=utf8
insert INTO account (`name`,`money`)
value ('A',2000.00),('B',10000.00)

-- 模拟事务：转账
set autocommit = 0 -- 关闭自动提交
start transaction -- 开启一个事务（一组事务）

update account set money=money-500 where `name` = 'A' -- A减500
SELECT * FROM account 
update account set money=money+500 where `name` = 'B' -- A减500

COMMIT; -- 提交事务，就被持久化了
rollback; -- 回滚(只要不执行提交语句，都能被回滚到初始数据)

set autocommit = 1;

索引

MySql官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构，数据是数据结构

索引的分类

主键索引不可重复,唯一索引可以重复

主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复列的出现，唯一索引可以重复，多个列都可以表示位
常规索引（key/index）
- 默认的，可以用index关键字或者key关键字来设置
全文索引（fullText）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示索引的索引信息
show index from student

-- 增加一个全文索引 （索引名） 列名
alter table school.student add fullText index `studentName`(`studentName`);

-- explain分析sql执行的情况
explain select * from student; -- 非全文索引

explain select * from student where match(studentName) against('陈');

测试

CREATE table `app_user`(
	`id` bigint(20) unsigned not null auto_increment,
	`name` varchar(50) default '' comment '用户名称',
	`email` varchar(50) not null comment '用户邮箱',
	`phone` varchar(20) default ''  comment '用户手机号',
	`gender` tinyint(4) unsigned comment '0' comment '性别（0:男 1:女）',
	`password` varchar(100) not null comment '密码',
	`age` tinyint(4) default '0' comment '年龄',
	`create_time` datetime default current_timeStamp,
	`update_time` timestamp null default current_timeStamp on update current_timeStamp,
	primary key (`id`)
)engine=innodb default charset=utf8mb4 comment='app用户表'

-- 插入1000000条数据
delimiter $$ -- 写函数之前必须要写，这是一个标志
create procedure mock_data()
-- returns int
begin
	declare num int default 1000000;
	declare i int default 0;
	while i<num do
		INSERT INTO app_user (`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES (CONCAT('用户',i),'23.574201@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND() *100));
    	set i = i + 1;	
    end while;
   -- return i;
end;

call mock_data()
SELECT * from app_user
select * from app_user WHERE name = "用户99999" -- 527ms
explain select * from app_user WHERE name = "用户99999" 

-- id_表名_字段名
-- create index 索引名 on 表名(字段名)
CREATE index id_app_user_name on app_user(`name`);
select * from app_user WHERE name = "用户99999" -- 2ms