SQL必知必会要点记录（2/6）

SQL必知必会要点记录（2/6）

*字段拼接：+或者|| 或者,
SELECT vend_name + '(' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;
或者
SELECT vend_name || '(' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;
或者
SELECT vend_name , '(' , vend_country , ')'
FROM Vendors
ORDER BY vend_name;

*去掉空格SQL：RTRIM()函数：去掉值右侧的所有空格，类似的还有LTRIM()：去掉值左侧的所有空格，TRIM()：去掉值左右两侧的所有空格
SELECT RTRIM(vend_name) +'('+RTRIM(vend_country)+')'
FROM Vendors
ORDER BY vend_name;

*别名AS 某某
SELECT RTRIM(vend_name) +'('+RTRIM(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 = 10008;

*函数
UPPER(vender_name) // LOWER(vender_name)

*SOUNDEX():若Michael Green 非数据库输入格式，这将与数据库输入名字Michelle Green差之毫厘，实在可惜，这个时候可以用SOUNDEX()函数
SELECT cust_name , cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';

SELECT cust_name , cust_contact
FROM Customers
WHERE cust_contact = SOUNDEX('Michael Green') ;

*日期与时间
SELECT  order_num
FROM Orders
WHERE DATEPART(yy,order_date)=2012; -- OR WHERE DATEPART('year',order_date)=2012; OR WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012'); OR WHERE YEAR(order_date) = 2012 ; OR WHERE strftime('%Y' , order_date) = '2012';

*平方根：SQRT();AVG()：平均值

*返回Customers表中顾客总数
SELECT COUNT(*) AS num_cust
FROM Customers;

*删选有电子邮件的顾客总数
SELECT COUNT(cust_email) AS num_cust
FROM Customers;

*平均数的不同测算方式得到不同结果
1.
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Customers
WHERE vend_id = '1001';
--测算排除相同值价格的平均数，例如一个编号为1001的超市数据集中有2、2、3、4、5、5、5标价的不同商品，上式测算2、3、4、5的平均值
2.
SELECT AVG(prod_price) AS avg_price
FROM Customers
WHERE vend_id = '1001';
--测算不排除相同值价格的平均数，例如一个编号为1001的超市数据集中有2、2、3、4、5、5、5标价的不同商品，测算2、2、3、4、5、5、5的平均值

*组合聚集函数
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;

*数据分组：GROUP BY子句与HAVING子句
SELECT vend_id , COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
ORDER BY num_prods;

*Having：筛选分组后的数据