MMM--数据库方面

基本知识

传统关系型数据库,很难满足 海量数据存储、高并发请求、高可用、高可扩展性 等特性要求;而非关系型数据库却又无法保证事物的功能。因此,在互联网和大数据的背景下,需要将两者结合起来使用。

列存储

传统关系型数据库是 按照行 来存储数据库,称为 行式数据库。而 列式数据库 是 按照列 来存储数据。优势是存储利用率高,查询效率高,适合做聚合操作和大量的数据。如:HBase、BigTable。

k-v数据库

使用 键值(key-value)存储的数据库,其数据按照 键值对 的形式进行 组织、索引 和 存储。优势是拥有很好的读写性能。如:Redis、Cassandra、Memcached、LevelDB。

文档型数据库

文档数据库 用于将 半结构化数据 存储为 文档 的一种数据库。文档数据库通常以 JSON 或 XML 格式存储数据。优势是表结构不明确,且字段在增加,数据量较大。如:MongoDB、CouchDB。

全文搜索引擎

全文搜索引擎的出现,正是解决关系型数据库 全文搜索较弱 的问题。如:elasticsearch、Solr。

图形数据库

图形数据库 应用 图形理论 存储 实体 之间的 关系信息。最常见例子就是 社会网络中人与人之间的关系。关系型数据库 用于存储这种 关系型数据 的效果并不好,其查询 复杂、缓慢、超出预期。
图形数据库 的独特设计弥补了这个缺陷,解决 关系型 数据库 存储 和 处理复杂关系型数据 功能较弱的问题,同时还完全支持ACID属性。如:Neo4j、ArangoDB

系统类型 数据库选型
企业内部管理系统 例如运营系统,数据量少,并发量小,首选考虑 关系型数据库
互联网大流量系统 例如电商单品页,后台考虑选 关系型数据库,前台考虑选 内存型数据库
日志型系统 原始数据 考虑选 列式数据库,日志搜索 考虑选 倒排索引
搜索型系统 例如站内搜索,非通用搜索,商品搜索,后台考虑选 关系型数据库,前台考虑选 倒 排索引
事务型系统 例如库存管理,交易,记账,考虑选 关系型数据库 + 缓存数据库 + 一致性型协议
离线计算 例如大量数据分析,考虑选 列式数据库 或者 关系型数据库 都可以
实时计算 例如实时监控,可以考虑选 内存型数据库 或者 列式数据库

案例

编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。

嵌套子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+

1
2
3
4
5
6
7
8
9
10
11
### 方式1
select Score,
(select count(distinct Score) from Scores as s2 where s2.Score >= s1.Score) Rank
from Scores as s1
order by Score DESC;

### 方式2
select s1.Score, count(distinct s2.Score) Rank
from Scores as s1 join Scores as s2 on s1.Score <= s2.Score
group by s1.Id
order by s1.Score DESC;
SQL查询应该返回 员工工资表 中第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null

可直接使用IFNULL()这个方法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
返回结果应为:
+----------+
| Employee |
+----------+
| Joe |
+----------+

1
2
3
4
5
### 方法一
select IFNULL((select Distinct Salary from Employee order by Salary DESC limit 1,1),null) as SecondHighestSalary;

### 方法二
select max(Salary) as SecondHighestSalary from Employee where Salary<(select max(Salary) from Employee);
给定一个Weather表,编写一个SQL查询来查找与之前(昨天的)日期相比温度更高的所有日期的id。

运用一些内置函数辅助完成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如,根据上述给定的 Weather 表格,返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+

1
2
3
4
5
6
7
8
9
10
11
### 方法一
select w1.Id from weather w1
inner join weather w2 on w1.Temperature > w2.Temperature and DATEDIFF(w1.RecordDate, w2.RecordDate) = 1;

### 方法二
SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND TO_DAYS(w1.RecordDate)=TO_DAYS(w2.RecordDate) + 1;

### 方法三
SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature AND SUBDATE(w1.RecordDate, 1) = w2.RecordDate;
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

group by可直接对邮箱进行过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根据以上输入,你的查询应返回以下结果:

+---------+
| Email |
+---------+
| a@b.com |
+
+---------+
说明:所有电子邮箱都是小写字母。

1
Select Email From Person Group By Email Having Count(*) > 1
删除 Person 表中重复的电子邮件

建立临时表。

1
delete p1 from Person p1,Person p2 where p1.Email = p2.Email and p1.Id>p2.Id;

有一个courses 表 ,有: student (学生) 和 class (课程)。请列出所有超过或等于5名学生的课。

先分组,然后再过滤。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
应该输出:

+---------+
| class |
+---------+
| Math |
+---------+

1
SELECT class from courses group by class having count(DISTINCT student) >= 5;
找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。

取奇数:id&1和匹配 not like'%boring%',注意没有is关键字
%(匹配任意次数的任意字符),_(只匹配一个字符),*(匹配前面的子表达式零次或多次)。注意和java中的是有区别的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
例如,下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
对于上面的例子,则正确的输出是为:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+

1
select * from cinema where description not like'boring' and id&1 order by rating
给定一个 salary表,如下所示,有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。

使用if函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
例如:

| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的查询语句之后,将会得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |

1
update salary set sex = if(sex='m','f','m')
文章作者: gqsu
文章链接: http://www.ipdax.com/2018/08/04/sql-数据库方面/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 技术笔记分享
支付宝打赏
微信打赏