728x90
반응형
- 2014-08-07: MySQL install 부분 작성. + 더 정리.
- 2014-06-21: docuK upgrade.
- 2014-??-??: First posting.
참고해서 에서 다운받아 설치. 아니면 에서 설명하듯 APM (Apache server, PHP, MySQL 통합) 같은 것을 깔아서 테스트 하셔도 됨.
Zip 파일로 깔기보다 그냥 MSI installer 로 깔면 편하긴 한듯. Zip 파일로 깔면 뭔가 세부 설정하기는 까다로운듯 하긴... 그런데 세부적인 설정할때는 또 zip 이 괜찮은것도 같고;;;;;;
제 윈도우 path 설정은 대충 아래와 같음.
```[.linenums.lang-bat]
PATH=
%USERPROFILE%\AppData\Local\Microsoft\WindowsApps
C:\Users\kipac\AppData\Local\GitHubDesktop\bin
%JAVA_HOME%\bin
%VERTX_HOME%\bin
%MYSQL_HOME%\bin
```/
#### Using .zip file
참고: dev.mysql.com :: 2.3.2 Choosing an Installation Package - MySQL noinstall ZIP Archives, dev.mysql.com :: 2.3.4 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive.
MySQL 을 깐 (압축 푼) 폴더가 %MYSQL_HOME%=C:\Program Files\mysql-8.0.33-winx64 라 치면,
```[.linenums]
// %MYSQL_HOME% folder 에 새폴더 data 를 만들고
mysqld --initialize --user=mysql --console
// or
mysqld --initialize-insecure --user=mysql --console
// mysqld --init-file=C:/Recoeve/root_pw.txt
mysqld --console
mysql -u root -p
ALTER USER 'eve'@'localhost' IDENTIFIED BY '{--pwd--}';
FLUSH PRIVILEGES;
// mysqld --install mysql --defaults-file=C:\Program Files\mysql-8.0.33-winx64\my.ini
```/
잘 안됨 ㅠㅜ;;; .msi 로 깝시다.
#### Using .msi file
#### Error 1067
깔다가 이런 에러가 떴는데... 뭐 때문에 난 에러인지는 모르겠음 아직. 폴더 이름에 스페이스가 들어가서 인거 같기도 하고... (검색해보면 너무 많은 이야기가 있어서 어떤걸 시도해야 해결되는지 감을 잡기가 힘듬;;;) msi 로 깔았을때 에러나서 지웠다가, zip 파일로 다운받아서 다시 스페이스 없는 폴더 C:\mysql-5.6.20-winx64 에 압축풀고, mysqld --install mysql --defaults-file=C:\mysql-5.6.20-winx64\my.ini 명령어로 까니까 에러가 안뜨긴 하는데... (my.ini 파일은 블로그 글 참고해서 파일 만듬. 폴더 구분자는 '\' 대신 '/' 사용. 마지막 부분에도 '/' 붙이고. 이런 사소한 거에 영향 받는건가?)
지우는 명령어는 mysqld --remove mysql, 윈도우에서 mysql 서버를 시작하는 명령어는 net start mysql, 멈추는 명령어는 net stop mysql. (msi 로 깔면 이런 명령어들이 안먹히는듯??? my.ini 랑 my.cnf 파일을 MySQL 폴더에 잘 넣어놓기만 하면 설정은 잘 적용됨. 재부팅은 해야 적용 제대로 될듯.)
파일 my.ini 는 다음과 같이...
```[.linenums]
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
character-set-server=utf8mb4
# collation-server=utf8mb4
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir=C:/Program Files/mysql-8.0.28-winx64/
datadir=C:/Program Files/mysql-8.0.28-winx64/data/
port=3306
# server_id = Data
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
```/
#### Encoding
한글 인코딩 및 여러 언어 지원을 위해 utf8 인코딩을 쓰는것이 좋아 보임. (UTF-8 인코딩에서 한글은 무조건 3byte(=24bit) 인코딩 .) 위의 my.ini 파일에서도 인코딩 관련 설정을 해놓긴 했는데, my.cnf 파일을 생성해서 다음과 같이 추가하면 전체적인 인코딩 설정이 되는듯. Emoji 저장을 위해서는 utf8mb4 를 써야 하는듯.
파일 my.cnf 는 다음과 같음.
```[.scrollable]
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
[mysqldump]
default-character-set=utf8mb4
[mysqld_safe]
timezone=UTC
default-time-zone='+00:00'
[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection=utf8mb4_unicode_ci"
init_connect="SET NAMES utf8mb4"
default-character-set=utf8mb4
character-set-server=utf8mb4
character-set-system=utf8mb4
collation-server=utf8mb4_unicode_ci
collation-database=utf8mb4_unicode_ci
collation-connection=utf8mb4_unicode_ci
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size=128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir=C:/Program Files/mysql-8.0.28-winx64/
datadir=C:/Program Files/mysql-8.0.28-winx64/data/
port=3306
# server_id=Data
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size=128M
# sort_buffer_size=2M
# read_rnd_buffer_size=2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
```/
my.ini 파일을 인스톨할때 쓰는건가? 이걸 이상하게 바꾸면, "Error 1067"이 뜨는듯한데... my.ini 를 위와같이 my.cnf 처럼 바꿔도 에러가 나네;;;;; my.ini 에는 [mysqld] 관련 설정만 있어야 하는건가?
STATUS를 치거나
```[.linenums]
--------------
mysql Ver 8.0.28 for Win64 on x86_64 (MySQL Community Server - GPL)
Connection id: 9
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 8 days 8 hours 18 min 0 sec
Threads: 2 Questions: 10 Slow queries: 0 Opens: 117 Flush tables: 3 Open tables: 36 Queries per second avg: 0.000
--------------
```/
SHOW variables like 'c%';를 치면
+----------------------------------------------+------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------------+------------------------------------------------------+
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_digest_rounds | 5000 |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\mysql-8.0.28-winx64\share\charsets\ |
| check_proxy_users | OFF |
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
| connection_memory_chunk_size | 8912 |
| connection_memory_limit | 18446744073709551615 |
| core_file | OFF |
| create_admin_listener_thread | OFF |
| cte_max_recursion_depth | 1000 |
+----------------------------------------------+------------------------------------------------------+
```/
와 같이 뜨는걸로 확인 가능.
#### Connection Exception
Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. 관련해서는 SHOW GLOBAL VARIABLES LIKE '%_timeout'; 을 치면 나오는
```[.linenums]
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+
```/
interactive_timeout 하고 wait_timeout 에 관련된 문제인데, MySQL 에 접속한뒤 28800초 (=8시간) 동안 아무활동이 없으면 session 이 끊기도록 설정되어 있는거 같음 http://redutan.blogspot.kr - No operations allowed after connection closed 오류 해결, 2013-12-19dev.mysql.com - 5.1.4 Server System Variables - #wait_timeout. 보안상의 이유로 이런게 있는듯한데... 어찌 해결해야 할래나?
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| system_time_zone | Coordinated Universal Time |
| time_zone | SYSTEM |
+------------------+----------------------------+
SHOW VARIABLES LIKE '%time_n%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| lc_time_names | en_US |
+---------------+-------+
```/
### 데이터 접근 권한 (암호 설정 및 추가 계정 생성)
Root 권한으로 모든걸 진행하면 안될거 같은데...
Ref. - MySQL manual - Chapter 6 Security에서 보안 및 권한 관련.
- Choosing good passwords, not granting unnecessary privileges to users, ensuring application security by preventing SQL injections and data corruption, and others. See Section 6.1, “General Security Issues”.
- Security of the installation itself. The data files, log files, and the all the application files of your installation should be protected to ensure that they are not readable or writable by unauthorized parties. For more information, see Section 2.9, “Postinstallation Setup and Testing”.
- Access control and security within the database system itself, including the users and databases granted with access to the databases, views and stored programs in use within the database. For more information, see Section 6.2, “The MySQL Access Privilege System”, and Section 6.3, “MySQL User Account Management”.
- Network security of MySQL and your system. The security is related to the grants for individual users, but you may also wish to restrict MySQL so that it is available only locally on the MySQL server host, or to a limited set of other hosts.
- Ensure that you have adequate and appropriate backups of your database files, configuration and log files. Also be sure that you have a recovery solution in place and test that you are able to successfully recover the information from your backups. See Chapter 7, Backup and Recovery.
+------+-----------+------+
| User | Host | Password |
+------+-----------+------+
| root | localhost | *... |
| root | 127.0.0.1 | *... |
| root | ::1 | *... |
| | localhost | *... |
+------+-----------+------+
# Showing current user
SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
# Setting password for root.
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
# root (admin)
SET PASSWORD FOR 'root'@'%' = PASSWORD('newpwd');
# % is wildcard which means all host.
# Creating/Registering a new user with a password
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpass';
# Or changing password for existing user.
SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');
SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
# Anonymous user
# Or directly UPDATE mysql.user table.
UPDATE mysql.user SET Password = PASSWORD('newpwd')
WHERE User = 'root';
FLUSH PRIVILEGES;
# The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.
# Dropping/Deleting/Unregistering user
DROP USER ''@'localhost';
# Removing the anonymous account
```/
Syntax: 13.7.5.22 SHOW GRANTS Syntax
```[.linenums.lang-sql]
# Showing grants for current user (권한 보기)
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
# Showing grants for a user
SHOW GRANTS FOR ''@localhost;
# result: GRANT USAGE ON *.* TO ''@'localhost' IDENTIFIED BY PASSWORD '**....'
# The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges. (뭔 말이여? USAGE 는 갓 만들어진 계정에 주어지는 것으로 아무 권한이 없다는 말인듯?)
```/
Syntax: 13.7.1.4 GRANT Syntax and 13.7.1.6 REVOKE Syntax
```[.linenums.scrollable.lang-sql]
# GRANT
GRANT
Priv_type [(Column_list)]
[, Priv_type [(Column_list)]] ...
ON [Object_type] Priv_level
TO User_specification [, User_specification] ...
[REQUIRE {NONE | Ssl_option [[AND] Ssl_option] ...}]
[WITH With_option ...];
GRANT PROXY ON User_specification
TO User_specification [, User_specification] ...
[WITH GRANT OPTION];
# GRANT Examples
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';
GRANT USAGE ON *.* TO ...
WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
IDENTIFIED BY 'goodsecret' REQUIRE SSL;
# with
Object_type:
TABLE
| FUNCTION
| PROCEDURE
Priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
User_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
IDENTIFIED BY [PASSWORD] 'password'
]
Ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
With_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
# REVOKE
REVOKE
Priv_type [(Column_list)]
[, Priv_type [(Column_list)]] ...
ON [Object_type] Priv_level
FROM User [, User] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM User [, User] ...
REVOKE PROXY ON User
FROM User [, User] ...
# REVOKE Ex
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM User [, User] ...
```/
#### 예제
```[.linenums.lang-sql]
# Creating/Registering a new user with a password
CREATE USER 'eve'@'localhost' IDENTIFIED BY 'some$password';
# 생성 확인
SELECT User, Host, Password AS Pwd FROM Mysql.user;
+------+-----------+------+
| User | Host | Pwd |
+------+-----------+------+
| root | localhost | *... |
| root | 127.0.0.1 | *... |
| root | ::1 | *... |
| | localhost | *... |
| eve | localhost | *... |
+------+-----------+------+
SHOW GRANTS FOR 'eve'@'localhost';
# GRANT USAGE ON *.* TO 'eve'@'localhost' IDENTIFIED BY PASSWORD '*...'
GRANT Select, Delete, Update ON `Data0.1`.* TO 'eve'@'localhost';
SHOW GRANTS FOR 'eve'@'localhost'; # Again
+-----------------------------------------------------------------------+
| Grants for eve@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'eve'@'localhost' IDENTIFIED BY PASSWORD '*...' |
| GRANT SELECT, UPDATE, DELETE ON `Data0.1`.* TO 'eve'@'localhost' |
+-----------------------------------------------------------------------+
REVOKE Select, Delete, Update ON `Data0.1`.* FROM 'eve'@'localhost';
# or
REVOKE All Privileges, Grant Option FROM 'eve'@'localhost';
SHOW GRANTS FOR 'eve'@'localhost'; # Again
+-----------------------------------------------------------------------+
| Grants for eve@localhost |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'eve'@'localhost' IDENTIFIED BY PASSWORD '*...' |
+-----------------------------------------------------------------------+
# Dropping/Deleting/Unregistering user
DROP USER 'eve'@'localhost';
SELECT User, Host, Password AS Pwd FROM Mysql.user; # DROP 확인
```/
## SQL query
여기선 우선 MySQL 이야기. NoSQL, MongoDB 같은 비 관계형 database도 요새 뜨는 중인듯.
그냥 MySQL 을 깔았을 경우 (기본 조작법) : Window cmd 창에서 mysql -u아이디 -p, 보통 아이디가 'root' (관리자 계정) 라서 mysql -uroot -p.
APM (Apache server, PHP, MySQL 통합) 으로 깔았을 경우 : 인터넷 주소창에 "localhost"를 치면, "PHP Info"와 "phpMyAdmin"에 접속할 수 있음. 이 중 "phpMyAdmin"은 "http://127.0.0.1/myadmin/" 로 연결됨. 사용자명/암호: root/(각자의 암호) 치면 web 형태로 MySQL을 조작할 수 있음.
### DATABASE (Outermost Container)
```[.scrollable.lang-sql]
/****************** Database ******************/ // start
/****************** Database ******************/ // start
/****************** Database ******************/ // start
// Database: 데이터가 실질적으로 적재되는 테이블들을 분류하는 상위 개념
// 생성
// SQL CREATE DATABASE Syntax
CREATE DATABASE dbname;
CREATE DATABASE `데이터베이스명` CHARACTER SET utf8 COLLATE utf8_general_ci;
// 생성 Ex.
CREATE DATABASE my_db;
CREATE DATABASE `class0` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `class1` CHARACTER SET utf8 COLLATE utf8_general_ci;
// 삭제/제거
DROP DATABASE `데이터베이스명`; // DROP TABLE 과 구분하기 위해 DATABASE, TABLE 써줘야 함.
// 열람
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| class0 |
| class1 |
| my_db |
| mysql |
| phpmyadmin |
+--------------------+
// 선택
USE `데이터베이스명`;
/****************** Database ******************/ // end
/****************** Database ******************/ // end
/****************** Database ******************/ // end
```/
```[.scrollable.lang-sql]
// SQL is NOT case sensitive: "SELECT" is the same as "select".
// ` `는 optional(써도 그만, 안써도 그만). But 한글쓸땐 필수인듯.
/* Semicolon(;) after SQL Statements is the standard way
to separate each SQL statement in database systems
that allow more than one SQL statement to be executed in the same call to the server. */
Some of The Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database // 잘 쓰나? 자주 안쓸듯?
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
```/
### TABLE (+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | tinyint(2) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| sex | enum('남자','여자') | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
SHOW CREATE TABLE `student`;
+---------+------------------------------+
| student | CREATE TABLE `student` (
`id` tinyint(2) NOT NULL,
`name` char(10) NOT NULL,
`sex` enum('남자','여자') DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+------------------------------+
INSERT INTO `student` (`id`, `name`) VALUES (11, '가나다');
INSERT INTO `student` (`id`, `name`) VALUES (1, 'abcd');
INSERT INTO `student` (`id`, `name`) VALUES (201, 'xyz');
INSERT INTO `student` (`id`, `name`) VALUES (101, 'xyz');
SELECT * FROM `student`;
+-----+--------+------+---------+----------+
| id | name | sex | address | birthday |
+-----+--------+------+---------+----------+
| 11 | 가나다 | NULL | NULL | NULL |
| 1 | abcd | NULL | NULL | NULL |
| 127 | xyz | NULL | NULL | NULL | // 201 넣었지만, tinyint 범위가 -128 ~ 127 정수형이라 127로 바뀜.
| 101 | xyz | NULL | NULL | NULL | // tinyint(2)는 상관 없는듯??? int(2)도 상관없네?
+-----+--------+------+---------+----------+
ALTER TABLE `student` MODIFY COLUMN `id` int(2) NOT NULL; // int = int(11) 기본인듯.
INSERT INTO `student` (`id`, `name`, `birthday`) VALUES (3001, 'xyz', '1990-01-01 23:11:15');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES (2001, 'xyz', '1990-01-01 23:11:15', '남자');
UPDATE `student` SET `sex`='남자';
UPDATE `student` SET `sex`='남자', `address`='서울시 관악구'
WHERE `id`=2001;
UPDATE `student` SET `sex`='여자', `address`='서울시 관악구 봉천7동 봉천 초등학교', `birthday`='1990-07-01 10:05:35'
WHERE `id`=3001;
SELECT * FROM `student`;
+------+--------+------+-------------------------------------+---------------------+
| id | name | sex | address | birthday |
+------+--------+------+-------------------------------------+---------------------+
| 11 | 가나다 | 남자 | NULL | NULL |
| 1 | abcd | 남자 | NULL | NULL |
| 127 | xyz | 남자 | NULL | NULL |
| 101 | xyz | 남자 | NULL | NULL |
| 3001 | xyz | 여자 | 서울시 관악구 봉천7동 봉천 초등학교 | 1990-07-01 10:05:35 |
| 2001 | xyz | 남자 | 서울시 관악구 | 1990-01-01 23:11:15 |
+------+--------+------+-------------------------------------+---------------------+
```/
#### ALTER TABLE
```[.scrollable.lang-sql]
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE Persons(
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_Person PRIMARY KEY (`PersonID`, `LastName`)
);
DESC `Persons`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| PersonID | int(11) | NO | PRI | NULL | | // int(11)로 자동적으로.
| LastName | varchar(255) | NO | PRI | NULL | |
| FirstName | varchar(255) | YES | | NULL | |
| Address | varchar(255) | YES | | NULL | |
| City | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
SHOW CREATE TABLE Persons;
+---------+------------------------------+
| Persons | CREATE TABLE `persons` (
`PersonID` int(11) NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`City` varchar(255) DEFAULT NULL,
PRIMARY KEY (`PersonID`,`LastName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------+------------------------------+
ALTER TABLE employees CHANGE id employee_id VARCHAR(25);
ALTER TABLE employees RENAME COLUMN id TO employee_id;
ALTER TABLE Persons DROP PRIMARY KEY;
// ALTER TABLE Persons DROP INDEX pk_Person; 요건 에러나네.
// ALTER TABLE Persons DROP PRIMARY KEY (`PersonID`,`LastName`); 이것도 에러.
ALTER TABLE Persons ADD PRIMARY KEY (`PersonID`,`LastName`);
ALTER TABLE Persons DROP `PersonID`;
ALTER TABLE Persons DROP `LastName`;
ALTER TABLE Persons ADD `PersonID` int(11) NOT NULL;
ALTER TABLE Persons ADD `LastName` varchar(255) NOT NULL;
// ALTER TABLE Persons ADD `PersonID` int(11) NOT NULL, `LastName` varchar(255) NOT NULL; 두개 한꺼번에는 안되는듯?
// 순서도 뒤바뀜.(`PersonID`,`LastName`이 제일 뒤에 붙음.) 이건 어찌 못하나???
ALTER TABLE Persons ADD UNIQUE KEY (`PersonID`);
// ALTER TABLE Persons DROP UNIQUE KEY; 요런것도 안먹히는듯.
ALTER TABLE Persons ADD PRIMARY KEY (`PersonID`,`LastName`);
ALTER TABLE Persons DROP PRIMARY KEY;
INSERT INTO persons VALUES (1,'Einstein', 'Albert', 'Germany', 'Berlin');
INSERT INTO persons VALUES (1,'Eins', 'Albert', 'Germany', 'Berlin');
INSERT INTO persons (personId, lastName, firstName) VALUES (2,'Eins', 'Albert');
select * from persons;
// 명령어들, table, column은 NOT case-sensitive. ' ' 안에 들어가는 문자열만 case-sensitive.
+----------+----------+-----------+---------+--------+
| PersonID | LastName | FirstName | Address | City |
+----------+----------+-----------+---------+--------+
| 1 | Einstein | Albert | Germany | Berlin |
| 1 | Eins | Albert | Germany | Berlin |
| 2 | Eins | Albert | NULL | NULL |
+----------+----------+-----------+---------+--------+
```/
```[.scrollable.lang-sql]
// 한글 쓸땐 table명과 column명은 반드시 ` `안에...
DROP TABLE IF EXISTS `회원`;
CREATE TABLE `회원`(
`회원번호` int NOT NULL,
`이름` char(10),
`회원등급` enum('일반','우수') DEFAULT '일반',
`적립금` int DEFAULT 0,
PRIMARY KEY (`회원번호`)
);
DESC `회원`;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| 회원번호 | int(11) | NO | PRI | NULL | |
| 이름 | char(10) | YES | | NULL | |
| 회원등급 | enum('일반','우수') | YES | | 일반 | |
| 적립금 | int(11) | YES | | 0 | |
+----------+---------------------+------+-----+---------+-------+
SHOW CREATE TABLE `회원`;
+-------+----------------------------------------+
| Table | Create Table |
+-------+----------------------------------------+
| 회원 | CREATE TABLE `甕원` (
`회원번호` int(11) NOT NULL,
`이름` char(10) DEFAULT NULL,
`회원등급` enum('일반','우수') DEFAULT '일반',
`적립금` int(11) DEFAULT '0',
PRIMARY KEY (`회원번호`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------+
INSERT INTO `회원` (`회원번호`,`이름`,`회원등급`,`적립금`) VALUES (1,'가나다','우수',100000);
UPDATE `회원` SET `회원번호`=`회원번호`+1 WHERE `회원번호`=1; // `회원번호`+=1 명령어는 안먹힘.
INSERT INTO `회원` (`회원번호`,`이름`,`회원등급`,`적립금`) VALUES (1,'가나다','우수',100000);
UPDATE `회원` SET `회원번호`=`회원번호`+10;
INSERT INTO `회원` (`회원번호`,`이름`,`회원등급`,`적립금`) VALUES (1,'가나다','우수',200000);
INSERT INTO `회원` (`회원번호`,`이름`,`회원등급`,`적립금`) VALUES (2,'바바바','우수',200000);
INSERT INTO `회원` (`회원번호`,`이름`,`회원등급`,`적립금`) VALUES (3,'띠로링','우수',300000);
INSERT INTO `회원` (`회원번호`,`이름`) VALUES (4,'거지');
INSERT INTO `회원` (`회원번호`,`이름`) VALUES (5,'거지2');
INSERT INTO `회원` (`회원번호`,`이름`,`적립금`) VALUES (6,'돈있어',1000);
select * from `회원`;
+----------+--------+----------+--------+
| 회원번호 | 이름 | 회원등급 | 적립금 |
+----------+--------+----------+--------+
| 12 | 가나다 | 우수 | 100000 |
| 11 | 가나다 | 우수 | 100000 |
| 1 | 가나다 | 우수 | 200000 |
| 2 | 바바바 | 우수 | 200000 |
| 3 | 띠로링 | 우수 | 300000 |
| 4 | 거지 | 일반 | 0 |
| 5 | 거지2 | 일반 | 0 |
| 6 | 돈있어 | 일반 | 1000 |
+----------+--------+----------+--------+
SELECT count(*), avg(`적립금`), sum(`적립금`) FROM `회원`;
+----------+---------------+---------------+
| count(*) | avg(`적립금`) | sum(`적립금`) |
+----------+---------------+---------------+
| 8 | 112625.0000 | 901000 |
+----------+---------------+---------------+
SELECT count(*) AS "적립 한 사람수", avg(`적립금`) AS "평균 적립금", sum(`적립금`) AS "적립금 총액"
FROM `회원` WHERE `적립금`>0;
+------------+-------------+-------------+
| 적립 했나? | 평균 적립금 | 적립금 총액 |
+------------+-------------+-------------+
| 6 | 150166.6667 | 901000 |
+------------+-------------+-------------+
INSERT INTO `회원` (`회원번호`,`이름`,`적립금`) VALUES (7,'돈있어1',1000);
INSERT INTO `회원` (`회원번호`,`이름`,`적립금`) VALUES (8,'돈있어2',1000);
INSERT INTO `회원` (`회원번호`,`이름`,`적립금`) VALUES (9,'돈있어3',1000);
INSERT INTO `회원` (`회원번호`,`이름`,`적립금`) VALUES (10,'돈있어4',1000);
select sum(`적립금`) from `회원` group by `회원번호` asc;
// group by는 자동으로 asc 정렬까지. (asc 없어도)
DROP TABLE IF EXISTS `상품구매내역`;
CREATE TABLE `상품구매내역`(
`상품구매번호` int NOT NULL,
`회원번호` int NOT NULL,
`상품코드` char(11),
`구매일` date,
PRIMARY KEY (`상품구매번호`, `회원번호`),
KEY `fk_회원` (`회원번호`)
);
/*
CREATE TABLE `상품구매내역`(
`상품구매번호` int NOT NULL,
`회원번호` int NOT NULL,
`상품코드` char(11),
`구매일` date,
CONSTRAINT `pk_상품구매내역` PRIMARY KEY (`상품구매번호`, `회원번호`),
CONSTRAINT `fk_회원` FOREIGN KEY (`회원번호`) REFERENCES `회원`(`회원번호`)
); */
// MySQL에서는 FOREIGN KEY를 그냥 KEY로 쓰는듯?
// REFERENCES 는 전혀 쓸모가 없는거 같은데... 값 넣을때(INSERT INTO) 확인도 안하는거 같고...
DESC `상품구매내역`;
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| 상품구매번호 | int(11) | NO | PRI | NULL | |
| 회원번호 | int(11) | NO | PRI | NULL | |
| 상품코드 | char(11) | YES | | NULL | |
| 구매일 | date | YES | | NULL | |
+--------------+----------+------+-----+---------+-------+
SHOW CREATE TABLE `상품구매내역`;
+--------------+--------------------------------+
| Table | Create Table |
+--------------+--------------------------------+
| 상품구매내역 | CREATE TABLE `상怜구멨내역` (
`상품구매번호` int(11) NOT NULL,
`회원번호` int(11) NOT NULL,
`상품코드` char(11) DEFAULT NULL,
`구매일` date DEFAULT NULL,
PRIMARY KEY (`상품구매번호`,`회원번호`),
KEY `fk_회원` (`회원번호`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------------+--------------------------------+
ALTER TABLE `상품구매내역` DROP KEY `fk_회원`;
// ALTER TABLE `상품구매내역` DROP FOREIGN KEY `fk_회원`; 해도 에러는 안뜨는데... 지워지지는 않음?
// ALTER TABLE `상품구매내역` DROP FOREIGN KEY; 해도 에러는 안뜨는데... 지워지지는 않음?
ALTER TABLE `상품구매내역` ADD KEY `fk_회원` (`회원번호`);
ALTER TABLE `상품구매내역` ADD `가격` int DEFAULT 0;
ALTER TABLE `상품구매내역` MODIFY COLUMN `상품구매번호` char(10) NOT NULL;
INSERT INTO `상품구매내역` (`상품구매번호`, `회원번호`, `상품코드`, `구매일`)
VALUES ('구매1', 1, '상품코드1', '2013-10-01');
UPDATE `상품구매내역` SET `가격`=10000 WHERE (`상품구매번호`='구매1' AND `회원번호`=1);
INSERT INTO `상품구매내역` (`상품구매번호`, `회원번호`, `상품코드`, `가격`, `구매일`)
VALUES ('구매2', 1, '상품코드1', 1000, '2013-10-01');
INSERT INTO `상품구매내역` (`상품구매번호`, `회원번호`, `상품코드`, `가격`, `구매일`)
VALUES ('식사1', 1, '볶음밥', 6000, '2013-10-01');
INSERT INTO `상품구매내역` (`상품구매번호`, `회원번호`, `상품코드`, `가격`, `구매일`)
VALUES ('식사1', 2, '볶음밥', 6000, '2013-10-01');
SELECT * from `상품구매내역`;
+--------------+----------+-----------+------------+-------+
| 상품구매번호 | 회원번호 | 상품코드 | 구매일 | 가격 |
+--------------+----------+-----------+------------+-------+
| 구매1 | 1 | 상품코드1 | 2013-10-01 | 10000 |
| 구매2 | 1 | 상품코드1 | 2013-10-01 | 1000 |
| 식사1 | 1 | 볶음밥 | 2013-10-01 | 6000 |
| 식사1 | 2 | 볶음밥 | 2013-10-01 | 6000 |
+--------------+----------+-----------+------------+-------+
SELECT m.`회원번호`, m.`이름`, sum(p.`가격`) AS "총 구매액"
FROM `회원` AS m LEFT JOIN `상품구매내역` AS p ON m.`회원번호`=p.`회원번호`
GROUP BY m.`회원번호`
ORDER BY m.`이름` ASC, sum(p.`가격`) DESC, m.`회원번호` DESC;
// ORDER BY는 제일 뒤에것부터 정렬한 뒤 앞에것들 정렬한 식으로 보여줌.
DROP TABLE IF EXISTS `LawMakers`;
CREATE TABLE `LawMakers` (
`의원명` char(10) NOT NULL,
`소속정당` char(10) DEFAULT NULL,
`지역구` char(10) DEFAULT NULL,
`세부지역` varchar(100) DEFAULT NULL,
`직함` set('17대', '18대', '19대', '20대') DEFAULT NULL,
`직함 비고` varchar(255) DEFAULT NULL,
`당선횟수` enum('초선', '재선', '3선', '4선', '5선', '6선', '7선', '8선', '9선') DEFAULT '초선',
`생년월일` date DEFAULT NULL,
`성별` enum('남자', '여자') DEFAULT NULL,
`군대` enum('군필', '면제', '여자', '모름', '회피') DEFAULT NULL,
`입대일` date DEFAULT NULL,
`군대 비고` varchar(255) DEFAULT NULL,
`2011재산` int DEFAULT NULL,
`2012재산` int DEFAULT NULL,
PRIMARY KEY (`의원명`,`생년월일`),
KEY `fk_지역` (`지역구`)
);
DESC lawmakers;
+-----------+---------------------------------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------------------------------------------------+------+-----+------------+-------+
| 의원명 | char(10) | NO | PRI | NULL | |
| 소속정당 | char(10) | YES | | NULL | |
| 지역구 | char(10) | YES | MUL | NULL | |
| 세부지역 | varchar(100) | YES | | NULL | |
| 직함 | set('17대','18대','19대','20대') | YES | | NULL | |
| 직함 비고 | varchar(255) | YES | | NULL | |
| 당선횟수 | enum('초선','재선','3선','4선','5선','6선','7선','8선','9선') | YES | | 초선 | |
| 생년월일 | date | NO | PRI | 0000-00-00 | |
| 성별 | enum('남자','여자') | YES | | NULL | |
| 군대 | enum('군필','면제','여자','모름','회피') | YES | | NULL | |
| 입대일 | date | YES | | NULL | |
| 군대 비고 | varchar(255) | YES | | NULL | |
| 2011재산 | int(11) | YES | | NULL | |
| 2012재산 | int(11) | YES | | NULL | |
+-----------+---------------------------------------------------------------+------+-----+------------+-------+
SHOW CREATE TABLE `LawMakers`;
+-----------+------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------+
| LawMakers | CREATE TABLE `lawmakers` (
`의원명` char(10) NOT NULL,
`소속정당` char(10) DEFAULT NULL,
`지역구` char(10) DEFAULT NULL,
`세부지역` varchar(100) DEFAULT NULL,
`직함` set('17대','18대','19대','20대') DEFAULT NULL,
`직함 비고` varchar(255) DEFAULT NULL,
`당선횟수` enum('초선','재선','3선','4선','5선','6선','7선','8선','9선') DEFAULT '초선',
`생년월일` date NOT NULL DEFAULT '0000-00-00',
`성별` enum('남자','여자') DEFAULT NULL,
`군대` enum('군필','면제','여자','모름','회피') DEFAULT NULL,
`입대일` date DEFAULT NULL,
`군대 비고` varchar(255) DEFAULT NULL,
`2011재산` int(11) DEFAULT NULL,
`2012재산` int(11) DEFAULT NULL,
PRIMARY KEY (`의원명`,`생년월일`),
KEY `fk_지역` (`지역구`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+------------------------------------------------------------------+
INSERT INTO `LawMakers` (`생년월일`) VALUES ('1990-00-00');
// 월일을 모를땐 이런식으로 넣어야 하는듯. '1990'만 넣으면 0000-00-00으로 저장.
// `직함`, `직함 비고`는 나중에
INSERT INTO `LawMakers` (`지역구`, `세부지역`, `의원명`, `소속정당`, `당선횟수`, `생년월일`, `군대`, `2011재산`, `2012재산`)
VALUES (); ...
패스.
SELECT * FROM lawmakers LIMIT 0,10;
// LIMIT from, number;
// 테이블 리스트 열람
SHOW TABLES;
+------------------+
| Tables_in_class0 |
+------------------+
| 상怜구멨내역 |
| 甕원 |
| persons |
| student |
+------------------+
// 한글은 깨지네;;;
// 테이블 제거
DROP TABLE `테이블명`;
// 테이블 변경
// Syntax
ALTER TABLE `table_name` ADD `column_name` datatype;
ALTER TABLE `상품구매내역` ADD `상품가격` double(10,1);
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| 상품구매번호 | int(11) | NO | PRI | NULL | |
| 회원번호 | int(11) | NO | PRI | NULL | |
| 상품코드 | int(11) | YES | | NULL | |
| 구매일 | varchar(255) | YES | | NULL | |
| 상품가격 | double(10,1) | YES | | NULL | | // 10자리수(소수점 아래 포함), 소수점 뒤에는 1자리까지 표시하라는 뜻.
+--------------+--------------+------+-----+---------+-------+
INSERT INTO `상품구매내역` (`상품구매번호`, `회원번호`, `상품코드`, `구매일`, `상품가격`)
VALUES (1, 0, 1001, NOW(), 1050.25);
...
// Syntax
ALTER TABLE `table_name` MODIFY COLUMN `column_name` datatype;
ALTER TABLE `상품구매내역` MODIFY COLUMN `상품가격` double(5,2);
ALTER TABLE `상품구매내역` MODIFY COLUMN `상품구매번호` char(10);
ALTER TABLE `상품구매내역` MODIFY COLUMN `회원번호` char(10);
ALTER TABLE `상품구매내역` MODIFY COLUMN `상품코드` char(10);
ALTER TABLE `상품구매내역` MODIFY COLUMN `구매일` datetime;
ALTER TABLE `상품구매내역` DROP `구매일`;
ALTER TABLE `상품구매내역` DROP `상품가격`;
// table 데이터 모두 지우기
TRUNCATE `상품구매내역`;
mysql> select * from `상품구매내역`;
+--------------+----------+----------+---------------------+----------+
| 상품구매번호 | 회원번호 | 상품코드 | 구매일 | 상품가격 |
+--------------+----------+----------+---------------------+----------+
| 0 | 1 | 0 | 2013-10-09 23:40:04 | NULL |
| 0 | 2 | 1001 | 2013-10-09 23:41:55 | NULL |
| 1 | 2 | 1001 | 2013-10-09 23:42:47 | 10.20 | // 10.25 입력. double(10,1)이어서 소수점 뒤에 짤림.
| 1 | 1 | 1001 | 2013-10-09 23:45:42 | 10.25 | // 10.25 입력. double(5,2)이어서 잘 저장.
| 1 | 0 | 1001 | 2013-10-09 23:46:14 | 999.99 | // 1050.25 입력. double(5,2)이어서 5자리 최대수로 바뀜.
+--------------+----------+----------+---------------------+----------+
/****************** Table ******************/ // end
/****************** Table ******************/ // end
/****************** Table ******************/ // end
```/
```[.scrollable.lang-sql]
/****************** Manipulating Datas ******************/ // start
/****************** Manipulating Datas ******************/ // start
/****************** Manipulating Datas ******************/ // start
// 테이블에 데이터(행/row 1개)를 삽입
INSERT INTO table_name VALUES (value1, value2, value3,...);
INSERT INTO class_name.table_name VALUES (value1, value2, value3,...);
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...);
/* column 이름을 명시하고 value를 집어넣는 방식이 조금 더 권장되는 방법,
table의 schema가 나중에라도 변경될수 있기 때문에.
또한 "Insert Data Only in Specified Columns"이 가능. */
// 조회
SELECT * FROM `table_name`;
// '*'는 '모든것(all)'을 뜻함.
SELECT `column_name1`,`column_name2` FROM `table_name`;
SELECT DISTINCT `column_name1`,`column_name2` FROM `table_name`;
// The SELECT DISTINCT statement is used to return only distinct (different) values.
SELECT `column_name1`,`column_name2` FROM `table_name` WHERE condition (like `column`=`value`);
// 변경
UPDATE `테이블명` SET `컬럼1`='컬럼1의 값',`컬럼2`='컬럼2의 값', ...;
// If you omit the WHERE clause, all records will be updated!
UPDATE `테이블명` SET `컬럼1`='컬럼1의 값',`컬럼2`='컬럼2의 값', ... WHERE condition (like `column`=`value`);
// table 삭제
// 행단위로 데이터를 삭제
DELETE FROM `table_name`;
DELETE * FROM `table_name`;
// If you omit the WHERE clause, all records will be deleted!
// Note: Be very careful when deleting records. You cannot undo this statement!
DELETE FROM `table_name` WHERE condition (like `column`=`value`);
// 테이블의 전체 데이터를 삭제
// 테이블에 외부키(foreign key)가 없다면 DELETE보다 훨씬 빠르게 삭제됨
TRUNCATE `테이블명`;
// 테이블을 삭제
DROP TABLE `테이블명`;
// DELETE / TRUNCATE / DROP 차이 :
// Delete는 행 단위의 삭제
// Truncate는 모든 행을 (행 삭제 로그, 트랜젝션 유발없이)단번에 삭제(테이블 구조와 테이블의 열, 제약 조건, 인덱스 등은 그대로 남음)
// Drop은 파일에 해당하는 Table을 삭제
// WHERE Clause: to filter records.
SELECT `column_name1`,`column_name2` FROM `table_name` WHERE `column_name` operator value;
출처: http://www.w3schools.com/sql/sql_where.asp
Operators in The WHERE Clause
= Equal
!=, <> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
// AND & OR Operators: more than one condition.
SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';
SELECT * FROM Customers WHERE City='Berlin' OR City='Munchen';
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
// Demo Database start
// Demo Database start
// Demo Database start
CREATE TABLE Customers(
CustomerID int,
CustomerName varchar(50),
ContactName varchar(50),
Address varchar(255),
City char(20),
PostalCode char(20),
Country char(20),
PRIMARY KEY (CustomerID)
);
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| CustomerID | int(11) | NO | PRI | 0 | |
| CustomerName | varchar(50) | YES | | NULL | |
| ContactName | varchar(50) | YES | | NULL | |
| Address | varchar(255) | YES | | NULL | |
| City | char(20) | YES | | NULL | |
| PostalCode | char(20) | YES | | NULL | |
| Country | char(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
INSERT INTO `Customers` (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany');
INSERT INTO `Customers` (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (2, 'Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.', '05021', 'Mexico');
INSERT INTO `Customers` (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (3, 'Antonio Moreno Taqueria', 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '05023', 'Mexico');
INSERT INTO `Customers` (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK');
INSERT INTO `Customers` (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (5, 'Berglunds snabbkop', 'Christina Berglund', 'Berguvsvagen 8', 'Lulea', 'S-958 22', 'Sweden');
SELECT * FROM Customers;
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbkop | Christina Berglund | Berguvsvagen 8 | Lulea | S-958 22 | Sweden |
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
SELECT CustomerName,City FROM Customers;
+------------------------------------+-------------+
| CustomerName | City |
+------------------------------------+-------------+
| Alfreds Futterkiste | Berlin |
| Ana Trujillo Emparedados y helados | Mexico D.F. |
| Antonio Moreno Taqueria | Mexico D.F. |
| Around the Horn | London |
| Berglunds snabbkop | Lulea |
+------------------------------------+-------------+
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Kipid', 'Seoul', 'Korea');
-> ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
INSERT INTO Customers (CustomerID, CustomerName, City, Country)
VALUES (7, 'Kipid', 'Seoul', 'Korea');
SELECT * FROM Customers;
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbkop | Christina Berglund | Berguvsvagen 8 | Lulea | S-958 22 | Sweden |
| 0 | Cardinal | NULL | NULL | Stavanger | NULL | Norway |
| 7 | Kipid | NULL | NULL | Seoul | NULL | Korea |
+------------+------------------------------------+--------------------+-------------------------------+-------------+------------+---------+
SELECT DISTINCT City FROM Customers;
+-------------+
| City |
+-------------+
| Berlin |
| Mexico D.F. |
| London |
| Lulea |
| Stavanger |
| Seoul |
+-------------+
SELECT City FROM Customers;
+-------------+
| City |
+-------------+
| Berlin |
| Mexico D.F. |
| Mexico D.F. | // duplicate value
| London |
| Lulea |
| Stavanger |
| Seoul |
+-------------+
SELECT * FROM Customers WHERE Country='Mexico';
+------------+------------------------------------+----------------+-------------------------------+-------------+------------+---------+
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
+------------+------------------------------------+----------------+-------------------------------+-------------+------------+---------+
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
+------------+------------------------------------+----------------+-------------------------------+-------------+------------+---------+
DESC class1.student;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | tinyint(4) | NO | PRI | NULL | |
| name | char(4) | NO | | NULL | |
| sex | enum('남자','여자') | NO | | NULL | |
| address | varchar(50) | NO | | NULL | |
| birthday | datetime | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
INSERT INTO class1.student (id, name)
VALUES (0, '이강수');
INSERT INTO class1.student (id, name, sex, birthday)
VALUES (1, '가나', '남자', '2000-01-01 12:15:12');
INSERT INTO class1.student (id, name, sex, birthday)
VALUES (2, '다라', '여자', '2001-12-01 19:30:45');
INSERT INTO class1.student (id, name, sex, address, birthday)
VALUES (3, '마바', '여자', '서울시 관악구 서울대학교', '2001-12-01 19:30:45');
SELECT * FROM class1.student;
+----+------+------+--------------------------+---------------------+
| id | name | sex | address | birthday |
+----+------+------+--------------------------+---------------------+
| 0 | 이강 | 남자 | | 0000-00-00 00:00:00 |
| 1 | 가나 | 남자 | | 2000-01-01 12:15:12 |
| 2 | 다라 | 여자 | | 2001-12-01 19:30:45 |
| 3 | 마바 | 여자 | 서울시 관악구 서울대학교 | 2001-12-01 19:30:45 |
+----+------+------+--------------------------+---------------------+
SELECT * FROM student;
-> Empty set (0.00 sec)
// "USE class0;"였기 때문에.
INSERT INTO `student` VALUES ('2', 'leezche', '여자', '서울', '2000-10-26');
SELECT * FROM student;
+----+------+------+---------+---------------------+
| id | name | sex | address | birthday |
+----+------+------+---------+---------------------+
| 2 | leez | 여자 | 서울 | 2000-10-26 00:00:00 |
+----+------+------+---------+---------------------+
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` tinyint(4) NOT NULL,
`name` char(8) NOT NULL,
`sex` enum('남자','여자') NOT NULL,
`address` varchar(50) NOT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울', '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '대전', '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', '1981-2-3 00:00:00');
+----+--------+------+---------+---------------------+
| id | name | sex | address | birthday |
+----+--------+------+---------+---------------------+
| 1 | 이숙경 | 여자 | 청주 | 1982-11-16 00:00:00 |
| 2 | 박재숙 | 남자 | 서울 | 1985-10-26 00:00:00 |
| 3 | 백태호 | 남자 | 경주 | 1989-02-10 00:00:00 |
| 4 | 김경훈 | 남자 | 제천 | 1979-11-04 00:00:00 |
| 6 | 김경진 | 여자 | 제주 | 1985-01-01 00:00:00 |
| 7 | 박경호 | 남자 | 영동 | 1981-02-03 00:00:00 |
| 8 | 김정인 | 남자 | 대전 | 1990-10-01 00:00:00 |
+----+--------+------+---------+---------------------+
UPDATE `student` SET address='서울';
+----+--------+------+---------+---------------------+
| id | name | sex | address | birthday |
+----+--------+------+---------+---------------------+
| 1 | 이숙경 | 여자 | 서울 | 1982-11-16 00:00:00 |
| 2 | 박재숙 | 남자 | 서울 | 1985-10-26 00:00:00 |
| 3 | 백태호 | 남자 | 서울 | 1989-02-10 00:00:00 |
| 4 | 김경훈 | 남자 | 서울 | 1979-11-04 00:00:00 |
| 6 | 김경진 | 여자 | 서울 | 1985-01-01 00:00:00 |
| 7 | 박경호 | 남자 | 서울 | 1981-02-03 00:00:00 |
| 8 | 김정인 | 남자 | 서울 | 1990-10-01 00:00:00 |
+----+--------+------+---------+---------------------+ // all addresses are changed.
UPDATE `student` SET name='이진경' WHERE id=1;
+----+--------+------+---------+---------------------+
| id | name | sex | address | birthday |
+----+--------+------+---------+---------------------+
| 1 | 이진경 | 여자 | 서울 | 1982-11-16 00:00:00 | // name changed
| 2 | 박재숙 | 남자 | 서울 | 1985-10-26 00:00:00 |
| 3 | 백태호 | 남자 | 서울 | 1989-02-10 00:00:00 |
| 4 | 김경훈 | 남자 | 서울 | 1979-11-04 00:00:00 |
| 6 | 김경진 | 여자 | 서울 | 1985-01-01 00:00:00 |
| 7 | 박경호 | 남자 | 서울 | 1981-02-03 00:00:00 |
| 8 | 김정인 | 남자 | 서울 | 1990-10-01 00:00:00 |
+----+--------+------+---------+---------------------+
UPDATE `student` SET name='이고잉', birthday='2001-4-1' WHERE id=3;
+----+--------+------+---------+---------------------+
| id | name | sex | address | birthday |
+----+--------+------+---------+---------------------+
| 1 | 이진경 | 여자 | 서울 | 1982-11-16 00:00:00 |
| 2 | 박재숙 | 남자 | 서울 | 1985-10-26 00:00:00 |
| 3 | 이고잉 | 남자 | 서울 | 2001-04-01 00:00:00 | // name and birthday are changed.
| 4 | 김경훈 | 남자 | 서울 | 1979-11-04 00:00:00 |
| 6 | 김경진 | 여자 | 서울 | 1985-01-01 00:00:00 |
| 7 | 박경호 | 남자 | 서울 | 1981-02-03 00:00:00 |
| 8 | 김정인 | 남자 | 서울 | 1990-10-01 00:00:00 |
+----+--------+------+---------+---------------------+
// Demo Database end
// Demo Database end
// Demo Database end
// 삽입 Ex.
/****************** Manipulating Datas ******************/ // end
/****************** Manipulating Datas ******************/ // end
/****************** Manipulating Datas ******************/ // end
/****************** Selecting Datas ******************/ // start
/****************** Selecting Datas ******************/ // start
/****************** Selecting Datas ******************/ // start
/****************** Selecting Datas ******************/ // end
/****************** Selecting Datas ******************/ // end
/****************** Selecting Datas ******************/ // end
/****************** Daum 2011 Test ******************/ // start
/****************** Daum 2011 Test ******************/ // start
/****************** Daum 2011 Test ******************/ // start
CREATE DATABASE `daum2011` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `회원` (
`회원번호` int NOT NULL,
`이름` char(8) NOT NULL,
`회원등급` enum('일반','우수'),
`적립금` int,
PRIMARY KEY (`회원번호`)
);
CREATE TABLE `상품구매내역` (
`상품구매번호` int NOT NULL,
`회원번호` int NOT NULL,
`상품코드` char(4) NOT NULL,
`구매일` date,
PRIMARY KEY (`상품구매번호`)
);
INSERT INTO `회원` VALUES (1001, '이도균', '우수', 30000);
INSERT INTO `회원` VALUES (1002, '김대철', '일반', 10000);
INSERT INTO `회원` VALUES (1003, '박세황', '우수', 30000);
INSERT INTO `회원` VALUES (1004, '오태원', '일반', 10000);
INSERT INTO `회원` VALUES (1005, '송광석', '일반', 0);
+----------+--------+----------+--------+
| 회원번호 | 이름 | 회원등급 | 적립금 |
+----------+--------+----------+--------+
| 1001 | 이도균 | 우수 | 30000 |
| 1002 | 김대철 | 일반 | 10000 |
| 1003 | 박세황 | 우수 | 30000 |
| 1004 | 오태원 | 일반 | 10000 |
| 1005 | 송광석 | 일반 | 0 |
+----------+--------+----------+--------+
INSERT INTO `상품구매내역` VALUES (10001, 1001, 'S001', '2011/07/02');
INSERT INTO `상품구매내역` VALUES (10002, 1001, 'S099', '2011/07/15');
INSERT INTO `상품구매내역` VALUES (10003, 1002, 'S001', '2011/08/10');
INSERT INTO `상품구매내역` VALUES (10004, 1003, 'S078', '2011/08/21');
INSERT INTO `상품구매내역` VALUES (10005, 1004, 'S099', '2011/09/09');
INSERT INTO `상품구매내역` VALUES (10006, 1003, 'S099', '2011/09/17');
INSERT INTO `상품구매내역` VALUES (10007, 1001, 'S017', '2011/09/22');
INSERT INTO `상품구매내역` VALUES (10008, 1003, 'S032', '2011/10/01');
+--------------+----------+----------+------------+
| 상품구매번호 | 회원번호 | 상품코드 | 구매일 |
+--------------+----------+----------+------------+
| 10001 | 1001 | S001 | 2011-07-02 |
| 10002 | 1001 | S099 | 2011-07-15 |
| 10003 | 1002 | S001 | 2011-08-10 |
| 10004 | 1003 | S078 | 2011-08-21 |
| 10005 | 1004 | S099 | 2011-09-09 |
| 10006 | 1003 | S099 | 2011-09-17 |
| 10007 | 1001 | S017 | 2011-09-22 |
| 10008 | 1003 | S032 | 2011-10-01 |
+--------------+----------+----------+------------+
4. “회원” 테이블에서, 적립금이 10000 이상인 회원의 “회원번호”, “이름”, “적립금”을
적립금이 많은 순서대로 조회하는 SQL을 작성하시오 (배점 5점. 부분점수 있음)
(단, 적립금 금액이 같은 경우에는 어떤 데이터가 먼저 나오皐 순서에 상관없음)
SELECT `회원번호`,`이름`,`적립금` FROM `회원` ORDER BY `적립금` DESC;
+----------+--------+--------+
| 회원번호 | 이름 | 적립금 |
+----------+--------+--------+
| 1001 | 이도균 | 30000 |
| 1003 | 박세황 | 30000 |
| 1002 | 김대철 | 10000 |
| 1004 | 오태원 | 10000 |
| 1005 | 송광석 | 0 |
+----------+--------+--------+
5. “회원” 테이블과 “상품구매내역” 테이블을 조인해서,
“회원등급”이 “우수”인 회원이 “상품코드”가 “S099” 인 상품을 언제 구매했는지
“구매일” 을 조회하는 SQL을 작성하시오
(배점 5점. 부분점수 있음)
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,PD.`상품코드`,PD.`구매일`
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
WHERE M.`회원등급`='우수' AND PD.`상품코드`='S099';
+----------+--------+----------+----------+------------+
| 회원번호 | 이름 | 회원등급 | 상품코드 | 구매일 |
+----------+--------+----------+----------+------------+
| 1001 | 이도균 | 우수 | S099 | 2011-07-15 |
| 1003 | 박세황 | 우수 | S099 | 2011-09-17 |
+----------+--------+----------+----------+------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,PD.`상품코드`,PD.`구매일`
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`;
+----------+--------+----------+----------+------------+
| 회원번호 | 이름 | 회원등급 | 상품코드 | 구매일 |
+----------+--------+----------+----------+------------+
| 1001 | 이도균 | 우수 | S001 | 2011-07-02 |
| 1001 | 이도균 | 우수 | S099 | 2011-07-15 |
| 1001 | 이도균 | 우수 | S017 | 2011-09-22 |
| 1002 | 김대철 | 일반 | S001 | 2011-08-10 |
| 1003 | 박세황 | 우수 | S078 | 2011-08-21 |
| 1003 | 박세황 | 우수 | S099 | 2011-09-17 |
| 1003 | 박세황 | 우수 | S032 | 2011-10-01 |
| 1004 | 오태원 | 일반 | S099 | 2011-09-09 |
| 1005 | 송광석 | 일반 | NULL | NULL |
+----------+--------+----------+----------+------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,PD.`상품코드`,PD.`구매일`
FROM `상품구매내역` AS PD LEFT JOIN `회원` AS M
ON PD.`회원번호`=M.`회원번호`;
+----------+--------+----------+----------+------------+
| 회원번호 | 이름 | 회원등급 | 상품코드 | 구매일 |
+----------+--------+----------+----------+------------+
| 1001 | 이도균 | 우수 | S001 | 2011-07-02 |
| 1001 | 이도균 | 우수 | S099 | 2011-07-15 |
| 1002 | 김대철 | 일반 | S001 | 2011-08-10 |
| 1003 | 박세황 | 우수 | S078 | 2011-08-21 |
| 1004 | 오태원 | 일반 | S099 | 2011-09-09 |
| 1003 | 박세황 | 우수 | S099 | 2011-09-17 |
| 1001 | 이도균 | 우수 | S017 | 2011-09-22 |
| 1003 | 박세황 | 우수 | S032 | 2011-10-01 |
+----------+--------+----------+----------+------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,PD.`상품코드`,PD.`구매일`
FROM `회원` AS M RIGHT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`;
+----------+--------+----------+----------+------------+
| 회원번호 | 이름 | 회원등급 | 상품코드 | 구매일 |
+----------+--------+----------+----------+------------+
| 1001 | 이도균 | 우수 | S001 | 2011-07-02 |
| 1001 | 이도균 | 우수 | S099 | 2011-07-15 |
| 1002 | 김대철 | 일반 | S001 | 2011-08-10 |
| 1003 | 박세황 | 우수 | S078 | 2011-08-21 |
| 1004 | 오태원 | 일반 | S099 | 2011-09-09 |
| 1003 | 박세황 | 우수 | S099 | 2011-09-17 |
| 1001 | 이도균 | 우수 | S017 | 2011-09-22 |
| 1003 | 박세황 | 우수 | S032 | 2011-10-01 |
+----------+--------+----------+----------+------------+
개인적인 test. '2011/09/01'보다 이후에 구입한 물품들수, 종류수를 출력.
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS '구입 물품수',count(DISTINCT PD.`상품코드`) AS '구입 물품 종류수'
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
WHERE PD.`구매일`>'2011/09/01'
GROUP BY M.`회원번호`;
// "GROUP BY"가 제일 마지막에 위치.
+----------+--------+----------+-------------+------------------+
| 회원번호 | 이름 | 회원등급 | 구입 물품수 | 구입 물품 종류수 |
+----------+--------+----------+-------------+------------------+
| 1001 | 이도균 | 우수 | 1 | 1 |
| 1003 | 박세황 | 우수 | 2 | 2 |
| 1004 | 오태원 | 일반 | 1 | 1 |
+----------+--------+----------+-------------+------------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS '구입 물품수',count(DISTINCT PD.`상품코드`) AS '구입 물품 종류수'
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
WHERE PD.`구매일`>'2011/09/01'
GROUP BY M.`회원번호`
ORDER BY `구입 물품수` DESC;
+----------+--------+----------+-------------+------------------+
| 회원번호 | 이름 | 회원등급 | 구입 물품수 | 구입 물품 종류수 |
+----------+--------+----------+-------------+------------------+
| 1003 | 박세황 | 우수 | 2 | 2 |
| 1001 | 이도균 | 우수 | 1 | 1 |
| 1004 | 오태원 | 일반 | 1 | 1 |
+----------+--------+----------+-------------+------------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS '구입 물품수',count(DISTINCT PD.`상품코드`) AS '구입 물품 종류수'
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
GROUP BY M.`회원번호`;
+----------+--------+----------+-------------+------------------+
| 회원번호 | 이름 | 회원등급 | 구입 물품수 | 구입 물품 종류수 |
+----------+--------+----------+-------------+------------------+
| 1001 | 이도균 | 우수 | 3 | 3 |
| 1002 | 김대철 | 일반 | 1 | 1 |
| 1003 | 박세황 | 우수 | 3 | 3 |
| 1004 | 오태원 | 일반 | 1 | 1 |
| 1005 | 송광석 | 일반 | 0 | 0 |
+----------+--------+----------+-------------+------------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS '구입 물품수',count(DISTINCT PD.`상품코드`) AS '구입 물품 종류수'
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
GROUP BY M.`회원번호`
ORDER BY `구입 물품수` DESC;
+----------+--------+----------+-------------+------------------+
| 회원번호 | 이름 | 회원등급 | 구입 물품수 | 구입 물품 종류수 |
+----------+--------+----------+-------------+------------------+
| 1003 | 박세황 | 우수 | 3 | 3 |
| 1001 | 이도균 | 우수 | 3 | 3 |
| 1004 | 오태원 | 일반 | 1 | 1 |
| 1002 | 김대철 | 일반 | 1 | 1 |
| 1005 | 송광석 | 일반 | 0 | 0 |
+----------+--------+----------+-------------+------------------+
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS `구입 물품수`,count(DISTINCT PD.`상품코드`) AS `구입 물품 종류수`
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`
GROUP BY M.`회원번호`
ORDER BY `구입 물품수` DESC;
SELECT M.`회원번호`,M.`이름`,M.`회원등급`,count(PD.`상품코드`) AS '구입 물품수',count(DISTINCT PD.`상품코드`) AS '구입 물품 종류수'
FROM `회원` AS M LEFT JOIN `상품구매내역` AS PD
ON PD.`회원번호`=M.`회원번호`;
+----------+--------+----------+-------------+------------------+
| 회원번호 | 이름 | 회원등급 | 구입 물품수 | 구입 물품 종류수 |
+----------+--------+----------+-------------+------------------+
| 1001 | 이도균 | 우수 | 8 | 5 |
+----------+--------+----------+-------------+------------------+
SELECT p.회원번호, p.이름, count(DISTINCT pd.상품코드) AS 구매갯수
FROM 회원 AS p
INNER JOIN 상품구매내역 AS pd
ON p.회원번호=pd.회원번호
GROUP BY p.회원번호
WHERE pd.구매일>'2011/09/01';
/****************** Daum 2011 Test ******************/ // end
/****************** Daum 2011 Test ******************/ // end
/****************** Daum 2011 Test ******************/ // end
/****************** Daum 2012 Test ******************/ // start
/****************** Daum 2012 Test ******************/ // start
/****************** Daum 2012 Test ******************/ // start
CREATE DATABASE `daum2012`;
USE `daum2012`;
CREATE TABLE `학생`(
`학번` char(10) PRIMARY KEY,
`이름` char(10) NOT NULL,
`성별` enum('M','F'),
`생년월일` date
);
DESC `학생`;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| 학번 | char(10) | NO | PRI | NULL | |
| 이름 | char(10) | NO | | NULL | |
| 성별 | enum('M','F') | YES | | NULL | |
| 생년월일 | date | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
SHOW CREATE TABLE `학생`;
+-------+------------------------------+
| Table | Create Table |
+-------+------------------------------+
| 학생 | CREATE TABLE `嗚생` (
`학번` char(10) NOT NULL,
`이름` char(10) NOT NULL,
`성별` enum('M','F') DEFAULT NULL,
`생년월일` date DEFAULT NULL,
PRIMARY KEY (`학번`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------+
INSERT INTO `학생` VALUES ('S001', '김세용', 'M', '1986-05-21');
INSERT INTO `학생` VALUES ('S002', '박신주', 'F', '1990-07-12');
INSERT INTO `학생` VALUES ('S003', '최경아', 'F', '1988-12-09');
INSERT INTO `학생` VALUES ('S004', '박진성', 'M', '1991-01-11');
INSERT INTO `학생` VALUES ('S005', '이창진', 'M', '1987-01-25');
INSERT INTO `학생` VALUES ('S006', '이창진', 'M', '1987-03-01');
SELECT * FROM `학생`;
+------+--------+------+------------+
| 학번 | 이름 | 성별 | 생년월일 |
+------+--------+------+------------+
| S001 | 김세용 | M | 1986-05-21 |
| S002 | 박신주 | F | 1990-07-12 |
| S003 | 최경아 | F | 1988-12-09 |
| S004 | 박진성 | M | 1991-01-11 |
| S005 | 이창진 | M | 1987-01-25 |
| S006 | 이창진 | M | 1987-03-01 |
+------+--------+------+------------+
CREATE TABLE `수강신청`(
`신청번호` char(10) PRIMARY KEY,
`학번` char(10) NOT NULL,
`수강과목명` varchar(100) NOT NULL,
`신청일` date NOT NULL,
`취소일` date DEFAULT NULL,
CONSTRAINT `fk_Sid` FOREIGN KEY (`학번`) REFERENCES `학생`(`학번`)
);
DESC `수강신청`;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| 신청번호 | char(10) | NO | PRI | NULL | |
| 학번 | char(10) | NO | MUL | NULL | |
| 수강과목명 | varchar(100) | NO | | NULL | |
| 신청일 | date | NO | | NULL | |
| 취소일 | date | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
SHOW CREATE TABLE `수강신청`;
+----------+--------------------------------+
| Table | Create Table |
+----------+--------------------------------+
| 수강신청 | CREATE TABLE `수강썲屍` (
`신청번호` char(10) NOT NULL,
`학번` char(10) NOT NULL,
`수강과목명` varchar(100) NOT NULL,
`신청일` date NOT NULL,
`취소일` date DEFAULT NULL,
PRIMARY KEY (`신청번호`),
KEY `fk_Sid` (`학번`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+--------------------------------+
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00001', 'S001', '영어', '2012-07-21');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`, `취소일`) VALUES ('00002', 'S002', '국어', '2012-07-24', '2012-08-22');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00003', 'S004', '컴퓨터 프로그래밍', '2012-07-18');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00004', 'S001', '컴퓨터 프로그래밍', '2012-07-19');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00005', 'S001', '통계학 개론', '2012-07-21');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00006', 'S005', '영어', '2012-07-30');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00007', 'S002', '통계학 개론', '2012-07-25');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00008', 'S004', '국어', '2012-07-21');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00009', 'S003', '영어', '2012-07-23');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00010', 'S002', '영어', '2012-07-23');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00011', 'S001', 'DBMS 개론', '2012-07-20');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00012', 'S001', '통계학 개론', '2012-07-22');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`, `취소일`) VALUES ('00013', 'S002', '통계학 개론', '2012-07-19', '2012-07-22');
UPDATE `수강신청` SET `취소일`='2012-08-01' WHERE `신청번호`='00004';
UPDATE `수강신청` SET `취소일`='2012-08-01' WHERE `신청번호`='00005';
UPDATE `수강신청` SET `취소일`='2012-08-01' WHERE `신청번호`='00006';
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00014', 'S015', '통계학 개론', '2012-07-22');
INSERT INTO `수강신청` (`신청번호`, `학번`, `수강과목명`, `신청일`) VALUES ('00015', 'S014', '통계학 개론', '2012-07-22');
ALTER TABLE `수강신청` ADD CONSTRAINT fk_Sid FOREIGN KEY (`학번`) REFERENCES `학생`(`학번`);
// FOREIGN KEY 설정이 뭔가 이상한듯? 학생이 없어도 데이터가 들어가지네.
SELECT * FROM `수강신청`;
+----------+------+-------------------+------------+------------+
| 신청번호 | 학번 | 수강과목명 | 신청일 | 취소일 |
+----------+------+-------------------+------------+------------+
| 00001 | S001 | 영어 | 2012-07-21 | NULL |
| 00002 | S002 | 국어 | 2012-07-24 | 2012-08-22 |
| 00003 | S004 | 컴퓨터 프로그래밍 | 2012-07-18 | NULL |
| 00004 | S001 | 컴퓨터 프로그래밍 | 2012-07-19 | 2012-08-01 |
| 00005 | S001 | 통계학 개론 | 2012-07-21 | 2012-08-01 |
| 00006 | S005 | 영어 | 2012-07-30 | 2012-08-01 |
| 00007 | S002 | 통계학 개론 | 2012-07-25 | NULL |
| 00008 | S004 | 국어 | 2012-07-21 | NULL |
| 00009 | S003 | 영어 | 2012-07-23 | NULL |
| 00010 | S002 | 영어 | 2012-07-23 | NULL |
| 00011 | S001 | DBMS 개론 | 2012-07-20 | NULL |
| 00012 | S001 | 통계학 개론 | 2012-07-22 | NULL |
| 00013 | S002 | 통계학 개론 | 2012-07-19 | 2012-07-22 |
| 00014 | S015 | 통계학 개론 | 2012-07-22 | NULL |
| 00015 | S014 | 통계학 개론 | 2012-07-22 | NULL |
+----------+------+-------------------+------------+------------+
SELECT s.`학번`, s.`이름`, s.`생년월일`, (count(a.`신청일`)-count(a.`취소일`)) AS "수강신청한 과목수"
FROM (`학생` AS s LEFT JOIN `수강신청` AS a
ON s.`학번`=a.`학번`)
GROUP BY s.`학번`
ORDER BY s.`생년월일` DESC;
+------+--------+------------+-------------------+
| 학번 | 이름 | 생년월일 | 수강신청한 과목수 |
+------+--------+------------+-------------------+
| S004 | 박진성 | 1991-01-11 | 2 |
| S002 | 박신주 | 1990-07-12 | 2 |
| S003 | 최경아 | 1988-12-09 | 1 |
| S006 | 이창진 | 1987-03-01 | 0 |
| S005 | 이창진 | 1987-01-25 | 0 |
| S001 | 김세용 | 1986-05-21 | 3 |
+------+--------+------------+-------------------+
// ORDER BY s.`학번` ASC; 요건 그냥 자동으로 됨. group by에 의해서...
+------+--------+------------+-------------------+
| 학번 | 이름 | 생년월일 | 수강신청한 과목수 |
+------+--------+------------+-------------------+
| S001 | 김세용 | 1986-05-21 | 3 |
| S002 | 박신주 | 1990-07-12 | 2 |
| S003 | 최경아 | 1988-12-09 | 1 |
| S004 | 박진성 | 1991-01-11 | 2 |
| S005 | 이창진 | 1987-01-25 | 0 |
| S006 | 이창진 | 1987-03-01 | 0 |
+------+--------+------------+-------------------+
SELECT s.`학번`, s.`이름`, s.`생년월일`, a.`신청번호`, a.`수강과목명`, a.`신청일`, a.`취소일`
FROM (`학생` AS s LEFT JOIN `수강신청` AS a
ON s.`학번`=a.`학번`);
+------+--------+------------+----------+-------------------+------------+------------+
| 학번 | 이름 | 생년월일 | 신청번호 | 수강과목명 | 신청일 | 취소일 |
+------+--------+------------+----------+-------------------+------------+------------+
| S001 | 김세용 | 1986-05-21 | 00001 | 영어 | 2012-07-21 | NULL |1
| S001 | 김세용 | 1986-05-21 | 00004 | 컴퓨터 프로그래밍 | 2012-07-19 | 2012-08-01 |2
| S001 | 김세용 | 1986-05-21 | 00005 | 통계학 개론 | 2012-07-21 | 2012-08-01 |3
| S001 | 김세용 | 1986-05-21 | 00011 | DBMS 개론 | 2012-07-20 | NULL |4
| S001 | 김세용 | 1986-05-21 | 00012 | 통계학 개론 | 2012-07-22 | NULL |5
| S002 | 박신주 | 1990-07-12 | 00002 | 국어 | 2012-07-24 | 2012-08-22 |6
| S002 | 박신주 | 1990-07-12 | 00007 | 통계학 개론 | 2012-07-25 | NULL |7
| S002 | 박신주 | 1990-07-12 | 00010 | 영어 | 2012-07-23 | NULL |8
| S002 | 박신주 | 1990-07-12 | 00013 | 통계학 개론 | 2012-07-19 | 2012-07-22 |9
| S003 | 최경아 | 1988-12-09 | 00009 | 영어 | 2012-07-23 | NULL |10
| S004 | 박진성 | 1991-01-11 | 00003 | 컴퓨터 프로그래밍 | 2012-07-18 | NULL |11
| S004 | 박진성 | 1991-01-11 | 00008 | 국어 | 2012-07-21 | NULL |12
| S005 | 이창진 | 1987-01-25 | 00006 | 영어 | 2012-07-30 | 2012-08-01 |13
| S006 | 이창진 | 1987-03-01 | NULL | NULL | NULL | NULL |14
+------+--------+------------+----------+-------------------+------------+------------+
// count(a.`취소일`)에서 (a.`최소일`)이 NULL이면 count하지 않음.
// count(*)은 전체가 NULL이어여 count하지 않기 때문에 (그런 데이터는 없으므로) 전체가 count.
// count(DISTINCT a.`수강과목명`)은 과목명이 다른 것들만 count. 즉 (영어, 영어, 국어)는 count=2.
ALTER TABLE `학생` ADD `수강신청여부` enum('Y','N') DEFAULT 'N';
// ALTER TABLE `학생` MODIFY COLUMN `수강신청여부` enum('Y','N') DEFAULT 'N'
// ALTER TABLE `학생` DROP `수강신청여부`;
// UPDATE `학생` SET `수강신청여부`='N';
SELECT * FROM `학생`;
+------+--------+------+------------+--------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 |
+------+--------+------+------------+--------------+
| S001 | 김세용 | M | 1986-05-21 | N |
| S002 | 박신주 | F | 1990-07-12 | N |
| S003 | 최경아 | F | 1988-12-09 | N |
| S004 | 박진성 | M | 1991-01-11 | N |
| S005 | 이창진 | M | 1987-01-25 | N |
| S006 | 이창진 | M | 1987-03-01 | N |
+------+--------+------+------------+--------------+
UPDATE `학생` AS s SET `수강신청여부`='Y'
WHERE 0<(SELECT (count(a.`신청일`)-count(a.`취소일`))
FROM `수강신청` AS a
WHERE s.`학번`=a.`학번`);
SELECT * FROM `학생`;
+------+--------+------+------------+--------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 |
+------+--------+------+------------+--------------+
| S001 | 김세용 | M | 1986-05-21 | Y |
| S002 | 박신주 | F | 1990-07-12 | Y |
| S003 | 최경아 | F | 1988-12-09 | Y |
| S004 | 박진성 | M | 1991-01-11 | Y |
| S005 | 이창진 | M | 1987-01-25 | N |
| S006 | 이창진 | M | 1987-03-01 | N |
+------+--------+------+------------+--------------+
ALTER TABLE `학생` ADD `수강 과목수` int DEFAULT 0;
SELECT * FROM `학생`;
+------+--------+------+------------+--------------+-------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 | 수강 과목수 |
+------+--------+------+------------+--------------+-------------+
| S001 | 김세용 | M | 1986-05-21 | Y | 0 |
| S002 | 박신주 | F | 1990-07-12 | Y | 0 |
| S003 | 최경아 | F | 1988-12-09 | Y | 0 |
| S004 | 박진성 | M | 1991-01-11 | Y | 0 |
| S005 | 이창진 | M | 1987-01-25 | N | 0 |
| S006 | 이창진 | M | 1987-03-01 | N | 0 |
+------+--------+------+------------+--------------+-------------+
UPDATE `학생` AS s SET `수강 과목수`=(SELECT (count(a.`신청일`)-count(a.`취소일`))
FROM `수강신청` AS a
WHERE s.`학번`=a.`학번`);
SELECT * FROM `학생`;
+------+--------+------+------------+--------------+-------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 | 수강 과목수 |
+------+--------+------+------------+--------------+-------------+
| S001 | 김세용 | M | 1986-05-21 | Y | 3 |
| S002 | 박신주 | F | 1990-07-12 | Y | 2 |
| S003 | 최경아 | F | 1988-12-09 | Y | 1 |
| S004 | 박진성 | M | 1991-01-11 | Y | 2 |
| S005 | 이창진 | M | 1987-01-25 | N | 0 |
| S006 | 이창진 | M | 1987-03-01 | N | 0 |
+------+--------+------+------------+--------------+-------------+
SELECT avg(`수강 과목수`) FROM `학생`;
+--------------------+
| avg(`수강 과목수`) |
+--------------------+
| 1.3333 |
+--------------------+
SELECT avg(`수강 과목수`) FROM `학생` WHERE `수강신청여부`='Y';
SELECT avg(`수강 과목수`) FROM `학생` WHERE `수강 과목수`>0;
+--------------------+
| avg(`수강 과목수`) |
+--------------------+
| 2.0000 |
+--------------------+
SELECT * FROM `학생`
WHERE `수강 과목수`>=avg(`수강 과목수`); // 이렇게 쓰면 에러: ERROR 1111 (HY000): Invalid use of group function
SELECT * FROM `학생`
WHERE `수강 과목수`>=(SELECT avg(`수강 과목수`) FROM `학생`);
+------+--------+------+------------+--------------+-------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 | 수강 과목수 |
+------+--------+------+------------+--------------+-------------+
| S001 | 김세용 | M | 1986-05-21 | Y | 3 |
| S002 | 박신주 | F | 1990-07-12 | Y | 2 |
| S004 | 박진성 | M | 1991-01-11 | Y | 2 |
+------+--------+------+------------+--------------+-------------+
SELECT * FROM `학생`
WHERE `수강 과목수`>=(SELECT avg(`수강 과목수`) FROM `학생` WHERE `수강신청여부`='Y');
+------+--------+------+------------+--------------+-------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 | 수강 과목수 |
+------+--------+------+------------+--------------+-------------+
| S001 | 김세용 | M | 1986-05-21 | Y | 3 |
| S002 | 박신주 | F | 1990-07-12 | Y | 2 |
| S004 | 박진성 | M | 1991-01-11 | Y | 2 |
+------+--------+------+------------+--------------+-------------+
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
// 위와 같은 조건문도 되는듯.
UPDATE PROJECT SET DEPTNO = (SELECT WORKDEPT FROM EMPLOYEE
WHERE PROJECT.RESPEMP = EMPLOYEE.EMPNO)
WHERE RESPEMP='000030';
// WHERE 두번 걸기.
// Back up
// Back up
// Back up
CREATE TABLE `학생 backup`(
`학번` char(10) PRIMARY KEY,
`이름` char(10) NOT NULL,
`성별` enum('M','F'),
`생년월일` date
);
INSERT INTO `학생 backup` (`학번`, `이름`, `성별`, `생년월일`)
SELECT `학번`, `이름`, `성별`, `생년월일` FROM `학생`;
SELECT * FROM `학생 backup`;
CREATE TABLE `수강신청 backup`(
`신청번호` char(10) PRIMARY KEY,
`학번` char(10) NOT NULL,
`수강과목명` varchar(100) NOT NULL,
`신청일` date NOT NULL,
`취소일` date DEFAULT NULL,
CONSTRAINT `fk_Sid` FOREIGN KEY (`학번`) REFERENCES `학생`(`학번`)
);
INSERT INTO `수강신청 backup` SELECT * FROM `수강신청`;
SELECT * FROM `수강신청 backup`;
/* 이거 안되는데??? */
UPDATE `학생` AS s SET (`학번`, `이름`, `성별`, `생년월일`)=
(SELECT `학번`, `이름`, `성별`, `생년월일` FROM `학생 backup` AS b WHERE s.`학번`=b.`학번`)
WHERE s.`학번`='S014';
UPDATE `학생` SET (`학번`, `이름`, `성별`, `생년월일`) =
('S014', '가나다', 'M', '1990-01-01')
WHERE `학번`='S014';
// 이거 안되는데???
INSERT INTO `학생` (`학번`, `이름`, `성별`, `생년월일`) VALUES ('S014', '가나다', 'M', '1990-01-01');
INSERT INTO `학생` (`학번`, `이름`, `성별`, `생년월일`) VALUES ('S015', '마바사', 'F', '1992-07-01');
UPDATE `학생` AS s SET `수강신청여부`='Y'
WHERE 0<(SELECT (count(a.`신청일`)-count(a.`취소일`))
FROM `수강신청` AS a
WHERE s.`학번`=a.`학번`);
UPDATE `학생` AS s SET `수강 과목수`=(SELECT (count(a.`신청일`)-count(a.`취소일`)) FROM `수강신청` AS a
WHERE s.`학번`=a.`학번`);
mysql> SELECT * FROM `학생`;
+------+--------+------+------------+--------------+-------------+
| 학번 | 이름 | 성별 | 생년월일 | 수강신청여부 | 수강 과목수 |
+------+--------+------+------------+--------------+-------------+
| S001 | 김세용 | M | 1986-05-21 | Y | 3 |
| S002 | 박신주 | F | 1990-07-12 | Y | 2 |
| S003 | 최경아 | F | 1988-12-09 | Y | 1 |
| S004 | 박진성 | M | 1991-01-11 | Y | 2 |
| S005 | 이창진 | M | 1987-01-25 | N | 0 |
| S006 | 이창진 | M | 1987-03-01 | N | 0 |
| S014 | 가나다 | M | 1990-01-01 | Y | 1 |
| S015 | 마바사 | F | 1992-07-01 | Y | 1 |
+------+--------+------+------------+--------------+-------------+
/****************** Daum 2012 Test ******************/ // end
/****************** Daum 2012 Test ******************/ // end
/****************** Daum 2012 Test ******************/ // end
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
```/
#### CREATE INDEX
참조: spiderwebcoding.tistory.com :: [MySQL] 인덱스(INDEX) 정리 (효과적인 INDEX 설계)
```[.lang-sql]
-- 단일 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1);
-- 다중 컬럼 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...);
-- 인덱스 조회
SHOW INDEX FROM 테이블이름;
UNUQUE 인덱스 생성(중복 값을 허용하지 않는 인덱스)
-- 단일 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1);
-- 다중 컬럼 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1, 필드이름2, ...);
인덱스 정렬(인덱스 생성 시점에 필드의 정렬방식 설정)
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 DESC);
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 ASC);
인덱스 삭제
ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;
인덱스 추가
ALTER TABLE 테이블이름 ADD (UNIQUE)INDEX 인덱스이름(컬럼명1, 컬럼명2...);
```/
### 명령어 분류 (DCL, DDL, DML, TCL, CRUD?)
명령어를 크게 CRUD 랑 어쩌구로 나뉘는듯?
```
CRUD : Create (생성), Read (읽기), Update (갱신), Delete (삭제)
DCL (Data Control Language, User 관리) : Grant, Revoke
DDL (Data Definition Language, DB 및 Table 관리) : Create, Alter, Drop
DML (Data Manipulation Language, Record 작업) : Select, Insert, Update, Delete
TCL (Transaction Control Language) : Commit, Rollback, Savepoint
```/
이런 분류가 큰 의미가 있나? =ㅇ=;;; SQL 만들고 디자인 하는 사람이 아니라면 그냥 잘 쓸 줄 알면 될듯? 명령어 체계적으로 암기하는데에는 좋긴 하구나.
## JDBC (Java DataBase Connector)
데이터베이스는 서버에서 접근해서 처리하는 경우가 많아서, cmd 창이나 sql shell 에서 명령어로 어떻게 데이터를 처리하는지 아는것에 추가로 서버를 작성한 언어에서 어떻게 접근해서 데이터를 다룰 수 있는지도 알아야 한다.
여기서는 JAVA (서버를 자바로 다루는 경우가 많아서) 에서 MySQL 혹은 Java DB를 어떻게 다룰 수 있는지 설명. 자세한 설명은 에 있음.
우선 MySQL - Connector/J 에서 다운받고, 설명은 MySQL Connector/J Developer Guide
www.docjar.com - docs/api/com/mysql/jdbc/jdbc2/optional/MysqlDataSource.html
### Transaction (All or Nothing)
Transaction 은 참고. 가장 간단하게 설명하면 에러가 났을때를 대비 All or Nothing 이 실행되도록 만들어 놓은것. 한묶음으로 실행되어야 하는 계좌이체 같은 것들에 쓰임.
#### Console 명령어들
Console 명령어들. (테스트는 안해봄. 에서 대부분 긁어온건데 에러 있을지도... 세미콜론 ';' 이 중간중간 빠진거 같아서 넣어놓긴 했음. 빼야하는건가;;;;;)
```[.linenums.lang-sql]
-- 자동 커밋 트랜잭션
Delete TB_Member Where isValid='N';
-- 명시적 트랜잭션
Begin TRAN
Delete TB_Member Where user_id='tester';
Commit TRAN
```/
```[.linenums.lang-sql]
BEGIN TRAN
Update TB_Account Set money = money - 100000 Where id='A';
Update TB_Account Set money = money + 100000 Where id='B';
Insert TB_Account_Log(id, money) Values ('A',-100000);
Insert TB_Account_Log(id, money) Values ('B',100000);
IF (@@ERROR <> 0)
ROLLBACK TRAN
ELSE
COMMIT TRAN
GO
```/
```[.linenums.lang-sql]
SET XACT_ABORT OFF
GO
BEGIN TRAN
UPDATE TB_Account SET money = money - 100000 WHERE id='A';
UPDATE TB_Account SET money = money + 100000 WHERE id='B';
INSERT TB_Account_Log(id, money)VALUES('A',-100000);
INSERT TB_Account_Log(id, money)VALUES('B',100000);
COMMIT TRAN
GO
```/
```[.linenums.lang-sql]
BEGIN TRAN TranMaster
UPDATE TB_Account SET money = money - 100000 WHERE id='A';
UPDATE TB_Account SET money = money + 100000 WHERE id='B';
SAVE TRAN TransSubSave
INSERT TB_Account_Log(id, money) VALUES ('A',-100000);
INSERT TB_Account_Log(id, money) VALUES ('B',100000);
ROLLBACK TRAN TranSubSave
COMMIT TRAN TranMaster
```/
#### JAVA 명령어들
우선 MySQL 및 JDBC connector/J 가 설치되어 있어야 하고, JAVA 도 당연히 설치되어 있어야 함. 설치 관련은 참고하시길.
그리고 Table 생성은 그냥 MySQL Console 에서 다음과 같이 합시다.
```[.linenums.lang-sql]
CREATE TABLE `Users` (
`i` bigint
, `id` char(21) NOT NULL UNIQUE
, `email` varchar(50) NOT NULL UNIQUE
, `class` int NOT NULL DEFAULT 0 #0: Not verified yet
, PRIMARY KEY (`i`)
);
```/
아래와 같은 JAVA code 로 테스트 해볼 수 있음.
```[.scrollable.lang-java]
package kipid.jdbc; // jdbc test by kipid
import java.sql.*;
import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
public class TransactionTest {
public static void main(String... args) {
MysqlConnectionPoolDataSource ds=new MysqlConnectionPoolDataSource();
ds.setServerName("serverName");
ds.setPort(3306);
ds.setDatabaseName("dbName");
// ds.setURL("serverName/dbName");
ds.setUser("user");
ds.setPassword("user password");
Connection con=ds.getConnection();
PreparedStatement pstmtCreateUser=con.prepareStatement("INSERT INTO `Users` (`i`, `id`, `email`) VALUES (?, ?, ?);");
PreparedStatement pstmtFindUserById=con.prepareStatement("SELECT * FROM `Users` WHERE `id`=?;"
, ResultSet.TYPE_SCROLL_SENSITIVE
, ResultSet.CONCUR_UPDATABLE
);
try {
con.setAutoCommit(false);
pstmtCreateUser.setLong(1, (long)10);
pstmtCreateUser.setString(2, "test id");
pstmtCreateUser.setString(3, "test email");
if (pstmtCreateUser.executeUpdate()>0) {
pstmtFindUserById.setString(1, id);
user=pstmtFindUserById.executeQuery();
if (user.next()) {
user.updateInt("class", 6);
user.updateRow(); // 이것도 autoCommit 영향받는건가? 당연히 받을거라 생각하긴 하는데, 테스트 좀 해봐야지.
done=true;
}
}
} catch (SQLException e) {
err(e); // printing error.
}
try {
if (done) {
con.commit();
} else {
con.rollback();
}
} catch (SQLException e) {
err(e); // printing error.
}
}
}
```/
## RRA
- SQL Quick Reference From W3Schools
- IBM Knowledge Center - database
- Mongo DB.org
- dev.MySQL.com; and dev.mysql.com - Download MySQL Community Server
- dev.mysql.com - MySQL 5.6 Reference Manual
- dev.mysql.com - Installing MySQL on Microsoft Windows Using a noinstall Zip Archive; and bang2001's blog - Window에서 MySQL 5.6.14 설치하기, 2013-11-14, by Elinjade
- 생활코딩 Open Tutorials.org - MySQL, and MySQL 수업, and MySQL 고급
- kipid's blog - Installing and Learning JAVA
- docs.oracle.com - Trail: JDBC(TM) Database Access: Table of Contents
- dev.mysql.com - START TRANSACTION, COMMIT, and ROLLBACK Syntax; and The Java™ Tutorials - Using Transactions; and tutorialspoint.com - JDBC - Transactions
- devholic.net - 트랜잭션 (Transaction)
- helloworld.naver.com - 한글 인코딩의 이해 1편: 한글 인코딩의 역사와 유니코드, 2012-02-29, by NHN Business Platform 쇼핑서비스개발팀 오영은.
- helloworld.naver.com - 한글 인코딩의 이해 2편: 유니코드와 Java를 이용한 한글 처리, 2012-06-07, by NHN Business Platform 쇼핑서비스개발팀 오영은.
Database
MySQL
JDBC (Java DataBase Connector)
Transaction
Encoding
Etc.
728x90
반응형
