MySQL学习(三)

Wesley13
• 阅读 520

MySQL函数

官网:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1、常用函数

-- ======================= 常用函数 =====================

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4); -- 向上取整
SELECT FLOOR(9.4); -- 向下取整
SELECT RAND(); -- 返回一个0-1之间的随机数
SELECT SIGN(10); -- 判断一个数的符号 负数返回-1 整数返回1

-- 字符串函数
SELECT CHAR_LENGTH('abcdefghijkl'); -- 字符串长度
SELECT CONCAT('i','love','you');
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 从替换某段字符串 1是起始,2是长度
SELECT LOWER('LenStar'); -- 转为小写
SELECT UPPER('Lenstar'); -- 转为大写
SELECT INSTR('Lenstar','h'); -- 返回第一次出现的字符串的索引
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换指定的字符串
SELECT SUBSTR('坚持就能成功',4,6); -- 截取指定的字符串 4是起始位置,6是长度
SELECT REVERSE('上海自来水来自海上'); -- 反转

-- 查询姓周的同学,并将周改为邹
SELECT REPLACE(studentname,'周','邹') FROM student 
WHERE studentname LIKE '周%';

-- 时间和日期函数(记住)
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()); -- 秒

-- 系统
SELECT SYSTEM_USER(); -- 系统用户
SELECT USER(); -- 用户
SELECT VERSION(); -- 版本

2、聚合函数(常用)

函数名称

描述

AONUT()

计数

SUM()

求和

AVG()

平均值

MAX()

最大值

MIN()

最小值

...

...

-- ================ 聚合函数 =================
-- 都能够统计表中的数据(想查询一个表中有多少个记录,就使用这个count())
SELECT COUNT(`BornDate`) FROM student; -- count(段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*),不会忽略null值,本质:计算行数
SELECT COUNT(1) FROM result; -- count(1), 不会忽略忽略所有的null值,本质 计算函数

SELECT SUM(`StudentResult`) AS 总分 FROM result;
SELECT AVG(`StudentResult`) AS 平均分 FROM result;
SELECT MAX(`StudentResult`) AS 最高分 FROM result;
SELECT MIN(`StudentResult`) AS 最低分 FROM result;

3、数据库级别的MD5加密

什么是MD5?

主要增强算法复杂度和不可逆性

MD5不可逆,具体的值的MD5值是一样的

MD5破解网站的原理,背后有一个字典,MD5加密后的值---加密前的值

-- ============== 测试MD5加密 ==============

CREATE TABLE `testmd5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 密文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1; 

UPDATE testmd5 SET pwd=MD5(pwd); -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'));

-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT * FROM `testmd5` 
WHERE `name`='xiaoming' AND `pwd` = MD5('123456');

事务

要么都成功,要么都失败

1、SQL执行:A给B转账 A 1000 ---> 200 B 200

2、SQL执行:B给A的钱 A 800 ---> 800 -->B 400

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

事物原则:ACID 原则: 原子性、一致性、隔离性、持久性

参考博客链接: https://blog.csdn.net/dengjili/article/details/82468576

原子性(Atomicity)

要么都成功,要么都失败

一致性(Consistency)

事物前后的数据完整性要保持一致

**持久性(Durability)**---事物提交

事务一旦提交则不可逆,被持久化到数据库中!

隔离性(lsolation)

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

隔离所导致的一些问题

脏读:

值一个事务读取了另外一个事务未提交的数据。

不可重复读:

在一个十五内读取表中的某一行数据,多次读取结果不同,(这个不一定时错误,知识某些场合不不对)

虚读(幻读)

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

执行事务

-- ============ 事务 =================

-- mysql是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启

-- 手动处理事务 关闭自动提交
SET autocommit = 0

-- 事务开启
START TRANSACTION -- 标记一个事务的开始吗,从这个之后的sql都在同一个事务内

INSERT xx
INSERT xx

-- 提交:持久化(成功的时候)
COMMIT
-- 回滚:回到原来的样子(失败的时候)
ROLLBACK
-- 事务结束 开启自动提交
SET autocommit = 1  

模拟场景

-- 转账
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`)
VALUES('a',2000.00),('B',10000.00)

-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务(一组事务)

UPDATE account SET money=money-500 WHERE `name` = 'A' -- A减500
UPDATE account SET money=money+500 WHERE `name` = 'B' -- A加500

COMMIT; -- 提交事务,就被持久化了!
ROLLBACK; -- 回滚

SET autocommit = 1; -- 回复默认值。开启自动提交

MySQL学习(三)

索引

MySQL官方对缩影的而定义为:索引(index)时帮助MySQL高效获取数据的数据结构。

提取句子主干,可以得到索引的本质:索引就是数据结构。

1、索引的分类

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

  • 主键索引(PRIMARY KEY)

    • 唯一的标志,主键不可重复,之恶能有一个列作为主键
  • 唯一索引(UNIQUE KEY)

    • 避免重复的列出现,唯一索引可以重复,多个列都乐意标志为唯一索引
  • 常规索引(KEY/INDEX)

    • 默认的,index、keyguanjianzilaishezhi
  • 全文索引(FULLTEXT)

    • 在特定的数据库引擎下才有,MAISAM
    • 快速定位数据

基础语法

-- 索引的使用
-- 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('刘');-- match:获取; aginst:匹配

2、测试索引

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

-- 插入100万数据  41.664 sec
DELIMITER $$  -- 写函数之前必须要写,一个标识
CREATE FUNCTION 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),'24736743@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;
SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.259 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.256 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.279 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

-- 添加索引后再测试
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.003 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

MySQL学习(三)

MySQL学习(三)

索引再小数据量的时候,用处不打,但是再大数据的时候,区别十分明显

3、索引原则

  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据的表不需要加索引
  • 索引一般加载常用来查询的字段上!

索引的数据结构

hash类型的索引

Btree:INNODB的默认数据结构

深入了解参考:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

权限管理和备份

1、用户管理

SQLyog可视化管理

MySQL学习(三)

SQL命令操作

用户表:mysql.user

本质:对这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER lenstar IDENTIFIED BY '123456'

-- 修改密码 (修改当前用户密码)
SET PASSWORD = PASSWORD('111111');

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR lenstar = PASSWORD('111111');

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER lenstar TO lenstar2

-- 用户授权 ALL PRIVILEGES 全部的权限
-- ALL PRIVILEGES 除了给别人授权,其他的都能干
GRANT ALL PRIVILEGES ON *.* TO lenstar2

-- 查询权限
SHOW GRANTS FOR lenstar2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- root用户权限:grant all privileges on *.* to 'root@localhost' with grant optton

-- 撤销权限 REVOKE 哪些权限,在哪个库撤销,给谁撤销
RENAME ALL PRIVILEGES ON *.* FROM lenstar2

-- 删除用户
DROP USER lenstar2

2、MySQL备份

为什么要备份:

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件
  • 再sql'yog这种可视化工具中手动导出
    • 在想要导出的表或者库中,右键,选择备份或导出

MySQL学习(三)

使用命令行导出 mysqldumo 命令行使用

# mysqldumo -h主机  -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysql -hlocahost -uroot -p123456 school student >D:/student.sql

# mysqldumo -h主机  -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
mysqldump -hlocahost -uroot -p123456 school student >D:/abc.sql

# mysqldumo -h主机  -u用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocahost -uroot -p123456 school >D:/school.sql

# 导入
# 登录的情况下,切换到指定的数据库
# source 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名< 备份文件

规范数据库设计

1、为什么需要设计

当数据库比较复杂的时候,我们就需要设计了

糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库插入和删除都会很麻烦、异常
  • 程序的性能差

良好的数据库设计:

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中,关于数据库的设计:

  • 分析需求:分析业务和需要处理的数据库的请求
  • 概要设计:设计关系图E-R图

2、三大范式

为什么需要数据规范化?

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正常显示信息
  • 删除异常

    • 丢失有效的信息

三大范式

第一范式(1NF)

原子性:保证每一列不可再分

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

第三范式(3NF)

前提:满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接访问。

规范性和性能的问题

关联查询的表不得超过三张

  • 考虑商业化的需求和目标,数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当的考虑一下规范性!
  • 故意给某些表增加一些冗余的字段。(从对标查询中变为单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这