SQL

SQL을 정리 합니다.

MySQL

Select

• Select 문

• http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

select distinct ~
  from ~
  left join ~
    on ~
 inner join ~
    on ~
 where ~
   and date_start >= '2013-03-17 07:09:06'
   and date_start <= '2013-03-18 07:09:06'
   and ~ like '~%'
   and ~ in (~, ~, ~)   
 order by ~ asc/desc

Insert

• Insert 문

insert into ~ (~)
       values (~);

• Insert with select 문

insert into ~ (~)
select ~;

• Insert/Update with select 문

insert into table1 (~)
select ~
    on duplicate key update table1.id = table1.id;

Update

• Update 문

update ~
   set ~ = ~,
       execute_time='2013-03-17 06:58:00'

• table2로 table1 update

update table1, table2
   set table1.val = table2.val
 where table1.id = table2.id;

Delete

• Delete 문

Function

• ltrim(~), rtrim(~)
• ifnull($value, $default)
• concat(~, ~)

• function 생성

DROP FUNCTION IF EXISTS getTotals #
CREATE FUNCTION getTotals(d_id VARCHAR(50), start_date VARCHAR(30), end_date VARCHAR(30)) RETURNS INT(10)
BEGIN
    DECLARE totals INT(10);
    SET totals = (
        SELECT COUNT(*) AS total FROM device_info
        WHERE
        DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")>=start_date
        AND
        DATE_FORMAT(CONVERT_TZ(device_info.create_time, "UTC", "Asia/Seoul"), "%Y-%m-%d %H:%i:%S")<=end_date
        AND
        device_info.device_id = d_id
    );
    RETURN totals;
END #
 
SELECT getTotals("2012-11-26 00:00:00", "2012-12-26 23:00:00");

• 참고 문헌

• http://dev.mysql.com/doc/refman/5.0/en/functions.html
• http://php.net/manual/kr/ref.mysql.php

View

drop view CustInfo_V;
create view CustInfo_V as
select ~;

역공학

• Table 목록 가져오기

SELECT table_name, table_rows, avg_row_length, table_collation 
  FROM information_schema.`TABLES` T
where table_schema = '테이블명';

• Table 정보 가져오기

SELECT column_name, column_default, is_nullable, data_type, character_maximum_length,
      numeric_precision, numeric_scale, collation_name
 FROM information_schema.`COLUMNS` C
where table_schema = '스키마명'
  and table_name = '테이블명'
order by ordinal_position;

• Procedure 정보 보기

#--- Procedure 목록
SHOW PROCEDURE STATUS where db = 'DB_명';
#--- Procedure 소스
SHOW CREATE PROCEDURE DB_명.Procedure_명;