MySQL_d10_class01

# 2021 0517(월요일)

************************************
# 캐글(튜토리얼 개꿀잼)
https://www.kaggle.com/colinmorris/hello-python
************************************

database
-table
-view
보기 편하게 따로 만들어둔것
(g마켓 구매목록같은것)
-index
-fucntion
-sored precedure
-triger
(엑셀함수 작동원리 같은거)
(입력값을 바꾸면 탕!하고 결과에 반영)

/*root 고유권한
create database mydb;
drop database mydb;
*/

-- 한줄 주석

**********************************
use acedb;
show tables;
create table Man(
	name char(20),
  year int);
    
    
create table if not exists Man(
	name char(20),
  year int);    
    
desc Man;

drop table Man;
show tables;

ALTER(수정)

테이블명 수정
alter table Man rename Person;
필드추가
alter table Man add habit char(20);
필드삭제
alter table Man drop habit char(20);

INSERT(추가)

데이터 추가(순서 바뀌어도 괜찮)
insert into Man(name, year) values('kim', 2000);
insert into Man(year, name) values(1995, 'choi');

데이터만 지우기
(테이블은 있다)
delete from Man;


************************************

https://www.w3schools.com/mysql
MySQL Constraints

SQL constraints are used to specify
rules for the data in a table. Constraints
are used to limit the type of data that can
go into a table. This ensures the accuracy
and reliability of the data in the table.
If there is any violation between the
constraint and the data action, the action
is aborted.

Constraints can be column level or table
level. Column level constraints apply to
a column, and table level constraints
apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
(공백을 허용x)
UNIQUE - Ensures that all values in a column are different

PRIMARY KEY 
- A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
(기본키 : 고유번호 : 주민등록번호, 학번, 회원번호)
(기본키는 공백x : not null, 같은값x : unique)

FOREIGN KEY 
- Prevents actions that would destroy links between tables
(link)

CHECK 
- Ensures that the values in a column satisfies a specific condition

DEFAULT 
- Sets a default value for a column if no value is specified

CREATE INDEX 
- Used to create and retrieve data from the database very quickly




************************************
관계형 DB(RDB)
- TABLE TABLE 간 관계를 통한 DB관리

JOIN
-두개 이상의 TABLE에서 원하는 필드값을
가져와서 출력하는것





***********************************

DML
-inser into table
-update set table
-delete table
-select field ,,,, from table
where
limit
order by


DCL
-grant
-revoke
-commit
-rollback

-transaction : 트랙잭션 : 작업의 단위
은행송금(all or nothing)




***********************************
id(primary key 자동상승잼)

create table if not exists s(
	id int auto_increment primary key,
	name varchar(20),
    kor tinyint,
    eng tinyint,
    mat tinyint);

# id값(primary key)이 자동으로 입력된다.


desc s;
insert into s(id, name, kor, eng, mat) values(101,'이순신',85,87,90);
insert into s(name, kor, eng, mat) values('강감찬',75,80,90);
insert into s(name, kor, eng, mat) values('한석봉',99,98,99);
insert into s(name, kor, eng, mat) values('황진이',35,45,20);
insert into s(name, kor, eng, mat) values('안중근',90,85,90);
insert into s(name, kor, eng, mat) values('박문수',95,98,96);
insert into s(name, kor, eng, mat) values('임꺽정',15,35,45);
insert into s(name, kor, eng, mat) values('김정호',90,95,80);
insert into s(name, kor, eng, mat) values('정몽주',90,90,95);
insert into s(name, kor, eng, mat) values('양주종',50,45,60);



***********************************
select name, eng from s;
select name, eng from s limit 5;
select name, eng from s limit 5,3;
select name, eng from s order by name;
select name, eng from s order by name asc; -- 기본값
select name, eng from s order by name desc; -- 반대

-- 기본값(영어가 동점일 경우 수학점수 순으로)
select name, eng, mat from s order by eng desc, mat desc;


select name as '이름', kor as '국어' from s;
select name as '이름', min(kor) as '국어' from s;
select max(kor), min(kor), sum(kor), avg(kor) from s;
-- 집계함수

-- 총점과 평균
select name, (kor+eng+mat) as '총점' from s;
select name, (kor+eng+mat)/3 as '평균' from s;
select name, (kor+eng+mat) as '총점',
			 (kor+eng+mat)/3 as '평균' from s;
			 
			 
			 
			 

***********************************
-- 총점과 평균만으로 새로운 테이블 만들기
create table s2(select name, (kor+eng+mat) as '총점',
			 (kor+eng+mat)/3 as '평균' from s);
             
show tables;
select * from s2;

create table if not exists s5(
	name char(20),
    total int,
	avg float);
	
	
	
	
***********************************
-- s의 데이터 s5에 넣기    
insert into s5
	select name, kor+eng+mat, (kor+eng+mat)/3.0 from s;
	
	
		

************ 파이썬 응용 **************

>>> man = ['이순신', '황진이', '한석봉']
>>> job = ['군인','가수','공무원']
>>> man
['이순신', '황진이', '한석봉']
>>> job
['군인', '가수', '공무원']
>>> zip(man, job)
<zip object at 0x0000024F5031DEC0>
>>> list(zip(man, job))
[('이순신', '군인'), ('황진이', '가수'), ('한석봉', '공무원')]
>>> k2 = list(zip(man, job))
>>> k2
[('이순신', '군인'), ('황진이', '가수'), ('한석봉', '공무원')]
>>> dict(zip(man, job))
{'이순신': '군인', '황진이': '가수', '한석봉': '공무원'}
>>> d2 = dict(zip(man, job))
>>> d2
{'이순신': '군인', '황진이': '가수', '한석봉': '공무원'}

>>> d2.keys()
dict_keys(['이순신', '황진이', '한석봉'])
>>> d2.values()
dict_values(['군인', '가수', '공무원'])
>>> d2.items()
dict_items([('이순신', '군인'), ('황진이', '가수'), ('한석봉', '공무원')])



******* 자동으로 인덱스 만들어줌 ***********
>>> for i, j in enumerate(man):
	print(i, j)

	
0 이순신
1 황진이
2 한석봉

>>> list(enumerate(man))
[(0, '이순신'), (1, '황진이'), (2, '한석봉')]
>>> dict(enumerate(man))
{0: '이순신', 1: '황진이', 2: '한석봉'}
>>> 


***********************************