SQL必知必会

检索数据

SELECT prod_name FROM Products;
#检索多列数据
SELECT prod_id,prod_name,prod_price FROM Products;
#检索所有列
SELECT * FROM Products;
#DISTINCT关键字，返回不同的值，去除相同的值
SELECT DISTINCT vend_id FROM Products;
#LIMIT关键字限制返回的数据行数
SELECT prod_name FROM Products LIMIT 5;
#指定从哪儿开始检索已经检索的函数 LIMIT 4 OFFSET 3 表示从第3行开始检索4行数据
SELECT prod_name FROM Products LIMIT 4 OFFSET 3;

排序检索数据

SELECT prod_name FROM Products ORDER BY prod_name;
#返回多列进行排序，首先按照价格排序，然后按照名称
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name;
#按照检索数据的第二列prod_price和第三列prid_name进行排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
#DESC关键字以降序来排序检索数据，ASC为升序，默认为升序。
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;

过滤数据

SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;
#检索prod_price小于10的数据
SELECT prod_name,prod_price FROM Products WHERE prod_price <10;
#检索vend_id不等于DLL01的数据
SELECT vend_id,prod_name FROM Products WHERE vend_id != 'DLL01';
#BETWEEN关键字范围值检索
SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
#检索空值
SELECT cust_name FROM Customers WHERE cust_email IS NULL;

高级数据过滤

SELECT prod_name,prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
#由于AND关键字在求值过程中优先级更高，操作符被错误组合
SELECT prod_name,prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price ####= 10;
#圆括号的优先级高于AND和OR，所以先过滤括号内的OR条件
SELECT prod_name,prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price ####= 10;
SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

用通配符进行过滤

SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE "Fish%";
#检索prod_name包含bean bag的数据
SELECT prod_id,prod_name FROM Products 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;