CREATE, INSERT, SELECT 예제

(mission1)
CREATE DATABASE pokemon;

/*ctrl+enter =실행하기*/

USE pokemon;
CREATE TABLE mypokemon(
number INT,
name VARCHAR(20),
type VARCHAR(10)
);

/*ctrl+enter =실행하기*/

/*USE 사용안하기 -> 매번 pokemon. 적어야해서 불편*/
CREATE TABLE pokemon.mypokemon(
number INT,
name VARCHAR(20),
type VARCHAR(10)
);

INSERT INTO mypokemon(number, name, type)
VALUES (10, 'caterpie', 'bug'),
(25, 'pikachu', 'electric'),
(133, 'eevee', 'normal');

/*ctrl+enter =실행하기*/
SELECT * FROM mypokemon; --테이블 전체를 조회하는 쿼리

 

 

(mission2)

USE pokemon; --이 데이터베이스 사용하겠습니다.
CREATE TABLE mynewpokemon(
number INT,
name VARCHAR(20)
type VARCHAR(40)
);

INSERT INTO mynewpokemon(number,name,type)
VALUES (77, '포니타', '불꽃'),
(132, '메타몽', '노말'),
(151, '뮤', '에스퍼');

SELECT * FROM mynewpokemon;

 

(mission1) 테이블을 변경하고 지워보자

USE pokemon;
--ctrl+enter : 실행하기

ALTER TABLE mypokemon RENAME myoldpokemon;

--CHANGE COLUME : 컬럼 이름만 바꿀순 없고, 타입도 함께 적어줘야함
ALTER TABLE myoldpokemon CHANGE COLUME name eng_nm VARCHAR(20);

ALTER TABLE mynewpokemon CHANGE COLUME name kor_nm VARCHAR(20);

SELECT * FROM mynewpokemon;

 

(mission2) 테이블을 지워보자

USE pokemon;
--ctrl+enter

SELECT * FROM myoldpokemon;

--테이블은 살리고 값만 지우기
TRUNCATE TABLE myoldpokemon;

SELECT * FROM myoldpokemon;

SELECT * FROM mynewpokemon;

--테이블 전체 지우기
DROP TABLE mynewpokemon;

SELECT * FROM mynewpokemon; --ctrl+enter시 에러발생

mission7)

SELECT DISTINCT height
FROM mypokemon;

mission 8)

SELECT name, attack*2 AS attack2, attack
FROM mypokemon;

mission 9)
SELECT name AS 이름
FROM mypokemon;

mission 10)
SELECT attack 공격력, defense 방어력
FROM mypokemon;

mission 11)
SELECT height*100 AS 'height(cm)'
FROM mypokemon;

mission 12)
SELECT *
FROM mypokemon
LIMIT 1;

mission 13)
SELECT name 영문명, height '키(m)', weight '몸무게(kg)'
FROM mypokemon
LIMIT 2;

mission 14)
SELECT name, attack+ defense+speed AS total
FROM mypokemon;

mission 15)
SELECT name, weight/height^2 AS BMI
FROM mypokemon;