《数据库基础语法》1. 在SQL的世界里一切都是关系

楔子

SQL 是每个开发人员都应该掌握的，很多人可能觉得 SQL 没啥大不了的，但是说真的，SQL要是写好了，是很厉害的。下面我们来从零开始学习SQL。

基本概念

正如 Linux 中一切皆文件，Python中一切皆对象，SQL（Structured Query Language，结构化查询语言）中可以把一切都看成是关系。我们来看看一些概念：

关系型数据库：

关系型数据库（Relational database）是指基于关系模型的数据库。关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。

数据结构：

关系模型中，用于存储数据的逻辑结构称为 关系（Relation）；对于使用者而言，关系就是 二维表（Table）。

以下是一张员工信息表，知道 Excel 的人肯定会很熟悉，因为它和 Excel 的结构非常类似，由行（Row）和列（Column）组成。

关系型数据库中还有一些常见的概念：

关系，也称为表，用于表示现实世界中的实体（Entity）或者实体之间的联系（Relationship）。举例来说，一个公司的员工、部门和职位都是实体，分别对应员工信息表、部门信息表和职位信息表；销售的产品和订单都是实体，同时它们之间存在联系，对应订单明细表。
行，也称为记录（Record），代表了关系中的单个实体。上图中pk为 8 的数据行存储了id 为 "01010009350" 的相关信息。关系（表）可以看作是由行组成的集合。
列，也称为字段（Field），表示实体的某个属性。上图中的第二列包含了员工的id。表中的每个列都有一个对应的数据类型，常见的数据类型包括字符类型、数字类型、日期时间类型等。

操作集合：

有了关系结构之后，就需要定义基于关系的数据操作。

常见的数据操作包括：增加（Create）、查询（Retrieve）、更新（Update）、以及删除（Delete），它们统称为：增删改查（CRUD）

其中，使用最多、也最复杂的操作就是查询，具体来说包括：选择（Selection）、投影（Projection）、并集（Union）、交集（Intersection）、差集（Exception）以及笛卡尔积（Cartesian product）等等，我们后面会介绍如何使用 SQL 语句完成以上各种数据操作。

为了维护数据的完整性或者满足业务需求，关系模型还定义了完整性约束。

完整性约束：

关系模型中定义了三种完整性约束：实体完整性、参照完整性、以及用户定义完整性。

实体完整性是指表的主键字段不能为空。现实中的每个实体都具有唯一性，比如每个人都有唯一的身份证号；在关系数据库中，这种唯一标识每一行数据的字段称为主键（Primary Key），主键字段不能为空。每个表可以有且只能有一个主键。但是一个主键不一定只对应一个字段，也可以是多个字段共同组合成联合主键，但它仍然是一个主键
参照完整性是指外键参照的完整性。外键（Foreign Key）代表了两个表之间的关联关系，比如员工属于某个部门；因此员工表中存在部门编号字段，引用了部门表中的部门编号字段。对于外键引用，被引用的数据必须存在，员工不可能属于一个不存在的部门；删除某个部门之前，也需要对部门中的员工进行相应的处理。
用户定义完整性是指基于业务需要自定义的约束。非空约束（NOT NULL）确保了相应的字段不会出现空值，例如员工一定要有姓名；唯一约束（UNIQUE）用于确保字段中的值不会重复，每个员工的电子邮箱必须唯一；检查约束（CHECK）可以定义更多的业务规则。例如，薪水必须大于 0 ，字符必须大写等；默认值（DEFAULT）用于向字段中插入默认的数据。

另外我们说的 SQL 其实是一个工业标准，或者说它指的是操作数据库中的二维表时所使用的语言，而不同种类的关系型数据库的 SQL 也是不同的，但是它们大致都是一样的。常见的数据库一般有 MySQL、Oracle、PostgreSQL、SQL Server，我们后续介绍的 SQL，这四种数据库都支持，如果不支持，我们单独指出来。

SQL 的语法特性

SQL 是一种声明性的编程语言，语法接近于自然语言（英语）。通过几个简单的英文单词，例如 SELECT、INSERT、UPDATE、CREATE、DROP 等，来完成大部分的数据库操作。以下是一个简单的查询示例，这里只是提前感受一下 SQL，具体的语法细节我们后面会说：

SELECT emp_id, emp_name, salary
FROM employee
WHERE salary > 10000
ORDER BY emp_id;

即使没有学过 SQL 语句，但只要知道几个单词的意思，就能明白该语句的作用。它查询员工表（employee）中月薪（salary）大于 10000 的员工，返回工号、姓名以及月薪，并且按照工号进行排序。可以看出，SQL 语句非常简单直观。

以上查询中的 SELECT、FROM 等称为关键字（也称为子句），一般大写；表名、列名等内容一般小写；分号表示语句的结束。其实 SQL 语句的关键字、以及内置的函数是不区分大小写的，但是遵循一定的规则可以让代码更容易阅读。

对于 SQL 语句而言，它所操作的对象是一个集合（表），操作的结果也是一个集合（表）。例如以下查询：

SELECT emp_id, emp_name, salary
FROM employee;

其中 employee 是一个表，它是该语句查询的对象；同时，查询的结果也是一个表。所以，我们可以继续扩展该查询：

SELECT emp_id, emp_name, salary
FROM (
         SELECT emp_id, emp_name, salary
         FROM employee
     ) as t;

我们将括号中的查询结果（取名为 t）作为输入值，传递给了外面的查询，最终整个语句的结果仍然是一个表。这种嵌套在其他语句中的查询就是子查询（Subquery），后续我们会详细介绍。

总之，SQL 中的查询可以完成各种数据操作，例如过滤转换、分组汇总、排序显示等；但是它们本质上都是针对表的操作，结果也是表。

不仅仅是查询语句，SQL 中的插入、更新和删除都以集合为操作对象。我们再看一个插入数据的示例：

CREATE TABLE t(id INTEGER);

-- 适用于 MySQL、SQL Server 以及 PostgreSQL
INSERT INTO t(id)
VALUES (1), (2), (3);

我们首先使用 CREATE TABLE 语句创建了一个表，然后使用 INSERT INTO 语句插入数据。在执行插入操作之前，会在内存中创建一个包含 3 条数据的临时集合（表），然后将该集合插入目标表中。由于我们通常一次插入一条数据，以为是按照数据行进行插入；但实际上，一条数据也是一个集合，只不过它只有一个元素而已。

Oracle 不支持以上插入多行数据的语法，可以使用下面的插入语句：

-- 适用于 Oracle
INSERT INTO t(id)
SELECT 1 FROM DUAL
UNION ALL 
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

UNION ALL 是 SQL 中的并集运算，用于将两个集合组成一个更大的集合。此外，SQL 还支持交集运算（INTERSECT）、差集运算（EXCEPT）以及笛卡儿积（Cartesian product）。我们后面会慢慢介绍。

安装数据库

我们这里介绍的 SQL 支持 MySQL、PostgreSQL、SQL Server 以及 Oracle 四种数据库，但是我们在演示的时候只用 MySQL 和 PostgreSQL 进行演示，因为这两个是开源的。

下面我们来介绍一下如何在 Linux 上安装 MySQL 和 PostgreSQL，这里我使用的是阿里云上的 CentOS 7.3，至于在 Windows 上如何安装这两个数据，这里就不介绍了，可以自己搜索一下，说实话 Windows 安装没有任何难度。安装完毕之后我们会在本地使用 DataGrip 这个数据库连接工具进行连接、并执行相关操作，当然你也可以使用 Navicat、甚至命令行操作也是可以的。

安装 MySQL

1. 我们需要去 https://dev.mysql.com/downloads/mysql/ 下载 MySQL 所需要的安装包。

默认显示的是最新版，当然你也可以点击 Archives 来指定下载的版本，这里我选择的版本是 8.0.18。

然后是选择 Operating System，页面默认显示的便是你当前的操作系统，但这里我们下载安装在 CentOS 7 上的 MySQL，所以将操作系统选择为：Red Hat Enterprise Linux / Oracle Linux。

注意当你选择操作系统之后，下面会多出来一个下拉菜单：选择 OS Version，也就是指定 Linux 内核版本，这里我们使用的是 CentOS 7，所以选择 Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit)。

然后我们就可以安装了，下面会提供很多种安装包，我们找到 RPM Bundle（一般是第一个），然后点击 Download 下载即可。

2. 下载完毕之后是一个 tar 包，我们将其丢到阿里云服务器上。

上传成功之后先不着急安装，我们先查看当前是否安装了 MySQL 或者 MariaDB，如果安装了那么将其卸载掉（如果你执行安装 MySQL 8.x 版本的话），具体方式如下：

[root@matsuri ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
# 卸载掉 MariaDB
[root@matsuri ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
# 再次查看
[root@matsuri ~]# rpm -qa | grep mariadb

然后 cd 到 /usr/local 目录，这个目录是存放一些本地的共享资源的，然后我们在里面创建一个名叫 mysql 的目录，直接 mkdir mysql 即可。

接下来将之前上传的 tar 包移动到 mysql 下，解压即可，或者你直接解压到 mysql 目录中也可以。

[root@matsuri ~]# ll
total 668804
-rw-r--r-- 1 root root 684851200 Dec 26 16:32 mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar

[root@matsuri ~]# mkdir /usr/local/mysql

[root@matsuri ~]# tar -xvf mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar -C /usr/local/mysql/
mysql-community-libs-8.0.18-1.el7.x86_64.rpm
mysql-community-devel-8.0.18-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.18-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm
mysql-community-common-8.0.18-1.el7.x86_64.rpm
mysql-community-test-8.0.18-1.el7.x86_64.rpm
mysql-community-server-8.0.18-1.el7.x86_64.rpm
mysql-community-client-8.0.18-1.el7.x86_64.rpm

[root@matsuri ~]# cd /usr/local/mysql/

[root@matsuri mysql]# ll
total 668812
-rw-r--r-- 1 7155 31415  40104640 Sep 23  2019 mysql-community-client-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415    611436 Sep 23  2019 mysql-community-common-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   6915400 Sep 23  2019 mysql-community-devel-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415  23683600 Sep 23  2019 mysql-community-embedded-compat-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   3877664 Sep 23  2019 mysql-community-libs-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   1363968 Sep 23  2019 mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 450282440 Sep 23  2019 mysql-community-server-8.0.18-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 158001648 Sep 23  2019 mysql-community-test-8.0.18-1.el7.x86_64.rpm
[root@matsuri mysql]#

3. 使用 rpm 进行安装。

[root@matsuri mysql]# rpm -ivh mysql-community-common-8.0.18-1.el7.x86_64.rpm --nodeps --force
warning: mysql-community-common-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-8.0.18-1.e################################# [100%]

[root@matsuri mysql]# rpm -ivh mysql-community-libs-8.0.18-1.el7.x86_64.rpm --nodeps --force
warning: mysql-community-libs-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-8.0.18-1.el7################################# [100%]

[root@matsuri mysql]# rpm -ivh mysql-community-client-8.0.18-1.el7.x86_64.rpm --nodeps --force
warning: mysql-community-client-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-8.0.18-1.e################################# [100%]

[root@matsuri mysql]# rpm -ivh mysql-community-server-8.0.18-1.el7.x86_64.rpm --nodeps --force
warning: mysql-community-server-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-8.0.18-1.e################################# [100%]
[root@matsuri mysql]#

通过 rpm -qa | grep mysql 命令查看 mysql 的安装包。

[root@matsuri mysql]# rpm -qa | grep mysql
mysql-community-libs-8.0.18-1.el7.x86_64
mysql-community-client-8.0.18-1.el7.x86_64
mysql-community-server-8.0.18-1.el7.x86_64
mysql-community-common-8.0.18-1.el7.x86_64
[root@matsuri mysql]#

4. MySQL 数据库的初始化和相关配置。

[root@matsuri mysql]# mysqld --initialize

# 安装之后会自动创建一个 mysql组、该组下面有一个 mysql 用户, 我们赋予其操作 /var/lib/mysql 目录的权限
[root@matsuri mysql]# chown mysql:mysql /var/lib/mysql -R

# 开启服务
[root@matsuri mysql]# systemctl start mysqld.service

# 设置开启自启
[root@matsuri mysql]# systemctl enable mysqld
[root@matsuri mysql]#

5. 查看数据库的初始密码并进行修改。

[root@matsuri mysql]# cat /var/log/mysqld.log | grep password
...... A temporary password is generated for root@localhost: fwHt=wN<g4aa
[root@matsuri mysql]#

fwHt=wN<g4aa 便是使用 root 登陆的密码，这个密码是随机生成的，然后我们把它改掉。使用初始密码登入到数据库中，然后使用如下命令对密码进行修改：

ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '你的密码';

然后退出，使用新密码进行登录，发现登录成功。

6. 通过以下命令，进行远程访问的授权。

# 创建一个用户叫 root, 这个是从外界连接的时候所使用的用户
# 然后 % 表示接收任意的 IP, 也就是任何一台计算机都可以通过 root 用户来连接
# 同理密码也是从外界连接的时候所使用的密码 
create user 'root'@'%' identified with mysql_native_password by '你的密码';
# 当然你也可以创建其它用户, 比如: create user 'mea'@'%' identified with mysql_native_password by '123456';
# 那么外界便可以通过 用户名: "mea", 密码: "123456" 连接到当前服务器上的 MySQL

# 进行授权
grant all privileges on *.* to 'root'@'%' with grant option;
# 刷新权限
flush privileges;

7. 使用 DataGrip 连接工具进行连接测试。

显示连接成功，并成功获取 mysql 库下面的表名。

至于 MySQL 的一些配置可以通过配置文件 /etc/my.cnf 进行修改。

安装 PostgreSQL

安装 PostgreSQL 比较简单，我们直接去 https://www.postgresql.org/ 页面，点击 Download，然后会进入如下页面：

直接点击 Linux，会弹出 Linux 发行版种类，让你选择，我们选择 Red Hat/CentOS。

之后会进入新的页面，让你选择版本，操作系统种类以及架构。

根据当前的系统进行选择，完毕之后直接显示安装命令，我们按照上面的提示进行操作即可。安装完成之后，默认会有一个 postgres 用户，密码为空。

我们切换到 postgres 用户，然后输入 psql 即可进入控制台，输入 \password postgres 即可设置用户 postgres 的密码。

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=#

下面我们就可以在外界通过 postgres 用户进行连接了，密码就是新设置的密码。但是如果现在就连接的话其实是连接不上的，因为此时 PostgreSQL 数据库还不接受外界的任何请求，我们需要修改配置文件，首先在目录 /var/lib/pgsql/11/data/ 中有两个重要的文件：pg_hba.conf、postgresql.conf。

pg_hba.conf: 配置对数据库的访问权限
postgresql.conf: 配置PostgreSQL数据库服务器的相应的参数

我们需要对这两个文件进行修改：

# 修改 pg_hba.conf, 在文件下方加入如下内容, 表示允许所有主机使用所有合法的用户名访问数据库，并提供加密的密码验证
host    all             all             0.0.0.0/0               trust

# 修改 postgresql.conf, 里面会有一个 #listen_addresses = "localhost", 这被注释掉了
# 凡是被注释掉的, 都是默认值, 我们将注释打开, 然后把 localhost 改成 *, 表示允许数据库监听来自任何主机的连接请求
listen_addresses = '*'
# 在这个文件里面我们可以修改很多配置, 比如监听的端口等等呢个

修改之后重启数据库服务，直接 systemctl restart postgresql-11 即可，下面连接就没有任何问题了，我们使用 DataGrip 连接工具测试一下。

所以我们看到安装非常的简单，没有任何的麻烦之处。但是很明显，这需要你的服务器是联网的，如果不能联网那么这个办法就失效了。所以下面我们介绍另一种安装 PostgreSQL 的方式，也就是编译安装。

编译安装 PostgreSQL

首先进入到 https://www.postgresql.org/ftp/source/ 中，会看到各种不同版本的PostgreSQL，这里我们随便选一个吧，就选10.12，首先10表示大版本，后面12属于小版本。这里我们只是介绍，并不会真的安装，我们后续使用的仍是上面通过 yum 安装的 PostgreSQL。

点击进去，再点击后缀为 tar.gz 的，即可开始源码包的下载，下载之后将其扔到服务器上。

然后 mkdir -p /opt/postgres 创建相应目录，然后执行tar -zxvf postgresql-10.12.tar.gz -C /opt/postgres 解压到该目录中。

进入到 /opt/postgres/postgresql-10.12 之后

chmod 755 configure
# 安装在/opt/postgres/下面
./configure -prefix=/opt/postgres/ --without-readline
# 编译安装
make && make install

编译安装之后，再 cd 到 contrib 目录中，执行 make && make install，这里安装 contrib 目录下的一些第三方组织的一些工具代码。

因为 PostgreSQL 会默认将数据写在data目录下，所以执行：

mkdir -p /opt/postgres/data

但是 PostgreSQL 不允许使用root用户初始化数据库，所以需要创建一个新用户，这里叫 postgres

# 创建组和用户
groupadd postgres
useradd -g postgres postgres

可以的话，也可以设置用户 postgres 的密码，通过 passwd postgres 即可设置。然后将 /opt/postgres/ 的权限交给postgres用户：

chown postgres:postgres /opt/postgres -R
chown postgres:postgres /opt/postgres/data -R

通过命令 su postgres 切换至 postgres 用户，在其 ~/.bashrc 中加入如下内容，并进行 source。

export PGHOME=/opt/postgres/
export PGDATA=/opt/postgres/data
export PATH=$PGHOME/bin:$PATH
export PATH=$PATH:/opt/postgres/bin
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
alias rm='rm  -i'
alias ll='ls -lh'
#alias pg_start='pg_ctl start -D $PGDATA'
#alias pg_stop='pg_ctl stop -D $PGDATA -m fast'

初始化数据库：

initdb -D /opt/postgres/data

然后启动服务：

pg_ctl -D /opt/postgres/data -l logfile start

一些配置我们可以通过 /opt/postgres/data/postgresql.conf、/opt/postgres/data/pg_hba.conf 来进行设置。

最后我们可以通过如下命令来控制服务的启停。

# 启动
pg_ctl -D /opt/postgres/data -l /opt/postgres/logfile start
# 重启
pg_ctl -D /opt/postgres/data -l /opt/postgres/logfile restart
# 停止
pg_ctl -D /opt/postgres/data -l /opt/postgres/logfile stop
# 查看端口是否启用
netstat -anp | grep 5432

以上就是 MySQL 和 PostgreSQL 的安装方式，但是说实话我们搞开发的，应该很少会遇到安装、部署之类的工作，所以了解一下即可，我们的重点是使用 SQL 查询想要的数据。

小结

关系模型中定义了一个简单的数据结构，即关系（表），用于存储数据。SQL 是关系数据库的通用标准语言，它使用接近于自然语言（英语）的语法，通过声明的方式执行数据定义、数据操作、访问控制等。对于 SQL 而言，一切都是关系（表）。

现在主流的数据库有 MySQL、PostgreSQL、SQL Server、Oracle，当然还有其它的，比如：DB2、Teradata 等等。后续我们介绍的 SQL 都支持 MySQL、PostgreSQL、SQL Server、Oracle，不支持的会单独指出来，并使用 MySQL 和 PostgreSQL 进行演示。

posted @ 2019-06-12 10:35 古明地盆阅读(966) 评论(0) 编辑收藏举报

刷新页面返回顶部

来自东方地灵殿的小提琴手

愿时间能带走痛苦