Mysql配套测试之更新篇

create databate text_3_22;

use text_3_22;

-- 创建表结构
create table exam_result (
  id int unsigned primary key auto_increment,
  name varchar(20) not null comment '同学姓名',
  chinese float default 0.0 comment '语文成绩',
  math float default 0.0 comment '数学成绩',
  english float default 0.0 comment '英语成绩'
);

-- 插入测试数据
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);

select *from exam_result;

update exam_result set math=80 where name='孙悟空';

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

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

delete from exam_result where name='孙悟空';

delete from exam_result order by english+math+chinese asc limit 1;

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

---准备一个测试表结构

insert for_delete (name) values('a'),('b'),('c');

----准备测试数据

create table 1_distinct_text(
name varchar(20)
);
---创建测试表

insert 1_distinct_text values
('aaa'),('bbb'),('aaa'),('bbb'),('aaa'),('bbb');

select count(math) from exam_result;

select sum(math) from exam_result;

select sum(math)/count(math) from exam_result;

select avg (math) from exam_result;

select min(math) from exam_result where math>70;

select max(sal),avg(sal) from emp group by deptno;

select min(sal),avg(sal) from emp group by job;

select min(sal),avg(sal) from emp group by deptno,jop;

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