数据库系统操作备忘（一）

• 选择需要的列

SELECT prod_name FROM products;
SELECT prod_id,prod_name,prod_price FROM products;

• 按顺序排列

SELECT prod_id,prod_name,prod_price
FROM products
ORDER BY prod_name,prod_price

以上，默认升序，先按名字排列，相同的再按价格排列
若要降序，则需：

ORDER BY prod_name DESC,prod_price

• WHERE条件筛选

WHERE prod_price <= 10 ;
WHERE vend_id <> 'DLL01'; //不匹配检查

WHERE vend_id = 'DLL01' AND prod_price <= 10 ; //逻辑AND
WHERE vend_id = 'DLL01' OR  vend_id = 'BRS01'; //逻辑OR

要注意的是，同时使用WHERE与ORDER BY时，应将WHERE置于之前。
并且AND与OR联合使用时，AND优先级要高于OR。使用时注意加括号。

• IN操作符用于指定条件范围清单

WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;

• 通配符LIKE（只能用于文本字串）

  1. 百分号%，表示任何字符出现任意次数
  2. 下划线_ ，用途与%一样，但是只表示单个字符
  3. 方括号[]，用来指定一个字符集，它必须匹配指定位置的单个字符

WHERE prod_name LIKE 'fish%'; //以fish开头的所有项
WHERE prod_name LIKE '%bean bag%'; //之中含有bean bag的所有项
WHERE prod_name LIKE '__ inch teddy bear';//匹配两个关键字符
WHERE cust_contact LIKE '[JM]%';//名字以J或M开头的联系人

要注意的是，通配符搜索时间代价比较大，不要过分使用，不要把其置于搜索模式的开始处，要特别注意通配符位置放置的正确性。

• 计算字段 ：用于对数据库中的元素进行特定的操作

  1. 拼接操作， + 或者 || 。RTRIM(vend_name) ，此方法可以去除输出串右边的空格
  2. 赋予别名，AS vend_title。
  3. 算术运算，quantity * item_ price AS expanded_price

SELECT vend_name || '(' || vend_country || ')' FROM…… //拼接格式为：供应商名字(国家)
SELECT CONACT ( vend_name || '(' || vend_country || ')' ) //MYSQL专用
AS vend_title //加在串格式之后，可以对新生成的串起一个别名，方便程序调用

• 函数

  1. 文本处理函数。LEFT() ; LENGTH() ; LOWER() ; LTRIM() ; RIGHT() ; RTRIM() ; SOUNDEX() ; UPPER()
  2. 日期和时间处理函数
  3. 数值处理函数。ABS() ; COS() ; EXP() ; PI() ; SIN() ; SQRT() ; TAN()

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase //将vend_name字串转为大写
WHERE SOUNDEX(cust_contact) = SOUNDEX("Michael Green") //寻找发音类似"Michael Green"的项，预防因数据库录入错误而导致搜索为空

WHERE DATEPART('yyyy',order_date) = 2004 ;//DATEPART(返回的成分，过滤项)，此函数返回日期的一部分。
WHERE YEAR(order_date) = 2004 ;//MYSQL里使用的函数
WHERE order_date BETWEEN to_date('01-JAN-2004') AND to_date('31-DEC-2004') ; // Oracle 里 将字符转转换为日期 

• 汇总数据（确定满足特定条件的行数、获得表中行组的和、找出表列最大最小及均值）

  1. AVG() ， 返回某列平均值
  2. COUNT() ，返回某列行数，COUNT(*)返回列所有计数，包括NULL。COUNT(colum)则忽略NULL。
  3. MAX()，返回某列最大值
  4. MIN()，返回某列最小值

  5. SUM()，返回某列之和

  6. DISTINCT 参数可用在()里参数的前边，例如DISTINCT prod_price，这样它筛选价格只会取不同值，相同的值会被过滤只留下一个

  7. 以上函数均可组合使用，每使用一次就用AS指定一个别名。

SELECT AVG(prod_price) AS avg_price //可加WHERE筛选条件，若要多个列，必须使用多个AVG()
SELECT COUNT( * ) AS num_cust FROM Customers; //返回Customers的所有
SELECT COUNT(cust_email) AS num_cust FROM Customers; //只对拥有电子邮件地址的客户计数
SELECT MAX(prod_price) AS max_price //返回最值，MIN()函数同理，MIN()会自动忽略值为NULL的行
SELECT SUM(item_price * quantity) AS total_price FROM orderItems WHERE order_num = 2005; //统计2005年订单的总价

• 分组语句： GROUP BY
  GROUP BY语句必须出现在WHERE子句之后，ORDER BY子句之前。

SELECT vend_id,COUNT( * ) AS num_prods FROM Proudcts GROUP BY vend_id //按vend_id分类来统计数目

• HAVING，分组过滤
  WHERE过滤行，HAVING用于过滤分组。
  WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT cust_id,COUNT( * ) AS orders FROM Orders GROUP BY cust_id
HAVING COUNT( * ) >= 2   //过滤分组后，orders大于2 的顾客
ORDER BY orders  //可以添加ORDER BY 语句

• 总结SELECT子句出现的顺序：

• 子查询

  1. 可以进行SELECT的嵌套，将里层的SELECT的结果，作为外层SELECT的筛选条件，用在WHERE···IN （SELECT ···） 中。
  2. 用子查询语句来筛选统计在两张表里的信息。例如，要统计客户信息以及每个客户的订单总数：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num 
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01')

SELECT cust_name,(SELECT COUNT(*)
                  FROM Orders
                  WHERE Orders.cust_id=Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;