赞
踩
sql语句执行顺序:1、最先执行from table;2、where语句是对条件加以限定;3、分组语句【group by…… having】;4、聚合函数;5、select语句;6、order by排序语句。
如果一个国家满足下述两个条件之一,则认为该国是 大国 :
面积至少为 300 万平方公里(即,3000000 km2),或者
人口至少为 2500 万(即 25000000)
编写一个 SQL 查询以报告 大国 的国家名称、人口和面积。
示例: 输入: World 表: +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+ 输出: +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/big-countries
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
代码:(or和union的区别)
SELECT name, population, area FROM world WHERE area >= 3000000 UNION SELECT name, population, area FROM world WHERE population >= 25000000 ;
使用 or 会使索引会失效,在数据量较大的时候查找效率较低,通常建议使用 union 代替 or。
这里看到有个评论提到了or和union的区别,我去stackoverflow上面查了一下,具体链接放在末尾。大致意思是: 对于单列来说,用or是没有任何问题的,但是or涉及到多个列的时候,每次select只能选取一个index,如果选择了area,population就需要进行table-scan,即全部扫描一遍,但是使用union就可以解决这个问题,分别使用area和population上面的index进行查询。 但是这里还会有一个问题就是,UNION会对结果进行排序去重,可能会降低一些performance(这有可能是方法一比方法二快的原因),所以最佳的选择应该是两种方法都进行尝试比较。 (stackoverflow链接: https://stackoverflow.com/questions/13750475/sql-performance-union-vs-or)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
**注释:**默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
**注释:**UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都 不是 2。
SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;
SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;
MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身!这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。
因此,在 WHERE 语句中我们需要做一个额外的条件判断 `referee_id IS NULL’。
作者:LeetCode
链接:https://leetcode.cn/problems/find-customer-referee/solution/xun-zhao-yong-hu-tui-jian-ren-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
写出一个SQL 查询语句,计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头,那么他的奖金是他工资的100%,否则奖金为0。
Return the result table ordered by employee_id.
返回的结果集请按照employee_id排序。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/calculate-special-bonus
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
代码:
# Write your MySQL query statement below
SELECT
employee_id,
(CASE WHEN employee_id%2 != 0 AND name NOT LIKE 'M%' THEN salary
WHEN employee_id%2 = 0 THEN 0
ELSE 0 END) AS 'bonus'
FROM Employees
ORDER BY employee_id;
# Write your MySQL query statement below
UPDATE salary
SET sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
UPDATE Salary SET sex = char(ascii('m') + ascii('f') - ascii(sex))
ASCII()
函数接受字符表达式并返回字符表达式最左侧字符的ASCII代码值。
以下是ASCII()
函数的语法:
ASCII ( input_string )
input_string
可以是文字字符,字符串表达式或列。 如果input_string
有多个字符,则该函数返回其最左侧字符的ASCII代码值。注:ASCII代表美国信息交换标准代码,ASCII用作现代计算机的字符编码标准。
以下示例返回字符A
和Z
的ASCII代码值:
SELECT
ASCII('AB') A,
ASCII('Z') Z;
执行上面查询语句,得到以下结果:
A Z
----------- -----------
65 90
(1 row affected)
//更多请阅读:https://www.yiibai.com/sqlserver/sql-server-ascii-function.html
CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
等同于,使用CASE WHEN条件表达式函数实现:
CASE WHEN SCORE = 'A' THEN '优'
WHEN SCORE = 'B' THEN '良'
WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
————————————————
版权声明:本文为CSDN博主「涛声依旧叭」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/rongtaoup/article/details/82183743
编写一个 SQL 查询来修复名字,使得只有第一个字符是大写的,其余都是小写的。
返回按 user_id
排序的结果表。
查询结果格式示例如下。
SQL函数:https://www.runoob.com/mysql/mysql-functions.html
注意:有一些默认值。
# Write your MySQL query statement below
SELECT
user_id,CONCAT(UPPER(LEFT(name,1)),LOWER(SUBSTRING(name,2))) name
FROM Users
ORDER BY user_id;
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/fix-names-in-a-table
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
示例 1: 输入: Activities 表: +------------+-------------+ | sell_date | product | +------------+-------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+-------------+ 输出: +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+
解释:
对于2020-05-30,出售的物品是 (Headphone, Basketball, T-shirt),按词典序排列,并用逗号 ‘,’ 分隔。
对于2020-06-01,出售的物品是 (Pencil, Bible),按词典序排列,并用逗号分隔。
对于2020-06-02,出售的物品是 (Mask),只需返回该物品名。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/group-sold-products-by-the-date
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
解题思路
produts字段那里需要拼接多个产品product,自然会想到用concat函数来解决
用到concat函数的衍生:group_concat函数
代码:
# Write your MySQL query statement below
SELECT sell_date,
COUNT(DISTINCT product) AS num_sold,
GROUP_CONCAT(DISTINCT product
ORDER BY product ASC
SEPARATOR ',') AS products
FROM Activities
GROUP BY sell_date;
在许多情况下,您可以应用GROUP_CONCAT
函数来产生有用的结果。 以下列表是使用GROUP_CONCAT
函数的一些常见示例。
//更多请阅读:https://www.yiibai.com/mysql/group_concat.html
写一条 SQL 语句,查询患有 I 类糖尿病的患者 ID (patient_id)、患者姓名(patient_name)以及其患有的所有疾病代码(conditions)。I 类糖尿病的代码总是包含前缀 DIAB1 。
示例 1: 输入: Patients表: +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 1 | Daniel | YFEV COUGH | | 2 | Alice | | | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | | 5 | Alain | DIAB201 | +------------+--------------+--------------+ 输出: +------------+--------------+--------------+ | patient_id | patient_name | conditions | +------------+--------------+--------------+ | 3 | Bob | DIAB100 MYOP | | 4 | George | ACNE DIAB100 | +------------+--------------+--------------+ 解释:Bob 和 George 都患有代码以 DIAB1 开头的疾病。 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/patients-with-a-condition 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/patients-with-a-condition
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
# Write your MySQL query statement below
select
*
from
patients
where
conditions rlike '^DIAB1|.*\\sDIAB1';
# rlike 后面跟着的 字符串可以使用正则匹配、
作者:100000000000001
链接:https://leetcode.cn/problems/patients-with-a-condition/solution/by-jam007-5jd4/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
写出一个查询语句,找到所有 丢失信息 的雇员id。当满足下面一个条件时,就被认为是雇员的信息丢失:
雇员的 姓名 丢失了,或者
雇员的 薪水信息 丢失了,或者
返回这些雇员的id employee_id , 从小到大排序 。
查询结果格式如下面的例子所示。
思路:
代码:
# Write your MySQL query statement below
SELECT employee_id
FROM
(SELECT employee_id FROM Employees
UNION ALL
SELECT employee_id FROM Salaries)
AS t
GROUP BY employee_id
having count(employee_id) = 1
ORDER BY employee_id;
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/employees-with-missing-information
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
请你重构 Products 表,查询每个产品在不同商店的价格,使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售,则不输出这一行。
输出结果表中的 顺序不作要求 。
查询输出格式请参考下面示例。
输入: Products table: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 0 | 95 | 100 | 105 | | 1 | 70 | null | 80 | +------------+--------+--------+--------+ 输出: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 0 | store1 | 95 | | 0 | store2 | 100 | | 0 | store3 | 105 | | 1 | store1 | 70 | | 1 | store3 | 80 | +------------+--------+-------+ 解释: 产品0在store1,store2,store3的价格分别为95,100,105。 产品1在store1,store3的价格分别为70,80。在store2无法买到。
代码:
# Write your MySQL query statement below
SELECT product_id,'store1' AS store,store1 AS price
FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id,'store2' AS store,store2 AS price
FROM Products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id,'store3' AS store,store3 AS price
FROM Products WHERE store3 IS NOT NULL
列转行:
将表1的数据列转换成表2的行:
SELECT name,
MAX(CASE WHEN subject='语文' THEN score ELSE 0 END) AS '语文',
MAX(CASE WHEN subject='数学' THEN score ELSE 0 END) AS "数学",
MAX(CASE WHEN subject='英语' THEN score ELSE 0 END) AS "英语"
FROM student1
GROUP BY name
作者:lyon-bryant
链接:https://leetcode.cn/problems/rearrange-products-table/solution/zong-jie-xing-zhuan-lie-lie-zhuan-xing-b-g2je/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
运行过程:From student1 选择表,然后group by name 变成只有小明大花的两行表,然后使用case when进行选择,符合为分数,不符合则是0,最后用max进行选择出不为0的成绩即可;
将表2的数据行转换成表1的列:
SELECT NAME,'语文' AS subject,语文 AS score
FROM student2 WHERE '语文' IS NOT NULL
UNION
SELECT NAME,'数学' AS subject,数学 AS score
FROM student2 WHERE '数学' IS NOT NULL
UNION
SELECT NAME,'英语' AS subject,英语 AS score
FROM student2 WHERE '英语' IS NOT NULL
作者:lyon-bryant
链接:https://leetcode.cn/problems/rearrange-products-table/solution/zong-jie-xing-zhuan-lie-lie-zhuan-xing-b-g2je/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
运行过程:从student2表中选出不为空的值,然后选择名字,将subject这一列的值设置为‘语文’(一个值),将语文(一个列名)这一列的值,设置为新列的值。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/rearrange-products-table
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
为什么不能够select * from Table group by id,为什么一定不能是*,而是某一个列或者某个列的聚合函数
如果执行select *的话,那么返回的结果应该是虚拟表3,可是id和number中有的单元格里面的内容是多个值的,而关系数据库就是基于关系的,单元格中是不允许有多个值的,所以你看,执行select * 语句就报错了。
(2)我们再看name列,每个单元格只有一个数据,所以我们select name的话,就没有问题了。为什么name列每个单元格只有一个值呢,因为我们就是用name列来group by的。
(3)那么对于id和number里面的单元格有多个数据的情况怎么办呢?答案就是用**聚合函数,聚合函数就用来输入多个数据,输出一个数据的。**如cout(id),sum(number),而每个聚合函数的输入就是每一个多数据的单元格。
# Write your MySQL query statement below
SELECT customer_number
FROM Orders
GROUP BY customer_number
ORDER BY count(order_number) DESC
LIMIT 1;
编写一个 SQL 查询,获取并返回 Employee
表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null
查询结果如下例所示。
思路:
代码1:
SELECT IFNULL(
(SELECT MAX(DISTINCT Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee))
,null) AS SecondHighestSalary;
注意:要将第二个嵌套的SELECT语句用括号括起来。
代码2:
将不同的薪资按降序排序,然后使用 LIMIT
子句获得第二高的薪资。
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary;
作者:LeetCode
链接:https://leetcode.cn/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
参考:https://www.yiibai.com/sql/sql-limit.html
要检索查询返回的行的一部分,请使用LIMIT
和OFFSET
子句。
SELECT
column_list
FROM
table1
ORDER BY column_list
LIMIT row_count OFFSET offset;
在这个语法中,
row_count
确定将返回的行数。OFFSET
子句在开始返回行之前跳过偏移行。 OFFSET
子句是可选的。 如果同时使用LIMIT
和OFFSET
子句,OFFSET
会在LIMIT
约束行数之前先跳过偏移行。在使用LIMIT
子句时,使用ORDER BY
子句确保返回的行按指定顺序非常重要。
如果要只返回前5
行,请使用LIMIT
子句,如以下语句。
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name
LIMIT 5;
要跳过两行并获取接下来的五行,请使用LIMIT
和OFFSET
子句,如以下语句所示。
SELECT
employee_id, first_name, last_name
FROM
employees
ORDER BY first_name
LIMIT 5 OFFSET 3;
MySQL IFNULL
函数是MySQL控制流函数之一,它接受两个参数,如果不是NULL
,则返回第一个参数。 否则,IFNULL
函数返回第二个参数。
两个参数可以是文字值或表达式。
以下说明了IFNULL
函数的语法:
IFNULL(expression_1,expression_2);
SQL
如果expression_1
不为NULL
,则IFNULL
函数返回expression_1
; 否则返回expression_2
的结果。
//更多请阅读:https://www.yiibai.com/mysql/ifnull.html
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id
。
返回结果 不要求顺序 。
代码:
SELECT w2.id
FROM Weather w1 JOIN Weather w2
ON w1.Temperature < w2.Temperature
AND DATEDIFF(w2.recordDate,w1.recordDate) = 1;
注:join跟inner join 一样
MySQL DATEDIFF
函数计算两个DATE,DATETIME或TIMESTAMP值之间的天数。
MySQL DATEDIFF
函数的语法如下:
DATEDIFF(date_expression_1,date_expression_2);
mysql> SELECT DATEDIFF('2017-08-17','2017-08-08');
+-------------------------------------+
| DATEDIFF('2017-08-17','2017-08-08') |
+-------------------------------------+
| 9 |
+-------------------------------------+
更多请阅读:https://www.yiibai.com/mysql/datediff.html
编写一个 SQL 查询,该查询可以获取在 2020
年登录过的所有用户的本年度 最后一次 登录时间。结果集 不 包含 2020
年没有登录过的用户。
代码:
# Write your MySQL query statement below
SELECT user_id,
MAX(time_stamp) AS last_stamp
FROM Logins
WHERE time_stamp LIKE '2020%'
# year(time_stamp) = 2020
GROUP BY user_id;
巧妙点:用 LIKE '2020%'来表示2020年,正常则用year( )来表示
编写一个SQL查询以计算每位员工每天在办公室花费的总时间(以分钟为单位)。 请注意,在一天之内,同一员工是可以多次进入和离开办公室的。 在办公室里一次进出所花费的时间为out_time 减去 in_time。
# Write your MySQL query statement below
SELECT event_day AS day,
emp_id,
SUM(out_time)-SUM(in_time) AS total_time
FROM Employees
GROUP BY emp_id,event_day;
注释:在select语句中进行sum加减
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/find-total-time-spent-by-each-employee
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。
思路1:自连接,id不同;
代码:groupby后进行count>1;顺序:where>group by>having>order by
# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Id) > 1;
编写一个SQL查询,报告2019年春季
才售出的产品。即仅在**2019-01-01**
至**2019-03-31**
(含)之间出售的商品。
代码:理解题目意思:只有在春季才出售,别的季节不卖;
注意:限制时间大小可以用MAX和MIN (时间是字符串,要加引号)
# Write your MySQL query statement below
SELECT p.product_id,p.product_name
FROM Product p INNER JOIN Sales s
ON p.product_id = s.product_id
GROUP BY s.product_id
HAVING MAX(sale_date) <= '2019-03-31' AND MIN(sale_date) >= '2019-01-01';
写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单,以 travelled_distance 降序排列 ,如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
# Write your MySQL query statement below
SELECT u.name,IFNULL(SUM(distance),0) AS travelled_distance
FROM Users u LEFT JOIN Rides r
ON u.id = r.user_id
GROUP BY r.user_id
ORDER BY SUM(distance) DESC,u.name ASC;
注意:所有的用户都要显示出来,没有的则返回0;
对于SUM()函数:如果对多行进行sum,如果有一行为null,则不加入计算,如果group by 中没有这个字段,则返回null。
来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/top-travellers
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
编写一个SQL查询来报告每支股票的资本损益。
股票的资本损益是一次或多次买卖股票后的全部收益或损失。
代码:
# Write your MySQL query statement below
SELECT stock_name,
SUM(CASE WHEN operation = 'Buy' THEN -price ELSE price END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name;
注意:对CASE WHEN得到的结果进行SUM,a AS b是指用a的值来放在b这个新的列;
请写出一条SQL语句以查询每个用户的注册日期和在 2019
年作为买家的订单总数。
以 任意顺序 返回结果表。
# Write your MySQL query statement below
SELECT u.user_id AS buyer_id ,u.join_date,
IFNULL(t.num,0) AS orders_in_2019
FROM Users u LEFT JOIN (
SELECT o.buyer_id,count(o.order_id) AS num
FROM Orders o
WHERE year(order_date) = '2019'
GROUP BY buyer_id
) AS t
ON u.user_id = t.buyer_id
注意:先把2019年的进行分组筛选作为临时表和users进行LEFT JOIN;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。