MySQL/MariaDB基础性知识及DDL操作详解

前言

MySQL/MariaDB是一个开放源码的小型关联式数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL/MariaDB作为网站数据库。

基础架构

MySQL核心组件

连接池：认证、线程重用、连接数限制、内存检查、缓存

SQL接口：DDL, DML, 关系型数据库的基本抽象

parser: 查询转换、对象权限检查

优化器：访问路径，性能相关的统计数据

caches和buffers：与存储引擎自身相关的I/O性能提升工具

存储引擎：MyISAM、InnoDB(变种：XtraDB)、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB

内部结构

程序类别及命令选项

服务器端程序：启动并监听于套接字上；mysqld, mysqld_safe, mysqld_multi

客户端程序：可通过mysql协议连入服务器并发出请求的；mysql, mysqlbinlog, mysqladmin, mysqldump等

工具程序：运行于服务器进程所在的主机，实现一些管理或维护操作，myisamchk

客户端程序通用选项

-u,--user #指定登陆用户
-h,--host #指定登陆主机
-p,--password #指定登陆密码
--protocol={tcp|socket|memory|pipe} #指定协议
-P,--port #指定端口，默认监听端口：tcp/3306
--socket #指定本地连接的sock文件，相当于--protocol socket
--compress #数据传输采用压缩格式
-D,--database #指定连接后默认使用的数据库
-H,--html #指定产生html输出
-X,--xml #指定产生xml输出
--safe-updates #拒绝使用无where子句的update或delete命令

客户端程序命令

交互式模式：
#客户端命令
   mysql> help #列出所有命令
   mysql> \? #相当于help
   mysql> \c #取消命令执行
   mysql> \g #发送命令至服务器端
   mysql> \G #发送命令至服务器端，垂直显示结果
   mysql> \q #退出
   mysql> \! #运行shell命令
   mysql> \s #显示服务器端状态信息
   mysql> \. /path/to/somefile.sql #批量执行sql
   mysql> \u #将指定的库设为默认库
#服务器端命令
   mysql> help KEYWORD 获取关键字的帮助信息
批模式：
mysql < /path/from/somefile.sql

管理工具mysqladmin

格式：mysqladmin [options] command [arg] [command [arg]] ...
#常用命令
create DB_Name：创建数据库
drop DB_Name：删除数据库
debug：打开调试日志并记录于error log中
status：显示简要状态信息
        --sleep #间隔秒数
        --count #显示的次数
extend-status: 显示mysqld的所有服务器状态变量
flush-privileges: 刷新授权表，相当于reload命令
flush-hosts: 清除dns缓存及被拒绝的客户端列表缓存
flush-logs: 滚动日志, 二进制日志和中继日志
flush-status: 重置各状态变量
flush-tables: 关闭当前打开的所有的表文件句柄；
flush-treads: 重置线程缓存；
password: 设置密码
ping: 测试服务器是否在线
processlist: 显示当前服务器上的所有线程
refresh: 相当于执行flush-hosts和flush-logs
shutdown: 关闭服务器进程 ；
start-slave, stop-slave: 启动、关闭从服务器线程；
variables: 显示服务器变量

功能特性补充

命令行编辑功能

Ctrl+a: 快速移动光标至行首
Ctrl+e: 快速移动光标至行尾
Ctrl+w: 删除光标之前的单词
Ctrl+u: 删除行首至光标处的所有内容
Ctrl+y: 粘贴使用Ctrl+w或Ctrl+u删除的内容

提示符

mysql> #等待输入命令
->   #续行
‘>  #还需补全后半部单引号
“>  #还需补全后半部双引号
`>   #还需补全后半部反引号
/*>  #注释，不执行，需以*/结束注释

SQL语言的组成部分

DDL：数据定义语言

DCL：数据控制语言，如授权

DML：数据操作语言

完整性定义语言：DDL功能性约束（主键、外键、惟一键、条件、非空、事务）

视图定义：虚表，存储下来的select语句

事务控制

数据字典

数据字典：系统编目（system catalog）

保存了数据库服务器上的元数据

元数据

关系的名称
每个关系中各字段的名称
各字段的类型和长度
约束
每个关系上的视图的名字及视图的定义
授权的用户名字
用户的授权和帐户信息
统计类数据
	每个关系中字段数；
	每个关系中行数；
	每个关系的存储方法；

保存元数据的数据库

infomation_schema
mysql
performance_schema

数据类型及属性修饰符

数据类型的功用

①存储的值类型

②占据的最大存储空间

③定长、变长

④如何被索引和排序

⑤是否能够被索引

数据类型
字符型

CHAR、VARCARH、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOG、LONGBLOB

修饰符：
NULL：允许为空
NOT NULL：不允许为空
DEFAULT 'string'：默认值，不适用于TEXT
CHARACTER SET 'set'：设定字符集
    SHOW CHARACTER SET;查看可用字符集
COLLATION 'collation'：设定字符集排序规则
    SHOW COLLATION;查看可用排序规则
DEFAULT：不用于BLOB类型
字符有通配符：
	%: 匹配任意长度的任意字符
	_: 匹配任意单个字符；

整型

TINYINT、SMALLINT、MEDIUMINT 、INT、BININT

修饰符：
UNSIGNED：无符号
NULL
NOT NULL
DEFAULT
AUTO_INCREMENT: 自动增长
#特殊要求：非空，且必须是主键或惟一键

浮点型

FLOAT、DOUBLE

修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED

布尔型

没有专用布尔型，其是TINYINT(1)的别名

日期时间型

DATE、TIME、DATETIME、TIMESTAMP、YEAR(2)、YEAR(4)

修饰符：
NULL
NOT NULL
DEFAULT VALUE

内建类型

ENUM：枚举，表示仅能从给出的选项选择其中一个
      ENUM('string1','string2')
SET：集合, 表示能使用给出的元素组合成字符串
      SET('a','b','c')
      
修饰符：
NULL
NOT NULL
DEFAULT ''

SQL模式及服务器变量

sql模式：用来限定mysqld的工作特性

TRADITIONAL：传统模式

STRICT_TRANS_TABLES：对支持事务的表使用严格模式

STRICT_ALL_TABLES：对所有表使用严格模式

服务器变量的类型

全局：对所有会话都生效

所有的会话在建立时都从全局继承，但继承完成后每个会话独立维护自己会话级变量

修改全局的服务器变量仅对之后建立的会话生效

要求有管理权限

会话：仅对当前会话有效

修改即刻生效

不要求管理权限

修改方式

动态修改: 会话级别，立即生效；全局级别，新建立的会话有效，重启服务会失效

静态修改：要修改配置文件，或修改传递给mysqld的选项的值，重启后有效

注：并非所有的服务器变量都支持动态修改

查看服务器变量

mysql> SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];

mysql> SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;

mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';

mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';

修改服务器变量

动态修改变量的值：
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE'
让设置永久有效的方式：
[mysqld]
sql_mode = 'STRICT_ALL_TABLES'

语句书写大小写说明

①SQL关键字及函数名不区字符大小写

②数据库、表、索引及视图的名称是否区分大小写取决于低层的OS及FS

③存储过程、存储函数及事件调度器不区分字符大小写，但触发器区分

④表别名不区分大小写

⑤字段中字符数据，类型为binary、blog、varbinary时区分大小写，其它的不区分

DDL操作

数据库操作

创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
修改数据库
ALTER {DATABASE | SCHEMA} [IF EXISTS] db_name

表操作

创建表
CREATE TABLE [IF NOT EXISTS] tb_name
(col1_def,col2_def,PRIMARY KEY(col_name,...),UNIQUE (col1,...),INDEX (col1,...))
[table_option]
table_option:
	ENGINE [=] engine_name
	COMMENT [=] 'string'
	ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
	TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #复制表数据
    [(create_definition,...)]
    [table_options]
    select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   #复制表结构
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
删除表
DROP [TEMPORARY] TABLE [IF EXISTS]  tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]  #cascade会将有依赖关联的表一并删除
修改表
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
alter_specification:
#插入新字段
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
#删除字段
DROP [COLUMN] col_name
#修改字段属性
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} 
#修改字段名
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
#修改字段类型及属性等
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改表名
ALTER TABLE tb_name RENAME TO new_tb_name;
RENAME TABLE old_name TO new_name;
#指定排序字段
ORDER BY col_name [, col_name] ...
#转换字符集及排序规则
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

The end

MySQL/MariaDB的知识就先说到这里了，后续文章还会继续讲解MySQL/MariaDB的相关知识，有兴趣可以继续关注。以上仅为个人学习整理，如有错漏，大神勿喷~~~