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;