테이블 만들기

CREATE TABLE 이름;
CREATE TABLE members(
memberID int(10) unsigned auto_increment,
youName varchar(10) NOT NULL,
youPass varchar(10) NOT NULL,
youAge int(5) NOT NULL,
youBirth int(5) NOT NULL,
regTime int(30) NOT NULL,
PRIMARY KEY(memberID)
) charset=utf8;

테이블 구조 보기

DESC 이름;
mysql>DESC members;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| memberID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| youName  | varchar(10)      | NO   |     | NULL    |                |
| youPass  | varchar(10)      | NO   |     | NULL    |                |
| youAge   | int(5)           | NO   |     | NULL    |                |
| youBirth | int(5)           | NO   |     | NULL    |                |
| regTime  | int(30)          | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

테이블 데이터 입력

INSERT INTO 테이블 이름(필드명) VALUES(값);

INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("황상연", "1234", "33", "990304", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("남윤서", "1234", "19", "060121", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김진우", "1234", "21", "040416", "1231231233");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("최선화", "1234", "26", "990123", "1104781152");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("서대원", "1234", "26", "011019", "1111100000");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김현민", "1234", "32", "930604", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("황인지", "1234", "20", "990315", "1474187756");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박정민", "1234", "29", "961017", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("강서연", "1234", "27", "980420", "0123456744");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("이재혁", "1215", "24", "010712", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박소현", "1234", "29", "960917", "1231223123");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김혜지", "1234", "28", "951002", "0123456789");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("강경민", "1234", "28", "970611", "1023952467");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("안지은", "1234", "33", "840816", "1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박진아", "1232", "33", "990304", "1023958375");

테이블 삭제

DROP TABLE 이름;
mysql>DROP TABLE members;
Query OK, 0 rows affected (0.01 sec)

테이블 초기화

TRUNCATE TABLE 이름;
mysql>TRUNCATE TABLE members;
Query OK, 0 rows affected (0.01 sec)

테이블 합치기(JOIN)

SELECT [필드명] FROM [테이블.앨리어스] JOIN [테이블.앨리어스] ON [기준이 되는 조건문]
mysql>SELECT m.youName, m.youAge, r.youText, r.regTime FROM members m JOIN reviews r ON (m.memberID = r.memberID);  // 필요한 필드명 적고 앞에m은 멤버스를 지칭
    +---------+--------+---------------------------------------+------------+
    | youName | youAge | youText                               | regTime    |
    +---------+--------+---------------------------------------+------------+
    | 김진우  |     21 | 매우 만족스러운 경험이었습니다.         | 1023958375 |
    | 황상연  |     33 | 멋진 내용입니다.                       | 1023958375 |
    | 김현민  |     32 | 이런 피드백을 받을 수 있어 기쁩니다.    | 1023958375 |
    | 황인지  |     20 | 훌륭한 서비스였습니다.                 |  123456789 |
    | 강경민  |     28 | 매우 도움이 되었습니다.                | 1023958375 |
    | 서대원  |     26 | 좋은 경험을 공유해 주셔서 감사합니다.   | 1023958375 |
    | 김현민  |     32 | 귀중한 의견 감사드립니다.              | 1023958375 |
    | 김진우  |     21 | 이용해 주셔서 감사합니다.              | 1023958375 |
    | 김현민  |     32 | 소중한 피드백 감사합니다.              | 1023958375 |
    | 황상연  |     33 | 대단히 만족스러웠습니다.               | 1023958375 |
    +---------+--------+---------------------------------------+------------+
    10 rows in set (0.00 sec)

create table members(
memberID int(10) unsigned auto_increment,
youName varchar(10) NOT NULL,
youPass varchar(10) NOT NULL,
youAge int(5) NOT NULL,
youBirth int(5) NOT NULL,
regTime int(30) NOT NULL,
PRIMARY KEY(memberID)
) charset=utf8;


INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("황상연", "1234", "33", "990304",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("남윤서", "1234", "19", "060121",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김진우", "1234", "21", "040416",
"1231231233");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("최선화", "1234", "26", "990123",
"1104781152");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("서대원", "1234", "26", "011019",
"1111100000");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김현민", "1234", "32", "930604",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("황인지", "1234", "20", "990315",
"1474187756");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박정민", "1234", "29", "961017",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("강서연", "1234", "27", "980420",
"0123456744");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("이재혁", "1215", "24", "010712",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박소현", "1234", "29", "960917",
"1231223123");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("김혜지", "1234", "28", "951002",
"0123456789");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("강경민", "1234", "28", "970611",
"1023952467");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("안지은", "1234", "33", "840816",
"1023958375");
INSERT INTO members(youName, youPass, youAge, youBirth, regTime) VALUES("박진아", "1232", "33", "990304",
"1023958375");


create table reviews(
reviewID int(10) unsigned auto_increment,
memberID int(10) unsigned,
youText tinytext NOT NULL,
regTime int(30) NOT NULL,
PRIMARY KEY(reviewID)
) charset=utf8;


INSERT INTO reviews(memberID, youText, regTime) VALUES("3", "매우 만족스러운 경험이었습니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("1", "멋진 내용입니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("6", "이런 피드백을 받을 수 있어 기쁩니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("7", "훌륭한 서비스였습니다.", "0123456789");
INSERT INTO reviews(memberID, youText, regTime) VALUES("13", "매우 도움이 되었습니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("5", "좋은 경험을 공유해 주셔서 감사합니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("6", "귀중한 의견 감사드립니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("3", "이용해 주셔서 감사합니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("6", "소중한 피드백 감사합니다.", "1023958375");
INSERT INTO reviews(memberID, youText, regTime) VALUES("1", "대단히 만족스러웠습니다.", "1023958375");