MySQL_d09_class02

********* 오후 수업 ********
################################# 
# ace로 접속
################################# 

C:\Users\CPB06GameN>mysql -u ace -p



################################# 
database 확인
################################# 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| acedb              |
| information_schema |
+--------------------+




################################# 
database 사용체크
################################# 

mysql> use acedb;
Database changed





################################# 
table생성 및 필드값 입력
################################# 

mysql> create table score(
    -> name char(20),
    -> kor int,
    -> mat int,
    -> eng int);
Query OK, 0 rows affected (0.03 sec)


################################# 
table생성 확인
################################# 

mysql> show tables;
+-----------------+
| Tables_in_acedb |
+-----------------+
| score           |
+-----------------+
1 row in set (0.01 sec)



################################# 
table, 필드값 속성 확인
################################# 

mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | YES  |     | NULL    |       |
| kor   | int      | YES  |     | NULL    |       |
| mat   | int      | YES  |     | NULL    |       |
| eng   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)




################################# 
root가 아니기 떄문에 ace는 mysql에 
접근권한이 없다.
################################# 

mysql> use mysql;
ERROR 1044 (42000): Access denied for user 'ace'@'%' to database 'mysql'






################################# 
트랜잭션(tranaction)
################################# 

두개 이상의 과정을 하나의 개념으로 보고
중간에 완료가 되지 않을 경우 0으로 처리해
버린다.
commit = 시행
rollback = 완료가 되지 않을경우 0으로 돌림


######################################
데이터 입력(workbench에서 실행)
######################################

insert into score values('이순신',85,87,90);
insert into score values('강감찬',75,80,95);
insert into score values('한석봉',99,98,99);
insert into score values('황진이',35,45,20);
insert into score values('안중근',90,85,90);
insert into score values('박문수',95,98,96);
insert into score values('임꺽정',15,35,45);
insert into score values('김정호',90,95,80);
insert into score values('정몽주',90,90,95);
insert into score values('오우석',50,45,60);

# 전체선택하고 번개 

select * from score;

# 한줄 실행은 Ctrl + Enter

이순신	85	87	90
이순신	85	87	90
이순신	85	87	90
강감찬	75	80	95
한석봉	99	98	99
황진이	35	45	20
안중근	90	85	90
박문수	95	98	96
임꺽정	15	35	45
김정호	90	95	80


######################################
cmd 에서 실행
######################################



mysql> select * from score;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    11
Current database: acedb

+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 이순신 |   85 |   87 |   90 |
| 이순신 |   85 |   87 |   90 |
| 이순신 |   85 |   87 |   90 |
| 강감찬 |   75 |   80 |   95 |
| 한석봉 |   99 |   98 |   99 |
| 황진이 |   35 |   45 |   20 |
| 안중근 |   90 |   85 |   90 |
| 박문수 |   95 |   98 |   96 |
| 임꺽정 |   15 |   35 |   45 |
| 김정호 |   90 |   95 |   80 |
| 정몽주 |   90 |   90 |   95 |
| 오우석 |   50 |   45 |   60 |
+--------+------+------+------+
12 rows in set (0.01 sec)

mysql> select count(*) from score;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> select * from score limit 5;
+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 이순신 |   85 |   87 |   90 |
| 이순신 |   85 |   87 |   90 |
| 이순신 |   85 |   87 |   90 |
| 강감찬 |   75 |   80 |   95 |
| 한석봉 |   99 |   98 |   99 |
+--------+------+------+------+
5 rows in set (0.00 sec)

mysql> select * from score limit 3, 3;
+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 강감찬 |   75 |   80 |   95 |
| 한석봉 |   99 |   98 |   99 |
| 황진이 |   35 |   45 |   20 |
+--------+------+------+------+
3 rows in set (0.00 sec)

mysql> desc score;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(20) | YES  |     | NULL    |       |
| kor   | int      | YES  |     | NULL    |       |
| mat   | int      | YES  |     | NULL    |       |
| eng   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select name, kor from score limit 5;
+--------+------+
| name   | kor  |
+--------+------+
| 이순신 |   85 |
| 이순신 |   85 |
| 이순신 |   85 |
| 강감찬 |   75 |
| 한석봉 |   99 |
+--------+------+
5 rows in set (0.00 sec)



######################################
조금 중요한거
######################################

mysql> select name as '이름', kor as '국어' from score limit 5;
+--------+------+
| 이름   | 국어 |
+--------+------+
| 이순신 |   85 |
| 이순신 |   85 |
| 이순신 |   85 |
| 강감찬 |   75 |
| 한석봉 |   99 |
+--------+------+
5 rows in set (0.00 sec)



######################################
총점 평균 구하기
######################################

select (kor+eng+mat) as '총점' from score;

select ((kor+eng+mat)/3) as '평균' from score;


######################################
이름, 총점, 평균 다 나오게
######################################
#1

select name as '이름', (kor+eng+mat) as '총점', 
					((kor+eng+mat)/3) as '평균' from score;
                    
#2
select name as '이름', kor as '국어', eng as '영어', mat as '수학',
(kor+eng+mat) as '총점', ((kor+eng+mat)/3) as '평균' from score;

+--------+------+------+------+------+---------+
| 이름   | 국어 | 영어 | 수학 | 총점 | 평균    |
+--------+------+------+------+------+---------+
| 이순신 |   85 |   90 |   87 |  262 | 87.3333 |
| 이순신 |   85 |   90 |   87 |  262 | 87.3333 |
| 이순신 |   85 |   90 |   87 |  262 | 87.3333 |
| 강감찬 |   75 |   95 |   80 |  250 | 83.3333 |
| 한석봉 |   99 |   99 |   98 |  296 | 98.6667 |
| 황진이 |   35 |   20 |   45 |  100 | 33.3333 |
| 안중근 |   90 |   90 |   85 |  265 | 88.3333 |
| 박문수 |   95 |   96 |   98 |  289 | 96.3333 |
| 임꺽정 |   15 |   45 |   35 |   95 | 31.6667 |
| 김정호 |   90 |   80 |   95 |  265 | 88.3333 |
| 정몽주 |   90 |   95 |   90 |  275 | 91.6667 |
| 오우석 |   50 |   60 |   45 |  155 | 51.6667 |
+--------+------+------+------+------+---------+
12 rows in set (0.00 sec)

==============================================


###############################################
데이터 재입력
(다 지우고 INSERT 한번 더 하기)
###############################################
delete from score;

insert into score values('이순신',85,87,90);
insert into score values('강감찬',75,80,95);
insert into score values('한석봉',99,98,99);
insert into score values('황진이',35,45,20);
insert into score values('안중근',90,85,90);
insert into score values('박문수',95,98,96);
insert into score values('임꺽정',15,35,45);
insert into score values('김정호',90,95,80);
insert into score values('정몽주',90,90,95);
insert into score values('오우석',50,45,60);

#결과물
mysql> select * from score;
+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 이순신 |   85 |   87 |   90 |
| 강감찬 |   75 |   80 |   95 |
| 한석봉 |   99 |   98 |   99 |
| 황진이 |   35 |   45 |   20 |
| 안중근 |   90 |   85 |   90 |
| 박문수 |   95 |   98 |   96 |
| 임꺽정 |   15 |   35 |   45 |
| 김정호 |   90 |   95 |   80 |
| 정몽주 |   90 |   90 |   95 |
| 오우석 |   50 |   45 |   60 |
+--------+------+------+------+
10 rows in set (0.00 sec)





###########################################
정렬하기
###########################################

select * from score order by name asc;
#순서대로(기본값적용)
+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 강감찬 |   75 |   80 |   95 |
| 김정호 |   90 |   95 |   80 |
| 박문수 |   95 |   98 |   96 |
| 안중근 |   90 |   85 |   90 |
| 오우석 |   50 |   45 |   60 |
| 이순신 |   85 |   87 |   90 |
| 임꺽정 |   15 |   35 |   45 |
| 정몽주 |   90 |   90 |   95 |
| 한석봉 |   99 |   98 |   99 |
| 황진이 |   35 |   45 |   20 |
+--------+------+------+------+
10 rows in set (0.00 sec)




select * from score order by name desc;
#역순(desc)
+--------+------+------+------+
| name   | kor  | mat  | eng  |
+--------+------+------+------+
| 황진이 |   35 |   45 |   20 |
| 한석봉 |   99 |   98 |   99 |
| 정몽주 |   90 |   90 |   95 |
| 임꺽정 |   15 |   35 |   45 |
| 이순신 |   85 |   87 |   90 |
| 오우석 |   50 |   45 |   60 |
| 안중근 |   90 |   85 |   90 |
| 박문수 |   95 |   98 |   96 |
| 김정호 |   90 |   95 |   80 |
| 강감찬 |   75 |   80 |   95 |
+--------+------+------+------+
10 rows in set (0.00 sec)


#############################################
오후 수업 마지막 핵심 내용
#############################################
select  user();
use mysql;
use acedb;
show tables;
create table s(
	id int auto_increment primary key,
	name varchar(20),
    kor tinyint,
    eng tinyint,
    mat tinyint);
    
desc s;
insert into s(name, kor, eng, mat) values('이순신',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 * from s limit 5;
select * from s where  kor > 80;
select * from s where  mat <= 50;
select * from s where  mat <= 50 or kor <= 50;
select * from s where  mat <= 50 and kor <= 50;