8单表练习2

USE j2005_db;
set foreign_key_checks = off;

DROP TABLE IF EXISTS department;
CREATE TABLE department
(
  `id`        INT  AUTO_INCREMENT COMMENT '部门ID',
  `name`      VARCHAR(15)         COMMENT '部门名称',
  `location`  VARCHAR(13)         COMMENT '部门所在地',
  PRIMARY KEY (id)
) ENGINE = InnoDB
  COMMENT '部门信息表'; 

DROP TABLE IF EXISTS employee;
CREATE TABLE employee
(
  `id`            INT AUTO_INCREMENT COMMENT '员工ID',
  `name`          VARCHAR(16)        COMMENT '员工姓名',
  `job`           VARCHAR(16)        COMMENT '员工岗位名称',
  `manager_id`    INT                COMMENT '员工上级领导编号',
  `hire_date`     DATE               COMMENT '入职日期',
  `salary`        INT                COMMENT '工资',
  `commission`    INT                COMMENT '佣金/奖金',
  `department_id` INT                COMMENT '所属部门编号',
  PRIMARY KEY (id)
) ENGINE = InnoDB
  COMMENT '雇员信息表'; 

# ALTER TABLE employee DROP FOREIGN KEY fk_employee_department_id
ALTER TABLE employee 
    ADD CONSTRAINT fk_employee_department_id 
    FOREIGN KEY employee(department_id) 
    REFERENCES department(id);

INSERT INTO department VALUES 
    (1, 'ACCOUNTING', 'NEW YORK'),
    (2, 'RESEARCH', 'DALLAS'),
    (3, 'SALES', 'CHICAGO'),
    (4, 'OPERATIONS', 'BOSTON');

INSERT INTO employee VALUES 
    (1, 'SMITH', 'CLERK', 13, '1980-12-17', 800, NULL, 2),
    (2, 'ALLEN', 'SALESMAN', 6, '1981-02-20', 1600, 300, 3),
    (3, 'WARD', 'SALESMAN', 6, '1981-02-22', 1250, 500, 3),
    (4, 'JONES', 'MANAGER', 9, '1981-04-02', 2975, NULL, 2),
    (5, 'MARTIN', 'SALESMAN', 6, '1981-09-28', 1250, 1400, 3),
    (6, 'BLAKE', 'MANAGER', 9, '1981-05-01', 2850, NULL, 3),
    (7, 'CLARK', 'MANAGER', 9, '1981-06-09', 2450, NULL, 1),
    (8, 'SCOTT', 'ANALYST', 4, '1987-07-13', 3000, NULL, 2),
    (9, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 1),
    (10, 'TURNER', 'SALESMAN', 6, '1981-09-08', 1500, NULL, 3),
    (11, 'ADAMS', 'CLERK', 8, '1987-07-13', 1100, NULL, 2),
    (12, 'JAMES', 'CLERK', 6, '1981-12-03', 950, NULL, 3),
    (13, 'FORD', 'ANALYST', 4, '1981-12-03', 3000, NULL, 2),
    (14, 'MILLER', 'CLERK', 7, '1982-01-23', 1300, NULL, 1);

set foreign_key_checks = on;

--   --------------------------------
查看两张表的信息
select * from department;
select * from employee;

-- ------------------------------------
-- 1. 查询所有员工信息
      select * from employee;
-- 2. 查询指定的字段，只查询姓名和工资
      select name,salary from employee;
-- 3. 查询SMITH的所有信息
      select * from employee where name='SMITH';

-- 4. 查询工资在800-1500之间的员工所有信息
      select * from employee where salary between 800 and 1500;
      select * from employee where salary >= 800 and salary <= 1500;

-- 5. 统计一共有多种职业
      select count(distinct job)  多种职业 from employee;

-- 6. 使用 as 给表或者列取别名

-- 7. 求出所有员工的总工资(总工资=月薪+奖金)
      
      select salary+ifnull(commission,0) 总工资 from employee 

      -- ifnull(参数1,参数2) 若参数1为NULL 使用参数2代替

-- 8. 查询出前5条员工的信息
     select * from employee where id<=5 order by id;

     select * from employee order by id limit 0,5;

-- 9. 查询出有奖金的员工的姓名、职位、工资
    select name,job,salary from employee where commission is not  null; 

-- 10. where 子句，设置查询条件
--     101- 查询工资大于1000的员工信息
       select * from employee where salary>1000;
--     102- 查询不是SALESMAN的员工信息
        select * from employee where job !='SALESMAN';
--     103- 查询not between...and查询工资不在 1000-2000之间的员工信息
       select * from employee where salary  not between 1000 and 2000;
--     104- 模糊查询，查询出最后1个字母为S的员工信息
        select * from employee where name  like '%S';
--     105- and > or > not
--          and 所有条件都必须满足  or 只有有1个条件满足  not 取反

-- 11. 按照员工的工资进行降序排序，再按照入职时间进行升序排序

      select * from employee order by salary desc,hire_date asc;

-- 12. 按照部门编号进行分组，统计每个部门的员工人数

      select  department_id 部门编号,count(*) 员工人数  from employee group by department_id