数据库学习笔记

[hantianzhun]

1. 为什么要学习数据库？在工作中数据库的作用

1.1作用

• 数据库用于存储和管理数据。

• 测试场景：在测试过程中，常用数据库校验数据是否正确。

• 工作应用

  • 数据校验：验证系统生成的数据是否符合预期。
  • 构造测试数据：通过数据库插入或修改必要的数据（如insert into语句）。
  • 核验功能：如注册、登录、添加商品、购物车、订单、搜索商品等功能。

1.2性能测试

• 模拟大量用户操作系统（如1000个用户同时登录），通过数据库记录和分析数据行为。

---

2. 数据库基础

2.1数据库的分类

• 关系型数据库（以表为单位）：如 MySQL、Oracle。
• 非关系型数据库（键值对存储）：如 Redis。

2.2基本概念

• 数据：可以是数字、文字、图片、音频、视频等。
• 表：由行和列组成，每行代表一条记录，每列代表一个字段。
• 数据库：数据存储的仓库，一个数据库可以包含多个表。

2.3数据分表的必要性

• 避免单张表数据量过大，导致查询效率低下。
• 减少数据冗余和损坏风险。

---

3. 数据库学习内容

3.1表的操作：

3.1.1创建表

CREATE TABLE 表名(
    字段1 数据类型(尺寸),
    字段2 数据类型(尺寸),
    ...
);

3.1.2修改表

• 增加字段：ALTER TABLE 表名 ADD 字段名 数据类型(尺寸);

• 删除字段：ALTER TABLE 表名 DROP COLUMN 字段名;

• 修改字段数据类型：ALTER TABLE 表名 MODIFY 字段名 新的数据类型(尺寸);

• -- 1.给表中增加一个字段
  alter  table  表名   add   字段名  数据类型(尺寸);
  
  -- 2.给表中删除一个字段
  alter  table  表名   drop  column   字段名;
  
  -- 3.修改表中的字段数据类型
  alter  table  表名   modify   字段名   新的数据类型(新的尺寸);

3.1.3删除表

• 删除表数据及结构：DROP TABLE 表名;
• 仅删除表中的数据：DELETE FROM 表名;
• 快速删除数据：TRUNCATE TABLE 表名;

3.2表中数据的操作

3.2.1插入数据

INSERT INTO 表名 VALUES (值1, 值2, 值3);

3.2.2更新数据

UPDATE 表名 SET 字段名=新值 WHERE 条件;

3.2.3查询数据

SELECT * FROM 表名;

3.3表结构查看

• 查看表数据：SELECT * FROM 表名;
• 查看表结构：DESCRIBE 表名;

---

4. 表的约束

4.1常见约束类型

• PRIMARY KEY：主键，唯一且不能为空。
• UNIQUE：唯一约束，可以为空。
• NOT NULL：非空约束。
• CHECK：检查约束。
• FOREIGN KEY：外键约束，用于表之间的关联。

4.2约束定义方式

• 创建表时定义。
• 创建表后通过修改方式添加。

---

5. 数据类型

5.1整数

• 如：INT、SMALLINT、BIGINT。

5.2浮点数

• 如：DOUBLE(m, n)，m为总位数，n为小数点后的位数。

5.3字符串

• CHAR：固定长度。
• VARCHAR：可变长度。

5.4日期

• 如：DATE、YEAR、DATETIME。

---

6. SQL语句基础

6.1SQL语句模版

select   *  |  表1.字段1，表2.字段2，表3.字段3  |  max(),avg() ,count(),min(),sum()
from   表1  
left   join 表2
on  表1.XX =表2.XXX
left  join   表3
on    表2.XXX=表3.XXX
where   字段1>     and   字段2 <    or   字段3！=
group   by   表3.字段3 
having   max（）>   avg()<   count()=
order  by    表.字段  ASC ,表.字段2  desc

6.2操作顺序

1. FROM：定义数据来源的表。
2. WHERE：过滤条件。
3. GROUP BY：分组。
4. HAVING：过滤分组数据。
5. SELECT：选择查询字段。
6. ORDER BY：排序。

6.3排序

• 升序：ASC。
• 降序：DESC。

---

7. 查询操作

7.1精确查询

SELECT * FROM 表名 WHERE 字段名 = 值;

7.2模糊查询

• %：匹配任意长度字符。
• _：匹配一个字符。

SELECT * FROM 表名 WHERE 字段名 LIKE '张%';

7.3去重

SELECT DISTINCT 字段 FROM 表名;

7.4别名

SELECT 字段 AS 别名 FROM 表名;

---

7.5运算符

'算数运算符' + - * / '两个字段的数据类型相同，且是int或者是double'
'比较运算符' > < = >= <= != 'ASCII排序'
'逻辑运算符' and  '并且 当多个条件同时满足的时候结果才为真'
         or '或者 当有一个条件为真时，结构为真'
         not '非 取反'
         
'limit 的使用'
limit m,n m代表的是从第几条开始取值，n代表的是去几条数据
limit 0,1 从第一条开始取，去1条数据
'求出这个表中第三名到第五名的学生信息'
limit 2,3

8. 多表连接

8.1内连接

• 仅显示两个表中满足条件的数据。

SELECT * FROM 表1, 表2 WHERE 表1.字段 = 表2.字段;

8.2左连接

• 以左表为主，右表不足部分用 null 填充。

SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

8.3右连接

• 以右表为主，左表不足部分用 null 填充。

SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

8.4自连接

• 表自身与自身连接。

---

9. 聚合函数

• MAX()：求最大值。
• MIN()：求最小值。
• AVG()：求平均值。
• SUM()：求和。
• COUNT()：统计数量。
• 当字段和聚合函数同时出现在select 后面的时候，group by 后面一定要接这个字段
• ASII码：A 65 a 97

---

10. 常用函数

• NOW()：获取系统当前时间
• YEAR()：提取年份
• MONTH()：提取月份
• DAY()：提取天数
• IFNULL()：将NULL值转换为其他值（如0）
• DATEDIFF()：计算两个日期的差值（天数）
• date_formate(date,'%Y-%m-%d') ：第一个字段传入日期字段， 第二个值传入要显示的日期格式

---

11. 数据库远程连接工具

• Sqlyog
• Navicat

---

12. 常用场景

12.1功能测试

• 如电商系统的搜索功能：通过 SQL 按条件（价格、品牌等）查询数据，比对页面展示是否与数据库一致。

12.2计算验证

• 比如银行系统的还款计算（提前还款、逾期还款等），通过 SQL 比对利息、金额计算结果的准确性。