MySQL表的增删查改
目录
- 一、MySQL表的增删查改
- 二、Create
- 单行数据+全列插入
- 多行数据 + 指定列插入
- 插入否则更新
- 替换数据
- 三、Retrieve
- SELECT 列
- WHERE 条件
- NULL的查询
- 结果排序
- 筛选分页结果
- 四、Update
- 将孙悟空同学的数学成绩修改为80分
- 将曹孟德同学的数学成绩修改为60分，语文成绩修改为70分
- 将总成绩倒数前三的3位同学的数学成绩加上30分
- 将所有同学的语文成绩修改为原来的2倍
- 五、Delete
- 删除数据
- 删除整张表数据
- 截断表
- 六、插入查询结果
- 七、聚合函数
- 统计班级共有多少同学
- 统计班级收集的QQ号有多少个
- 统计本次考试数学成绩的分数个数
- 统计数学成绩总分
- 统计平均总分
- 返回英语最高分
- 返回70分以上的英语最低分
- 八、分组查询
- 分组查询测试表 —— 雇员信息表
- 显示每个部门的平均工资和最高工资
- 显示每个部门的每种岗位的平均工资和最低工资
- HAVING 条件
一、MySQL表的增删查改
- 表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）。
- CRUD的操作对象是对表当中的数据，是典型的DML（Data Manipulation Language）数据操作语言。
二、Create
新增数据
INSERT [INTO] table_name [(column1 [, column2] ...)] VALUES (value_list1) [, (value_list2)] ...;
说明：
- SQL中大写的表示关键字，[ ]中代表的是可选项。
- SQL中的每个value_list都表示插入的一条记录，每个value_list都由若干待插入的列值组成。
- SQL中的column列表，用于指定每个value_list中的各个列值应该插入到表中的哪一列。
创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。
创建完表后，查看表的结构。
单行数据+全列插入
下面使用insert语句向学生表中插入记录，每次向表中插入一条记录，并且插入记录时不指定column列表，表示按照表中默认的列顺序进行全列插入，因此插入的每条记录中的列值需要按表列顺序依次列出。
多行数据 + 指定列插入
使用insert语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。
说明： 在插入记录时，只有允许为空的列或自增长字段可以不指定值插入，不允许为空的列必须指定值插入，否则会报错。
插入否则更新
这时可以选择性的进行同步更新操作：
- 如果表中没有冲突数据，则直接插入数据。
- 如果表中有冲突数据，则将表中的数据进行更新。
插入否则更新的SQL如下：
INSERT ... ON DUPLICATE UPDATE column1=value1 [, column2=value2] ...;
说明：
- SQL中大写的表示关键字，[ ]中代表的是可选项。
- SQL中INSERT之后语法与之前的INSERT语句相同。
- UPDATE后面的column=value，表示当插入记录出现冲突时需要更新的列值。
向学生表中插入记录时，如果没有出现主键冲突则直接插入记录，如果出现了主键冲突，则将表中冲突记录的学号和姓名进行更新。
执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：
- 0 row affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
- 1 row affected：表中没有冲突数据，数据直接被插入。
- 2 row affected：表中有冲突数据，并且数据已经被更新。
替换数据
- 如果表中没有冲突数据，则直接插入数据。
- 如果表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据。
只需要在插入数据时将SQL语句中的INSERT改为REPLACE即可。
若3号不存在则直接插入，否则修改数据。
执行替换数据的SQL后，也可以通过受影响的数据行数来判断本次数据的插入情况：
- 1 row affected：表中没有冲突数据，数据直接被插入。
- 2 row affected：表中有冲突数据，冲突数据被删除后重新插入。
三、Retrieve
查找数据
SELECT [DISTINCT] {* | {column1 [, column2] ...}} FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];说明：
- SQL中大写的表示关键字，[ ]中代表的是可选项。
- { }中的 | 代表可以选择左侧的语句或右侧的语句。
创建一个成绩表，表当中包含自增长的主键id、姓名以及该同学的语文成绩、数学成绩和英语成绩。
创建完表后查看表结构，可以看到表结构。
插入一些信息，以供进行查找。
SELECT 列
全列查询
在查询数据时直接用 * 代替column列表，表示进行全列查询，这时将会显示被筛选出来的记录的所有列信息。
说明： 通常情况下不建议使用*进行全列查询，因为被查询到的数据需要通过网络从MySQL服务器传输到本主机，查询的列越多也就意味着需要传输的数据量越大，此外，进行全列查询还可能会影响到索引的使用。
指定列查询
在查询数据时也可以只对指定的列进行查询，这时将需要查询的列在column列表列出即可。
查询字段为表达式
查询数据时，column列表中除了能罗列表中存在的列名外，也可以将表达式罗列到column列表中。
select不仅能够用来查询数据，还可以用来计算某些表达式或执行某些函数。
如果我们将表达式罗列到column列表，那么每当一条记录被筛选出来时就会执行这个表达式，然后将表达式的计算结果作为这条记录的一个列值进行显示。
column列表中的表达式可以包含表中已有的字段，这时每当一条记录被筛选出来时，就会将记录中对应的列值提供给表达式进行计算。
column列表中的表达式中也可以包含多个表中已有的字段，这时我们就可以通过表达式计算出更多有意义的数据。
为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
说明：
- SQL中大写的表示关键字，[ ]中代表的是可选项。
比如查询成绩表中的数据时，将每条记录中的三科成绩相加，然后将计算结果对应的列指定别名为“总分”。
结果去重
查询成绩表时指定查询数学成绩对应的列，可以看到数学成绩中有重复的分数。
若想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct。
WHERE 条件
添加where子句的区别：
- 如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句。
- 如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句。
where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。
比较运算符：
运算符 说明 >、>=、
- SQL中大写的表示关键字，[ ]中代表的是可选项。
