本文主要讲述数据库的创建、删除、修改，表的创建、插入、删除、修改，MariaDB的查询、查询缓存、索引和视图的相关知识
一、数据库
1.创建数据库
|
1
2
3
4
5
|
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name 默认的字符集
| [DEFAULT] COLLATE [=] collation_name 默认的排序方式
|
2.删除数据库
|
1
|
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
|
3.修改数据库的字符集和排序方式以及数据字典
|
1
2
3
4
5
6
7
8
9
|
修改数据库的字符集和排序方式
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
修改数据库的数据字典
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
|
二、数据表
1、创建数据包
1）、第一种方式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 存储引擎
AUTO_INCREMENT [=] VALUE 指定AUTO_INCREMENT的起始值
[DEFAULT] CHARACTER SET [=] charset_name 指定字符集
CHECKSUM [=] {
0
|
1
}是否使用校验值，建议不使用
[DEFAULT] COLLATE [=] collation_name 指定排序规则
COMMENT [=]
'string'
表的注释信息
DELAY_KEY_WRITE [=] {
0
|
1
}是否启用间延迟写入
ROW_FORMAT [=] {DEFAULT（默认）|DYNAMIC（动态）|FIXED（静态）|COMPRESSED（压缩）|REDUNDANT（冗余）|COMPACT（紧致）} 定义表的格式
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 定义表空间
|
2）、第二种方式（复制表数据）
|
1
|
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)] [table_options] select_statement
|
3）、第三种方式（复制表结构）
|
1
|
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{ LIKE old_tbl_name | (LIKE old_tbl_name) }
|
2.查看表的信息
|
1
|
show table status like ‘table_name’
|
3.删除表
|
1
|
DROP [TEMPORARY] TABLE [IF EXISTS]tbl_name [, tbl_name] ...[RESTRICT | CASCADE]
|
4.修改表：
|
1
|
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name[alter_specification [, alter_specification] ...]
|
1）、修改字段定义
插入新的字段。并设置字段的位置
|
1
|
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
|
删除字段
|
1
|
DROP [COLUMN] col_name
|
2）、修改字段
修改字段名称，并设置字段的位置
|
1
|
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
|
修改字段类型及属性及位置
|
1
|
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
|
3）、表改名
|
1
|
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
|
4）、修改存储引擎
|
1
|
engine =
|
5）、指定排序标准的字段
|
1
|
ORDER BY col_name [, col_name] ...
|
5.插入数据
|
1
2
3
4
5
6
|
第一种：
INSERT INTO tbl_name [(col1,col2...)]{VALUES | VALUE}(
var
1,
var
2....)[,(val21,
var
22,....),....]
第二种
INSERT INTO tbl_name SET col1=val1,col2=val2
第三种（将一个表中的数据插入到另外一张表中）
INSERT INTO tbl_name SELECT ...
|
6.replace 替换表中数据，用法同insert，除了在新插入的数据与表中的主键或唯一索引定义的数据相同会替换老的行
7.更新数据，启动时--safe-updates:启动时应该带此选项
|
1
|
UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...[WHERE where_condition][ORDER BY ...] [LIMIT row_count]
|
update通常情况下，必须使用where字句，或者使用limit限制要修改的行数
8.删除数据
|
1
|
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
|
truncate tb_name 重置表的所有内容
三、表的查询操作
1.简单查询
单表查询：简单查询
多表查询：连续查询
联合查询
选择和投影：
投影：挑选符合的字段
选择：挑选符合的行
投影：select 字段1，字段2，....from tb_name
select * from tb_name
选择：select 字段1，字段2，....from tb_name where 子句
布尔条件表达式
= 等值比较
<=> 跟空值比较不会产生额外信息
<> 不等值比较
< 小于
<= 小于等于
> 大于
>= 大于等于
IS NULL：是否为空
IS NOT NULL：是否不空
LIKE：支持的通配符%（任意长度的任意字符）_(任意长度的单个字符)
RLIKE,REGEXP：支持使用郑州表达式作为条件
IN：判断某行的某一个字段的值是否在给定的列表中
BETWEEN...AND...：判断指定的值是否位于指定的范围之间
组合条件测试：
NOT |
AND &&
OR ||
排序
order by ‘排序字段’
ASC：默认为升序
DESC:降序
内置的聚合函数
SUM()：和
AVG()：平均值
MAX()：最大值
MIN()：最小值
COUNT(): 统计个数
分组
group by
对分组的条件进行过滤
having
只返回指定的行
limit（[m]，n）
m表示为偏移的行数
n表示为显示的行数
select语句的执行流程
from clause --> where clause -->group by -->having clause -->order by -->select -->limit
select语句
distinct 重复的只显示一次
sql_cache 缓存查询结果
sql_no_cache 不缓存查询结果
2.多表查询
联结查询：事先将两张或者多张表join，根据join的结果进行查询
交叉联结：cross join
自然联结：
等值联结：把相同的字段进行等值联结
外联结：
左外联结：只保留出现在左外连接元算之前（左边）的关系中的元组（以左表为准）
left_tb LEFT JOIN right_tb ON 条件
右外联结：只保留出现在右外连接元算之前（右边）的关系中的元组（以右表为准）
left_tb RIGHT JOIN right_tb ON 条件
全外联结
自联结
3.别名：AS
表别名
字段别名
4.子查询：在查询中嵌套的查询
用于where中的子查询
1.用于比较表达式中的子查询
子查询的返回值只能有一个
2.用于exists中的子查询
用于判断存在与否
3.用于IN中的子查询
用于判断是否存在与指定的列表中
用于from中的子查询
select alias.col,....from(select cluser) alias where condition
MariaDB不擅长于子查询，应该避免使用子查询
5.MariaDB的联合查询：
把两个或多个查询语句的结果合并成一个结果进行输出
select clauase union select clause union....
6.索引
查看索引
show indexs from tb_name
创建索引
alter table tb_name add index(字段)
7.视图：为存储下来的select的语句，是一个虚表
创建：
create view 视图名 as select语句
删除：
drop view 视图名
四、查询缓存
用于保存MariaDB查询语句返回的完整结果，被命中时，MariaDB会立即返回结果，省去解析、优化和执行等操作
1.如何检查缓存
MariaDB保存结果与缓存中，会把select语句本身作为hash计算，把计算的结果作为key，查询结果作为value
2.什么样的语句不会被缓存
查询语句中有一些不确定的数据时，不会缓存；如now（），current_time()
一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、MariaDB库中系统表、或者任何包含权限的表，一般都不会缓存
3.查询会带来额外的开销，
1.每个查询都会先检查是否命中
2.查询结果要先缓存
4.查询缓存的系统变量
|
1
2
3
4
5
6
7
8
9
10
11
|
MariaDB [(none)]> show global
var
iables like
'query_cache%'
;
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit |
1048576
|
| query_cache_min_res_unit |
4096
|
| query_cache_size |
16777216
|
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
|
query_cache_type：查询缓存类型，是否开启缓存功能，有三种选择{ON|OFF|DEMAND（意味着select语句明确使用sql_cache选项才会缓存）}
query_cache_size：缓存的总空间大小，单位为字节，大小必须为1024的整数倍。MariaDB启动时，会一次分配并立即初始化这里指大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化
query_cache_min_res_unit：存储缓存的最小内存块
query_cache_limit：单个缓存对象的最大值，超过时则不予缓存，手动使用SQL_NO_CACHE可以人为的避免尝试婚车返回超过此参数限定值的语句
(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值
query_cache_wlock_invalidate：如果某个表被其他永辉连接锁定了，是否仍然从缓存中返回结果，OFF表示返回
5.如何判断命中率：
分为次数命中率和字节命中率
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
MariaDB [(none)]> show global status like
'qcache%'
;
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks |
1
|
| Qcache_free_memory |
16759688
|
| Qcache_hits |
0
|
| Qcache_inserts |
0
|
| Qcache_lowmem_prunes |
0
|
| Qcache_not_cached |
1
|
| Qcache_queries_in_cache |
0
|
| Qcache_total_blocks |
1
|
+-------------------------+----------+
|
Qcache_free_blocks：空闲块数
Qcache_free_memory：空间空间数，单位为字节
Qcache_hits：命中次数
Qcache_inserts：向缓存空间中插入的缓存次数
Qcache_lowmem_prunes：内存大小，修剪内存的次数
Qcache_not_cached：没被缓存的个数
Qcache_queries_in_cache：缓存中缓存的查询个数
Qcache_total_block：总块数
内存碎片整理：flush query_cache
清理缓存：reset query_cache
6.计算缓存命中率
|
1
2
3
4
5
6
7
|
MariaDB [(none)]> show global status where
var
iable_name=
'qcache_hits'
or
var
iable_name=
'com_select'
;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select |
1
|
| Qcache_hits |
0
|
+---------------+-------+
|
计算方法：qcache_hits/(qcache_hits+com_select)
也可以参考另外一个指标，命中和写入的比率，即qcache_hits/qcache_inserts的值，此比值如果大于3：1，则表明缓存也是有效的，能到达10：1，为比较理想的情况
7.缓存优化思路
1.批量写入，而非多次单个写入
2.缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死
3.必要时，使用sql_cache和sql_no_cache手动控制缓存
4.对写密集的应用场景来说，禁用缓存反提高性能
