所有数据库的主要目的是存储和查询数据,具体体现为对数据的增、删、改、查。不同的数据库其实区别在于存储的数据结构不同或者说对数据这4个操作的实现方式不同。
数据模型
数据库按照数据结构来组织、存储和管理数据,实际上,数据库一共有三种模型:
- 层次模型
- 网状模型
- 关系模型
层次模型就是以“上下级”的层次关系来组织数据的一种方式,层次模型的数据结构看起来就像一颗树:
┌─────┐
│ │
└─────┘
│
┌───────┴───────┐
│ │
┌─────┐ ┌─────┐
│ │ │ │
└─────┘ └─────┘
│ │
┌───┴───┐ ┌───┴───┐
│ │ │ │
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│ │ │ │ │ │ │ │
└─────┘ └─────┘ └─────┘ └─────┘
网状模型把每个数据节点和其他很多节点都连接起来,它的数据结构看起来就像很多城市之间的路网:
┌─────┐ ┌─────┐
┌─│ │──────│ │──┐
│ └─────┘ └─────┘ │
│ │ │ │
│ └──────┬─────┘ │
│ │ │
┌─────┐ ┌─────┐ ┌─────┐
│ │─────│ │─────│ │
└─────┘ └─────┘ └─────┘
│ │ │
│ ┌─────┴─────┐ │
│ │ │ │
│ ┌─────┐ ┌─────┐ │
└──│ │─────│ │──┘
└─────┘ └─────┘
关系模型把数据看作是一个二维表格,任何数据都可以通过行号+列号来唯一确定,它的数据模型看起来就是一个Excel表:
┌─────┬─────┬─────┬─────┬─────┐
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
└─────┴─────┴─────┴─────┴─────┘
因为相比层次模型和网状模型,关系模型理解和使用起来最简单。随着时间的推移和市场竞争,最终,基于关系模型的关系数据库获得了绝对市场份额。而安装MySQL在关系型数据库中最为代表性的一种数据库。
安装
要在Windows或Mac上安装MySQL,首先从MySQL官方网站下载最新的MySQL Community Server版本:
https://dev.mysql.com/downloads/mysql/
选择对应的操作系统版本,下载安装即可。在安装过程中,MySQL会自动创建一个root用户,并提示输入root口令。
要在Linux上安装MySQL,可以使用发行版的包管理器。例如,Debian和Ubuntu用户可以简单地通过命令apt-get install mysql-server
安装最新的MySQL版本。
运行
MySQL安装后会自动在后台运行。为了验证MySQL安装是否正确,我们需要通过mysql这个命令行程序来连接MySQL服务器。
在命令提示符下输入mysql -u root -p
,然后输入口令,如果一切正确,就会连接到MySQL服务器,同时提示符变为mysql>
。
输入exit
退出MySQL命令行。注意,MySQL服务器仍在后台运行。
增
当我们需要向数据库表中插入一条新记录时,就必须使用INSERT
语句。
INSERT
语句的基本语法是:
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
例如,我们向students表插入一条新记录,先列举出需要插入的字段名称,然后在VALUES子句中依次写出对应字段的值:
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;
还可以一次性添加多条记录,只需要在VALUES子句中指定多个记录值,每个记录是由(…)包含的一组值:
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
SELECT * FROM students;
删
如果要删除数据库表中的记录,我们可以使用DELETE
语句。
DELETE
语句的基本语法是:
DELETE FROM <表名> WHERE ...;
例如,我们想删除students表中id=1的记录,就需要这么写:
DELETE FROM students WHERE id=1;
要特别小心的是,不带WHERE条件的DELETE语句会删除整个表的数据:
DELETE FROM students;
改
如果要更新数据库表中的记录,我们就必须使用UPDATE
语句。
UPDATE
语句的基本语法是:
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
例如,我们想更新students表id=1的记录的name和score这两个字段,先写出UPDATE students SET name='大牛', score=66
,然后在WHERE子句中写出需要更新的行的筛选条件id=1:
UPDATE students SET name='大牛', score=66 WHERE id=1;
在UPDATE
语句中,更新字段时可以使用表达式。例如,把所有80分以下的同学的成绩加10分:
UPDATE students SET score=score+10 WHERE score<80;
查
基本查询
要查询数据库表的数据,我们使用如下的SQL语句:
SELECT * FROM <表名>
假设表名是students
,要查询students
表的所有行,我们用如下SQL语句:
SELECT * FROM students;
条件查询
条件查询的语法:
SELECT * FROM <表名> WHERE <条件表达式>
条件表达式可以用<条件1> AND <条件2>
表达满足条件1并且满足条件2。例如,符合条件“分数在80分或以上”,并且还符合条件“男生”,把这两个条件写出来:
- 条件1:根据score列的数据判断:
score >= 80
; - 条件2:根据gender列的数据判断:
gender = 'M'
,注意gender列存储的是字符串,需要用单引号括起来。
就可以写出WHERE条件:score >= 80 AND gender = 'M'
:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
第二种条件是<条件1> OR <条件2>
,表示满足条件1或者满足条件2。例如,把上述AND查询的两个条件改为OR,查询结果就是“分数在80分或以上”或者“男生”,满足任意之一的条件即选出该记录:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
第三种条件是NOT <条件>
,表示“不符合该条件”的记录。例如,写一个“不是2班的学生”这个条件,可以先写出“是2班的学生”:class_id = 2
,再加上NOT:NOT class_id = 2
:
SELECT * FROM students WHERE NOT class_id = 2;
如果不加括号,条件运算按照NOT
、AND
、OR
的优先级进行,即NOT
优先级最高,其次是AND
,最后是OR
。加上括号可以改变优先级。
常用的条件表达式
条件 | 表达式举例1 | 表达式举例2 | 说明 |
---|---|---|---|
使用=判断相等 | score = 80 | name = ‘abc’ | 字符串需要用单引号括起来 |
使用>判断大于 | score > 80 | name > ‘abc’ | 字符串比较根据ASCII码,中文字符比较根据数据库设置 |
使用>=判断大于或相等 | score >= 80 | name >= ‘abc’ | |
使用<判断小于 | score < 80 | name <= ‘abc’ | |
使用<=判断小于或相等 | score <= 80 | name <= ‘abc’ | |
使用<>判断不相等 | score <> 80 | name <> ‘abc’ | |
使用LIKE判断相似 | name LIKE ‘ab%’ | name LIKE ‘%bc%’ | %表示任意字符,例如’ab%’将匹配’ab’,’abc’,’abcd’ |
使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的,即结果集的所有列与原表的所有列都一一对应。条件>表名>
投影查询
如果我们只希望返回某些列的数据,而不是所有列的数据,我们可以用SELECT 列1, 列2, 列3 FROM …,让结果集仅包含指定列。这种操作称为投影查询。
投影查询同样可以接WHERE条件,实现复杂的查询:
SELECT id, score, name FROM students WHERE gender = 'M';
排序
我们使用SELECT查询时,细心的读者可能注意到,查询结果集通常是按照id排序的,也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办?可以加上ORDER BY
子句。例如按照成绩从低到高进行排序:
SELECT id, name, gender, score FROM students ORDER BY score;
如果要反过来,按照成绩从高到底排序,我们可以加上DESC表示“倒序”:
SELECT id, name, gender, score FROM students ORDER BY score DESC;
如果score列有相同的数据,要进一步排序,可以继续添加列名。例如,使用ORDER BY score DESC
, gender表示先按score列倒序,如果有相同分数的,再按gender列排序:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
分页
使用SELECT查询时,如果结果集数据量很大,比如几万行数据,放在一个页面显示的话数据量太大,不如分页显示,每次显示100条。
要实现分页功能,实际上就是从结果集中显示第1~100条记录作为第1页,显示第101~200条记录作为第2页,以此类推。
因此,分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>
子句实现。
例如:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
聚合查询
SQL提供了如下聚合函数: | 函数 | 说明 | | ———— |:——: | | SUM | 计算某一列的合计值,该列必须为数值类型 | | AVG | 计算某一列的平均值,该列必须为数值类型 | | MAX | 计算某一列的最大值 | | MIN | 计算某一列的最小值 |
注意,MAX()和MIN()函数并不限于数值类型。如果是字符类型,MAX()和MIN()会返回排序最后和排序最前的字符。
要统计男生的平均成绩,我们用下面的聚合查询:
SELECT AVG(score) average FROM students WHERE gender = 'M';
分组
如果我们要统计一班的学生数量,我们知道,可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;
。如果要继续统计二班、三班的学生数量,难道必须不断修改WHERE条件来执行SELECT语句吗?
对于聚合查询,SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询:
SELECT COUNT(*) num FROM students GROUP BY class_id;
多表查询
TODO
连表查询
TODO