小前端MySQL从零入门 | 小册训练营

薄墨无痕
401 阅读16分钟

安装与初体验

# 启停mysql服务
net start MySQL57
net stop MySQL57

服务的名称（这里是MySQL57）在安装的时候，在Window Service里面设置.

安装之后需要将该bin目录的路径加入到环境变量Path中

mysqld可执行文件代表着服务器程序 -- 类似于mongod.exe，但是一般需要配置一个数据目录，有点像mongoDB里面的data；加入一个--console来显示命令行的信息

mysqld --datadir="C:\ProgramData\MySQL\MySQL Server 5.7\Data" --console

不管是使用mysqld可执行文件启动的还是使用Windows服务的方式启动的），就可以接着启动客户端程序来连接到这个服务器：

mysql -h 主机名  -u用户名 -p密码

使用短flag的时候不加“=”号。或者可以这样写： --host=localhost

MySQL的基本运行过程就是：通过客户端程序发送命令给服务器程序，服务器程序按照接收的命令去操作实际的数据。

  • 命令结束符号; \g \G
  • 可以一次提交多个命令，用分隔符分割
  • 最好使用单引号来表示字符串

数据类型与基本操作

常见的类型

  1. 整型，不同的范围对应不同的存储空间，例如TINYINT占一个字节，所以空间中8bit可以表示的数字范围是由二进制转换而来，是0-255,如果是有符号的数的话需要一个符号位，考虑这样的二进制数，所以是-127-+127 吗？

    非也，由于+0和-0是一样的但是却存在着符号的差别，所以-128是采用的-0的二进制来表示的。

  2. 浮点型 FLOAT & DOUBLE 分别占4&8字节，是利用的二进制表示十进制小数

    9.875 = 8 + 1 + 0.5 + 0.25 + 0.125 = 1 × 2³ + 1 × 2⁰ + 1 × 2⁻¹ + 1 × 2⁻² + 1 × 2⁻³
    • FLOAT4个字节是按照1个符号位+8个指数位+23个尾数位构成的。

  3. FLOAT(M, D)或者DOUBLE(M, D)来限制可以存储到本列中的小数范围。M为有效数字，D是小数位数

  4. 浮点数存在误差，因此有DECIMAL(M,D)定点数，占用的空间随着M,D变化

    • 定点数类型DECIMAL(16, 4)存储一个负数怎么办，比方说-1234567890.1234，这时只需要将0x80010DFB38D204D2中的每一个比特位都执行一个取反操作

  5. 无符号类型 数值类型 UNSIGNED，如： INT UNSIGNED

  6. 时间日期类型： YEAR,DATE,TIMESTAMP,DATETIME etc

    • 对毫秒、微秒的支持--小数秒，占一个字节的空间（since mysql v5.6.4）

  7. 字符串类型

    • 一旦你确定了CHAR(M)类型的M的值，如果M的值很大，而你实际存储的字符串占用字节数又很少，会造成存储空间的浪费。
    • VARCHAR(M)： 表中某一行包含的所有列中存储的数据大小总共不得超过65535个字节（注意是字节），也就是说VARCHAR(M)类型实际能够容纳的字符数量是小于65535的。
    • VARCHAR(M)是一种可变长度的字符串类型。
    • TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT四种可以存储大型的字符串的类型。（空间1-4字节）它们也都是变长类型，也就是说这些类型占用的存储空间由实际内容和内容占用的字节长度两部分构成。

  8. ENUM和SET类型都是一种特殊的字符串类型，在从字符串列表中单选或多选元素的时候会用得到它们。

  9. 对于比较大的二进制数据，比方说图片、音频、压缩文件什么的，通常情况下都不直接存储到数据库管理系统中，而是将它们保存到文件系统中，然后在数据库中之存放一个文件路径即可。

基操

SHOW DATABASES;

CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;

USE 数据库名称;
mysql -h localhost -u root -p123456 {DBNAME}

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

列的属性

  • 查询： SELECT * FROM 表名;

  • MySQL插入数据的时候是以行为单位的，语法格式如INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');

  • 没有显式指定的列的值将被设置为NULL

  • 逗号分隔Values可以插入多列

  • 创建表的时候可以设置默认值与非空值CREATE TABLE first_table (first_column INT NOT NULL,second_column VARCHAR(100) DEFAULT 'abc')

  • 主键PRIMARY KEY，作为数据的主要索引，和数据是一一对应的关系,主键列默认是有NOT NULL属性

  • UNIQUE

    表示值的唯一性

    • 主键列不允许存放NULL，而声明了UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中！
    • 一张表中只能定义一个主键，却可以定义多个UNIQUE约束

  • 外键

    CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);
    • 建立本表中某几列和父表之间的关系
  • AUTO_INCREMENT

    翻译成中文可以理解为

    自动增长

    ，简称自增。是一个标识符。

    • 一个表中最多有一个具有AUTO_INCREMENT属性的列。
    • 具有AUTO_INCREMENT属性的列必须建立索引。
    • 拥有AUTO_INCREMENT属性的列就不能再通过指定DEFAULT属性来指定默认值
    • 一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

  • COMMENT语句来给表添加注释

  • 对于无符号整数类型的列，我们可以在查询数据的时候让数字左边补0，如果想实现这个效果需要给该列加一个

    ZEROFILL

    属性（也可以理解为这是一个属于数据类型的属性）

    • 如果声明了 ZEROFILL 属性的整数列的实际值的位数小于显示宽度时，会在实际值的左侧补0，使补0的位数和实际值的位数相加正好等于显示宽度
    • 显示宽度可以在声明的时候设置 i1 INT(5) UNSIGNED ZEROFILL,即为补到5位
    • 如果声明了ZEROFILL属性的列没有声明UNSIGNED属性，那MySQL会为该列自动生成UNSIGNED属性。
    • 每个整数类型都会有默认的显示宽度。TINYINT的默认显示宽度是4，INT的默认显示宽度是(11),但是补0的时候又是无符号数UNSIGNED，需要减掉一个符号位，分别是3和10位。
    • 实际值的位数大于显示宽度时照原样输出。

  • 风格指南：

    • 不要名称中全都是数字。
    • 不要名称中有空白字符，mysql按照空格分词，但是不考虑空格的个数。
    • 尽量使用反引号将名称包裹起来，不适用mysql 的保留字。
CREATE TABLE student_info (
    ## id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    ## PRIMARY KEY (number) 
    number INT ZEROFILL,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    >> id_number CHAR(18) UNIQUE,
    >> UNIQUE KEY uk_id_number (id_number) 也可以声明unique
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    >> CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

查询表中的某一列

简单查询

  • select #col from #table
  • 尽量不要使用 * 性能
  • 使用AS添加别名
  • 多列逗号分隔
  • DISTINCT 去重
  • ASC DESC 排序 ORDER BY 选择排序列
  • LIMIT 开始行 持续行数
  • ORDER BY 需要在LIMIT之前

查询条件

SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '论萨达姆的战争准备';

  1. 通过where来添加条件，并且AND用于表示与的关系，优先级高于或OR；

  2. 使用IS <NOT> NULL 查询空值

  3. IN (cols)表示特定列

  4. 模糊查询

    1. %：代表任意一个字符串。
    2. _：代表任意一个字符。
    3. 使用\来转译上面的字符。

表达式&函数

  1. DIV表示整除，/就是原本的除法
SELECT  number, subject, score + 100 AS score FROM student_score;
# 表示将score加上100分 ，并且起了别名仍是score

  1. 函数

    1. UPPER函数是用来把给定的文本中的小写字母转换成大写字母。
    2. MONTH函数是用来把某个日期数据中的月份值提取出来。
    3. NOW函数用来获取当前的日期和时间。

  2. 聚集函数

    1. COUNT选择列时会忽略NULL,但是选择全部的时候不会。
    2. 不在搜索条件中的那些记录是不参与统计的
    3. 使用DISTINCT来过滤掉这些重复数据 SELECT COUNT(DISTINCT major) FROM student_info;
COUNT返回某列的行数
MAX返回某列的最大值
MIN返回某列的最小值
SUM返回某列值之和
AVG返回某列的平均值
  1. 隐式类型转换
    1. 把操作数类型转换为适合操作符计算的相应类型。 隐式转换为数字
    2. 将函数参数转换为该函数期望的类型
    3. 存储数据时，把某个值转换为某个列需要的类型。
    4. MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误。 '23sfd' → 23
    5. 强制转换不能用于存储数据中
    6. 使用CAST函数完成显式地类型转换

分组查询

# 按照subject分类,展示平均分大于60的
SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;

把非分组列放到查询列表中会引起争议，导致结果不确定

只想把那些符合某些条件的分组加入到结果集，从而减少结果集中记录的条数，那就需要把针对分组的条件放到HAVING子句 ,只有符合having的条件的才会显示

SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73;

需要为查询列表中有聚集函数的表达式添加别名 实现排序

SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC;

我们把这种对大的分组下继续分组的的情形叫做嵌套分组 GROUP BY department, major; 聚集函数将作用在最后一个分组列上

  1. WHERE,GROUP BY, ORDER BY的顺序
  2. GROUP BY concat('专业：', major); 这也是可以的
  3. WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。
SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]

子查询

多表查询的子查询，什么样的情况需要子查询以及如何书写MySQL语句，特殊情况的行子查询，并且聚集函数不能放到where里面去

连接查询

连接查询、外连接的模式，如何从多个表中查询到一种结果以及通过起别名的方式完成的对同一个表的连接查询，并且有的连接查询可以用子查询代替

组合查询

  1. 使用OR进行合并查询的结果
  2. 使用UNION连接起来的各个查询语句，并且UNION会对合并的查询结果去重。
  3. 使用ORDER BY和LIMIT对查询结果排序筛选

删除，插入，更新

插入数据，使用INSERT INTO VALUES方法，使用IGNORE方法来规避UNIQUE列出现重复的情况。使用DELETE来删除，使用UPDATE来更新数据，其中之前学到的ORDER BY 和 LIMIT仍然是有效的

便于使用的功能

视图VIEW

使用视图（VIEW）将查询的结果保存为虚拟表，视图其实就相当于是某个查询语句的别名，使用时可以看成是一个表，但是事实上是封装的对表的操作。命名视图的时候注意不要和表名重复，尽量只在查询中使用视图

自定义参数

学习了自定义变量和自定义分隔符，可以方便我们操作数据库

存储函数

存储函数是一种执行MySQL的函数方法，直接按名称调用

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
    (RETURN)函数体内容
END

使用SHOW CREATE FUNCTION和DROP FUNCTION <NAME>

在存储函数的函数体中使用变量前必须先声明这个变量 , 意思就是不存在变量提升（笑） DECLARE语句必须放到其他语句的前边。

函数体中的局部变量名不允许加@前缀 ，因为局部的一定是变量

在函数体中使用自定义变量,类似于非纯函数，会改变@var变量的值

CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END

函数参数不可以指定默认值，我们在调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配

判断

IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;

循环

WHILE 表达式 DO
    处理语句列表
END WHILE;
REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;
LOOP
    处理语句列表 内部包含终止条件
END LOOP;
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT

BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP
        IF i > n THEN
            LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END

我们在LOOP语句前加了一个flag:这样的东东，相当于为这个循环打了一个名叫flag的标记，然后在对应的END LOOP语句后边也把这个标记名flag给写上了。在存储函数的函数体中使用LEAVE flag语句来结束flag这个标记所代表的循环。

存储过程

存储函数侧重于执行这些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句。

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

存储过程的定义不需要声明返回值类型

调用 CALL 存储过程([参数列表]);

比存储函数强大的一点是，存储过程在定义参数的时候可以选择添加一些前缀

参数类型 [IN | OUT | INOUT] 参数名 数据类型
  • IN用于调用者向存储过程传递数据，如果IN参数在过程中被修改，调用者不可见。
  • OUT 用于把存储过程运行过程中产生的数据赋值给OUT参数，存储过程执行结束后，调用者可以访问到OUT参数。
  • INOUT 综合IN和OUT的特点，既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用。

**如果我们不写明参数前缀的话，默认的前缀是IN **

存储过程和存储函数的不同点

存储过程和存储函数非常类似，我们列举几个它们的不同点以加深大家的对这两者区别的印象：

  • 存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要。
  • 存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数。
  • 存储函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果。
  • 存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端。
  • 存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。

游标

游标其实就是用来标记结果集中我们正在访问的某一条记录。初始状态下它标记结果集中的第一条记录， 创建游标的语句一定要放在局部变量声明后头。

DECLARE 游标名称 CURSOR FOR 查询语句;

用OPEN和CLOSE启停游标。

  • 用法FETCH 游标名 INTO 变量1, 变量2, ... 变量n，有点类似于对象展开运算符。

  • 如果想获取多条记录，那需要把 FETCH 语句放到循环语句中

  • 每调用一次 FETCH 语句，游标就移动到下一条记录的位置 ， 迭代器

  • DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_done = 0;处理未查到的事件，从而中止循环

触发器TRIGGER

触发器和事件是MySQL服务器在特定情况下自动调用，有点类似于钩子函数，在增删改操作之前对变化的数据进行校验。

定义

CREATE TRIGGER 触发器名
{BEFORE|AFTER}  # 表示二选一
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN # 只有一条语句可以省略
    触发器内容
END

MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

  • 对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。

  • 对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。

  • 对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

有点类似于vue watch中的新旧值。

触发器的命名格式类似于<before|after(b|a)><insert|update|delete(i|u|d)>_<table_name>

查看删除触发器操作

查看当前数据库中定义的所有触发器的语句：

SHOW TRIGGERS;

查看某个具体的触发器的定义：

SHOW CREATE TRIGGER 触发器名;

删除触发器：

DROP TRIGGER 触发器名;

注意事项

  • 触发器内容中不能有输出结果集的语句。 即不能有return，不能select
  • 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。
  • before触发器可以修改待更新的记录，after不行

事件EVENT 定时任务

有时候我们想让MySQL服务器在某个时间点或者每隔一段时间自动地执行一些语句，这时候就需要去创建一个事件。

CREATE EVENT 事件名
ON SCHEDULE
{
  AT 某个确定的时间点| 
  EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
  具体的语句
END

默认情况下，MySQL服务器并不会帮助我们执行事件，除非我们使用下边的语句手动开启该功能：

 SET GLOBAL event_scheduler = ON;

原文发布在我的个人站（后天云服务到期就变成静态的了）

参考：juejin小册

avatar
文章
阅读
粉丝