不说废话,直接上干货!
1. 首先打开要保证本地已经装了mysql, 然后打开终端,启动mysql 如下:
2. 然后创建一个库,在库里面创建对应的表结构和数据(也可自行修改表结构和数据),
create table departments (deptid int(10) primary key,deptname varchar(20) not null -- 部门名称);insert into departments values ('1001', ' 市场部 ');insert into departments values ('1002', ' 测试部 ');insert into departments values ('1003', ' 开发部 ');
create table employees (empid int(10) primary key,empname varchar(20) not null, -- 姓名sex varchar(4) default null, -- 性别deptid int(20) default null, -- 部门编号jobs varchar(20) default null, -- 岗位politicalstatus varchar(20) default null, -- 政治面貌leader int(10) default null);
insert into employees values ('1', ' 王昭君 ', ' 女 ', '1003', ' 开发 ', ' 群众 ', '9');insert into employees values ('2', ' 诸葛亮 ', ' 男 ', '1003', ' 开发经理 ', ' 群众 ',null);insert into employees values ('3', ' 张飞 ', ' 男 ', '1002', ' 测试 ', ' 团员 ', '4');insert into employees values ('4', ' 白起 ', ' 男 ', '1002', ' 测试经理 ', ' 党员 ', null);insert into employees values ('5', ' 大乔 ', ' 女 ', '1002', ' 测试 ', ' 党员 ', '4');insert into employees values ('6', ' 孙尚香 ', ' 女 ', '1001', ' 市场 ', ' 党员 ', '12');insert into employees values ('7', ' 百里玄策 ', ' 男 ', '1001', ' 市场 ', ' 团员 ', '12');insert into employees values ('8', ' 小乔 ', ' 女 ', '1002', ' 测试 ', ' 群众 ', '4');insert into employees values ('9', ' 百里守约 ', ' 男 ', '1003', ' 开发 ', ' 党员 ', '9');insert into employees values ('10', ' 妲己 ', ' 女 ', '1003', ' 开发 ', ' 团员 ', '9');insert into employees values ('11', ' 李白 ', ' 男 ', '1002', ' 测试 ', ' 团员 ', '4');insert into employees values ('12', ' 孙膑 ', ' 男 ', '1001', ' 市场经理 ', ' 党员 ', null);
create table salary (sid int(10) primary key,empid int(10) not null,salary int(10) not null -- 工资);
insert into salary values ('1', '7', '2100');insert into salary values ('2', '6', '2000');insert into salary values ('3', '12', '5000');insert into salary values ('4', '9', '1999');insert into salary values ('5', '10', '1900');insert into salary values ('6', '1', '3000');insert into salary values ('7', '2', '5500');insert into salary values ('8', '5', '2000');insert into salary values ('9', '3', '1500');insert into salary values ('10', '8', '4000');insert into salary values ('11', '11', '2600');insert into salary values ('12', '4', '5300');
1.
列出总人数大于
4
的部门号和总人数
select deptid, count(*) from employees e group by deptid having count(*)>4;
2.
列出开发部和测试部的职工号,姓名
select e.empid,d.empname from employees e inner join departments d on e.deptid = d.deptid where d.deptname in ('开发部 ',' 测试部 ');
3.
求出各部门党员的人数,要求显示部门名称
select d.deptname,count(*) from employees e inner join departments d on e.deptid=d.deptidwhere e.politicalstatus =" 党员 " group by e.deptid;
4.
列出市场部的所有女职工的姓名和政治面貌
select e.empname,e.politicalstatus from employees e inner join departments d on e.deptid = d.deptid where e.sex= '女 ' and d.deptname = ' 市场部 '
5.
显示所有职工的姓名,部门名和工资数
select e.empname,d.deptname,s.salary from employees e inner join departments d on e .deptid = d.deptid inner join salary s on e.empid = s.empid
6.
显示各部门名和该部门的职工平均工资
select d.deptname, avg(s.salary) from departments d inner join employees e on d.deptid = e.deptid inner join salary s on e.empid = s.empid group by d.deptname;
7.
显示工资最高的前
3
名职工的职工号和姓名
select e.empid, e.empname,s.salary from salary s inner join employees e on s.empid = e.empid order by s.salary desc limit 3;
8.
列出工资在
1000-2000
之间的所有职工姓名
select e.empname,s.salary from salary s inner join employees e on s.empid = e.empidwhere s.salary between 1000 and 2000;
9.
列出工资比王昭君高的员工
select * from employees e inner join salary s on e.empid=s.empid where s.salary > (select s.salary from employees e inner join salary s on e.empid=s.empid where e.empna me='王昭君 ';)