从入门到精通【MySQL】 CRUD

文章目录

• 📕1. Create 新增
  • ✏️1.1 单行数据全列插入
  • ✏️1.2 单行数据指定列插入
  • ✏️1.3 多行数据指定列插入
• 📕2. Retrieve 检索
  • ✏️2.1 全列查询
  • ✏️2.2 指定列查询
  • ✏️2.3 查询字段为表达式
  • ✏️2.4 为查询结果指定别名
  • ✏️2.5 结果去重查询
• 📕3. WHERE条件查询
  • ✏️3.1 基本查询
  • ✏️3.2 AND和OR
  • ✏️3.3 AND和OR的优先级
  • ✏️3.4 范围查询
  • ✏️3.5 模糊查询
  • ✏️3.6 NULL的查询
  • ✏️3.7 ORDER BY 排序
  • ✏️3.8 分页查询
• 📕4. Update 修改
• 📕5. Delete 删除
  • ✏️5.1 普通Delete删除
  • ✏️5.2 截断表
  • ✏️5.3 插入查询结果
• 📕6. 聚合函数
• 📕7. Group by 分组查询
  • ✏️7.1 HAVING子句
• 📕8. 内置函数
  • ✏️8.1 日期函数
  • ✏️8.2 字符串处理函数
  • ✏️8.3 数学函数
  • ✏️8.4 其他常用函数

📕1. Create 新增

INSERTINTO name(filed1，filed2……)VALUE(value1，value2……);

• INSERT INTO 是插入关键字
• INTO 一般都要写，也可以省略
• name为表名字
• filed是要插入的列
• value是要插入的值

✏️1.1 单行数据全列插入

值的数量必须和定义表的列的数量及顺序一致

-- 创建一个用于演示的表CREATETABLEIFNOTEXISTS users( id BIGINT,`name`VARCHAR(20)COMMENT'用户名称');-- 全列插入INSERTINTO users VALUES(1,'张三');INSERTINTO users VALUES(2,'李四');

✏️1.2 单行数据指定列插入

-- 指定了要插入的列INSERTINTO users(id,`name`)VALUES(3,'艾米');

✏️1.3 多行数据指定列插入

INSERTINTO users(id,`name`)VALUES(5,'孙悟空'),(6,'猪八戒'),(7,'唐僧');

📕2. Retrieve 检索

SELECT 列名1，列名2FROM 表名;

• SELECT 为查询关键字
• 列名处是 * 的时候是查询所有列（一般不建议，数据过多时会很慢）

✏️2.1 全列查询

SELECT*FROM 表名;

✏️2.2 指定列查询

SELECT 指定列名称 FROM 表名;

✏️2.3 查询字段为表达式

1. 常量表达式

SELECT10FROM 表名;

2. 把所有学生的语文成绩加10分

SELECT Chinese+10FROM 表名;

3. 计算所有学生语文、数学和英语成绩的总分

SELECT chinese+nath+english FROM 表名;

✏️2.4 为查询结果指定别名

SELECT 列名1+列名2 【AS】 别名 FROM 表名;

AS可以省略，别名如果包含空格必须用单引号包裹

✏️2.5 结果去重查询

SELECTDISTINCT 列名1，列名2FROM 表名;

使用DISCTINCT去重时，只有查询列表中所有列的值都相同才会判定为重复

📕3. WHERE条件查询

SELECT 列名1，列名2FROM 表名 WHERE 条件……;

比较运算符

逻辑运算符

✏️3.1 基本查询

1. 查询英语不及格的同学及英语成绩 ( < 60 )

SELECT id,name,english FROM exam WHERE english <60;

2. 查询语文成绩高于英语成绩的同学

SELECT id,name,chinese,math FROM exam WHERE chinese > math;

3. 总分在 200 分以下的同学

SELECT id,name,chinese+math+english FROM exam WHERE chinese+math+english <200;

✏️3.2 AND和OR

1. 查询语文成绩大于80分且英语成绩大于80分的同学

SELECT id,name FROM exam WHERE chinese >80AND english >80;

2. 查询语文成绩大于80分或英语成绩大于80分的同学

SELECT*FROM exam WHERE chinese >80OR english >80;

✏️3.3 AND和OR的优先级

select*from exam where chinese >80or math >70and english >70;select*from exam where(chinese >80or math >70)and english >70;

注意：AND的优先级高于OR,括号的优先级高于AND和OR。

✏️3.4 范围查询

1. 语文成绩在 [80, 90] 分的同学及语文成绩

SELECT name，chinese FROM exam WHERE chinese >=80AND chinese <=90;

SELECT name，chinese FROM exam WHEREBETWEEN chinese 80AND90;

2. 数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩

SELECT name,math FROM exam WHERE math =78OR math =79OR math =98OR math =99;

SELECT name,math FROM exam WHEREIN(78,79,98,99);

✏️3.5 模糊查询

1. 查询所有姓孙的同学

SELECT*FROM exam WHERE name LIKE'孙%';

2. 查询姓孙且姓名共有两个字同学

SELECT*FROM exam WHERE name LIKE'孙_';

✏️3.6 NULL的查询

1. 查询英语成绩为NULL的记录

SELECT*FROM exam WHERE english ISNULL;

2. 查询英语成绩不为NULL的记录

SELECT*FROM exam WHERE english ISNOTNULL;

💡💡💡💡💡💡💡💡💡💡💡💡注意事项💡💡💡💡💡💡💡💡💡💡💡

1. WHERE条件中可以使用表达式，但不能使用别名
2. AND的优先级高于OR，在同时使用时，建议使用小括号()包裹优先执行的部分
3. 过滤NULL时不要使⽤等于号(=)与不等于号(!= , <>)
4. NULL与任何值运算结果都为NULL

✏️3.7 ORDER BY 排序

-- ASC 为升序（从⼩到⼤-- DESC 为降序（从⼤到⼩）-- 默认为ASCSELECT 列名1，列名2FROM 表名 WHERE 条件 ORDERBY 列名 DESC/ASC;

1. 按数学成绩从低到高排序(升序)

SELECT*FROM exam ORDERBY math ASC;

2. 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

SELECT*FROM exam ORDERBY math DESC,english ASC,chinese ASC;

3. 查询同学及总分，由高到低排序，可以使用列的别名进行排序

SELECT math+english+chinese AS 总分 FROM exam ORDERBY 总分 DESC;

4. 所有英语成绩不为NULL的同学，按语文成绩从高到低排序

SELECT*FROM exam WHERE english ISNOTNULLORDERBY chinese DESC;

💡💡💡💡💡💡💡💡💡💡💡💡注意事项💡💡💡💡💡💡💡💡💡💡💡

1. 查询中没有ORDER BY子句，返回的顺序是未定义的，永远不要依赖这个顺序
2. ORDER BY 子句中可以使用列的别名进行排序
3. NULL 进行排序时，视为比任何值都小，升序出现在最上面，降序出现在最下面

✏️3.8 分页查询

-- 起始下标为 0-- 从 0 开始，筛选 num 条结果SELECT 列名 FROM 表名 ((WHERE 条件)(ORDERBY 列名 ASC/DESC)LIMIT num;-- 从 start 开始，筛选 num 条结果SELECT 列名 FROM 表名 (WHERE 条件)(ORDERBY 列名 ASC/DESC)LIMITstart,num;-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤SELECT 列名 FROM 表名 (WHERE 条件)(ORDERBY 列名 ASC/DESC)LIMIT num OFFSETstart;

Tips:

1. 总页数 = 数据的总条数 / 每页要展示的数量，如果有余数则 + 1.
2. start = （当前页号 - 1）* 每页要展示的数量

📕4. Update 修改

UPDATE 表名 SET 修改值 (WHERE 条件)(ORDERBY 列名 ASC/DESC)(LIMIT num);

对符合条件的结果进行列值更新

1. 将孙悟空同学的数学成绩变更为 80 分

UPDATE exam SET math =80WHERE name ='孙悟空';

2. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

UPDATE exam SET math =60,chinese =70WHERE name ='曹孟德';

3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

UPDATE exam SET math = math +30WHERE chinese+math+english ISNOTNULLORDERBY chinese+math+english ASCLIMIT0,3

4. 将所有同学的语文成绩更新为原来的 2 倍

UPDATE exam SET Chinese= Chinese *2;

💡💡💡💡💡💡💡💡💡💡💡💡注意事项💡💡💡💡💡💡💡💡💡💡💡

1. 以原值的基础上做变更时，不能使用math += 30这样的语法
2. 不加where条件时，会导致全表数据被列新，谨慎操作

📕5. Delete 删除

✏️5.1 普通Delete删除

DELETEFROM 表名 [WHERE 条件][ORDERBY...][LIMIT count];

• 删除孙悟空同学的考试成绩

DELETEFROM exam WHERE name ='孙悟空';

💡💡💡💡💡💡💡💡💡💡💡💡注意事项💡💡💡💡💡💡💡💡💡💡💡

执行Delete时不加条件会删除整张表的数据，谨慎操作

✏️5.2 截断表

TRUNCATETABLE 表名;

🌰例如：

-- 准备测试表CREATETABLE t_truncate ( id INTPRIMARYKEYAUTO_INCREMENT, name VARCHAR(20));-- 插入新数据INSERTINTO t_truncate (name)VALUES('A'),('B'),('C');-- 此时查看建表结构，注意AUTO_INCREMENT=4-- 截断表TRUNCATETABLE t_truncate;--继续查看表结构，注意AUTO_INCREMENT已被重置为0

💡TRUNCATE 的注意事项：

1. 只能对整表操作，不能像 DELETE ⼀样针对部分数据
2. 不对数据操作所以比DELETE更快，TRUNCATE在删除数据的时候，不经过真正的事物
3. 会重置 AUTO_INCREMENT 项，而DELETE不会

✏️5.3 插入查询结果

INSERTINTO 表名 SELECT...

🌰例如:删除表中的重复记录，重复的数据只能有一份

实现思路：原始表中的数据一般不会主动删除，但是真正查询时不需要重复的数据，如果每次查询
都使用DISTINCT进行去重操作，会严重效率。可以创建一张与 t_recored 表结构相同的表，把去重的记录写入到新表中，以后查询都从新表中查，这样真实的数据不丢失，同时又能保证查询效率

-- 创建测试表，并构造数据CREATETABLE t_recored (id int, name varchar(20));INSERTINTO t_recored VALUES(100,'aaa'),(100,'aaa'),(200,'bbb'),(200,'bbb'),(200,'bbb'),(300,'ccc');--创建⼀张新表，表结构与t_recored相同createtable t_recored_new like t_recored;-- 原表中的记录去重后写⼊到新表insertinto t_recored_new selectdistinct*from t_recored;

📕6. 聚合函数

常用聚合函数

📕7. Group by 分组查询

GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的分组，然后针对若干个
分组进行数据处理，比如使用聚合函数对分组进行统计。

SELECT 列名 FROM 表名 GROUPBY 列名 HAVING 条件;

1. 统计每个角色的人数

SELECT role,count(*)FROM emp GROUPBY role;

✏️7.1 HAVING子句

使用GROUP BY 对结果进行分组处理之后，对分组的结果进行过滤时，不能使用 WHERE 子句，而要使用 HAVING 子句

1. 显⽰平均工资低于1500的角色和它的平均工资

SELECT role,AVG(salary)FROM emp GROUPBY role HAVINGAVG(salary)<1500;

Having 与Where 的区别

•Having 用于对分组结果的条件过滤

• Where 用于对表中真实数据的条件过滤

📕8. 内置函数

✏️8.1 日期函数

✏️8.2 字符串处理函数

✏️8.3 数学函数

✏️8.4 其他常用函数