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)

如上代码创建了usersarticles 表、。在创建时通过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中使用 BEGINSTART 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;

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

条件表达式:

条件 表达式举例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 JOINLEFT OUTER JOINRIGHT OUTER JOINFULL 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 分别修改为其他的连接方式进行测试,可知连接方式类似于两个有交集的集合取值范围:

image

同时,连接查询之后可以加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 合并查询结果

UNIONUNION 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;