[mySQL] 데이터베이스, SQL 기본 문법, 집계함수

SQL 파일

day1.sql

0.01MB

RDB(Relational DataBase)
- 관계형 데이터 모델에 기초를 둔 데이터베이스
- 모든 데이터를 2차원 테이블 형태로 표현

RDBMS
- RDB를 생성하고 수정하고 관리할 수 있는 소프트웨어
- RDBMS의 테이블은 서로 연관되어 있어 일반 DBMS보다 효율적으로 데이터를 저장, 구성 및 관리할 수 있음
- 정규화를 통해 데이터의 중복성을 최소화하여 트랜젝션을 수행하는 것이 더 쉬움
- 데이터의 원자성, 일관성, 격리 및 내구성을 유지하며 데이터의 무결성을 높임

SQL(Structured Query Language)
- 관계형데이터베이스(RDBMS)에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
- 대소문자를 구별하지 않음
- 문자열을 저장할 때 ''(싱글따옴표)만 사용 (sql문법은 보통 다른 언어 내에서 사용. 다른 언어 문자열 저장 기호 "") 

            데이터베이스                  ----->      테이블(엑셀과 유사)
(사용자, 함수, 프로시저, 테이블, 뷰 ..)                  행 : 레코드, 로우
                                                                 열 : 필드, 컬럼, 어트리뷰트

SQL 구문

데이터베이스 확인

데이터베이스 생성

    create database 데이터베이스명;

데이터베이스 삭제

    drop database 데이터베이스명;

데이터베이스 사용

    use 데이터베이스명;

 

테이블(Table)
- 데이터를 행과 열로 스키마에 따라 저장할 수 있는 구조

✅ 스키마
데이터베이스의 구조와 제약 조건에 관한 명세를 기술한 집합을 의미

테이블 생성

    create table 테이블명(
        컬럼명1 데이터타입 제약조건,
        컬럼명2 데이터타입 제약조건,
        컬럼명3 데이터타입 제약조건,
        ...
        컬럼명n 데이터타입 제약조건
    )

데이터 타입
1. 숫자 타입
    정수 : tinyint, smallint, midiumint, int, bigint ..
    소수 : float, double

2. 문자열 타입
    텍스트 : char, varchar, text
    char(10) : 1byte만 저장하더라도 총 10byte를 소모
    varchar(10) : 1byte만 저장하면 총 1byte를 소모, 총 65535 byte 까지 저장 가능
    // 파일 저장시 경로를 텍스트로 저장.

    바이너리(이진파일) : binary, varbinary // 데이터 비교시 사용.(음악검색, 이미지 검색 ..)
    열거 : enum
    enum('남자', '여자') // 남자, 여자 외 다른 글자x

3. 날짜와 시간타입
    날짜 : date
    날짜와 시간타입 : datetime, timestamp
    timestamp : 1970년 1월 1일 0시 0분 0초 부터, 밀리세컨드 단위로 증가


제약조건
데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행하는 검사 규칙

not null : 컬럼에 null을 저장할 수 없음
unique : 중복된 값을 저장할 수 없음, null 값을 허용
default : 기본값을 설정 // 값을 넣지 않을 시 기본적으로 들어갈 값
auto_increment : 자동으로 숫자가 증가되어 추가, 중복값이 저장되지 않음, 직접 데이터를 추가할 수 없음.
primary key : unique 제약과 not null 제약을 동시에 가짐, 테이블에 오직 하나의 컬럼에만 적용,
            데이터를 쉽고 빠르게 찾을 수 있게 설정(색인(index)), 외래키가 참조할 수 있도록 설정
foreign key : 다른 테이블과 연결해주는 역할, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력
             primary key를 참조

예제 테이블 생성

    create table tb_member (
        mem_idx bigint auto_increment primary key,
        mem_userid varchar(20) unique not null,
        mem_userpw varchar(20) not null,
        mem_name varchar(20) not null,
        mem_hp varchar(20) not null,
        mem_email varchar(100) not null,
        mem_hobby varchar(100),
        mem_ssn1 varchar(6) not null,
        mem_ssn2 varchar(7) not null,
        mem_zipcode varchar(5),
        mem_address1 varchar(100),
        mem_address2 varchar(100),
        mem_address3 varchar(100),
        mem_regist datetime default now()
    )

테이블 구조 확인

    desc 테이블명;

테이블 삭제

    drop table 테이블명;

테이블 컬럼 추가

    alter table 테이블명 add 컬럼명 타입 제약조건;

alter table tb_member add mem_point int default 0;

테이블 컬럼 수정

    alter table 테이블명 modify column 컬럼명 타입 제약조건;

alter table tb_member modify column mem_point double default 0;

테이블 컬럼 삭제

    alter table 테이블명 drop 컬럼명;

alter table tb_member drop mem_point;

테이블 자료 확인

    select * from 테이블명;

select * from tb_member;

데이터 삽입(insert)

    insert into 테이블명 (컬럼명1, 컬럼명2, ..) values (값1, 값2, ..);

- 테이블 컬럼 개수와 삽입할 데이터의 개수가 같을 때. (auto_increment를 적용한 테이블 x) 

    insert into 테이블명 values (값1, 값2, ...);

예제

    insert into tb_member (mem_userid, mem_userpw, mem_name, mem_hp, mem_email,
    mem_hobby, mem_ssn1, mem_ssn2, mem_zipcode, mem_address1, mem_address2, mem_address3) values
    ('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '드라이브', '001011', '4068518',
    '12345', '서울 서초구 양재동', '11-11', '11111');

 

---

insert 에러종류

 

-- Columm count doesn`t match value count at row 1 - 인덱스개수가 칼럼개수랑 안맞을 경우
insert into tb_member (mem_userid, mem_userpw, mem_name, mem_hp, mem_email) values 
('banana', '2222', '반하나', '010-2222-22222', 'banana@banana.com', '등산', '001011', '4068518', 
'12345', '서울 서초구 양재동', '22-22', '22222'); 

 -- ERROR 1364: Field 'mem_ssn1' doesn't have a default value - not null인 컬럼의 데이터를 제외하고 데이터를 삽입할 수 없음
insert into tb_member (mem_userid, mem_userpw, mem_name, mem_hp, mem_email) values 
('banana', '2222', '반하나', '010-2222-22222', 'banana@banana.com');

-- not null이 아닌 컬럼 제외하고 삽입 가능. 넣지 않은 값은 default로 설정한 컬럼 외에는 null로 채워짐.
insert into tb_member (mem_userid, mem_userpw, mem_name, mem_hp, mem_email, mem_ssn1, mem_ssn2) values 
('banana', '2222', '반하나', '010-2222-22222', 'banana@banana.com', '001111', '4011111');

---

데이터 수정(update)

- 모든 로우의 컬럼 값 변경

    update 테이블명 set 컬럼1 = 값1, 컬럼2 = 값2, ...;
    update tb_member set mem_point = 500;

 

└처음 실행 시 오류남 (safe mode : 한꺼번에 전체 데이터 수정 시 경고)

 

-조건을 준 컬럼 값만 수정

     update 테이블명 set 컬럼1 = 값1, 컬럼2 = 값2 ... where 조건절;
     update tb_member set mem_point = mem_point + 200 where mem_idx = 1;

      

✅ mysql safe mode 해체
    상단메뉴 edit > preferences -> sql editor -> safe updates (reject ...) 체크 해제 -> 종료 후 다시 실행

데이터 삭제(delete)
    delete from 테이블명;
        delete from tb_member;
    delete from 테이블명 where 조건절;
        delete from tb_member where mem_idx = 1;

SQL 연산자
산술 연산자
    +, -, *, /, div, mod
    div : 왼쪽 피연산자를 오른쪽 피연산자로 나눈 후 소수 부분을벌미
    mod : 나머지 연산자
대입 연산자
    =  : 대입
비교 연산자
    >, <, =, <>, >=, <=, is, is not, between A and B, in()
    = : 같다
    <> : 다르다
    is : 왼쪽 피연산자와 오른쪽 피연산자가 같으면 참
    is not : 왼쪽 피연산자와 오른쪽 피연산자가 다르면 참
    between A and B : 값이 A보다는 크거나 같고, B보다는 작거나 같으면 참
    in() : 연산자의 값이 인수로 전달받은 리스트에 존재하면 참
논리연산자
    and, or, xor, not

검색(select)
    select 컬럼명1, 컬럼명2, ... from 테이블명;
    select mem_idx, mem_userid, mem_name from tb_member;
    select mem_userid, mem_name, mem_idx from tb_member;

✅ * : 모든 컬럼, 속도가 느려짐

    select 컬럼명1, 컬럼명2, ... from 테이블명 where 조건절;
    select mem_idx, mem_name, mem_userid from tb_member where mem_idx = 3;

-- 로그인 쿼리(and 연산자)
select mem_idx, mem_name, mem_userid from tb_member where mem_userid = 'orange' and mem_userpw = '3333';

    select 컬럼명1, 컬럼명2, ... from 테이블명 order by 정렬할 컬럼명 (asc(오름차순), desc(내림차순));
    select mem_idx, mem_name, mem_userid from tb_member order by mem_idx asc;
    select mem_idx, mem_name, mem_userid from tb_member order by mem_idx desc;
✅ asc 는 생략 가능

오름차순 정렬

내림차순 정렬

⭐ 집계함수
    데이터의 그룹을 집계(연산)할 수 있는 함수
    count() : 데이터의 개수
    max() : 최대값
    min() : 최소값
    sum() : 합계
    avg() : 평균

    select 집계함수 또는 그룹을 맺은 컬럼 from 테이블명 group by 컬럼명;
    select mem_gender from tb_member group by mem_gender; // 성별로 그룹 생성

 

    -- 그룹을 맺어서 null값이 들어가지 않을만한 컬럼을 count()에 넣어서 개수 확인
    select mem_gender, count(mem_idx) from tb_member group by mem_gender;

✅ 컬럼 별명 주기
    select 컬럼명 as 별명 from 테이블명;
    select 컬럼명 별명 from 테이블명;

 

-- 'tot'로 컬럼 별명주기

-- as, ' ' : 별명 생성

select mem_gender, sum(mem_point) as tot from tb_member group by mem_gender;

select mem_gender, sum(mem_point) tot from tb_member group by mem_gender;