《SQL必知必会》笔记一：1-10课

〇、基础常识

0.1 大小写

SQL不区分大小写，但一般关键字使用大写，分行书写。主要可以增加美观和可读性。

 

一、简单查询语句

1.1 简单查询

SELECT ...,  ..., ...
FROM ...;

1.2 排序

ORDER BY ...

ORDER BY ... DESC, ...

默认是升序排列。如果需要按照某个键值降序排列，则在该键值后加上DESC。

1.3 过滤

WHERE  ... > 2
WHERE  ... BETWEEN 1 AND 2
WHERE  ... != 1

逻辑关系有：>、 >=、<、<=、==、!=。

可以再加上AND和OR来实现多组的过滤。

1.4 IN操作符

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name DESC;

实际上该功能也可以使用OR来完成。、

但是IN操作符的优点是：（1）IN操作符比一般OR操作符的执行速度更快

　　　　　　　　　　　（2）IN的最大优点是可以包含其他SELECT语句，能够更动态的建立WHERE子句。

1.5 NOT操作符

NOT否定其后所跟的任何条件。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'    /*不等于*/
ORDER BY prod_name;


SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

上述两个代码的作用是一样的。

 1.6 通配符与LIKE操作符

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

下面是几种通配符：%、_ 、[ ]、

 

%匹配多个字符，但下划线只匹配单个字符。

/*以Fish开头的词*/
SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';

WHERE prod_name LIKE '%bean bag%';

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '__ inch teddy bear';

/*找出所有名字以 J 或 M 起头的联系人*/
SELECT cust_contact
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;

/*找出所有名字不以 J 或 M 起头的联系人*/
SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[^JM]%' 
ORDER BY cust_contact;

 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

1.7 计算字段

拼接与别名

SELECT Concat(vend_name, ' (', vend_country, ')') 
FROM Vendors 
ORDER BY vend_name;
//输出如下所示
Bear Emporium (USA ) 
Bears R Us (USA ) 
Doll House Inc. (USA )

//使用别名
SELECT Concat(vend_name, ' (', vend_country, ')') 
       AS vend_title 
FROM Vendors 
ORDER BY vend_name;

算术计算

SELECT prod_id, 
 quantity, 
 item_price, 
 quantity*item_price AS expanded_price 
FROM OrderItems 
WHERE order_num = 20008;

//输出如下所示
prod_id quantity item_price expanded_price 
---------- ----------- ------------ ----------------- 
RGAN01 5 4.9900 24.9500 
BR03 5 11.9900 59.9500 
BNBG01 10 3.4900 34.9000 
BNBG02 10 3.4900 34.9000 
BNBG03 10 3.4900 34.9000

1.8 汇总数据

聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。

主要有：AVG()，返回某列的平均值；COUNT()，返回某列的行数

　　       MAX()，返回某列的最大值；MIN()，返回某列的最小值；

              SUM()，返回某列值之和

 

SELECT AVG(prod_price) AS avg_price 
FROM Products
WHERE vend_id = 'DLL01';
//输出
avg_price 
------------- 
6.823333

/*返回 Customers 表中顾客的总数*/
SELECT COUNT(*) AS num_cust 
FROM Customers;
/*只对具有电子邮件地址的客户计数*/
SELECT COUNT(cust_email) AS num_cust 
FROM Customers;

SELECT MAX(prod_price) AS max_price 
FROM Products;

SELECT MIN(prod_price) AS min_price 
FROM Products;

SELECT SUM(item_price*quantity) AS total_price 
FROM OrderItems 
WHERE order_num = 20005;

/*组合聚合函数*/
SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM Products 
WHERE vend_id = 'DLL01';

SELECT COUNT(*) AS num_items, 
 MIN(prod_price) AS price_min, 
 MAX(prod_price) AS price_max, 
 AVG(prod_price) AS price_avg 
FROM Products;

1.9 数据分组

GROUP BY 子句和 HAVING 子句。

GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

 

HAVING 非常类似于 WHERE。事实上，目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。

唯一的差别是，WHERE过滤行，而 HAVING 过滤分组。

/*按照供应商进行分组*/
SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id;

/*HAVING保留两个及以上订单的那些分组*/
SELECT cust_id, COUNT(*) AS orders 
FROM Orders 
GROUP BY cust_id 
HAVING COUNT(*) >= 2;

/*复杂组合*/
/*WHERE 子句过滤所有 prod_price 至少为 4 的行，然后按 vend_id
分组数据，HAVING 子句过滤计数为 2 或 2 以上的分组。*/
SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 
GROUP BY vend_id 
HAVING COUNT(*) >= 2;

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;