一、新建几张表如下：

/*

SQLyog 企业版 - MySQL GUI v8.14 

MySQL - 5.5.62 : Database - offcnoa

*********************************************************************

*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`offcnoa` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `offcnoa`;

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (

  `DEPTNO` int(2) NOT NULL,

  `DNAME` varchar(14) DEFAULT NULL,

  `LOC` varchar(13) DEFAULT NULL,

  PRIMARY KEY (`DEPTNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

/*Table structure for table `emp` */

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (

  `EMPNO` int(4) NOT NULL,

  `ENAME` varchar(10) DEFAULT NULL,

  `JOB` varchar(9) DEFAULT NULL,

  `MGR` int(4) DEFAULT NULL,

  `HIREDATE` date DEFAULT NULL,

  `SAL` double(7,2) DEFAULT NULL,

  `COMM` double(7,2) DEFAULT NULL,

  `DEPTNO` int(2) DEFAULT NULL,

  PRIMARY KEY (`EMPNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `emp` */

insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,NULL,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10),(7988,'amithss','clerk',7934,'2021-06-29',3000.00,NULL,10),(7989,'a_thor','clerk',7902,'2021-06-29',2900.00,NULL,10);

/*Table structure for table `salgrade` */

DROP TABLE IF EXISTS `salgrade`;

CREATE TABLE `salgrade` (

  `GRADE` int(11) DEFAULT NULL,

  `LOSAL` int(11) DEFAULT NULL,

  `HISAL` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `salgrade` */

insert  into `salgrade`(`GRADE`,`LOSAL`,`HISAL`) values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

二、查询操作：

-- 1.查询薪资大于2000的员工信息

SELECT *FROM emp WHERE sal > 2000;

-- 2.查询员工job不是经理的

SELECT *FROM emp WHERE job != 'manager';

-- 3.查询员工的薪资大于2000 并且小于3000的员工名，薪资

SELECT *FROM emp WHERE sal > 2000 AND sal < 3000;

-- 4.查询员工的薪资不在 2000 到 3000之间的 员工名和薪资

SELECT *FROM emp WHERE sal <=2000 OR sal >=3000;

SELECT *FROM emp WHERE NOT(sal > 2000 AND sal < 3000);

-- 5.查询名中第一个字符为s的员工信息

SELECT *FROM emp WHERE ename LIKE 's%';

-- 6.查询名中第二个字符为m ,第五个字符为h的员工信息

SELECT *FROM emp WHERE ename LIKE '_m__h%';

-- 7.查询员工名中第二个字符为_的员工信息   \_将_转成普通字符

SELECT *FROM emp WHERE ename LIKE '_\_%';

-- 8.查询员工年薪(不包括补助)在2W到4W之间的 员工的年薪和名字

SELECT sal*12 年薪,ename 姓名 FROM emp WHERE sal*12 BETWEEN 20000 AND 40000;

/*

ifnull(参数1,参数2)

判断参数1  字段名称

参数1为null 取参数2的值,参数1不为null 取参数1 的值

*/

-- 9.查询员工年薪(包括补助)在2W到4W之间的 员工的年薪和名字

SELECT sal,comm,ename,(sal + IFNULL(comm,0))*12 年薪 FROM emp WHERE (sal + IFNULL(comm,0))*12 BETWEEN 20000 AND 40000;

-- 10.查询部门编号为20或者30的员工信息

SELECT *FROM emp WHERE deptno = 20 OR deptno = 30;

-- 11.查询员工的补助为null  的员工信息

SELECT *FROM emp WHERE comm IS NULL;

-- 12.查询员工的补助不为null  的员工信息

SELECT *FROM emp WHERE comm IS NOT NULL;

-- 13.查询部门编号为不为20也不为30的员工信息

SELECT *FROM emp WHERE deptno != 20 AND deptno != 30;

SELECT *FROM emp WHERE deptno NOT IN(20,30); 

-- 14.查询薪资不在2000到3000之间的员工信息

SELECT *FROM emp WHERE NOT(sal >= 2000 AND sal <= 3000);