Mysql 常用语句实战（3）

前置 sql 语句

用来创建表、插入数据

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE
IF
    EXISTS dept_;-- 部门表
DROP TABLE
IF
    EXISTS emp_;-- 部门表
SET FOREIGN_KEY_CHECKS = 1;

SELECT  @@FOREIGN_KEY_CHECKS;

CREATE TABLE dept_ ( DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR ( 14 ), -- 部门名称
LOC VARCHAR ( 13 ) -- 部门地址
);
INSERT INTO dept_
VALUES
    ( 10, 'ACCOUNTING', 'NEW YORK' );
INSERT INTO dept_
VALUES
    ( 20, 'RESEARCH', 'DALLAS' );
INSERT INTO dept_
VALUES
    ( 30, 'SALES', 'CHICAGO' );
INSERT INTO dept_
VALUES
    ( 40, 'OPERATIONS', 'BOSTON' );
CREATE TABLE emp_ (
emp_NO INT PRIMARY KEY,-- 员工编号
ENAME VARCHAR ( 10 ),-- 员工名称
JOB VARCHAR ( 9 ),-- 工作
MGR DOUBLE,-- 直属领导编号
HIREDATE DATE,-- 入职时间
SAL DOUBLE,-- 工资
COMM DOUBLE,-- 奖金
DEPTNO INT,-- 部门号
FOREIGN KEY ( DEPTNO ) REFERENCES dept_ ( DEPTNO ) 
);
INSERT INTO emp_
VALUES
    ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30 );
INSERT INTO emp_
VALUES
    ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30 );
INSERT INTO emp_
VALUES
    ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30 );
INSERT INTO emp_
VALUES
    ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30 );
INSERT INTO emp_
VALUES
    ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10 );
INSERT INTO emp_
VALUES
    ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10 );
INSERT INTO emp_
VALUES
    ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30 );
INSERT INTO emp_
VALUES
    ( 7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30 );
INSERT INTO emp_
VALUES
    ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20 );
INSERT INTO emp_
VALUES
    ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10 );

 

问题列表

1、列出至少有三个员工的所有部门和部门信息。

2、列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称

3、列出职位为“CLERK”的姓名和部门名称，部门人数：

 

答案列表

1.列出至少有三个员工的所有部门和部门信息。

select * from dept_ where DEPTNO in (select DEPTNO from emp_ group by DEPTNO having count(1) >= 3)

2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select a.emp_NO,a.ENAME,(select DNAME from dept_ c where a.DEPTNO = c.DEPTNO) "部门名称" from emp_ a join emp_ b on a.MGR = b.emp_NO where a.HIREDATE < b.HIREDATE

3.列出职位为“CLERK”的姓名和部门名称，部门人数：
select group_concat(a.ENAME),b.DNAME,count(1) from emp_ a join dept_ b on a.DEPTNO = b.DEPTNO  where a.JOB = "CLERK" group by b.DEPTNO