mysql存储引擎的选择及优缺点),管理表记录，匹配条件

MySQL存储引擎
作为可插拔式的组件提供
MYSQL服务软件自带的功能程序，处理表的处理器
不同的存储引擎有不同的功能和数据存储方式
默认的存储引擎
MySQL 5.0/5.1 --> MyISAM
MySQL 5.5/5.6 --> InnoDB

#列出可用的存储引擎类型
mysql> show engines;或mysql> show engines\G;

引擎类型默认描述事务
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

#设置默认存储引擎
systemctl stop mysqld
vim /etc/my.cnf
......
[mysqld]
default-storage-engine=myisam
......
systemctl start mysqld
mysql -uroot -p123qqq...A
mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

#MyISAM存储引擎的特点
--支持表级锁
--不支持事务、事务回滚、外键
MyISAM存储引擎的存储文件，每个MISAN表都有下列3个存储文件（独享表空间）
--表名.frm:存储表结结构
--表名.MYI:存储表的索引信息
--表名.NYD:存储数据

#InnoDB存储引擎的特点
--支持事务transactions：一次SQL操作从开始建立连接，执行各种sql命令到断开连接的过程
--支持事务回滚：事务执行时，任意一步操作没有成功，会恢复之前的所有操作
--支持行级锁row-level locking：在有客户访问表时，锁定访问的当前行
--支持外键foreign keys:
InnoDB存储引擎的存储文件，每个InnoDB表都有下列2个存储文件
--表名.frm:存储表结结构
--表名.ibd:存储索引和数据
事物日志文件
--ibdata1
--ib_logfile0
--ib_logfile1

#MEMORY存储引擎
--存储在内存中
--重启后消失

#指定存储引擎
mysql> create table 表名(。。。)engine=memory;
#修改存储引擎
mysql> alter table 表名 engine=innodb;

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

查看当前的锁状态
--检查Table_lock开头的变量，%作通配符
mysql> show status like '%lock%';

+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 4 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6695 |
| Key_blocks_used | 3 |
| Locked_connects | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_metadata_lock_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Table_locks_immediate | 101 |
| Table_locks_waited | 0 | #表等待
+------------------------------------------+-------+

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

#存储引擎的选择
写操作多的适合使用innodb存储引擎，这样并发访问量大 insert update delete
读操作多的适合使用myisam存储引擎，这样性能好,节省系统资源 select

############################################################################################################

总结：
搭建mysql数据库服务
建表：数据类型约束条件键值存储引擎修改表结构

#管理表记录
为了方便管理，为user表添加ID列
mysql> alter table user add id int(2) primary key auto_increment first;
mysql> desc user;
+----------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(30) | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+

#增加表记录
语法格式：
格式1：添加1条记录，给所有字段赋值
--insert into 表名 values(字段值列表); #有几个字段必须给几个值，而且字段必须匹配
格式2：添加N条记录，给所有字段赋值
--insert into 表名 values(字段值列表1)，(字段值列表2)，(字段值列表3)。。。; #注意设置主键的值不能重复
格式3：添加1条记录，给指定字段赋值
--insert into 表名(字段名列表) values(字段值列表);
格式4：添加N条记录，给指定字段赋值
--insert into 表名(字段名列表) values(字段值列表1)，(字段值列表2)，(字段值列表3)。。。;
注意事项
--字段值要与字段类型相匹配
--对于字符类型的字段，要用双引或单引括起来
--依次给所有字段赋值时，字段名可以省略
--只给一部分字段赋值时，必须明确写出对应的字段名称

#查询表记录
语法格式：
格式1：
--select 字段1,字段2,。。。。 from 表名;
格式2：
--select 字段1,字段2,。。。。 from 表名 where 条件表达式;
注意事项
--使用*可以匹配所有字段
--指定表名时，可采用库名.表名的形式

#更新表记录
语法格式：
格式1：更新表内的所有记录
--update 表名 set 字段1=字段1值，字段2=字段2值,。。。。; #不加条件时为批量修改
格式2：只更新符合条件的部分记录
--update 表名 set 字段1=字段1值，字段2=字段2值,。。。。 where 条件表达式; #加条件时为指定修改
注意事项：
--字段值要与字段类型相匹配
--对于字符类型的字段，要用双或单引号括起来
--若不使用where限定条件，会更新所有记录
--限定条件时，只更新匹配条件的记录

#删除表记录
语法格式：
格式1：仅删除符合条件的记录
--delete from 表名 where 条件表达式;
格式2：删除所有的表记录
--delete from 表名;
例：
mysql> delete from user where id > 40

#匹配条件
--基本匹配条件
--高级匹配条件
--操作查询结果
基本匹配条件高级匹配条件应用于select\update\delete操作

#基本匹配条件
--数值比较 > >= < <= = !=
--字符比较/匹配空/非空 = != is noll is not null
--逻辑比较 and or ! ()
--范围内匹配/去重显示 in() not in() between 数字 and 数字 distinct
数值比较：
只段类型必须是数值类型
类型用途
= 等于
>、>= 大于、大于等于
<、<= 小于、小于等于
!= 不等于
例：
mysql> select name from user where gid=7;
mysql> select name,uid,gid from user where gid=7;
mysql> select name,uid,gid from user where uid=gid;
mysql> select name,uid,gid from user where 1=2;
mysql> select name,uid,gid from user where 1=2;
mysql> select name,uid,gid from user where id<=10;

字符比较/匹配空/非空：
字符比较时，字段类型必须是字符类型
类型用途
= 相等
!= 不相等
IS NULL 匹配空
IS NOT NULL 非空
例：
mysql> select name from user where shell="/bin/bash";
mysql> select name from user where shell!="/bin/bash";
mysql> select name from user where shell is null;
mysql> select name from user where shell is not null;

逻辑匹配
多个判断条件时使用
类型用途
OR 逻辑或
AND 逻辑与
! 逻辑非
() 提高优先级
mysql> select name,uid,shell from user where name="haxi" or uid=0 or shell="/bin/bash";
mysql> select name,uid,shell from user where name="haxi" and uid=0 and shell="/bin/bash";
mysql> select name from user where shell!="/bin/bash";

范围内匹配/去重显示
匹配范围内的任意一个值即可
类型用途
in (值列表) 在...里...
not in (值列表) 不在...里...
between 数字1 and 数字2 在...之间...
distinct 字段名去重显示
例：
mysql> select name,uid,gid from user where gid in (10,20,30,40);
mysql> select name,uid,gid from user where name in ("root","mysql","lp");
mysql> select name,uid,gid from user where gid not in (10,20,30,40);
mysql> select name,uid,gid from user where gid between 10 and 100;
mysql> select distinct shell from user;
mysql> select distinct shell from user where gid<=100;

#高级匹配条件
模糊查询
基本用法
--where 字段名 like '通配符'
--"_"匹配单个字符，"%"匹配0～N个字符

例：
mysql> select name from user where name like '____';
mysql> select name from user where name like '%a%';
mysql> select name from user where name like '%____%';

正则表达式
基本用法
--where 字段名 regexp '正则表达式'
--正则元字符' ^ $ . [] * | '

例：
mysql> insert into user(name) values("7bob"),("lily6"),("jer2ry");
mysql> select name from user where name regexp '[0-9]';
mysql> select name from user where name regexp '^[0-9]';
mysql> select name from user where name regexp '[0-9]$';
mysql> select name,uid from user where uid regexp '....';
mysql> select name,gid from user where gid regexp '^....$';

四则运算
运算操作：字段必须是数值类型
类型用途
+ 加法
- 减法
* 乘法
/ 除法
% 取余数（求模）

例：
mysql> alter table user add age tinyint(2) default 18 after name;
mysql> select name,age,2018-age syear from user;
mysql> select name,age,2018-age syear from user where name="root";
mysql> select name,uid from user where uid%2=0;
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where name="root";
mysql> update user set age=age+1;
mysql> select name,age from user;
mysql> update user set age=age+1 where name="root";
mysql> select name,age from user;

#操作结果查询
聚集函数
mysql内置数据统计函数
--avg(字段名) #统计字段平均值
--sum(字段名) #统计字段之和
--min(字段名) #统计字段最小值
--max(字段名) #统计字段最大值
--count(字段名) #统计字段值个数

例：
mysql> select count(name) from user;
mysql> select count(name) from user where shell="/bin/bash";
mysql> select avg(uid) from user;
mysql> select max(uid) from user;
mysql> select min(uid) from user;
mysql> select sum(uid) from user;

查询结果排序
基本用法
--SQL查询 order by 字段名 [asc|desc]; #通常是数值类型的字段，asc升序从小到大(默认)，desc降序从大到小

例：
mysql> select name,uid from user where uid between 10 and 600;
mysql> select name,uid from user where uid between 10 and 600 order by uid;
mysql> select name,uid from user where uid between 10 and 600 order by uid desc;

查询结果分组
基本用法
--SQL查询 group by 字段名; #通常是字符类型字段

例：
mysql> select shell from user group by shell;
mysql> select shell,count(*) from user group by shell;
mysql> select shell from user where uid<=500 group by shell;

查询结果过滤
基本用法
--SQL查询 having 条件表达式；
--SQL查询 where 条件 having 条件表达式；
--SQL查询 group by 字段名 having 条件表达式；

例：
mysql> select name from user having name="adm";
mysql> select name from user where uid<=200 having name="root";
mysql> select shell from user group by shell having shell="/sbin/nologin";

限制查询结果显示行数
基本用法
--SQL查询 limit N； #显示查询结果前N条记录
--SQL查询 limit N,M； #显示指定范围内的查询记录，从N+1行开始，显示M行
--SQL查询 where 条件 limit N；
--SQL查询 group by 字段名 3,3； #从第4行开始，共显示3行

例：
mysql> select * from user limit 3;
mysql> select * from user limit 3,4;
mysql> select name,uid from user where uid<=200 limit 2;
mysql> select name,uid from user where uid<=200 order by uid desc limit 2;

mysql存储引擎的选择及优缺点),管理表记录，匹配条件

推荐阅读