MySQL(마이에스큐엘) 관련

13년 8월 기준.

 

mysql-installer-web-community // 웹에서 다운

mysql-installer-community // 풀 버전

 

mysql 콘솔

mysql -u root -p

Enter password:

use garage;

select * from garage_info

quit;

 

root 패스워드 재설정

mysql stop후에 콘솔창에서

mysqld --skip-grant

// 권한 스킵

다른창에서

mysql> use mysql;

mysql> UPDATE user SET password=PASSWORD('root') where user='root';

mysql> FLUSH PRIVILEGES;

mysql> quit

 

localhost가 아닌 서버 아이피로 접속시에는 root외의 계정이 필요

(Host is not allowed to connect to this MySQL server)

 

phpMyAdmin의 사용권한에서 사용자 추가 가능

// mysql자체에 사용자를 추가

// phpMyAdmin하고 매칭되지는 않는듯

 

phpMyAdmin에서는 not null이 아니라 null로 판단. ex)Null 아니오 = not null, Null 예 = null 허용

 

/usr/share/phpmyadmin/libraries/config.default.php

// phpmyadmin euc-kr 추가

 

테이블명 대소문자 구분

 

mysql DATETIME, TIMESTAMP 차이

자동입력 기능 여부

DATETIME 수정시마다 날짜를 적어줘야함.

TIMESTAMP 날짜를 지정 안해줘도 자동으로 입력 가능. (옵션에 CURRENT_TIMESTAMP 를 사용)

자료형에서 지원되는 범위

DATETIME 의 경우 1000년 ~ 9999년까지 지원

TIMESTAMP 의 경우 1970년 ~ 2037년까지만 사용가능

크기

DATETIME은 8bytes

TIMESTAMP는 4bytes

 

SELECT * FROM test WHERE ENCRYPT('test1234', ddata)=ddata

INSERT INTO test VALUES(id, "test1234", ENCRYPT('test1234'))

// ENCRYPT 관련해서 INSERT

 

Every derived table must have its own alias 에러

()를 사용했을때 이름을 지정해주지 않아서 생기는 에러

()a 이런식으로 이름지정

 

MySQL 함수 생성 에러

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

show global variables like 'log_bin_trust_function_creators';

를 해서 상태를 확인한 후,

SET GLOBAL log_bin_trust_function_creators = 1;

을 실행해서 ON으로 바꿔준다.

 

newly created file is older than distributed files!

// 시스템의 시간보다 패키징된 시간이 최근일 경우 미래에서 온 파일로 생각하고 컴파일 도중 에러.

// rdate -s time.bora.net && clock -w 명령어 실행 후 다시 configure.

 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

mysql -u root -p로 접속. 패스워드 입력.

 

SELECT * FROM user WHERE user_id=&user_id;

// mysql 대체 변수 사용안됨

 

날짜 VARCHAR(14)로 잡음. 통계시 더 편리.

YN 자료형 CHAR로 하나 VARCHAR(1)로 하나 근소한차이. 일치성을 위해 그냥 VARCHAR 사용

 

테이블명 무조건 대문자. varchar "", 숫자는 없어도됨.

 

SELECT DATE_FORMAT('2017-05-04 20:23:01', '%Y%m%d');

-> '20170504'

// 날짜타입 문자열 변환. DATETIME 형식 변환해서 값받음.

 

ALTER TABLE board CHANGE count cnt int;

// 컬럼명 변경

 

SELECT DATE_FORMAT("2019-12-03 12:34:56", '%Y-%m-%d') DATEONLY,

DATE_FORMAT("2019-12-03 12:34:56",'%H:%i:%s') TIMEONLY

// DATE_FORMAT

SELECT DATE_FORMAT(REG_DT, '%Y-%m-%d') REG_DT,

 

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

// Edit-Preferences-SQL Editor에 Safe Updates 체크 해제

 

LEFT(orderStatus, 1) = 'd' // 왼쪽 1개 자르기

 

ALTER TABLE test MODIFY SEQ INT NOT NULL AUTO_INCREMENT;

// auto_increment로 변경

ALTER TABLE test AUTO_INCREMENT=1

// auto_increment 초기화

 

SELECT CONCAT(firstName, lastName) FROM USER;

// concat 문자열 합치기

 

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

// auto_increment는 항상 pk여야 함.

ALTER TABLE `NCdinos`.`BOARD_INFO`

CHANGE COLUMN `SEQ` `SEQ` INT(11) NOT NULL AUTO_INCREMENT ,

ADD PRIMARY KEY (`SEQ`);

 

Error Code: 1136. Column count doesn't match value count at row 1

// SEQ auto_increment가 없다고 뜨는 에러. 값 명시해줘야함.

INSERT INTO table(ID) VALUES("id");

 

SELECT COUNT(*) AS MAX FROM BOARD_INFO WHERE USE_YN="Y";

// COUNT 가져오기

 

SEQ INT not null auto_increment primary key, 하면 크기 int(11)로 잡힘.

 

INSERT INTO TB_BOARD (COULUMN) VALUES (VALUE)

ON DUPLICATE KEY UPDATE COULUMN = VALUE;

// 오라클 MERGE INTO 기능. 없으면 삽입, 있으면 갱신.

 

테이블 작업-AUTO_INCREMENT

// AUTO_INCREMENT 수정

SELECT LAST_INSERT_ID();

// AUTO_INCREMENT 확인

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'table name' AND table_schema = DATABASE( );

// AUTO_INCREMENT 확인. 테이블 선택

 

SELECT * FROM 'es_memberSns' WHERE 1 LIMIT 10;

// TOP 10 아니라 LIMIT 10

SELECT * FROM `es_memberSns` WHERE 1 ORDER BY sno DESC LIMIT 10

 

SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR )

// 1년전

SELECT * FROM `es_orderGoods` WHERE goodsNo='1000000327' and regDt between DATE_SUB(DATE(NOW()), INTERVAL 1 YEAR) AND DATE(NOW()) AND orderStatus != 'r3' GROUP BY orderNo

// DATE between. DATE_ADD(DATE(NOW()), INTERVAL -1 YEAR ) 잘못된 쿼리.

BETWEEN '2019-09-04 00:00:00' AND '2020-09-02 23:59:59'

// BETWEEN 값 시분초까지 들어가야 제대로 검색 됨. 연월일만 넣었을때 데이터 누락

 

Data truncation: Truncated incorrect DOUBLE value: '<br>'

// 조건문 필드 비교할 수 없는 데이터가 존재. IFNULL(field, 0) 또는 AND를 ,로 변경

 

mysql 외부 접속 설정

use mysql; // mysql db 접속

insert into user (host,user,password) values('xx.xx.xx.xxx','USER',password('pswd')); // ip, user 추가

insert into db values('xx.xx.xx.xxx','USER','DB','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y','y') // 사용할 db 설정

// ip 대신 % 입력하면 anybody.

flush privileges; // 적용

 

phpmyadmin description 확인

구조-인쇄용 보기 or 구조-데이터 사전

 

가져오기-파일 내용으로 테이블 데이터 대체하기

// csv 에러시 체크

 

MySQL은 FULL OUTER JOIN 없음. LEFT OUTER JOIN과 RIGHT OUTER JOIN을 UNION해서 구현.

 

SHOW FULL COLUMNS FROM es_orderGoods;

// 테이블 코멘트 확인 위해 테이블 FULL 조회.

 

SELECT

table_name, table_comment

FROM

information_schema.tables

WHERE

table_schema = 'medits_godomall_com' AND table_name = 'es_orderGoods';

// 특정 테이블 코멘트 조회. 테이블 미지정시 DB 전체 테이블.

 

SELECT

table_name, column_name, column_comment

FROM

information_schema.columns

WHERE

table_schema = 'medits_godomall_com' AND table_name = 'es_orderGoods';

// 특정 테이블 컬럼 코멘트 조회. 테이블 미지정시 DB 전체 테이블 컬럼.

 

replace(og.goodsNo,',','')

// 콤마 제거

 

EXPLAIN

// type이 all이면 풀스캔. 수정 필요.

STRAIGHT_JOIN

// mysql 내부에서 join 순서바꿔서 실행. 명시된대로 하려면 STRAIGHT_JOIN 사용.

 

GROUP BY SQL syntax 에러

// ORDER BY는 GROUP BY 뒤에.

 

Duplicate column name error

// 필요 컬럼명 명시

 

SET @rownum:=0; // 초기화

SELECT

@rownum:=@rownum+1, oc.*

FROM es_orderCoupon oc

// ROWNUM 사용.

 

SELECT JSON_UNQUOTE(JSON_EXTRACT('{"ports": "1"}', '$.ports')) AS PORT

// JSON 쌍따옴표 제거

CONVERT(info USING utf8) // 한글깨짐. CONVERT 해줘야 함.

 

LENGTH // Byte 길이를 가져옴. 한글은 정확한 길이를 알 수 없음.

CHAR_LENGTH // 문자 수 가져옴.

 

REPLACE(og.optionInfo, '\\', '')

// \ REPLACE시 두번 사용.

 

Unknown column 'sid' in 'order clause'

// union 시에 안쓰는 column으로 order by 하면 에러

 

check the manual that corresponds to your MySQL server version for the right syntax to use near 'mm

// DELETE는 alisas 사용이 안됨.

 

SELECT *

FROM es_member

WHERE 1=1

AND CHAR_LENGTH(memId) <= 4

LIMIT 5

// 아이디 4자 이하

LENGTH() // byte 값을 반환

CHAR_LENGTH() // 문자열 길이를 반환

 

UPDATE product SET column=REPLACE(column, "china", "korea")

// mysql 문자열 치환

 

SELECT ROUND(AVG(goodsPt), 1) FROM es_plusReviewArticle p

WHERE 1=1

AND goodsNo = '1000000412'

// 평점 평균의 반올림.

 

SELECT ROUND(2.25, 1)

// ROUND 반올림, 2번째 파라미터. CEIL은 파라미터 없음.

 

SELECT * FROM es_order LIMIT 1,10; // 1은 두번째부터 10개라는 뜻.

SELECT * FROM es_order LIMIT 0,10; // LIMIT 10과 같음.

 

SELECT * FROM es_orderGoods ORDER BY regDt DESC LIMIT 10

-- 최근 상품 주문 데이터

// LIMIT는 역순 안됨. ORDER BY로 사용.

 

SET SESSION sql_mode='NO_BACKSLASH_ESCAPES';

SET SESSION sql_mode='';

// \\로 LIKE 백슬래시 사용. 안될경우, 백슬래시 끄고 시도.

 

ALTER TABLE mc_todayDeliveryAllowZone

AUTO_INCREMENT=1;

// AUTO_INCREMENT 리셋

 

SHOW INDEX FROM tablename;

// 인덱스 확인

ALTER TABLE tablename ADD INDEX indexname (column1, column2);

// 인덱스 추가, 컬럼은 1개도 가능, 2개 이상도 가능

ALTER TABLE tablename ADD UNIQUE INDEX indexname (column1, column2);

// 유니크 인덱스 추가 컬럼은 1개도 가능, 2개 이상도 가능

ALTER TABLE tablename DROP INDEX indexname;

// 인덱스 삭제

 

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbName';

// 테이블 개수 확인

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE 1=1 AND TABLE_NAME LIKE '%es\_%'

// 특정 테이블 개수 확인

 

TRUNCATE TABLE mc_todayDeliveryAllowZone

// 테이블 내 데이터 삭제

 

Data truncated for column

// 컬럼 데이터 넣어야 insert 됨.

 

mysqldump -u 아이디 -p패스워드 Database명 > 파일명.sql

// 백업

 

mysqldump -u 아이디 -p패스워드 Database명 Table명 > 파일명.sql

// 테이블 백업

 

mysql -u 아이디 -p패스워드 Database명 < 파일명.sql

// 백업 복구

 

SELECT * FROM EMP WHERE DEPTNO IN (20, 30);

// IN 으로 배열 데이터 삽입

// mysql은 LIKE IN 안됨. REGEXP '2021-03-22|2021-03-23'; 대체 쿼리

// AND (og.regDt LIKE '2021-03-22%' OR og.regDt LIKE '2021-03-23%') 괄호 제대로 명시

 

SET 이외의 명령문에서는 =가 비교연산자로 취급되기 때문에 SELECT로 변수를 선언하고 값을 대입할 때는 := 를 사용.

SET @goodsNo := '1000000161';

// 변수를 선언해서 컴파일 하는 개념이 아니라 미리 따로 실행시켜서 등록해놓는 개념 SET.

LIKE 할때 + 아니라 CONCAT('%', @email, '%')

 

#1142 -

INSERT INTO medits_godomall_com.es_manager

// export한 db import 시 db명 수정

 

SELECT * FROM es_memberSns

where 1=1

AND snsTypeFl = 'payco'

ORDER BY regDt DESC

LIMIT 10

// LIMIT이 ORDER BY 보다 뒤에 위치.

 

csv로 내보내기해야 sno값까지 export됨.

 

파일 내용으로 테이블 데이터 대체하기

// 체크해야 CSV 데이터 제대로 불러옴.

 

SET @goodsNo := '1000000161,1000000134';

 

SELECT COUNT(*) FROM es_orderGoods og

WHERE 1=1

AND FIND_IN_SET (og.goodsNo, @goodsNo)

// 변수 여러개 등록 후 조회. 각각의 데이터를 조회하는게 아니라 합계를 조회.

// SELECT * FROM es_goods og로 조회하니 각각으로 조회.

 

SELECT m.memId, m.memNm, m.cellPhone, og.orderNo FROM es_orderGoods og

LEFT JOIN es_order o ON o.orderNo=og.orderNo

LEFT JOIN es_member m ON o.memNo=m.memNo

WHERE 1=1

AND og.goodsNo = '1000000298'

AND DATE(og.regDt) = CURDATE()

// 오늘 날짜 CURDATE()

DATE_ADD(CURDATE(), INTERVAL -1 DAY)

// 어제 날짜

AND og.regDt > "2021-04-02 12"

AND og.regDt < "2021-04-02 14"

-- AND og.regDt > "2021-04-01"

-- AND og.regDt < "2021-04-02"

// 특정 기간

AND m.cellPhone IN ('010-1234-5678')

 

LOAD DATA [LOCAL] INFILE '/home/계정/data.txt' INTO TABLE

// 텍스트 파일을 TABLE에 넣기

 

AND mc.memberCouponUseDate > "2021-04-01"

-- AND mc.memberCouponUseDate <= "2021-04-06"

AND mc.memberCouponUseDate < "2021-04-07"

// 날짜엔 <=안됨. 다음날을 적어줘야 함.

 

date type is null, is not null 안됨. !=0, =0은 됨.

 

SELECT @RNUM:=@RNUM+1 AS ROWNUM, B.* FROM TB_BOARD B, SELECT @RNUM:=0) AS A

// mysql 오라클 rownum(조회된 순서대로 넘버링) 구현

 

ORDER BY 1

// 첫번째 컬럼 기준 정렬

 

create user 'userid'@'localhost' identified by 'password'

// localhost대신 %로 써도 됨.