[MySQL] 表的增删查改（查询是重点）

需要用到的文件在我的gitee中
tpoog的gitee

导入表的操作

下载完成之后

1. rz
2. source [存放的路径] 文件名

1.Create（插入）

mysql> create table students(-> id int unsigned primary key auto_increment,-> sn int not null unique,-> name varchar(20) not null,-> qq varchar(20)-> );

1.1 单行数据+全列插入

进行全列插入的时候不需要指明要插入的列，但是插入的顺序要和表的顺序一致

1.2 多行数据+指定列插入

mysql> insert into students (id,sn,name) values-> (102,20001,'曹阿曼'),-> (103,20002,'江东鼠辈');

1.3 插入否则更新

由于主键冲突或者是唯一键冲突的时候导致的插入失败。 我们可以进行选择性的同步更新。

mysql> insert into students(id,sn,name) values (100,10010,'唐三')-> on duplicate key update sn=10010;

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

我们可以通过row_count()获取受影响的行数

1.4 替换

2.Retrieve(查询)

案例：
mysql> create table exam_result(
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null,
-> chinese float default 0.0 comment ‘语文成绩’,
-> math float default 0.0 comment ‘数学成绩’,
-> english float default 0.0 comment ‘英语成绩’
-> );

//插入测试数据
mysql> INSERT INTO exam_result (name, chinese, math, english) VALUES
-> (‘唐三藏’, 67, 98, 56),
-> (‘孙悟空’, 87, 78, 77),
-> (‘猪悟能’, 88, 98, 90),
-> (‘曹孟德’, 82, 84, 67),
-> (‘刘玄德’, 55, 85, 45),
-> (‘孙权’, 70, 73, 78),
-> (‘宋公明’, 75, 65, 30);

2.1 select 列

2.1.1 全列查询

select * from exam_result;

注意： 通常情况下不建议使用*进行全列查询

1. 查询的列越多，意味着需要传输的数据量越大
2. 可能会影响到索引的使用。

2.1.2 指定列查询

select id, name, math, english, chinese from exam_result;
指定列的顺序不需要按定义表的顺序来

2.1.3 查询字段为表达式

我们之前就试过查找NULL，这个就是一种表达式，当然表达式的作用远远不止这些

这里我们可以获取总分，当然还可以给这个总分取别名；这里的as可以省略

2.1.4 结果去重（distinct）

只需要加一个distinct即可

2.2 where 条件（影响行数）

比较运算符：

逻辑运算符：

我们用几个例子来学习这个where语句：

2.2.1 例子

1.英语不及格的同学及英语成绩 ( < 60 )
select name,english from exam_result where english<60;

2.语文成绩在 [80, 90] 分的同学及语文成绩
select name,english from exam_result where chinese>=80 and chinese<=90;
select name,english from exam_result where chinese between 80 and 90;

3.数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
select name,math from exam_result where math in(58,59,98,99);
这里不建议使用or（我认为不美观）

4.姓孙的同学 及 孙某同学

5.语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese > english;

6.总分在 200 分以下的同学
SELECT name, chinese + math + english 总分 FROM exam_result
WHERE chinese + math + english < 200;

语文成绩 > 80 并且不姓孙的同学
select name,chinese from exam_result where chinese>80 and name not like ‘孙%’;

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select name,chinese,math,english,chinese+math+english from exam_result where name like ‘孙%’ or(chinese+math+english>200 and chinese80);

2.2.2 NULL的查询

这里的<=>表示的是不等于的意思，也可以用！=

2.3 结果排序

默认为asc所以拍升序的时候可以不显示的写。

1. 同学及数学成绩，按数学成绩升序显示
select name,math from exam_result order by math asc;

补充：NULL值视为比任何数字都小，所以如果是升序NULL就排在最上面面。

2**.查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示**
select name ,math, english,chinese from exam_result order by math desc,english,chinese;

3.查询同学及总分，由高到低
select name ,math+english+chinese 总分 from exam_result order by 总分 desc;

（重点）order by 和where的区别

这里order by 可以使用别名，但是在上面where的时候并不可以使用，这里能看出来他们两个的执行顺序是不同的。

上面的这条语句中，各个部分的执行顺序就如上，

• 先找出在哪个表中

• where：相当于对表的数据做一次清理（清理出不需要的）

• 然后就是取出条件

• 最后就是排序

• 总结：先有数据，在进行排序，因为取别名的顺序在排序之前，所以order by 才可以使用别名。而where的顺序在别名之前，所以mysql识别不出这个别名是哪个表
  

我们再来看看where和order by 结合使用的例子

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
select name,math from exam_result where name like ‘孙%’ or name like ‘曹%’ order by math desc;

2.4 筛选分页结果

建议：对未知表进行查询的时候，最好使用这个语句，避免表中数据过大，查询导致整个数据库卡死的问题。

按照id 进行分页，分别显示1，2，3

3.Update（查询+更新）

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

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

4.Delete(删除)

4.1 删除数据

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

4.2 删除整张表的数

delete from 【表名】

4.3 截断表

在mysql中，会有记录我们操作的日志，前面提到过的拷贝就是把当前对表的所有操作都记录下来，然后进行恢复；
这里截断的时候这个操作是不会被记录在log中的。（无法回滚）

测试：


这里显示影响行数是0，也就是说实际上truncate并没有对数据进行真正的操作

5. 插入查询结果

5.1 例子

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

//创建数据表
mysql> create table duplicate_table(-> id int,-> name varchar(20)-> );
Query OK, 0 rows affected (0.01 sec)//插入数据
mysql> insert into duplicate_table values-> (100, 'aaa'), -> (100, 'aaa'), -> (200, 'bbb'), -> (200, 'bbb'), -> (200, 'bbb'), -> (300, 'ccc'); 
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

思路步骤：

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

分析：为什么最后是通过重命名的方式去重的？这样的好处是什么？
因为重命名的方式是原子的，而一条条的插入的方式肯定不是原子，这样的数据容易收到影响，
所以重命名的本质目的就是：等一切的数据就绪了，然后一起操作，放入，更新，生效

6.聚合函数

6.1 例子

1.统计班级共有多少同学
SELECT COUNT(*) FROM students;
//这里面的值还可以填入1，2，3，4，
//count记录的数不会把NULL统计进去
//如果想要去重统计，只要在count参数里面写入distinct即可

2.统计数学成绩总分
select sum(math) from exam_result;

3.统计平均总分
select avg(chinese+math+english) 平均分 from exam_result;

4.返回英语最高分
select max(english) from exam_result;

5.返回 > 70 分以上的数学最低分
select min(math) from exam_result where math>70;

7.group by 语句（分组查询）

分组的目的是为了方便聚合统计
在使用这个语句之前我们需要对这一部分的做一点准备

1.显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno;

指定列名，实际分组使用改列的不同的行数据进行分组的；分组条件deptno，组内一定是相同的，把一张表照逻辑拆成了多个子表

2.显示每个部门的每种岗位的平均工资和最低工资

如果我们前面加入了ename就无法查看了，因为每个人的名字都不同无法被聚合 一般来说：能出现在前面的只有聚合函数与group by后面的。

3.显示平均工资低于2000的部门和它的平均工资
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;

having的作用：他常与group by合作使用，作用是对聚合和后的统计数据，进行条件筛选。

having VS where 你们知道吗？
他们的最大区别就在于筛选的阶段不同
where的筛选是对具体的任意列进行条件筛选
having：对分组聚合后的结果进行条件筛选