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