'데이터 다루기/DB운영(MYSQL)'에 해당되는 글 8건

mysql은 패턴검색시 like만으로 해결이 되지 않는 경우가 있다

그럴때 regexp와 rlike를 사용해보자

-- a로 시작하는

where name like 'a%';

where name REGEXP '^a'

-- b로 끝나는

where name like '%b'

where name REGEXP 'b$'

-- c를 포함하는

where name like '%c%'

where name REGEXP 'c'

-- c를 포함하지 않는

where name not like '%c%'

-- 2번째 문자가 a인

where name like '_a%'

-- 2개문자로 구성된

where name REGEXP '^..$'

where name REGEXP '^.{2}$'

-- 숫자가 포함된

where name rlike '[0-9]*'

-- 알파벳이 포함된

where name rlike '[a-zA-Z]*'

-- 한글이 포함된 

WHERE name RLIKE '[가-힣]'

LIKE문으로 문자열을 가공하고자 할 경우 결과가 나오지 않을 때가 있다

글 제목이 '오늘'로 시작되는 글을 찾아보자

select title

from 테이블

where title like '오늘%'

LIKE문을 사용하지 않을 때는 오늘로 시작되는 글이 보이는데 왜 이럴까?

그럴 때는 어떤 문자가 숨어있는 건 아닌지 의심해보자

아래와 같이 ASCII함수를 사용해서 나오는 숫자를 확인해보자

select title, ASCII(title)

from 테이블

where title like '오늘%'

만일 13, 10, 9가 나왔다면 줄바꿈문자, 탭처럼 보이지 않는 문자가 숨어있어서 그렇다

이러한 문자를 제거해 줘야 한다

UPDATE 테이블명 SET title = REPLACE(title, CHAR(13), "")

UPDATE 테이블명 SET title = REPLACE(title, CHAR(10), "")

UPDATE 테이블명 SET title = REPLACE(title, CHAR(9), "")

다시 select문을 돌려보면 결과가 제대로 나올 것이다

1 인덱스 생성

alter table 테이블명 add index명 (컬럼들);

alter table 테이블명 add unique index명 (컬럼들);

alter table 테이블명 add PRIMARY KEY (컬럼들);

create index 인덱스명 on 테이블명(컬럼명);

create table (..., primary key (컬럼명), unique(컬럼명))

2 인덱스 삭제

drop index 인덱스명 on 테이블명;

drop index ‘PRIMARY’ on 테이블명;

alter table 테이블명 drop index 테이블명;

alter table 테이블명 drop PRIMARY KEY;

3 테이블 내 인덱스 보기

show index FROM 테이블명;

1 컬럼 추가

alter table 테이블명 add 컬럼명 데이터타입;

-- 가장 위에 추가 

alter table 테이블명 add 컬럼명 데이터타입 FIRST;

-- 컬럼명2 다음에 추가

alter table 테이블명 add 컬럼명 데이터타입 AFTER 컬럼명2;

2 컬럼의 자료형 변경

alter table 테이블명 modify 컬럼명 바꿀자료형;

3 컬럼명 변경

alter table 테이블명 change old컬럼명 new컬럼명 데이터타입 NULL여부;

4 컬럼 위치이동

alter table 테이블명 modify 컬러명 자료형 FIRST;

5 컬럼의 문자셋 변경

- alter table 테이블명 modify column 컬럼명 자료형   character set utf8 collate utf8_general_ci;

6 컬럼 삭제

- alter table 테이블명 drop column 컬럼명;

1 엔진 종류

innodb : 트랜잭션 엔진

MyiSAM : 트랜잭션을 지원하지 않는 기본 스토리지 엔진

memory : 인메모리 테이블

2 지원되는 엔진 확인

- show engines

3 INNODB 엔진을 가진 테이블 생성

create table 테이블명 ( ... ) engine=innodb;

4 엔진 변경

- alter table 테이블명 engine=innodb;

1 문자셋 & 콜레이션

문자셋 : 서버가 지원하는 문자셋

콜레이션 : 문자셋의 콜레이션

문자셋과 콜레이션은 호환되어야 한다

2 데이터베이스에 지정

create database db명 CHARACTER SET 문자셋 COLLATE 콜레이션;

3 테이블에 지정

create table 테이블명 (...) CHARACTER SET 문자셋 COLLATE 콜레이션;

4 컬럼에 지정

컬럼명 데이터타입 CHARACTER SET 문자셋 COLLATE 콜레이션

5 대소문자 구분여부

utf_general_ci 와 utf_general_cs를 살펴보자

ci는 cast insensitive로 대소문자를 가리지 않는다는 의미이고

cs는 cast sensitive로 대소문자를 민감하게 가리겠다는 의미이다

6 문자셋 유효성 확인

show character set like ‘utf%’; 

show collation like ‘utf%’;

7 문자셋 변경

alter table character set = 'utf8‘

alter table 테이블명 modify column 컬럼명 자료형  character set utf8 collate utf8_general_ci;

데이터베이스를 설치했다면 기본정보를 확인해 보시기 바랍니다

우선 접속정보를 확인해보겠습니다

1 현재시간, 현재접속자, 버전정보

SELECT now(), user(), version()

2 DB연결

mysql –p apmsetup –u root –h localhost

mysql -p [비밀번호] -u [사용자] -h [접속IP OR 사이트주소]

3 사용자생성

create user ‘aaa’@’localhost’ IDENTIFIED BY ‘pwd’

4 사용자 권한 부여

GRANT ALL ON sampledb.* TO ‘aaa’@’localhost’

5 생성한 사용자로 로그인

mysql –p pwd –u aaa –h localhost

6 접속끊기

quit 또는 exit 또는 \q 또는 ^D(유닉스)

테이블은 데이터를 담는 곳입니다

테이블을 관리하려면 테이블생성, 구조변경, 삭제 등을 알아야 합니다

아래 테이블 관련 쿼리문들이니 필요할 때 활용하시기 바랍니다

1 테이블 생성

create table 테이블명 ~

2 테이블명 변경

alter table 테이블명 RENAME TO new테이블명;

rename table 테이블명 TO new테이블명;

rename table 테이블명1 TO new테이블명1, 테이블명2 TO new테이블명2; 

3 테이블 수정 

alter table 테이블명 ~

4 테이블 삭제

drop table 테이블명

drop table IF EXISTS 테이블명

5 테이블 구조 보기

desc 테이블

show create table 테이블명;

6 데이터베이스 내의 테이블 보기 

show tables;