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;
SELECT youName FROM members3;
+-----------+
| youName |
+-----------+
| 황상연 |
| 남윤서 |
| 김진우 |
| 최선화 |
| 서대원 |
| 김현민 |
| 황인지 |
| 박정민 |
| 강서연 |
| 이재혁 |
| 박소현 |
| 김혜지 |
| 강경민 |
| 안지은 |
| 박진아 |
+-----------+
15 rows in set (0.00 sec)이름 + 나이 데이터만 가져오기
SELECT youName, youAge FROM members;
SELECT youName,youAge FROM members3;
+-----------+--------+
| 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 IN(1);
SELECT * FROM members3 WHERE memberID IN(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 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 members3 WHERE memberID > 5 AND memberID < 10;
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가 5보다 크고 10보다 작은 데이터
SELECT * FROM members3 WHERE memberID > 5 AND memberID < 10;
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인 데이터
SELECT * FROM members 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이 아닌 경우만 불러오기(BWTWEEN)
SELECT * FROM members WHERE youAge NOT BETWEEN '30' OR '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 '%연%';
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)데이터를 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개(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;
SELECT count(youName) FROM members;
mysql> SELECT count(*) FROM members;
+----------+
| count(*) |
+----------+
| 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.00 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 );
SELECT * FROM members WHERE youAge >= 30 AND (memberID = 2 OR memberID =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)