MySQL练习题一

一、Member成员表

表结构：

数据：

-- 1.得到公司的所有部门
SELECT member.department FROM member GROUP BY member.department
-- 2.得到每个部门及其人数
SELECT member.department AS 部门,
COUNT(member.id) AS 人数
FROM member
GROUP BY member.department
-- 3.得到每个部门的最高工资
SELECT member.department AS 部门,
MAX(member.salary) AS 最高工资
from member
GROUP BY member.department
-- 4.得到公司中男员工以及女员工的人数
SELECT member.sex AS 性别,
COUNT(member.id) AS 人数
FROM member
GROUP BY member.sex
-- 5.取得公司男员工和女员工的平均工资
SELECT member.sex AS 性别,
AVG(member.salary) AS 平均工资
FROM member
GROUP BY member.sex
-- 6.找到男员工中工资最低者和女员工中工资最低者
SELECT member.`name` AS 姓名,member.sex AS 性别,MIN(member.salary) AS 最低工资
from member
GROUP BY member.sex
-- 7.找出高于公司所有员工的平均工资的男员工数目和女员工数目
--方法一：
SELECT
	member.sex AS 性别,
	COUNT( member.sex ) AS 人数 
FROM
	member 
WHERE
	member.salary > ( SELECT AVG( member.salary ) FROM member ) 
GROUP BY
	member.sex
	
--方法二：	
SELECT
	AVG( salary ) AS 平均工资,
	(
	SELECT
		count(*) 
	FROM
		member 
	WHERE
		sex = '男' 
		AND salary >(
		SELECT
			AVG( salary ) 
		FROM
			member 
		)) AS 女,
	(
	SELECT
		count(*) 
	FROM
		member 
	WHERE
		sex = '女' 
		AND salary >(
		SELECT
			AVG( salary ) 
		FROM
			member 
		)) AS 男 
FROM
	member

-- 8.得到每个部门最高工资对应的员工
SELECT member.`name` AS 姓名,member.department AS 部门,MAX(member.salary) AS 最高工资
FROM member
GROUP BY member.department

二、company表和sales表

-- 1.得出各人员及所在公司信息
SELECT *
FROM company
LEFT JOIN sales ON company.id = sales.companyid

-- 2.得到各公司的人数
SELECT COUNT(sales.id) AS 人数,company.`name` AS 公司
FROM company
LEFT JOIN sales ON sales.companyid = company.id
GROUP BY company.`name`

-- 3.取得销售业绩最好的两个公司及其销售总额
SELECT SUM(sales.salesvolume) AS 销售业绩,company.`name` AS 公司
FROM sales
LEFT JOIN company ON company.id = sales.companyid
GROUP BY sales.companyid
ORDER BY SUM(sales.salesvolume) DESC
LIMIT 2
-- 4.找出销售业绩最高的个人及所在的公司
SELECT MAX(sales.salesvolume) AS 销售业绩,company.`name` AS 公司,sales.`name` AS 姓名
FROM company
LEFT JOIN sales ON sales.companyid = company.id
WHERE sales.salesvolume = (SELECT MAX(sales.salesvolume) FROM sales)

-- 5.得到各公司的销售总额。
SELECT SUM(sales.salesvolume) AS 销售业绩,company.`name` AS 公司
FROM sales
LEFT JOIN company ON company.id = sales.companyid
GROUP BY sales.companyid

-- 6.得到销售业绩最好的三个个人及所在公司信息。
SELECT sales.`name` AS 姓名,company.`name` AS 公司,sales.salesvolume AS 个人业绩
from company
LEFT JOIN sales ON sales.companyid = company.id
ORDER BY sales.salesvolume DESC
LIMIT 3
-- 7.得到个人销售业绩最差的个人及所在公司。
SELECT sales.`name` AS 姓名,company.`name` AS 公司,sales.salesvolume AS 个人业绩
from company
LEFT JOIN sales ON sales.companyid = company.id
WHERE sales.salesvolume IS NOT NULL
ORDER BY sales.salesvolume 
LIMIT 1

以上图表的sql文：

create table member(
  id INT primary key,
  name varchar(20),
  sex varchar(20),
  salary double(10,2),
  department varchar(20)
);

insert into member values(1,'小王','男',1500,'市场部');
insert into member values(2,'小李','女',1800,'销售部');
insert into member values(3,'小刘','男',2300,'生产部');
insert into member values(4,'小赵','女',2000,'财务部');
insert into member values(5,'小黄','男',3500,'市场部');
insert into member values(6,'吴工','女',4500,'生产部');
insert into member values(7,'小孙','男',2600,'生产部');
insert into member values(8,'老胡','男',7500,'人力部');
insert into member values(9,'温哥','男',10000,'总务部');


create table company(
id varchar2(10) primary key,
name varchar2(20)
);

insert into company values('01','蜀国分公司');
insert into company values('02','吴国分公司');
insert into company values('03','魏国分公司');
insert into company values('04','西南夷分公司');


create table sales(
id INT primary key,
name varchar(20),
companyid varchar(20),
salesvolume INT
);

insert into sales values(1,'关羽','01',10000);
insert into sales values(10,'张辽','03',10500);
insert into sales values(11,'吕布','05',18000);
insert into sales values(2,'张飞','01',11000);
insert into sales values(3,'赵云','01',12000);
insert into sales values(4,'马超','01',9000);
insert into sales values(5,'黄忠','01',8500);
insert into sales values(6,'甘宁','02',5000);
insert into sales values(7,'黄盖','02',3000);
insert into sales values(8,'周泰','03',6000);
insert into sales values(9,'徐晃','03',9500);


create table movie(
   ID INT not null primary key, 
   NAME VARCHAR(500) 
);


 insert into movie ( ID, NAME ) values ( 1, '倩女幽魂' );
 insert into movie ( ID, NAME ) values ( 2, '杀破狼' );
 insert into movie ( ID, NAME ) values ( 3, '刀' );
 insert into movie ( ID, NAME ) values ( 4, '七剑下天山' );
 insert into movie ( ID, NAME ) values ( 5, '枪火' );
 insert into movie ( ID, NAME ) values ( 6, '黑楼孤魂' );
 
 
 create table tag(
   ID INT not null primary key, 
   NAME VARCHAR(500) 
);


 insert into tag ( ID, NAME ) values ( 1, '动作' );
 insert into tag ( ID, NAME ) values ( 2, '古装' );
 insert into tag ( ID, NAME ) values ( 3, '现代' );
 insert into tag ( ID, NAME ) values ( 4, '鬼片' );
 insert into tag ( ID, NAME ) values ( 5, '枪战' );
 insert into tag ( ID, NAME ) values ( 6, '悬疑' );

 
 create table movie2tag(
   ID INT not null primary key, 
   movied INT,
   tager INT    
   );

 insert into  movie2tag  values ( 1, 1,2);
 insert into  movie2tag  values ( 2, 1,4);
 insert into  movie2tag  values ( 3, 2,1);
 insert into  movie2tag  values ( 4, 2,3);
 insert into  movie2tag  values ( 5, 3,1);
 insert into  movie2tag  values ( 6, 3,2);
 insert into  movie2tag  values ( 7, 4,1);
 insert into  movie2tag  values ( 8, 4,2);
 insert into  movie2tag  values ( 9, 4,6);
 insert into  movie2tag  values ( 10, 5,3);
 insert into  movie2tag  values ( 11, 5,5);
 insert into  movie2tag  values ( 12, 5,6);
 insert into  movie2tag  values ( 13, 6,4);
 insert into  movie2tag  values ( 14, 6,6);