mysql中表的知識點總結

這篇文章主要介紹“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中表的知識點總結”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注創新互聯網站，小編會繼續努力為大家帶來更多實用的文章！

分享標題：mysql中表的知識點總結
標題網址：http://m.kartarina.com/article18/jedhgp.html

成都網站建設公司_創新互聯，為您提供服務器托管、營銷型網站建設、網站收錄、品牌網站設計、軟件開發、App開發

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創新互聯

猜你還喜歡下面的內容