数据库相关

数据库5.7安装方法

1. 安装yum源: rpm -ivh https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
2. 刷新yum源: yum repolist
3. 卸载mariadb: yum -y remove mari*
4. 安装数据库: yum -y install mysql-community-server
5. 启动数据库: systemctl enable –now mysqld
6. 在配置文件中写入: /etc/my.cnf

validate-password=OFF # 不使用密码强度审计插件

1. 重启数据库: systemctl restart mysqld
2. 查看初始密码: grep password /var/log/mysqld.log
3. 登录数据库: mysql -uroot -p’初始密码’
4. 设置密码: set password for ‘root’@’localhost’= password(‘密码’);

5. 刷新权限: flush privileges;

   sql语句结构

6. 数据库查询语句: select , where , order by , group by , having

7. 数据库操作语言: insert , update , delete
8. 事务处理语言: begin , transaction , commit , rollback
9. 数据控制语言: grant , revoke
10. 数据定义语言: create , alter , drop
11. 指针控制语言: declare cursor , fetch into , update where current

    常见数据库类型

    整形:
    tinyint,占 1 字节，有符号：-128~127，无符号位：0~255
    smallint,占 2 字节，有符号：-32768~32767，无符号位：0~65535
    mediumint,占 3 字节，有符号：-8388608~8388607，无符号位：0~16777215
    int,占 4 字节，有符号：-2147483648~2147483647，无符号位：0~4284967295
    bigint,占 8 字节
    bool 等价于 tinyint(1) 布尔型

浮点型:
float([m[,d]]) 占 4 字节，1.17E-38~3.4E+38
double([m[,d]]) 占 8 字节
decimal([m[,d]]) 以字符串形式表示的浮点数

字符型
char([m])：固定长度的字符,占用 m 字节
varchar[(m)]：可变长度的字符,占用 m+1 字节，大于 255 个字符：占用 m+2
tinytext,255 个字符(2 的 8 次方)
text,65535 个字符(2 的 16 次方)
mediumtext,16777215 字符(2 的 24 次方)
longtext,(2 的 32 次方)
enum(value,value,…)占 1/2 个字节 最多可以有 65535 个成员 枚举型
set(value,value,…)占 1/2/3/4/8 个字节，最多可以有 64 个成员

数据库基本用法

登录：mysql -u账号 -p密码 -h 登录的ip
进入一个数据库： use 数据库名；
登录时直接进入数据: mysql -u账号 -p密码 数据库名
禁止预读表信息: mysql -u账号 -p密码 -A
查看帮助: help show;
查看某个命令帮助: help 命令;

增

创建一个数据库: create database 数据库名 # 如果创建数据库为保留字段需要加`<br />创建数据库判断: create database if not exists 数据库 # 如果数据库不存在则创建<br />创建表: create table表名`
表中添加新字段: alter table 表名 add 新字段 数据类型;
指定位置添加字段: alter table 表名 add 新字段 数据类型 位置;
#位置信息: first(添加到第一个) after字段(在哪个字段之后)
插入数据: insert into 表名 values(字段1，字段2),(字段1，字段2);

删

删除数据库：drop database 数据库名
删除整个表：drop table 表名;
删除判断: drop database if exists 数据库名 # 如果数据库存在则删除
删除表中字段: alter table 表名 drop 字段名;
删除表中的数据: delete from 表名 where 匹配条件;

改

修改表的名字: alter table 旧表名 rename 新表名
修改表中的字段数据类型: alter table 表名 modify 修改的字段 修改后数据类型
修改表中的字段名字: alter table 表名 change 原字段 新字段 新字段数据类型
修改表中数据: update 表名 set 字段名=修改数据 where 匹配条件

查

查询自己所在数据库: select database();
查询当前日期和时间: select now();
查询当前日期: select curdate();
查询当前时间: select curtime();
查询当前版本: select version();
查询当前登录用户: select user();
查询所有变量: show variables;
查询全局变量: show global variables;
查看数据库中表: show tables;
查看表的数据类型: desc 表名；
查询表中所有数据: select字段 from 表名;
查询其他数据库中的表: select 字段 from 数据库.表名;
匹配查询: select 字段1,字段2 from 表名 where 匹配条件;
去重查询: select distinct 字段 from 表名;
逻辑与: select 字段 from 表名 where 匹配条件 and 匹配条件;
逻辑或： select 字段 from 表名 where 匹配条件 or 匹配条件;
逻辑组合查询: select 字段 from 表名 where 匹配条件 and (匹配条件 or 匹配条件);
查询区别大小写: select 字段 from 表名 where binary 匹配条件;
查询排序(从小到大): select 字段 from 表名 where 匹配条件 order by 字段;
查询排序(从大到小): select 字段 from 表名 where 匹配条件 order by 字段 desc

like模糊查询

mysql的通配符:
% #允许匹配任何字符串的零个或多个字符
_ # 允许匹配任何单个字符
使用方法: select 字段 from 表名 where 字段 like’匹配数据’；
使用方法2: select 字段 from 表名 where 字段 not like’匹配数据’；

导入导出数据库

导入数据库:
方法1:
创建数据库: mysql -uroot -p密码 -e ‘create database 数据库名;’
导入数据: mysql-uroot -p密码 数据库名 < 备份文件路径
方法2：
登录到数据库: mysql -uroot -p密码
创建数据库: create database 数据库;
进入到数据库: use 数据库;
导入数据: source 备份文件路径
导出数据库：
mysqldump -uroot -p密码 数据库 > 备份文件路径
-A # 导出所有数据库 -B # 导出单个数据库包括建库命令
导出单个表:
Mysqldump -uroot -p密码 -B 数据库名 –tables 表名 > 备份文件路径
导出查询结果:

1. 修改配置文件: vim /etc/my.cnf

secure-file-priv=”/ “ # 让数据库知道根目录

1. 重启数据库: systemctl restart mysqld
2. 登录数据库: mysql -uroot -p密码
3. 查询语句导出: select 字段 into outfile ‘保存路径’ from 表名

   逻辑运算符

   and 且 or 或 not 非
   且: select 字段 from 表名 where 字段=匹配条件1 and 匹配条件2
   或: select 字段 from 表名 where 字段=匹配条件1 or 匹配条件2
   非: select 字段 from 表名 where 字段=not 匹配条件

   算术运算符

   = 等于 <> 不等于 > 大于 < 小于 >= 大于等于 <= 小于等于
   in 运算法
   语法: select 字段 from 表名 where 字段 in (数据1，数据2)
   ont in 运算法: 和in语法一致，取反

   排序

   order by 字段 asc # 升序
   order by 字段 desc # 降序、
   升序: select 字段 from 表名 where字段=匹配条件 order by 字段 asc, 字段2 asc;
   降序: select 字段 from 表名 where 字段=匹配条件 order by 字段 desc, 字段2 desc;

   范围运算

   between … and … # 在范围之间 not between..and.. # 不在范围之间
   在范围之间: select 字段 from 表名 where 字段 between 范围1 and 范围2
   不在范围之间: select 字段 from 表名 where 字段 not between 范围1 and 范围2

   子查询（嵌套查询）

   语法:
   select 字段 from 表名 where 字段=(select 字段 from 表名 where 字段=匹配条件)
   多行子查询: all 表示小于子查询中返回全部值中的最小值
   语法: select 字段 from 表名，where 字段=all(select 字段 from 表名 where 字段=匹配条件)

   limit 限定显示查询结果

   LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0（而不是 1）。
   语法: select * from table limit m,n # 限定结果返回 从第m数开始返回n个

连接查询

inner join(等值连接，内连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
等值连接语法:
select 表A字段,表B字段 from 表A inner join 表B where 表A字段=表B字段;
select 表A字段,表B字段 from 表A,表B where 表A字段=表B字段
左连接语法:
select 表A字段,表B字段 from 表A left join 表B on 表A字段=表B字段
右连接语法:
select 表A字段,表B字段 from 表A right join 表B on 表A字段=表B字段

聚合函数

求和函数语法:
select sum(字段) from 表名;
平均值语法:
select avg(字段) from 表名;
字段最大值:
select max(字段) from 表名;
字段最小值:
select min(字段) from 表名;
统计记录数:
select count(字段) from 表名 where 字段 匹配条件;

算术运算

• • • /
      update 表名 set 字段=字段 算术运算 where 字段 匹配条件

字符串函数

截取字段语法：
select substr(字段,start,len) from 表名
# 从 start 开始，截取 len 长.start 从 1 开始算起
拼接字段语法:
select concat(字段1,字段2) from 表名

大小写转换

转换成大写语法:
select upper(字段) from 表名
转换成小写语法:
select lower(字段) from 表名

字段约束符

null 和 not null 语法: # 设定字段是否允许为空和非空
create table 表名 (字段 数据类型 not null)
create table 表名 (字段 数据类型 null)
default 语法： # 给字段插入默认值
create table 表名 (字段 数据类型 default(默认值))
auto_increment语法： # 自增长,唯一性(只能int类型使用)
create table 表名 (字段 数据类型 auto_increment)
清除表中所有数据(不会清空自增长): delete from 表名;
清楚表中所有数据并清空自增长: truncate table 表名

索引

普通索引语法: (不具备唯一性，只是加快查询速度，允许字段为null)
方法1: # 创建表时创建
create table 表名 (字段 数据类型,字段 数据类型, index 索引名 (字段名))
方法2： # 创建表后添加
alter table 表名 add index 索引名字 字段
删除索引: alter table 表名 drop key 索引名字
唯一索引语法： （索引列只能出现一次，必须唯一，允许字段为null）
方法1: # 创建表时创建
create table 表名( 字段 数据类型,字段 数据类型, unique key 索引名字 (字段))
方法2： # 创建表后添加,如果数据有重复则无法添加
alter table 表名 add unique key 索引名 字段
主键索引语法: (主键索引只能出现一次，必须唯一，不允许字段为null)
方法一: # 创建表时创建
create table 表名(字段 数据类型 not null primary key(字段名))
方法二: # 创建表后添加(不推荐),如果数据不唯一则报错
alter table 表名 change 字段 数据类型 not null primary key
# 如果有字段有自增长，需要先取消自增长
复合索引语法: # 索引包含一个、两个或更多列，则是符合索引
create table 表名(字段 数据类型,字段 数据类型，prmary key(字段1,字段2))
全文索引语法: # 全文索引只能用于varchar text
方法一： # 创建表时创建
create table 表名 (字段 数据类型,字段 数据类型 fulltext key 索引名 (字段))
方法二: # 创建表后添加
alter table 表名 add fulltext 索引名 (字段)

外键约束

创建外键约束语法:
create table 表名 (字段 数据类型,字段 数据类型,foreign key order_f_key(本表字段) referenes 其他表名(字段) on delete cascade on update cascade)
注意：on update cascade 是级联更新的意思，on delete cascade 是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。
删除外键语法:
show create 表名 # 查询外键名字
alter table 表名 drop foreign key 外键名;

视图

创建查询视图语法:
create view 视图名字 as select 字段 from 表名
修改视图中语法:
alter view 视图名字 as select 字段 from 表名
查看所有视图:
show table status where comment=’view’\G
删除视图
drop view 视图名

存储过程

创建存储过程：
create procedure 过程名(参数1，参数2) begin
sql语句
end
修改结束符: delimiter 结束符
调用存储过程: call 存储过程名(传参1，传参2)
in参数: # 读取外部变量，有效范围仅限存储过程:
create procedur 过程名( in 变量名 数据类型) begin
sql语句
end
out参数: # 不读取外部变量，存储过程执行后保留新值
create procedure 过程名(out 变量名 数据类型)
sql语句
end
inout参数: # 读取外部变量，存储过程执行后，保留新值
create procedure 过程名(inout 变量名 数据类型)
sql语句
end
declare 进行变量定义:
create procedure 过程名() begin
declare 变量名 数据类型
sql 语句
end
存储过程注释：
“—“：单行注释 “/…../”:一般用于多行注释
查看存储过程内容： show create procedure 过程名
查看所有存储过程: show procedure status
删除存储过程: drop procedure 过程名

存储过程中流程控制语句

if条件语句:
create procedure 过程名() begin
if 判断条件 then
sql语句
else
sql 语句
end if;
end
case选择语句:
create procedure 过程名() begin
case 选择语句
when 0 then
sql语句
when 1 then
sql语句
else
sql语句
end case
end
while循环语句: # 执行前检查结果
create procedure 过程名() begin
while 判断条件
sql语句
end while
end
repeat循环语句: # 执行后检查结果
create procedure 过程名() begin
repeat
sql语句
until 判断条件
end repeat
end
loop 循环语句: # 不需要初始条件,不需要结束条件，手动结束循环
create procedure 过程名() begin
loop
sql语句
end loop
end

触发器

创建触发器语法:
create trigger 触发器名 触发时机 触发的动作 on 表名 for each row 触发器状态
触发的时机： before /after 在执行动作之前还是之后
触发的动作：指的激发触发程序的语句类型
each row：操作每一行我都监控着触发器创建语法四素：

1. 监视地点(table)
2. 监视事件(insert/update/delete)
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

查看单个触发器: show create trigger 触发器名
查看所有触发器: show trigger
删除触发器: drop trigger 触发器名

事务

MYSQL 中只有 INNODB 和 BDB 类型的数据表才能支持事务处理，其他的类型都不支持！
设置事务不自动提交: set autocommit=0
事务语法:
start transaction;
sql语句
commit
回滚事务: rollback （没有commit提交才能使用）

字符集相关

1.如果处理各种各样的文字，发布到不同语言国家地区，应选 Unicode 字符集，对 MySQL 来说就是 UTF-8(每个汉字三个字节)
2.如果只是需要支持中文，并且数据量很大，性能要求也高，可选 GBK（定长，每个汉字占双字节，英文也占双字节），如果是大量运算，比较排序等，定长字符集更快，性能也高
3.处理移动互联网业务，可能需要使用 utf8mb4 字符集，比如一些奇怪的文字。火星文
查看所有字符集：show character set
查看当前字符集: show variables like ‘character_set%’
修改字符集: vim /etc/my.cnf 在下面加入character-set-server=utf8
修改已有数据库的字符集:
1.先查看数据是什么字符集: show create table 表名

2.导出表结构: mysqldump -uroot -p密码 —default-character-set=当前字符集 -d 数据库 > 保存文件路径
3.修改文件: vim 导出文件 在vim命令模式下: %s/字符集/utf8/g
4.导出所有数据: mysqldump -u root -p123456 —quick —no-create-info —extended-insert —default-character-set=latin1 数据库 > 保存文件路径
5.打开数据保存文件:vim 保存文件找到SET NAME latin1 修改成SET NAME utf8
6.删除数据库并重新创建：
drop database 数据库
create database 数据库 default charset utf8
8.导入表结构文件: mysql -uroot -p密码 数据库 < 表结构文件
9.导入数据文件: mysql -uroot -p密码 数据库 < 数据文件

mysql修复工具

mysqlcheck –-help # 帮助选项
-c # 检查表
-r # 修复表
-a # 分析表
-o # 优化表
-A # 所有数据库
语法: mysqlcheck -u账号 -p密码 选项 数据库 表名

mysql备份工具

1. mysqldump (MyISAM，innodb)

1.备份语法：mysqldump -u账号 -p密码 备份数据库 > 保存路径
-A # 导出所有数据
-B # 导出数据库包括建库语句
-d # 导出数据库表结构
-t # 只导出数据库中数据
2.恢复语法: mysql -u账号 -p密码 指定数据库 < 备份文件
3.恢复语法2：create databases 数据库
use 数据库
source 备份文件路径

1. mysqlhotcopy （5.5版本以下，只支持 MyISAM 引擎）
2. 安装依赖包：yum install perl-DBD* -y
3. 语法:mysqlhotcopy -u账号 -p密码 数据库 文件保存路径 # 备份某个数据库

mysqlhotcopy -u 账号 -p 密码 数据库./要备份的表名/ 文件保存路径 #备份单表
—allowold #不覆盖以前备份的文件
—addtodest #属于增量备份
—noindices #不备份索引文件
—debug #启用调试输出
—regexp= #使用正则表达式
—checkpoint= #插入检查点条目
—flushlog #所有表锁定后刷新日志
—resetmaster #一旦锁表重置 binlog 文件
—resetslave #一旦锁表重置 master.info文件

1. xtrabackup 5.7版本数据库需要最新2.4.9版本
2. 下载安装: yum -y install percona-xtrabackup

备注: 需要在/etc/my.cnf配置中指定数据目录 datadir=xxx

1. 使用语法:

innobackupex —user=账号 —password=密码 保存路径 # 创建全备
—no-timestamp，指定了这个选项备份会直接备份在 BACKUP-DIR，不再创建时间戳文件夹。
—default-file，指定配置文件，用来配置 innobackupex 的选项
innobackupex —user=root —password=123456 —no-timestamp /tmp/db_backup/full(使用—no-timestamp 时，后面的这个 full 目录必须跟上且不能提前自己建立，它由 innobackupex自动建立，否则会报错 innobackupex: Error: Failed to create backup directory)

1. 还原语法: innobackupex —copy-back 备份目录 ，还原后需要重新给文件授权，否则数据库无法正常启动，chown -R mysql:mysql /var/lib/mysql
2. innobackup的增量备份
3. 首先创建一个全备份: innobackupex — user 账号 —password 密码 备份路径
4. 创建增量备份: innobackupex —user=root —password=123456 –incremental 指定备份路径 —incremental-basedir=指定在哪个全备基础上

5. 查看日志序列号: cat /备份目录/xtrabackup_checkpoints

6. 指定lsn号来代替增量备份:

innobackupex —user=root —password=123456 –incremental 指定备份路径 —incremental-lsn=指定lsn号

1. 还原:还原首先还原全备份，然后按照从早到晚的日期来进行增量备份恢复

innobackupex —apply-log —redo-only 全备目录
innobackupex —apply-log —redo-only 全备目录 —incremental-dir=增量备份
最后一步没有—redo-only 参数,最终数据在全备目录下

1. 还原: innobackupex —apply-log 全备目录
2. 修改权限: chown -R mysql:mysql /var/lib/mysql/

   mysql用户管理

   创建用户: create user 用户名@登录主机 IDENTIFIED BY 密码;
   授权: grant 权限 on 数据库.* to 用户名@登录主机 identified by 密码
   刷新权限: flush privileges
   删除用户: delete from mysql.user where user=用户名 and host=登录主机
   忘记密码: vim /etc/my.cnf 加入 skip-grant-tables
   登录后修改: update mysql.user set authentication_string=password(密码) where user=root