Database/SQL 기초
flask RestAPI개발
cloud computing
AWS 클라우드 활용
스키마 생성
PK: 테이블당 1개만 존재 가능,
Unique한 값 (Not Null 필수) //데이터 반드시 필수
- NN: Not Null, 데이터가 반드시 저장되어야 함 -
UQ: Unique한 값 (Null 가능) -
UN: Unsigned -+부호만 있음
AI: Auto Increment - 데이터 들어갈때 자동으로 숫자가 올라가서 카운팅
Default: 디폴트 값
테이블 생성
테이블 생성
[root@server1 ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> create database my_db;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use my_db
Database changed
MariaDB [my_db]> CREATE TABLE mytable(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> ouccupation VARCHAR(20) NULL,
-> height SMALLINT,
-> profile TEXT NULL,
-> date DATETIME,
-> CONSTRAINT testTable_PK PRIMARY KEY(id)
-> );
MariaDB [my_db]> show tables;
+-----------------+
| Tables_in_my_db |
+-----------------+
| mytable |
+-----------------+
1 row in set (0.001 sec)
MariaDB [my_db]> DESC mytable;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| ouccupation | varchar(20) | YES | | NULL | |
| height | smallint(6) | YES | | NULL | |
| profile | text | YES | | NULL | |
| date | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.002 sec)
테이블 변경
컬럼추가
ALTER TABLE table_name ADD COLUMN column_name data_type ;
컬럼 드롭
ALTER TABLE table_name DROP COLUMN column_name ;
테이블 삭제
DROP TABLE table_name ;
데이터 입력 -pn인경우는 반드시 입력 그 외에는 다 입력 안넣어도됨
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
INSERT INTO table_name VALUES (value1, value2, value3, ...) //전체다 입력할때
데이터 조회
SELECT column1, column2, ... FROM table_name;
SELECT * FROM table_name;
갯수 조회
컬럼 타입 조회
SHOW COLUMNS FROM customers
테이블 조회
SHOW TABLES;
비교 연산자
AND/OR 연산자
UPDATE -기존데이터 수정
DELETE 삭제 -JP 국적인 사람데이터를 삭제
오름차순/내림차순
TOP-N 데이터 조회
IN 연산자 -(연산자 OR 개념과 같다)
between 연산자 특정 범위를 나오게 할 때
SELECT DISTINCT 문 -엑셀의 중복제거와 같다. KR , JP 국가인 여라사람중에 2개만 나오게..
IS NULL, IS NOT NULL 연산자로 null인 또는 null이 아닌 데이터만 조회
- 특정 컬럼이 Null인 모든 데이터를 제거
- Null Value를 Default 값으로 모두 대체
LIKE 연산자
문자열 안에서 특정 패턴을 검색하기 위해 사용
문자열의 검색 패턴을 위해 % 기호를 사용 - WHERE column_name LIKE 'a%' : "a"로 시작하는 문자열을 검색
- WHERE column_name LIKE '%a' : "a"로 끝나는 문자열을 검색
- WHERE column_name LIKE '%a%‘ : "a"가 들어있는 문자열을 검색
- WHERE column_name LIKE 'a%o‘ : "a" 로 시작하고 "o"로 끝나는 문자열을 검색
Alias
- 컬럼 명에 별칭을 붙여서 조회 가능
COUNT 함수
- 조회 된 데이터의 개수를 계산하는 함수
- 조건을 명시 가능
- Null Value는 카운트 하지 않
SUM 함수
- 총 합을 계산하는 함수 - ‘*’ 를 사용할 수 없고 컬럼 명을 명시해야 함
MIN / MAX 함수
- MIN은 컬럼의 최소 값을 계산하는 함수
- MAX는 컬럼의 최대 값을 계산하는 함수
AVG 함수
- 특정 컬럼의 평균 값을 구하는 함수
VARIANCE: 특정 컬럼의 분산 계산
- STDDEV: 특정 컬럼의 표준편차 계산
- 분산과 표준편차는 데이터들이 평균에서 얼마나 벗어나 있나를 측정하는 지표
Group by
- 데이터를 그룹으로 나누어 분석
- 한 개 이상의 컬럼으로 그룹화 가능
HAVING
- 그룹화 한 결과를 필터링
- 그룹화 전 필터링은 WHERE, 그룹화 후 필터링은 HAVING
디폴트 스키마 - 해당 스키마로 변경가능 use [scheme_name]
SQL JOIN
- 두 개 이상의 테이블을 결합 가능
- Column을 확장하기 위한 용도
- 사용자의 이름은 customer 테이블에 있고, 사용자의 구매정보는 order 테이 블에 있을 경우, 사용자 이름 별 구매 정보를 조회하고 싶다면 Join 필요
- Primary Key
- 테이블에 1개 (1개의 column) 만 정의 가능
- Primary Key Column은 값이 중복될 수 없음
- Primary Key Column은 Null이 입력될 수 없음
- PK, 주키, 기본키 등으로도 칭함
- Foreign Key
- 다른 테이블의 Primary Key를 참조함
- 참조 되는 테이블을 부모 테이블, 참조 하는 테이블을 자식 테이블
- 부모 테이블에 제약 조건을 걸 수 있음
- 외래키, 참조키 등으로도 칭함
INNER JOIN (교집합)
- 두 개의 테이블에서 공통 된 값이 있는 데이터 만 조회
LEFT (OUTER) JOIN (A집합)
- 왼쪽에 위치한 테이블을 기준으로 오른쪽의 테이블의 데이터를 붙임
- 왼쪽 테이블의 데이터는 모두 조회 됨
- 오른쪽 테이블의 데이터는 Null 가능
- person을 기준으로 car 데이터를 Left Join
RIGHT(OUTER) JOIN (B집합)
basic은 스키마로이렇게도 가능
constraint <FK_이름> FOREIGN KEY (자식테이블 열이름) REFERENCES <부모테이블이름>(부모 테이블의 열 이름)
ex: constraint id_pk primary key(id);
FOREIGN KEY 생성
CONSTRAINT <FK_이름> FOREIGN KEY (자식테이블의 열이름) REFERENCES<부모테이블 이름> (부모테이블의 열이름)
constraint fk_region foregin key (region_id) references parent (region_id)
####코드입력####
create table parent(
region_id int not null,
region varchar(10) not null,
primary key (region_id)
) engine = innodb default character set = utf8;
insert into parent (region_id, region) values (1,'서울'),(2,'목포'),(3,'해남');
create table child(
cid int not null auto_increment,
region_id int not null,
name varchar(10) not null,
primary key (cid),
constraint fk_region foregin key (region_id) references parent (region_id)
) engine = InnoDB default character set=utf8;
drop table parent; //했을 시 오류남,child 삭제 하지 않는한 parent 삭제 못함,
//부모테이블 값 1,2,3,이외 지역을 자식 테이블에 저장 할 수 없음
insert into child(region_id, name) values(1,'일순이'),(2,'이순이'),(3,'삼순이'),(4,'사순이') //에러남
//이유는 부모테이블의값이 3까지만 있기때문 즉, 4가 있어서 에러남
insert into child(region_id, name) values(1,'일순이'),(2,'이순이'),(3,'삼순이'),(2,'사순이')//정상 입력됨
FOREIGN KEY 삭제
ALTER TABLE <테이블 이름> DROP CONSTRAINT <FK_이름>;
alter table child drop constraint fk_region;
FOREIGN KEY 추가
ALTER TABLE <테이블 이름> ADD CONSTRAINT <FK_이름> FOREIGN KEY ( 자식테이블 열 이름) REFERENCES <부모테이블 이름> (부모테이블 열 이름)
alter table child add constraint fk_region foreign key (region_id) references parent (region_id);
외부 접속 안될때
root는 localhost만 등록 되어 있어서 계정 새로 생성함(ip : "x.x.x.%" 는 x.x.x.0 으로 나머지255에 대해서 허용함.
모든IP 허용
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%' IDENTIFIED BY '패스워드';
INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('%','아이디',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'%';
FLUSH PRIVILEGES;
IP대역허용
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%' IDENTIFIED BY '패스워드';
INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.%','아이디',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.%';
FLUSH PRIVILEGES;
특정ip1개 허용
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44' IDENTIFIED BY '패스워드';
INSERT INTO mysql.user (host,user,password,ssl_cipher, x509_issuer, x509_subject) VALUES ('111.222.33.44','아이디',password('패스워드'),'','','');
GRANT ALL PRIVILEGES ON *.* TO '아이디'@'111.222.33.44';
FLUSH PRIVILEGES;
권한 원래대로 복구
DELETE FROM mysql.user WHERE Host='%' AND User='아이디'; FLUSH PRIVILEGES;
외부 접속
MariaDB [my_db]> grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.002 sec)