MySQL学习（三）

-- 计算字段
-- 拼接字段
SELECT CONCAT(vend_name, ' (',vend_country,')')
FROM Vendors
ORDER BY vend_name;

SELECT CONCAT(vend_name,vend_country)
FROM Vendors
ORDER BY vend_name;

-- CONCAT(str1,str2,...) 拼接查询的值
SELECT CONCAT(vend_name,vend_country)
FROM Vendors
ORDER BY vend_name;


-- RTRIM(str) 去掉值右边的所有空格  LTRIM(str) 去掉值左边的所有空格
SELECT RTRIM(vend_name),RTRIM(vend_country)
FROM Vendors
ORDER BY vend_name;

SELECT RTRIM(vend_name)
FROM Vendors


-- 使用别名 AS
SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')')
AS vend_title
FROM Vendors
ORDER BY vend_name;


-- 执行算术运算
SELECT prod_id,quantity,item_price
FROM OrderItems
WHERE order_num = 20008;

-- 价格汇总
SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;


-- 文本处理函数
/*
常用文本处理函数
`LEFT`(str,len)        返回字符串左边的字符
LENGTH(str)                返回字符串的长度
LOWER(str)                将字符串转换为小写
LTRIM(str)                去掉字符串左边的空格
`RIGHT`(str,len)  返回字符串右边的字符
RTRIM(str)                去掉字符串右边的空格
SOUNDEX(str)            返回字符串的soundex值
UPPER(str)                将字符串转换为大写

*/
-- UPPER(str) 将所有字符转换为大写
SELECT vend_name,UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

SELECT LEFT(vend_name,1),vend_name
FROM Vendors
ORDER BY vend_name;


-- SOUNDEX(str) 返回读音相近的结果
SELECT cust_name,cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';
-- 返回空的结果

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


-- 日期与时间处理函数
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;


-- 数值处理函数
/*
ABS()  返回一个数的绝对值
COS()  返回一个角度的余弦
EXP()  返回一个数的指数值
PI()   返回圆周率
SIN()  返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN()  返回一个角度的正切
*/