MySQL 문자열1 - CONCAT, SUBSTRING

[주인장]

-- 테이블 만들기

CREATE TABLE books

(

book_id INT 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);

-- 데이터 확인

SELECT * FROM books;

SELECT 1 + 4;

-- CONCAT(문자열 합치기)

SELECT CONCAT('k','i','m');

SELECT 'hello';

SELECT CONCAT(author_fname, '!!!') FROM books b ;

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

SELECT CONCAT(author_fname,' ', author_lname) as 'author_name' FROM books b ;

-- CONCAT_WS(특정 문자로 결합하기)

SELECT CONCAT_WS('!', 'hi', 'by', 'lol');

SELECT CONCAT_WS("-", title, author_fname, author_lname) FROM books;

-- SUBSTRING (부분 문자열)

SELECT SUBSTRING('Hello World', 1, 4); -- 문자열, 시작위치, 길이

SELECT SUBSTRING('Hello World', 1, 6);

SELECT SUBSTRING('Hello World', 2, 7);

SELECT SUBSTRING('Hello World', 7); -- 문자열, 시작위치

SELECT SUBSTRING('Hello World', -1); -- 문자열, 역순 시작 위치

SELECT SUBSTRING('Hello World', -3);

SELECT SUBSTRING('Hello World', -3, 2); -- 문자열, 역순 시작위치, 길이

SELECT SUBSTRING(title, 1, 10) FROM books;

-- SUBSTR은 SUBSTRING과 동일한 동작 수행

SELECT SUBSTRING(author_lname, 1, 1) FROM books b ;

SELECT SUBSTR(author_lname, 1, 1) FROM books b ;