mysql存储,常用命令

mysql服务软件体系结构（8个功能模块）
管理工具：安装服务软件后提供的命令
连接池：响应客户端连接
sql接口；负责把sql命令传递给mysql 服务
分析器：检查执行的sql 命令（语法错误 访问权限….）
优化器：自动对执行的sql 命令做优化
查询缓存：mysql 服务启动时，从主机的物理内存获取空间，存储曾经查询过的数据
存储引擎：处理表的处理器
文件系统：存储数据硬盘
一、mysql 存储引擎
作为可插拔式的组件提供
mysql 服务软件自带的功能程序，处理表的处理器
不同的存储引擎有不同的功能和数据存储方式
默认的存储引擎
mysql 5.0/5.1——> myisam
mysql 5.5/5.6——> innodb
列出可用的存储引擎类型
show engines; 或 show engines\G
修改表的存储引擎
建表时手动指定
未指定时，使用默认存储引擎
alter table 表 engine=存储引擎名;
create table 表(字段列表) engine=存储引擎名;
show create table 表名\G;
设置默认存储引擎
修改/etc/my.cnf 配置文件## 标题 ##
default-storage-engine=存储引擎名称
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
systemctl restart mysqld
myisam存储引擎
主要特点；支持表级锁
不支持事务、事务回滚、外键
相关的表文件：表名.frm、 //存储表结构定义
表名.MYI //存储索引数据
表名.MYD //存储表数据
innodb存储引擎
主要特点；支持行级锁
支持事务、事务回滚、外键
相关的表文件：表名.frm、 //表结构
表名.ibd //数据和索引
mysql 锁机制
查看当前的锁状态
-检查table_lock 开头的变量，%作通配符

show status like ‘table_lock%’;
事务特性（ACID）
atomic : 原子性
- 事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失效
consistency : 一致性
- 事务操作的前后，表中的记录没有变化
isolation : 隔离性
- 事务操作是互相隔离不受影响的
durability : 持久性
- 数据一旦提交，不可改变，永久改变表数据

show variables like “autocommit” ; //查看自动提交设置
set autocommit=off ; //禁用自动提交
rollback; //数据回滚
commit ; //手动提交数据

二、数据导入与导出
自定义导入导出时检索文件的目录
默认目录: /var/lib/mysql-files/
数据的导入：把系统文件的内容，存储到数据库的表里
数据导入的步骤：1、建表 2、导入数据
设置搜索路径
查看默认使用目录及目录是否存在

show variables like “secure_file_priv”;
variable_name value
secure_file_priv /var/lib/mysql-files/
ls -ld /var/lib/mysql-files/
修改目录及查看修改结果
mkdir /myload
chown mysql /myload
vim /etc/my.cnf
secure_file_priv=”/myload”
systemctl restart mysqld
show variables like “secure_file_priv”;
variable_name value
secure_file_priv /myload
数据导入的基本用法
load data infile “目录名/文件名” into table 表名
fields terminated by “分隔符”
lines terminated by “\n”;
注意事项：
字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的表字段类型要与文件字段匹配
禁用selinux保护机制
数据导出的基本用法
select * from 表名 into outfile “目录名/文件名”
fields terminated by “分隔符”
lines terminated by “\n”;
注意事项：
导出的内容由sql查询语句决定
导出的是表中的记录，不包括字段名、
禁用selinux

三、管理表记录
插入表记录 insert into
(值必须与字段类型匹配且不能与字段约束)
格式1：添加1条记录，给所有字段赋值
insert into 表名 values (字段值列表)；
格式2：添加N条记录，给所有字段赋值
insert into 表名 values
(字段值列表),——第1条表记录
(字段值列表),——第2条表记录
(字段值列表),——第3条表记录
格式3：添加1条记录，给指定字段赋值
insent into 表名(字段名列表) values (字段值列表)；
格式4：添加N条记录，给指定字段赋值
insent into 表名 (字段名列表) values
(字段值列表),——第1条表记录
(字段值列表);——第2条表记录

查询表记录 select
格式1：select 字段名列表 from 表名；
格式2： select 字段列表 from 表名 where 条件表达式；

更新记录的字段值 update
格式1：update 表名 set 字段名=值；
格式2：update 表名 set 字段名=值，字段名=值 where 条件表达式；

删除记录 delete
格式1：仅删除符合条件的记录
delete from 表名 where 条件表达式；
格式2：删除所有的表记录
delete from 表名；

四、匹配条件
1、基本匹配条件
数值比较：=， >，>=，<，<=，!=
字符比较：=，！=，is null，is not null
逻辑匹配：or (逻辑或)，and (逻辑与)，! (逻辑非)，() (提高优先级)
范围内匹配/去重显示：
in (值列表)——>在…里…
not in (值列表)——>不在…里…
between 数字1 and 数字2 ——>在…之间…
distinct 字段名——>去重显示
2、高级匹配条件
模糊查询
where 字段名 like ‘通配符’；
_ 匹配单个字符、% 匹配0-N个字符
示例：列出name值‘y结尾’的记录
where name like ”J%’ or name like ‘%y’;
正则表达式
where 字段名 regexp ‘正则表达式’；
正则元字符 ^ $. [] * | 示例：列出name值 ‘以J开头或以Y结尾’ 的记录 select * from stu_info where name regexp “^J|Y$”;
四则运算
字段必须是数值类型：+，-，*，/，%
聚集函数
mysql 内置数据统计函数
avg(字段名) //统计字段平均值
sum(字段名) //统计字段之和
min(字段名) //统计字段最小值
max(字段名) //统计字段最大值
count(字段名) //统计字段值个数
查询结果排序
select 字段名 from 表名
order by 字段名 [asc|desc]; //asc正序，desc到序
select name,uid from user where uid between 10 and 40 order by uid desc;
select name,uid from user order by uid desc;
查询结果分组
select 字段名 from 表名
group by 字段名；
select shell from user where uid<=1000 group by shell;
select bm from yg where sex=”girl” group by bm;
查询结果过滤
select 字段名 from 表名
having 条件表达式；
where 条件 having 条件表达式；
group by 字段名 having 条件表达式；
select name from user where shell=”/bin/bash” having name=”lisi”;
select shell from user group by shell having shell=”/bin/bash”;
限制查询结果显示行数
select 字段名 from 表名
limit N; //显示查询结果前N条记录
limit N,M; //显示指定范围内从N行，共查M行记录
where 条件查询 limit 3;
//显示查询结果前3条记录
where 条件查询 limit 3，3；
//从第4条开始，共显示3条