Mysql存储引擎

最新推荐文章于 2023-05-04 09:44:33 发布

Jiali Li

最新推荐文章于 2023-05-04 09:44:33 发布

阅读量210

点赞数 1

分类专栏： DBA

本文链接：https://blog.csdn.net/weixin_43669585/article/details/89917427

版权

DBA 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

定义:mysql服务软件自带的功能程序，处理表的处理器；

mysql> show engines; #查看可用的存储引擎

Supports transactions, row-level locking, and foreign keys

最常用的存储引擎： innodb myisam
设置mysql服务默认的存储引擎：只是影响新建的表

mysql> SET default_storage_engine=MyISAM; //改用MyISAM引擎 ,只是临时修改默认引擎

[root@host50 ~]# vim /etc/my.cn # 在配置文件修改永久修改

[m ysqld] # 在[mysqld] 下面新加的
default-storage-engine=myisam

mysql> use db3; # 进入udb3库
mysql> create table t1(name char(10));
mysql> show create table t1; #查看新建表中的存储引擎
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`name` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

innodb 特点：

表名.frm 表结构

表名.ibd:表数据+ 表索引

支持行级锁定，支持事务，事务回滚，外建

事务日志文件 /var/lib/mysql

ibdata1

ib_logfile1

ib_logfile0

myisam 特点：

表名.frm 表结构

表名.MYD 表数据

表名.MYI 表索引

支持表级锁

不支持事务，不支持回滚，不支持外键

mysql 锁机制

锁：解决并发访问的冲突问题

锁类型：

读锁（select）共享锁：可以支持并发读

写锁（互斥锁，排他锁）insert,update:是读占锁，上锁期间其他线程不能读表或写表

锁粒度：加锁时的锁的范围

表级锁：给一表加锁（范围大）myisam

行级锁：仅仅给被访问的每一行分别加锁（范围小） innodb

事务：一次访问从建立连接到断开连接的整个过程

事务回滚：在执行事务时任意一步失败，能恢复所有的操作。 rollback

事务特点：ACID

Atomic ：原子性 Consistency：一致性 lsolation :隔离性，durability 持久性

mysql> show variables like "%commit%"; #查看提交状态

mysql> set autocommit=off; # 关闭自动提交

mysql> show variables like "%commit%";

mysql> insert into t3 values (1); # 往t3表里面填入数据
mysql> select * from t3; #如果打开第二个终端，这个命令是显示空的
+------+
| id |
+------+
| 1 |
| 2 |
+------+

mysql> select * from t3; # 打开第二个终端显示为空
Empty set (0.00 sec)

mysql> commit; # 提交数据后第二个终端mysql> select * from t3; 显示数据

mysql> select * from t3; #删除t3表里面的数据

mysql> rollback; # 在未敲commit 时进行事务回滚，可以恢复数据

mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

一。工作中建表如何决定表使用的是哪种存储引擎

select 读锁共享锁

查询访问多的表使用mysam存储引擎，节省系统资源表级锁

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

二。数据导入到出步骤： 1.建表 2导入数据

数据导入：把系统文件内容存储在表里面；

命令格式： load data infile "目录/文件名" into table 库.表 fileds terminated by "分隔符号" line terminated by "\n"

数据导出：把表记录保存到系统文件里；

命令格式：sql查询命令 into outfile "目录/文件名" fields terminated by "分隔符号" line terminated by "\n";

例子：将系统文件中的/etc/password的文件导入表里：

建立表格：

mysql> create table user(
-> name char(50),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(50),
-> homedir char(100),
-> shell char(80)
-> );
mysql> desc user;

[root@host50 db3]# cd /var/lib/mysql-files/
[root@host50 mysql-files]# cp /etc/passwd /var/lib/mysql-files/ #将需要导入的文件复制到数据库里面使用的目录
mysql> load data infile "/var/lib/mysql-files/passwd" into table db3.user fields terminated by ":" lines terminated by "\n";#将系统文件导入数据库里面所建立的user表中
mysql> select * from user;

mysql> alter table user add id int primary key auto_increment first; #增加字段id

例子二：将数据库里面的表格数据导出到文件系统里：

mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a.txt"; # 可以不指定分隔符和换行符号，系统默认给出
mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a1.txt" fields terminated by "#"; # 指定列的分隔符是#
mysql> select * from user where id <= 5 into outfile "/var/lib/mysql-files/a2.txt" fields terminated by "#" lines terminated by " !!! "; # 将查找到的内容导出到文件系统/var/lib/mysql-files/a2.txt中此时指定的列分隔符是#，行的分隔符是！！！
Query OK, 5 rows affected (0.00 sec)
[root@host50 ~]# cd /var/lib/mysql-files/
[root@host50 mysql-files]# ls
a1.txt a2.txt a.txt passwd # 此时该目录下拥有3个文件；

修改检索的目录：

[root@host50 mysql-files]# mkdir /myload #自定义目录
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/myload"

[root@host50 ~]# ll -ld /myload/
drwxr-xr-x. 2 root root 6 5月 7 16:24 /myload/
[root@host50 ~]# chown mysql /myload/ # 给自定义的目录的所属主为mysql
[root@host50 ~]# ll -ld /myload/
drwxr-xr-x. 2 mysql root 6 5月 7 16:24 /myload/
mysql> select * from user where id <= 5 into outfile "/myload/a3.txt" fields terminated by "#" lines terminated by " !!! ";

mysql> select * from user where uid < 100 limit 10; 输出10行

管理表记录： 1增加表记录，2查询记录，3更新表记录，4删除表记录

（1）增加表记录

格式1： insert into 表名 values (字段值列表）

格式2：insert into 表名 values (字段值列表），（字段值列表）；
mysql> insert into user values ( 42,"bob","x",2000,2000,"test user","/home/bob","/bin/bash");#添加一条表记录，给所有字段赋值

mysql> insert into user values ( 43,"bob","x",2000,2000,"test user","/home/bob","/bin/bash") , ( 44,"lucy","x",2000,2000,"test user","/home/bob","/bin/bash"), ( 45,"lilei","x",2000,2000,"test user","/home/bob","/bin/bash"); # 添加3条表记录，给所有字段赋值）

mysql> insert into user(name,uid,gid) values("tom",3000,3000),("you",4000,2000); #给name,uid,gid字段赋值，未给出的赋默认值

注意事项：

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

对于字符类型的字段，要用双引号或者是单引号括起来

依次给所有的字段赋值时，字段名可以省略

只给一部分字段赋值的时，必须明确给出对应的字段的名称；

（2）查询表记录 : select 字段名，字段名 from 表名 where 条件表达式 #

mysql> select name,uid,shell from db3.user where shell="/bin/bash"; # select 后面加的表示显示列内容，where后面是显示行的内容
+-------+------+-----------+
| name | uid | shell |
+-------+------+-----------+
| root | 0 | /bin/bash |
| lisi | 1000 | /bin/bash |
| bob | 2000 | /bin/bash |
| bob | 2000 | /bin/bash |
| lucy | 2000 | /bin/bash |
| lilei | 2000 | /bin/bash |
+-------+------+-----------+
6 rows in set (0.00 sec)
mysql> select * from user where id=10;

（3）更新表记录： update

update 表名 set 字段1=字段1值，字段2=字段2值 where 条件表达式修改字段1 和字段2的值

mysql> update user set password="f",comment="student"; # 将user表里面的password字段的内容更新为 f, comment字段里面的内容更新为 student
mysql> select password,comment from user;
+----------+---------+
| password | comment |
+----------+---------+
| f | student |
| f | student |
| f | student |
| f | student |

mysql> select * from user where name="root";
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | f | 0 | 0 | student | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+

mysql> update user set password="x",comment="root" where name="root";

mysql> select * from user where name="root";
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------

（4）删除表记录

格式1： delect from 表名 where 条件表达式 #仅仅删除符合条件的记录

格式2： delete from 表名； #删除所有的表记录

mysql> delete from user where id>=43; # 删除id>=43的所有行

匹配条件（update delete select ）

基本匹配条件

数值比较：= > >= < <= !=

字符比较：= ！= is null is not null

逻辑匹配：多个条件使用时， or and ! ()提高优先级

mysql> select * from user where name="syn" and uid=20 and shell="/bin/bash";

mysql> select * from user where name="syn" or uid=20 or shell="/bin/bash";

范围内匹配： in （在，，里） not in (不在，，里) between (在，，之间) distinct (去重显示)

in ( 值列表）

not in (值列表）

between 数字1 and 数字2

distinct 字段名

高级匹配条件

模糊查询： where 字段名 like ‘通配符’

_ 匹配单个字符， % 匹配0～n 个字符

mysql> select name from user where name like '___'; #___表示3个下划线，即是_ _ _ ,输出name 字段里面的内容只有3个字符的

mysql> select name from user where name like '%a%'; # 输出name字段内容里面的是包含a的
mysql> select name from user where name like '_ _%_ _'; # 输出name字段内容里面含有4个字符以及4个字符以上的
正则表达式：

格式： where 字段名 regexp '正则表达式'

正则元字符：^ $ . [] * |

mysql> select name from user where name regexp '^a|t$'; # 输出name 字段里面以a 开头或者以t结尾的内容
mysql> select name from user where name regexp '^a.*t$'; # 输出name 字段里面以a 开头以t结尾的内容，中间的内容是任意的
mysql> select name from user where name regexp '[0-9]';#

四则运算

字段必须是数字类型的：+ - * / % （取余）

mysql> select name,uid,gid,uid+gid he from user where id <= 10;
+----------+------+------+------+
| name | uid | gid | he |
+----------+------+------+------+
| root | 0 | 0 | 0 |
| bin | 1 | 1 | 2 |

mysql> update user set uid=uid+1 where name="root";

mysql> update user set uid=uid+1;
mysql> select name,uid,gid,(uid+gid)/2 pjz from user where name="bin";

操作查询结果

聚集函数：服务内置的对数据统计的命令

avg(字段名) # 统计字段平均值

sum(字段名) # 统计字段之和

min(字段名) #统计字段内容里最大值

max(字段名） # 统计字段里面最大值

count(字段名） # 统计字段值个数

mysql> select max(uid) from user;

mysql> select min(uid) from user;
mysql> select name from user where shell!="/sbin/nologin";
mysql> select count(name) from user where shell!="/sbin/nologin";
mysql> select * from user; # 查询所有表的记录；
mysql> select count(*) from user; # 统计表记录个数；所有行的行数，所有列里面的内容的个数

查询结果排序：

格式： sql 查询命令 order by 字段名 [asc|desc]

sql 查询命令 group by 字段名

sql 查询命令 where 条件 having 条件表达式

sql 查询命令 group by 字段名 having 条件表达式子

mysql> select name,uid from user where uid <= 500 order by uid desc; # uid降序
mysql> select name,uid from user where uid <= 500 order by uid asc; # uid 升序
mysql> select shell from user where uid >= 10 group by shell;
mysql> select distinct shell from user where uid >= 10; # 绿色字体的查询结果是一样的，只是执行的动作不一样，后面那条比较耗费cpu
mysql> select name from user where shell!="/bin/bash" having name="nobody";
mysql> select name from user where name="nobody" and shell!="/bin/bash"; 蓝色这两条的操作结果是一样的，只是后面那条比较耗费cpu

限制查询结果行数：

格式： sql 查询命令 limit N；查询结果显示前N条记录

sql 查询命令 limit N,M //显示指定范围内的查询记录

sql 查询命令 where limit 3; // 查询结果显示前3条记录

sql 查询命令 limit 3，3 //从第4调开始，共显示3条记录

mysql> select name,uid from user where uid <= 10 limit 1;
mysql> select name,uid from user where uid <= 10 limit 3;
mysql> select name,uid from user where uid <= 10 limit 3,3;
mysql> select * from user where uid >= 100 and uid <=65535 order by uid desc limit 5;

Jiali Li

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql存储引擎

定义:mysql服务软件自带的功能程序，处理表的处理器；mysql> show engines; #查看可用的存储引擎Supports transactions, row-level locking, and foreign keys最常用的存储引擎： innodb myisam设置mysql服务默认的存储引擎：只是影响新建的表mysql> SET def...
复制链接

扫一扫