使用MySQL完成题目一-- 部门表create table deptdeptno int primary key auto_increment -- 部门编号dname varchar14 -- 部门名字loc varchar13 -- 地址 ;-- 员工表create table empempno int primary key auto_increment-- 员工编号ename varc

SELECT DISTINCT dept.dname FROM dept INNER JOIN emp ON dept.deptno = emp.deptno;

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '刘一');

SELECT e1.ename, e2.ename AS manager_name FROM emp e1 INNER JOIN emp e2 ON e1.mgr = e2.empno;

SELECT e1.* FROM emp e1 INNER JOIN emp e2 ON e1.mgr = e2.empno AND e1.hiredate < e2.hiredate;

SELECT dept.dname, emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;

SELECT emp.ename, dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE emp.job = '职员';

SELECT job FROM emp GROUP BY job HAVING MIN(sal) > 1500;

SELECT emp.ename FROM emp INNER JOIN dept ON emp.deptno = dept.deptno AND dept.dname = '销售部';

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

SELECT e1.* FROM emp e1 INNER JOIN emp e2 ON e1.job = e2.job AND e2.ename = '周八';

SELECT e1.ename, e1.sal FROM emp e1 WHERE e1.sal = (SELECT sal FROM emp WHERE deptno = 30);

SELECT e1.ename, e1.sal FROM emp e1 WHERE e1.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

SELECT dept.dname, COUNT(emp.empno), AVG(emp.sal) FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.dname;

SELECT emp.ename, dept.dname, emp.sal FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

SELECT dept.*, COUNT(emp.empno) AS employee_count FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno GROUP BY dept.deptno;

SELECT job, MIN(sal) FROM emp GROUP BY job;

SELECT deptno, MIN(sal) AS min_sal FROM emp WHERE job = '经理' GROUP BY deptno;

SELECT ename, sal * 12 AS annual_salary FROM emp ORDER BY annual_salary;

SELECT empno, ename, sal FROM emp WHERE sal >= 3000;

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '陈二');

SELECT empno AS '员工编号', ename AS '员工名字', sal AS '薪水' FROM emp WHERE deptno = 20 AND sal > 2000;

SELECT DISTINCT job FROM emp;

SELECT * FROM emp WHERE comm IS NOT NULL;

-- 方法1 SELECT * FROM emp WHERE sal >= 800 AND sal <= 2500;

-- 方法2 SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;

-- 方法1 SELECT * FROM emp WHERE empno = 7521 OR empno = 7900 OR empno = 7782;

-- 方法2 SELECT * FROM emp WHERE empno IN (7521, 7900, 7782);

SELECT * FROM emp WHERE ename LIKE '%张%' AND sal > 1000;

SELECT * FROM emp WHERE SUBSTR(ename, 3, 1) = '忌';

SELECT * FROM emp ORDER BY sal ASC, hiredate DESC;

SELECT * FROM emp ORDER BY CONVERT(ename USING gbk) ASC, sal DESC;

SELECT ename, hiredate, sal FROM emp ORDER BY hiredate ASC LIMIT 1;

SELECT ename, sal, IFNULL(comm, 100) AS comm FROM emp;

SELECT job FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

SELECT deptno, MAX(sal) AS max_sal, MIN(sal) AS min_sal FROM emp WHERE deptno != 10 GROUP BY deptno;

DELETE FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = 10) AND deptno = 10) ORDER BY sal DESC LIMIT 1;

UPDATE emp SET sal = sal * 0.7 WHERE sal = (SELECT MAX(sal) FROM emp)