[mysql] 기초

mysql 간단하게 설치 하기 위해 bitnami에서 wamp를 설치하였다.

WAMP는 window에 apache, mysql, php를 설치해주는 프로그램이다. 

https://bitnami.com/stack/wamp 설치 후 mysql 세팅한다. (생활코딩 mysql window 설치 방법 참고하였다)

 

1. cmd에서 mysql에 접속하기 mysql -uroot -p를 입력후 비밀번호를 입력한다.

2. SHOW DATABASES  : mysql의 데이터베이스들을 보여준다.

Microsoft Windows [Version 10.0.17134.165]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\user>cd C:\Bitnami\wampstack-7.1.19-1\mysql\bin
C:\Bitnami\wampstack-7.1.19-1\mysql\bin>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| userdatabase       |
+--------------------+
5 rows in set (0.01 sec)

3. USE 스키마명  : 사용할 스키마로 입장 한다.

mysql> use userdatabase;
Database changed

4. CREATE TABLE 테이블명 (...) : 입장한 스키마에서 테이블을 생성한다.

mysql> CREATE TABLE topic(
    -> id INT(11) NOT NULL AUTO_INCREMENT,
    -> title VARCHAR(100) NOT NULL,
    -> description TEXT NULL,
    -> created DATETIME NOT NULL,
    -> author VARCHAR(30) NULL,
    -> profile VARCHAR(100) NULL,
    -> PRIMARY KEY(id));
Query OK, 0 rows affected (0.84 sec)

5. SHOW TABLES : 해당 스키마의 테이블들을 보여준다. 

mysql> SHOW TABLES;
+------------------------+
| Tables_in_userdatabase |
+------------------------+
| topic                  |
+------------------------+
1 row in set (0.00 sec)

6. DESC 테이블명 : 테이블의 상세 구조를 확인 

mysql> DESC topic;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| title       | varchar(100) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
| created     | datetime     | NO   |     | NULL    |                |
| author      | varchar(30)  | YES  |     | NULL    |                |
| profile     | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

7. INSERT, SELECT 사용

mysql> INSERT INTO topic (title, description, created, author, profile) values('mysql', 'mysql is...', now(), 'misom', 'developer');
Query OK, 1 row affected (0.01 sec)

 
mysql> select topic;
ERROR 1054 (42S22): Unknown column 'topic' in 'field list'
mysql> select * from topic;
+----+-------+-------------+---------------------+--------+-----------+
| id | title | description | created             | author | profile   |
+----+-------+-------------+---------------------+--------+-----------+
|  1 | mysql | mysql is... | 2018-07-21 20:20:42 | misom  | developer |
+----+-------+-------------+---------------------+--------+-----------+
1 row in set (0.00 sec)