mysql 명령어 예제(create, insert, show, update)

▶ user DB를 생성한다.
  mysql> create database user;
  Query OK, 1 row affected (0.00 sec)

mysql> show databases; //database 확인
+----------+
| Database |
+----------+
| mysql    |
| test     |
| user     |
+----------+
3 rows in set (0.00 sec)

mysql> use user; //사용하고자 하는 DB 설정
Database changed 

mysql> select database(); //DB 확인
+------------+
| database() |
+------------+
| user       |
+------------+
1 row in set (0.00 sec)


▶ member 테이블을 생성한다.

create table member (
id int not null default '0' auto_increment,
username varchar(20) not null,
userpass varchar(25) not null,
tel varchar(13),
email varchar(50) not null,
primary key(id));

mysql> desc member;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     |      | PRI | NULL    | auto_increment |
| username | varchar(20) |      |     |         |                |
| userpass | varchar(25) |      |     |         |                |
| tel      | varchar(13) | YES  |     | NULL    |                |
| email    | varchar(50) |      |     |         |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

▶ member 테이블에 자료를 넣는다.
mysql> insert into member values(
    -> '', 'bluesky', 'bluesky1234',
    -> '010-1234-5678', 'blue@aaa.com');
Query OK, 1 row affected (0.00 sec)

▶ member 테이블에 있는 자료를 출력한다.
mysql> select * from member;
+----+----------+-------------+---------------+--------------+
| id | username | userpass    | tel           | email        |
+----+----------+-------------+---------------+--------------+
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com |
+----+----------+-------------+---------------+--------------+
1 row in set (0.00 sec)

mysql> insert into member values(
    -> '', 'kimsk', 'kimsk0000', '010-3223-9876', 'kimsk@naver.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into member values(
    -> '', 'hongkd', '1234hongkd', '010-9878-6524', 'hong@daum.net');
Query OK, 1 row affected (0.00 sec)

mysql> select * from member;
+----+----------+-------------+---------------+-----------------+
| id | username | userpass    | tel           | email           |
+----+----------+-------------+---------------+-----------------+
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com    |
|  2 | kimsk    | kimsk0000   | 010-3223-9876 | kimsk@naver.com |
|  3 | hongkd   | 1234hongkd  | 010-9878-6524 | hong@daum.net   |
+----+----------+-------------+---------------+-----------------+
3 rows in set (0.00 sec)


▶ 필요한 컬럼에 자료를 넣고 싶다면 아래처럼 각 컬럼명을 써줘야 한다.
mysql> insert into member (username,userpass,tel,email) values(
    -> 'aaa', 'aaa1234', '010-5677-3456', 'aaa@aaa.com');
Query OK, 1 row affected (0.00 sec)

mysql> select  * from member;
+----+----------+-------------+---------------+-----------------+
| id | username | userpass    | tel           | email           |
+----+----------+-------------+---------------+-----------------+
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com    |
|  2 | kimsk    | kimsk0000   | 010-3223-9876 | kimsk@naver.com |
|  3 | hongkd   | 1234hongkd  | 010-9878-6524 | hong@daum.net   |
|  4 | aaa      | aaa1234     | 010-5677-3456 | aaa@aaa.com     |
+----+----------+-------------+---------------+-----------------+
4 rows in set (0.00 sec)


▶ id 가 3 이상인 자료를 출력하시오.
mysql> select *  from member where id >= 3; //id 값이 3이상인 값을 위한 조건을 작성해 준다.
+----+----------+------------+---------------+---------------+
| id | username | userpass   | tel           | email         |
+----+----------+------------+---------------+---------------+
|  3 | hongkd   | 1234hongkd | 010-9878-6524 | hong@daum.net |
|  4 | aaa      | aaa1234    | 010-5677-3456 | aaa@aaa.com   |
+----+----------+------------+---------------+---------------+
2 rows in set (0.00 sec)

 

▶ username 이 bluesky 이고 email 주소가 aaa@aaa.com 사용자를 출력
mysql> select * from member where username = 'bluesky' or email = 'aaa@aaa.com';
+----+----------+-------------+---------------+--------------+
| id | username | userpass    | tel           | email        |
+----+----------+-------------+---------------+--------------+
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com |
|  4 | aaa      | aaa1234     | 010-5677-3456 | aaa@aaa.com  |
+----+----------+-------------+---------------+--------------+
2 rows in set (0.00 sec)

▶ 자료를 내림차순으로 정렬해서 출력한다.
mysql> select *    from member order by id desc;
+----+----------+-------------+---------------+-----------------+
| id | username | userpass    | tel           | email           |
+----+----------+-------------+---------------+-----------------+
|  4 | aaa      | aaa1234     | 010-5677-3456 | aaa@aaa.com     |
|  3 | hongkd   | 1234hongkd  | 010-9878-6524 | hong@daum.net   |
|  2 | kimsk    | kimsk0000   | 010-3223-9876 | kimsk@naver.com |
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com    |
+----+----------+-------------+---------------+-----------------+
4 rows in set (0.00 sec)


▶ 자료를 삭제하고 자료 1개를 insert 한 후 ASC를 하지 않는다면  삭제된 자료에 들어가므로 정렬이 안된다.
mysql> delete from member where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from member;
+----+----------+-------------+---------------+---------------+
| id | username | userpass    | tel           | email         |
+----+----------+-------------+---------------+---------------+
|  1 | bluesky  | bluesky1234 | 010-1234-5678 | blue@aaa.com  |
|  3 | hongkd   | 1234hongkd  | 010-9878-6524 | hong@daum.net |
|  4 | aaa      | aaa1234     | 010-5677-3456 | aaa@aaa.com   |
+----+----------+-------------+---------------+---------------+
3 rows in set (0.00 sec)

mysql> insert into member values( '', 'bbb', password('1234'), '010-7899-2341', 'bbb@naver.com');
Query OK, 1 row affected (0.00 sec)

mysql> select *  from member;
+----+----------+------------------+---------------+---------------+
| id | username | userpass         | tel           | email         |
+----+----------+------------------+---------------+---------------+
|  1 | bluesky  | bluesky1234      | 010-1234-5678 | blue@aaa.com  |
|  5 | bbb      | 446a12100c856ce9 | 010-7899-2341 | bbb@naver.com |
|  3 | hongkd   | 1234hongkd       | 010-9878-6524 | hong@daum.net |
|  4 | aaa      | aaa1234          | 010-5677-3456 | aaa@aaa.com   |
+----+----------+------------------+---------------+---------------+
4 rows in set (0.00 sec)

mysql> select *  from member  order by id asc;
+----+----------+------------------+---------------+---------------+
| id | username | userpass         | tel           | email         |
+----+----------+------------------+---------------+---------------+
|  1 | bluesky  | bluesky1234      | 010-1234-5678 | blue@aaa.com  |
|  3 | hongkd   | 1234hongkd       | 010-9878-6524 | hong@daum.net |
|  4 | aaa      | aaa1234          | 010-5677-3456 | aaa@aaa.com   |
|  5 | bbb      | 446a12100c856ce9 | 010-7899-2341 | bbb@naver.com |
+----+----------+------------------+---------------+---------------+
4 rows in set (0.00 sec)

- id 5번을 2번으로 수정해보자.
mysql> update member set id = 2 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from member;
+----+----------+------------------+---------------+---------------+
| id | username | userpass         | tel           | email         |
+----+----------+------------------+---------------+---------------+
|  1 | bluesky  | bluesky1234      | 010-1234-5678 | blue@aaa.com  |
|  2 | bbb      | 446a12100c856ce9 | 010-7899-2341 | bbb@naver.com |
|  3 | hongkd   | 1234hongkd       | 010-9878-6524 | hong@daum.net |
|  4 | aaa      | aaa1234          | 010-5677-3456 | aaa@aaa.com   |
+----+----------+------------------+---------------+---------------+
4 rows in set (0.00 sec)

▶ hongkd 사용자의 전화번호를 010-7***-8*** 으로 수정한다.
mysql> update member set tel = '010-7***-8***' //*은 숫자를 입력해야 한다.
    -> where username = 'hongkd';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from member;
+----+----------+------------------+---------------+---------------+
| id | username | userpass         | tel           | email         |
+----+----------+------------------+---------------+---------------+
|  1 | bluesky  | bluesky1234      | 010-1???-5??? | blue@aaa.com  |
|  2 | bbb      | 446a12100c856ce9 | 010-7???-2??? | bbb@naver.com |
|  3 | hongkd   | 1234hongkd       | 010-7***-8*** | hong@daum.net |
|  4 | aaa      | aaa1234          | 010-5???-3??? | aaa@aaa.com   |
+----+----------+------------------+---------------+---------------+
4 rows in set (0.00 sec)

 

예전 공부할떄 사용했던 자료이며, 사용을 하지 않으면 잘 까먹기 때문에 가끔 명령어를 찾아보곤 한다

그래서 정리해 두는 자료이며, 나처럼 자주 명령어를 깜빡하는 사람이 있다면 도움이 되었으면 한다.