DB 테이블 실습

조건

-. 버추얼박스에 MARIA DB 서버 생성함

 

 

* 로컬 PC 실행해서 MARIA DB 서버로 접근하여 실습 진행

 

1.C:\Users\YONSAI>ssh vuser@localhost
2.vuser@localhost's password: 비밀번호 입력
3. vuser@ubuntu:~$ sudo mariadb
4. [sudo] password for vuser: 비밀번호 입력
5.MariaDB [(none)]> show databases

출력 :
+--------------------+
| Database           |
+--------------------+
| Chatbot            |
| Mydata             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

6. 데이터베이스 생성 MariaDB [(none)]> create database testDB;

+--------------------+
| Database           |
+--------------------+
| Chatbot            |
| Mydata             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testDB             |
+--------------------+

7.데이터 베이스 삭제 MariaDB [(none)]> drop database testDB;

+--------------------+
| Database           |
+--------------------+
| Chatbot            |
| Mydata             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

 

8.데이터 베이스 사용 선언

MariaDB [(none)]> use testDB;

->Database changed

 

9. none-> testDB 로 변경됨

-> MariaDB [testDB]>

 

10.테이블 생성(신규로생성)

create table Persons( 

PersonID int, 

LastName varchar(255), 

FirstName varchar(255), 

Address varchar(255), 

City varchar(255) 

);

 

11.테이블 생성(기존 테이블을 사용하여 생성)

create table TestTable as select LastName, City from Persons;

 

12. MariaDB [testDB]> show tables;
+------------------+
| Tables_in_testDB |
+------------------+
| Persons          |
| TestTable        |
+------------------+

 

13.컬럼에 데이터가 넣기 MariaDB [testDB]> insert into Persons(LastName) values("하늘");

 

MariaDB [testDB]> select * from Persons;
+----------+----------+-----------+---------+------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+------+
|     NULL | 하늘     | NULL      | NULL    | NULL |
+----------+----------+-----------+---------+------+

 

14.테이블 데이터만 삭제(하늘 삭제됨)

MariaDB [testDB]> truncate table Persons;  

 

15. 데이터가 삭제 되서 없음

MariaDB [testDB]> select * from Persons;

 

16.테이블 칼럼 수정(Email 칼럼 추가)

MariaDB [testDB]> ALTER TABLE Persons ADD Email varchar(255);

 

MariaDB [testDB]> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID  | int(11)      | YES  |     | NULL    |       |
| LastName  | varchar(255) | YES  |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
| Email     | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

 

17. 테이블 칼럼 삭제

MariaDB [testDB]> alter table Persons drop Email;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID  | int(11)      | YES  |     | NULL    |       |
| LastName  | varchar(255) | YES  |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

 

18. 칼럼 타입 변경

alter table Persons modify column City varchar(100);

 

MariaDB [testDB]> desc Persons;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID  | int(11)      | YES  |     | NULL    |       |
| LastName  | varchar(255) | YES  |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | YES  |     | NULL    |       |
| City      | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

 

19.데이트 칼럼 추가

MariaDB [testDB]> alter table Persons add DateOfBirth date;

MariaDB [testDB]> desc Persons;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| PersonID    | int(11)      | YES  |     | NULL    |       |
| LastName    | varchar(255) | YES  |     | NULL    |       |
| FirstName   | varchar(255) | YES  |     | NULL    |       |
| Address     | varchar(255) | YES  |     | NULL    |       |
| City        | varchar(100) | YES  |     | NULL    |       |
| DateOfBirth | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

 

20.null 미허용으로 설정 변경

MariaDB [testDB]> alter table Persons modify LastName varchar(255) NOT NULL;

MariaDB [testDB]> desc Persons;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| PersonID    | int(11)      | YES  |     | NULL    |       |
| LastName    | varchar(255) | NO   |     | NULL    |       |
| FirstName   | varchar(255) | YES  |     | NULL    |       |
| Address     | varchar(255) | YES  |     | NULL    |       |
| City        | varchar(100) | YES  |     | NULL    |       |
| DateOfBirth | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

 

21.null 미허용으로 변경
MariaDB [testDB]> alter table Persons modify PersonID int NOT NULL;
MariaDB [testDB]> desc Persons;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| PersonID    | int(11)      | NO   |     | NULL    |       |
| LastName    | varchar(255) | NO   |     | NULL    |       |
| FirstName   | varchar(255) | YES  |     | NULL    |       |
| Address     | varchar(255) | YES  |     | NULL    |       |
| City        | varchar(100) | YES  |     | NULL    |       |
| DateOfBirth | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

 

22.프라이머리 키 및 유니크 설정
alter table Persons add constraint pk_person primary key (PersonId, LastName);
alter table Persons add constraint uc_person unique (PersonId, LastName);

 

MariaDB [testDB]> desc Persons;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| PersonID    | int(11)      | NO   | PRI | NULL    |       |
| LastName    | varchar(255) | NO   | PRI | NULL    |       |
| FirstName   | varchar(255) | YES  |     | NULL    |       |
| Address     | varchar(255) | YES  |     | NULL    |       |
| City        | varchar(100) | YES  |     | NULL    |       |
| DateOfBirth | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

 

23. 테이블 재 생성(외래키)

create table Orders ( 

OrderID int NOT NULL, 

OrderNumber int NOT NULL, 

PersonID int, PRIMARY KEY(OrderID), 

FOREIGN KEY(PersonID) REFERENCES Persons(PersonID));

 

MariaDB [testDB]> show tables;
+------------------+
| Tables_in_testDB |
+------------------+
| Orders           |
| Persons          |
+------------------+

Orders_ibfk_1; -> 이게 생성됨, cmd에서는 보이지 않음

 

 

24. 외래키 설정  삭제 하기
alter table Orders drop foreign key Orders_ibfk_1;