初识MySQL（一）

数据库（DATABASES）

数据库是一个单位或是一个应用领域的通用数据处理系统，存储有企业和事业部门、团体和个人的有关数据的集合，该集合中数据是从全局观点出发建立的，按一定的数据模型进行组织、描述和存储。其结构基于数据间的自然联系，从而可提供一切必要的存取路径，且数据不再针对某一应用，而是面向全组织，具有整体的结构化特征，为众多用户所共享其信息而建立的，已经摆脱了具体程序的限制和制约。不同的用户可以按各自的用法使用数据库中的数据；多个用户可以同时共享数据库中的数据资源，即不同的用户可以同时存取数据库中的同一个数据。数据共享性不仅满足了各用户对信息内容的要求，同时也满足了各用户之间信息通信的要求。

数据库分类

按数据结构模型的分类，数据库可分为如下几种：

1、层次结构模型：

层级模型是一种有根节点的定向有序树，根节点唯一，其余为分支。按照层次结构建立的数据库，称为层次模型数据库。例如IMS（Information Management System）数据库。

2、网状结构模型：

用网络结构表示实体类型以及实体之间联系模型。顾名思义是一个事务和其它多个事务都有联系进而构成一张网状图。按照网状结构模型建立的数据库，称为网状数据库。例如DBTG（Database Task Group）数据库。

3、关系结构模型：

关系型数据结构把一些复杂的数据结构归结为二元关系（即二维表格形式），对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关系表格的分类、合并、连接或选取等运算来实现数据管理。当前主流的关系型数据库有Oricle、DB2、Microsoft SQL Server、Microsoft Access、MySQL等。

4、对象-关系型结构模型：

对象关系型数据库会将数据以对象形式组织起来，而不像关系型数据库以表的形式组织数据，亦能在必要时提供ACID等事务特点，可以处理更复杂的数据类型以及复合数据类型，并建立索引，数据查询时不需要表格join，通过指针和索引查找数据；数据间可以有继承关系等等显著特点。

5、非关系型结构模型：

以键值存储，且结构不固定，每个元组可以有不固定的字段，每个元组可以根据需要增加一些自己的键值对，不需要对表进行关联查询（因为它不依赖表结构），类似于memchache，只需要根据ID取出相应的value即可完成查询。例如Redis数据库。

关系型数据库

针对关系型数据库作如下解释：

1、关系型数据库可以简单的理解为二维数据库，表格式类似于excel表格，我们平时我接触的数据据，一般都是关系型数据库。

2、关系型数据库不是唯一的高级数据库模型，也不是最优的一种，但是，关系型数据库是现今使用最广泛、最易于理解和使用的数据库模型。

3、关系：可以理解成一张二维表，每个关系都有一个关系名，即表名。

元组：可以理解成二维表中的一行，在数据库中常被称作记录。

属性：可以理解成二维表中的一列，在数据库中常被成为字段。

域：属性的取值范围，也是数据库中某一列的取值限制。

关键字：一组可以唯一标识元组的属性。数据库中常称为主键有一个或多个列组成。

关系模式：对关系的描述，在数据库中通常称之为表结构。

4、关系型数据库特点：

容易理解：二维表结构是非常贴近逻辑世界的一个概念，相对与网状、层次以及其它模型更容易理解。

使用方便：通过SQL语言程序员和数据管理员可以很方便的在逻辑层面操作数据库而不必理解其底层实现。

易于维护：丰富的完整性（实体完整性、参照完整性、用户自定义完整性）降低数据冗余和数据不一致的概率。

5、关系操作：

数据查询：选择、投影、连接、并、交、差、除

数据操作：增加、删除、修改、查询

6、SQL(Structured Query Language):结构化查询语言，一种特殊目的的编程语言，数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库。

SQL查询语言组成部分：

DDL(Data Definition Languages)语句：数据定义语句，用于定义不同的数据段、数据库、表、列、索引等数据库的对象定义。常用关键字主要包括create、drop、alter等。

DML(Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用关键字包括insert、delete、update和select等。

DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，用于定义数据库、表、字段、用户的访问权限和安全级别。常用关键字有grant、revoke等。

MySQL数据库

一、MySQL数据库实现模型

二、MySQL的核心组件（参照上图）

1、连接池：用于完成用户认证、线程重用、并发连接数限制、内存检测以及连接缓存。

2、SQL接口：使用SQL结构查询语言对数据库就行操作的接口（DDL DML等）

3、Parser：分析器，用来进行查询转换、权限分析获取的操作。

4、Optimizer：优化器，生成并选择最佳的访问路径。

5、Cache & Buffers：与存储引擎相关的I/O性能提升工具。

6、存储引擎（真正执行数据查询操作的工具）：MyISAM,InnoDB,NDB,Archive,Federated,Memory,Merge,Partner,Community,Custom等等。

三、MySQL数据软件安装（本文使用编译安装方式安装，后续涉及实际操作部分均基于本安装版本）

1、源码文件以及版本

2、实验环境

平台：VMware Workstation 10

主机A：IP地址：192.168.5.205 系统：CentOS 6.5 x86_64 编译安装mariadb-10.0.12（演示用主机）

主机B：IP地址：192.168.5.206 系统：CentOS 6.5 x86_64 编译安装mariadb-10.0.12（mysql备份还原时用到）

3、实验拓扑

4、实验目的

演示mariadb数据库的安装、使用、管理与维护相关操作。

5、创建逻辑卷mydata并挂载至/mydata/data目录下，作为mariadb的data目录

# fdisk -l /dev/sdb

# pvcreate /dev/sdb1

Physical volume "/dev/sdb1" successfully created

# vgcreate myvg /dev/sdb1

Volume group "myvg" successfully created

# lvcreate -n mydata -L 10G myvg

Logical volume "mydata" created

# mke2fs -t ext4 -L mydata -b 4096 -m 3 /dev/myvg/mydata

编辑/etc/fstab，实现mariadb的data目录的自动开机挂载

# mount -a

//挂载成功

6、安装必要组件以及依赖程序

# yum -y install cmake //mariadb使用cmake编译安装

# yum -y groupinstall Development Tools

# yum -y groupinstall "Server Platform Development"

# yum -y install libxml2-devel //默认支持libxml2，以及需要安装libxml-devel开发组件

注意：cmake的使用方式不同于./config 编译方式，为方便理解，做对应如下

./configure cmake .

./configure –help cmake -LH 或者 ccmake

清理编译生成的文件

make clean 或者 rm CMakeCache.txt

指定安装路径时使用的选项：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DMYSQL_DATADIR=/mydata/data

-DSYSCONFDIR=/etc

编译的存储引擎，使用如下格式：

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DWITH_FEDERATED_STORAGE_ENGINE=1

若要明确指定不编译某存储引擎，可以使用类似如下的选项：

-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1

比如：

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

如若要编译进其它功能，如SSL等，则可使用类似如下选项来实现编译时使用某库或不使用某库：

-DWITH_READLINE=1

-DWITH_SSL=system

-DWITH_ZLIB=system

-DWITH_LIBWRAP=0

其它常用的选项：

-DMYSQL_TCP_PORT=3306

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock

-DENABLED_LOCAL_INFILE=1

-DEXTRA_CHARSETS=all

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DWITH_DEBUG=0

-DENABLE_PROFILING=1

7、创建mysql用户并将mariadb的data目录的属主数组改为响应用户和组

# useradd -r mysql

# chmod -R mysql:mysql /mydata/data

注意：以下编译安装操作均在~/mariadb-10.0.12/ 目录下执行

8、编译安装mariadb

执行cmake -LH查看默认项

// install prefix

CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql //默认安装路径即可

# cmake . -DMYSQL_DATADIR=/mydata/data -DWITH_SSL=system -DWITH_SPHINX_STORAGE_ENGINE=1

# make

# make install

注意：以下操作在/usr/local/mysql/下执行（必须在此目录下，否则mysqld服务不能启动）

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld //提供mysql服务启动文件。

# chmod +x /etc/rc.d/init.d/mysqld //赋予mysql服务启动文件执行权限。

# cp support-files/my-large.cnf /etc/my.cnf //为mysql提供配置文件

# vim /etc/my.cnf //修改配置文件，在mysqld段中指定mysql的data目录

# scripts/mysql_install_db –user=mysql –datadir=/mydata/data //初始话mysql

# vim /etc/profile.d/mysq.sh //导出到环境变量

# . /etc/profile.d/mysql.sh //重读以生效

# chkconfig mysqld on //配置mysqld永久开机启动

# service mysqld start //启动mysqld服务

# ss -tnl //查看TCP 3306端口是否已监听

使用mysql客户端登陆服务器端

、

并且，此时mysql的data目录中已有初始化生成的相关数据

查看默认数据库信息

> SHOW DATABASES; //此命令显示当前数据库服务器已创建的数据库

information_schema数据库

information_schema数据库是MariaDB自带的数据库，它提供了访问数据库元数据的方式。元数据是关于数据的数据，如数据库名、表明，列的字段类型，访问权限，也称之为“数据词典”和“系统目录”。information_schema是一个信息数据库，将其它数据库的信息（如表名、数据库名、权限等）放在数个只读的数个表中，此类表实际是视图而不是基本表，无法看到与之相关的文件。

information_schema数据库表说明:

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

performance_schema数据库

此数据库用来手机服务器性能参数，用户不能删除创建该数据库中的表，该数据库提供一下功能。

A、提供进程等待的详细信息，包括锁、互斥变量、文件信息。

B、保存历史事件汇总信息，为提供MySQL服务器性能提供判断参考。

C、易于监控新增和删除事件点，可随意更改数据库监控周期

Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的值为ON，则说明性能数据库正常开启状态。

mysql数据库

mysql服务器核心数据库，负责存储数据的用户、权限设置、关键字等mysql自己需要和管理信息，不能也不应去修改该数据库中的信息。

9、由于安装完成后，root用户没有密码，并且默认拥有匿名登陆用户，会有安全风险，因此需要赋予root用户密码并删除匿名用户，一下分部进行分步操作

mysql的用户信息存储于mysql数据库中的user表中，查看如下

>SELECT user,host,password FROM mysql.user;

上图看以看到用户信息

为root用户设置密码（值得注意的是，mysql用户信息的表示方式为 'username'@'host'格式）。

A、SET PASSWORD FOR 'username'@'host' = PASSWORD('yourpassword');

例：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('peter');

B、UPDATE mysql.user SET password=PASSWORD('yourpassword') WHERE user='username' and host='hostname or ip';

例：

UPDATE mysql.user SET password=PASSWORD('peter') WHERE user='root' and host='127.0.0.1';

C、使用mysqladmin客户端软件进行修改

用法：# mysqladmin -u USERNAME -h{HOSTNAME|IP} -p password 'newpassword'

例：

# mysqladmin -uroot -h127.0.0.1 -p password '@5server'

删除匿名用户

A、DROP USER ''@'HOST';

例：

DROP USER ''@'localhost';

B、DELETE FROM user WHERE user='' AND host='HOSTNAME';

例：

DELETE FROM user WHERE user='' AND host='nod4.nod.com';

Mysql程序类别以及简介

1、服务器端程序：启动并监听在套接字上，有mysqld,mysqld_safe,mysqld_multi

2、客户端程序：可以通过mysql协议连入服务器并发出请求，有mysql,mysqlbinlog,mysqladmin,mysqldump等。

客户端程序通用选项

-u –user 指定用户名，例如：-u root,-uroot,–user=root

-h –host 指定主机名称,例如：-h localhost,-hlocalhost,–host=localhost

-p –password 指定密码

–protocol={tcp|socket} //unix,linux系统指定通讯时使用的通讯协议

–protocol={pipe|memory} //windows系统指定通讯使用的协议，其中pipe命名管道；memory共享内存。

–port //当–protocol=tcp时指定使用的端口号

–socket //相当于–protocol=socket

客户端程序mysql

功能特性：

（1）、命令历史

（2）、命令行编辑功能

Ctrl+a //快速移动光标至行首

Ctrl+e //快速移动光标至行尾

Ctrl+w //删除光标之前的单词

Ctrl+u //删除行首至光标处的所有内容

Ctrl+y //粘贴使用Ctrl+w或Ctrl+u删除的内容

（3）、提示符

mariadb>等待输入命令

->：续行

‘>:还需要补全后半部的单引号

“>：还需要补全后半部的双引号

·>:还需补全后半部的反引号

（4）、客户端命令：help列出所有命令

clear (\c) Clear the current input statement //清除当前命令行命令

ego (\G) Send command to mysql server, display result vertically //竖排显示

go (\g) Send command to mysql server //取代终止符

delimiter (\d) Set statement delimiter //定义分隔符

exit (\q) Exit mysql. Same as quit //退出客户端程序，quit (\q) Quit mysql

help (\h) Display this help //显示帮助信息

prompt (\R) Change your mysql prompt //定义命令提示符

source (\.) Execute an SQL script file. Takes a file name as an argument （\. /path/from/somefile.sql),相当于mysql < /path/from/somefile.sql）

system (\!) Execute a system shell command //运行shell命令

use (\u) Use another database. Takes database name as argument //指定默认库

（5）、mysql -e选项，在shell命令行模式下执行mysql客户端命令，而后退出，例如：

# mysql -e 'SHOW DATABASES;' //显示当前mysql服务器上的数据库信息，注意，此处-e后面跟的参数必须用单引号或双引号引起来，否则不生效

（6）、服务器端命令：

使用help KEYWORD可以得到命令帮助，信息，最重要的是执行SQL语句：

DDL

DML

客户端工具mysqladmin

（1）、格式mysqladmin [options] command [command-arg] [command] [command-arg]…

（2）、mysaladmin [options] command 中的command列表

（3）、mysqladmin命令解释

status 显示mysql的简要状态信息

专用选项

–sleep # 间隔#秒查看一次

–count # 显示多少次

extended-status：显示所有mysql服务器状态变量相当于 mysql -e 'SHOW GLOBAL STATUS;'

flush-privileges：mysql -e 'FLUSH PRIVILEGES;'刷新授权表

flush-hosts：清除被拒绝的主机缓存列表

flush-logs：滚动日志，二进制日志和中继日志

flush-status：重置mysql服务器状态变量

flush-tables：关闭当前打开的所有文件的句柄，如果句柄正在使用则等待其结束。

flush-threads：重置线程缓存 mysql -e 'SHOW PROCESSLIST;'

kill：杀死指定进程

password：修改设定密码

ping：查看服务器存活状态

processlist：显示当前服务器的进程列表，不用进入客户端模式

refresh：相当于执行flush-hosts和flush-logs

shutdown：关闭服务进程

start-slave,stop-slave：启动和关闭从服务器线程

variables：显示服务器变量

3、工具程序：运行于服务器进程所在的主机，实现一些管理和维护操作，例如MyISAMcheck。

MySQL数据库的数据类型

1、数值类型

MySQL支持多种数值类型

整型：

注意：在mysql中没有专用布尔型数据类型，BOOL 是TINYINT(1)的别名

整型修饰符：UNSIGNED（无符号），NULL（空值）,NOTNULL（非空）,DEFAULT#（默认值为#）,AUTO_INCREMNET:自动增长字段，产生唯一标识符或顺序值时使用此属性，只用于整数，一般由1开始逐行加1，应用此属性的列应该具有NOTNULL并定义为PRIMARY KEY或UNIQUE键。

在整数类型中，按照取值范围和存储方式的不同，分为TINYINT,SMALLINT,MEDIUMINT,INT、INTEGER、BIGINT,如操作取值范围会报“out of range”错误提示

MySQL支持在类型名称后面的小括号内指定显示宽度，例如INT（5）

浮点数值型、定点数类型：