MySQL练习题 参考

#第一题 查询商品表中的重复商品记录
select pname , count(pname) from product
group by pname having cunt(panme)>1;

#count 函数是对象每个组单独进行汇总的

#第二题 查询商品表中 商品名和数量都重复的
#对商品名和数量都分组，求什么重复堆什么分组
select pname,num count(*) from product
group by pname,num
habving count(pname) >1 and
	count(num)   >1;
/*
求什么字段重复
  1、对重复字段分组
  2、having count(重复字段)>1
*/
#删除重复记录
#delete from product; 表内数据全删
#在删除上使用内连接inner join 来删除重复数据
#删除重复并保存最大id，使用inner join +自连接
#注意：将被删除数据的表起别名
#并且放在delete后面
#第一步、起别名，并且放在delete后面 便是要删除表
delete form product pro1

#第二步、自连接 建立连接
inner join product pro2 on pro1.pname = pro2.pname

第三步、加判断
where pro.pid < pro2.pid;
#要保留最大的 写小于 要保留最小的

#3、选择随机记录
#对标中数据根据随机数进行排序
select *,rand() from student order by rand();

#取表中第一个数据  limt X,Y   X开始的下标 Y取几个
select * from student limit 1;


#对数据随机排序取出第1个
select * from student order by rand() limit 1;
#对数据随机排序取出第3个
select * from student order by rand() limit 3;
#对数据随机排序取出第N个
select * from student order by rand() limit N;

#取出指定位置的数据
#取出第一个limit怎么写
select * from student limit 0,1;
#认知的第一个数据 ，下标0
#第二个数据 下标1  我们认知的第五条数据  下表是几
#取出指定位置的数据
#首先第一条是limit 0,1; 0代表第一个的下标 1 代表只能取一个
# 所以退出，第n条就是 limit n-1,1
select * from student limit 4,1;

#求出商品
select * from product order by num desc limit 1,1;

#求A1字段，存在t_a，不存t_b
#求t_a 表中 A1字段 不存在t_b的
select * from t_a where A1 not in(
	select A1 from t_b)

连表查询 inner join     left join  right join 
select *  from  t_a a
left join t_b b on a.A1 = b.A1
where b.b1 is null;

#联合查询  union 合并结果集
/*
语法：
select 字段1，字段2  from 表名1
union 
select 字段1，字段2  from 表名1
注意：
  1、表1 和表2 的字段必须数量相等类型兼容顺序一致
*/
select name, age from t_stu
union
select name, age from t_tea;
 

delete pro1 from product2 pro
inner join product2 pro2 on pro1.pname = pro2.pname
where pro1.pid <pro2.pid;#删除最大，保留最小


#随机数
select * from student order by rand();
#前五个
select * from student 
order by rand() limit 5;


#行列转换时面试题中经常会考察的题目
select * from test_tb_grade
#第一步是将每个人的数组汇总成一组显示
#行列转换第一步先分组，效果最左边的是哪个列就根据谁分组
select #分组后 select 后只能显示分组和聚合函数
*
from test_tb_grade tgg
group by ttg.user_name

#第二步求总分
select #分组后 select后面只能显示分组和聚合函数
  user_name,
  sum(77)'数学',
  sum(88)'语文',
  sum(99)'英语',
  sum(score)'总分'
from test_tb_grade
group by user_name

#第三步求个字段的和
select #分组后 select后面只能显示分组和聚合函数
  user_name,#如果要使用min 不能写else 0
# 判断 科目 当是 数学 显示 对应的成绩 显示0
  sum(case course when'数学' then score else 0 end)'数学', 
  sum(case course when'语文' then score else 0 end)'语文',
  sum(case course when'英语' then score else 0 end)'英语',
#当course 是数学的时候显示对应的值
  sum(score)'总分'
from test_tb_grade
group by user_name

#当XXX的时候，做XXX处理——判断语句
/*
 if then elseif 只能用在begin...end中
 case...when.
 语法：
   case 列 when 用于判断的值 then 满足显示内容 else 不符合 显示内容 end

   case 列      类似于java的switch做等值判断
       when 判断值1 then 符合显示内容1
       when 判断值2 then 符合显示内容2
       ...
       else 以上都不符合显示的内容
   end

    case 列      类似于java多重if 做区间判断
       when 列 = 判断值1 then 符合显示内容1
       when 列 = 判断值2 then 符合显示内容2
       ...
       else 以上都不符合显示的内容
   end

语法：exists + 子查询
外层                              内层
select 字段 from 表 where exists(子查询);
  1、用于判断 子查询有没有查询结果 
     
     如果子查询有结果  那里是null  返回true    外层就会继续查询
     如果子查询没结果  返回值false   外层就停止查询
*/
select * from student stu1
where
exists(select * from student stu2.address = '北京');


/*
查询成绩表中科目编号为2的考试成绩中是否存在不及格的学生，
如果存在不及格的学生就将参加科目编号 2
考试的学生编号和成绩全部查询显示出来
*/
select * from result rs1
where rs1.subjectid = '2'
exists(
select * from result rs2 where rs2.subjectid = '2' and rs2.score <60
);

/*
select * from 表名 where 字段 >|=|< all|any|some(子查询);
1、all any some 都是用到子查询前的
2、all 所有的 any|some 任意一个  any和some用法一样
   >all(子查询) 大于子查询中返回的所有值--大于最小
   >any(子查询) 大于子查询中返回任意一个值--大于最小
   6 > all (1,3,5)
   2 > any (1,3,5)
   小于同理
3、<> all(1,3,5)既不等于1，也不等于3，也不等于5
   <> all 等价于 not in 用法
   <> any(1,3,5)不等于1或不等于3或不等于5
4、=  all 没有意义不用
   =  any(1,3,5) 等于1 或者 等于3 或 等于5
   =  any 等价于 in的用法 
*/

#查询成绩大于科目2所有人成绩的学生学号和成绩
#第一步求出科目2所有的成绩
select rs2.score from result rs2 where rs2.subjectid = '2'

select * from result rs1
where rs1.score >
all(select rs2.score
    from result rs2
    where rs.subjectid = '2')

select rs1.studenttno,rs1.score
from result rs1
where rs1.score >
      any(select rs2.score
          from result rs2
          where rs2.subjectid = '2');



下午————————————————————————————————————————————————————————————————————————————
/*
多数情况下，表中数据会存在有一些不能直接使用的
需要我们去加工，例如字符串拼接，计算数字...
在查询中会单独形成一个虚拟列，这就是计算列
*/


#concat 
#concat(字符1,字符2)可以进行字符的拼接

select id,concat)first_name,last_name)name form users;

#计算列 数字计算
select 
  g.name '商品名称',
  g.price '商品价格',
  g.discoount '商品折扣',
  g.price * g.discoount'商品售价'
from goods g;



#排名学习 对成绩进行降序排序
select 
  rs.studentno'学号',

  rs.score'成绩'

  #第二步将排名修改为变量=变量+1 没查询一条自动+1 相当于i = i+1
  @rownum := @rownum + 1 '排名'

#第一步、在表后面给排名变量赋值为0  赋值方式是select @变量名 ：=值
from result rs,(select @rownum := 0)r
order by rs.score desc;

#用户变量(全局的) @变量名 无需声明直接使用

#局部变量（写在begin..end中）

#需求 成绩相同的都是一个名次，不跳跃排名
#当成绩一样显示 显示同一个排名 如果不一样 排名+1
/*
排名变量0
当成绩一样显示，显示同一个排名 如果不一样排名+1
成绩变量  判断成绩是否一致  初始null  无论最高成绩是多少都不会一样
排名 = 排名 + 1 ————1
 99     0   + 1  =  1
成绩  排名
99    0+1=1           将新成绩赋值给  成绩变量  99
99    使用上一个排名  将新成绩赋值给  成绩变量  99
99    使用上一个排名  将新成绩赋值给  成绩变量  99
98    1+1=2           将新成绩赋值给  成绩变量  98
排名变量@pm           成绩变量@cj
在这里面case可以实现
*/

#  := 是给变量赋值的意思
select 
  rs.studentno'学号',
  rs.score'成绩',
  case
    #当成绩相等  显示上一个排名 @cj = rs.score 是判断
    when @cj = rs.score ten @pm
    #下面是不相等的 如果不相等  @cj := rs.score 是赋值
    #如果不想等 成绩更新为最新成绩，排名+1
    when @cj := rs.score then @pm = @pm +1
  end '排名'
#第一步、给排名变量和成绩变量赋值
from result rs,(select @pm := 0,@cj := null) pc
order by rs.score desc;



#@cj 存储成绩用来判断作用：1、判断成绩是否一致 = 等号判断
#			   2、等不一致时接受新的成绩
#@pm 排名变量 1、当成绩不一样时，更新排名  @pm = @pm + 1
select 
  rs.studentno '学号',
  rs.score'成绩',
  #第二步、case when 给成绩和排名变量赋值
  case 
  #第三步、判断成绩字段和成绩变量一致，如果一致，使用统一排名
  when @cj = rs.score then @pm #这里不成了就说明不一样
  #第四步、当成绩不一样，如果成绩变量等于新成绩，排名+1
  when @cj := rs.score then @pm := @pm + 1
 end as '排名'
#第一步、给成绩和排名变量赋值
from result rs,(select @pm := 0,@cj := null) pc
order by rs.score desc;



/*
  分组统计
  分组  group by
*/

#根据地址分组，统计每个地址多少人
select
 stu.address'地址',
 count(*) '人数'
from student stu 
group by stu.address;

#需要添加添加最后一行，汇总数据  总计 使用 group by a with rollup
select 
  case
    when stu.address is null then '总计',
    else stu.address
  end'地址',
  count(*)'人数'
from student stu
group by stu.address with rollup;