一、数据库基础概念
- 数据库（database）：是表的集合，一个数据库中能够有多个表；
- 表（table）：由行列组成的二维表格（类似于excel）；
- 行：记录（record）；
- 列：字段（field）。
二、SQL语法
- 注释：单行注释：-- 注释内容；多行注释：/* 注释内容 */
- 语法忽略单词的大小写，语句用英文分号进行分隔
- 常用数据类型：
| 序号 | 数据类型 | 英文 | 说明 |
|---|---|---|---|
| 1 | 整数 | int | |
| 2 | 小整数 | tinyint | 有符号范围（-128，127），无符号范围（0，255） |
| 3 | 小数 | decimal | decimal(5,2)存5位数，小数占2位 |
| 4 | 字符串 | varchar | varchar(3)最多存3个字符，一个中文或一个字母都占一个字符，中文需要用英文单引号括起来 |
| 5 | 日期时间 | datetime |
- 写SQL基本方法：基本的select框架（包括连接表）=》where后面的条件=》select需要显示的字段
三、常用SQL操作语句表
| 序号 | 操作 | 关键词 | 语法 | 说明 |
|---|---|---|---|---|
| 1 | 创建表 | create | create table 表名 (字段名 字段类型 约束（选填）,字段名 字段类型 约束（选填）) | 1. 多个字段用英文逗号分隔 |
| 2 | 删除表 | drop | drop table 表名 | drop table if exists 表名：如果表存在才删除，避免报错 |
| 3 | 插入记录 | insert | 1. 插入一组记录：insert into 表名 values (值1,值2…) 2. 插入一组记录的部分值：insert into 表名 (字段名) values (值) | 值的顺序和字段名的顺序需要一致 3. 一条语句插入多个记录：insert into 表名 values (值1,值2), (值3, 值4) |
| 4 | 查询记录 | select | 1. 查询表中所有内容：select * from 表名 2.条件查询指定字段：select 字段1,字段2 from 表名 where 条件 3. 查询记录并进行排序：select * from 表名 order by 字段1 asc, 字段2 desc… 4. 条件查询并排序：select * from 表名 where 条件 order by 字段及排序方式 | 1. *：表示所有内容 2. 通过as给字段起别名（as可以省略）：select 字段1 as 别名1 from 表名 3.通过as给表起别名（as可以省略）：select * from 表名2 as 别名2 4.过滤重复记录（distinct）：示例select distinct * from 表名 5.where条件查询规则 5.1 如果没有where条件，就修改表中所有记录 5.2 相关运算符 ： 不等于：!=或者<> 与：and 或：or 非：not 5.3.like模糊查询规则 %：任意个数的任意字符 _：一个任意字符 示例：select * from 表名 where name like ‘A%’ 5.4.范围查询规则 in：在一个非连续的范围内，where size in (‘s’,‘m’,‘l’) between…and…：在一个连续的范围内（范围包含两个边界值），where age between 20 and 25 5.5.空判断 注意：null：表示什么都没有；''表示长度为0的字符串 is null:为空，where name is null is not null：不为空 6.order by排序规则 6.1 asc（默认值）从小到大排序，升序； desc从大到小排序，降序。 6.2 order by能够多个字段排序，以字段顺序进行先后排序 |
| 5 | 修改记录 | update | update 表名 set 字段1=值1,字段2=值2… where 条件 | 可使用where补充修改条件 |
| 6 | 删除记录 | delete | delete from 表名 where 条件 | 可使用where补充删除条件 |
| 7 | 删除记录 | truncate | truncate table 表名 | 1. 删除表中所有数据，保留表结构 2. 不需要添加条件 3. 主键的自增长字段恢复从1开始（delete会从之前的值继续向后+1） |
| 8 | 数据分组 | group by | 1. select 字段, count(*) from 表名 group by 字段 2. select 聚合函数 from 表名 where 条件 group by 字段 3. 注意多种组合的先后顺序【先筛选后分组】：select 字段 from 表名where 条件 group by 字段 order by 字段 4. 【先分组后筛选】：select 字段 from 表名 group by 字段 having 字段 | 1. 以字段进行分组，便于配合聚合函数进行计算 2.having分组后的筛选规则 2.1 having总是出现在group by之后 2.2 having能够配合聚合函数使用 3. having与where筛选的区别： 3.1 where是对表的原始数据进行筛选，having是对group by后的分过组数据进行筛选 3.2 having后面能够使用聚合函数，但是where后面不能使用聚合函数 |
| 9 | 数据分页显示 | limit | select * from 表名 limit start, count | 1. start表示开始行，省略时采用默认值0，从第一行开始 count表示显示行数 2. limit也能和where查询语句结合使用，且总放在最后 |
| 10 | 子查询 | select嵌套 | select * from 表 where (select子查询) | 1. 子查询嵌套在主查询里面，一般用括号括起来 2. 子查询作为条件或者数据源来辅助主查询 3. 子查询是可以独立存在的一条完整select语句 4. 主查询不能独立运行，需要依赖子查询的结果 5. 特殊子查询定义： 标量子查询：子查询返回结果只有一行一列 列子查询：子查询返回结果为一列多行 表级子查询：子查询返回结果为多行多列（一个表） |
四、字段的约束——校验记录
| 序号 | 约束 | 关键词 | 说明 |
|---|---|---|---|
| 1 | 主键 | primary key | 1. 此字段值不能重复，auto_increment表示值自动增长 2. 插入值时，如果不想插入主键自增长字段可用占位符：0或者null |
| 2 | 非空 | not null | 此字段不允许空值 |
| 3 | 唯一 | unique | 此字段不允许重复 |
| 4 | 默认值 | default | 1. 当不填写此值时会使用默认值 2. 示例：age int default 30 |
五、聚合函数——简单计算查询结果
注意聚合函数能用于select中的显示结果和having后的条件，不能在where后面的条件中套用
| 序号 | 函数 | 作用 | 示例 |
|---|---|---|---|
| 1 | count() | 统计记录条数 | 1. select count(字段或者*) from 表名 2. 去重处理：select count(distinct 字段) from 表名 |
| 2 | max() | 查询数值型字段中的最大值 | select max(字段) from 表名 |
| 3 | min() | 查询数值型字段中的最小值 | select min(字段) from 表名 |
| 4 | sum() | 求数值型字段数值之和 | select sum(字段) from 表名 |
| 5 | avg() | 求数值型字段平均值 | 1. select avg(字段) from 表名 2. null值不计算在avg的分母中（仅计算有值数据的平均值） |
六、多张表的连接方式
| 序号 | 连接方式 | 关键词 | 查询结果 | 示例 | 说明 |
|---|---|---|---|---|---|
| 1 | 内连接 | inner join…on… | 只显示两个表的交集 | 1. 标准语法：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件 order by 排序 2. 多表内连接：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 inner join 表3 on 表1（也有可能是表2）.字段 = 表3.字段 3. 隐式内连接（了解）：select * from 表1,表2 where 表1.字段 = 表2.字段 | 1. 着重关注表中映射的字段 2. 通过控制select后面的内容来展示部分内容（多表中的同名字段需要补充上表名来进行区分：表1.字段） |
| 2 | 左连接 | left join…on… | 显示两个表的交集 + 左表特有的数据 | 标准语法：select * from 表1 left join 表2 on 表1.字段 = 表2.字段 where 条件 order by 排序 | 右表中不存在的数据用null填充 |
| 3 | 右连接 | right join…on… | 显示两个表的交集 + 右表特有的数据 | 标准语法：select * from 表1 right join 表2 on 表1.字段 = 表2.字段 where 条件 order by 排序 | 左表中不存在的数据用null填充 |
| 4 | 自关联 | on 表的别名1.字段1 = 表的别名2.字段2 | / | / | 自关联定义：同一张表做连接查询，表中的某一字段关联了这个表中另外的一个字段，但是他们的业务逻辑含义不一样 |
七、MySQL常用内置函数
内置函数能够在where后面的条件和select的显示中进行套用
| 序号 | 函数 | 关键词 | 示例 | 说明 |
|---|---|---|---|---|
| 1 | 拼接字符串 | concat | concat(字符1, 字符2, 字符3…) | 1. 对多个字符串进行拼接 2. 参数可以是数字也可以是汉字 |
| 2 | 计算字符个数 | length | length(str) | 计算字符串长度，一个汉字长度为3 |
| 3 | 截取字符串 | left/right/substring | left(str,截取长度) right(str,截取长度) substring(str,起始位置（从1开始）,截取长度) | 1. 从左边/右边/中间截取字符串 2. 英文和中文都视为1个字符 |
| 4 | 去除字符串空格 | ltrim/rtrim/trim | ltrim(str) rtrim(str) trim(str) | 去除字符串左侧/右侧/两侧的空格 |
| 5 | 求四舍五入值 | round | round(数字, 需要保留的小数位数（默认是0） | 对数字进行四舍五入 |
| 6 | 随机数 | rand | rand() 实现表的随机排序：order by rand() | 生成一个0~1的随机数 |
| 7 | 返回当前时间信息 | current_date/current_time/now | 返回当前日期：current_date() 返回系统当前时间：current_time() 返回系统当前日期+时间：now() | 日期和时间通常在插入记录时调用 |
八、一些拓展知识
| 序号 | 函数 | 关键词/解释 | 示例 | 说明 |
|---|---|---|---|---|
| 1 | 存储过程 | procedure | 1. 创建存储过程： create procedure 存储过程名称(参数列表) begin sql 语句 end 2. 调用存储过程：call 存储过程(参数列表) 3. 删除存储过程：drop procedure if exists 存储过程 | |
| 2 | 视图 | view | 1. 创建视图： create view 视图名称 as select 语句 2. 删除视图：drop view if exists 视图 | 1. 视图就是对查询进行封装，返回结果也是一张表 2. 能够对视图进行表的只读操作 |
| 3 | 事务 | 事务是多条更改数据操作的sql语句集合，这些操作要么都执行，要么都不执行 | 开启事务：begin； 回滚事务：rollback; 提交事务：commit; | 1. 数据的更改涉及到事务 2. 开启事务，执行update或delete语句，变更写到缓存中不会立即生效 3. 回滚事务，放弃修改 4. 提交事务：对表的修改生效 |
| 4 | 索引 | index | 1. 创建索引：create index 索引名称 on 表名(字段) 2. 查看索引：show index from 表名 3. 删除索引：drop index 索引名称 on 表名 | 1. 创建索引时，如果指定的字段是字符串则还需要指定长度，一般长度与定义字段时的长度一致 2. 索引能够加快数据查询效率，where后面的字段，数据库会自动查找是否有索引 3. 系统会自动为主键创建索引 |
九、基于命令行的mysql
| 序号 | 操作 | 命令 | 说明 |
|---|---|---|---|
| 1 | 连接数据库并登录 | mysql -h mysql服务器地址 -u 用户名 -p | -p：输入密码进入登录 |
| 2 | 展示数据库/表名 | show databases/tables | |
| 3 | 进入数据库 | use 数据库名 | |
| 4 | 告诉mysql客户端编码方式 | set names gbk | 避免数据库查询结果返回乱码 |
| 5 | 查看表的结构 | desc 表名 | |
| 6 | 创建数据库 | create database 数据库名 default charset 字符集 | 字符集：默认为utf8 |
十、参考学习资料
软件测试工程师所需的MySQL数据库技术，mysql系统精讲+课后练习
B站学习连接：https://www.bilibili.com/video/BV1M541147Cn/?spm_id_from=333.788.videopod.episodes&vd_source=7089343738fb1b5a72c1db4d1f9c49ea
