MySQL之基本查询(下)-表的增删查改

表的增删查改：CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

Update(更新)

语法：

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

列值更新

案例 

更新值为具体值

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

mysql> update exam_result set math=80;//这个语句是将所有人的数学成绩变为80

mysql> update exam_result set math=80 where name='孙悟空';//进行where条件筛选

 一次更新多个列

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

mysql> update exam_result set math=60 ,chinese=70  where name='曹孟德';

 更新值为原值基础上变更

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

数据更新，不支持 math += 30 这种语法

原本排名

update exam_result set math=math+30 order by chinese+math+english limit 3;

执行后变化了

 将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！没有 WHERE 子句，则更新全表

mysql> update exam_result set chinese=chinese*2;

set左侧是列名右侧是表达式或者常数 ；

 Delete(删除)

删除数据

语法：DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

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

查看原数据：mysql> select * from  exam_result where name='孙悟空';

删除数据：mysql> delete from exam_result where name='孙悟空';

查看删除结果：mysql> select * from  exam_result where name='孙悟空';

 删除总分最低的人

查看原数据最低的人：mysql> select name,chinese+math+english total from  exam_result order by total limit 1;

删除操作：mysql> delete from  exam_result order by chinese+math+english limit 1;
查看删除操作：宋公明不存在了

 删除整张表数据

注意：删除整表操作要慎用！在MySQL中分为表和表里面的数据；

delete语句重点是删除表里的数据；表结构不受影响

delete删除表数据

准备测试表：mysql> CREATE TABLE for_delete ( 
    ->  id INT PRIMARY KEY AUTO_INCREMENT, 
    ->  name VARCHAR(20) 
    -> );

插入测试数据：mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); 

查看测试数据：mysql> SELECT * FROM for_delete;

查看表结构：mysql> show create table for_delete\G

可以看到有auto_increment(可以理解为计数器),有专门的维护他

删除整表数据：DELETE FROM for_delete;

查看表结构：发现还是计数器值还是从4开始，并不会因为清除数据我的auto_increment清零
再插入一条数据：INSERT INTO for_delete (name) VALUES ('D');

查看数据发现自增 id 在原值上增长：

截断表 

语法：TRUNCATE [TABLE] table_name

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

测试表：mysql> CREATE TABLE for_truncate ( 
    ->  id INT PRIMARY KEY AUTO_INCREMENT, 
    ->  name VARCHAR(20) 
    -> ); 
插入测试数据：mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); 
查看测试数据：mysql> SELECT * FROM for_truncate;

查看表结构：mysql> show create table for_truncate\G

计数器的值是在4开始

截断整表数据：mysql> truncate for_truncate;

注意影响行数是 0，所以实际上没有对数据真正操作

查看删除结果：mysql> SELECT * FROM for_truncate;
表还在：只是数据没了
再次查看表结构：mysql> show create table for_truncate\G

发现计数器值清空了

再插入一条数据：mysql> INSERT INTO for_truncate (name) VALUES ('D');
再次查看表结构：发现id在重新增长
查看数据：mysql> SELECT * FROM for_truncate;

 插入查询结果

语法：INSERT INTO table_name [(column [, column ...])] SELECT ...

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

 创建原数据表：CREATE TABLE duplicate_table (id int, name varchar(20));

插入测试数据：INSERT INTO duplicate_table VALUES (100, 'aaa'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');

去重：mysql> select distinct * from duplicate_table;
这步操作只是显示出去重后的数据，对原始表的数据没有影响

 思路：

创建一个结构一样的空表，然后将原来的表的去重数据放到新表里，再重命名两个表；

创新表：mysql> create table no_duplicate_table like duplicate_table;

你也可以重新create结构；不过用like方便因为是结构相似的表；新表插入数据：mysql> insert into no_duplicate_table select distinct * from duplicate_table;

重命名：mysql> rename table duplicate_table to old_duplicate_table,no_duplicate_table to duplicate_table;
/var/lib/mysql 是 Linux 系统中 MySQL 数据库默认的数据存储目录。可以到配置文件cd /var/lib/mysql中找到文件，在cd你所用的数据库可以看到你所建的表；

在MySQL中你建立一个数据库就是相当于建立一个文件夹，创一个表就是创立一个文件；

 最后rename防止其他文件关联使用，如果最开始rename可能其他文件无法使用了因为没有原来的了。

聚合函数

MySQL中也有自己的函数

count：

统计班级共有多少同学：mysql> select count(*) from exam_result;

使用 * 做统计，不受 NULL 影响
支持重命名：mysql> select count(*) 总数 from exam_result;

使用count(1),count(2)结果一样的；因为你的表有多少行我就显示多少行，类似于select 1 from exam_result;你有5行那我就显示5行的1；那count(1)就是几行
统计本次考试的数学成绩分数个数：mysql> select count(math) 总数 from exam_result;

对成绩做去重：distinct放count前没有用，因为我count已经聚合了是一个整数，再怎么去重我还是整数不变

错误：mysql> select distinct count(math) 总数 from exam_result;

正确：mysql> select  count(distinct math) 总数 from exam_result;

 sum： 

统计数学成绩总分：mysql> select  sum( math) 总数 from exam_result;

 AVG:

统计平均总分: mysql> select  avg( math+chinese+english) 总数 from exam_result;
返回数学平均分：mysql> select  avg( math) 总数 from exam_result;

也可以这样：mysql> select  sum( math)/count(1) 总数 from exam_result;

 返回数学成绩不及格的人数：mysql> select count(1) from exam_result where math<60;

英语不及格的人的总分：mysql> select sum(english) from exam_result where english<60;

所有不及格的人总分为101

max

返回英语最高分：mysql> select max(english) from exam_result;

错误：mysql> select name,max(english) from exam_result;不允许这样操作

 min

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

 group by子句的使用

在select中使用group by子句可以对指定列进行分组查询

语法：select column1, column2, .. from table group by column;

分组的目的是进行分组后，方便进行聚合统计 

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

将表上传到linux中

 将表导入到我们的数据库里：mysql> source /home/wwz/scott_data.sql;

可以看到成功导入并且数据库中有了scoot库；

 使用当前数据库：mysql> use scott;
查表：mysql> show tables;成功导入表

emp员工表 dept部门表 salgrade工资等级表

显示员工平均工资和最高工资：mysql> select avg(sal),max(sal) from emp;

按照部门显示平均工资和最高工资：mysql> select avg(sal),max(sal) from emp group by deptno;
显示每个部门的平均工资和最高工资：mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
分组是指定列名(group by table_name)表示分组列是它，实际分组是使用这一列不同行的数据进行聚合统计分组(把这一列的一组分为多个组，进行组内统计)

显示每个部门的每种岗位的平均工资和最低工资：mysql>  select avg(sal),min(sal),job, deptno from emp group by deptno, job;//用逗号来隔离不同分组条件

先按照部门分组再按照工作分组(将表分为部门组表，再将每个部门组表分为工作表，最后聚合统计；相当于一个树向下分支再分支)

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

1. 统计各个部门的平均工资

mysql> select avg(sal) from emp group by deptno;

2. 对group by结果进行过滤

mysql> select avg(sal),deptno from emp group by deptno having avg(sal)<2000;

having的作用是你分完组了也聚合完了我再根据聚合统计的结果刷选；

having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

先执行select avg(sal),deptno from emp group by deptno后执行having avg(sal)<2000;

 mysql>  select max(mgr),avg(sal),min(sal),job, deptno from emp where ename!='SMITH' group by deptno, job having avg(sal)<2000;
where是对具体的任意列进行条件筛选；

having是对我们分组聚合之后的结果进行条件刷选；

他们俩的应用场景是不同的，如果他俩在一块，先where后having；

执行顺序：1.告诉我你从那个表里拿数据from emp 2.告诉我拿数据的筛选条件是什么 where ename!='SMITH' 3. 按照对应的组进行分组 group by deptno, job 4. 按照分组后的进行聚合统计以及重命名 5. 进行结果having筛选

注意的是from左边的列必须在from右边分组出现，否则不成功

比如:mysql>  select ename,avg(sal),min(sal),job, deptno from emp group by deptno, job;//ename不属于分组条件所以无法分组

错误：ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scott.emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误：select mgr,avg(sal),min(sal),job, deptno from emp group by deptno, job;

正确：select max(mgr),avg(sal),min(sal),job, deptno from emp group by deptno, job;
所以只有在分组条件中出现的列才能在select后面，或者直接聚合函数也行

不要单纯的认为只有磁盘上表结构导入到MySQL，真实存在的表才叫表；

中间筛选出来的包括最终结果，可以理解为逻辑上的表！--"MySQL中一切皆表 ";

只要能对单表处理好CURD，那么所有的sql场景，都能用统一的方式进行；

 

原文地址：https://blog.csdn.net/yiqizhuashuimub/article/details/140317424

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！