mysql数据库的存储引擎和匹配条件

最新推荐文章于 2022-12-28 10:46:28 发布

原创最新推荐文章于 2022-12-28 10:46:28 发布 · 314 阅读

0 ·

CC 4.0 BY-SA版权

云计算专栏收录该内容

26 篇文章

订阅专栏

本文深入解析MySQL中的存储引擎，包括MyISAM和InnoDB的特点、事务处理、锁机制及如何修改默认存储引擎。同时，文章涵盖了SQL匹配条件、操作查询结果的多种方式，如聚合函数、排序、分组和限制查询行数等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储引擎

自带的软件功能程序，是表的处理器

默认的存储引擎
mysql 5.0/5.1 myisam	支持表级锁，不支持事务回滚，外键，表名.frm（表结构）表名.myi 表名.myd
mysql 5.5/5.6及以上 innodb	支持行级锁，支持事务回滚，外键，表名.frm（表结构），表名.ibd（表空间），ibdata1，ib_logfile0/1

列出可用的存储引擎类型

Mysql> show engines\G;
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
...
*************************** 9. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 10. row ***************************
      Engine: Aria
     Support: YES
     Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
          XA: NO
  Savepoints: NO

修改表的存储引擎

建表时手动指定，如果未指定，使用默认引擎

mysql> create database db;
Query OK, 1 row affected (0.00 sec)
mysql> use db；
Database changed
mysql> create table test( id int(4) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以修改默认存储引擎

msyql> system vim /etc/my.cnf
[mysqld]
...
default-storage-engine=mysiam    #引擎名称根据实际需要修改

存储引擎特点

MyISAM	支持表级锁，不支持事务、事务回滚、外键相关文件有表名.frm 表名.MYI 表名.MYD
InnoDB	支持行级锁定，支持事务、事务回滚、外键相关文件有表名.frm 表名.ibd ibdata1 ib_logfile0 ib_logfile1

MySQL锁机制

查看当前的锁状态检查Table_lock开头的变量，% 作通配符

mysql> show status like 'Table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 36    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

事务特性（ACID)

Atomic-原子性:事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败

Consistency-一致性：事务操作的前后，表中的记录没有变化

Isolation-隔离性：事务操作是相互隔离不受影响的

Durability-持久性：数据一旦提交，不可改变，永久改变表数据

mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit=off；            //关闭自动给提交
Query OK, 0 rows affected (0.00 sec) 

mysql> select * from test;
Empty set (0.00 sec)
mysql> insert test values(231);
Query OK, 1 row affected (0.00 sec)
mysql> rollback；                     //数据回滚
Query OK, 0 rows affected (0.01 sec)
MariaDB [db]> commit；                //提交数据
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)


mysql> insert test values(123);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+
| id   |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

匹配条件

基本匹配

数值比较（字段类型必须为数据数值类型） = , > >= , < <=, !=(不等于)

字符比较（字段类型必须为字符） = ，!=, IS NULL(匹配空), IS NOT NULL（非空）

逻辑匹配（多个判断条件时用） OR（逻辑或）; AND（逻辑与）; !（逻辑非）; () (提高优先级)

范围内匹配（匹配范围内的任意一个值） in （值列表） 在...里... ； not in （值列表）不在...里...；

between 数字1 and 数字 2 在 ...之间 ...； distinct 字段名 去重显示

高级匹配

模糊查询 where 字段名 like ‘通配符’ (注： _ 匹配单个字符 % 匹配0~N个字符）

mysql> select * from test where id like '1_3';
+------+
| id   |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

mysql> select * from test where id like '1%';
+------+
| id   |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

正则表达式 where 字段名 regexp ‘正则表达式’

（正则元字符 ^匹配值行首 $匹配值结尾 .匹配任意单个字符 *匹配子表达式任意次 [] 字符集合，匹配其中任意 | 或）

mysql> select * from test where id regexp '^1';
+------+
| id   |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

四则运算（字段必须为数值类型） + - * / %

操作查询结果

1.聚集函数：avg(字段名)统计字段平均值，sum(字段名) 字段之和 ， min(字段名) 字段最小,max(字段名) 字段最大，count(字段名) 个数

2.查询结果排序：SQL查询 order by 字段名-通常是数值 [asc | desc ]-升/降序;

3.查询结果分组：SQL查询 group by 字段名-通常是字符类型；

4.查询结果过滤：

SQL查询 having 条件表达式；

SQL查询 where 条件 having 条件表达式；

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

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

SQL查询 limit N； 显示查询结果前N条记录

SQL查询 limit N，M； 显示指定范围内的查询结果

SQL查询 where 条件查询 limit 3； 显示查询结果前3条记录

SQL查询 where 条件查询 limit 3，3； 从第四条开始，共显示3条

mysql> CREATE TABLE student(
    -> name varchar(12) NOT NULL,
    -> gender enum('boy','girl') DEFAULT 'boy',
    -> age int(3) NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)


mysql> INSERT INTO student VALUES
    -> ('Jim','girl',24),
    -> ('Tom','boy',21),
    -> ('Lily','girl',20),
    -> ('Jerry','boy',27),
    -> ('Mike','boy',21)
    -> ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT count(*) FROM student;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT avg(age),max(age),min(age) FROM student;
+----------+----------+----------+
| avg(age) | max(age) | min(age) |
+----------+----------+----------+
|  22.6000 |       27 |       20 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT count(gender) FROM student WHERE gender='boy';
+---------------+
| count(gender) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM student WHERE age>21;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student WHERE age BETWEEN 20 and 24;
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
| Lily | girl   |  20 |
| Mike | boy    |  21 |
+------+--------+-----+
4 rows in set (0.00 sec)

ysql> SELECT * FROM student WHERE age < 23 AND gender='girl';
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Lily | girl   |  20 |
+------+--------+-----+
1 row in set (0.00 sec)

mysql> SELECT * FROM student WHERE name IN
    -> ('Jim','Tom','Mickey','Minnie');
+------+--------+-----+
| name | gender | age |
+------+--------+-----+
| Jim  | girl   |  24 |
| Tom  | boy    |  21 |
+------+--------+-----+
2 rows in set (0.00 sec)

mysql> SELECT 1234*5678;
+-----------+
| 1234*5678 |
+-----------+
|   7006652 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT name,age+15 FROM student;
+-------+--------+
| name  | age+15 |
+-------+--------+
| Jim   |     39 |
| Tom   |     36 |
| Lily  |     35 |
| Jerry |     42 |
| Mike  |     36 |
+-------+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM student GROUP BY age ASC;
+-------+--------+-----+
| name  | gender | age |
+-------+--------+-----+
| Lily  | girl   |  20 |
| Tom   | boy    |  21 |
| Jim   | girl   |  24 |
| Jerry | boy    |  27 |
+-------+--------+-----+
4 rows in set (0.00 sec)