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

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)

 

 

mysql> select * from ccna union select * from ccnp;
+--------+----------+----------------+------+
| UserID | UserPass | UserNAME       | age  |
+--------+----------+----------------+------+
| ccna1  | ccna1111 | kim jung woo  |   23 |
| ccna2  | ccna2222 | kim jung woo   |   26 |
| ccna3  | ccna3333 | kim jung woo   |   29 |
| ccnp1  | ccnp1111 | kim jung woo   |   33 |
| ccnp2  | ccnp2222 | kim jung woo |   36 |
| ccnp3  | ccnp3333 | kim jung woo |   39 |
+--------+----------+----------------+------+
6 rows in set (0.00 sec)

mysql>

 

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 age>25;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
2 rows in set (0.00 sec)

 

 

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

 

 

mysql> select * from ccna where age=23 or age=29;
+--------+----------+---------------+------+
| UserID | UserPass | UserNAME      | age  |
+--------+----------+---------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna3  | ccna3333 | kim jung woo  |   29 |
+--------+----------+---------------+------+

 

 

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

 

 

mysql> select * from ccna where age between 20 and 26;
+--------+----------+---------------+------+
| UserID | UserPass | UserNAME      | age  |
+--------+----------+---------------+------+
| ccna1  | ccna1111 | hong jil dong |   23 |
| ccna2  | ccna2222 | park chan ho  |   26 |
+--------+----------+---------------+------+
2 rows in set (0.00 sec)

 

 

mysql> select * from ccna where userid in ("ccna3");
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
1 row in set (0.00 sec)

 

 

mysql> select * from ccna where userid in ("ccna1","ccna3");
+--------+----------+---------------+------+
| 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 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)

 

 

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

 

 

mysql> select * from ccna limit 2;
+--------+----------+---------------+------+
| UserID | UserPass | UserNAME      | age  |
+--------+----------+---------------+------+
| ccna1  | ccna1111 | hong jil dong |   23 |
| ccna2  | ccna2222 | park chan ho  |   26 |
+--------+----------+---------------+------+
2 rows in set (0.00 sec)

 

 

mysql> select * from ccna limit 0,2;
+--------+----------+---------------+------+
| UserID | UserPass | UserNAME      | age  |
+--------+----------+---------------+------+
| ccna1  | ccna1111 | kim jung woo |   23 |
| ccna2  | ccna2222 | kim jung woo  |   26 |
+--------+----------+---------------+------+
2 rows in set (0.00 sec)

 

 

mysql> select * from ccna limit 1,2;
+--------+----------+--------------+------+
| UserID | UserPass | UserNAME     | age  |
+--------+----------+--------------+------+
| ccna2  | ccna2222 | kim jung woo |   26 |
| ccna3  | ccna3333 | kim jung woo |   29 |
+--------+----------+--------------+------+
2 rows in set (0.00 sec)