简单学习SQL语言

SQL（Structured Query Language）就是一个语言，操作数据库的语言，可以实现增删改查（库、表、列、数据）。

• 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
• 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。
• 字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为 NULL。注意 NULL 表示字段数据不存在。一个整型字段如果为 NULL 不表示它的值为 0，同样的，一个字符串型字段为 NULL 也不表示它的值为空串''
• 主键是表里记录的唯一标识，现在一般采用自增，与业务逻辑无关

MySQL

MySQL 是应用最光泛的数据库，本文以此为例。

• Ubuntu 用户 - 可通过命令apt-get install mysql-server安装最新的 MySQL 版本。
• Mac用户 - 可通过命令brew install mysql安装，mac详细安装教程

安装完MySQL后，以下两个同时具备了

• 一个是 MySQL Server，即真正的 MySQL 服务器，
• 还附赠一个 MySQL Client 程序。其是一个命令行客户端，可登录 MySQL，然后，输入 SQL 语句并执行。

MySQL Client命令行客户端输入命令，mysql -u root -p，输入正确的密码，即可连接MySQL Server，输入exit，退出连接模式。

EXIT 仅仅断开了客户端和服务器的连接，MySQL 服务器仍然继续运行。

• 在MySQL Client中输入的 SQL 语句通过TCP连接发送到MySQL Server。默认端口号是3306，
• 如果发送到本机MySQL Server，地址就是127.0.0.1:3306
• 如果连接远程，需要指定IP或者域名，使用命令mysql -h 10.0.1.99 -u root -p

管理库、表、列

库：

• 列出所有数据库 - SHOW DATABASES
• 创建一个新数据库 - CREATE DATABASE <数据库名字>
• 删除一个数据库 - DROP DATABASE <数据库名字>，删除一个数据库将导致该数据库的所有表全部被删除
• 切换数据库 - SHOW TABLES，对一个数据库进行操作时，要首先将其切换为当前数据库

表：

• 列出当前数据库的所有表 - USE <数据库名字>
• 查看一个表的结构 - DESC <数据库名字>
• 查看创建表的 SQL 语句 - SHOW CREATE TABLE <数据库名字>
• 创建一个新表 - CREATE TABLE <表名字>
• 删除一个表 - DROP TABLE <表名字>

列：

• 给表增加新列 - ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;，给 students 表新增一列 birth
• 修改列名 - ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;，列名改为 birthday，类型改为 VARCHAR(20)
• 删除列 - ALTER TABLE students DROP COLUMN birthday;

查询数据

基础查询

-- SELECT * FROM students;
SELECT * FROM <表名>

• SELECT是关键字，表示将要执行一个查询
• *表示“所有列”
• FROM表示将要从哪个表查询，本例中是 students 表。
• 该 SQL 将查询出 students 表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据
• 许多检测工具会执行一条SELECT 1;来测试数据库连接

条件查询

很多时候，我们并不需要获得所有记录，而是获取指定条件的记录

-- SELECT * FROM students WHERE score >= 80;
SELECT * FROM <表名> WHERE <条件表达式>

• 条件 1：根据 score 列的数据判断：score >= 80
• 条件 2：根据 gender 列的数据判断：60 <= score <= 90
• AND：score >= 80 AND gender = 'M'
• OR：score >= 80 OR gender = 'M'
• NOT：NOT class_id = 2，其实等价于class_id <> 2，所以不常用NOT
• LIKE：name LIKE 'ab%'，LIKE 相似，%是任意字符

投影查询

希望返回某些列的数据，而不是所有列的数据

-- SELECT id, score, name FROM students;
SELECT 列1, 列2, 列3 FROM ...
-- 也可别名 SELECT id, score points, name FROM students;
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

排序查询

默认是主键排序，其他排序的话用ORDER BY，不写的话，默认ASC

SELECT * FROM students ORDER BY score;
SELECT * FROM students ORDER BY score DESC;

• 分数相同按 gender 排序的话，ORDER BY score DESC, gender

分页查询

数据量庞大的话，就需要分页展示，可以设置每页 100 条

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
-- 也可简写为 LIMIT 3,0
LIMIT 3 OFFSET 0;

• LIMIT 3 OFFSET 0表示，对结果集从 0 号记录开始，最多取 3 条。注意 SQL 记录集的索引从 0 开始
• LIMIT总是设定为pageSize，OFFSET计算公式为pageSize * (pageIndex - 1)

聚合查询

统计一张表的数据量用COUNT()，表示查询所有列的行数，就是记录的数量

SELECT COUNT(*) FROM students;

• 一般给列名设置一个别名，便于处理结果：SELECT COUNT(*) num FROM students;，没有记录返回0
• 其他聚合函数，可以计算某列的总体值：SUM、AVG、MAX、MIN，SELECT AVG(score) average FROM students，找不到列返回NULL
• 获取总页数SELECT CEILING(COUNT(*) / <pageSize>) FROM students

分组

学生列表里，想要分别统计一班、二班、三班的学生数量，就是用分组，省的一个个查询

SELECT class_id,COUNT(*) num FROM students GROUP BY class_id;
复制代码

执行该 SELECT 语句时，会把class_id相同的列先分组，再分别计算，因此，得到了多行结果。

• 查询各班的男女生人数：SELECT class_id,gender,count(*) num FROM students GROUP BY class_id,gender
• 查询各班的平均分：SELECT class_id,AVG(score) average FROM students GROUP BY class_id;

多表查询

多表查询其实就是FROM <表名1>, <表名2>

比如联合查询学生和班级表

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
WHERE s.gender = 'M' AND c.id = 1;

• 两个表有相同字段的时候，需要使用别名
• 表也可以使用别名
• 多表查询，记录可能会很多，所以尽量带条件

连接查询

主表想增加别的表的字段

比如学生表想增加班级名称字段，需要班级表配合

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

• 先确定主表，仍然使用FROM <表1>的语法；
• 再确定需要连接的表，使用INNER JOIN <表2>的语法；
• 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示 students 表的 class_id 列与 classes 表的 id 列相同的行需要连接；
• 可选：加上 WHERE 子句、ORDER BY 等子句。

不同的 join:

• INNER JOIN 只返回同时存在于两张表的行数据
• RIGHT OUTER JOIN 返回右表都存在的行
• LEFT OUTER JOIN 则返回左表都存在的行
• FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为 NULL

修改数据

关系数据库的基本操作就是增删改查，即 CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了 SELECT 语句的详细用法。

而对于增、删、改，对应的 SQL 语句分别是：

• INSERT：插入新记录；
• UPDATE：更新已有记录；
• DELETE：删除已有记录。

INSERT：插入新记录

-- INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
-- SELECT * FROM students;
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

可以添加多条记录：

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

UPDATE

-- UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
-- SELECT * FROM students WHERE id=1;
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

更新字段，可以使用表达式UPDATE students SET score=score+10 WHERE score<80;

UPDATE语句可以没有WHERE条件，整个表的所有记录都会被更新。所以，在执行 UPDATE 语句时要非常小心 最好先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集，然后再用 UPDATE 更新

DELETE

DELETE FROM <表名> WHERE ...;

同样也应该SELECT提前测试

SQL 的常用语句

插入一条新记录（INSERT）

• 若记录已存在则删除，再插入新记录，否则直接插入新纪录。可以使用 REPLACE 语句，省去查询步骤（记录存在与否通过主键判断）

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

复制表

复制一份当前表的数据到一个新表，专业名词快照，新创建的表结构和原表结构完全一致。

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
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;

强制使用指定索引

在查询的时候，数据库默认选择最合适的索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

-- 指定索引的前提是索引idx_class_id必须存在
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

事务

事务，其实是说一系列的操作，都执行成功才成功，否则等于没执行。

一系列的操作作为整体，需要用关键词括起来：

BEGIN;
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

引用

本文主要参考和大量使用廖大神的原句，简单构成手册使用，感谢廖大神

• MySQL安装（Mac版）
• 廖雪峰大神的SQL教程