[SQL]테이블에서 데이터 검색

 

SQL 공부 겸, 시작한 내용정리. 

공부할 때 참고한 도서는 

SQL 첫걸음

---

1. 테이블에서 데이터 검색

 

1. Hello world 실행하기

SELECT 명령

select * from 테이블명

1. select * from 테이블명 실행

sql 명령은 mysql 클라이언트에 문자를 입력해 실행할 수 있다.

C:\\Users\\user\\dump>mysql -uroot -ppassword sample

C:\\Users\\qkrwl>mysql -uroot -p****(비밀)

mysql> SELECT * FROM sample21;
+------+--------+------------+-------------------+
| no   | name   | birthday   | address           |
+------+--------+------------+-------------------+
|    1 | 박준용 | 1976-10-18 | 대구광역시 수성구 |
|    2 | 김재진 | NULL       | 대구광역시 동구   |
|    3 | 홍길동 | NULL       | 서울특별시 마포구 |
+------+--------+------------+-------------------+
3 rows in set (0.00 sec)

 

💡 mysql 클라이언트에 SQL 명령을 입력하여 실행할 수 있다. 이때 SQL 명령의 마지막에는 세미콜론을 붙인다.

 

2. SELECT 명령 구문

select 명령으로 데이터 베이스의 데이터를 읽어올 수 있다. select 명령은 질의나 쿼리라 불리기도 한다.

SELECT * FROM sample21;

맨 앞의 select는 SQL 명령의 한 종류로 'SELECT 명령을 실행하세요'라는 의미이다. 그다음의 * 은 모든 열을 의미하는 메타문자이다.

이 코드를 실행하면 sample21 테이블의 모든 데이터를 읽어온다.

 

3. 예약어와 데이터베이스 객체명

SELECT * FROM sample21;

SELECT와 FROM이 구를 결정하는 키워드이자 예약어이다.

데이터베이스에는 테이블 외에 다양한 데이터를 저장하거나 관리하는 '어떤 것'을 만들 수 있다. 이것을 '데이터베이스 객체'라 부르는데, 뷰(view)가 그에 해당한다.(뷰는 나중에 설명)

데이터베이스 객체는 이름을 붙여 관리한다. 가은 이름으로 다른 데이터베이스 객체는 만들 수 없다. 예를 들면, sample21이 기존 데이터 베이스 내에 존재하는 테이블이므로 sample21이라는 동일한 이름으로 새로운 테이블을 만들 수 없다.

• 예약어와 데이터베이스 객체명은 대소문자를 구별하지 않는다.

4. select * from 을 실행한 결과 = 테이블

select 명령을 실행하면 표 형식의 데이터가 출력된다. 표 형식의 데이터는 행과 열로 구성된다. 행은 모두 동일한 형태로 되어있고 옆으로 열이 나열된다. 열마다 이름이 지정되어 있다. 각각의 행과 열이 만나는 부분을 셀이라 부른다. 셀에는 하나의 데이터 값이 저장되어있다.

 

💡 데이블은 행과 열로 구성된 표 형식의 데이터이다.
💡 데이터는 자료형으로 분류할 수 있다. 열은 하나의 자료형만 가질 수 있다.

2. 테이블 구조 보기기

1. DESC명령

• DESC명령

DESC 테이블명;

select 명령으로 테이블의 데이터를 읽어왔다. 테이블은 한 개 이상의 열로 구성이 된다. select 명령에서는 생략했지만, 열을 지정하여 조건을 붙이거나, 특정 열의 값을 읽어올 수 있다. 테이블에 어떠 이 있는지 참조할 수 있다면 select 명령을 작성하기 쉬워진다. 여기에서는 DESC명령으로 테이블 구조를 참조하는 방법을 알아보겠다.

mysql> DESC sample21;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| no       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| address  | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

이와 같이 DESC 명령으로 테이블에 어떤 열이 정의되어 있는지 알 수 있다.

• field에는 열 이름이 표시되면 type은 해당 열의 자료형을 나타낸다. 괄호 안의 숫자는 최대 길이를 나타낸다.
• NULL은 NULL값을 허용할 것인지 아닌지를 나타내는 제약사항으로 yes를 지정하면 NULL값을 허용하게 된다.
• Key는 해당 열이 키로 지정되어 있는지를 나타낸다.
• Default는 그 열에 주어진 기본값, 즉 생략했을 경우 적용되는 값이다. 테이블에 행을 추가할 때, 열의 데이터 값을 생략하면 이 기본 값으로 채워진다.

 💡 DESC 명령으로 테이블 구조를 참조할 수 있다.

 

2. 자료형

테이블은 하나 이상의 열로 구성되며 DESC 명령으로 그 구조를 참조할 수 있다.

• integer : 수치형의 하나로 정수값을 저장할 수 있는 자료형, 소수점은 포함할 수 있다.

• char : 문자열형의 하나로 문자열을 저장할 수 있는 자료형, 문자열형에서는 열의 최대 길이를 지정해야 한다. char으로 자료형을 지정했을 경우 최대 10 문자로 된 문자열을 저장할 수 있으며 11 문자로 된 문자열은 저장할 수 없다.

• varchar 형 : 문자열을 저장할 수 있는 자료형. 최대 길이를 지정하는 점은 char형과 같다. 단 char형과 달리 데이터 크기에 맞춰 저장공간의 크기도 변경이 된다. 그에 따라 가변 길이 문자열 자료형이라고 한다.

• date 형 : date 형은 날짜값을 저장할 수 있는 자료형,

• time 형 : 시간을 저장할 수 있는 자료형

💡 문자열형에는 고정길이와 가변길이가 있다.

 

3. 검색 조건 지정하기

조건을 지정하여 데이터를 검색하는 방법. 데이터 검색에는 열을 지정하는 방법과 행을 지정하는 방법이 있다.

select 열1, 열2 from 테이블명 where 조건식

통상적인 데이터베이스에는 많은 행들이 있다. 화면에 표시해 데이터를 확인할 수 있는데, 한 번에 볼 수 있는 데이터양은 한정되어 있다. 화면에 표시할 때는 행이나 열을 선택해 데이터베이스에서 데이터를 읽어오는 다시 말해 '검색'이 빈번하게 일어난다. 행을 선택할 때는 where 구를 사용하며 열을 선택할 때는 select 구를 사용한다.

 

1. select 구에서 열 지정하기

데이터를 선택할 때는 열이나 행을 한정한다. 먼저 열을 한정하는 방법

select 열1, 열2, .. from 테이블명

열은 콤마를 이용해 구분 지으며 여러 개를 지정할 수 있다.

mysql> select no,name from sample21;
+------+--------+
| no   | name   |
+------+--------+
|    1 | 박준용 |
|    2 | 김재진 |
|    3 | 홍길동 |
+------+--------+
3 rows in set (0.00 sec)

• 열을 전혀 지정하지 않으면 구문 에러 발생
• 테이블에 존재하지 않는 열을 지정해도 에러 발생,
• 열 지정 순서는 임의로 정할 수 있음
• 테이블에서 열이 정의된 순서와 동일한 순으로 지정할 필요는 없다.
• 동일한 열을 중복해서 지정해도 무관

 

2. where 구에서 행 지정하기

데이터베이스의 규모에 따라 다르지만 일반적으로 수백 건에서 대규모의 경우 수 천만 건의 행에 달하는 경우도 있음 많은 행 속에서 필요한 데이터만 검색하기 위해 where 구를 사용한다.

where 구는 from 구의 뒤에 표기한다.

select 열 from 테이블명 where 조건식

구에는 순서가 있어서 바꿔 적을 수가 없다. from 구 뒤에 무조건 where 구를 표기한다. 만약 where 구를 생략한 경우는 테이블 내의 모든 행이 검색 대상이 된다.

• where구
• 조건식은 열과 연산자, 상수로 구성되는 식이다. no=2인 행만 선택해 출력해 보겠다.

mysql> select * from sample21 where no=2;
+------+--------+----------+-----------------+
| no   | name   | birthday | address         |
+------+--------+----------+-----------------+
|    2 | 김재진 | NULL     | 대구광역시 동구 |
+------+--------+----------+-----------------+
1 row in set (0.00 sec)

💡 where 구의 조건에 일치하는 행만 결과로 반환된다.

 

• 조건식

💡 조건식은 참 또는 거짓의 진리값을 반환하는 식으로 비교 연산자를 사용해 표현한다.

 

• 값이 서로 다른 경우 '<>' : where 구로 no 열값이 2가 아닌 행만 추출

mysql> select * from sample21 where no <> 2;
+------+--------+------------+-------------------+
| no   | name   | birthday   | address           |
+------+--------+------------+-------------------+
|    1 | 박준용 | 1976-10-18 | 대구광역시 수성구 |
|    3 | 홍길동 | NULL       | 서울특별시 마포구 |
+------+--------+------------+-------------------+
2 rows in set (0.00 sec)

💡 <>연산자를 통해 값이 서로 다른 경우 참이 되는 조건식으로 변경할 수 있다.

조건식에 일치하는 행이 전혀 없는 경우에는 아무것도 반환되지 않는다.

 

3. 문자열형의 상수

이번에는 name열을 사용해 위와 동일한 조건으로 검색해 보겠다.

mysql> select * from sample21 where name='박준용';
+------+--------+------------+-------------------+
| no   | name   | birthday   | address           |
+------+--------+------------+-------------------+
|    1 | 박준용 | 1976-10-18 | 대구광역시 수성구 |
+------+--------+------------+-------------------+
1 row in set (0.00 sec)

문자열형을 비교할 경우 '박준용'처럼 '' 로 둘러싸 표기해야 함.

날짜 시간형의 경우도 '' 로 둘러싸 표기한다. 이때 연월일을 하이픈으로 구분한다. 시각은 시분초를 :(콜론)으로 구분해 표기한다.

4. NULL값 검색

NULL값을 검색할 경우에는 조금 주의해야 한다.

mysql> select * from sample21 where birthday=NULL;
Empty set (0.00 sec)

birthday 열 값이 NULL인 행이 있지만 birthday=NULL과 같은 조건식으로 검색이 되지 않는다. 즉, =NULL 연산자로 NULL을 검색할 수 없다.

 

• IS NULL

NULL값을 검색할 때는 = 연산자가 아닌 IS NULL을 사용한다. IS NULL은 술어로 연산자의 한 종류이다.

mysql> select * from sample21 where birthday IS NULL;
+------+--------+----------+-------------------+
| no   | name   | birthday | address           |
+------+--------+----------+-------------------+
|    2 | 김재진 | NULL     | 대구광역시 동구   |
|    3 | 홍길동 | NULL     | 서울특별시 마포구 |
+------+--------+----------+-------------------+
2 rows in set (0.00 sec)

💡 NULL값을 검색할 경우에는 IS NULL을 사용한다.

 

5. 비교 연산자

• = 연산자 : 좌변과 우변의 값이 같을 경우 참이 된다.
• <> 연산자 : 좌변과 우변의 값이 같지 않을 경우 참이 된다.
• 연산자 : 좌변의 값이 우변의 값보다 클 경우 참이 된다.
• ≥연산자 : 좌변의 값이 우변의 값보다 크거나 같을 경우 참이 된다. 작을 경우는 거짓이 된다.
• <연산자 : 좌변의 값이 우변의 값보다 작을 경우 참이 됨
• ≤연산자 : 좌변의 값이 우변의 값보다 작거나 같을 경우 참이 됨.

 

4. 조건 조합하기

3개의 논리연산자 and, or, not에 관해 설명

조건식1 and 조건식2
조건식1 or 조건식2
not 조건식

1. AND로 조합하기

복수의 조건을 조합할 경우 and를 가장 많이 사용한다.

조건식1 and 조건식2

일단 sample24를 이제 사용하려고 한다.

sample24에서 a열과 b열이 모두 0이 아닌 행을 검색하는 경우를 생각해 보겠다. 0이 아니라는 조건은 a <>0과 b <>이라는 조건식이 된다.

mysql> select * from sample24;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    2 |    0 |    1 |    0 |
|    3 |    0 |    0 |    1 |
|    4 |    2 |    2 |    0 |
|    5 |    0 |    2 |    2 |
+------+------+------+------+
5 rows in set (0.01 sec)

mysql> select * from sample24 where a<>0 and b<>0;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    4 |    2 |    2 |    0 |
+------+------+------+------+
1 row in set (0.00 sec)

and연산은 조건을 만족하는 행을 집합으로 표현했을 때 이들 집합이 겹치는 부분, 즉 교집합으로 표현할 수 있다.

2. OR로 조합하기

또는 이라는 뜻을 가짐. 어느 쪽이든 하나만 참이 되면 조건식은 참이 된다.

a열이 0이 아니거나 b열이 0이 아닌 행을 검색

mysql> select * from sample24 where a<>0 or b<>0;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    2 |    0 |    1 |    0 |
|    4 |    2 |    2 |    0 |
|    5 |    0 |    2 |    2 |
+------+------+------+------+
4 rows in set (0.00 sec)

💡 or로 조건식을 연결하면 어느 쪽이든 조건을 만족하는 행을 모두 검색할 수 있다.

 

3. and와 or를 사용할 경우 주의할 점

and 연산자나 or 연산자의 좌우로 참과 거짓을 반환하는 조건식을 지정하는 경우가 많다. 열이나 상수 만을 지정해도 에러가 발생하지는 않지만 기대한 결괏값을 얻을 수 없다.

예를 들어 no 열의 값이 1 또는 2인 행을 추출하고 싶을 경우, 다음과 같은 조건식을 사용하면 올바른 결과를 얻을 수 있다.

mysql> select * from sample24 where no=1 or 2;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    2 |    0 |    1 |    0 |
|    3 |    0 |    0 |    1 |
|    4 |    2 |    2 |    0 |
|    5 |    0 |    2 |    2 |
+------+------+------+------+
5 rows in set (0.00 sec)

상수 2는 논리연산으로 항상 참이 되기 때문에 결과적으로 모든 행을 반환하게 된다. 올바른 조건식은 아래와 같다.

mysql> select * from sample24 where no=1 or no=2;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    2 |    0 |    1 |    0 |
+------+------+------+------+
2 rows in set (0.00 sec)

 

and과 or을 조합해 사용하기

a열이 1 또는 2이고, b열이 1 또는 2인 행을 검색

mysql> select * from sample24 where a=1 or a=2 and b=1 or b=2;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    4 |    2 |    2 |    0 |
|    5 |    0 |    2 |    2 |
+------+------+------+------+
3 rows in set (0.00 sec)

위 조건식은 a <>0 and b <>0과 같다. 하지만 결과는 다르다!! 이게 머선 일 😓

mysql> select * from sample24 where a<>0 and b<>0;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    4 |    2 |    2 |    0 |
+------+------+------+------+
1 row in set (0.00 sec)

 

연산자의 우선순위

이유는 and과 or의 계산 우선순위가 다르기 때문이다. or보다 and 쪽이 우선순위가 높기 때문에 a=2 and b=1이 먼저 계산이 된다.

이를 괄호로 나타내면 다음과 같다.

mysql> select * from sample24 where a=1 or (a=2 and b=1) or b=2;
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    0 |    0 |
|    4 |    2 |    2 |    0 |
|    5 |    0 |    2 |    2 |
+------+------+------+------+
3 rows in set (0.00 sec)

처음 원한 대로 조건을 지정하기 위해서는 다음과 같이 괄호로 우선순위를 변경하면 된다.

mysql> select * from sample24 where (a=1 or a=2) and (b=1 or b=2);
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    4 |    2 |    2 |    0 |
+------+------+------+------+
1 row in set (0.00 sec)

💡 and는 or에 비해 우선순위가 높다.

4. not으로 조합

not 연산자는 오른쪽에만 항목을 지정하는 단항연산자이다. 오른쪽에 지정한 조건식의 반대 값을 반환한다. 만약 조건식이 참을 반환하면 not은 이에 반하는 거짓을 반환한다. 주로 복수의 조건식에 대해 '아닌' ,'외에' '~를 제외한 나머지' 등의 조건을 지정할 경우 사용한다.

mysql> select * from sample24 where not(a<>0 or b<>0);
+------+------+------+------+
| no   | a    | b    | c    |
+------+------+------+------+
|    3 |    0 |    0 |    1 |
+------+------+------+------+
1 row in set (0.00 sec)

 

5. 패턴 매칭에 의한 검색

like 술어를 사용하면 문자열의 일부분을 비교하는 부분검색을 할 수 있다.

열 LIKE 패턴

특정문자나 문자열이 포함되어 있는지를 검색하고 싶은 경우에 사용하는 방법이 패턴매칭, 부분검색이다.

1. LIKE로 패턴 매칭하기

= 연산자로 검색할 경우에는 열 값이 완전히 일치할 때 참이 된다 LIKE 술어를 사용하면 열 값이 부분적으로 일치하는 경우에도 참이 된다.

열명 LIKE '패턴'

like 술어는 이항 연산자처럼 항목을 지정한다 왼쪽에는 매칭 대산을 지정하고 오른쪽에는 패턴을 문자열로 지정한다 단 수치형 상수는 지정할 수 없다. 패턴을 정의할 는 다음과 같은 메타문자를 사용할 수 있다.

%_

'임의의 문자'또는 '문자열'에 매치하는 부분을 지정하기 위해 쓰이는 특수문자. %는 임의의 문자열을 의미하며 _는 임의의 문자 하나를 의미.

메타문자를 전혀 정의하지 않아도 문제는 없지만 완전 일치로 검색되므로 정의하는 의미가 없다. 와일드카드로 자주 쓰이는 *는 like에서는 사용할 수 없다.

💡 like 술어를 사용해 패턴 매칭으로 검색할 수 있다. 패턴을 정의할 때 사용할 수 있는 메타문자로는 % 와 _이 있다.

 

 

• 예제

mysql> select * from sample25;
+------+-------------------------------------------------+
| no   | text                                            |
+------+-------------------------------------------------+
|    1 | SQL은 RDBMS를 조작하기 위한 언어이다.           |
|    2 | LIKE에서는 메타문자 %와 _를 사용할 수 있다.     |
|    3 | LIKE는 SQL에서 사용할 수 있는 술어 중 하나이다. |
+------+-------------------------------------------------+
3 rows in set (0.03 sec)

mysql> select * from sample25 where text like 'SQL%';
+------+---------------------------------------+
| no   | text                                  |
+------+---------------------------------------+
|    1 | SQL은 RDBMS를 조작하기 위한 언어이다. |
+------+---------------------------------------+
1 row in set (0.00 sec)

문자열 'SQL'을 포함하는 행이 검색이 되었는데 3행에도 포함되어 있는데 왜 3행은 출력이 안된 걸까?? 그 이유는 text 열값이 SQL로 시작되지 않기 때문이다. SQL 앞에서 문자열이 존재하기 때문에 패턴을 바꿀 필요가 있다.

 

mysql> select * from sample25 where text like '%SQL%';
+------+-------------------------------------------------+
| no   | text                                            |
+------+-------------------------------------------------+
|    1 | SQL은 RDBMS를 조작하기 위한 언어이다.           |
|    3 | LIKE는 SQL에서 사용할 수 있는 술어 중 하나이다. |
+------+-------------------------------------------------+
2 rows in set (0.00 sec)

💡 %는 임의의 문자열과 매치하며 빈 문자열에도 매치한다.

 

• sql% : 문자열 앞쪽에 지정한 문자와 일치하므로 전방일치라고 부름. 지정한 문자 뒤로 임의의 문자열이 존재한다.
• %sql% : 지정 문자열이 중간에 있기 때문에 중간일치라고 부르며 지정한 문자 앞뒤로 임의의 문자열이 존재한다.

2. LIKE로 % 검색하기

like로 %를 검색하는 경우에는 '\\%' 와 같이 \ 을 % 앞에 붙인다.

mysql> select * from sample25 where text like '%\\%%';
+------+---------------------------------------------+
| no   | text                                        |
+------+---------------------------------------------+
|    2 | LIKE에서는 메타문자 %와 _를 사용할 수 있다. |
+------+---------------------------------------------+
1 row in set (0.00 sec)

💡 %를 LIKE로 검색할 경우에는 \%로 한다! _를 LIKE로 검색할 경우에는 \ _ 로 한다!

 

3. 문자열 상수의 ' 의 이스케이프

문자열 상수는 '문자열' 같이 '로 둘러싸 표기. 그럼 문자열 상수 안에 '을 포함하고 싶을 경우는 어떻게 할까?

표준 SQL에서는 '를 2개 연속해서 기술하는 것으로 이스케이프 처리를 할 수 있다. 예를 들면 'It's' 라는 문자열을 문자열 상수로 표기하면 'It''s'로 쓴다.

 

💡 '을 문자열 상수 안에 포함할 경우 '를 2개 연속해서 기술!

 

간단한 패턴매칭은 like로 충분하지만 더 복잡한 패턴을 매칭하는 경우는 정규 표현식을 사용. 최근에는 대부분의 데이터베이스가 정규표현식을 원한다.