231020_DB 설계와 SQL 활용

My SQL 은 국제 표준화 기구(IOS)에서 표준으로 만든 것으로, 오라클과 어느정도 공통적인 부분은 있지만 조금씩 차이가 난다. 오라클을 기준으로 작성된 교재와 조금씩 차이가 있을 수 있으므로, 진행하다가 막힌다면 찾아보고 변형해서 쓸 수 있도록 하자.

SQL은 크게 DDL, DML, DCL 로 이루어져 있다. (DDL은 스키마를 생성, 변경, 삭제 하는 것으로 지난 수업때 진행 완료함.)

DML은 Insert, upDATE, delete, select.

위 4가지는 신택스적으로 CRUD, create(: Insert)  read(: select), U(:upDATE), D(: delete) 라는 용어로 말한다. 그 중에서도 검색을 중심으로 나가며, 검색은 Insert, upDATE, delete 와 달리 데이터가 점점 쌓이게 된다는 특징이 있다. 소프트웨어는 가장 효율적인 판단을 잘 하지 못하므로, SQL 을 얼마나 효율적으로 만들어주는지가 중요한 요소가 된다.

워크벤치에서 쿼리를 전송하면 쿼리는 DB로 전송이 된다. (프로그래밍에서 이러한 문의는 요청, 즉 '데이터베이스에 정보를 요청하는 일'을 말함.) 그럼 제일 먼저 1) 문법검색 (문법적으로 빠진게 있는지, 오탈자는없는지) 을 한 뒤, 2) 메타정보(데이터를 위한 데이터) 를 확인하고, 3) 효율적인 처리방법을 고민한다.

참고로 데이터는 파일로 하드디스크에 저장되며, 하드디스크에 저장된 데이터를 찾는 속도는 상당히 느리다. 사람이라면 경우 저장된 데이터를 어떻게 찾을지 뇌가 처리방법을 고민하듯, 컴퓨터도 최적화된 방법으로 찾기 위해 옵티마이저 (최적화) 가 존재한다.

옵티마이저가 고민하는 방법은 두가지로 1) 규칙기반, 2) 비용기반이 있으며, 각각의 특징은 아래와 같다.

1) 규칙기반은 정해진 규칙에 따라 처리하는 것으로, 단순하지만 효율적이지 않을 수 있다.

2) 비용기반은 그떄그때 처리방법이 달라지므로 복잡할 수 있지만, 빠르다는 장점이 있다.

옵티마이저는 이 두가지를 고민하고, 이를 통해 실행계획을 만들어 4)실행 한다. 여기서 SQL 전문가가 실행 결과물을 보고 좀 더 효율적으로 바꾸는 것을 SQL튜닝이라고 한다.

---

 [MySQL] DATETIME 원하는 유형으로 변경 (YYMMDD) 

 mysql string fuction 

테이블에서 특정 열 선택하기

 

테이블에서 모든 열 출력하기

참고, 특정 컬럼을 뒤에 한번 더 출력하기

컬럼 별칭을 사용하여 출력되는 컬럼명 변경하기

 

연결연산자 사용하기, 오라클과 달리 My SQL 워크벤치에서는 concat() 사용

참고, 작은 따움표를 이용해서 문자를 붙여 출력할 수 있다.

 

중복된 데이터는 제거해서 (distinct) 출력하기

 

데이터 정렬(ORDER BY)해서 출력하기

ORDER BY 뒤에 콤마(,)를 붙여서 추가로 입력할 수 있다. asc는 오름차순, desc는 내림차순.

 

where절 1) 문자 데이터  중 ring 데이터만 출력

 

where절 1)   문자 데이터  중 kim 데이터만 출력

 

customer_information 테이블에서 membership 이 '2022-01-26' 인 사람 정보 출력

 

산술 연산자 진행을 위한 전체 칼럼 참고

 

비교연산자 >, <, >= 등... price 칼럼에서 50.00 이상인 데이터만 출력

 

비교연산자 between, 50.00 ~ 100.00 사이 price 데이터를 출력

 

비교연산자 Like, Address 칼럼에서 g로 시작하는 정보를 출력

 

비교 연산자 in (카테고리가 링, 이어링, 바지 인 것들의 물건 넘버, 재질, 무게, 가격 표시하기)

비교 연산자 not in (링, 이어링, 바지를 제외한 물건 표기)

논리 연산자(And) 카테고리가 top이고, 가격이 100.00 이상인 것의 카테고리, 재질, 무게, 가격 출력하기

 

대소문자 변환 함수(UPPER, LOWER, initcap)

문자에서 특정 철자 추출 (SUBSTR)

문자열의 길이 출력(length)

문자에서 특정 철자의 위치 출력 (INSTR)

문자에서 특정 철자의 위치 출력 (INSTR) 추가 예제

but SELECT RTRIM( SUBSTR(email, INSTR(email,'@')+1), 'il') FROM customer_information; 는 데이터에 .com 이 없어서 그런건지 실행이 되지 않았다. 데이터를 수정한 뒤 다시 실행해볼 필요가 있다. 

특정 철자를 다른 철자로 변경하기 (REPLACE)

특정 철자를 다른 철자로 변경하기2 (REPLACE)

 

특정 철자 잘라내기 (TRIM, RTRIM, LTRIM) But MySQL 의 기능과 다른 점 확인하기.

특정 철자 잘라내기 (TRIM, RTRIM, LTRIM)

위에서 사용된 코드는 아래와 같다.

email 데이터를 입력할 때 _ _ _ _ @ gmail 만 입력했더니 SELECT RTRIM( SUBSTR(email, INSTR(email,'@')+1), 'il') FROM customer_information; 를 실행할 수 없었다. gmail.com 으로 다시 데이터를 입력해서 결과를 확인하지 못한 부분들을 재 확인 할 필요가 있다.  

CREATE DATABASE Customer_list_3;
USE Customer_list_3;

CREATE TABLE Customer_Information ( -- 고객정보
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR (20),
    Address VARCHAR (50),
    tell VARCHAR (20),
    email VARCHAR (20),
    membership DATE,
    PRIMARY KEY(seq)
);
CREATE TABLE product_name ( -- 상품명
    seq INT NOT NULL AUTO_INCREMENT,
    product_number VARCHAR(30),
    category VARCHAR(30),
    material VARCHAR(30),
    weight DECIMAL(10, 2),
    price  DECIMAL(10, 2),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq) -- 고객정보와 1:N
);
CREATE TABLE product_reservation ( -- 예약
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    Product_number INT,
    confirmation_of_deposit DATE,
    Address VARCHAR(50),
    tell VARCHAR(20),
    email VARCHAR(20),
    customer_information_id INT,
    PRIMARY KEY (seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq), -- 고객정보와 1:N
    FOREIGN KEY (Product_number) REFERENCES product_name(seq) -- 상품명과 1:1
);
CREATE TABLE confirmation_of_deposit ( -- 입금확인
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    email VARCHAR(20),
    deposit_type VARCHAR(40),
    card_number VARCHAR(50),
    price INT,
    product_reservation_id INT,
    product_name_id INT,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq), -- 고객정보와 1:N 
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq), -- 예약정보와 1:N
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq) -- 상품명과 1:N
);
CREATE TABLE Questions ( -- 문의사항 
    seq INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(20),
    tell VARCHAR(20),
    Inquiry_code VARCHAR(20),
    email VARCHAR(20),
    date_of_inquiry DATE,
    customer_information_id INT,
    PRIMARY KEY(seq),
    FOREIGN KEY (customer_information_id) REFERENCES Customer_Information(seq) -- 고객정보와 1:N
);

CREATE TABLE Questions_ProductName ( -- 문의사항과 상품명 (N:M)
    question_id INT,
    product_name_id INT,
    PRIMARY KEY (question_id, product_name_id), 
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_name_id) REFERENCES product_name(seq)
);

CREATE TABLE Questions_ProductReservation ( -- 문의사항과 예약 (N:M)
    question_id INT,
    product_reservation_id INT,
    PRIMARY KEY (question_id, product_reservation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (product_reservation_id) REFERENCES product_reservation(seq)
);

CREATE TABLE Questions_ConfirmationOfDeposit ( -- 문의사항과 입금정보 (N:M)
    question_id INT,
    confirmation_id INT,
    PRIMARY KEY (question_id, confirmation_id),
    FOREIGN KEY (question_id) REFERENCES Questions(seq),
    FOREIGN KEY (confirmation_id) REFERENCES confirmation_of_deposit(seq)
);

select * from Customer_Information; -- 고객정보 데이터
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'seoul', '01012345678', 'qwer@gmail', '2020-05-14');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'gyeonggi-do', '01023456789', 'asdf@gmail', '2022-01-26');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('hong', 'gyeonggi-do', '01022223333', 'zxcv@gmail', '2019-07-17');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('gang', 'gangwon-do', '01033334444', 'wert@gmail', '2018-03-20');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('jeong', 'busan', '01055556666', 'sdfg@gmail', '2015-04-08');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('choi', 'daejeon', '01066667777', 'xcvb@gmail', '2016-12-24');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'busan', '01077778888', 'erty@gmail', '2019-08-27');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'chungnam', '01088889999', 'dfgh@gmail', '2021-04-06');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('song', 'seoul', '01044445555', 'cvbn@gmail', '2023-01-18');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'seoul', '01011112222', 'rtyu@gmail', '2020-11-13');

select * from product_name; -- 상품명 데이터
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('1', 'ring', 'silver', 7.50, 100.00 , 1);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('2', 'ring', 'gold', 3.08, 80.00, 2);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('3', 'earring', 'silver', 10.00, 70.30, 3);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('4', 'earring', 'gold', 7.54, 23.40, 4);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('5', 'top', 'silk', 200.00, 110.20, 5);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('6', 'top', 'linen', 340.00, 54.06, 6);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('7', 'bottom', 'leather', 45.20, 14.06, 7);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('8', 'bottom', 'linen', 300.00, 102.49, 8);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('9', 'shoes', 'rubber', 460.20, 48.78, 9);
INSERT INTO product_name (product_number, category, material, weight, price, customer_information_id) VALUES ('10', 'shoes', 'leather', 400.64, 100.75, 10);

select * from product_reservation; -- 예약 데이터
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '1', '2023-10-14', 'seoul', '01012345678', 'qwer@gmail', 1);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '2', '2023-10-11', 'gyeonggi-do', '01023456789', 'asdf@gmail', 2);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('hong', '3', '2020-08-20', 'gyeonggi-do', '01022223333', 'zxcv@gmail', 3);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('gang', '4', '2021-10-14', 'gangwon-do', '01033334444', 'wert@gmail', 4);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('jeong', '5', '2016-11-06', 'busan', '01055556666', 'sdfg@gmail', 5);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('choi', '6', '2018-02-25', 'daejeon', '01066667777', 'xcvb@gmail', 6);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '7', '2020-01-18', 'busan', '01077778888', 'erty@gmail', 7);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '8', '2023-10-23', 'chungnam', '01088889999', 'dfgh@gmail', 8);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('song', '9', '2023-06-10', 'seoul', '01044445555', 'cvbn@gmail', 9);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '10', '2021-07-15', 'seoul', '01011112222', 'rtyu@gmail', 10);

select * from confirmation_of_deposit; -- 입금 데이터
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01012345678', 'qwer@gmail', 'card', '123', 10, 1, 1, 1);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01023456789', 'asdf@gmail', 'card', '234', 13, 2, 2, 2);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('hong', '01022223333', 'zxcv@gmail', 'card', '345', 17, 3, 3, 3);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('gang', '01033334444', 'wert@gmail', 'card', '456', 20, 4, 4, 4);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('jeong', '01055556666', 'sdfg@gmail', 'card', '678', 55, 5, 5, 5);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('choi', '01066667777', 'xcvb@gmail', 'card', '789', 45.6, 6, 6, 6);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01077778888', 'erty@gmail', 'card', '912', 25, 7, 7, 7);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01088889999', 'dfgh@gmail', 'card', '987', 18.4, 8, 8, 8);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('song', '01044445555', 'cvbn@gmail', 'card', '876', 20, 9, 9, 9);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01011112222', 'rtyu@gmail', 'card', '765', 8, 10, 10, 10);

select * from Questions; -- 문의 데이터
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01012345678', '1', 'qwer@gmail', '2023-10-17', 1);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01023456789', '2', 'asdf@gmail', '2023-10-10', 2);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('hong', '01022223333', '3', 'zxcv@gmail', '2021-07-13', 3);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('gang', '01033334444', '4', 'wert@gmail', '2021-10-17', 4);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('jeong', '01055556666', '5', 'sdfg@gmail', '2016-10-30', 5);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('choi', '01066667777', '1', 'xcvb@gmail', '2018-02-25', 6);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01077778888', '2', 'erty@gmail', '2020-01-20', 7);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01088889999', '3', 'dfgh@gmail', '2023-10-22', 8);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('song', '01044445555', '4', 'cvbn@gmail', '2023-06-15', 9);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01011112222', '5', 'rtyu@gmail', '2021-07-15', 10);

select * from Questions_ProductName; -- 문의와 상품 연결
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (1, 1);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (2, 2);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (3, 3);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (4, 4);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (5, 5);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (6, 6);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (7, 7);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (8, 8);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (9, 9);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (10, 10);

select * from Questions_ProductReservation; -- 문의와 예약 연결
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (1, 1);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (2, 2);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (3, 3);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (4, 4);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (5, 5);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (6, 6);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (7, 7);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (8, 8);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (9, 9);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (10, 10);

select * from Questions_ConfirmationOfDeposit; -- 문의와 입금 정보 연결
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (1, 1);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (2, 2);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (3, 3);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (4, 4);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (5, 5);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (6, 6);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (7, 7);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (8, 8);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (9, 9);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (10, 10);

SELECT seq, tell, customer_name
FROM confirmation_of_deposit;

SELECT seq, membership, customer_name
FROM customer_information;

SELECT *
FROM product_name;

SELECT *
FROM questions;

SELECT customer_name as 성함, tell as 전화번호, membership as "가입일자" FROM customer_information;

SELECT customer_information.*, customer_name from customer_information;

SELECT concat(customer_name, email) FROM customer_information;

SELECT CONCAT(customer_name, '님의 이메일은 "', email, '" 입니다.') AS 고객이메일 FROM customer_information;

select * from product_name;
select distinct category from product_name;

select * from customer_information;
SELECT customer_name, membership FROM customer_information ORDER BY membership asc;
SELECT customer_name, membership FROM customer_information ORDER BY membership asc, customer_name desc;

SELECT category, material, weight FROM product_name WHERE category = 'ring';

select customer_name, Address, tell, email, membership FROM customer_information WHERE customer_name = 'kim';

select * from product_name;
SELECT category, product_number, price FROM product_name ORDER BY price asc;
select category, material, weight, price from product_name where price >= 50.00;
select category, material, weight, price from product_name 
where price between 50.00 and 100.00;

select * from customer_information;

select customer_name, Address, membership from customer_information where Address Like 'g%';

select customer_name, Address, tell, email, membership from customer_information where membership = '2022-01-26';

select product_number, material, weight, price from product_name where category in ('ring', 'earring', 'bottom');

select product_number, material, weight, price from product_name where category not in ('ring', 'earring', 'bottom');

select category, material, weight, price from product_name where category='top' and price >= 100.00;

select UPPER(customer_name), LOWER(customer_name), initcap(customer_name) from customer_information; 
-- MySQL에는 initcap 함수가 내장 함수로 포함되어 있지 않음.
select UPPER(customer_name), LOWER(customer_name) from customer_information;
SELECT UPPER(customer_name) FROM customer_information; -- 대문자로 출력
SELECT LOWER(customer_name) FROM customer_information; -- 소문자로 출력
select tell, membership from customer_information where UPPER(customer_name)='kim'; 
-- 이름이 kim 인 사람의 데이터가 대문자인지 소문자인지 확실하지 않을때, 대문자로 이름을 모두 조회한 뒤 kim 을 찾고 번호와 가입일을 출력함.

select customer_name from customer_information;
SELECT SUBSTR(customer_name, 1, 2) FROM customer_information;

select customer_name, length(customer_name) FROM customer_information;

SELECT INSTR(customer_name,'k') FROM customer_information;
select INSTR(email,'@') FROM customer_information;
SELECT SUBSTR(email, INSTR(email,'@')+1) FROM customer_information;
SELECT RTRIM( SUBSTR(email, INSTR(email,'@')+1), 'il') FROM customer_information; -- 데이터에 com 이 없어서 안되는건지? 다시해보기

SELECT price, REPLACE(price, '0', '*') FROM product_name; 
-- REPLACE 함수는 문자열을 대체하는 함수이므로 문자열을 지정해야 함. '0'을 문자열로 사용.
SELECT price, REGEXP_REPLACE (price, '[0-1]', '*') FROM product_name; -- REGEXP_REPLACE 함수 제공 안함
SELECT REPLACE(customer_name, SUBSTR(customer_name,2,1),'*') as "전광판_이름" FROM customer_information; 

SELECT Address, LPAD(sal, 11,'*') as Address1, RPAD(sal,11,'*') as Address2 FROM customer_information; -- 데이터 타입이 맞지 않아 사용 불가. 변환 필요...

-- My SQL 은 오라클과 달리 특정 철자 잘라내는 방식이 다르다.
-- 오라클은 TRIM, RTRIM, LTRIM 을 이용하지만 My SQL 은 LEADING, BOTH, TRAILING 를 사용한다.
SELECT TRIM(LEADING 'x' FROM'xxxbarxxx');
-> 'barxxx'
SELECT TRIM(BOTH 'x' FROM'xxxbarxxx');
-> 'bar'
SELECT TRIM(TRAILING 'xyz' FROM'barxxyz');
-> 'barx'

-- My SQL 에서 문자를 잘라내는 방법은 옆과 같음. 010 을 없애고 싶다면 ''에 공백을, XXX로 바꾸고 싶다면 'XXX' 로 작성하면 됨. 
SELECT REPLACE(tell, '010', '') FROM Customer_Information; 


-- LEADING 을 이용하면 아래처럼 쓸 수 있다 
SELECT tell as "전화번호", TRIM(LEADING '010' FROM tell) as "뒷번호"
FROM customer_information
WHERE tell like '010%';