SQL查询语句

SQL查询语句

• 创建计算字段
  SELECT vender_name || ' (' || vender_country || ')'

• MySQL上 创建计算字段

  SELECT Contact(vender_name,' (',vender_country,')') From table order by name

• 去除两边的空格。
  SELECT RTRIM(vender_name) + '(' + RTRIM(vender_country) + ')'

• 为创建的计算字段起别名
  SELECT Contact(vender_name,' (',vender_country,')') AS vender_title From table order by name

• 执行算数计算
  SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price From OrderItems WHERE order_num = 20008

文本处理函数

• UPPER
  SELECT vender_name ,UPPER(vender_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name
• LEFT 返回字符串左边的字符
• LENGTH() 返回字符串的长度
• LOWER() 将字符串变为小写
• LIRIM() 去掉字符串左边的空格
• RIRIM() 去掉字符串右面的空格
• RIGHT() 返回字符串右面的字符
• SOUNDEX()返回字符串的SOUNDEX值

汇总数据

• AVG 求平均值
  SELECT AVG(price) AS avg_price FROM table
• COUNT(*) 对表中行进行计数。无论是列中是否包含空值。COUNT(column)对特定的列中具有值的行进行统计
• MAX() 返回指定列中的最大值。如果是文本则返回排序后的最后一行
• MIN()作用和上面完全相反。返回最大值的行。 文本的话返回排序后的第一行。
• SUM(item_price * quantity) 返回订单中所有物品价钱之和。
• SELECT COUNT(DISTINCT price ) AS item_price FROM Table WHERE id = 10.过滤不同值。
• 组合使用
  SELECT COUNT(*) AS num_items MIN(prod_price) AS price_min,MAX(price_max) AS price_max,AVG(prod_price) AS price_avg FROM TABLE

创建分组

• GROUP BY创建分组

     SELECT vend_id ,COUNT(*) AS num_prods FROM Products Group By vend_id

• GROUP By子句必须出现在WHERE 之后，ORDER BY之前。
• HAVING 可以替代所有的WHERE 只不过HAVING过滤的是分组，WHERE过滤的是行。
• HAVING和WHERE 结合用。

      SELECT vend_id,COUNT(*) AS num_prods FROM table WHERE price>4 GROUP by vend_id HAVING COUNT(*)>2

• ORDER By 和GROUP BY结合来使用

      SELECT order_numer,COUNT(*) AS item_count FROM table GROup By id HAVing COUNT(*)>2 ORDER by item_count

子查询

• 我们先查阅包含RANG01的订单号
  SELECT order_id FROM goods WHERE goods_name = 'RANG01'
• 根据订单id查询所有user_id
  SELECT user_id FROM oders WHERE order_id in ('1001','1002')
• 根据user_id获取用户的信息
  SELECT user_name ,user_age FROM users WHERE user_id in ('1333','1332')
• 主查询嵌套子查询

      SELECT user_name ,user_age FROM users WHERE user_id in (SELECT user_id FROM oders WHERE order_id in (SELECT order_id FROM goods WHERE goods_name = 'RANG01'))

查询语句可以嵌套，作为子查询的SELECT 语句只能查询单独的列，企图索引多列将返回错误。

• 计算字段使用子查询

      SELECT name,age,(COUNT(*)FROM wn_table WHERE wn_table.order_id = ll_table.order_id) AS ids FROM wn_table ORDER BY cust_name

• 上面的复杂的子查询还可以通过内连结的方式

  SELECT cust_name,cust_contact FROM
  Customers,Orders,OrderItems WHERE Customers.cust_id =  
  Orders.cust_id And OrderItems.order_num =   Order.order_num               AND prod_id = 'RANG01';