【若泽大数据】MySQL(MariaDB)笔记 - （超详细）

SQL: Structure Query Language


DDL: CREATE, DROP, ALTER
DML: SELECT, INSERT, UPDATE, DELETE
DCL: GRANT, REVOKE


事务：
ACID
A: 原子性
C: 一致性
I：隔离性
D：持久性


提交：持久
未提交：提交，回滚




隔离：隔离级别
读未提交：read uncommitted
读提交：  read committed
可重读：  repeatable read
串行化：  serializable


MySQL: 存储引擎
MyISAM: 无事务
非聚集
InnoDB: 事务型
聚集索引


机械式硬盘：
随机读写
顺序读写


关系型数据库设计前三范式：
字段的原子性
主键
非主属性不允许重复


SQL: 规范，ANSI
SQL-86， SQL-89， SQL-92， SQL-99， SQL-03


关系数据库的约束：主键，外键，惟一键，条件约束，非空约束


MySQL的安装方式：
源码编译
rpm包：
OS Vendor
MySQL
通用二进制格式


初始化：提供配置文件

配置文件.cnf
集中式的配置:多个应用程序共用的配置文件
[mysqld]
[mysqld_safe]
[client]


# /usr/local/mysql/bin/mysqld --help --verbose | head -20


Default options are read from the following files in the given order:
/etc/mysql/my.cnf  /etc/my.cnf  ~/.my.cnf 


使用配置文件的方式
1、它依次查找每个需要查找的文件，结果是所有文件并集；
2、如果某参数在多个文件中出现多次，后读取的最终生效；


# /usr/local/mysql/bin/mysqld --help --verbose
1、显示mysqld程序启动时可用的选项，通常都是长选项
2、显示mysqld的配置文件中可用的服务变量
mysql> SHOW GLOBAL VARIABLES
mysql> SHOW SESSION VARIABLES


初始化：第二个操作
1、删除所有匿名用户
mysql> DROP USER ''@'localhost';
mysql> DROP USER ''@'www.magedu.com';


用户帐号由两部分组成：username@host
host还可以使用通配符：
%: 任意长度的任意字符
_: 匹配任意单个字符


2、给所有的root用户设定密码:
第一种方式：
mysql> SET PASSWORD FOR username@host = 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接口进行通信；
Linux OR Unix: Unix Sock, /tmp/mysql.sock, /var/lib/mysql/mysql.sock
Windows: memory, pipe
远程通信：客户端与服务器位于不同的主机，或在同一主机便使用非回环地址通信
TCP socket


客户端工具：mysql, mysqladmin, mysqldump, mysqlcheck
[client]


通行的选项：
-u, --user=
-h, --host=
-p, --password=
--protocol={tcp|socket|memory|pipe}
--port=
--socket=    例如：/tmp/mysql.sock




mysql监听的端口： 3306/tcp


非客户端类的管理工具：myisamchk, myisampack


mysql工作模式: 
交互式模式
mysql> 
脚本模式
mysql < /path/to/mysql_script.sql


mysql交互式模式：
客户端命令
mysql> help
mysql> \?
\c
\g
\G
\q
\!
\s
\. /path/to/mysql_script.sql
服务器端命令：需要命令结束符，默认为分号(;)
mysql> help contents


mysql> help Keryword


mysql命令行选项：
--compress
--database=, -D 
-H, --html：输出结果为html格式的文档
-X, --xml: 输出格式为xml
--sate-updates: 拒绝使用无where子句的update或delete命令；


mysql命令提示符：
mysql> 等待输入命令
->
'>
">
`>
/*> 


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




mysqldmin工具：
mysqladmin [options] command [arg] [command [arg]] ...


command: 
create DB_NAME
drop DB_NAME
debug: 打开调试日志并记录于error log中；


status：显示简要状态信息
--sleep #: 间隔时长
--count #: 显示的批次


extended-status: 输出mysqld的各状态变量及其值，相当于执行“mysql> SHOW GLOBAL STATUS”
variables: 输出mysqld的各服务器变量


flush-hosts: 清空主机相关的缓存：DNS解析缓存，此前因为连接错误次数过多而被拒绝访问mysqld的主机列表
flush-logs: 日志滚动，二进制日志和中继日志
refresh: 相当于同时使用flush-logs和flush-hosts


flush-privileges: 
reload: 功能同flush-privileges


flush-status: 重置状态变量的值


flush-tables: 关闭当前打开的表文件句柄


flush-threads：清空线程缓存


kill:　杀死指定的线程，可以一次杀死多个线程，以逗号分隔，但不能有多余空格


password: 修改当前用户的密码；


ping: 


processlist：显示mysql线程列表


shutdown: 关闭mysqld进程；


start-slave
stop-slave: 启动/关闭从服务器线程


GUI客户端工具：
Navicat for mysql
Toad for mysql
mysql front
sqlyog
phpMyAdmin


总结：mysql初始化，mysql配置文件读取次序，mysql初始用户，mysql客户端命令，mysqldmin，GUI




开发DBA：数据库设计(E-R关系图)、SQL开发、内置函数、存储例程(存储过程和存储函数)、触发器、事件调度器(event scheduler)
管理DBA：安装、升级，备份、恢复，用户管理、权限管理，监控、分析、基准测试，语句优化(SQL语句)，数据字典，按需要配置服务器（服务器变量：MyISAM, InnoDB, 缓存, 日志）


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


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


数据字典：系统编目(system catalog)
保存数据库服务器上的元数据


元数据：
关系的名字
每个关系的各字段的名字
各字段的数据类型和长度
约束
每个关系上的视图的名字及视图的定义


授权用户的名字
用户的授权和帐户信息


统计类的数据：
每个关系字段的个数；
每个关系中行数；
每个关系的存储方法；


保存元数据的数据库：
information_schema
mysql
performance_shcema


数据类型：
字符型
char
varchar
binary
varbinary
text
blob
数值型
精确数值型
整型
十进制数据：decimal
近似数值型
单精度浮点型
双精度浮点型
日期时间型
日期型
时间型
日期时间型
时间戳
布尔型


内建类型
ENUM, SET


数值型：
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
DECIMAL
FLOAT
DOUBAL


BIT


字符型：
CHAR
VARCHAR
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT


BINARY
VARBINARY
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB


ENUM
SET


日期时间型：
DATE
TIME
DATETIME
TIMESTAMP
YEAR


CHAR、VARCHAR和TEXT几种字符型常用的属性修饰符：
NOT NULL：非空约束
NULL：允许为空
DEFAULT 'string'：默认值，不适用于TEXT类型
CHARACTER SET '字符集'
mysql> SHOW VARIABLES LIKE '%char%';
mysql> SHOW CHARACTER SET
COLLATION '规则': 排序规则
mysql> SHOW COLLATION;


BINARY、VARBINARY和BLOB几种字符型常用的属性修饰符：
NOT NULL
NULL
DEFAULT: 不适用于BLOB


整型的常用属性修饰符：
AUTO_INCREMENT：自动增长
前提：非空，且惟一；支持索引，非负值；
UNSIGNED：无符号
NULL
NOT NULL
DEFAULT 


浮点型常用修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED


日期时间型的修饰符：
NOT NULL
NULL
DEFAULT


ENUM和SET的修饰符：
NOT NULL
NULL
DEFAULT ''


MySQL SQL_MODE: SQL模式
TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES




设定服务器变量的值：（仅用于支持动态的变量）
支持修改的服务器变量：
动态变量：可以MySQL运行时修改
静态变量：于配置文件中修改其值，并重启后方能生效；


服务器变量从其生效范围来讲，有两类：
全局变量：服务器级别，修改之后仅对新建立的会话有效；
会话变量：会话级别，仅对当前会话有效；
会话建立时，从全局继承各变量；


查看服务器变量：
mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];
mysql> SELECT @@{GLOBAL|SESSION}.VARILABLE_NAME;
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';


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


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


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




MySQL中字符大小写：
1、SQL关键字及函数名不区分字符大小写；
2、数据库、表及视图名称的大小区分与否取决于低层OS及FS
3、存储过程、存储函数及事件调度器的名字不区分大小写，但触发器区分；
4、表别名区分大不写；
5、对字段中的数据，如果字段类型为Binary类型，则区分大小写；非Binary不区分大小写；


数据库：
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']


DROP {DATABASE | SCHEMA} [IF EXISTS] db_name


ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']


表：
表创建：第一种方式
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   (create_definition,...)
   [table_options]


   (create_definition,...)：
    字段的定义：字段名、类型和类型修饰符
    键、约束或索引：
    PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK
    {INDEX|KEY} 


   [table_options]
    ENGINE [=] engine_name
    mysql> SHOW ENGINES;
    AUTO_INCREMENT [=] value
    [DEFAULT] CHARACTER SET [=] charset_name
    [DEFAULT] COLLATE [=] collation_name
    COMMENT [=] 'string'