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

(0)
上一篇 2015-05-07 09:29
下一篇 2015-05-07 09:36

相关推荐

  • 目录的三权限之——R,W,X之间的爱恨纠葛!

    目录的三权限之——R,W,X之间的爱恨纠葛! 文件的权限我们一般都很容易了解,但是目录的权限,我们可能很多人就容易含混不清了,而许多时候目录的权限也是非常重要的。 因此我专门做了一个关于目录其他用户的权限实验,以此来解开目录权限的神秘面纱。在此我分别设定了三种情况,看看当某个目录/testdir对其他用户user1仅没有R,W,X权限时,其他用户对此目录有什…

    Linux干货 2016-08-08
  • 第六周小练习

    详细总结vim编辑器的使用并完成一下练习 vim编辑器的基本使用 vim模式: 1、编辑模式(命令模式) 2、输入模式 3、末行模式  一、打开文件 vim /path/to/somefile 打开单个文件 如果文件存在为打开,否则保存退出时为新建 vim /path/to/somefile vim +# : 打开文件…

    Linux干货 2016-12-19
  • CollabNetSubversionEdge服务搭建

    准备目录并设置权限 [root@localhost ~]# mkdir /var/svn [root@localhost ~]# chown bohui:bohui /var/svn/ [root@localhost ~]# chmod 700 /v…

    Linux干货 2015-10-18
  • N21沉舟第十周作业

    1、请详细描述CentOS系统的启动流程(详细到每个过程系统做了哪些事情) 2、为运行于虚拟机上的CentOS 6添加一块新硬件,提供两个主分区;   (1) 为硬盘新建两个主分区;并为其安装grub;   (2) 为硬盘的第一个主分区提供内核和ramdisk文件; 为第二个分区提供rootfs;   (3) 为rootfs提供…

    Linux干货 2016-09-10
  • Linux系统程序包管理工具-RPM

    一、rpm是什么 RPM 是RPM Package Manager(RPM软件包管理器)的缩写,这一文件格式名称虽然打上了RedHat的标志,但是其原始设计理念是开放式的。RPM包管理器(RPM)是一个强大的命令行驱动的包管理系统能够安装、卸载、验证、查询和更新计算机软件包。每个软件包包括存档的文件连同包和它的版本信息,描述等。还有一个库API,允…

    Linux干货 2015-11-19
  • keepalived+nginx部署(单主模型)

    环境准备Centos7系统,后端服务器提供web服务。地址规划: VS1: 172.18.51.7 VS2:172.17.51.77 RS1:172.18.51.74 RS2:172.18.51.75 VirtualIP:172.18.51.82 拓扑图: 原理:nginx是高度模块化的应用程序,其中nginx_proxy模块即可实现负载均衡,将前端的用户请…

    2017-05-17