mysql 练习1

-- 显示所有部门名称
SELECT dname FROM dept;
SELECT * FROM emp
-- 显示所有雇员及其全年收入，13月（工资加补助），并指定名'年收入'
SELECT ename,(sal*12 + IFNULL (comm,0))AS '年收入'
    FROM emp;
    
-- 显示工资超过2850的雇员姓名和工资
SELECT ename,sal FROM emp
    WHERE sal > 2850;
-- 显示工资不再1500到2850之间的雇员名字和工资
SELECT ename,sal FROM emp
    WHERE sal < 1500 OR sal > 2850;
-- 显示编号为7566的雇员姓名及部门编号
SELECT ename ,deptno FROM emp
    WHERE empno = 7566;
-- 显示部门10和30中工资超过1500的雇员名及工资
SELECT ename,sal,deptno FROM emp
    WHERE sal > 1500 AND (deptno = 10 OR deptno = 30);
-- 显示无管理者的雇员名及岗位
SELECT ename,job FROM emp
    WHERE mgr IS NULL;

-- 显示在1991年2月1日 到1991年5月1日之间的雇员名，岗位，雇佣日期，并以雇佣日期排序
SELECT ename,job,hiredate FROM emp
    WHERE hiredate BETWEEN '1991-2-1' AND '1991-5-1'
    ORDER BY hiredate;
-- 显示获得补助的所有雇佣员名，工资及补助，并以工资降序
SELECT ename,sal,comm FROM emp
    WHERE comm IS NOT NULL
    ORDER BY sal DESC;
    

-- 选择部门中30的所有员工
SELECT * FROM emp
    WHERE deptno = 30;
-- 列出所有办事员clerk的姓名，编号，和部门编号
SELECT ename, empno,deptno,job FROM emp
    WHERE job = 'CLERK';
-- 找出佣金高于薪金的员工
SELECT ename,sal,comm FROM emp
    WHERE IFNULL(comm,0) > sal;
-- 找出佣金高于薪金60%的员工
SELECT ename,sal,comm FROM emp
    WHERE IFNULL(comm,0) > (sal * 0.6);
-- 找出部门10中所有经理MANAGER,部门20中所有办事员CLERK的详细资料
SELECT * FROM emp
    WHERE (deptno = 10 AND job = 'MANAGER')
    OR (DEPTNO = 20 AND job = 'CLERK');

-- 找出部门10中所有经理MANAGER，部门20所有办事员CLERK
-- 还有既不是经理又不是办事员，但其薪金>=2000的所有员工详细资料
SELECT * FROM emp
    WHERE ((deptno = 10 AND job = 'MANGER'))
    OR (deptno = 20 AND job = 'CLERK')
    OR (job != 'MANGER' AND job != 'CLERK' AND sal >= 2000);
-- 找出收取佣金的员工的不同工作
SELECT DISTINCT job FROM emp
    WHERE comm IS NOT NULL;
-- 找出不收佣金或收取的佣金低于100的员工
SELECT * FROM emp
    WHERE comm IS NULL OR IFNULL(comm,0) < 100;
-- 找出各月倒数第三天受雇的所有员工
-- 提示 LAST_DAY() 返回该日期月的最后一天
SELECT * FROM emp
    WHERE hiredate = LAST_DAY(hiredate) - 2; # 最后一天的前两天是这个月倒数第三天
-- 找出早于十二年前受雇的员工,入职时间超过12年的员工
SELECT * FROM emp
    WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW();
SELECT * FROM emp
    WHERE DATE_SUB(NOW(),INTERVAL 12 YEAR) > hiredate;
-- 以首字母小写方式显示所有员工姓名
SELECT CONCAT(LCASE(LEFT(ename,1)),SUBSTRING(ename,2))
    AS '首字母小写name'
    FROM emp;
-- 显示正好为5个字符的员工姓名
SELECT ename FROM emp
    WHERE LENGTH(ename) = 5;
    
    

-- 显示不带有R的员工姓名
SELECT ename FROM emp
    WHERE ename NOT LIKE '%R%';
-- 显示所有员工姓名前三个字符
SELECT LEFT(ename,3) FROM emp;
-- 显示所有员工的姓名，用a替代所有A
SELECT  REPLACE(ename,'A','a')FROM emp;
-- 显示满十年工作的员工姓名和受雇日期
SELECT ename,hiredate FROM emp
    WHERE DATE_SUB(NOW(),INTERVAL 10 YEAR) >= hiredate;
-- 显示员工的详细资料，按姓名排序
SELECT * FROM emp
    ORDER BY ename;
-- 显示员工姓名和受雇日期，根据工作年限，最老排最前
SELECT ename,hiredate FROM emp
    ORDER BY hiredate ;

-- 显示所有员工姓名，工作和薪资，按照工作降序排序，若工作相同按薪资排序
SELECT ename,job, sal FROM emp
    ORDER BY job DESC,sal;
-- 显示所有员工姓名，加入公司的年份和月份，受雇日期所在月排序，
-- 如月份相同则按年份早到晚排序
SELECT ename,CONCAT(YEAR(hiredate),'年',MONTH(hiredate),'月')
    AS '受雇年月' FROM emp
    ORDER BY MONTH(hiredate),YEAR(hiredate);
-- 显示在一个月为30天的情况所有员工的日薪资，忽略余数
SELECT ename,FLOOR(sal/30),sal/30 FROM emp;
-- 找出在（任何年份的）2月受聘的所有员工
SELECT * FROM emp
    WHERE MONTH(hiredate) = 2;
-- 对于每个员工，显示加入公司的天数
SELECT ename,DATEDIFF(NOW(),hiredate)
    AS '加入公司时间' FROM emp;
    
    

-- 显示姓名字段任何位置包含A的所有成员
SELECT ename  FROM emp
    WHERE ename LIKE '%A%' ;
-- 以年月日的方式显示所有员工的服务年限(大概)
SELECT ename ,'工作年月日',FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '年',
    FLOOR(FLOOR(DATEDIFF(NOW(),hiredate)%365)/30) AS '月',
    FLOOR(DATEDIFF(NOW(),hiredate)%365)%30 AS '天'
    FROM emp ;
    
    

-- 工资 = sal + comm
-- 列出至少有一个员工的所有部门
SELECT COUNT(*)AS c,deptno FROM emp
    GROUP BY deptno
    HAVING c > 1
-- 列出薪金比SMITH多的所有员工
SELECT sal FROM emp
    WHERE ename = 'SMITH';
SELECT * FROM emp
    WHERE sal >= (SELECT sal FROM emp
        WHERE ename = 'SMITH')
    HAVING ename != 'SMITH';
-- 列出受雇日期晚于其直接上级的所有员工
-- emp 当作两张表，一张为worker,一张为leader (自连接)
-- 通过worker.mgr与leader.empno 进行日期比较
SELECT leader.ename,leader.hiredate ,worker.ename AS '其员工',
    worker.hiredate AS '员工入职日期'
    FROM emp worker,emp leader
    WHERE worker.hiredate > leader.hiredate
    AND worker.mgr = leader.empno
-- 列出所有部门名称和这些部门员工的员工信息，同时列出那些员工没有部门
SELECT *FROM emp
SELECT * FROM dept
SELECT * FROM  salgrade
SELECT  dname,emp.*
    FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno

-- 列出所有CLERK的姓名及其部门名称
SELECT emp.ename,job,dept.dname
    FROM emp LEFT JOIN dept ON dept.deptno = emp.deptno
    AND emp.job = 'CLERK';
-- 列出最低薪金大于1500的各种工作
SELECT job,MIN(sal)AS min_sal FROM emp
        GROUP BY job
        HAVING min_sal > 1500;
    
-- 列出在部门SALES 工作的员工的姓名
SELECT dname,ename FROM emp,dept
    WHERE emp.deptno = dept.deptno
    AND dept.dname = 'sales';
-- 列出薪金高于公司平均薪金的所有员工
SELECT ename,sal FROM emp
    WHERE sal > (SELECT AVG(sal)
            FROM emp
            )
-- 列出与SCOTT从事相同工作的所有员工
SELECT * FROM emp
    WHERE job = (SELECT job FROM emp
            WHERE ename = 'SCOTT')
    HAVING ename != 'SCOTT';
-- 列出薪金高于所在部门30工作的所有员工的薪金的员工姓名和薪金
SELECT * FROM emp
    WHERE sal > (SELECT MAX(sal) FROM emp
            WHERE deptno = 30);
-- 列出每个部门工作的员工数量，平均工资和平均服务期限
SELECT COUNT(*)AS '部门人数',
    AVG(sal)AS '部门平均工资',
    AVG(DATEDIFF(NOW(),hiredate))/ 365 AS '部门平均服务年数'
    FROM emp GROUP BY deptno
-- 列出所有员工姓名，部门名称和工资
SELECT ename,dname,sal
    FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
-- 列出所有部门的详细信息和部门人数
SELECT dept.*,dept_nums.num
    FROM dept LEFT JOIN (SELECT COUNT(*) AS num ,deptno
    FROM emp
    GROUP BY deptno) dept_nums ON dept_nums.deptno = dept.deptno
;
-- 列出各种各工作的最低工资
SELECT job,MIN(sal) FROM emp
    GROUP BY job
-- 列出MANAGER的最低薪金
SELECT MIN(sal) FROM emp
    WHERE job = 'MANAGER';
-- 列出所有员工的年工资，按年薪从低到高
SELECT ename ,(sal+IFNULL(comm,0))*12
    year_sal FROM emp
    ORDER BY year_sal