【MySQL】 常用字符串函数

-- Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert --
-- -- -- String Functions 字符串函数 -- -- --
-- 字符串包含单引号''或者双引号""，比如'123'或者"123"


-- -- ① CONCAT -- -- 合并、连结字符串
CONCAT('Hello',' ', 'Everyone'); 
-- ❌错误
SELECT CONCAT('Hello',' ', 'Everyone');
-- ✔ 正确，放在SELECT后面
SELECT CONCAT('Hello', 1, 'Everyone');
-- ❌错误，必须是字符，可以是'1'
SELECT CONCAT("Hello",' ', 'Everyone');
-- ✔ 正确，可以单双引号混用


-- -- ② SUBSTRING/SUBSTR -- -- 截取字符串
-- 放在SELECT 后面
SELECT SUBSTRING('Hello Everyone', 1, 7); 
-- 截取从左到右，1~4号位的字符 
SELECT SUBSTRING('Hello Everyone', 7);
-- 截取从左到右，>= 7号位的字符
SELECT SUBSTRING('Hello Everyone', -3);
-- -- 截取从左到右，>= -3号位的字符


-- -- ③ REPLACE -- -- 替换
SELECT REPLACE('Hello Everyone', 'l', '7');
-- 将字符串里的字母'l'全部替换为'7'
SELECT REPLACE('you me', ' ', ' and ');
-- 将字符串里的空白格' '替换为'and


-- -- ④ REVERSE -- -- 颠倒、反转
SELECT REVERSE('Hello Everyone');


-- -- ⑤ CHAR LENGTH -- -- charactor字符串长度
SELECT CHAR_LENGTH('Hello Everyone');


-- -- ⑥ UPPER 和 LOWER -- --字符串大小写转换
SELECT UPPER('Hello Everyone'); 
SELECT LOWER('Hello Everyone');


-- -- 结合不同string functions的运用 -- --
SELECT CONCAT(
    'author_fname',
    ' ', 
    'author_lname'
) FROM books;


SELECT CONCAT(
    author_fname,
    ' ', 
    author_lname
) FROM books;


SELECT CONCAT(
    author_fname,
    ' ', 
    author_lname
) AS 'Full Name' FROM books;


SELECT UPPER(
    CONCAT(
        author_fname, 
        ' ', 
        author_lname)
) AS "full name in caps"


FROM books; -- 注意UPPER和CONCAT所放的位置顺序


SELECT CONCAT(UPPER(author_fname, ' ', author_lname)) AS "full name in caps" 
FROM books; -- ❌错误


SELECT
   CONCAT(SUBSTRING(title, 1, 10), '...') AS 'short title',
   CONCAT(author_lname, ',', author_fname) AS author,
   CONCAT(stock_quantity, ' in stock') AS quantity
FROM books;

-- 创建表格 --
DROP DATABASE IF EXISTS book_shop;
CREATE DATABASE book_shop;
USE book_shop; 


CREATE TABLE books 
  (
    book_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(100),
    author_fname VARCHAR(100),
    author_lname VARCHAR(100),
    released_year INT,
    stock_quantity INT,
    pages INT,
    PRIMARY KEY(book_id)
  );


INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),
("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Don', 'DeLillo', 1985, 49, 320),
('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);


INSERT INTO books
    (title, author_fname, author_lname, released_year, stock_quantity, pages)
    VALUES ('10% Happier', 'Dan', 'Harris', 2014, 29, 256), 
           ('fake_book', 'Freida', 'Harris', 2001, 287, 428),
           ('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);