시행착오 기술 블로그

UCASE / LCASE: 대소문자 변환

# 대문자
select ucase('hello'); # return HELLO

# 소문자
select lcase('HELLO'); # return hello

MID : 문자열 중 부분을 반환해옴. mid(string, start_num, length)로 사용

select mid('hello world', 0, 5); # hello 반환

select mid('hello world', -5, 5); # world 반환

1. count, sum, avg

count : 컬럼의 개수 반환

select count(*) from people; # 갯수 반환

select count(distint phone) from people; # 중복 제거된 갯수 반환

sum: 컬럼의 모든값을 덧셈한 총합 반환

select sum(age) from person; # 사람들의 나이의 총합을 반환

avg: 컬럼의 평균을 반환

select avg(age) from people; # 사람들의 평균나이를 반환

2. min, max

min: 컬럼의 최소값을 반환

select min(age) from people; # 사람들의 최연소 나이를 반환

max: 컬럼의 최대값을 반환

select min(age) from people; # 사람들의 최고령 나이를 반환

3. group by 와 having

컬럼을 기준으로 그룹화하여 데이터를 조회할 때 쓴다.

group by와 집계함수 sum

예시) subject 수강과목 별로 사람들을 그룹화 하여 인원수를 반환

select subject, count(name) from people
group by subject
order by id
limit 5;

group by와 having

조건에 집계함수가 포함되는 경우 where 대신 having을 쓴다.

예시) 수강과목 인원이 50명 넘는 과목만 검색

select subject, count(name) cnt
from people
group by subject
having cnt >= 50;

예시) 평균 점수가 80점 미만인 과목만 검색

select subject, avg(rate)
from people
group by subject
having avg(rate) < 80;

기본키(primary key)

1. 기본키 생성

테이블 만들 때, primary key (컬럼명) 추가

create table person
(
    pid int NOT NULL,
    name varchar(16),
    age int,
    sex char,
    primary key (pid)	# pid를 기본키로 설정하겠다는 뜻
)

2. 기본키 삭제

테이블 alter 이용해서 drop primary key 한다.

alter table animal
drop primary key;

3. 기존 테이블에서 기본키 설정

alter table person
add primary key (pid);

만약 기본키 여러개 쓰고싶으면 (pid, name) 이렇게도 가능

외래키(foreign key)

테이블과 테이블을 연결해주는 키

참조되는 테이블의 항목은 그 테이블의 기본키 또는 단일값

외래키 생성

constraint 외래키명 foreign key (컬럼명) references 테이블명 (컬럼명)

create table orders 
(
    oid int not null,
    order_no varchar(16),
    pid int,
    PRIMARY KEY(oid),
    constraint FK_person foreign key (pid) references person(pid)
);

외래키 삭제

drop foreign key 외래키명

alter table 테이블명
drop foreign key 외래키명;

기존 테이블에 외래키 지정

add foreign key (컬럼명) references 테이블명 (컬럼명);

alter table 테이블명
add foreign key (컬럼명) references 테이블명(컬럼명);

예제

police_station에서 name이 기본키 이고,

crime_status 테이블에서 reference 컬럼이 외래키이다. 

이때 두 테이블은 name과 reference 컬럼을 이용해 연결될 수 있다.

기본키와 외래키는 where 조건절에서 비교가 가능하다. 

select c.police_station, p.address
from crime_status c, police_station p
where c.reference = p.name
group by c.police_station;

1. 설치 :

파이썬에서 mysql 사용하려면 mysql driver 설치해야함.

pip install mysql-connector-python

2. import 후 db 연결

<로컬>

import mysql.connector

mydb = mysql.connector.connect(
    host = "",
    user = "",
    password = ""
)

<aws>

import mysql.connector

mydb = mysql.connector.connect(
    host = "aws_end_point",
    port = "port number",
    user = "admin",
    password = "password",
    database = "mydb"
)

3. DB 지정 쿼리

# DB 지정
cur = mydb.cursor()
cur.execute("use oneday")
cur.close()

4. 파이썬에서 sql 쿼리 실행하기

cur = mydb.cursor()

# 테이블 생성 쿼리
cur.execute("CREATE DATABASE oneday;")

# 사용자 생성 쿼리
cur.execute("create user 'oneday'@'%' identified by '1234';")

# 사용자 권한부여 쿼리
cur.execute("grant all on oneday.* to 'oneday'@'%';")

# 조회 쿼리
sql = "show create database oneday"
cur.execute(sql)

# 조회 쿼리 리턴값이 여러개인 경우, fetchall() 필요
sql = "show grants for 'oneday'@'%'"
cur.execute(sql)
result = cur.fetchall()
for i in result:
    print(i)
    
    
# 삽입 쿼리
sql = "INSERT INTO COFFEE_STORE (brand, name, gu_name, address, lat, lng) VALUES ((SELECT id FROM COFFEE_BRAND WHERE name = 'STARBUCKS'), %s, %s, %s, %s, %s)"
cur.execute(sql, (name, gu_name, address, lat, lng))
mydb.commit() # 반드시 커밋해야 DB에 저장됨

5. db 연결 종료

cur.close()
mydb.close()

1. 별칭 alias

컬럼명도 별칭 붙일 수 있고, 테이블에도 별칭 붙일 수 있다.

SELECT zm.name AS 회원이름,
	zm.mobile_no AS 휴대폰번호,
	zm.email AS 회원이메일, 
	zm.password AS 비밀번호
FROM zero_member AS zm;

AS는 생략 가능

2. JOIN : 데이터 2개 이상을 연결하여 출력하는 것.

inner join

select * from 테이블명1
join 테이블명2
on 테이블명1.col1 = 테이블명2.col (join 조건)
where 조건;

SELECT * FROM MEMBER;
SELECT * FROM member_detail;

-- inner join
SELECT * FROM member AS m
JOIN member_detail AS md
ON m.id = md.id;

left join

-- left join
SELECT * FROM member AS m
LEFT JOIN member_detail AS md
ON m.id = md.id;

right join

-- right join
SELECT * FROM member AS m
RIGHT JOIN member_detail AS md
ON m.id = md.id;

self join (제일 많이 쓰임- where 조건문 형태로 사용)

SELECT * 
FROM member AS m, member_detail AS md
WHERE m.name = md.host

3. DBMS 내장 함수 (단일행 함수)

벤더(오라클,mysql..등) 에서 제공하는 내장함수

- 단일행 함수: 함수의 입력값이 단일행 값이 입력됨.

- 다중행 함수: 함수의 입력값이 여러행 값이 입력됨.

(1) 문자형 처리 함수: 문자를 입력하면 문자나 숫자값을 반환한다.

- upper/lower: 대/소문자 변환

- concat: 문자열 더하기

- substring: 특정 길이의 문자열 추출

- length: 길이 반환

- 양끝 공백 제거

예시) 비밀번호 앞자리 2개 + '**' 붙여서 마스킹 처리

substring 이용해 문자열 일부 추출하고,

concat 이용해 문자열끼리 이어붙이기

distint : 중복을 제거한 값 반환

(sex, job_title) set에 대한 중복된 값은 제거하고 반환해줌.

limit : 해당 숫자만큼만 조회해서 반환해준다 (페이징에 쓰임 1부터 10까지 반환, 11부터 20까지 반환... 등)

(2) 숫자형 처리 함수: 숫자를 입력하면 숫자값을 반환한다.

- 절댓값

- 양수, 음수, 0 구분

- 나머지 값

- 올림, 버림, 반올림

- 삼각함수, 지수함수, 로그함수 등

(3) 날짜형 처리 함수: DATE 타입의 값을 연산한다.

날짜를 문자열로: date_format(date, '%Y%d%m')

문자열을 날짜로: str_to_date(string, '%Y%d%m')

날짜 연산: date_add(date, interval 1 month) 또는 date_add(date, interval -1 day)

현재 날짜의 월초/월말 구하기

(4) 변환형 처리 함수: 문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.

4. UNION

select 문 여러개 조합하여 데이터 조회할 때 사용된다. (컬럼이 모두 동일한 경우만 가능)

UNION: 중복된 값을 제거해서 알려준다.

UNION ALL: 중복된 값도 모두 보여준다.

예시)

select 컬럼명1, 컬럼명2 ... from 테이블명 where 조건

union (또는 union all)

select 컬럼명1, 컬럼명2 ... from 테이블명  where 조건

1. CREATE 이용한 테이블 생성

- 회원 테이블 생성하기

create table member
(
	name varchar(20),
	email varchar(100),
	mobile_no varchar(12),
	password varchar(50),
	marketing_yn bit,
	register_date datetime
);

- 현재 생성되어 있는 테이블 목록 확인하기

show tables;

- 테이블 내 기본키(primary key, pk) 지정하기

ALTER TABLE member 
ADD CONSTRAINT PRIMARY KEY pk_zerobase_member (email);

* 테이블 삭제하기 (거의 사용할 일이 없음)

DROP TABLE zerobase_member;

2. INSERT 문 이용한 데이터 추가(Create)

>> insert into 테이블명 (컬럼1, 컬럼2, ... - 생략 가능) values(값1, 값2, ...);

INSERT INTO member 
(name, email, mobile_no, password, marketing_yn, register_date)
VALUES ('kim', 'kim@naver.com', '01012345678','1234', TRUE, now());

INSERT INTO member 
(name, email, mobile_no, password, marketing_yn, register_date)
VALUES ('lee', 'lee@naver.com', '01012345678','1234', TRUE, now());

INSERT INTO member 
(name, email, mobile_no, password, marketing_yn, register_date)
VALUES ('park', 'park@naver.com', '01012345678','1234', TRUE, now());

3. UPDATE 문 이용한 데이터 수정(Update)

>> update 테이블명 set 변수1=값1, 변수2=값2 where 조건; 

UPDATE member 
SET 
	marketing_yn = FALSE,
	password = 1111
WHERE name = 'lee';

4. DELETE 문 이용한 데이터 삭제(Delete)

DELETE FROM member 
WHERE email = 'park@naver.com';

5. SELECT 문 이용한 데이터 조회(Read)

테이블 전체 데이터 확인하기.

>> select 컬럼명 from 테이블명

>> where 조건1 and 조건2

>> orderby 컬럼명 desc ;

SELECT * FROM member;

AND 또는 OR 로 조건들 여러개 걸 수 있다.

SELECT * FROM member
WHERE marketing_yn = TRUE
AND name = 'lee';

1. 데이터베이스 인스턴스 

(1) 생성

>> create database 이름;

>> use 이름; (데이터베이스 선택)

>> create database 이름 default character set utf8mb4; (다국어지원이 디폴트)

(2) 삭제

>> drop database 이름;

(3) 조회

>> show databases;

2. DDL 이용한 데이터베이스 계정 생성

계정 확인

>> select User, Host, Password from user;

계정 생성 [계정이름: testuser // 비밀번호: 1234]

>> CREATE USER 'testuser '@'접속가능범위' identified by '1234';

새로 만든 계정으로 접속하기 (localhost로 제한하였기에 외부에서 접속은 안된다! 범위를 %로 또 선언해주면 가능해짐.)

>> mysql -u testuser -p

3. DDL 이용한 데이터베이스 계정권한 설정

>> grant all privileges on testdb.* to 'testuser'@'localhost' identified by '1234';  (권한부여)

>> grant all privileges on testdb.* to 'testuser'@'%' identified by '1234';

>> flush privileges;  (DB에 반영)

>> remove all on testdb.* from 'testuser'@'localhost'; (권한 삭제)

>> show grants for 'tesetuser'@'localhost'; (계정에 부여된 권한 조회)

4. 테이블 정의 및 생성

(1) 테이블이란, 데이터를 저장하는 기본 단위. 

(2) 데이터 자료형

- 숫자 데이터 (정수, 실수 등)

- 문자열 데이터

- Boolean 데이터

- 날짜/시간 데이터

- 큰 객체 데이터

DB 별 명칭

(3) 테이블 생성 쿼리

>> create table 테이블명( 변수명 타입, 변수명 타입, ...);

>> desc 테이블명; (테이블 정보확인)

create table member
(
	id varchar(50),
	name varchar(20),
	password varchar(20),
	old_year int,
	birth_date date,
	sms_send_yn bit,
	register_date datetime
)

(4) 테이블 조회 쿼리

>> show tables; (테이블 목록 조회)

>> desc 테이블명; (특정테이블 정보 조회)

(5) 테이블 수정

>> alter table 테이블명 add column 컬럼명 데이터타입;

>> alter table 테이블명 modify column 컬럼명 데이터타입;

>> alter table 테이블명 chage column 기존컬럼명 변경될컬럼명 데이터타입;

>> alter table 테이블명 drop column 컬럼명;

(6) 테이블 삭제

>> drop table

1. mariadb.org 접속해서 운영체제에 맞는 버전 다운로드

( 여기서 zip 파일로 받아서 실습을 진행할 예정.)

2. 압축 풀고 원하는 경로에 위치시키기.

( c드라이브 하위에 dev 폴더 생성 후 폴더 위치 시킴)

3. mariaDB 설치하기

- cmd를 관리자 권한으로 연다

- 해당 폴더로 이동한 다음

- 설치 명령어 실행

>> mariadb-install-db.exe -S 이름 -p 비밀번호

4. 서비스 실행

>> sc start MariaDB

5. 서비스 종료

>> sc stop MariaDB

6. 현재 상태 확인

>> sc query MariaDB

7. 관리자권한이 아닌 프롬프트에서 DB에 접속하기

[필요 정보]

원격 서버에 접속하려면

- ip 주소 또는 도메인 주소

- 포트번호 DB 3306 / 웹 80,443

- 계정 및 비밀번호

- 인스턴스 

>> mysql -u root -p

-u(user)

-h(ip)

-P(port)

8. instance 확인하기

>> show databases;

>> use maysql;

9. 테이블 확인하기

>> show tables;

10. 특정 테이블 User를 보기 위해

>>select Host, User, Password from user;