MySQL存储引擎，表记录管理

一、mysql存储引擎

自带功能，处理表的处理器
MySQL 5.0/5.1 ----> MyISAM
MySQL 5.5/5.6 ----> InnoDB

列出可用的存储类型
show engines;或show engines\G

 MyISAM   | DEFAULT | MyISAM storage engine   | NO    | NO   |NO   

默认存储引擎：InnoDB
mysql>show create table 表名\G;
ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

设置默认存储引擎
mysql>set default_storage_engine=myisam;

修改/etc/my.cnf
[mysqld]
..
default-storage-engine=myisam

 

[root@localhoat~]#systemctl restart mysqld

可建表时指定引擎

> create table 表名(id int )engine=myisam

 

MyISAM存储引擎
特点：支持表级锁
相关文件：
.frm(表结构)、 
.MYI(表索引)、 
.MYD 表数据
 (/var/lib/mysql/表名/*.frm)

InnoDB存储引擎
特点：支持行级锁；支持事务、事务回滚、外键
相关文件： .frm 、 .ibd 、 ibdata1 、 ib_logfile0  、 ib_logfile1

MySQL锁机制
*锁:解决并发访问冲突问题

锁粒度

表级锁：一次直接对整张表进行加锁(范围大)myisam
行级锁：只锁某一行    (范围小)innodb
页级锁：对整个页面(MySQL管理数据的基本存储单位)进行加锁

锁类型
读锁(select)：支持并发读
写锁(insert update delete)：独占锁（互斥锁、排它锁），上锁期间其他线程无法读表或写表

查询访问多的表使用myisam存储引擎，节省系统资源--->表级锁
写操作多的表使用innodb存储引擎，并发访问量大------>行级锁

查看当前锁状态，%作通配符
mysql>show  status like 'Table_lock%';

+-------------------------------+-------+
| Variable_name             | Value |
+--------------------------------+-------+
| Table_locks_immediate | 908   |
| Table_locks_waited        | 0     |
+--------------------------------+-------+

事务特性  ACID
1次访问从建立到断开连接 整个过程

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

事务回滚：在执行事务时任意一步失败，会恢复所有操作--->rolback;

mysql>show variables like "autocommit";查看提交状态
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| autocommit      | ON    |
+---------------------+-------+

mysql>set autocommit=off;关闭自动提交
mysql>rolback;数据回滚
mysql>commit;提交数据

开2个终端测试

工作中建表如何决定表使用那种存储引擎？

select 读锁共享锁
查询访问多的表使用myisam存储引擎，节省系统资源。

写操作多的表使用innodb存储引擎，并发访问量大。

终端1]#  UPDATE  T1  SET  NAME=ZHANGSAN  where id<=5；

终端2]#  update t1 set name=lisi where id >=10;
提交前查看表记录的不同

二、数据导入导出

查看默认使用目录
mysql>show variables like "secure_file_priv";

+-----------------------------------------------+-------------------------+
| Variable_name                               |        Value                |
+---------------------------------------------+--------------------------+
| secure_file_priv                               | /var/lib/mysql-files/ |
+----------------------------------------------+--------------------------+
[root@localhost~]#ls -ld /var/lib/mysql-files/
drwxr-x---. 2 mysql mysql 72 5月   7 16:18 /var/lib/mysql-files/

设置目录
mkdir /mypriv  ; chown mysql:mysql /mypriv

vim /etc/my.cnf
[mysqld]
secure_file_priv="/mypriv"

重启mysqld

mysql> show variables like "secure_file_priv"

• 数据导入

load  data infile "目录/文件名(默认搜索路径)" into table 库.表
fields terminated by "分隔符" lines terminated by "\n";
字段分隔符要与文件内的一致；
指定导入文件的绝对路径；
导入数据的表字段类型要与文件字段匹配；
禁用SELinux保护机制

• 数据导出

sql查询命令(select * from user) into outfile  "绝对路径/文件名(默认搜索路径下新建文件)" 
fields terminated by "分隔符" lines terminated by "\n";

导出内容由查询语句决定；
导出的是表中记录，不包括字段名；
禁用SELinyx

例子：
1.将/etc/passwd文件cp到搜索目录下，数据库建库Pass，
然后建好表结构(name,password,uid,gid,commit,homedir,shell),
再
mysql>load data infile "/mypriv/passwd" into table Pass.user
->fields terminated by ":" lines terminated by "\n";

最后加个id int not null priamry key auto_increment 方便查看

2.mysql>select * from Pass.user where uid<100  limit 10  into outfile  "/mypriv/user.txt" 可加fields terminated by ":"
lines terminated by "\n";

select name,homedir(列)  from user where(行) shell="/bin/bash";

三、管理表记录

字段值要与字段类型相匹配

1.增加表记录
--insert  into 表名(可加字段名列表) 
values (字段值列表),(字段值列表)(N..)可多个表记录;
#指出各项值所对应的字段,未赋值的字段应设置有默认值或者有自增填充属性或者允许为空，否则插入操作将会失败

2.查询
--select  字段1，..或*(所有)..字段N from 库.表名 where 条件表达式;

3.更新
--update 表名 set 字段1=字段1值，字段2=字段2值，字段N=字段N值
where  条件表达式;(若没有where会更新所有记录)

4.删除
--delete from 表名 where 条件表达式;
--delete  from 表名;(删除所有表记录)

四、匹配条件

 

• 1.基本匹配条件

字段类型必须数据值类型（数值比较）
=
>,>=
>.>=
!=

字符比较
=
!=
is null
is not null

逻辑匹配
or
and
!
()  提高优先级，(5+6)/3先算括号内再算括号外

范围内匹配
in(值列表)  在..里..
not in (值列表)  不在..里..
between 数字1 and 数字2   在..之间..
distinct 字段名 去重显示
例：select * from user where name in("root","bin","sync");

----------------------------------------

• 2.高级匹配条件

1）模糊查询
where 字段名 like '通配符'
 _  匹配单个字符；
% 匹配任意多个字符 类似于  .*(正则，匹配任意字符多次,与regexp使用)

例：select * from user where name like 'r%' or name like '%t';

2）正则表达式
where 字段名 regexp '正则表达式'
正则元字符  ^  $ . [ ] * |
 例：select * from user where name regexp '^r|t$';

 

• 3.四则运算

注：字段必须数值类型
+  加法
-  减法
*  乘法
/  除法
%  取余数(求模)

• 4.操作查询结果

1）MySQL内值数据统计函数

avg(字段名)    统计字段平均值
sum(字段名)    统计字段之和
min(字段名)    统计字段最小值
max(字段名)    统计字段最大值
count(字段名) 统计字段值个数

2）查询结果排序

SQL查询 order by 字段名(数值类型字段) asc(升序)|desc(降序);

3）查询结果分组

SQL查询 group by 字段名(字符类型字段);
例：select shell from user where uid>=10  group by shell;
或  select distinct shell from user where uid>=10;
结果一样但过程不一样

4）查询结果过滤

SQL查询 having 条件表达式;
SQL查询 where 条件 having 条件表达式;
SQL查询 group by 字段名 having 条件表达式;

注：SQL查询出的结果再由having查询一遍
例：select name from user where shell !="/bin/bash"
 having name="nobody"; 表里查询结果再过滤一遍(节省CPU)
或 select name from user where shell !="/bin/bash" 
and name="nobody; 每行查询符合条件

5）限制查询结果显示行数

SQL查询 limit N; 显示查询 结果前N条记录
SQL查询 limit N,M; 显示指定范围内的查询记录
SQL查询 where 条件查询 limit 3;  显示查询结果前3条记录
SQL查询 where 条件查询 limit 3,3; 从第4条开始，共显示3条
 (0开始计数，0就是1,1是2..)

----------------------------------------------------------------------------------------------------
导入数据要与字段类型匹配(alter table 表 modify 字段名 类型..)

导出对应的目录要让mysql有w权限(默认搜索路径)