题目地址:查询多列_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university FROM user_profile
题目地址:查询所有列_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT * FROM user_profile
题目地址:查询结果去重_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT DISTINCT university FROM user_profile
题解2:
SELECT university FROM user_profile GROUP BY university
题目地址:查询结果限制返回行数_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT device_id FROM user_profile LIMIT 2
题解2:
SELECT device_id FROM user_profile LIMIT 0,2
题目地址:将查询后的列重新命名_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id AS user_infos_example FROM user_profile LIMIT 2
题目地址:查找后排序_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,age FROM user_profile ORDER BY age ASC
题目地址:查找后多列排序_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC
题目地址:
题解:
SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC
题目地址:查找学校是北大的学生信息_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,university FROM user_profile WHERE university="北京大学"
题目地址:查找年龄大于24岁的用户信息_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university FROM user_profile WHERE age > 24
题目地址:查找某个年龄段的用户信息_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT device_id,gender,age FROM user_profile WHERE age>=20 AND age<=23
题解2:
SELECT device_id,gender,age FROM user_profile WHERE BETWEEN 20 AND 23
题目地址:查找除复旦大学的用户信息_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT device_id,gender,age,university FROM user_profile WHERE university NOT IN ("复旦大学")
题解2:
SELECT device_id,gender,age,university FROM user_profile WHERE university != "复旦大学"
题目地址:用where过滤空值练习_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university FROM user_profile WHERE age IS NOT NULL
题目地址:高级操作符练习(1)_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE gender = 'male' AND gpa > 3.5
题目地址:高级操作符练习(2)_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university = '北京大学' OR gpa > 3.7
题目地址:Where in 和Not in_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university IN ('北京大学','复旦大学','山东大学')
题解2:
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university NOT IN ('浙江大学')
题目地址:操作符混合运用_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,gender,age,university,gpa FROM user_profile WHERE (university='山东大学' and gpa > 3.5)
OR (university="复旦大学" and gpa > 3.8)
题目地址:查看学校名称中含北京的用户_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT device_id,age,university FROM user_profile WHERE university LIKE "%北京%"
题目地址:查找GPA最高值_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT MAX(gpa) FROM user_profile WHERE university="复旦大学"
题目地址:计算男生人数以及平均GPA_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT COUNT(gender) AS 'male_num',ROUND(AVG(gpa),1) AS 'avg_gpa' FROM user_profile WHERE gender="male"
题目地址:分组计算练习题_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT gender,university,COUNT(device_id) AS 'user_num',AVG(active_days_within_30) AS 'avg_active_day',AVG(question_cnt) AS 'avg_question_cnt' FROM user_profile GROUP BY gender,university
题目地址:分组过滤练习题_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT university,AVG(question_cnt) AS 'avg_question_cnt',AVG(answer_cnt) AS 'avg_answer_cnt' FROM user_profile GROUP BY university HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20
HAVING
子句来指定一组行或聚合的过滤条件。
HAVING
子句通常与 GROUP BY 子句一起使用
题目地址:分组排序练习题_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT university,AVG(question_cnt) AS avg_question_cnt FROM user_profile GROUP BY university ORDER BY avg_question_cnt ASC
题目地址:浙江大学用户题目回答情况_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT device_id,question_id,result FROM question_practice_detail WHERE device_id
IN (SELECT device_id FROM user_profile WHERE university='浙江大学')
题解2:
SELECT t1.device_id, t1.question_id, t1.result
FROM question_practice_detail AS t1
INNER JOIN user_profile AS t2
ON t2.device_id = t1.device_id AND t2.university='浙江大学'
题目地址:统计每个学校的答过题的用户的平均答题数_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT t1.university, ROUND(COUNT(t2.question_id)/ COUNT(DISTINCT t2.device_id),4)AS avg_answer_cnt
FROM question_practice_detail AS t2
INNER JOIN user_profile as t1
ON t1.device_id = t2.device_id
GROUP BY university
ORDER BY university ASC
题目地址:统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网 (nowcoder.com)
题解:
问题分解:
group by university
group by difficult_level
count(qpd.question_id) / count(distinct qpd.device_id)
SELECT
t1.university,
t3.difficult_level,
ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id), 4) AS avg_answer_cnt
FROM question_practice_detail AS t2
LEFT JOIN user_profile AS t1
ON t1.device_id = t2.device_id
LEFT JOIN question_detail AS t3
ON t3.question_id = t2.question_id
GROUP BY t1.university, t3.difficult_level
题解2:
SELECT
t1.university,
t3.difficult_level,
ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM
user_profile t1,
question_practice_detail t2,
question_detail t3
WHERE
t1.device_id = t2.device_id
and
t2.question_id = t3.question_id
GROUP BY t1.university,t3.difficult_level;
题目地址:统计每个用户的平均刷题数_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT
t1.university,
t3.difficult_level,
ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM
user_profile AS t1,
question_practice_detail AS t2,
question_detail AS t3
WHERE
t1.device_id = t2.device_id
and
t2.question_id = t3.question_id
and
university ="山东大学"
GROUP BY t3.difficult_level
题解2:
SELECT
university,
difficult_level,
ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM question_practice_detail AS t2
INNER JOIN user_profile AS t1
ON t1.device_id = t2.device_id AND t1.university="山东大学"
INNER JOIN question_detail AS t3
ON t3.question_id = t2.question_id
GROUP BY difficult_level
题目地址:查找山东大学或者性别为男生的信息_牛客题霸_牛客网 (nowcoder.com)
题解:
university='山东大学'
, gender='male'
;
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university='山东大学'
UNION ALL
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE gender='male'
题目地址:计算25岁以上和以下的用户数量_牛客题霸_牛客网 (nowcoder.com)
CASE函数
是一种多分支的函数,可以根据条件列表的值返回多个可能的结果表达式中的一个。可用在任何允许使用表达式的地方,但不能单独作为一个语句执行。
CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END
IF函数
IF( expr1 , expr2 , expr3 )
题解1:
SELECT CASE
WHEN age < 25 OR age is null THEN '25岁以下'
WHEN age >= 25 THEN '25岁及以上'
END age_cut,COUNT(*) AS number
FROM user_profile
GROUP BY age_cut
题解2:
SELECT CASE
WHEN age >= 25 THEN '25岁及以上'
ELSE '25岁以下'
END age_cut,COUNT(*) AS number
FROM user_profile
GROUP BY age_cut
题解3:
SELECT IF(age >= 25,"25岁及以上","25岁以下") AS age_cut,count(*) AS number
FROM user_profile
GROUP BY age_cut;
题目地址:查看不同年龄段的用户明细_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT
device_id,
gender,
CASE
WHEN age >= 25 THEN "25岁及以上"
WHEN age < 20 THEN "20岁以下"
WHEN 20 <= age <= 24 THEN "20-24岁"
ELSE "其他"
END AS age_cut
FROM user_profile
题目地址:计算用户8月每天的练题数量_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT DAY(date) AS day,COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date)="2021" and MONTH(date)="08"
GROUP BY day
题目地址:计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)
题解:
次日留存率 = 去重的数据表中符合次日留存的条目数目 / 去重的数据表中所有条目数目
SELECT
COUNT(t2.device_id) / COUNT(t1.device_id) AS avg_ret
FROM
(SELECT DISTINCT device_id, date FROM question_practice_detail)as t1
LEFT JOIN
(SELECT DISTINCT device_id, date FROM question_practice_detail) AS t2
ON t1.device_id = t2.device_id AND t2.date = DATE_ADD(t1.date, interval 1 day)
题目地址:统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)
substring_index函数
substring_index(str,delim,count)
str = 'www.wikidm.cn'
//取前面
substring_index(str,'.',1)
结果是:www
substring_index(str,'.',2)
结果是:www.wikidm
//取后面
substring_index(str,'.',-1)
结果为:cn
substring_index(str,'.',-2)
结果为:wikidm.cn
//取中间
substring_index(substring_index(str,'.',-2),'.',1);
结果为:wikidm
题解1:
SELECT substring_index(profile,',',-1) AS gender, COUNT(*) AS number
FROM user_submit
GROUP BY gender
题解2:
SELECT CASE
WHEN `profile` LIKE "%,male" THEN 'male'
WHEN `profile` LIKE "%,female" THEN 'female'
END AS gender, COUNT(device_id) AS number
FROM user_submit
GROUP BY gender
题解3:
SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;
题目地址:截取出年龄_牛客题霸_牛客网 (nowcoder.com)
题解1:
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY age
SUBSTR函数
截取字符串
substr(string str,num start,num length);
题解2:
SELECT
SUBSTR(profile,12,2) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY age
题目地址:提取博客URL中的用户名_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT
# 替换法 replace(string, '被替换部分','替换后的结果')
device_id, replace(blog_url,'http:/url/','') as user_name
# 删除法 trim('被删除字段' from 列名)
# device_id, trim('http:/url/' from blog_url) as user_name
# 截取法 substr(string, start_point, length*可选参数*)
# device_id, substr(blog_url,11,length(blog_url)-10) as user_nam
# 字段切割法 substring_index(string, '切割标志', 位置数(负号:从后面开始))
# device_id, substring_index(blog_url,'/',-1) as user_name
FROM user_submit
题目地址:找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)
题解1:用 group by 把学校分组,然后计算得到每个学校最低 gpa,再去找这个学校里和这个 gpa 相等的同学 device_id
SELECT device_id,university,gpa
FROM user_profile
WHERE (university,gpa)
IN (SELECT university,MIN(gpa) FROM user_profile GROUP BY university)
ORDER BY university ASC
题解2:窗口函数
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
窗口函数语法:通俗易懂的学会:SQL窗口函数
SELECT device_id,university,gpa
FROM (SELECT *,rank() over (partition by university
order by gpa) AS rk FROM user_profile) AS uni_min
WHERE uni_min.rk=1
ORDER BY university
题目地址:统计复旦用户8月练题情况_牛客题霸_牛客网 (nowcoder.com)
题解:
SELECT
t1.device_id,
t1.university,
SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt,
SUM(IF(t2.result = "right",1,0)) AS right_question_cnt
FROM
user_profile AS t1
LEFT JOIN question_practice_detail AS t2
ON t1.device_id = t2.device_id AND MONTH(t2.date) = 8
WHERE university = "复旦大学"
GROUP BY t1.device_id;
题目地址:
题解:题解 | #浙大不同难度题目的正确率#_牛客博客 (nowcoder.net)
SELECT
difficult_level,
sum(if(t2.result='right', 1, 0)) / count(t2.question_id) as correct_rate
FROM
user_profile AS t1
INNER JOIN question_practice_detail AS t2
ON t1.device_id = t2.device_id
INNER JOIN question_detail AS t3
ON t2.question_id = t3.question_id
WHERE university = "浙江大学"
GROUP BY difficult_level
ORDER BY correct_rate
题目地址:21年8月份练题总数_牛客题霸_牛客网 (nowcoder.com)
题解1:date_format
SELECT
COUNT(DISTINCT device_id) AS did_cnt,
COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE date_format(date, '%Y-%m')='2021-08'
题解2:YEAR、MONTH
SELECT
COUNT(DISTINCT device_id) AS did_cnt,
COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date) = 2021 AND MONTH(date) = 08
题解3:LIKE
SELECT
COUNT(DISTINCT device_id) AS did_cnt,
COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE date LIKE "2021-08%"
题解:
SELECT cust_id FROM Customers
题解:
SELECT DISTINCT prod_id FROM OrderItems
题解:
SELECT * FROM Customers
-- SELECT cust_id FROM Customers
题解:
SELECT cust_name FROM Customers ORDER BY cust_name DESC
题解:
SELECT cust_id,order_num FROM Orders ORDER BY cust_id,order_date DESC
题解:
SELECT quantity,item_price FROM OrderItems ORDER BY quantity DESC,item_price DESC;
题解:
SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC
题解:
SELECT prod_id,prod_name
FROM Products
WHERE prod_price = 9.49
题解:
SELECT prod_id,prod_name
FROM Products
WHERE prod_price >= 9
题解:
SELECT prod_name,prod_price
FROM Products
WHERE prod_price between 3 and 6
ORDER BY prod_price
题解:
SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity >= 100
题解:
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
题解:
SELECT order_num,prod_id,quantity
FROM OrderItems
WHERE quantity >= 100 AND prod_id IN ('BR01','BR02','BR03')
ORDER BY prod_id,order_num
题解:
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price
题解:
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name
题解:
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE "%toy"
题解:
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc NOT LIKE "%toy"
ORDER BY prod_name
题解:
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE "%toy%" AND prod_desc LIKE "%carrots%"
题解:
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc LIKE "%toy%carrots%"
题解:
SELECT vend_id,vend_name AS vname,vend_address AS vaddress,vend_city AS vcity
FROM Vendors
ORDER BY vname
题解:
SELECT prod_id,prod_price,(prod_price * 0.9) AS sale_price
FROM Products
题解:
SELECT
cust_id,
cust_name,
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) AS user_login
FROM Customers
题解1:
SELECT order_num,order_date
FROM Orders
WHERE YEAR(order_date) = '2020' AND MONTH(order_date) = '1'
ORDER BY order_date
题解2:
SELECT order_num,order_date
FROM Orders
WHERE date_format(order_date,'%Y-%m')='2020-01'
ORDER BY order_date
题解:
SELECT sum(quantity) AS items_ordered
FROM OrderItems
题解:
SELECT sum(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id ='BR01'
题解:
SELECT max(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10
题解:
SELECT order_num,count(order_num) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines
题解:
SELECT vend_id, min(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item
题解:
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING sum(quantity) >= 100
ORDER BY order_num
题解:
SELECT order_num, sum(item_price*quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num
题解:
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
题解:
SELECT cust_id
FROM Orders
WHERE order_num in(
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(item_price) >= 10
)
题解:
SELECT cust_id,order_date
FROM Orders
WHERE order_num in(
SELECT order_num
FROM OrderItems
WHERE prod_id = 'BR01'
ORDER BY cust_id,order_date
)
题解:
select cust_email from Customers
where cust_id in (select cust_id from Orders where order_num
in (select order_num from OrderItems where prod_id='BR01'))
题解:
select cust_id, sum(quantity*item_price) total_ordered
from OrderItems AS a, Orders AS b
where a.order_num = b.order_num
group by cust_id
order by total_ordered desc
题解:
select prod_name, SUM(quantity) quant_sold
from Products AS a, OrderItems AS b
where a.prod_id = b.prod_id
group by prod_name
联结表:
题解1:
SELECT cust_name,order_num
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name,order_num
题解2:
SELECT cust_name,order_num
FROM Customers AS t1
INNER JOIN Orders AS t2
ON t1.cust_id = t2.cust_id
ORDER BY cust_name,order_num
题解1:
SELECT cust_name,Orders.order_num, sum(quantity*item_price) AS OrderTotal
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name,order_num
ORDER BY cust_name,order_num
题解2:
select cust_name,Orders.order_num,sum(quantity*item_price) as OrderTotal
from Orders
inner join Customers on Orders.cust_id = Customers.cust_id
inner join OrderItems on Orders.order_num = OrderItems.order_num
group by cust_name,Orders.order_num
order by cust_name,Orders.order_num
题解1:
SELECT cust_id,order_date
FROM OrderItems,Orders
WHERE OrderItems.order_num = Orders.order_num AND prod_id = "BR01"
ORDER BY order_date
题解2:
SELECT cust_id, order_date
FROM Orders
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
WHERE prod_id = "BR01"
ORDER BY order_date
题解1:
SELECT cust_email
FROM OrderItems,Orders,Customers
WHERE OrderItems.order_num = Orders.order_num
AND Orders.cust_id = Customers.cust_id AND prod_id = "BR01"
ORDER BY order_date
题解2:
SELECT cust_email
FROM Orders
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
INNER JOIN Customers ON Orders.cust_id = Customers.cust_id
WHERE prod_id = "BR01"
ORDER BY order_date
题解:
SELECT cust_name,sum(item_price*quantity) AS total_price
FROM Orders
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
INNER JOIN Customers ON Orders.cust_id = Customers.cust_id
GROUP BY Customers.cust_name
HAVING total_price >= 1000
ORDER BY total_price
题解:
SELECT cust_name,order_num
FROM Orders
INNER JOIN Customers ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name
题解:
SELECT cust_name,order_num
FROM Customers
LEFT JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name
题解:
SELECT prod_name,order_num
FROM Products
LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name
题解:
SELECT prod_name, COUNT(order_num) AS orders
FROM Products
LEFT JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name
SELECT Vendors.vend_id,COUNT(prod_id) AS prod_id
FROM Vendors
LEFT JOIN Products ON Products.vend_id = Vendors.vend_id
GROUP BY vend_id
ORDER BY vend_id
题解:
SELECT prod_id,quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE "BNBG%"
题解:
SELECT prod_id,quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE "BNBG%"
ORDER BY prod_id
题解:
SELECT prod_name FROM Products
UNION
SELECT cust_name FROM Customers
ORDER BY prod_name
题解:
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_state = 'MI'
#ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;
题解:
INSERT INTO exam_record VALUES(null,1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),(null,1002,9002,'2021-09-04 07:01:02',null,null)
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2,... FROM 来源表 [WHERE key=value]
题解:
INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';
题解1:
REPLACE INTO examination_info VALUES(null,9003,"SQL","hard",90,"2021-01-01 00:00:00")
题解2:
DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')
题解:
UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON"
题解:
UPDATE exam_record
SET submit_time = "2099-01-01 00:00:00",score = 0
WHERE start_time < "2021-09-01" AND submit_time is null
时间差函数:
题解:
DELETE FROM exam_record
WHERE timestampdiff(minute,start_time,submit_time) < 5 AND score < 60
题解:
DELETE FROM exam_record
WHERE submit_time is null OR timestampdiff(minute,start_time,submit_time) < 5
ORDER BY start_time
LIMIT 3
1.DROP TABLE 清除数据并且销毁表,是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销,被删除表格的关系,索引,权限等等都会被永久删除。
2.TRUNCATE TABLE 只清除数据,保留表结构,列,权限,索引,视图,关系等等,相当于清零数据,是一种数据库定义语言(DDL Data Definition Language),执行后不能撤销。
3.DELETE TABLE 删除(符合某些条件的)数据,是一种数据操纵语言(DML Data Manipulation Language),执行后可以撤销。
题解:
# -- 清空表,并将自增主键设置为1
truncate table exam_record
# -- 清空表数据,自增主键不恢复
DELETE FROM exam_record;
ALTER TABLE exam_record auto_increment = 1;
题解:
create table user_info_vip(
id int(11) not null primary key auto_increment Comment "自增ID" ,
uid int(11) not null unique comment "用户ID",
nick_name varchar(64) comment "昵称",
achievement int(11) default 0 comment "成就值",
level int(11) comment "用户等级",
job varchar(32) comment "职业方向",
register_time datetime default CURRENT_TIMESTAMP comment "注册时间"
)character set utf8 collate utf8_general_ci engine innodb
题解:
alter table user_info
add school varchar(15) after `level`,
change job profession varchar(10),
modify achievement int(11) default 0
题解:
DROP TABLE IF EXISTS exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014
索引创建:
CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
# UNIQUE -- 唯一索引
# FULLTEXT -- 全文索引
# 不指定唯一索引或全文索引时默认为普通索引
ALTER TABLE 表名
ADD [UNIQUE | FULLTEXT] INDEX 索引名(column)`
索引删除
DROP INDEX <索引名> ON <表名>
ALTER TABLE <表名> DROP INDEX <索引名>
索引使用:
题解1:
CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);
题解2:
-- 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);
-- 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
-- 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);
删除
DROP INDEX <索引名> ON <表名>
ALTER TABLE <表名> DROP INDEX <索引名>
题解1:
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;
题解2:
ALTER TABLE examination_info DROP INDEX uniq_idx_exam_id;
ALTER TABLE examination_info DROP INDEX full_idx_tag;
题解:
select
tag,
difficulty,
round((sum(score)-(max(score)+min(score)))/(count(score)-2),1) clip_avg_score
from examination_info t1
inner join exam_record t2
on t1.exam_id = t2.exam_id
where tag = "SQL" and difficulty = "hard"
题解1:
SELECT
COUNT(exam_id) AS total_pv,
COUNT(submit_time) AS complete_pv,
COUNT(DISTINCT exam_id AND score IS NOT NULL) AS complete_exam_cnt
FROM exam_record
题解2:
SELECT
COUNT(exam_id) AS total_pv,
COUNT(submit_time) AS complete_pv,
COUNT(DISTINCT IF(score IS NOT NULL,exam_id,NULL)) AS complete_exam_cnt
FROM exam_record
题解1:聚合函数求出平均值,作为表中的一个筛选条件
select min(t1.score) min_score_over_avg
from exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
where t2.tag = 'SQL'
and t1.score >= (
select avg(t1.score)
from exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
where t2.tag = 'SQL');
题解2:用窗口函数直接求出avg,作为一列。然后score与avg比较
select min(t.score) min_score_over_avg
from (select t1.score,avg(t1.score) over() avg_score
from exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
where t2.tag = 'SQL' and t1.score is not null) as t
where t.score >= t.avg_score;
本题陷阱在于九月份有个用户同一天做了两种卷子,直接count统计活跃天数会多一天,即用户ID和做题日期submit_time要同时去重才能得出正确的活跃天数.
题解:
select
date_format(start_time,"%Y%m") as month,
round(count(distinct uid,date_format(submit_time,'%Y%m%d'))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where year(submit_time) = 2021
group by month
last_day()函数:获取对应月的最后一天的日期
day()函数:取出天数
因为有多个 submit_time,所以再 avg() 取个值
题解:
select
date_format(submit_time,"%Y%m") as submit_month,
count(date_format(submit_time,"%Y%m")) as month_q_cnt,
round(count(submit_time)/avg(day(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
group by DATE_FORMAT(submit_time, "%Y%m")
union ALL
SELECT '2021汇总' as submit_month,
count(question_id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
order by submit_month;
concat_ws(':', date(start_time), tag)
group_concat(distinct concat_ws(':', date(start_time), tag) separator ';')
题解:
select
uid,
(count(start_time) - count(submit_time)) as incomplete_cnt,
count(submit_time) as complete_cnt,
group_concat(distinct concat(date(start_time),':',tag) order by start_time asc separator ';') as detail
from exam_record as t1
left join examination_info as t2
on t1.exam_id = t2.exam_id
where year(start_time) = 2021
group by t1.uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1
order by incomplete_cnt desc
group by uid
count(exam_id)
count(distinct DATE_FORMAT(start_time, "%Y%m"))
having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
题解:
select tag,count(tag) as tag_cnt
from exam_record as t1
inner join examination_info as t2
on t1.exam_id = t2.exam_id
where uid in (
select uid
from exam_record
where submit_time is not null
group by uid
having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc
题解:
select
t2.exam_id,
count(distinct t1.uid) as uv,
round(avg(score),1) as avg_score
from user_info as t1
inner join exam_record as t3 on t1.uid = t3.uid
inner join examination_info as t2 on t2.exam_id = t3.exam_id
where t1.level > 5 and t2.tag = "SQL" and date(submit_time) = date(release_time) and score is not null
GROUP BY exam_id
order by uv desc,avg_score
题解1:
select * from () t1 union select * from () t2
select level,count(level) as level_cnt
from exam_record as t3
inner join examination_info as t2
on t3.exam_id = t2.exam_id
inner join user_info as t1
on t1.uid = t3.uid
where tag = 'SQL' and score > 80
group by level
order by level_cnt desc
题解2:
select exam_id as tid,count(distinct er.uid) as uv,count(exam_id) as pv
from exam_record er
group by exam_id
union all
select question_id as tid,count(distinct pr.uid) as uv,count(question_id) as pv
from practice_record pr
group by question_id
order by left(tid,1) desc, uv desc,pv desc
题解:
select * from
(select exam_id as tid,
count(distinct uid) as uv,
count(*) as pv
from exam_record
group by exam_id
order by uv desc, pv desc) as t1
union
select * from
(select question_id as tid,
count(distinct uid) as uv,
count(*) as pv
from practice_record
group by question_id
order by uv desc, pv desc) as t2
暂停刷题,后面再接着刷。。。
题解:
题解
题解:
题解:
题解:
题解: