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';
