[大数据]mysql表的增删查改

常见的四种对数据的操作
CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）
其中create是插入数据，retrieve是读取数据。

create（插入数据）

语法：
insert into table_name values(),(),()…其中into可以省略。
由于插入很简单，下面给例子就算了，对着例子写就好

单行数据全列插入

insert into students values (100, 10000, ‘zhangsan’, NULL);

多行数据全列插入

在数据与数据之间用逗号隔开

insert into actor values
(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

指定列插入

如果不指定列插入的话，sql默认是按照表的成员顺序进行插入。

指定列插入的语法是
insert into table_name (指定列名，…)values(xxx),(),(),…

INSERT INTO students (id, stuNum, name) VALUES
(102, 20001, 'zhangsan'),
(103, 20002, 'lisi');

插入数据，如果是唯一性的数据且表中有重复数据，更新它

目前我没想到什么实际场景需要用这个
主键 或者 唯一键 对应的值已经存在，插入的时候更新一下。

关键字：ON DUPLICATE KEY update xxx=xxx；

先创建一个表

插入100这个数据，如果表里有100了，就把它更新成1000

第一次由于表是空的，因此成功插入100

第二次由于已经有100了，因此这个100变成1000了。

replace

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

replace into table_name values();

原来插入了一个张三，后面由于id相同了，replace把张三换成李四了。

retrieve（重点是查）

SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit

select

select有很多用法，可以加上where order limit distinct来实现各种功能
SELECT
[DISTINCT] {* | {column [, column] …}
[FROM table_name]
[WHERE …]
[ORDER BY column [ASC | DESC], …]
LIMIT …

select *

一般不建议select *，当表太大的时候，select * 太浪费资源了。（毕竟mysql是从服务器down东西下来的）

select 指定列

语法：select 列名字 from tablename

查询表达式

啥叫表达式呢？可以理解成查询对原来的数据进行运算之后的结果

这就是select把所有成绩都加起来之后的结果。as是可以让表达式进行重命名。

注意：这个表达式并不属于表，它只是把所有数据拿出来之后做的一次运算。

select distinct

如果现在有一个需求就是对查找出来的数据去重，就需要用到distinct
没有用distinct的

用了distinct的

select + where

select后面接要选的列（如果有多个，用逗号隔开），where是筛选的条件。
运算符 说明

运算符 说明

有几个题目可以做一下：
这是题目的表

英语不及格的同学及英语成绩 ( < 60 )

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

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

使用or

使用集合in

姓孙的同学

%可以匹配后面一个或者多个字符

名字两个字且姓孙的人

_只会匹配一个字符

语文成绩好于英语成绩的同学

总分在 200 分以下的同学

注意：这里where不能写成total < 200；
where只能根据表里面有的列来进行数据筛选，total并不是表中的列。

null之间的比较

对于null来讲，不能使用=，要使用<=>

order by asc/desc

按照升序或者降序排序。
– ASC 为升序（从小到大）
– DESC 为降序（从大到小）
– 默认为 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];

asc

desc

有一点要注意的：order by后面可以接别名。这里也可以看出order by并不是只能排表中的数据的。

where可以和order by结合
查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

limit & offset

limit可以只显示前面n行的数据，如果是limit 2，就是只显示前两行的数据

也可以搭配order by来显示倒数两行的数据

offset是偏移量，意味着从哪里开始，offset 1就是从第二行开始

update

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

update set是两个关键字，很容易漏写set。

update也有挺多用法。但是最常用还是搭配where
下面有几道题目可以做一下：

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

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

更新多个数值，数值之间要用逗号隔开。

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

mysql不支持+=，只能写成x = x + a；的形式。

update全表

只要你不写where语句，就可以更新全表。

delete

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

删除是以行作为单位的，最少要删除一行数据。至于要删除哪一行，由where说了算。不加where就是删除整一个表的数据

删除孙悟空的成绩
delete from exam_result where name = ‘孙悟空’;

删除整一个表

delete from table_name
注：只是删除了整一个表的数据，表还是存在的。

truncate（截断表）

trucate也是删除数据，但是它和delete有三个不同点：

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

先插入三个数据

然后truncate，从0rows affected可以发现，其实数据没有被影响

现在是empty set了，再插入一个数据看一下：

发现auto_increment重置为1了。

insert + select（插入查询结果）

insert和select可以搭配使用

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

思路就是把不重复的数据插入到新表中，然后把旧表删除即可。

验证一下：
先插入6个数据，其中三个是重复的

然后创建一个新表，把去重后结果插入新表。

聚合函数

这些聚合函数要搭配select一起使用，select才能显示出结果。这些函数里面需要加上你想查询的列名。

测试count

测试sum，就是把那一列的所有数字加起来

测试avg，可以通过avg来求平均值。

group by（分组查询）和having

这里用oracle的经典测试表来测试
下载好之后如何允许sql文件呢？

在mysql下 输入source + 文件路径名

就可以把表导入进mysql服务器了。

下面开始测试：
在select中使用group by 子句可以对指定列进行分组查询
select column1, column2, … from table group by column1,…

注意：分组查询可以分好多组。

原理就是：mysql把group by后面的值作为key，和key相等的放进一个set里面，对set里面的行的数据进行运算。

有几道题目做一下：

题目：显示每个部门的平均工资和最高工资

也就是说要不同部门是不同组，是分组查询。

每个部门的每种岗位的平均工资和最低工资

这里如果写成了下面这样就会报错（group by最后少了一个job）：

原因看这篇文章
https://blog.csdn.net/study_in/article/details/92625397

显示平均工资低于2000的部门和它的平均工资

平均工资低于2000的部门，因为要算部门的平均工资，因此要分组查询，又由于它有一个低于2000的条件，因此要进行筛选。

在group by里面筛选条件不能用where，要用having，用法是一样的。

超强干货来袭

云风专访：近40年码龄，通宵达旦的技术人生