mysql學習10：第五章:表
建立表的原則：
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）;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29519108/viewspace-2218966/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 學習opencv 習題答案-第五章OpenCV
- 深入學習MySQL授權表MySql
- mysql修改表欄位學習筆記MySql筆記
- MySql 學習筆記四：表的拆分MySql筆記
- Laravel 第五章學習——使用者模型Laravel模型
- MySQL學習之全域性鎖和表鎖MySql
- 【Mysql 學習】 MERGE表方面的問題(二)MySql
- 學習MySQLMySql
- MySQL 學習MySql
- Mysql學習MySql
- Ext學習筆記10-表單續筆記
- MySQL優化學習手札(四) 單表訪問方法MySql優化
- MYSQL學習（二） --MYSQL框架MySql框架
- Mudo C++網路庫第五章學習筆記C++筆記
- 《Java核心技術》第五章 繼承 學習筆記Java繼承筆記
- MySQL學習 - 索引MySql索引
- MySQL深度學習MySql深度學習
- Mysql學習教程MySql
- mysql學習（一）MySql
- mysql學習（二）MySql
- mysql學習二MySql
- 【Mysql 學習】字串MySql字串
- (轉):學習Oracle動態效能表-(10)-V$FILESTATOracle
- 【Mysql 學習】Mysql 日誌(一)MySql
- 【Mysql 學習】mysql 字符集MySql
- 【Mysql 學習】Mysql 儲存引擎MySql儲存引擎
- ES6學習 第五章 正則的擴充套件套件
- 雲原生入門 第五章：kubernetes學習實踐
- Oracle外部表學習Oracle
- oracle學習（建立表）Oracle
- oracle IOT表學習Oracle
- MySql學習筆記MySql筆記
- MySQL學習之索引MySql索引
- MySQL學習之行鎖MySql
- MySQL學習記錄MySql
- mysql學習資料MySql
- MySQL分割槽學習MySql
- MYSQL-mysqldump學習MySql