mysql基础

Mysql 5.7之后都会有默认的root密码：通过grep "temporary password" /var/log/mysqld.log来找回

文件存储的劣势

    数据冗余的不一致性性

    数据访问困难

    数据孤立

    完整性问题

    原子性问题

    并发访问异常

    安全性问题

DML：数据操作语言

    INSERT

    DELETE

    SELECT

    UPDATE

DDL：数据定义语言

    CREATE

    DROP

    ALTER

DCL：数据控制语言

    GRANT

    REVOKE

RDB对象：库、表、索引、视图、用户、存储过程、存储函数、时间调度器

约束

    域约束：数据类型约束

    外键约束：引用完整性约束

    主键约束：某字段能唯一标识此字段所属的实体，且不为空

    唯一性约束：每一行的某字段都不允许出现相同值，可以为空

        一张表中可以有多个

    检查性约束：不能出现违反常理的类型

    constraint

数据查询和存储

    存储管理器

        权限及完整性管理器

        事务管理器

        文件管理器

        缓冲区管理器

    查询管理器

        dml解释器

        ddl解释器

        dcl解释器

        查询执行引擎

mysql是单进程多线程的模型

    守护线程

    应用线程

关系运算

    投影：只输出指定属性

    选择：只输出符合条件的行

    自然连接：具有相同名字的属性上所有取值相同的行

    笛卡尔积：

        (a+b)*(c+d)=ac+ad+bc+bd

    并：两个表关系相同的部分。集合运算

sql查询语句

    DDL

    DML

    完整性定义语言：ddl的一部分

    视图定义语言

    事务控制语言

    嵌入式sql和动态sql：把sql嵌入到程序语言中叫嵌入式sql；程序设计语言使用函数或者方法和rdbms服务器建立连接并进行交互叫动态sql

    授权：DCL

使用程序设计语言和rdbms交互：

    嵌入式sql：与动态sql相似但是要编译时完全确定下来

        odbc

    动态sql：直接发送的语句

        jdbc

mysql的存储引擎是插件式的

表管理器：负责创建、读取或修改表定义文件；维护表描述符高速缓存；管理表锁

    表结构定义文件

表修改模块：表创建、删除、重命名、地处、更新或插入之类的操作

表维护模块：表的检查、修理、备份、恢复、优化（碎片整理）及解析

文件中记录组织

    堆文件组织：一条记录可以放在文件中任何地方

    顺序文件组织：根据“搜索码”值顺序存放

    散列文件组织：人为的根据哈希值分“桶”

表空间（table space）：一个空间放多个表的空间。

数据字典（data dictionary）：关系的元数据，比如，关系的名字、字段的名字、字段的类型和长度、视图、约束、用户名字、授权、密码

缓冲区管理器：

    缓冲置换策略

    被钉住的块

mysql启动装载的文件，每次执行都会装载一次，以最后一个配置文件的配置为准(~/.my.cnf)

    1./etc/my.cnf

    2./etc/nysql/my.cnf

    3.$MYSQL_HOME/my.cnf

/path/to/file when defaults-extra-file=/path/to/file is specified

    4.~/.my.cnf

安装完成后会有五个用户

    root

    root@127.0.0.1

    root@localhost

    ''@localhost

    ''@hostname

mysql用户密码修改

    1.#mysqladmin -u USERNAME -p HOSTNAME password 'NEW_PASS' -p 'OLD_PASS'

    2.mysql>SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD('new-pass');

    3.mysql>UPDATE mysql.user SET PASSWORD=PASSWORD('new_pass')WHERE CONDITION;

mysql客户端工具：

    mysql

    mysqldump

    mysqladmin

    mysqlcheck

    mysqlimport

mysql非客户端工具

    myisamchk

    myisampack

两大存储引擎

    MyISAM--->表类型，无事务，表锁

        每个表有三个文件

            .frm:表结构

            .MYD:表数据

            .MYI:表索引

    InnoDB--->事务，行锁

        所有表共享一个表空间文件

        建议：每表用一个单独表空间文件

            .frm:表结构

            .ibd:表空间（表数据索引）

客户端命令

\c:提前终止语句执行

\g:无论语句结束符是什么，直接将此语句发送服务器端执行

\G:无论语句结束符是什么，直接将此语句发送服务器端执行，以竖排方式显示

\! COMMAND:执行shell命令

\w:语句执行结束后显示警告信息

\#:对新建的对象，支持名称补全功能

服务器端命令

mysqladmin

    create DATABASE

    dorp DATABASE

    ping

    processlist

    status

        --sleep N:显示频率

        --count N:显示多个状态

    extended--status:显示状态变量

    variables:显示服务器变量

    flush-privileges:让mysqld重读授权表，等同于reload

    flush-status:重置大多数服务器状态变量

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

    flush-hosts:重置主机、账户等计数器

    refresh:相当于同时执行flush-hosts和flush-logs

    shutdown:关闭mysql服务器进程

    start-slave:启动复制，启动从服务器复制进程

        SQL thread

        IO thread

    stop-slave:关闭复制

数据类型

数值型

    精确数值

        int

            TINYINT:占用1字节

            SMALLINT:占用2字节

            MEDIUMINT:占用3字节

            INT:占用4字节

            BIGINT:占用8字节

            decimal:1.11之类的精确数值

    近似数值

        float(4 bytes)：单精度浮点型

        double(8 bytes)：双精度浮点型

        real

字符型

    定长

        char--->255

        BINARY

    变长

        VARBINARY

        varchar(65535-:+1;65535+:+2)--->65535

        varbinary

        text

            TINYTEXT(多占一个字节)--->255

            TEXT(+2)--->65535

            MEDIUMTEXT(+3)--->16777215

            LONGTEXT(+4)--->4294967295

    TINYBLOB--->255(+1)

    MEDIUMBLOB--->16Mb(+4)

    LONGBLOB--->4Gb(+5)

    BLOB--->64Kb(+3)

枚举

    ENUM

集合

    SET

日期时间型

    date(3 bytes)

    time(3 bytes)

    datetime(8 bytes)

    timestamp(4 bytes)

    year(1 bytes)

AUTO_INCERMENT

    整形

    非空

    无符号

    主键或唯一键

MySQL服务器变量

作用域

    全局变量

        SHOW GLOBAL VARIABLES

    会话变量

        SHOW [SESSION]VARIABLES

生效时间

    动态

        可即时生效

        生效方式

            全局：对当前会话无效，只对新建会话有效

            会话：即时生效，但只对当前会话有效

    静态

        不需要重启生效，但是重启会失效。可以写在配置文件中，通过参数传递给mysqld

服务器变量：@@变量名

    显示：select

    设定：set {global|session} 变量名='value'

连接管理器：

    接受请求

    建立安全连接

    创建线程

    认证用户

并发控制：

并发控制依赖的手段

    锁

    时间戳

    多版本和快照隔离

多版本并发控制：MVCC。每个用户执行的都是一个语句的副本，最后合并副本

简单的并发控制依靠锁

    锁：

        读锁：共享锁

        写锁：独占锁（排他锁）

            LOCK TABLES tab_name {READ|WRITE};

            UNLOCK TABLES--->解所有锁

    锁粒度：从大到小，MySQL服务器仅支持表级锁，行锁需要由存储引擎完成。越粗糙越容易管理，越精细越容易实现多个操作的并发性（内部实现的就越复杂）

            表锁：锁定数据表

            页锁：锁定一个数据块

            行锁：锁定一行

    事务（状态：活跃、部分提交（最后一句执行中）、失败的、终止的、提交的）：

        RDBMS:ACID(原子性，一致性，隔离性，持久性)

            原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

            一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

            隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

            持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MyISAM不支持事务，InnoDB支持事务

事务日志（顺序IO。数据文件是随机IO）：

    重做日志

        redo log根据日志重复操作

    撤销日志

        undo log每次操作都保留下来，以保证撤销

事务隔离（定义了数据库系统中一个操作的结果在何时以何种方式对其他并发操作可见）：

    隔离级别：

        READ UNCOMITTED：未提交读，是最低的隔离级别。允许“脏读”（dirty reads），事务可以看到其他事务“尚未提交”的修改。

        READ COMMITTED：提交读级别，基于锁机制并发控制的DBMS需要对选定对象的写锁一直保持到事务结束，但是读锁在SELECT操作完成后马上释放（因此“不可重复读”现象可能会发生，见下面描述）。和前一种隔离级别一样，也不要求“范围锁”。

        REPATEABLE READ：可重复读隔离级别，基于锁机制并发控制的DBMS需要对选定对象的读锁（read locks）和写锁（write locks）一直保持到事务结束，但不要求“范围锁”，因此可能会发生“幻影读”。--->mysql默认

        SERIABLIZABLE：可串行。在基于锁机制并发控制的DBMS实现可串行化，要求在选定对象上的读锁和写锁保持直到事务结束后才能释放。在SELECT 的查询中使用一个“WHERE”子句来描述一个范围时应该获得一个“范围锁”（range-locks）。这种机制可以避免“幻影读”（phantom reads）现象。

事务调度：

    可恢复调度

    无级联调度

SQL语句

数据库：

    CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=字符集] [COLLATE=排序方式]

    ALTER DATABASE|SCHEMA CHARACTER SET=字符集 COLLATE=排序方式

    DROP DATABASE|SCHEMA db_name

表：

    1.直接定义一张空表

    2.从其他表中查询出数据，并创建新表

    3.以其他模板创建一个空表

键也成为约束，可用作索引，属于特殊的索引(有特殊限定)：B+TREE的索引结构

DDL(Data Definition Language):

    CREATE

    ALTER

    DROP

    TRUNCATE

    COMMENT

    RENAME

创建表：

    CREATE TABLE [IF NOT EXISTS] tb_name(col_name col_definition,constraint)

创建索引

    CREATE INDEX

    SHOW INDEXS FROM tb_name

单字段：

    PRIMARY KEY

    UNIQUE KEY

单或多字段：

    PRAMARY KEY (col,...)

    UNIQUE KEY (col,...)

    INDEX (col,...)

修改表：

    ALTER TABLE

        添加、删除、修改字段

        添加、删除、修改索引

        修改表名

        修改表属性

删除表：

    DROP TABLE

创建索引（索引只能创建和删除，不能被修改，因为索引是表自动维护的数据结构）

    CREATE INDEX index_name ON tb_name(col,...) USING BTREE;

        col_name[(length)] [ASC 升序|DESC 降序]

删除索引

    DROP INDEX index_name from tb_name;

DML(Data Manipulation Language):

    SELECT

    INSERT

    UPDATE

    DELETE

    MERGE

    CALL

    EXPLAIN PLAN

    LOCK TABLE

SELECT select-list FROM tb WHERE qualification

查询语句类型：

    单表查询

    多表查询

    子查询

单表查询

SELECT * FROM tb_name;

SELECT filed1,filed2 FROM tb_name; 投影

SELECT [DISTINCT相同的值只显示一次] * FROM tb_name WHERE qualification; 选择

FROM子句：要查询的关系。表、多个表、其他的SELECT语句

WHERE子句：布尔关系表达式

=,>,>=,<,<=

逻辑关系。与（AND &&）、或（OR ||）、非（NOT ！）关系

ORDER BY field_name{ASC|DESC} 查询后排序

LIMIT [offset偏移了多少个,]count取多少个

聚合函数（分组：GROUP BY）

SUM() 求和

MIN() 最小

MAX() 最大

AVG() 平均值

COUNT() 个数和

GROUP BY：分组

HAVING qualification--->再次过滤

特殊操作符

表示之间的值。BETWEEN...AND...

比较两个值。LIKE ''

%:任意长度任意字符

_:任意单个字符

支持正则表达式。RLIKE ''

在列表中。IN

判断是否为空：IS NULL

判断是否不空：IS NOT NULL

字段别名：AS

多表查询

连接

交叉连接：笛卡尔乘积

自然连接：是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。

外连接：

左外连接 ... LEFT JOIN ... ON ...

右外连接 ... RIGHT JOIN ... ON ...

自连接：表的数据连接到表自己中的数据

子查询

比较操作使用子查询：子查询只能返回单值

IN()中使用子查询

FROM中使用子查询

联合查询

... UNION ...

DELETE:

DELETE FROM tb_name WHERE condition;

INSERT INTO:

INSERT INTO ta_name (col1,col2,...) VALUES (val1,val2,...)[,(val1,val2,...),...]

字符型：单引号

数值型：不需要引号

日期时间型：不需要引号

空值：NULL

UPDATE:

UPDATE tb_name SET col1=...,col2=... WHERE

视图：存储下来的SELECT语句

基于基表的查询结果

创建视图

CREATE VIEW

删除视图

DROP VIEW

DCL(Data Control Language):

GRANT

REVOKE

GRANT ALL PRIVIEGES ON [object_type] db.* TO username@'%';

GRANT OPTION（资源使用限定）

MAX_QUERIES_PER_HOUR count--->每小时最大查询次数

MAX_UPDATES_PER_HOUR count--->每小时最大更新次数

MAX_CONNECTIONS_PER_HOUR count--->每小时用户最大连接数

MAX_USER_CONNECTIONS count--->用户最大连接数

REVOKE SELECT ON db.* FROM 'username'@'%';

mysql用户调用查看的六张表

user:用户帐号、全局权限

db:库级别权限

host:已废弃（已经整合到user表）

tables_priv:表级别权限

columns_priv:列级别权限

procs_priv:存储过程和存储函数相关的权限

proxies_priv:代理用户权限

用户帐号：

用户名@主机

用户名：16字符以内

主机：

主机名：www.aaaa.com，mysql

ip地址：172.16.10.177

网络地址：172.16.0.0/255.255.0.0

通配符：172.16.%.%

--skip-name-resolve:略过正解反解名称

权限级别：

全局级别：SUPER

库：

表：DELETE,ALTER,TRIGGER

列：SELECT,INSERT,UPDATE

存储过程和存储函数

创建mysql用户：

CREATE USER username@'%' [IDENTIFIED BY 'password']

INSERT INTO mysql.user;

mysql> FLUSH PRIVILEGES;

删除mysql用户：

DROP USER 'username'@'host';

重命名mysql用户

RENAME USER old_name TO new_name;

TCL(Transaction Control Language):

SAVEPOINT

ROLLBACK

SET TRANSACTION

START TANSACTION:启动事务

COMMIT:提交事务

ROLLBACK:回滚事务

如果没有明确启动事务:

autocommit：能实现自动提交，每一句操作都能直接提交

select @@autocommit

SAVEPOINT:保存点

ROLLBACK TO sid:回滚保存点

SHOW CREATE：查看创建某个对象的时候使用的语句

在shell中可以直接使用mysql -e来调用mysql command

TRUNCATE tb_name:清空表，并重置AUTOINCREMENT计数器；delete语句则会清空表不重置计数器

忘记mysql密码：

启动mysqld_safe时传递两个参数：

--skip-grant-tables

--skip-networking

通过更新授权表的方式直接更改其密码，而后移除两个选项重启服务器

日志

错误日志

mysql选项：

log_error

log_warnings

erver启动和关闭过程的信息

server运行过程中的错误信息

事件调度器运行一个事件时产生的信息

在从服务器上启动从服务器进程时产生的信息

一般查询日志

general_log

general_log_file

log

还可以保存到表中，默认不开启，要手动创建该表

慢查询日志

long_query_time 默认为10秒

log_slow_queries={YES|NO}

slow_query_log 默认关闭

slow_query_log_file 数据目录下的localhost-slow.log

二进制日志：任何引起或可能引起数据库变化的操作（DDL，DML）：复制，即时点恢复

mysqlbinlog

--start-datetime 'yyyy-mm-dd hh:mm:ss' 起始时间

--stop-datetime 'yyyy-mm-dd hh:mm:ss' 结束时间

--start-position 起始位置

--stop-position 结束位置

二进制日志的格式

基于语句:statement

基于行:row

混合方式:mixed

二进制日志事件：

产生事件

相对位置

查看当前正在使用的二进制日志文件

mysql> SHOW MASTER STATUS;

查看二进制日志事件

mysql> SHOW BINLOG ENENTS IN 'mysql-bin.000000x' [FROM position];

查看所有二进制日志文件

mysql> SHOW BINARY LOGS;

删除二进制日志

mysql> PURGE BINARY LOGS TO 'mysql-bin.000000x';

二进制日志滚动

mysql> FLUSH LOGS;

中继日志

从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件

事务日志：保证ACID，将随机IO转换为顺序IO

innodb_flush_log_at_trx_commit

0:每秒同步，并执行磁盘flush操作

1:每事务同步，并执行磁盘flush操作

2:没事务同步，但不执行磁盘flush操作

mysql存储引擎

MyISAM:

不支持事务

表锁

b-tree索引、fulltext索引、空间索引

支持表压缩

InnoDB:

事务

行锁

b-tree索引、聚簇索引、自适应hash索引

表空间，raw磁盘设备

mysql备份

备份内容：

数据

配置文件

二进制日志

事务日志

备份类型

热备：读写不受影响

xtrabackup,mysqldump

温备：能读不能写

冷备：离线备份

物理备份：复制数据文件

速度快

逻辑备份：将数据导出至文本文件中

速度慢、丢失浮点数精度。方便使用文本处理工具直接对其处理、可移植能力强

完全备份：备份全部数据

增量备份：仅备份上次完全备份或增量备份以后变化的数据

差异备份：仅备份上次完全备份以来变化的数据

备份策略：

完全+增量

完全+差异

mysql备份工具：

mysqldump：逻辑备份工具、MyISAM（温）、InnoDB（热）

mysqlhotcopy：物理备份工具、温备

参考文档：

MySQL中的事务与锁：http://zheming.wang/blog/2015/04/23/16301743-802B-4795-B79F-5DB37C7D587B/