mysql>
여러가지 명령정리
mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%' 조건에 맞는 variables만 출력
mysql> show databases; database목록
mysql> show tables; 현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명; 지정된 db명이 소유한 테이블목록
mysql> show tables like 'mem%'; 조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> show columns from 테이블명; 테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status; 현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명; 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2; 테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼추가
mysql> alter table 테이블명 del 컬럼명; 컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb; 테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version(); MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert
테이블이 존재여부 파악
DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (...);
프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.
ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.
접속
mysql {-h 접속호스트} -u 사용자 -p 사용DB
-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.
mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges
검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp "정규표현식";
백업 & 복구
mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일
mysqldump -u root -p --opt db_moyiza > moyiza.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)
mysql -u moyiza -p db_moyiza < moyiza.sql
mysqldump -u root -p --opt db_moyiza | mysql ---host=ns.moyiza.net -C db_moyiza
테이블 생성구문만을 화면에서 보려면 다음과 같이 --no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력
mysqldump -u 유저명 -p --no-data db명 테이블명
테이블 검사
isamchk
오라클 sysdate와 동일
insert into test values('12', now());
유닉스 time()함수 리턴값 사용
FROM_UNIXTIME(954788684)
UNIX_TIMESTAMP("2001-04-04 :04:04:04")
MySQL 디폴트 DB&로그파일 위치
/var/lib/mysql
/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이스라고 볼 수 있다.
replace
해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)
replace into test values('maddog','kang myung gyu')'
explain
explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌
mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+-------+------+-----------------+-----------------+---------+-------+------+-------+
2 rows in set (0.01 sec)
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Mysql 날짜 관련함수
select date_sub(now(), interval 5 day);
5일후...
select date_add(now(), interval 5 day);
5일 전...
############################################################
- DAYOFWEEK(date) : 해당 날짜의 요일을 숫자로 반환한다. 일요일은 1, 토요일은 7 이다.
- 예 : select DAYOFWEEK('1998-02-03');
- WEEKDAY(date) : 해당 날짜에 대한 요일을 반환한다. 월요일은 0, 일요일은 6 이다.
- 예 : select WEEKDAY('1997-10-04 22:23:00');
- DAYOFYEAR(date) : 해당 날짜의 1월 1일부터의 날수를 반환한다. 결과값은 1에서 366 까지이다.
- 예 : select DAYOFYEAR('1998-02-03');
- YEAR(date) : 해당 날짜의 년을 반환한다.
- 예 : select YEAR('98-02-03');
- MONTH(date) : 해당 날짜의 월을 반환한다.
- 예 : select MONTH('1998-02-03');
- DAYOFMONTH(date) : 해당 날짜의 일을 반환한다. 결과값은 1 에서 31 까지이다.
- 예 : select DAYOFMONTH('1998-02-03');
- HOUR(time) : 해당날짜의 시간을 반환한다. 결과값은 0 에서 23 이다.
- 예 : select HOUR('10:05:03');
- MINUTE(time) : 해당날짜의 분을 반환한다. 결과값은 0 에서 59 이다.
- 예 : select MINUTE('98-02-03 10:05:03');
- SECOND(time) : 해당날짜의 초를 반환한다. 결과값은 0 에서 59 이다.
- 예 : select SECOND('10:05:03');
- DAYNAME(date) : 해당 날짜의 요일 이름을 반환한다. 일요일은 'Sunday' 이다.
- 예 : select DAYNAME("1998-02-05");
- MONTHNAME(date) : 해당 날짜의 월 이름을 반환한다. 2월은 'February' 이다.
- 예 : select MONTHNAME("1998-02-05");
- QUARTER(date) : 해당 날짜의 분기를 반환한다. 결과값은 1 에서 4 이다.
- WEEK(date,first) : 1월 1일부터 해당날가지의 주 수를 반환한다. 주의 시작을 일요일부터 할경우는 두번째 인자를 0, 월요일부터 시작할 경우는 1 을 넣는다. 결과값은 1 에서 52 이다.
- 예 : select WEEK('1998-02-20',1);
- PERIOD_ADD(P,N) : P (형식은 YYMM 또는 YYYYMM 이어야 한다.) 에 N 만큼의 달 수를 더한값을 반환한다. 주의할것은 두번째 인자는 숫자라는 것이다.
- 예 : select PERIOD_ADD(9801,2);
- PERIOD_DIFF(P1,P2) : 두개의 인자 사이의 달 수를 반환한다. 두개의 인자 모두 형식은 YYMM 또는 YYYYMM 이어야 한다.
- DATE_ADD(date,INTERVAL expr type) : 날짜를 더한 날짜를 반환한다.
- DATE_SUB(date,INTERVAL expr type) : 날짜를 뺀 날짜를 반환한다.
- ADDDATE(date,INTERVAL expr type) : DATE_ADD(date,INTERVAL expr type) 와 동일하다.
- SUBDATE(date,INTERVAL expr type) : DATE_SUB(date,INTERVAL expr type) 와 동일하다.
- EXTRACT(type FROM date) : 날짜에서 해당 부분을 추출한다.
- 예 : SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);
SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND);
SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
SELECT EXTRACT(YEAR FROM "1999-07-02");
SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
- 참고 : type 에 사용되는 키워드는 SECOND, MINUTE, HOUR, DAY, MONTH, YEAR, MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, YEAR_MONTH, HOUR_SECOND, DAY_MINUTE, DAY_SECOND 이다.
- 주의 : 계산한 달의 날수가 작을 경우는 해당달의 마지막 날을 반환한다. 예를 들어 select DATE_ADD('1998-01-30', Interval 1 month); 의 경우 1998-02-28 을 반환한다.
- TO_DAYS(date) : 0 년 부터의 날짜수를 반환한다. 이 함수는 1582 이전 날에 대해서는 계산하지 않는다.
- 예 : select TO_DAYS(950501);
- FROM_DAYS(N) : 해당 숫자만큼의 날짜를 반환한다. 이 함수는 1582 이전 날에 대해서는 계산하지 않는다.
- 예 : select FROM_DAYS(729669);
- DATE_FORMAT(date,format) : 날짜를 해당 형식의 문자열로 변환하여 반환한다.
- 예 : select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
- 참고 : 형식은 다음과 같다. %M (달 이름), %W (요일 이름), %Y (YYYY 형식의 년도), %y (YY 형식의 년도), %a (요일 이름의 약자), %d (DD 형식의 날짜), %e (D 형식의 날짜), %m (MM 형식의 날짜), %c (M 형식의 날짜), %H (HH 형식의 시간, 24시간 형식), %k (H 형식의 시간, 24시간 형식), %h (HH 형식의 시간, 12시간 형식), %i (MM 형식의 분), %p (AM 또는 PM)
- TIME_FORMAT(time,format) : DATE_FORMAT(date,format) 과 같은 방식으로 사용할수 있으나 날 이상의 것에 대해서는 NULL 이나 0 을 반환한다.
- CURDATE() : 현재날짜를 반환한다. 숫자와 연산을 할경우 숫자로 변환된다. 형식은 'YYYY-MM-DD' 또는 YYYYMMDD 이다.
- 예 : select CURDATE();
select CURDATE() + 0;
- CURTIME() : 현재시간을 반환한다. 숫자와 연산을 할경우 숫자로 변환된다. 형식은 'HH:MM:SS' 또는 HHMMSS 이다.
- 예 : select CURTIME();
select CURTIME() + 0;
- SYSDATE() : 현재날짜시간을 반환한다. 숫자와 연산을 할경우 숫자로 변환된다. 형식은 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 이다.
- 예 : select NOW();
select NOW() + 0;
- NOW() : SYSDATE() 와 동일하다.
- UNIX_TIMESTAMP() : '1970-01-01 00:00:00' 부터의 초를 반환한다. 인자가 주어질 경우는 해당 날짜에 대한 유닉스 시간을 반환한다.
- 예 : select UNIX_TIMESTAMP();
select UNIX_TIMESTAMP('1997-10-04 22:23:00');
- FROM_UNIXTIME(unix_timestamp) : 유닉스시간에서 날짜 형식으로 변환한다.
- 예 : select FROM_UNIXTIME(875996580);
- FROM_UNIXTIME(unix_timestamp,format) : 유닉스시간을 날짜형식으로 변환하고 DATE_FORMAT(date,format) 에서 설명한 포맷으로 변환하여 반환한다.
- 예 : select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
- TIME_TO_SEC(time) : 해당 시간의 0:0:0 에서부터의 초를 반환한다.
- 예 : select TIME_TO_SEC('22:23:00');
- SEC_TO_TIME(seconds) : 초를 시간으로 바꾼다.
- 예 : select SEC_TO_TIME(2378);
@#@#@#@# @등록된 계시물이 5분을 넘었느냐를 알아봄....
select FROM_UNIXTIME(UNIX_TIMESTAMP(r_resdate)+300) as chk_date1, now() as chk_date2
from reserve_info where r_midx=1 order by r_resdate desc limit 0, 1
@@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$################
SELECT a.* ,
(select max(sgl_cnt) from room_info where r_hno=a.ad_no) as aaa
FROM hotel_info a where a.ad_no=1
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@33333333@@333333333333333333333333333333333333333333333333333333333333333333333333
고급쿼리 종류별로 있음.
@33333333@@333333333333333333333333333333333333333333333333333333333333333333333333
스칼라 서브쿼리
-- 하나의 행에서 하나의 칼럼값(필드값= 스칼라값)을 출력하는 서브쿼리
-- 서브쿼리의 결과값이 메인쿼리의 조건값으로 쓰인다.
select employee_id, last_name, (case
when department_id=
(select department_id
from departments
where location_id = 1800)
then 'Canada' else 'USA' end) location
from employees;
select * from departments where location_id = 1800;
***********************************************************************
CORRELATED Subquery
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
from employees
where department_id = outer.department_id);
-- 메인 쿼리의 행을 받아서 서브쿼리에 적용해서 메인쿼리을 수행하고
다시 다음 행을 하나씩 순차적으로 서브쿼리에 적용해서 메인쿼리을 실행한다.
-- 각 행과 서로 연관된 값을 출력할때 사용
-- 부서별 평균 월급을 검색하고 각 부서별 평균 월급 보다 많이 받는 사원을 출력하라
select avg(salary) from employees where department_id =90; -- 부서별 평균 월급
select last_name, salary from employees where department_id =90; -- 부서별 검색
select avg(salary) from employees where department_id =60;
select last_name, salary from employees where department_id =60;
************************************************************
select e.employee_id, last_name, e.job_id
from employees e
where 2<= (select count(*) from job_history where employee_id = e.employee_id);
-- employees 테이블에서 사원 id를 받아서 서브쿼리의 job_history 테이블에서
count 한값을 메인쿼리의 조건으로 해서 두번 이상 업무를 바꾼 사원정보를
순차적으로 출력하라.
select * from job_history;
**********************************************************************
EXISTS 연산자 사용예
select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 'x'
from employees
where manager_id= outer.employee_id);
-- 서브쿼리에서 만족하는 값이 검색되면 더이상 서브쿼리에서 검색않고 메인쿼리에서
출력한후 서브쿼리에서 다음 행을 검색한다.
-- IN 연산자를 사용하여 같은 결과를 나오게 할 수 있다
select employee_id, last_name, job_id, department_id
from employees
where employee_id in (select manager_id
from employees
where manager_id is not null);
NOT EXISTS 연산자 사용 예
SELECT department_id, department_name
FROM departments d
where not exists (select 'x'
from employees
where department_id = d.department_id);
-- 서브쿼리에서 조건에 만족하지 않은값일 경우 메인쿼리에서 출력
-- NOT IN을 사용할수 있다 . 하지만 NULL 값이 존재할경우 조건에 만족하더라도
쿼리에서 no rows 로 출력될수 있다
SELECT department_id, department_name
FROM departments
where department_id not in (select department_id from employees);
*********************************************************************************
CORRELATED UPDATE
--연관관계를 이용해서 UPDATE 함으로써 추가된 칼럼에 필드 값 입력
alter table employees add (department_name varchar2(14));
desc employees
select employee_id, last_name, job_id, manager_id, department_name from employees;
update employees e
set department_name = (select department_name
from departments d
where e.department_id = d.department_id);
select employee_id, last_name, job_id, manager_id, department_name from employees;
alter table employees drop (department_name) ;
desc employees
CORRELATED DELETE
-- 연관관련 서브쿼리을 이용해서 copy_emp 테이블의 row 중에서 job_history 테이블에
존재하는 행을 삭제
create table copy_emp as select * from employees; -- TEST 테이블
desc copy_emp
desc job_history
select employee_id from copy_emp;
DELETE from copy_emp e
where employee_id =
(select distinct employee_id from job_history
where employee_id = e.employee_id);
select employee_id from copy_emp;
rollback;
*********************************************************************************
WITH 절 사용 예
-- 내부적으로 WHIT절은 인라인 뷰 혹은 임시 테이블로 해석 된다.
with
dept_costs as
(select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name),
avg_cost as
(select sum(dept_total)/count(*) as dept_avg
from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name;
-- 부서별 salary total이 전체 salary total의 평균보다 높은 부서를 출력하라.
-- dept_costs : 부서별 salary total avg_cost : 전체 부서의 salary total의 평균
-- 여러번 사용되어야 하는 인라인 뷰 혹은 임시 테이블을 WITH 절을 이용하여 미리 생성한 후
select 문에서 호출한다. 쉼표로써 여러개 생성할수 있다.
select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name; -- 부서별 월급 합계
select avg(sum(e.salary))
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name; -- 부서별 월급 합계의 평균