MySQL 语句查询的方法案例分享，和一些常用查询语句 2023.7.08

一、案例分享

/* 素材 */
#创建数据表
CREATE TABLE `emp`  (
  empno int(4) NOT NULL,
  ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  mgr int(4) NULL DEFAULT NULL,
  hiredate date NOT NULL,
  sai int(255) NOT NULL,
  comm int(255) NULL DEFAULT NULL,
  deptno int(2) NOT NULL,
  PRIMARY KEY (empno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

#插入数据
INSERT INTO 'emp' VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO 'emp' VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO 'emp' VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO 'emp' VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO 'emp' VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO 'emp' VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO 'emp' VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO 'emp' VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO 'emp' VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO 'emp' VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO 'emp' VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO 'emp' VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO 'emp' VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO 'emp' VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO 'emp' VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);

案例查询要求

1. 查询出部门编号为30的所有员工

mysql> select * from emp where deptno = 30;

 2. 所有销售员的姓名、编号和部门编号

mysql> select empno,ename,deptno from emp where job = '销售员';

3. 找出奖金高于工资的员工

mysql> select * from emp where comm>sai;

4. 找出奖金高于工资60%的员工

mysql> select * from emp where comm>sai*0.6;

5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料

mysql> select * from emp where deptno = 10 and job = '经理' or deptno = 20 and job = '销售员';

6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有既不是经理又不是销售员但其工资大或等于20000的所有员工详细资料

mysql> select * from emp where deptno = 10 and job = '经理' or deptno = 20 and job = '销售员' or  job != '销售员' and job != '经理' and sai > 20000;

7. 无奖金或奖金低于1000的员工

mysql> select * from emp where comm is NULL or comm < 1000;

8. 查询名字由三个字组成的员工

mysql> select * from emp where ename like '___';   //三个下划线

9.查询2000年入职的员工

mysql> select * from emp where year(hiredate)=2000;

10. 查询所有员工详细信息，用编号升序排序

mysql> select * from emp order by empno;

11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

mysql> select * from emp order by sai desc,hiredate;

12.查询每个部门的平均工资

mysql> select deptno,avg(sai) tail from emp group by deptno;

13.查询每个部门的雇员数量

mysql> select deptno, count(*) from emp
    -> group by deptno;

14.查询每种工作的最高工资、最低工资、人数

mysql> select max(sai),min(sai),count(*) from emp group by job;