MySql DDL 기초 - CREATE 외 (기초)

Agnes Park·2022년 3월 19일
0

DATABASE

목록 보기
2/9

1. 기본

  • SQL 명령문은 대소문자 가리지 않습니다
  • 문자열 은 ' ~ ' (홀따옴표)로 감쌈, 문자열 내의 내용은 대소문자 가림
  • 여러줄에 걸쳐 명령문 입력 가능
  • 명령문이 끝나면 반드시 ; <- 입력
  • COMMIT; 을 실행하기 전까지는 임시저장만 되는 것임, COMMIT; 을 해야만 데이터베이스의 내용이 업데이트 됨. (ORACLE 의 경우)
    (MySQL 의 경우 기본적으로 auto-commit 한다)

2. 데이터베이스 접속

커맨드 라인에서

> mysql -u [사용자아이디] -p [데이터베이스이름]

3. 사용자 확인

현재 접속한 사용자 확인

> SELECT user()

생성된 사용자 정보 확인 : (root) 계정에서

SELECT user, host FROM mysql.user;

4. CREATE DATABASE / DROP DATABASE

CREATE DATABASE [데이터베이스명] ;         <- 데이터 베이스 생성
CREATE DATABASE [데이터베이스명]  DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;         <- 유니코드 사용 데이터 베이스 생성
DROP DATABASE [IF EXISTS] [데이터베이스명] ;         <- 데이터 베이스 삭제\

5. SHOW DATABASES;

  • 현재 데이터 베이스 조회 / 열람
  • 기본적으로 설치된 DB
    - information_schema
    - mysql : MySql 관련 정보 세팅 (사용자 계정…)
    - performance_schema
  • 샘플 DB
    - sakila
    - world : 지역 정보 (국가, 도시..)

6. CREATE USER : 사용자 생성

CREATE USER  ‘[사용자아이디]’  IDENTIFIED BY ‘[암호]’
CREATE USER  ‘[사용자아이디]’@’[호스트]’  IDENTIFIED BY ‘[암호]’

[예시]

CREATE USER 'myuser' IDENTIFIED BY '1234';
CREATE USER 'myuser'@'127.0.0.1' IDENTIFIED BY '1234';
CREATE USER 'myuser'@'%' IDENTIFIED BY '1234';
CREATE USER 'myuser'@'localhost' IDENTIFIED BY '1234';

7. ALTER USER ~ : 비밀번호 변경

ALTER USER myuser IDENTIFIED BY '1111'

[예시]

USE mysql
UPDATE user SETPASSWORD=PASSWORD('1111') 
WHERE user='myuser';
FLUSH privileges;

8. 권한설정

grant all privileges on 디비명.테이블명 to '사용자'@'접속지 주소' (with grant option);
flush privileges;      ← grant 명령 후에 flush 를 해주어야 실제 적용됨.

[예시]

# '사용자'가 111.222.333.0~255 에서 모든 디비와 테이블에 접속하도록 허용
grant all privileges on *.* to '사용자'@'111.222.333.%';

# '사용자'가 111.222.333.444 에서 db1 디비의 모든 테이블에 접속하도록 허용
grant all privileges on db1.* to '사용자'@'111.222.333.444';

# '사용자'가  localhost 에서 db1 디비의 table1에 접속하도록 허용
grant all privileges on db1.table1 to '사용자'@'localhost';

(8.x <= 에선 identified by 생략해야 함)

9. 권한확인

SHOW GRANTS FOR '사용자'@'호스트';
ex)  SHOW GRANTS FOR 'myuser'@'localhost';

10. 권한 삭제

revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';

11. DROP USER : 사용자 삭제

DROP USER [사용자아이디];
DROP USER [사용자아이디]@[호스트];

[예시]

USE mysql;
DELETE FROM user WHERE user =   ‘[사용자아이디]’;
FLUSH PRIVILEGES;

12. 접속 전환

> system mysql -u myuser -p

system 명령으로 커맨드 명령어 사용 가능 : ex) dir

13. USE : 데이터 베이스 선택

  • 현재 데이터 베이스 조회 / 열람
  • 기본적으로 설치된 DB
    • information_schema
    • mysql : MySql 관련 정보 세팅 (사용자 계정…)
    • performance_schema
    • world : 지역 정보 (국가, 도시..)

14. STATUS ;

‘현재 데이터 베이스’ 정보를 보기

15. SHOW TABLES;

현재 데이터베이스 내의 테이블들을 리스트

16. CREATE TABLE : 테이블 생성

  • PRIMARY KEY : 고유한 키값을 같는 필드
  • AUTO_INCREMENT : 레코드가 생성될때마다 자동증가
  • NOT NULL : Null 값 허용 안함

MySQL 필드의 데이터 타입

  • 숫자형 필드 : TINYINT, INT, FLOAT...
  • 문자형 필드 : CHAR, VARCHAR, TEXT, ENUM...
  • 날짜형 필드 : DATE, DATETIME, TIMESTAMP ...

특히 많이 사용되는 타입

  • INT : 4bytes 정수를 저장할때 사용
  • CHAR(n) : 문자의 수가 n 개인 문자열을 저장할때 사용
  • VARCHAR(n) 문자의 수가 최대 n 개인 문자열을 저장할때 사용
  • TEXT : 문자의 수가 최대 65535 개인 문자열을 저장할때 사용

http://fendee.egloos.com/3961946

[예시]
use 같은 명령어를 테이블 명이나 컬럼명으로 할 경우~ (backtick) 으로 감싼다. 

create table items (
	id int(10) not null auto_increment,
	content varchar(200),
	created_on date,
	due_date date,
	`use` int(1) not null default 1,
	primary key(id)
)
collate = 'utf8_general_ci'
engine=MyISAM;
INSERT INTO items (content, created_on, due_date) values ('웅파미팅', '2012-09-23', '2012-09-24');
INSERT INTO items (content, created_on, due_date) values ('스터디', '2012-09-23', '2012-09-25');

17. 오타등으로 에러발생 주의

에러메세지 꼭 읽자!

  • 띄어쓰기, 콤마, 따옴표, 괄호 등등… 구문오류 발생 가능성 매우 높다,.
  • syntax 에러 : 구문에러
  • near '어쩌구' 문장의 앞으로 보라!
mysql> CREATE TABLE items (
    ->      id int(10)
    ->      content varchar(200),
    ->      created_on date
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'content varchar(200),
     created_on date
)' at line 3
CREATE TABLE phonebook (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  phonenum VARCHAR(20) DEFAULT '010-0000-000',
  email VARCHAR(100),
  regDate DATETIME DEFAULT now()
);

18. ALTER TABLE ~ MODIFY COLUMN: 테이블 수정

ALTER TABLE phonebook
MODIFY COLUMN name varchar(50);

ALTER TABLE phonebook
MODIFY COLUMN phonenum varchar(35) UNIQUE;

19. ALTER TABLE ~ ADD COLUMN: 테이블 추가

ALTER TABLE phonebook
ADD COLUMN age INT(3) DEFAULT 1 CHECK(age >= 0);
CREATE TABLE phonebook (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  phonenum VARCHAR(20) DEFAULT '010-0000-000',
  email VARCHAR(100),
  regDate DATETIME DEFAULT now()
);

20. ALTER TABLE ~ RENAME : 테이블 이름 변경

ALTER TABLE phonebook
RENAME phonedict;

ALTER TABLE phonedict
RENAME phonebook;

21. ALTER TABLE ~ DROP: 테이블 컬럼 삭제

ALTER TABLE phonebook
DROP email;

22. ALTER TABLE ~ MODIFY COLUMN 컬럼 이동

ALTER TABLE phonebook
MODIFY COLUMN age int(3) DEFAULT 1 CHECK(age >= 0) AFTER phonenum;
ALTER TABLE phonebook
MODIFY COLUMN age int(3) FIRST;\

23. ALTER TABLE ~ CHANGE COLUMN 컬럼 이름변경

ALTER TABLE phonebook
CHANGE COLUMN phonenum number varchar(20) NOT NULL;

24. DROP TABLE

테이블 삭제

DROP TABLE [IF EXISTS] [테이블 명] ;         <- 테이블 삭제

25. DESC

DESC [테이블 명] ;         <- 테이블 열 구조 보기

[코드 실습]

Microsoft Windows [Version 10.0.19043.1466]
(c) Microsoft Corporation. All rights reserved.

C:\Users\박규언>cd C:\Program Files\MySQL\MySQL Server 8.0\bin

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u myuser111 -p mydb111
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 tables;
+-------------------+
| Tables_in_mydb111 |
+-------------------+
| items             |
| phonebook         |
| test_book         |
| test_movie        |
+-------------------+
4 rows in set (0.08 sec)

mysql> DROP table phonebook;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE phonebook(
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(80) NOT NULL,
    -> phonenum varchar(20) DEFAULT '010-0000-0000',
    -> email varchar(100),
    -> regdate datetime DEFAULT now()
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO phonebook VALUES(4, 'jake', '010-1234-5678', 'test@test.com', '2021-10-06 21:06:23');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM phonebook;
+----+------+---------------+---------------+---------------------+
| id | name | phonenum      | email         | regdate             |
+----+------+---------------+---------------+---------------------+
|  4 | jake | 010-1234-5678 | test@test.com | 2021-10-06 21:06:23 |
+----+------+---------------+---------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO phonebook (id, name, phonenum)
    -> VALUES (3, '홍길동', '010-1111-2222');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM phonebook;
+----+--------+---------------+---------------+---------------------+
| id | name   | phonenum      | email         | regdate             |
+----+--------+---------------+---------------+---------------------+
|  3 | 홍길동 | 010-1111-2222 | NULL          | 2022-03-18 01:38:38 |
|  4 | jake   | 010-1234-5678 | test@test.com | 2021-10-06 21:06:23 |
+----+--------+---------------+---------------+---------------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO phonebook (name, phonenum)
    -> VALUES ('김영찬', '8888-9999');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM phonebook;
+----+--------+---------------+---------------+---------------------+
| id | name   | phonenum      | email         | regdate             |
+----+--------+---------------+---------------+---------------------+
|  3 | 홍길동 | 010-1111-2222 | NULL          | 2022-03-18 01:38:38 |
|  4 | jake   | 010-1234-5678 | test@test.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL          | 2022-03-18 01:40:44 |
+----+--------+---------------+---------------+---------------------+
3 rows in set (0.00 sec)

INSERT INTO phonebook (phonenum)
VALUES ('4444-3999');
INSERT INTO' at line 2
mysql> desc phonebook;
+----------+--------------+------+-----+-------------------+-------------------+
| Field    | Type         | Null | Key | Default           | Extra             |
+----------+--------------+------+-----+-------------------+-------------------+
| id       | int          | NO   | PRI | NULL              | auto_increment    |
| name     | varchar(80)  | NO   |     | NULL              |                   |
| phonenum | varchar(20)  | YES  |     | 010-0000-0000     |                   |
| email    | varchar(100) | YES  |     | NULL              |                   |
| regdate  | datetime     | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)

mysql> INSERT INTO phonebook VALUES
    -> (1, '전우길', '11-111-111', 'Cheon@mail.com', '2021-08-01');INSERT INTO phonebook VALUES
Query OK, 1 row affected (0.00 sec)

    -> (2, '제임스', '22)222-222', null, now());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM phonebook;
+----+--------+---------------+----------------+---------------------+
| id | name   | phonenum      | email          | regdate             |
+----+--------+---------------+----------------+---------------------+
|  1 | 전우길 | 11-111-111    | Cheon@mail.com | 2021-08-01 00:00:00 |
|  2 | 제임스 | 22)222-222    | NULL           | 2022-03-18 01:48:27 |
|  3 | 홍길동 | 010-1111-2222 | NULL           | 2022-03-18 01:38:38 |
|  4 | jake   | 010-1234-5678 | test@test.com  | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL           | 2022-03-18 01:40:44 |
+----+--------+---------------+----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET name = 'jesse' WHERE id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+----------------+---------------------+
| id | name   | phonenum      | email          | regdate             |
+----+--------+---------------+----------------+---------------------+
|  1 | 전우길 | 11-111-111    | Cheon@mail.com | 2021-08-01 00:00:00 |
|  2 | 제임스 | 22)222-222    | NULL           | 2022-03-18 01:48:27 |
|  3 | 홍길동 | 010-1111-2222 | NULL           | 2022-03-18 01:38:38 |
|  4 | jesse  | 010-1234-5678 | test@test.com  | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL           | 2022-03-18 01:40:44 |
+----+--------+---------------+----------------+---------------------+
5 rows in set (0.01 sec)

mysql> UPDATE phonebook SET email = 'jesse@naver.com', phonenum = '052)333-4555' WHERE id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  1 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
|  2 | 제임스 | 22)222-222    | NULL            | 2022-03-18 01:48:27 |
|  3 | 홍길동 | 010-1111-2222 | NULL            | 2022-03-18 01:38:38 |
|  4 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL            | 2022-03-18 01:40:44 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET email = 'jesse@naver.com', phonenum = '052)333-4555' WHERE id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  1 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
|  2 | 제임스 | 22)222-222    | NULL            | 2022-03-18 01:48:27 |
|  3 | 홍길동 | 010-1111-2222 | NULL            | 2022-03-18 01:38:38 |
|  4 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL            | 2022-03-18 01:40:44 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET email = 'jesse@naver.com', phonenum = '052)333-4555' WHERE id = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE phonebook SET id = id + 10 WHERE id <= 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  4 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL            | 2022-03-18 01:40:44 |
| 11 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
| 12 | 제임스 | 22)222-222    | NULL            | 2022-03-18 01:48:27 |
| 13 | 홍길동 | 010-1111-2222 | NULL            | 2022-03-18 01:38:38 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET email = 'test@mail.com' WHERE email = null;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  4 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | NULL            | 2022-03-18 01:40:44 |
| 11 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
| 12 | 제임스 | 22)222-222    | NULL            | 2022-03-18 01:48:27 |
| 13 | 홍길동 | 010-1111-2222 | NULL            | 2022-03-18 01:38:38 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET email = 'test@mail.com' WHERE email is null;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  4 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
|  5 | 김영찬 | 8888-9999     | test@mail.com   | 2022-03-18 01:40:44 |
| 11 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
| 12 | 제임스 | 22)222-222    | test@mail.com   | 2022-03-18 01:48:27 |
| 13 | 홍길동 | 010-1111-2222 | test@mail.com   | 2022-03-18 01:38:38 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> UPDATE phonebook SET id = id * 2;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM phonebook;
+----+--------+---------------+-----------------+---------------------+
| id | name   | phonenum      | email           | regdate             |
+----+--------+---------------+-----------------+---------------------+
|  8 | jesse  | 052)333-4555  | jesse@naver.com | 2021-10-06 21:06:23 |
| 10 | 김영찬 | 8888-9999     | test@mail.com   | 2022-03-18 01:40:44 |
| 22 | 전우길 | 11-111-111    | Cheon@mail.com  | 2021-08-01 00:00:00 |
| 24 | 제임스 | 22)222-222    | test@mail.com   | 2022-03-18 01:48:27 |
| 26 | 홍길동 | 010-1111-2222 | test@mail.com   | 2022-03-18 01:38:38 |
+----+--------+---------------+-----------------+---------------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM phonebook WHERE id > 25;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM phonebook;
+----+--------+--------------+-----------------+---------------------+
| id | name   | phonenum     | email           | regdate             |
+----+--------+--------------+-----------------+---------------------+
|  8 | jesse  | 052)333-4555 | jesse@naver.com | 2021-10-06 21:06:23 |
| 10 | 김영찬 | 8888-9999    | test@mail.com   | 2022-03-18 01:40:44 |
| 22 | 전우길 | 11-111-111   | Cheon@mail.com  | 2021-08-01 00:00:00 |
| 24 | 제임스 | 22)222-222   | test@mail.com   | 2022-03-18 01:48:27 |
+----+--------+--------------+-----------------+---------------------+
4 rows in set (0.00 sec)

mysql> drop table if exists t_emp;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE t_emp (
    ->   EMPNO     INT NOT NULL,
    ->   ENAME     VARCHAR(10),
    ->   JOB       VARCHAR(9),
    ->   MGR       INT,
    ->   HIREDATE  DATETIME,
    ->   SAL       INT,
    ->   COMM      INT,
    ->   DEPTNO    INT,
    ->   PRIMARY KEY(EMPNO)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO t_emp VALUES (7369,'SMITH','CLERK',7902,'1990-12-17',800,NULL,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_emp VALUES (7499,'ALLEN','SALESMAN',7698,'1991-02-20',1600,300,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7521,'WARD','SALESMAN',7698,'1992-02-22',1250,500,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7566,'JONES','MANAGER',7839,'1991-04-02',2975,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7654,'MARTIN','SALESMAN',7698,'1991-09-28',1250,1400,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7698,'BLAKE','MANAGER',7839,'1991-05-01',2850,NULL,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7782,'CLARK','MANAGER',7839,'1991-06-09',2450,NULL,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7788,'SCOTT','ANALYST',7566,'1997-04-17',3000,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000,NULL,10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7844,'TURNER','SALESMAN',7698,'1991-09-08',1500,0,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7876,'ADAMS','CLERK',7788,'1997-05-23',1100,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7900,'JAMES','CLERK',7698,'1991-12-03',950,NULL,30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7902,'FORD','ANALYST',7566,'1991-12-03',3000,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp VALUES (7934,'MILLER','CLERK',7782,'1992-01-23',1300,NULL,10);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t_dept;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t_dept(
    ->   DEPTNO   INT,
    ->   DNAME    VARCHAR(14),
    ->   LOC      VARCHAR(13),
    ->   PRIMARY KEY(DEPTNO)
    ->   );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_dept VALUES (10,'ACCOUNTING','NEW YORK');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept VALUES (20,'RESEARCH','DALLAS');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept VALUES (30,'SALES','CHICAGO');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept VALUES (40,'OPERATIONS','BOSTON');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t_professor;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t_professor(
    -> PROFNO INT,
    -> NAME  VARCHAR(10) NOT NULL,
    -> ID  VARCHAR(15) NOT NULL,
    -> POSITION VARCHAR(10) NOT NULL,
    -> PAY INT NOT NULL,
    -> HIREDATE  DATE NOT NULL,
    -> BONUS INT,
    -> DEPTNO  INT,
    -> EMAIL  VARCHAR(50),
    -> HPAGE  VARCHAR(50),
    -> PRIMARY KEY(PROFNO)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_professor VALUES (1001,'조인형','captain','정교수',550,'1990-06-23',100,101,'captain@abc.net','http://www.abc.net');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_professor VALUES (1002,'박승곤','sweety','조교수',380,'1997-01-30',60,101,'sweety@abc.net','http://www.abc.net');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (1003,'송도권','powerman','전임강사',270,'2008-03-22',NULL,101,'pman@power.com','http://www.power.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (2001,'양선희','lamb1','전임강사',250,'2011-09-01',NULL,102,'lamb1@hamail.net',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (2002,'김영조','number1','조교수',350,'1995-11-30',80,102,'number1@naver.com','http://num1.naver.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (2003,'주승재','bluedragon','정교수',490,'1992-04-29',90,102,'bdragon@naver.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (3001,'김도형','angel1004','정교수',530,'1991-10-23',110,103,'angel1004@hanmir.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (3002,'나한열','naone10','조교수',330,'2007-07-01',50,103,'naone10@empal.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (3003,'김현정','only-u','전임강사',290,'2012-02-24',NULL,103,'only_u@abc.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4001,'심슨','simson','정교수',570,'1991-10-23',130,201,'chebin@daum.net',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4002,'최슬기','gogogo','조교수',330,'2019-08-30',NULL,201,'gogogo@def.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4003,'박원범','mypride','조교수',310,'2009-12-01',50,202,'mypride@hanmail.net',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4004,'차범철','ironman','전임강사',260,'2019-01-28',NULL,202,'ironman@naver.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4005,'바비','standkang','정교수',500,'1995-09-18',80,203,'standkang@naver.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4006,'전민','napeople','전임강사',220,'2020-06-28',NULL,301,'napeople@jass.com',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_professor VALUES (4007,'허은','silver-her','조교수',290,'2011-05-23',30,301,'silver-her@daum.net',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #-------------------------------------------------
mysql>
mysql> drop table if exists t_department;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_department(
    ->   deptno INT,
    ->   dname varchar(20) not null,
    ->   part INT,
    ->   build  varchar(14),
    ->   PRIMARY KEY(deptno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t_department VALUES (101,'컴퓨터공학과',100,'정보관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (102,'멀티미디어공학과',100,'멀티미디어관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (103,'소프트웨어공학과',100,'소프트웨어관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (201,'전자공학과',200,'전자제어관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (202,'기계공학과',200,'기계실험관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (203,'화학공학과',200,'화학실습관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (301,'문헌정보학과',300,'인문관');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (100,'컴퓨터정보학부',10,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (200,'메카트로닉스학부',10,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (300,'인문사회학부',20,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (10,'공과대학',NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_department VALUES (20,'인문대학',NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> #--------------------------------------------------------------------
mysql>
mysql>
mysql> drop table if exists t_student;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_student
    -> (
    ->   studno int,
    ->   name   varchar(10) not null,
    ->   id varchar(20) not null unique,
    ->   grade int check(grade between 1 and 6),
    ->   jumin varchar(13) not null,
    ->   birthday  date,
    ->   tel varchar(15),
    ->   height  int,
    ->   weight  int,
    ->   deptno1 int,
    ->   deptno2 int,
    ->   profno  int,
    ->   primary key(studno)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO t_student VALUES (9411,'서진수','75true',4,'9510231901813','1995-10-23','055)381-2158',180,72,101,201,1001);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_student VALUES (9412,'서재수','pooh94',4,'9502241128467','1995-02-24','051)426-1700',172,64,102,NULL,2001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9413,'이미경','angel000',4,'9506152123648','1995-06-15','053)266-8947',168,52,103,203,3002);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9414,'김재수','gunmandu',4,'9512251063421','1995-12-25','02)6255-9875',177,83,201,NULL,4001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9415,'박동호','pincle1',4,'9503031639826','1995-03-03','031)740-6388',182,70,202,NULL,4003);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9511,'김신영','bingo',3,'9601232186327','1996-01-23','055)333-6328',164,48,101,NULL,1002);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9512,'신은경','jjang1',3,'9604122298371','1996-04-12','051)418-9627',161,42,102,201,2002);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9513,'오나라','nara5',3,'9609112118379','1996-09-11','051)724-9618',177,55,202,NULL,4003);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9514,'구유미','guyume',3,'9601202378641','1996-01-20','055)296-3784',160,58,301,101,4007);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9515,'임세현','shyun1',3,'9610122196482','1996-10-12','02)312-9838',171,54,201,NULL,4001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9611,'일지매','onejimae',2,'9711291186223','1997-11-29','02)6788-4861',182,72,101,NULL,1002);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9612,'김진욱','samjang7',2,'9704021358674','1997-04-02','055)488-2998',171,70,102,NULL,2001);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9613,'안광훈','nonnon1',2,'9709131276431','1997-09-13','053)736-4981',175,82,201,NULL,4002);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9614,'김문호','munho',2,'9702261196365','1997-02-26','02)6175-3945',166,51,201,NULL,4003);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9615,'노정호','star123',2,'9712141254963','1997-12-14','051)785-6984',184,62,301,NULL,4007);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9711,'이윤나','prettygirl',1,'9808192157498','1998-08-19','055)278-3649',162,48,101,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9712,'안은수','silverwt',1,'9801051776346','1998-01-05','02)381-5440',175,63,201,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9713,'인영민','youngmin',1,'9808091786954','1998-08-09','031)345-5677',173,69,201,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9714,'김주현','kimjh',1,'9803241981987','1998-03-24','055)423-9870',179,81,102,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_student VALUES (9715,'허우','wooya2702',1,'9802232116784','1998-02-23','02)6122-2345',163,51,103,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #------------------------------------------
mysql>
mysql> drop table if exists t_credit;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table t_credit
    -> (grade char(3) ,
    ->  min_point  int,
    ->  max_point  int );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t_credit values ('A+',96,100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('A0',90,95);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('B+',86,89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('B0',80,85);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('C+',76,79);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('C0',70,75);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_credit values ('D',0,69);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #-----
mysql>
mysql> drop table if exists t_exam01;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table t_exam01
    -> (studno  int,
    ->  total int);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t_exam01 values (9411,97);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9412,78);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9413,83);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9414,62);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9415,88);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9511,92);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9512,87);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9513,81);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9514,79);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9515,95);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9611,89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9612,77);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9613,86);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9614,82);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9615,87);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9711,91);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9712,88);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9713,82);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9714,83);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_exam01 values (9715,84);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #--------------------------------------------------------
mysql> # 시나리오
mysql> #--------------------------------------------------------
mysql>
mysql> drop table if exists t_emp2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t_emp2 (
    ->  EMPNO       INT  PRIMARY KEY,
    ->  NAME        VARCHAR(10) NOT NULL,
    ->  BIRTHDAY    DATE,
    ->  DEPTNO      VARCHAR(06) NOT NULL,
    ->  EMP_TYPE    VARCHAR(08),
    ->  TEL         VARCHAR(15),
    ->  HOBBY       VARCHAR(30),
    ->  PAY         INT,
    ->  POST   VARCHAR(08),
    ->  PEMPNO      INT
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO t_emp2 VALUES (20000101,'나사장','19740125','0001','정규직','054)223-0001','음악감상',100000000,'대표이사',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20060101,'전부장','19830322','1000','정규직','02)6255-8000','독서',72000000,'부장',20000101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20070201,'최일도','19850415','1000','정규직','02)6255-8005','운동',50000000,'과장',20060101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20030331,'백원만','19860525','1001','정규직','02)6255-8010','자전거타기',60000000,'차 장',20060101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20050303,'천만득','19830615','1002','정규직','02)6255-8020','마라톤',56000000,'과장',20060101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20066102,'일지매','19820705','1003','정규직','052)223-4000','음악감상',75000000,'부장',20000101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20030402,'유관순','19820815','1004','정규직','042)998-7005','등산',51000000,'과장',20066102);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20060303,'김문호','19810925','1005','정규직','031)564-3340','등산',35000000,'대리',20066102);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20070112,'노정호','19861105','1006','정규직','054)223-4500','수영',68000000,'부장',20000101);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20060212,'이윤나','19821215','1007','정규직','054)223-4600',NULL,49000000,'과장',20070112);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100101,'이태백','19950125','1008','계약직','051)123-4567','등산', 30000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100102,'김설악','19930322','1009','계약직','031)234-5678','낚시', 30000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100203,'최오대','19920415','1010','계약직','02)2345-6789','바둑', 30000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100334,'박지리','19910525','1011','계약직','053)456-7890','노래', 30000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100305,'정북악','19900615','1008','수습직','051)567-8901','독서', 22000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20106106,'유도봉','19900705','1009','수습직','031)678-9012','술',   22000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100407,'윤주왕','19900815','1010','수습직','02)2789-0123','오락', 22000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100308,'강월악','19900925','1011','인턴직','053)890-1234','골프', 20000000,NULL,20060212);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100119,'장금강','19901105','1004','인턴직','042)901-2345','술',   20000000,NULL,20030402);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_emp2 VALUES (20100210,'나한라','19901215','1005','인턴직','031)345-3456','독서', 20000000,NULL,20060303);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #---
mysql> drop table if exists t_dept2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE t_dept2 (
    ->  DCODE   VARCHAR(06)  PRIMARY KEY,
    ->  DNAME   VARCHAR(20) NOT NULL,
    ->  PDEPT VARCHAR(06) ,
    ->  AREA        VARCHAR(16)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO t_dept2 VALUES ('0001','사장실','','포항본사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1000','경영지원부','0001','서울지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1001','재무관리팀','1000','서울지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1002','총무팀','1000','서울지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1003','기술부','0001','포항본사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1004','H/W지원','1003','대전지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1005','S/W지원','1003','경기지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1006','영업부','0001','포항본사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1007','영업기획팀','1006','포항본사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1008','영업1팀','1007','부산지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1009','영업2팀','1007','경기지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1010','영업3팀','1007','서울지사');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_dept2 VALUES ('1011','영업4팀','1007','울산지사');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #---
mysql>
mysql> drop table if exists t_post;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table t_post
    -> ( post varchar(10) ,
    ->   s_age int,
    ->   e_age int ,
    ->   s_year int ,
    ->   e_year int ,
    ->   s_pay  int ,
    ->   e_pay  int );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into t_post values ('주임',0,24,1,2,12000000,29990000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_post values ('대리',25,28,3,5,30000000,45000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_post values ('과장',29,32,6,8,45010000,51000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_post values ('차장',33,36,9,10,51010000,60000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_post values ('부장',37,40,11,13,60010000,75000000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_post values ('이사',41,55,14,99,75010000,100000000);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #--------------------------------------------------------
mysql> # 시나리오
mysql> #--------------------------------------------------------
mysql>
mysql> drop table if exists t_gift;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table t_gift
    -> ( g_no  int,
    ->   g_name varchar(15) ,
    ->   g_start  int ,
    ->   g_end  int );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into t_gift values(1,'참치세트',1,100000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(2,'샴푸세트',100001,200000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(3,'세차용품세트',200001,300000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(4,'주방용품세트',300001,400000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(5,'산악용자전거',400001,500000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(6,'LCD모니터',500001,600000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(7,'노트북',600001,700000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(8,'벽걸이TV',700001,800000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(9,'드럼세탁기',800001,900000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_gift values(10,'양쪽문냉장고',900001,1000000);
Query OK, 1 row affected (0.00 sec)

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> #---
mysql>
mysql> drop table if exists t_customer;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> create table t_customer
    -> (c_no  int,
    ->  c_name varchar(12) ,
    ->  c_jumin char(13) ,
    ->  c_point int) ;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO t_customer VALUES (20110001,'서진수','8510231369824',980000);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_customer VALUES (20110002,'서재수','8502241128467',73000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110003,'이미경','8506152123648',320000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110004,'김재수','8512251063421',65000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110005,'박동호','8503031639826',180000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110006,'김신영','8601232186327',153000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110007,'신은경','8604212298371',273000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110008,'오나라','8609112118379',315000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110009,'김설희','8601202378641',542000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110010,'임세현','8610122196482',265000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110011,'최순규','8711291186223',110000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110012,'정현영','8704021358674',99000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110013,'안광훈','8709131276431',470000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110014,'모병환','8702261196365',298000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110015,'노정호','8712141254963',420000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110016,'이윤나','8808192157498',598000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110017,'안은수','8801051776346',625000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110018,'인영민','8808091786954',670000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110019,'김지영','8803242114563',770000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_customer VALUES (20110020,'허우','8802232116784',730000);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> #--------------------------------------------------------
mysql> # 시나리오
mysql> #--------------------------------------------------------
mysql> show tables;
+-------------------+
| Tables_in_mydb111 |
+-------------------+
| items             |
| phonebook         |
| t_credit          |
| t_customer        |
| t_department      |
| t_dept            |
| t_dept2           |
| t_emp             |
| t_emp2            |
| t_exam01          |
| t_gift            |
| t_post            |
| t_professor       |
| t_student         |
| test_book         |
| test_movie        |
+-------------------+
16 rows in set (0.01 sec)

mysql> SELECT * FROM t_professor;
+--------+--------+------------+----------+-----+------------+-------+--------+----------------------+-----------------------+
| PROFNO | NAME   | ID         | POSITION | PAY | HIREDATE   | BONUS | DEPTNO | EMAIL                | HPAGE                 |
+--------+--------+------------+----------+-----+------------+-------+--------+----------------------+-----------------------+
|   1001 | 조인형 | captain    | 정교수   | 550 | 1990-06-23 |   100 |    101 | captain@abc.net      | http://www.abc.net    |
|   1002 | 박승곤 | sweety     | 조교수   | 380 | 1997-01-30 |    60 |    101 | sweety@abc.net       | http://www.abc.net    |
|   1003 | 송도권 | powerman   | 전임강사 | 270 | 2008-03-22 |  NULL |    101 | pman@power.com       | http://www.power.com  |
|   2001 | 양선희 | lamb1      | 전임강사 | 250 | 2011-09-01 |  NULL |    102 | lamb1@hamail.net     | NULL                  |
|   2002 | 김영조 | number1    | 조교수   | 350 | 1995-11-30 |    80 |    102 | number1@naver.com    | http://num1.naver.com |
|   2003 | 주승재 | bluedragon | 정교수   | 490 | 1992-04-29 |    90 |    102 | bdragon@naver.com    | NULL                  |
|   3001 | 김도형 | angel1004  | 정교수   | 530 | 1991-10-23 |   110 |    103 | angel1004@hanmir.com | NULL                  |
|   3002 | 나한열 | naone10    | 조교수   | 330 | 2007-07-01 |    50 |    103 | naone10@empal.com    | NULL                  |
|   3003 | 김현정 | only-u     | 전임강사 | 290 | 2012-02-24 |  NULL |    103 | only_u@abc.com       | NULL                  |
|   4001 | 심슨   | simson     | 정교수   | 570 | 1991-10-23 |   130 |    201 | chebin@daum.net      | NULL                  |
|   4002 | 최슬기 | gogogo     | 조교수   | 330 | 2019-08-30 |  NULL |    201 | gogogo@def.com       | NULL                  |
|   4003 | 박원범 | mypride    | 조교수   | 310 | 2009-12-01 |    50 |    202 | mypride@hanmail.net  | NULL                  |
|   4004 | 차범철 | ironman    | 전임강사 | 260 | 2019-01-28 |  NULL |    202 | ironman@naver.com    | NULL                  |
|   4005 | 바비   | standkang  | 정교수   | 500 | 1995-09-18 |    80 |    203 | standkang@naver.com  | NULL                  |
|   4006 | 전민   | napeople   | 전임강사 | 220 | 2020-06-28 |  NULL |    301 | napeople@jass.com    | NULL                  |
|   4007 | 허은   | silver-her | 조교수   | 290 | 2011-05-23 |    30 |    301 | silver-her@daum.net  | NULL                  |
+--------+--------+------------+----------+-----+------------+-------+--------+----------------------+-----------------------+
16 rows in set (0.00 sec)

mysql> DESC t_professor;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| PROFNO   | int         | NO   | PRI | NULL    |       |
| NAME     | varchar(10) | NO   |     | NULL    |       |
| ID       | varchar(15) | NO   |     | NULL    |       |
| POSITION | varchar(10) | NO   |     | NULL    |       |
| PAY      | int         | NO   |     | NULL    |       |
| HIREDATE | date        | NO   |     | NULL    |       |
| BONUS    | int         | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
| EMAIL    | varchar(50) | YES  |     | NULL    |       |
| HPAGE    | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

mysql> SELECT * FROM t_emp;
+-------+--------+-----------+------+---------------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1990-12-17 00:00:00 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1991-02-20 00:00:00 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1992-02-22 00:00:00 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1991-04-02 00:00:00 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1991-09-28 00:00:00 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1991-05-01 00:00:00 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1991-06-09 00:00:00 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1997-04-17 00:00:00 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1991-11-17 00:00:00 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1991-09-08 00:00:00 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1997-05-23 00:00:00 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1991-12-03 00:00:00 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1991-12-03 00:00:00 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1992-01-23 00:00:00 | 1300 | NULL |     10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)

mysql>
profile
Agnes Park
이전 포스트

Database 공통

다음 포스트

MySql - DML, 기타

0개의 댓글

관련 채용 정보