Mysql学习之基础知识总结（一）

2022-06-16 13:20:40

一、基本介绍：

MySQL是一款开源的关系数据库管理系统，最早有瑞典的MySQL AB公司开发，2008年被SUN公司收购，2009年，SUN公司被Oracle公司收购。MySQL性能高、成本低、安全稳定，被广泛应用在中小型网站中，随着MySQL的性能不断提高，像*、Google、Facebook等大公司也正在使用MySQL（*现在以迁移到MariaDB）。

MySQL使用C和C++编写，并为多种语言提供了API，支持多线程，充分利用CPU资源，支持多用户，提供TCP/IP/、ODBC和JDBC等等多种数据库连接途径，且提供了用于管理、检查、优化数据库操作的管理工具，可以处理拥有上千万条记录的大型数据库。

二、MySQL体系结构：

1、Connectors指的是不同语言中与SQL的交互

2、Management Serveices & Utilities：系统管理和控制工具

3、Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。

4、SQL Interface: SQL接口

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

5、Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

主要功能：

a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

6、Optimizer: 查询优化器

SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = f;

这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。

将这两个查询条件联接起来生成最终查询结果。

7、Cache和Buffer：查询缓存

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

8、Engine ：存储引擎

存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。

现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。

默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。

Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

后面我会详细介绍这些引擎各自的特性。

从上图我们换个角度(微观)来解析一下它：

1、连接管理器：

当客户发起请求后由连接管理器负责接收这个请求;

2、线程管理器：

MySQL是以单进程多线程的方式运作的,于是它将会为每一个用户生成一个线程

3、用户模块

它的作用是进行身份进行认证,管理用户,一旦访问完成后，退出了,线程就会被线程管理器回收到连接池中；其实用户最终要用户模块打交道，只有第一次刚刚发起连接时才需要认证;后续的SQL语句都与用户模块打交道，而不再是连接管理器了;

4、命令派发器：

一旦用户认证通过连接进来后它会将那些SQL语句派发到：

"查询缓存",如果查询缓存一旦命中，那么结果也就直接返回客户端；

"日志记录",一旦我们开启了查询日志，待我们查询完成后需要将执行操作记录日志；

5、分析器：

如果上面的由命令派发到查询缓存没有命中，那么命令派发器会将SQL语句转交给分析器，由分析器进行SQL语句分析;这些语句有可能包含：DML、DDL,这些不同的语句就需要有不同的机制来处理；

优化器:分析器分析出这是一条查询语句那么由优化器来负责处理;SELECT

表修改模块:如果是更新或修改表中数据的操作，则由表修改模块负责;UPDATE/INSET/DELETE/REPLACE.....

表维护模块:如果表需要修复则由表维护模块负责;

复制模块:如果分析出是表或数据复制操作,则有复制模块操作;Replication(要启用该模块功能才起作用)

状态报告模块:优化器之所以能够完成优化,它是根据Mysql服务器不断收集状态信息得来的,所以由此模块负责状态报告;

6.访问控制模块：

上面的用户认证授权通过后并不意味着就有权限访问任意数据，这里通过访问控制模块再次对权限进行检查;

7.表管理器

如果访问控制检查通过，真正执行操作则有表管理器来负责,无论是增删查改等这样的语句；

8.存储引擎

表管理器执行操作则会通过它的内部接口，这个操作要依赖于存储引擎; 存储引擎这个抽象接口将用户请求转交给各个对应使用的存储引擎，完成操作最终将其结果返回客户端。

OK,了解了MySQL的基本体系架构后我们尝试安装一下吧,我们知道安装mysql的方式有三种：二进制源码包安装、RPM包安装以及编译安装前面的那些试验当中也演示到了二进制源码和rpm包安装,这里我将采用源码包的方式安装：

说明：

MySQL从5.5版本开始，通过./configure进行编译配置方式已经被取消，取而代之的是cmake工具。

因此，我们首先要在系统中源码编译安装cmake工具,我这里使用的cmake版本为cmake-2.8.10.2,mysql为5.5.36

注意：开发环境

1.获取cmake,安装之

[root@mysql ~]# wget http://down1.chinaunix.net/distfiles/cmake-2.8.10.2.tar.gz
[root@mysql ~]# tar -xf cmake-2.8.10.2.tar.gz -C /usr/src/
[root@mysql ~]# cd /usr/src/cmake-2.8.10.2/
[root@mysql cmake-2.8.10.2]# ./configure

2. 安装mysql前的系统设置

安装路径:

[root@mysql ~]# mkdir /usr/local/mysql

数据库路径:

[root@mysql ~]# mkdir /data/mysql
#建议此目录挂载至LVM上面，这样对于后期的扩容有很大的帮助！

创建用户和用户组

[root@mysql ~]#groupadd mysql
[root@mysql ~]#useradd -g mysql mysql

赋予数据存放目录权限

[root@mysql ~]# chown mysql:mysql -R /data/mysql

3.编译安装 MySQL 5.5

通过http://www.mysql.com/downloads/mysql官方网址或国内的sohu镜像下载软件包.

[root@mysql ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.36.tar.gz
[root@mysql ~]# cd /usr/src/
[root@mysql src]# ln -sv mysql-5.5.36 mysql
`mysql‘ -> `mysql-5.5.36‘
[root@mysql ~]# cd mysql
[root@mysql mysql]#  cmake . -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_READLINE=1  -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci
[root@mysql ~]# make && make install

在make与make install的时候可以看到进度百分比，感觉这一点要比configure方式要好。

参数说明：

指定安装文件的安装路径时常用的选项：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFDIR=/etc

默认编译的存储引擎包括：csv、myisam、myisammrg和heap。若要安装其它存储引擎，可以使用类似如下编译选项：

-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

如果想清理此前的编译所生成的文件，则需要使用如下命令：

make clean
rm CMakeCache.txt

在make与make install的时候可以看到进度百分比，感觉这一点要比configure方式要好。

首先说一下mysql主配置文件的特点（按以下序号次序来寻找）

1  /etc/my.cnf  集中式配置文件，为多个命令提供配置
2  /etc/mysql/my.cnf
3  $MYSQL_HOME/my.cnf
4  /path/to/file when defaults-extra-file=/path/to/file is specified
5   ～/.my.cnf

如果发现多个配置文件， MySQL会将所有的配置文件组合起来，将他们的合合集作为配置文件来使用。如果配置出现冲突，则以读取最后一个配置文件中的配置为准。

如果启动服务的时候想不使用默认的数据存放目录，需要在初始化的时候使用datadir指令来定义。

让mysql服务启动的时候读取额外的配置文件：

mysqld_safe --defaults-extra-file=/etc/mysql/my_instance.cnf。

将源码安装的apache的二进制文件的路径加入环境变量中，并通过source对文件重读：

[root@mysql mysql]# vim /etc/profile.d/mysqld.sh
export PATH=$PATH:/usr/local/mysql/bin:PATH
[root@mysql mysql]# source /etc/profile.d/mysqld.sh

将源码安装的mysql的头文件导入到系统找得到的位置：

[root@mysql mysql]# mkdir  /usr/local/include/mysql
[root@mysql mysql]# ln -sv /usr/local/mysql/include /usr/local/include/mysql
/usr/local/include/mysql/include‘ -> `/usr/local/mysql/include‘
[root@mysql mysql]#

将源码安装的apache的库文件导出给系统找得到的位置：

[root@mysql mysql]# vim /etc/ld.so.conf.d/mysqld.conf
/usr/local/mysql/lib  #加入此行

将源码安装的apache的man手册的导入系统找得到的位置：

[root@mysql mysql]# vim /etc/man.config
MANPATH /usr/local/mysql/man

创建my.cnf配置文件

[root@mysql mysql]# cp support-files/my-large.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf‘? y
[root@mysql mydata]# vim /etc/my.cnf
datadir = /data/mydata  #加入此行

执行初始化脚本对数据库进行初始化：

[root@mysql mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mydata/

创建管理MySQL数据库的shell脚本

[root@mysql mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@mysql mysql]# chmod +x /etc/rc.d/init.d/mysqld

添加为开机自启动项

[root@mysql mysql]# chkconfig --add mysqld
[root@mysql mysql]# chkconfig mysqld on

启动mysql

[root@mysql mysql]# service mysqld start
Starting MySQL..              [  OK  ]
[root@mysql mysql]# netstat -an | grep :3306
tcp        0   0    0.0.0.0:3306    0.0.0.0:*        LISTEN
[root@mysql mysql]#

ok!至此我们源码包编译安装操作已经完成，接下来就是对他进行简单配置即可：

在mysql安装完之后，要做的第一件事就是为管理员账户设定密码，并删除两个匿名用户；一般mysql安装完成之后会生成三个管理员用户账号，和两个匿名用户账号。

四个个管理员用户账号：

root@127.0.0.1
root@localhost
root@hostname
root@::1

两个匿名用户账号：

‘‘@localhost
‘‘@hostname

删除所有匿名用户

mysql> DROP USER ‘‘@‘localhost‘;
mysql> DROP USER ‘‘@‘www.magedu.com‘;

用户帐号由两部分组成：username@hostname
host还可以使用通配符：
%: 任意长度的任意字符
_: 匹配任意单个字符
给所有的root用户设定密码:
第一种方式：

mysql> SET PASSWORD FOR username@hostname = PASSWORD(‘your_passwrod‘);

第二种方式：

mysql> UPDATE user SET password = PASSWORD(‘your_password‘) WHERE user = ‘root‘;
mysql> FLUSH PRIVILEGES;

第三种方式：

# mysqladmin -uUserName -hHost password ‘new_password‘ -p
# mysqladmin -uUserName -hHost -p flush-privileges

连入MySQL服务器
mysql client <--mysql protocol--> mysqld
mysqld接收连接请求：
本地通信：客户端与服务器端位于同一主机，而且还要基于127.0.0.1(localhost)地址或lo接口进行通信；
远程通信：客户端与服务器位于不同的主机，或在同一主机便使用非回环地址通信
客户端工具：mysql, mysqladmin, mysqldump, mysqlcheck

帮助信息：
-?
-I
--help
**********************************************************
（单字符后面不带空格）
--user,-u 指定用户访问mysqld
--host，-h 指定服务器的名字
--password，-p
--protocol 指定连接协议（tcp/socket/pipe/memory）
使用--protocol socket时，本地客户端是通过/tmp/mysql.sock（sock文件位置可能不同）连接本地mysqld服务
--port 远程服务器的连接端口
--socket
-D db_name 直接使用某个数据库作为默认数据库
--datebase=...
--compress 数据在服务器端和客户端之间压缩传输
--default-character-set=charset_name 指定字符集
-V  显示版本号
-v命令执行时显示详细信息
--ssl-ca=/path/to/ssl_ca_file证书存放位置 （为了验证对方的证书）
--ssl-capath=/path/to/ca_dir证书目录
--ssl-cert=/path/to/cert_file//自己的证书
--ssl-cipher=cipher_list   加密方式
--ssl-key=/path/to/key_file自己的私钥
--ssl-verify-server-cert    验证服务器证书

mysql -e 不登陆mysql直接执行命令

[root@mysql ~]# mysql -e ‘SHOW DATABASES;‘
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql ~]#

mysqladmin常用选项：

create 创建数据库
drop   删除数据库
debug  用于输出调试信息
status 显示全局变量和他的值
--sleepn  每个n秒显示一次
--count n  一共显示n次
例如：#mysqladmin --sleep 3 --count 3 status
extended-status 显示所有系统变量和值/运行状态属性信息
flush-hosts  清空hosts内部信息
flush-logs   做二进制日志滚动
flush-privileges 刷新
flush-status  重新开始计数
flush-tables  关闭表
flush-threads 重置线程缓存池
kill结束某个客户端线程
processlist 列举所有的进程和线程
password 为某用户设置密码
ping查看是否处于运行状态
reload   flush-privilege
refresh    flush-hosts&flush-logs
shutdown关闭指定mysql服务器
start-slave 启动从服务器
stop-slave
variables 显示全局变量

mysql的快捷键：

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

数据类型的功用：

1、存储的值类型；
2、占据的礁存储空间；
3、定长，变长；
4、如何被索引及排序；
5、是否能够被索引；

SQL语言组成部分：

DDL:数据库定义语言
DML:
完整性定义语言：DDL的一部分功能
    主键、外键、惟一键、条件、非空、事务
视图定义：虚表，存储下来的SELECT语句
事务控制
嵌入式SQL和动态SQL
DCL:授权

数据字典：系统编目(system catalog, )

保存数据库服务器上的元数据(非数据本身没有关系，但是可以通过其管理数据)

初始化mysql后生成的"mysql"

元数据：
关系的名字
每个关系的各字段的名字
各字段的数据类型和长度
约束
每个关系上的视图的名字及视图的定义
                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
授权用户的名字
用户授权和账户信息
统计类的数据
    每个关系字段的个数
    每个关系中行数
    每个关系的存储方法
保存原数据的数据库：
    information_schema
    performance_schema (类似于Linux文件系统中的proc)

三、mysql数据类型：

1.常用数据类型：

数值型:

整型(5种)

TINYINT

SMALLINT

MEDIUMINT

INT （大于前面的）

BIGINT

十进制定点数

DESCIMAL

单精度浮点数：

FLOAT

双精度浮点数

DOUBLE

位:按位存储

BIT

字符型

===========================

作为非二进制对象存储<不区分大小写>

CHAR(定长), VARCHAR(变长)

TINYTEXT, TEXT, MEDIUTEXT, LONGTEXT

===========================

++++++++++++++++++++++++++++

作为二进制对象存储<区分大小写.没有字符集>

BINARY(0-255定长),

VARBINARY(65535变长)

TINYBLOB(Up to 255 bytes, 1bytes overhead)

BLOB(Up to 64 kb, 2bytes overhead)

MEDIUMBLOB(Up to 16M, 3bytes overhead)

LONGBLOB(Up to 4Gb, 4bytes overhead)

++++++++++++++++++++++++++++

日期时间型

2.常用修饰符

1）、字符串常用修饰符：只修饰字符型，不修饰二进制型

CHAR,VARCHAR和TEXT字符型常用的属性修饰符:
1.NOT NULL：非空约束 
2.NULL：允许
3.DEFAULT‘string‘默认值，仅用于char,varchar不适用于TEXT类型
4.CHARACTER SET ‘字符集‘
    mysql>SHOW VARIABLES LIKE ‘%char%‘ 当前系统上的字符集
    mysql>SHOW CHARACTER SET 查看所有支持的字符集
    默认情况下如果没有指定字符集它会去继承表的，如果表也没有指,就会继承库的,如果库也没有指，那么会继承服务器的。
5.COLLATION ‘规则‘
    查看本机上面排序规则 mysql>SHOW COLLATION;
BINARY,VARBINARY和BLOB只用三种修饰符(NULL, NOT NULL, DEFAULT:不适用于BLOB)
内置类型
ENUM   存储的值字串
SET    集合,存储的是组合索引
   修饰符:NOT NULL
          NULL
          DEFAULT ‘string‘

2）、整型常见的修饰符

整型的常用属性修饰符：
     AUTO_INCREMENT:自动增长(前提:非空、且唯一;支持索引, 非负值)
批量插入的副作用：一次插入多行数据时,仅记录第一个值
通过mysql>SELECT LAST_INSERT_ID();查看增长值,显示结果并非精确
    mysql>TRUNCATE tb_name
     UNSIGNED：无符号
          mysql>LAST_INSERT_ID();显示结果并非精确
          mysql>TURNCATE tb_name;
浮点型常用属性修饰符
     NULL
     NOT NULL
     DEFAULT
     UNSINGNED

任何字符型必须加引号，任何数值型不能加！

3）、日期时间型的修饰符

NOT NULL
NULL
DEFAULT

四、SQL 模式：

TRADITIONAL           传统模式
STRICT_TRANS_TABLES   仅对支持事务的表,严格模式
STRICT_ALL_TABLES     对所有表都是用严格模式

系统默认使用的是空模式

设定服务器变量的值：（仅于支持动态的变量）

支持修改的服务器变量

动态变量

可以在Mysql运行是修改

静态变量

在配置文件中修改其值，并重启后方能修改

服务器变量从其生效范围来讲，有两类：

全局变量：服务器级别，修改之后仅对新建立的会话有效

会话变量：会话级别，仅对当前会话有效;会话建立时，从全局继承各变量

查看服务器变量三种方式：

mysql>SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ‘‘];
mysql>SETLECT @@{GLOBAL|SESSION}.sql_mode;
mysql>SETLECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘SOME_VARIABLE_NAME‘;

修改变量：前提：默认仅管理员有权限修改全局变量

mysql> SET {GLOBAL|SESSION} VARIABLE_NAME=‘VALUE‘;

注意：无论是全局还是会话级别的动态变量修改,在重启mysqld后都会失效;想永久有效,可定义在配置文件中的[mysqld]段落中！

五、Mysql中字符大小写：

1.SQL关键字及函数名不区分字符大小写;要么大写、要么小写

2.数据库、表以及视图名称的大小写区分与否取决于底层OS及File System;

3.存储过程、存储函数及时间调度器的名称不区分大小写，但触发器区分;

4.表的别名区分大小写

5.对于字段中的数据，如果字段类型为Binary类型，则区分大小写，非Binary不区分大小写;

Mysql学习之基础知识总结（一）,布布扣,bubuko.com

Mysql学习之基础知识总结（一）

码农公寓

相关文章