https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298
💖牛客题目, SQL必知必会。不断熟悉,不断进步,加油!👍 💬 ⭐️
题目
题目比较基础就不解析了
简单查询
# 60. 查询 cust_id
SELECT cust_id FROM Customers
# 61. distinct 去重(必须放最前面, 表示后面参数拼接去重)
SELECT distinct prod_id FROM OrderItems
# 62. 查询所有列 *
SELECT * FROM Customers
排序
# 63. 对检索结果排序 orderby 字段 DESC/ASC
SELECT cust_name FROM Customers
ORDER BY cust_name DESC
# 64. 对两个字段进行排序 (多个字段排序,逗号隔开即可)
SELECT cust_id, order_num FROM Orders
ORDER BY cust_id ASC, order_date DESC
# 65. 对两个字段排序
SELECT quantity, item_price FROM OrderItems
ORDER BY quantity DESC, item_price DESC
# 66. 检查SQL, 注意逗号和关键字等书写是否正确
SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC;
过滤
# 67. Where 过滤数据
SELECT prod_id, prod_name FROM Products
WHERE prod_price = 9.49
# 67. Where 过滤数据, 返回某种价格的商品
SELECT prod_id, prod_name FROM Products
WHERE prod_price = 9.49
# 68. Where 过滤数据 返回价格更高的商品
SELECT prod_id, prod_name FROM Products
WHERE prod_price >= 9
# 69. where过滤 + 排序, BETWEEN a AND b 区间 左右ab都包含
SELECT prod_name, prod_price FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price
# 70. 分组 + 过滤 ,检索出所有总量大于100的订单 分组查询后,用HAVING聚合计算后过滤
SELECT order_num FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) > 100
# 71. 多个条件用 AND 拼接
SELECT DISTINCT vend_name FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
# 72. in() 在这些数目范围内, in() 相当于用or = 比较 not in() 相当于用 and != 比较。 且 != null 和 == null 永远为 false
SELECT order_num, prod_id, quantity FROM OrderItems
WHERE quantity >= 100 AND prod_id in('BR01', 'BR02', 'BR03')
# 73. AND 拼接条件 + 排序
SELECT prod_name, prod_price FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price ASC
# 74. SQL 纠错, 注意各个关键字的位置 和 标点符号
SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name
知识点
DISTINCT
distinct 用法 https://blog.csdn.net/shenziheng1/article/details/102536146
WHERE 和 HAVING 的区别?
WHERE | HAVING |
---|---|
过滤数据行的 | 过滤数据分组的 |
不可以用聚合函数 | 可以用如SUM(), MAX()等聚合函数 |
分组之前进行过滤的 | 分组之后进行过滤的 |
针对数据库文件过滤 | 针对查询结果过滤 |
不可以使用字段别名 | 可以使用字段别名 |
in() 和 not in() 中有 null 的坑
SQL避坑:当in,not in遇上null这种坑你避过吗?https://blog.csdn.net/godlovedaniel/article/details/125376244
in() 相当于用 or = 比较
not in() 相当于用 and != 比较。
且 != null 和 == null 永远为 false
以此就可以发现 not in() 如果里面有 null 的话, 一定返回false, 所有条件都不满足