时间戳

首页 > 技术文章 > Mysql 基础

Mysql 基础

hp-mzx 2021-03-09 16:23 原文

Mysql

问题

1.root管理员密码忘记或管理员用户被误删除（drop user root@'localhost';）

1.停库
systemctl stop mysqld
或
service mysqld stop

2.启动安全模式
mysqld_safe --skip-grant-tables --skip-networking &

--skip-networking：关闭远程连接的协议，防止远程连接
--skip-grant-tables：跳过授权表加载

3.刷新授权表
flush privilege;

4.创建用户
grant all on *.* to root@'localhost' identified by '123456' with grant option;

5.重启数据库
service mysqld restart

 

版本问题

5.6与5.7版本的区别

5.6版本的用户权限表
select user,host,password from mysql.user;

5.7版本的用户权限表
select user,host,authentication_string from mysql.user;

8.0版本

1、必须先建用户，再授权。

2、密码加密算法程度更高。

8.0以前的加密算法：
mysql_native_password
8.0之后的加密算法：
caching_sha2_password

新建用户解决加密算法兼用办法：

新建：
create user novel@'%' identified with mysql_native_password by '123456';

如果已经建立的用户修改加密算法
alter user novel@'%' identified with mysql_native_password by '123456';

 

mysql体系结构

mysql的工作模型：C/S模式

Client:
	自带的：mysql（管理）、mysqldump（备份）
	第三方：navicat、sqlyog、workbench
	应用：pymysql驱动
Server：
	mysqld
	连接协议：本地套接字文件、TCP/IP连接串（IP、Port）

什么是实例

实例是操作系统后台程序的资源集合。

实例=mysqld核心守护进程 + Master Tread + IO、SQL、purge等线程	+ 内存池

 

SQL语句规范

什么是SQL

SQL是结构化查询语言，RDBMS关系型数据库（MySQL、Oracle、MSSQL、PG）通用语言。

SQL标准

SQL89
SQL92
SQL99
SQL03

字符集、校验规则

utf8mb4：支持编码更加全面、emoji字符（表情）
校验规则：排序规则、影响排序。

对象属性

库：字符集、校验规则
表：
​	表的属性：引擎、字符集、校验规则、注释。
​	列的属性：数据类型、约束、其他属性

SQL基础

DDL语句规范
  • 库名、表名：不要有大写字母、不要数字开头和业务有关，不要超过18。
  • 建库：要有字符集
  • 建表：有存储引擎、有字符集
  • 数据类型：适合的、简短的、足够的
  • 必须要有主键、尽量是自增列
  • 尽量设置每列非空。特别是索引列，强烈建议设置非空，否则统计信息不准确，索引失败。
  • 建表要有注释。
  • DDL语句（Alter table），不要在业务繁忙时间操作，容易造成非常严重的问题。
  • 屏蔽非管理用户的drop权限。
DML语句
insert
update
delete

伪删除？

通过 update语句代替delete。
表中设置一个状态列。state 默认1 update 1 ---> 0

dro table t1：表结构（元数据删除）+ 数据彻底删除（表空间）
truncate table t1：清空数据页。物理删除数据，表结构保留。
以上两种方法会立即释放磁盘空间。

delete from t1：全表逐行“删除”记录。不真正删除底层数据。
磁盘看空间不会立即释放，会产生碎片。

 

DQL语句

子句顺序

select 
from 
where 
group by
having
order by
limit

select单独使用

查询函数、系统函数、字符处理、数字处理、时间处理、聚合函数

 

Mysql内置函数

单行函数

字符函数

length

作用：获取字节量，收到字符集影响
select length('abc');
select length('你好世界');

concat

作用：拼接字符串
select concat(user,"@",host) from mysql.user;

upper$lower

作用：转换大小写
select upper('abc')
select lower('DEF')

substr

作用：截取字符串
语法：substr(字符串,pos,len)
select substr("地狱空荡荡，魔鬼在人间",5,3);
select substr("地狱空荡荡，魔鬼在人间",5,3) as test;

instr

作用：返回子集首次出现的索引
select instr('ABCDEDCBA','B') as test;

trim

作用：掐头去尾
select trim('  abc ') as test;
select trim('a' from 'aaa你好世界aaa') as test;

lpad

作用：左填充
select lpad('你好世界',10,'*') as test;

rpad

作用：右填充
select rpad('你好世界',10,'*') as test;

replace

作用：替换
select replace('你好***世界','世界','未来') as test;
例子：替换-为空
select replace(uuid(),'-','') as uuid;

 

数字函数

round

作用：四舍五入
select round(3.14159);
select round(3.14159,3);

ceil

作用：向上取值 >= 最小取整
select ceil(3.14);
select ceil(3.00);
select ceil(-3.14);

floor

作用：向下取值 <= 最大取整
select ceil(9.99);
select ceil(9.00);

truncate

作用：小数保留截断
select truncate(-3.1415,1);

mod

作用：取模
算法：
	方法一：
		mod(a,b)	--> a-a/b*b
	方法二：
		被除数为正则为正，被除数为负则为负
案例：
select mod(10,3)
select mod(10,-3)
select mod(-10,3)

rand

作用：生成某个范围的随机小数
select floor(rand()*10)+1;

进制换算

conv(n,from_base,to_base)
例子：
select conv("a",16,2);

ascii(str)
bin(n)
oct(n)
hex(n)

 

时间函数

当前时间、日期函数

select NOW();
select curdate();
select curtime();

截取时间

select month(now());
select monthname(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

指定格式识别日期

select str_to_date()
例子：
select str_to_date('5-3 2020','%m-%d %Y') as test;
格式符作用
%Y 4位年份，例如：1997
%y 2位年份，例如：97
%m 月份，例如：01,02...,12
%c 月份，例如：1,2...,12
%d 日期，例如：01,02...,31
%H 24小时制
%h 12小时制
%i 分钟，例如：00-59
%s 秒，例如：00-59

指定字符串格式输出日期

select DATE_FORMAT()
select DATE_FORMAT(NOW(),'%Y-%m-%d');

 

其他函数

version()
database()
user()
uuid()
BENCHMARK(count,expr)

 

流程控制函数

if函数==> if else

select if(2>1,'yes','no')
select user,if(user='root','管理员','普通用户') from mysql.user

case函数

用法1：等值判断
    case 表达式
    when 等值判断 then 值1
    ...
    else 值N
    end
案例：
	select 
	case num
	when 110 then CONCAT(num,':警察')
	when 119 then CONCAT(num,':消防')
	else CONCAT(num,':医院')
	end
	as test;

用法2：范围判断
    case
    when 条件1 then 结果或语句
    when 条件2 then 结果或语句
    else
    end

 

Mysql变量

系统变量

全局变量

show global variables like '';
set global read_only=1;
select @@global.read_only;

会话变量

show session variables like '';
set session read_only=1;
select @@session.read_only;

自定义变量--用户变量

作用域

针对会话有效，会话任意位置使用。单独设置或者在存储过程函数。

声明并初始化

set @var=值;
set @var1:=值;
select @var2:=值;

赋值（更新）

方式1：
set @var=值;
set @var1:=值;
select @var2:=值;

方式2：
select count(*) into @count from world.city

使用

select @count;

自定义变量--局部变量

作用域

必须在存储过程内部使用，即：begin ... end中。

声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

赋值

方式1：
set var=值;
set var1:=值;
select @var2:=值;

方式2:
select count(*) into count from world.city

使用

select count;

 

 

 

mysql底层原理

索引

索引是帮助mysql 高效获取数据的排好序的数据结构。

索引的数据结构

  • 二叉树

  • 红黑树（二叉平衡树）

  • Hash表

    • 把任意长度值（key）通过散列算法变换成固定长度的key地址，通过地址访问数据结构
    • 关键码映射到表中一个位置来访问记录，加快查找速度

  • B-Tree（多叉平衡树）

    • 非叶子节点不存储data，只存储索引，可以放更多的索引
    • 叶子节点包含索引索引字段
    • 叶子节点用指针连接，提高区间访问性能

 

InnoDB存储引擎（聚集索引）

InnoDB是mysql数据库存储引擎之一，InnoDB最大特色就是支持ACID兼容的事务。

表数据本身就是B+Tree组织的一个索引结构文件。

聚集索引-叶节点包含完整的数据记录

使用InnoDB创建表会产生两个文件
xxx.frm：创建表的语句
xxx.ibd：表的数据+索引文件

 

 

MySAM存储引擎（非聚集索引）

MySAM是mysql默认的数据库存储引擎（5.5版本前），性能极佳，但缺点是不支持事务。

使用MySAM创建表会产生三个文件
xxx.frm：创建表的语句
xxx.MYD：表的数据的文件
xxx.MYI：表的索引文件

 

推荐阅读