【大数据学习心得】原创SQL教程（上）

本人自己码的数据库基础教程，每个步骤都有说明和代码辅助，大家如果需要可以自行食用（因字数超限分两篇发）。

一，认识数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

数据库的一行叫做一条记录，数据库的一列叫做一个字段。

数据库分为：关系型数据库和非关系型数据库。

    关系型数据库Tips:
    (1)关系型数据库中每条记录的字段数量都是相等的。新增一条纪录时，可空字段如果没有传值会用Null填充，而非空字段如果没有传值则无法生成该条记录。此外，关系型数据库中的记录还受到各字段类型的制约。
    (2)关系型数据库通常用于存储结构化数据，广泛应用于网站后台、数据中台、以及企业的数据管理。
    (3)关系型数据库将数据存放在内存中，因而存储空间较大，读取速度较慢，在不人为删除的情况下，存储永久有效，稳定性较好。
​
    非关系型数据库Tips:
    (1)非关系型数据库以键值对的形式存储数据，对键和值的数量和类型没有强制要求，相对来说较为自由，当出现个别异常数据时也能很好地处理。此外，非关系型数据库对网络传输的json字符串有较好的支持。
    (2)非关系型数据库通常用于存储非结构化数据，广泛应用于网站中间件、系统缓存、异步处理等技术中。
    (3)非关系型数据库的数据在缓存中，存储空间有限，不适用于存储大量数据，但是读取速度较快。通常数据会有一个过期时间，系统断电也会失效，存储的稳定性不好。

常见的关系型数据库：MySQL、Oracle。

常见的非关系型数据库：Redis、MongoDB。

二，认识MySQL

MySQL 是一个关系型数据库管理软件，是 Oracle 公司的产品。

• MySQL 是开源的，使用它不需要支付额外的费用。

• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

• MySQL 使用标准的 SQL 数据语言形式。

• MySQL 可以运行于多个系统上，并且支持多种语言。

三，从数据库的命令说起

MySQL 是一个关系型数据库管理软件，它的直接管理对象是数据库，成功连接MySQL 后无论是图形化界面还是命令行也是首先操作的数据库，那么我们就从数据库的命令说起，直接上代码：

-- 查看当前数据库
SHOW DATABASES;
​
-- 创建一个名为test的数据库
CREATE DATABASE test;
​
-- 创建数据库的同时指定字符集
CREATE DATABASE test1 DEFAULT CHARSET = utf8;
​
-- 库不能重名，重名会报错
-- CREATE DATABASE test;
-- > 1007 - Can't create database 'test'; database exists
​
-- 查看创建库的语句
SHOW CREATE DATABASE test;
-- CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */
​
-- 选择当前使用的数据库
USE test;
​
-- 查看当前所在的库
SELECT DATABASE();
​
-- 删除数据库
-- 这是程序员届著名的“删库跑路”的命令
-- 一般不是自己建的数据库不要去删除
DROP DATABASE test1;

从数据库的命令初窥SQL语法规则：

1. SQL语句中字母不区分大小写，但是一般习惯关键字大写，库名、表名、字段名小写；
2. SQL语句都是用";"结尾的，尤其是在命令行中，不输入";"，系统就会默认该条语句一直没有执行完；
3. SQL语句的语法符合英语的习惯，如创建数据库test的命令翻译过来就是：创建数据库test。

四，数据表增删改命令大全

讲完了数据库的命令，我们已经学会了怎么增、删、查数据库，并且成功地创建和使用自己的数据库。数据库是用来管理数据表的仓库，接下来需要学习数据表的命令。

在学习数据表的操作命令之前，有必要聊一下MySQL中的数据类型(了解即可)：

数值类型

日期和时间类型

字符串类型

1. 创建数据表

-- 创建学生表的命令
CREATE TABLE IF NOT EXISTS `student`(
   `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
   `stu_num` VARCHAR(5) NOT NULL COMMENT '学号',
   `name` VARCHAR(10) NOT NULL COMMENT '姓名',
   `age` TINYINT NOT NULL COMMENT '年龄',
   `birthday` DATE NULL COMMENT '生日'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生表';

先分析创建表命令的主体部分，只需要将用汉字写出来的替换成实际表的内容即可：

CREATE TABLE [IF NOT EXISTS] 表名(...) ENGINE=InnoDB DEFAULT CHARSET=utf8 [COMMENT 备注];

再分析创建表命令中每个字段是怎么创建的：

字段名 字段类型1 [字段类型2 [字段类型3 ...]] [COMMENT 备注]

可以在navicat中选中表-右键-设计表查看创建的结果：

双击表格则打开了一个只有字段名的空表：

2. 数据表的其它管理命令

数据表的其它管理命令可以参考库命令，直接上代码：

-- 查看当前数据库的表格
SHOW TABLES;
​
-- 查看某个表的创建语句
SHOW CREATE TABLE student;
-- CREATE TABLE `student` (
--   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
--   `stu_num` varchar(5) NOT NULL COMMENT '学号',
--   `name` varchar(10) NOT NULL COMMENT '姓名',
--   `age` tinyint(4) NOT NULL COMMENT '年龄',
--   `birthday` date DEFAULT NULL COMMENT '生日',
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='学生表';
​
-- 查看表结构
DESC student;
​
-- 删除表
DROP TABLE student;

3. 向数据表中新增数据

有了数据表，就可以向数据表中添加数据了，MySQL中添加数据是按行(记录)添加，其命令如下：

-- 插入一条数据
INSERT INTO student VALUES(1, '20001', '小明', 20, '2000-01-01');
​
-- 按字段插入
INSERT INTO student(stu_num, `name`, age) VALUES('20002', '小芳', 18);
​
-- 插入多条数据
INSERT INTO student(stu_num, `name`, age, birthday) 
VALUES('20003', '小磊', 21, '1999-05-23'),
      ('20004', '小赵', 19, '2001-08-18'),
      ('20005', '小王', 20, NULL);

由上面的代码可以总结插入数据的一般格式：

INSERT INTO 表名[字段1, 字段2,...字段n] 
VALUES(值1, 值2, ...值n),
      (值1, 值2, ...值n),
      (值1, 值2, ...值n);

需要注意的是，除了第一个命令中传入了id列的数据外，剩下的按字段插入数据的写法中均不需要传id。这是因为id列有自增属性，会根据已有的数值自动累加。但是无论怎样，表名后面所跟的字段数必须与VALUES后面所跟的字段数相同，且非空字段如果不传值则数据会添加失败。

下面的SQL语句均不能正确添加一条记录：

-- 表名后没有指定字段，而VALUES中又没有传id值
INSERT INTO student VALUES('20006', '小琳', 18, '2002-03-01');
-- > 1136 - Column count doesn't match value count at row 1
​
-- 表名后的字段数量与VALUES中的值数量不相等
INSERT INTO student(stu_num, `name`, age) VALUES('20006', '小琳', 18, '2002-03-01');
-- > 1136 - Column count doesn't match value count at row 1
​
-- 表名后的字段数量与VALUES中的值数量相等，但是部分非空字段未传值
INSERT INTO student(stu_num, age, birthday) VALUES('20006', 18, '2002-03-01');
-- > 1364 - Field 'name' doesn't have a default value

4. 删除数据表中的数据

上文提到的drop命令是删除整个数据表，如果想要删除部分数据，则可以使用delete命令，其中where条件是要删除数据的定位信息，如果没有定位，则默认清空整张表的数据，但是数据表依旧会保留：

-- 删除表数据
-- DELETE FROM 表名 [WHERE 条件];
-- 删除student表中姓名为小赵的同学信息
DELETE FROM student WHERE `name`='小赵';
-- 如果没有条件，则默认清空整张表
DELETE FROM student;

5. 修改数据表中的数据

与删除表中数据类似地，修改表中数据也要通过where条件定位，但是与删除表数据不同的是，如果不加where条件，则默认会修改每条记录的set后字段的值：

-- 更新表数据
-- UPDATE 表名 SET 字段1=字段1新值[, 字段2=字段2新值...] [WHERE 条件];
-- 将学号是2003的同学姓名修改为小强
UPDATE student SET `name` = '小强' WHERE stu_num = '2003';
-- 如果没有条件，则默认修改整个age字段的值
UPDATE student SET age = 18;

6. 修改数据表中的字段

操作数据表，有时候需要修改、添加和删除字段，其基本语法如下：

-- 基本语法
-- ALTER TABLE 表名 执行动作
​
-- 添加字段address
ALTER TABLE student ADD address VARCHAR(50) NULL;
​
-- 删除字段address
ALTER TABLE student DROP address;
​
-- 修改字段数据类型
ALTER TABLE student MODIFY stu_num INT;
​
-- 修改字段名和数据类型
ALTER TABLE student CHANGE stu_num stuNum INT(8);
​
-- 修改表名
ALTER TABLE student RENAME stu_info;

五，数据表的查询

1. MySQL运算符和聚合函数

不管什么应用场景，数据表的查询都是最难啃的一块骨头。为了能顺利地展示数据查询的代码，先来两碟开胃小菜，聊聊MySQL的运算符和聚合函数。

MySQL运算符

比较运算符：
数值比较：=, !=, <, <=, >, >=
字符比较：=, !=
​
逻辑运算符：AND, OR, NOT
​
空运算符：NULL, NOT NULL
​
范围：BETWEEN...AND..., IN, NOT IN
​
模糊比较：LIKE _(匹配单一字符)/%(匹配多字符)
​
正则匹配：regexp 正则表达式(^(以...开头), $(以...结尾), .(匹配单一字符), *(匹配多个字符), [](包含))

MySQL聚合函数

AVG(字段名)：求字段平均值
SUM(字段名)：求和
MAX(字段名)：取最大值
MIN(字段名)：取最小值
COUNT(字段名)：计数
​
两点理解：
1，聚合函数通常与分组GROUP BY联合使用，全名为分组聚合，意为先分组再聚合，单独使用聚合函数不会输出结果；
2，求平均、求和、取最值的操作往往都是针对数值型字段，字符型的无法参与计算，但是计数适用于所有类型。

2. MySQL查询的完整语法

下面正式进入MySQL查询的命令。为了能从一开始对MySQL语法有一个整体的把握，我们从一开始就先抛出查询的完整语法，再逐项分解其功能和用法。

-- SQL查询的完整语法(展示用，不能执行)
SELECT *                  -- 这里写最后要显示的内容，可以是字段名，也可以是聚合函数，查询所有用*， 
FROM stu_info [as] a      -- 这里写表名，可以用as关键字给表起别名，as可以省略
JOIN class [as] b         -- 这里是表连接的操作，使用join关键字连接两个表
ON a.stuNum = b.stuNum    -- 这里是连接表所依据的字段
WHERE a.age = 18          -- 这里是查询的条件
GROUP BY a.stuNum         -- 这里是分组的字段
HAVING COUNT(id) > 1      -- 这里是分组之后的筛选条件
ORDER BY a.stuNum DESC    -- 这里规定排序的规则，其中默认升序ASC，DESC是降序
LIMIT 5                   -- 这里是控制显示的数量
;                         -- 不管语句怎么变，最后的分号必须带上

3. 查询关键字SELECT

MySQL查询语句也被称为SELECT语句，先来了解一下SELECT的操作：

-- 最简单的MySQL查询语句
SELECT 1 + 1;
-- 猜猜会输出什么?

SELECT最基础的用法就是计算功能，四则运算通通不在话下。但是拿强大的SELECT去做算数运算好像还是大材小用了，下面我们去查询一下数据表吧！

-- 查询数据表的基本语法：
-- SELECT 字段名 FROM 表名;
​
-- 查询stu_info表中的所有内容
SELECT * FROM stu_info;
​
-- 查询stu_info表中stuNum和name字段的内容
SELECT stuNum, `name` FROM stu_info;
​
-- 使用AS给查询到的字段起个别名吧！AS也可以省略不写喔！
SELECT stuNum AS '学号', `name` AS '姓名' FROM stu_info;
SELECT stuNum '学号', `name` '姓名' FROM stu_info;

4. WHERE条件查询

之前的查询都会查出数据表的所有记录对应字段的信息，但是实际工作中，我们往往需要的是数据表中的部分数据，这时候通常会增加一个限制条件，如在某个时间段、数值大于多少等，因而引入了条件查询：

-- 条件查询的基本语法：
-- SELECT 字段名 FROM 表名 WHERE 条件;
​
-- 查询年龄为20岁的学员信息
SELECT * FROM stu_info WHERE age = 20;
​
-- 查询在2000年以后出生的学员信息
SELECT * FROM stu_info WHERE birthday >= '2000-01-01';
​
-- 查询学号在20002和20004之间的学员信息
SELECT * FROM stu_info WHERE stuNum BETWEEN 20002 AND 20004;
​
-- 查询生日为空的学员信息
SELECT * FROM stu_info WHERE birthday IS NULL;

5. 对基础查询和条件查询的理解

1，单独基础查询会显示整个数据表的所有记录所查询字段的信息，相当于在EXCEL中折叠了其它列而单独显示某些列；
2，单独的条件查询会显示数据表中某些记录的完整信息，相当于EXCEL中折叠了其它行而单独显示某些行；
3，联合使用基础查询和条件查询，则可以实现相当于EXCEL中既折叠行又折叠列的操作，显示数据块。

-- 查询年龄为20岁的学员学号和姓名
SELECT stuNum '学号', `name` '姓名' FROM stu_info WHERE age = 20;
​
-- 查询在2000年以后出生的学员姓名
SELECT `name` FROM stu_info WHERE birthday >= '2000-01-01';
​
-- 查询学号在20002和20004之间的学员姓名和年龄
SELECT `name`, age FROM stu_info WHERE stuNum BETWEEN 20002 AND 20004;
​
-- 查询生日为空的学员姓名
SELECT `name` FROM stu_info WHERE birthday IS NULL;

6. GROUP BY分组

分组操作的是查询出来的数据，一般出现在基础查询或条件查询后。分组通常跟聚合函数同时出现，名为分组聚合，意为先分组，再聚合。其功能可以类比excel中的数据透视表。

-- 分组查询一般语法
-- SELECT 字段 FROM 表名 GROUP BY 字段;
​
-- 按照年龄进行分组，查询每个年龄的人数
SELECT age, COUNT(`age`) FROM stu_info GROUP BY age;
​
-- 按照出生的日期的月份分组，查询学生出生月份的分布
SELECT MONTH(birthday) mo, COUNT(`name`) FROM stu_info GROUP BY mo;
​
-- 查询年龄大于18岁的学员信息，并按照出生的日期的月份分组，查询学生出生月份的分布
SELECT MONTH(birthday) mo, COUNT(`name`) FROM stu_info WHERE age > 18 GROUP BY mo;

7. HAVING对分组后的数据做筛选

HAVING也是条件关键字，用于对分组之后的数据进行筛选，因此必须与GROUP BY联用。出于对SQL性能的要求，一般希望在分组之前做好数据筛选，以减少对无效数据的操作，因而使用HAVING的场景并不多。本案例由于数据量有限，以下举出的例子均可以使用WHERE实现，我会将两种实现语句都写出来，仅供参考。需要注意的是，当数据量较大时，两种查询语句的效率还是会有差别的，更推荐使用第二种。最后的最后，还是要强调一下废话了这么多的中心思想：能用WHERE的尽量不要用HAVING！

-- HAVING筛选一般语法
-- SELECT 字段 FROM 表名 GROUP BY 字段 HAVING 筛选条件;
​
-- 按照出生的日期的月份分组，查询学生出生月份的分布，排除掉没有生日信息的同学
-- 使用HAVING实现
SELECT MONTH(birthday) mo, COUNT(`name`) FROM stu_info GROUP BY mo HAVING mo IS NOT NULL;
-- 使用where实现：这里不能使用聚合字段mo，原因是where执行在分组聚合之前，参考SQL函数的执行顺序
SELECT MONTH(birthday) mo, COUNT(`name`) FROM stu_info WHERE birthday IS NOT NULL GROUP BY mo;

8. DISTINCT控制数据唯一性输出

当数据库中存在重复值时，直接查询会造成脏数据，此时需要使用DISTINCT去重。DISTINCT相当于EXCEL中的去除重复行的操作。

DISTINCT的另一个使用场景是对单个字段的去重，如使用COUNT(DISTINCT 字段)的嵌套实现在计数时先将字段数据去重的操作，通常用于分组聚合中对某些字段有重复值不计入的操作，如区域数量、销售经理数量的统计等。

为了演示DISTINCT的用法，这里生成另外一张选课表并添加一波数据，可以看出这个学校的老师有的只教授一门课程，有的教授多门课程。

-- 创建学生选课表
CREATE TABLE IF NOT EXISTS `course`(
    `id` INT(8) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
    `course_num` INT(8) NOT NULL COMMENT '课程编号',
    `course_name` VARCHAR(20) NOT NULL COMMENT '课程编号',
    `teacher` VARCHAR(10) NOT NULL COMMENT '课程编号',
    `stu_num` VARCHAR(5) NOT NULL COMMENT '选课学生学号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生选课情况表';
​
-- 添加学生选课数据
INSERT INTO course(course_num, course_name, teacher, stu_num) 
VALUES(10001, '统计数学', '胡六一', 20001),
      (10001, '统计数学', '胡六一', 20002),
      (10001, '统计数学', '王瘦子', 20003),
      (10001, '统计数学', '王瘦子', 20004),
      (10001, '统计数学', '小铁牙', 20005),
      (10002, '计算机原理', '有正', 20001),
      (10002, '计算机原理', '有正', 20002),
      (10002, '计算机原理', '有正', 20003),
      (10002, '计算机原理', '胡六一', 20005),
      (10003, 'SQL从入门到放弃', '小铁牙', 20001),
      (10003, 'SQL从入门到放弃', '小铁牙', 20003),
      (10003, 'SQL从入门到放弃', '小铁牙', 20004),
      (10003, 'SQL从入门到放弃', '小铁牙', 20005);

-- 查询开始的课程
SELECT DISTINCT course_name from course;
​
-- 查看老师和所教课程的数量
-- 没有去重情况下筛选出来的是选择该老师的次数
SELECT teacher, COUNT(course_name) FROM course GROUP BY teacher;
-- 去重后筛选出来的才是老师教授的课程数量
SELECT teacher, COUNT(DISTINCT course_name) FROM course GROUP BY teacher;
​
-- 查看每门课的任课教师人数
SELECT course_name, COUNT(DISTINCT teacher) FROM course GROUP BY course_name;

9. ORDER BY排序

当需要对查询结果进行排序的时候，需要使用ORDER BY关键字，排序默认升序(ASC 可省略)，当需要降序排列时，可以使用DESC关键字做说明。

ORDER BY排序通常在所有的查询、分组聚合和分组后的筛选完成之后，因而在SQL语句中出现的位置往往靠后，执行的顺序也靠后。

-- 排序的一般语法
-- SELECT 字段 FROM 表名 ORDER BY 字段 ASC/DESC;
​
-- 查询所有学员信息，按年龄从小打到大排序
SELECT * FROM stu_info ORDER BY age;
​
-- 查询所有学员的姓名和年龄，按年龄从大打到小排序
SELECT `name`, age FROM stu_info ORDER BY age DESC;

10. LIMIT限制查询数量

使用LIMIT可以限制查询的偏移和一次查询的记录个数，LIMIT永远出现在SQL语句的最后，执行顺序上也是最后才执行。

-- LIMIT基本语法
-- 这里的m是限制显示的数量，意义为显示前m条记录
-- SELECT 字段 FROM 表名 LIMIT m;
-- 这里的m是限制偏移，n限制显示的数量，意义为从第m+1条开始显示n条记录
-- SELECT 字段 FROM 表名 LIMIT m, n;
​
-- 查询所有学员信息，按年龄从小打到大排序，只显示前三条
SELECT * FROM stu_info ORDER BY age LIMIT 3;
​
-- 查询所有学员信息，按年龄从小打到大排序，显示2-4条
SELECT * FROM stu_info ORDER BY age LIMIT 1, 3;