目录
1. 数据库****
1.1. 概念****
1.2. 分类****
1.2.1. 关系型数据库****
1.2.1.1. SQL****
1.2.2. 安装****
2. SQL语句****
2.1. 常用数据类型****
2.2. 数据库****
2.3. 表****
2.3.1. 字段约束****
2.4. 数据****
2.4.1. 增 insert****
2.4.2. 删 delete****
2.4.3. 改 update****
2.4.4. 查 select****
2.4.4.1. 条件查询where****
2.4.4.1.1. 比较运算符****
2.4.4.1.2. 逻辑运算符****
2.4.4.1.3. 模糊查询****
2.4.4.2. 范围查找****
2.4.4.2.1. 连续范围 between..and..****
2.4.4.2.2. 非连续范围 in****
2.4.4.3. Null****
2.4.4.4. 别名 as****
1. 表的别名****
2. 字段的别名****
2.4.4.5. 排序order by****
1. 单字段排序****
2. 多字段排序****
2.4.4.6. 聚合函数/统计****
1. 总数Count****
2. 去重 Distinct****
3. 最大值max****
4. 最小值min****
5. 求和sum****
6. 平均avg****
2.4.4.7. 数据分组group by****
1. 单字段分组****
2. 多字段分组****
3. 分组后的排序****
2.4.4.8. 分组后筛选 having****
1. 配合聚合函数****
2.4.4.9. 分页 limit****
1.1.1. 连表查询****
1. 数据准备****
2. 内连接 inner join****
(1) 内连接查询指定字段****
(2) 带有where的内连接****
3. 外连接****
(1) 左连接left join****
(2) 右连接right join****
4. 小结****
5. 自关联****
6. 子查询****
3. 存储过程****
4. 非关系型数据库****
5. 物理删除和逻辑删除****
级别:
1. 了解,面试概率10%
2. 掌握,面试概率50%
3. 重点,面试概率80%
1. 数据库****
1. 为什么学习数据库?
(1) 测试理论,测试对象,源程序,目标程序,各种文档,数据
(2) 几乎所有软件的数据都存储在数据库中
(3) 方便更深层的定位bug
① 如:刚从页面注册成功的用户,无法登录
② 如:用户注册登录都没有问题
面试常见的问题:你在测试数据的过程中发现过拿些bug
1.1. 概念****
数据库 DB:英文是database,简单来说就是一个存储数据的地方
DB是DBMS(database management system 数据管理系统)所创建的管理数据的容器
如:一个企业(DBMS)创建了一个动物园(DB),包括很多动物(Datas)
表 table:数据库中存储数据的基本单位,数据按照分类存储到不同的表中
如:为了方便管理各种动物,在一个动物园(DB)中,把一群老虎(data)放在老虎分园(table)中
1.2. 分类****
1.2.1. 关系型数据库****
概念:由多张有关联的表组成的数据库
核心数据:
1. 数据库:表的集合,一个数据库中可以有多张表
2. 表:由行和列组成的二维表
3. 行:记录,即一条数据
4. 列:字段
产品:
1. Oracle:用于大型项目,如:银行、电信等项目,付费
2. Mysql:开源免费,互联网时代使用最广泛的关系型数据库
3. Sqlserver:微软平台项目常用,使用极小
4. Sqlite:轻量级数据库,主要应用于移动平台
1.2.1.1. SQL****
定义:结构化查询语言
Sql是一门语言,专门用来操作关系型数据库,可以操作oracle、mysql、sqlserver、sqlite
Sql语言不区分大小写
1.2.2. 安装****
MySQL :: Download MySQL Installer (Archived Versions)
MySQL :: Download MySQL Installer
MySQL :: Download MySQL Community Server (Archived Versions)
1.2.2.1. Navicat****
客户端连接数据库
2. SQL语句****
2.1. 常用数据类型****
面试题:
1. 数据库中常见的数据类型
2. 工作中有没有遇见和数据类型有关的bug
有的。我记得当时开发的是一个论坛项目,在一次开发评审会议上,后端设计的数据表里,文章标题用的数据类型是varchar(10),后来我做了调
创建表:
1. create table 表名(
2. 字段名 数据类型(长度) 约束,
3. 字段名 数据类型(长度) 约束
4. );
研,发现10个字符根本不够,后来建议改为20个字符的限制。
2.2. 数据库****
显示所有数据库:show databases;
使用指定的数据库:use 数据库名称;
显示指定数据库的所有表:show tables;
2.3. 表****
删除表:包括表结构+表数据
1. drop table 表名; #若表不存在,则会报错
2. drop table if exists 表名 #若表不存在,也不会报错
2.3.1. 字段约束****
常用约束****
1. 主键primary key:表示值是唯一的,不可重复,auto_increment代表自动增长
2. 非空not null:此字段不允许填写控制
3. 默认值default:当不填写此字段的值时,会使用默认值
面试题【3】
Delete、truncate、drop的区别
Delete删表数据时,会保留主键记录和表结构
Truncate删表数据,会同时删除主键记录,保留表结构
Drop 删表,表结构、主键记录、表数据全被删除
2.4. 数据****
2.4.1. 增 insert****
插入单条:
1. insert into 表名 values(值1,值2,值3);
插入多条:
1. insert into 表名 values
2. (值1,1,1),
3. (值2,2,2),
4. (值3,3,3);
指定字段插入:
1. insert into 表名 (字段1,字段2) values(值1,值2);
面试题
1. 什么时候需要插入数据
没有硬件设备:如,查询商品出库信息,如果没有出库信息,就可以插入一条出库的记录,以此来准备测试环境。
需要付款的时候,如,游戏客户端上有个按钮,可以查询游戏皮肤,此时就可以插入一条数据,将账户和皮肤id信息关联起来。
2.4.2. 删 delete****
1. delete from 表名 where 条件;
Truncate****
删除表中所有数据,保留表结构
1. truncate table 表名;
2.4.3. 改 update****
1. update 表名 set 字段=值,字段=值 where 条件;
2.4.4. 查 select****
生活中的软件需要查询数据的场景
1. 购物类型的软件:搜商品、看详情、看评价、看物流、看订单
2. 学生管理系统:搜学生、看班级信息、看成绩
3. 聊天系统:搜附近、搜在线、看朋友圈、看对方详情、看聊天记录
4. ....
数据准备****
1. #创建表
2. create table student(
3. id INT PRIMARY KEY auto_increment,
4. name VARCHAR(20) not null,
5. sex CHAR(1),
6. age TINYINT UNSIGNED,
7. class TINYINT UNSIGNED,
8. position VARCHAR(5)
9. );
1. #插入数据
2. insert into students values
3. (1,"白起","男",40,1,"坦克"),
4. (2,"猪八戒","男",80,2,"坦克"),
5. (3,"花木兰","女",22,3,"战士"),
6. (4,"狂铁","男",30,1,"战士"),
7. (5,"娜可露露","女",20,2,"刺客"),
8. (6,"李白","男",28,3,"刺客"),
9. (7,"妲己","女",18,1,"法师"),
10. (8,"周瑜","男",25,2,"法师"),
11. (9,"孙尚香","女",18,3,"射手"),
12. (10,"鲁班","男",16,3,"射手"),
13. (11,"庄周","男",21,3,"辅助"),
14. (12,"瑶","女",18,3,"辅助");
查询所有字段
1. select * form 表名;
查询指定字段
1. Select 字段1,字段2, from 表名;
2.4.4.1. 条件查询where****
Where中不能使用聚合函数****
1. select *****/字段 from 表名 where 条件;
补充:where关键字也可以在update和delete语句中使用
1. # 查询students表中id为1的数据
2. select * from students where id=1;
3.
4. # 查询students表中age为30的名字name和班级class
5. select name,class from students where age=30;
工作中用到where的场景:
1. 搜商品:where在商品名或者商品介绍中包含搜索的关键字
2. 看详情,where id为具体某一个商品
3. 搜学生,where id或者name为具体某一个学生
4. 搜附近,where 距离小于多少公里
5. ....
2.4.4.1.1. 比较运算符****
1. 等于=****
1. select * from students where name=’周瑜’;
2. 小于<****
1. select * from students where age<25;
3. 小于等于<=****
1. select * from students where age<=25;
4. 大于>****
1. select * from students where age>25;
5. 大于等于>=****
1. select * from students where age>=25;
6. 不等于!=****
1. select * from students where age!=25;
2.4.4.1.2. 逻辑运算符****
1. 与and****
1. # 查询age小于30,且sex为女的学生记录
2. select * from students where age<30 and sex='女';
2. 或or****
1. # 查询sex为女,或者class为1的学生记录
2. select * from students where sex='女' or class=1;
3. 非not****
1. # 查询position非辅助的学生记录
2. select * from students where position!='辅助';
2.4.4.1.3. 模糊查询****
Like实现模糊查询
1. %****
代表任意多个字符
1. # 查询名字里带白的学生记录
2. select * from students where name like '%白%';
2. _****
代表任意一个字符
3. # 查询姓白的二字学生记录
4. select * from students where name like '白_';
2.4.4.2. 范围查找****
2.4.4.2.1. 连续范围 between..and..****
1. # 查询age为25-30的学生记录
2. select * from students where age between 25 and 30;
2.4.4.2.2. 非连续范围 in****
1. # 查询position是 刺客、射手、辅助的学生记录
2. select * from students where position in ('刺客','射手','辅助');
2.4.4.3. Null****
Null在sql中代表空,不是0
Is null 判断为空
Is not null 判断非空
Null 不能用比较运算符判断
2.4.4.4. 别名 as****
1. 表的别名****
1. # 给表起别名 stu
2. select * from students as stu;
3. # as 可以省略
4. select * from students stu;
2. 字段的别名****
1. # 给字段起别名
2. select name as 姓名,sex as 性别 from students;
2.4.4.5. 排序order by****
1. order by 字段名 asc/desc
Asc:升序 从小到大
Desc:降序 从大到小
1. # 按age从小到大给students排序
2. # 当年龄相同时再按id从大到小排序
1. 单字段排序****
1. # 按age从小到大给students排序
2. select * from students order by age asc
2. 多字段排序****
3. select * from students order by age asc,id desc;
2.4.4.6. 聚合函数/统计****
1. 总数Count****
1. count(字段名/*)
1. # 查询students学生总数
2. select count(*) from students
3. # 查询students表中所有男性学生总数
4. select count(*) from students where sex='男';
5. select count(id) from students where sex='男';
6. Count*和countid查询结果是一样的,效率不同,大量数据时后者更快
2. 去重 Distinct****
7. # 查询students学生总数
8. select count(distinct class) from students
3. 最大值max****
1. max(字段)
1. # 查询students表中的最大年龄
2. select max(age) from students;
3. # 查询students表中女生最大年龄
4. select max(age) from students where sex='女';
4. 最小值min****
1. M in(字段)
1. # 查询students表中的最小年龄
2. select m in(age) from students;
3. # 查询students表中女生最小年龄
4. select m in(age) from students where sex='女';
5. 求和sum****
1. sum(字段)
1. # 查询students表中的年龄总和
2. select sum(age) from students;
3. # 查询students表中女生总和
4. select sum(age) from students where sex='女';
6. 平均avg****
1. avg(字段)
1. # 查询students表中的平均年龄
2. select avg(age) from students;
3. # 查询students表中女生平均年龄
4. select avg(age) from students where sex='女';
2.4.4.7. 数据分组group by****
1. group by(字段)
2. select 字段名,聚合函数 from 表 where 条件 group by 字段1,字段2;
1. 单字段分组****
1. #按照不同性别分组来查询students表中男女的总数
2. select sex,count(*) from students group by sex;
2. 多字段分组****
1. # 按不同班级,不同性别分组,查询students表中男女的总数
2. select class,sex,count(*) from students group by class,sex;
3.
4. # 按不同班级,不同职业分组,来统计students表中男女的总数
5. select class,position,count(*) from students group by class,position;
3. 分组后的排序****
1. # 按不同班级,不同性别分组,查询students表中男女的总数
2. select class,sex,count(*) from students group by class,sex;
3.
4. # 按不同班级,不同职业分组,来统计students表
1. # 查询sutudents表的男生总数
2.
3. # where
4. select count(*) from students where sex="男";
5.
6. # having
7. select count(*) from students group by sex having sex="男";
中男女的总数
5. select class,position,count(*) from students group by class,position;
2.4.4.8. 分组后筛选 having****
1. group by 字段 having 条件
面试题【2】
Having和Where的区别:
1. 筛选逻辑
(1) Where对原始数据进行筛选,原始数据量较大,效率较低
(2) Having使用group by分组后的数据进行筛选,效率较高
涉及SQL调优,与数据库的执行性能有关
2. Having可以使用聚合函数,where不可以
1. 配合聚合函数****
1. # 查询students表,统计班级人数大于3人的班级是?
2. select class from students group by class having count(*)>3;
2.4.4.9. 分页 limit****
位置:limit总是出现在select语句之后
1. limit 开始行,显示行数
2. 如:limit 0,3 :从第1行开始,显示三行,0可以省略 limit 3
1. limit 开始行,显示行数
2. 如:limit 0,3 :从第1行开始,显示三行,0可以省略 limit 3# 查询students表中前三条记录
3. select * from students limit 3;
4.
5. # 按照每页4条,展示2页
6. select * from students limit 4,4
面试题
SQL语句的综合书写顺序【2】
1. Select
2. From
3. Where
4. Group by
5. Having
6. Order by
7. Limit
1.1.1. 连表查询****
1. 数据准备****
Students表
Course表
Se表
1. # 数据准备
2. #课程数据
3. create table course(
4. id int primary key auto_increment,
5. name varchar(20) not null
6. );
7.
8. insert into course
9. values
10. (1,'大学语文'),
11. (2,'火系魔法导论'),
12. (3,'高等恋爱理论');
13.
14. # 成绩数据
15. create table se(
16. s_id int,
17. c_id int,
18. score int
19. );
20.
21. insert into se
22. values
23. (8,2,98),
24. (5,2,66),
25. (2,1,20),
26. (2,3,88),
27. (6,1,99);
2. 内连接 inner join****
1. select * from 表1 inner join 表2 on 表1.字段=表2.字段
1. # 将students表中学生的课程和分数结合
2. select * from students inner join se on students.id = se.s_id;
内连接显示指定字段
1. # students表与se内连接,只显示name,c_id,score
2. select students.name,se.c_id,se.score from students inner join se on students.id = se.s_id;
(1) 内连接查询指定字段****
1. # students表与se内连接,只显示name,c_id,score
2. select
3. s.name 姓名,se.c_id 课程id,se.score 成绩
4. from
5. students s
6. inner join se on s.id = se.s_id;
(2) 带有where的内连接****
1. select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件
1. # 查询猪八戒的成绩,要求显示姓名,课程号,成绩
2. select s.name 姓名,e.c_id 课程号,e.score 成绩
3. from students s inner join se e on s.id=e.s_id where s.name="猪八戒"
多表内连接
1. # 查询学生选课成绩,要求显示姓名,课程名,成绩
2. select
3. s.name 姓名,
4. c.name 课程名,
5. sc.score 成绩
6. from
7. students s
8. inner join sc on s.id=sc.s_id
9. inner join course c on sc.c_id=c.id;
写SQL三步法
1. 搭框架
(1) 基本的select语句,如果涉及多表,相应多表连接也写完
2. 看条件
(1) 决定where后面的具体条件
3. 显示字段
(1) Select后要显示的字段
3. 外连接****
(1) 左连接left join****
1. select 字段 from 表1 left join 表2 on 表1.字段=表2.字段
(2) 右连接right join****
1. select 字段 from 表1 right join 表2 on 表1.字段=表2.字段
4. 小结****
面试题
内连接、左连接、有链接的区别【3】
内连接:严格显示两张表连接字段相等的数据
左连接:显示左表全部数据,包括连接字段相等的数据,如果右表无数据则以null显示
右连接:显示右表全部数据,包括连接字段相等的数据,如果左表无数据则以null显示
5. 自关联****
同一张表做连接查询,一定要找到同一张表可关联的不同字段
自关联是一种关联的思路,并不属于关联方式
面试题
自关联使用场景【】
1. 同一张表中,有父子关系的字段时可能需要使用
(1) 如:行政区划表
(2) 如:商品类目表
6. 子查询****
子查询是嵌套在主查询里的
1. Where后做条件
2. From后作为数据源
3. 存储过程****
定义:存储在数据库服务器的SQL语句
作用:
1. 批量处理****
2. 重复使用
面试题
什么时候会用到存储过程【3】
1. 批量添加数据的时候用到
(1) 做性能测试时,会使用存储过程添加海量用户数据
(2) 对功能做一些极限验证时,如测试海量商品时分页功能和分类跳转功能时,需要加数据
创建存储过程,录入10000条数据
4. 非关系型数据库****
面试题
你用过redis嘛,有没有发现过什么bug?【3】
有。缓存雪崩,遇到一个线上的bug,redis缓存在某一时刻失效,大量数据请求到达mysql导致数据库崩溃。
当时的的做法是建议
1. 构建多级缓存,部署多个redis,避免同时过期
2. 建议后端做请求的队列机制
5. 物理删除和逻辑删除****
物理删除:执行的是delete操作,数据从硬盘中删除
逻辑删除:执行的是update操作,数据还存在硬盘中
面试题
使用数据库时,遇到过哪些bug?【3】
1. Oa项目,其中员工表在员工离职的功能上使用的是物理删除,这样不妥,因为这样就不知道哪个员工是“二进宫”了
2. 电商项目,其中商品的下架功能使用的是物理删除,这样不妥,因为这样再次商家该商品,这样又要重新操作商家流程,给商家带来了困扰。属于用户体验bug