SQL 必知必会--notes-优快云博客

本文链接：https://blog.youkuaiyun.com/JasonRight/article/details/83648276

本文介绍了SQL查询的基础知识，包括按指定列排序、使用BETWEEN进行范围查询、理解AND与OR的运算优先级、利用通配符进行模糊匹配、去除字符串两端的空白字符以及聚合函数的应用等。同时，还探讨了LEFT OUTER JOIN与RIGHT OUTER JOIN在外连接选择上的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.按列位置排序

SELECT prod_id, prod_price,prod_name FROM products ORDER BY 2, 3;
    --2 refer to prod_price--
    --3 refer to prod_name--

2.BETWEEN 包含边界值

SELECT prod_price, prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;
    --包含5和10--

3.AND 优先于 or （不要使用默认优先级，始终使用括号）

SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id = 'DLL01' or vend_id = 'BRS01' AND prod_price > 10
    --相当于--
SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id = 'DLL01' or (vend_id = 'BRS01' AND prod_price > 10)

4.通配符'%'匹配0个,1个和多个字符， '_'匹配1个字符

5.TRIM 去除两边的空格， LTRIM 去除左边的空格， RTRIM 去除右边的空格--TODO:补充所有的文本操作函数，见8.2.1

6.AVG(columA) , MAX(columnA), MIN(columnA), SUM(columnA), COUNT(columnA) 忽略 NULL 值， COUNT(*)不忽略

7.LEFT OUTER JOIN 选择左边表所有的行 *=

SELECT c.cust_id, o.order_num FROM customers AS c 
		LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id;

------------------------------
--cust_id order_num
--"1000000001";20009
--"1000000001";20005
--"1000000002";
--"1000000003";20006
--"1000000004";20007
--"1000000005";20008--
------------------------------
RIGHT OUTER JOIN 选择右边表所有的行 =*

SELECT c.cust_id, o.order_num FROM customers AS c 
		RIGHT OUTER JOIN orders AS o ON c.cust_id = o.cust_id;

------------------------------
--cust_id order_num
--"1000000001";20005
--"1000000003";20006
--"1000000004";20007
--"1000000005";20008
--"1000000001";20009
------------------------------