MySQL or MariaDB：

层次模型 --> 网状模型 --> (IBM Codd)关系模型 --> No-SQL关系型数据库管理系统(RDBMS)： 范式：第一范式，第二范式，第三范式； 表：二维表； Row：Record，记录； Column：Field，字段；描述每个记录的特定属性； 任何一张RDBMS的表中，可以没有任何Row，但至少应该有一个Column； 对表的操作： 关系运算： 选择：从指定的表中检索符合条件的行； SELECT * FROM tbl_name WHERE clause; 投影：从指定的表中检索符合条件的列； SELECT column1,column2,column3 as column3_alias FROM tbl_name; 注意：可以同时实现选择和投影的关系运算操作； SELECT column1,column2,column3 as column3_alias FROM tbl_name WHERE clause;数据库： 表，索引，视图(虚表)，SQL_interface，存储过程，存储函数，触发器，事件调度器; DDL DML约束： 主键约束：数据唯一且不能为空，每张表只能有一个主键； 唯一键约束：数据唯一，可以为空，每张表中不限制唯一键的数量； 外键约束：引用性约束或参考性约束；即：如果某表中频繁出现冗余信息，应该将此类信息存储于其他的表中，而此表中该字段的数据为另一张表的主键中所包含的值； 检查性约束：表达式约束；DBMS的三层模型： 视图层；面向最终用户； 逻辑层；面向程序员或DBA； 物理层；面向系统管理员；RDBMS的应用实现： 商业版：Oracle，DB2，Sybase，Infomix，SQL Server，MySQL； 开源版：MySQL Community Server，MariaDB，PostgreSQL，SQLlite；MySQL/MariaDB： MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 --> 8.0 --> 8.0.11 (mysql shell, mysqlsh) MariaDB：5.5.x --> 10.x 特性： 插件式的存储引擎： 存储引擎于MySQL/MariaDB而言，也被称为"表类型"； MyISAM，InnoDB，XtraDB(InnoDB)，Aria，BlackHole，... 单进程多线程； 在Linux(CentOS)中安装MySQL/MariaDB的方式： 1.rpm包： OS Vendor Third Side 官方； 2.Binary Package： OS：Linux、windows Arch：i686，x86_64，ppc，... Glibc：msyql-linux-glibc214 Init/Systemd：Systemd 3.SourceCode Package： mysql/mariadb-Version.tar.gz cmake 配置文件： 位于不同位置的多个配置文件，通常按照一定的顺序来读取其中的配置参数： Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf /etc/my.cnf.d ~/.my.cnf 已经被指定顺序的配置中如果出现同一配置参数且具有不同的值，最后读取的文件中的参数值将成为最终生效的参数值； 在使用mysqld_safe命令启动mysqld服务进程时，可以通过一些选项来更改或附加配置文件的读取顺序； -c, --defaults-file=name Like --config-file, except: if first option, then read this file only, do not read global or per-user config files; should be the first option 如果在命令行中给出此选项，则此选项必须是第一个选项，此时，仅从此选项指定的配置文件中读取参数值，不会再读取任何其他的配置文件，包括全局的和个人的； -e, --defaults-extra-file=name Read this file after the global config file and before the config file in the users home directory; should be the first option 如果在命令行中给出此选项，则此选项必须是第一个选项，此时，将所有其他的配置文件按照指定顺序读取完成之后，最后再附加读取此选项指定的配置文件中参数值配置，只要有参数配置冲突，则必然以此选项指定的文件中的参数中为最终生效的参数值； 配置文件的风格： ini风格的配置文件；即：分段式配置文件； 为MySQL的各应用程序提供与之对应的配置参数信息： 服务器端应用程序： [server] socket= datadir= basedir= [mysqld] socket= [mysqld_safe] socket= [mysqld_multi] 客户端应用程序： [client] user= password= host= [mysql] user= password= host= [mysqladmin] [mysqldump] 配置文件中各参数的定义方法： PARAMETER_NAME = VALUE PARAMETER_NAME=VALUE PARAMETER_NAME innodb_file_per_table = ON innodb-file-per-table = ON defaults-file=/tmp/my.cnf defaults_file=/tmp/my.cnf skip_name_resolve=ON skip_name_resolve 程序环境： 程序文件： 服务器端程序： mysqld_safe、mysqld_multi 客户端程序： msyql、mysqladmin、mysqldump、... 服务器端辅助管理工具程序： myisam*、my_print_defaults、mysql_secure_installation、mysql_install_db、... mysql：交互式命令行工具和非交互式命令行工具； -u, -h, -p, -P, -D, -S, -e 交互式模式： 客户端命令： ?(\?,\h), \c, \d, \g, \G, \q, \., \!, \s, \u, ... 服务器端命令： SQL语句，需要使用语句结束符； help COMMAND DDL： CREATE、DROP、ALTER、SHOW； DML： SELECT、INSERT、UPDATE、DELETE； DCL： GRANT、REVOKE； mysql命令支持mysql脚本的执行： mysql [options] [DB_NAME] < /PATH/TO/SOME_SQL_SCRIPT_FILE mysqladmin命令： mysqladmin - client for administering a MySQL server 常用选项： -u, -h, -p, -P, -D, -S 各选项功能同mysql命令的选项功能； 常用子命令： create db_name：创建数据库； drop db_name：删除数据库； flush-hosts：清除服务器缓存中所有信息； flush-logs：清除日志，让日志滚动； flush-privileges：刷新授权表； flush-tables：为表加锁； password new-password：为指定的用户设置新密码； start-slave：在msyql的集群服务中的从节点启动用于实施复制功能的线程； stop-slave：在msyql的集群服务中的从节点关闭用于实施复制功能的线程； shutdown：停止服务； mysqld_safe命令：用于启动mysql服务，定义mysql的工作特性； 格式：/usr/bin/mysqld_safe [OPTIONS] 注意：所有给出的 OPTION(--option)都是一次性生效；如果期望配置参数永久有效，需要将此类配置参数及参数值直接定义在配置文件中即可； 服务器运行时变量/服务器运行时参数：MySQL的运行工作特性；这里特性有两种： 1.全局特性： 在全局范围均有效的服务器参数所配置定义的工作特性；将会作为每个mysql的会话连接的默认特性参数； 2.会话特性： 仅针对于本次mysql的连接会话生效的特性参数； 查看已经生效的mysql运行参数(特性，变量) SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] 示例： show [global] variables like ‘%innodb%‘; 查看所有名字中包含innodb字样的服务器参数及参数值；可以一次显示多个； show [global] variables like ‘innodb_file_per_table‘; 仅查看指定名称的服务器参数的参数值； select @@[global.]innodb_file_per_table; 仅查看指定名称的服务器参数的参数值； 服务器状态参数/服务器状态变量：MySQL工作时的统计信息；有两种状态参数： 1.全局 2.会话 查看与状态及统计信息相关的参数/变量的值； SHOW [GLOBAL | SESSION] STATUS [like_or_where] 示例： show [global] status like ‘%innodb%‘; 查看所有名字中包含innodb字样的服务器状态参数及其值；可以一次显示多个； show [global] staus like ‘Com_select‘; 仅查看指定名称的服务器状态参数的值； 服务器变量/服务器参数的修改或调整方法： 1.运行时修改： MariaDB [(none)]> SET [GLOBAL|SESSION] system_var_name = expr; MariaDB [(none)]> SET @@[GLOBAL.|SESSION.]system_var_name = expr; 示例： set global innodb_file_per_table=1; set @@global.innodb_file_per_table=0; 2.永久修改： 通过在配置文件中直接书写服务器参数或变量的赋值语句；重启服务即可生效； innodb_file_per_table = ON mysql_secure_installation：安全初始化脚本；

SQL：结构化查询语言；
ANSI：美国国家标准委员会；
SQL-86，SQL-89，SQL-92，SQL-99，SQL-03

SQL-MODE：MariaDB/MySQL： ANSI：宽松模式。对于插入的数据进行校验，如果该数据不符合字段定义的数据类型或长度要求，则对数据类型进行调整，也可以对数据进行截取保存，发送warning警告； TRADITIONAL：严格模式。对于插入的数据进行校验，如果该数据不符合字段定义的数据类型或长度要求，报告Error错误警告，且错误数据不会被写入； 该模式用于事务时，此前可能已经进行了一系列的数据插入和修改操作，而一旦发送上述错误，此前所有的INSERT/UPDATE等操作均会被立即放弃并回滚。 该模式用于非事务时，一旦发送上述错误，则此前所有的INSERT/UPDATE等操作均不会回滚 STRICT_TRANS_TABLES：严格模式。对于插入的数据进行校验，如果该数据不符合字段定义的数据类型或长度要求，报告Error错误警告，且错误数据不会被写入； STRICT_ALL_TABLES：严格模式。对于事务型操作，与STRICT_TRANS_TABLES模式效果一样；对于非事务操作，与TRADITIONAL模式效果一样。 实时修改sql_mode： MariaDB [(none)]> set @@global.sql_mode=TRADITIONAL; 注意： 1.没有最好或最差的模式，只有最合适的模式； 2.使用上述SQL命令只能一次性生效，如果想要永久修改，则需要编辑配置文件；MySQL的数据类型： 字符型： CHAR(#),BINARY(#)：定长字符类型；CHAR类型不区分字符大小写，BINARY类型区分； VARCHAR(#),VARBINARY(#)：变长字符类型； TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT； BLOB：TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB； 数值型： 浮点型：近似值； 单精度 双精度 REAL BIT 整型：精确值； INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT； BOOLEAN DICIMAL OCTAL HEXIMAL 日期时间型： 日期型：DATE 2018/07/19 时间型：TIME 9:38:42 日期时间型：DATETIME 2018/07/19 9:38:42 时间戳：TIMESTAMP，数值型的整型； 内建类型： ENUM：枚举； SET：集合； 数据类型的修饰符： 字符型：NULL, NOT NULL, DEFAULT ‘STRING‘,CHARACTER SET ‘CHARSET‘, COLLATION "COLLATION"； 整型：NULL, NOT NULL, DEFAULT ‘VALUE‘, AUTO_INCREMENT, UNSIGNED； 日期时间型：NULL, NOT NULL, DEFAULT ‘DATE/TIME/DATETIME‘

SQL：
DDL：数据库对象；
获取DDL相关SQL语句的方法：
MariaDB [testdb]> help data definition

 数据库对象： DATABASE, TABLE, VIEW, FUNCTION, PRECEDURE, INDEX, ...DML：数据； 获取DML相关SQL语句的方法： MariaDB [testdb]> help data manipulation 数据操纵： INSERT/REPLACE, DELETE, UPDATE, SELECT, ...

DDL：
数据库：
创建数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name]

 修改数据库的字符集或排序规则： ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] [CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name] 删除数据库： DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 使用客户端命令创建数据库： ~]# mysqladmin create mydb ~]# mysql -e "create database mydb;" 使用客户端命令删除数据库： ~]# mysqladmin drop mydb //会对删除数据库的操作进行确认； ~]# mysql -e "drop database mydb;"表： 创建表： CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] //使用SQL语句全新的定义出一张新表，包括表的名称、字段数量、数据类型、存储引擎的选择等各种属性； Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement //利用SELECT语句的查询结果来填充新表的内容，但是新表的表格式可能与基表不一致，很多的数据类型的修饰符可能会丢失； Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) } //直接复制基本的表格式到新表上，但新表中没有任何数据，即为空表； 注意： 1.对于MySQL或MariaDB的表来说，存储引擎是非常重要的概念，通常需要在创建表的时候来指定；如果没有明确指定，则使用默认的存储引擎； 2.对于已经创建完成的空表，可以任意调整其存储引擎； 3.对于非空表，不建议直接修改表的存储引擎； 良心建议：在创建表之初或者存储数据之前，确定表的存储引擎； 删除表： DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] 建议：使用修改表名称的方式使指定表不再被继续使用； 修改表格式： ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] 可以修改的内容： ADD：字段，索引，约束，键(主键，唯一键，外键) CHANGE：字段名称，字段定义格式和字段的位置； MODIFY：字段定义格式和字段的位置； DROP：字段，索引，约束，键； RENAME：修改表名称； 查看表结构： DESC [db_name.]tbl_name; 查看表的定义方式： SHOW CREATE TABLE tbl_name; 查看表的状态和属性信息： SHOW TABLE STATUS [from | in db_name] like ‘PATTERN‘ | where expr; 示例： MariaDB [hellodb]> show table status where name=‘students‘\G视图：VIEW，虚表； 就是一个SELECT语句的执行结果； 创建视图： CREATE VIEW view_name [(column_list)] AS SELECT clause; 示例： MariaDB [hellodb]> create view student as select StuID,Name,Gender from students; 删除视图： DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] 示例： MariaDB [hellodb]> drop view student; 注意： 能否在视图中实现插入新的数据记录，取决于基表中没有被视图选择的字段是否要求不能为空，如果有此类约束，则结果是无法插入新数据；否则可以插入新数据，没有被视图选择的字段内容，默认都为"NULL"；索引： 索引的类型： 聚集索引、非聚集索引： 聚集索引：索引和数据存放在一起，找到索引即找到数据； 非聚集索引：索引和数据不存放在一起，索引通过索引指针指向数据所在位置； 稠密索引、稀疏索引：是否索引了每一条数据记录； 稠密索引：每条数据记录都有一条索引与之对应； 稀疏索引：并不是每条数据记录都有一条索引与之对应，而是一条索引对应某个或某些数据块； 主键索引、辅助索引： BTree：Balance Tree，B- Tree，BTree，B+Tree 左前缀索引： 注意：在使用BTree索引进行检索时，给出的PATTERN的最左侧字符不要出现通配符，否则，无法使用索引进行检索；只能全文遍历； Hash索引： R Tree：Spacial，空间索引； FULLTEXT：全文索引； 覆盖索引：索引中的内容就是要检索的内容，通过检索索引内容即可立即找到数据，并直接返回检索结果； EXPLAIN：分析查询语句的执行路由； 创建索引： 1.在创建表时，通过指定主键或唯一键，可以自动创建索引； 2.如果在创建表时，没有指定主键或唯一键，则可以在表成功创建之后添加索引； CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ... 示例： MariaDB [hellodb]> create index name_index on students(Name); 查看索引： SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] 示例： MariaDB [hellodb]> show index from students\G 删除索引： DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name 示例： MariaDB [hellodb]> drop index name_index on students;DML：操纵数据； INSERT/REPLACE、DELETE、UPDATE、SELECE INSERT：向表中插入新的数据记录；每次可以向表中插入一行或多行数据； INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... 示例： MariaDB [hellodb]> insert into students (Name,Age,Gender) values (‘Rio Messi‘,31,‘M‘); MariaDB [hellodb]> insert into students (Name,Age,Gender) values (‘Guo Jing‘,40,‘M‘),(‘Huang Rong‘,27,‘F‘); 没有明确的规定字段名称，则意味着为一行中的各个字段添加数据内容： MariaDB [hellodb]> insert into students values (30,‘Liu Bei‘,57,‘M‘,1,2); 注意：添加的数据内容，必须要严格的对应每个数据字段，需要保证数据类型的匹配； INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ... 示例： MariaDB [hellodb]> insert into students set Name=‘Tang Xuanzang‘,Age=35,Gender=‘M‘; INSERT [INTO] tbl_name [(col_name,...)] SELECT ... 将后面SELECT语句的查询结果插入到选中的目标表中；注意下列问题： 1.SELECT语句的查询结果中包含的字段数量，应该和目标表中的指定字段数量相同； 2.SELECT语句的查询结果中包含的各字段的数据类型，必须要与目标表中各字段的数据类型保持一致； 此种插入数据的方法，更多的用于表复制操作； 此前曾经使用CREATE TABLE命令通过复制表格式的方式创建过一个空的新表，然后再将原表中的数据以方法复制到新表中； REPLACE命令与INSERT命令的功能几乎完全相同，除了一种特殊情况之外： 当向表中插入数据时，如果主键位置或唯一键位置出现重复数据时，不会继续插入而是选择替换对应行中各字段的数据；DELETE： Single-table syntax: DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax: DELETE tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] Or: DELETE FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] 注意：默认情况下，MySQL或MariaDB都不会阻止不带有WHERE条件子句的删除操作，这将意味着，有可能会因为此操作导致清空整张表中的数据； 限制条件： WHERE where_condition LIMIT row_count ORDER BY ... LIMIT row_count WHERE where_condition LIMIT row_count WHERE where_condition ORDER BY ... LIMIT row_count 示例： MariaDB [hellodb]> delete from students limit 3; 删除正常的查询结果中的前三行数据记录； MariaDB [hellodb]> delete from students where Age<20; 删除Age字段中值小于20的所有数据记录； MariaDB [hellodb]> delete from students where Name like ‘h%‘ limit 2; 删除Name字段以"H|h"开头的所有数据记录中的前两条记录； MariaDB [hellodb]> delete from students order by age desc limit 3; 删除根据Age字段进行降序排序的查询结果中的前三条数据记录；UPDATE： Single-table syntax: UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax: UPDATE table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] 注意：默认情况下，MySQL或MariaDB都不会阻止不带有WHERE条件子句的修改操作，这将意味着，有可能会因为此操作导致整张表中的所有数据记录被同时修改； 限制条件： WHERE where_condition LIMIT row_count ORDER BY ... LIMIT row_count WHERE where_condition LIMIT row_count WHERE where_condition ORDER BY ... LIMIT row_count 示例： 同DELETE的示例；注意：在MySQL或MariaDB中，如果服务器变量sql_safe_updates=ON，则可以阻止不带有限制条件的UPDATE更新操作或DELETE删除操作； 临时调整： MariaDB [hellodb]> set @@sql_safe_updates=ON; 永久生效：/etc/my.cnf sql_safe_updates=ON MariaDB [hellodb]> update students set ClassID=1; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnSELECT Query Cache：MySQL/MariaDB的查询结果缓存； K/V对存储； Key：查询语句经过hash之后的hash值； Value：查询语句的执行结果； MySQL/MariaDB的查询执行路径： 1.用户发送请求 --> 查询缓存(命中) --> 响应用户； 2.用户发送请求 --> 查询缓存(未命中) --> 解析器 --> 预处理器 --> [查询优化器 -->] 查询执行引擎 --> 存储引擎 --> 查询执行引擎 --> [缓存查询结果 -->] 响应用户； SELECT [ALL | DISTINCT | DISTINCTROW ] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE ‘file_name‘ [CHARACTER SET charset_name] export_options | INTO DUMPFILE ‘file_name‘ | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] DISTINCT：数据去重；即：重复出现的数据仅显示一次； SQL_CACHE： 显式的指出必须将此次的查询语句的执行结果存放至查询缓存； SQL_NO_CACHE： 显式的指出绝对不能将此次的查询语句的执行结果存放至查询缓存； query_cache_type服务器变量是MySQL的缓存开关，通常有三个取值： 1.ON：启用缓存功能； 默认缓存所有符合缓存条件的查询结果；除非使用SQL_NO_CACHE参数明确指出不缓存查询结果； 2.OFF：关闭缓存功能； 默认不缓存任何查询结果；仅能缓存使用SQL_CACHE参数明确的指出的查询结果； 3.DEMAND：按需缓存； 如果明确指出SQL_CACHE，即缓存查询结果，否则，默认隐式关闭查询缓存； query_cache_limit | 1048576 query_cache_min_res_unit | 4096 select_expr： *：表示表中的所有字段(列)； col1,col2,...coln：普通的列名列表；通常用于执行投影运算； col_name1 as col_alias1,col_name2 as col_alias2, ... 对于查询结果中的各字段名称使用别名进行重定义； table_references： [db_name.]tbl_name db_name.* [db_name.]tbl_name1,[db_name.]tbl_name2, ... [db_name.]tbl_name1 as tbl_alias1,[db_name.]tbl_name2 as tbl_alais2, ... 单表查询： select select_expr1,select_expr2,... from tbl_name where expr group by col_name having expr order by col_name limit WHERE条件子句： 通过指明特定的过滤条件或表达式来实现"选择"运算；过滤条件有下列几种： 1.算术表达式：Age+10， 算术操作符：+, -, *, /, %； 2.比较表达式：Age+10<20; 比较操作符：=, <=>, <>, !=, >, >=, <, <=； 3.逻辑表达式： 逻辑操作符：AND, OR, NOT, XOR； 4.其他表达式： 空值判断：IS NULL, IS NOT NULL； 连续区间判断：BETWEEN ... AND ... 相当于<=100 AND >=50； 列表从属关系判断：IN (LIST)； 模糊值判断： LIKE：可以支持通配符，%和_；如果想要使用索引实现加速检索，则最左侧字符不能使用通配符； RLIKE或REGEXP：可以支持正则表达式元字符；只要在查询条件中包含正则表达式元字符，则一定无法使用索引进行检索；功能很强大，但检索性能可能变差； GROUP BY子句： 根据指定的字段将查询结果进行分组归类，以方便进行聚合运算； 常用的聚合运算函数： avg()：取平均值运算； max()：取最大值运算； min()：取最小值运算； sum()：做和运算； count()：做次数统计； 示例： 统计每个班级里面的人数： MariaDB [hellodb]> select ClassID,count(ClassID) as nos from students group by ClassID; 统计每个班级里面所有人的平均年龄： MariaDB [hellodb]> select ClassID,avg(Age) as nos from students where ClassID is not null group by ClassID; 统计所有学生中男生和女生的平均年龄： MariaDB [hellodb]> select Gender,avg(Age) as nos from students group by Gender; HAVING子句：对于经过分组归类并进行了聚合运算以后的结果进行条件过滤； 其条件表达式的书写格式与WHERE子句相同； 示例： 统计人数超过3人的班级及其人数数据： MariaDB [hellodb]> select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3; ORDER BY子句：根据指定的字段将查询结果进行排序，可以使用升序或降序，默认是升序； 升序：ASC 降序：DESC 示例： MariaDB [hellodb]> select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc; LIMIT子句： 对于查询的结果进行限定行数的输出； LIMIT {[offset,] row_count | row_count OFFSET offset} 1.LIMIT [offset,] row_count offset：偏移量，在输出结果中，从第一行开始(含)跳过的不显示的行数； row_count：要显示的行数； 示例：显示查询结果中的第二行和第三行； MariaDB [hellodb]> select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc limit 1,2; 2.LIMIT row_count OFFSET offset 示例：显示查询结果中的第二行和第三行； MariaDB [hellodb]> select ClassID,count(ClassID) as nos from students where ClassID is not null group by ClassID having nos>=3 order by nos desc limit 2 offset 1; 多表查询： 建议：在生成环境中，能使用单表查询即可得到结果的操作，尽可能使用单表查询；因为多表查询会给服务器造成过大的负载压力； 所谓多表查询，即指通过对多个表内容的查询，以获得具有一定关联关系的查询结果的查询方式；也称为连接操作，连接操作也就是将多张表关联在一起的方法； 连接操作： 交叉连接： 也称为笛卡尔积连接； 内连接： 等值连接：让表和表之间通过某特定字段的等值判断的方式建立的内连接； 非等值连接：让表和表之间通过某特定字段的不等值判断的方式建立的内连接；在极少的场合中才有应用； 外连接：以某张为基准表，判断参考表与基准表之间的连接关系； 左外连接： 以左表为基准表，右表为参考表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL； 连接操作符：LEFT JOIN 右外连接： 以右表为基准表，左表为参考表，显示出基准表中所有的行，并将参考表中与基准表中有关联关系的行合并输出，如果基准表中的行与参考表中无关，则输出NULL； 连接操作符：RIGHT JOIN 自然连接： 通过MySQL的进程自行判断并完成的连接过程。通常MySQL会使用表中的名称相同的字段作为基本的连接条件； 连接操作符：NATRUAL INNER 自然外连接： 自然左外连接： 连接操作符：NATURAL LEFT JOIN 自然右外连接： 连接操作符：NATURAL RIGHT JOIN 自连接： 人为的将一张表中的两个字段之间建立的连接关系； 示例： 交叉内连接： 每个学生所在的班级名称： MariaDB [hellodb]> select Name,Class from students as s,classes as c where s.CLassID=c.ClassID; MariaDB [hellodb]> select Name,Class from students,classes where students.CLassID=classes.ClassID; 交叉左外连接： 每个学生所在班级的名称，即使该学生不属于任何班级： MariaDB [hellodb]> select Name,Class from students left join classes on students.CLassID=classes.ClassID; 交叉右外连接： 每个班级的学生姓名，即使该班级中没有任何学生； MariaDB [hellodb]> select Class,Name from students right join classes on students.ClassID=classes.ClassID; || MariaDB [hellodb]> select Class,Name from classes left join students on students.ClassID=classes.ClassID; 子查询：嵌套查询； 在SELECT查询语句中嵌套另一个SELECT查询语句；等同于从某个视图中获取查询结果； SELECT col1,col2,* FROM tbl_name WHERE col OPTS VALUE; 示例： 用于WHERE子句中的子查询： 查询学生中年龄大于全班平均年龄的学生的姓名和年龄； MariaDB [hellodb]> select Name,Age from students where Age>(select avg(Age) from students); 用于IN子句中的子查询： 查询学生的年龄和老师的年龄相同的学生的名字： MariaDB [hellodb]> select Name from students where Age in (select Age from teachers); 查询学生的年龄和老师的年龄相同的学生和老师的名字： MariaDB [hellodb]> select t.Name as Teacher,s.Name as Student from students as s,teachers as t where s.Age=t.Age; 用于FROM子句的子查询： 查询有班级的学生对应的班级名称： MariaDB [hellodb]> select s.Name,s.Class from (select StuID,students.Name,students.Age,Gender,Class from students,classes where students.ClassID=classes.ClassID) as s; 联合查询： 将多张表的内容通过多个SELECT语句查询得到的结果组合输出； 注意：使用联合查询的前提条件： 多张表需要有相同数据类型的字段； 操作符：UNION 示例： MariaDB [hellodb]> select StuID as ID,Name,Age,Gender from students union select TID as ID,Name,Age,Gender from teachers;select查询： 单表查询 --> 多表查询(交叉内连接) --> 多表查询(外连接) --> 子查询 --> 联合查询；MySQL的用户和权限管理： 用户账号： ‘Username‘@‘Hostname‘ skip_name_resolve = ON ‘user‘@‘172.16.0.1‘ ‘user‘@‘a.qhdlink.com‘ skip_name_resolve = OFF Client_IP --> Client_Hostname 在MySQL上能够实施的用户账户的管理操作： CREATE USER DROP USER GRANT RENAME USER REVOKE SET PASSWORD MySQL中的权限类别： 库级别； 表级别； 字段级别； 管理类； 程序类； 管理类的权限： CREATE USER DROP USER RELOAD LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHUTDOWN FILE SHOW DATABASES PROCESS SUPER 程序类的权限： FUNCTION PROCEDURE TRIGGER 操作：CREATE，ALTER，DROP，EXECUTE 库级别和表级别的权限： CREATE ALTER SHOW DROP INDEX CREATE VIEW SHOW VIEW GRANT OPTION：能够将管理员自身获得的权限生成一个副本，并转赠给目标用户； 表级别的数据操作的权限： INSERT REPLACE DELETE UPDATE SELECT 字段级别(列级别)的数据操作的权限： SELECT(col1,col2,...) UPDATE(col1,col2,...) INSERT(col1,col2,...) 所有的权限： ALL ALL PRIVILEGES MySQL的元数据数据库：mysql 数据字典数据库； host db user column_priv procs_priv proxies_prive tables_priv 上述元数据数据库中的表统称为"授权表"； 如果对于授权表的内容进行了修改，MySQL每300秒会自动重读并使新设置生效； 如果不打算等待，可以手动刷新授权表： MariaDB [mysql]> FLUSH PRIVILEGES; MySQL用户管理： ‘Username‘@‘Hostname‘ Username：任意的字符串组合，只能包含基本意义的字符；可以包含"_"、"."、"-"； Hostname： FDQN Domain_name IP_ADDRESS 可以使用MySQL的专用通配符：%, _ skip_name_resolve={ON|OFF} 创建用户账户： CREATE USER语句： CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password‘ | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘]] 示例： MariaDB [mysql]> create user ‘testuser‘@‘%‘; MariaDB [mysql]> create user ‘testuser‘@‘%‘ identified by ‘qhdlink‘; 也可以使用DML语句创建用户账户： INSERT INTO mysql.user SET User=‘testuser‘,Host=‘%‘,Password=PASSWORD(‘qhdlink‘); 示例： MariaDB [mysql]> insert into user set User=‘user1‘,Host=‘%‘,Password=PASSWORD(‘qhdlink‘),ssl_cipher=‘‘,x509_issuer=‘‘,x509_subject=‘‘,authentication_string=‘‘; 重命名用户账户： RENAME USER语句： RENAME USER old_user TO new_user [, old_user TO new_user] ... 示例： MariaDB [mysql]> rename user ‘testuser‘@‘%‘ to ‘test‘@‘172.16.%.%‘; 也可以使用DML语句重命名用户账户： 示例： MariaDB [mysql]> update user set User=‘user01‘,Host=‘172.16.75.%‘ where User=‘user1‘; 删除用户账户： DROP USER语句： DROP USER user [, user] ... 示例： MariaDB [mysql]> drop user ‘test‘@‘172.16.%.%‘; 也可以使用DML语句删除用户账户： 示例： MariaDB [mysql]> delete from user where User=‘user01‘; 用户账户的密码管理： 1.SET PASSWORD语句： SET PASSWORD [FOR user] = { PASSWORD(‘cleartext password‘) | OLD_PASSWORD(‘cleartext password‘) | ‘encrypted password‘ } 示例： MariaDB [mysql]> set password for ‘test‘@‘%‘ = PASSWORD(‘qhdlink‘); 2.也可以使用DML语句修改用户账户密码： 示例： MariaDB [mysql]> update user set Password=PASSWORD(‘qhdlink.com‘) where User=‘test‘; 3.mysqladmin工具： # mysqladmin -uUSERNAME -hHOSTNAME -p password ‘NEW_PASSWORD‘ 注意：执行此操作的MySQL用户需要对mysql.user表有修改权限； 忘记MySQL管理员的密码的解决办法： 方法一： 1.停止当前的MySQL或MariaDB服务； 2. 在/etc/my.cnf文件中加入下列两条服务器参数： skip-grant-tables = ON skip-networking = ON 3.启动MySQL或MariaDB服务，使用mysql或mysqladmin客户端工具以空秘密的root用户登录，进行root用户的密码修改； 4.从/etc/my.cnf中删除上述两条服务器参数，再重启服务即可； 方法二： 1.停止当前的MySQL或MariaDB服务； 2.使用命令启动MySQL服务： # mysqld_safe --skip-grant-tables --skip-networking 3.启动另一个会话连接，并使用mysql或mysqladmin客户端工具以空密码的root用户的身份修改其密码； 4.kill掉此前的mysqld-safe及衍生的mysqld服务； 5.再正常启动服务即可； 对于已经建立的用户或未建立的用户进行授权： GRANT语句： GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...] priv_type: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, FILE, SHOW DATABASES, PROCESS, SUPER object_type: TABLE | FUNCTION | PROCEDURE priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name *：表示所有的数据库； *.*：表示所有数据库中的所有表对象； db_name.*：表示指定数据库中的所有表对象； db_name.tbl_name：表示指定数据库中的指定的表对象； tbl_name：表示当前正在使用的数据库中的指定的表对象； db_name.routine_name：表示指定数据库中的指定存储函数后存储过程对象；通常需要使用object_type参数共同决定； user_specification: user [ IDENTIFIED BY [PASSWORD] ‘password‘ | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘ ] ] ssl_option: SSL | X509 | CIPHER ‘cipher‘ | ISSUER ‘issuer‘ | SUBJECT ‘subject‘ with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count 示例： MariaDB [mysql]> grant all privileges on hellodb.* to ‘test‘@‘%‘; MariaDB [mysql]> grant select,update on hellodb.students to ‘test‘@‘%‘; MariaDB [mysql]> grant select(Name,Age,ClassID) on hellodb.students to ‘test‘@‘%‘; 也可以对某些基本表创建视图之后，再对视图进行用户权限授权： MariaDB [hellodb]> create view stu_base as select Name,Age,ClassID from students; MariaDB [hellodb]> grant all on hellodb.stu_base to ‘test‘@‘%‘; 取消授权/收回授权: REVOKE语句： REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... 示例： MariaDB [mysql]> revoke delete on hellodb.* from ‘test‘@‘%‘; MariaDB [mysql]> revoke all on hellodb.students from ‘test‘@‘%‘; MariaDB [mysql]> revoke select(Age,ClassID) on hellodb.students from ‘test‘@‘%‘; 注意：在取消已经做出的授权时，REVOKE语句所指定的priv_level部分应该和授权时GRANT语句所指定的priv_level保持绝对一致；否则判定此次取消授权的操作失败； 示例：前提是testdb数据库中包含有tb1和tb2两张表； MariaDB [testdb]> grant all on testdb.* to ‘test‘@‘%‘; MariaDB [testdb]> revoke all on testdb.tb2 from ‘test‘@‘%‘; ERROR 1147 (42000): There is no such grant defined for user ‘test‘ on host ‘%‘ on table ‘tb2‘ 正确的取回授权的方式： MariaDB [testdb]> revoke all on testdb.* from ‘test‘@‘%‘; MariaDB [testdb]> grant all on testdb.tb1 to ‘test‘@‘%‘; 此时，‘test‘@‘%‘用户就只有对testdb数据库中tb2表有所有操作权限； 查看用户的授权： SHOW GRANTS语句： SHOW GRANTS [FOR user]

MySQL的存储引擎：
存储引擎也称为表类型；表级别的概念；
原则上来说，每张表都可以独立地使用某一个存储引擎；
出于稳定性考虑，在同一个数据库中的多张表最好选择同一种存储引擎；

对于MySQL或MariaDB来说，支持插件式存储引擎： 默认情况下，如果使用RPM包安装的MySQL或MariaDB，插件文件所在的目录为： /usr/lib64/mysql/plugin 安装一个插件： INSTALL PLUGIN plugin_name SONAME ‘shared_library_name‘ plugin_name的相应内容，可以参考官方站点： http://dev.mysql.com/doc/refman/5.5/en/plugin-data-structures.html 示例： MariaDB [mysql]> install plugin sphinx soname ‘ha_sphinx.so‘; 注意：使用此方法安装插件(存储引擎)之后，会立即自动激活； 卸载一个插件： UNINSTALL PLUGIN plugin_name 示例： MariaDB [mysql]> uninstall plugin sphinx; 注意：每张表，只能使用一个存储引擎；如果在创建表时没有给出指定的存储引擎，MySQL会将新建表的存储引擎设置为默认的存储引擎； 使用SHOW ENGINES命令查看时，support字段的值为DEFAULT的即为当前MySQL的默认存储引擎；当前版本的MariaDB中，默认存储引擎为"InnoDB"； 在创建表时，可以用的一个选项： CREATE TABLE tbl_name (...) ENGINE [=] engine_name; 在表创建之后，插入数据之前，可以修改存储引擎的类型： ALTER TABLE tbl_name ENGINE [=] engine_name 注意：不同的存储引擎对于数据的管理方式会不同，有时可能会有很大区别，因此，对于空表来说，可以任意修改其存储引擎；对于已经存放了数据的表来说，强烈不建议修改表的存储引擎；当前MySQL系统上默认可以使用的存储引擎： MyISAM： 最经典的MySQL的存储引擎；对于MyISAM而言，有一个缺陷——如果MySQL运行时因意外崩溃，再重启时需要对表进行修复，而MyISAM存储引擎无法保证表的安全修复； Aria： 具有崩溃后安全修复表特性的增强版的MyISAM存储引擎； InnoDB： CSV： 基于文本文件存储数据的存储引擎；跨数据库实现数据彼此交换时，比较有用；但CSV中存放的浮点类型的数值数据一定会损失数据精度； MRG_MYISAM： 用于实现将多个MyISAM表在逻辑层上连接在一起，用户就像使用一张表一样去使用多张表； BLACKHOLE： 黑洞存储引擎，在级联复制时比较有用；一般用于记录binlog做复制的中继； MEMORY： 内存级的存储引擎；无法实现数据持久功能，多用于作为临时表(内存表)的存储引擎；MEMORY是唯一的支持用户显式定义hash索引的存储引擎，因此其查询速度是非常快的； PERFORMANCE_SCHEMA 展示MySQL运行过程中的状态参数和统计数据；这些统计数据本身是MySQL内部的专有数据结构，但是有时用户需要查询这些信息，因此PERFORMANCE_SCHEMA就将此类数据虚化成一个MySQL认为可以使用的关系型接口(表)； ARCHIVE： 归档存储引擎，通常用来做数据仓库；这种类型的存储引擎一般仅支持select和insert语句，且本身不支持索引。适合于存放大量的独立的作为历史记录的数据。ARCHIVE存储引擎拥有高效的插入速度，查询速度相对较慢，用于日志记录和聚合函数分析较合适； FEDERATED：将不同MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合于分布式应用；MyISAM存储引擎： 优点： 支持全文索引(FULLTEXT INDEX)，可以实现类似于搜索引擎的功能；实施数据的高效检索 支持R TREE索引(空间函数的支持(GIS)) 支持数据的压缩存储；一旦数据被压缩，使得数据的占用空间更小，但是只能检索； 支持复杂的行格式定义： ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 缺点： 不支持事务； 锁粒度粗糙，仅支持表级锁； 崩溃后无法保证表能够进行安全修复；(Aira) MyISAM存储引擎的适用场景： 1.只读或读多写少的场景 2.较小的表，以保证崩溃后修复的时间较短，数据丢失较少； MyISAM存储引擎管理数据表文件的方式： 每个由MyISAM存储引擎管理的表，都包含三个文件，直接存储于数据库目录中； tbl_name.frm：format，表格式定义的内容； tbl_name.MYD：MyISAM Data，表中的数据内容； tbl_name.MYI：MyISAM Index，表中可用的索引； 总结： 性能： 表级锁，并发访问性能相对较差，尤其是写锁，阻塞所有的读操作，串行访问； 支持非聚集索引，全文索引及空间索引，检索数据时的速度和效率相对较高； 表压缩，只能读，无法接收写操作请求； 延迟的索引更新，比较合理的利用磁盘IO的性能； 数据安全： 支持手动或自动修复，但可能会导致数据丢失；InnoDB： Sleepycat Berkeley DB； InnoBase，研发了InnoDB存储引擎，参考了Oracle的主流存储引擎； Percona： InnoDB - Percona-XtraDB, Supports transactions, row-level locking, and foreign keys ACID： AUTOMICITY：原子性； CONSISTENCY：一致性； ISLATION：隔离性； DURABILITY：持久性； 特点： 1.InnoDB存储引擎将数据存储于"表空间"中；(Table Space) 表空间是一个自组织的自管理的文件系统； 默认的表空间是所有的表在同一个表空间中；即所有的表在同一个文件中； innodb_file_per_table=OFF 意味着所有的InnoDB存储引擎所管理的表，包括数据，表格式及索引都在同一个表空间中； 文件名：/$DATA_DIR/ibdata1 innodb_file_per_table=ON 意味着每张表使用单独的表空间； 每张表都使用两个文件表示表空间： tbl_name.ibd tbl_name.frm 注意： 如果在设置innodb_file_per_table=ON服务器参数之前已经创建了利用InnoDB存储引擎管理的表，则其表空间不会因为设置该服务器参数而变化；只有在定义了该参数之后新建的表才能以单独的表空间进行数据存储； 应该尽早设置该服务器参数；在安装服务结束之后第一次启动服务之前配置该服务器参数为最佳选择； 2.支持事务： 事务型存储引擎，适合对事务要求较高的场景的应用； 但是对于大量处理的短期事务支持的更好； 为了实现事务的隔离，并且尽可能提高并行处理效率，定义了四种不同的隔离级别； 3.精细锁粒度支持： 表级锁： 页级锁： 行级锁： 间隙锁：防止幻读； 4.支持聚集索引，主键索引以及辅助索引； 支持自适应的hash索引；MySQL或MariaDB还支持很多的额外的第三方开源社区级别的存储引擎： Sphinx存储引擎：全文索引的存储引擎； OQGraph：图示存储引擎； Cassandra：FaceBook研发的无中心节点的分布式NoSQL的数据库的存储引擎；HBase(ASF) Connect Squence ToKuDB： 1.将数据处理性能提升20-80倍； 2.支持压缩，支持压缩时写操作； 3.数据量扩展到数个TB； 4.完全避免索引碎片； 5.支持事务； 6.支持亿级行海量数据存储； ...