mariadb运维日志
mariadb
复制代码
ALTER TABLE <旧表名> RENAME [TO] <新表名>
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST |AFTER <字段2>
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST |AFTER 已存在的字段名]
ALTER TABLE <表名> DROP <字段名>
ALTER TABLE <表名> DROP FOREGIN KEY <外键约束名>
ALTER TABLE <表名> ENGINE=**
DROP TABLE <表名>
复制代码
复制代码
CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name [length]) [ASC|DESC]
唯一索引|全文索引|空间索引 索引名字
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name [length]) [ASC|DESC]
– alter table t6 add fulltext index infosuoyin(info)
ALTER TABLE table_name DROP [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
– alter table t6 drop fulltext index infosuoyin
– drop fulltext index infosuoyin on t6
复制代码
show create table – 查看表结构
MARIADB 允许不严格的语法：任何标点符号都可以用作日期部分之间的间隔符
例如：98-11-31、98.11.31、98/11/11、98@11@31是等价的，都可以正确插入到数据库
mariadb函数：不区分大小写
数学函数
复制代码
ABS(-33.3) – 绝对值
PI() – 3.14159
SQRT(49) – 算术平方根
MOD(45.5,6) – 求余数
CEIL(-3.35) – 返回不小于x的最小整数值，向下取整
CEILING(-3.35) – 返回不大于x的最小整数值，向上取整
FLOOR(-3.35) – 返回最大整数值
RAND() – 随机一个值 0~1， 当含有参数不变x，rand(x)多次依然是同一个结果
ROUND(x) – 四舍五入
ROUND(x,y) – 四舍五入到y位小数点
TRUNCATE(x,y) – 舍弃小数 （1.99,1—>1.9;1.99,0—>1;1.99,-1—>0）
SIGN(X) – 符号函数 负0正分别对应-1,0,1
POW(X,Y) – 幂函数 x^y
POWER(X,Y) – 幂函数 x^y
EXP(X) – 返回e的x次方
LOG(X) – 求对数 x是整数，否则返回NULL
LOG10(X) – 求对数
RADIANS(X) – 角度转弧度
DEGREES(X) – 弧度转角度
SIN(x) – x是弧度值
ASIN(x) – 反正弦 x=[-1,1]
COS(X) – x是弧度值
ACOS(X) – 反余弦 x=[-1,1]
TAN(X) – x是弧度值
ATAN(X) – 反正切 x=[-1,1]
COT(X) – x是弧度值
复制代码
字符串函数
复制代码
CHARACTER_LENGTH(str) – 返回字符串长度 # 预编码方式有关
LENGTH(str) – 返回字节长度 # 预编码方式有关
CONCAT(str1,str2,…) – 字符串拼接，只要有NULL就返回null
CONCAT_WS(separator,str1,str2,…) – 字符串拼接，只要有NULL就返回null，只不过可以指定以什么拼接
INSERT(str,pos,len,newstr) – 字符串替换，pos确实有，否则返回原值
LOWER(str) – 小写
LCASE(str) – 小写
UPPER(str) – 大写
UCASE(str) – 大写
LEFT(S,N) – 返回字符串s开始的最左边n个字符
RIGHT(S,N) – 返回字符串s开始的最右边n个字符
LPAD(str,len,padstr) – 左填充字符串 ‘aaaa’
RPAD(str,len,padstr) – 右填充字符串 'aaaa’
LTRIM(str) – 去除字符串左边空格
RTRIM(str) – 去除字符串右边空格
TRIM( [remstr FROM] ’ asdsad ') – 去除指定字符串
REPEAT(S,N) – S重复n次
SPACE(N) – 返回一个有n个空格的字符串
REPLACE(str,from_str,to_str) – 替换指定字符
STRCMP(expr1,expr2) – 比较2个字符串大小，返回-1,0，1
SUBSTRING(str pos len) – pos开始位置;支持负数，len为长度
MID(str pos len) – pos开始位置;支持负数，len为长度;如果len小于1；返回空字符串
LOCATE(substr,str) – 返回substr在str中开始的位置
POSITION(substr IN str) – 返回substr在str中开始的位置
INSTR(str,substr) – 返回substr在str中开始的位置
REVERSE(str) – 反转字符串
ELT(N,str1,str2,str3,…) – 返回字符串strn
FIELD(str,str1,str2,str3,…) – 返回str在参数中第一次出现的位置
FIND_IN_SET(str,strlist) – str在strlist的位置， strlist每个子字符串以,分割
MAKE_SET(bits,str1,str2,…) – 由二进制数指定相应位置的字符串组成的字符串
复制代码
日期函数
复制代码
CURRENT_DATE() – 当前时间 YYYY-MM-DD 或YYYYMMDD
CURDATE() – 同上
CURRENT_TIMESTAMP() – 日期和时间 YYYY-MM-DD HH:MM:SS 或YYYYMMDDHHMMSS
LOCALTIME() – 同上
NOW() – 同上
SYSDATE() – 同上
UNIX_TIMESTAMP(date) – 时间戳，可以指定一个日期字符串
FROM_UNIXTIME() – 与上相反
UTC_DATE() – UTC日期
UTC_TIME() – UTC时间
MONTH(date) – 从字符串中获取月份
MONTHNAME(date) – 从字符串中获取英文月份
WEEKNAME(date) – 从字符串中获取英文周几
WEEKDAY(date) – 从字符串中获取周几
DAYNAME(date) – 从字符串中获取英文周几
DAY(date) – 从字符串中获取天数
YEAR(date) – 从字符串中获取年数
QUARTER(date) – 从字符串中获取季度值 1-4
MONTHNAME(date) – 从字符串中获取英文月份
MONTHNAME(date) – 从字符串中获取英文月份
MONTHNAME(date) – 从字符串中获取英文月份
MINUTE(time) – 对应的分钟数
SECOND(time) – 对应的秒数
WEEK(D) – 日期d是一年中的第几周
WEEKOFYEAR(D) – 某天位于一年周的第几周 相当于week(d,3)
DAYOFYEAR(D) – 某天位于一年周的第几天
DAYOFMONTH(D) – 某天位于一月周的第几天
EXTRACT(unit FROM datetime) – 提取一部分日期 EXTRACT(YEAR_MONTH FROM ‘2011-07-12 01:02:03’) ----->>201107
TIME_TO_SEC(TIME) – 转换成秒值 小时3600+分钟60+秒值
SEC_TO_TIME(TIME) – 相反
– 日期加操作
DATE_ADD(date,INTERVAL expr unit) – ADDDATE(‘2010-12-31 23:59:59’, INTERVAL ‘1:1’ MINUTE_SECOND)
ADDDATE(date,INTERVAL expr unit)
ADDTIME(expr1,expr2)
– 日期减操作
SUBDATE(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
SUBTIME(expr1,expr2)
DATEDIFF(expr1,expr2) – 两个日期之间的间隔天数 expr1 - expr2
DATE_FORMAT(date,format) – 根据format指定的格式显示date值
说明符 说明
%a 工作日的缩写名称(Sun…Sat)
%b 月份的缩写名称(Jan…Dec)
%c 月份，数字形式0…12
%D 带有英语后缀的该月日期
%d 该月日期00…31
%e 该月日期0…31
%f 微秒000000…999999
%H 小时00…23
%h,%I 小时01…12
%i 分钟00…59
%j 天数001…366
%k 小时0…23
%l 小时0…12
%M 月份名称January…December
%m 月份00…12
%p 上午AM 下午PM
%r 时间，12小时制 hh:mm:ss AM/PM
%S,%s 秒 00…59
%T 时间，24小时制 hh:mm:ss
%U 一年中的第几周 周日为第一天 00…53
%u 一年中的第几周 周一为第一天 00…53
%W 工作日名称 周日…周六
%w 一周中的每日 0=周日
%Y 4位数形式年份
%y 2位数年份
%% %字符
GET_FORMAT(val_type,format_type) – 返回日期时间字符串的显示形式
– DATE_FORMAT(‘2000-10-25 22:23:00’,GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’}))
一些组合
类型 格式化类型 显示格式字符串
DATE EUR %d.%m.%Y
DATE INTERVAL %Y%m%d
DATE ISO %Y-%m-%d
DATE JIS %Y-%m-%d
DATE USA %m.%d.%Y
TIME EUR %H.%i.%s
TIME INTERVAL %H%i%s
TIME ISO %H:%i:%s
TIME JIS %H:%i:%s
TIME USA %h:%i:%s %p
DATETIME EUR %Y-%m-%d %H.%i.%s
DATETIME INTERVAL %Y%m%d %H%i%s
DATETIME ISO %Y-%m-%d %H:%i:%s
DATETIME JIS %Y-%m-%d %H:%i:%s
DATETIME USA %Y-%m-%d %H.%i.%s
复制代码
IF(expr,v1,v2) – v2 if(expr) v1
IFNULL(V1,V2) – v2 if(v1==NULL) v1
系统信息函数
复制代码
VERSION() – 版本号
CONNECTION_ID() – 服务器当前连接的次数
SHOW PROCESSLIST – 用户连接信息，和用户权限有关
已查询为例：可能会经过Copying to map table ;Sorting result ;sending data 等状态才可以完成
DATABASE(),SCHEMA() – 返回使用utf8字符集的当前数据库名
USER() – 被验证的用户名和 主机名组合
CURRENT_USER()
SYSTEM_USER()
SESSION_USER()
CHARSET(str) – 返回字符串str自变量的字符集
CONVERT(str USING transcoding_name) – 用什么字符集编码
LAST_INSERT_ID() – 返回最后一个insert或update为auto_increment列设置的第一个发生的值，
–（当一次性操作多个，返回第一个）
PASSWORD(str) – 加密，str不可为空;不应将它用于个人程序中;加密不可逆;
MD5(str) – 加密，str不可为空;不应将它用于个人程序中;加密不可逆; 32位16进制
ENCODE(str,pass_str) – pass_str 相当于盐，str需要加密的
DECODE(crypt_str,pass_str) – pass_str 相当于盐，cryptstr需要解密的
FORMAT(X,D) – 格式化函数，四舍五入数字用的
CONV(N,from_base,to_base) – 进制转换 N 从from_base进制转化为to_base进制
INET_ATON(‘192.168.1.1’) – 将ip对应的字符串转化为对应的10进制
INET_NTOA(3232235777) – 与上边相反
GET_LOCK(str,timeout) – 加锁;成功1;超时0;错误null
RELEASE_LOCK(str) – 解锁;
IS_FREE_LOCK(str) – 判断是否可用
IS_USED_LOCK(str) – 是否在被使用
BENCHMARK(count,expr) – 重复count次执行expr表达式，报告的时间是客户端经过的时间，问不是服务端的CPU时间，
– 每次报告的时间并不一定是相同的
CAST(expr AS type) – 将一个类型的值转换为另一个类型的值
CONVERT(expr,type) – BINARY,CHAR,DATE,TIME,DATETIME,DECIMAL,SIGNED,UNSIGNED
复制代码
增删改查
复制代码
5 select
1 from
2 where
=;!=;>;>=;<;<=;
x in []
x between a and b;
x is NULL
(and ;or)
x REGEXP ‘^be’
x like ‘%_*’
3 group by
……
with rollup – 在显示时候会多添加一行数据（统计了多少行数据）
4 having
6 order by
7 limit [位置偏移量,] 行数
SELECT nid,count(*),GROUP_CONCAT(sid) from test GROUP BY nid
------------子查询
ALL,SOME – 关键字，在匹配的时候只要满足内层子查询的任何一个比较条件，就会返回一个结果作为外层查询条件
– where num1 > any (select num2 from tb12)
ALL – 关键字，在匹配的时候满足内层子查询的所有比较条件，就会返回一个结果作为外层查询条件
– where num1 > all (select num2 from tb12)
EXISTS – 关键字，在匹配的时候只要内层子查询查询到了数据，就继续外层查询，否则终止
– where exists (select num2 from tb12 where id = 107)
IN – 关键字，在匹配的时候满足内层子查询的条件，就会返回一个结果作为外层查询条件
– where num1 IN (select num1 from tb12 where f_id = ‘123sadasd’)
= – 关键字，在匹配的时候满足内层子查询的条件，就会返回一个结果作为外层查询条件
– where num1 = (select num1 from tb12 where f_id = ‘123sadasd’)
insert into persion(id,name,age,info) select id,name,age,info from persion_old;
update table set cloumn = xxx where (condition)
delete from table where (condition)
复制代码
索引
复制代码
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。
使用索引用于快速找出某个或多个列中有一特定值的行,所有mariadb列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引分类：
普通索引、唯一索引
单列索引、组合索引
组合索引遵循最左前缀集合
全文索引
char、varchar、text类型创建
只有Myisam引擎支持
空间索引
对空间数据类型的字段建立的索引;GEOMETRY、POINT、LINESTRING、POLYGON,
mariadb使用SPATIAL关键字进行扩展,使得能够进行创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为Not Null,空间索引只能在存储引擎为MyIsam的表中创建
创建索引的原则
1、索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且也会影响insert、delete、update等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
2、避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段
3、数据量小的表最好不要使用索引，由于数据少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
4、在条件表达式中经常用到的不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如‘男，女’就无需创建索引
5、当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
6、在频繁进行排序或分组（group by 或order by）的列上创建索引，如果待排序的列有多个，可以在这些列上建立组合索引
复制代码
存储过程和函数
复制代码
------ 存储过程
CREATE PROCEDURE sp_name([[IN|OUT|INOUT] param_name type])
[characteristices …] rountine_body
-- DELIMITER //
CREATE PROCEDURE avgf()
BEGIN
SELECT AVG( DISTINCT nid) AS XX FROM test;
END;
-- DELIMITER //
------ 存储函数
– 如果return 的类型和returns不一样将会被强制转换
– DELIMITER //
CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT nid FROM test WHERE sid = 1);
– DELIMITER //
------- 如何调用
– xxxxx(in sid INT,OUT SNA CHAR(50),)
call xxxxx(101,@num)
select @num
show {procedure|function} status like ‘C%’\G
------ 在存储过程中定义变量
DECLARE var_name[,var_name]… date_type [default value];
SET var_name=expr [,var_name=expr]
---------------
declare xxx char(50);
select nid into xxx from test where id =1;
---------------
------ 光标的使用
只能在存储过程和函数使用
查询语句可能返回多条记录,如果数据量很大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录
– 声明游标
DECLARE cursor_name CURSOR FOR select_statement;
DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;
-- 打开游标
OPEN cursor_fruit;
-- 使用光标
declare name char(50);
declare price int(50);
FETCH cursor_fruit into name,price;
-- 关闭光标
CLOSE cursor_fruit;
------ 流程控制
if
if val is null then
select ‘val is null’
else
select ‘val is not null’
end if
case
CASE 2 WHEN 1 THEN ‘ASD’ WHEN 2 THEN “ASD” ELSE ‘XZCAFD’ END CASE
CASE WHEN 1 THEN ‘ASD’ WHEN 2 THEN “ASD” ELSE ‘XZCAFD’ END CASE
loop
– 重复执行某些语句
DECLARE id int DEFAULT 0;
loop_name:loop
set id = id +1;
INSERT into test VALUE(id,id,id);
if id >=1000000 then leave loop_name;
end if ;
end loop loop_name;
leave
常与loop联用
iterate
iterate label
将执行语句顺序转到语句段开头处;
只可以出现在loop\repeat\while语句内
iterate：再次循环
label：循环的标志
repeat
– 重复执行循环过程
DECLARE id int DEFAULT 0;
repeat_name:repeat
set id = id +1;
until id >=10
end repeat repeat_name;
while
– 重复执行循环过程
DECLARE id int DEFAULT 0;
while id<10 do
set id = id +1;
end while;
复制代码
视图
复制代码
视图是一个虚拟表
视图的作用：
简单化
看到的就是需要的,那些经常被使用的查询可以被定义为视图,从而使得不必为以后的操作每次指定全部的条件。
安全性
通过视图用户只能查看和修改他们所能看到的数据。数据库中其他的数据则即看不见也取不到,数据库授权可以将检索权限限制到db上，但不能限制到数据库特定行和列上，通过视图，
用户被限制在数据的不同子集上
逻辑数据独立性
视图可以帮助用户屏蔽真实表结构变化带来的影响
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_STATEMENT
[WITH [CASCADED|LOCAL] CHECK OPTION]
– view_name,默认在当前数据库下创建，如需明确数据库：db_name.view_name
– column_list 替换查找的列的名字
– REPLACE 是否替换已经创建的视图
– ALGORITHM视图选择的算法
– 自动选择算法、视图语句于视图定义合并起来，使得试图定义的某一部分取代语句对应的部分、视图的结果存入临时表，
– 然后用临时表来执行语句
– [WITH [CASCADED|LOCAL] CHECK OPTION] 视图在更新时保证在视图的权限范围之内
– 默认CASCADE更新视图时要满足所有相关视图和表的条件
– LOCAL更新视图时满足该视图本身定义的条件即可
–涉及到的权限
– 创建视图的权限
– select的权限
– 当有replace时，还要有对视图drop的权限
DESCRIBE 视图名
DESC 视图名
show table status 视图名
show create view 视图名
SELECT * FROM information_schema.VIEWS – 存储了所有视图的定义
-------------修改视图
1、利用创建视图时的OR REPLACE 参数
2、ALTER语句
ALTER [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_STATEMENT
[WITH [CASCADED|LOCAL] CHECK OPTION]
-------------更新视图
UPDATE view_name set field_name = 5;
DELETE FROM view_name WHERE price = 5;
– 操作之后对应的原表的数据也会修改
当视图中包含有如下内容时，视图的更新操作将不能被执行
视图中不包含基表中被定义为非空的列
在定义视图的select语句后的字段列表中使用了数学表达式
在定义视图的select语句后的字段列表中使用了聚合函数
在定义视图的select语句后的字段列表中使用了DISTINCT、UNION、TOP、GROUP BY或HAVING 子句
-------------删除视图
DROP VIEW [IF EXISTS]
view_name [,view_name]…
[RESTRICT|CASCADE]
MARIADB 中视图和表的区别以及联系是什么？
两者的区别：
1、视图是已经编译好的sql语句,是基于sql语句的结果集的可视化的表,而表不是。
2、视图没有实际的物理记录，而表有
3、表是内容,视图是窗口
4、表占用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时对他进行修改,但视图只能用创建的语句来修改。
5、视图是查看数据表的一种方法,可以查询数据表某些字段构成的结构,只是一些SQL语句的集合,从安全的角度来说,视图可以防止用户接触数据表,因而用户不知道表结构
6、表属于全局模式中的表,是实表;视图是属于局部模式的表,是虚表;
7、视图的建立和删除只影响试图本身,不影响对应的基本表
两者的联系：
视图是在基本表之上建立的表，它的结构和内容都来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
复制代码
触发器
复制代码
触发器是个特殊的存储过程,不同的是,执行存储过程要使用call语句来调用,而触发器的执行不需要使用call语句来调用,也不需要手工启动,
只要当一个预定义的事件发生的时候，就会被自动执行。比如对某张表增删改的时候。
可以包含复杂的sql语句,可以查询其他表，主要用于满足复杂的的业务规则或要求
应用场景：
根据客户当前的账户状态，控制是否允许插入新菜单。
– 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON TABLE1 FOR EACH ROW trigger_stmt
– trigger_name 触发器名称
– trigger_time 触发器时机
after|before
– trigger_event 触发器事件
insert|delete|update
– trigger_stmt
触发器程序体
– 触发触发器
实例1：
CREATE TABLE account(account_num int,amount decimal(10,2))
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW
SET @sum =@sum + NEW.amount;
set @sum = 0;
insert into account value(1,2);
select @sum;
实例2：
当程序体比较复杂是使用
DELIMITER //
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW BEGIN
......
END;
DELIMITER //
– 查看触发器
触发器是指查看数据库中已存在的触发器的定义,状态,信息和语法信息等.
1、SHOW TRIGGERS
2、在triggers表中查看信息
SELECT * FROM information_schema.TRIGGERS ;
– 删除触发器
DROP TRIGGER [schema_name.]trigger_name
复制代码
用户管理
复制代码
mysql数据库下的user表
CREATE TABLE user (
– 用户列Host char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘’,User char(80) COLLATE utf8_bin NOT NULL DEFAULT ‘’,Password char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ‘’,
– 权限列Select_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Insert_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Update_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Delete_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Drop_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Reload_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Shutdown_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Process_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,File_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Grant_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,References_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Index_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Alter_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Show_db_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Super_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_tmp_table_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Lock_tables_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Execute_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Repl_slave_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Repl_client_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_view_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Show_view_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_routine_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Alter_routine_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_user_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Event_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Trigger_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Create_tablespace_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,Delete_history_priv enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,
– 安全列ssl_type enum(’’,‘ANY’,‘X509’,‘SPECIFIED’) CHARACTER SET utf8 NOT NULL DEFAULT ‘’, – 加密ssl_cipher blob NOT NULL, – 加密x509_issuer blob NOT NULL, – 标识用户x509_subject blob NOT NULL, – 标识用户
– 资源控制列：一旦超出，*一小时，也可以使用GRANT更新max_questions int(11) unsigned NOT NULL DEFAULT 0, – 用户每小时允许的查询次数max_updates int(11) unsigned NOT NULL DEFAULT 0, – 用户每小时允许的更新次数max_connections int(11) unsigned NOT NULL DEFAULT 0, – 用户每小时允许的连接操作次数max_user_connections int(11) NOT NULL DEFAULT 0, – 用户允许同时建立的连接次数
– 安全列plugin char(64) CHARACTER SET latin1 NOT NULL DEFAULT ‘’, – 验证用户身份authentication_string text COLLATE utf8_bin NOT NULL,
password_expired enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,is_role enum(‘N’,‘Y’) CHARACTER SET utf8 NOT NULL DEFAULT ‘N’,default_role char(80) COLLATE utf8_bin NOT NULL DEFAULT ‘’,max_statement_time decimal(12,6) NOT NULL DEFAULT 0.000000,
PRIMARY KEY (Host,User)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘Users and global privileges’;
mysql数据库下的db表和host表
db储存了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库
host存储了某个主机对数据库的操作权限，力度更细
mariadb命令参数
-h 主机名
-u 用户名
-p 密码
-P 端口
数据库名：在命令最后可以指定数据库名
-e 可以执行sql语句
– create 创建的用户没有任何权限，还要用grant授予权限
– 在user表添加记录;
CREATE USER ‘ROOT’@‘localhost’ IDENTIFIED BY ‘PASSWORD’ – 明文
CREATE USER ‘ROOT’@‘localhost’ IDENTIFIED BY PASSWORD ‘哈希值’ – 也可以使用对应的哈希值 内置的password()函数生成
CREATE USER ‘ROOT’@‘localhost’ IDENTIFIED WITH my_auth_plugin; --基于插件登录的
– grant:
– 在user表添加记录;
GRANT PRIVILEGES ON db_name.table_name TO user@host [IDENTIFIED BY ‘PASSWORD’][,IDENTIFIED BY ‘PASSWORD’] [WITH GRANT OPTION]
– PRIVILEGES 用户的权限类型
– db_name.table_name 用户权限所作用的表
– GRANT 为新创建的用户授予GRANT权限
例子：GRANT SELECT,UPDATE,DELETE,ADD ON . TO user@host IDENTIFIED BY ‘testpwd’
User表中的user和host字段区分大小写，在查询的时候需要指定正确的用户名称或者主机名
– 直接操作用户表
use mysql;
INSERT INTO mysql.user(Host,User,Password,[PRIVILEGES])
VALUES(‘host’,‘username’,PASSWORD(‘password’),PRIVILEGES_list));
–删除用户
drop USER user[,user]; – user : ‘user’@‘localhost’
delete from mysql.user where host=‘user’@‘localhost’ and user = ‘xxx’;
– 更改密码
mysqladmin -u usernmae -h localhost -p password ‘new_pwd’
update mysql.user set password=password(‘new_pwd’) where User = username and Host = ‘xxxx’
set password=password(‘new_pwd’)
– root 修改其他用户的密码
set password for ‘root’@‘localhost’ = password(‘new_pwd’)
update mysql.user set password=password(‘new_pwd’) where User = username and Host = ‘xxxx’
GRANT USAGE ON . TO ‘’@’’ IDENTIFIED BY ‘new_pwd’
修改完密码后：FLUSH PRIVILEGES 重新加载用户权限
– root用户丢失密码
– 使用–skip-grant-tables
win：
切换到安装目录
mysqld --skip-grant-tables
mysqld-nt --skip-grant-tables
linux:
mysqld_safe --skip-grant-tables user=mysql
/etc/init.d/mysql start-mysqld --skip-grant-tables
-------权限
GRANT ALL ON .
REVOKE ALL ON .
GRANT ALL ON db_name.*
REVOKE ALL ON db_name.*
GRANT ALL ON db_name.table_name
REVOKE ALL ON db_name.table_name
SHOW GRANT FOR ‘’@’’
–访问控制
1、连接核实阶段
基于mysql.USER (Host,User,Password)三个字段验证用户信息
2、请求核实阶段
判断有没有对应的权限
复制代码
数据备份
复制代码
– 一个数据库
mysqldump -u user -h host -ppassword dbname[table_name,] >filename.sql
# shell/cmd管理员运行
mysqldump -u root -h localhost -p1234 mysql >filename.sql
– 多个数据库
mysqldump -u user -h host -ppassword --databses [dbname,] >filename.sql
– 所有数据库
mysqldump -u user -h host -ppassword --all-databses >filename.sql
mysql -u root -p --html --execute=“select * from account;” sad >123.html
– 直接复制目录
停止Mariadb服务
win:
C:\Program Files\MariaDB 10.3\data
linux:
/var/lib/mysql
对InnoDB存储引擎的表不适用
-------------------------------------------------------数据还原--------------------------------------------------------
1、mysql -u user -p [dbname] <filename.sql
2、登录之后选择好要回复的数据库
source …\123.sql
-------------------------------------------------------数据迁移--------------------------------------------------------
mysqldump -h 193.112.27.149 -u root -ppassword dbname | mysql -h 192.168.1.2 -u root -ppassword
mysqldump -h 193.112.27.149 -u root -ppassword --all-databases | mysql -h 192.168.1.2 -u root -ppassword
-------------------------------------------------------表的导入导出--------------------------------------------------------
SELECT … INTO OUTFILE [OPTIONS]
– OPTIONS
FIELDS TERMINATED BY ‘,’ – 字段之间的分割符，默认\t
[OPTIONALLY] ENCLOSED BY ‘,’ – 字段的包围字符只能为单个字符;如果设置OPTIONALLY，只有char/varchar被包括
ESCAPED BY ‘"’ – 写入和读取特殊字符,及设置转义字符 默认\
LINES STARTING BY ‘’’ – 每行数据的开头的字符
TERMINATED BY ‘\r\n’ – 每行数据的结束的字符
用mysqldump导出文本文件
mysqldump -T path -u root -p dbname [tables] [options]
– options
–field-terminated-by=value – 字段之间的分割符，默认\t
–field-enclosed-by=value – 字段的包围字符
–field-optionally-enclosed-by=value – 字段的包围字符只能为单个字符;如果设置OPTIONALLY，只有char/varchar被包括
–lines-escaped-by=value – 写入和读取特殊字符,及设置转义字符 默认\
–lines-terminated-by=value – 每行数据的结束的字符
复制代码
mariadb日志
复制代码
错误日志：记录服务的启动、运行、停止时出现的问题
查询日志：记录建立的客户端连接和执行的语句 – 开启，消耗性能
二进制日志：记录所有更改数据的语句，可以用于数据复制
慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询 – 开启，消耗性能
– C:\Program Files\MariaDB 10.3\data
[mysqld]
log-bin [=‘path/[filename]’]
expire_logs_days=10 – 清楚过期日志的事件
max_binlog_size=100M – 单个文件的大小限制
– 查询日志设置
SHOW VARIABLES LIKE ‘log_%’
–查看二进制文件列表
SHOW BINARY LOGS;
–查看二进制文件
mysqlbinlog C:\Program Files\MariaDB 10.3\data\logs.000001
– 删除所有二进制文件
RESET MASTER
– 删除指定日志文件
PURGE {MASTER|BINARY} LOGS TO ‘log_name’
PURGE {MASTER|BINARY} LOGS BEFORE ‘date’
– 利用二进制数据还原数据
mysqlbinlog [option] filename |mysql -uuser -ppass
–start-date
–stop-date
–start-position
–stop-position
例子：
mysqlbinlog --stop-date=‘2014-03-30 15:27:48’ C:\Program Files\MariaDB 10.3\data\logs.000001 |mysql --uuser -ppass
SET sql_log_bin = {0|1} – 暂停|开启二进制记录
– 错误日志
log-error [=path / [file_name]]
SHOW VARIABLES LIKE ‘log_error’
– 删除和重建错误日志
mysqladmin -u root -p flush-logs
或者登录客户端以后：flush logs;
– 查询日志
log[ =path/[filename]]
– 删除和重建查询日志
mysqladmin -u root -p flush-logs
– 慢日志
log-slow-queries[=path/[filename]]
long_query_time=3 – 超过3秒
复制代码
性能优化
复制代码
1、找出系统的瓶颈，提高数据整体的性能
2、需要合理的结构设计和参数调整，以提高用户操作响应的速度;同时还要尽可能节省系统资源，以便系统可以提供更大负载的服务
方案：
减小系统瓶颈
减少资源的占用
增加系统的反应速度
提高负载能力
优化表结构，索引，查询语句等
SHOW STATUS;
Connections – 连接服务器的次数
Uptime – 服务器的上线时间
Slow_queries – 慢查询的次数
Com_select – 查询操作的次数
Com_insert – 插入操作的次数
Com_update – 更新操作的次数
Com_delete – 删除操作的次数
— 查看语句的性能
EXPLAIN [EXTENDED] SELECT * FROM test WHERE id > 22;
mysql> EXPLAIN extended SELECT * FROM test where id > 222;
±—±------------±------±-----±--------------±-----±--------±-----±------±---------±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----±--------------±-----±--------±-----±------±---------±------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 39809 | 100 | Using where |
±—±------------±------±-----±--------------±-----±--------±-----±------±---------±------------+
1 row in set
id – select识别符
select_type – 语句的类型
– SIMPLE :简单查询;不包括连接查询和子查询
– PRIMARY :主查询;或者最外层的语句查询
– UNION :连接查询的第二个或后面的查询语句
– DEPENDENT UNION
– UNION RESULT
– SUBQUERY
– DEPENDENT SUBQUERY
– DERIVED :导出表的select
key_len – 索引字段长度按字节计算的长度
rows – 检索的行数
type: – 连接类型
好到差
1、system
仅有一行的系统表,const的特殊类型
2、const
常用于比较PRIMARY KEY 或 UNIQUE
3、eq_ref
外键关联
4、ref
5、ref_or_null
同ref，单添加了专门搜索null值的行
6、index_merge
表示使用了索引合并
7、unique_subquery
IN子查询
8、index_subquery
IN子查询,只适合非唯一索引
9、range
根据索引检索范围的行
10、index
与ALL相同，除了只扫描索引树
11、ALL
进行完整的扫描
索引注意
like ‘x%’ – %不能在开头
组合索引 – 要遵循最左前缀集合
OR – 只有前后都是索引列,才会使用索引
join代替子查询
优化表结构
1、字段多 --> 分表
2、增加中间表:
对于经常联合查询的表，可以建立第三张表以提高查询效率,通过中间表，把需要经常联合查询的数据插入中间表中，
然后将原来的联合查询改为对第三张表的查询，以此来提高查询效率
3、增加冗余字段
表设计应尽量遵循范式理论的规约，尽量减少冗余字段，让表设计看起来精致优雅;
但合理地加入冗余字段可以提高查询速度。
表的规范化程度越高，标语表之间的关系就越多，需要链表查询的情况也就越多。
例子：A需要B表里的C字段,可以直接在A表也添加一个C字段。
缺点,数据同步,要修改两个字段
优化插入速度
影响因素：（索引、唯一性校验、一次性插入）
--MyISAM引擎的表
1、禁用索引
对于非空表
插入记录时，会根据标的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。
方法：插入前禁用索引，插入以后在开启索引
ALTER TABLE table_name DISABLE KEYS;
ALTER TABLE table_name ENABLE KEYS;
空表：不需要
2、禁用唯一性检查
插入数据时，会先对插入的数据进行唯一性校验。
SET UNIQUE_CHECKS = 0; -- 关闭
SET UNIQUE_CHECKS = 1; -- 开启
3、使用批量插入
代替多条单个插入
4、使用LOAD DATA INFILE 批量导入
他比insert快
--InnoDB引擎的表
1、禁用唯一性检查
2、禁用外键检查
SET foreign_key_checks=0
SET foreign_key_checks=1
-- 插入了不满足条件的数据也删不了
3、禁用自动提交
set autocommit = 0;
set autocommit = 1;
分析表、检查表、优化表
-- 分析关键字的分布（InnoDB\BDB\MyISAM）
ANALYZE [LOCAL|NO_WAITE_TO_BINLOG] TABLE table_name[,table_name]
-- LOCAL是NO_WAITE_TO_BINLOG的别名, 都是不写入二进制日志
-- 分析过程中只能读，不能更新或插入
mysql> ANALYZE TABLE asd,asdf;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| a.asd | analyze | status | OK |
| a.asdf | analyze | status | OK |
+--------+---------+----------+----------+
2 rows in set
--Op；表示执行的操作,analyze表示进行分析操作
--Msg_type；信息类型，tatus\info\note\warning\error
-- 检查表是否存在错误
CHECK TABLE table_name[,table_name] ... [OPTION]...
OPTION = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
-- QUICK 不检查行，不检查错误的连接
-- FAST 只检查没有被正常关闭的表
-- MEDIUM 只检查上次检查后被更改的表和没有被正确关闭的表
-- EXTENDED 扫描行
-- CHANGED 全面查找，可以保证表100%一致，时间较长
OPTION 只对MyISAM有效, 过程中只能读，不能更新或插入
-- 消除删除或者更新造成的空间浪费
OPTIMIZE [LOCAL|NO_WAITE_TO_BINLOG] TABLE table_name[,table_name]
对InnoDB、MyISAM都有效，
针对的是VARCHAR\BLOG\TEXT类型
建议：一周、一月运行一次
复制代码
本文地址：https://blog.csdn.net/mingxiawdv11/article/details/107312004
上一篇： Spring事务管理（详解+实例）
下一篇： Spark