mysql中表的知识点总结
发布时间：2022-01-19 13:25:40 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍mysql中表的知识点总结，在日常操作中，相信很多人在mysql中表的知识点总结问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答mysql中表的知识点总结的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 1. 表
 这篇文章主要介绍“mysql中表的知识点总结”，在日常操作中，相信很多人在mysql中表的知识点总结问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql中表的知识点总结”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 1. 表 创建表的原则： l 禁止使用中文做字段名； l 禁止使用字符型做主键； l 禁止无主键或唯一索引； 1.1. 整型 int、tinyint是使用最多的整型类型。 主键选择： 为什么要选择ID做主键：主键字段要选择不经常修改的、尽量与业务无关、无具体含义的。因为InnoDB是索引组织表，需要保证索引结构不经常防撞，避免造成性能的消耗。 int(4) 和int(10)都是占用4字节空闲，区别就是数据库中表示的数据位数，一个4位一个10位。有些在数字前自动用0补位。 1.2. 浮点型 常用decimal存储金钱字段，但在运算过程中会转成浮点运算，且会出现四舍五入的情况，建议使用int类型。 decimal(M,D); D是小数部分位数，超过小数部分四舍五入截断，不足补足； M是整数部分加小数部分的总长度，即插入整数部分不能超过M-D位，否则插入失败。 1.3. 时间类型 datetime:5.6前占8个字节，5.6后占5个字节，可用范围比timestamp大，物理存储上仅比timestamp多一个字节； 可以用int存储时间，通过两个函数转换:unix_timestamp和from_unixtime。 [mysql]>select unix_timestamp('2018-11-06 16:42:00'); +---------------------------------------+ | unix_timestamp('2018-11-06 16:42:00') | +---------------------------------------+ | 1541493720 | +---------------------------------------+ [mysql]>select from_unixtime(1541493720); +---------------------------+ | from_unixtime(1541493720) | +---------------------------+ | 2018-11-06 16:42:00 | +---------------------------+ 从5.6开始，datetime和timestamp都支持自动更新为当前时间。 1.4. 字符串类型 text和blob这种大数据类型建议不要和业务表放一起。 char和varchar区别 char：用于定长字符串，范围0~255，不够空格补全存库；超过截断； varchar:变长，范围0~65535，不够不补；超过截断；可节约空间提高存储效率。 varchar多用一到两个字节记录长度，数据位占用字节小于255用1字节记录长度；超过255则用2字节；还有一位用来记录是否为null值； 示例：varchar(100) UTF8字符集：存储空间100*3+1=301字节； GBK字符集：存储空间100*2+1=201字节； mysql每行最大字节数65535，不同字符集下字符最大长度； 使用UTF8，每个字符最多占3个字节，最大长度不能超过（65535-1-2）/3=21844 使用GBK，每个字符2字节，最大长度不超过(65535-1-2)/2=36766 存储IP,推荐用int存储，使用inet_aton和inet_ntoa两个参数； [mysql]>select inet_aton('10.98.156.210'); +----------------------------+ | inet_aton('10.98.156.210') | +----------------------------+ | 174234834 | +----------------------------+ [mysql]>select inet_ntoa(174234834); +----------------------+ | inet_ntoa(174234834) | +----------------------+ | 10.98.156.210 | +----------------------+ 1.5. 字符集 mysql数据库字符集包括字符集（character）和校对规则（collation）两个概念。 字符集：定义mysql数据库字符串的存储方式； 校对规则：定义比较字符串的方式。 常用字符集： l GBK：每个汉字两个字节。 l Latin1：停用。5.1前默认，1汉字或字母占1字节。 l UTF8：每个汉字3个字节。 l UTF8mb4:是utf8的超集，每个汉字4个字节。5.7建议使用。 数据库配置文件中相关参数 [mysql]>show variables like '%character%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/charsets/ | +--------------------------+----------------------------------+ 避免汉字乱码，做到三线统一； 连接端的字符集必须是UTF8; 操作系统字符集必须是UTF8, mysql数据库字符集必须是UTF8； [mysql]>s -------------- mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 9 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.24-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 3 hours 6 min 28 sec Threads: 1 Questions: 299 Slow queries: 3 Opens: 372 Flush tables: 1 Open tables: 144 Queries per second avg: 0.026 -------------- 数据库临时修改字符集：命令行执行set names 字符集名称; 如：set names utf8 1.6. 表碎片产生的原因 delete操作产生数据碎片，碎片占用磁盘空间且读取效率低。 1.7. 碎片计算方法及整理过程 查看表统计信息： [mysql]>show table status like 't'G; *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 3 Create_time: 2018-11-06 10:20:10 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) 碎片大小计算 碎片大小=数据总大小-实际表空间文件大小 数据总大小=data_length+index_length 实际表空间文件大小=rows*avg_row_length 碎片大小MB=（数据总大小-实际表空间文件大小）/1024/1024 清除碎片的两种方法： alter table table_name engine=innodb；会给表加写锁，时间长。 备份原表数据，删掉，重新导入新表中； 在线整理表结构的工具pt-online-schema-charge； Mysql 5.7后已支持在线online ddl了。 1.8. 表统计信息 统计每个库大小 SELECT table_schema, sum(data_length) / 1024 / 1024 / 1024 AS data_length, sum(index_length) / 1024 / 1024 / 1024 AS index_length, sum(data_length + index_length) / 1024 / 1024 / 1024 AS sum_data_index FROM information_schema. TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_SCHEMA != 'mysql' GROUP BY table_schema; 统计库中每个表大小 SELECT TABLE_NAME, SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'mysql' GROUP BY TABLE_NAME; 统计所有数据的大小 SELECT SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE FROM information_schema.`TABLES`; 1.9. 统计信息的收集方法 l 遍历information_schema_tables，收集su表的统计信息； [mysql]>select * from information_schema.tables where table_name='t' G; *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: mysql TABLE_NAME: t TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 2 AVG_ROW_LENGTH: 8192 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: 3 CREATE_TIME: 2018-11-06 10:20:10 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: l 重启mysql实例 l show table status like ‘%table_name%’; [mysql]>show table status like 't'G; *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 2 Avg_row_length: 8192 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 3 Create_time: 2018-11-06 10:20:10 Update_time: NULL Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) 1.10. MySQL库表常用命令总结 登陆同时修改命令提示符:主机-用户-数据库-日期 mysql -uroot -p --prompt='h-u-d-D' 退出：exit quit q 命令行结束符号：；或g c 取消当前命令执行，就不用删除了； MySQL注释： # 或 -- s 实例信息； show database;显示实例下所有数据库； show schemas; 显示实例下所有数据库； show warings;查看警告 use database;选择数据库； show full processlist;查看数据库当前连接情况； select user();得到登陆用户 select version();得到版本信息； select now();得到当前日期时间； seleect database();得到当前打开数据库； create database db_name；创建数据据库； create databse if not exists test1; 检测数据库不存在则创建 create databse if not exists test1 default character set 'utf8';创建时指定编码方式 show create database dbname;查看数据库信息 alter databse dbname default character set 'gbk';修改指定数据库的编码方式 drop database db_name;删除数据库； show tables;查看库下所有表； show create table tab_name G; 查看建表语句； desc tab_name;查看表结构； show table status;获取表基础信息； show index from tab_name;查看当前表下索引情况； create table tab_name:创建表； drop table tab_name;删除表包括结构； select * from tab_name; delete from tab_name where ;或truncate table tab_name; insert into tab_name (字段列表) values（对应字段值） update tab_name set :字段名=某值（where）; 到此，关于“mysql中表的知识点总结”的学习就结束了，希望能够解决大家的疑惑。 （编辑：92站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
