数据库

数据库管理系统类型

关系型数据库~

使用关系模型来存储数据，使用二维表来关联数据。
常见有：MySQL、ORACLE、PostgreSQL

非关系型~

对前者的补充和发展。
常见：Redis、mongoDB、neo4j、cassandra

SQL

用来操作关系型数据库的语言，用于操作关系型数据库中表中的数据。
一般不区分大小写，但是对于关键字，一般全大写。
分为有以下几种：

DDL 数据定义语言

定义数据库对象，如数据库、表、列等
相关关键字：CREATE、DROP、ALTER、TRUNCATE等。

DML 数据操作语言

用于对数据库中的记录进行新增、删除或修改操作
相关关键字有：INSERT、UPDATE、DELETE、CALL

DQL 数据查询语言

查询数据库中记录。
SELECT、WHERE等

DCL 数据控制语言

定义数据库访问权限和安全级别。
GRANT、REVOKE等。

常用语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
SELECT * FROM player WHERE level > 1 AND level < 5;
SELECT * FROM player WHERE level > 1 OR level < 5;
SELECT * FROM player WHERE level NOT IN (1， 3， 5)；
SELECT * FROM player WHERE level BETWEEN 1 AND 10；
-- LIKE是模糊查询，其中%表示任意个字符，而_代表任意一个字符
SELECT * FROM player WHERE name LIKE '王%';
SELECT * FROM player WHERE name LIKE '王_';
-- REGEXP表示正则表达式，语法规则与此前接触的一致。
SELECT * FROM player WHERE name REGEXP '...';
-- 由于在sql中null与任何值都不等，包括null，因此不能使用= null来判断其是否与null相等，应该使用is null来判断,但是对于空字符串，只能使用=来判断
SELECT * FROM player WHERE name is null;
SELECT * FROM player WHERE name = '';   
-- order by 用于排序,默认升序排列，后加上DESC使其降序排列
SELECT * FROM player WHERE ORDER BY level DESC;
-- 直接跟数字表示根据第几列的数据来排序
SELECT * FROM player WHERE ORDER BY 5 DESC;
-- 还可以在此后再多加一列来表示在第一种排序方式的基础上，以第二种方式作为后续的排列方式，例：(其中ASC表示按顺序即先排level后exp，不填写默认ASC)
SELECT * FROM player WHERE ORDER BY level DESC, exp ASC;

-- 还有一些常用的聚合函数:COUNT()、MAX() 、MIN() 、SUM() 、AVG()
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;

-- group by 来分组查询
SELECT sex, count(*) FROM player group by sex; 
-- SELECT 后的level代表查询level列，count(level)表示不同level中的个数，group by后的level表示按level来分组
SELECT level, count(level) FROM player group by level;
-- HAVING：筛选分组后数据
SELECT level, count(level) FROM player group by level having count(level) > 4;
SELECT level, count(level) FROM player group by level order by count(level) DEC;
-- LIMIT可以限制显示的数量
-- 例如下语句只会返回前三名
LIMIT 3;
-- DISTINCT可用来去重
SELECT DISTINCT sex from player;

-- UNION来合并查询结果集(并集)
-- 例如下语句将会把上下语句的结果合并起来，UNION默认去重，若不想去重，可使用UNION ALL
SELECT * FROM player WHERE level BETWEEN 1 AND 3;
UNION;
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
-- INTERSECT 用来查找两个结果的交集
-- 用法与UNION类似
-- EXCEPT 用于查找两个结果的差集

-- 子查询的使用
SELECT AVG(level) from player;
select * from player where level > (SELECT AVG(level) from player);
select level, ROUND(SELECT AVG(level) from player) as average, level - ROUND(SELECT AVG(level) from player) as diff from player;  
-- 使用子查询创建新表
create table new_player select * from player where level < 5;
insert into new_player select * from player where level between 6 and 10;
-- EXISTS判断一个查询是否有结果,只返回0或1
select exists (select * from player where level > 10);
-- 表连接
-- 内连接，返回两个表中都有的数据
INNER JOIN
-- 左连接，返回左表所有数据和右表中匹配数据,右表中没有的数据用NULL填充
LEFT JOIN
-- 右连接，同理
RIGHT JOIN

-- 例如:下表示将玩家同对应装备通过对应的玩家id关联起来
select * from player
inner join equip
on player.id = equip.player_id

-- 索引的创建,INDEX前可加上索引类型，UNIQUE表示唯一索引，FULLTEXT表示全文索引，SPATIAL表示空间索引，index_name为索引名称
-- UNIQUE强制列值唯一性，适用所有标量类型(INT、VARCHAR等)、允许出现NULL，常用于查询身份证号、用户名等唯一值
-- FULLTEXT是进行文本内容的语义搜索，适用于CHAR、VARCHAR、TEXT，允许空值和重复值，适用于文章内容搜索、商品描述搜索
-- SPATIAL是地理空间数据检索，适用于GEOMETRY、POINT、POLYGON等，允许重复值，适用于地图应用、位置服务
-- on tbl_name 表示要在tbl_name这张表上创建索引，(index_col_name, ...)是用一个或多个名称括起来的字段名，是我们对哪些字段创建索引，一般对于表的主键字段或常用于查询的字段创建索引，即where语句后的条件字段，来提高查询的效率。
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
on tbl_name (index_col_name, ...);

-- 查看索引
show index from tbl_name;  

-- 删除索引
drop index index_name on tbl_name; 

-- 可在修改表结构时创建索引
alter table tbl_name add index on index_name (name);  

-- 视图作为一种虚拟存在的表，本身不包含数据，而作为一个查询语句保存在数据字典中，查询视图时会根据查询语句的定义动态生成数据
-- 例如：下表示查询player表中玩家level在前三名的数据
-- 视图的使用方式与表类似，也可使用select等语句来操作
create view view_name 
as 
select * from player order by level desc limit 3;
-- 视图中的数据是动态的，会随着原表中数据的改变而改变
;