[mysql] 실습환경 준비 (이것이mysql이다. youtube 6장)

실습 DB생성

mysql> drop database if exists sqlDB; -- 만약 sqlDB가 존재하면 우선 삭제한다.
mysql> create database sqlDB;

USE sqlDB;

user table 생성

mysql> create table usertbl
    -> ( userid char(20) not null primary key,
    -> name varchar(20) not null,
    -> birthyear int not null,
    -> addr char(20) not null,
    -> mobile1 char(20),
    -> mobile2 char(20),
    -> height smallint,
    -> mdate datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc usertbl;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| userid    | char(20)    | NO   | PRI | NULL    |       |
| name      | varchar(20) | NO   |     | NULL    |       |
| birthyear | int(11)     | NO   |     | NULL    |       |
| addr      | char(20)    | NO   |     | NULL    |       |
| mobile1   | char(20)    | YES  |     | NULL    |       |
| mobile2   | char(20)    | YES  |     | NULL    |       |
| height    | smallint(6) | YES  |     | NULL    |       |
| mdate     | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

구매 테이블 생성

mysql> create table buytbl (

num int auto_increment not null primary key,

userid char(20) not null,

prodname char(20) not null,

groupname char(20), price int not null,

amount smallint not null,

foreign key (userid) references usertbl(userid));
Query OK, 0 rows affected (0.01 sec)

mysql> desc buytbl;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| num       | int(11)     | NO   | PRI | NULL    | auto_increment |
| userid    | char(20)    | NO   | MUL | NULL    |                |
| prodname  | char(20)    | NO   |     | NULL    |                |
| groupname | char(20)    | YES  |     | NULL    |                |
| price     | int(11)     | NO   |     | NULL    |                |
| amount    | smallint(6) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

테이블에 실습용 데이터 입력

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl(userid,prodname,groupname,price,amount)  VALUES('KBS', '운동화', NULL   , 30,   2);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl(userid,prodname,groupname,price,amount) VALUES('BBK', '운동화', NULL   , 30,   2);

확인

mysql> select * from usertbl;
+--------+-----------+-----------+--------+---------+---------+--------+---------------------+
| userid | name      | birthyear | addr   | mobile1 | mobile2 | height | mdate               |
+--------+-----------+-----------+--------+---------+---------+--------+---------------------+
| BBK    | 바비킴    |      1973 | 서울   | 010     | 0000000 |    176 | 2013-05-05 00:00:00 |
| EJW    | 은지원    |      1972 | 경북   | 011     | 8888888 |    174 | 2014-03-03 00:00:00 |
| JKW    | 조관우    |      1965 | 경기   | 018     | 9999999 |    172 | 2010-10-10 00:00:00 |
| JYP    | 조용필    |      1950 | 경기   | 011     | 4444444 |    166 | 2009-04-04 00:00:00 |
| KBS    | 김범수    |      1979 | 경남   | 011     | 2222222 |    173 | 2012-04-04 00:00:00 |
| KKH    | 김경호    |      1971 | 전남   | 019     | 3333333 |    177 | 2007-07-07 00:00:00 |
| LJB    | 임재범    |      1963 | 서울   | 016     | 6666666 |    182 | 2009-09-09 00:00:00 |
| LSG    | 이승기    |      1987 | 서울   | 011     | 1111111 |    182 | 2008-08-08 00:00:00 |
| SSK    | 성시경    |      1979 | 서울   | NULL    | NULL    |    186 | 2013-12-12 00:00:00 |
| YJS    | 윤종신    |      1969 | 경남   | NULL    | NULL    |    170 | 2005-05-05 00:00:00 |
+--------+-----------+-----------+--------+---------+---------+--------+---------------------+
10 rows in set (0.00 sec)

mysql> select * from buytbl;
+-----+--------+-----------+-----------+-------+--------+
| num | userid | prodname  | groupname | price | amount |
+-----+--------+-----------+-----------+-------+--------+
|   1 | KBS    | 운동화    | NULL      |    30 |      2 |
|   2 | KBS    | 노트북    | 전자      |  1000 |      1 |
|   3 | JYP    | 모니터    | 전자      |   200 |      1 |
|   4 | BBK    | 모니터    | 전자      |   200 |      5 |
|   5 | KBS    | 청바지    | 의류      |    50 |      3 |
|   6 | BBK    | 메모리    | 전자      |    80 |     10 |
|   7 | SSK    | 책        | 서적      |    15 |      5 |
|   8 | EJW    | 책        | 서적      |    15 |      2 |
|   9 | EJW    | 청바지    | 의류      |    50 |      1 |
|  10 | BBK    | 운동화    | NULL      |    30 |      2 |
|  11 | EJW    | 책        | 서적      |    15 |      1 |
|  12 | BBK    | 운동화    | NULL      |    30 |      2 |
+-----+--------+-----------+-----------+-------+--------+
12 rows in set (0.00 sec)

mysql>