Mysql知识总结（一）

lbdbzj110

　　
　　一、基本介绍：
　　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前的系统设置
　　建立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__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  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不区分大小写;