5월 21일 select문 실습(order by, distinct, where, offset

<Dataytepe(자료형)>

● 새로운 DBMS를 접했을때 가장 먼저 확인 할 사항 중 하나

● DBMS 마다 조금씩 차이가 있음

● 미리 잘효형을 선언 -> 효율적인 자료의 저장 / 이용(검색)

<SQLite2 Datatype>

● Datatype 선언을 (필요로) 하지 않음

● 입력갑에 따라 일정한 규칙에 의하여 자료형을 스스로 선택.

● 구체적으로 명시된 자료형을 이용하여 내부적인 자료형을 선택.

<SQLite3 Datatype - 5 Datatype>

● NULL    : 값이 정해지지 않음, 값이 없음.

● INTEGER : 정수(양수 / 음수), 1/2/3/4/6/8/ byte 크기

● REAL    : 실수(유리수 / 무리수), 8byte 크기

● TEXT    : 문자열, 크기 무한대(메로리, 저장용량)

● BLOB    : 이진자료(Binary Large OBject), 크기 무한대

<SQL DDL>

CREATE TABLE contacts(

           contact_id integer PRIMARY KEY ,

           first_name text   NOT NULL    ,

           last_name  text  NOT NULL    ,

           email text NOT  NULL UNIQUE ,

           phone text NOT NULL UNIQUE

);

이메일(email)테이블은 이메일(email_id)를 주키(primary key)로 가진다.

이메일(email)은 보내는 사람, 받는 사람, 제목, 내용으로 구성되어 있다.

제목을 제외한 항목은 NULL값을 가진다. 

NOT NULL    (이름이나 성이 하나라도 들어가야 됨, 즉 생략을 하지 않겠다는 뜻

NULL UNIQUE (DB에 동일한 이름이 있는 경우, error메시지를 뜨윔. 즉, 연락처나 mail이 

             중복되지 않게 하는 것이다.)

NOT EXISTS = 미리 정의되어 있으니 이걸 사용해야 된다라고 알려주는 속성.

<SQLite DDL - Attribute 조건>

● PRIMARY KEY

● UNIQUE

● NOT NULL

● DEFAULT  

 

● SQLite   

"." = DB 명령어

위의 내용을 입력하면 test_email이라는 DB 테이블이 생성된다. 이부분은 나중에 자세히 포스팅.

 

.schema 테이블 네임 = table에 내용을 간략하게 볼수 있는 명령어.

 

 

 

 

headers : off (제목, 타이틀

 

.mode   : 출력형태의 지정.

 

.stat   : 통계, 메모리, 쿼리 on/off시키는 스위지.

 

--------------- 간단한 개론 ---------------

';'입력하면 빠져나온다.

사칙연산 +.-.*,/,%(나머지), <, >, >= , <=

비교  = ex) select 5=5, 1출력, 5=4; 0을 출력.

출력할 데이터, 출력의 형태를 결정.

--------------- 간단한 개론 ---------------

select 

select title from albums;

select title artists, title form albums; 순서 아티스트, 타이틀 

select artists, title, artistid form albums; 타이틀, 타이틀

select artists*5 from albums;

 

 

 고객(customer)의 이름(FirstName)과 도시(City)의 목록

 -  select firstname, city from customers;

select문으로 custormer내부에 있는 firstname, city의 정보를 불러온 화면.

 

아래그림은 playslists에 모든 내용을 그냥 출력.

 플레이리스(playlists)의 목록

 - select * from playlists;

 

 

select genres로부터 genreid, name만 내용을 출력한다.

<!--[endif]-->

 장르 이름, id 순으로 정리된 내역 (id, 이름순으로 )

 - select GenreID, Name      from Genres;

 - select Name   ,  GenreID  from Genres;

바꿔서 name과 generid를 바꾸서 입력하면 바꿔서 출력한다.

 

테이블 내용 정렬하기 

 

select name , trackid, albumID from tracks;

 

select name , trackid, albumID from tracks

- order by name asc;     오름차순

- order by trackid desc; 내림차순

- order by name, trackid desc; 조건 2개를 줄수 있음. 

: 1차적으로 이름 정력, 2차적으로 trackid로 정렬. (같은 이름인 경우)

 

장르의 이름 순선로 오름차순 정려된 목록

- select name from genres order by name asc;

 

 

 

장르의 아이디를 기준으로 내림차순 정렬된 목록

- select name from genres order by name desc;

 

 

트랙의 앨범 id와 장르 id를 출력하면서,

앨범 아이디를 오름차순으로 먼저 정렬하고,

또 장르 아이디를 내림차순으로 정렬한 목록

 

 select city form custormer;

 

 고객이 살고 있는 국가의 목록(중복없으며, 오름차순 정렬)

 select distinct country from customers order by country asc;

 

 

 

 

 

 -  select albumid, genreid from tracks order by albumid asc, genreid desc;

distinct(중복제거 select)

 selcet distinct(중복제거 select)

 

 

 고객이 살고 있는 국가의 목록(중복없으며, 오름차순 정렬)

 select distinct country from customers order by country asc;

 

 

(조건, 필터링)

 

select tile from alubms where artists < 100;

 

select tile form alubms where artists < 'B'; 

 

select tile form alubms where artists < 100 and title < 'B';

select tile form alubms where artists < 100;

 

문자열을 조건걸때는 '를 사용한다. 이점만 주의 하면 된다. 

<select where>

where는 조건이다. 조건을 주어 조건에 맞는 내용을 출력이 가능하다.

다음은 artistid가 100보다 작고, title제목이 B문자보다 작은 앨범을 찾는것이다. 이것을 동시에 만족시키기 위하여 

and를 사용함. 

장르 아이디가 10이하인것을작은 것 출력. 이하이기 때문에 10도 포함.

아래는 city가 런던인곳만 출력.

 1|MPEG audio file, 

 6|Blues

Blues, MPEG audio file, id를 알고 있기 때문에 track내에서 id로 조건을 건다. 아직 index를 배우지 않았기

때문에 번거로운 과정을 지닌다. 이번시간에는 활용을 연습하는 차원에서 포스팅한 것이다. 

IN(값의 범위)

select albumid, title from albums where albumid=1 or albumid = 2;

select albumid, title from albums where albumid in(1,2); // 2번째 형식이 좀 더 간략하다. 

이것이 in의 활용이다. 길게 사용하는거를 축약할 수 있다.

not in을 사용하면 아래와 같이 1,2,5를 제외하고 전부 출력된다.

아래 그림도 내용은 같다

< 패턴매칭>

like가 동사로 좋아하다이지만, 동사가 아님 부사로 ~처럼이라는 뜻도 있다. 여기서는 ~처럼으로 뜻으로 명령어가 사용 됨.

select albumid, tile from albums whree title like 'A%'; = A로 시작하는 조건

%a= 소문자 a로 끝나는 

 

 %: 0개 또는 1개이상의 문자열

 _: 1개의 문자열

 

 select albumid, title from albums where 

 이름이 A로 시작하는 고객 리스트

  select firstname from customers where firstname like 'J%';

 이름이 J로 시작하고 j를 포함 알바벳 네글자 길이인 고객명

 

 select firstname from customers where firstname like 'J___';

< select limit >

 

select albumid, title from albums limit 10;

- 말그대로 제한이다. 위의 명령어를 치면 albums내에 albumid와 title을 10개씩만 출력.

너무 쉬워서 생략.

offset 개념.

  

select albumid, title from albums limit 10 offset 5; offset은 출력 시작 위치를 지정

  6부터 보고 싶다면 offset을 5로 설정하면 5까지는 보여주지 않고 6부터 출력한다.

  

select albumid, title from albums limit 5,10;  = 위와 동일.

   

트랙 이름을 25개씩 묶어 처리할때, 3번째 묶음 목록. 25개씩 묶었으므로 offset 25를 하면 해당 목록의 25개의 목록을 보연준다. 당연히 26을 하면 한 칸 내려간다. 

문제의 3번째 목록 처음 시작이기 때문에 50을 한다. 

이해를 돕기 위해 그림을 그렸다.

아래 그림은 sqlite를 그래픽화 시킨 도구로 오픈한 모습.

 

QUERY문을 작성하기 위해서는 단축키 ALT + E키를 누르면 된다.

employees들의 나이를 추출하였다. 50이상만 출력.

종업원의 이름과 datatime함수를 이용한 나이를 출력.

 

select firstname, 2018-datetime(BirthDate) from employees ;

나이가 50 이상인 사람

- select firstname, datetime('now')-datetime(BirthDate) as Age from employees where

  (datetime('now')-datetime(BirthDate)) > 50;

 

 

select datetime('now'); = 현재 컴퓨터의 시간, 날짜