数据库存储引擎、数据导入导出

mysql存储引擎
mysql服务组件:管理工具（mysql）
链接池（进程 cpu 内存）
SQL接口
分析器（执行命令语法判断对错）
优化器
查询缓存 （空间是从系统的物理内存获取用来存储查找过的数据）
存储引擎
文件系统 硬件
列出可用的
show engines;

mysql存储引擎
作为可拔式的组件提供
mysql服务软件自带的功能程序，处理表的处理器
不同的存储引擎有不同的功能和数据存储方式
默认的存储引擎
Mysql 5.0/5.1------》 Myisam
Mysql 5.5/5.6 -----》 innodb
列出可用的 存储引擎类型
show engines; 或 show engines\G
修改表的存储引擎
建表时手动指定
未指定时，使用默认存储引擎
show create table 表名\G 可确定
设置默认存储引擎
修改/etc/my.cnf
default-storage-engine=存储引擎名称 （修改存储引擎）
systemctl restart mysqld

myisam存储引擎特点
支持表级锁 (给整张表加锁)
不支持事务 事物回滚 外键 （事务回滚：在事务执行过程中，任何一步操作没有正确，恢复之前所有的操作。）–事务日志文件：记录对innodb存储引擎的表执行的操作
相关的表文件
表名.frm -----存的表结构（desc 表）
表名.MYI------表索引 （create index xxx on 表）
表名.MYD------ 表数据 （insert 表… select。。）
innodb存储引擎
特点：支持行级锁定 （只给被访问的行加锁）
支持事务 事务回滚 外键
相关的表文件
表名.frm ---------》表结构
表名.ibd ---------》 存放索引与数据
ibdata1 ib_logfile0 ib_logfile1
update t1 set name=“bob” where age<=10;

mysql锁机制
表级锁：一次直接对整张表进行加锁
行级锁：只锁定某一行
页级锁：对整个页面（mysql管理数据的基本存储单位）进行加锁
锁类型
读锁 ：支持并发读 （select） 共享锁（第二用户也能读）
写锁：（排他锁或互斥锁）：是独占锁，上锁期期间其他线程不能读表或写表 insert
update delete）
show status like “%lock%”； （查看当前锁状态）

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

show variables like “%auto%”; 查看提交状态
set autocommit=off; (关闭自动提交。只对当前有效，退出在连又还原)
create table t4(id int)engine=innodb;
insert t4 values(13);
commit （提交数据）
rollback （数据回滚 ，，数据未提交可以还原）
查询操作多的表使用 myisam 存储引擎 并发访问量大 （节省资源）
写 （insert update delete）操作多的表使用 innodb 存储引擎，并发访问量大

数据导入导出
数据导入 把 系统内容 存储到 数据库的表里
数据导出 把数据表里记录 保存到 系统文件里
数据导入导出时mysql服务 检索文件的默认目录 /var/lib/mysql-files
show variables like “secure_file_priv”
退出数据库
修改配置文件（/etc/my.cnf）
secure_file_priv="/mydata" （检索目录）
character_set_server=utf8 （修改语言建表支持中文）
mkdir /mydata
chown mysql /mydatav
show variables like “secure_file_priv”;
------------------±---------+
| Variable_name | Value |
±-----------------±---------+
| secure_file_priv | /mydata/ |
±-----------------±---------+
建表
create table user(
-> name char(35),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(150),
-> shell char(40));

数据用法
导入
load data infile “目录名/文件名” into table 表名 fields terminated by “分隔符” lines terminated by “\n”;
注意事项
字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的表字段类型要与文件字段匹配
禁用selinux 保护机制
system cp /etc/passwd /mydata （直接在数据库执行cp 命令）
mysql> system ls /mydata
passwd
load data infile “/mydata/passwd” into table userdb.user fields terminated by “:” lines terminated by “\n”;
alter table userdb.user add id int primary key auto_increment first; (在表前加编号)
导出
用法
sql查询 into outfile “目录/文件名” fields terminated by “分隔符” (查到的记录用什么分割 )lines terminated by “\n”;（\n 换行结束）–（间隔符）
select * from user into outfile “/mydata/user.txt”;
select * from user where id <=10 into outfile “/mydata/user.txt”;
select name,shell from user into outfile “/mydata/user.txt”;
system cat /mydata/user.txt (查看)
把uid小于100的前10行导出到“/mydata/user5.txt”： select * from userdb.user where uid <=100 limit 10 into outfile “/mydata/user5.txt”

注意事项
导入的内容由sql查询语句决定
导出的表中的记录，不包括字段名
禁用selinux
ERROR 1136 (21S01): Column count doesn’t match value count at row 1(字段个数与赋值不匹配)
当有特殊符号用双引，字符用单引。一般都用双引

语法格式
添加 （insert）
insert （into） 表名 values（字段列表）
insert user(name,password) values(“a”,“b”),(“q”,“d”),(“z”,“t”) （批量给表中name password 赋值）
查询（select）
select
select name,password,uid from user where uid <=5;(显示表中所有name password uid 并且uid 小于等于5 )
更新（update）
update 表名 set 字段1=字段1 where 字段2=…
update user set password=“a” (改表中所有password 为a)
update user set password=“a” where name=“root” （只改user表 name为root 的password为a）
update 工资单 set 工资=18000 where 工资号>1 limit 1; (修改工资号大于一的第一行) 在表中为第二行
±----------±---------+
| 工资号 | 工资 |
±----------±---------+
| 1 | 20000.00 |
| 2 | 13000.00 |
| 2 | 13000.00 |
| 3 | 15000.00 |
±----------±---------+
±----------±---------+
| 工资号 | 工资 |
±----------±---------+
| 1 | 20000.00 |
| 2 | 18000.00 |
| 2 | 13000.00 |
| 3 | 15000.00 |
±----------±---------+

删除 （delete drop）
delete from 表名 where 条件表达式
delete from 表名 只删除表中所有记录，不能删除表
drop table 表 名 删除表
delete from user where gid is null （删除user表中 gid为空值（null））

基本匹配条件
数值比较 （= 、> >=、< <=、 !=）
字符比较/匹配空/非空（= 、 ！= 、 is null is『匹配空』 、not null『非空』）
逻辑匹配 （or 、and、 ！、 （）） 多个判断条件使用
or 逻辑或 （满足任意一个） select name，uid from user where uid=1 or uid=9 or uid=9
and 逻辑与 （几个条件都成立）
！（not） 逻辑非 select name，uid from user where uid！=1；
（） 提高优先级

in （值列表） 在,里 select name from user where name in （“root”,“adm”）
not in (值列表) 不在… 里 select nmae，uid from user where uid not in (7,8,10）
betwwen 数字 and 数字 在…之间… select name,uid from user where uid between 10 and 30;
distinct 字段名 去除重复显示 select distinct sheel from user; (显示表中所有sheel ，并且屏蔽重复的，只显示一个)
高级匹配条件
模糊查询
用法 where 字段名 like ‘通配符’

• 匹配单个字符、% 匹配0～N 个字符
  select name from user where name like ‘____’; (匹配name 有四个字符)
  select name from user where name like “%y”; （匹配表中 name 以y 结尾）

正则表达式
用法 where 字段名 regexp “正则表达式”
正则元字符 ^ 、$ 、 . 、 [ ] 、 * 、 |
select name from user where name regexp “^j|y$";（显示j开头y结尾）selectuid,namefromuserwhereuidregexp{"}^{.}...$”; (显示四位数）
select uid,name from user where uid regexp “…”; （显示三位及以上）
select name from user where name regexp “a”; （显示name 中有a的，不在意a在哪里）
四则运算 （+ - * / %）—%（取余）
select name,uid,gid,uid+gid from user where id<=10;
±---------±-----±-----±--------+
| name | uid | gid | uid+gid |
±---------±-----±-----±--------+
| root | 0 | 0 | 0 |
| bin | 1 | 1 | 2 |
| daemon | 2 | 2 | 4 |
| adm | 3 | 4 | 7 |
| lp | 4 | 7 | 11 |
| sync | 5 | 0 | 5 |
| shutdown | 6 | 0 | 6 |
| halt | 7 | 0 | 7 |
| mail | 8 | 12 | 20 |
| operator | 11 | 0 | 11 |
±---------±-----±-----±--------+
select name,uid,gid,uid+gid abc from user where id <=5;
±-------±-----±-----±-----+
| name | uid | gid | abc |
±-------±-----±-----±-----+
| root | 0 | 0 | 0 |
| bin | 1 | 1 | 2 |
| daemon | 2 | 2 | 4 |
| adm | 3 | 4 | 7 |
| lp | 4 | 7 | 11 |
±-------±-----±-----±-----+
select name,uid,gid,(uid+gid)/2 from user where name=“adm”; （“（）” 优先级 先加在相处）
±-----±-----±-----±------------+
| name | uid | gid | (uid+gid)/2 |
±-----±-----±-----±------------+
| adm | 3 | 4 | 3.5000 |
±-----±-----±-----±------------+
select name,uid from user where uid%2= 0; （取出uid 为偶数的）
select name,uid （as） he from user where uid%2= 0; （取出偶数 ，并改名为 he）
±-----±-----±-----±-------+
| name | uid | gid | haha |
±-----±-----±-----±-------+
| adm | 3 | 4 | 3.5000 |
±-----±-----±-----±-------+
update user set uid=uid+1 where uid<=10;(把user表中uid 小于等于10 的uid 都加一)
select name，uid from user where uid <=10; (查看)
聚集函数
mysql内置数据统计函数
avg（字段名） 统计字段平均值 select avg(uid) from user;

sum（字段名） 统计字段之和 select sum(uid) from user where uid <=10;
min（字段名） 统计字段最小值 select min(uid) from user;
max（字段名） 统计字段最大值 select max（uid） from user where sheel=“/bin/bash”
count（字段名） 统计字段值个数 select count（id） from user

查询结果排序
order by 字段名 （desc）
select name,uid from user where uid>100 order by uid; （将uid按从小到大排序）
select name,uid from user where uid>100 order by uid desc; （将uid按从大到小排序）
查询结果分组
group by 字段名
用法
select having 条件表达式
select where 条件 having 条件表达式
select group by 字段名 having 条件表达式
select shell from user group by shell; （查询shell 有哪些组）
select 部门 from 员工信息表 where 性别=“女” group by 部门；（查询员工信息表有哪些女在哪些部门）
seelct 专业 from 学生表 where 性别=“女” group by 专业； （查询学校女在那些专业）
select name from user where sheel!="/bin/bash" having name=“mysql”;
select sheel from user group by sheel having sheel="/sbin/shutdown"; (查询sheel为/sbin/shutdown)
select * from user having uid>4000;===select * from user where uid>4000;

限制查询结果显示行数
基本用法
select limit N 显示查询结果前N条记录
select name from user where id<=20 limit 10; ====select name from user where id<=10; （显示uid小于等于20 的前十行）
select limit N，M 显示查询范围内的查询记录
select id,name from user where id<=20 limit 3; （显示id小于等于20 的前三行）
select id,name from user where id<=20 limit 3,1; （显示id小于等于20的第四行）
select id,name from user where id<=20 limit 1,3; （显示id小于等于20 的第二到四行）
select id,name from user where id<=20 limit 2,6 （三到八行）