004-数据库查询练习
练习：
CREATE TABLE product
(
pid INT PRIMARY KEY,
pname VARCHAR(20),
price DOUBLE,
category_id VARCHAR(32)
);
INSERT INTO product VALUES (1,'联想',5000,'c001');
INSERT INTO product VALUES (2,'海尔',3000,'c001');
INSERT INTO product VALUES (3,'雷神',5000,'c001');
INSERT INTO product VALUES (4,'杰克琼斯',800,'c002');
INSERT INTO product VALUES (5,'真维斯',200,'c002');
INSERT INTO product VALUES (6,'花花公子',440,'c002');
INSERT INTO product VALUES (7,'劲霸',2000,'c002');
INSERT INTO product VALUES (8,'香奈儿',800,'c003');
INSERT INTO product VALUES (9,'相宜本草',200,'c003');
INSERT INTO product VALUES (10,'面霸',5,'c003');
INSERT INTO product VALUES (11,'好想你枣',56,'c004');
INSERT INTO product VALUES (12,'香飘飘奶茶',1,'c005');
INSERT INTO product VALUES (13,'海澜之家',1,'c002');
select * from product; # 1.查询所有商品
select pname,price from product;# 2.查询商品名和商品价格.
select pid,pname,price+10,category_id from product;# 3.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price from product where price = 800;# 4.查询价格为800商品
select pname,price from product where price > 60;# 5.查询商品价格大于60元的所有商品信息
select pname,price from product where price between 200 and 1000;# 6.查询商品价格在200到1000之间所有商品
select pname,price from product where pname like '_想%';# 7.查询第二个字为'想'的所有商品
SQL聚合函数
# 创建考试表
create table exam
(
id int primary key comment '学号',
name varchar(20) comment '学生姓名',
email varchar(50) not null comment '电子邮箱',
chinese decimal(4, 1) comment '语文成绩',
english decimal(4, 1) comment '英语成绩',
math decimal(4, 1) comment '数学成绩'
);
# 插入学生考试数据
insert into exam
values (202301, '张华', '123452@163.com', 69, 112, 110),
(202302, '李三', '1452563@163.com', 115.5, 120, 89),
(202303, '宋七', '36215465@qq.com', 110, 113, 66),
(202304, '王五', '15547522@163.com', 89, 65, 78),
(202305, '赵四', '15623355@163.com', 90, 112, 130),
(202306, '李八', '18625222@163.com', null, null, null);
# 试题1、查询此班级参加考试的学生总人数
select count(*) from exam;
# 试题2、查询语文成绩及格的总人数
select count(*) from exam where chinese >= 60;
# 试题3、查询学生的各科平均成绩（评判班级的整体水平），查询语文、英语、数学考试成绩的平均分
select avg(chinese),avg(english),avg(math) from exam;
# 试题4、查询学生是否有偏科情况，数学成绩高于105分的学员，语文考试成绩的平均分
select avg(chinese) from exam where math > 105;
# 试题5、（拔尖学员）查询班级的各科最高分、最低分
select max(chinese) as chinese_max ,
min(chinese) as chinese_min ,
max(english) as english_max ,
min(english) as english_min ,
max(math) as math_max ,
min(math) as math_min
from exam;
# 试题6、查询个人综合实力拔尖的学员
select id,name,(chinese+math+english) as total_score from exam order by total_score desc limit 1;
连接查询
# 试题1：参考如下表格，回答相关问题
#
# 1，Person为左表：Id_P为用户唯一性主键，LastName和FristName为用户的名和姓，Address为地址，City为所在城市。
#
# 2，Orders为右表：Id_O为订单唯一性主键，OrderNo为订单号，Id_P用于标识此订单属于谁。
#
# 根据需求判断选择的连接查询方式：v
CREATE TABLE Persons
(
Id_P int not null primary key,
LastName char(20),
FirstName char(20),
Address char(20),
City char(20)
);
CREATE TABLE Orders
(
Id_O int not null primary key,
OrderNo char(20),
Id_P int not null
);
insert into Persons (id_p, lastname, firstname, address, city)
values (1, 'Adams', 'John', 'Oxford Street', 'London'),
(2, 'Bush', 'George', 'Fifth Avenue', 'New York'),
(3, 'Carter', 'Thomas', 'Changan Street', 'Beijing');
select * from Persons;
insert into Orders (id_o, orderno, id_p)
values (1, '77895', 3),
(2, '44678', 3),
(3, '22456', 1),
(4, '24562', 1),
(5, '34764', 65);
select * from Orders;
# 问题1：编写一个SQL查询出每个订单属于谁，如果订单没有归属人也需展示。
select O.OrderNo,P.FirstName,P.LastName from Persons P right join Orders O on P.Id_P = O.Id_P;
# 问题2：编写一个SQL查询每个用户有没有订单，有没有订单都要展示。
select P.FirstName,P.LastName,count(O.OrderNo)
from Persons P
left join Orders O on P.Id_P = O.Id_P
group by P.Id_P, P.FirstName, P.LastName;
# 问题3：编写一个SQL查看每个有消费记录的人，都下了那些单。
select *
from Persons inner join Orders O on Persons.Id_P = O.Id_P;
# 前置条件创建表员工表emp 部门表dep
CREATE TABLE emp (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
sex varchar(3) DEFAULT NULL,
d_id int(11) DEFAULT NULL,
age int(3) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;
CREATE TABLE dep (
id int(11) NOT NULL AUTO_INCREMENT,
dname varchar(20) DEFAULT NULL,
g_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;
# 1.使用内连接查询销售部年龄在35岁以上的员工。
select * from emp where age > 35;
-- 下面是答案
SELECT name,sex,age,dname
FROM emp
JOIN dep ON emp.d_id=dep.id
WHERE dep.dname="销售部" AND emp.age >35;
# 2.使用子查询查出下图效果的SQL。
select E.name,E.sex,E.age,D.dname,(select E2.name from emp E2 where E2.id = D.g_id)
from emp E left join dep D on E.d_id = D.id;
-- 下面是答案
SELECT `name`,sex,age,dep1.部门名称,dep1.部门负责人
FROM emp
JOIN (
SELECT d1.id as "did",dname as "部门名称",`name` as "部门负责人"
FROM dep as d1
JOIN emp as e1 ON d1.g_id=e1.id
) dep1 WHERE emp.d_id=dep1.did;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ===
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE order_items (
id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO orders (user_id, order_date) VALUES
(101, '2021-01-01'),
(102, '2021-01-02'),
(101, '2021-01-05'),
(103, '2021-01-06'),
(102, '2021-01-07'),
(101, '2021-01-10');
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1001, 2, 50.00),
(1, 1003, 1, 30.00),
(2, 1002, 1, 80.00),
(3, 1001, 4, 50.00),
(4, 1004, 1, 20.00),
(4, 1001, 2, 50.00),
(5, 1002, 3, 80.00),
(6, 1003, 1, 30.00),
(6, 1004, 2, 20.00);
# 假设你正在管理一个销售订单的数据库，其中有两个表：orders和order_items。
#
# orders表包含订单的基本信息，如订单号、用户ID、下单日期等；
# order_items表包含订单中的商品信息，如商品ID、数量、价格等。
# 现在你需要编写一个SQL查询，查找每个用户的最早和最晚下单日期，并计算其之间的天数差距。
-- 使用 GROUP BY 按 user_id 对 orders 表进行分组
SELECT
user_id,
-- 使用 MIN 函数找出每个用户的最早下单日期
MIN(order_date) AS earliest_order_date,
-- 使用 MAX 函数找出每个用户的最晚下单日期
MAX(order_date) AS latest_order_date,
-- 使用 DATEDIFF 函数计算最晚和最早下单日期之间的天数差距
DATEDIFF(MAX(order_date), MIN(order_date)) AS days_difference
FROM
orders
GROUP BY
user_id;
-- 建表语句
drop table if exists student;
create table if not exists student(
name varchar(50) not null,
continent varchar(50) not null
);
insert into student (name,continent) values ('Jack','America');
insert into student (name,continent) values ('Pascal','Europe');
insert into student (name,continent) values ('Xi','Asia');
insert into student (name,continent) values ('Jane','America');
select * from student;
# 一所学校有来自亚洲、欧洲和美洲的学生，写一个查询语句实现对大洲（continent）列的透视表 操作，
# 使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面，
# 输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe），
# 测试用例的生成使得来自美国的学生人数不少于亚洲或欧洲的学生人数。
-- 步骤一：找出美洲大陆的人，并将America作为列名，学员列表展示
select row_number() over (order by name) as rn, name as America
from student
where continent = 'America';
-- 步骤二：找出亚洲大陆的人，并将Asia作为列名，学员列表展示
select row_number() over (order by name) as rn, name as Asia
from student
where continent = 'Asia';
-- 步骤三：找出亚洲大陆的人，并将Europe作为列名，学员列表展示
select row_number() over (order by name) as rn, name as Europe
from student
where continent = 'Europe';
-- 步骤四:通过编号关联查询获取结果
select America, Asia, Europe
from (
select row_number() over (order by name) as rn, name as America
from student
where continent = 'America'
) a
left join(
select row_number() over (order by name) as rn, name as Asia
from student
where continent = 'Asia'
) b on a.rn = b.rn
left join(
select row_number() over (order by name) as rn, name as Europe
from student
where continent = 'Europe'
) c on a.rn = c.rn;
-- 方法二 利用组内编号 再加编号分组聚合来完成
select rn,
max(if(continent == 'America', name, null)) America,
max(if(continent == 'Asia', name, null)) Asia,
max(if(continent == 'Europe', name, null)) Europe
from (select continent,
name,
row_number() over (partition by continent order by name) rn
from student) t
group by rn;
CREATE TABLE IF NOT EXISTS trade_history
(
BASE_DATE DATE NOT NULL comment '交易日期',
SEQUENCE INT NOT NULL comment '交易序列号',
USER_ID INT NOT NULL comment '用户ID',
TRADE_AMOUNT INT NOT NULL comment '交易金额',
PRIMARY KEY (BASE_DATE, SEQUENCE, USER_ID)
);
insert into trade_history
values ('2020-06-05', 1, 1, 100),
('2020-06-07', 1, 1, 200),
('2020-06-07', 2, 1, 150),
('2020-06-25', 1, 2, 100),
('2020-06-27', 1, 2, 200),
('2020-06-27', 2, 2, 250),
('2020-06-27', 3, 2, 120);
select * from trade_history;
# 试题1：计算每天共成交多少单
select BASE_DATE,count(*) from trade_history group by BASE_DATE;
# 试题2：计算每天成交额是多少
select BASE_DATE,sum(TRADE_AMOUNT) from trade_history group by BASE_DATE;
# 试题3：获取成交额高于400元的日期：分组后在筛选用having
select BASE_DATE from trade_history group by BASE_DATE having sum(TRADE_AMOUNT)>400;
# 试题4：获取单日成交额的平均值
select avg(daily_total)
from (
select sum(TRADE_AMOUNT) daily_total from trade_history group by BASE_DATE
)a;
# 试题5：成交额高于400元的一共有几天
select count(BASE_DATE)
from (
select BASE_DATE from trade_history group by BASE_DATE having sum(TRADE_AMOUNT)>400
)a;
# 编写一个SQL查询来查找收入比经理高的员工，以任意顺序返回结果。
drop table kettle_demo.Employee;
Create table Employee
(
id int,
name varchar(255),
salary int,
managerId int
);
Truncate table Employee;
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3');
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4');
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', NULL);
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', NULL);
select employee.name
from kettle_demo.Employee
join Employee E on employee.managerId = E.id
where employee.salary > E.salary;
use kettle_demo;
drop table Employee;
Create table If Not Exists Employee
(
id int,
salary int
);
insert into Employee (id, salary) values ('1', '100');
insert into Employee (id, salary) values ('2', '200');
insert into Employee (id, salary) values ('3', '300');
# 编写SQL取出第二高的工资的人。
select * from Employee order by salary desc limit 1 offset 1;
# 编写SQL查询以查找每个部门中薪资最高的员工，按任意顺序返回结果表。
# 建表语句如下：
drop table kettle_demo.Employee;
drop table Department;
Create table If Not Exists Employee
(
id int,
name varchar(255),
salary int,
departmentId int
);
Create table If Not Exists Department
(
id int,
name varchar(255)
);
Truncate table Employee;
insert into Employee (id, `name`, salary, departmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1');
Truncate table Department;
insert into Department (id, name) values ('1', 'IT');
insert into Department (id, name) values ('2', 'Sales');
# 答
SELECT
department,
employee,
salary
FROM(
SELECT
e.id,
e.name AS employee,
e.salary,
d.name AS department,
# 使用 RANK() 窗口函数为每个部门内的员工按薪资降序排名
RANK() OVER (PARTITION BY e.departmentId ORDER BY e.salary DESC) as salary_rank
FROM
Employee e
JOIN
Department d ON e.departmentId = d.id
)a
WHERE
salary_rank = 1;
# 有一群人在等着上电梯。然而，电梯有1000 公斤的重量限制，
# 所以可能会有一些人不能上，写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。
# 题目确保队列中第一位的人可以进入电梯，不会超重。
Create table If Not Exists Queue
(
person_id int,
person_name varchar(30),
weight int,
turn int
);
Truncate table Queue;
insert into Queue (person_id, person_name, weight, turn) values ('5', 'Alice', '250', '1');
insert into Queue (person_id, person_name, weight, turn) values ('4', 'Bob', '175', '5');
insert into Queue (person_id, person_name, weight, turn) values ('3', 'Alex', '350', '2');
insert into Queue (person_id, person_name, weight, turn) values ('6', 'John Cena', '400', '3');
insert into Queue (person_id, person_name, weight, turn) values ('1', 'Winston', '500', '6');
insert into Queue (person_id, person_name, weight, turn) values ('2', 'Marie', '200', '4');
SELECT person_id,
person_name,
weight,
turn,
-- 使用 SUM() 窗口函数计算从第一个人到当前人的累计重量
SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM
Queue;
-- 从 CalculatedQueue 中筛选出累计重量不超过 1000 公斤且 turn 值最大的记录
SELECT
person_name
FROM(
SELECT
person_name,
weight,
turn,
-- 使用 SUM() 窗口函数计算从第一个人到当前人的累计重量
SUM(weight) OVER (ORDER BY turn) AS cumulative_weight
FROM
Queue
)CalculatedQueue
WHERE
cumulative_weight <= 1000
ORDER BY
turn DESC
LIMIT 1;
# 假设我们有一个销售订单表sales_orders，包含以下字段：
CREATE TABLE sales_orders
(
order_id INT NOT NULL AUTO_INCREMENT,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id)
);
INSERT INTO sales_orders (order_date, customer_id, product_id, quantity, price)
VALUES ('2021-01-01', 1, 1001, 2, 50.00),
('2021-01-01', 1, 1002, 1, 30.00),
('2021-01-02', 1, 1001, 3, 50.00),
('2021-01-03', 2, 1002, 2, 30.00),
('2021-01-03', 2, 1003, 1, 40.00),
('2021-01-04', 3, 1001, 1, 50.00),
('2021-01-04', 3, 1003, 2, 40.00),
('2021-01-05', 3, 1004, 3, 20.00),
('2021-01-06', 3, 1005, 1, 60.00);
# 我们希望查询每日订单的销售额及其在该日期的排名。
# 请使用窗口函数编写SQL查询语句，返回销售订单表中的每个订单日期、销售额和在该日期的排名。
select order_date,daily_sales,rank() over(order by daily_sales desc)
from (
select order_date, SUM(quantity * price) as daily_sales
from sales_orders
group by order_date
)a
;