【数据库】表的增删改查

核心词：Create ,Retrieve ,UpDate ,Delete

1. Create

1.1 创建一张表

1.2 插入数据

1. 单行数据
   
2. 多行数据
   

1.3 插入失败更新

由于主键或者唯一键对应的值存在而导致插入失败

1.4 替换

2. Retrieve

准备工作：

2.1 SELECT 列

2.1.1 全列查询

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

2.1.2 指定列查询

– 指定列的顺序不需要按照定义表的数据来

2.1.3 查询字段为表达式

2.1.4 为查询结果指定别名

2.1.4 结果去重

2.2 WHIERE 条件

2.3 结果排序

– ASC 为升序
– DESC 为降序
– 默认为 ASC

注意：没有 ORDER BY ⼦子句句的查询，返回的顺序是未定义的，永远不不要依赖这个顺序

2.4 筛选分页结果

注意：对未知表进行查询的时候，最好加上一条LIMIT1,避免因为表中数据过大，查询表数据导致数据库卡死。
语法：

-- 起始下标为0

-- 从0开始，筛选n条结果
SELECT ... FROM table_name  [WHERE...] [ORDER BY...] LIMIT n;

-- 从s开始，筛选n条结果
SELECT ... FROM table_name  [WHERE...] [ORDER BY...]  LIMIT s,  LIMIT n;

-- 从s开始，筛选n条结果，比第二种用法更准确，建议使用
SELECT ... FROM table_name  [WHERE...] [ORDER BY...]LIMIT n OFFSET n;

3. Update

语法：

UPDATE table_name SET column = expr
 [,column = expr ...] [ WHERE ... ] [ ORDER BY ...] [LIMIT...]

对查询到的结果进行列值更新
案例：

– 将孙悟空数学成绩改成80

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

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

– 将所有同学的语⽂文成绩更更新为原来的 2 倍
更新全表的语句慎用
没有 WHERE ⼦子句句，则更更新全表

4. Delete

4.1 删除数据

DELETE FROM table_name [WHERE ...] [ ORDER BY ...] [LIMIT ...]

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

4.2 截断表

TRUNCATE [TABLE] table_name
注意：这个操作慎⽤用

1. 只能对整表操作，不不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不不对数据操作，所以比 DELETE 更更快
3. 会重置 AUTO_INCREMENT 项
   

5. 插入查询结果

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

– 删除表中的的重复复记录，重复的数据只能有一份

6. 聚合函数

统计班级一共多少同学：

统计班级收集的qq号有多少：

统计本次考试的数学成绩分数个数：

统计数学成绩总分：

统计平均总分：

返回英语最高分：

返回>70分以上的数学最低分:

7. group by 子句的使用

在select中使⽤用group by ⼦子句句可以对指定列列进⾏行行分组查询
select column1, column2, .. from table group by column;
案例例：

• 准备⼯工作，创建⼀一个雇员信息表（来⾃自oracle 9i的经典测试表）

• EMP员⼯工表
• DEPT部⻔门表
• SALGRADE⼯工资等级表

• 如何显示每个部⻔门的平均⼯工资和最⾼高⼯工资
  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的部门和它的平均工资
  • 统计各个部门的平均工资
    select avg(sal) from EMP group by deptno;
  • having 和 group by 配合使用，对group by 结果进行过滤
    select avg(sal) as myavg from EMP group by deptno having myavg<2000;