字符串函数

1SELECT CONCAT(string1, string2, string3,...) FROM TableName;

1-- Customers 表定义
2
3-- CREATE TABLE Customers (
4--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
5--     CustomerName VARCHAR(50) NOT NULL,
6--     Email VARCHAR(50),
7--     Phone VARCHAR(15),
8--     Address VARCHAR(100)
9-- );
10-- ------------------------------------------------------------------------------
11SELECT CustomerName, Email, CONCAT(CustomerName, ' - ', Email) AS ContactInfo
12FROM Customers;

1SELECT SUBSTRING(string, start, length) FROM TableName;

1SELECT SUBSTRING(string, start) FROM TableName;

1-- Products 表定义
2
3-- CREATE TABLE Products (
4--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
5--     ProductName VARCHAR(50) NOT NULL UNIQUE,
6--     CategoryID INT,
7--     Price DECIMAL(10, 2) NOT NULL,
8--     Stock INT NOT NULL,
9--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
10-- );
11-- ------------------------------------------------------------------------------
12SELECT ProductName, SUBSTRING(ProductName, 1, 3) AS ShortCode
13FROM Products;

1SELECT REPLACE(string, from_string, new_string) FROM TableName;

1-- Customers 表定义
2
3-- CREATE TABLE Customers (
4--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
5--     CustomerName VARCHAR(50) NOT NULL,
6--     Email VARCHAR(50),
7--     Phone VARCHAR(15),
8--     Address VARCHAR(100)
9-- );
10-- ------------------------------------------------------------------------------
11SELECT CustomerName, Address, REPLACE(Address, 'Street', 'St') AS UpdatedAddress
12FROM Customers;

1SELECT CHAR_LENGTH(string) FROM TableName;

1-- Products 表定义
2
3-- CREATE TABLE Products (
4--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
5--     ProductName VARCHAR(50) NOT NULL UNIQUE,
6--     CategoryID INT,
7--     Price DECIMAL(10, 2) NOT NULL,
8--     Stock INT NOT NULL,
9--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
10-- );
11-- ------------------------------------------------------------------------------
12SELECT ProductName, CHAR_LENGTH(ProductName) AS NameLength
13FROM Products;

1SELECT UPPER(string) FROM TableName;

1SELECT LOWER(string) FROM TableName;

1-- Customers 表定义
2
3-- CREATE TABLE Customers (
4--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
5--     CustomerName VARCHAR(50) NOT NULL,
6--     Email VARCHAR(50),
7--     Phone VARCHAR(15),
8--     Address VARCHAR(100)
9-- );
10-- ------------------------------------------------------------------------------
11SELECT CustomerName, UPPER(CustomerName) AS NameInUpperCase
12FROM Customers;

1SELECT LOCATE(substring, string, start) FROM TableName;

1-- Customers 表定义
2
3-- CREATE TABLE Customers (
4--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
5--     CustomerName VARCHAR(50) NOT NULL,
6--     Email VARCHAR(50),
7--     Phone VARCHAR(15),
8--     Address VARCHAR(100)
9-- );
10-- ------------------------------------------------------------------------------
11SELECT CustomerName, LOCATE(' ', CustomerName) 
12FROM Customers;

1-- Categories 表定义
2-- CREATE TABLE Categories (
3--     CategoryID INT PRIMARY KEY AUTO_INCREMENT,
4--     CategoryName VARCHAR(50) NOT NULL UNIQUE
5-- );
6
7-- Products 表定义
8-- CREATE TABLE Products (
9--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
10--     ProductName VARCHAR(50) NOT NULL UNIQUE,
11--     CategoryID INT,
12--     Price DECIMAL(10, 2) NOT NULL,
13--     Stock INT NOT NULL,
14--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
15-- );
16-- ------------------------------------------------------------------------------
17
18-- 请在此处编写代码

1SELECT CONCAT(SUBSTRING(CategoryName, 1, 3), SUBSTRING(ProductName, 1, 3)) AS ProductCode
2FROM Products
3JOIN Categories ON Products.CategoryID = Categories.CategoryID;

1SELECT CONCAT(SUBSTRING(CategoryName, 1, 3), SUBSTRING(ProductName, 1, 3)) AS ProductCode
2FROM Products
3JOIN Categories ON Products.CategoryID = Categories.CategoryID;

1-- Products 表定义
2
3-- CREATE TABLE Products (
4--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
5--     ProductName VARCHAR(50) NOT NULL UNIQUE,
6--     CategoryID INT,
7--     Price DECIMAL(10, 2) NOT NULL,
8--     Stock INT NOT NULL,
9--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
10-- );
11-- ------------------------------------------------------------------------------
12
13-- 请在此处编写代码

1SELECT ProductName, CONCAT(UPPER(SUBSTRING(ProductName, 1, 1)), LOWER(SUBSTRING(ProductName, 2))) AS TitleCaseName
2FROM Products;

1SELECT ProductName, CONCAT(UPPER(SUBSTRING(ProductName, 1, 1)), LOWER(SUBSTRING(ProductName, 2))) AS TitleCaseName
2FROM Products;

1-- Customers 表定义
2
3-- CREATE TABLE Customers (
4--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
5--     CustomerName VARCHAR(50) NOT NULL,
6--     Email VARCHAR(50),
7--     Phone VARCHAR(15),
8--     Address VARCHAR(100)
9-- );
10-- ------------------------------------------------------------------------------
11
12-- 请在此处编写代码

1SELECT Email, SUBSTRING(Email, LOCATE('@', Email) + 1) AS Domain
2FROM Customers;

1SELECT Email, SUBSTRING(Email, LOCATE('@', Email) + 1) AS Domain
2FROM Customers;