Select查询语句详解（MySQL）

-- 创建数据库
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 创建养老院表结构
CREATE TABLE ljb_t_house
(
id INT PRIMARY KEY auto_increment,
`name` VARCHAR(20),
address VARCHAR(20),
manager VARCHAR(10)
) 
ENGINE=INNODB DEFAULT CHARSET=utf8 auto_increment=1;
-- 创建人员表
CREATE TABLE ljb_t_man
(
id INT PRIMARY KEY auto_increment,
houseid INT,
pername VARCHAR(10),
age INT,
title VARCHAR(10),
department VARCHAR(10),
sex INT
)
ENGINE=INNODB DEFAULT CHARSET=utf8 auto_increment=1;
-- 创建工资表
CREATE TABLE ljb_t_salary
(
id INT PRIMARY KEY auto_increment,
manid INT,
salary FLOAT,
time DATE)
ENGINE=INNODB DEFAULT CHARSET=utf8 auto_increment=1;
-- 创建收入表
CREATE TABLE ljb_t_income
(
id INT PRIMARY KEY auto_increment,
houseid INT,
income FLOAT,
type VARCHAR(10),
product VARCHAR(10),
producter VARCHAR(10)
)
ENGINE=INNODB DEFAULT CHARSET=utf8 auto_increment=1;
-- 插入数据
-- 插入养老院
INSERT INTO ljb_t_house (`name`,address,manager)
VALUES ('小李村养老院','江苏省南京市江宁区','李小龙');

INSERT INTO ljb_t_house (`name`,address,manager)
VALUES ('帝都养老院','北京市朝阳区','张三');

INSERT INTO ljb_t_house (`name`,address,manager)
VALUES ('魔都养老院','上海市浦东区','王五');
-- 插入人员
INSERT INTO ljb_t_man (houseid,pername,age,title,department,sex)
VALUES (1,'刘俊杰',25,'高级','集团服务部',1);

INSERT INTO ljb_t_man (houseid,pername,age,title,department,sex)
VALUES (1,'李丹',25,'中级','集团服务部',0);

INSERT INTO ljb_t_man (houseid,pername,age,title,department,sex)
VALUES (3,'王倩',23,'中级','招待部',0);

INSERT INTO ljb_t_man (houseid,pername,age,title,department,sex)
VALUES (2,'张三丰',20,'初级','功夫部',1);
-- 插入工资
INSERT INTO ljb_t_salary (manid,salary,time)
VALUES (1,10000,20170420);

INSERT INTO ljb_t_salary (manid,salary,time)
VALUES (4,7000,20170420);

INSERT INTO ljb_t_salary (manid,salary,time)
VALUES (3,15000,20170410);

INSERT INTO ljb_t_salary (manid,salary,time)
VALUES (2,9000,20170415);

TRUNCATE TABLE ljb_t_salary;

-- 插入收入
INSERT INTO ljb_t_income (houseid,income,type,product,producter)
VALUES (1,100000,'养老金','颐养天年',2);

INSERT INTO ljb_t_income (houseid,income,type,product,producter)
VALUES (2,200000,'保健品','脑白金',3);

INSERT INTO ljb_t_income (houseid,income,type,product,producter)
VALUES (3,300000,'保健品','哇哈哈',1);

INSERT INTO ljb_t_income (houseid,income,type,product,producter)
VALUES (3,100000,'保健品','哇哈哈',4);
-- 查询
-- 创建总表视图
CREATE VIEW ljb_v_all
AS
SELECT a.*,b.age,b.department,b.pername,b.sex,b.title,c.salary,c.time,d.income,d.product,d.type
FROM ljb_t_house a,ljb_t_man b,ljb_t_salary c,ljb_t_income d
WHERE 1=1
AND a.id=b.houseid
AND b.id=c.manid
AND c.id=d.producter;

-- 江苏省内养老院
SELECT DISTINCT a.`name`,a.address FROM ljb_v_all a WHERE a.address LIKE '%江苏省%';

-- 创建小李村养老院视图
CREATE VIEW ljb_v_xiaoli
AS
SELECT *
FROM ljb_v_all a
WHERE a.`name` LIKE '%小李村%';

-- 小李村养老院的女员工
SELECT a.`name`,a.pername
FROM ljb_v_xiaoli a
WHERE a.sex=0;

-- 3.2017.4江苏省养老院的工资总和
SELECT SUM(salary)
FROM ljb_v_all a
WHERE a.address LIKE '%江苏省%'
AND a.time<20170431
AND a.time>20170400;

-- 小李村的2017年中级职称的性别比例
SELECT COUNT(CASE WHEN sex=0 THEN 'female' END) 女,
COUNT(CASE WHEN sex=1 THEN 'male' END)男,
left(COUNT(CASE WHEN sex=1 THEN 'male' END)/COUNT(CASE WHEN sex=0 THEN 'female' END),3)男女比例
FROM ljb_v_xiaoli a
WHERE a.title LIKE '中级';

SELECT  CONCAT (LEFT((d.`男`/d.`女`),3)) 男女比例
FROM (SELECT COUNT(CASE WHEN sex=0 THEN 'female' END) 女,
COUNT(CASE WHEN sex=1 THEN 'male' END)男
FROM ljb_v_xiaoli a
WHERE a.title LIKE '中级')d;

-- 5.小李村2017年每个人的总工资
SELECT a.pername,SUM(salary)
FROM ljb_v_xiaoli a
WHERE a.time<20171232
AND a.time>20170000
GROUP BY a.pername;

-- 6.脑白金在2017年上半年的总销售额
SELECT a.product,SUM(income)
FROM ljb_v_all a
WHERE a.time<20170700
AND a.time>20170000
AND a.product LIKE '%白金%';

-- 7.脑白金在哪个院的销售额最多
SELECT a.`name`,a.product,SUM(income)
FROM ljb_v_all a
WHERE a.time<20171232
AND a.time>20170000
AND a.product LIKE '%白金%'
GROUP BY a.`name`
ORDER BY SUM(income) DESC
LIMIT 1;

-- 8.2017脑白金销量最高的销售员的养老院的职称，薪资（2017年总和）
SELECT a.pername,a.`name`,a.product,SUM(income) 销售额,a.time,SUM(a.salary)总收入
FROM ljb_v_all a
WHERE a.time<20171232
AND a.time>20170000
AND a.product LIKE '%白金%'
GROUP BY a.pername
ORDER BY SUM(income) DESC
LIMIT 1;

-- 9.2017年各个养老院的收入与支出比
SELECT a.`name`,SUM(a.income)总收入,SUM(a.salary)总支出,CONCAT(LEFT(SUM(a.income)/SUM(a.salary),4)) 收入支出比
FROM ljb_v_all a
GROUP BY a.`name`;