Mariadb
结构化数据–>关系型数据库 范式：Entry（每一行来描述一个整体）
半结构化数据–>YAML,XML,JSON 非结构化数据–>日志文件 NoSQL
关系型数据库：事务能力 ACID测试（原子性，一致性，隔离性，持久性）
MariaDB or MySQL: 层次模型–>网状模型–>（Codd）关系模型
DBMS（数据管理系统） --> RDBMS（关系型数据管理系统） 表：row，column； 关系运算： 选择 投影 数据库：表、索引、视图（虚表）、SQL、存储过程、存储函数、触发器、事件调度器 DDL(数据定义语言)：CREATE,ALTER,DROP DML(管理表中的数据):INSERT/UPDATE/DELETE/SELECT 约束： 主键约束：唯一、非空；一张表只能有一个； 唯一键约束：唯一，可以存在多个； 外键约束：参考性约束； 检查性约束：check； 三层模型： 物理层-->系统管理员(SA) 逻辑层-->DBA 视图层-->程序员(Coder) 实现： Oracle，DB2，Sybase，Infomix，SQL Server； Mysql，Mariadb，PostgreSQL,SQLite；
Mysql： 5.1–>5.5–>5.6–>5.7–>8.0
Mariadb:5.5.x-->10.x
特性：
插件式存储引擎
单进程多线程
安装Mysql：
OS Vendor：rpm
MySQL：
source code：cmake
binary package：
i686, x86_64;
glibc VERSION
prepackage：rpm, deb
os, arch,
服务端程序：
mysqld, mysqld_safe, mysqld_multi
客户端程序：
mysql, mysqldump, mysqlbinlog, mysqldmin, ...
非客户端类程序：
myisamchk, myisampack, ...
配置文件：
读取多处的多个配置文件，而且会以指定的次序的进行；
# my_print_defaults
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
不同的配置文件中出现同一参数且拥有不同值时，后读取将为最终生效值；
修改默认读取的配置文件（mysqld_safe命令）：
--defaults-file=file_name
于读取的默认配置文件之外再加载一个文件：
--defaults-extra-file=path
配置文件格式：ini风格的配置文件，能够为mysql的各种应用程序提供配置信息：
[mysqld]
[mysqld_safe]
[mysqld_multi]
[server]
[mysql]
[mysqldump]
[client]
...
PARAMETER = VALUE
PARAMETER：
innodb_file_per_table
innodb-file-per-table
程序文件：
服务端程序：mysqld_safe, mysqld_multi
客户端程序：mysql, mysqldump, mysqladmin
工具程序：myisampack, ...
mysql --> mysql protocol --> mysqld
mysql：交互式CLI工具；
mysql [options] db_name
常用选项：
--host=host_name, -h host_name：服务端地址；
--user=user_name, -u user_name：用户名；
--password[=password], -p[password]：用户密码；
--port=port_num, -P port_num：服务端端口；
--protocol={TCP|SOCKET|PIPE|MEMORY}：
本地通信：基于本地回环地址进行请求，将基于本地通信协议；
Linux：SOCKET
Windows：PIPE，MEMORY
非本地通信：使用非本地回环地址进行的请求； TCP协议；
--socket=path, -S path
--database=db_name, -D db_name：
--compress, -C：数据压缩传输
--execute=statement, -e statement：非交互模式执行SQL语句；
--vertical, -E：查询结果纵向显示；
命令：
客户端命令：于客户端执行；
服务端命令：SQL语句，需要一次性完整地发往服务端；语句必须有结束符；
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
mysql命令的使用帮助：
# man mysql
# mysql --help --verbose
sql脚本运行：
mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT
mysqld服务器程序：工作特性的定义方式
命令行选项
配置文件参数
服务器参数/变量：设定MySQL的运行特性；
mysql> SHOW GLOBA|[SESSION] VARIABLES [LIKE clause];
like后面字符串要用引号引起来
SHOW GLOBAL VARIABLES; 查看全局的服务器变量，定义服务器的工作特性的 只想查看Threads开头的状态变量
状态（统计）参数/变量：保存MySQL运行中的统计数据或状态数据； mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause]; 显示单个变量设定值的方法： mysql> SELECT @@[global.|session.]system_var_name %：匹配任意长度的任意字符； _：匹配任意单个字符； 变量/参数级别： 全局：为所有会话设定默认； 会话：跟单个会话相关；会话建立会从全局继承； 服务器变量的调整方式： 运行时修改： global：仅对修改后新建立的会话有效； session：仅对当前会话有效，且立即生效； 启动前通过配置文件修改： 重启后生效； 运行时修改变量值操作方法： mysql> HELP SET SET [GLOBAL | SESSION] system_var_name = expr SET [@@global. | @@session. | @@]system_var_name = expr
把autocommit的值改为0
方法一
方法二
改全局级别不影响当前会话，但是对新建立的回话全局级别时生效的
安装完成后的安全初始化：
mysql_secure_installation
运行前常修改的参数：
innodb_file_per_table=ON
skip_name_resolve=ON
...
SQL：ANSI SQL
SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ...
MySQL的数据类型：
字符型
数值型
日期时间型
内建类型
字符型：
CHAR(#)， BINARY：定长型；CHAR不区分字符大小写，而BINARY区分；
VARCHAR(#)， VARBINARY：变长型
TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
数值型：
浮点型：近似
FLOAT
DOUBLE
REAL
BIT
整型：精确
INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
DECIMAL
日期时间型：
日期：DATE
时间：TIME
日期j时间：DATETIME
时间戳：TIMESTAMP
年份：YEAR(2), YEAR(4)
内建：
ENUM：枚举
ENUM('Sun','Mon','Tue','Wed')
SET：集合
类型修饰符：
字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED
日期时间型：NOT NULL， NULL， DEFAULT
SQL MODE：定义mysqld对约束等违反时的响应行为等设定；
常用的MODE：
TRADITIONAL
STRICT_TRANS_TABLES
STRICT_ALL_TABLES
不应该默认使用空模式
修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
CREATE TABLE tbl1 (id tinyint unsigned,name CHAR(5)) 创建一个表，定义一个字段为id，另一个字段是name，只能有五个字符
MariaDB [testdb]> CREATE TABLE tbl3 (id tinyint unsigned,name char(6));
MariaDB [testdb]> INSERT INTO tbl3 (id) VALUES (16),(256);
超过的字符被修剪了，违反了规则后，正常插入没问题，但会有错误。插入时会发生警告。一旦违反约束规则的话，应该拒绝数据插入。
这时候定义一种功能
默认mysql定义在空模式下，TRADITIONAL,严格遵守规则，不然无法定义
这时候就会提示插入数据错误；
SQL：DDL，DML
DDL：
mysql> HELP Data Definition
CREATE, ALTER, DROP
DATABASE, TABLE
INDEX, VIEW, USER
FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER, SERVER
DML:
mysql> HELP Data Manipulation
INSERT/REPLACE, DELETE, SELECT, UPDATE
数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
表：
CREATE
(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE TABLE [IF NOT EXISTS] tble_name (col_name data_typ|INDEX|CONSTRAINT);
table_options：
ENGINE [=] engine_name
查看支持的所有存储引擎：
mysql> SHOW ENGINES;
查看指定表的存储引擎：
mysql> SHOW TABLE STATUS LIKE clause;
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
(2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
直接创建表，并将查询语句的结果插入到新创建的表中；
(3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
复制某存在的表的结构来创建新的空表；
DROP:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name];
ALTER：
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
可修改内容：
(1) table_options
(2) 添加定义：ADD
字段、字段集合、索引、约束
(3) 修改字段：
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
(4) 删除操作：DROP
字段、索引、约束
表重命名：
RENAME [TO|AS] new_tbl_name
查看表结构定义：
DESC tbl_name；
查看表定义：
SHOW CREATE TABLE tbl_name
查看表属性信息：
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
索引：
创建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
删除：
DROP INDEX index_name ON tbl_name
索引类型：
聚集索引、非聚集索引：索引是否与数据存在一起；
主键索引、辅助索引
稠密索引、稀疏索引：是否索引了每一个数据项；
BTREE（B+）、HASH、R Tree、FULLTEXT
BTREE：左前缀；
EXPLAIN：分析查询语句的执行路径；
视图：VIEW
虚表：存储下来的SELECT语句；
创建：
CREATE VIEW view_name [(column_list)] AS select_statement
修改：
ALTER VIEW view_name [(column_list)] AS select_statement
删除：
DROP VIEW [IF EXISTS] view_name [, view_name] ...
DML：
INSERT/REPLACE，DELETE，UPDATE，SELECT
INSERT：
单行插入
批量插入
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
DELETE：
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
注意：一定要有限制条件，否则将清空整个表；
限制条件：
[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]
UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制条件，否则将修改整个表中指定字段的数据；
限制条件：
[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]
注意：sql_safe_updates变量可阻止不带条件更新操作；
SELECT：
Query Cache：缓存查询的执行结果；
key：查询语句的hash值；
value：查询语句的执行结果；
SQL语句的编写方式：
SELECT name FROM tbl2;
select name from tbl2;
查询执行路径：
请求-->查询缓存
请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应
SELECT语句的执行流程：
FROM --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit
先找到执行的表，用where做条件过滤，就能拿到子集，对这个子集要拿到分组。做聚合计算（最大值，平均值）。把聚合都的结果指定过滤条件用having，having用来描述group by的结果如何被过滤的。order by排序，select执行，limit显示。
挑选行
既能挑选字段，又能挑选行；
添加一个列，性别
往新加的列上赋值
显示F性别的平均值，和M性别的平均值
聚合后的结果指明过滤条件；
单表查询：
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}]
用法：
SELECT col1, col2, ... FROM tble_name；
SELECT col1, col2, ... FROM tble_name WHERE clause；
SELECT col1, col2, ... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]；
DISTINCT：数据去重；
SQL_CACHE：显式指定缓存查询语句的结果；
SQL_NO_CACHE：显式指定不缓存查询语句的结果；
query_cache_type服务器变量有三个值：
ON：启用；
SQL_NO_CACHE：不缓存；默认符合缓存条件都缓存；
OFF：关闭；
DEMAND：按需缓存；
SQL_CACHE：缓存；默认不缓存；
字段可以使用别名 ：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现“选择”功能；
过滤条件：布尔型表达式；
[WHERE where_condition]
算术操作符：+, -, *, /, %
比较操作符：=, <>, !=, <=>, >, >=, <, <=
IS NULL， IS NOT NULL
区间：BETWEEN min AND max
IN：列表；
LIKE：模糊比较，%和_；
RLIKE或REGEXP
逻辑操作符：
AND， OR， NOT， XOR
GROUP BY：根据指定的字段把查询的结果进行“分组”以用于“聚合”运算；
avg(), max(), min(), sum(), count()
HAVING：对分组聚合后的结果进行条件过滤；
ORDER BY：根据指定的字段把查询的结果进行排序；
升序：ASC
降序：DESC
LIMIT：对输出结果进行数量限制
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
练习：导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；
(2) 以ClassID为分组依据，显示每组的平均年龄；
(3) 显示第2题中平均年龄大于30的分组及平均年龄；
(4) 显示以L开头的名字的同学的信息；
(5) 显示TeacherID非空的同学的相关信息；
(6) 以年龄排序后，显示年龄最大的前10位同学的信息；
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；
练习：导入hellodb.sql，以下操作在students表上执行 1、以ClassID分组，显示每班的同学的人数；
2、以Gender分组，显示其年龄之和；
3、以ClassID分组，显示其平均年龄大于25的班级；
4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；
多表查询： 连接操作： 交叉连接：笛卡尔乘积； 内连接： 等值连接：让表之间的字段以等值的方式建立连接；
不等值连接： 自然连接 自连接 外连接： 左外连接： FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col 右外连接： FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col 子查询：在查询中嵌套查询； 用于WHERE子句中的子查询； (1) 用于比较表达式中的子查询：子查询仅能返回单个值； (2) 用于IN中的子查询：子查询可以返回一个列表值； (3) 用于EXISTS中的子查询： 用于FROM子句中的子查询； SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause; 联合查询：将多个查询语句的执行结果相合并； UNION SELECT clause UNION SELECT cluase； 练习：导入hellodb.sql，完成以下题目： 1、显示前5位同学的姓名、课程及成绩；
2、显示其成绩高于80的同学的名称及课程；
3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
4、显示每门课程课程名称及学习了这门课的同学的个数；
原创文章，作者：15152188070，如若转载，请注明出处：http://www.178linux.com/59378
