第三节 储存引擎，导入导出
l 储存引擎的介绍和使用
**********************************************************
- mysql存储引擎
1.1存储引擎介绍
1.2查看数据库服务支持的存储引擎有那些？
1.3查看已有的表使用的存储引擎
1.4修改数据库服务默认使用的存储引擎
1.5修改表使用的存储引擎，或建表时指定表使用的存储引擎
1.6常用存储引擎的特点
1.7建表时如何决定表使用那种存储引擎
1.8工作中怎么决定数据库服务器使用那种存储引擎
- MySQL存储引擎
•作为可插拔式的组件提供
–MySQL服务软件自带的功能程序,处理表的处理器
–不同的存储引擎有不同的功能和数据存储方式
•默认的存储引擎
–MySQL 5.0/5.1--->MyISAM
–MySQL 5.5/5.6--->InnoDB
•列出可用的存储引擎类型
–SHOW ENGINES;或SHOW ENGINESG
**********************************************************************
- 修改存储引存
1）修改表使用的存储引擎，或建表时指定表使用的存储引擎
alter table表engine=存储引擎名；
create table表（字段列表）engine=存储引擎名；
2）存储引擎介绍:是mysql数据库软件自带的功能程序，
每种存储引擎的功能和数据存储方式也不同
存储引擎就处理表的处理器
3）查看数据库服务支持的存储引擎有那些？修改数据库服务默认使用的存储引擎
]#vim/etc/my.cnf
[mysqld]
default-storage-engine=myisam
:wq
]#systemctl restart mysqld
mysql>show engines;
① 锁粒度：
表级锁（myisam）给整张表加锁
行级锁(innodb)只给表中当前被操作行加锁
锁的作用：解决对表的并发访问冲突问题。
select*from表名where id<=20;
insert
delete from表名；
update表名set name="bob"where name="lucy";
update表名set name="tom"where name="jerry";
锁类型
读锁（共享锁）
写锁（互斥锁）
- 修改数据库服务默认使用的存储引擎
]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
]#systemctl restart mysqld
- 修改表使用的存储引擎，或建表时指定表使用的存储引擎
alter table表engine=存储引擎名；
create table表（字段列表）engine=存储引擎名；
l Myisam存储
- 引擎主要特点
–支持表级锁
–不支持事务、事务回滚、外键
•相关的表文件
–表名.frm、
–表名.MYI
–表名.MYD
•存储方式：一个表对应3个存储文件
表名.frm表结构
表名.MYD数据
表名.MYI索引
l nnoDB存储引擎
•主要特点
–支持行级锁定
–支持事务、事务回滚、支持外键
•相关的表文件
–xxx.frm、xxx.ibd
–ibdata1
–ib_logfile0
–ib_logfile1
•存储方式：一个表对应2个存储文件
表名.frm表结构
表名.ibd数据和索引
l MySQL锁机制
•锁粒度
–表级锁:一次直接对整张表进行加锁。
–行级锁:只锁定某一行。
–页级锁:对整个页面(MySQL管理数据的基本存储单位)进行加锁。
•锁类型
–读锁(共享锁):支持并发读。
–写锁(互斥锁、排它锁):是独占锁,上锁期间其他
线程不能读表或写表。
l 事务和事物回滚
事务：对数据库服务的访问过程（连接数据库服务器操作数据断开连接）
事务回滚：在事务执行过程中，任何一步操作失败，都会恢复之前的所有操作。
l MySQL锁机制的使用
1）mysql数据库服务使用事务日志文件记录，对innodb存储引擎表执行的sql操作。
cd/var/lib/mysql/
ib_logfile0-|
|------>记录SQL命令
ib_logfile1-|
insert into t1 values(8888);
ibdata1---->数据源（sql命令执行后产生的数据信息）
2）锁粒度：的介绍
表级锁（myisam）给整张表加锁
行级锁(innodb)只给表中当前被操作行加锁
锁的作用：解决对表的并发访问冲突问题。
select*from t1 where id<=20;
insert
delete from t1;
update t1 set name="bob"where name="lucy";
update t1 set name="tom"where name="jerry";
锁类型
读锁（共享锁）
写锁（互斥锁）
3）建表时如何决定表使用那种存储引擎
执行写操作多的表适合使用innodb存储引擎，可以并发访问。
执行查操作多的表适合使用myisam存储引擎,可以节省系统资源
数据导入导出（批量操作数据）
参考：
1）查看导入文件内容时，默认在系统的什么目录下搜索文件？
mysql>show variables like"secure_file_priv";
2）数据导入的命令格式及数据导入时的注意事项
导入数据的命令格式：
命令格式？
LOAD DATA INFILE"目录/文件名"
INTO TABLE库.表
FIELDS TERMINATED BY"列间隔符号"
LINES TERMINATED BY" "
数据导入：把系统文件的内容存储到数据库服务器的表里。
把系统已有用户的信息保存到db3库下的usertab表里
数据导入
案例：
修改导入文件内容时，默认搜索文件的目录？
]#vim /etc/my.cnf
[mysqld]
secure_file_priv=“/mysqldata”
:wq
]#mkdir -p/mysqldata
]#chown mysql/mysqldata
]#setenforce 0
]#systemctl restart mysqld
1）创建库
create database db3;
2）创建存储数据表
Mysql>create table db3.usertab(
>username char(50),
>password char(1),
>uid int(2),
>gid int(2),
>comment char(100),
>homedir char(100),
>shell char(50),
>index(username)
);
3)查看表结构
desc db3.usertab;
4）查看数据
select*from db3.usertab;
5）导入数据
]#cp /etc/passwd /var/lib/mysql-files/
Mysql>load data infile "/var/lib/mysql-files/passwd"
>into table db3.usertab
>fields terminated by":"
>lines terminated by" ";
6)添加id排序
mysql>alter table db3.usertab add
id int(2)primary key auto_increment first;
7）查看数据
mysql>select * from db3.usertab;
mysql>select * from db3.usertab where id=20;
数据导出
数据导出的命令格式及数据导出时的注意事项
数据导出：把表记录存储到系统文件里
注意事项？
命令格式
sql查询命令into outfile"目录/文件名"[FIELDS TERMINATED BY"列间隔符号"LINES TERMINATED BY" "]；
案例：
1)数据导入
Mysql>load data infile "/mysqldata/passwd"
>into table db3.usertab
>fields terminated by":"
>lines terminated by" ";
2)数据导出
mysql>select username,uid from db3.usertab into outfile "/mysqldata/user1.txt";
mysql>select * from db3.usertab into outfile "/mysqldata/user2.txt";
mysql>select username,uid from db3.usertab into outfile "/mysqldata/user3.txt" fields terminated by "###";
3)查看数据
]#cat /mysqldata/user1.txt
]#cat /mysqldata/user2.txt
]#cat /mysqldata/user3.txt
管理表记录
1）插入记录insert into（值要与字段类型和约束条件匹配）
2）插入1条记录给所有字段赋值
insert into库.表values(字段值列表);
3）插入N条记录给所有字段赋值
insert into库.表values(字段值列表)，(字段值列表);
4）插入1条记录给指定的字段赋值
insert into库.表（字段名列表）values(字段值列表);
5）插入N条记录给指定的字段赋值
insert into库.表（字段名列表）values(字段值列表)，(字段值列表);
查看记录
1）命令格式
select字段名列表from库.表where匹配条件；
2）查看表中所有行的所有字段的值
select*from库.表；
3）查看表中所有行的指定字段的值
select字段名1，字段名2，字段名n from库.表；
4）查看指定行的指定字段的值
select字段名1，字段名2，字段名n from库.表where匹配条件；
修改记录
1）修改所有记录指定字段的值
update库.表set字段名=值，字段名=值；
2）修改与条件匹配的记录指定字段的值
update库.表set字段名=值，字段名=值where匹配条件；
3）例子
update db3.usertab set password="A";
update db3.usertab set password="x" where id=1;
select * from db3.usertab;
删除记录
1）删除表中的所有行
delete from库.表；
2）仅删除与条件匹配的记录
delete from库.表where匹配条件；
3）例子
delete from db3.usertab where id=3;
匹配条件
（查看修改删除记录时可以加条件）
4.1数值比较字段名符号数字
=!=<<=>>=
select username from usertab where uid=10;
select id,username,uid from usertab where uid=1001;
select * from usertab where id<=10;
4.2字符比较字段名符号“字符串”
=!=
select username from usertab where username="apache";
select username,shell from usertab where shell="/bin/bash";
select username,shell from usertab where shell!="/bin/bash";
4.3范围内比较
字段名between数字1 and数字2在...之间...
字段名in(值列表)在...里
字段名not in(值列表)不在...里
select username from usertab where uid between 100 and 150;
select username,uid from usertab where uid in(10,20,30,50);
Select username,uid from usertab where username in("root","rsync","mysql");
select username from usertab where username not in("root","bin");
4.4逻辑比较（就是有个查询条件）
逻辑与and多个条件同时成立才匹配
逻辑或or多个条件,某一个条件成立就匹配
逻辑非！或not取反
>select username,uid from usertab where username="root" and uid=0 and shell="/bin/bash";
>select username,uid from usertab where username="root" or uid=1 or shell="/bin/bash";
>select username,uid from usertab where username="root" or username="apache" or username="bob";
4.5匹配空字段名is null
匹配空字段名is not null
mysql>select username,uid,gid from usertab
where
uid is null and gid is null;
mysql>update usertab set uid=3000,gid=3000 where username="lucy";
Mysql>select id from usertab where name="yaya"
and uid is not null;
Mysql>update usertab set username=null where id=2;
4.6模糊匹配
字段名like'表达式';
%表示零个或多个字符
_表任意一个字符
例：
>select username from usertab where username like'____';
>select username from usertab where username like'a__t';
>insert into usertab(username)values("a");
>select username from usertab where username like'a%';
>select username from usertab where username like'_%_';
4.7正则匹配
字段名regexp'正则表达式';
^$.*[]
例：
select username from usertab where username regexp'[0-9]';
select username from usertab where username regexp'^[0-9]';
select username from usertab where username regexp'[0-9]$';
select username from usertab where username regexp'a.*t';
select username from usertab where username regexp'^a.*t$';
select username,uid from usertab where uid regexp'..';
select username,uid from usertab where uid regexp'^..$';
4.7四则运算(select和update操作是可以做数学计算)
字段类型必须数值类型（整型或浮点型）
+-*/%
例：
mysql>select id,username,uid from usertab where id<=10;
mysql>update usertab set uid=uid+1 where id<=10;
mysql>select username,uid,gid from usertab where usernane="mysql";
mysql>select username,uid,gid,uid+gid as zh from usertab
where username="mysql";
mysql>select username,uid,gid,uid+gid as zh,(uid+gid)/2 as pjz
From usertab where username="mysql";
mysql>alter table usertab add age tinyint(2)unsigned default 21
after username;
mysql>select username,age from usertab;
select username,age,2018-age s_year from usertab where username="root";
4.9聚集函数（对字段的值做统计，字段的类型要求是数值类型）
count(字段名）统计字段值的个数
sum(字段名）求和
max(字段名）输出字段值的最大值
min(字段名）输出字段值的最小值
avg(字段名）输出字段值的平均值
select max(uid)from usertab;
select sum(uid)from usertab;
select min(uid)from usertab;
select avg(uid)from usertab;
select count(id)from usertab;
select count(username)from usertab where shell="/bin/bash";
4.10查询不显示字段重复值distinct字段名
>select distinct shell from usertab;
>select distinct shell from usertab where uid>10 and uid<=100;
4.11查询分组
sql查询group by字段名；
mysql>select shell from usertab where uid>10 and uid<=100
group by shell;
4.12查询排序(按照数值类型的字段排队)
sql查询order by字段名asc|desc;
>select username,uid from usertab where uid>10 and uid<=100
order by uid;
>select username,uid from usertab where uid>10 and uid<=100
order by uid desc;//按照uid从大到小排序
>select username,uid from usertab where uid>10 and uid<=100
order by uid asc;//按照uid从小到大排序
4.12.1查询结果过滤
•基本用法
–SQL查询HAVING条件表达式;
–SQL查询where条件HAVING条件表达式;
–SQL查询group by字段名HAVING条件表达式;
>Select class from表where ssex=”条件”group by class having coune(ssex)>=2;
4.13限制查询显示行数(默认显示所有查询的记录)
sql查询limit数字；显示查询结果的前几行
sql查询limit数字1，数字2；显示查询结果指定范围的行
>select username,uid from usertab where uid>10 and uid<=100
order by uid desc limit 1;
>select username,uid from usertab where uid>10 and uid<=100
order by uid desc limit 2,3;
4.14基本匹配条件:适用于select update delete
数值比较=>>=<<=!=
where字段名符号数字
例：
>select id,name from user1 where id=10;
>update user1 set password="B",homedir="/root"where id<=10;
>delete from user1 where uid>=3000;
>select name,uid,gid from user1 where uid=gid;
>select name,uid,gid from user1 where uid!=gid;
4.15字符比较=!=
where字段名符号"字符串"
例：
>select name from user1 where name="root";
>select name from user1 where shell="/bin/bash";
>select name,shell from user1 where shell!="/bin/bash";
4.16匹配空is null
匹配非空is not null
例：
>insert into user1(name)values("zhangshu");
>select * from user name="zhangshu";
>select id,name,uid from user1 where uid is null;
>select name,uid from user1 where uid is not null;
>update user1 set uid=250 where uid is null;
>select id,name,uid from user1 where name="zhangshu";
>update user1 set uid=null where name="bin";
>update user1 set name="" where name="bin";
4.17逻辑比较:多个匹配条件
逻辑与and多个匹配条件同时匹配
逻辑或or多个匹配条件时某个条件匹配即可
逻辑非!或者not取反
例2：
>select id,name,uid from user1 where name="zhangshu" and
uid=250;
>select id,name,uid from user1 where name="zhangshu" or
uid=250 or id=1;
>select id,name,uid from user1
where
name="zhangshu" or id=1 and name="root";
>select id,name,uid from user1
where
uid=3 or id=1 and name="root";
>select id,name,uid from user1
where
(uid=3 or id=1)and name="root";
4.18范围内匹配
>select name from user1
where
name in("zhangshu","mysql","rsync","apache");
>select name,uid from user1 where uid in(10,200,250,1,13);
>select name,uid,shell from user1
where
shell not in("/bin/bash","/sbin/nologin");
>select id,name from user1 where id between 10 and 25;
>select DISTINCT shell from user1;
>select DISTINCT shell from user1 where uid>=100;
4.19高级匹配条件：适用于select update delete
模糊匹配like
>insert into user1(id,name)values(67,null),(69,"");
>select name from user1 where name like'____';
>select name from user1 where name like'__%__';
>select name from user1 where name like'a%';
>select name from user1 where name like'%';
4.20正则匹配regexp'正则表达式'
.^$[]*
例：
select name from user1 where name regexp'^a';
select name from user1 where name regexp'a';
insert into user1(name)values("yaya8"),("ya6ya"),("1yaya");
select name from user1 where name regexp'[0-9]'
select name from user1 where name regexp'[0-9]$'
select name,uid from user1 where uid regexp'...';
select name,uid from user1 where uid regexp'^...$';
select name,uid from user1 where uid regexp'^r.*t$';
select name,uid from user1 where name regexp'^a.*t$';
4.21四则计算适用于select update
+-*/%
例：
>select name,uid from user1 where uid>=10 and uid<=20;
>update user1 set uid=uid+1 where uid>=10 and uid<=20;
>update user1 set uid=uid-2 where uid>=10 and uid<=20;
>alter table user1 add age int(2)default 19 after name;
>select name,age from user1 where name="root";
>select name,2018-age s_year from user1 where name="root";
>select name,uid,gid,(uid+gid)/2 pjz from user1;
*************************************************************************