这篇文章主要介绍“MySQL中表的知识点总结”,在日常操作中,相信很多人在mysql中表的知识点总结问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”mysql中表的知识点总结”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
创新互联服务项目包括鼓楼网站建设、鼓楼网站制作、鼓楼网页制作以及鼓楼网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,鼓楼网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到鼓楼省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
创建表的原则:
l 禁止使用中文做字段名;
l 禁止使用字符型做主键;
l 禁止无主键或唯一索引;
int、tinyint是使用最多的整型类型。
主键选择:
为什么要选择ID做主键:主键字段要选择不经常修改的、尽量与业务无关、无具体含义的。因为InnoDB是索引组织表,需要保证索引结构不经常防撞,避免造成性能的消耗。
int(4) 和int(10)都是占用4字节空闲,区别就是数据库中表示的数据位数,一个4位一个10位。有些在数字前自动用0补位。
常用decimal存储金钱字段,但在运算过程中会转成浮点运算,且会出现四舍五入的情况,建议使用int类型。
decimal(M,D);
D是小数部分位数,超过小数部分四舍五入截断,不足补足;
M是整数部分加小数部分的总长度,即插入整数部分不能超过M-D位,否则插入失败。
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都支持自动更新为当前时间。
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 |
+----------------------+
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
delete操作产生数据碎片,碎片占用磁盘空间且读取效率低。
查看表统计信息:
[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了。
统计每个库大小
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`;
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)
登陆同时修改命令提示符:主机-用户-数据库-日期 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中表的知识点总结”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!