mysql 기본-심화 명렁어 정리 모음(feat. MySQL Workbench)
2021. 10. 22. 21:21ㆍDevelop/SQL
안녕하세요 Mysql 공부를 통해 MySQL Workbench 명령어를 메모하겠습니다.
MYSQL 데이터 공부하면서 기록해 놓은 명령어를 블로그를 통해 2차로 기록하겠습니다.
순서는 아래와 같이 기재할 예정이니 원하시는 카테고리를 ctrl + f 로 검색하셔서 빠르게 찾으시길
명령어는 워크벤치 기준으로 작성되어서 줄 바꿈이 되어있는 것이니 참고하시길.
해당 내용은 네이버 부트캠프 강의를 통해 공부 후 해당 내용을 메모한 것입니다.
카테고리
- 데이터 조작어
- 데이터 조회
- 데이터 제어어
- 데이터 정의어
- 테이블 결합(JOIN)
- 계속 추가 예정(...)
데이터 조작어
/***************테이블 생성(Create)***************/
/* 회원테이블 생성 */
CREATE TABLE 회원테이블 (
회원번호 INT PRIMARY KEY,
이름 VARCHAR(20),
가입일자 DATE NOT NULL,
수신동의 BIT
);
/***************데이터 삽입*******************/
INSERT INTO 회원테이블 VALUES (1001, '홍길동', '2020-01-02', 1);
INSERT INTO 회원테이블 VALUES (1002, '이순신', '2020-01-03', 0);
INSERT INTO 회원테이블 VALUES (1003, '장영실', '2020-01-04', 1);
INSERT INTO 회원테이블 VALUES (1004, '유관순', '2020-01-05', 0);
/* 회원테이블 조회 */
SELECT * FROM 회원테이블;
/***************조건 위반*******************/
/* PRIMARY KEY 제약 조건 위반 */
INSERT INTO 회원테이블 VALUES (1004, '장보고', '2020-01-06', 0);
/* NOT NULL 제약 조건 위반 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', NULL, 0);
/* 데이터 타입 조건 위반 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', 1, 0);
/***************데이터 조회***************/
/* 모든 열 조회 */
SELECT * FROM 회원테이블;
/* 특정 열 조회 */
SELECT 회원번호,
이름
FROM 회원테이블;
/* 특정 열 이름 변경하여 조회 */
SELECT 회원번호,
이름 AS 성명
FROM 회원테이블;
/***************데이터 수정*******************/
/* 모든 데이터 수정 */
UPDATE 회원테이블
SET 수신동의 = 0;
/* 회원테이블 조회 */
SELECT *
FROM 회원테이블;
/* 특정 조건 데이터 수정 */
UPDATE 회원테이블
SET 수신동의 = 1
WHERE 이름 = '홍길동';
/* 회원테이블 조회 */
SELECT *
FROM 회원테이블;
/***************데이터 삭제*******************/
/* 특정 데이터 삭제 */
DELETE
FROM 회원테이블
WHERE 이름 = '홍길동';
/* 회원테이블 조회 */
SELECT *
FROM 회원테이블;
/* 모든 데이터 삭제 */
DELETE
FROM 회원테이블;
/* 회원테이블 조회 */
SELECT *
FROM 회원테이블;데이터 조회
/***************FROM***************/
/* Customer 테이블 모든 열 조회 */
SELECT *
FROM CUSTOMER;
/***************WHERE***************/
/* 성별이 남성 조건으로 필터링 */
SELECT *
FROM CUSTOMER
WHERE GENDER = 'MAN';
/***************GROUP BY***************/
/* 지역별로 회원수 집계 */
SELECT ADDR
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE GENDER = 'MAN'
GROUP
BY ADDR;
/* COUNT: 행들의 개수를 구하는 집계함수 */
/***************HAVING***************/
/* 집계 회원수 100명 미만 조건으로 필터링 */
SELECT ADDR
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE GENDER = 'MAN'
GROUP
BY ADDR
HAVING COUNT(MEM_NO) < 100;
/* < : 비교 연산자 / ~ 보다 작은*/
/***************ORDER BY***************/
/* 집계 회원수가 높은 순으로 */
SELECT ADDR
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE GENDER = 'MAN'
GROUP
BY ADDR
HAVING COUNT(MEM_NO) < 100
ORDER
BY COUNT(MEM_NO) DESC;
/* DESC : 내림차순 / ASC : 오름차순 */
/***************FROM -> (WHERE) -> GROUP BY***************/
/* FROM -> GROUP BY 순으로 작성해도 됩니다. */
SELECT ADDR
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
/* WHERE GENDER = 'MAN' */
GROUP
BY ADDR;
/***************GROUP BY + 집계함수***************/
/* 거주지역을 서울, 인천 조건으로 필터링 */
/* 거주지역 및 성별로 회원수 집계 */
SELECT ADDR
,GENDER
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE ADDR IN ('SEOUL', 'INCHEON')
GROUP
BY ADDR
,GENDER;
/* IN : 특수 연산자 / IN (List) / 리스트 값만 */
/* GROUP BY에 있는 열들을 SELECT에도 작성해야 원하는 분석 결과를 확인할 수 있습니다. */
SELECT GENDER
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE ADDR IN ('SEOUL', 'INCHEON')
GROUP
BY ADDR
,GENDER;
/***************SQL 명령어 작성법***************/
/* 회원테이블(Customer)을 */
/* 성별이 남성 조건으로 필터링하여 */
/* 거주지역별로 회원수 집계 */
/* 집계 회원수 100명 미만 조건으로 필터링 */
/* 모든 열 조회 */
/* 집계 회원수가 높은 순으로 */
SELECT ADDR
,COUNT(MEM_NO) AS 회원수
FROM CUSTOMER
WHERE GENDER = 'MAN'
GROUP
BY ADDR
HAVING COUNT(MEM_NO) < 100
ORDER
BY COUNT(MEM_NO) DESC;
데이터 제어어
/***************사용자 확인***************/
/* MYSQL 데이터베이스 사용 */
USE MYSQL;
/* 사용자 확인 */
SELECT *
FROM USER;
/***************사용자 추가***************/
/* 사용자 아이디 및 비밀번호 생성 */
CREATE USER 'TEST'@LOCALHOST IDENTIFIED BY 'TEST';
/* 사용자 확인 */
SELECT *
FROM USER;
/* 사용자 비밀번호 변경 */
SET PASSWORD FOR 'TEST'@LOCALHOST = '1234';
/***************권한 부여 및 제거***************/
/** 권한: CREATE, ALTER, DROP, INSERT, DELETE, UPDATE, SELECT 등 **/
/* 특정 권한 부여 */
GRANT SELECT, DELETE ON PRACTICE.회원테이블 TO 'TEST'@LOCALHOST;
/* 특정 권한 제거 */
REVOKE DELETE ON PRACTICE.회원테이블 FROM 'TEST'@LOCALHOST;
/* 모든 권한 부여 */
GRANT ALL ON Practice.회원테이블 TO 'TEST'@LOCALHOST;
/* 모든 권한 제거 */
REVOKE ALL ON Practice.회원테이블 FROM 'TEST'@LOCALHOST;
/***************사용자 삭제***************/
/* 사용자 삭제 */
DROP USER 'TEST'@LOCALHOST;
/* 사용자 확인 */
SELECT *
FROM USER;
데이터 정의어
/* 기본키(PRIMARY KEY): 중복되어 나타날 수 없는 단일 값 + NOT NULL */
/* NOT NULL: NULL 허용하지 않음 */
/* 회원테이블 조회 */
SELECT * FROM 회원테이블;
/***************테이블 열 추가*******************/
/* 성별 열 추가 */
ALTER TABLE 회원테이블 ADD 성별 VARCHAR(2);
/* 회원테이블 조회 */
SELECT * FROM 회원테이블;
/***************테이블 열 데이터 타입 변경***************/
/* 성별 열 타입 변경 */
ALTER TABLE 회원테이블 MODIFY 성별 VARCHAR(20);
/***************테이블 열 이름 변경**************/
/* 성별 -> 성 열 이름 변경 */
ALTER TABLE 회원테이블 CHANGE 성별 성 VARCHAR(2);
/***************테이블명 변경**************/
/* 테이블명 변경 */
ALTER TABLE 회원테이블 RENAME 회원정보;
/* 회원테이블 조회 --> 이름이 변경되었기 때문에 조회되지 않음*/
SELECT *
FROM 회원테이블;
/* 회원정보 조회 */
SELECT *
FROM 회원정보;
/***************테이블 삭제**************/
/* 테이블 삭제 */
DROP TABLE 회원정보;
/* 회원정보 조회 --> 삭제되었기 때문에 조회되지 않음*/
SELECT *
FROM 회원정보;
테이블 결합(JOIN)
/***************INNER JOIN***************/
/* INNER JOIN: 두 테이블의 공통 값이 매칭되는 데이터만 결합 */
/* Customer + Sales Inner JOIN */
SELECT *
FROM CUSTOMER AS A
INNER
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO;
/* Customer 및 Sales 테이블은 mem_no(회원번호) 기준으로 1:N 관계 */
SELECT *
FROM CUSTOMER AS A
INNER
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO
WHERE A.MEM_NO = '1000970';
/***************LEFT JOIN***************/
/* LEFT JOIN: 두 테이블의 공통 값이 매칭되는 데이터만 결합 + 왼쪽 테이블의 매칭되는 않는 데이터는 NULL */
/* Customer + Sales LEFT JOIN */
SELECT *
FROM CUSTOMER AS A
LEFT
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO;
/* NULL은 회원가입만하고 주문은 하지 않는 회원을 의미 */
/***************RIGHT JOIN***************/
/* RIGHT JOIN: 두 테이블의 공통 값이 매칭되는 데이터만 결합 + 오른쪽 테이블의 매칭되는 않는 데이터는 NULL */
/* Customer + Sales RIGHT JOIN */
SELECT *
FROM CUSTOMER AS A
RIGHT
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO
WHERE A.MEM_NO IS NULL;
/* 회원번호(9999999)는 비회원 */
/* IS NULL: 비교 연산자 / NULL인 값만 */
/***************테이블 결합(JOIN) + 데이터 조회(SELECT)***************/
/* 회원(Customer) 및 주문(Sales) 테이블 Inner JOIN 결합 */
SELECT *
FROM CUSTOMER AS A
INNER
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO;
/* 임시테이블 생성 */
CREATE TEMPORARY TABLE CUSTOMER_SALES_INNER_JOIN
SELECT A.*
,B.ORDER_NO
FROM CUSTOMER AS A
INNER
JOIN SALES AS B
ON A.MEM_NO = B.MEM_NO;
/* 임시테이블 조회 */
SELECT * FROM CUSTOMER_SALES_INNER_JOIN;
/* 임시테이블(TEMPORARY TABLE)은 서버 연결 종료시 자동으로 삭제됩니다. */
/* 성별이 남성 조건으로 필터링하여 */
SELECT *
FROM CUSTOMER_SALES_INNER_JOIN
WHERE GENDER = 'MAN';
/* 거주지역별로 구매횟수 집계 */
SELECT ADDR
,COUNT(ORDER_NO) AS 구매횟수
FROM CUSTOMER_SALES_INNER_JOIN
WHERE GENDER = 'MAN'
GROUP
BY ADDR;
/* 구매횟수 100회 미만 조건으로 필터링 */
SELECT ADDR
,COUNT(ORDER_NO) AS 구매횟수
FROM CUSTOMER_SALES_INNER_JOIN
WHERE GENDER = 'MAN'
GROUP
BY ADDR
HAVING COUNT(ORDER_NO) < 100;
/* 모든 열 조회 */
/* 구매횟수가 낮은 순으로 */
SELECT ADDR
,COUNT(ORDER_NO) AS 구매횟수
FROM CUSTOMER_SALES_INNER_JOIN
WHERE GENDER = 'MAN'
GROUP
BY ADDR
HAVING COUNT(ORDER_NO) < 100
ORDER
BY COUNT(ORDER_NO) ASC;
/***************3개 이상 테이블 결합***************/
/* 주문(Sales) 테이블 기준, 회원(Customer) 및 상품(Product) 테이블 LEFT JOIN 결합 */
SELECT *
FROM SALES AS A
LEFT
JOIN CUSTOMER AS B
ON A.MEM_NO = B.MEM_NO
LEFT
JOIN PRODUCT AS C
ON A.PRODUCT_CODE = C.PRODUCT_CODE;'Develop > SQL' 카테고리의 다른 글
| [MySQL] MySQL 기본 명령어 모음 (0) | 2021.09.27 |
|---|