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