• MySQL
• Select
• Insert
• Update
• Delete
• Function
• View
• 역공학
• 참고 문헌

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_명;

 

참고 문헌

---

분류: Database

---