Database – TrudsKr

282월 2023 by Truds No Comments

Groupby 에 대해서 Count 하기

Database

MySQL에서 Count를 하다 보니 SELECT에서 GroupBy 를 하면은 정확한 Count가 나오지 않는 문제가 있어서 찾아 보니 찾은 SQL 입니다. SELECT IFNULL(COUNT(S.컬럼), ' ') AS cnt FROM ( SELECT 컬럼 FROM 테이블 WHERE 컬럼='1' GROUP BY 컬럼) AS S; 별다른 형태가 없이 보통 사용하는 SELECT에서 한번 더 SELECT 를 감싸니 쉽게 해결이 되었습니다. 기록을 하지 않으면은 잊어 버릴것 같아서 작성해 둡니다. 결과 화면은 별도로 없습니다.

311월 2023 by Truds No Comments

WebServer에서 사용률 체크하기

CSharp, Database

홈페이지를 만들고 나서 사용률이 저조하면은 상당히 힘이 빠지는 일입니다. 더욱이 정부에서 사용을 하라고 하는데 제작을 할때는 사람들이 요구사항이 상당히 많이 발생하고 좀 더 자기에게 유리하게 만들기 위해서 시간과 노력을 투자 하지만 일단 정부에서 대금이 지급되고 나서는 시들해지기 마련입니다. 하지만 정부도 바보가 아닌 이상 몇년간 사용을 해야 하는데 사람들이 일이 늘어나서 사용하지 않는다는것을 알고 있어서 조금은 강제로 사용 현황을 수집하고 있습니다. 정부 사이트에 현황을 볼 수 있는 사이트가 있지만 매일 들어가서 보기 힘들고 어차피 우리가 만들어준 시스템에서 자료를 가지고 통계를 전송하기에 해당 부분에 착안해서 먼저 우리가 시스템에서 데이터를 통계를 내서 메일을 발송하기로 하였습니다. 상태 표시 화면 이렇게 매일 실행을 해서 상태를 확인 후 우리가 정한 상한선 보다 낮을 경우에 메일을 발송을 해서 경각심을 가지고 해야 합니다. 정부는 몇달간 모았다가 갑자기 감사를 나오기 때문에 미리 미리 해 놓아야 합니다. 메일 발송 내역…

3011월 2022 by Truds No Comments

MySQL 정렬 특정 단어를 우선순위에 두기

Database

MySQL을 이용할 경우에 정렬을 사용할때가 있습니다. 문자를 숫자로 변형해서 하기도 하고 Abc순으로 할 수도 있고 가나다 방법등 다양하게 사용을 할 수 있습니다. 보통의 방법으로 오름차순, 내림차순 정렬을 사용하면은 원하는 결과물에 대해서 정렬을 해서 목록을 만들수 있습니다. 하지만, 특정 단어가 목록중에서 가장 최상단에 와야 하는 경우라는 보통의 방법으로는 할 경우에는 WHERE 조건후에 다른것까지 붙이다 보면은 원하는 정렬이 되지 않아서 고생을 하게 됩니다. ORDER BY CASE WHEN 정렬컬럼명 = '정렬단어A' THEN 1 WHEN 정렬컬럼명 = '정렬단어B' THEN 2 WHEN 정렬컬럼명 = '정렬단어C' THEN 3 WHEN 정렬컬럼명 = '정렬단어D' THEN 4 ELSE 10 END , 정렬컬럼명2 ASC 위에 예제를 이용 할 경우에는 1차적으로 정렬컬럼명을 통해서 정렬을 시도하고 정렬컬럼명2를 통해서 정렬을 하기 때문에 원하는 단어를 문저 올려서 처리를 할 수 있습니다. 잘 사용을 하지 않지만은 꼭 필요할때 유용하게 사용을 할 수 있을것 같아서 글을…

2211월 2022 by Truds No Comments

MySQL 시간정보로 조회

Database

// 1분전 데이터 조회 SELECT * FROM 테이블명 WHERE 시간칼럼명 >= DATE_ADD(NOW(), INTERVAL -1 MINUTE) // 10분전 데이터 조회 SELECT * FROM 테이블명 WHERE 시간칼럼명 >= DATE_ADD(NOW(), INTERVAL -10 MINUTE); // 한시간전 데이터 조회 SELECT * FROM 테이블명 WHERE 시간칼럼명 >= DATE_ADD(NOW(), INTERVAL -1 HOUR); // 하루전 데이터 조회 SELECT * FROM 테이블명 WHERE 시간칼럼명 >= DATE_ADD(NOW(), INTERVAL -1 DAY); // 한달전 데이터 조회 SELECT * FROM 테이블명 WHERE 시간칼럼명 >= DATE_ADD(NOW(), INTERVAL -1 MONTH); DBMS에서 자동적으로 현재 시간을 기준으로 정보를 조회를 할때 사용을 할 수 있습니다. 다른 프로그램에서 SQL문으로 조회를 할때야 시간 정보를 알아서 조합을 할 수 있지만, SQL로만 할 경우에는 이렇게 하면은 시간을 정해서 내용이 조회가 가능 합니다.

102월 2022 by Truds No Comments

MySQL 스키마 변경 프로그램

CSharp, Database

웹 개발을 하다 보면은 동일한 내용을 DBTable를 생성하고 복제를 하는 일이 많이 발생을 하기 마련입니다. 모든 사이트가 동일하지는 않지만 관리를 위해서라도 서버마다 다를수 있어서 한번에 변경할려니 DB툴을 가지고 하는것도 작업 시간이 늘어나는 일이 이고 해서 한번에 바꿀수 있도록 만들게 되었습니다. 개발자에 따라서 여러가지 프로그램으로 제작을 하지만 저는 C#을 이용하여서 GUI 화면에서 보면서 지금것은 어떤지 실시간으로 보기도 하고 변경도 하고 싶어서 만들게 되었습니다. 프로그램에 제가 접속을 해야 하는 서버들이나 DB를 기록을 해 놓은곳에서 가져오는것인지 SQL 자체는 상당히 간단한 편입니다. 컬럼을 변경할 경우에 사용하는 SQL ALTER TABLE `대상DB`.`대상TABLE` CHANGE COLUMN `변경할컬럼` `변경할컬럼명` 형식 NULL여부 DEFAULT '기본값' COMMENT '코멘트'; 컬럼을 추가할 경우에 사용하는 SQL ALTER TABLE `대상DB`.`대상TABLE` ADD COLUMN `추가할컬럼명` 형식 NULL여부 DEFAULT '기본값' COMMENT '코멘트' AFTER `대상컬럼`; 컬럼을 삭제할 경우에 사용하는 SQL ALTER TABLE `대상DB`.`대상TABLE` DROP COLUMN `지울컬럼명`; 이렇게 SQL 명령어만 조건에…

063월 2021 by Truds No Comments

기록물

Database

MES 개념도 그냥 관계도를 그려본것입니다. 상세한 자료는 솔직히 인터넷에 공개를 할 수 없어서 이렇게 구분을 해야 하지 않을까 하는 형태로 그려본 것입니다. MES 개발이라는것을 처음해보는것이라서 준비과정이 상당히 많고 어딘가에 참조를 해서 개발을 한다는것이 조금은 시간이 걸리는것 같습니다. 각자 고유의 기능을 포함하고 DATA를 관리하는 방법을 보여주지 않으려고 해서 화면만으로 보고 기능을 개발하는것은 상당히 고역입니다. 하지만 한번에 성공한다고 하기 보다는 실제 DATA를 입력을 하고 진행을 하다 보면은 조금씩 보완이 되고 업데이트가 되면서 조금씩 완성형에 다가가지 않을까 하는 생각을 하게 되기에 초기 자료로서 기록을 남겨 둡니다.

2312월 2020 by Truds No Comments

[CSharp] MySQL 접속 처리

CSharp, Database

using (dbConnection = new MySqlConnection("Server={0}Port={1};Database={2};Uid={3};Pwd={4}")) { try { /* INSERT, UPDATE 처리 */ dbConnection.Open(); dbQuery = string.Format("UPDATE `table` SET colume = '{0}' WHERE idx = '{1}'", VALUE, IDX); dbConnection.Open(); dbCmd = new MySqlCommand(dbQuery, dbConnection); dbCmd.ExecuteNonQuery(); dbConnection.Close(); /* bind 형식 */ dbConnection.Open(); dbQuery = "INSERT INTO log (`content`,`ip`,`creator`) VALUES (@content,@ip,@creator); "; dbCmd = new MySqlCommand(dbQuery, dbConnection); var bind1 = new MySqlParameter("@content", this.logString() + TEXT +"했습니다."); dbCmd.Parameters.Add(bind1); if( dbCmd.ExecuteNonQuery() == 1) { //성공 } dbConnection.Close(); /* SELECT */ dbConnection.Open(); dbQuery = string.Format("SELECT * FROM `table` WHERE idx = '{0}'; ", IDX); dbCmd = new MySqlCommand(dbQuery, dbConnection); dbTable = dbCmd.ExecuteReader(); if (dbTable.HasRows) { while (dbTable.Read()) { dbTable["cnt"].ToString().Trim(); } } } catch (MySqlException db_ex) { MessageBox.Show(db_ex.Message, "경고"); } finally { } } 그냥 소스코드의 일부 기능을 정리한 코드 입니다. 누군가가 바로 사용할 정도의 코드가…

2311월 2020 by Truds No Comments

MySQL 엑셀로 산출물 만들기

CSharp, Database

문서를 작성해야 하는 일이 항상 있습니다. 그중에서 가장 귀찮은 일이 MySQL의 DB의 형식이나 현재 어떤 스키마를 가지고 있는지를 문서를 만드는 일이 정말 귀찮습니다. 물론 처음 문서를 작성하고 나서 큰 변화가 없다면은 다행이지만 항상 똑같은 상태라고 할 수가 없다 보니 매번 문서를 만들어야 하는 일이 정말 귀찮기만 할뿐입니다. 그렇다고 매번 Database에 들어가서 명령어를 실행하고 만드는것도 일입니다. 고정된 형태면 좋지만 다른 사람에게 공유하기 위해서로 문서로 만들어야 하는 일이 발생을 하게 되면은 또 다시 세팅하고 실행하고 결과값을 저장하는 일이 반복되는 일을 줄이는것이 가장 큰 목표였습니다. C#으로 만들다보니 엑셀 파일 만드는것이 가장 큰 일이였습니다. 내가 설치된 컴퓨터의 환경에 맞추면은 가장 좋지만은 프로그램이라 함은 어느정도 범용성을 가져야 하기에 엑셀로 출력하는 방식에서 많은 고민을 하였습니다. 물론 그전에 MySQL에서 정보를 빼 내는 로직 자체는 이미 완성되어 있어서 그래도 실행을 하면 되지만 마무리가 되지 않아서 한참을 헤메이다가 우연한…

209월 2020 by Truds No Comments

단일 테이블에서 실시간 가동률 뽑기

Database, PHP

일단 하기는 하였는데, 말이 안된다고 생각함! 문제가 뭐냐 하면은 모니터링 데이터를 제가 가공하는것이 아니라, 누군가 가동해 놓은 데이터를 가지고 실시간으로 전달해주는 행단위로 입력된것으로 데이터를 뽑습니다. 위와 같은 화면을 뽑기 위해서 처리해야 될 일은 많지만 이미 내가 만들지 않았고, 있는 정보를 가지고 처리를 해야 합니다. 위와 같은 형식으로 행단위로 엄청나게 쌓이기 시작합니다. 매일 몇초 단위로 데이터가 계속 들어오는 상태입니다. 1개의 Table를 가지고 데이터를 뽑는게 이렇게 힘들것이라고 이번에 처음 느꼈습니다.※ 가능하면 2번 다시 하고 싶지 않음 이제 이름별로 해서 가동률을 한번 뽑아보도록 하겠습니다. MySQL에서 다음의 쿼리문들 사용하였습니다. 위의 화면에 필요한 부분이 아래의 Query 입니다. 보기보다 쉽죠? 만들어 내는대는 하루지만 정리해 놓고 보면은 쉽게 보여집니다. // 이름별 가동율 SELECT A.MACHINE_NAME, CAST(IFNULL(B.MACHINE_PCNT, 0) AS UNSIGNED ) AS MACHINE_PRODUCT_CNT, MIN(C.MACHINE_DATETIME_MIN) AS MACHINE_TIME_MIN, MAX(C.MACHINE_DATETIME_MAX) AS MACHINE_TIME_MAX, CAST(IFNULL(C.USE_TIME, '0') AS UNSIGNED ) AS USE_TIME, CAST(IFNULL(E.USE_TIME_STOP, '0') AS…

139월 2020 by Truds No Comments

MongoDB 백업 & 복원

Database

어느 업체에서 MongoDB를 사용하는데, 해당 DB에서 필요한 자료를 가져와서 정보를 가공해서 보여줘야 하는 사항입니다. 그대로 복사한다고해서 그대로 사용을 할수가 없어서 찾아보게 된 자료입니다. ## MongoDB 백업 mongodump.exe --out .\backup\ --host 127.0.0.1 --port 27017 -uadmin1234 -p1234 --db "데이타베이스" ## MongoDB 복원 mongorestore.exe --host localhost --port 27017 -d"데이타베이스" -uadmin1234 -p1234 --drop "백업디렉토리" 위에 DB를 백업 & 복원을 통해서 C# Applicaiont을 통해서 자료를 조회한다음에 MySQL로 이전하는 작업입니다. MongoDB자체가 RDBMS가 아니라서 가져오는데 애를 먹었지만, 일단 2주간의 노력으로 일단은 만들게 되었습니다.

069월 2020 by Truds No Comments

MySQL 트리거를 통해서 프로시저의 동적 쿼리 실행

Database

결론부터 말하면은 대실패 입니다. 의도한대로 전혀 움직이지 않습니다. 그렇기에 MySQL에서는 절대 하지 마십시오. 물론 다른 DBMS인 오라클, MSSQL은 어떤지 아직 직접해보지 않아서 정확히는 모릅니다. 하지만 제가 테스트한 MySQL에서는 전혀 실행이 되지 않습니다. 실패라고 하여도 과정에 대해서 일단 간단히 작성해 놓고 추후에 이런 일이 있으면 교본을 삼으려고 글을 남깁니다. 요구조건1. INSERT 시에 해당 값을 trigger를 통해서 다른 테이블에 기록2. 다른테이블 기록은 '테이블이름_년월' 형식에 기록해서 추후에 관리 용이3. 값이 없는 부분은 가공해서 넣기 어떻게 보면은 정말 간단한 요구사항입니다. trigger의 INSERT시에 캐치해서 월별로 관리하는 Table에 Insert 하는 부분입니다. 물론 이 외에 될수도 있지만 현재로서는 실패 입니다. DROP TRIGGER IF EXISTS `TRIGGER_TEST`; DELIMITER // CREATE TRIGGER `TRIGGER_TEST` AFTER INSERT ON `TRIGGER_TEST_RAW` FOR EACH ROW BEGIN -- 선언 DECLARE Access_Time VARCHAR(20); DECLARE Ip_From VARCHAR(20); DECLARE Status_From VARCHAR(20); DECLARE Work_Rate VARCHAR(10); DECLARE OrgMessageFrom VARCHAR(80); -- 값…

237월 2020 by Truds No Comments

MySQL Grant 권한 옵션 설명

Database

MySQL User 추가를 할 경우 보통 Grant 옵션을 주지 않는데, 개발서버에서 필요한 경우가 있어서 간단히 한번 정리를 한것입니다. CREATEdatabases,tables, indexs데이터베이스, 테이블 생성DROPdatabases,tables,views데이터베이스, 테이블 삭제GRANT OPTIONdatabases,tables,stored routines사용자들의 권한을 부여하거나 제거LOCK TABLESdatabases테이블 잠그기ALTERtables테이블의 구조를 변경DELETEtables테이블의 행을 삭제INDEXtables인덱스를 생성하거나 삭제INSERTtables,columns행추가SELECTtables,columns행조회UPDATEtables,columns행변경CREATE TEMPORART TABLEStables임시테이블생성CREATE VIEWviews뷰의 생성SHOW VIEWviewsSHOW CREATE VIEWALTER ROUTINEstored routines프로시저 함수의 변경 삭제CREATE ROUTINEstored routines프로시저 함수의 생성 그다지 잘 주지 않는 옵션인데 Root 권한을 주기에는 뭔가 애매모호해서 필요한 부분만 허용을 하고 나머지는 주지 않을 생각이라서 현재 몇가기중 View 관련만 허용을 해주고 하지 않은 상태입니다.

296월 2020 by Truds No Comments

MySQL 에서 ON DUPLICATE KEY UPDATE 사용시 last_insert_id 값 획득

Database

MySQL에서 Insert, Update를 동시에 할 수 있게 해주는 "ON DUPLICATE KEY UPDATE" 쿼리를 사용할 경우 last_insert_id 결과값을 정상적으로 가져오지 못할 경우가 생기기 마련이다. 이 문제를 해결 하기 위해서는 해당 Table의 컬럼중 "AUTO_INCREMENT" 지정된 컬럼을 가져오면 된다. 쿼리의 예를 들면 다음과 같다. INSERT INTO test(name, age) VALUES ('이름', '19') ON DUPLICATE KEY UPDATE name='이름', age='19', expr=last_insert_id(idx); Insert가 실행되고, last_insert_id()를 획득 할 수 있습니다.

313월 2020 by Truds No Comments

C# SQLite 설치해서 사용하기

Database

메뉴 -> 도구 -> Nuget패키지 관리자 > 패키지 관리자 콘솔 "install -Package System.Data.SQLite SQLite 기능을 쓰기 위해서 추가적으로 설치해야 되는 사항입니다. 해당 사항을 설치하고 나면서 빌드할 경우에 x86, x64 폴더가 포함되며, 해당 기능도 같이 있어 다른 컴퓨터에서 사용할 수 있을 것이라고 사료 됩니다.

2611월 2019 by Truds No Comments

[MySQL] 패스워드 정책 변경

Database

unix_socket 방식을 mysql_native_password 으로 변경하는 방법입니다 SHOW VARIABLES LIKE 'validate_password%'; << 패스워드 정책 SET GLOBAL validate_password_policy=LOW; <- MEDIUM으로 변경도 가능 SELECT password('변경할PASSWORD'); UPDATE user SET plugin='mysql_native_password' WHERE user='root'; ALTER USER 'root'@'localhost' identified with mysql_native_password by '변경할PASSWORD'; ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password USING PASSWORD("변경할PASSWORD"); show grants for 'root'@'localhost'; flush privileges; 이렇게 하여도 적용이 안될때가 가끔있는데 서버 재시작을 해주시기 바랍니다. 대부분 변경이 가능하리라 봅니다.

[카테고리:] Database