ID Name
1 bill
2 Gaiz
2 hell
2 Till
3 ggoe
3 ggoe
3 ggoe
3 ggoe
1、查询表中存在ID重复三次以上的记录
ID Name
2 Gaiz
2 hell
2 Till
答:创建表db
select * from db where id in (select id from db group by id having count(id)>3);
首先用分组对数据进行分组,分组条件为id重复次数>3,在主体检索函数用in关键字将分组作为条件
学生成绩表
id name kecheng fenshu
1 张三 语文 81
1 张三 数学 75
2 李四 语文 40
2 李四 数学 70
3 王五 语文 81
3 王五 数学 100
3 王五 英语 90
2、查询平均成绩大于60分的同学的学号和平均成绩
创建student表
select id,avg(fenshu) from student group by id having avg(fenshu)>60;
将同学id分组,然后用聚合函数计算平均值![](https://i-blog.csdnimg.cn/blog_migrate/2ac78e4b77352918a7b4eb1680c49bf2.png)
3. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名
select name from student group by name having min(fenshu)>80;
每门课都大于80分即最小分数>80分,使用聚合函数
4. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除自动编号不同, 其他都相同的学生冗余信息
创建学生表,并对id(自动编号)设置主键且自动排序
但是中间遇到了一些问题:
①直接使用查询删除
delete from student1 where id not in(select min(id) from student1 group by xuehao,name,kecheng,mingcheng,fenshu);
会提示报错 ERROR 1093 (HY000): You can't specify target table 'student1' for update in FROM clause
这是因为在MySQL中,不能通过嵌套子查询来直接删除或者修改记录,需要通过别名来指定嵌套子查询作为一个临时表。
解决办法:给嵌套子查询的结果取一个别名,然后从这个表中再次查询出记录,然后再做删除或者修改操作。
②修改后的语句:delete from student1 where id not in (select t.id from(select id from student1 group by xuehao,name,kecheng,mingcheng,fenshu) t);
此时会提示报错 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sqlmianshi.student1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这个错误是由于 MySQL 的新版本中默认开启了ONLY_FULL_GROUP_BY模式(我的MySQL是8.0),所以在 GROUP BY 语句中的 SELECT 列表中,只能包含分组或聚合函数,不能包含其他列。
解决方法:关闭only_full_group_by的规则校验
SELECT @@sql_mode;(用这个查询你的MySQL的规则)
set @@GLOBAL.sql_mode='';
set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
但是此方法只是改变了MySQL的全局设置,只对新的数据库生效,所以还要去表本身的数据库执行set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
解决上述问题后,再输入语句: delete from student1 where id not in (select t.id from(select id from student1 group by xuehao,name,kecheng,mingcheng,fenshu) t);
5. 面试题:怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
提示:创建视图
create view m1 as select * from biao wuere month =1;
先创建一个表
分别创建视图m1,m2,m3,m4
create view m1 as select * from times where month=1;
select year,
(select amount from times m where month=1 and m.year=times.year) m1,
(select amount from times m where month=2 and m.year=times.year) m2,
(select amount from times m where month=3 and m.year=times.year) m3,
(select amount from times m where month=4 and m.year=times.year) m4
from times group by year;
用year分组,用amount列查询,用m数值进行分类