MySQL表的CURD

目录

一、Create

1.1单行数据+全列插入

1.2多行数据+指定列插入

1.3插入否则更新

1.4替换

2.Retrieve

2.1 select列

2.1.1全列查询

2.1.2指定列查询

2.1.3查询字段为表达式

2.1.4为查询结果指定别名

2.1.5结果去重 

2.2where条件

2.3结果排序

2.4筛选分页结果

三、Update

四、Delete

 4.1删除数据

 4.2截断表

五、插入查询结果

六、聚合函数

七、group by子句的使用

---

一、Create

创建一张学生表

1.1单行数据+全列插入

1.2多行数据+指定列插入

1.3插入否则更新

• 主键冲突
• 
• 唯一键冲突
• 
• 可疑选择性进行同步更新操作：使用ON DUPLICATE KEY UPDATE
• 
• -- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
  -- 1 row affected: 表中没有冲突数据，数据被插入
  -- 2 row affected: 表中有冲突数据，并且数据已经被更新
• 使用MySQL库函数来获取受到影响的数据行数：SELECT ROW_COUNT();
• 

1.4替换

• - - 主键 或者 唯一键 没有冲突，则直接插入；
  -- 主键 或者 唯一键 如果冲突，则删除后再插入
• 

2.Retrieve

创建表结构

插入数据

2.1 select列

2.1.1全列查询

• -- 通常情况下不建议使用 * 进行全列查询
  -- 1. 查询的列越多，意味着需要传输的数据量越大；
  -- 2. 可能会影响到索引的使用。
• 

2.1.2指定列查询

• 指定列的顺序不需要按定义表的顺序来

• 

2.1.3查询字段为表达式

• 表达式不包含字段 

• 
• 表达式包含一个字段
• 
• 表达式包含多个字段
• 

2.1.4为查询结果指定别名

• SELECT column [AS] alias_name [...] FROM table_name;
• 

2.1.5结果去重 

• distinct关键字
• 

2.2where条件

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于， NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于， NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配， [a0, a1] ，如果 a0 <= value <= a1 ，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配， % 表示任意多个（包括 0 个）任意字符； _ 表示任意一个字符

逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1) ，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1) ，结果为 FALSE(0

2.3结果排序

• -- ASC 为升序（从小到大）
• -- DESC 为降序（从大到小）
• -- 默认为 ASC
• SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
• 没有order by返回的结果是未定义的

2.4筛选分页结果

•  -- 起始下标为 0 -- 从 s 开始，筛选 n 条结果

• SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n

• -- 从 0 开始，筛选 n 条结果

• SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

• -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

• SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

• 按 id 进行分页，每页 3 条记录，分别显示 第 1 、 2 、 3 页
• 

三、Update

• UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
• 对查询到的结果进行列值更新
• 更新为具体指
• 
• 一次更新多个列
• 
• 更新值为原值基础上变更
• 
• 没有 WHERE 子句，则更新全表
• 

四、Delete

 4.1删除数据

• DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
• 删除“孙悟空”的数据
• 
•  删除整张表的数据
• 
• 在删除的基础上再插入一条数据，可见删除数据并不会重置auto_increment项
• 

 4.2截断表

• TRUNCATE [TABLE] table_name
• 只对整张表进行操作，不能像delete一样对部分数据操作
• 实际上不对数据进行操作，影响行数是0，所以比delete快，但是truncate在删除数据时，并不经过真正的事务，所以无法回滚
• 会重置auto_increment项
• 

五、插入查询结果

• INSERT INTO table_name [(column [, column ...])] SELECT ... 

• 案例：删除表中的的重复复记录，重复的数据只能有一份

1. 创建一张空表 no_duplicate_table ，结构和 duplicate_table 一样
2. 将 duplicate_table的去重数据插入到no_duplicate_table
3. 通过重命名表实现原子的去重操作
4. 

六、聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数字总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数字平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数字最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数字最小值，不是数字没有意义

• 使用 * 做统计，不受 NULL 影响
•  
• 使用表达式做统计
• 
• NULL 不会计入结果
• 
• COUNT(math)统计的是全部的数学成绩
• 
• 统计去重的数学成绩个数
• 
• 统计数学成绩总分
• 
• 配合where条件使用
• 
• 统计平均分
• 
• 返回英语最高分
• 
• 返回>70以上数学的最低分
• 

七、group by子句的使用

• 在 select 中使用 group by 子句可以对指定列进行分组查询
• 案例
• 显示每个部门的平均工资和最高工资： select deptno,avg(sal),max(sal) from EMP group by deptno;
• 显示每个部门的每种岗位的最低工资和平均工资：select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
• 显示平均工资低于2000的部门和它的平均工资

1. 统计各个部门的平均工资： select avg(sal) from EMP group by deptno
2. having搭配group by使用，对group by的查询结果进行筛选： select avg(sal) as myavg from EMP group by deptno having myavg<2000;
3. --having 经常和 group by 搭配使用，作用是对分组进行筛选，作用有些像 where