Linux_MySQL二次整理(2)

配置文件參數

服務器參數/變量：設定MySQL的運行特性；

顯示:

mysql> SHOW GLOBA|[SESSION] VARIABLES [LIKE clause];  LIKE 過濾

狀態（統計）參數/變量：保存MySQL運行中的統計數據或狀態數據；

mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];

顯示單個變量設定值的方法：精確匹配

mysql> SELECT @@[global.|session.]system_var_name

%：匹配任意長度的任意字符；

_：匹配任意單個字符；

變量/參數級別：

全局(GLOBA)：為所有會話設定默認； root權限才能修改

會話(SESSION)：跟單個會話相關；會話建立會從全局繼承；

服務器變量的調整方式：

運行時修改：

mysql> HELP SET

SET [GLOBAL | SESSION] system_var_name = expr

SET [@@global. | @@session. | @@]system_var_name = expr

啟動前通過配置文件修改：

安裝完成后的安全初始化：

mysql_secure_installation

刪除匿名用戶 管理員設置密碼

運行前常修改的參數：

innodb_file_per_table=ON innodb引擎每表使用單獨表空間

skip_name_resolve=ON 禁止dns解析

...

SQL：ANSI SQL 標準

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ... (還分級別.級別高限制越嚴格 性能越低)

MySQL的數據類型：

字符型

數值型

日期時間型

內建類型

字符型：

CHAR(#)， BINARY：定長型；CHAR不區分字符大小寫，而BINARY區分；性能不好

VARCHAR(#)， VARBINARY：變長型

TEXT：TINYTEXT(1字節)，TEXT(2)，MEDIUMTEXT(3)，LONGTEXT(4) 文本

BLOB：TINYBLOB(255字節)，BLOB(65k)，MEDIUMBLOB(16M)， LONGBLOB(4G) 二進制大對象

數值型：

浮點型：近似

FLOAT

DOUBLE

REAL

BIT

整型：精確

INTEGER：TINYINT(1)，SMALLINT(2)，MEDIUMINT(3)，INT(4)，BIGINT(8)

DECIMAL

日期時間型：

日期：DATE

時間：TIME

日期j時間：DATETIME

時間戳：TIMESTAMP

年份：YEAR(2), YEAR(4)

內建：

ENUM：枚舉

ENUM('Sun','Mon','Tue','Wed') 只能填入枚舉定義的某一個數據

SET：集合 一堆字符中的各種組合

類型修飾符：

字符型：

NOT NULL 非空

NULL 可以空

DEFALUT ‘STRING’默認值

CHARACET SET ‘CHARSET’ 字符集格式

COLLATION ‘collocation' 排序規則

整型：

NOT NULL

  NULL

 DEFALUT value 不能加引號

 AUTO_INCREMENT 自增

 UNSIGNED 無符號整數

日期時間型：NOT NULL， NULL， DEFAULT 

*************************************************

SQL MODE：定義mysqld對約束等違反時的響應行為等設定；(比如數據類型錯誤或溢出)

常用的MODE：

TRADITIONAL 傳統限制方式

STRICT_TRANS_TABLES 支持事物操作的表做限制

STRICT_ALL_TABLES 對所有表都進行限制

修改方式：

mysql> SET GLOBAL sql_mode='MODE';

mysql> SET @@global.sql_mode='MODE'; 

再次插入大于255的數值則報錯;

本次有效,重啟失效 要修改配置文件里的數值

*******************************************************

數據庫：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name  COLLATE [=] collation_name

創建 數據庫 [如果不存在] 數據庫名稱  指定字符集 排序規則

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name  COLLATE [=] collation_name

修改 數據庫  數據庫名稱  字符集 排序規則

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

刪除  數據庫  [如果存在] 數據庫名

表：

CREATE 

(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    #TEMPORARY 臨時表

(create_definition,...)       #字段定義格式

[table_options] # 表選項

[partition_options] # 表分區選項

CREATE TABLE [IF NOT EXISTS] tble_name (col_name  data_type|INDEX|CONSTRAINT);

例:MariaDB>CREATE TABLE student (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,name CHAR(50) NOT NULL ,age TINYINT UNSIGNED,INDEX(name) );
 

(2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    復制表數據

[(create_definition,...)]

[table_options]

[partition_options]

select_statement

直接創建表，并將查詢語句的結果插入到新創建的表中；

例:MariaDB[mydb]>CREATE TABLE users(Name VARCHAR(100),Host VARCHAR(100),Passwd CHAR(48)) SELECT User AS Name,Host,Password As passwd  FROM mysql.user;

如果新建表的列名稱和原來的不對應 則必須 指定名稱  old_name As new_name;

查詢mysql.user中的數據并導入到新建的(users表)中;

(3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    復制表結構創建

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

例:CREATE TABLE user LIKE mysql.user;

查看表數據: SELECT * FROM user;

查看表結構:DESC user;

復制某存在的表的結構來創建新的空表；數據不會被復制

***************

table_options：

ENGINE [=] engine_name 存儲引擎

查看支持的所有存儲引擎：

mysql> SHOW ENGINES;

查看指定表的存儲引擎：

mysql> SHOW TABLE STATUS LIKE clause;

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

行格式 

*************************

DROP:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name];

ALTER：

ALTER  TABLE tbl_name

[alter_specification [, alter_specification] ...]

可修改內容：alter_specification

(1) table_options

(2) 添加定義：ADD

字段、字段集合、索引、約束

(3) 修改字段：

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] 修改名字和選項

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] 只能修改名字

(4) 刪除操作：DROP

字段、索引、約束

表重命名：

RENAME [TO|AS] new_tbl_name

查看表結構定義：

DESC tbl_name；

查看表定義：

SHOW CREATE TABLE tbl_name

查看表屬性信息：

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

例:SHOW TABLESTATUS [FROM mysql] LIKE user; 不指定表 表示當前表

***************************************

索引：

創建：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)

創建 為一|全文|空間  索引 索引名稱 索引類型

例: CREATE INDEX name_on_students ON students(Name);

查看：

SHOW  {INDEX | INDEXES | KEYS}  {FROM | IN}  tbl_name  [{FROM | IN}  db_name]  [WHERE expr]

例:SHOW INDEX FROM students [WHERE Column_name=’Name’];

刪除：

DROP  INDEX index_name ON tbl_name

例: DROP INDEX name_on_students ON students;

****************************************

索引類型：

聚集索引、非聚集索引：看 索引是否與數據存在一起；

主鍵索引、輔助索引

稠密索引、稀疏索引：是否索引了每一個數據項； 稠密索引表示:每個索引對應的數據都存在 稀疏相反 一般一級索引是稠密 二級三級有可能不是稠密

BTREE（B+）平衡樹索引

HASH

R Tree 空間索引

FULLTEXT   全文索引

BTREE：左前綴；

EXPLAIN：分析查詢語句的執行路徑；

視圖：VIEW

虛表：存儲下來的SELECT語句；(對某些用戶隱藏字段)

創建：

CREATE  VIEW view_name [(column_list)] AS select_statement

修改：

ALTER  VIEW view_name [(column_list)] AS select_statement

刪除：

DROP VIEW [IF EXISTS] view_name [, view_name] ...