redhat9---mysql8练习

目录

练习1：SELECT语句的基本使用 

1. 查询每个雇员的所有记录；

 2. 查询前5个会员的所有记录；

 3. 查询每个雇员的地址和电话；

4. 查询num为001的雇员地址和电话；

 5. 查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话；

6. 计算每个雇员的实际收入；

7. 找出所有姓王的雇员的部门号（部门号不能重复显示）；

8. 找出所有收入在2000－3000元之间的雇员编号

练习2：子查询的使用（答案可以不唯一）

  1. 查找在财务部工作的雇员情况；

2. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名；

 3. 查找比所有财务部雇员收入都高的雇员的姓名；

练习3：连接查询的使用

1. 查找每个雇员的情况及薪水情况；

 2. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况；

练习4：数据汇总

1. 求财务部雇员的平均实际收入；

2. 求财务部雇员的总人数；

练习5：GROUP BY 、ORDER BY 子句的使用

1. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；

 2. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）

---

表1 Employee表

create table Employee(
  num int primary key auto_increment,
  name varchar(30) not null,
  addr varchar(30) not null unique,
  zip varchar(30) not null,
  tel varchar(30) not null,
  email varchar(30) unique,
  depno int not null,
  birth date not null,
  sex set ("男","女")
);

表2 Department

create table department(
  depno int primary key auto_increment,
  depName varchar(30) unique not null,
  remark varchar(50)
);

表3 salay

create table salay(
  num int primary key auto_increment,
  inCome double not null,
  outCome double not null
);

alter table Employee add constraint fk_Employee_department foreign key(depno) references department(depno);

往表里添加数据：

insert into Employee(name,addr,zip,tel,email,depno,birth,sex) values
("王林","武汉大学","430074","87598405",null,2,"1985-2-1","男"),
("王芳","华中科大","430073","62534231",null,1,"1966-3-28","男"),
("张晓","武汉理工大","430072 ","87596985",Null,1,"1972-12-9","男"),
("王小燕","武汉交大","430071","85743261","lili@sina.com",1 ,"1950-7-30","女"),
("李华"," 华中农大","430070","87569865",Null,5,"1962-10-18","男"),
("李明","华中师大","430075","85362143","zhujun@sina.com ",5,"1955-09-28","男"),
("田丽","中南财大","430076","85693265","zgming@sohu.com",3,"1968-08-10","女"),
("吴天","武汉电力","430077","36985612 ","zjamg@china.com",5,"1964-10-01","男"),
("刘备"," 武汉邮科院","430078","69865231",Null,3,"1967-04-02","男"),
("赵云","学府家园","430071","68592312 ",Null,4,"1968-11-18","男"),
("貂禅","湖北工大"," 430074","65987654", null,4,"1959-09-03","女");

insert INTO department(depName,remark) values ("财务部",Null),("人力资源部",Null),("经理办公室",Null),
("研发部",Null),("市场部",Null);

INSERT INTO salay(inCome,outCome) values (2100.7,123.09),(1582.62,88.03),(2569.88,185.65),(1987.01 ,79.58),
(2066.15 ,108.0),( 2980.7, 210.2),(3259.98 ,281.52),(2860.0,198),(2347.68,180),(2531.98,199.08),(2240.0,121.0);

 

 

练习1：SELECT语句的基本使用 

1. 查询每个雇员的所有记录；

mysql> select * from Employee;

---

 2. 查询前5个会员的所有记录；

mysql> select * from Employee limit 5;

---

 3. 查询每个雇员的地址和电话；

mysql> select name,addr,tel from Employee;

---

4. 查询num为001的雇员地址和电话；

mysql> select name,addr,tel from Employee where num=001;

---

 5. 查询表Employee表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话；

mysql> select name,addr as 地址,tel as 电话 from Employee where sex='女';

---

6. 计算每个雇员的实际收入；

mysql> select num,inCome-outCome as 实际收入 from salay;

 

---

7. 找出所有姓王的雇员的部门号（部门号不能重复显示）；

mysql> select distinct depno from Employee where name like '王%';

---

8. 找出所有收入在2000－3000元之间的雇员编号

mysql> select num from salay where inCome between 2000 and 3000;

---

练习2：子查询的使用（答案可以不唯一）

  1. 查找在财务部工作的雇员情况；

mysql> select * from Employee where depno=(select depno from department where depName='财务部');

---

2. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名；

select name from Employee where depno in(
select depno from department where depName='财务部' and birth < all(
select birth from Employee where depno in(
select depno from department where depName='研发部')));

 

---

 3. 查找比所有财务部雇员收入都高的雇员的姓名；

SELECT name FROM Employee WHERE depno IN(
SELECT depno FROM salay WHERE inCome>ALL(
SELECT inCome FROM salay WHERE depno IN(
SELECT depno FROM Employee WHERE depno=(
SELECT depno FROM department WHERE depName='财务部'))));

---

练习3：连接查询的使用

1. 查找每个雇员的情况及薪水情况；

SELECT e.*,s.* FROM Employee e
INNER JOIN salay s
ON e.num=s.num;

---

 2. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况；

SELECT e.name, s.* FROM salay s
INNER JOIN Employee e
ON s.Num =e.Num
INNER JOIN department d
ON e.depno=d.depno
WHERE s.income>2200 AND d.depname='财务部';

---

练习4：数据汇总

1. 求财务部雇员的平均实际收入；

select avg(inCome-outCome) as 财务部平均实际工资 from Employee e inner join salay s on s.num=e.num inner join department d on d.depno=e.depno where d.depName='财务部'group by e.depno;

 

---

2. 求财务部雇员的总人数；

mysql> select sum(e.depno) as 财务部总人数 from Employee e inner join salay s on s.num=e.num inner join department d on d.depno=e.depnoo where d.depName='财务部'group by e.depno;

---

练习5：GROUP BY 、ORDER BY 子句的使用

1. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；

mysql> select d.depno,d.depName,sum(e.depno) from Employee e inner join department d on d.depno=e.depno group by depno;

 

---

 2. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）

select d.depno,d.depname,avg(s.income) from Employee e inner join department d on d.depno=e.depno inner join salay s on s.num=e.num group by e.depno having avg(s.inCome)>2500;