|
5. MySQL의 column types
이장에서는 다양한 column type들과 그 예를 배운다.
5.1 수치 데이타 타입
M : 표시할수 있는 최대 사이즈. 최대 255
D : 소수점이하의 숫자 갯수
- TINYINT[(M)] [UNSIGNED] [ZEROFILL]
정수형(1 byte)으로 -128부터 127까지. unsigned로 사용시 0부터 255
- SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
정수형(2 byte)으로 -32768부터 32767까지. unsigned로 사용시 0부터 65535
- MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
정수형(3 byte)으로 -8388608부터 8388607까지. unsigned로 사용시 0부터 16777215
- INT[(M)] [UNSIGNED] [ZEROFILL]
정수형(4 byte)으로 -2147483648부터 214748364까지. unsigned로 사용시 0부터
4294967295
- INTEGER[(M)] [UNSIGNED] [ZEROFILL]
위의 INT와 같다.
- BIGINT[(M)] [UNSIGNED] [ZEROFILL]
정수형(8 byte)으로 -922337203685477580 부터 9223372036854775807까지.
unsigned로 사용시 0부터 18446744073709551615
- FLOAT(precision) [ZEROFILL]
부동 소수(4,8 byte) , unsinged는 사용할수 없다. precision은 4또는 8
FLOAT(4)는 일반적인 FLOAT형이며
FLOAT(8)는 DOUBLE형이라고 생각하면 된다.
- FLOAT[(M,D)] [ZEROFILL]
부동소수 (4byte) , unsigned는 사용할수 없다.
-3.402823466E+38 에서 -1.175494351E-38, 0 그리고
1.175494351E-38 에서 3.402823466E+38.
- DOUBLE[(M,D)] [ZEROFILL]
부동소수 (8byte) , unsigned는 사용할수 없다.
-1.7976931348623157E+308에서 -2.2250738585072014E-308, 0 그리고
2.2250738585072014E-308에서 1.7976931348623157E+308.
- DOUBLE PRECISION[(M,D)] [ZEROFILL]
(8 byte)
- REAL[(M,D)] [ZEROFILL]
DOUBLE 과 같다.
- DECIMAL(M,D) [ZEROFILL]
(M byte)unpacked floating-point number, unsigned는 사용할수 없다.
이것은 각각의 숫자를 CHAR로 저장한다.
- NUMERIC(M,D) [ZEROFILL]
DECIMAL과 같다.
[b]5.2 문자 테이타 타입[/b]
- CHAR(M) [BINARY]
문자의 수가 M인 문자열
- VARCHAR(M) [BINARY]
문자의 수가 최대 M인 문자열
- TINYBLOB
255 (2^8 - 1)문자.
- TINYTEXT
255 (2^8 - 1)문자.
- TEXT
65535 (2^16 - 1)문자.
- BLOB ( Binary Large OBject )
65535 (2^16 - 1)문자.
- MEDIUMBLOB
16777215 (2^24 - 1)문자.
- MEDIUMTEXT
16777215 (2^24 - 1)문자.
- LONGBLOB
4294967295 (2^32 - 1)문자.
- LONGTEXT
4294967295 (2^32 - 1)문자.
[참고 ]
- 모든 문자는 \ 또는 \ 로 묶어 준다.
C에서의 같이 \\는 문자열 안에서 특수한 의미를 가진다.
\0 NULcharacter.
\n newline character.
\t tab character.
\r carriage return character.
\b backspace character.
\ single quote (`\) character.
\ double quote (`\) character.
\ backslash (`\) character.
\% `%\ character. 이것은 like문안에서 모든 문자를 의미한다.
\_ A `_\ character. 이것은 like문안에서 한 문자를 의미한다.
[b]5.3 날짜와 시간 데이타 타입[/b]
- DATE
날짜(3 byte), \1000-01-01\에서 \9999-12-31\.
- DATETIME
날짜와 시간(8 byte), \1000-01-01 00:00:00\에서 \9999-12-31 23:59:59\.
- TIMESTAMP[(M)]
timestamp(4 byte). 유닉스 타임으로 1970년부터 1초단위로 시간을 표시한다.
\1970-01-01 00:00:00\에서 2037.
YYYYMMDDHHMMSS => TIMESTAMP(14)
YYMMDDHHMMSS => TIMESTAMP(12)
YYYYMMDD => TIMESTAMP(8)
YYMMDD => TIMESTAMP(6)
- TIME
시간(3 byte), \-838:59:59\에서 \838:59:59\.
- YEAR
년도(1 byte), 1901에서 2155, 그리고 0000.
5.5 기타
---------
- ENUM(\value1\,\value2\,...)
목록, value는 문자형 타입이다. 그리고 여러개의 값중 오로지 한가지의 값만이
저장 된다. NULL도 가질수 있다. 최고 65535
- SET(\value1\,\value2\,...)
집합. 0부터 64개까지의 멤버를 가질수 있다.
6. MySQL에서 SQL( Standed Query Language)
이장에선 MySQL에서 지원하는 SQL과 확장된 SQL에 대해 알아보기로 한다.
6.1 CREATE DATABASE syntax
* CREATE DATABASE db_name
db_name라는 이름을 가지는 데이타베이스를 생성한다.
실제로 MySQL Data 디렉토리내에 이름이 db_name인 디렉토리를 생성한다.
rpm인 경우 /var/lib/mysql/db_name
예)
mysql> CREATE DATABASE school;
또는
shell> mysqladmin CREATE school
실제로 확인을 해보자
shell> ls -l /var/lib/mysql/school
6.2 DROP DATABASE syntax
* DROP DATABASE [IF EXISTS] db_name
db_name라는 이름을 가지는 데이타베이스를 삭제한다.
마찬가지로 /var/lib/mysql/db_name을 삭제한다.
6.3 CREATE TABLE syntax
* CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
- NULL
널과 0는 의미가 다른다. NULL는 값이 아예 저장되지 않은 상태, 즉 입력을 받지
않은 상태이며 0라는 값을 받은 상태이며 empty string도 마찬가지이다.
- DEFAULT
하나의 레코드를 생성할때 값이 지정되지 않았을 경우 이 디폴트 값을 저장한다.
- AUTO_INCREMENT
이것은 테이블마다 오직 한개만, 항상 NOT NULL, 수치 데이타 형만 가능, insert
시 마지막 값에 하나를 더해 저장. 마지막 값이 삭제 되었을 경우 그 값을 다시
사용가능. PostgreSQL에서는 Sequence로 처리.
- PRIMARY KEY
자동으로 unique key을 생성하고 항상 NOT NULL이어야 한다. 한 테이블에 한개
- KEY
index와 같다.
- INDEX
테이타베이스의 성능을 높이기 위해 사용, 인덱스는 따로 관리 되어지며 시스템은
만들어진 인덱스를 이용하고 유지한다. 데이타베이스의 공간이 더 많이 필요하게
되고 INSERT나 UPDATE시 느려지는 단점이 있어 가능한 최소의 인덱스를 생성하여야
한다.
- type : 5장의 컬럼 타입 참고
- index_col_name : col_name
- select_statement :
[IGNORE | REPLACE] SELECT ...
예)
CREATE TABLE student
( no SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
age TINYINT,
birth DATE,
d_no SMALLINT
)
CREATE TABLE depart
( d_no TINYINT NOT NULL,
dept_name VARCHAR(20),
PRIMARY KEY (d_no) );
6.4 ALTER TABLE syntax
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ALTER [COLUMN] col_name
{SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX key_name
or RENAME [AS] new_tbl_name
or table_option
- CHANGE는 컬럼 이름까지 바꿀때
- MODIFY는 컬럼 이름외에 바꿀때
- 변경시 여러가지 조건에 맞는 지 항상 고려해봐야한다.
예)
mysql> ALTER TABLE student RENAME std;
mysql> ALTER TABLE std MODIFY dept TINYINT;
mysql> ALTER TABLE std ADD sex ENUM( \x\,\y\) DEFAULT \x\;
6.5 DROP TABLE syntax
---------------------
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
테이블 tbl_name을 삭제한다.
예)
mysql> DROP TABLE std;
6.6 INSERT syntax
* INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=expression, col_name=expression, ...
- LOW_PRIORITY 옵션은 입력하고자하는 테이블이 다른 클라이언트가 접근해있으며
기다렸다가 접근이 없을시에 INSERT을 수행한다.
- DELAYED 옵션은 LOCK을 거는 효과를 낸다.
- IGNORE 옵션은 입력시 PRIMARY, UNIQUE key 가 중복 될때 무시하고 계속 입력을
하게 한다.. 이 옵션이 없는 경우 입력은 중단된다.
예)
mysql> INSERT INTO std VALUES ( \, \길동\, \홍\, \20\, \1980-1-1\, \1\, \);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | dept | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
+----+------------+-----------+------+------------+------+------+
1 row in set (0.00 sec)
[/pre]
- 앞에서 테이블을 만들때 sex의 default는 \x\이지만 디폴트 값은 입력하는 값이 없을 경우에 한해서 적용된다. 즉 \은 empty string을 의미한다.
- 위의 경우와 달리 no 컬럼은 auto_increment로 선언이 되어있기 때문에 \을 사용 하면 자동적으로 맨 마지막의 값에 1을 증가한 값을 더해 저장이 되어진다.
( 뒤장의 select에서 NULL의 의미를 다시 알아본다. )
mysql> INSERT INTO std ( first_name, last_name, birth, d_no) VALUES
-> ( \기철\, \홍\, \1976-10-23\,\2\);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | NULL | 1976-10-23 | 2 | x |
+----+------------+-----------+------+------------+------+------+
2 rows in set (0.01 sec)
mysql> INSERT INTO std(\,\기자\,\홍\,\22\,\1978-2-1\,\2\,NULL),
(\,\기순\,\홍\,21,\1979-9-24\,\1\, \y\);
Query OK, 2 rows affected (0.19 sec)
레코드: 2개 중복: 0개 경고: 2개
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | 24 | 1976-10-23 | 2 | x |
| 3 | 기자 | 홍 | 22 | 1978-02-01 | 2 | NULL |
| 4 | 기순 | 홍 | 21 | 1979-09-24 | 1 | y |
+----+------------+-----------+------+------------+------+------+
4 rows in set (0.01 sec)
mysql> INSERT INTO depart VALUES (1,\컴퓨터공학과\),(2,\전자계산학과\);
Query OK, 2 rows affected (0.38 sec)
레코드: 2개 중복: 0개 경고: 0개
mysql> select * from depart;
+----+--------------+
| d_no | dept_name |
+----+--------------+
| 1 | 컴퓨터공학과 |
| 2 | 전자계산학과 |
+----+--------------+
2 rows in set (0.03 sec)
[주의]
아래와 같은 경우중 두번째는 NULL이 입력되므로 주의하기 바란다.
mysql> INSERT INTO std ( age, dept ) VALUES ( 10, age*2);
Query OK, 1 row affected (0.22 sec)
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | 24 | 1976-10-23 | 2 | x |
| 3 | NULL | NULL | 10 | NULL | 20 | x |
+----+------------+-----------+------+------------+------+------+
3 rows in set (0.01 sec)
mysql> INSERT INTO std ( age, d_no ) VALUES ( d_no*2, 10);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | 24 | 1976-10-23 | 2 | x |
| 3 | NULL | NULL | 10 | NULL | 20 | x |
| 4 | NULL | NULL | NULL | NULL | 10 | x |
+----+------------+-----------+------+------------+------+------+
4 rows in set (0.01 sec)
6.7 DELETE syntax
-----------------
* DELETE [LOW_PRIORITY] FROM tbl_name [WHERE where_definition] [LIMIT rows]
tbl_name에서 조건에 맞는 레코드를 삭제한다.
mysql> DELETE FROM std WHERE no >=3;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | 24 | 1976-10-23 | 2 | x |
+----+------------+-----------+------+------------+------+------+
2 rows in set (0.00 sec)
[ 주의 ]
- 만일 where절에 조건을 적지 않으면 모든 레코드가 삭제 되어버린다.
6.8 SELECT syntax
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO OUTFILE \file_name\ export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
- AS는 alias이고 이 alias는 order by나 having, group by에서 사용할 수 있다.
mysql> SELECT concat(last_name,,first_name) AS full_name
FROM std ORDER BY full_name;
+-----------+
| full_name |
+-----------+
| 홍기철 |
| 홍길동 |
+-----------+
위와 같이 alias인 full_name은 하나의 필드와 같이 취급되어 진다.
- table-references는 JOIN syntax를 통해 만들어질수도 있다. (JOIN 참고)
- order by는 col_name에 맞추어 정렬한다. (Descending, Ascending)
- DISTINCT 또는 DISTINCTROW는 중복된 컬럼은 제거하고 보여준다.
- LIMIT는 select된 rows을 특정 부분을 넘겨준다. ( 인자는 숫자만 )
인자가 하나일 경우 : 아래쪽부터 인자만큼
인자가 두개일 경우 : 인자를 초과해서 두번째 인자만큼
mysql> select * from std LIMIT 1;
mysql> select * from table LIMIT 1,1;
- SELECT ... INTO OUTFILE \file_name\은 출력을 파일로 하는 방법으로 뒤에서 배울 LOAD문하고
유사하다. 뒤에 LOAD문에서 다시 상세히 설명하도록한다.
[ 참고 ] select와 where에서 쓸수 있는 비교 연산자와 간단한 함수
mysql> select 2 > 2;
+-------+
| 2 > 2 |
+-------+
| 0 |
+-------+
mysql> select 2 >=2 ;
+-------+
| 2 >=2 |
+-------+
| 1 |
+-------+
mysql> select 2 <2 ;
+------+
| 2 <2 |
+------+
| 0 |
+------+
mysql> select 2 <= 2 ;
+--------+
| 2 <= 2 |
+--------+
| 1 |
+--------+
mysql> select 2 <> 2 ;
+--------+
| 2 <> 2 |
+--------+
| 0 |
+--------+
mysql> select 2 != 2 ;
+--------+
| 2 != 2 |
+--------+
| 0 |
+--------+
mysql> select 2 = \2\ ;
+---------+
| 2 = \2\ |
+---------+
| 1 |
+---------+
* 위와 같이 String인 경우 숫자로 전환하여 비교한다.
mysql> select \홍길동\ like \_길동\;
+-----------------------+
| \홍길동\ like \_길동\ |
+-----------------------+
| 0 |
+-----------------------+
mysql> select \홍길동\ like \__길동\;
+------------------------+
| \홍길동\ like \__길동\ |
+------------------------+
| 1 |
+------------------------+
mysql> select \홍길동\ like \%길동\;
+-----------------------+
| \홍길동\ like \%길동\ |
+-----------------------+
| 1 |
+-----------------------+
mysql> select \홍길동\ like \%동\;
+---------------------+
| \홍길동\ like \%동\ |
+---------------------+
| 1 |
+---------------------+
* 그외의 다양한 함수는 메뉴얼을 참고한다.
- AS는 alias이고 이 alias는 order by나 having, group by에서 사용할 수 있다.
mysql> select concat(last_name,first_name) AS full_name
from std ORDER BY full_name;
+-----------+
| full_name |
+-----------+
| 홍기철 |
| 홍길동 |
+-----------+
위와 같이 alias인 full_name은 하나의 필드와 같이 취급되어 진다.
- table-references는 JOIN syntax를 통해 만들어질수도 있다. (JOIN 참고)
- order by는 col_name에 맞추어 정렬한다. (Descending, Ascending , Descending)
- group by는 해당되는 컬럼의 값으로 그룹을 묶어준다.
- having 은 group by로 나온 결과에 대한 조건식을 명시한다.
( having과 group by는 뒤에 다시 언급하기로 한다. )
- DISTINCT 또는 DISTINCTROW는 중복된 컬럼은 제거하고 보여준다.
- LIMIT는 select된 rows을 특정 부분을 넘겨준다. ( 인자는 숫자만 )
인자가 하나일 경우 : 아래쪽부터 인자만큼
인자가 두개일 경우 : 인자를 초과해서 두번째 인자만큼
mysql> select * from table LIMIT 5,10; # Retrieve rows 6-15
mysql> select * from table LIMIT 5; # Retrieve first 5 rows
- SELECT ... INTO OUTFILE \file_name\은 출력을 파일로 하는 방법으로 뒤에서 배울 LOAD문하고
유사하다. 뒤에 LOAD문에서 다시 상세히 설명하도록한다.
[참고] NULL의 의미
- NULL이란 데이타의 입력이 없다는 뜻이다. 이것은 한번 입력 0이나 \의 의미와는
다른 뜻이다. 궁극적으로 입력을 받지 않았다는 뜻이며 이것의 구분은 데이타 베이스
에서는 매우 중요하다. 실제 예로 알아보기로 하자.
mysql> SELECT * FROM std WHERE sex IS NULL;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 3 | 기자 | 홍 | 22 | 1978-02-01 | 2 | NULL |
+----+------------+-----------+------+------------+------+------+
1 row in set (0.04 sec)
mysql> SELECT * FROM std WHERE sex = \;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | d_no | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
+----+------------+-----------+------+------------+------+------+
1 row in set (0.01 sec)
[중요]
* IS NULL은 NULL을 체크하는 연산자이다. 이것 대신 NULL을 체크하기 위해 \=\
연산자를 사용하지 않도록 주의하자.
6.9 LOAD DATA INFILE syntax
LOAD DATA [LOCAL] INFILE \file_name.txt\ [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY \t\]
[OPTIONALLY] ENCLOSED BY \]
[ESCAPED BY \\ ]]
[LINES TERMINATED BY \n\]
[IGNORE number LINES]
[(col_name,...)]
- SQL문이 아닌 특정 파일로부터 core파일을 읽어 들인다.
- 이것은 SQL문으로 쓰여진 외부 파일로부터 입력을 받은 것보다 매우 빠르게 입력이
된다. 만일 대용량의 데이타를 입력을 하는 경우 꼭 이것을 사용해야한다.
- mysqlimport 와 같다. ( 8장에서 다시 설명)
- 이것은 4.3절에서 말한것과 같이 file 권한이 꼭 있어야한다.
- file의 work 디렉토리는 해당 database가 존재하는 물리적 디렉토리이다. 그러므로
현재의 자신의 계정에 있는 파일을 입력할때는 절대경로를 사용하는 것이 좋다.
예)
shell> vi std.dat
\N 철수 이 33 1968-3-21 2 x
\N 순이 김 33 1968-12-24 1 y
\N 영수 박 34 1967-5-14 2 x
~
- 한 컬럼후 TAB으로 나누어 준다.
- 문자열이라고 \로 묶지 않도록 한다.
- NULL은 \N
mysql> LOAD DATA INFILE \/root/std.dat\ INTO TABLE std;
Query OK, 3 rows affected (0.01 sec)
레코드: 3개 삭제: 0개 스킵: 0개 경고: 3개
mysql> SELECT * FROM std;
+----+------------+-----------+------+------------+------+------+
| no | first_name | last_name | age | birth | dept | sex |
+----+------------+-----------+------+------------+------+------+
| 1 | 길동 | 홍 | 20 | 1980-01-01 | 1 | |
| 2 | 기철 | 홍 | 24 | 1976-10-23 | 2 | x |
| 3 | 기자 | 홍 | 22 | 1978-02-01 | 2 | NULL |
| 4 | 기순 | 홍 | 21 | 1979-09-24 | 1 | y |
| 7 | 영수 | 박 | 34 | 1967-05-14 | 2 | x |
| 6 | 순이 | 김 | 33 | 1968-12-24 | 1 | y |
| 5 | 철수 | 이 | 33 | 1968-03-21 | 2 | x |
+----+------------+-----------+------+------------+------+------+
7 rows in set (0.00 sec)
[참고]
SELECT select_expression,... FROM table_name INTO OUTFILE \file_name.txt\
[FIELDS
[TERMINATED BY \t\]
[OPTIONALLY] ENCLOSED BY \]
[ESCAPED BY \\ ]]
[LINES TERMINATED BY \n\]
- 실제적인 work 디렉토리는 위에서 말한바와 같으므로 해당 디렉토리에서 OUTFILE을
찾으면 된다.
mysql> SELECT * FROM std INTO OUTFILE \std_out.txt\ ;
- 파일로 출력된 것을 확인해보자.
shell> vi /var/lib/mysql/school/std_out.txt
1 길동 홍 20 1980-01-01 1
2 기철 홍 24 1976-10-23 2 x
3 기자 홍 22 1978-02-01 2 \N
4 기순 홍 21 1979-09-24 1 y
7 영수 박 34 1967-05-14 2 x
6 순이 김 33 1968-12-24 1 y
5 철수 이 33 1968-03-21 2 x
~
6.10 JOIN syntax
table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
- 조인은 두개의 테이블에서 특정한 조건을 만족하는 각각의 컬럼들을 합쳐서 만든다.
- select문에서 사용할수 있다.
- OUT JOIN은 조건을 만족하지 못하더라도 그 값을 필요로 할때 사용할수 있다.
예)
mysql> INSERT INTO std SET First_name= \말자\, last_name = \이\;
mysql> SELECT concat( std.last_name, std.first_name ) AS Name,
-> depart.dept_name AS department FROM std, depart WHERE std.d_no=depart.d_no;
+--------+--------------+
| Name | department |
+--------+--------------+
| 홍길동 | 컴퓨터공학과 |
| 홍기순 | 컴퓨터공학과 |
| 김순이 | 컴퓨터공학과 |
| 홍기철 | 전자계산학과 |
| 홍기자 | 전자계산학과 |
| 박영수 | 전자계산학과 |
| 이철수 | 전자계산학과 |
+--------+--------------+
7 rows in set (0.00 sec)
mysql>SELECT concat( std.last_name, std.first_name ) AS Name,
->depart.dept_name AS department FROM std join depart
->WHERE std.d_no=depart.d_no;
+--------+--------------+
| Name | department |
+--------+--------------+
| 홍길동 | 컴퓨터공학과 |
| 홍기순 | 컴퓨터공학과 |
| 김순이 | 컴퓨터공학과 |
| 홍기철 | 전자계산학과 |
| 홍기자 | 전자계산학과 |
| 박영수 | 전자계산학과 |
| 이철수 | 전자계산학과 |
+--------+--------------+
7 rows in set (0.00 sec)
- OUTER JOIN의 예
mysql> SELECT concat( std.last_name, std.first_name ) AS Name,
-> depart.dept_name AS department
-> FROM std LEFT OUTER JOIN depart USING (d_no);
+--------+--------------+
| Name | department |
+--------+--------------+
| 홍길동 | 컴퓨터공학과 |
| 홍기철 | 전자계산학과 |
| 홍기자 | 전자계산학과 |
| 홍기순 | 컴퓨터공학과 |
| 이말자 | NULL |
| 박영수 | 전자계산학과 |
| 김순이 | 컴퓨터공학과 |
| 이철수 | 전자계산학과 |
+--------+--------------+
8 rows in set (0.01 sec)
mysql> select concat( std.last_name, std.first_name ) AS Name,
-> depart.dept_name AS department
-> from depart left outer join std using (d_no);
+--------+--------------+
| Name | department |
+--------+--------------+
| 홍길동 | 컴퓨터공학과 |
| 홍기순 | 컴퓨터공학과 |
| 김순이 | 컴퓨터공학과 |
| 홍기철 | 전자계산학과 |
| 홍기자 | 전자계산학과 |
| 박영수 | 전자계산학과 |
| 이철수 | 전자계산학과 |
+--------+--------------+
7 rows in set (0.00 sec)
- LEFT의 의미를 파악할수 있을 것이다.
[/pre]
6.11 UPDATE syntax
UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...
[WHERE where_definition]
- 해당하는 레코드를 변경한다.
- 한번 입력된 레코드는 오로지 UPDATE로만 변경이 가능하다.
mysql> UPDATE std SET sex=x where no=1;
[주의]
- 능숙하게 SQL을 사용하는 사람이라도 가끔씩은 where절을 빠뜨리는 경우가 있다.
이때는 모든 레코드들이 영향을 받으므로 UPDATE, DELETE 명령은 주의해서 사용하자.
6.12 SHOW syntax (Get information about tables, columns,...)
------------------------------------------------------------
SHOW DATABASES
or SHOW TABLES [FROM db_name]
or SHOW COLUMNS FROM tbl_name [FROM db_name]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW PROCESSLIST
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
- 데이타베이스의 정보를 보여준다.
F
mysql> SHOW DATABASES;
+-----------+
| Database |
+-----------+
| school |
| test |
+-----------+
2 rows in set (0.00 sec)
mysql> SHOW TABLES;
+------------------+
| Tables in school |
+------------------+
| depart |
| std |
+------------------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM std;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| no | smallint(6) | | PRI | 0 | auto_increment |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| birth | date | YES | | NULL | |
| d_no | tinyint(4) | YES | | NULL | |
| sex | enum(\x\,\y\) | YES | | x | |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
mysql> SHOW INDEX FROM std;
+-----+----------+--------+------------+-----------+---------+-----------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part |
+-----+----------+--------+------------+-----------+---------+-----------+---------+
| std | 0 |PRIMARY | 1 | no | A | 8 | NULL |
+-----+----------+--------+------------+-----------+---------+-----------+---------+
1 row in set (0.01 sec)
6.13 USE db_name
-----------------
사용할 데이타 베이스를 db_name으로 바꾼다.
mysql> USE test;
mysql> show tables;
Empty set (0.00 sec)
mysql> USE school;
mysql> show tables;
+------------------+
| Tables in school |
+------------------+
| depart |
| std |
+------------------+
2 rows in set (0.01 sec)
6.14 DESCRIBE syntax (Get information about columns)
{DESCRIBE | DESC} tbl_name {col_name | wild}
SHOW COLUMNS FROM과 유사하다.
6.15 LOCK TABLES/UNLOCK TABLES syntax
LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
[, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
- table을 LOCK, UNLOCK한다.
- mysql은 거의 모든 작업들을 테이블 단위로 이루어진다. ( 아직 컬럼단위는 아니다.)
- 테이블을 READ LOCK을 걸면 자신을 포함한 다른 쓰레드들은 오직 READ만 할수 있다.
- WRITE LOCK을 걸면 다른 쓰레드에게 write와 read도 불가능한다. ( 자신은 가능)
- 다른 쓰레드들은 자신이 참조할려는 테이블이 LOCK이 걸려 있으면 풀리기을 기다린다.
- 정확한 처리를 위해서는 필요한 부분이므로 충분히 이해와 개념을 잡기
바란다.
예제)
가령 예를 들어 은행의 입출금의 경우를 생각해보자.
출금의 경우는 먼저 잔액이 있는 지를 확인하는 작업이 이루어진후, 잔액에 여유가 있는 경우 거래가 이루어진다. 하지만 A라는 구좌에 100만원이 있는 경우 B라는 사람이 먼저 100만원을 출금한다고 하자. 우선 구좌에 현금이 있는 지 확인을 할것이다. 현재는 잔고가 100만원이 있기 때문에 거래가 성사되어진다. 하지만 확인을 하는 시점에서 또라는 C라는 사람이 50만월을 출금할려고 한다면 이 사람도 현재 잔액이 100만원이 있다는 사실을 알고 출금을 할려고 할것이다. 정상적인 거래라고 하면 C라는 사람의 거래는 성사되지 않아야 할것이다. 하지만 위와 같은 경우 150만원이 출금되고도 잔액이 50만원이 남아 있는 불상사가 있어날 확률이 있다.
이런 경우에는 먼저 확인한 프로세스가 write LOCK을 사용함으로써 안정하게 거래가 이루어질 수 있다.
에)
mysql> LOCK TABLES std READ;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT first_name ,age FROM std WHERE d_no=1;
+------------+------+
| first_name | age |
+------------+------+
| 길동 | 20 |
| 기순 | 21 |
| 순이 | 33 |
+------------+------+
mysql> INSERT INTO std SET d_no=\1\;
ERROR 1099: 테이블 \std\는 READ 락이 잠겨있어서 갱신할 수 없습니다.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO std SET d_no=\1\;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT first_name ,age FROM std WHERE d_no=1;
+------------+------+
| first_name | age |
+------------+------+
| 길동 | 20 |
| 기순 | 21 |
| 순이 | 33 |
| NULL | NULL |
+------------+------+
- 다른 쓰레드에서선 SELECT는 되지만 INSERT라던지 UPDATE는 LOCK을 풀리기만을 기다린다.
mysql> LOCK TABLES std WRITE;
mysql> SELECT first_name ,age FROM std WHERE d_no=1;
+------------+------+
| first_name | age |
+------------+------+
| 길동 | 20 |
| 기순 | 21 |
| 순이 | 33 |
| NULL | NULL |
+------------+------+
mysql> SELECT first_name ,age FROM std WHERE d_no=1;
+------------+------+
| first_name | age |
+------------+------+
| 길동 | 20 |
| 기순 | 21 |
| 순이 | 33 |
| NULL | NULL |
| NULL | NULL |
+------------+------+
mysql> UNLOCK TABLES;
- LOCK을 해제하기전에 다른 쓰레드에서 쿼리를 주면 LOCK이 풀리기만을 기다리는것을 볼 수 있다.
6.16 GRANT and REVOKE syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY \password\]
[, user_name [IDENTIFIED BY \password\] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
- GRANT : 권한 부여
- REVOKE : 권한 삭제
- priv_type : SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, INDEX, ALTER
- priv_type : SELECT, INSERT, UPDATE. ( 컬럼인 경우 )
- user_name : user@host ( ex: hoonix@%.linxnet.co.kr )
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON intra.*
-> TO hoonix@localhost
-> IDENTIFIED BY \pass1234\
6.17 CREATE INDEX syntax
CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length]),... )
- 인텍스를 생성한다. 멀티 인텍스도 생성할수 있다.
- CHAR, VARCHAR, TEXT, BLOB등은 자료의 일부분으로 인텍스를 생성할수 있다.
mysql>delete from std where no > 8;
mysql> CREATE INDEX name ON std (first_name);
Query OK, 8 rows affected (0.03 sec)
레코드: 8개 중복: 0개 경고: 0개
mysql> SHOW INDEX FROM std;
+-----+----------+--------+------------+-----------+---------+-----------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality| Sub_part|
+-----+----------+--------+------------+-----------+---------+-----------+---------+
| std | 0 |PRIMARY | 1 | no | A | 8 | NULL |
| std | 1 | name | 1 |first_name | A | NULL | NULL |
+-----+----------+--------+------------+-----------+---------+-----------+---------+
2 rows in set (0.00 sec)
[주의] index을 삭제할때는 DROP INDEX가 아닌 ALTER TABLE을 사용해야한다.
mysql> ALTER TABLE std DROP INDEX name;
Query OK, 8 rows affected (0.02 sec)
레코드: 8개 중복: 0개 경고: 0개
mysql> show index from std;
+-----+----------+--------+------------+-----------+---------+-----------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality| Sub_part|
+-----+----------+--------+------------+-----------+---------+-----------+---------+
|std | 0 |PRIMARY | 1 | no | A | 8 | NULL |
+-----+----------+--------+------------+-----------+---------+-----------+---------+
1 row in set (0.00 sec)
6.18 select에서 group by와 having의 예
--------------------------------------
- 성(last_name)별 인원 출력
mysql> SELECT last_name , count(*) AS no FROM std
-> GROUP BY last_name ORDER BY no;
+-----------+----+
| last_name | no |
+-----------+----+
| 김 | 1 |
| 박 | 1 |
| 이 | 2 |
| 홍 | 4 |
+-----------+----+
4 rows in set (0.01 sec)
- 위의 결과중 윈원이 2이상인것만 출력
mysql> SELECT last_name , count(*) AS no FROM std
-> GROUP BY last_name HAVING no >= 2
+-----------+----+
| last_name | no |
+-----------+----+
| 이 | 2 |
| 홍 | 4 |
+-----------+----+
2 rows in set (0.00 sec)
- 과의 이름과 인원 출력
mysql> SELECT depart.dept_name, count(*) FROM std, depart
-> WHERE std.d_no = depart.d_no GROUP BY std.d_no ;
+--------------+----------+
| dept_name | count(*) |
+--------------+----------+
| 컴퓨터공학과 | 3 |
| 전자계산학과 | 4 |
+--------------+----------+
2 rows in set (0.00 sec)
6.19 MySQL에서 바이너리 파일의 저장 ( BLOB)
기영
|
