SELECT

전체 데이터 가져오기

SELECT * FROM members(테이블명);
mysql> SELECT * FROM members3(테이블명);
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375  |
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375  |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233  |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152  |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000  |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375  |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756  |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375  |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744  |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375  |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123  |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789  |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467  |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375  |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375  |
+----------+---------+---------+--------+----------+------------+
15 rows in set (0.00 sec)

이름 데이터만 가져오기

SELECT youName(필드명) FROM members(테이블명);
mysql> SELECT youName(필드명) FROM members3(테이블명);
+---------+
| youName |
+---------+
| 황상연   |
| 남윤서   |
| 김진우   |
| 최선화   |
| 서대원   |
| 김현민   |
| 황인지   |
| 박정민   |
| 강서연   |
| 이재혁   |
| 박소현   |
| 김혜지   |
| 강경민   |
| 안지은   |
| 박진아   |
+---------+
15 rows in set (0.00 sec)

이름 + 나이 데이터만 가져오기

SELECT youName(필드명), youAge(필드명) FROM members(테이블명);
mysql> SELECT youName(필드명), youAge(필드명) FROM members(테이블명);
+---------+--------+
| youName | youAge |
+---------+--------+
| 황상연  |     33  |
| 남윤서  |     19  |
| 김진우  |     21  |
| 최선화  |     26  |
| 서대원  |     26  |
| 김현민  |     32  |
| 황인지  |     20  |
| 박정민  |     29  |
| 강서연  |     27  |
| 이재혁  |     24  |
| 박소현  |     29  |
| 김혜지  |     28  |
| 강경민  |     28  |
| 안지은  |     33  |
| 박진아  |     33  |
+---------+--------+
15 rows in set (0.00 sec)

이름 내림차순 불러오기

SELECT * FROM members ORDER By youName DESC;
mysql> SELECT * FROM members ORDER By youName DESC;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
+----------+---------+---------+--------+----------+------------+
15 rows in set (0.00 sec)

이름 올림차순 불러오기

SELECT * FROM members ORDER By youName ASC;
mysql> SELECT * FROM members ORDER By youName ASC;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
+----------+---------+---------+--------+----------+------------+
15 rows in set (0.00 sec)

나이 내림차순 불러오기

SELECT * FROM members ORDER By youAge DESC;
mysql> SELECT * FROM members ORDER By youAge DESC;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
15 rows in set (0.00 sec)

나이 올림차순 불러오기

SELECT * FROM members ORDER By youAge ASC;
mysql> SELECT * FROM members ORDER By youAge ASC;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
15 rows in set (0.00 sec)

memberID가 1인 데이터

SELECT * FROM members(테이블명) WHERE memberID(필드명) = 1;
 SELECT * FROM members3(테이블명) WHERE memberID(필드명) = 1;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
1 row in set (0.00 sec)

memberID가 10보다 큰 데이터

SELECT * FROM members(테이블명) WHERE memberID(필드명) > 10;
SELECT * FROM members3(테이블명) WHERE memberID(필드명) > 10;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
5 rows in set (0.00 sec)

memberID가 5보다 크고 10보다 작은 데이터

SELECT * FROM members3(테이블명) WHERE memberID(필드명) > 5 AND memberID(필드명) < 10;
mysql> SELECT * FROM members3(테이블명) WHERE memberID(필드명) > 5 AND memberID(필드명) < 10;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
+----------+---------+---------+--------+----------+------------+
4 rows in set (0.00 sec)

memberID가 1, 5, 10인 데이터 - IN

SELECT * FROM members3(테이블명) WHERE memberID(필드명) IN(1, 5, 10);
SELECT * FROM members3(테이블명) WHERE memberID(필드명) IN(1, 5, 10);
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
3 rows in set (0.00 sec)

나이가 30~40인 경우만 불러오기 - BETWEEN

SELECT * FROM members WHERE youAge BETWEEN 30 AND 40;
mysql> SELECT * FROM members WHERE youAge BETWEEN 30 AND 40;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
4 rows in set (0.00 sec)

나이가 30~40이 아닌 경우만 불러오기 - NOT BETWEEN

SELECT * FROM members WHERE youAge NOT BETWEEN 30 AND 40;
mysql> SELECT * FROM members WHERE youAge NOT BETWEEN 30 AND 40;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
+----------+---------+---------+--------+----------+------------+
11 rows in set (0.00 sec)

"연"이 포함된 이름만 불러오기

SELECT * FROM members WHERE youName LIKE "%연%";
mysql> SELECT * FROM members WHERE youName LIKE "%연%";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
+----------+---------+---------+--------+----------+------------+
2 rows in set (0.00 sec)

"김"으로 시작되는 이름만 불러오기

SELECT * FROM members WHERE youName LIKE "김%";
mysql> SELECT * FROM members WHERE youName LIKE "김%";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
+----------+---------+---------+--------+----------+------------+
3 rows in set (0.00 sec)

"현"으로 끝나는 이름만 불러오기

SELECT * FROM members WHERE youName LIKE "%현";
mysql> SELECT * FROM members WHERE youName LIKE "%현";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
+----------+---------+---------+--------+----------+------------+
1 row in set (0.00 sec)

중간에 "상"이라는 단어가 포함된 3글자 이름만 불러오기

SELECT * FROM members WHERE youName LIKE "_상_";
mysql> SELECT * FROM members WHERE youName LIKE "_상_";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
1 row in set (0.00 sec)

"김" 또는 "이"로 시작하는 이름만 불러오기

SELECT * FROM members WHERE youName LIKE "김%" OR youName LIKE "이%";
mysql> SELECT * FROM members WHERE youName LIKE "김%" OR youName LIKE "이%";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
+----------+---------+---------+--------+----------+------------+
4 rows in set (0.00 sec)

데이터를 5개만 불러오기

SELECT * FROM members LIMIT 5;
mysql> SELECT * FROM members LIMIT 5;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
|        2 | 남윤서  | 1234    |     19 |    60121 | 1023958375 |
|        3 | 김진우  | 1234    |     21 |    40416 | 1231231233 |
|        4 | 최선화  | 1234    |     26 |   990123 | 1104781152 |
|        5 | 서대원  | 1234    |     26 |    11019 | 1111100000 |
+----------+---------+---------+--------+----------+------------+
5 rows in set (0.00 sec)

데이터를 5개만 불러오기(6~10번)

SELECT * FROM members LIMIT 5, 5;
mysql> SELECT * FROM members LIMIT 5, 5;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        6 | 김현민  | 1234    |     32 |   930604 | 1023958375 |
|        7 | 황인지  | 1234    |     20 |   990315 | 1474187756 |
|        8 | 박정민  | 1234    |     29 |   961017 | 1023958375 |
|        9 | 강서연  | 1234    |     27 |   980420 |  123456744 |
|       10 | 이재혁  | 1215    |     24 |    10712 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
5 rows in set (0.00 sec)

데이터를 5개만 불러오기(11~15번)

mysql> SELECT * FROM members LIMIT 10, 5;
mysql> SELECT * FROM members LIMIT 10, 5;
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|       11 | 박소현  | 1234    |     29 |   960917 | 1231223123 |
|       12 | 김혜지  | 1234    |     28 |   951002 |  123456789 |
|       13 | 강경민  | 1234    |     28 |   970611 | 1023952467 |
|       14 | 안지은  | 1234    |     33 |   840816 | 1023958375 |
|       15 | 박진아  | 1232    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
5 rows in set (0.00 sec)

이름 필드 데이터 총 개수 불러오기 - count(*)

mysql> SELECT count(*) FROM members;
SELECT count(*) FROM members;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

이름 필드 데이터 총 개수 불러오기 - count(필드명)

mysql> SELECT count(youName) FROM members;
mysql> SELECT count(*) FROM members;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

나이 불러오기(중복된 나이 제거) - DISTINCT

mysql> SELECT DISTINCT youName FROM members;
mysql> SELECT DISTINCT youName FROM members;
+---------+
| youName |
+---------+
| 황상연  |
| 남윤서  |
| 김진우  |
| 최선화  |
| 서대원  |
| 김현민  |
| 황인지  |
| 박정민  |
| 강서연  |
| 이재혁  |
| 박소현  |
| 김혜지  |
| 강경민  |
| 안지은  |
| 박진아  |
+---------+
15 rows in set (0.00 sec)

나이가 25살 이상, 성이 황씨인 사람 불러오기

mysql> SELECT * FROM members WHERE youAge > 25 AND youName LIKE "황%";
mysql> SELECT * FROM members WHERE youAge > 25 AND youName LIKE "황%";
+----------+---------+---------+--------+----------+------------+
| memberID | youName | youPass | youAge | youBirth | regTime    |
+----------+---------+---------+--------+----------+------------+
|        1 | 황상연  | 1234    |     33 |   990304 | 1023958375 |
+----------+---------+---------+--------+----------+------------+
1 row in set (0.00 sec)

나이가 30살 이상, memberID가 2 또는 15인 멤버 불러오기 - 2가지 방법

mysql> SELECT * FROM members WHERE youAge >= 30 AND memberID in(2,15);
mysql> SELECT * FROM members WHERE youAge >= 30 AND (memberID = 2 OR memberID = 15);

mysql> SELECT * FROM members WHERE youAge >= 30 AND memberID in(2,15);
mysql> SELECT * FROM members WHERE youAge >= 30 AND (memberID = 2 OR memberID = 15); +----------+---------+---------+--------+----------+------------+ | memberID | youName | youPass | youAge | youBirth | regTime | +----------+---------+---------+--------+----------+------------+ | 15 | 박진아 | 1232 | 33 | 990304 | 1023958375 | +----------+---------+---------+--------+----------+------------+ 1 row in set (0.00 sec)