#查询员工的姓名

SELECT ename FROM emp;

#查询员工的薪水以及姓名

SELECT ename,sal FROM emp;

#插叙员工表所有的数据

SELECT * FROM emp;

#查询每一个员工的年薪（包含补助）

SELECT ename,(sal + IFNULL(comm,0)) * 12 AS 年薪 FROM emp;

#查询薪水为5000的员工

SELECT * FROM emp WHERE sal = 5000;

#查询职位为MANAGER的员工的信息

SELECT * FROM emp WHERE job = "manager";

#查询薪水不等于5000的员工的信息

SELECT * FROM emp WHERE sal != 5000;

#查询职位不是MANAGER的员工的信息

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

#薪水大于1600的员工信息

SELECT * FROM emp WHERE sal > 1600;

#薪水在1600和3000之间的员工信息

SELECT * FROM emp WHERE sal BETWEEN 1600 AND 3000;

#查询部门编号为20或者30的部门信息

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

#查询部门编号为20或者30的信息

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

#查询部门编号不为20也不为30的信息

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

#查询第一个字母带有s的员工信息

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

#要求第二个字母带有m的员工的信息

SELECT * FROM emp WHERE ename LIKE "_m%";

#根据员工的薪水排序（升序）

SELECT * FROM emp ORDER BY sal ASC;

#根据员工的薪水排序（降序）

SELECT * FROM emp ORDER BY sal DESC;

#员工入职日期降序查询

SELECT * FROM emp ORDER BY hiredate DESC;

#查询职位为MANAGER的员工信息，并且按照薪资从高到低排序

SELECT * FROM emp WHERE job = "manager" ORDER BY sal DESC;

#查询emp前五条数据

SELECT * FROM emp LIMIT 5;

#员工每页5条数据，查询前三页

SELECT * FROM emp LIMIT 0,5;

SELECT * FROM emp LIMIT 5,5;

SELECT * FROM emp LIMIT 10,5;

#查询JAMES的部门地址

ALTER TABLE emp ADD CONSTRAINT fk_ed FOREIGN KEY (deptno) REFERENCES dept(deptno);

SELECT e.ename,d.loc FROM emp e INNER JOIN dept d ON d.deptno = e.deptno AND e.ename = "james";

#查询RESEARCH部门所有员工信息

SELECT * FROM dept d LEFT JOIN emp e ON d.deptno = e.DEPTNO AND d.dname = 'research';