쏭쎡

현재 로그인되어있는 계정에 lock을 걸어도 곧바로 적용되지 않는다. 그것을 적용할 수 있게 루트에서 강제로 kill을 해준다.

먼저, show processlist로 process를 조회하여 해당 번호를 kill한다.

접속 해있던 계정이 서버에서 locked되었다면서 아무것도 못한다.

아래는 그냥 참고용. 바로 생성된 테이블을 조회하는 명령어.

alter 명령어를 이용하여 table drop, insert 등이 가능하다. 

아래는 name이라는 tuple을 drop하였다. 

alter ~ change로 튜플명을 변경도 가능하다.

아래는 auto_increment 설정이다. 

primary key가 필수이며, 해당 데이터를 insert시킬때마다, 자동으로 +1 증가 해주는 기능이다. 

'sam'@'localhost 패스워드를 변경. 

아래 그림처럼 패스워드를 user()함수를 통해서 바로 바꾸는것도 가능하다.

select current_role();

현재 role을 조회하는 것. 아무것도 설정안해서 없다. 

set role 'dev';를 실행

=> 다시 조회하면 'dev''@'%'가 생성 

set role default;

=> default로 돌리는것. 여기서는 처음에 아무것도 없고 딱히, default가 지정이 안되어 있으므로 

   아무것도 안나온다.

먼저 이론 

service -e 

=>현재 구동되고 있는 서비스 목록

sudo service myslq-server status 

=> mqsql 상태

su

=> 바로 root로 접속

numberic(3,2) 

=> 3자리 표현하면 소수점 2자리 표시

enum('wes','central','east');

<관리자 프로그램 , 유틸리티>

- ibd2sdi

- innochecksums

- myisam_ftpdump

- myisamchk

- myisamlog

- mysampack

- myslql_config_editor

- mysqlbinlog

- myusqldumpslow 

● 프로그램 개발 유틸리티

- mysql_config

- my_print_defautlts

- resolve_statck_dump

- Iz4_decompress

- perror

- resloveip

- zlib_decompress

<>

● 오픈소스 데이터베이스 엔진

● 데이터를 디스크에 어떠한 형태로 기록, 관리할 것인가를 결정

● MyISAM : 구형 엔진, 읽기 위주 데이터 저장에 적합

● 새로운 버전, MyISAM을 대체해 나가는 중

<Mysql - 사용자 계정관리>

● 데이터 베이스는 별도의 사용자 데이터 베이스를 구축

● 시스템 사용자와는 다른 사용자 세트를 사용

● select current_user(); -- 현재 사용중인 계정명 반환

<사용자 계정의 표시>

● 'user_name'@'host_name'

● user_name은 최대 32개 문자까지 가능

● 여러 호스트/도메인에 돔영 이인이 있을 수 있으므로 

메일 주소와 비슷한 형식을 사용자

- 'alice'@'192.168.0.10'

- 'alice'@'%.my_domian.com'

- 'bob'@'localhost'

● 도메인과 호스트 이름

● www.naver.com에서 

- www는 특정 서버 컴퓨터를 뜻하는 호스트의 이름

- naver.com은 도메인 이름을 뜻함

- .com은 최상위 도메인(Top Level domain)이라 부름 

● 일반적으로 호스트명(hostname) www만을 지칭하나

  명확한 호스트명을 표시하기 위해 모든 파트를 다 언급하기도 함

● www.naver.com 과 같이 모든 파트를 다 언급하는 형태를 

  FQFN(Fully qualified domain Name)이라고 하고

● 특히 DNS 시스템을 구성할 때에는 www.naver.com.과 같이

  마지막에 .을 표기하도록 하는 경우도 있음

● 사용자 명은 특정 사용자 이름 외 빈칸을 입력할 수 있음

● 사용자 명에는 패턴 매칭 사용 불가   

● 빈칸은 모든 사용자 명을 뜻함 

  - ''@'192.168.0.10'

  - ''@'%.mydomain.com'

● 호스트/도메인을 명을 입력하지 않으면 '%'로 처리

  - 'alice'

  - 'alice'@'%'

● 괄호에 주의 

  - 'alice@localhost'

     => 사용자 이름으로 인식

     'alice'@'localhost;로 사용하여야 한다.

  - 'alice@localhost'@'%'

● 호스트/ 도메인 명에 Like 구문에서와 가트은 패턴매칭 사용가능

- 'alice'@'192.168.100.%'

- 'bob'@'%.com'

● IPv4 주소를 사용할 경우 마스크 값 사용가능

- 'alice'@'192.168.100.0/255.255.255.0'

● h1.example.net에서 접속하는 fred

=>'fred'@'h1.example.net'

● h1.example.net에서 접속하는 모든 사용자명

=> ''@'h1.example.net'

● 불특정한 호스트(모든 호스트)에서 접속하는 fred

=>'fred'@'%'

● 불특정한 호스트(모든 호스트)에서 접속하는 모든 사용자명

=> "@'%'

● example.net 도메인의 모든 호스트에서 접속하는 fred

=> 'fred'@'%.example.net'

● 192.51.100.0/24 네트워크에서 접속하는 fred

=>'fred'@'192.51.100.0/255.255.255.0'

<사용자 인증>

● 호스트명, 사용자명, 패스워드 검증

● 검증시 사용자 테이블을 정렬하여 최초 매칭되는 경우를 사용

1. 호스트가 가장 구체적으로 명시되어 있는 순서대로 정렬

2. 사용자명이 가장 구체적으로 명시되어 있는 순서대로 정렬

3. 호스트와 사용자 명의 구체성이 동일한 경우, 정렬순서는 랜덤 

● 구체적인 명시

- 'h1.example.net'이 '%.example.net'보다 더 구체적임

- 'alice' 보다 더 구체적임 

<사용자 작업권한 인증>

● 접속이후 사용자가 입력하는 명령어 사용권한을 인증함

● mysql 데이터 베이스의 user테이블에 기록되는 권한은 

  모든 데이터 베이스에 대한 사용자 계정의 권한을 표시

● 관리자 계정 외에는 myslq.user테이블에 기록되는 권한은 'N'으로,

  mysql.db 테이블을 사용하여 각 데이터베이스, 테이블, 칼럼 별 권한을

  지정하여 사용하는 것이 안전 

   <사용자 작업권한 적용 시기>

● 서버 시작시 권한 사항을 메모리에 저장하여 구동되고

● gran, revoke등 DCL을 사용한 구너한 조정은 실시간 권한 적용

● insert, update 등 DML을 이용하여 직접 테이블 값을 수정한 겂은 

 - FLUSH PRIVILEGES;

 - msysqladmin flush-privileges

 - mysqladmin reload

  명령어 이후 적용

● 명령어 권한은 다음번 명령어 사용시 적용시작

● 데이터베이스 권한은 다음번 use db_name; 구문 사용시 적용

<사용자 계정의 추가 /삭제>

● CREATE USER user_nema IDENTIFIED BY password;

● user_name 은 올바른 사용자계정 이름의 형식을

● password는''를 사용한 문자열 

● DROP USER user_name;

select user,host from mysql.user;

=> 사용자 조회

<사용자 계정: 리소스 권한>

s

● 시간당 SQL문 사용 가능 횟수

=> WITH MAX_QUERIES_PER_HOUR 

● 시간당 update 명령어 사용 가능 횟수

=> WITH MAX_UPADATES_PER_HOUR

● 시간당 접속 가능 횟수

=> WITH MAX_CONNECTIONS_PER_HOUR

● 가능한 동시접속 횟수 

=> WITH MAX_USERS_CONNNECTIONS 

<사용자 계정 : 패스워드 관리 >

/usr/local/etc/mysql

● 패스워드 변경

 - ALTER USER user_name IDENTIFIED BY password;

 - SET password for user_name = password;

● 패스워드 만료 설정

 - ALTER USER user_name PASSWORD EXPIRE

 - ALTER USER user_name PASSWORD EXPIRE INTERVAL 90 DAY;

 - my.cnf mysqlid 섹션 : default_passwordk_lifetime

   0는 제한없음, 기타 숫자는 패스워드의 기본 유효기간 

● 패스워드 재사용

  - my.conf의 mysql 섹션 : password_history

                           password_reuse_interval

● 패스워드 재사용 전 5번의 다른 패스워드 사용을 강제   

- ALTER USER user_name PASSWORD HISTORY 5;

● 패스워드 재상용 전 6일이 지나도록 강제 

- ALTER USER user_name PASSWORD REUSE INTERVAL 6 DAY;

● 패스워드가 만료된 계정으로 접속이후

mysql --connect-expired-password 사용 

<사용자 계정 잠금>

● ALTER USER user_name ACCOUNT LOCK

● ALTER USER user_name ACCOUNT UNLOCK

<사용자 계정 보안 접속방법 제한>

● ALTER USER user_name REQUIRE NONE;

● ALTER USER user_name REQUIRE SSL;

● ALTER USER user_name REQUIRE X509;

● ALTER USER user_name REQUIRE ISSUER;

● ALTER USER user_name REQUIRE SSL;

'/C=SE/ST=Stcoholm/L=stockholm/O=MySQL/CN=CA/

 emailAddress=ca@example.com

<사용자 계정 실습>

● vic.esample.net에서 접속하는 vitcor계정을 생성하면서

  'my_pass'를 패스워드로 지정

=>  create user victro'@'vic.example.net identifed by 'my_pass';

● t5.example.net에서 접속하는 trent계정을 생성하면서

  SSL 암호화된 접속만을 허용하고 동시 접속 가능 회선을 3개로 제한 

=> create user trent'@'t5.example.net' identifed by 'password' REQUIRE 

   SSL with MAX_USER_CONNECTION 3;

<Grant, Revoke>   

● 계정에 대한 현재 GRANT 내역 조회

● 사용가능한 SQL문의 제한없음

ALL

SELECT

UPDATE

INSERT

CREATE

DROP

ALTER

...

● 레벨: 전체, 테이블, 칼럼

● 레벨의 표시

*

*.*

db_name.*

db_name.table_name

<사용자 계정 SQL >

● GRANT _ 허용 SQL_ON_ 레벨_TO user_name;

● GRANT SELECT ON test.*TO 'eve'@'localhost';

● GRANT SELECT(col1). INSERT(col1, col2) ON test.tb1 TO 'may'@'localhost';

● REVOKE는 GRANT와 같은 형태의 문법 사용 

● FLUSH PRIVILEGES

  => 권한 다시 재로드.(초기화)

root에서 권한 

grant all on world.* to 'tuser1'@'localhost';

tuser1으로 로그인

=> show databases;

● 192.168.0.0/24 네트워크 대에서 접속하는 trent계정

=> create user 'trent'@'192.168.0.0/255.255.255.0' identfied by 'pass';

● trent는 test 데이터베이스의 t1 테이블에 데이터 추가 가능

=> grant insert on test.t1 to 'trent'@'192.198.0.0/255.255.255.0';

● trent는 test 데이터베이스의 모든 테이블 값을 제한없이 조회가능

=> grant all on test.* to 'trent'@'192.168.0.0/255.255.255.0';

● trent는 test 데이터베이스의 t2 테이블의 c1 칼럼의 값을 변경 가능 

=> grant update(c1) on test.t2 to 'trent'@'192.168.0.0/255.255.255.0';

   칼럼은 ()쳐서 칼럼을 적는다.

//////////////////////////////////////////////////////////////////////////////

service -e 

=>현재 구동되고 있는 서비스 목록

sudo service myslq-server status 

=> mqsql 상태

아래 명령어도 mysql -uroop -p와 같은 명령어이다. 길게 사용할 때는 '--'를 두번 붙인다.

그냥 pager라고 하면 more인식되나, pager less라고 입력시 less처럼 사용할 수 있다.

● select current_user(); -- 현재 사용중인 계정명 반환

mysql데이터베이스에 user라는 테이블에서 계정을 관리하고 생성, 삭제가 이루어진다. 따라서 계정을 조회하기 위해서는

mysql.user에서 조회 가능하다. 

계정을 삭제하는 모습 

계정을 생성이 가능하다. 생성된 계정을 한 번 더 만들려고 하면 에러가 발생한다. 그러나 syntax를 참고하여 if not exist를 적어두면

에러를 내지 않는다. 왜냐하면 적어도 되고, 안적어도 된다라고 알려주므로 PC가 있으면 생성하지 않고, 없으면 생성한다고 알아먹기

때문이다. 

<사용자 작업권한 인증>

● 접속이후 사용자가 입력하는 명령어 사용권한을 인증함

● mysql 데이터 베이스의 user테이블에 기록되는 권한은 

  모든 데이터 베이스에 대한 사용자 계정의 권한을 표시

● 관리자 계정 외에는 myslq.user테이블에 기록되는 권한은 'N'으로,

  mysql.db 테이블을 사용하여 각 데이터베이스, 테이블, 칼럼 별 권한을

  지정하여 사용하는 것이 안전 

   <사용자 작업권한 적용 시기>

● 서버 시작시 권한 사항을 메모리에 저장하여 구동되고

● gran, revoke등 DCL을 사용한 구너한 조정은 실시간 권한 적용

● insert, update 등 DML을 이용하여 직접 테이블 값을 수정한 겂은 

 - FLUSH PRIVILEGES;

 - msysqladmin flush-privileges

 - mysqladmin reload

  명령어 이후 적용

● 명령어 권한은 다음번 명령어 사용시 적용시작

● 데이터베이스 권한은 다음번 use db_name; 구문 사용시 적용

<사용자 계정의 추가 /삭제>

● CREATE USER user_nema IDENTIFIED BY password;

● user_name 은 올바른 사용자계정 이름의 형식을

● password는''를 사용한 문자열 

● DROP USER user_name;

select user,host from mysql.user;

=> 사용자 조회

<사용자 계정: 리소스 권한>

● 시간당 SQL문 사용 가능 횟수

=> WITH MAX_QUERIES_PER_HOUR 

● 시간당 update 명령어 사용 가능 횟수

=> WITH MAX_UPADATES_PER_HOUR

● 시간당 접속 가능 횟수

=> WITH MAX_CONNECTIONS_PER_HOUR

● 가능한 동시접속 횟수 

=> WITH MAX_USERS_CONNNECTIONS 

<사용자 계정 : 패스워드 관리 >

/usr/local/etc/mysql

● 패스워드 변경

 - ALTER USER user_name IDENTIFIED BY password;

 - SET password for user_name = password;

● 패스워드 만료 설정

 - ALTER USER user_name PASSWORD EXPIRE

 - ALTER USER user_name PASSWORD EXPIRE INTERVAL 90 DAY;

 - my.cnf mysqlid 섹션 : default_passwordk_lifetime

   0는 제한없음, 기타 숫자는 패스워드의 기본 유효기간 

● 패스워드 재사용

  - my.conf의 mysql 섹션 : password_history

                           password_reuse_interval

● 패스워드 재사용 전 5번의 다른 패스워드 사용을 강제   

- ALTER USER user_name PASSWORD HISTORY 5;

● 패스워드 재상용 전 6일이 지나도록 강제 

- ALTER USER user_name PASSWORD REUSE INTERVAL 6 DAY;

● 패스워드가 만료된 계정으로 접속이후

mysql --connect-expired-password 사용 

<사용자 계정 잠금>

● ALTER USER user_name ACCOUNT LOCK

● ALTER USER user_name ACCOUNT UNLOCK

<사용자 계정 보안 접속방법 제한>

● ALTER USER user_name REQUIRE NONE;

● ALTER USER user_name REQUIRE SSL;

● ALTER USER user_name REQUIRE X509;

● ALTER USER user_name REQUIRE ISSUER;

● ALTER USER user_name REQUIRE SSL;

'/C=SE/ST=Stcoholm/L=stockholm/O=MySQL/CN=CA/

 emailAddress=ca@example.com

● t5.example.net에서 접속하는 trent계정을 생성하면서

  SSL 암호화된 접속만을 허용하고 동시 접속 가능 회선을 3개로 제한 

=> create user trent'@'t5.example.net' identifed by 'password' REQUIRE 

   SSL with MAX_USER_CONNECTION 3;

이 부분은 syntax를 참고해야 한다. syntax는 나중에 포스팅 예정 

<사용자 계정 SQL >

● GRANT _ 허용 SQL_ON_ 레벨_TO user_name;

● GRANT SELECT ON test.*TO 'eve'@'localhost';

● GRANT SELECT(col1). INSERT(col1, col2) ON test.tb1 TO 'may'@'localhost';

● REVOKE는 GRANT와 같은 형태의 문법 사용 

● FLUSH PRIVILEGES

  => 권한 다시 재로드.(초기화)

<실습>

create user 'tuser'@'localhost' (identified by '패스워드'); 명령어로 임의의 계정을 생성.

root 계정에서 먼저 권한을 준다.

=>grant all on world.* to 'tuser1'@'localhost';

tuser1으로 로그인

=> show databases;

BSD mysql 설치 절차.

sudo pkg update

=> 패키지 목록 받아오기

sudo pkg search mysql | less

=> 정확한 패키지 이름 검색 

sudo pkg install mysql80-server

=> mysql 서버 설치 

service mysql-server start

=> mysql 서버 구동

mysql_secure_installation

=> mysql 초기 설정 

mysql -uroot -p

=> mysql 서버에 루트 계정으로 접속 

sudo pkg search mysql | less

설치할 mysql을 확인한다. 

vi /usr/local/etc/mysql/my.conf 

=> mysql config 파일 

그러나 어떻게 설정할지 모른다. 

그러기 때문에 /usr/local/etc/rc.d로 간다.

아래 그림을 참고 

rc.d에 보면 설명이 대략 나와있다. 

/etc/rc.conf

=> 프로그램 구동하기 위한 설정. 

아래와 같이 설정.

설정을 했으면 서비스를 시작함. 

mysql -uroot -p

=> mysql을 접속하기 위한것. (아직 패스워드를 지정하지 않은 상태)

pager 

=> less나 more과 같은 mysql 명령어 

mysql passwd 설정하기

안전하게 mysql을 설치하기 위한 파일. 이것을 실행.

mysql_secure_installation 

원격 로그인이 필요하나? n

test database를 제거하기를 원하나? no

prvililedge table를 가져오기를 원하나? y

FreeBSD에서 인터넷에 있는 파일 다운받고 실행하기.

 fetch https://dev.mysql.com/doc/index-other.html

인터넷상에서 다운받고자 하는 파일을 커서에 갖다댄다.

아래와 같이 주소가 뜬다.

우분투도 마찬가지이다.

// 우분투도 비슷하나 apt 명령어를 사용해야한다는점. 그리고 일반계정인 경우 무조건 

   sudo 명령어를 사용한다는 점이 BSD와 차이가 있다.

데이터베이스 mysql을 조회한 모습. 

결국 정리하자면 다음과 같다.

linux mysql 절차

sudo atp-get update

=> 패키지 목록 받아오기

sudo apt-cache search mysql | less

=> 정확한 패키지 이름 검색 

sudo apt-install mysql-server 

=> mysql 서버 설치 

sudo service mysql start

=> mysql 섭 구도 

sudo mysql_secure_installation

=> mysql 초기 설정 

sudo mysql -uroot -p

=> mysql 서버에 루트 계정으로 접속 

<my sql >

mysql과 sqlite의 차이점

----------------------------------------------------------------------------

| sql language  |       sqlite             |         mysql                                                  |

 ---------------------------------------------------------------------------

| 데이터 베이스 | 개별 파일               |    통합 use         (DB이름)                                 |

 ---------------------------------------------------------------------------

| 테이블 보기   | .tables                  |    show tables;                  |

 ---------------------------------------------------------------------------

| 스키마 보기   |  .schema              |    describe __table (이름)                                     |

 ---------------------------------------------------------------------------

| 구동방식      |  로컬파일 읽기 / 쓰기  |    네트워크 엑세스  (port 3306)                              |

 ---------------------------------------------------------------------------

| 데이터타입    |  간단함                 |    복잡함                           |

 ---------------------------------------------------------------------------

show databases;

=> 데이터베이스를 list를 보여준다. 

   sys, world는 기본제공 database 나머지는 내부 데이터 베이스.

use 데이터베이스 명

=> 데이터 베이스를 사용하겠다는 명령어

show tables;

=> 테이블 조회

describe table 명.

=> city라는 데이터베이스 DDL 조회. 

select city.city, country.country from city inner join country on city.country_id=country.country_id limit 10;

mysql에서도 inner join이 가능하다.

create database db명으로 데이터베이스 생성 가능.

생성한 테이블 

정가 20000원이상 책.

=> select * from book where price >= 20000;

책의 평균 가격

=>  select avg(price) from book;

gmail 이메일을 사용하는 저자

=> select name from author where email like '%gmail%';

아직 책을 발간하지 않은 저자

author_id가 일치하는 것인 발행된 저자이고 아닌것이 미발행 저자이다. 그러므로 

author로 join시키면 된다. 

select author.name, book.title from author left join book on author.author_id=book.author_id where 

   book.title is NULL;

출간된 저자목록과 price*qty를 출력

=>select  book.title,author.name, book.price*book.qty as 'price*qty from author inner join book on author.author_id=book.author_id;

book.price*book.qty = 테이블 book에 있는 price와 qty 곱한값을 출력하면서 

as 'price*qty'를 하여 속성명을 정해줌. 

book.author_id = author.author_id이라는 조건을 통하여 출력.

cc -E -o main main.c

=> -E 옵션은 전처리기 옵션이다. 해당 실행파일인 main을

    전처리만 한 실행파일로 만들어준다.

./main(실행파일이름)을 실행하면 아래 코드가 보인다.

#define하였던 setp, lower.. 등이 전부 숫자로 치환되었다. 

.header on을 하면 속성을 추가하여 출력해준다.

.mode tab을 누르면 아래 그림과 같이 속성간에 tab을 하여 출력한다.

table을 만들 때. NOT NULL을 지정해주면 반드시 입력해줘야 된다는 것이다. 입력하지 않으면

아래와 같이 출력된다.  

그래서 데이터베이스를 만들시에는 설계를 잘해서 초기에 잘해야 한다. 물론 alter명령어로 속성이 변경 가능하지만,

변경할게 1000개, 10000개가 된다면 정말 답이 없다.

insert into A vlaues (1,'a')

-> 행에 값을 추가.

insert into A(id, type) vlaues (1,'a');

-> 행에 속성을 지정하여 추가

아랙 그림에서는 rval속성에는 추가하지 않고 id, type, note에만 값을 추가하였다.

위의 첫번째 문으로 하면 속성마다 타입이 다르고 제약사항이 있어서 입력이 잘 안될 수 있다.(바로 위그림처럼)

따라서 2번째 유형으로 명령어를 써서 추가하였다. 

delete from A where type =='b';

type이 b인 행을 지운다.

조건을 주지않으면 행(tuple)이 전부 삭제 됨. 

type 값이 d보자 작은 tuple 삭제

 delete from A where type <'d';

수정

update A set note='FOUR' where id = 4;

id가 4인것에 note 속성을 four로 지정해라.

조건이 없으면 마찬가지로 모든 행에 note속성이 four로 바뀐다.

모든 rval 5.0으로 수정

-> where없이 지정하면 된다.

짝수 id의 rval을 수정

-> 조건을 주고 id%2 == 0;이면 짝수로 판단하여 10.0으로 바뀐다.

입력할 데이터가 많다면 text파일에 명령어를 써놓고 리다이렉션을 통해서 추가가 된다. 

메모장에 아래와 같은 record를 추가한다. (전화번호는 privacy상 그림판에서 지웠다)

아래그림에 추간된 모습(전화번호는 privacy상 그림판에서 지웠다)

용어정리

attribute/column /field/열

테이블 속성 여기서는 rank, class가 해당.

tuple/row /recode/행 -> 테이블의 가로 행전체. 각 속성에 해당하는 값들이

존재. 각각 rank, class의 해당하는 가로 값 (1, a), (2 ,b)...등을 말함. 

cardinality - tuple 갯수

degree = attribute갯수 id, type 이 존재하면 2개가 존재..

domain - attribute에 적용 가능한 값의모음

- 테이블에 정의된 attribute 타입이 적용가능한 모든 값.(에러체킹)

<union과 join>

select * from A union select * from B;

-> cardinaliy 

select * from A union select * from B order by type;

-> 정렬도 가능.

Select -attribute 별명

select id as no, type as category from A;

->원래 테이블에 카테고리는 그대로 놔두고 출력만 별명으로 하는것.

select id as rank, type as class from A;

아래 그림은 에러. 앞쪽을 보면 select id, type에 대해서 별명을 지으지 않고 뒤쪽 odrer by class로 찾으려 해서 에러.

그러나 마찬가지로 별명을 앞쪽에는 짓지 않았으안, 뒤쪽에 union 다음 문장에서 B테이블 속성에 별명를 지어줌.

에러를 발생하지 않으며, class로 정렬을 하되, 출력(union 앞쪽)에는 지정을 하지않아, id, type을 출력.

즉, class를 type 대응하여 정렬은 해준다.

select * from A, B where A.id==B.id;

테이블.속성(attribute)

A,B id가 같은것만 join하는것.

select A.id,A.type,B.type  from A, B where A.id==B.id;

select A.id,A.type,B.type  from A inner join B on A.id=B.id;

=>요즘 형태. on뒤에 조건.

A inner join B 

.mode column

=> 칼럼 형식으로 출력해준다.

A로 시작하는 앨범의 이름과 아티스트 이름의 tuple 리스트.

select albums.title, artists.name from albums inner join artists on albums.title like 'A%' and artists.name like 'A%';

limit도 가능하다. 

inner join, left join 비교

inner join

-> 1:1 매칭으로 join

left join

왼쪽 테이블 기준으로 join 오른쪽 테이블을 가져다 붙인다.  즉, 왼쪽 테이블은 유지한채로 오른쪽만 조건에 맞게 붙인다.

where is b.type is NULL이면 type이 NULL인 것을 출력.

아티스트 ID, 앨벌 아티스트 id를 왼쪽 ,join을 하고 앨범 id가 없는 즉, 미발매된 앨범의 네임과 id를 출력.

select artists.artistid, artists.name, albums.albumid from artists left join albums on artists.artistid == albums.artistid where albums.albumid is NULL;

alter, drop, 

 DDL - Alter table

 테이블 이름 변경

 alter table order rename to orders;

 테이블에 새로운 속성(attribute(column)추가

 alter table order add colunm tax integer;

테이블 전체 삭제

테이블 이름 변경

테이블 속성 (행)추가

alter table orders(테이블 명) add colunm customerid integer not null default 0;

테이블 이름 변경

vi 내에서 이름 바꾸기

확장자 모드 : 1,$ 1부터 전체 라인

s     = substitute. 대체하다.

/b    = b라는 문자

/sum = b를 대신할 문자

/g    = b라는 문자 전부를 바꾸겠다는 것.

 함수의 활용

● dateime, strftime

● round(반올림),min, max

● count, sum, avg

● length, trim, ltrim, rtrim, instr , substr

  - 문자열 관련 trim = 문자열 짜르는것. left, right, 문자열 내부 검색, 문자열 내부 특정한 부분 추출

round(반올림할 수, 소수점 자리수) - 반올림하는 함수.

strftime 함수 

이달의 마지막 날

 select strftime('start of monthy', '+1 month', '-1 days');

 =>select strftime('%Y-%m-%d','now', 'start of month','+1 months', '-1 days');

 본 과정을 시작한 후 경과 일

 - select strftime('%d ', 'now', '-10 days' );

 => select strftime('%j ', 'now') - strftime('%j', '2018-05-10'); 

 올해 마지막 날(2018-12-31)의 요일 

 => select strftime('%Y-%m-%d', 'now', 'start of year', '+1 year', '-1 day');

 min, max 

 최소값 , 최대값

트랙 테이블에는 각 트랙의 파일 크기( Byte)가 기록되어 있다.

트랙중 파일 크기가 가장 큰 것과, 가장 작은것의 트랙 이름을 구하라.

select min(Bytes) from tracks;

select max(Bytes) from tracks;

 count, sum, avg

 count = 세다

 sum   = 합

 avg   = 평균

 미국에 살고 있는 고객의 명수

 -select count(country) from customers where country = 'USA';

 Bluse장르의 트랙 갯수

- select count(TrackID) from tracks where TrackID==6;

 invoice_items 테이블에 기록된 주문량(Quantity)의 합계

 -select sum(quantity) from invoice_items;

 트랙 파일의 평균 크기를 소수점 3자리 표시되고 반올림 처림

 -  select round(avg(Bytes),3) from tracks;

2번째 문제는 잘못됨. 아래 그림의 genreid로 하여야 한다.

문자열 - 왼쪽부터 하나의 알파벳 씩 인데스 값을 가짐.(맨 왼쪽부터 1부터 시작)

 length :문자열 길이를 반환

 trim('문자','삭제 할 문자')    :문자열의 왼쪽/오른쪽 끝의 문자를 삭제

trim은 원래 공백 제거용이였으나, 문자삭제도 활용 가능하다. 

ex) select trim ('abcdefga', 'a'); 라고 입력시 => bcdefg 를 출력 양쪽 a를 지운다. 

 instr('문자','검색할 문자')  :문자열 내 특정 문자열을 검색

  - 문자의 위치를 알려준다. 

 substr('문자',문자위치, 위치로부터 출력할 자리수.) :문자열을 특정 위치, 특정 길이로 절단

 /abcdef/  

trim은 원래 공백 제거용이였으나, 문자삭제도 활용 가능하다. 

sql의 문자열 인덱스는 1부터 시작한다.  

a b c d e f

1 2 3 4 5

trim은 원래 공백 제거용이였으나, 문자삭제도 활용 가능하다. 

rtrim은 right trim으로 문자열에 오른쪽을 지운다. 없으면 지우지 않는다. 

ltrim은 left trim으로 왼쪽 문자열을 지운다. 

 academy로 시작하는 아티스트 이름의 목록을 출력하면서 academy of 문자를 제외하고 출력

 =>  select ltrim(name, 'Academy of') from artists where name like 'academy%';

 직원의 타이틀 중 세번째 문자부터 다섯번 째 문자까지 출력한 목록.

 => select substr(title  ,3, 5) from employees;

 직원의 타이틀 중 문자열 이 시작되는 문자열 인덱스의 목록

 =>  select instr(title, 'Ssru') from employees;

symphony로 끝나는 아티스트 이름의 목록을 symphony 문자를 제외하고 출력함.

 - select name rtrim('symphony','symphony') from artists where name like '%symphony';

 =>  select rtrim(name,'symphony') from artists where name like '%symphony';

트랙의 이름의 길이가 10미만의 목록

 => select name, length(name) from tracks where length(name) < 10;

virsual Box -환경설정-네트워크에서 nat 네트워크 추가 

사설 IP설정

그리고 FreeBSD설치(설치는 생략함)후에 가상시스템 내보내기

파일- 가상 시스템 가져오기를 클릭. 확장자명이 .ova파일을(아래그림에서는 freeBSD.ova) 불러온다. 

vitual machine내에서 모든 네트워크의 MAC 주소 초기화

<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'); = 현재 컴퓨터의 시간, 날짜