SQL数据库查询笔记

SQL数据库是指关系型数据库，相对的NoSQL泛指非关系型数据库。

SQL是结构化查询语言并且是ANSI组织定义的标准，这就意味着SQL语言在各个不同的数据库平台都支持。事实也是这样，各个平台都支持SQL核心功能但是各平台都有自己特定的扩展。

SQL定义了三种数据库操作能力：

DDL：Ddta Definition Language，数据定义语言允许用户定义数据，也就是对表结构的创建、删除和修改。
DML：Data Manipulation Language, 数据操作语言，对表中数据的添加、删除和更新。
DQL：Data Query Language，数据查询语言，对表中数据的查询。

SQL语言并未定义如何创建一个数据库，以及数据库种字段应该是那些类型等，这些都需要个数据库平台自己扩展。

1、MySQL配置远程可访问

在网络可以轻易查到MySQL在各个平台的安装教程，在安装完成后数据库只能再本地访问，要想配置远程可访问需要做以下配置：

一、修改配置文件的bind-address属性
在/etc/mysql/my.conf文件中查找bind-address属性，如果没有则查看该文件中引用的文件。将bind-address = 127.0.0.1修改为 bind-address = 0.0.0.0

二、为需要远程登录的用户赋予权限
新建用户并赋予远程连接权限

grant all on *.* to admin@'%' identified by '123456' with grant option; 
flush privileges;

直接赋予root用户远程连接权限

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

2、分析

2.1 建表

关系型数据库建表当然要符合数据库建表的三大范式；但当我们对实际问题定义及分析时，通常会做出与范式不匹配的决策，因此我们的表只根据实际需求来定义。当我们将问题定义清楚时，所谓一对多，多对多都不是问题！

假设我们要为一个博客系统创建数据库，系统有文章和用户两张表，如下：

articles表 id | user_id | title | text | visit_num —|—|—|—|— 1 | 1 | 文章1 | Hello World | 10 2 | 1 | 文章2 | 我的博客 | 100 3 | 1 | 文章3 | 文章内容呀 | 11 4 | 2 | 文章4 | 我的博客 | 13 5 | 2 | 文章5 | 文章内容呀 | 16 6 | 2 | 文章6 | 我的博客 | 150

users表 id | name —|— 1 | 小明 2 | 大牛 3 | 大姚

2.2 数据类型

表中列数据应该都有一个数据类型，例如 visit_num (访问量)应该是个数值的类型，title和text应该是字符串的类型等。

MySQL中常用数据类型

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-10^38
DOUBLE	浮点型	8字节浮点数，范围约+/-10^308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

3、管理MySQL

安装和配置MySQL服务器后，继续在MySQL服务器种创建我们需要的数据库，该部分是MySQL特有的管理数据库的方式不属于核心的SQL功能：

3.1 登录MySQL

mysql -uroot -p123456

通过如上mysql命令登录数据库，-u后是用户名-p后加密码。登录成功后展示：

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 147
Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3.2 查看已有数据库

如上已经登录MySQL系统并且命令行前的提示符也已编程mysql。要查看当前数据库种的表，可以使用SHOW DATABASES;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

在MySQL中，命令和关键词部分是不区分大小写的，show databases;和SHOW DATABASES;是等价的。

操作具体的数据库

当前系统中展示的四个数据库均为MySQL系统相关的配置，我们不能直接使用。要创建自己能够使用的数据库，使用CREATE

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

再次使用show databases;时会多出一个数据库项，如果不想用了可以通过DROP删除数据库：

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

在创建数据库时，还可以指定数据库种默认的字符集和比较规则：

mysql> create database mydb character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

如上指定了utf8mb4编码为默认数据库编码，并指定了比较规则为utf8mb4_general_ci，也可以单独设置编码然后mysql会自动匹配该编码默认的比较规则。

编码好理解，比较规则影响数据库中所有涉及到字符串的操作，例如order by，group by，索引的建立等。

创建完数据库之后才真正开始，对于代码编程访问数据的方式，由于在连接时即指定了数据库，可以直接进行表创建等语句；对于命令行登录需要明确指明我接下来要进入那个数据库进行操作：

mysql> use test;
Database changed

mysql> show tables;
Empty set (0.00 sec)

如上切换数据库到创建的test数据库，通过show tables;可以看到当前数据库没有表，接下来可以操作test数据库了。

4、DDL语句

有了数据库，接下来的DDL语句就进入SQL核心功能范围了。

4.1 创建表 `CREATE TABLE`

mysql> CREATE TABLE `users` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(100) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `articles` (
    -> `id` bigint(20) NOT NULL AUTO_INCREMENT,
    -> `user_id` bigint(20) NOT NULL,
    -> `title` varchar(100) NOT NULL,
    -> `text` varchar(999) NOT NULL,
    -> `visit_num` int(11) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

如上代码创建了users 和 articles 表、。在创建时通过AUTO_INCREMENT 设置自动增长，通过NOT NULL设置该列不准为空；并且在最后使用primary key设置了主键。同时在建表语句最后指定了引擎为InoDB，表的编码，和自增长值。

4.2 外键约束

主键是建表是指定的，能够唯一确定一条数据的列。外键是个约束，例如articles表中的user_id列，值必然是user表中的id，如此才能意义对应。在MySQL中可以添加一个外键的约束，在插入式检查user_id是不是合法的。

mysql> ALTER TABLE articles ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

由于外键检查是浪费数据库性能的，而通常我们在插入式可以通过程序来保证外键准确，可以取消外键约束。

mysql> ALTER TABLE articles DROP FOREIGN KEY fk_user_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.3 索引

一个表默认对主键创建索引，也就是说在数据库中表的顺序按主键排列。对于文章来说我们通常需要对title进行检索，而如果对title创建索引则会提高检索效率。

mysql> ALTER TABLE articles ADD INDEX idx_title (title);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

如上创建了一个名为 idx_title 的索引。如果要同时对比标题和内容创建索引，可以在title后加text列：

ALTER TABLE articles ADD INDEX idx_title_text (title, text);

4.4 修改表结构

创建完成后如果想要添加或修改表结构，需要使用ALTER TABLE（上文的索引和外键均添加在表上，用的也是 ALTER TABLE）。

为articles表添加一列 tag：

ALTER TABLE articles ADD COLUMN tag VARCHAR(10) NOT NULL;

修改tag的名称为tags并修改数据类型：

ALTER TABLE articles CHANGE COLUMN tag tags VARCHAR(20) NOT NULL;

删除tags列：

ALTER TABLE articles DROP COLUMN tags;

4.5 删除表

删除创建的表可以使用 drop table +表名称：

mysql> drop table articles;
Query OK, 0 rows affected (0.02 sec)

5、DML语句

表定义完成之后需要向表中写入数据，写完了还可能修改和删除，至于查询则是DQL的部分。

5.1 插入数据

要发布一篇文章，即向数据库种添加一条数据，插入数据的基本语法：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

向articles中插入一条数据：

INSERT INTO articles (user_id, title, text, visit_num) VALUES (1,'文章标题呀','文章内容呀',10);

如上代码在插入时没有指定id，id根据自增长规则默认从1开始加，如果想指明id可以将id明确指出来：

INSERT INTO articles (id,user_id, title, text, visit_num) VALUES (5, 1,'文章标题1','文章内容1',10);

如果要同时插入多条数据可以将数据并排写在VALUES之后：

INSERT INTO articles (user_id, title, text, visit_num) VALUES (1,'标题1','内容1',11),(2,'标题2','内容2',110);

5.2 修改数据

文章发布之后经常会发现错别呀什么的，要修改错别字则要更新数据。修改数据的基本语法：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

修改错别字：

UPDATE `articles` SET `title` = '修改后的标题', `text` = '修改后的内容' WHERE `id` = 1;

当有文章被浏览时，要增加一次访问数：

UPDATE `articles` SET `visit_num` = `visit_num`+1 WHERE `id` = 1;

如果不加WHERE 语句进行条件筛选，则表中所有行都将被修改。

5.3 删除一条数据

删除一条数据的基本语法：

DELETE FROM <表名> WHERE ...;

删除一篇文章：

DELETE FROM `articles` WHERE `id` = 1;

同Update，如果不加WHERE则删除整个表中的数据。

5.4 事务

在执行SQL语句时，某些业务可能被认为是一起执行的，要不全部执行要不都不执行，例如用户A给B转账时用户A减少100则用户B加100，否则退回转账失败。

事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为 ACID：

原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
隔离性（Isolation）：使事务操作相互独立和透明。
持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

在MySQL中使用 BEGIN 或 START TRANSACTION 开始一个事务，在一组语句完成时使用 COMMIT 提交一个事务，在失败时使用 ROLLBACK 回滚到开始状态。

BEGIN;
UPDATE accounts SET amount = amount - 100 WHERE id = 1;
UPDATE accounts SET amount = amount + 100 WHERE id = 2;
COMMIT;

BEGIN;
UPDATE accounts SET amount = amount - 100 WHERE id = 1;
UPDATE accounts SET amount = amount + 100 WHERE id = 2;
ROLLBACK;

6、DQL 语句

查询是数据库最常用的语句，并且为了在结构化的数据结构中查询出需要的内容，查询语句相对于其他语句更加庞大和复杂，在开始查询之前，我们先插入一些数据以备使用：

delete from users;
delete from articles;

INSERT INTO users (name) VALUES ('小明'),('大牛'),('大姚');
INSERT
  INTO `articles`
  (`user_id`, `title`, `text`, `visit_num`)
VALUES
  (1, '标题1', '内容1', 10),
  (2, '标题2', '内容2', 11),
  (1, '标题3', '内容3', 12),
  (1, '标题4', '内容4', 13),
  (1, '标题5', '内容5', 110),
  (2, '标题6', '内容6', 120),
  (2, '标题7', '内容7', 130);

6.1 基本查询

要查询数据库，我们使用SELECT语句，基本格式如下：

SELECT * FROM <表名>

查询所有文章：

SELECT * FROM `articles`;

* 表示要查找所有列，FROM 后面接要查询的表。

FROM 关键词并非必须的，例如可以执行 SELECT 1;，SELECT后可以接任意表达式。

6.2 条件查询

在基本查询后面加WHERE筛选语句即可筛选出要查询的数据：

SELECT * FROM <表名> WHERE <条件表达式>

列出文章中访问数大于100的条目：

SELECT * FROM `articles` WHERE `visit_num` > 100;

条件表达式是可以运算，运算符有NOT、AND 和 OR优先级依次降低。像其他任何编程语言一样可以用圆括号来改变优先级。

条件表达式：

条件	表达式举例1	表达式举例2	说明
=	score = 80	name = ‘abc’	判断相等，字符串需要用单引号括起来
>	score > 80	name > ‘abc’	判断大于，字符串根据数据库的`collate` 进行比较
>=	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’

列出文章中访问量在50 至100之间的：

SELECT * FROM `articles` WHERE `visit_num` < 100 AND `visit_num > 50`;

6.3 投影查询

使用 SELECT * ... 查询时，数据库返回查询结果的所有列，但大多数情况下只需要其中指定的列即可，投影查询即使用如下方式查询指定列：

SELECT 列1, 列2, 列3 FROM ...

查出文章的用户id和标题：

SELECT `user_id`, `title` FROM `articles`;

还可以为每一列起个名字，这样查询结果的表头即为新其的名字：

SELECT `user_id` 用户, `title` 标题 FROM `articles`;

投影查询也是可以接WHERE 语句筛选要查询的条目。

6.4 结果排序

查询语句后加 ORDER BY 对查询结果进行排序，默认升序（ASC）。

ORDER BY 列1 (排序方式), 列2 （排序方式）

排序方式可省略，默认升序（ASC）或者降序（DESC），并且排序依据的列可以是多个。

SELECT * FROM `articles` ORDER BY `visit_num` DESC;
SELECT * FROM `articles` ORDER BY `visit_num` DESC, `title` ASC;

如果有WHERE语句，ORDER BY语句在WHERE语句后面：

SELECT * FROM `articles` WHERE `user_id` = 1 ORDER BY `visit_num` DESC;

6.5 分页查询

分页查询用LIMIT <M> OFFSET <N> 实现，表示每页最多M条记录，从第N条记录开始算。N通常是0或者M的倍数。

SELECT * FROM `articles` LIMIT 3 OFFSET 3;

6.6 聚合查询

聚合函数

SQL内置了多个常用函数，可以通过函数实现聚合查询：查询一共多少篇文章：

SELECT COUNT(*) FROM `articles`;

同时聚合查询也支持创建别名：

SELECT COUNT(*) num FROM `articles`;

常用聚合函数：

函数	说明
COUNT	计算查询结果数
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值，可以为字符串
MIN	计算某一列的最小值，可以为字符串

分组

如果我们要统计各用户的发文章量，使用GROUP BY 语句进行分组，聚合函数将按分的组各自计算。

SELECT COUNT(*) FROM `articles` GROUP BY `user_id`;

为了查看方便，我们可以将分组依据列拿到SELECT 中：

SELECT `user_id`, COUNT(*) num FROM `articles` GROUP BY `user_id`;

GROUP BY 后面可以加多个分组依据列，假设我们的文章有标签列tag，则可以使用如下查询：

SELECT `user_id`, `tag`, COUNT(*) num FROM `articles` GROUP BY `user_id`, `tag`;

6.7 多表查询

SELECT 不仅可以从一张表中查询，还可以从多张表中查询数据，查询结果为多张表的乘积。

SELECT * FROM <表1> <表2>

假设表1有10条记录，表2有5条记录，则结果为50条记录。

SELECT * FROM `articles`, `users`;

在投影时，某些列（如id）名称冲突，投影式列名称前应加表名称：

SELECT
  `articles`.`id`, `users`.`id`, `name`, `articles`.`title`
FROM
  `articles`, `users`;

当然也可以取别名。

同时FROM语句中的表名称也可以取别名：

FROM <表名1> <别名1>, <表名2> <别名2>

如上的查询可以修改为：

SELECT
  `a`.`id` a_id, `u`.`id` u_id, `name` name, `a`.`title` title
FROM
  `articles` a, `users` u;

也可以通过WHERE语句进行筛选，例如筛选出所有文章表中user_id 与用户表id相同的条目：

SELECT
  *
FROM
  `articles` a,
  `users` u
where
  a.user_id = u.id;

6.8 连接查询

连接查询时另一个格式的多表查询，在查询时以一张表为主然后将另一张表选择性的连接到结果上。连接的方式有4种INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN。

SELECT ... FROM tableA 连接方式 JOIN tableB ON tableA.column1 = tableB.column2;

以INNER JOIN为例，我们来实现上节中查询文章对应用户的用户名的语句

SELECT
  `title`, `visit_num` 'num', `name`
FROM
  `articles` a
    INNER JOIN `users` u ON `a`.`user_id` = `u`.`id`;

将代码中INNER JOIN 分别修改为其他的连接方式进行测试，可知连接方式类似于两个有交集的集合取值范围：

同时，连接查询之后可以加WHERE语句进行筛选：

SELECT
  `title`, `visit_num` 'num', `name`
FROM
  `articles` a
    RIGHT OUTER JOIN `users` u ON `a`.`user_id` = `u`.`id`
where visit_num >100;

6.9 合并查询结果

UNION 和 UNION ALL 和合并两条查询结果，区别在于UNION会自动去除重复的值，UNION ALL保留所有值。

两条查询语句的查询结果或者投影查询结果列数必须相等，且对应列类型相同。

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

实用SQL语句

插入或替换

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

插入或更新

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

快照

CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

写入查询结果集

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

Note (1)

SQL (1)

置顶文章

最新发表

友情链接

联系我