MYSQL一日游

Table of Contents

以前没怎么用过MYSQL,最近发现招聘要求好多都要求会MYSQL,读书的时候学的是微软家的SQL SERVER,出来工作用的是Postgresql 和大部份时间都是用SQLITE,偶尔还会用一下NOSQL,其中MongoDB用的最多.不过Django的ORM实在是太方便了,导致现在除了基本的 CRUD,增加/删除/修改表的SQL语句读忘的差不多了.而且虽然所每个数据库都在一定程度上遵守SQL标准,但是其实比对一下上面提到 的关系型数据库后你会发现,这简直就跟Lisp一样各种方言.所以我个人喜欢用ORM,这样就可以一份代码多个数据库使用.不过呢,懂 SQL是十分有必要的,而且ORM会有办不到的时候,当然最重要的还是概念,懂了概念后可以在以后不得不换数据库的时候知道怎么使用 文档.鉴于我自己的概念也不是太全,所以学习MYSQL的时候顺便写一篇笔记方便以后查阅,严格上来讲,这篇文章就是我从别的SQL数 据库转到MYSQL的心路里程和复习笔记.注意,本人用的版本是8.0.

SQL分类,有4类,维基百科上讲的很详细,所以大概列个分类然后给上参考链接(真的不是在偷懒)

  1. DDL (Data Definition Language) 通俗地讲就是定义好怎么存数据.详情参考维基百科上的DDL.
  2. DML (Data Manipulation Language) 就是操纵记录的,有增删改查/CRUD(insert/delete/update/select)4种操作.详情参考维基百科上的DML.
  3. DCL (Data Control Language) 就是控制使用者的权限,权限颗粒度非常高.比如可以控制到函数的使用.详情参考维基百科上的DCL.
  4. DQL (Data Query Language) 就是SELECT的语句的详细使用.详情参考维基百科上的DQL.

想查阅具体的语法,请看考文档MYSQL的SQL语法.

由于我严格上来说不是SQL Newbie,只是对MYSQL不了解而已,还是有个概念在的,所以接下来会手动做一下练习来快速上手.

目标

就建一个叫做GAMEDB的数据库,分别有GAME,PUBLISHER,DEVELOPER,PLATFORM,GAMEPLATFORM 5张表来记录游戏,发行商,开发商 ,和游戏平台以及游戏和平台的关系.其中游戏只能游戏只能由一个开发商开发并且由一个发行商发售,开发商和发行商可能是同一个 公司,这个要看开发商是不是属于发行商的旗下的开发团队了.开发商不一定是属于某个开发商的,比如白金工作室.平台就是这个游戏 在什么机器上进行游玩,一般就是手机,PC,主机平台3个大平台,其中主机平台有Nintendo的主机,Sony的PS主机,微软的XBOX主机, 当然这么多年了,每个公司的主机有已经有很多代了,就不列举了.一个游戏可以多平台登录,游戏名字不能一样.

DDL

  1. 创建数据库,并用CHARACTER SET设置gbk编码,当然默认就是utf8编码,也是支持中文的. 

    CREATE DATABASE GAMEDB CHARACTER SET gbk;

  2. 查看数据库 

    SHOW DATABASES;

    这样,你就可以看到有什么数据库了,如果你的数据库新增成功,那么GAMEDB就会出现在上面.

    切换到GAMEDB数据库中, 

    USE GAMEDB;

  3. 创建表和添加主外键约束 

    CREATE TABLE PUBLISHER(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` varchar(50) NOT NULL,
    PRIMARY KEY (`ID`)) ENGINE=InnoDB;

CREATE TABLE DEVELOPER(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` varchar(50) NOT NULL,
    PRIMARY KEY (`ID`)) ENGINE=InnoDB;

CREATE TABLE PLATFORM(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `NAME` varchar(50) NOT NULL,
    PRIMARY KEY (`ID`)) ENGINE=InnoDB;

CREATE TABLE GAME(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `TITLE` varchar(50) NOT NULL,
    `PUBLISHER_ID` int(11) NOT NULL,
    `DEVELOPER_ID` int(11) NOT NULL,
    PRIMARY KEY(`ID`),
    CONSTRAINT `FK_PUBLISHER` FOREIGN KEY (`PUBLISHER_ID`) REFERENCES `PUBLISHER` (`ID`),
    CONSTRAINT `FK_DEVELOPER` FOREIGN KEY (`DEVELOPER_ID`) REFERENCES `DEVELOPER` (`ID`)
) ENGINE=InnoDB;

CREATE TABLE GAMEPLATFORM(
   `GAME_ID` int(11) NOT NULL,
   `PLATFORM_ID` int(11) NOT NULL,
   PRIMARY KEY (`GAME_ID`, `PLATFORM_ID`),
   CONSTRAINT `FK_GAME` FOREIGN KEY (`GAME_ID`) REFERENCES `GAME` (`ID`),
   CONSTRAINT `FK_PLATFORM_ID` FOREIGN KEY (`PLATFORM_ID`) REFERENCES `PLATFORM` (`ID`)
) ENGINE=InnoDB;

ALTER TABLE PUBLISHER ADD UNIQUE (`NAME`);
ALTER TABLE DEVELOPER ADD UNIQUE (`NAME`);
ALTER TABLE PLATFORM ADD UNIQUE (`NAME`);
ALTER TABLE GAME ADD UNIQUE (`TITLE`);

    这里有两个东西是我之前在其他SQL数据库里面没有见过的,第一个是`符号,这个符号是防止字段名字,表或数据库名字用了 SQL的关键字发生冲突,这个例子里面可以不用;第二个就是ENGINE=InnoDB从句,这个用于指定InnoDB做为数据库储存引擎, 这个也是默认的.我还是第一次接触这个MYSQL里面的概念.最后一点,我用的是MYSQL 8.0,对象名是区分大小写的.

  4. 查看表的结构和创建表的SQL语句 DESC GAME; SHOW CREATE GAME;

DML

  1. 插入数据 

    INSERT INTO PLATFORM VALUES (NULL, 'Nintendo Switch');
INSERT INTO PLATFORM (`id`, `name`) VALUES (NULL, "PS4 PRO");
INSERT INTO PLATFORM (`name`, `id`) VALUES ("XBOX Scorpio", NULL);
INSERT INTO PLATFORM VALUES (NULL, 'PC');
INSERT INTO PLATFORM values (NULL, 'Mobile Phone');

INSERT INTO PUBLISHER VALUES (NULL, 'Nintendo');
INSERT INTO PUBLISHER VALUES (NULL, 'Bethesda Softworks');
INSERT INTO PUBLISHER VALUES (NULL, 'SIE');

INSERT INTO DEVELOPER VALUES (NULL, 'Monolith Soft');
INSERT INTO DEVELOPER VALUES (NULL, 'Intelligent System');
INSERT INTO DEVELOPER VALUES (NULL, 'BattleCry Studios');
INSERT INTO DEVELOPER VALUES (NULL, 'Arkane');
INSERT INTO DEVELOPER VALUES (NULL, 'Bend Studio');
INSERT INTO DEVELOPER VALUES (NULL, 'Nintendo');

INSERT INTO GAME VALUES (NULL, 'Fire Emblem: Three Houses', 1, 2);
INSERT INTO GAME VALUES (NULL, 'The Legend of Zelda: Breath of the Wild', 1, 6);

  2. 删除手机平台条目 

    DELETE FROM PLATFORM where name='Mobile Phone';

  3. 更新PS4 PRO平台条目 

    UPDATE PLATFORM SET name="PlayStation 4 PRO" WHERE name="PS4 PRO";

DQL

  1. 查询,这一部分我看在跟SQLITE的差不多,而且没什么好写的,写起来还繁琐,所以大概总结一下几种连接就过了
    • 连接

      • 交叉连接 (cross join):查询结果为多张的乘积,下面3种写法的作用是一样的.

        SELECT * FROM GAME CROSS JOIN DEVELOPER CROSS JOIN PUBLISHER;

        SELECT * FROM GAME JOIN DEVELOPER JOIN PUBLISHER;

        SELECT * FROM GAME, DEVELOPER, PUBLISHER;

      • 内连接 (inner join): 查询结果为多个指定关系的表的数据,这里把主表的外键引用表的信息也查出来,这个例子非常简单, 主表是GAME,DEVELOPER和PUBLISHER是附表,只要GAME的PUBLISHERID和PUBLISHER的ID相等并且GAME的DEVELOPERID 和DEVELOPER的ID相等的数据就是满足条件.

        SELECT GAME.ID, TITLE, DEVELOPER.NAME AS DEVELOPER, PUBLISHER.NAME AS PUBLISHER FROM GAME INNER JOIN DEVELOPER ON DEVELOPERID=DEVELOPER.ID INNER JOIN PUBLISHER ON PUBLISHERID=PUBLISHER.ID;

      • 外连接 (outer join)

        • 左外连接 (left outer join),以最第一张表的数据长度为准,其他两种表超出这个长度的数据就不显示,小于这个长度的 表的数据列显示NULL

          SELECT * FROM GAME LEFT JOIN DEVELOPER ON GAME.PUBLISHERID=DEVELOPER.ID LEFT JOIN PUBLISHER ON GAME.PUBLISHERID=PUBLISHER.ID;

        • 右外连接 (right outer join),这个以最后一张表的长度为准,跟上面完全相反.

          SELECT * FROM GAME LEFT JOIN DEVELOPER ON GAME.PUBLISHERID=DEVELOPER.ID LEFT JOIN PUBLISHER ON GAME.PUBLISHERID=PUBLISHER.ID;

        • 最后左右外连接是可以混合使用的.

      • 联合(union, union all)

        把多个表的查询结果放在一张表内,有个前提就是结果的列数量要相同,最后结果的列名就是第一张表结果的列名.

        SELECT ID FROM PUBLISHER UNION SELECT NAME FROM DEVELOPER UNION SELECT TITLE FROM GAME;

        union和union all的区别就是,前者会把重复的结果去掉,后者没有,可以自己执行以下两句感受一下,两者会差一条数据.

        SELECT NAME FROM PUBLISHER UNION SELECT NAME FROM DEVELOPER;

        SELECT NAME FROM PUBLISHER UNION ALL SELECT NAME FROM DEVELOPER;

DDL EXT,由于这部分是我平常不怎么注重的内容,所以要单独写出来,其实这部分对于如何优化也是有点帮助的(虽然有人说性能是 部署出来的,但是从开发上优化也是有必要的)

  1. 把GAME表的TITLE字段定义为唯一索引,因为找游戏一般都是根据名字并且名字是唯一的,适合出现在WHERE从句中,这种是单列索引. 

    CREATE UNIQUE INDEX INDEX_TITLE ON GAME (TITLE);

    • 什么时候才用索引呢?

      一般在表中的数据量比较大的时候才用索引,不使用索引进行搜索的话,假设有N条数据,那么时间复杂度就是O(N),也就是顺序 查找,通俗点就是一个单层循环到底,或者用递归的思想就是比对完为止;而使用了索引,有两种典型的索引B-Tree和Hash,两者 的时间复杂度分别是O(logN)和O(1),就算是B-Tree索引那也是比O(N)好多了.所以索引是一种数据结构,如果数据表经常进行 CUD 3种操作,那么也要频繁的重新生成这个数据结构,这样CUD的操作时间也会增加,而且索引还需要额外的空间进行储存,所以 如果表要频繁更新,那么就不要建索引了.

    • 怎么使用索引呢?

      官方文档有说如何正确使用, How MySQL Uses Indexes 和 Comparison of B-Tree and Hash Indexes.同样网上也有 关于很多MySQL优化的文章和书,这里就不详细说了,知道如何找解决问题的方法就可以了.

  2. 创建一个只有ID和TITLE两个字段的视图 

    CREATE OR REPLACE VIEW GAME_VIEW AS SELECT ID, TITLE FROM GAME;

SELECT TITLE FROM GAME_VIEW WHERE ID=1;
    • 视图优点
      1. 直接把查询结果作为一张表(当然这不是真的表),把不必要的结果过滤掉.
      2. 视图没有使用过滤掉的字段的能力,也就是说上面的查询语句中的WHERE不能出现DEVELOPERID或者PUBLISHERID. 可以限制用户访问被允许的结果,而且还能精确到某个行某个列.如果GAME表新增了列,也不会对视图造成影响.
      3. 视图GAMEVIEW与GAME表中的数据能够保证一致,你可以通过在GAMEVIEW进行DML语句来更新GAME表,反过来也是一 样.

  3. 创建一个储存过程,利用WHILE循环在PLATFORM表中插入n条数据 

    DELIMITER //
CREATE PROCEDURE insert_n_datas(in times int(2))
BEGIN
  DECLARE counter INT DEFAULT times;
  DECLARE maxid INT;
  WHILE counter > 0 DO
    SELECT max(id) INTO maxid FROM PLATFORM;
    SET counter = counter -1;
    INSERT INTO PLATFORM VALUES (NULL, CONCAT("Phone", maxid));
  END WHILE;
END //
DELIMITER ;

    大概说明一下,

    • "DELIMITER "把SQL的结束符号变成""了,后面又把它变会";".
    • "CREATE PROCEDURE"就是创建储存过程,"insertndatas"是过程的名字
    • "(in times int(2))"的"in"代表times是input参数,默认就是in,所以可以不用写, 如果需要一个变量做为返回值,把in改为out.后面的"int(2)"是类型,这里是显示长度为2的整数.
    • 还有DECLARE只能用在储存过程里面(文档说的是BEGIIN…END组合语句中的第一句, 不过不在储存过程中使用就报错了),用于声明变量局部变量.
    • "CONCAT("Phone", maxid)"把"Phone"和整数拼接
    • 更多参考
      1. CREATE PROCEDURE and CREATE FUNCTION Syntax
      2. Stored Programs Defining
      3. Flow Control Statements

  4. 创建一个事件,每5秒插入一条新的记录到PLATFORM表中 

    CREATE EVENT insert_data_every_5s
ON SCHEDULE
EVERY 5 SECOND
DO
INSERT INTO PLATFORM VALUES (NULL, CONCAT("time: ", NOW()));

    然后启用事件调度器 

    SET GLOBAL event_scheduler = 1; // 0 是关闭
ALTER EVENT insert_data_every_5s disable; // 也可以在启用调度器的情况下单独关闭一个事件

    这样每隔5秒就会在PLATFORM表中插入一条数据.

  5. 创建一个触发器,设定在PLATFORM表中每删除一条记录就会在DEVELOPER表中插入一条当前时间的记录 

    DELIMITER //
CREATE TRIGGER insert_new
AFTER DELETE ON PLATFORM FOR EACH ROW
BEGIN
INSERT INTO DEVELOPER VALUES (NULL, CONCAT("time: ", NOW()));
END //
DELIMITER ;

    当删除PLATFORM表中数据的时候,DEVELOPER表就会插入一条数据.要注意,是不能够在同一张表上进行设定以及触发,也就是 说不能在PLATFORM设定触发器,触发后的任务还是作用在PLATFORM表上.

    如果想了解更多的触发顺序请看这个参考资料.

  6. 事务,首先等了解一下什么是事务.有时候插入新的记录要求先在其它表中插入相应数据才可以,比如这文章的例子中,GAME表中有 两个外键,分别引用自PUBLISHER和DEVELOPER表,所以新增一条GAME表的记录就有可能要求分别给PUBLISHER和DEVELOPER表增 加记录.不过可能会出现一种情况,就是先给PUBLISHER插入数据,轮到DEVELOPER的时候出错了,这个时候就多了一条没有的数据了, 你可能会想如果这整一个过程是一个最小单元就好了,这时候事务就符合你的需求了,它能够在整个过程完成后才提交到数据库中. 

    BEGIN; // or START TRANSACTION;
INSERT INTO PUBLISHER VALUES (NULL, "BANDAI NAMCO");
INSERT INTO DEVELOPER VALUES (NULL, "Anyway");
SELECT max(id) INTO @pid FROM PUBLISHER;
SELECT max(id) INTO @did FROM DEVELOPER;
INSERT INTO GAME VALUES (NULL, "Dark Soul 3", @pid, @did);

    虽然用你SELECT查看数据表是有新的记录,但其实到这里数据还没真正提交到数据上,假设现在发现不存在"Anyway"的开发商, 那么我们就不提交了,用ROLLBACK回滚. 

    ROLLBACK; // 想要提交就要COMMIT;

    再次SELECT的时候就发现新增的三条记录都不见了.

  7. 最后一点补充,就是MySQL的SHOW语句,是一个十分有用的助手语句,可以查看数据库,表,表的结构等等信息,由于这部分是属于 MYSQL里面的,单独给出个文档链接就好了.

DCL 就不多说了,这个直接看文档就可以了.

这篇笔记的SQL语句都是没有问题的,毕竟是我边敲边写的,在头脑有概念的情况下实践是最快的学习方法了.顺便把以前不太了解 的概念也一次清了,估计很长时间都不用翻基础的书了.优化这块我也不太了解,以后有时间就去读一下相关的书好了.(PS:其实复 习很快,不过写笔记真的要不少时间).

进阶 

开始写于 2019/3/21

其实上面的部分已经能满足日常的普通任务了,但是技术这块上不能只满足目前的一点知识,特别对于想在这块长时间发展的人来说,于是就有这一部分的更新.

之所以在这里写新内容而不是开新文章那是因为方便以后的查阅,我不否认我的记忆力不行,我也不是什么天才,不是什么学富五车,单纯一个普通人,既然记忆力不够用那就跟需要找方法解决.

以前的自己只满足于能完成当前任务,虽然能完成当前任务,但是却做不了什么高难的任务,每次遇到点陌生的问题都会耗半天时间,效率实在低下,所以进阶部分的笔记是十分有必要的.

MySQL 的数据类型

  • 数字类型 (Numeric Type)

Author: saltb0rn (asche34@outlook.com)

Date: 2018-08-14

Emacs 28.2 (Org mode 9.5.5)

Validate