1
CREATE TABLE Customers (
2
    Name VARCHAR(100),
3
    Username VARCHAR(100)
4
);

1
INSERT INTO Customers (Name, Username) VALUES
2
    ('John Doe', 'JDoe'),
3
    ('Jane Smith', 'JSmith'),
4
    ('Jim Brown', 'JBrown'),
5
    ('Joseph Lee', 'JLee');

1
SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
2
-- 输出：Hello World

1
SELECT SUBSTRING('Hello World', 1, 5) AS Substring;
2
-- 输出：Hello

1
SELECT UPPER('hello world') AS Uppercase;
2
-- 输出：HELLO WORLD
3

4
SELECT LOWER('HELLO WORLD') AS Lowercase;
5
-- 输出：hello world

1
SELECT LENGTH('Hello World') AS Length;
2
-- 输出：11

1
SELECT TRIM('   hello world   ') AS TrimmedString;
2
-- 输出：hello world
3

4
SELECT LTRIM('   hello world   ') AS LeftTrimmedString;
5
-- 输出：hello world
6

7
SELECT RTRIM('   hello world   ') AS RightTrimmedString;
8
-- 输出：   hello world

1
SELECT LEFT('Hello World', 5) AS Left;
2
-- 输出：Hello
3

4
SELECT RIGHT('Hello World', 5) AS Right;
5
-- 输出：orld

1
SELECT SOUNDEX('John Doe') AS Soundex;
2
-- 输出：J460

1
SELECT * FROM Customers WHERE SOUNDEX(Username) = SOUNDEX('J.Lie');

1
SELECT ROUND(3.14159, 2) AS RoundedNum; -- 3.14
2
SELECT FLOOR(3.99999) AS FloorNum; -- 3
3
SELECT CEILING(3.00001) AS CeilingNum; -- 4
4
SELECT ABS(-123.45) AS AbsNum; -- 123.45

1
SELECT NOW() AS CurrentDateTime; -- 当前日期和时间
2
SELECT CURDATE() AS CurrentDate; -- 当前日期
3
SELECT CURTIME() AS CurrentTime; -- 当前时间
4
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS FormattedDateTime; -- 格式化后的日期和时间

1
CREATE TABLE blog (
2
    id INT PRIMARY KEY auto_increment,
3
    NAME CHAR (32),
4
    sub_time datetime
5
);

1
INSERT INTO blog (NAME, sub_time)
2
VALUES
3
    ('第1篇','2015-03-01 11:31:21'),
4
    ('第2篇','2015-03-11 16:31:21'),
5
    ('第3篇','2016-07-01 10:21:31'),
6
    ('第4篇','2016-07-22 09:23:21'),
7
    ('第5篇','2016-07-23 10:11:11'),
8
    ('第6篇','2016-07-25 11:21:31'),
9
    ('第7篇','2017-03-01 15:33:21'),
10
    ('第8篇','2017-03-01 17:32:21'),
11
    ('第9篇','2017-03-01 18:31:21');

1
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
2

3
select 8 from blog where Date(sub_time) = '2015-03-01'
4

5
select 8 from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

1
SELECT COUNT(*) AS RowCount FROM Employees;
2
SELECT SUM(Salary) AS SalarySum FROM Employees;
3
SELECT AVG(Salary) AS AverageSalary FROM Employees;
4
SELECT MIN(Salary) AS MinSalary FROM Employees;
5
SELECT MAX(Salary) AS MaxSalary FROM Employees;

1
SELECT IF(Age > 18, 'Adult', 'Child') AS AgeGroup FROM Employees;
2
SELECT CASE WHEN Salary > 5000 THEN 'High' ELSE 'Low' END AS SalaryGroup FROM Employees;