users 테이블

idfirst_namesecond_nameattribute

와일드카드 _ & %

_는 한 글자만을 의미하며, %는 0글자부터 그 이상을 의미한다.

%와 % 사이에 텍스트를 넣으면 해당 텍스트가 포함된 모든 raw를 불러올 수 있다.

-- TEST가 중간에 들어가는 모든 raw를 불러온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';

idfirst_namesecond_nameattribute

SELECT * FROM users WHERE attribute LIKE '_TEST%';
=> TEST 앞에 1만 존재하는 3번 raw만 가져온다.

와일드카드 없이 텍스트로 시작하거나 끝나면 해당 텍스트로 시작되거나 끝나야 한다.

SELECT * FROM users WHERE attribute LIKE 'TEST%';
=> TEST로 시작하는 2번 raw만 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%Axc';
=> Axc로 끝나는 3,4번 raw를 가져온다.

case sensitivity

case sensitivity는 대소문자를 구별하느냐의 차이를 의미한다.

MySQL - case-insensitive <-> BINARY

MySQL에서 LIKE는 기본적으로 case-insensitive하다. 즉, 대소문자를 구별하지 않는다.

-- 모두 같은 결과를 가져온다.
SELECT * FROM users WHERE attribute LIKE '%TEST%';
SELECT * FROM users WHERE attribute LIKE '%test%';
SELECT * FROM users WHERE attribute LIKE '%TeSt%';

case-sensitive 하려면 즉, 대소문자를 구별하여 패턴을 찾기 위해서는 BINARY를 사용해야 한다.

SELECT * FROM users WHERE attribute LIKE BINARY '%TEST%';

PostgreSQL - case-sensitive <-> ILIKE

PostgreSQL에서는 MySQL과는 반대로 case-sensitive하다. 즉, 대소문자를 구별한다.

case-insensitive 하려면 ILIKE를 사용해야 한다. ILIKE는 PostrgreSQL에서만 지원하는 방식이다.

SELECT * FROM users WHERE attribute LIKE '%TEST%'; # case-sensitive
SELECT * FROM users WHERE attribute ILIKE '%tEsT%'; # case-insensitive

LIKE PERFORMANCE 관련

정규식 표현 REGEXP (MySQL), ~ (PostgreSQL)와 비교

-- 두 쿼리가 같은 의미 (MySQL)
SELECT * FROM users WHERE attribute LIKE '%TEST%'; -- LIKE 가 더 빠름
SELECT * FROM users WHERE attribute REGEXP 'TEST'; -- REGEXP

정규식 표현을 LIKE와 같은 용도로 활용하면 성능이 훨씬 느리기 때문에 주의가 필요하다.

정규식 표현은 필요한 상황에 맞춰서 활용해야 한다.

INDEX 활용

와일드카드가 뒤에 오는 prefix search( e.g. LIKE ‘TEST%’ )의 경우, B-TREE INDEX를 활용하여 성능을 높일 수 있다.

하지만, ‘%TEST’처럼 와일드카드가 먼저 나오는 postfix search경우는 FULL TABLE SCAN을 실행할 수밖에 없다.

( INDEX는 값을 LEFT-TO-RIGHT로 처리하는 특성 때문 )

‘%TEST%’같은 infix search도 마찬가지다. 이럴 경우, FULL TEXT SEARCH를 고려할 수 있다.

문제 설명

이번 문제는 attribute에서 %{first_name}_{second_name}%의 구조를 가진 raw를 찾는 것이다. 단, 이름 철자 그대로 적용되어야 한다.

예를 들면, 1번 raw에서 Mike Cober은 %Mikel_Cober%형태를 띄고 있다. 하지만, 4번 raw의 경우, %MACk_ALice%형태로 대소문자에 문제가 있음을 볼 수 있다.

CONCAT은 column value와 텍스트를 이어주는 역할을 한다. 와일드카드를 문자로 활용하기 위해서 \를 앞에 붙여야 한다.

-- MySQL 기준
-- 와일드카드 %와 % 사이에 %{fisrt_name}_{second_name}% 구조를 넣은 형식이다.
SELECT * FROM users WHERE attribute 
    LIKE BINARY CONCAT('%\%', first_name , '\_' , second_name ,'\%%')

3번은 모두 소문자이고, 4번은 대소문자가 다르므로 정확하게 입력된 건 1,2번 raw뿐이다.

idfirst_namesecond_nameattribute

ALTER가 아닌 RENAME을 통해 테이블에 이름을 변경하는 법을 알아보도록 하겟습니다.

테이블 이름 변경할 때, ALTER와 RENAME에 차이점을 뭘까요?

바로 RENAME을 사용하면 한 번에 다수의 테이블 이름을 변경할 수 있다는 것입니다!

그리고 RENAME은 테이블을 다른 데이터베이스로 이동시킬 수도 있습니다.

▶사용법

단일 테이블 이름 변경

RENAME TABLE old_table TO new_table;

단일 테이블 이름 변경 (ALTER)

ALTER TABLE old_table RENAME new_table;

다수 테이블 이름 변경

RENAME TABLE old_table1 TO new_table1, old_table2 TO new_table2, old_table3 TO new_table3;

다른 데이터베이스로 테이블 이동

RENAME TABLE current_db.table_name TO other_db.table_name;

▶MySQL 데이터 추가, 수정, 삭제 (INSERT, UPDATE, DELETE)

▶데이터 추가 (INSERT)

사용법

INSERT INTO 테이블명 [컬럼1, 컬럼2, ...] VALUES (값1, 값2, ...);

예제 : 전체 컬럼

INSERT INTO table_name VALUES (1105, '테스트', '대한민국');

예제 : 일부 컬럼

INSERT INTO table_name (id, name) VALUES (1105, '테스트');

예제 : 다수 추가

INSERT INTO table_name VALUES (1105, '테스트', '대한민국'),(1106, '테스트2', '미국');

▶데이터 수정 (UPDATE)

사용법

UPDATE 테이블명 SET 컬럼1 = 수정값1 [, 컬럼2 = 수정값2 ...] [WHERE 조건];

예제 : 테이블 데이터 전체 수정

UPDATE table_name SET country = '대한민국';

예제 : 테이블 데이터 일부 수정

UPDATE table_name SET name = '테스트 변경', country = '대한민국' WHERE id = 1105;

▶데이터 삭제 (DELETE)

사용법

DELETE FROM 테이블명 [WHERE 조건];

예제 : 테이블 데이터 전체 삭제

DELETE FROM table_name;

예제 : 테이블 데이터 일부 삭제

DELETE FROM table_name WHERE id = 1105;

개념

특정 데이터는 구분자가 되기위해 PK(Primary key) 설정을 한다.

PK의 특징은 고유성(유니크)을 가져야하기 때문에 똑같은 데이터를 넣을 수 없다.

그래서 보통 PK를 int 타입으로 겹치지않게 인덱스(정수값)를 계속 올린다.

그때 그 인덱스(정수값)를 체크해 자동으로 올려주는 옵션이 바로 AUTO_INCREMENT이다.

자동으로 올려주는 옵션

자동으로 올려주는 옵션

한번 설정 해두면 Insert into 할 때 마다 자동으로 값을 올려준다.

조건

기본키(Primary Key)에만 옵션 부여가 가능하다.

문법

테이블 생성시 옵션 부여

CREATE TABLE auto_test(
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64));

이미 생성한 테이블에 옵션 추가 - Command

ALTER TABLE auto_test MODIFY id INT NOT NULL AUTO_INCREMENT;

이미 생성한 테이블에 옵션 추가 - GUI (HeidiSQL 10.2.0.5599 version)

라디오 버튼 체크하고 밑에 저장을 눌러주면 된다.

AUTO_INCREMENT 초기화

개념

AUTO_INCREMENT을 사용하다가 1~5번까지의 데이터를 넣어뒀다가

2~5번 데이터를 지우고 데이터를 다시 생성(insert)해보면 다시 2~5번 키의 데이터가 생성되는게 아니라

5~8번의 키를 가진 데이터가 생성된다.

이 값을 다시 2~5번 데이터로 넣고 싶을 땐 초기화를 해줘야한다.

문법

ALTER TABLE [TABLE] AUTO_INCREMENT = [시작 값];

주의 : 테이블에 새로 시작할 값보다 높은 값이 있으면 안됩니다. 

AUTO_INCREMENT 값을 초기화 후, 테이블 안의 모든 데이터의 ID값을 재조정 하기

무슨 말이냐 하면, 데이터 1000개가 존재하는데 많이 삭제됬다가 다시 생성되고 해서 첫번 째 데이터가 2000 or 10000 이런식으로 높게 존재할 경우

다시 1000개의 데이터를 1~1000번의 키를 가질 수 있게 하는 방법이다.

문법

ALTER TABLE tableName AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE tableName SET auto_id(auto 설정한 컬럼) = @COUNT:=@COUNT+1;

Ubuntu에 mysql을 설치해 보겠습니다.

언제나 그렇듯이 Ubuntu 버전부터 살펴보겠습니다.

이제 설치해 보겠습니다.

apt-get을 먼저 update하고 mysql-server를 설치합니다.

만약 iptable이 실행되고 있다면 외부에서 접속할 수 있도록 mysql 포트(3306)를 열어줘야 합니다.

mysql이 설치되었다면 실행해 줍니다.

Ubuntu 서버가 재시작 되더라도 mysql이 자동 시작되도록 등록합니다.

sql에 접속해 보겠습니다.

접속이 되었다면 아래처럼 sql 프롬프트가 보일 겁니다.

mysql에 접속할때 root 계정에 패스워드 없이 접속했습니다. root 계정에 패스워드를 추가해 보겠습니다. 

패스워드를 변경하는 쿼리는 mysql 버전별로 조금 다릅니다. 그래서 버전을 먼저 확인해 보겠습니다.

제가 설치한 mysql 버전은 5.7.25입니다.

5.7.6 이후 버전이라면 아래와 같이 쿼리를 실행합니다. 저는 패스워드를 ngle1234로 했습니다.

만약 5.7.6 이전 버전이라면 아래와 같이 쿼리를 실행하면 됩니다.

저는 최신 버전이라 그런지 위 두 쿼리 모두 적용됩니다.

이제 mysql을 사용할 계정을 만들어 주겠습니다. 웹 서버 등과 연결해 사용할 때는 root 계정을 사용하는 것 보다 계정을 만들고 권한을 부여해 사용하는 것이 좋습니다.

먼저 현재 mysql의 계정(User) 정보를 확인하겠습니다.

database를 만들고 database에 계정을 추가해 보도록 하겠습니다.

아래와 같이 ngletutor라는 database를 만듭니다.

잘 만들어 졌는지 database 리스트를 확인해 보겠습니다.

ngletutor라고 database가 있는걸 확인할 수 있습니다.

이번에는 ngletutor를 사용하는 계정을 만들겠습니다.

계정은 tongchun이고 Host는 localhost, 그리고 패스워드는 ngle1234로 만들었습니다.

mysql의 user 테이블에 추가하거나 변경이 있을 경우 flush privileges 쿼리를 실행해 줍니다.

아래 쿼리가 대문자인 것은 mysql의 예약어(select, update 등)은 대문자로 하는 것이 관례(convention)입니다.

계정이 잘 만들어 졌는지 user 정보도 다시 확인해 봅니다.

이제 tongchun 계정에 ngletutor DB를 사용할 수 있도록 권한을 부여합니다.

database의 권한을 변경했으니 flush privileges 쿼리를 다시 해줍니다.

이번에는 tongchun계정이 localhost에서 어떤 권한을 가지고 있는지 확인해 보겠습니다.

위 쿼리 결과로 나온 두 건 중 첫 번째에 GRANT USAGE ON *.* TO가 있습니다. 이건 *.*는 아무 권한이 없다는 의미 입니다. 현제 tongchun 계정은 ngletutor DB만 모든 권한을 가지고 있고 mysql의 admin이나 system을 접근할 수 있는 권한은 없다는 것을 의미합니다.

계정 뒤에 붙은 @localhost는 해당 계정이 localhost 즉 mysql이 설치된 pc에서만 접속할 수 있다는 의미입니다. 만약 다른 서버(remote)에서 접속하고 싶다면 접속하려는 서버의 IP로 계정을 새로 만들어 줘야 합니다.

예를 들어 192.168.0.10에서 접속하고 싶다면 아래와 같이 쿼리를 실행하면 됩니다.

CREATE USER 'root'@'192.168.0.10' IDENTIFIED BY 'ngle1234';

GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.0.10' WITH GRANT OPTION; 

FLUSH PRIVILEGES;

만약 특정 PC(192.168.0.10)이 아닌 어디서든 접속하려면 IP대신 %로 해주면 됩니다.

CREATE USER 'root'@'%' IDENTIFIED BY 'ngle1234';

GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' WITH GRANT OPTION; 

FLUSH PRIVILEGES;

위 쿼리로 어디에서든 접속 가능하게 %로 계정을 추가했습니다.

계정 리스트를 다시 보면 root@% 계정이 추가된걸 확인할 수 있습니다.

마찬가지로 tongchun계정도 remote 접속이 필요하다면 아래와 같이 쿼리를 실행해 줍니다.

CREATE USER 'tongchun'@'%' IDENTIFIED BY 'ngle1234';

GRANT ALL PRIVILEGES ON ngletutor.* TO 'tongchun'@'%';

FLUSH PRIVILEGES;