[SQL] 데이터 가져오기

SELECT ~ FROM

Use 사용 안할 시엔 DB명을 특정해주어야 함 -> DB이름.테이블명

여러 컬럼시 -> select 컬럼 이름1, 컬럼이름 2, ... ~ from 테이블명

전체 컬럼시 -> select * from 테이블명

AS(별칭)

• AS [컬럼 별명] 형식으로 사용

• 테이블 내의 실제 컬럼 이름은 변하지 않으며, 별명은 쿼리 내에서만 유효

• 만약 실제 컬럼 이름을 변경하고 싶다면, ALTER TABLE 구문 사용

LIMIT

LIMIT[로우 수]

DISTINCT

중복된 데이터는 제외하고 같은 값은 한번만 가져오도록 하는 옵션

DISTINCT[칼럼 이름]

코드 예시

DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (

number int,
name varchar(20),
type varchar(20),
height float, ## 입력값을 근사치로 저장;
weight float,
attack float,
defense float,
speed float
);

INSERT INTO mypokemon (number, name, type, height, weight, attack, defense, speed)
VALUES (10, 'caterpie', 'bug', 0.3, 2.9, 30, 35, 45),
(25, 'pikachu', 'electric', 0.4, 6, 55, 40, 90),
(26, 'raichu', 'electric', 0.8, 30, 90, 55, 110),
(133, 'eevee', 'normal', 0.3, 6.5, 55, 50, 55),
(152, 'chikoirita', 'grass', 0.9, 6.4, 49, 65, 45);

## 가져오기 예시
select 123*456;
select 2310 / 30;

select "피카츄" AS "포켓몬";

select * from mypokemon;

select name from mypokemon;

select name,height,weight from mypokemon;

select distinct height from mypokemon;

select attack*2 as attack2 from mypokemon;

select name as '이름' from mypokemon;

select attack as '공격력', defense as '방어력' from mypokemon;

select height * 100 as 'height(cm)' from mypokemon;

select * from mypokemon limit 1;

select name as '영문명', height as '키(m)', weight as '몸무게(kg)' from mypokemon limit 2;

select name as '이름', attack+defense+speed as 'total' from mypokemon;

select name as '이름', weight / height^2 as 'BMI지수' from mypokemon;