mysql案例（基础）

三张表：
　　员工 employees
　　工资表 salary
　　部门 departments

# 创建员工表
create or replace table employees(
empID INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empName VARCHAR(20) NOT NULL , #姓名
sex VARCHAR(4) , #性别
birth date, #出生日期
deptID INT(20) , #部门编码
jobs VARCHAR(20) , #工作岗位
firJob date, #开始工作的日期
hiredate date,#入职日期
politicalStatus VARCHAR(20) , # 政治面貌
leader INT(10) #领导编码
);

# 创建工资表
create or replace table salary(
sid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empID INT(10) not null,
salary INT(10) not null,#工资
lastedit date #上次调薪日期
);

# 创建部门表
create or replace table departments(
deptid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
deptname VARCHAR(20) not null,#部门名称
faterdeptid INT(10) # 上级部门编码
);

一、单表查询

1、显示所有职工的基本信息。

select * from employees；

2、查询所有职工所属部门的部门号，不显示重复的部门号。

select distinct e.deptID from employees e；

3、求出所有职工的人数。

select count(*) from employees；

4、列出最高工和最低工资。

select max(s.salary) as ‘最高工资’,min(s.salary) as ‘最低工资’ from salary s；

5、列出职工的平均工资和总工资。

select AVG(s.salary) as ‘平均工资’,SUM(s.salary) as ‘总工资’ from salary s；

6、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

Create table emp2 (select e.empID,e.empName,e.firJob from employees e);

7、显示所有女职工的年龄

select e.empID, e.empName, (DATEDIFF(CURDATE(),e.birth) DIV 365) as ‘年龄’ from employees e where e.sex = ‘女’;

8、列出所有姓刘的职工的职工号、姓名和出生日期。

select e.empID,e.empName,e.birth from employees e where e.empName like ‘张%’；

9、列出1990年以前出生的职工的姓名、参加工作日期。

select e.empName,e.firJob from employees e where YEAR(e.firJob)<1990；

10、列出总人数大于4的部门号和总人数。

select e.deptID, count(*) from employees e group by e.deptID having count(1)>4；

11、列出所有陈姓和李姓的职工姓名。

select e.empName from employees e where e.empName like’张%’ or e.empName like’李%’；

12、列出所有部门号为1002和1003的职工号、姓名。

select e.empID, e.empName from employees e where e.deptID in (1002,1003);

13、将职工表worker中的职工按出生的先后顺序排序。

select * from employees e order by e.birth asc；

14、求出各部门党员的人数。

select e.deptID,count(*) from employees e where e.politicalStatus = ‘党员’ GROUP BY e.deptID；

二、多表查询

1、列出每名职工的职工号、姓名和部门名。

select e.empID, e.empName, d.deptname from employees e,departments d where e.deptID = d.deptid；

2、列出市场部的所有女职工的姓名和政治面貌。

select e.empName,e.politicalStatus,d.deptname from employees e,departments d where e.deptID = d.deptid and e.sex= ‘女’ and d.deptname = ‘市场部’；

3、显示所有职工的姓名、部门名和工资数。

select e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID

4、显示所有职工的职工号、姓名、部门名和工资，并按部门名顺序排列。

select e.empid, e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID ORDER BY e.deptID

5、显示各部门名和该部门的所有职工平均工资。

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname

6、显示所有平均工资高于1200的部门名和对应的平均工资。

SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname having AVG(s.salary)>1200

7、查询刘欣所在的部门。

select d.deptname from employees e, departments d where e.deptID = d.deptid and e.empName = ‘刘欣’

或者

select d.deptname from departments d where d.deptid =(select deptid from employees e where e.empName =‘刘欣’)

8、查询部门名为财务处的职工的情况。

select * from employees e,departments d where e.deptID = d.deptid and d.deptname =‘财务处’

或者

select * from employees e where e.deptid =(select deptid from departments d where d.deptname =‘财务处’)

9、列出工资在1000－2000之间的所有职工姓名。

select e.empName,s.salary from salary s,employees e where s.salary between 1000 and 2000 and s.empID = e.empID

10、显示工资最高的前3名职工的职工号和姓名。

select e.empID, e.empName from salary s, employees e where s.empID = e.empID ORDER BY s.salary desc LIMIT 3