mysql> select * from user; +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes | +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ | % | zachary | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$B?<M[emkFiH6r2qdU1jhF3ONtn8Pjzx.2mGkUGv24EjzSFpo6J6qZzZm9 | N | 2021-07-01 21:15:28 | NULL | N | N | N | NULL | NULL | NULL | NULL | | localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-02-16 20:09:44 | NULL | Y | N | N | NULL | NULL | NULL | NULL | | localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-02-16 20:09:44 | NULL | Y | N | N | NULL | NULL | NULL | NULL | | localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2021-02-16 20:09:44 | NULL | Y | N | N | NULL | NULL | NULL | NULL | | localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$[v3;mqn}dytE6eHf1cnInBjCR5s.3TE1uHerOPadrzC83pYLSQCfO1y4 | N | 2021-02-16 20:13:33 | NULL | N | Y | Y | NULL | NULL | NULL | NULL | +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ 5 rows in set (0.00 sec)
表数据精确查看
但是这样数据太多又很乱，我们可以只选择其中一部分字段 select host,user from user;
创建表的时候，如果表不存在就执行这一条，如果表不存在就不执行这一条命令 create table if not exists 表名(字段名 1 类型, 字段名 2 类型)engine=innodb default charset=utf8;
1 2 3 4 5 6
createtable if notexists users( id intnotnullprimary key auto_increment, name varchar(4) notnull, age tinyint, gender enum('男','女') )engine=innodb default charset=utf8;
删除表 drop table 表名; 表结构 desc 表名; 查看建表语句 show create table 表名;
mysql> show create table users\G; *************************** 1. row *************************** Table: users Create Table: CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(4) NOT NULL, `age` tinyint DEFAULT NULL, `gender` enum('男','女') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
# 直接查看当前表引擎 mysql> show table status from tlxy where name = 'users'\G; *************************** 1. row *************************** Name: users Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 4 Create_time: 2022-04-10 16:10:12 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec)
ERROR: No query specified
# 修改表引擎语句 alter table users engine = 'myisam';
6.删除表
语法格式 drop table 表名;
详解 MySQL 存储引擎
客户端向服务器发送的各类增删查改的请求
1 2 3 4 5 6 7 8 9 10
graph TD A[客户端] --> B(处理连接) B --> C(查询缓存) C --> D(语法解析) D --> E{查询优化} E --> F[MyISAM] E --> G[InnoDB] E --> H[Memory] E --> I[...] G --> J(文件系统)
MySQL 服务器把数据的存储和提取都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL 提供了各式各样的存储引擎，不同的存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。
存储引擎
存储引擎以前叫做表处理器，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。
为了管理方便，人们把 连接管理、查询缓存、语法解析、查询优化这些并不涉及在真实数据存储的功能划分为 MySQL server 的功能，把真实存储数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（即 存储引擎 API），包含了几十个底层函数，像“读取索引第一条内容”、“读取索引下一条内容”、“插入记录”等等。
所以在 MySQL server 完成了查询优化之后，只需要按照生成的执行计划调用底层存储引擎提供的 API，获取到数据后返回给客户端就好啦
MyISAM：保存有表的总行数，如果 select count() from table;会直接取出改值 InnoDB：没有保存表的总行数(只能遍历)，如果使用 select count() from table;就会遍历整个表，消耗相当大，但是在加了 where 条件之后，MyISAM 和 InnoDB 处理的方式是一样的