sql

CREATE TABLE 컬럼 하나 생성

CREATE TABLE `tabPractice` (`Id` int(11)  NULL DEFAULT NULL) COMMENT = 'a' ENGINE = InnoDB;

CREATE TABLE 컬럼 여러개 생성

CREATE TABLE `dw_heat_mqtt` 
(`HeatSeqmqtt` int(11)  NOT NULL DEFAULT 0  COMMENT '',
`nowtime` varchar(25)  NULL DEFAULT '' COMMENT '날짜' COLLATE 'utf8_general_ci',
`code` varchar(25)  NULL DEFAULT '' COMMENT '농장코드' COLLATE 'utf8_general_ci',
`aniSeq` int(11)  NULL DEFAULT 0  COMMENT '개체 시퀀스',
`aniRFID` varchar(20)  NULL DEFAULT '' COMMENT 'RFID' COLLATE 'utf8_general_ci',
`aniOwnerNo` varchar(20)  NULL DEFAULT '' COMMENT '개체번호' COLLATE 'utf8_general_ci',
`Device` varchar(20)  NULL DEFAULT '' COMMENT '기기코드' COLLATE 'utf8_general_ci',
`Walk` decimal(10,0)  NULL DEFAULT 0  COMMENT '행동',
`Temp` decimal(10,2)  NULL DEFAULT 0  COMMENT '온도',
`nowSeq` varchar(25)  NULL DEFAULT '' COMMENT '현재MQTT' COLLATE 'utf8_general_ci',
`totalSeq` varchar(25)  NULL DEFAULT '' COMMENT '총MQTT' COLLATE 'utf8_general_ci',
`OVER_RANGE` varchar(50)  NULL DEFAULT '' COMMENT '' COLLATE 'utf8_general_ci',
`MOTION_DETECT` varchar(50)  NULL DEFAULT '' COMMENT '' COLLATE 'utf8_general_ci',
`ROLL_PITCH_RANGE` varchar(50)  NULL DEFAULT '' COMMENT '' COLLATE 'utf8_general_ci',
`OLD_SUM_CNT` varchar(50)  NULL DEFAULT '' COMMENT '' COLLATE 'utf8_general_ci',
`AllCows` int(11) unsigned  NULL DEFAULT 0  COMMENT '',
`MoveYN` varchar(1)  NULL DEFAULT 'N' COMMENT '' COLLATE 'utf8_general_ci',
`gflagYN` varchar(1)  NULL DEFAULT 'N' COMMENT '' COLLATE 'utf8_general_ci',
`flagYN` varchar(1)  NULL DEFAULT 'Y' COMMENT '' COLLATE 'utf8_general_ci',
`regDate` datetime  NULL DEFAULT NULL  COMMENT '최초저장일',
`issueDate` datetime  NULL DEFAULT NULL  COMMENT '최종저장일',
`issueID` varchar(20)  NULL DEFAULT '' COMMENT '최종저장자 ID' COLLATE 'utf8_general_ci',
 PRIMARY KEY (`HeatSeqmqtt`) USING BTREE
)
 COMMENT = '' 
ENGINE = InnoDB;

 

프로시저 생성시 delimiter // 시작시, end// delimiter //

-- 프로시저 dawoon.dw_rumination_SP 구조 내보내기
DROP PROCEDURE IF EXISTS `dw_rumination_SP`;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `dw_rumination_SP`(
	IN `_ruSeq` INT(10)
		,
	IN `_aniSeq` SMALLINT(10)
		,
	IN `_rumDate` DATETIME
		,
	IN `_farmcode` SMALLINT(10)
		,
	IN `_comp` varchar(300)
		,
	IN `_timer2` varchar(300)
		,
	IN `_deviceCd` INT(10)
		,
	IN `_uniqueIndex` INT,
	IN `_indx` VARCHAR(100),
	IN `_mx` INT,
	IN `_msds` DECIMAL(10,5),
	IN `_coss` DECIMAL(10,5),
	IN `_ps` DECIMAL(10,5),
	IN `_flagYN` VARCHAR(1)
		,
	IN `_issueID` VARCHAR(20)


)
BEGIN
	
	DECLARE _seq INT default 0;
	
	select ruseq into _seq from dw_rumination 
    	where rumdate = _rumdate and devicecd=_deviceCd 
        	and farmcode = _farmcode and comp = _comp and timer2 = _timer2;
	
	if _seq = 0 then
	begin
		IF _ruSeq = 0 THEN
			BEGIN			
				SELECT IFNULL(max(ruSeq),0)+1 INTO _ruSeq FROM dw_rumination;
				INSERT INTO dw_rumination(ruSeq, aniSeq, farmcode, rumDate, comp
                , timer2, deviceCd, uniqueIndex, indx, mx, msds, coss, ps
                ,flagYN, regDate, issueDate, issueID) 
				VALUES( _ruSeq, _aniSeq, _farmcode, _rumDate, _comp
                , _timer2, _deviceCd, _uniqueIndex, _indx, _mx, _msds, _coss, _ps
                , 'Y', now(), now(), _issueID);
			END;
		ELSE
			BEGIN
				UPDATE dw_rumination SET 
				aniSeq =_aniSeq, farmcode= _farmcode, rumDate =_rumDate
                , comp=_comp, timer2=_timer2, deviceCd=_deviceCd
                , uniqueIndex = _uniqueIndex, indx= _indx, mx = _mx
                , msds = _msds, coss = _coss , ps = _ps ,
				flagYN=_flagYN, issueDate=now(), issueID=_issueID WHERE ruSeq=_ruSeq;
			END;
		END IF;
		
		call sp_Synch('dw_rumination', _ruSeq, '', '', '', 0);
	end;
	end if;
END//
DELIMITER //;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

show index로 인덱스 가지고 있는 컬럼보기

SHOW INDEX FROM dw_feed_move_robot;

schema.innodb_sys_indexes에서 데이터베이스에서 인덱스 가지고 있는 컬럼 전부 보기

SELECT * FROM Information_schema.innodb_sys_indexes

information_schema.statistics 에서 인덱스보기

SELECT index_name AS 'index' 
	, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS 'index_columns' 
    , IF( MAX(NON_UNIQUE)=0, 'Yes', '') AS 'IsUnique' 
FROM information_schema.statistics 
WHERE table_schema = 'dawoon' AND TABLE_NAME = 'dw_feed_move_robot' 
GROUP BY TABLE_NAME, index_name 
order BY isUnique desc;

 

alter를 사용해서 컬럼이름 바꾸기

ALTER TABLE account_book CHANGE `열바꿀내용` `열 바꾼내용` INT(11);

alter를 사용해서 컬럼 더하기

ALTER TABLE account_book ADD `열 추가` int(11) NULL DEFAULT NULL

alter modify를 사용해서 null허용시키기

ALTER TABLE account_book MODIFY `열 내용` int(11) NOT NULL DEFAULT NULL

alter modify를 사용해서 기본값 내용 바꾸기

ALTER TABLE dc_account MODIFY COLUMN `accAcount` varchar(20) NULL DEFAULT '기본값내용'

show table 사용해보기

show table STATUS;
show table status where NAME = 'dw_breeding';

특정 데이터베이스 schem.column 보기

SELECT * FROM information_schema.`COLUMNS` WHERE table_schema ='데이터베이스이름';

테이블정보, 컬럼정보 leftjoin

SELECT b.table_name tbl, a.table_comment cmt, COUNT(*) cnt, b.EXTRA ex  
	FROM information_schema.tables a 
   left JOIN information_schema.columns b 
   ON a.TABLE_NAME=b.table_name 
   WHERE a.table_schema = 'dawoon' AND b.table_schema = 'dawoon'  
   	AND a.table_type='BASE TABLE' group BY b.TABLE_NAME ORDER BY b.TABLE_NAME asc;

프로시저 생성쿼리 보기 show create procedure

SHOW CREATE procedure dw_Heat__Bal_Check_Insert_SP__HER

SHOW CREATE procedure dw_Heat__Bal_Check_Insert_SP__HER

프로시저보기

 show procedure status

function보기

show function status

뷰 보기

SHOW FULL TABLES IN dawoon WHERE TABLE_TYPE LIKE 'VIEW';

뷰 보기2

SELECT TABLE_NAME viewName FROM information_schema.`TABLES` 
	WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'dawoon';

이벤트 보기

SELECT * FROM information_schema.EVENTS  WHERE event_schema='dawoon';

function 생성쿼리 보기

show create function split

event 생성쿼리보기

SHOW CREATE EVENT dw_Heat__Event_Auto_Bal_1

컬럼 맨앞으로 보내기

ALTER TABLE dw_device_config MODIFY COLUMN devSeq int(10) unsigned FIRST

컬럼 지정된컬럼뒤에 위치 시키기

ALTER TABLE dw_device_config MODIFY COLUMN issueDate datetime AFTER regDate