上课笔记第二十九天、mysql、mysql编译安装及组成、客户端、变量和类型

原创

xiangjin1 2015-06-11 23:04:39 博主文章分类：马哥教育学习进度 ©著作权

文章标签 mysql 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者xiangjin1的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、关系型数据库基础理论及MySQL数据库基础架构
关系型数据的基础理论
  文件系统上：最初所有的数据都存储于文件中，每次读取文件时需要把所有的块数据都加载到内存中，通过工具grep查询需要的数据，如果文件比较大（100万行），这时加载会很慢而且占用资源比较大。

数据管理软件：
层次模型、网状模型、关系模型、对象-关系模型、非关系模型

DBMS: Database Management System
mysql对每个客户请求都是由一个线程来响应，

2、MySQL核心概念及MariaDB编译安装

数据存储机制：
数据按索引顺序存储，索引顺序文件
数据是按需随机存放，堆文件

mysql核心部件组成：
connection pool（连接池），负责用户认证、线程重用、连接限制、内存检查、提供缓存
sql interface（sql 接口），负责DML、DDL、触发器各类功能
parser（分析器），对象权限检查，查询转换，把用户写的sql语句转换为数据库能执行的语句。
optimizer（优化器），优化mysql性能
caches&&buffers（缓存和缓冲），用于提升mysql I/O方面性能
之后就是选择使用存储引擎。存储引擎就可以直接读取文件系统中的文件。
MyISAM、InnoDB、Memory、Merge、Federated、CSV、Archive、Blackholl、Aria、SphinxSE、TokuDB

3、mysql客户端工具的使用
设置密码：
   可以在mysql客户端中使用：
set password for 'root'@'::1' = password('123456');
update user set password=password('123456') where user='root';
    mysqladmin -uUSERNMAE -hHOSTNAME_OR_IP -p password 'new_password'：用mysqladmin工具

删除用户：
    delete from user where user='';

获取帮助：
mysql --verbose --help
  Default options are read from the following files in the given order:
  /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf ：mysql启动时读取默认配置文件的次序
--defaults-extra-file=#：读取默认配置文件后，额外读取指定配置文件设置
--defaults-file=#：不读默认配置文件，自定义配置文件路径。

客户端程序的通用选项：
-u，--usser
-h，--host
-p，--password
--protocol=（tcp、socket（unix sock）、windows用以下2种方式连接（pipe、memory））
--port：当protocol是tcp时使用的端口
--socket：相当于--protocol socket，socket只能用于本地连接。

其他选项：
-D：指定登录时用的默认库为指定库：mysql -uroot -D mysql -p：登录时直接使用mysql为默认库。

mysql客户端程序：mysql
  批模式：mysql < /path/from/somefile.sql
交互式模式:
客户端命令：help可列出所有命令
  clear, \c: 取消当前要执行的语句。
  ego, \G: 竖排显示结果
  go, \g:语句结束符
  delimiter, \d:定义语句结束符
quit、exit，\q：退出客户端
（source, \. ）/path/from/somefile.sql:   举例source /opt/123.sql
（system, 或\!） COMMAND: 运行shell命令：举例 \! ls /etc或system ls /etc
（use, \u） DB_NAME: 将指定的库设为默认库：举例use mysql
  服务器端命令：help KEYWORD
sql语句（DDL、DML）

mysqladmin工具：
create DB_NAME:
drop DB_NAME:
status:（显示mysqld简要状态信息，--sleep #: 间隔秒数，--count #: 显示的次数）：
  mysqladmin status --sleep 2 --count 3
  mysqladmin status -uroot -p123456
extend-status: 显示mysqld的所有服务器状态变量：mysqladmin extended-status -uroot -p123456
  flush-privileges: 刷新授权表，相当于reload命令：mysqladmin flush-privileges -uroot -p123456
flush-hosts: 清除dns缓存及被拒绝的客户端列表缓存   ：mysqladmin flush-hosts -uroot -p123456
flush-logs: 滚动日志, 二进制日志和中继日志
flush-status: 重置各状态变量
flush-tables: 关闭当前打开的所有的表文件句柄；
flush-treads: 重置线程缓存；
password: 设置密码
ping: 测试服务器是否在线
processlist: 显示当前服务器上的所有线程
refresh: 相当于执行flush-hosts和flush-logs
shutdown: 关闭服务器进程；
start-slave, stop-slave: 启动、关闭从服务器线程；
variables: 显示服务器变量

命令：
show processlist;显示当前连接服务器端的用户

4、mysql数据类型及服务器变量
数据类型的功用：
存储的值类型；
占据的最大存储空间；
定长、变长；
如何被索引和排序；
是否能够被索引；

数据字典：系统编目（system catalog）
保存了数据库服务器上的元数据
关系的名称
每个关系中各字段的名称
各字段的类型和长度
约束
每个关系上的视图的名字及视图的定义
授权的用户名字
用户的授权和帐户信息
统计类数据（每个关系中字段数、每个关系中行数、每个关系的存储方法）
  元数据也通过数据库保存：（infomation_schema、mysql、performance_schema）

数据类型：
  字符型（CHAR、VARCHAR、BINARY、VARBINARY、TEXT、BLOB）
  数值型（INT、DECIMAL、FLOAT、DOUBLE）
  日期时间型：（DATE、TIME、DATETIME、YEAR、STAMP）
  内建类型:（ENUM、SET）

  字符型：
  CHAR：255
  VARCARH：65535
  TINYTEXT: 255
  TEXT: 65535
  MEDIUMTEXT: 2^24
  LONGTEXT: 2^32
  BINARY: 255
  VARBINARY：65535
  TINYBLOB
  BLOB
  MEDIUMBLOG
  LONGBLOB
修饰符：
  NULL
  NOT NULL
  DEFAULT 'string'
  CHARACTER SET 'set'、SHOW CHARACTER SET;
  COLLATION 'collation'、SHOW COLLATION；

字符有通配符：DEFAULT不用于BLOB类型；
  %: 匹配任意长度的任意字符
  _: 匹配任意单个字符；

整型：
  TINYINT: 1Byte
  SMALLINT: 2Bytes
  MEDIUMINT: 3Bytes
  INT：4Bytes
  BININT：8Bytes
  修饰符：
UNSIGNED
NULL
NOT NULL
DEFAULT #
AUTO_INCREMENT: 自动增长
  特殊要求：非空，且必须是主键或惟一键；

浮点型：
FLOAT
DOUBLE
  修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED

日期时间型：
DATE: 3Bytes
TIME: 3Bytes
DATETIME: 8Bytes
TIMESTAMP: 4Bytes
YEAR(2)：1Byte
YEAR(4): 1Byte
修饰符：
NULL
NOT NULL
DEFAULT VALUE

  内建类型：
ENUM：枚举，表示只能从给出的选项选择其中一个；
  ENUM('string1','string2')
SET：集合, 表示能使用给出的元素组合成字符串
  SET('a','b','c')
修饰符：
   NULL
NOT NULL
DEFAULT ''

  MySQL sql_mode:
sql模式：用来限定mysqld的工作特性
  TRADITIONAL：传统模式
  STRICT_TRANS_TABLES：对支持事务的表使用严格模式；
  STRICT_ALL_TABLES：对所有表使用严格模式

服务器变量的类型：
  全局（global）：对所有会话都生效；
所有的会话在建立时都从全局继承，但继承完成后每个会话独立维护自己会话级变量
修改全局的服务器变量仅对之后建立的会话生效
要求有管理权限
  会话（session）：仅对当前会话有效；
修改即刻生效；
不要求管理权限
  修改方式：
动态修改: 会话级别，立即生效；全局级别，新建立的会话有效；重启服务会失效
静态修改：要修改配置文件，或修改传递给mysqld的选项的值；重启后有效；
并非所有的服务器变量都支持动态修改；

查看服务器变量：
SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];：show global variables like 'sql_mode';
SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;select @@session.sql_mode;
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';

动态修改变量的值：
SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE'：set session sql_mode='STRICT_ALL_TABLES';

  让设置永久有效的方式在配置文件中加载：
  [mysqld]
sql_mode = 'STRICT_ALL_TABLES'