【SQL自学】SQL函数

转换大写

• UPPER()
• UCASE()

//UPPER
SELECT UPPER(CustomerName) AS UppercaseCustomerName
FROM Customers;
//UCASE
SELECT UCASE(CustomerName) AS UppercaseCustomerName
FROM Customers;

切割字符串

SUBSTRING

SUBSTRING(string, start, length)
SELECT SUBSTRING(CustomerName, 2, 5) AS ExtractString
FROM Customers;

字符串拼接

CONCAT(expression1, expression2, expression3,...)
SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address
FROM Customers;

---

应用题

cust_id cust_name cust_contact cust_city

【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

SELECT cust_id,cust_name,UCASE(CONCAT(LEFT(cust_contact,2),LEFT(cust_city,3))) AS user_login FROM Customers 

LEFT():从左到右截取字符

返回固定日期的记录

SELECT order_num,order_date FROM Orders WHERE SUBSTRING(order_date,1,7) = '2020-01' ORDER BY order_date ASC
、、