5、MySQL：常用的函数，事务和索引

最新推荐文章于 2022-09-02 14:20:58 发布

Surplus...

最新推荐文章于 2022-09-02 14:20:58 发布

阅读量107

点赞数

分类专栏： MySQL

原文链接：https://www.bilibili.com/video/BV1NJ411J79W?p=28

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

1、常用函数
2、聚合函数
- 1、类型
- 2、sql语句
3、事务
4、索引

1、常用函数

1、数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

2、字符串函数

/*返回字符串包含的字符数*/
SELECT CHARACTER_LENGTH('坚持就是胜利')  
/*合并字符串,参数可以有多个*/
SELECT CONCAT('我','爱','你')	
/*替换字符串,从某个位置开始替换某个长度*/
SELECT INSERT('我爱编程',1,2,'超级热爱')    
/*小写*/
SELECT LOWER('LOVE')
/*大写*/
SELECT UPPER('love')
/*从左边截取*/
SELECT LEFT('hello,world',5)
/*从右边截取*/
SELECT RIGHT('hello,world',5)
/*替换字符串*/
SELECT REPLACE('坚持就是成功','坚持','努力');  
/*截取字符串,开始和长度*/
SELECT SUBSTR('坚持就是成功',4,2)
/*反转*/
SELECT REVERSE('坚持就是成功')

3、日期和时间函数

/*获取当前日期*/
SELECT CURRENT_DATE();
SELECT CURDATE();

/*获取当前日期和时间*/
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();

 -- 获取年月日,时分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

2、聚合函数

1、类型

函数类型	描述
COUNT()	返回满足Select条件的记录总和数，如 select count() 【不建议使用，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值

2、sql语句

-- 聚合函数
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推荐*/
 
 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */
 
 SELECT SUM(StudentResult) AS 总和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;

注意：

 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;

where写在group by前面,要是放在分组后面的筛选，要使用HAVING，因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的

3、事务

1、什么是事务

简单来说：要么都成功，要么都失败

事务就是将一组SQL语句放在同一批次内去执行
如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
MySQL事务处理只支持InnoDB和BDB数据表类型

2、事务的ACID原则

原子性
- 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样
- 概括来说：要么都成功，要么都失败
一致性
- 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性
- 概括来说：事务前后的数据完整性要保持一致
隔离性
- 事务的隔离是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离
- 隔离的级别
- 脏读，幻读
持久性 ———— 事务提交
- 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
- 概括来说：事务一旦提交则不可逆，被持久化到数据库

3、基本语法和测试

1、基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点(了解)
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

2、测试

/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

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`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION;  
UPDATE account SET money=money-500 WHERE `name`='A';
UPDATE account SET money=money+500 WHERE `name`='B';
-- 提交事务,提交完之后就被持久化
COMMIT; 
# rollback;
SET autocommit = 1; -- 恢复自动提交

4、索引

索引(Index)是帮助MySQL高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构

作用：

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

1、索引的分类

	在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（Primary Key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引 (Unique)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引 (Index)
- 默认的，index，key关键字来设置
全文索引 (FullText)
- 快速定位特定数据

2、索引创建方式

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );

-- id_表名_字段名
#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

3、测试索引

数据库建表

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 DEFAULT '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用户表'

批量插入数据：100w

-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写，标志

CREATE FUNCTION mock_data2()
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),'19224305@qq.com',FLOOR(RAND()*2),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
-- 执行语句，开始插入数据
SELECT mock_data2();

索引效率测试

无索引时

-- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999'

在这里插入图片描述创建索引

CREATE INDEX id_app_user_name ON app_user(`name`);

测试普通索引

SELECT * FROM app_user WHERE name = '用户9998'

在这里插入图片描述

4、索引的原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

学习视频：https://www.bilibili.com/video/BV1NJ411J79W?p=28

Surplus...

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
5、MySQL：常用的函数，事务和索引

文章目录1、常用函数1、数据函数2、字符串函数3、日期和时间函数1、常用函数1、数据函数 SELECT ABS(-8); /*绝对值*/ SELECT CEILING(9.4); /*向上取整*/ SELECT FLOOR(9.4); /*向下取整*/ SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/ SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/2、字符串函数/*返回字符串包含的字符数*/SELECT CHARA
复制链接

扫一扫