微信小程序网站模板,电子印章在线制作生成器,建工类培训机构,官方网站怎么注册目录
前言
一、概述
二、 SQL
1. SQL通用语法
2. SQL分类
3. DDL
3.1 数据库操作
3.2 表操作
4. DML
5. DQL
5.1 基础查询
5.2 条件查询
5.3 聚合函数
5.4 分组查询
5.5 排序查询
5.6 分页查询
6. DCL
6.1 管理用户
6.2 权限控制
三、数据类型
1. 数值类…目录
前言
一、概述
二、 SQL
1. SQL通用语法
2. SQL分类
3. DDL
3.1 数据库操作
3.2 表操作
4. DML
5. DQL
5.1 基础查询
5.2 条件查询
5.3 聚合函数
5.4 分组查询
5.5 排序查询
5.6 分页查询
6. DCL
6.1 管理用户
6.2 权限控制
三、数据类型
1. 数值类型
2. 字符串类型
3. 日期时间类型
4. 选择建议
四、函数
1. 字符串函数
2. 数值函数
3. 日期函数
4. 流程函数
五、约束
六、多表查询
1. 内连接
2. 外连接
3. 自连接
4. 联合查询
5. 子查询
5.1 标量子查询
5.2 列子查询
5.3 行子查询
5.4 表子查询
七、事务
1. 事务概述
2. 并发事务问题
3. 事务隔离级别
总结 前言 本篇文章总结了与MySQL有关的一些基础知识包含SQL、数据类型、函数、多表查询和事务一些知识点不会说明地特别详细可用来随时回顾与复习。与MySQL有关的其他知识点可以参考其相关的手册。 MySQL 8.0 参考手册_MySQL 8.0 参考手册 MySQL :: MySQL 8.4 Reference ManualMySQL 8.4 参考手册MySQL :: MySQL 8.4 Reference Manual 一、概述 我们需要了解下图所示的三个概念 另外目前市面上有许多流行的关系型数据库如Oracle、MySQL、SQLlite等等。不论我们使用的是上面的哪一个关系型数据库最终在操作时都是使用SQL语言来进行统一操作因为我们前面讲到SQL语言是操作关系型数据库的统一标准。
二、 SQL
1. SQL通用语法
SQL语句可以用单行活多行书写以分号结尾。SQL语句可以使用空格/缩进来增强语句的可读性。MySQL数据库的SQL语句不区分大小写关键字建议使用大写。注释 单行注释 -- 注释内容 或 # 注释内容 多行注释/* 注释内容 */
2. SQL分类
SQL语句根据其功能主要分为四类DDL、DML、DQL、DCL。 3. DDL 3.1 数据库操作 下面是数据库操作的示例其中注释解释了SQL的具体作用 SHOW DATABASES; # 查询所有数据库SELECT DATABASE(); # 查询当前数据库/*
说明创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;
*/
CREATE DATABASE IF NOT EXISTS animal DEFAULT CHARSET utf8mb4;DROP DATABASE IF EXISTS animal; # 删除数据库USE animal; # 切换数据库
3.2 表操作 下面是表操作的示例其中注释解释了SQL的具体作用
SHOW TABLES; # 查询当前数据库所有表DESC sys_user; # 查看指定表结构SHOW CREATE TABLE sys_user; # 查询指定表的建表语句/*
说明创建表结构
CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ],字段2 字段2类型 [COMMENT 字段2注释 ],字段3 字段3类型 [COMMENT 字段3注释 ],......字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
*/
CREATE TABLE tb_student(id INT COMMENT 编号,name VARCHAR(15) COMMENT 姓名
) COMMENT 学生表;/*
说明添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
*/
ALTER TABLE tb_student ADD age TINYINT COMMENT 年龄;/*
说明修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度) [ COMMENT 注释 ] [ 约束 ];
*/
ALTER TABLE tb_student MODIFY age INT COMMENT 年龄;/*
说明修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
*/
ALTER TABLE tb_student CHANGE age age_new INT COMMENT 年龄;ALTER TABLE tb_student RENAME TO tb_student_new; # 修改表名DROP TABLE IF EXISTS tb_student_new; # 删除表TRUNCATE TABLE tb_student; # 删除指定表并重新创建表
4. DML 下面是DML的示例其中注释解释了SQL的具体作用
/*
说明给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
*/
INSERT INTO tb_student (name) VALUES (小明);/*
说明给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
*/
INSERT INTO tb_student VALUES (1, 小王);/*
说明批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;
*/
INSERT INTO tb_student (name) VALUES (小美), (王肖);/*
说明修改数据
UPDATE 表名 SET 字段名1 值1 , 字段名2 值2 , .... [ WHERE 条件 ] ;
*/
UPDATE tb_student SET name 老王 WHERE name 小王;/*
说明删除数据
DELETE FROM 表名 [ WHERE 条件 ] ;
*/
DELETE FROM tb_student;
5. DQL DQL查询语句基本语法如下
SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数 DQL的编写顺序与实际的执行顺序不太一样具体的顺序如下图所示 5.1 基础查询 在基本查询的DQL语句中不带任何的查询条件查询的语法如下
1). 查询多个字段 SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ; 2). 字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
3). 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
5.2 条件查询 条件查询的语法格式如下
SELECT 字段列表 FROM 表名 WHERE 条件列表 ; 对于条件来说有如下图所示的常用的比较运算符和逻辑运算符 5.3 聚合函数 聚合函数是将一列数据作为一个整体进行纵向计算 聚合函数的语法如下
SELECT 聚合函数(字段列表) FROM 表名 ; 注意 : NULL 值是不参与所有聚合函数运算的。 常见的聚合函数如下图所示 5.4 分组查询 分组查询的语法格式如下所示
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ]; WHERE 和 HAVING的区别如下 执行时机不同where是分组之前进行过滤不满足where条件不参与分组而having是分组之后对结果进行过滤。判断条件不同where不能对聚合函数进行判断而having可以。 注意事项: 分组之后查询的字段一般为聚合函数和分组字段查询其他字段无任何意义。执行顺序: where 聚合函数 having 。支持多字段分组, 具体语法为 : group by columnA,columnB 5.5 排序查询 排序查询的语法格式如下
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ; 排序方式如下
ASC : 升序(默认值) DESC: 降序 注意事项 如果是升序, 可以不指定排序方式ASC ;如果是多字段排序当第一个字段值相同时才会根据第二个字段进行排序 ; 5.6 分页查询 分页查询的语法格式如下
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ; 注意事项: 起始索引从0开始起始索引 查询页码 - 1* 每页显示记录数。分页查询是数据库的方言不同的数据库有不同的实现MySQL中是LIMIT。如果查询的是第一页数据起始索引可以省略直接简写为 limit 10。 6. DCL
6.1 管理用户
-- 创建用户
CREATE USER 用户名主机名 IDENTIFIED BY 密码;-- 修改用户密码
ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码 ;-- 删除用户
DROP USER 用户名主机名 ; 注意事项 在MySQL中需要通过用户名主机名的方式来唯一标识一个用户。主机名可以使用 % 通配。这类SQL开发人员操作的比较少主要是DBA Database Administrator 数据库管理员使用。 6.2 权限控制 MySQL 中定义了很多种权限但是常用的就以下几种 更多权限可参考6.2.2 MySQL提供的权限_MySQL 8.0 参考手册 下面是有关权限控制的语法示例
-- 查询权限
SHOW GRANTS FOR 用户名主机名 ;-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO 用户名主机名;-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名主机名; 注意事项 多个权限之间使用逗号分隔。授权时 数据库名和表名可以使用 * 进行通配代表所有。 三、数据类型 对于表操作来说我们需要先了解数据库中包含哪些数据类型才能更好地进行操作下面我们将补充说明MySQL中存在的一些数据类型下图是对数据类型的一个分类 备注下面我们将简单介绍前面三种常见的数据类型有关其他类型和更详细的解释可以参考下面 博客MySQL数据类型精讲-CSDN博客 MySQL中文手册第 11 章数据类型_MySQL 8.0 参考手册 1. 数值类型 整数类型是数据库中最基本的数据类型。 MySQL中使用浮点类型和定点类型来表示小数。浮点类型包括单精度浮点数FLOAT型和双精度浮点数DOUBLE型定点数类型就是DECIMAL型。浮点数型在数据库中存放的是近似值而定点数类型在数据库中存放的是精确值。 举例说明DECIMALDECIMAL(5,2) 中的第一个参数 5 指定了数值的最大总长度即整数部分加上小数部分的总和不能超过 5 位。第二个参数 2 表示小数点后的位数也就是小数点后面应该保留两位小数。如果插入的数据没有提供足够多的小数位数则会自动补足到指定的数量反之如果有更多的小数位数则会被截断或四舍五入到最接近的有效值2。 2. 字符串类型 CHAR和VARCHAR区别
都可以通过指定n来限制存储的最大字符数长度CHAR(20)和VARCHAR(20)将最多只能存储20个字符超过的字符会被截掉。n必须小于该类型允许的最大字符数。CHAR类型指定了n之后如果存入的字符数小于n后面将会以空格补齐查询的时候再将末尾的空格去掉所以CHAR类型存储的字符串末尾不能有空格VARCHAR不受此限制。内部存储机制不同CHAR是固定长度CHAR4不管是存入1个字符2个字符或者是4个字符英文的都将占用4个字节。VARCHAR是存入的实际字符数1个字字节n255或2个字节n255所以VARCHAR(4),存入1个字符将占用2个字节2个字符占用3个字节4个字符占用5个字节。CHAR类型的字符串检索速度要比VARCHAR类型的快。 BLOB和TEXT是对应的不过存储方式不同TEXT是以文本方式存储的而BLOB是以二进制方式存储的。如果存储英文的话TEXT区分大小写而BLOB不区分大小写。TEXT可以指定字符集BLOB不用指定字符集。 二进制类型实在数据库中存储二进制数据的数据类型如数码照片、视频和扫描的文档等数据。在MySQL中是用BLOB数据类型存储这些数据。BLOB有四种类型TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB,其中最大长度对应4种TEXT数据类型。 BINARY和VARBINARY数据类型类似于CHAR和VARCHAR。不同之处在于BINARY与VARBINARY使用字节为存储单位而CHAR与VARCHAR使用字符为存储单位。例如INARY(5)表示5字节的二进制数据CHAR(5)表示存储5个字符的数据。 BINARY(n)固定n个字节二进制数据。n的取值范围为1~255默认为1.若输出的字节长度小于n则不足部分以0填充。BINARY(n)数据存储的长度为n4个字节。 VARBINARY(n)可变长度n的取值范围是1~65535默认为1数据存储长度为实际长度4个字节。
3. 日期时间类型 日期和时间类型是为了方便在数据库中存储日期和时间而设计的。 4. 选择建议 四、函数 函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着这一段程序或代码在 MySQL中已经给我们提供了我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 1. 字符串函数 2. 数值函数 3. 日期函数 4. 流程函数 五、约束 约束是作用于表中字段上的规则用于限制存储在表中的数据。其目的是保证数据库中数据的正确、有效性和完整性。约束是作用于表中字段上的可以在创建表/修改表的时候添加约束。约束的分类如下 下面是一个建表的时候使用约束的例子
CREATE TABLE tb_user(id int AUTO_INCREMENT PRIMARY KEY COMMENT ID唯一标识,name varchar(10) NOT NULL UNIQUE COMMENT 姓名 ,age int check (age 0 age 120) COMMENT 年龄 ,status char(1) default 1 COMMENT 状态,gender char(1) COMMENT 性别
); 在这些约束里面比较重要的是外键约束下面是外键约束的一般语法格式
-- 添加外键
CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 添加了外键之后再删除父表数据时产生的约束行为我们就称为删除 / 更新行为。具体的删除 /更新行为有以下几种 : 示例如下
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
六、多表查询 多表查询就是指从多张表中查询数据。在多表查询中我们可以给表起别名但是一旦为表起了别名就不能再使用表名来指定对应的字段了此时只能够使用别名来指定字段。 对于多表查询来说有以下一些分类 1. 内连接 内连接的语法格式如下
-- 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;-- 显示内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; 注意如果一边或两边的数据对于连接字段是NULL那么这些行就不会出现在内连接的结果集中。 2. 外连接 外连接的语法格式如下
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ; 注意事项 左外连接和右外连接是可以相互替换的只需要调整在连接查询时SQL中表结构的先后顺序就可以了。而我们在日常开发使用时更偏向于左外连接。 3. 自连接 自连接查询顾名思义就是自己连接自己也就是把一张表连接查询多次。对于自连接查询可以是内连接查询也可以是外连接查询。自连接查询的语法格式如下
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; 在自连接查询中必须要为表起别名要不然我们不清楚所指定的条件、返回的字段到底 是哪一张表的字段。 4. 联合查询 对于union查询就是把多次查询的结果合并起来形成一个新的查询结果集其语法格式如下
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起union 会对合并之后的数据去重。
5. 子查询 SQL 语句中嵌套 SELECT 语句称为嵌套查询又称子查询。对于子查询来说大致分为如下几类 标量子查询子查询结果为单个值 列子查询(子查询结果为一列) 行子查询(子查询结果为一行) 表子查询(子查询结果为多行多列) 5.1 标量子查询 子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。常用的操作符有 、、 、、 、 。示例如下 -- 根据销售部部门ID查询员工信息
select * from emp where dept_id (select id from dept where name 销售部); 5.2 列子查询 子查询返回的结果是一列可以是多行这种子查询称为列子查询。常用的操作符有 IN 、 NOT IN 、 ANY 、 SOME 、 ALL。 示例如下 -- 根据部门ID查询员工信息
select * from emp where dept_id in (select id from dept where name 销售部 or name 市场部);-- 查询比财务部所有员工工资都高的员工信息
select * from emp where salary all ( select salary from emp where dept_id (select id from dept where name 财务部) ); 5.3 行子查询 子查询返回的结果是一行可以是多列这种子查询称为行子查询。 常用的操作符有 、 、 IN 、 NOT IN。示例如下 -- 查询与张无忌的薪资及直系领导相同的员工信息
select * from emp where (salary,managerid) (select salary, managerid from emp where name 张无忌); 5.4 表子查询 子查询返回的结果是多行多列这种子查询称为表子查询。 常用的操作符为 IN。示例如下 -- 查询与鹿杖客, 宋远桥的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name 鹿杖客 or name 宋远桥 );
七、事务
1. 事务概述 事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。事务具有如下四个特性简称ACID这四个特性如下
原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。 一致性Consistency事务完成时必须使所有的数据都保持一致状态。 隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的 注意 默认MySQL的事务是自动提交的也就是说当执行完一条DML语句时MySQL会立即隐式的提交事务。 下面是MySQL当中对事务的控制
SELECT autocommit ; # 查询事务提交方式
SET autocommit 0 ; # 把默认的自动提交修改为了手动提交, 默认值为1自动提交-- 开启事务
START TRANSACTION 或 BEGIN ;-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;下面是有关事务的一个转账案例
-- 开启事务
start transaction-- 1. 查询张三余额
select * from account where name 张三;-- 2. 张三的余额减少1000
update account set money money - 1000 where name 张三;-- 3. 李四的余额增加1000
update account set money money 1000 where name 李四;-- 如果正常执行完毕, 则提交事务
commit;-- 如果执行过程中报错, 则回滚事务
-- rollback;
2. 并发事务问题 赃读一个事务读到另外一个事务还没有提交的数据。比如 B 读取到了 A 未提交的数据。 不可重复读一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。事务A两次读取同一条记录但是读取到的数据却是不一样的。 幻读一个事务按照条件查询数据时没有对应的数据行但是在插入数据时又发现这行数据已经存在好像出现了 幻影 。 3. 事务隔离级别 为了解决并发事务所引发的问题在数据库中引入了事务隔离级别。主要有以下几种 有关事务隔离级别的操作语法如下 -- 查看事务隔离级别
SELECT TRANSACTION_ISOLATION;-- 设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE } 注意事务隔离级别越高数据越安全但是性能越低。 总结 本篇博客总结了MySQL数据库的一些基础知识如果您觉得有用的话可以点一个关注让我们一起努力进步吧
