MySQL存储引擎及数据处理

一、MySQL存储引擎的配置
MySQL存储引擎：MySQL服务软件自带的功能程序，处理表的处理器
不同存储引擎有不同功能和数据存储方式

MySQL服务体系结构
管理工具：命令
连接池：检查服务器有无空闲进程、cpu、内存供客户端的连接，有就进入mysql>
SQL接口：在输入select、insert等命令，通过mysqld服务去执行
分析器
优化器
查询缓存：缓存曾经查找过的数据，存储空间从系统的物理内存中来
客户端向服务器发起请求—>服务器先在缓存查数据—>如果有直接从缓存给
客户端—>没有就从硬盘找—>先存到缓存—>给客户端—>如果有另一个客
户端查询同样数据—>直接从缓存给
存储引擎
文件系统

列出可用的存储引擎类型
mysql> show engines;
常用：
MyISAM：支持表级锁，不支持事务、事务回滚、外键
表名.MYI：存储表里字段的排列信息 表名.MYD：存储表里的数据 表名.frm
InnoDB（默认）：支持事务、事务回滚、外键、行级锁
表名.ibd：存储表里数据和索引信息 表名.frm
事务日志文件：ibdata1、ib_logfile0、ib_logfile1
（在启动数据库服务自动创建出来的，记录所有的innodb存储引擎的表操作）
表名.frm：存储表的表结构

设置默认存储引擎

# vim /etc/my.cnf 
在[mysqld]下面添加default-storage-engine=myisam 
# systemctl restart mysqld 
mysql> show engines; 
mysql> create database db3; 
mysql> use db3; 
mysql> create table t1(id int); 

另开终端验证：

# cd /var/lib/mysql/db3 
# ls 
mysql> create table t2(name char(3))engine=innodb; 
另开终端验证: 
# cd /var/lib/mysql/db3 
# ls 
mysql> alter table t1 engine=innodb;      //可以修改，但是有数据的时候尽量不用MySQL锁机制 

锁粒度
表级锁：一次直接对整张表进行加锁
行级锁：只锁定某一行
页级锁：对整个页面（MySQL管理数据的基本存储单元）进行加锁
锁类型
读锁（共享锁）：支持并发锁
写锁（互斥锁、排它锁）：是独占锁，上锁期间其他线程不能读表或写表
查看当前锁状态

mysql> show status like 'table_lock%'; 
Table_locks_immediate：产生表级锁定的次数； 
Table_locks_waited：出现表级锁定争用而发生等待的次数； 

MyISAM
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
InnoDB
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE
用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作
但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁

事务特性（ACID）
原子性：事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败
一致性：事务操作的前后，表中的记录没有变化
隔离性：事务操作是相互隔离不受影响的
持久性：数据一旦提交，不可改变，永久改变表数据

事务：从连接数据库服务开始到断开的过程
事务回滚：当在一次访问过程中，如果任意一步没有执行成功，都会恢复到之前的所有操作
例:（两个终端，两个会话）

# mysql -u root -p123456 
mysql> desc t2; 
mysql> show variables like "autocommit";        //终端1 
mysql> set autocommit=off; 
 
# mysql -u root -p123456 
mysql> show variables like "autocommit";        //终端2 
 
mysql> insert into t2 values("bob"); 
mysql> insert into t2 values("bob");              //终端1 
mysql> select * from t2; 
 
mysql> use db3; 
mysql> select * from t2;                          //终端2 
 
mysql> commit;                                  //终端1 
 
mysql> select * from t2;                          //终端2 
 
mysql> delete from t2; 
mysql> select * from t2;                          //终端1 
 
mysql> select * from t2;                          //终端2 
 
 
mysql> rollback; 
mysql> select * from t2;                           //终端1 

查询访问多的表适合使用myisam存储引擎，节省系统资源
写访问多的表适合使用innodb存储引擎，并发访问量大

二、数据导入/导出
1.数据导入：把文件的内容保存到表里
功能：批量把数据存储到数据库的表里
基本用法：
Load data infile “目录名/文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”;
注意：字段分隔符要与文件内的一致；指定导入文件的绝对路径；导入数据的表字段类型
要与文件字段匹配；禁用Selinux保护机制
例：

mysql> show variables like "secure_file_priv";         
                       //默认使用目录：/var/lib/mysql-files/ 
mysql> create table user( 
    -> name char(30), 
    -> password char(1), 
    -> uid int, 
    -> gid int, 
    -> comment char(150), 
    -> homedir char(150), 
    -> shell char(50) 
    -> ); 
mysql> desc user; 
mysql> system cp /etc/passwd /var/lib/mysql-files/ 
mysql> system ls /var/lib/mysql-files/              //在mysql命令行执行系统命令 
mysql> load data infile "/var/lib/mysql-files/passwd" 
    -> into table db3.user 
-> fields terminated by ":" 
-> lines terminated by "\n"; 
mysql> select * from user; 
mysql> alter table user 
-> add                     
-> id int primary key auto_increment first;  
//要在建完表才添加，不然数据的表字段类型不与文件字段匹配 
mysql> select * from user; 

修改搜索路径：

# vim /etc/my.cnf 
在[mysqld]下面添加secure_file_priv="/mydata" 
# mkdir /mydata 
# chown mysql /mydata 
# systemctl restart mysqld 
# mysql -u root -p123456 
mysql> show variables like "secure_file_priv"; 

建在/root会出问题，尽量不要把目录建在管理员的家目录（ls -ld /root ls -ld /）

数据导出：把表记录存储到系统文件里面
基本用法：
SQL查询 into outfile “目录名/文件名”
Fields terminated by “分隔符” //不打默认空格隔开
Lines terminated by “\n”; //不打默认一行一条记录
注意：导出的内容由SQL查询语句决定；导出的是表中的记录，不包括字段名；禁用Selinux
例：

mysql> select * from db3.user into outfile 
    -> "/mydata/db3_user.txt"; 
mysql> system ls /mydata 
mysql> system cat -b /mydata/db3_user.txt 
              （cat -b与cat -n类似，只不过对于空白行不编号）

管理表记录
增加表记录
1）添加一条记录，给所有字段赋值：
Insert into 表名 values(字段值列表);
例：

mysql> insert into db3.user values( 
    ->42,"bob","x",3001,3001,"test user","/home/bob","/bin/bash" 
    ->); 

2）添加N条记录，给所有字段赋值；
Insert into 表名 values
(字段值列表)，
…
…;

添加一条记录，给指定字段赋值：
Insert into 表名(字段名列表) values(字段值列表);
例：
mysql> insert into db3.user(name,uid,gid) values(“tom”,4001,4002);
添加N条记录，给指定字段赋值：
Insert into 表名(字段名列表) values
(字段值列表),
…
…;

查询表记录
1）select 字段1，… …，字段N from 表名;
例：
mysql> select * from db3.user;

2）select 字段1，… …，字段N from 表名 where 条件表达式；
例：
mysql> select name,uid from db3.user where uid<=10;

注意：使用*可以匹配所有字段；指定表名时，可采用 库名.表名 的形式

更新表记录
更新表内所有记录
Update 表名 set 字段1=字段1值,字段2=字段2值,字段N=字段N值;
例：

mysql> update db3.user 
    -> set password="A",comment="student"; 
mysql> select * from db3.user; 

2）只更新符合条件的记录
Update 表名 set 字段1=字段1值,字段2=字段2值,字段N=字段N值 where 条件表达式;
例：

mysql> update db3.user 
    -> set password="x",comment="root" 
    -> where id=1; 
mysql> select * from db3.user where id=1; 

删除表记录
仅删除符合条件的记录
Delete from 表名 where 条件表达式;
例：

mysql> delete from db3.user where shell is null; 
mysql> select * from db3.user; 

删除所有表记录
Delete from 表名;

四、查询及匹配条件
基本匹配条件
1.数值比较
=、>、>=、<、<=、!=
例：

mysql> select name from db3.user where uid=500; 
mysql> select name from db3.user where id=1; 
mysql> select name from db3.user where id<=10; 
mysql> select name,uid,gid from db3.user where uid=gid; 
mysql> select name,uid,gid from db3.user where uid!=gid; 
mysql> select name,uid,gid from db3.user where 1<2; 

字符比较/匹配空/非空
=、!=、is null、is not null
例：

mysql> select name from db3.user where uid=500; 
mysql> select name,shell from db3.user where shell!="/bin/bash"; 
mysql> select id,name,uid from db3.user where uid is null; 
mysql> select id,name,uid from db3.user where uid is not null; 

逻辑匹配
Or—逻辑或、and—逻辑与、!或not—逻辑非、()—提高优先级
例：

mysql> select name,uid,shell from db3.user 
-> where name="root" and uid=1 and shell="/bin/bash"; 
mysql> select name,uid,shell from db3.user 
-> where name="root" or uid=1 or shell="/bin/bash"; 

范围内匹配/去重显示
In (值列表)—在…里、not in (值列表)—不在…里、
betwenn 数字1 and 数字2—在…之间、distinct—去重显示
例：

mysql> select name from db3.user  
    -> where name in ("daemo","root","adm","bob"); 
mysql> select name,uid from db3.user 
    -> where uid in (1,7,3,40,50); 
mysql> select name,shell from db3.user 
    -> where shell not in("/bin/bash","/sbin/nologin"); 
mysql> select * from db3.user 
-> where id between 10 and 20; 
mysql> select distinct shell from db3.user; 

高级匹配条件
模糊查询
Where 字段名 like ‘通配符’
_匹配单个字符、%匹配0~N个字符
例：

mysql> select name from db3.user where name like "%a%"; 
mysql> select name from db3.user where name like "a%"; 
mysql> select name from db3.user where name like "a%a"; 
mysql> select name from db3.user where name like "___"; 
mysql> select name from db3.user where name like "_a_"; 
mysql> select name from db3.user where name like "__%__"; 

正则表达式
Where 字段名 regexp ‘正则表达式’
^ $ . [] * |
例：

mysql> select name from db3.user where name regexp '^a|^t'; 
mysql> select name,uid from db3.user where uid regexp '^....$'; 
mysql> select name,uid from db3.user where name regexp '^a.*t$'; 

四则运算
+、-、*、/、%
例：

mysql> select name,uid,gid from db3.user; 
mysql> update db3.user set uid=uid+1,gid=gid-1; 
mysql> select name,uid,gid from db3.user; 
mysql> alter table db3.user  
    -> add age int default 18 after name; 
mysql> select name,age,2018-age start from db3.user; 
mysql> select name,uid,gid,(uid+gid)/2 pjz from db3.user where name="bin"; 
mysql> select name,uid from db3.user 
    -> where name="root" and uid=1 or uid=2; 
mysql> select name,uid from db3.user where name="root" and (uid=1 or uid=2); 

()—提高优先级

操作查询结果
1.聚集函数
Avg(字段名) //统计字段名
Sum(字段名) //统计字段之和
Min(字段名) //统计字段最小值
Max(字段名) //统计字段最大值
Count(字段名) //统计字段值个数
例：

mysql> select count(name) from db3.user; 
mysql> select count(name) from db3.user  
    -> where shell!="/bin/bash"; 
mysql> select min(uid) from db3.user; 
mysql> select min(uid) from db3.user 
    -> where uid<=1000; 
mysql> select max(uid) from db3.user; 
mysql> select avg(uid) from db3.user; 
mysql> select sum(uid) from db3.user; 

查询结果排序
SQL查询 order by 字段名 [asc | desc];
（通常是数值类型字段，desc降序）
例：

mysql> select name,uid from db3.user order by uid; 
mysql> select name,uid from db3.user order by uid desc; 

查询结果分组
SQL查询 group by 字段名;
（通常是数值类型字段）
例：

mysql> select gid from db3.user group by gid; 
mysql> select shell from db3.user group by shell; 
mysql> select shell from db3.user  
-> where uid>10 and uid<=2000 group by shell; 

查询结果过滤
SQL查询 having 条件表达式;
SQL查询 where 条件 having 条件表达式;
SQL查询 group by 字段名 having条件表达式;
例：

mysql> select name from db3.user 
    -> where shell!="/bin/bash"  
    -> having name="mysql"; 
mysql> select name from user 
    -> where shell!="/bin/bash" and name="mysql"; 
//查询结果一样，执行过程不一样，第一种效率高 

5.限制查询结果显示行数
SQL查询 limit N; //显示查询结果前N条记录
SQL查询 limit N,M; //显示指定范围内的查询记录
SQL查询 where 条件查询 limit 3; //显示查询结果前3条记录
SQL查询 where 条件查询 limit 3,3; //从第4条开始，共显示3条
例：

mysql> select name,uid from db3.user; 
mysql> select name from db3.user where id<=8 limit 1,3; 
mysql> select name from db3.user where id<=8 limit 1,1; 
mysql> select * from db3.user 
    -> where shell!="/bin/bash" limit 3,3;