MySQL 练习题2 参考答案

最新推荐文章于 2021-02-02 08:41:56 发布

原创最新推荐文章于 2021-02-02 08:41:56 发布 · 335 阅读

0 ·

CC 4.0 BY-SA版权

本文通过具体的SQL实战案例，展示了如何利用SQL进行数据查询、更新、删除等操作，涵盖基本查询、条件筛选、聚合函数使用、子查询、连接查询等多个方面。

1.表关系

2.下面:开始你的表演

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `dname` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '武当', '湖北');
INSERT INTO `dept` VALUES ('2', '华山', '陕西');
INSERT INTO `dept` VALUES ('3', '嵩山', '河南');
INSERT INTO `dept` VALUES ('4', '日月神教', '黑木崖');

-- ----------------------------
-- Table structure for ren
-- ----------------------------
DROP TABLE IF EXISTS `ren`;
CREATE TABLE `ren` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` int(10) DEFAULT NULL,
  `leader` int(11) DEFAULT NULL,
  `menpai` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ren
-- ----------------------------
INSERT INTO `ren` VALUES ('1', '张三丰', '100', '10000', '0', '武当');
INSERT INTO `ren` VALUES ('2', '张无忌', '20', '8000', '0', '明教');
INSERT INTO `ren` VALUES ('3', '岳不群', '40', '6500', '0', '华山');
INSERT INTO `ren` VALUES ('4', '东方不败', '35', '12000', '0', '日月神教');
INSERT INTO `ren` VALUES ('5', '令狐冲', '21', '4000', '3', '华山');
INSERT INTO `ren` VALUES ('6', '林平之', '18', '2000', '3', '华山');
INSERT INTO `ren` VALUES ('7', '金毛狮王', '50', '10000', '2', '明教');
INSERT INTO `ren` VALUES ('8', '张翠山', '46', '10000', '1', '武当');
INSERT INTO `ren` VALUES ('9', '张远桥', '55', '6500', '1', '武当');
INSERT INTO `ren` VALUES ('10', 'Alex', '12', '350', '0', 'python');

创建表及数据

1.查询所有人员信息

select * from ren;

2.只查询人员的姓名和年龄

select name,age from ren;

3.查询年龄为20岁的有哪些人员

select * from ren where age = 20;

4.查询60岁以下的人员有哪些人员

select * from ren where age < 60;

5.查询50岁以上并且工资大于8000的人员有哪些

select * from ren where age > 50 and salary >8000;

6.查询姓[张]的人员有哪些

select * from ren where name LIKE '张%';

7.查询哪些人员属于武当/华山/嵩山

select * from ren where menpai in('武当','华山','嵩山');

8.查询工资在 5000-8900 的人员有哪些

select * from ren where salary BETWEEN 5000 and 8900;

9.查询所有人员,要求按工资倒序排列

select * from ren ORDER BY salary DESC;

10.查询令狐冲的领导人是谁

select * from ren where id = (select leader from ren where name = '令狐冲');

11.查询人员表中最高工资是多少

select MAX(salary) from ren;

12.查询人员表中最低工资是多少

select MIN(salary) from ren;

13.查询所有人员的平均工资是多少

select AVG(salary) from ren;

14.查询所有人员的工资总和是多少

select SUM(salary) from ren;

15.查询目前有多少个人员

select COUNT(*) from ren;

16.查询当前武林中有哪些门派

#方式一:
    select DISTINCT menpai from ren;
 
#方式二:
    select menpai from ren GROUP BY menpai;　

17.查询武当派最高工资是谁

select name from ren WHERE menpai ='武当' AND salary = (SELECT MAX(salary) FROM ren where menpai='武当');

18.查询各门派的平均工资是多少

select menpai,AVG(salary) from ren GROUP BY menpai;

19.查询当前武林中有哪些门派的平均工资大于8000 并按工资倒序排列

select menpai,AVG(salary) from ren GROUP BY menpai HAVING AVG(salary)>8000 ORDER BY AVG(salary) DESC;

20.查询当前人员表的中的第3条数据到第7条数据

select * from ren LIMIT 2,5;

21.查询哪些门派下没有弟子

select * from ren GROUP BY menpai HAVING COUNT(*) =1;

22.查询武当派下有哪些弟子

select * from ren where menpai ='武当' and leader <> 0;

23.查询各门派的工资总和按倒序/正序排列

#正序排列
select menpai,SUM(salary) from ren GROUP BY menpai ORDER BY SUM(salary) ASC;
 
#倒序排列
select menpai,SUM(salary) from ren GROUP BY menpai ORDER BY SUM(salary) DESC;

24.删除工资重复的人员,请保留年龄最大的一个人

DELETE FROM ren where id in(
    SELECT id FROM (SELECT id from ren where salary in(SELECT salary FROM ren GROUP BY salary HAVING count(*)>1)
    AND age not in (SELECT MAX(age) FROM ren GROUP BY salary HAVING count(*)>1)) as temp
);　

25.将武当派张三丰修改为张丰

update ren set name = '张丰' where name='张三丰';

26.将所有门派大哥工资上调10%,但不包括Alex.

update ren set salary = salary+salary*0.1 where leader = 0 and name !='Alex';

27.查看哪些人员的门派已登记地理位置.

SELECT name,address FROM ren,dept where ren.menpai = dept.dname;

28.查询所有人员门派的位置信息,不存在位置信息则不显示

SELECT name,address FROM ren LEFT JOIN dept on ren.menpai = dept.dname;

29.在湖北省内的门派中的人员有哪些.

SELECT * FROM ren INNER JOIN dept on ren.menpai = dept.dname AND dept.address = '湖北';

30.在陕西省内门派中的工资小于5000,年龄大于20岁的人员有哪些,按主键倒序排列

SELECT * FROM ren INNER JOIN dept on ren.menpai = dept.dname
AND dept.address = '陕西' and ren.salary <5000 AND ren.age >20 ORDER BY ren.id DESC;