前端进阶-MySQL知识点上篇(近5千字)

我搁这敲代码呢
182 阅读13分钟

前端进阶-MySQL知识点

近段时间，利用工作之外的闲暇时间学习了koa和mysql，丰富了些许自己的技术体系；也在此将自己所总结的关于mysql的基础知识点提供给一样学习mysql的小伙伴们做一个参考，希望对大家有所帮助，如其中有错误之处，欢迎大家在评论区指正，谢谢！

一、数据库的基础概念

1-1、传统数据库与新型数据库的区别

传统数据库（关系型数据库）：MySQL，Oracle，SQL Server等

优点：

  • 易于维护：都是使用表结构，格式一致；
  • 使用方便：SQL语言通用，可用于复杂查询；
  • 复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

  • 读写性能比较差，尤其是海量数据的高效率读写；
  • 固定的表结构，灵活度稍欠；
  • 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

新型数据库（非关系型数据库）：mongoDB，redis等

优点：

  • 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
  • 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
  • 高扩展性；
  • 成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

  • 不提供sql支持，学习和使用成本较高；
  • 无事务处理；
  • 数据结构相对复杂，复杂查询方面稍欠。

1-2、传统数据库组成

传统型数据库中，数据的组织结构分为数据库（database），数据表（table），数据行（row），字段（field）四部分组成。

1-3、MySQL的体系结构

  1. 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程 池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务 器也会为安全接入的每个客户端验证它所具有的操作权限。

  1. 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大， 这样在解决大量读操作的环境中能够很好的提升系统的性能。

  1. 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通 信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。

  1. 存储层

数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

二、数据库语法操作（DDL）

2-1、操作数据库类

  1. 查询所有数据库：how databases;
  2. 查询当前数据库：select database();
  3. 创建数据库：create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ;
  4. 删除数据库：drop database [ if exists ] 数据库名 ;
  5. 切换数据库：use 数据库名 ;

2-2、操作数据库表

  1. 查询当前数据库所有表：show tables;
  2. 查看指定表结构：desc 表名 ;
  3. 查询指定表的建表语句：show create table 表名 ;
  4. 创建表结构;
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
) comment '用户表';
  1. 表操作-修改:
  • 添加字段： ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
  • 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
  • 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
  • 删除字段：ALTER TABLE 表名 DROP 字段名;
  • 修改表名：ALTER TABLE 表名 RENAME TO 新表名;

2-3、表操作-删除

  1. 删除表：DROP TABLE [ IF EXISTS ] 表名;
  2. 删除指定表, 并重新创建表：TRUNCATE TABLE 表名 ；

2-3、表操作-删除

2-3-1、数值类型

2-3-2、字符串类型

备注： char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和 字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性 能会更高些。

2-3-3、日期时间类型

三、MySQL的增删改查基本语法

3-1、select查询数据

SELECT 语句用于从表中查询数据。执行的结构被存储在一个结果表中（被称为结果集）。

-- 从 FROM 指定的【表】，查询出【所有的】数据。* 表示【所有列】

SELECT * FROM 表名称

-- 从 FROM 指定的【表】，查询出【指定的】类名称（字段）的数据。

SELECT 列名称 FROM 表名称

注意： SQL 语句中的关键字对 大小写不敏感 。 SELECT 等效于 select ，FROM 等效于 from。

3-2、insert into增加插入数据

-- 语法解读 向指定的表中，插入如下几列的数据，列的值通过 values 指定
-- 注意： 列和值要 一一 对应，多个列和多个值之间，使用英文逗号分隔

INSERT INTO table_name(列1,列2,...) VALUES (值1,值2,...)

注意： MySQL 新版本插入语法：INSERT INTO ev_users SET ?

3-3、update 更新数据

-- 语法解读 ：
-- 1、用 UPDATE 指定要更新哪个表中的数据
-- 2、用 SET 指定列对应的新值
-- 3、用 WHERE 指定更新的条件

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

3-4、where根据条件增删改查

-- 语法解读 ：
-- 1、查询语句中的 WHERE 条件

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

-- 2、更新语句中的 WHERE 条件

UPDATE 表名称 SET 列 = 新值 WHERE 列 运算符 值 

-- 3、删除语句中的 WHERE 条件 

DELETE FROM 表名称 WHERE 列 运算符 值

WHERE 子句用于 限定选择的标准。在 SELECT,UPDATE,DELETE 语句中，皆可使用 WHERE 子句来限定选择的标准。

3-5、where中的运算符

3-6、where 中的 and 和 or

  1. AND 和 OR 在 WHERE 子语句中把两个或多个条件结合起来。
  2. AND 表示 必须同时满足多个条件，相当于 JavaScript 中的 && 运算符。
  3. OR 表示 只要满足任意一个条件即可，相当于 JavaScript 中的 || 运算符。
SELECT * FROM users WHERE status = 0 and id < 3

3-7、order by升序降序

语法： SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2

排序方式：

  • ASC : 升序(默认值)
  • DESC: 降序

3-8、as 给列起别名

-- 使用 AS 关键字给列起别名

SELECT count(*) as total FROM users WHERE status=0

注意： as 可以省略： select workaddress '工作地址' from emp;

3-9、分页查询数据

语法： SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

3-10、分页查询数据

语法： SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ]

3-11、聚合函数

语法： SELECT 聚合函数(字段列表) FROM 表名 ; （ 注意 : NULL值是不参与所有聚合函数运算的）

案例：

-- 根据性别分组，统计男性员工 和 女性员工的数量 

select gender, count(*) from emp group by gender

3-12、having 与 where 的区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组执行 之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：

  1. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
  2. 执行顺序: where > 聚合函数 > having 。
  3. 支持多字段分组, 具体语法为 : group by columnA,columnB 。

3-13、语法执行顺序

四、数据控制语言（DCL）

4-1、管理用户

  1. 查询用户：select * from mysql.user;
  2. 创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  3. 修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
  4. 删除用户：DROP USER '用户名'@'主机名' ;

注意事项：

  • 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
  • 主机名可以使用 % 通配。
  • 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。

4-2、权限控制

  1. 查询权限：SHOW GRANTS FOR '用户名'@'主机名' ;
  2. 授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  3. 撤销权限：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

  • 多个权限之间，使用逗号分隔
  • 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

五、函数

5-1、字符串函数

5-2、数值函数

  • mod 就是 x 除以 y 的余数

5-3、日期函数

5-4、流程函数

六、约束

6-1、表的数据写入约束

注意： 约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

6-2、外键约束（即数据关联的完整性保证）

  1. 添加外键：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
  2. 删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  3. 删除/更新行为约束：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

七、多表查询

7-1、多表查询基本语法

  1. 查询多张表，用 , 分隔即可： select * from emp , dept;
  2. 查询多表之间关联数据： select * from emp , dept where emp.dept_id = dept.id;

7-2、内连接

  1. 隐式内连接 ： SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
  1. 显式内连接 ： SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

备注：表的别名简写

①. tablea as 别名1 , tableb as 别名2 ; (正常语法)

②. tablea 别名1 , tableb 别名2 ; （简写语法）

7-3、外连接

7-3-1、左外链接

语法： SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

7-3-2、右外链接

语法： SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

7-4、自连接

语法： SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

 select a.name , b.name from emp a , emp b where a.managerid = b.id;

注意事项： 在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。

7-5、联合查询

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
  1. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
  2. union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

7-6、子查询

// 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

语法： SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

7-6-1、标量子查询

定义： 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。 常用的操作符：= <> > >= < <= ；

select * from emp where dept_id = (select id from dept where name = '销售部');

7-6-2、列子查询

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') ); 
-- 查询 比 财务部 所有人（all）工资都高的员工信息

7-6-3、行子查询

定义： 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');

7-6-4、表子查询

定义： 子查询返回的结果是多行多列，这种子查询称为表子查询。

select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );

八、事务

定义： 事务就是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

8-1、控制事务

8-1-1、方法一（不推荐使用）

  1. 查看/设置事务提交方式
SELECT @@autocommit ;
SET @@autocommit = 0 ; // 0 表示手动提交，1 表示自动提交
  1. 提交事务

COMMIT;

  1. 回滚事务

ROLLBACK;

8-1-2、方法二（推荐使用）

  1. 开启事务

START TRANSACTION 或 BEGIN ;

  1. 提交事务

COMMIT;

  1. 回滚事务

ROLLBACK;

8-2、事务的四大特性

  1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  3. 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
  4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

8-3、并发事务问题

  1. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
  1. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  1. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。

8-4、 事务隔离级别

  1. 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

  1. 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低

avatar
文章
阅读
粉丝