CH13-12: Python with MySQL 혼자서 해봅시다
AWS RDS(Databas-1) zerobase에 접속하기
cur = conn.cursor()
sql = 'show tables'
cur.execute(sql)
result = cur.fetchall()
for i in range(len(result)):
print(result[i])
CCTV 테이블 생성하기
sql = 'create table cctv(기관명 varchar(16),\
소계 int,\
2013년도이전 int,\
2014년 int,\
2015년 int,\
2016년 int)'
cur.execute(sql, multi = True)
res = cur.fetchall()
print(res)
AWS RDS(Database-1)의 zerobase 데이터베이스의 cctv 테이블에cctv.csv 파일 데이터 insert하기
sql = 'insert into cctv values (%s,%s,%s,%s,%s,%s)'
for idx, row in df.iterrows():
cur.execute(sql, tuple(row), multi=True)
print(tuple(row))
conn.commit()
cctv테이블의 데이터를 조회하기
cur.execute('select * from cctv')
result = cur.fetchall()
for i in result:
print(i)
방금 조회한 cctv 테이블 데이터를 pandas로 변환하여 출력
CH14-01~02: Primary KEY(기본 키)
테이블의 각 레코드를 식별
중복되지 않는 고유값으로 이뤄짐
NULL 값은 포함하지 않음
테이블 당 하나의 기본키를 가짐
하나의 칼럼을 기본키로 설정하기
mysql> CREATE TABLE person(
-> pid INT NOT NULL,
-> name VARCHAR(36),
-> age INT,
-> sex CHAR(1),
-> PRIMARY KEY (pid));
Query OK, 0 rows affected (0.03 sec)
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
여러 칼럼을 기본키로 설정하기
mysql> DESC ANIMAL;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(36) | NO | PRI | NULL | |
| type | varchar(36) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
기존의 기본키를 삭제하기
mysql> ALTER TABLE person
-> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
| name | varchar(36) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
이미 있는 테이블에 primary key 설정하기
mysql> ALTER TABLE person
-> ADD PRIMARY KEY (pid);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC PERSON;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
제약조건과 함께 여러 칼럼에 PRIMARY KEY 설정하기
mysql> ALTER TABLE ANIMAL
-> ADD CONSTRAINT PK_ANIMAL
-> PRIMARY KEY (name, type);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC ANIMAL;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(36) | NO | PRI | NULL | |
| type | varchar(36) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
CH14-03~04: Foreign Key(외래키)
한테이블을 다른 테이블과 연결해주는 키임
NULL 값을 가질 수 있으며
중복값도 가질 수 있다
참조되는 테이블의 항목은 그 테이블의 기본키(혹은 단일값)
orders 테이블의 pid칼럼을 person테이블의 pid칼럼을 참조하는 외래키로 설정하기
mysql> CREATE TABLE ORDERS(
-> oid INT NOT NULL,
-> order_no VARCHAR(36),
-> pid INT,
-> PRIMARY KEY (oid),
-> CONSTRAINT FK_PERSON FOREIGN KEY (pid) REFERENCES PERSON(pid));
Query OK, 0 rows affected (0.04 sec)
mysql> DESC ORDERS;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(36) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
CREATE 를 통하여 테이블 생성시에 외래키도 함께 생성하는 경우에는 외래키 제약조건(CONSTRAINT)를 명시안해줘도 됨
mysql> CREATE TABLE JOB(
-> jid INT NOT NULL,
-> name VARCHAR(36),
-> pid INT,
-> PRIMARY KEY (jid),
-> FOREIGN KEY (pid) REFERENCES PERSON(pid));
Query OK, 0 rows affected (0.04 sec)
mysql> DESC JOB;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
CREATE TABLE 등 SQL 구문에서 컴마 찍는 기준
CREATE TABLE JOB(
jid INT NOT NULL, -- 첫 번째 컬럼 정의 후에 컴마
name VARCHAR(36), -- 두 번째 컬럼 정의 후에 컴마
pid INT, -- 세 번째 컬럼 정의 후에 컴마
PRIMARY KEY (jid), -- 테이블 수준의 제약조건(기본 키) 후에 컴마
FOREIGN KEY (pid) REFERENCES PERSON(pid));
-- 마지막 테이블 수준의 제약조건(외래 키) 후에는 컴마를 찍지 않음
주의해야 할 점은 마지막 컬럼 정의나 제약조건 뒤에는 컴마를 찍지 않는 것입니다. 컴마는 다음 항목이 있을 때만 필요하며, 마지막 항목 뒤에는 컴마를 사용하지 않습니다. 위의 CREATE TABLE 구문의 마지막 부분인 FOREIGN KEY (pid) REFERENCES PERSON(pid) 뒤에는 컴마가 없음에 주목하세요.
이 기준은 일반적인 SQL 문법에서 통용되며, MySQL을 비롯한 대부분의 관계형 데이터베이스 관리 시스템에서 적용됩니다.DDL명령어를 사용하여 테이블 생성 또는 수정시에 외래키도 같이 생성하는 경우에는 외래키 제약조건을 따로 설정할 필요가 없음
자동 생성된 외래키 제약조건 확인하기
mysql> SHOW CREATE TABLE JOB;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JOB | CREATE TABLE `job` (
`jid` int NOT NULL,
`name` varchar(36) DEFAULT NULL,
`pid` int DEFAULT NULL,
PRIMARY KEY (`jid`),
KEY `pid` (`pid`),
CONSTRAINT `job_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
CH14-04: FOREIGN KEY(외래키) 삭제하기
JOB 테이블의 외래키 제약조건 확인 후 외래키 삭제하기
mysql> ALTER TABLE JOB
-> DROP FOREIGN KEY job_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC JOB;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
JOB테이블에 pid의 key가 mul로 되어 있는 이유는 FOREIGN KEY는 삭제되었으나 FOREIGN KEY 삭제해도 INDEX로는 기능하고 있기 때문임 INDEX 까지 삭제해주려면
mysql> ALTER TABLE JOB
-> DROP INDEX pid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC JOB;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
| pid | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
기존의 테이블에 외래키 추가하고 자동으로 생성된 외래키 제약조건까지 확인하기
mysql> ALTER TABLE JOB
-> ADD FOREIGN KEY (pid) REFERENCES PERSON(pid);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE JOB;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JOB | CREATE TABLE `job` (
`jid` int NOT NULL,
`name` varchar(36) DEFAULT NULL,
`pid` int DEFAULT NULL,
PRIMARY KEY (`jid`),
KEY `pid` (`pid`),
CONSTRAINT `job_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
CH14-05~07: FOREIGN KEY 실습
POLICE_STATION과 CRIME_STATUS 테이블 사이의 관계를 설정해봅시다. AWS RDS의 zerobase에서 작업하기 , police_station 테이블의 name을 references로 하여 crime_status의 reference칼럼을 생성하고 foreign key로 지정하고 두 테이블을 조인하여 조회해보기
CH14-08: 외래키 문제풀이
다음과 같이 study_id가 primary key, parient_id가 person 테이블의 pid와 연결된 foreign key로 지정된 study 테이블 생성하기
mysql> create table study(
-> study_id int not null,
-> study_date date,
-> study_time time,
-> patient_id int,
-> primary key (study_id),
-> foreign key (patient_id) references person(pid));
Query OK, 0 rows affected (0.03 sec)
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)
생성한 테이블의 primary key 삭제하기
mysql> alter table study
-> drop primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)
생성한 테이블의 foreign key 삭제하기
mysql> alter table study
-> drop foreign key study_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
study 테이블의 patient_id를 person 테이블의 pid와 연결된 foreign key로 등록하기
mysql> alter table study
-> add foreign key (patient_id) references person(pid);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)
study 테이블의 study_id를 primary_key로 등록하기
mysql> alter table study
-> add primary key (study_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.01 sec)
CH15-01~02 : 집계함수
COUNT 함수
crime_status테이블에서 경찰서는 총 몇 군데인가?
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.01 sec)
crime_status 테이블에서 총 범죄 발생 건수는
mysql> select sum(case_number)
-> from crime_status
-> where status_type like '발생';
+------------------+
| sum(case_number) |
+------------------+
| 92679 |
+------------------+
1 row in set (0.01 sec)
crime_status 테이블에서 살인 발생 건수는
mysql> select sum(case_number)
-> from crime_status
-> where status_type like '발생' and crime_type like '%살인%';
+------------------+
| sum(case_number) |
+------------------+
| 141 |
+------------------+
1 row in set (0.01 sec)
crime_status 테이블에서 중부 경찰서의 총 검거 건수는
mysql> select sum(case_number)
-> from crime_status
-> where status_type like '검거' and police_station like '%중부%';
+------------------+
| sum(case_number) |
+------------------+
| 1406 |
+------------------+
1 row in set (0.01 sec)
CH15-03~04: COUNT, SUM 문제풀이
police_station 테이블에서 경찰서는 총 몇개이고 각 경찰서 이름 조회하기
mysql> select count(distinct name), name from police_station
-> group by name;
+----------------------+------------------+
| count(distinct name) | name |
+----------------------+------------------+
| 1 | 서울강남경찰서 |
| 1 | 서울강동경찰서 |
| 1 | 서울강북경찰서 |
| 1 | 서울강서경찰서 |
| 1 | 서울관악경찰서 |
| 1 | 서울광진경찰서 |
| 1 | 서울구로경찰서 |
| 1 | 서울금천경찰서 |
| 1 | 서울남대문경찰서 |
| 1 | 서울노원경찰서 |
| 1 | 서울도봉경찰서 |
| 1 | 서울동대문경찰서 |
| 1 | 서울동작경찰서 |
| 1 | 서울마포경찰서 |
| 1 | 서울방배경찰서 |
| 1 | 서울서대문경찰서 |
| 1 | 서울서부경찰서 |
| 1 | 서울서초경찰서 |
| 1 | 서울성동경찰서 |
| 1 | 서울성북경찰서 |
| 1 | 서울송파경찰서 |
| 1 | 서울수서경찰서 |
| 1 | 서울양천경찰서 |
| 1 | 서울영등포경찰서 |
| 1 | 서울용산경찰서 |
| 1 | 서울은평경찰서 |
| 1 | 서울종로경찰서 |
| 1 | 서울종암경찰서 |
| 1 | 서울중랑경찰서 |
| 1 | 서울중부경찰서 |
| 1 | 서울혜화경찰서 |
+----------------------+------------------+
31 rows in set (0.01 sec)
crime_status 테이블에서 status_type은 총 몇 개이고 각각 타입이 뭔지 확인하기
mysql> select status_type, count(status_type) from crime_status
-> group by status_type;
+-------------+--------------------+
| status_type | count(status_type) |
+-------------+--------------------+
| 발생 | 155 |
| 검거 | 155 |
+-------------+--------------------+
2 rows in set (0.01 sec)
crime_status 테이블에서 종로, 남대문 경찰서에서 발생한 강도건수의 합계 구하기
mysql> select sum(case_number) from crime_status
-> where (police_station in ('종로','남대문')) and status_type = '발생' and crime_type = '강도';
+------------------+
| sum(case_number) |
+------------------+
| 6 |
+------------------+
1 row in set (0.01 sec)
폭력범죄의 검거건수의 합계는
mysql> select sum(case_number) from crime_status
-> where crime_type like '%폭력%' and status_type like '%검거%';
+------------------+
| sum(case_number) |
+------------------+
| 41019 |
+------------------+
1 row in set (0.01 sec)
CH15-05: AVG(평균), MIN(최소값)
평균 폭력 검거 건수는?
mysql> select avg(case_number) from crime_status
-> where status_type like '%검거%' and crime_type = '폭력';
+------------------+
| avg(case_number) |
+------------------+
| 1323.1935 |
+------------------+
1 row in set (0.01 sec)
중부경찰서 범죄 평균 발생 건수?
mysql> select avg(case_number) from crime_status
-> where police_station like '%중부%' and status_type like '%발생%'
-> ;
+------------------+
| avg(case_number) |
+------------------+
| 411.4000 |
+------------------+
1 row in set (0.01 sec)
crime_status 테이블에서 각 경찰서별 강도 발생 건수가 10건 미만인 데이터 조회
mysql> select police_station, min(case_number) from crime_status
-> where status_type = '발생' and crime_type = '강도'
-> group by police_station
-> having min(case_number) <10;
+----------------+------------------+
| police_station | min(case_number) |
+----------------+------------------+
| 중부 | 3 |
| 종로 | 4 |
| 남대문 | 2 |
| 서대문 | 2 |
| 혜화 | 3 |
| 용산 | 6 |
| 성북 | 2 |
| 동대문 | 6 |
| 마포 | 4 |
| 영등포 | 6 |
| 성동 | 3 |
| 동작 | 1 |
| 광진 | 4 |
| 서부 | 3 |
| 강북 | 5 |
| 금천 | 7 |
| 중랑 | 5 |
| 관악 | 3 |
| 강서 | 6 |
| 종암 | 1 |
| 구로 | 5 |
| 서초 | 5 |
| 양천 | 3 |
| 노원 | 3 |
| 방배 | 1 |
| 은평 | 1 |
| 도봉 | 2 |
| 수서 | 2 |
+----------------+------------------+
28 rows in set (0.01 sec)
중부경찰서에서 가장 낮은 검거건수는 ?
mysql> select min(case_number)
-> from crime_status
-> where police_station = '중부' and status_type = '검거';
+------------------+
| min(case_number) |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
강남경찰서에서 가장 많이 발생한 범죄 건수와 범죄 종류는??
mysql> select max(case_number), crime_type
-> from crime_status
-> where police_station like '%강남%';
+------------------+------------+
| max(case_number) | crime_type |
+------------------+------------+
| 2283 | 살인 |
+------------------+------------+
1 row in set (0.01 sec)
