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

前言

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

基础架构

MySQL架构.jpg

MySQL核心组件

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

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

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

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

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

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

内部结构

内部结构.jpg

程序类别及命令选项

服务器端程序:启动并监听于套接字上;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的相关知识,有兴趣可以继续关注。以上仅为个人学习整理,如有错漏,大神勿喷~~~

原创文章,作者:书生,如若转载,请注明出处:http://www.178linux.com/3996