面试-MySQL(七)
面试-MySQL(一)
面试-MySQL(二)
面试-MySQL(三)
面试-MySQL(四)
面试-MySQL(五)
面试-MySQL(六)
文章目录
1.有表如下,请完成以下问题
去除重复的手机号码并升序输出
SELECT number
FROM phone
GROUP BY number
ORDER BY number ASC;
找出重复记录大于2 手机号码
SELECT number FROM phone
GROUP BY number HAVING COUNT(number) >= 2 ;
2.有学生成绩表如下:
S_C表
name subject ach
查找各科成绩都大于80分的学生姓名
SELECT DISTINCT `name` FROM S_C
WHERE `name` NOT IN (
SELECT DISTINCT `name` FROM S_C
WHERE ach <= 80 );
查找平均成绩大于80分的学生姓名
SELECT `name`,AVG(ach) AS Avg_ach FROM S_C
GROUP BY `name` HAVING Avg_ach > 80;
3.编写sql语句
A表
aid anme task cid
B表
bid bname remark keys
C表
cid cname age sex
现将A表与C表中的数据插入B表,条件当A表cid与C表cid值相等,将A表 task 对应B表 remark,表C cname 对应B表 bname ,表A aid 与 表C cid 拼接成B表 bid ,B表keys 固定为202107
INSERT BB (bid,banem,remark,keys)
(SELECT CONCAT(a.aid,c.cid) AS bid,
c.cname AS bname,a.task AS remark,202107 AS keys
FROM AA a,CC c WHERE a.cid = c.cid);