Mysql sql

Overview

Mysql 사용법 정리.

Basic

Data types

datetime

Mysql-5.6 버전 이후부터 사용가능.

Naming

Mysql 에서 Table, View 등의 이름을 숫자로 만들 수는 없다. 하지만 숫자-스트링으로는 가능하다. 이 경우 숫자-스트링을 ``로 감싸주면 된다.

mysql> create view 123 as select * from t1 where time="now";
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near '123 as select * from t1 where time="now"' at line 1

mysql> create view "123" as select * from t1 where time="now";
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near '"123" as select * from t1 where time="now"' at line 1

mysql> create view '123' as select * from t1 where time="now";
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near ''123' as select * from t1 where time="now"' at line 1

mysql> create view `123` as select * from t1 where time="now";
Query OK, 0 rows affected (0.10 sec)

foreign key

Mysql-3.23.43 버전 이후부터는 외래키 지정이 가능하다.

Options

• on delete cascade

부모의 해당키가 삭제되면 자동으로 삭제된다.

• on delete set null

부모의 해당키가 삭제되면 자동으로 null 로 만든다.

• on update cascade

부모의 해당키가 삭제되면 자동으로 갱신된다.

Example

agent id 를 외래키로 지정을 한 agent_group 테이블이다. agent_group 에 속한 agent 삭제시 같이 삭제되고, 업데이트 시, 같이 업데이트된다.

<source lang=sql> create table agent (

   id          int not null unique,
   name        varchar(255),

   primary key(id)

);

create table agent_group(

   group_id    int not null unique,
   agent_id    int not null,

   foreign key(agent_id)       references agent(id) on delete cascade on update cascade,

   primary key(group_id, agent_id)

); </source>

Alter

이미 생성되어 있는 테이블의 내용을 변경한다.

Drop the column from the table. <source lang=sql> ALTER TABLE table_name DROP column_name; </source>

Add the column to the table <source lang=sql> ALTER TABLE Customers ADD Email varchar(255); </source>

<source lang=sql> ALTER TABLE table_name MODIFY COLUMN column_name datatype; </source>

Update

이미 존재하는 레코드의 값을 변경한다.

UPDATE <table> SET <field> = "<value>" WHERE <field> > "<condition>";
조건 값보다 큰 필드의 레코드 값을 수정 

UPDATE <table> SET <field> = "<value>" WHERE <field> <> "<condition>";
조건 값을 제외한 모든 레코드 값을 수정 

UPDATE <table> SET <field1> = "<value>", <field2> = "<value>" WHERE <conditions>;
조건에 맞는 두개의 필드를 바꿈

UPDATE <table> SET <field> = REPLACE(<field>, 'alpha', 'beta')
필드의 값에 'alpha'라는 단어가 포함 되어 있다면 모두 'beta'로 수정

UPDATE <table> SET <field> = CONCAT(<field>,'alpha') WHERE <conditions>
조건에 맞는 필드명의 값에 'alpha' 단어를 덧붙임

Insert

INSERT INTO <table> (columns) VALUES (values);

Delete

DELETE FROM `table_name` [WHERE condition];

View

View 는 임시 테이블로도 번역이 된다. 어떤 테이블에서 특정 자료들만을 관리하는 임의의 테이블을 만들고자할 때, 사용할 수 있다.

Create

View 를 생성한다. <source lang=sql> create view <view_name> as <select_query>; </source>

Show all views

현재 생성되어 있는 모든 view 들을 확인하고자 할 때는 다음의 쿼리를 사용하면 된다. <source lang=sql> SHOW FULL TABLES IN <database_name> WHERE TABLE_TYPE LIKE 'VIEW'; </source>

Error

Warnings

Mysql Query 를 실행하다보면 한번씩 Warning 메시지를 확인할 수 있다. 뭔가 경고할만한 내용이 있다는 것인데, 확인하기 위해서는 다음의 명령어를 입력하면 된다<ref>http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html</ref>. <source lang=sql> SHOW WARNINGS; </source>

mysql> select * from `32e9e86e_f57f_4156_8d1a_26f169b845c4`;
Empty set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '32e9e86e-f57f-4156-8d1a-26f169b845c4' for column 'time' at row 1 |
| Warning | 1292 | Incorrect datetime value: '32e9e86e-f57f-4156-8d1a-26f169b845c4' for column 'time' at row 1 |
+---------+------+---------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

ETC

Checksum

CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]

CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to verify that the contents are the same before and after a backup, rollback, or other operation that is intended to put the data back to a known state.

Check table exist

특정 테이블이 있는지, 아닌지를 알고 싶을 때가 있다. 이런 경우, 다음의 쿼리를 입력하면, 테이블이 있으면 정상, 없으면 오류를 리턴한다.<ref>http://stackoverflow.com/questions/8829102/mysql-check-if-table-exists-without-using-select-from</ref> <source lang=sql> SELECT 1 FROM testtable LIMIT 1; </source>

Execute sql script

SQL 스크립트를 실행하고자 할 때는 다음과 같이 하면 된다.<ref>https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html</ref>

-- shell
shell> mysql db_name
shell> mysql db_name < text_file
shell> mysql < text_file

-- mysql
mysql> source file_name
mysql> \. file_name

Create Primary Key with UUID()

uuid 를 primary key로 지정할 때, UUID() 함수를 사용할 수 있다. trigger 를 만들어 놓으면, 매번 테이블에 데이터를 입력할 때마다 자동으로 UUID() 함수가 실행되어 입력되도록 할 수도 있다.<ref>http://forums.mysql.com/read.php?10,175939,176708#msg-176708</ref> <source lang=sql> DROP TABLE IF EXISTS t; CREATE TABLE t(id CHAR(128));

DELIMITER // CREATE TRIGGER init_uuid BEFORE INSERT ON t

 FOR EACH ROW SET NEW.id = UUID();

// DELIMITER ;

INSERT INTO t VALUES( NULL ); SELECT * FROM t; +--------------------------------------+ | id | +--------------------------------------+ | 42114800-6f22-1000-9611-2eb9174826e3 | +--------------------------------------+ </source>

MAX_EXECUTION_TIME

Mysql 5.7 버전부터 사용할 수 있다. select 및 다른 SQL query 실행에 timeout 을 설정하는 옵션이다.

<source lang=sql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM calls where destination = '11234';

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ... </source>

• https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-time
• https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

See also

• http://sjs0270.tistory.com/54 - 뷰(View)

References

<references />