Linux_MySQL二次整理(2)

配置文件參數(shù)

服務(wù)器參數(shù)/變量：設(shè)定MySQL的運(yùn)行特性；

顯示:

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

狀態(tài)（統(tǒng)計(jì)）參數(shù)/變量：保存MySQL運(yùn)行中的統(tǒng)計(jì)數(shù)據(jù)或狀態(tài)數(shù)據(jù)；

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

顯示單個(gè)變量設(shè)定值的方法：精確匹配

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

%：匹配任意長(zhǎng)度的任意字符；

_：匹配任意單個(gè)字符；

變量/參數(shù)級(jí)別：

全局(GLOBA)：為所有會(huì)話設(shè)定默認(rèn)； root權(quán)限才能修改

會(huì)話(SESSION)：跟單個(gè)會(huì)話相關(guān)；會(huì)話建立會(huì)從全局繼承；

服務(wù)器變量的調(diào)整方式：

運(yùn)行時(shí)修改：

mysql> HELP SET

SET [GLOBAL | SESSION] system_var_name = expr

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

啟動(dòng)前通過配置文件修改：

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

mysql_secure_installation

刪除匿名用戶 管理員設(shè)置密碼

運(yùn)行前常修改的參數(shù)：

innodb_file_per_table=ON innodb引擎每表使用單獨(dú)表空間

skip_name_resolve=ON 禁止dns解析

...

SQL：ANSI SQL 標(biāo)準(zhǔn)

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

MySQL的數(shù)據(jù)類型：

字符型

數(shù)值型

日期時(shí)間型

內(nèi)建類型

字符型：

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

VARCHAR(#)， VARBINARY：變長(zhǎng)型

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

BLOB：TINYBLOB(255字節(jié))，BLOB(65k)，MEDIUMBLOB(16M)， LONGBLOB(4G) 二進(jìn)制大對(duì)象

數(shù)值型：

浮點(diǎn)型：近似

FLOAT

DOUBLE

REAL

BIT

整型：精確

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

DECIMAL

日期時(shí)間型：

日期：DATE

時(shí)間：TIME

日期j時(shí)間：DATETIME

時(shí)間戳：TIMESTAMP

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

內(nèi)建：

ENUM：枚舉

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

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

類型修飾符：

字符型：

NOT NULL 非空

NULL 可以空

DEFALUT ‘STRING’默認(rèn)值

CHARACET SET ‘CHARSET’ 字符集格式

COLLATION ‘collocation' 排序規(guī)則

整型：

NOT NULL

  NULL

 DEFALUT value 不能加引號(hào)

 AUTO_INCREMENT 自增

 UNSIGNED 無符號(hào)整數(shù)

日期時(shí)間型：NOT NULL， NULL， DEFAULT 

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

SQL MODE：定義mysqld對(duì)約束等違反時(shí)的響應(yīng)行為等設(shè)定；(比如數(shù)據(jù)類型錯(cuò)誤或溢出)

常用的MODE：

TRADITIONAL 傳統(tǒng)限制方式

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

STRICT_ALL_TABLES 對(duì)所有表都進(jìn)行限制

修改方式：

mysql> SET GLOBAL sql_mode='MODE';

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

再次插入大于255的數(shù)值則報(bào)錯(cuò);

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

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

數(shù)據(jù)庫：

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

創(chuàng)建 數(shù)據(jù)庫 [如果不存在] 數(shù)據(jù)庫名稱  指定字符集 排序規(guī)則

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

修改 數(shù)據(jù)庫  數(shù)據(jù)庫名稱  字符集 排序規(guī)則

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

刪除  數(shù)據(jù)庫  [如果存在] 數(shù)據(jù)庫名

表：

CREATE 

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

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

[table_options] # 表選項(xiàng)

[partition_options] # 表分區(qū)選項(xiàng)

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    復(fù)制表數(shù)據(jù)

[(create_definition,...)]

[table_options]

[partition_options]

select_statement

直接創(chuàng)建表，并將查詢語句的結(jié)果插入到新創(chuàng)建的表中；

例: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;

如果新建表的列名稱和原來的不對(duì)應(yīng) 則必須 指定名稱  old_name As new_name;

查詢mysql.user中的數(shù)據(jù)并導(dǎo)入到新建的(users表)中;

(3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name    復(fù)制表結(jié)構(gòu)創(chuàng)建

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

例:CREATE TABLE user LIKE mysql.user;

查看表數(shù)據(jù): SELECT * FROM user;

查看表結(jié)構(gòu):DESC user;

復(fù)制某存在的表的結(jié)構(gòu)來創(chuàng)建新的空表；數(shù)據(jù)不會(huì)被復(fù)制

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

table_options：

ENGINE [=] engine_name 存儲(chǔ)引擎

查看支持的所有存儲(chǔ)引擎：

mysql> SHOW ENGINES;

查看指定表的存儲(chǔ)引擎：

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] ...]

可修改內(nèi)容：alter_specification

(1) table_options

(2) 添加定義：ADD

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

(3) 修改字段：

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] 修改名字和選項(xiàng)

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

(4) 刪除操作：DROP

字段、索引、約束

表重命名：

RENAME [TO|AS] new_tbl_name

查看表結(jié)構(gòu)定義：

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; 不指定表 表示當(dāng)前表

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

索引：

創(chuàng)建：

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

創(chuàng)建 為一|全文|空間  索引 索引名稱 索引類型

例: 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;

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

索引類型：

聚集索引、非聚集索引：看 索引是否與數(shù)據(jù)存在一起；

主鍵索引、輔助索引

稠密索引、稀疏索引：是否索引了每一個(gè)數(shù)據(jù)項(xiàng)； 稠密索引表示:每個(gè)索引對(duì)應(yīng)的數(shù)據(jù)都存在 稀疏相反 一般一級(jí)索引是稠密 二級(jí)三級(jí)有可能不是稠密

BTREE（B+）平衡樹索引

HASH

R Tree 空間索引

FULLTEXT   全文索引

BTREE：左前綴；

EXPLAIN：分析查詢語句的執(zhí)行路徑；

視圖：VIEW

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

創(chuàng)建：

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] ...