[大数据]MySQL员工表

// 员工表
CREATE TABLE emp (
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    ename VARCHAR(20) NOT NULL DEFAULT "",
    job VARCHAR(20) NOT NULL DEFAULT "",
    mgr MEDIUMINT UNSIGNED,
    hiredate DATE NOT NULL,
    sal DECIMAL (7,2) NOT NULL,
    comm DECIMAL(7,2),
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00, NULL, 20),
                       (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
                       (7521, 'WARD', 'SALESMAN',7698, '1991-2-22', 1250.00, 500.00, 30),
                       (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00, NULL, 20),
                       (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28', 1250.00, 1400.00, 30),
                       (4088, 'BLAKE', 'MANAGER', 7839, '1991-5-1', 2850.00, NULL, 30),
                       (7782, 'CLARK', 'MANAGER', 7839, '1991-6-9', 2450.00, NULL, 10),
                       (7829, 'KINc',  'PRESIDENT', NULL, '1991-11-17', 5000.00, NULL, 10),
                       (7900, 'JAMES', 'CLERK', 7698, '1991-9-8', 1500.00, NULL, 30),
                       (7902, 'FORD', 'ANALYST', 7566, '1991-12-3', 3000.00, NULL, 20),
                       (7934, 'MILLER', 'CLERK', 7782, '1992-1-23', 1300.00, NULL, 10);





// 工资等级表
CREATE TABLE salgrade (
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    losal DECIMAL (17, 2) NOT NULL,
    hisal DECIMAL (17, 2) NOT NULL
);

INSERT INTO salgrade VALUES (1, 700, 1200),
                            (2, 1201, 1400),
                            (3, 1401, 2000),
                            (4, 2001, 3000),
                            (5, 3001, 9999);




// 部门表
create table dept (
    deptno mediumint unsigned not null default 0,
    dname varchar(20) not null default '',
    loc varchar(13) not null default '');

insert into dept values (10, 'ACCOUNTING', 'NEW YORK'),
                        (20, 'RESEARCH', 'DALLAS'),
                        (30, 'SALES', 'CHICAGO'),
                        (40,'OPERATION','BOSTON');

分组函数练习

// 分组函数练习

// 1. 查询每个部门的平均工资和最高工资
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno ORDER BY deptno;

// 2. 查询每个部门的每个岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno, job FROM emp
GROUP BY deptno, job
ORDER BY deptno;

// 3. 查询平均工资低于 2000 的部门号和它的平均工资
// 分析:先查询所有部门的平均工资,然后使用 HAVING 过滤找出平均工资低于 2000 的部门
SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;

字符串函数