MySQL数据库

MySQL数据库

什么是数据库?

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
    1.数据以表格的形式出现
    2.每行为各种记录名称
    3.每列为记录名称所对应的数据域
    4.许多的行和列组成一张表单
    5.若干的表单组成database

RDBMS数据

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• **行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• **外键：**外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

表格的概念

• 表头(header): 每一列的名称;
• 列(col): 具有相同数据类型的数据的集合;
• 行(row): 每一行用来描述某条记录的具体信息;
• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
• 键(key): 键的值在当前列中具有唯一性。

---

Mariadb使用

安装mariadb

yum -y isntall mariadb mariadb-server

启动

systemctl start mariadb
systemctl enable mariadb

---

Mariadb密码设置

设置数据库密码

mysqladmin -uroot -p'123456'		#初始化数据库在终端设置密码
set password for 'root'@'localhost'=password('123456')	＃进入数据设置root密码

查看数据库

show databases;
show create database my；

创建数据库

create database my;
create database my default character set utf8 collate utf8-general_ci;

修改数库

alter database my default chacater set utf8 collate utf8-general_cl;

删除数据库

drop database my;

进入数据库

ues my;

查看表

show tables;
show create table test;
desc test;

创建表

create table test (
id int(10) primary key auto_increment,
name char(30),
age int(10) comment '年龄');

创建表常用数据类型

CHARACTER(n)	　	＃字符/字符串。固定长度 n。
VARCHAR(n) 			 #字符/字符串。可变长度。最大长度 n。
BINARY(n)			 #二进制串。固定长度 n。
BOOLEAN				 #存储 TRUE 或 FALSE 值
INTEGER(p)			 #整数值（没有小数点）。精度 p。
SMALLINT			 #整数值（没有小数点）。精度 5。
INTEGER				 #整数值（没有小数点）。精度 10。
BIGINT				 #整数值（没有小数点）。精度 19。
DECIMAL(p,s)		 #精确数值，精度 p，小数点后位数 s。
FLOAT(p)			 #近似数值，尾数精度 p。
REAL				 #近似数值，尾数精度 7。
FLOAT			 	 #近似数值，尾数精度 16。
DATE				 #存储年、月、日的值
TIME				 #存储小时、分、秒的值
TIMESTAMP			 ＃存储年、月、日、小时、分、秒的值。
INTERVAL			 ＃由一些整数字段组成，代表一段时间，取决于区间的类型
ARRAY				 ＃元素的固定长度的有序集合
MULTISET			 ＃元素的可变长度的无序集合

修改表

alter table test modify name varchar(30);		#修改字段类型
alter table test change name NAME varchar(30);	#修改字段和数据类型
alter table test rename to test1;				＃修改表名		

删除表

drop table test1;

查看数据

select * from test1;
select id,name,age from test1;
select id,name,age from where age=17;
select id,name,age from where age=17 order by id;
select id,name,age from where age=17 group by 17;

【列表特定行/列】
mysql> SELECT * FROM pet WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
！！！AND并且 OR可以混合，但 AND优先级高于 OR。

【顺序排列】
mysql> SELECT DISTINCT owner FROM pet;						#检索列中重复的出现一次
mysql> SELECT name, birth FROM pet ORDER BY birth;			#排序
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;		#降序
mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat'; 

插入数据

insert into test1 (name, age) values
('zhangsan', 15)
('lisi', 20)
('wangwu', 30);           

修改数据

update test1 set name='zhaoliu' where name='lisi';

删除数据

delete from test1 where name='wangwu';

MySQL索引

五种索引：

• 普通索引
• 唯一性索引
• 主键索引
• 复合索引
• 全文索引

索引的优缺点：

优点：

• 由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会很快，查询时间会减少

缺点：

• 索引是以文件存储的。如果索引过多，占磁盘空间较大。而且他影响： insert ,update ,delete 执行时间。
• 索引中数据必须与数据表数据同步：如果索引过多，当表中数据更新的时候后，索引也要同步更新，这就降低了效率。

查询索引

desc mytable
show index from mytable\G

删除索引

alter table mytable drop key index_age;

（添加/删除）索引(key/index)

创建表时直接指定
    create table mytable (
    id int not null,
    name varchar(16) not null,
    index index_name(rname)			#普通索引
    primary key(id),				#主键索引
    unique index index_name(name),	#唯一索引
    index index_id_name(id,name),	#复合索引
    fulltext index index_name(name) #全文索引
    );
    
修改表添加索引
	alter table mytable add index index_id(id);				#添加普通索引
	alter table mytable add primary key(id);				#添加主键索引
	alter table mytable add unique inndex index_id(id)  	#添加唯一索引
	alter table mytable add index index_id_name(id,name) 	#添加复合索引
	alter table mytable add fulltest index index_name(name) #添加全文索引
	
修改表删除索引
	drop index index_id;
	alter table mytable drop index index_id;		#删除索引
	alter table mytable drop primary key			#删除主键索引
	alter table mytable drop inndex index_id		#删除唯一索引
	alter table mytable drop index index_id_name	#删除复合索引
	alter table mytable drop index index_name		#删除全文索引

事务

主要用于处理操作量大，复杂度高的数据

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
• 事务用来管理 insert,update,delete 语句

事务特点

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

#####　事务控制语句：

• BEGIN或START TRANSACTION；显式地开启一个事务；
• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

事务处理

• begin #开始一个事务
• rollback #事务回滚
• commit #事务确认
• set autocommit=0 #禁止自动提交
• set autocommit=1 #开启自动提交

忘记root密码

systemctl stop mariadb

ps aux | grep mariadb

kill mariadb

方式一：

	mysqld_safe --skip-grant-table&

方式二：

	vi /etc/my.conf

	--skip-grant-table
	
!!!!!!修改完密码之后还要注释掉者配置文件里skip-grant-table

mysql

update mysql.user set password=password(‘123456’) where user=‘root’;

update mysql.user set password=password(‘123456’) where user=‘root’ and host=‘localhost’; #修改本地一个用户

flush privileges;

用户管理

查询

show grant for 'root'@'localhost'\G
select * from mysql.user\G
select user,password,host from mysql.user\G

创建用户

• 方式一

create user 'alice'@'localhsot';                 #'%'通配符作为主机部分
create user 'alice1'@'%' identified by '123456';			
grant all privileges on *.* to 'alice2'@'localhost' with grant option

• 方式二：

MariaDB [(none)]> insert into mysql.user(user,host,password) values('alice','localhost',password('1234'));

修改用户

rename user 'alice'@'%' to 'tom'@'%';

修改密码

set password=password('123456');				#修改root密码
set password for 'tom'@'%'=password('123456');	#修改用户密码

mysql> UPDATE mysql.user SET Password = PASSWORD('new_password') where User = 'root';  #设置匿名用户密码
mysql> flush privileges;	#服务器重新读取授权表

#####　删除用户

drop user 'tom'@'localhost';

mysql> DROP USER ''@'localhost';	#删除匿名用户
mysql> DROP USER ''@'host_name';	#删除匿名用户

mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';	#删除测试数据库的任何用户访问权限
mysql> FLUSH PRIVILEGES;

授权管理

查看用户权限

show grants from 'alice'@'localhost';

新建一个tom用户授权全部权限

grant all on *.* to 'tom'@'%' identified by '123456';	#授权并创建用户（即使用户不存在，授权之后会自动创建）

#####　指定部分授权

grant insert,update,delete,select on *.* to 'tom'@'%';

取消授权

revoke all on *.* from 'tom'@'%';

日志管理

用户mysql错误排查错误的重要依据

查看日志

• 默认路径/var/log/mariadb/mariadb.log

配置错误的日志路径

vi /etc/my.conf
log_error=/var/log/mariadb/mariadb.log

通用日志

查看日志的状态

mysql	#进入数据库
show variables like 'general%';

配置日志

vi /etc/my.conf		#数据库的配置文件

general_log=ON
general_log_file=/var/log/mariadb/mysql.log

二进制日志

二进制日志也叫bin.log，记录了mysql的所有执行的语句，可用bin.log对数据库进行恢复
默认路径/var/lib/mysql

查看是否开启

mysql
show variables like 'log%';

慢日志

查询是否开启

mysql
show variables like '%slow%';

开启慢日志

vi /etc/my.conf

slow_query_log=ON
slow_query_log_file=mysql_slow_query.log
long_query_time=1

###　备份与还原

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。

• 物理备份：对数据库文件的复制备份

  • 冷备份：关闭数据库执行 (用的比较少)
  • 热备份：开启数据库执行 （常用）

• 逻辑备份：对数据库的组件进行备份

  • 完整备份：每次备份都对数据库进行完整的备份
  • 差异备份：每次备份都只备份从上一次完整备份后修改过的数据
  • 增量备份：每次备份都只备份从上一次完整备份，或者增量备份后增长的数据

完整备份文件的方式

数据库的默认路径：/var/lib/mysql

备份

cd /var/lib/mysql
tar cf mysql_backup.tar.gz /var/lib/mysql/test		#使用tar包的形式直接打包文件

还原

tar xf mysql_backup.tar.gz -C /var/lib/mysql/test	#解压到数据目录下

mysqldump工具

使用命令行进行数据库，数据，表结构的备份

备份单个数据库

mysqldump -uroot -p alice > alice.sql	#备份后缀必须.sql结尾

备份多个数据库

mysqldump -uroot -p --database alice mydb tom > amt.sql

备份所有的数据库

mysqlsump -uroot -p --all-database > base.sql

备份表结构

mysqldump -uroot -p -desc alice t1 > alice_ti.sql

还原

进入数据库source恢复

如果回复单一数据库必须先创建丢失的数据库名和原来的一样，进入数据在执行恢复
create database alice;
source /root/alice.sql			＃数据库支持备份存放的路径

mysql

不进入数据库使用mysql命令恢复

create database alice
mysql -uroot -p alice < /root/alice.sql

增量备份

#####　开启binlog日志

vi /etc/my.conf
[mysqld]
log-bin=mysql-bin

增量备份

刷新二进制文件，也就是binlog文件，使用mysqlbinlog查询二进制文件，备份二进制文件即可

mysqladmin -uroot -p flush-logs

恢复增量值

[root@server ~]# cd /var/lib/mysql/mysql-bin.000001				#（时间节点或位置节点）默认路径

[root@server ~]# mysqlbinlog mysql-bin.000001

基于时间点的恢复（#181113 17:16:01 ）

mysqlbinlog  mysqlbin.000001	#查看二进制的配置文件的内容，找到要恢复的时间

mysqlbinlog  mysqlbin.000001 --start-datetime='2019-02-13 17:15:50' |mysql -uroot -p
mysqlbinlog  mysqlbin.000001 --stop-datetime='2019-02-13 17:15:50'|mysql -uroot -p

实例：
mysqlbinlog  mysqlbin.000026 --start-datetime='2018-11-13 17:15:50' --stop-datetime='2018-11-13 17:16:25' -r /opt/time.binlog

基于位置的恢复（#at 523）

mysqlbinlog mysql-bin.00001		#先查看二进制的配置文件的内容，找到要恢复的位置

mysqlbinlog mysql-bin.00001 start-position='154' |mysql -uroot -p
mysqlbinlog mysql-bin.00001 start-position='721' |mysql -uroot -p

实例：
mysqlbinlog mysql-bin.00001　--start-position=154 --stop-position=721  > /u01/log/mysql/07.sql

Mysql引擎

mysql工作形势为大部分组件都采用插件的形式，即需要什么就可以添加什么，其中mysql的存储引擎也是如此，得力于如此独特的特性，mysql当中的引擎也可以使用插件的方式进行更换。

引擎种类

• innodb

  • 支持事务
  • 行级锁定
  • 具备缓存性
  • 支持分区表空间
  • 支出外键，但不主持全文索引

• myisam

  • 不支持事务
  • 表级锁，数据更新锁定整个表
  • 数据库在读写过程中互相阻塞
  • 可以对数据读取或写入速度快占用资源少
  • 不支持外键索引，只支持全文索引

###　配置数据的引擎

• 查看数据库表使用的引擎
• mariadb默认数据库的引擎是innodb,下面以修改myisam引擎为示例：

show table status from mydb where name='test1'\G

show engines;

show create table test1;

• 创建表指定引擎

create table test(
id int,
name char)engine=myisam;

• 修改已存在表的引擎

alter table test1 engine=myisam;

• 命令行修改整个数据库里的所有表的引擎

mysql_convert_table_format -uroot -p -S /var/lib/mysql/mysql.sock -e myisam mydb

• 修改默认引擎的配置文件

vi /etc/my.cnf
[mysqld]
deafault-storage-engine=(innodb或者myisam)

！！！总结

１、数据库的引擎是针对数据库里面表的
２、修改数据库配置文件，重启服务，对之前已经存在的数据的引擎是不会改变的，如果想改变已存在只能手动去修改。