Server/MySQL 2018.02.27 21:14

MySQL- 6. MySQL 데이터 정보 검색

 

 

 

1. 모든 필드 정보 확인

 

mysql> select * from ccna;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 


2. 특정 필드 정보 확인

 

mysql> select userid from ccna;
+--------+
| userid |
+--------+
| ccna1  |
| ccna2  |
| ccna3  |
+--------+
3 rows in set (0.00 sec)

 

 


3. 다수 필드 정보 확인

 

mysql> select userid,userpass from ccna;
+--------+----------+
| userid | userpass |
+--------+----------+
| ccna1  | ccna1111 |
| ccna2  | ccna2222 |
| ccna3  | ccna3333 |
+--------+----------+
3 rows in set (0.00 sec)

 

 


4. 테이블 정보 조건 검색

 

mysql> select * from ccna where userid='ccna1';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
+--------+----------+--------------+------+
1 row in set (0.00 sec)

 

 

mysql> select * from ccna where username='kim jung woo';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

mysql> select * from ccna where age=23;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
+--------+----------+--------------+------+
1 row in set (0.00 sec)

 


mysql> select * from ccna where username like 'kim%';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 


mysql> select * from ccna where username like '%jung%';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
3 rows in set (0.00 sec)

 

 

mysql> select * from ccna where userpass like '%1111%' or age like '29';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
2 rows in set (0.00 sec)

 

 

mysql> select * from ccna where userpass not like '%3333%';
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo |   26 |
+--------+----------+--------------+------+
2 rows in set (0.00 sec)

Posted by 김정우 강사(카카오톡 : kim10322)


Q