MySQL学习笔记

MySQL学习笔记

created by cjk on 2016.12.7

一、数据库操作

  • 连接数据库
mysql -hlocalhost -P3306 -uroot -p	# -h指定主机名称 -P指定端口号 -u指定用户名称 -p指定用户密码
  • 查看当前所有数据库
SHOW DATABASES;
  • 查看指定前缀的数据库
SHOW DATABASES LIKE 'xxx%';
  • 创建数据库
CREATE DATABASE db_name;
  • 查看表选项(数据库创建信息包含默认字符集信息等)
SHOW CREATE DATABASE db_name;
  • 删除数据库
DROP DATABASE db_name; # 不存在则报错
DROP DATABASE IF EXISTS db_name; # 存在即删除，不存在也不报错

  • 如何修改数据库的名字：

    # 修改数据库名称的过程实际上是新建一个数据库，然后将原有数据移至新的数据库中，再删除原有数据库
# 以下方法仅针对于表的数量较少或者单表所在的数据库重命名操作，对于数据量较大的表所属数据库重命名参考SQL编程，写一个SQL脚本来批量完成此操作
# 1. 新建数据库
CREATE DATABASE new_db_name;
# 2. 将原数据库数据导入
RENAME TABLE old_tb_name TO new_db_name.new_tb_name;
# 3. 删除原数据库
DROP DATABASE old_db_name;

二、表操作

  1. 结构操作

    • 指定表相关联的默认数据库
    USE db_name;
    • 创建表
    CREATE TABLE tb_name (id INT PRIMARY KEY, name VARCHAR(64) NOT NULL, age INT);
    • 查看当前数据库所有表
    SHOW TABLES;
    • 查看指定前缀的表
    SHOW TABLES LIKE xxx%;
    • 查看表选项
    # 结束符为;常规的结果展示方式，分隔符不展示结果行数
SHOW CREATE TABLE tb_name;
# 结束符为\G，分隔符会展示结果行数，更易读
SHOW CREATE TABLE tb_name\G
    • 删除表
    # 若不存在即报错
DROP TABLE tb_name;
# 存在即删除，不存在也不报错
DROP TABLE IF EXISTS tb_name;
    • 修改表选项
    ALTER TABLE tb_name [command];
# 常用command如下
# 向表增加一个额外的字段
ALTER TABLE tb_name ADD COLUMN extra VARCHAR(256);
# 更改字段定义
ALTER TABLE tb_name MODIFY COLUMN extra INT NOT NULL;
# 删除某个字段
ALTER TABLE tb_name DROP COLUMN extra;
# 为某个字段增加索引，括号内可以写多个字段组成联合索引
ALTER TABLE tb_name ADD CONSTRAINT my_unique_key UNIQUE(test_field);
ALTER TABLE tb_name ADD PRIMARY KEY (test_field);
# 删除某字段上的索引
ALTER TABLE tb_name DROP INDEX my_unique_key; # 删除唯一索引
ALTER TABLE tb_name DROP PRIMARY KEY; # 删除主键索引
    • 修改表名
    RENAME TABLE old_tb_name TO new_tb_name;
    • 修改多个表名
    RENAME TABLE old_tb_name TO new_tb_name, .........;
    • 跨数据库重命名表
    # 将原数据库中的表移动到指定的新数据库中并重命名表
RENAME TABLE old_tb_name TO db_name.new_tb_name;

  2. 列操作

    • 添加新列
    ALTER TABLE tb_name ADD COLUMN field_name field_type;
# 示例: 向test_table表添加test字段并设置类型为varchar长度为256位，默认非空
ALTER TABLE test_table ADD COLUMN test VARCHAR(256) NOT NULL;
    • 删除列
    ALTER TABLE tb_name DROP COLUMN field_name;
# 示例: 删除test_table表test字段
ALTER TABLE test_table DROP COLUMN test;
    • 修改列定义
    ALTER TABLE tb_name MODIFY COLUMN field_name field_type;
# 示例: 修改test_table表test字段为int类型
ALTER TABLE test_table MODIFY COLUMN test INT;
    • 重命名并修改列定义
    ALTER TABLE tb_name CHANGE COLUMN old_field_name new_field_name field_type;
# 示例: 将test_table表test字段名称修改为test2并指定类型为varchar类型长度64位，默认非空，设置为唯一索引
ALTER TABLE test_db CHANGE COLUMN test1 test2 VARCHAR(64) NOT NULL UNIQUE;
    • 查看列定义
    # 完整写法
DESCRIBE tb_name;
# 简写，常用
DESC tb_name;

三、数据操作

  • 添加指定字段的数据
# 要求：字段与值对应
INSERT INTO tb_name (field_list) VALUES(value_list); 
# 示例: 向test_table表插入一条数据，设置name字段值为zhangsan，age字段值为23
INSERT INTO test_table (id, name, age) VALUES(NULL, 'zhangsan', 23);
  • 表中所有字段添加数据
# 要求：字段与值对应
INSERT INTO tb_name VALUES(value_list);
# 示例: 向test_table表中插入一条完整数据(所有字段都有值)
INSERT INTO test_table VALUES(NULL, 'lisi', 25);
  • 查数据
# 字段列表可用 * 代替，表示显示所有字段，查询条件可用 1 代替，表示查找所有
# 注意: 尽量不要使用不带条件的查询操作，尤其是在数据量庞大的情况下
SELECT field_list FROM tb_name WHERE conditions;
# 示例: 查找test_table的id与name字段，设置查询年龄age为22
SELECT id, name FROM test_table WHERE age = 22;
  • 删除数据
# 删除条件用 1 代替，表示删除所有
# 注意: 删除数据要谨慎，可以的情况下，先做备份再删除，使用 1 作为删除条件一般情况下是绝对禁止的，测试情况下可以使用
DELETE FROM tb_name WHERE conditions;
# 示例: 删除test_table表姓名name为zhangsan的记录
DELETE FROM test_table WHERE name = 'zhangsan';
  • 修改字段的值
# 条件为 1 表示将所有行的某一字段的值用新值代替
UPDATE tb_name SET field_name = new_value WHERE conditions;
# 示例: 将test_table表姓名为zhangsan且id为3的记录修改年龄age为88
UPDATE test_table SET age = 88 WHERE name = 'zhangsan' AND id = 3;
  • 修改记录的多个字段的值
UPDATE tb_name SET field_name = new_value, field_name1 = new_value1, .... WHERE conditions;
# 示例: 将test_table表id为1的记录姓名修改为player，年龄修改为88岁
UPDATE test_table SET age = 88, name = 'player' WHERE id = 1;

四、校对规则

  • 所谓的校对规则就是字符集内用于比较字符的一套规则，为便于理解，下述统一以排序规则代以称呼。

  • 查看字符集信息

# 此指令除了可以看到数据库字符集外，还可以看到服务器使用的字符集以及客户端使用的字符集信息等
# 此外，如果只是查看数据库的字符集可以通过SHOW CREATE DATABASE db_name来查看
SHOW VARIABLES LIKE 'character_set%';
  • 查看字符集排序规则
# 此指令列出的是当前支持的所有字符集排序规则，一般情况下，字符集使用的是utf8，排序规则使用的是utf8_general_ci，但中华文化博大精深，如果考虑到偏僻字符及某些特殊符号的情况三个字节的utf8就无法使用了，可以使用utf8mb4来代替utf8，对应排序规则一般使用的是utf8mb4_general_ci。日常使用中utf8已经足够。
# 排序规则说明: ci不区分大小写  cs区分大小写  bin二进制格式比较
SHOW COLLATION;
  • 查看某字符集有哪些排序规则
SHOW COLLATION LIKE character_set;
# 示例: 查看gb2312字符集有哪些排序规则，本质上是使用查看所有支持的字符集排序规则，通过like条件筛选出结果，%表示后续匹配任意多个任意字符，如果是_表示后续只匹配一个任意字符
SHOW COLLATION LIKE 'gb2312%';
  • 在创建表时指定字符集排序规则
CREATE TABLE tb_name (field_list) CHARACTER SET character_set COLLATE character_collation;
# 示例: 创建test_table表，设置字符集为utf8，排序规则为utf8_general_ci
# 值得注意的是，在创建表未指定字符集时，将会应用数据库设置的字符集，但是如果创建数据库时使用utf8字符集，创建表时依旧可以手动指定表使用gbk的字符集，最终表使用的还是gbk的字符集。也就是说，它们存在一个优先级的关系，但互相之间并不受影响。
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, test_field VARCHAR(64)) CHARACTER SET utf8 COLLATE utf8_general_ci;
  • 修改表的字符集
ALTER TABLE tb_name CHARACTER SET = character_set;
# 示例: 修改test_table表的字符集为utf8
ALTER TABLE test_table CHARACTER SET = 'utf8';
  • 修改表的排序规则
ALTER TABLE tb_name COLLATE = character_collation;
# 示例: 修改test_table表的排序规则为utf8_general_ci
ALTER TABLE test_table COLLATE = 'utf8_general_ci';
  • 将表中的数据使用某一列按照校对规则排序输出
SELECT * FROM tb_name WHERE conditions ORDER BY field_name;
# 示例: 将test_table表中id大于5的记录按姓名name升序排序输出
SELECT * FROM test_table WHERE id > 5 ORDER BY name;
SELECT * FROM test_table WHERE id > 5 ORDER BY name ASC;
# 示例: 将test_table表中id大于5的记录按姓名name降序排序输出
SELECT * FROM test_table WHERE id > 5 ORDER BY name DESC;

五、数值类型简介

  • 整型

    • 在MySQL中整型类型有以下几种：
      1. int类型：最常用的类型，有符号的范围是-2^31 到 2^31 - 1的整型数据，无符号的范围是 0 到 2^32-1 。大小为 4 个字节即32位，有符号由于其中一位用于符号位，可用数值位只有31位。
      2. tinyint类型：最小的整型，有符号的范围是-128 到 127，无符号的范围是 从 0 到 255 的整型数据。大小为 1 个字节即8位，有符号由于其中一位用于符号位，可用数值位只有7位。
      3. smallint类型：一个小整数。有符号的范围是-2^15 到 2^15 - 1的整型数据，无符号的范围是 0 到 2^16-1 。大小为 2 个字节即16位，有符号由于其中一位用于符号位，可用数值位只有15位。
      4. mediumint类型：一个中等大小整数，有符号的范围是 -2^23 到 2^23-1 的整型数据，无符号的范围是 0 到 2^24-1。 大小为3个字节，即24位，有符号由于其中一位用于符号位，可用数值位只有23位。
      5. bigint类型：大整型数，从 -2^63 到 2^63-1 的整型数据，无符号的范围是 0 到 2^64-1 。大小为 8 个字节，即64位，有符号由于其中一位用于符号位，可用数值位只有63位。
    • 除此之外，整型还可以在其后定义一些额外的属性，如空位零填充，并指定为无符号等
    # 注意此处指定了zerofill之后，字段将会默认为无符号类型，5为指定的显示的长度，注意，是显示长度，不是大小，字段的大小早已由类型确定，如INT(5)类型指定显示长度是5，如果将该字段存入一个55那么，在数据库中该字段的值为00055，显示长度为5，实际长度为2，其余三位由于指定了zerofill，因此会使用0来填充，但是你依然可以向该字段中添加超出显示长度的值，比如555555，因为unsigned int的最大值是 2^32-1 远大于此值。
# 向test_table中添加test字段，类型为无符号整型，空位使用零填充
ALTER TABLE test_table ADD COLUMN test INT(5) ZEROFILL;
# 此种方式定义效果同上
ALTER TABLE test_table ADD COLUMN test INT(5) UNSIGNED ZEROFILL;
# 向test_table中添加test字段，类型为无符号整型
ALTER TABLE test_table ADD COLUMN test INT UNSIGNED;

  • 布尔型

    • 在MySQL中，Bool 类型使用tinyint(1) 指定值为0或1来代替

  • 浮点数

    • 在MySQL中浮点数的类型有两种
      1. float类型：表示有效位在6位左右，可能产生精度丢失。
      2. double类型：表示有效位在16位左右，可能产生精度丢失。
    • 浮点数可以指定数据范围。如设置字段类型为double浮点型，设置整数范围为10位，小数范围为2位
    # 向test_table表添加test字段，类型为double型，其中整数位占10位，小数位占2位
ALTER TABLE test_table ADD COLUMN test DOUBLE(10, 2);
    • 数值可以使用科学计数法来表示。如11442.41546可写为0.1144241546E5

  • 定点数

    • 在MySQL中定点数使用decimal表示，字节长度变化，一般用于精度要求非常高的计算中，支持科学计数法。
    • 定点数也可以指定数据范围。如设置字段类型为定点型decimal，指定数据范围为10位，小数范围2位
    # 向test_table表添加test字段，类型为decimal型，其中整数位占10位，小数位占2位
ALTER TABLE test_table ADD COLUMN test DECIMAL(10, 2);

  • 日期时间类型

    • 在MySQL中，日期时间类型有以下几种

      1. 日期时间类型：占有8字节，范围：1000-01-01 00:00:00 至 9999-12-31 23:59:59，数值格式：'YYYY-MM-DD HH:MM:SS'
      # 向test_table表添加test字段，类型为datetime
ALTER TABLE test_table ADD COLUMN test DATETIME;
      1. 时间戳类型：占用4字节，范围：1970-01-01 00:00:00 至 2038-01-19 03:14:07，数值格式：'YYYY-MM-DD HH:MM:SS'
      # 向test_table表添加test字段，类型为timestamp
ALTER TABLE test_table ADD COLUMN test TIMESTAMP;
      1. 日期类型：占用3个字节，范围：1000-01-01 至 9999-12-31，数值格式：'YYYY-MM-DD'
      # 向test_table表添加test字段，类型为date
ALTER TABLE test_table ADD COLUMN test DATE;
      1. 时间类型：占用3个字节，范围：-838:59:59 至 838:59:59 可以表示时间段，过去时间为负，数值格式：'HH:MM:SS'，另一格式 'D HH:MM:SS'
      # 向test_table表添加test字段，类型为time
ALTER TABLE test_table ADD COLUMN test TIME;
      1. 年类型：占用一个字节，范围：1901 至 2155， 数值格式：'YYYY'
      # 向test_table表添加test字段，类型为year
ALTER TABLE test_table ADD COLUMN test YEAR;

    • 日期时间类型支持0值，时间戳不支持，例如，表示2016年4月整月可以用 2016-04-0 来表示。年份使用 0 值可以用 0000-00-00 来表示没有确切的时间。

    • 最常用的类型为日期时间类型 datetime ，以及时间戳类型 timestamp ，但我们现在的时间已经接近于时间戳的最大值，推荐使用 datetime 类型代替。其他的几种用法较为少见。

  • 字符串类型

    • 在MySQL中，字符串类型有以下几种
      1. char(M)：固定长度，M指定字符个数，最大长度为255。长度不确定的情况下可能存在空间浪费的情况。
      2. varchar(M)：可变长度，M指的是最大字符个数，最大长度为65535，但记录字符个数占用1-2个字节，存储null 值个数占用1个字节，因此，保存数据一般只用到65532个字节。大小根据实际情况确定，较为灵活，推荐使用。
      3. text：不指定长度，自动指定长度，分为tinyText，text，mediumText，longText,最大长度分别为28+1，216+2，224+3，232+4，不统计当前占用的字节。不常使用，长文本通常是以文件的形式保存，数据库中通常只保存文件路径，其他媒体文件同理。

六、字段限制

  • 空或非空限制
# 创建test_table表，指定年龄字段age为非空正整型，邮件地址email为可空varchar，长度64位
CREATE TABLE test_table(id INT PRIMARY KEY, age INT UNSIGNED NOT NULL, email VARCHAR(64) NULL);
  • 非空且设置默认值
CREATE TABLE test_table(id INT PRIMARY KEY, age INT UNSIGNED NOT NULL DEFAULT 18, email VARCHAR(64) NOT NULL DEFAULT 'test@example.com');
  • 主键： 用来唯一标识某条记录，主键可以由某字段或字段的集合来组成，但是要求不能重复，主键可以是真实的实体属性，但是常用的解决方案是利用一个与实体信息不相关的属性作为唯一标识。
# 设置方式一，优点:书写流畅简单。缺点:这种写法只支持单字段主键
CREATE TABLE test_table (id INT PRIMARY KEY);
# 设置方式二，优点:支持设置多字段为联合主键。缺点:书写不是那么简便
CREATE TABLE test_table (id INT, PRIMARY KEY(id));
CREATE TABLE test_table (id1 INT, id2 INT, PRIMARY KEY(id1, id2));
  • 主键设置自动增长
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT);
  • 修改自动增长的起始数值
# 如果修改的自动增长起始数值小于当前最大的值，那么自动增长还是以当前最大值开始自增。
ALTER TABLE test_table AUTO_INCREMENT 10;

七、数据间的关系

  1. 一对一

    • 常用情况：常用的数据与不常用的数据的分离，储存在两个不同的表中。
    • 例子：学生常用信息有姓名，性别，不常用信息有家庭住址等，因此可以分成两个表来储存。
    • 实现：记录的主键值等于另一个关系表的主键值。

  2. 一对多

    • 常用情况：一个表中的一条记录对应另一个表的多条记录。
    • 例子：一个班级对应有多个学生。
    • 实现：外键一般设置在多的一方，指向少的一方的主键，如将学生表中设置外键class_id指向班级表的主键id。这种情况下，需要额外添加一个字段class_id，确定该学生所属哪个班级。否则在关联查询时将无法根据关联关系查询出数据。同时，在使用外键约束的情况下，该class_id必须在班级表的主键id中可以找到。

  3. 多对多

    • 常用情况：一个表中的一条记录对应另一个表的多条记录，另一个表中的一条记录同样对应本表中的多条记录。
    • 例子：一个教师对应有多个教课班级，且一个班级也有多个教师教课。
    • 实现：使用教师-班级关系表来储存两个表之间的关系，分解成两个一对多的关系，如关系表中设置指向教师表主键的外键teacher_id和指向班级表主键的外键class_id。这种情况下，需要插入关系表的记录，通过这个关系表中的class_id和teacher_id两个字段，表明教师与班级之间的关系。多对多关系处理起来相对较为复杂，说起来比较笼统，还是以图说话吧。
    • 教师表
    CREATE TABLE teacher (id INT PRIMARY KEY AUTO_INCREMENT, teacher_name VARCHAR(32));

    • 班级表
    CREATE TABLE class (id INT PRIMARY KEY AUTO_INCREMENT, class_name VARCHAR(64));

    • 教师-班级关系表
    # 此处建立了两个外键，teacher_id指向teacher表的主键id，class_id指向了class表的主键id
CREATE TABLE teacher_class_rel (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    teacher_id INT, class_id INT, 
    FOREIGN KEY(teacher_id) REFERENCES teacher(id),
    FOREIGN KEY(class_id) REFERENCES class(id)
);

    • 此时，我们向teacher表中添加三位老师
    INSERT INTO teacher VALUES (null, '老子');
INSERT INTO teacher VALUES (null, '孔子');
INSERT INTO teacher VALUES (null, '荀子');

    • 再向class表中添加两个班级
    INSERT INTO class VALUES(null, '国学1班');
INSERT INTO class VALUES(null, '国学2班');

    • 那么，表示老子教国学1班和国学2班，孔子教国学2班，荀子教国学1班的逻辑体现在关系表中如下：
    INSERT INTO teacher_class_rel (id, teacher_id, class_id) VALUES(null, 1, 1);
INSERT INTO teacher_class_rel (id, teacher_id, class_id) VALUES(null, 1, 2);
INSERT INTO teacher_class_rel (id, teacher_id, class_id) VALUES(null, 2, 2);
INSERT INTO teacher_class_rel (id, teacher_id, class_id) VALUES(null, 3, 1);

    • 这样，一旦教师与班级的关联关系建立起来，我们查找关联数据就会很简单。如，我们需要查找国学1班有哪些老师教课
    # 下面是一个嵌套子查询，先在class班级表中使用班级名称为国学1班作为检索条件找出国学1班的主键id，根据这个主键id再去关联表中查询关联的教师id，最后通过教师id在教师表中就可以找到教师的名字了。
SELECT teacher_name FROM teacher WHERE id IN (
    SELECT teacher_id FROM teacher_class_rel WHERE
    class_id = (
        SELECT id FROM class WHERE class_name = '国学1班'
    )
);

    • 查找老子教了哪些班级
    # 下面是一个嵌套子查询，先在teacher教师表中使用教师名称为老子作为检索条件找出老子的主键id，根据这个主键id再去关联表中查询关联的班级id，最后通过班级id在班级表中就可以找到班级名称了。
SELECT class_name FROM class WHERE id IN (
    SELECT class_id FROM teacher_class_rel WHERE
    teacher_id = (
        SELECT id FROM teacher WHERE teacher_name = '老子'
    )
);
  • 以上设计的是一个很简单的多对多的关系表，实际过程中可能出现比这个更为复杂的多对多的关系，设计的便捷方法：micrsoft visio来设计表与表之间的关系，再在数据库实现。
  • 常用的通常是一对多的关系设计，一对一与多对多涉及的情况都比较少。

八、外键

  • 定义：如果表的某个字段使用了foreign key约束并指向另一个表的主键，就称该字段为该表的外键。被指向的表称为主表，也叫父表，负责指向的表称之为从表，也叫子表。
  • 作用：用来约束处于关系内的表。且可以设置在删除或更新主表记录时，从表进行的操作。
  • 设置外键：
# 当某个字段设置外键之后，外键将无法添加对应主表字段中没有值
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field)
);
# 示例: 下面建立了两张表，主人表person，宠物狗表dog，一个主人可以拥有多只宠物狗，根据前面所说的，这种情况属于一对多的关系，因此我们的外键应该是建立在多的一方，也就是宠物狗表，其中person_id就是外键，指向主人表person的主键id。需要注意的是，外键定义中的person_id外键字段必须要先定义，不能直接使用 FOREIGN KEY(person_id)来确定外键，同时指向的主表person表以及对应字段id也必须存在。
CREATE TABLE person (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32));
CREATE TABLE dog (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(16), 
    person_id INT, 
    FOREIGN KEY(person_id) REFERENCES person(id)
);
  • 修改外键
  • 修改外键的原理其实时先删除表上原有的外键约束，再创建新的外键约束。那么问题来了，我们如何删除表上已经定义的外键？如果是根据外键名称删除，但是之前我们在定义外键的时候并没有指定外键名称怎么办？其实，通过查看表的创建信息就可以看到外键的名称是什么了，它是数据库自动分配的名称。
SHOW CREATE TABLE dog;
  • 下图红色标记的位置就是外键的名称

ALTER TABLE tb_name DROP FOREIGN KEY (foreign_key_name);  
# 示例: 以上图的外键为例，删除该外键
ALTER TABLE dog DROP FOREIGN KEY dog_ibfk_1;
# 此时创建外键时外键名称没有指定
ALTER TABLE tb_name ADD CONSTRAINT FOREIGN KEY (foreign_key_field) REFERENCES master_table_name(associated_field);
# 此时创建外键时指定外键名称foreign_key_name，注外键名称不是随便命名的，推荐以下命名方式: fk__从表名称__从表外键字段名称__主表名称__主表关联字段名称，这样只要看到外键名称一眼就能识别出它的作用
ALTER TABLE tb_name ADD CONSTRAINT foreign_key_name FOREIGN KEY (foreign_key_field) REFERENCES master_table_name(associated_field);
# 示例: 以上图dog表为例，在person表新建s_id字段，这里注意了，引用字段必须具备唯一属性，之前指向id由于id是主键，因此具备唯一性，如果s_id字段不是唯一的，那么还需要将其设定为唯一索引，除此之外，外键与被引用字段的类型必须一致。否则会出现: ERROR 1215 (HY000): Cannot add foreign key constraint
ALTER TABLE person ADD COLUMN s_id INT;
# 设置主表person的s_id字段为唯一索引
ALTER TABLE person ADD CONSTRAINT un__person__s_id UNIQUE(s_id);
# 使dog表的person_id外键指向person表的s_id字段，同时指定外键名称为fk__dog__person_id__person__s_id
ALTER TABLE dog ADD CONSTRAINT fk__dog__person_id__person__s_id FOREIGN KEY (person_id) REFERENCES person(s_id);

  • 外键的关联动作

    1. 设置级联操作 casecade
    2. 设置为空 set null
    3. 拒绝主表的操作 restrict

  • 设置外键级联触发事件

    1. 更新时 on update
    # 主表引用字段更新时，从表外键级联更新
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON UPDATE CASCADE
);
# 主表引用字段更新时，从表外键设置为NULL
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON UPDATE SET NULL
);
# 主表引用字段更新时，拒绝操作
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON UPDATE RESTRICT
);
# 示例: 在创建表时指定主表引用字段更新时，从表外键级联更新
# 创建主表tb_test_master
CREATE TABLE tb_test_master (
	id INT PRIMARY KEY AUTO_INCREMENT
);
# 创建从表tb_test_slave，m_id字段设置外键，关联tb_test_master表的主键id字段，关联字段更新时级联更新外键
CREATE TABLE tb_test_slave (
	id INT PRIMARY KEY AUTO_INCREMENT,
    m_id INT,
    FOREIGN KEY(m_id) REFERENCES tb_test_master(id) ON UPDATE CASCADE
);
# 示例: 在创建表后指定主表引用字段更新时，从表外键级联更新
# 创建主表tb_test_master
CREATE TABLE tb_test_master (
	id INT PRIMARY KEY AUTO_INCREMENT
);
# 创建从表tb_test_slave
CREATE TABLE tb_test_slave (
	id INT PRIMARY KEY AUTO_INCREMENT,
    m_id INT
);
# 创建tb_test_slave表外键关联，m_id字段设置外键，关联tb_test_master表的主键id字段，关联字段更新时级联更新外键
ALTER TABLE tb_test_slave ADD CONSTRAINT fk__tb_test_slave__m_id__tb_test_master__id FOREIGN KEY (m_id) REFERENCES tb_test_master(id) ON UPDATE CASCADE;
    1. 删除时 on delete
    # 主表记录被删除时，从表记录级联删除
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON DELETE CASCADE
);
# 主表记录被删除时，从表外键设置为NULL
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON DELETE SET NULL
);
# 主表记录被删除时，拒绝操作
CREATE TABLE tb_name (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    foreign_key_field field_type, 
    FOREIGN KEY(foreign_key_field) REFERENCES master_table_name(associated_field) ON DELETE RESTRICT
);
# 示例: 在创建表时指定主表记录被删除时，从表记录级联删除
# 创建主表tb_test_master
CREATE TABLE tb_test_master (
	id INT PRIMARY KEY AUTO_INCREMENT
);
# 创建从表tb_test_slave，m_id字段设置外键，关联tb_test_master表的主键id字段，主表记录被删除时级联删除从表记录
CREATE TABLE tb_test_slave (
	id INT PRIMARY KEY AUTO_INCREMENT,
    m_id INT,
    FOREIGN KEY(m_id) REFERENCES tb_test_master(id) ON DELETE CASCADE
);
# 示例: 在创建表后指定主表记录被删除时，从表记录级联删除
# 创建主表tb_test_master
CREATE TABLE tb_test_master (
	id INT PRIMARY KEY AUTO_INCREMENT
);
# 创建从表tb_test_slave
CREATE TABLE tb_test_slave (
	id INT PRIMARY KEY AUTO_INCREMENT,
    m_id INT
);
# 创建tb_test_slave表外键关联，m_id字段设置外键，关联tb_test_master表的主键id字段，主表记录被删除时级联删除从表记录
ALTER TABLE tb_test_slave ADD CONSTRAINT fk__tb_test_slave__m_id__tb_test_master__id FOREIGN KEY (m_id) REFERENCES tb_test_master(id) ON DELETE CASCADE;

  • 外键的删除：要删除从表中的外键字段或主表中的关联字段时，无法直接删除，需要先删除外键关系。

    1. 操作查看外键关系的名字

SHOW CREATE TABLE tb_name;
```

2. 删除外键关系

```mysql
ALTER TABLE tb_name DROP FOREIGN KEY foreign_key_name；
```

3. 删除外键字段

```mysql
ALTER TABLE tb_name DROP COLUMN foreign_key_field;
```
  • 外键 foreign key为 mysql中innodb储存引擎所特有的，开发时，可以使用程序逻辑来保证数据的一致性，优点是相对于使用外键效率更高。使用外键优点是因为程序逻辑不能100%保证数据的完整性，而使用外键可以确保数据的完整性，缺点则是会影响性能。

九、存储引擎

  • 定义：实际上指的是存储的结构。MySQL 5.5之前默认引擎是MyISAM，5.5之后默认是InnoDB。
  • 查看数据库支持的存储引擎
SHOW ENGINES;

  • 从上图中可以看出，我们数据库的默认存储引擎是InnoDB，FEDERATED则是不支持的。

  • 设置默认使用的数据库引擎

    1. 查看数据库Data目录位置
    SHOW GLOBAL VARIABLES LIKE '%datadir%';

    1. 上移一级目录，找到my.ini配置文件，更改default-storage-engine字段

  • 查看当前表所使用的存储引擎

SHOW CREATE TABLE test_table;

  • 修改表的存储引擎
ALTER TABLE tb_name ENGINE engine_name;
# 示例: 将test_table表的存储引擎更改为MyISAM
ALTER TABLE test_table ENGINE MyISAM;
  • 创建表时指定存储引擎
CREATE TABLE tb_name(field_name field_type, ...) ENGINE engine_name;
# 示例: 创建test_table表，并设定其存储引擎为MyISAM
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, extra_field VARCHAR(32)) ENGINE MyISAM;
  • 一般情况下表所包含的内容
    1. 结构
    2. 数据
    3. 索引
  • 查看数据库存储数据文件路径
SHOW GLOBAL VARIABLES LIKE '%datadir%';

  • 不同存储引擎保存的内容的文件后缀是不一样的

    1. MyISAM：保存文件通常将 .frm后缀名文件保存结构 .MYD后缀名保存数据 .MYI后缀名保存索引
    2. InnoDB： .frm后缀名保存结构，至于数据和索引存储位置，则需要查看my.ini中的 innodb_file_per_table 配置，如果为1，则数据和索引会单独存储在表自己的表空间中，以.ibd后缀保存，在5.6.6版本之后这个值默认为1。如果为0，则数据和索引会统一存放在系统表空间中，即Data目录下的ibdata文件中。

  • 选择存储引擎的依据

    1. 性能
    2. 功能

  • 插入、索引要求速度快、并发性低、存储空间要求小，可以使用Myisam引擎。

  • 并发性高，需要使用外键，需要使用事务功能，可以使用InnoDB引擎。

十、联合查询

  • 作用：将两个按照不同条件甚至不同表中检索出来的数据放到一个视图内查看。
  • 使用：两个查询的select语句用括号括起来，中间使用union关键字连接。
(
    SELECT 
    	field_name1,
    	field_name2,
    	...
    FROM tb_name1 
    WHERE conditions1
) UNION (
    SELECT
    	field_name1,
    	field_name2,
    	...
    FROM tb_name2 
    WHERE conditions2
);

  • 示例:

    • 创建开发人员developer表，并添加若干条记录
    CREATE TABLE developer (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    job_number VARCHAR(32), 
    name VARCHAR(32), 
    pro_language VARCHAR(32)
);
INSERT INTO developer VALUES (NULL, 'd10001', 'zhangsan', 'Java');
INSERT INTO developer VALUES (NULL, 'd10002', 'lisi', 'Python');
INSERT INTO developer VALUES (NULL, 'd10003', 'wangwu', 'C#');

    • 创建市场营销人员marketer表，并添加若干条记录
    CREATE TABLE marketer (
	id INT PRIMARY KEY AUTO_INCREMENT, 
    job_number VARCHAR(32), 
    name VARCHAR(32), 
    district VARCHAR(64)
);
INSERT INTO marketer VALUES (NULL, 'm10001', 'zhaoliu', 'ChangSha');
INSERT INTO marketer VALUES (NULL, 'm10002', 'zhouqi', 'ShenZhen');
INSERT INTO marketer VALUES (NULL, 'm10003', 'sunba', 'GuangZhou');

    • 假设公司下设只有开发人员和市场营销人员，那么要获取公司所有人员的工号和姓名，联合查询的SQL如下
    # 虽然开发人员developer表的开发语言pro_language字段与市场营销人员marketer表的营销区域district字段类型并不一致，但是我们只关心工号以及姓名，这两个字段在两张表中类型是一致的，因此可以使用联合查询
(
    SELECT 
    	job_number, 
    	name 
    FROM developer
) UNION (
    SELECT 
    	job_number, 
    	name 
    FROM marketer
);
    • 查询结果如下

  • 注意点

    1. 如果union的结果中有重复记录，默认会将重复记录去掉。使用union all 则会保留重复记录
    (
    SELECT 
    	field_name1,
    	field_name2,
    	...
    FROM tb_name1 
    WHERE conditions1
) UNION ALL (
    SELECT
    	field_name1,
    	field_name2,
    	...
    FROM tb_name2 
    WHERE conditions2
);
# 示例: 获取公司所有人员的工号和姓名，不去除重复记录
(
    SELECT 
    	job_number, 
    	name 
    FROM developer
) UNION ALL (
    SELECT 
    	job_number, 
    	name 
    FROM marketer
);

    1. union要求联合的两个结果集的列结构完全一致(联合查询内检索的字段数一致，类型一致)，否则无法使用union联合查询。但是字段名称并不要求一致，如果两个表中的字段名称不一致，检索结果中字段名称以第一条select语句字段名称为准。

      • 修改market表name字段为m_name
      # 此处字段名称已经由name变更为m_name，但是类型依旧为VARCHAR(32)，与developer表类型一致
ALTER TABLE marketer CHANGE COLUMN name m_name VARCHAR(32);

      • 获取公司所有人员的工号和姓名
      # 将第二条SELECT的name改为m_name即可，联合查询的结果集字段名称依旧是name
(
    SELECT 
    	job_number, 
    	name 
    FROM developer
) UNION (
    SELECT 
    	job_number, 
    	m_name 
    FROM marketer
);
      • 查询结果

十一、子查询

  • 查找最大值
SELECT MAX(search_field_name) FROM tb_name WHERE conditions;
# 示例: 查找test_table表中score字段的最大值
SELECT MAX(score) FROM test_table WHERE 1;
  • 使用子查询查找拥有最大值的数据
SELECT
	field_name1,
	field_name2,
	...
FROM tb_name 
WHERE 
	search_field_name = (
        SELECT MAX(search_field_name) FROM tb_name WHERE conditions
    );
# 示例: 查找test_table表中score拥有最大值的所有记录
SELECT * 
FROM test_table 
WHERE 
	score = (
        SELECT MAX(score) FROM test_table WHERE 1
    );

  • 子查询的分类

    1. 数据准备

      • 创建教师表teacher
      CREATE TABLE teacher (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32));
      • 插入测试数据
      INSERT INTO teacher VALUES(NULL, 'caocao');
INSERT INTO teacher VALUES(NULL, 'liubei');
INSERT INTO teacher VALUES(NULL, 'sunquan');

      • 创建学生表student
      # 这里的t_id实际上是打算引用teacher表的id的，但是这里并没有设置外键
CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), score INT, t_id INT);
      • 插入测试数据
      INSERT INTO student VALUES(NULL, 'zhangsan', 59, 1);
INSERT INTO student VALUES(NULL, 'lisi', 73, 1);
INSERT INTO student VALUES(NULL, 'wangwu', 97, 2);
INSERT INTO student VALUES(NULL, 'zhaoliu', 97, 3);
# 由于没有设置外键约束，因此才能把teacher表中不存在的id：4插入到stduent表中的t_id字段中
INSERT INTO student VALUES(NULL, 'zhouqi', 42, 4);

    2. 子查询出现的位置

      1. where型：出现在where后。一般情况下是子查询返回单值或者某一个字段的多条记录，将此值或结果集作为父查询的筛选条件。

        • 子查询返回单值的情况
        # 查找student表中score为最高值的所有记录。
SELECT * 
FROM student 
WHERE score = (
    SELECT MAX(score) FROM student
);

        • 子查询返回某一字段多条记录的情况
        # 查找student表中有对应老师的所有记录(即stdent表的t_id值在teacher表的id中存在)。
SELECT * 
FROM student
WHERE t_id IN (
	SELECT id FROM teacher
);

      2. from型：出现在from后。一般情况下是子查询返回某一个字段的多条记录或者多个字段的多条记录，作为一张临时表，父查询从这个表中获取和筛选数据。

        • 注意：使用from型查询时，需要对临时表指定别名，否则会出现 ERROR 1248 (42000): Every derived table must have its own alias
        • 子查询返回一个字段多条记录的情况
        # 查找student表中分数score小于60且姓名以z开头的学生的名字。
SELECT * 
FROM (
 SELECT name FROM student WHERE score < 60
) AS t_student
WHERE name LIKE 'z%';

        • 子查询返回多个字段多条记录的情况
        # 查找student表中分数大于等于60且学生姓名以z开头的所有记录。
SELECT * 
FROM (
    SELECT * FROM student WHERE score >= 60
) AS t_student
WHERE name LIKE 'z%';

      3. exists型：出现在exists后，可以看作是where型的一种。如果子查询可以返回数据，则认为where语句后的exists表达式为真，返回该条记录，否则为假，不返回此记录。

      # 查找student表中有对应老师的所有记录(即stdent表的t_id值在teacher表的id中存在)。
SELECT * 
FROM student 
WHERE EXISTS (
    SELECT * FROM teacher WHERE student.t_id = teacher.id
);

  • 集合操作符

    1. in：一个数值存在于集合中任意一个即可。

    2. not in：一个数值不存在于集合中的任意一个。

    3. =any：一个数值存在于集合中任意一个即可。 即 =any 与 in 对等。

    4. !=any：一个数值只要与集合中任意一个元素不等即可。

    5. =all：一个数值存在于集合中的所有元素。

    6. !=all：一个数值不存在与集合中的所有元素。 即 !=all 与 not in 对等。

    7. some：some与any没有区别。

  • 子查询返回单值的情况的比较一般使用 = 号比较，如果是返回单个字段多个记录，一般情况下都是使用上述集合操作符来进行比较。

十二、连接查询

  • 意义：将所有数据按照某种条件连接起来，再进行筛选处理。on 表示连接条件，where表示过滤条件。

  • 连接的分类

    1. 数据准备

      • 创建人物表
      CREATE TABLE person (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32));
      • 插入测试数据
      INSERT INTO person VALUES(NULL, 'zhangsan');
INSERT INTO person VALUES(NULL, 'lisi');
INSERT INTO person VALUES(NULL, 'wangwu');
INSERT INTO person VALUES(NULL, 'zhaoliu');

      • 创建邮箱表
      CREATE TABLE email (id INT PRIMARY KEY AUTO_INCREMENT, account VARCHAR(32), password VARCHAR(16), p_id INT);
      • 插入测试数据
      INSERT INTO email VALUES(NULL, 'zhangsan@qq.com', '123456', 1);
INSERT INTO email VALUES(NULL, 'zhangsan2@foxmail.com', '33222111', 1);
INSERT INTO email VALUES(NULL, 'lisi_test@gmail.com', '233731', 2);
INSERT INTO email VALUES(NULL, 'wangwu_company@hotmail.com', '886886', 3);
INSERT INTO email VALUES(NULL, 'wangwu_home@outlook.com', '23333333', 3);

    2. 外连接：负责连接的一个或多个数据不真实存在，称之为外连接。

      1. 左连接：以左表连接条件为基准，使用左表字段对接右表字段失败时，保留左表的数据，右表中的所有字段用空值NULL代替然后连接。
      #person表作为左表，email表为右表，根据person表id与email表的p_id字段相等作为连接的条件对两表使用左连接
SELECT * FROM person LEFT JOIN email ON person.id = email.p_id;

      • 从上述左连接的结果可以看出，由于左表person中存在id：4，但右表email中p_id不存在4，因此连接结果中右表字段全部为NULL。
      1. 右连接：以右表连接条件为基准，使用右表字段对接左表字段失败时，保留右表的数据，左表中的所有字段用空值NULL代替然后连接。
      SELECT * FROM person RIGHT JOIN email ON person.id = email.p_id;

      • 从上述右连接的结果可以看出，由于右表中的p_id字段的值在左表id字段中都可以找到，因此连接结果中不会出现NULL值。

    3. 全连接：mysql暂不支持，可以将左右两边的表没有数据匹配项都添加到结果中，没有的值用null代替。可以使用union查询来模拟全连接的情况。 使用情况很少。

    4. 内连接：数据内部的连接，左右两表的任意一个连接字段一旦没有对接上，该记录将会从连接结果集中被剔除。

      • 写法为 inner join ，on 后接条件。可以用 join 代替 inner join 的写法，更常用的是直接以逗号分隔两个表名，使用 where 来作为内连接的条件。
      # 根据person表id与email表的p_id字段相等作为连接的条件对两表使用内连接，以下三条SQL语句使用效果一致。
SELECT * FROM person INNER JOIN email ON person.id = email.p_id;
SELECT * FROM person JOIN email ON person.id = email.p_id;
SELECT * FROM person, email WHERE person.id = email.p_id;

      • 可以不使用 on 来限制条件，而使用using来限制，但除了两个表的连接字段类型必须相同以外，字段名称也必须相同。因此，建议在有同名字段时用using连接，通用条件下使用 on 连接。
      # 根据person表id与email表id相同作为连接条件，对两表使用内连接，去除重复字段id
SELECT * FROM person JOIN email USING (id);
# 根据person表id与email表id相同作为连接条件，对两表使用左连接，去除重复字段id
SELECT * FROM person LEFT JOIN email USING (id);
# 根据person表id与email表id相同作为连接条件，对两表使用右连接，去除重复字段id
SELECT * FROM person RIGHT JOIN email USING (id);

      • 从上述使用 using来连接的结果可以看出，重复字段id只剩下一个。

    5. 自然连接：不需要指定连接条件，MySQL 会将相同的字段自动进行连接处理。

      • 自然连接也分为内外连接，默认为内连接。外连接分为左外自然连接与右外自然连接 natural left join 和 natural right join
      # 自然内连接
SELECT * FROM person NATURAL JOIN email;
# 左外自然连接
SELECT * FROM person NATURAL LEFT JOIN email;
# 右外自然连接
SELECT * FROM person NATURAL RIGHT JOIN email;

  • 注意

    1. 外连接不写条件，不能使用。
    2. 如果内连接不写 on 条件，则产生一个m * n的笛卡尔集，也称为交叉连接。在数据量庞大的情况下，这种情况是绝对不允许的。

  • union查询的结果是两个相同类型字段的结果集的合并，结果集中不产生新的字段。join查询的结果是两个表字段的拼接，结果集中会生成两个表的字段。

  • 可以使用给表来起别名的方式来简化代码，提高阅读性，字段也可以根据需要起别名。

  • 连接时，支持多表连接，后续直接使用 join 接上即可。

# 示例
# 具体连接顺序可以在 SELECT 前加上 EXPLAIN关键字查看
SELECT * 
FROM person 
LEFT JOIN email 
ON person.id = email.p_id 
LEFT JOIN student 
ON person.id = student.id;

十三、将检索到的数据按自定义格式保存到文件中

  • 用法
# select_statement泛指所有的 select 语句，下面定义文件的格式是：字段以逗号分隔，每行记录以换行分隔。这种格式数据的文件称为csv文件，使用较为广泛。可以自定义分隔符，但是不建议。
select_statement INTO OUTFILE 'file_path'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
  • 示例：将email表中 id > 1 的数据保存到C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data.csv
# 将表中所有字段导出的情况
SELECT * 
FROM email
WHERE id > 1 
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data.csv' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
# 如果考虑到数据导入时，主键冲突的情况，那么应该将主键字段如id，单独处理，导出为NULL，使其应用自增长的机制自动分配一个主键值，避免主键冲突
SELECT NULL, account, password, p_id 
FROM email
WHERE id > 1 
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data_no_key.csv' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
  • select 语句结果

  • 导出所有字段，生成文件的内容

  • 主键id置空，导出数据生成文件的内容

  • 注意：

    1. 输出路径不能随便写，如果使用的输出路径与my.ini配置文件中 secure-file-priv 配置项的输出路径不一致会报：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，文件分隔符不能使用 \ ，这会导致字符被转义，也会报上述错误。文件分隔符应该使用 / 符号代替。

    2. 文件输出路径是哪一个，可以通过以下命令查看

      # 值得一提的是，无法使用 SET secure_file_priv = 'd:/test' 之类的命令临时设置输出路径，报错: ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable。最终解决办法还是要去修改my.ini配置文件，然后重启数据库服务。
SHOW GLOBAL VARIABLES LIKE '%secure_file_priv%';

    3. 除了输出文本文件以外，还可以输出二进制文件，使用语法一致，将 OUTFILE 关键字替换成 DUMPFILE 即可，且不用规定格式。

十四、从文件中导入数据至表中

  • 用法
# 文件加载数据的方式: 字段以逗号分隔，每行记录以换行分隔。这是csv文件数据加载的方式。
LOAD DATA INFILE 'file_path'
INTO TABLE tb_name;
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
  • 示例：将data_no_key.csv文件数据导入email表中
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/data_no_key.csv' 
INTO TABLE email 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
  • 导入结果

十五、增删改查操作补充说明

  1. 新增数据

    • 当某个字段设置了默认值，且在使用insert语句时这个字段没有指定值，那么它将会使用之前设定好的默认值。

      # 主键值id没有设置，它会自动使用默认值NULL，由于设置了AUTO_INCREMENT的原因，它最终会自动生成一个主键值。
INSERT INTO test_table (name, age) VALUES ('zhangsan', 66);
# 除了不指定字段的值外，我们也可以使用default关键字来设置字段的值为默认值。
INSERT INTO test_table (id, name, age) VALUES (DEFAULT, 'zhangsan', 66);

    • 添加记录时，如果只需要添加某几个字段，使用 SET 要更方便，不需要将所有的字段都写一遍。当然，某些字段如果设置了非空，是必须要设置值的。

      INSERT INTO tb_name SET field_name1 = field_value1, field_name2 = value2 ...;
# 示例: 往test_table表中添加一条记录，仅设置 name 与 age 字段
INSERT INTO test_table SET name = 'zhangsan', age = 23;

    • 在一条SQL语句中新增多条记录

      # 这种方式不是特别常用，一般情况下，都是采用执行SQL脚本的形式来完成批量操作。
INSERT INTO tb_name (
    field_name1, 
    field_name2, 
    ...
) VALUES (
    field_value11, 
    field_value12, 
    ...
), (
    field_value21,
    field_value22,
    ...
), ...;
# 示例: 向test_table中新增多条记录
INSERT INTO test_table (
    name, 
    age
) VALUES (
	'zhangsan',
    21
), (
	'lisi', 
    22
), (
	'wangwu',
    23
);

    • 在主键冲突时执行更新操作，不冲突时执行新增操作

      INSERT INTO tb_name (
    field_name1, 
    field_name2, 
    ...
) VALUES (
	field_value1, 
    field_value2, 
    ...
) ON DUPLICATE KEY UPDATE
field_name3 = field_value3, 
field_name4 = field_value4,
...;
# 示例：向test_table中新增一条数据，id设置为1，当没有出现主键冲突时，添加id = 1， name = 'xiaoming'，age = 21的记录。当出现主键冲突时，更新该id的记录，设置 name = 'xiaozhang', age = 88。
INSERT INTO test_table (
    id,
    name, 
    age
) VALUES (
    1,
	'xiaoming',
    21
) ON DUPLICATE KEY UPDATE
name = 'xiaozhang',
age = 88;

    • 将查询的结果集作为新增数据的数据源

      # 使用这种方式新增数据需要满足结果集的字段数与字段类型与被插入数据的表的字段数与字段类型一致，否则无法使用。
INSERT INTO tb_name1 (
    field_name1, 
    field_name2,
    ...
) SELECT
field_name1, 
field_nam2,
...
FROM tb_name2
WHERE conditions;
# 示例: 将test_table查询出来的name，age字段的结果集插入到test_table的name，age字段中。这是一种很特殊的情况，将自身的查询结果集插入自己表中，只要执行几次这样的指令，它就可以短时间内获得大量重复的数据，因此这种行为也叫蠕虫复制。
INSERT INTO test_table (
	name, 
    age
) SELECT
name,
age
FROM test_table;

    • 在主键冲突的情况下更新除主键外的其他字段，没有冲突时执行新增数据操作

      # 其实就是将普通的新增语句的 INSERT 关键字调整为了 REPLACE 关键字
REPLACE INTO tb_name (
	field_name1, 
    field_name2,
    ...
) VALUES (
	field_value1, 
    field_value2, 
    ...
);
# 向test_table中新增一条记录，如果id为1的记录不存在，则新增一条记录，如果存在，则更新该记录的name与age字段。
REPLACE INTO test_table (id, name ,age) VALUES(1, 'xiaoqiang', 42);

    • 主键没有自增长auto_increment功能时，添加自增长的方法

      # 注: 由于字段已经设置为主键，因此这里不再添加 PRIMARY KEY 选项，否则会出现多主键的错误提示。
ALTER TABLE tb_name MODIFY COLUMN id INT AUTO_INCREMENT;

  2. 更新数据

    • 更新数据除了允许单表更新外，还可以将多个表连接起来，一次更新多个表的字段。

      • teacher表数据情况

      • student表数据情况

      • 将两张表使用内连接后的数据情况

      • 执行多表更新操作
      UPDATE teacher JOIN student ON teacher.id = student.t_id SET teacher.name = 'wuji', student.name = 'dasheng' WHERE teacher.id = 1;
      • 执行完多表更新后两张表内连接的数据情况

      • 执行完多表更新后teacher表的数据情况

      • 执行完多表更新后student表的数据情况

      • 从上述结果来看，teacher 表 id 为1 和 student 表 t_id 为1的数据都被更新了。

  3. 删除数据

    • 删除数据除了可以单表删除数据外，也可以将多个表连接起来，一次删除多个表的记录。

      • 还是以上面teacher与student两张表的数据为例，执行多表删除操作
      # DELETE之后可以指定删除哪张表的数据，这里直接指定了删除连接结果集中的某条记录后，两张表的数据都被删除
DELETE teacher, student FROM teacher JOIN student ON teacher.id = student.t_id WHERE teacher.id = 1;
      • 执行完多表删除之前两张表内连接的数据情况

      • 执行完多表删除之后两张表内连接的数据情况

      • 执行完多表删除后teacher表的数据情况

      • 执行完多表删除后student表的数据情况

      • 从上述结果来看，teacher 表 id 为1 和 student 表 t_id 为1的数据都被删除了。

    • 删除操作除了使用 where 条件限制删除的内容外，还可以使用 limit 关键字限制删除的记录数，我们可以使用 limit 配合 order by 关键字将结果排序，然后删除固定数量的记录。

      DELETE FROM tb_name ORDER BY sort_field_name sort_type LIMIT delete_record_number;
# 示例: 将test_table记录以id降序排序，然后删除前10条记录
DELETE FROM test_table ORDER BY id DESC LIMIT 10;

    • 清空表是删除数据的一种特殊形式，它会不仅会删除表内所有的记录，还会将主键自增长的值重置。

      TRUNCATE TABLE tb_name;
# 示例: 清空test_table表的所有数据，并将主键自增值重置
TRUNCATE TABLE test_table;

十六、GROUP BY 分组

  • 数据准备：student表数据如下

  • GROUP BY 使用在 SELECT 中可以达到分组显示的目的。

    # 以 t_id 分组显示 stduent 结果集
# 注意，group by 分组的字段必须要包含在 select 的字段中，或者其中的聚合函数中，否则无法展示分组后的结果。
SELECT name, score, t_id FROM student GROUP BY t_id;

    • 从上述结果可以看出，由于 name 为 zhangsan 的记录与 name 为 lisi 的记录 为同一个 t_id，被分到了一个组里面，两个记录被合并了，导致结果集中 name 为 lisi 的记录丢失。

  • 实际情况中，group by 子句通常是与聚合函数组合在一起使用的

    # 统计同一t_id记录分值score的和
SELECT SUM(score), t_id FROM student GROUP BY t_id;

  • 提到 group by，就不得不提 having 子句了，它通常用于分组后数据集的筛选，且常常搭配聚合函数一起使用。

    # 统计同一t_id记录分值score的和，并把分值和大于100的记录筛除
SELECT SUM(score), t_id FROM student GROUP BY t_id HAVING SUM(score) < 100;

    • 与上图结果对比可以看出，由于 having 子句对分组后的结果进行了再筛选，因此分值总和大于100的数据被去除了。

  • having 与 where 的区别

    • having 子句是作用在分组结果之后再进行数据筛选的，因此一般情况下是和 group by 搭配使用的，不会单独出现。由于它是对分组结果进行筛选，因此 having 子句中还常常包含聚合函数。
    • where 子句是作用在分组结果之前进行数据筛选的，它使用的范围更加广泛，在 select 语句中经常出现。
    • where 与 having 可以搭配在一起使用，执行顺序大致为：where 执行数据筛选 -> group by 将数据分组 -> 聚合函数统计数据 -> having 执行数据筛选。书写顺序为 where -> group by -> having，这与它的执行顺序一致。
    # 示例: where 与 having 同时使用的情况，where 首先将 t_id 为 2 的数据剔除了，然后 group by 对剩余的数据进行分组，最后 having 将分组后分值总和大于100的数据剔除，得到最终结果
SELECT 
	t_id, 
	SUM(score) 
FROM student t_id 
WHERE t_id != 2 
GROUP BY t_id 
HAVING SUM(score) < 100;

    • 与上图结果对比可以看出，由于 where 语句在分组前事先将 t_id 为 2 的数据筛除出去了，因此结果集中不再有 t_id 为 2，score 为 97 的数据。

十七、备份与恢复

  • 使用 MySQL 官方提供的 mysqldump 工具可以便捷得进行数据的备份与恢复。备份后的文件实际上是一个包含多个SQL执行语句的SQL脚本，执行该脚本就可以还原表结构、索引、和数据了。

    1. 备份指定数据库中的所有表的数据

      mysqldump -uroot -p db_name > file_path
# 示例：备份test数据库中所有表的数据
mysqldump -uroot -p test > d:/db_backup/db_test.sql

    2. 备份指定数据库内某个指定表的数据

      mysqldump -uroot -p db_name tb_name > file_path
# 示例: 备份test数据库中的 student 表的数据
mysqldump -uroot -p test student > d:/db_backup/tb_student.sql

    3. 备份指定数据库内多个指定表的数据

      mysqldump -uroot -p db_name tb_name1 tb_name2 ... > file_path
# 示例: 备份test数据库中的 person 表、 email 表的数据
mysqldump -uroot -p test person email > d:/db_backup/tb_test.sql

    4. 使用备份文件还原数据库表

      # 选定被还原的数据库
USE db_name;
SOURCE file_path
# 示例: 还原test数据库中的 person 表、email 表的数据
USE test;
SOURCE d:/db_backup/tb_test.sql

十八、视图

  • 定义：视图本身是没有数据的，视图实际上是通过执行相应的查询操作，将查询到的部分信息显示出来，内部实际操作的是真实表。

  • 作用：通过创建视图可以显示一张虚拟表，将不想被看到的数据隐藏起来。此外，如果经常需要将多个表连接到一起来显示，可以通过转化为视图来更方便的查询。将连接查询的 select 语句接在 CREATE VIEW view_name AS 后即可。

  • 创建视图

    CREATE VIEW view_name (view_field_name1, view_field_name2, ...) AS select_statement;
# 示例: 创建一个视图 test_table_view 将 test_table 结果集中的 name, age 字段提取出来，并在视图中重命名为 test_name, test_age
CREATE VIEW test_table_view (test_name, test_age) AS SELECT name, age FROM test_table;

  • 删除视图：删除视图不会影响真实表内的数据。

    DROP VIEW IF EXISTS view_name;
# 示例: 删除视图 test_table_view
DROP VIEW IF EXISTS test_table_view;

  • 修改视图

    # 修改视图实际上是重新定义了视图所使用的 select 语句，以及在视图中字段的名称
ALTER VIEW view_name (view_field_name1, view_field_name2) AS select_statement;
# 示例: 修改test_table_view视图定义
ALTER VIEW test_table_view (modified_name, modified_age) AS SELECT name, age FROM test_table;

  • 查看视图创建信息

    # 可以查看视图创建所使用的SQL语句以及字符集等信息
SHOW CREATE VIEW view_name;
# 示例: 查看 test_table_view 视图的视图创建信息
SHOW CREATE VIEW test_table_view;

  • 关于向视图中添加数据的操作：MySQL是支持向视图中添加或更新数据的，对视图数据的操作将会同步到真实表中。通常不建议这么做，视图仅仅是作为一个查看信息的工具。

十九、事务的使用

  • 定义：将多条 SQL 语句组合成一个业务，一个业务中的某个 SQL 执行失败，则整个业务执行失败，之前执行的所有SQL语句都将会被回滚到业务操作前的状态。在整个业务成功之前，只有执行业务的客户端可以看到业务执行过程中数据的修改，其他客户端看不到业务内的数据修改。

  • 查看事务是否默认提交

    SHOW GLOBAL VARIABLES LIKE '%autocommit%';
    • 默认情况下，MySQL的自动提交机制是打开的，处于 ON 状态。

    • 临时关闭事务的自动提交机制，当前会话有效。

      SET autocommit = 'OFF';

    • 永久关闭事务的自动提交机制：修改 my.ini 文件，在 [mysqld] 选项下添加 autocommit="OFF"

    • 当自动提交开启时，不再需要手动输入 START TRANSACTION 指令，因为事务默认就是开启的。每当执行新增、更新、删除指令时，必须要手动输入 COMMIT 提交事务，执行才会生效。

  • 常见的事务指令

    • 开启事务：START TRANSACTION;
    • 提交事务：COMMIT;
    • 回滚事务：ROLLBACK;

  • 事务的特点

    1. 原子性：多条语句一起执行，不可分割。
    2. 一致性：其他用户在数据库添加或删除数据时，事务处理过程中的数据是不变的。
    3. 隔离性：两个事务处理过程中互不影响，一个事务修改的内容在另一个事务中不会显示。
    4. 持久性：事务一旦提交，将会对数据库产生影响，无法再回退。

  • 以上的四个事务的特点简称为ACID属性。

  • 提到事务的隔离性，就不可避免地谈到，并发事务带来的问题

    1. 脏读：一个事务可以读取到另外一个事务未提交的数据称为脏读。
    2. 不可重复读：一个事务还没有执行完毕，在两次查询同一数据的期间，由于另一个事务提交了修改，导致两次查询同一数据的结果不一样。
    3. 幻读：一个事务还没有执行完毕，在两次查询数据期间，另外一个事务提交了事务，添加了记录，第一个事务读取到了原本不存在的记录称为幻读。

  • SQL中针对上述问题，定义了四个隔离级别

    • 读未提交 (READ-UNCOMMITTED) ：事务的最低隔离级别，并发事务会出现脏读、不可重复读、幻读的问题。
    • 读提交 (READ-COMMITTED) ：阻止脏读，但是还是会出现不可重复度和幻读的问题。
    • 可重复读 (REPEATABLE-READ)：阻止脏读和不可重复读，但是还是会出现幻读的问题。
    • 可串行化 (SERIALIZABLE)：事务的最高隔离级别，阻止脏读、不可重复读和幻读，但是使用地非常少。因为这是将所有事务依次逐个执行，这样两个事务之间是完全隔离的，这样带来的代价是效率非常低下。会导致锁竞争，出现超时问题。

  • MySQL设置隔离级别

    • 查看当前会话的隔离级别

      SELECT @@tx_isolation;

    • 查看全局 (默认) 隔离级别

      SELECT @@global.tx_isolation;

    • 设置当前会话的隔离级别 (断开连接后隔离级别会恢复默认)

      # 设置事务隔离级别为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 设置事务隔离级别为读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 设置事务隔离级别为可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 设置事务隔离级别为可串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    • 永久设置默认隔离级别：修改 my.ini 文件，在 [mysqld] 选项下添加 transaction-isolation 选项

  • 注意点

    • 常用的InnoDB存储引擎具有事务功能,BDB存储引擎也具有此功能，其他存储引擎不支持事务。
    • 事务处理过程中更新的行将被锁定，其他用户无法操作。(基于Innodb存储引擎)
    • 事务处理只针对于数据的操作，表的操作以及数据库的操作依旧是即时生效的。

二十、触发器

  • 前提：触发器使用很少，且效率比较低下，很少使用，仅供了解。

  • 触发器功能：可以在表上设定一个对每行数据的一个监听器，监听相关事件，每当事件发生时，就会执行一段定义好的sql代码。

  • 触发器作用范围：定义在表上，作用在表的每一行记录。

  • 触发器生效的时机

    1. 执行之前：before
    2. 执行之后：after

  • 触发器支持的事件

    1. 新增：insert
    2. 修改：update
    3. 删除：delete

  • old 与 new 的定义：old 是相对于触发事件前的数据，new 是相对于触发事件后的数据。

  • 事件与时机组合成触发器的定义

    1. 新增前 (before insert)：不支持对 old 进行操作。
    2. 新增后 (after insert)：对 old 及 new 操作均不支持。
    3. 更新前 (before update)：可以对 old 及 new进行操作，且只支持对 new 进行更新操作。
    4. 更新后 (after update)：对 old 及 new 操作均不支持。
    5. 删除前 (before delete)：对 old 及 new 操作均不支持。
    6. 删除后 (after delete)：对 old 及 new 操作均不支持。

  • 完整的触发器应该定义如下规则

    1. 哪个表
    2. 监听什么动作
    3. 执行时机
    4. 执行的代码是什么
    CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW sql_statement;
# 示例: 在test_table上定义一个触发器，每当记录更新后该记录的 age 字段的值自加 1 
# 重定义结束符为 $$ 
DELIMITER $$
CREATE TRIGGER 
	trigger__after_update__age 
BEFORE UPDATE ON 
	test_table FOR EACH ROW
BEGIN
	# 注意，这里不应该使用 UPDATE ，因为会造成循环调用，报错: Can't update table 'test_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
	# UPDATE test_table SET age = age + 1;
	# 在定义 UPDATE 事件时，触发器执行语句如果要执行某个字段更新操作只需使用 SET 即可。
	SET NEW.age = OLD.age + 1;
END$$
# 恢复默认结束符
DELIMITER ;

  • 删除触发器

    DROP TRIGGER trigger_name;
# 示例: 删除 trigger__after_update__age 触发器
DROP TRIGGER trigger__after_update__age;

  • 查看触发器

    SHOW TRIGGERS;
# 示例: 查看 以trigger_after_update 开头的触发器
SHOW TRIGGERS LIKE 'trigger_after_update%';

  • 查看触发器创建信息

    SHOW CREATE TRIGGER trigger_name;
# 示例: 查看 trigger__after_update__age 触发器的创建信息
SHOW CREATE TRIGGER trigger__after_update__age;
posted @ 2019-06-25 16:04  soundofcolors  阅读(140)  评论(0)  编辑  收藏  举报