전산직의 삶

[MariaDB] 마리아DB SQL 쿼리 고급 - 데이터 형식 (MySQL)

숫자 데이터 형식

문자 데이터 형식

날짜와 시간 데이터 형식

예시)

SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE' ;
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME' ;
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME' ; 

/* 결과값 */
DATE		TIME		DATETIME
2022-10-19	12:3529		2022-10-19 12:35:29

기타 데이터 형식

[MariaDB] 전체보기

[MariaDB] 마리아DB SQL 쿼리 기본 - INSERT, UPDATE, DELETE (MySQL)

INSERT 기본형식

USE DB명;
CREATE TABLE 테이블명 (열이름1 INT, 열이름2 CHAR(3), 열이름3 INT);
INSERT INTO 테이블명 VALUES (1,'김이박', 30); -- 위 테이블에 생성된 열이름 순서에 맞게 INSERT 됨

INSERT INTO 테이블명(열이름1, 열이름2) VALUES (2, '박서왕'); -- 열이름3에는 NULL값이 입력됨

INSERT INTO 테이블명(열이름3, 열이름1, 열이름2) VALUES (40, 3, '이전심'); -- 열이름 3,1,2 순서로 입력됨

AUTO_INCREMENT(순서열 같은 경우 자동으로 1부터 증가된 값을 입력해주는 기능)

USE DB명;
CREATE TABLE 테이블명
  (순서열이름  int AUTO_INCREMENT PRIMARY KEY,  -- 순서열이름 열은 자동증가, 기본키
   열이름2 char(3), 
   열이름3 int );
INSERT INTO 테이블명 VALUES (NULL, '곽민아', 35); -- NULL 값이 아닌 1이 입력됨
INSERT INTO 테이블명 VALUES (NULL, '서나리', 32); -- 2가 입력됨
INSERT INTO 테이블명 VALUES (NULL, '유은성', 31); -- 3이 입력됨

다른 테이블의 조회값을 INSERT 하기

USE DB명;
CREATE TABLE 테이블명 (열이름1 int, 열이름2 varchar(50), 열이름3 varchar(50));
INSERT INTO 테이블명
  SELECT 조회열1, 조회열2, 조회열3  -- 인서트할 테이블의 열과 같은 수, 같은 데이터형식을 SELECT 해야함
    FROM 조회DB.조회테이블 ;

UPDATE (데이터 수정)

USE DB명;
UPDATE 테이블명 SET 열이름 = 바꿀값 WHERE 조건문;

// 예시
UPDATE 테이블명 SET 가격열 = 가격열 * 10; -- 가격열의 값을 모두 10을 곱하여 변경

조건부 데이터 입력(INSERT), 변경(UPDATE)

INSERT INTO 테이블명 VALUES('값1' , '값2', '값3')  -- 값1,2,3을 테이블에 입력합니다.
	ON DUPLICATE KEY UPDATE 열이름2='값2', 열이름3='값3';  -- 만약 기본키가 중복되면 열이름2를 값2로, 열이름3을 값3로 업데이트합니다.

DELETE FROM (행단위 삭제)

USE DB명;
DELETE FROM 테이블명 WHERE 조건문;

//예시
DELETE FROM 테이블명 WHERE 이름열 = '김이박'; -- 이름열 값이 김이박인 행들을 모두 지움

DELETE, DROP, TRUNCATE 차이

DELETE FROM 테이블명;	-- DML문. 트랜젝션 로그를 기록하여 느림. 테이블 모든 행 삭제.
DROP TABLE 테이블명;	-- DDL문. 트랜젝션 발생하지 않아 빠름. 테이블 자체를 삭제.
TRUNCATE TABLE 테이블명;	-- DDL문. 트렌젝션을 발생하지 않아 빠름. 테이블의 모든 행 삭제

[MariaDB] 전체보기

[MariaDB] 마리아DB SQL 쿼리 기본 - SELECT (MySQL)

DB목록 조회

SHOW DATABASES;

DB선택

USE DB명;

선택된 DB에 존재하는 테이블 정보 조회

SHOW TABLE STATUS;  -- 테이블 이름만 간단히 볼 때는 SHOW TABLES;

테이블 조회(SELECT)

SELECT * FROM 테이블명;

DB선택과 함께 테이블 조회

SELECT * FROM DB명.테이블명;

테이블에서 필요한 열만 조회

SELECT 열이름1, 열이름2, 열이름3 FROM 테이블명;

별칭 사용법 (AS)

SELECT 열이름1 AS 별칭1, 열이름2 AS 별칭2 FROM 테이블명; -- 열이름이 별칭으로 적혀서 테이블 조회됨

조건절 (WHERE)

SELECT * FROM 테이블명 WHERE 조건문; -- 테이블명에 있는 데이터중 조건문에 해당되는 행의 모든(*) 열 조회

조건절 AND, OR

SELECT * FORM 테이블명 WHERE 조건1 AND 조건2; -- 조건1, 2 모두 참인 행들만 조회

SELECT * FROM 테이블명 WHERE 조건1 OR 조건2; -- 조건1이거나 2를 만족하는 행들을 조회

조건절 BETWEEN...AND

/* 열이름이 시작값부터 끝값까지의 포함된 행들을 조회(값은 보통 수치) */
SELECT * FROM 테이블명 WHERE 열이름 BETWEEN 시작값 AND 끝값;

조건절 IN('값1', '값2', ...)

/* 열이름이 IN 뒤에 값에 포함되는 행들만 조회 */
SELECT * FROM 테이블명 WHERE 열이름 IN ('값1', '값2', '값3');

조건절 LIKE '%'

/* 열이름이 LIKE 뒤에 문자와 %(모든문자)를 합쳐서 해당되면 조회 */
SELECT * FROM 테이블명 WHERE 열이름 LIKE '김%' -- 열이름이 '김'으로 시작하는 모든 행 조회

순서정렬 (ORDER BY)

SELECT * FROM 테이블명 ORDER BY 열이름; -- 기본값이 ASC이며 열이름 기준 오름차순 정렬
SELECT * FROM 테이블명 ORDER BY 열이름 DESC; -- 열이름 기준 내림차순 정렬

중복제거 조회 (DISTINCT)

SELECT 지역명 FROM 동창회테이블;  -- 동창회테이블에 중복되는 지역명이 테이블 행만큼 조회됨
SELECT DISTICT 지역명 FROM 동창회테이블  -- 동창회테이블에 지역명중 중복되는 값은 1개만 조회됨

조회(출력) 갯수 제한 (LIMIT)

SELECT * FROM 테이블명 LIMIT 5; -- 5개 행까지만 조회

테이블 새이름으로 복사하기

/* DB명 안에 있는 기존테이블을 새테이블로 복사 */
USE DB명;
CREATE TABLE 새테이블이름 (SELECT 복사할열1, 복사할열2 FROM 기존테이블);

GROUP BY절

/* 그룹열을 중복제거 조회하고, 그룹열에 해당되는 합칠열 값을 모두 더해서(SUM) 출력 */
SELECT 그룹열, SUM(합칠열) FROM 테이블명 GROUP BY 그룹열;


/* 그룹열을 중복제거 조회하고, 그룹열에 해당되는 수량열과 가격열을 곱한(*) 값들을 모두 더해서(SUM) 출력 */
SELECT 그룹열, SUM(수량열*가격열) FROM 테이블명 GROUP BY 그룹열;

GROUP BY절과 함께 사용되는 집계함수

HAVING 절(GROUP BY의 조건절)

/* 그룹열을 중복제거 조회하고, 그룹열에 해당되는 합칠열 값을 모두 더한값이(SUM) 1000보다 큰 행들을 출력 */
SELECT 그룹열, SUM(합칠열) 
FROM 테이블명 
GROUP BY 그룹열
HAVING SUM(합칠열) > 1000;


/* 그룹열을 중복제거 조회하고, 그룹열에 해당되는 수량열과 가격열을 곱한(*) 값들을 모두 더한값이(SUM) 1000보다 큰 행들을 출력 */
SELECT 그룹열, SUM(수량열*가격열) 
FROM 테이블명 
GROUP BY 그룹열
HAVING SUM(수량열*가격열) > 1000;

[MariaDB] 전체보기

[MariaDB] HeidiSQL 사용방법 매뉴얼 3편 - GUI방식으로 사용자 계정 생성 및 권한부여하기

이전편에서는 HeidiSQL 포터블 설치 및 DB접속방법, GUI 방식으로 DB 및 TABLE 생성과 데이터 입력 방법, 그리고 기타 유용한 기능에 대해서 설명드렸습니다.

이번편에는 HeidiSQL의 GUI방식으로 사용자 계정을 생성하고 계정에 원하는 권한만 부여하는 방법에 대해서 설명드리겠습니다.

먼저 admin에 대한 권한을 설명드리겠습니다.

1. 계정명은 admin이다. PW는 adminpw이다.

2. 모든 데이터베이스의 모든 테이블에 권한을 준다. (*.*)

3. 권한은 모든 권한을 부여한다. (ALL)

4. admin은 모든PC(외부PC 포함)에서 접속 할 수 있다. (%)

위에서 정의한 admin 계정을 생성하고 권한을 부여하는 GUI 방법을 설명드리겠습니다.먼저 메뉴 - 도구 - 사용자 관리자를 선택합니다.

사용자 관리자 창에서 "추가"버튼을 클릭합니다.

1. 사용자 이름에 admin 입력합니다.

2. 호스트에서 란에 % (모든 곳에서 접속가능) 선택합니다.

3. 암호란에 adminpw 입력합니다. 재입력란에도 똑같이 입력합니다.

4. 접근 허용목록에는 전체권한을 선택해줍니다.

5. 저장을 클릭하면 계정 목록에 admin % 가 추가된 것을 확인 할 수 있습니다.

위 GUI 내용을 SQL문으로 변경하자면 아래 코드와 같습니다.

CREATE USER admin@'%' IDENTIFIED BY 'adminpw';  -- 계정생성 admin, % 모든곳에서 접근, pw는 adminpw
GRANT ALL ON *.* TO admin@'%' WITH GRANT OPTION; -- 계정권한부여 ALL, *.* 모든 DB.테이블, admin계정은 모든PC에서 접근가능, GRANT 권한까지 부여 

외부PC에서 HeidiSQL로 서버로 접속한 경우 저장 시 SQL (1045)오류가 날 수 있습니다.

이는 외부에서 서버DB에 접속하기 위해서 root@'%'에 GRANT 권한을 줬을때 마지막에 "WITH GRANT OPTION;"를 안 넣어서 생기는 문제입니다.

서버에서(localhost) MriaDB에 접속 후 아래 GRANT 쿼리와 FLUSH 쿼리를 입력합니다.

( # GRANT ALL PRIVILEGES ON *.* TO root@'%' identified by 'root의비밀번호' WITH GRANT OPTION; )

( # FLUSH PRIVILEGES; )

다음은 selecter 권한에 대해서 설명드리겠습니다.

1. 계정명은 selecter이다. PW는 selecterpw이다.

2. 모든 데이터베이스의 모든 테이블에 권한을 준다. (*.*)

3. 권한은 select 권한을 부여한다. (SELECT)

4. selecter는 임의 IP에서만 접속 할 수 있다. (예: 192.168.0.100)

사용자 관리자 창에서 "추가"버튼을 클릭합니다.

1. 사용자 이름에 selecter 입력합니다.

2. 호스트에서 란에 192.168.0.100 입력합니다.

3. 암호란에 selecterpw 입력합니다. 재입력란에도 똑같이 입력합니다.

4. 접근 허용목록에는 select을 선택해줍니다.

5. 저장을 클릭하면 계정 목록에 selecter / 192.168.0.100 이 추가된 것을 확인 할 수 있습니다.

위 GUI 내용을 SQL문으로 변경하자면 아래 코드와 같습니다.

CREATE USER selecter@'192.168.0.100' IDENTIFIED BY 'selecterpw';  -- 계정생성 selecter, 192.168.0.100에서만 접근, pw는 selecterpw
GRANT SELECT ON *.* TO selecter@'192.168.0.100'; -- 계정권한부여 SELECT, *.* 모든 DB.테이블, selecter계정은 192.168.0.100에서 접근가능

다음은 worker 권한에 대해서 설명드리겠습니다.

1. 계정명은 worker이다. PW는 workerpw이다.

2. "testDB" 데이터베이스의  "testTABLE" 테이블에 권한을 준다.

3. 권한은 INSERT, UPDATE 권한을 부여한다.

4. worker는 임의 IP에서만 접속 할 수 있다. (예: 192.168.0.200)

사용자 관리자 창에서 "추가"버튼을 클릭합니다.

1. 사용자 이름에 worker 입력합니다.

2. 호스트에서 란에 192.168.0.200 입력합니다.

3. 암호란에 workerpw 입력합니다. 재입력란에도 똑같이 입력합니다.

4. 객체 추가를 클릭 후 testDB의 testTABLE을 선택합니다.

5. 접근 허용목록에는 INSERT, UPDATE를 선택해 줍니다.

6. 저장을 클릭하면 계정 목록에 worker / 192.168.0.200 이 추가된 것을 확인 할 수 있습니다.

위 GUI 내용을 SQL문으로 변경하자면 아래 코드와 같습니다.

CREATE USER worker@'192.168.0.200' IDENTIFIED BY 'workerpw';  -- 계정생성 worker, 192.168.0.200에서만 접근, pw는 workerpw
GRANT INSERT, UPDATE ON testDB.testTABLE TO selecter@'192.168.0.200'; -- 계정권한부여 INSERT UPDATE, testDB.testTABLE에만 접근, worker계정은 192.168.0.200에서만 접근가능

이상 HeidiSQL에 관한 포스팅을 마치겠습니다.

[MariaDB] 전체보기

[MariaDB] HeidiSQL 사용방법 매뉴얼 2편 - GUI방식으로 DB, Table 생성 및 데이터 입력방법과 기타 유용한 기능

이전 포스팅에서는 HeidiSQL 포터블버전을 다운받는 방법과 세션관리자에서 DB를 접속하는 방법에 대해 설명했습니다.

이번편에서는 접속한 DB에서 새로운 DB, Table을 GUI방식으로 생성하는 방법과 데이터 입력방법을 설명드리겠습니다.

또한, HeidiSQL의 기타 유용한 기능에 대해서도 설명드리겠습니다.

DB목록 세션이름에서 마우스 우클릭 후 - 새로 생성(O) - 데이터베이스(T)를 선택합니다.

데이터베이스 생성 창이 열리면 이름란에 원하는 DB명을 적어주시고 확인을 클릭합니다.

하단에 CREATE코드에는 SQL 쿼리로 DB를 생성하는 코드도 나옵니다. 

다음은 생성된 "testDB"에서 마우스 우클릭 - 새로 생성(O) - 테이블(U)를 선택합니다.

테이블 생성창이 열리면 테이블 이름, 테이블 설명(코멘트)를 적습니다.

하단에 "추가"버튼을 클릭후 이름, 데이터유형, 길이/설정 값을 입력하고 NULL 값을 허용할거라면 체크박스를 체크해줍니다.

"저장"을 클릭하면 테이블이 생성이됩니다.

열을 선택 후 - 마우스 우측 클릭 - 새 인덱스 생성(Y) - 기본키로 설정할 수 있습니다.

테이블 선택 후 테이블 탭 중 "CREATE 코드"를 선택 하면 SQL 쿼리로 테이블을 만드는 코드도 나옵니다.

만들어진 테이블에 데이터를 입력하는 방법입니다.

테이블 클릭 후 - 데이터 탭 선택 - 데이터 창 빈공간에서 마우스 우클릭 후 - 행 삽입(I) 선택 합니다.

마우스로 행을 클릭 후 값을 입력 합니다.

쿼리 창에서 DB를 선택 후, 테이블을 조회해 봅니다. 데이터들이 정상적으로 조회되는 것을 확인 할 수 있습니다.

조회된 테이블 창에서 마우스 우클릭 후 "격자 행 내보내기"를 클릭하시면 파일로 다운받을 수 있습니다.

HeidiSQL의 유지보수 기능에는 DB 테이블에서 특정 문자를 찾는 기능이 있습니다.

먼저 메뉴 - 도구 - 유지보수를 선택 합니다.

찾을 DB를 선택 후 - 텍스트 찾기 탭을 선택 - 찾을 텍스트 입력 후 - 찾기를 클릭합니다.

잠시 후 찾기가 끝나면 결과보기를 클릭합니다.

쿼리창에 해당 텍스트를 찾는 쿼리가 입력되어서 실행된 상태가 보여집니다.

한줄로 길게 쓰여진 SQL 쿼리를 보다 보기 쉽게 정렬하는 기능이 있습니다.

SQL 입력창에서 마우스 우클릭 후 - SQL재구성(S)를 클릭합니다.

예약어들이 행 바꿈으로 재구성됩니다. (예약어가 소문자일 경우 대문자로 변경해줍니다.)

SQL작성 시 자동완성 기능도 재공하고 있습니다.

사용방법은 쿼리를 일부 입력 후 컨트롤(Ctrl) + 스페이스(Space)를 누르면 자동완성 화면이 뜹니다. 원하는 자동완성을 방향키로 선택후 엔터를 누릅니다.

주석기능도 재공하고 있습니다.

마우스로 드래그 선택 후 - 마우스 우클릭 - 주석 설정/해제(N)을 클릭합니다.

간단한 필터 기능도 제공해주고 있어 select문을 대신하여 조회가 가능합니다.

테이블 선택 - 데이터탭 선택 - 필더 선택 - 필터내용 입력 - 필터 적용 클릭 합니다.

다음편에서는 GUI로 사용자 추가(CREATE USER)와 사용자 권한(GRANT)을 주는 방법에 대해 설명하겠습니다. SQL쿼리로 하는 방법 또한 함께 설명드리겠습니다.

[MariaDB] 전체보기