SELECT

전체 데이터 가져오기

SELECT * FROM members;
mysql> select * from members;
+----------+-----------+---------+--------+----------+------------+
| 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)/code>

이름 데이터만 가져오기

SELECT youName FROM members;
mysql> SELECT youName FROM members;
+-----------+
| youName   |
+-----------+
| 황상연      |
| 남윤서      |
| 김진우      |
| 최선화      |
| 서대원      |
| 김현민      |
| 황인지      |
| 박정민      |
| 강서연      |
| 이재혁      |
| 박소현      |
| 김혜지      |
| 강경민      |
| 안지은      |
| 박진아      |
+-----------+
15 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 |
|        7 | 황인지      | 1234    |     20 |   990315 | 1474187756 |
+----------+-----------+---------+--------+----------+------------+
2 rows in set (0.01 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;
mysql> 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;
mysql> SELECT * FROM members 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 members where memberID > 5 and memberID < 10;
mysql> SELECT * FROM members 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 members where memberID IN (1,5,10);
mysql> SELECT * FROM members 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.01 sec)

나이가 30~40이 아닌 경우만 불러오기 (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.01 sec)

youAge가 30보다 작은 데이터

SELECT * FROM members where youAge < 30;
mysql> SELECT * FROM members where youAge < 30;
+----------+-----------+---------+--------+----------+------------+
| 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.01 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번)

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)

이름 필드 데이터 총 갯수 불러오기

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

이름 필드 데이터 총 갯수 불러오기

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

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

SELECT DISTINCT youAge From members;
mysql>SELECT DISTINCT youAge From members;
+--------+
| youAge |
+--------+
|     33 |
|     19 |
|     21 |
|     26 |
|     32 |
|     20 |
|     29 |
|     27 |
|     24 |
|     28 |
+--------+
10 rows in set (0.00 sec)

나이가 25살 이상, 성이 황씨인 사람

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.01 sec)

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

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