program1472 > 자료실 > MySQL

[MySQL] A 테이블에 있고, B 테이블에 없는 데이터 조회 및 삭제

하나를하더라도최선을 — 2023-07-27T13:34:15+09:00

A테이블에 있고, B테이블에 없는 데이터 조회

SELECT *

FROM `collection_and_payment` A

LEFT OUTER JOIN `balhaengmul` B

ON A.uid=B.rUid

WHERE B.rUid IS NULL;

삭제하기 위해서는 임시테이블 사용

DELETE FROM `collection_and_payment` WHERE uid IN

(SELECT * FROM 

(SELECT A.uid

FROM `collection_and_payment` A

LEFT OUTER JOIN `balhaengmul` B

ON A.uid=B.rUid

WHERE B.rUid IS NULL) AS temp_table);

[SQL] MySQL 날짜 요일(DAYOFWEEK) 구하기 및 주의사항

하나를하더라도최선을 — 2023-07-25T12:14:24+09:00

특정 일자의 요일을 구하고자 할 때, 사용하는 함수가 존재한다. 모든 언어에 요일을 구하는 함수가 있으며, 표준이 있기 마련이다.

일반적인 프로그래밍 언어는 전통적으로 일요일로 시작하며 0을 리턴한다.

일	월	화	수	목	금	토
0	1	2	3	4	5	6

프로그래밍 언어와 다르게 ODBC에서는 다른 값을 리턴한다.

일	월	화	수	목	금	토
1	2	3	4	5	6	7

여기서 MySQL과 MariaDB에서 제공하는 WEEKDAY()는 리턴 값이 ODBC 표준을 따르지 않는다는 점이다.

일	월	화	수	목	금	토
0	1	2	3	4	5	6

특별히 WEEKDAY()를 써야 되는 경우가 아니라면 ODBC 표준을 따르는 DAYOFWEEK()를 사용해야 실수를 줄일 수 있다.

SELECT DATE_FORMAT(NOW(),"%Y-%m-%d"),WEEKDAY(NOW()), DAYOFWEEK(NOW());

[MySQL] SELECT 문 특정 컬럼의 MIN값이 0보다 클경우 0을 반환하고 그렇지 않으면 MIN값을 반환

하나를하더라도최선을 — 2023-07-12T10:21:39+09:00

보통 다른 언어에서는 IF문을 사용하지만 MySQL에서는 CASE문을 사용합니다.

SELECT

CASE

WHEN MIN(`발주번호`) - 1 > 0

THEN 0

ELSE MIN(`발주번호`) - 1

END AS V

FROM `order_delivery_number`

WHERE 1

위 쿼리를 실행시키면 다음과 같은 결과를 볼 수 있습니다.

[MySql] DB 백업

하나를하더라도최선을 — 2023-06-07T17:04:21+09:00

//https://atg-cas.iptime.org/db_backup.php?db_nm=alltogreen

//https://atg-cas.iptime.org/db_backup.php?db_nm=aserpacific

//https://atg-cas.iptime.org/db_backup.php?db_nm=jeju

//https://atg-cas.iptime.org/db_backup.php?db_nm=mysql

//https://atg-cas.iptime.org/db_backup.php?db_nm=information_schema

/**

* Updated: IT HUB. heesung byeon

* Website: program1472.com

*/

//MySQL server and database

$dbhost = 'localhost';

$dbuser = '로그인아이디';

$dbpass = '로그인비번';

$dbname = isset($_GET['db_nm'])?$_GET['db_nm']:'*';

$tables = isset($_GET['tb_nm'])?$_GET['tb_nm']:'*';

//Call the core function

backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables);

//Core function

function backup_tables($host, $user, $pass, $databases = '*', $tables = '*') {

    $con = mysqli_connect($host,$user,$pass);

    // Check connection

    if (mysqli_connect_errno())

{

        echo "Failed to connect to MySQL: " . mysqli_connect_error();

        exit;

}

    if($databases == '*'){

        $databases = array();

        $result = mysqli_query($con, 'SHOW DATABASES');

        while($row = mysqli_fetch_row($result))

{

            $databases[] = $row[0];

}

    } else {

        $databases = is_array($databases) ? $databases : explode(',',$databases);        

}

    foreach($databases as $database)

{

        mysqli_select_db($con, $database);

        mysqli_query($con, "SET NAMES 'utf8'");

        $table_array = array();

        //get all of the tables

        if($tables == '*')

{

            $result = mysqli_query($con, 'SHOW TABLES');

            while($row = mysqli_fetch_row($result))

{

                $table_array[] = $row[0];

}

}

        else

{

            $table_array = is_array($tables) ? $tables : explode(',',$tables);

}

        $return = '';

        //cycle through

        foreach($table_array as $table)

{

            $result = mysqli_query($con, 'SELECT * FROM '.$table);

            if ($result){

                $num_fields = mysqli_num_fields($result);

                $num_rows = mysqli_num_rows($result);

                $return.= 'DROP TABLE IF EXISTS '.$table.';';

                $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));

                $return.= "\n\n".$row2[1].";\n\n";

                $counter = 1;

                //Over tables

                for ($i = 0; $i < $num_fields; $i++) 

                {   //Over rows

                    while($row = mysqli_fetch_row($result))

{

                        if($counter == 1){

                            $return.= 'INSERT INTO '.$table.' VALUES(';

                        } else{

                            $return.= '(';

}

                        //Over fields

                        for($j=0; $j<$num_fields; $j++) 

{

                            $row[$j] = addslashes($row[$j]);

                            $row[$j] = str_replace("\n","\\n",$row[$j]);

                            if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }

                            if ($j<($num_fields-1)) { $return.= ','; }

}

                        if($num_rows == $counter){

                            $return.= ");\n";

                        } else{

                            $return.= "),\n";

}

                        ++$counter;

}

}

                $return.="\n\n\n";

}

}

        //save file

        // $fileName = 'db-backup-'.$database.'-'.time().'-'.(md5(implode(',',$tables))).'.sql';

        $fileName = 'db/backup-'.$database.'-'.time().'.sql';

        $handle = fopen($fileName,'w+');

        fwrite($handle,$return);

        if(fclose($handle)){

            echo "Done, the file name is: ".$fileName.'\r\n
';

            // exit; 

}

}

}

[MySQL] A테이블에 있고, B테이블에 없는 데이터 조회 및 삭제

하나를하더라도최선을 — 2023-02-21T11:45:17+09:00

A테이블에 있고, B테이블에 없는 데이터 조회

SELECT A.str_id

FROM chat A

LEFT OUTER JOIN meeting B

ON A.str_id=B.multi_key

WHERE B.multi_key IS NULL;

임시테이블을 이용한 A테이블에 있고, B테이블에 없는 데이터 삭제

DELETE FROM chat WHERE str_id IN

(SELECT * FROM 

(SELECT A.str_id

FROM chat A

LEFT OUTER JOIN meeting B

ON A.str_id=B.multi_key

WHERE B.multi_key IS NULL) AS temp_table);

[MySql] MYSQL에서 문자열 SPLIT 해서 UPDATE(업데이트) - SUBSTRING_INDEX

하나를하더라도최선을 — 2022-09-17T14:18:17+09:00

Example:)

UPDATE `지도검색` SET `eMail` = SUBSTRING_INDEX(`homePage`, '/', -1) WHERE `homePage` LIKE '%blog.naver.com%'; 

 응용:)
UPDATE `지도검색` SET `eMail` = CONCAT(SUBSTRING_INDEX(`homePage`, '/', -1),'@naver.com') WHERE `homePage` LIKE '%blog.naver.com%' 

MYSQL에서는 SPLIT 함수가 없어서,

SUBSTRING_INDEX 함수를 사용해서 SPLIT 기능을 구현할 수 있습니다.

SUBSTRING_INDEX(문자열, 구분자, 구분자의 index)

"하나-둘-셋" 이라는 문자열을 구분자 -에 따라 나누어 출력해보겠습니다.

구문	결과
SUBSTRING_INDEX('하나-둘-셋', '-', 1)	하나
SUBSTRING_INDEX('하나-둘-셋', '-', 2)	하나-둘
SUBSTRING_INDEX('하나-둘-셋', '-', 3)	하나-둘-셋
SUBSTRING_INDEX('하나-둘-셋', '-', -1)	셋
SUBSTRING_INDEX('하나-둘-셋', '-', -2)	둘-셋
SUBSTRING_INDEX('하나-둘-셋', '-', -3)	하나-둘-셋

SUBSTRING_INDEX 함수는 문자열의 첫부분 혹은 끝부분부터 자르는 함수이기때문에

SPLIT처럼단순하게 출력할 수 없습니다.

대신, SUBSTRING을 한번 더 씌우는 방식으로 중간 문자열을 가져올 수 있습니다.

SUBSTRING_INDEX(SUBSTRING_INDEX('하나-둘-셋' '-', -2), '-', 1)

결과:)

SUBSTRING_INDEX('하나-둘-셋' '-', -2) ----결과----> '둘-셋'

↓

SUBSTRING_INDEX('둘-셋', '-', 1) ----결과----> '둘'

출처:) h t t p s : / / d a i l y - s t r u g g l e . t i s t o r y . c o m / 4 8

[MySQL] 테이블, 컬럼 코멘트(Comment) 조회

하나를하더라도최선을 — 2021-08-29T14:57:44+09:00

출처: https://extbrain.tistory.com/97 [확장형 뇌 저장소]

▶MySQL 테이블, 컬럼 코멘트(Comment) 조회

▶설명

MySQL에서 테이블과 컬럼에 작성되어 있는 코멘트를 조회할 수 있는 방법을 기록했습니다.

▶테이블 코멘트(Comment) 조회

DB 전체 테이블 코멘트 조회

SELECT 


    table_name, table_comment


FROM


    information_schema.tables


WHERE


    table_schema = 'DB 이름' AND table_name = '테이블 이름';

DB 특정 테이블 코멘트 조회

SELECT 


    table_name, table_comment


FROM


    information_schema.tables


WHERE


    table_schema = 'DB 이름' AND table_name = '테이블 이름';

▶컬럼 코멘트(Comment) 조회

DB 전체 테이블 컬럼 코멘트 조회

SELECT


    table_name, column_name, column_comment


FROM


    information_schema.columns


WHERE


    table_schema = 'DB 이름';

DB 특정 테이블 컬럼 코멘트 조회

SELECT


    table_name, column_name, column_comment


FROM


    information_schema.columns


WHERE


    table_schema = 'DB 이름' AND table_name = '테이블 이름';

MySQL 공백, 탭, 라인피드, 캐리지리턴 등을 제거하는 방법

하나를하더라도최선을 — 2021-02-24T20:42:06+09:00

MySQL 공백, 탭, 라인피드, 캐리지리턴 등을 제거하는 방법

필드값 변경 함수

replace(필드명, 변경할 문자, 변경될 문자)

- '필드명'에 해당하는 레코드에서 '변경할 문자'를 모두 '변경될 문자'로 변경한다.

1. 공백제거(앞/뒤의 공백을 제거한다)

- update table set field = replace(field, ' ', '');

* table은 테이블명, field는 필드명이다.

2. 개행문자 제거

- update table set field = replace(field, '\r\n', '');

3. 탭( Tab - char(9) ) 제거

- update table set field = replace(field, char(9), '');

- field 값에 탭(char(9))이 있다면, 없애서('') 레코드 값을 수정하라.

4. 라인피드(Line Feed) 제거

- update table set field = replace(field, char(10), '');

- field 값에 라인피드(char(10))이 있다면, 없애서('') 레코드 값을 수정하라.

5. 캐리지리턴(Carriage Return) 제거

- update table set field = replace(field, char(13), '');

- field 값에 캐리지리턴(char(13))이 있다면, 없애서('') 레코드 값을 수정하라.

이쯤 되면 응용도 가능하다.

6. 하이픈( - ) 제거

- update table set field = replace(field, '-', '');

출처: httpsː//curryyou.tistory.com/68 [카레유]

[MySQL] 내용 추가 / 값 추가

하나를하더라도최선을 — 2021-02-23T11:02:24+09:00

UPDATE `job_order` SET `InvoiceNumber` = concat(`InvoiceNumber`, '추가할값') WHERE `uid` = 1

mysql DB에서 쿼리에 영향을 줄 수 있는 문자열이나 특수문자 등을 확인하고 처리

하나를하더라도최선을 — 2021-02-22T13:40:16+09:00

mysql DB에서 쿼리에 영향을 줄 수 있는 문자열이나 특수문자 등을 확인하고 처리해야합니다.

이 방법으로 아래의 함수를 이용해 확인 가능합니다.

mysqli_real_escape_string($dbc, 처리할 문자열);

[MySQL] AUTO_INCREMENT 값을 초기화 후, 테이블 안의 모든 데이터의 ID값을 재조정

하나를하더라도최선을 — 2020-07-15T20:00:01+09:00

AUTO_INCREMENT 값을 초기화 하려면, 아래와 같은 쿼리를 실행시켜주면 됩니다.

ALTER TABLE [테이블명] AUTO_INCREMENT=1;

하지만 이 경우에는, 테이블에 새로 시작할 값보다 높은 값이 있으면 안됩니다.

아래는 AUTO_INCREMENT 값을 초기화 후, 테이블 안의 모든 데이터의 ID값을 재조정하는 방법입니다.

 
ALTER TABLE [테이블명] AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE [테이블명] SET [AUTO_INCREMENT 열 이름] = @COUNT:=@COUNT+1;
 
위 쿼리문은 현재 모든 데이터 ID값을 1부터 재조정하고, 다음 인덱스 값을 [마지막 행번호 + 1] 로 정하는 명령어입니다.

[MySQL] 1GB .sql 파일을 phpmyadmin으로 가져 오는 방법

하나를하더라도최선을 — 2020-05-16T16:20:37+09:00

phpmyadmin을 통해 1GB 파일을 가져올 수 있다고 생각하지만 php.ini에서 다음 값을 증가 시켜서 다시 시작하십시오.

post_max_size=1280M


upload_max_filesize=1280M


max_execution_time = 300 //increase time as per your server requirement.

명령 프롬프트에서 아래 명령을 시도해도 MySQL 설치에 따라 경로가 다를 수 있습니다.

C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe -u root -p db_name < C:\some_path\your_sql_file.sql

오류 를 피하기 위해 max_allowed_packetmysql을 늘리는 것보다 db가 훨씬 큰 경우 다음과 같이하십시오.my.iniMySQL server gone away

max_allowed_packet = 100M

max_execution_time = 200000

max_input_time = 200000

memory_limit = 1000M

upload_max_filesize = 750M

post_max_size = 750M

[MySQL] 여러개의 컬럼을 그룹으로 묶어서 검색

하나를하더라도최선을 — 2020-03-23T12:19:03+09:00

SELECT max(uid), CONCAT (`od_no`,`sku_id`,`bl_no`) AS Group1, count(CONCAT (`od_no`,`sku_id`,`bl_no`))

FROM `cp_bill` GROUP BY Group1 having count(CONCAT (`od_no`,`sku_id`,`bl_no`))>1

[MySQL] 기존 테이블에 고유 키를 추가하는 방법

하나를하더라도최선을 — 2020-03-23T11:42:18+09:00

아래 사이트에 다양한 답변들이 올라와 있습니다.

https://stackoverflow.com/questions/15255304/how-add-unique-key-to-existing-table-with-non-uniques-rows

ALTER TABLE TABLE_NAME ADD Id INT AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE TABLE_NAME ADD CONSTRAINT constr_ID UNIQUE (user_id, game_id, date, time);

ALTER TABLE Table_Name ADD UNIQUE (column_name);

ALTER TABLE  0_value_addition_setup ADD UNIQUE (`value_code`);

ALTER TABLE `database_name`.`table_name` 

ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,

ADD PRIMARY KEY (`id`);

ALTER TABLE table_name

ADD CONSTRAINT UC_table_name UNIQUE (field1,field2);

ALTER TABLE MyTable ADD MyId INT AUTO_INCREMENT PRIMARY KEY;

제가 찿던 내용이 딱 눈에 띄네요.

ALTER TABLE TABLE_NAME ADD CONSTRAINT KEY_NAME UNIQUE (COL_NAME1, COL_NAME2, COL_NAME3)

[MySQL] 데이터(자료)형 및 크기

하나를하더라도최선을 — 2020-03-03T18:52:56+09:00

출처 ::: https://blog.lael.be/post/115

자료형이 왜 필요하나?

먼저 자료형이 왜 필요하는지 부터 생각해보자.

컴퓨터 프로그램은 프로그래머가 의도한 대로 동작한다.
이 때 같은 목적의 프로그램이라고 할지라도 효율적으로 동작하는 것이 더 좋은 프로그램이 된다.

“컴퓨터 알고리즘(Computer Algorithm)” 학문에서는 이를 평가(Performance Analysis)하기 위해서,
“시간복잡도(time complexity)“와 “공간복잡도(space complexity)“라는 개념을 사용한다.

즉, “더 작은 공간을 사용하면서” , “더 빠르게 처리할수 있는” 프로그램을 만들기 위해서 데이터 자료형을 사용하는 것이다.

작은 공간에 넣을수록 연산(SELECT 등의 연산)이 빨라지고 공간도 적게 차지한다.

다음의 자료형 목록을 보면서 당신의 프로그램의 목적을 충분히 이룰 수 있는 작은 자료형을 찾아보도록 하자.

자료형에 관해 논의한 이 https://blog.lael.be/post/917 글도 한번 읽어보도록 하자.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

- 자료형의 크기 : 1바이트(1Byte, 2^8 = 8bit)
- 범위 : -128 에서 127 (UNSIGNED 일 경우 0 에서 255)

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

- 자료형의 크기 : 2바이트(2Byte, 2^16 = 16bit)
- 범위 : -32768 에서 32767 (UNSIGNED 일 경우 0 에서 65535)

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

- 자료형의 크기 : 3바이트(3Byte, 2^24 = 24bit)
- 범위 : -8388608 에서 8388607 (UNSIGNED 일 경우 0 에서 16777215)

INT[(M)] [UNSIGNED] [ZEROFILL]

- 자료형의 크기 : 4바이트(4Byte, 2^32 = 32bit)
- 범위 : -2147483648 에서 2147483647 (UNSIGNED 일 경우 0 에서 4294967295)
- 주의 : 모든 연산은 “signed BIGINT” 로 이루어진다. 따라서 더하기 곱하기 등으로 “signed BIGINT” 의 범위를 벗어난다면 값이 틀려질 것이다.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

- 자료형의 크기 : 8바이트(8Byte, 2^64 = 64bit)
- 범위 : -9223372036854775808 에서 9223372036854775807 (UNSIGNED 일 경우 0 에 18446744073709551615)

FLOAT[(M,D)] [ZEROFILL]

- 범위 : -3.402823466E+38 에서 -1.175494351E-38, 0 그리고 1.175494351E-38 에서 3.402823466E+38
- 주의 : unsigned 를 설정할수 없다.

DOUBLE[(M,D)] [ZEROFILL]

- 범위 : -1.7976931348623157E+308 에서 -2.2250738585072014E-308, 0 그리고 2.2250738585072014E-308 에서 1.7976931348623157E+308
- 주의 : unsigned 를 설정할수 없다.

REAL[(M,D)] [ZEROFILL]

- 범위 : DOUBLE 과 동일

DATE

- 범위 : ‘1000-01-01’ 에서 ‘9999-12-31’
- 주의 : unsigned 를 설정할수 없다.

DATETIME

- 범위 : ‘1000-01-01 00:00:00’ 에서 ‘9999-12-31 23:59:59’

TIMESTAMP[(M)]

- 범위 : ‘1970-01-01 00:00:00’ 에서 2037

CHAR(M) [BINARY]

- 자료형의 크기 : char 필드의 CHARACTER SET 에 따라 가변적임. 예를들어 char(10) 이면서 utf4mb8 일 경우 고정 40Byte 사용.
- 범위 : 1 에서 255 글자
- 주의 : 고정길이로 길이가 작은 값이 입력되면 오른쪽에 스페이스로 채워져서 입력된다. BINARY 키워드가 주어지지 않을 경우 대소문자 구분없이 검색한다.

VARCHAR(M) [BINARY]

- 자료형의 크기 : varchar 필드의 CHARACTER SET 에 따라 가변적임. 예를들어 varchar(10) 이면서 utf4mb8 일 경우 최대(크기인덱스 1Byte + 데이터 40Byte 사용).
- 범위 : 1 에서 255 글자 (현재는 1에서 65535 글자 까지. 그러나 varchar(255) 이상을 사용하고 싶은 경우 text 자료형을 사용하여라)
- 주의 : BINARY 키워드가 주어지지 않을 경우 대소문자 구분없이 검색한다.
- TIP1 : 모든 데이터 값의 길이가 고정일 경우 char 가 varchar 보다 저장공간도 적게 차지하고 속도도 아주 약간 더 빠르다.
- TIP2 : 대부분의 상황에서 varchar 와 char 는 IN/OUT 이 동일하게 처리된다. 아무거나 써도 되지만 varchar 를 선택하기를 권장한다.

TINYTEXT

- 범위 : 최대 255 byte

TEXT

- 자료형의 크기 : 최대 65535 Byte (= 대략 64 KB)
- 범위 : 최대 65535 byte. utf8 기준(3바이트 문자)으로 21844 글자 저장가능.
- 허용크기 계산법 : 65536 = 2^16 = 2Byte 이다. 모든 text 값에는 반드시 이 크기지정 바이트가 붙는다. 따라서 한글의 경우 (65535-2)/3 글자 만큼 저장할 수 있다.

MEDIUMTEXT

- 범위 : 최대 16777215 byte (= 대략 16MB)

LONGTEXT

- 범위 : 최대 4294967295 byte (= 대략 4GB)

ENUM(‘value1′,’value2’,…)

- 범위 : ‘value1’, ‘value2’, …, or NULL 만이 입력가능하다. 최대 65535 개의 다른 값이 들어갈수 있다.

SET(‘value1′,’value2’,…)

- 범위 : ‘value1’, ‘value2’, …, or NULL 만이 입력가능하다. 최대 64 개의 다른 값이 들어갈수 있다.

참조 : https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

참조 : https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html