MYSQL - 테이블

테이블

테이블 만들기

create table 테이블 이름;

CREATE TABLE myMember (
memberID int(10) unsigned auto_increment,
youEmail varchar(40) NOT NULL,
youName varchar(20) NOT NULL,
youPass varchar(20) NOT NULL,
youBirth int(20) NOT NULL,
youAge int(20) NOT NULL,
youPhone int(20) NOT NULL,
regTime int(11) NOT NULL,
PRIMARY KEY (memberID)
) charset=utf8 comment "회원 정보 테이블";

CREATE TABLE myReview (
reviewID int(10) unsigned auto_increment,
memberID int(10) unsigned,
youCont tinytext NOT NULL,
regTime int(11) NOT NULL,
PRIMARY KEY (reviewID)
) charset=utf8;

INSERT INTO myreview(memberID, youCont, regTime) VALUES('4', '정말 감사합니다. 너무 좋네요!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('7', '이거 정말 사야돼!!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('1', '이거 가방 오나전 별루예요.. 사진빨', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('9', '이거 좋다! 좋다! 좋다!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('12', '마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('3', '오늘은 정말 마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('16', '가성비 짱입니다. 당장 사세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('19', '안녕하세요. 안녕하세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('2', '안녕못해요. 안녕못해요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('14', '빨리빨리빨리.', '12345');

전체 테이블 보기

show tables;

show tables;
+--------------------+
| Tables_in_sample01 |
+--------------------+
| mymember           |
+--------------------+
1 row in set (0.00 sec)

 

테이블 속성 보기

desc 테이블 이름;

desc mymember;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| myMemberID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| youEmail   | varchar(40)      | NO   |     | NULL    |                |
| youName    | varchar(12)      | NO   |     | NULL    |                |
| youPass    | varchar(20)      | NO   |     | NULL    |                |
| youBirth   | varchar(12)      | NO   |     | NULL    |                |
| regTime    | int(11)          | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

테이블 삭제

drop table 삭제할 테이블 이름;

create table mymember2 (
-> myMemberID int(10) NOT NULL comment "회원 아이디");
Query OK, 0 rows affected (0.08 sec)

mysql> show tables;
+--------------------+
| Tables_in_sample01 |
+--------------------+
| mymember           |
| mymember2          |
+--------------------+
2 rows in set (0.00 sec)

mysql> desc mymember2;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| myMemberID | int(10) | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> drop table mymember2;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_sample01 |
+--------------------+
| mymember           |
+--------------------+
1 row in set (0.00 sec)

테이블 복사

테이블 구조, 테이터 복사

create table 새로운 테이블명 select * from 복사할 테이블명;

create table myMember1 select * from myMember;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

select * from myMember1;
+----------+-----------------------+---------+-----------+----------+--------+-------------+----------+
| memberID | youEmail              | youName | youPass   | youBirth | youAge | youPhone    | regTime  |
+----------+-----------------------+---------+-----------+----------+--------+-------------+----------+
|        1 | ohwehoh@naver.com     | 이연우  | dldus1358 | 19950720 |     28 | 01012341234 | 12341234 |
|        2 | ohwehoh@naver.com     | 김현우  | 159753    | 19950720 |     28 | 01012341234 | 40250371 |
|        3 | ohwehoh@naver.com     | 박지선  | 1234      | 19950720 |     28 | 01012341234 | 12341234 |
|        4 | ohwehoh@naver.com     | 류성일  | ryuPass   | 19950720 |     28 | 01012341234 |     1440 |
|        5 | ohwehoh@naver.com     | 최가영  | 12345678  | 19950720 |     28 | 01012341234 | 12345678 |
|        6 | ohwehoh@naver.com     | 박근희  | 1234qwer  | 19950720 |     28 | 01012341234 | 12345678 |
|        7 | ohwehoh@naver.com     | 배석찬  | 1234      | 19950720 |     28 | 01012341234 |  3270327 |
|        8 | ohwehoh@naver.com     | 최근영  | 1234      | 19950720 |     28 | 01012341234 |      234 |
|        9 | ohwehoh@naver.com     | 오세린  | 1234      | 19950720 |     28 | 01012341234 | 12345678 |
|       10 | ohwehoh@naver.com     | 조운섭  | qweasdf   | 19950720 |     28 | 01012341234 | 12345678 |
|       11 | ohwehoh@naver.com     | 천우진  | 12asdf    | 19950720 |     28 | 01012341234 | 45678941 |
|       12 | ohwehoh@naver.com     | 박성우  | 123456asd | 19950720 |     28 | 01012341234 | 54216451 |
|       13 | ohwehoh@naver.com     | 송준섭  | qweasdf   | 19950720 |     28 | 01012341234 | 21846351 |
|       14 | ohwehoh@naver.com     | 박근혁  | alcls2860 | 19950720 |     28 | 01012341234 | 12345678 |
|       15 | ohwehoh@naver.com     | 박종호  | 1234qwer  | 19950720 |     28 | 01012341234 | 10293857 |
|       16 | ohwehoh@naver.com     | 김정식  | 1234      | 19950720 |     28 | 01012341234 | 20220314 |
|       17 | ohwehoh@naver.com     | 한승보  | 1234      | 19950720 |     28 | 01012341234 |     1234 |
|       18 | ohwehoh@naver.com     | 김지섭  | 1234      | 19950720 |     28 | 01012341234 | 20220314 |
|       19 | ohwehoh@naver.com     | 김현우  | 159753    | 19950720 |     28 | 01012341234 | 40250371 |
|       20 | ohwehoh@naver.com     | 박종호  | 1234qwer  | 19950720 |     28 | 01012341234 | 10293857 |
+----------+-----------------------+---------+-----------+----------+--------+-------------+----------+
20 rows in set (0.00 sec)

테이블 구조 복사

create table 새로운 테이블명 LIKE 복사할 테이블명;

create table myMember2 LIKE myMember;

테이블 데이터 복사

INSERT INTO 새로운 테이블명 SELECT * FROM 복사할 테이블명;

INSERT INTO myMember3 SELECT * FROM myMember;