29일차 - SQL 실습

Date : 2023-07-28

Topic : MySQL에서 SQL Query 작성해보기

---

Note

테이블 생성

USE customer_db;
CREATE TABLE customer_db (
	no INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	age TINYINT,
	phone VARCHAR(20),
    email VARCHAR(30) NOT NULL,
    address VARCHAR(30) NOT NULL,
	PRIMARY KEY(no)
	);

/* 컬럼 추가 */
ALTER TABLE customer_db ADD COLUMN hobby VARCHAR(20);
/* 컬럼 타입 수정 */
ALTER TABLE customer_db MODIFY COLUMN name VARCHAR(20);
/* 컬럼 변경 (폰에서 모바일로) */
ALTER TABLE customer_db CHANGE COLUMN phone mobile VARCHAR(30);
/* 컬럼 삭제 */
ALTER TABLE customer_db DROP COLUMN hobby;

/* 테이블 구조를 확인할 때 DESC [테이블명] */
DESC customer_db;

출력

- UNSIGNED : 음수 제외한 정수 타입만

- AUTO_INCREMENT : 새 레코드가 테이블에 삽입될 때 열에 대해 고유한 값을 자동으로 생성 (각 레코드에 고유 식별

가 있는지 확인하기 위해 일반적으로 기본 키에 사용) 

---

연습 문제1 (데이터 검색)

CREATE TABLE mytable (
       id INT UNSIGNED NOT NULL AUTO_INCREMENT,
       name VARCHAR(20) NOT NULL,
       model_num VARCHAR(10) NOT NULL,
       model_type VARCHAR(10) NOT NULL,
       PRIMARY KEY(id)
);
INSERT INTO mytable (name, model_num, model_type) 
VALUES ('i5', '13400F', '랩터레이크');
INSERT INTO mytable (name, model_num, model_type) 
VALUES ('i5', '12400F', '엘더레이크');
INSERT INTO mytable (name, model_num, model_type) 
VALUES ('i5', '13400', '랩터레이크');
INSERT INTO mytable (name, model_num, model_type) 
VALUES ('i3', '13500', '랩터레이크');
INSERT INTO mytable (name, model_num, model_type) 
VALUES ('i5', '13500', '랩터레이크');

SELECT * FROM mytable;

- EX1: model_num 이 13400 으로 시작하는 로우(Row) 검색하기

SELECT * FROM mytable WHERE model_num LIKE '13400%';

 

- EX2: name 이 i5 인 로우(Row) 검색하기

SELECT * FROM mytable WHERE name LIKE '%i5%';

 

- EX3: model_type 이 엘더레이크 인 로우(Row) 를 1개만 검색하기(LIMIT 사용)

SELECT * FROM mytable WHERE model_type LIKE '%엘더레이크%' LIMIT 1;

 

연습문제2 (테이블 수정, 데이터 수정, 검색)

- EX1 : lowest_price(컬럼명) INT UNSIGNED(데이터 타입) 으로 컬럼 추가

ALTER TABLE mytable ADD COLUMN lowest_price INT UNSIGNED;

- EX2 : 웹페이지(https://www.enuri.com/list.jsp?cate=070701)에서 1~5위까지 lowest_price 값 수정하기

• lowest_price값은 정품 최저 가격으로 입력
• 예시) 222,180원 → 222180

/* id값을 기준으로 조건을 걸어 최저가 데이터 수정하기 */
UPDATE mytable SET lowest_price = 254310 WHERE id = 1;
UPDATE mytable SET lowest_price = 171910 WHERE id = 2;
UPDATE mytable SET lowest_price = 285920 WHERE id = 3;
UPDATE mytable SET lowest_price = 150810 WHERE id = 4;
UPDATE mytable SET lowest_price = 313700 WHERE id = 5;

- EX3 : lowest_price 이 300000 이하인 로우(Row) 중에서 name과 model_num만 검색하기
. 이하는 같거나, 작은 값을 의미하고, 조건으로는 <= 와 같이 작성한다.

SELECT name, model_num FROM mytable WHERE lowest_price <= 300000;

name, model_num만 출력하라고 했지만 조건값 성립을 확인하기 위해 lowest_price까지 출력

 

- EX4 : lowest_price 이 400000 이상인 로우(Row) 만 검색하기
. 이상은 같거나, 큰 값을 의미하고, 조건으로는 >= 와 같이 작성한다.

SELECT * FROM mytable WHERE lowest_price >= 400000;

---

DCL(Data Control Language)

▶️SQL DCL 명령은 mysql 관리자(데이터베이스 관리자)를 위한 명령이므로, 가볍게 알아두기!

 

mysql 사용자 확인, 추가, 비밀번호 변경, 삭제

/* mysql 사용자 확인 */
# mysql -u root -p
use mysql;
select * from user;

/* 사용자 추가 */
# mysql -u root -p
use mysql;

/* 1) 로컬에서만 접속 가능한 userid 생성 */
create user 'userid'@localhost identified by '비밀번호';
/* 2) 모든 호스트에서 접속 가능한 userid 생성 */ 
create user 'userid'@'%' identified by '비밀번호';

/* 사용자 비밀번호 변경 */
SET PASSWORD FOR 'userid'@'%' = '신규비밀번호';

/* 사용자 삭제 */
# mysql -u root -p
use mysql;
drop user 'userid'@'%';

 

mysql 접속 허용 관련 설정

/* 현재 부여된 권한 확인하기 */
SHOW GRANTS for 아이디;
예) SHOW GRANTS for 'davelee'@'%'

/* 로컬에서만 접속 허용 */
GRANT ALL ON DATABASE.TABLE to 'root'@localhost;

/* 특정 권한만 허용 */
GRANT SELECT, UPDATE ON DATABASE.TABLE to
'root'@localhost;

/* 옵션 상세
ALL – 모든 권한 / SELECT, UPDATE – 조회, 수정 권한등으로 권한 제한 가능
예) GRANT INSERT,UPDATE,SELECT ON *.* TO
'username'@'localhost';
(2) DATABASE.TABLE – 특정 데이터베이스에 특정 테이블에만 권한을 줄 수 있
음 / *.* – 모든 데이터베이스에 모든 테이블 권한을 가짐
(3) root – 계정명
(4) funcoding – 계정 비밀번호 */

연습

use mysql;
select * from user;
create user '아이디'@localhost identified by '비밀번호';
GRANT ALL ON *.* to '아이디'@localhost;

---

SQL 실습은 매번 DDL, DML만 하다가 DCL은 처음 만져봤는데 연습해본다고 이것저것 많이 실행을 했더니 유저 아이디가 여러 개 생성 됐는데 삭제가 안돼서 당황스럽다..ㅎㅎ 왜 쿼리 그대로 작성하는데 나는 실행이 안되는건지 sql이든 파이썬이든 배울 때마다 의문이 든다. 초심자 코드의 신비...ㅠㅠ 주말에 다시 열심히 복습하고 ! 강사님께 질문할 것도 많이 생각해봐야겠다. SQL 첫 수업 끝⭐