본문 바로가기
코딩/MySQL

MySQL 기본문법

by Song1234 2024. 3. 26. 
# 데이터 베이스 선택
USE KDT;

 

테이블 확인하기

DESC MEMBER;
userid varchar(20) NO PRI    
userpw varchar(20) NO      
name varchar(20) NO      
hp varchar(20) NO UNI    
email varchar(50) NO      
gender varchar(10) NO      
ssn1 char(6) NO      
ssn2 char(7) NO      
zipcode varchar(5) YES      
address1 varchar(100) YES      
address2 varchar(100) YES      
address3 varchar(100) YES      
regdate datetime YES   CURRENT_TIMESTAMP DEFAULT_GENERATED
POINT int YES   100  

테이블 삭제하기

DROP TABLE MEMBER;

필드 추가하기

ALTER TABLE MEMBER ADD MBTI VARCHAR(10);


#필드 수정하기
ALTER TABLE MEMBER MODIFY COLUMN MBTI VARCHAR(20);


#필드 삭제하기
ALTER TABLE MEMBER DROP MBTI;

추가된 필드

 

crud(Create Read Update Delete)

데이터 삽입하기

  1. INSERT INTO 테이블명 VALUES (값1, 값2, 값3,...) 전체 필드의 값 삽입
  2. INSERT INTO 테이블명 (필드명1, 필드명2,...) VALUES (값1, 값2, ...) 특정 필드에만 값 삽입
CREATE TABLE WORDS(
    ENG VARCHAR(50) PRIMARY KEY,
    KOR VARCHAR(50) NOT NULL,
    LEV INT DEFAULT 1
);

DESC TABLE WORDS;

INSERT INTO WORDS VALUES ('APPLE', '사과', 1);
# INSERT INTO WORDS VALUES ('APPLE', '사과', 1); # 중복 데이터 삽입 에러
-- INSERT INTO WORDS VALUES ('BANANA', '바나나'); # 컬럼 갯수가 일치하지 않음
INSERT INTO WORDS VALUES ('BANANA', '바나나', null); # NULL이 들어감
-- INSERT INTO WORDS VALUES ('ORANGE', NULL, NULL) ; # 뜻에 NULL을 넣을 수 없음


INSERT INTO WORDS (ENG, KOR, LEV) VALUES ('ORANGE', '오렌지', 1);    
INSERT INTO WORDS (ENG, KOR) VALUES ('MELON', '메론'); # 값을 넣지 않으면 DEFAULT값이 적용됨
INSERT INTO WORDS (LEV, ENG, KOR) VALUES(2, 'AVOCADO', '아보카도'); # 필드 순서에 상관없다
INSERT INTO WORDS (ENG) VALUES ('CHERRY'); # 뜻에 NULL을 넣을수 없음

데이터 삽입 실습

MEMBER 테이블에 5명의 데이터를 삽입

DESC MEMBER;

-- INSERT INTO MEMBER VALUES ('APPLE', '1234', '김사과', '010-1234-5678', 'APPLE@NAVER.COM', 'W', '000101', '4958374', '15487', '서울특별시', '강남구', '대치동', now(), 100);
-- INSERT INTO MEMBER (USERID, USERPW, NAME, HP, EMAIL, GENDER, SSN1, SSN2, ZIPCODE, ADDRESS1, ADDRESS2, ADDRESS3, POINT)
-- VALUES ('BANANA', '5678', '박나나', '010-2341-4532', 'BANANA@GMAIL.COM', 'M', '950423', '1523432', '15589', '서울특별시', '마포구', '상수동', 45);
-- INSERT INTO MEMBER VALUES ('MELON', '4312', '이메론', '010-1324-5675', 'MELON@NAVER.COM', 'W', '010101', '4954374', '16587', '서울특별시', '강남구', '역삼', now(), 12);
-- INSERT INTO MEMBER VALUES ('CHERRY', '6543', '이체리', '010-7899-5342', 'CERRY@NAVER.COM', 'W', '991218', '248554', '16657', '서울특별시', '강남구', '논현동', now(), 54);
-- INSERT INTO MEMBER VALUES ('DELMONT', '1414', '김델몬', '010-1645-4348', 'DELMONT@NAVER.COM', 'W', '000102', '4433374', '14587', '서울특별시', '강남구', '언주동', now(), 20);
CREATE TABLE MEMBER;
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '여자', '001011', '4015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('banana', '2222', '반하나', '010-2222-2222', 'banana@banana.com', '여자', '001011', '2015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('orange', '3333', '오렌지', '010-3333-3333', 'orange@orange.com', '남자', '001011', '3015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('melon', '4444', '이메론', '010-4444-4444', 'melon@melon.com', '남자', '001011', '3015111');
insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values
('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '001011', '1015111');

삽입된 데이터

 

데이터 수정하기

  1. UPDATE 테이블명 SET 필드명1=값1, 필드명2=값2,...;
  2. UPDATE 테이블명 SET 필드명1=값1, 필드명2=값2,... WHERE 조건절;
#UPDATE WORDS SET ENG = 'Rucy';#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.    0.000 sec
# 1. 일시적인 SAFE모드를 해제: SET SQL_SAFE_UPDATES=0; 
# 2. 영구적인 SAFE모드를 해제: edit -> preferences -> SQL editor -> safe update 체크헤제

MySQL 툴에는 기본적으로 세이프모드 가 적용되어 있어 많은 값들을 한번에 변경하려고 하면 에러가 난다.
따라서 많은 데이터를 한번에 변경하려면 세이프모드를 직접 해제 해 주어야 한다.

update words set lev=1;
SELECT * FROM kdt.words;
# 모든 유저에게 50 포인트를 더해주기
UPDATE MEMBER SET POINT = POINT + 50;

UPDATE WORDS SET LEV=2 WHERE ENG='AVOCADO';
UPDATE WORDS SET KOR = '어륀지', LEV=2 WHERE ENG = 'ORANGE';

 

# MEMBER 테이블의 아이디가 'APPLE'인 회원에 대해 우편번호는 '12345', 주소1은 '서울시 서초구',
# 주소2는 '양재동', 주소3은 'XX아파트 101동'으로 수정하기

UPDATE MEMBER SET ZIPCODE='12345', ADDRESS1='서울시 서초구', ADDRESS2='양재동', ADDRESS3='XX아파트 101동' WHERE USERID='APPLE';

 

 

 

데이터 삭제하기

  1. DELETE FORM 테이블명;
  2. DELETE FORM 테이블명 WHERE 조건절;
DELETE FROM words;
#DELETE FROM MEMBER WHERE USERID='AVOCAD'; # 삭제할려는 데이터가 없어도 실행은 되지만 데이터가 없어서 삭제되지 않는다
DELETE FROM MEMBER WHERE USERID='AVOCADO';

 

아보카도가 삭제 되었다

 

 

 

데이터 검색하기

  • SELECT 필드명1, 필드명2, ... FROM 테이블명
  • SELECT 필드면1, 필드명2, ... FORM 테이블명 WHERE 조건절;
SELECT ENG, KOR FROM WORDS;
SELECT ENG FROM WORDS;
SELECT KOR, ENG FROM WORDS;
SELECT 100;
SELECT 100 + 50;
SELECT 100 + 50 AS '덧셈';
SELECT 100 + 50 AS 덧셈;
SELECT 100 + 50 덧셈;
SELECT 100 + 50 '덧셈 연산'; # 따옴표를 사용하는 이유는 띄어쓰기가 있을 수 있기 때문이다
SELECT ENG AS '영단어', KOR AS '뜻', LEV AS '수준' FROM WORDS;

덧셈
별칭사용

SELECT * FROM WORDS; # 모든 컬럼을 가져오기
SELECT ENG, KOR, LEV FROM WORDS;

SELECT NULL; # 데이터가 없음, INSERT가 되지 않은 것
SELECT ''; #  해당 셀에 ''데이터가 삽입된 것, NULL이 아님
SELECT 100 + NULL; # 결과: NULL, 연산할 수 없음
SELECT 100 + ''; # 결과: 100, 연산할 수 있음

SQL 연산자

  1. 산술 연산자: +, -, *, /, MOD(나머지), DIV(몫)
  2. 비교 연산자: =(같음), <, >, >=, <=, <>(다름)
  3. 대입 연산자: =
  4. 논리 연산자: AND, OR, NOT, XOR
  5. 기타 연산자
    IS: 양쪽의 피연산자가 모두 같으면 TRUE, 아니면 FALSE
    BETWEEN A AND B: 값이 A보다 크거나 같고, B보다 작거나 같으면 TRUE, 아니면FALSE
    IN: 매개변수로 전달된 리스트에 값이 존재하면 TRUE 아니면 FALSE
    LIKE: 패턴으로 문자열을 검색하여 값이 존재하면 TRUE 아니면 FALSE
SELECT USERID, NAME FROM MEMBER WHERE USERID='apple';
SELECT USERID, NAME FROM MEMBER WHERE GENDER='남자';
SELECT USERID, NAME, GENDER FROM MEMBER WHERE GENDER='남자';

성별이 남자인 데이터만 출력

 

# POINT가 150 이상인 MEMBER의 아이디, 이름, 포인트를 출력
SELECT USERID, NAME, POINT FROM MEMBER WHERE POINT >= 150;

# 로그인
SELECT USERID FROM MEMBER WHERE USERID='APPLE' AND USERPW='1234'; # 로그인 실패
SELECT USERID FROM MEMBER WHERE USERID='APPLE' AND USERPW='1111'; # 로그인

# WORDS 테이블에서 LEV이 NULL인 데이터를 출력
SELECT * FROM WORDS WHERE LEV IS NULL; # NULL 값을 찾을때는 IS 를 사용해야한다

SELECT * FROM WORDS WHERE LEV=NULL;

# MEMBER 테이블에서 POINT가 0이상 150이하인 데이터를 출력
SELECT * FROM MEMBER WHERE POINT >= 0 AND POINT <= 150; SELECT * FROM MEMBER WHERE POINT BETWEEN 0 AND 150;

 

포인트가 0이상 150 이하인 데이터

 

# MEMBER 테이블에서 이름이 김사과, 반하나, 오렌지인 데이터 출력
SELECT * FROM MEMBER WHERE NAME IN ('김사과', '반하나', '오렌지');

SELECT * FROM MEMBER WHERE USERID LIKE 'A%'; # A로 시작하는 문자열
SELECT * FROM MEMBER WHERE USERID LIKE '%A'; # A로 끝나는 문자열
SELECT * FROM MEMBER WHERE USERID LIKE '%A%'; # A를 포함하는 문자열

 

A로 시작하는 문자열

 

 

 

정렬하기
SELECT 필드면1, 필드명2, ... FROM 테이블명 [WHERE 조건절] ORDER BY 필드명 [ASC, DESC]; # 대괄호는 생략가능

SELECT * FROM MEMBER ORDER BY USERID ASC; # 아이디로 오름차순
SELECT * FROM MEMBER ORDER BY USERID; # 아이디로 오름차순
SELECT * FROM MEMBER ORDER BY USERID DESC; # 아이디로 내림차순

오름차순
내림차순

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2, POINT) values
('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '001011', '1015111', 200);

SELECT * FROM MEMBER ORDER BY POINT;

포인트 기준 오름차순

 

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2, POINT) values
('cherry', '6666', '채리', '010-6666-6666', 'cherry@cherry.com', '여자', '001011', '4015111', 200);

# MEMBER 테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 USERID로 오름차순
SELECT * FROM MEMBER ORDER BY POINT DESC, USERID ASC;

포인트가 같을시 userid 가 알파벳 기준으로 오름차순

 

# MEMBER 테이블의 여성 회원을 포인트순으로 오름차순하고 포인트가 같다면 USERID로 오름차순
SELECT * FROM MEMBER WHERE GENDER='여자' ORDER BY POINT, USERID;

 

LIMIT(일부 갯수의 로우만 출력)
SELECT 필드명1, 필드명2,... FROM 테이블명 LIMIT 가져올 로우의 갯수
SELECT 필드명1, 필드명2,... FROM 테이블명 LIMIT 시작로우(인덱스), 가져올 로우의 갯수

SELECT * FROM MEMBER;
SELECT * FROM MEMBER LIMIT 3;
SELECT * FROM MEMBER LIMIT 2, 2;

 

3개의 로우만 출력
2번째 인덱스 부터 2개의 로우 출력

 

# MEMBER 테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 USERID로 오름차순한 뒤 TOP3을 출력
SELECT * FROM MEMBER ORDER BY POINT DESC, USERID ASC LIMIT 3;

# 집계(그룹) 함수
# COUNT(): 로우의 갯수를 세는 함수
SELECT * FROM MEMBER;
SELECT COUNT(*) FROM MEMBER;

# 전체 데이터의 갯수를 출력하려면 NULL이 없는 필드를 선택 -> PRIMARY KEY를 선택하는 것을 추천
SELECT COUNT(USERID) FROM MEMBER;
SELECT COUNT(ZIPCODE) FROM MEMEBER; # NULL을 세지 않음
SELECT COUNT(USERID) AS TOTALCOUNT FROM MEMBER; # 별칭 지정가능

 

# SUM(): 로우의 값을 더함
SELECT SUM(POINT) AS 'TOTALPOINT' FROM MEMBER;
-- SELECT USERID, SUM(POINT) AS 'TOTALPOINT' FROM MEMBER; # 집계함수는 그룹과 사용해야함

# AVG(): 로우의 평균을 구함
SELECT AVG(POINT) AS 'AVG' FROM MEMBER;

# MIN(): 로우의 최소값을 구함
SELECT MIN(POINT) AS 'MIN' FROM MEMBER;

# MAX(): 로우의 최대값을 구함
SELECT MAX(POINT) AS 'MAX' FROM MEMBER;

 

그룹

SELECT 그룹을 맺은 컬럼 또는 집계함수 FROM 테이블명 GROUP BY 필드명;
SELECT 그룹을 맺은 컬럼 또는 집계함수 FROM 테이블명 GROUP BY 필드명 HAVING 조건절; # 이 조건절은 그룹에 한한 조건절이다.

SELECT GENDER FROM MEMBER GROUP BY GENDER;

SELECT GENDER FROM MEMBER GROUP BY USERID; # 중복된 데이터가 없기 때문에 모든 로우가 개별 그룹

SELECT GENDER AS '성별', COUNT(USERID) AS '인원' FROM MEMBER GROUP BY GENDER;

SELECT GENDER, COUNT(USERID) AS '인원' FROM MEMBER GROUP BY GENDER HAVING GENDER='여자';

 

 

 

 

포인트가 100을 초과하는 MEMBER 중에서 남자, 여자 그룹으로 나눠 포인트의 평균을 구하고
평균 포인트가 150이상인 성별에 대해 출력 (단, 포인트가 많은 성별을 우선으로 출력)

SELECT GENDER ,AVG(POINT) AS AVG FROM MEMBER WHERE POINT > 100 GROUP BY GENDER HAVING  AVG >= 150 ORDER BY AVG DESC;

 

데이터 정규화

  • 데이터 베이스를 성계할 때 중복을 최소화하는것
  • 조직화되어 있지 않은 테이블과 관계들을 조직화된 테이블과 관계들로 나누는것

데이터 정규화가 필요한 경우

  • 데이터를 변경, 삽입, 삭제할 때 원하지 않게 데이터가 삭제되거나 가공되는 일이 발생할 수 있음(이상현상)
  • 이상 현상이 발생할 가능성이 있다면 정규화가 필요

정규화의 종류

  1. 1NF(제 1정규화)
  • 테이블 안의 모든 값들은 단일 값이어야 한다.
  • 더 이상 쪼개질 수 없는 단위로 저장되어야한다.

  1. 2NF(제 2정규화)
  • 1NF를 만족하면서 완전 함수 종속성을 가진 관계들로만 테이블을 생성
  • 종속성들 중 종속 관계에 있는 열들끼리 테이블을 구분해 주는 것
  • 기본키에 속하지 않은 속성 모두가 기본키에 완전 함수 종속인 정규형
    • 함수 종속성: X값에 따라 Y값이 결정되는 경우

  1. 3NF(제 3정규화)
  • 2NF를 만족하면서, 기본키에 대해 이행적 함수 종속이 되지 않는 것을 의미

  1. 비정규화
  • 정규형에 일치하게 되어 있는 테이블을 정규형을 지키지 않는 테이블로 변경
  • 테이블을 조회하는 용도로 사용하거나, 너무 데이터가 많이 나뉘어 성능이 저하된다면
    비정규화를 하여 테이블을 다루는 것이 더 효율적일 수 있음
  • 어떤 작업을 수행하는지, 어떤 데이터를 사용하는지에 따라 적절한 정규화를 하는 것이 좋음

CREATE TABLE PROFILE (
    USERID VARCHAR(20) NOT NULL,
    HEIGHT DOUBLE,
    WEIGHT DOUBLE,
    MBTI VARCHAR(10),
    FOREIGN KEY(USERID) REFERENCES MEMBER(USERID)
);
DESC PROFILE;

INSERT INTO PROFILE VALUES ('APPLE', 160, 50, 'ISTP');

SELECT * FROM PROFILE;

-- INSERT INTO PROFILE VALUES ('GRAPES', 170, 70, 'ESTP'); # 에러 MEMBER에 없는 데이터

INSERT INTO PROFILE VALUES ('AVOCADO', 180, 80, 'INFP');
INSERT INTO PROFILE VALUES ('ORANGE', 170, 70, 'ENFP');

member 테이블에 userid 가 있어야만 데이터 추가 가능

조인(Join)

SELECT 필드명1, 필드명2, ... FROM 테이블1 [INNERM, LEFT, RIGHT] JOIN 테이블2
ON 테이블1.필드명 = 테이블2.필드명;

SELECT * FROM MEMBER;
SELECT * FROM PROFILE;

INNER 조인

  • 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력
  • 두 테이블의 교집합
  • JOIN, INNER JOIN, CROSS JOIN 모두 같은 의미로 사용됨
SELECT MEMBER.USERID, NAME, GENDER, MBTI FROM MEMBER INNER JOIN PROFILE 
ON MEMBER.USERID=PROFILE.USERID;

SELECT M.USERID, NAME, GENDER, MBTI FROM MEMBER AS M INNER JOIN PROFILE  AS P
ON M.USERID = P.USERID;

별칭으로 사용가능

LEFT/RIGHT 조인

두 테이블이 조인될 때 왼쪽 또는 오른쪽을 기준으로 기준 테이블의 데이터를 모두 출력

SELECT M.USERID, NAME, GENDER, MBTI FROM MEMBER AS M LEFT JOIN PROFILE  AS P
ON M.USERID = P.USERID;

SELECT M.USERID, NAME, GENDER, MBTI FROM MEMBER AS M RIGHT JOIN PROFILE  AS P
ON M.USERID = P.USERID;

left 조인 member 테이블을 기준으로 데이터 가 모두 출력되었다

 

right 조인 profile 테이블 기준으로 모두 출력, profile 테이블에 데이터가 3개 밖에 없기때문에 3개만 출력 되었다

 

'코딩 > MySQL' 카테고리의 다른 글

MySQL과 파이썬 연동  (0) 2024.03.28
MySQL 계정,사용자,권한 관리  (0) 2024.03.27
ER 다이어그램  (0) 2024.03.27
MySQL의 함수  (0) 2024.03.27
DBMS  (1) 2024.03.25

관련글

티스토리툴바