db:mysql
차이
문서의 선택한 두 판 사이의 차이를 보여줍니다.
양쪽 이전 판이전 판다음 판 | 이전 판 | ||
db:mysql [2013/06/12 15:17] – [기타] kieuns | db:mysql [2024/04/23 22:43] (현재) – 바깥 편집 127.0.0.1 | ||
---|---|---|---|
줄 1: | 줄 1: | ||
+ | ====== SQL 기본 명령어 ====== | ||
+ | |||
+ | 난 기본도 없어서.. | ||
+ | |||
+ | ===== DB 생성 ===== | ||
+ | |||
+ | * full opt 사용 | ||
+ | |||
+ | <code sql> | ||
+ | create database tbook1_development | ||
+ | default character set = utf8 default collate = utf8_bin; | ||
+ | </ | ||
+ | |||
+ | TABLE 생성 | ||
+ | |||
+ | <code sql> | ||
+ | -- Create the table foo.example_02 if it doesn' | ||
+ | CREATE TABLE | ||
+ | IF NOT EXISTS `foo`.`example_02` | ||
+ | ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | </ | ||
+ | |||
+ | 예2 - 인덱스와 함께 사용 | ||
+ | |||
+ | * 메모용 테이블 (그냥 생각해본 것) | ||
+ | * 디비 타입 명시 | ||
+ | * 캐릭터셋 명시 | ||
+ | * 주석 추가 | ||
+ | * 프라이머리 키, 키 키워드 사용 | ||
+ | |||
+ | CREATE TABLE에서 KEY 키워드를 사용하는 것은 인덱스를 생성하라는 이야긴데, | ||
+ | 호환성을 위해서 KEY 명령어도 있다. | ||
+ | |||
+ | <code sql> | ||
+ | CREATE TABLE | ||
+ | IF NOT EXISTS example_1 | ||
+ | ( | ||
+ | `seq` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ' | ||
+ | `orders` bigint(19) NOT NULL COMMENT ' | ||
+ | `content` varchar(1000) DEFAULT NULL COMMENT ' | ||
+ | `writer` varchar(20) DEFAULT NULL COMMENT ' | ||
+ | `regdate` datetime DEFAULT NULL COMMENT ' | ||
+ | PRIMARY KEY (`seq`), | ||
+ | KEY `idx` (`orders`, | ||
+ | ) ENGINE=InnoDB DEFAULT=utf8 COMMNET=' | ||
+ | </ | ||
+ | |||
+ | 기본 값으로 DATETIME에 현재 시간을 입력하게 하는 것은 쉽지 않아서... INSERT 할때 NOW() 함수로 값을 넣는다. | ||
+ | |||
+ | <code sql> | ||
+ | INSERT INTO example_1 | ||
+ | ( orders, content, startdate, writer, regdate ) VALUES | ||
+ | ( 1, 1, ' | ||
+ | // NOW() 함수로 현재 시간을 입력. | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== 날짜 - 현재 시간 얻기 ===== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT NOW(); | ||
+ | select CAST(NOW() AS DATETIME); | ||
+ | </ | ||
+ | |||
+ | ===== 날짜 - 현재 시간 얻기 : UnixTime ===== | ||
+ | |||
+ | ref : [[http:// | ||
+ | |||
+ | <code sql> | ||
+ | -- STR to unixtime | ||
+ | select UNIX_TIMESTAMP(); | ||
+ | select UNIX_TIMESTAMP(" | ||
+ | </ | ||
+ | |||
+ | |||
+ | ref : [[http:// | ||
+ | |||
+ | <code sql> | ||
+ | -- unixtime to STR | ||
+ | SELECT FROM_UNIXTIME(1385690400); | ||
+ | </ | ||
+ | ===== Create - added ===== | ||
+ | |||
+ | Alter에 대해서 추가로 새로운 점, | ||
+ | |||
+ | <code sql> | ||
+ | ALTER TABLE `etdn_hg_img_list` ADD `kind` TINYINT NOT NULL DEFAULT | ||
+ | </ | ||
+ | |||
+ | * **AFTER `< | ||
+ | * DEFAULT는 항상 잘 쓰인다. | ||
+ | |||
+ | ===== DISTINCT ===== | ||
+ | |||
+ | col_a 컬럼에서 중복은 제거하고 출력 | ||
+ | |||
+ | <code sql> | ||
+ | select DISTINCT col_a from some_table; | ||
+ | </ | ||
+ | ===== Import ===== | ||
+ | |||
+ | * [[http:// | ||
+ | * 다른 참고 링크 : [[http:// | ||
+ | |||
+ | |||
+ | * 도움말 < | ||
+ | * csv 파일 형식(콤마, | ||
+ | * 데이터 파일의 확장자는 무시된다. | ||
+ | * patient.txt, | ||
+ | |||
+ | < | ||
+ | 【사용법】 | ||
+ | | ||
+ | |||
+ | 【예제】 | ||
+ | # cat employees | ||
+ | | ||
+ | | ||
+ | | ||
+ | # mysqlimport -u root -p --fields-terminated-by="," | ||
+ | </ | ||
+ | |||
+ | 다른 예제 | ||
+ | |||
+ | <code mysql> | ||
+ | # cat > imptest.txt | ||
+ | 100 Max Sydow | ||
+ | 101 Count Dracula | ||
+ | ^c | ||
+ | |||
+ | # cat imptest.txt | ||
+ | 100 Max Sydow | ||
+ | 101 Count Dracula | ||
+ | |||
+ | # mysql -e " | ||
+ | |||
+ | # mysqlimport -u jijoe -p --local test imptest.txt | ||
+ | test.imptest: | ||
+ | |||
+ | # mysql -e " | ||
+ | +------+---------------+ | ||
+ | | id | n | ||
+ | +------+---------------+ | ||
+ | | 100 | Max Sydow | ||
+ | | 101 | Count Dracula | | ||
+ | +------+---------------+ | ||
+ | </ | ||
+ | |||
+ | ==== 또 다른 예제 ==== | ||
+ | |||
+ | 일반 사용자(jijoe)가 자신의 데이터베이스(jijoeDB)에 테이블(test)를 생성하고 mysqldump로 데이터만 덤프한 다음 mysqlimport를 이용하여 테이블에 데이터를 복구하는 과정이다. | ||
+ | |||
+ | |||
+ | jijoeDB 의 덤프를 받는다. | ||
+ | |||
+ | <code bash> | ||
+ | shell> mkdir dumpdata | ||
+ | shell> chmod a+w dumpdata | ||
+ | shell> mysqldump -u jijoe -p jijoeDB --no-create-info \ | ||
+ | --tab=/ | ||
+ | --fields-terminated-by=',' | ||
+ | --lines-terminated-by=' | ||
+ | Enter password: ****** | ||
+ | |||
+ | shell> cat dumpdata/ | ||
+ | | ||
+ | | ||
+ | </ | ||
+ | |||
+ | 덤프 데이터를 **mysqlimport** 명령어로 데이터를 복사한다. | ||
+ | |||
+ | <code mysql> | ||
+ | mysql> select * from test; | ||
+ | +--------+---------------------+ | ||
+ | | name | login | | ||
+ | +--------+---------------------+ | ||
+ | | 아무개 | 2008-10-22 17:50:50 | | ||
+ | | 착한이 | 2008-10-22 17:51:21 | | ||
+ | +--------+---------------------+ | ||
+ | 2 rows in set (0.00 sec) | ||
+ | |||
+ | mysql> truncate table test; | ||
+ | Query OK, 0 rows affected (0.00 sec) | ||
+ | |||
+ | mysql> exit | ||
+ | |||
+ | shell> cd dumpdata | ||
+ | |||
+ | shell> cat test.txt | ||
+ | | ||
+ | | ||
+ | |||
+ | shell> mysqlimport -u jijoe -p \ | ||
+ | --local \ | ||
+ | --fields-terminated-by=',' | ||
+ | --lines-terminated-by=' | ||
+ | Enter password: ******* | ||
+ | jijoeDB.test: | ||
+ | |||
+ | shell> mysql -u jijoe -p jijoeDB | ||
+ | Enter password: ******* | ||
+ | |||
+ | mysql> select * from test; | ||
+ | +--------+---------------------+ | ||
+ | | name | login | | ||
+ | +--------+---------------------+ | ||
+ | | 아무개 | 2008-10-22 17:50:50 | | ||
+ | | 착한이 | 2008-10-22 17:51:21 | | ||
+ | +--------+---------------------+ | ||
+ | 2 rows in set (0.00 sec) | ||
+ | </ | ||
+ | |||
+ | ====== 서브쿼리(SubQuery) ====== | ||
+ | |||
+ | 빨리 읽어볼 수 있는 튜토리얼을([[http:// | ||
+ | |||
+ | 서브쿼리를 넣을 수 있는 위치 | ||
+ | |||
+ | * FROM 구문 다음의 테이블 선택지에서, | ||
+ | * 쿼리 결과와 선택한 데이터에 별명을 붙여 사용할 수 있다. | ||
+ | * WHERE 구문 다음의 조건자 선택시, 조건 항목을 쿼리 결과로서 전송. | ||
+ | |||
+ | from 다음의 서브쿼리가 유용한 것 같다. | ||
+ | |||
+ | 간단한 예제 - 삽입할 위치 개념 찾기 | ||
+ | |||
+ | <code sql> | ||
+ | -- 쓸모 없지만, 아 이렇구나 하는 감을 보내주는 예제 | ||
+ | SELECT name, db FROM country | ||
+ | WHERE code=(SELECT " | ||
+ | -- 조금 더 나은 예제 | ||
+ | SELECT name, headofstate, | ||
+ | WHERE population=(SELECT MAX(population) FROM Country); | ||
+ | </ | ||
+ | |||
+ | 복잡한 예제 | ||
+ | |||
+ | <code sql> | ||
+ | -- 1 | ||
+ | SELECT name, population, headofstate, | ||
+ | FROM | ||
+ | Country, | ||
+ | ( | ||
+ | --2 공식언어(official languages)의 개수에 기반한 국가 코드(country codes)를 비교한다. | ||
+ | SELECT countrycode, | ||
+ | FROM CountryLanguage | ||
+ | WHERE isofficial=' | ||
+ | GROUP BY countrycode | ||
+ | HAVING nr= | ||
+ | ( | ||
+ | -- 3 SELECT #4 번으로부터 nr_official_language의 최대크기를 알아낸다. | ||
+ | SELECT MAX(summary.nr_official_languages) | ||
+ | FROM | ||
+ | ( | ||
+ | -- 4 모든 국가와 각각의 공식언어(official languages)의 개수를 찾는다. | ||
+ | SELECT countrycode, | ||
+ | -- CountryLanguage 테이블에서 | ||
+ | FROM CountryLanguage | ||
+ | -- 공식언어가 있는 경우에만 | ||
+ | WHERE isofficial=' | ||
+ | -- countrycode 순서로 | ||
+ | GROUP BY countrycode | ||
+ | -- 원래의 테이블 이름 AS 별칭으로 임시로 쓸 이름 | ||
+ | ) AS summary | ||
+ | ) | ||
+ | ) as top | ||
+ | WHERE Country.code=top.countrycode | ||
+ | </ | ||
+ | |||
+ | |||
+ | 4. 이 SELECT 문은 모든 국가와 각각의 공식언어(official languages)의 개수를 찾는다. | ||
+ | |||
+ | 3. 이 SELECT 문은 SELECT #4 번으로부터 nr_official_language의 최대크기를 알아낸다. 동시에, 최대크기를 위해 countrycode 컬럼을 얻는 방법은 SQL-표준이 아니다. | ||
+ | |||
+ | 2. 이 SELECT에서, | ||
+ | |||
+ | 1. 마지막으로 우리는 매칭한(matching) 국가에 관해 국가정보(country information)를 찾기 위한 국가(country) 테이블에 기대어 JOIN을 사용할 수 있다. | ||
+ | |||
+ | ====== delete from 같은 테이블의 특정 조건 ====== | ||
+ | |||
+ | - Table AAA 에 seq, evt_type 이 있고 | ||
+ | - seq는 고유 번호 | ||
+ | - evt_type은 두번째 고유 번호라고 하고 | ||
+ | - evt_type 이 1 인 것만 삭제하려고 하면 | ||
+ | |||
+ | <code sql> | ||
+ | select * from AAA where evt_type = 1; -- 이 조건에 해당되는 것들만 삭제하려고 하면 | ||
+ | |||
+ | delete from AAA where seq = (select seq from AAA where evt_type = 1); | ||
+ | -- 이렇게 쓰면 아래 에러가 뜸 | ||
+ | </ | ||
+ | |||
+ | SQL Delete: can't specify target table for update in FROM clause | ||
+ | |||
+ | 에러가 뜬다. | ||
+ | |||
+ | JOIN을 사용해서 테이블을 다시 결햡 시켜 삭제 해야 한다. | ||
+ | |||
+ | <code sql> | ||
+ | DELETE a | ||
+ | FROM AAA AS a | ||
+ | JOIN ( select * from AAA where evt_type = 1 ) AS b | ||
+ | ON a.seq = b.seq; | ||
+ | </ | ||
+ | |||
+ | **evt_type == 1**이고, **seq가 같은 것**만 삭제 | ||
+ | ====== 덤프 ====== | ||
+ | |||
+ | !.edu라는 디비의 모든 테이블 스키마를 백업받으려면 | ||
+ | <code sql> | ||
+ | mysqldump -u mysql_admin -p -d edu > edu_db.sql </ | ||
+ | |||
+ | !.edu라는 디비의 a라는 테이블 스키마를 백업받으려면 | ||
+ | <code sql> | ||
+ | mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql</ | ||
+ | |||
+ | ====== 트랜잭션 ====== | ||
+ | |||
+ | * 도움말 [[http:// | ||
+ | |||
+ | 키워드 | ||
+ | |||
+ | * begin (또는 start TRANSACTION) 으로 시작 | ||
+ | * rollback 으로 되돌리기 | ||
+ | * commit으로 완료 | ||
+ | |||
+ | <code sql> | ||
+ | start TRANSACTION; | ||
+ | delete from my_table where idx=2; | ||
+ | select * from my_table; | ||
+ | rollback; -- 되돌리려면 | ||
+ | commit; -- 완료하려면 | ||
+ | </ | ||
+ | |||
+ | 트랜잭션을 걸고 나서 롤백이나 커밋을 해도, 몇개가 되돌려졌는지 로그에는 나오지 않는다. | ||
+ | select로 꼭 되돌려졌는지 확인해야 한다. | ||
+ | |||
+ | 주의 | ||
+ | |||
+ | * set autocommit=0 으로 자동 커밋되지 않도록 설정 되어야 한다. | ||
+ | |||
+ | |||
+ | |||
+ | ====== 기타 ====== | ||
+ | |||
+ | ===== limit ===== | ||
+ | |||
+ | * 가져온 곳 : [[http:// | ||
+ | |||
+ | 사용 법은, | ||
+ | |||
+ | <code sql> | ||
+ | select * from [TABLE] | ||
+ | limit [시작번호], | ||
+ | -- 또는 | ||
+ | select * from [TABLE] | ||
+ | limit [가져올 개수]; | ||
+ | </ | ||
+ | |||
+ | limit는 인덱스를 타지 않기 때문에 개수가 많아지면 느려진다고 한다. ([[http:// | ||
+ | |||
+ | 인덱스를 타게 할 방법이 있는데, PK가 INT 타입일때 효과를 볼 수 있다고, | ||
+ | |||
+ | <code sql> | ||
+ | -- 어거지로 인덱스를 사용하는건가? | ||
+ | select * from [TABLE] where [TABLE_PK] > [PIVOT_VALUE] limit 10; | ||
+ | </ | ||
+ | |||
+ | PK가 int 와 같은 숫자 타입이 아닌 경우라면, | ||
+ | |||
+ | ==== RowNum 구현 ==== | ||
+ | |||
+ | <code sql> | ||
+ | select @rnum: | ||
+ | from | ||
+ | (select * from [TABLE]) t, (select @rnum := 0 ) R; | ||
+ | </ | ||
+ | |||
+ | ===== delimiter $$ ===== | ||
+ | |||
+ | 딜리미터를 바꾸는 명령어 | ||
+ | |||
+ | * 그대로 사용한다면, | ||
+ | |||
+ | 따라서, | ||
+ | |||
+ | * 딜리미터 (문장 종결)를 다른 기호 (이번에는 $$)로 바꾸고 <code sql> | ||
+ | * SQL 문장을 여러 줄 추가 | ||
+ | * 마지막 부분에는 <code sql> | ||
+ | |||
+ | 프로시져나 펑션 트리거에 코드를 추가할때 사용한다. | ||
+ | |||
+ | ===== Lock 걸려 있는지 확인 하는 방법 ===== | ||
+ | |||
+ | SHOW PROCESSLIST; | ||
+ | |||
+ | state 가 **Waiting for table metadata lock** 라면 LOCK 상태. ID 번호를 기록했다가 KILL 한다. | ||
+ | ====== Web Link ====== | ||
+ | |||
+ | 나중에 읽어보면서 정리할 사이트 | ||
+ | |||
+ | * [[http:// |
db/mysql.txt · 마지막으로 수정됨: 2024/04/23 22:43 저자 127.0.0.1