测试常见八股之数据库基本概念

本文为测试或者测开面试时常会被问到的数据库相关知识点，当然由于涉猎内容较为宽泛，因此本文内容深度较浅，具体仍需要根据相关面试场景和题目进行深入学习，内容如有不全，烦请大家在下面补充

数据库知识点

1、数据类型

（1）基本概念

1. 数据：数据是描述事物、实体或概念的信息。数据库中的数据可以是数字、文本、图像、音频等形式。
2. 数据库管理系统（Database Management System，DBMS）：数据库管理系统是一种软件，用于管理和操作数据库。它提供了一组功能和工具，使用户能够创建、访问、更新和管理数据库。
3. 表（Table）：表是数据库中的基本组织单位，用于存储相关数据。表由行和列组成，行表示记录或数据的实例，列表示数据的属性或字段。
4. 字段（Field）：字段是表中的单个数据元素，代表数据的一个特定属性。每个字段具有名称和数据类型，如整数、字符串、日期等。
5. 记录（Record）：记录是表中的一行，表示一个完整的数据实例。它由一组字段值组成，每个字段值对应于记录在该字段中的数据。
6. 主键（Primary Key）：主键是表中唯一标识每个记录的字段或字段组合。它用于确保数据的唯一性和标识性，并在表中建立记录之间的关联。
7. 外键（Foreign Key）：外键是一个字段或字段组合，它与另一个表的主键形成关联。外键用于建立表之间的关系和引用，以实现数据的一致性和完整性。
8. 索引（Index）：索引是一种数据结构，用于提高数据库查询的性能。它存储了表中特定列的值和对应的行位置，以加快数据的查找和访问。
9. 查询（Query）：查询是使用特定语言（如结构化查询语言，SQL）编写的指令，用于从数据库中检索和操作数据。查询可用于搜索、过滤、排序和组合数据。
10. 视图（View）：视图是基于一个或多个表的查询结果，以虚拟表的形式呈现给用户。视图可以简化复杂的查询操作，并提供对特定数据的逻辑访问。

（2）常见数据模型

1. 层次模型（Hierarchical Model）：层次模型是早期的数据模型之一，它使用树状结构组织数据。数据通过父子关系连接，形成一个层次结构。每个父节点可以有多个子节点，但每个子节点只能有一个父节点。层次模型适用于表示具有明确父子关系的数据，如组织结构、文件系统等。
2. 网状模型（Network Model）：网状模型也是早期的数据模型之一，它使用复杂的网络结构组织数据。数据通过节点和边连接，形成复杂的图形结构。在网状模型中，一个节点可以与多个其他节点连接，而不限于父子关系。网状模型适用于表示具有复杂连接关系的数据，如网络拓扑、部件关系等。
3. 关系模型（Relational Model）：关系模型是当前最常用的数据模型之一，它使用表、行和列的结构组织数据。数据以关系（表）的形式存储，每个关系由多个属性（列）组成，每个关系的实例（行）表示一个数据记录。关系模型使用关系代数和SQL（Structured Query Language）进行数据查询和操作。关系模型提供了灵活性、简洁性和标准化的数据表示方式，适用于大多数企业应用和数据库系统。

（3）数据库三级模式

数据库的三级模式是指外模式（External Schema）、概念模式（Conceptual Schema）和内模式（Internal Schema），也被称为三级抽象。它们表示了数据库在不同层次上的不同视图和描述。

1. 外模式（External Schema）： 外模式是用户对数据库的可见部分，它描述了用户的视图和对数据的访问方式。
2. 概念模式（Conceptual Schema）： 概念模式是数据库的全局逻辑结构和整体描述。它定义了数据库中所有数据的逻辑结构、关系和约束，独立于具体的应用程序和用户需求。概念模式提供了一个中间层，将外模式和内模式连接起来。它是数据库设计的核心，包括实体、关系、属性、关系约束等。概念模式使得不同用户可以共享同一数据结构和一致的数据定义，提供了数据的一致性和数据独立性。
3. 内模式（Internal Schema）： 内模式是数据库的物理存储和底层实现方式的描述。它定义了数据在存储介质上的组织方式、索引结构、数据存储格式等底层细节。内模式通常是与数据库管理系统（DBMS）紧密关联的，它描述了数据在存储层面上的物理表示。内模式隐藏了底层细节，为上层提供了一个抽象的接口，使得外模式和概念模式可以独立于物理实现进行操作和查询。

2、约束

（1）主键、外键和索引之间的区别

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。

3、数据库操作

（1）创建数据表

-- 创建表
-- create table 表名(
-- 字段名 类型 约束,
-- 字段名 类型 约束

-- 简单创建
create table stu(
	name varchar(5)
);

-- 完整创建
-- unsigned : 无符号
-- primary key : 主键，值不能重复
-- auto_increment : 自动增长
create table students(
    id int unsigned primary key auto_increment,
    name varchar(20),
    age int unsigned,
    height decimal(5,2)
);

（2）查看表

-- 查看表信息
-- show create table 表名;
show create table students;

-- 执⾏结果
-- CREATE TABLE `students` (
-- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-- `name` varchar(20) DEFAULT NULL,
-- `age` int(10) unsigned DEFAULT NULL,
-- `height` decimal(5,2) DEFAULT NULL,
-- PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

扩展：判断表是否存在, 存在时先删除再创建

-- drop table : 删除表
-- if exists students : 如果 students 存在
drop table if exists students;
create table students(
    id int unsigned primary key auto_increment,
    name varchar(20),
    age int unsigned,
    height decimal(5,2)
);

查看表结构和删除表

-- 查看表结构(字段)
-- desc 表名;
desc students;
-- 删除表
-- drop table 表名;
drop table students;

4、查询语句

写SQL三步法

• 搭框架 基本的select语句框架搭建起来，如果有多表，把相应的多表也联合起来
• 看条件 决定where后面的具体条件
• 显示的字段 select后面到底要显示什么字段

（1）基本查询

-- 需求1: 准备商品数据, 查询所有数据, 查询部分字段, 起字段别名, 去重
1、查询所有数据: select * from 表名;
select * from goods;

2、查询部分字段: select 字段名1, 字段名2 from goods;
select goodsName, price from goods;

3、起字段别名: select 字段名 as '别名' from goods;
select goodsName as '商品名称', price as '价格' from goods;
-- 注意: 别名的引号可以省略
select goodsName as 商品名称, price as 价格 from goods;
-- 注意: as 关键字也可以省略[掌握]
select goodsName 商品名称, price 价格 from goods;
-- 起别名的作⽤用: 1、美化数据结果的显示效果 2、可以起到隐藏真正字段名的作⽤
-- 另: 除了可以给字段起别名以外, 还可以给数据表起别名(连接查询时使⽤用)

4、去重: select distinct(字段名) from goods;
-- 效果: 将⽬目标字段内重复出现的数据只保留留⼀一份显示
-- ⼩小需求: 显示所有的公司名称
select distinct(company) from goods;

（2）条件查询 where

比较运算符 and 逻辑运算符

-- 需求2: 查询价格等于30并且出⾃自并夕夕的所有商品信息
select * from goods;
1、查询价格等于30 : ⽐比较运算符 特殊: (⼤于等于)>= (⼩于等于)<= (不等于)!= <>
select * from goods where price=30;

2、并且出自并夕夕的所有商品信息 : 逻辑运算符 and(与) or(或) not(⾮)
-- 注意: 作为查询条件使⽤用的字符串串必须带引号''
select * from goods where price=30 and company='并夕夕';

-- 补充需求: 查询价格等于30但不出⾃自并夕夕的所有商品信息
select * from goods where not company='并夕夕' and price=30;
-- 注意: not 与 and 和 or (左右两边连接条件)不同之处在于, not 只对自己右侧的条件有作用(右边连接条件)
select * from goods where price=30 and not company='并夕夕';

（3）模糊查询（like）

-- 需求3: 查询全部一次性口罩的商品信息
-- 模糊查询: like 和符号 %(任意多个字符)/_(任意⼀一个字符)
-- 注意: 作为查询条件使用的字符串串必须带引号!
-- 注意: 如果需要控制字符数量量, 需要使用_, 并且有几个字符就使⽤用几个_
-- %关键词% : 关键词在中间
select * from goods where remark like '%一次性%';
-- %关键词 : 关键词在末尾
select * from goods where remark like '%一次性';
-- 关键词% : 关键词在开头
select * from goods where remark like '⼀次性';

（4）范围查询（in and between）

-- 需求4: 查询所有价格在30-100的商品信息
-- 范围查询: 
	1、非连续范围: in 
	2、连续范围: between ... and ...

select * frome students where hometown = '北京' or hometown = '上海' or hometown = '广东';
select * frome students where hometown in ('北京', '上海', '广东');

select * from goods where price >= 30 and price <= 100;
select * from goods where price between 30 and 100;
-- 注意: between ... and ... 的范围必须是从小到大
select * from goods where price between 100 and 30;

（5）判断空（is and is not）

-- 需求5: 查询没有描述信息的商品信息
-- 注意: 在 MySQL 中, 只有显示为 NULL 的才为空! 其余空⽩白可能是空格/制
表符(tab)/换行符(回⻋车键)等空⽩白符号
-- 判断空: 
    1、为空: is null 
    2、不为空(双重否定表肯定): is not null
    
select * from goods where remark is null;

-- 补充需求: 查询有描述信息的所有商品
select * from goods where remark is not null;           

（6）复杂查询

排序（order by）

-- 需求6: 查询所有商品信息, 按照价格从⼤到小排序, 价格相同时, 按照数量少到多排序
-- select * from 表名 order by 列1 asc|desc,列2 asc|desc,...
-- 说明: 
    1、order by 排序,
    2、asc（ascending） : 升序,
    3、desc（descending） : 降序
-- 注意: 排序过程中, 支持连续设置多条排序规则, 但离 order by 关键字越近, 排序数据的范围越大!
select * from goods order by price desc;
select * from goods order by price desc, count asc;

-- 注意: 默认排序为升序, asc 可以省略
select * from goods order by price desc, count;   #省略asc        

聚合函数（count、max、min、avg、sum）

- 需求7: 查询以下信息: 商品信息总条数; 最高商品价格; 最低商品价格;
商品平均价格; ⼀一次性⼝口罩的总数量量
-- 聚合函数: 系统提供的⼀一些可以直接⽤用来获取统计数据的函数
1、商品信息总条数: count(字段): 查询总记录数
select count(*) from goods;
-- 注意: 统计数据总数, 建议使用*, 如果使⽤用某一特定字段, 可能会造成数据总数错误!
select count(remark) from goods;

2、最高商品价格: max(字段): 查询最大值
select max(price) from goods;
3、最低商品价格: min(字段): 查询最小值
select min(price) from goods;
4、商品平均价格: avg(字段): 求平均值
select avg(price) from goods;
5、一次性口罩的总数量: sum(): 求和 
-- 注意: 此处的 count 是数据表中字段名!
select sum(count) from goods where remark like '%一次性%';

-- 扩展: 在需求允许的情况下, 可以一次性在一条 SQL语句句中, 使⽤用所有的聚合函数
select count(*), max(price), min(price), avg(price) from goods;                                   

分组（group by and having）

-- 需求8: 查询每家公司的商品信息数量
-- 分组: select 字段1,字段2,聚合... from 表名 group by 字段1,字段2...
-- 说明: group by : 分组
-- 注意:
    1、一般情况, 使⽤用哪个字段进行分组, 那么只有该字段可以在 * 的位置处使用, 其他字段没有实际意义(只要一组数据中的一条)
    2、分组操作多和聚合函数配合使用
    
select count(*) from goods group by company;
select * from goods;
select company, count(*) from goods group by company;
-- 说明: 其他字段没有实际意义(只要一组数据中的一条)
select price, count(*) from goods group by company;
 
-- 扩充: 分组后条件过滤
-- 说明: group by 后增加过滤条件时, 需要使用 having 关键字
-- 注意:
    1. group by 和 having 一般情况下需要配合使用，having不能单独出现
    2. group by 后边不推荐使用 where 进行条件过滤
    3. having 关键字后侧可以使用的内容与 where 完全一致(比较运算符/逻辑运算符/模糊查询/判断空)

-- 使用where查询男生总数 先筛选再统计   
select count(*) from students where sex = '男';
-- 使用group by和having查询男生总数 先分组聚合统计，在统计的结果中筛选
select count(*) from students group by sex having sex = '男';

-- having 关键字后侧允许使用聚合函数,但是where不可以
-- 求班级人数大于3人的班级名字
select class count(*) from students group by class having count(*)>3

-- where 和 having 的区别:
    1、where 是对 from 后⾯面指定的表进⾏行行数据筛选，属于对原始数据的筛选
    2、having 是对 group by 的结果进⾏行行筛选
    3、having 后⾯面的条件中可以⽤用聚合函数，where 后面不可以                                

条件查询与分组和排序联合使用

-- 练习：统计各个班级学生总数、平均年龄、最大年龄、最小年龄，但不统计'3班'，统计结果按照班级名称从大到小排序
-- where在group by前，先筛选（去掉3班）再分组
select class count(*) avg(age) max(age) min(age) from students 
where class <> '3班' group by class order by class desc;

GROUP BY 和 PARTITION BY 的区别

1、GROUP BY用于将数据按照某个或多个列的值进行分组，然后对每个分组进行聚合操作。GROUP BY通常与聚合函数（如SUM、COUNT、AVG等）一起使用，以计算每个分组的统计结果。GROUP BY生成的结果集中，每个分组都有唯一的键值，并且可以使用HAVING子句对结果进行进一步过滤。

2、PARTITION BY用于将表或索引的数据划分为多个分区，每个分区可以单独进行管理和操作。PARTITION BY通常用于优化大型表的查询性能，通过将数据分散存储在不同的分区中，可以减少查询的范围。PARTITION BY可以按照列的值范围、列表或哈希值等方式进行分区，提供了灵活的分区策略选择。

区别分析：

• 功能不同：GROUP BY用于对数据进行分组和聚合，得到每个分组的统计结果。PARTITION BY用于将表或索引的数据划分为多个分区，以提高查询性能。
• 数据操作层面不同：GROUP BY操作在查询结果集上进行，不会改变数据表的物理存储结构。PARTITION BY操作在数据表或索引的存储层面进行，会改变数据的物理分布。
• 使用场景不同：GROUP BY适用于对查询结果进行分组和聚合操作，常用于统计分析、报表生成等场景。PARTITION BY适用于大表的数据管理和查询优化，常用于分布式存储、数据仓库等场景。

分页查询（limit）

-- 需求9: 查询当前表当中第5-10行的所有数据
-- 分⻚页查询: select * from 表名 limit start,count
-- 说明: 
    1、limit 分页; 
    2、start : 起始行号; 
    3、count : 数据行数
-- 注意: 计算机的计数从 0 开始, 因此 start 默认的第⼀一条数据应该为 0,后续数据依次减1
-- 过渡需求: 获取前 5 条数据
select * from goods limit 0, 5;
-- 注意: 如果默认从第一条数据开始获取, 则 0 可以省略!
select * from goods limit 5;
-- 需求:
select * from goods limit 4, 6;

-- 查询年龄最大女同学的name
select name from students where sex = '女' order by age desc limit 1;

-- 扩展 1: 根据公式计算显示某页的数据
-- 已知：每页显示m条数据，求：显示第n页的数据
-- select * from 表名 limit (n-1)*m, m
-- 示例: 每页显示 4 条数据, 求展示第 2 页的数据内容
select * from goods limit 0, 4; -- 第1页(有数据)
select * from goods limit 4, 4; -- 第2页(有数据)
select * from goods limit 8, 4; -- 第3页(有数据)
select * from goods limit 12, 4; -- 第4页(一共12条数据, 每页显示4条, 没有第4页数据)

-- 扩展 2: 分页的其他应用
-- 需求: 要求查询商品价格最贵的数据信息
select * from goods order by price desc limit 1;
-- 进阶需求: 要求查询商品价格最贵的前三条数据信息
select * from goods order by price desc limit 3;

（7）连接查询

当查询结果来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的结果返回

内连接（inner join）

查询结果只显示两个表中满足连接条件的部分，最重要找到多表可关联的字段

-- 需求1: 查询所有存在商品分类的商品信息
select * from goods;
select * from category;
-- 内连接: select * from 表1 inner join 表2 on 表1.列=表2.列
-- 显示效果: 两张表中有对应关系的数据都会显示出来, 没有对应关系的数据均不再显示
select * from goods
inner join category on goods.typeId=category.typeId;

-- 扩充: 给表起别名(1> 缩短表名利利于编写 2> ⽤用别名给表创建副本)
select * from goods go
inner join category ca on go.typeId=ca.typeId;

-- 扩展: 内连接的另一种写法(旧式写法)
-- select * from 表1, 表2 where 表1.字段名=表2.字段名;
select * from goods, category 
where goods.typeId=category.typeId;

-- 练习: 查询王昭君的信息，要求只显示姓名、课程号、成绩
select name 姓名,courseNo 课程号, score 成绩 from students st
inner join scores sc on st.id = co.id where st.name = '王昭君' and sc.score >= 90;

-- 多表内连接查询：查询学生信息和成绩以及成绩对应的课程名称
select * from students st
inner join scores sc on st.id = sc.id
inner join courses co on sc.courseNo = co.courseNo;

-- 练习: 查询成绩最高的男生信息，要求显示姓名、课程名、成绩
select name, courseName, score from students st
inner join scores sc on st.id = sc.id
inner join courses co on sc.courseNo = courses.courseNo
where st.sex = '男'
order by sc.score desc
limit 1;

左连接（left join）

查询结果为两个表匹配到的数据加左表特有的数据，对于右表不存在的数据使用null填充（以左为主）

-- 需求2: 查询所有商品信息,包含商品分类
-- 左连接: select * from 表1 left join 表2 on 表1.列=表2.列
-- 注意: 如果要保证一张数据表的全部数据都存在, 则一定不不能选择内连接,可以选择左连接或右连接
-- 说明:
-- 以 left join 关键字为界, 关键字左侧表为主表(都显示), ⽽而关键字右侧的表为从表(对应内容显示, 不不对应为 null)
select * from goods go
left join category ca on go.typeId=ca.typeId;
-- 扩充需求: 以分类为主展示所有内容(以哪张表为主表, 显示结果上是有区别的!)
select * from category ca
left join goods go on ca.typeId=go.typeId;

-- 练习 查询所有学生的信息以及成绩，包括没有成绩的学生信息(students,scores)
select * from students
left join scores on students.id = scores.id;

右连接（right join）

查询结果为两个表匹配到的数据加右表特有的数据，对于左表不存在的数据使用null填充（以左右为主）

select * from students
left join scores on students.id = scores.id;-- 需求3: 查询所有商品分类及其对应的商品的信息
-- 右连接: select * from 表1 right join 表2 on 表1.列列=表2.列列
-- 说明:
-- 以 right join 关键字为界, 关键字右侧表为主表(都显示), ⽽而关键字左侧
的表为从表(对应内容显示, 不不对应为 null)
select * from goods go
right join category ca on go.typeId=ca.typeId;
-- 扩充需求: 查询所有商品信息及其对应分类信息
select * from category ca
right join goods go on ca.typeId=go.typeId;

-- 练习: 查询所有课程的信息，包括没有成绩的课程(scores,courses)
select * from scores
right join courses on scores.courseNo = courses.courseNo;

扩展1：同名字段

-- 多表联合查询的同名字段前要加上表名，加以区分
select students.studentNo from students
inner join scores on students.studentNo = scores.studentNo;

扩展2：自关联

前提: 1> 数据表只有⼀一张 2> 数据表中至少有两个字段之间有某种联系方式: 通过给表起别名的形式, 将原本只有一张的数据表变为两张, 然后通过对应字段实现连接查询

-- 查询河南省下所有市的信息
-- 需求4: 查询河南省所有的市
-- 说明: 无论是使用内连接还是左连接, 都只影响中间数据表的内容多少, 由
于最终的过滤条件相同, 因此查询结果一致
-- 使⽤用内连接
select * from areas a1
inner join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';
-- 使⽤用左连接
select * from areas a1
left join areas a2 on a1.aid=a2.pid
where a1.atitle='河南省';

-- 需求5: 查询河南省的所有的市和区
-- 说明: 想要实现三级行政单位显示, 需要分别处理理省和市及市和区(三表连查)
select * from areas a1
left join areas a2 on a1.aid=a2.pid
left join areas a3 on a2.aid=a3.pid
where a1.atitle='河南省'

扩展3 左右连接时查询到不存在的数据如何处理

1、IFNULL() 使用

## 假设要查询 pay 这张表所有的支付记录，如果产品名称为 NULL，则统一填充 “未知”
SELECT id,user_id,IFNULL(product_name,"未知") FROM `pay`;

2、Case… When… Then… Else… End… 多条件判断用法

## Case… When… Then… Else… End… 多条件判断用法
SELECT
	p.id AS id,
	p.user_id AS userId,
	IFNULL( p.product_name, "未知" ) AS productName,
CASE
	WHEN ( p.user_id = 0 ) THEN
	'机器人' ELSE u.`name` 
	END AS userName 
FROM
	`pay` p
LEFT JOIN USER u ON p.user_id = u.id

补充

存在左右连接的必要性

说明：能够体现左右连接必要性的场景为: 至少为三张表进行连接查询

注意: 实际工作中, 最多也就三张表连接查询

（8）子查询

子查询: 在一个 select 语句句中，嵌入了另外一个 select 语句句，那么嵌入的select 语句句称之为子查询语句

子查询是可以独立的查询语句，可以独立运行

作用: 子查询是辅助主查询的，要么充当[条件]，要么充当[数据源]

-- 例1: 查询大于平均年龄的学生记录(充当[条件])
select avg(age) from students;
select * from students where age > ();
使用子查询实现: 标量子查询
select * from students where age > (select avg(age) from students)

-- 例2: 查询30岁的学生成绩
select studentsNo from students where age = 30;
select * from scores where studentsNo in ('','','');
使用子查询实现：列子查询(子查询返回一列多行)
select * from scores where studentsNo in (select studentsNo from students where age = 30)

-- 例3: 查询所有女生的信息和成绩
select * from students inner join courses
on students.id = courses.id
where sex = '女';
子查询实现: 表级子查询(子查询返回多行多列)
select * from (select * from students where sex = '女') st
inner join scores sc on st.id = sc.id;

- 需求7: 查询所有来自并夕夕的商品信息, 包含商品分类(充当[数据源])
-- 子查询语句充当数据源:
-- select * from goods go
-- left join category ca on go.typeId=ca.typeId;
-- select * from (select * from goods go left join category ca on go.typeId=ca.typeId) new
-- where new.company='并夕夕';
-- 问题: 连接查询的结果中, 表和表之间的字段名不不能出现重复, 否则无法直
接使用
-- 解决: 将重复字段使⽤用别名加以区分(表名.* : 当前表的所有字段)
select * from (select go.*, ca.id cid, ca.typeId ctid,
ca.cateName from goods go left join category ca on
go.typeId=ca.typeId) new
where new.company='并夕夕'; 

5、数据操作

（1）插入数据（insert）

增加一行数据

-- 增加数据
-- 增加一行数据
-- insert into 表名 values(...)
-- 注意:
-- 1. 数据值需要和表的字段⼀一对应(数据个数及数据类型)
-- 2. 主键列是自动增长，插入时需要占位，通常使⽤用 0 或者 default 或者null 来占位，插入成功后以实际数据为准
insert into students values(0, '张三', 28, 1.78);

-- 若表中只有3条数据，自增长字段直到3，插入的时候指定id的值
insert into students(id, name, age) values(6, '张飞', 20)
-- 随后插入的时候不指定id的值，自增长字段随上一条数据继续增长，变为7
insert into students(name, age) values('张飞', 20)


-- 增加部分值
-- insert into 表名(字段1,...) values(值1,...)
-- 注意: 值的顺序与给出的字段顺序对应
insert into students(name, height) values('李四', 1.68);

增加多行数据

-- 插⼊入多行数据
-- 方式1: 将单行插入语句, 多句执行, 每句分号隔开
insert into students values(0, '王五', 28, 1.78);
insert into students(name, height) values('赵六', 1.68);

-- 方式2: 在插⼊入单行数据的语法基础上, 将 value 后边的数据进行多组化处理
-- insert into 表名 values(...),(...)...
-- insert into 表名(列列1,...) values(值1,...),(值1,...)...
insert into students 
	values(0, '王五1', 29, 1.78),(0, '王五2',30, 1.78);
insert into students(name, height) 
	values('赵六1', 1.78),('赵六2', 1.88);

（2）修改数据（update）

-- 修改数据
-- update 表名 set 列1=值1,列2=值2... where 条件
-- 注意: where 不能省略略, 否则会修改整列数据
update students set age=48 where id=9;

-- 修改数据使用加减乘除
update students set age = age + 10 where id=9;
update students set acount_money = acount_money / 10 where id=9;

（3）删除数据（delete and truncate）

-- 删除数据
-- delete from 表名 where 条件;
-- 注意: where 不能省略略, 否则会删除全部数据
delete from students where id = 6;
delete from students where age >= 50;

扩展1：逻辑删除

逻辑删除: 对于重要的数据，不能轻易执行 delete 语句句进行删除。因为一旦删除，数据无法恢复，这时可以进行逻辑删除。1、给表添加字段，代表数据是否删除，一般起名 isdelete，0代表未删除，1代表删除，默认值为02、当要删除某条数据时，只需要设置这条数据的 isdelete 字段为13、以后在查询数据时，只查询出 isdelete 为0的数据

-- 扩展 1: 逻辑删除(假删/标记删除)
-- 1> 修改要删除的数据的特定字段为删除状态
update students set isdelete=1 where id=4;
-- 2> 查询所有 isdelete 字段为 0 的所有数据
select * from students where isdelete=0;

扩展2：其他删除数据的方法

-- 扩展 2: 其他数据删除⽅方法
-- delete from 表名 : 删除所有数据, 但是不重置主键字段的计数
-- truncate table 表名 : 删除所有数据, 并重置主键字段的计数
-- drop table 表名 : 删掉表(字段和数据均不不再存在)
速度上：truncate > delete
如果想删除部分数据只能用delete，注意带上where语句
如果想要保留表而将所有数据删除，自增长字段恢复从1开始，用truncate，如果使用delete后，再次插入数据，自增长字段会从上次的继续变化

delete from students;
truncate table students;
drop table students;

6、存储过程（PROCEDURE）

存储过程：PROCEDURE，也翻译为存储程序，是一条或者多条SQL语句的集合，一次编译然后被缓存起来，可以理解为一个函数调用

create PROCEDURE 存储过程名称
begin
	sql语句
end

-- 例1: 创建存储过程stu()，查询students表所有学生信息
create PROCEDURE stu()
begin
	select * from students;
end

-- 调用存储过程
call stu();

-- 删除存储过程
drop PROCEDURE stu();

7、视图（VIEW）

对于复杂查询，在多个地方被使用，如果需求发生改变，需要更改SQL语句，则需要在多个地方进行修改，维护起来较为麻烦。

视图本质就是对查询的封装。

-- 语法
create VIEW 视图名称 as select语句

-- 例1 创建一个视图，查询所有男生的信息
create VIEW stu_nan as
select * from students where sex = '男';

-- 调用视图，视图生成的是一张只读的表，是一张表的封装
select * from stu_nan where age > 30;

-- 删除视图
drop VIEW stu_nan;
drop VIEW IF EXISTS stu_nan;

8、事务

事务的多条更改数据操作的SQL语句的集合；它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

1、开启事务

BEGIN：开启事务后执行修改update或者删除delete记录语句，变更会写到缓存中，而不会立即生效。

2、回滚事务

ROLLBACK：放弃修改。

3、提交事务

COMMIT：将修改的数据写入实际的表中。

-- 例1: 删除students表中shudentsNo为001的记录，删除scores表中shudentsNo为001的记录，最后回滚事务
-- 没有begin的非事务的表操作是实施生效的，但是加上begin后但没有使用commit，表操作后先写入缓存，并不会马上生效
BEGIN;
delete * from students where shudentsNo = 001;
delete * from scores where shudentsNo = 001;
ROLLBACK;

-- 例2: 删除students表中shudentsNo为001的记录，删除scores表中shudentsNo为001的记录，最后提交事务
-- commit提交事务是真删除
BEGIN;
delete * from students where shudentsNo = 001;
delete * from scores where shudentsNo = 001;
COMMIT;

如果开始一个事务，执行begin后，没有rollback和commit操作，中间系统出现问题，默认执行rollback

9、索引

index：当表中数据量很大时，可以添加索引提高select查询速度（优点）

使用索引的场景： 排序操作的列、频繁查询的列、表连接的列

索引类型：B-Tree 索引（Balanced Tree，由多个节点组成，每个节点有多个子节点）、Hash 索引（基于哈希表）、Bitmap 索引（将每个可能的列值映射为一个位图（bit-map））

-- 创建索引
create index 索引名称 on 表名(字段名称(长度));
-- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
-- 字段类型如果不是字符串，可以不填写长度部分

-- 例1: 为表students的age字段创建索引，，名为age_index
create index age_index on students(age);

-- 例2: 为表students的name字段创建索引，名为name_index
create index name_index on students(name(10));

-- 调用索引,where 后面的字段，数据库系统会自动查找是否有索引
select * from students where age = 30;
-- 自动调用name_index
select * from students where name = '李白';

-- 查看索引,主键，系统自动创建索引
show index from students;

-- 删除索引
drom index age_index on students;

缺点：

• 虽然索引提高了查询的速度，但同时却会降低更新表的速度，例如对表执行insert、update和delete不仅要保存数据，还要保存索引文件。
• 在实际应用中，执行select语句的次数远远大于执行insert、update和delete的次数，甚至可以占到百分之80%-90%，所以为表建立索引是必要的
• 在大量数据插入时，可以先删除索引，再批量插入数据，最后再添加索引，这样可以提高数据插入的效率

10、MySQL内置函数

（1）字符串函数

• 拼接字符串 concat(str1, str2, ...)
• 计算字符串长度 length(str1)
• 内置函数可以在where后使用
• 截取字符串 左侧截取left(str, len)，右侧截取right((str, len)，指定位置截取substring(str,pos,len)
• 去除左侧空格 ltrim，右侧空格 rtrim, 去除两侧的空格trim

（2）四舍五入函数 round

-- 例1: 1.653四舍五入，保留整数位
select round(1.653);
-- 例1: 1.653四舍五入，保留两位小数
select round(1.653，2);

（3）日期和时间相关函数

-- 当前日期
select current_date();
-- 当前时间
Select current_time();
-- 当前日期与时间
select now();

-- 插入记录时附带当前时间
insert into students(id,name,indate) values(0,'张飞',now());

11、数据库面试常见问题

[问题1] MySQL引擎常见有什么，各引擎之间有什么区别

1. 主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：InnoDB 支持事务，MyISAM 不支持。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原， 而MyISAM 就不可以了。MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；
2. InnoDB 支持外键，MyISAM 不支持；
3. MyISAM 是默认引擎，InnoDB 需要指定； InnoDB 不支持 FULLTEXT 类型的索引。

[问题2] 数据的完整性

数据完整性指的是存储在数据库中的数据的一致性和准确性。

完整性分类：（1） 实体完整性：主键值必须唯一且非空。（主键约束）（2） 引用完整性（也叫参照完整性）：外键要么为空，要么引用主表中存在的记录。（外键约束）。（3） 用户自定义完整性：针对某一具体关系数据库中的约束条件。

[问题3] 事务的特性

1. 原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
2. 一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
3. 隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。
4. 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

[问题4] Sql 注入是如何产生的，如何防止？

程序开发过程中不注意书写规范，对sql语句和关键字未进行过滤，导致客户端可以通过全局变量get或者post提交sql语句到服务器端正常运行，从而产生 Sql 注入。

下面是防止办法：

a. 过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤。 
b. 在 PHP 配置文件中将 Register_globals=off;设置为关闭状态 
c. SQL 语句书写的时候尽量不要省略小引号(tab 键上面那个)和单引号 
d. 提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的 
e. 对于常用的方法加以封装，避免直接暴漏 SQL 语句 
f. 开启 PHP 安全模式：Safe_mode=on; 
g. 打开 magic_quotes_gpc 来防止 SQL 注入 
h. 控制错误信息：关闭错误提示信息，将错误信息写到系统日志。 
i. 使用 mysqli 或 pdo 预处理。

[问题5] MySQL 的默认隔离级别

读未提交（Read Uncommitted）

读已提交（Read Committed）

可重复读（Repeatable Read）

串行化（Serializable）