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