안산드레아스

앞서 만든 DB오 테이블을 이용. (SQL으로 DB생성방법, Table생성 방법, 데이터값을 넣는 방법)

https://ansan-survivor.tistory.com/1372

WHERE문 사용법

-> 특정 조건으로 원하는 데이터를 보고자 할 때 사용

예) SELECT [필드이름] FROM [테이블이름] WHERE [조건];

USE  sqldb;
SELECT * FROM usertbl;

SELECT * FROM usertbl WHERE name = '김경호';

해당 테이블을 USE로 선택하여 usertbl의 name타이틀을 이용해 필터링하는 예시.

* WHERE 조건문, AND

    두가지가 모두 만족할 때

USE  sqldb;
SELECT * FROM usertbl;

SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 AND height >= 182;

    생일조건, 키 조건이 모두 만족할 때를 필터링하는 예제

* WHERE 조건문, OR

    둘중에 하나라도 만족 만족할 때

USE  sqldb;
SELECT * FROM usertbl;

SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 OR height >= 182;

-- SELECT userID, Name FROM usertbl WHERE birthYear >= 1970 AND height >= 182;

    생일조건, 키 조건이 둘중 하나라도 만족하면 포함하는 예제

* WHERE 조건문, BETWEEN A AND B

    두 값 사이에 있는 항목만 골라냄

USE  sqldb;
SELECT * FROM usertbl;

SELECT name, height FROM usertbl WHERE height BETWEEN 180 AND 183;

    height의 사잇값(180~183 사이)을 필터링 한다.

* WHERE 조건문,IN ('A','B' ... 'C')

    IN 내부에 포함되는 모든 항목을 골라냄

USE  sqldb;
SELECT * FROM usertbl;

SELECT name, addr FROM usertbl WHERE addr IN ('경남','전남','경북');

    addr 내의 IN 내부에 포함되는 모든 항목을 포함

* WHERE 조건문, LIKE '조건'

    아래 조건은 '김'으로 시작하는 모든 항목을 골라낸다. 뒤에 %를 붙여준다.

USE  sqldb;
SELECT * FROM usertbl;

SELECT name, height FROM usertbl WHERE name LIKE '김%';

    아래 조건은 앞에는 무엇으로 시작하는지 모르고, 뒤에는 '종신'으로 끝나는 항목을 필터하는 것.

USE  sqldb;
SELECT * FROM usertbl;

SELECT name, height FROM usertbl WHERE name LIKE '_종신';
-- SELECT name, height FROM usertbl WHERE name LIKE '김%';

데이터 베이스 SQL기본에 관한 동영상.

https://www.youtube.com/watch?v=Z66quG3HBls&list=PLVsNizTWUw7Hox7NMhenT-bulldCp9HP9&index=19 

위 영상을 참고로 데이터베이스 만들기

MySQL DBMS Workbench 설치법은 아래 참고.

https://ansan-survivor.tistory.com/1130

MySQL DBMS 구조 및 간단 사용법

https://ansan-survivor.tistory.com/1142

회원테이블은 아이디를 Primary Key(PK)로 사용하며, 8개의 Title이 있다.

구매테이블은, 회원테이블을 Foreign Key(FK)로 아이디를 연결하며, 순번이 Primary Key(PK)로 쓴다.

구매테이블의 FK는 구매 항목이므로 해당 아이디가 여러번 나올 수 있다.

1. 새로운 db 생성.

    이름은 sqldb로 했다.

CREATE DATABASE sqldb;

2. 첫번째 테이블 생성

    위에서 생성한 db 내에 사용하기 때문에 USE문으로 db명을 선택해야 한다.

    user에 관한 정보 "usertbl" 생성. 상단의 Title 부분만 생성하는 것이다.

USE sqldb;
CREATE TABLE usertbl -- 회원 테이블
( userID  	CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  name    	VARCHAR(10) NOT NULL, -- 이름
  birthYear   INT NOT NULL,  -- 출생년도
  addr	  	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  mobile1	CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
  mobile2	CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 키
  mDate    	DATE  -- 회원 가입일
);

3. 두번째 테이블 생성

    구매목록 관련 테이블 생성, PK으로는 구매의 순번. AUTO_INCREMENT는 자동으로 새롭게 생긴 열에 숫자를 매긴다.

    맨아래 옵션으로 FOREIGN KEY 으로 reference(참조)형식으로 설정

    FOREIGN KEY (FK로_사용할_타이틀) REFERENCES 다른테이블(FK로_사용할_타이틀)

CREATE TABLE buytbl -- 회원 구매 테이블(Buy Table의 약자)
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   userID  	CHAR(8) NOT NULL, -- 아이디(FK)
   prodName 	CHAR(6) NOT NULL, --  물품명
   groupName 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 단가
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (userID) REFERENCES usertbl(userID)
);

4. 생성한 테이블에 값을 넣기

    아래와 같이 각 테이블에 값을 넣을 수 있다. (순서대로 넣는다. 없으면 NULL을 넣으면 된다.)

    INSERT INTO 테이블이름 VALUES('첫번째값', '두번째값', ... '마지막값');

    AUTO_INCREMENT 옵션이 들어간 인자는 자동으로 숫자가 들어가기 때문에 NULL을 넣으면 된다.

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);

    테이블 생성되었나 확인

SELECT * FROM usertbl;
SELECT * FROM buytbl;

(결과) 두개의 테이블의 인자들이 잘 입력된 것을 볼 수 있다.

특정 Database를 사용하기 위해서는 먼제 아래와 같이 USE문이 선행되어야 한다.

USE 데이터베이스_이름

생성한 또는 불러온 db가 왼쪽 리스트에 있는데, USE문을 사용하고 번개로 실행하면, 해당 db가 찐하게 하이라이트되며, 이제 그 DB에 SQL문을 사용하는 것이 된다.

SELECT문 기본 형태 (옵션을 사용할 시 아래 옵션이 나열되는 순서는 변경되면 안된다.)

SELECT select_list
	[ FROM table_source ] 
    	[ WHERE search_condition ]  
	[ GROUP BY group_by_expression ]  
	[ HAVING search_condition ]  
	[ ORDER BY order_expression [ ASC | DESC ] ]

이를 더 간단히, 자주쓰이는 형태

SELECT [열이름] 
	FROM [테이블이름] 
	WHERE [조건]

제일 자주 사용하는 SELECT문 (특정 db의 테이블의 전체 내용을 볼 때)

SELECT * FROM [db이름/테이블이름]

특정 db를 USE로 잡고, 해당 db의 테이블의 모든 내용을 보여주는 결과

특정 db 내의 특정 table명을 불러와서 보여주는 방법 (2가지)

 - USE문을 사용안했을 때

SELECT * FROM db이름.테이블이름;

 - USE문을 사용했을 때

SELECT * FROM 테이블이름;

(USE로 database선택을 안했을 때)

SELECT * FROM employees.titles;

(동일 명령)

USE employees;
SELECT * FROM titles;

특정 Title명으로 골라서 뽑아낼 수 있다.

USE employees;
SELECT first_name, last_name, gender FROM employees;

<주석 처리하기, 한줄 주석, 여러줄 주석>

 한줄 주석 : --

 여러줄 주석 : /*   */

USE employees;  -- 여기는 한줄 주석
SELECT first_name, last_name, gender FROM employees;
/*
여기는 여러줄의 주석
입니다.
*/

해당부는 무시하고 진행된다.

<현재 존재하는 항목 보기 - SHOW문>

    모든 데이터베이스 항목 보기

SHOW DATABASES;

    특정 테이블의 상태를 확인하기

USE employees;
SHOW TABLE STATUS;

윈도우상에서 GUI를 이용해 손쉽게 MySQL을 관리하기 위해서 Oracle의 MySQL Workbench가 필요하다.

https://ansan-survivor.tistory.com/1130

어떤 회사를 가정하고 쇼핑몰을 운영하는데, 아래와 같이 root를 제외하고 3명의 관리자를 부여한다.

root 계정 자체는 모든 권한을 다 갖고있어 실수로 인한 db손상시 복구가 불가능 함으로 보통 관리 권한을 역할에 맡게 잘 부여해야 한다.

아래와 같은 예를 들어 db를 구축하는 테스트.

실제 root처럼 모든 관리를 하는 사람은 팀장이고 (but root까지는 아님)

사장은 모든 데이터를 전체적으로 볼 수 있다.

쇼핑관리자 직원은 shopping관련 db만 관리차 목적으로 읽기 쓰기가 가능하지만, 직원정보는 볼 수만 있다.

<팀장 계정 생성하기 - 전체 Read & Write>

1. 아래와 같이 GUI 를 이용해 계정 생성을 한다.

    새로운 계정 director가 생겼음을 확인.

2. 권한 설정

    * 쿼리의 갯수 제한, 연결의 갯수 제한 등을 설정할 수 있다.

    * Administrative Roles에서는 실제적으로 어떤 권한을 받을지 설정. DBA를 선택하면 거의 root같이 권한 부여

<사장 계정 생성하기 - 전체 Read Only>

1. 위와 동일하게 계정을 아래와 같이 생성한다.

2. 권한설정 (전체에 대해 Read Only)

<쇼핑관리자의 계정 설정하기>

1. 위와 동일하게 계정을 아래와 같이 생성한다.

2. Shopping_db 권한을 선택적으로 부여 (read & write)

해당 Shopping_db에 대해서 아래와 같이 권한을 부여한다.

3. employee 권한을 선택적으로 부여 (read only)

    * 읽기 권한은 Select 만 부여하면 된다.

<다른 계정으로 접속 테스트>

1. 설정 바꿔서 로그인하기

2. 새로운 db 생성해보기 (팀장이므로 생성 가능하다.)

3. 사장으로 접속(Read only 권한) 읽기

    * db생성 시도 !!  에러발생 !!

    하지만 읽기는 가능하다.

4. 직원으로 접속하기

    자기한테 부여된 권한만 옆에 db에 표시가 된다.

MySQL Workbench를 이용하면서 SQL서버로 리눅스 서버를 이용할 때 연결하는 방법이다.

먼저 사전작업으로 리눅스 MySQL 서버가 구성되어있어야 한다.

Test로 VM을 이용해 했다.

(VMware를 통해 MySQL (MariaDB)를 리눅스 CentOS 7에 구축하기)

https://ansan-survivor.tistory.com/1185

윈도우에 설치한 MySQL Workbench로 연결하기 위해서는 프로그램이 설치되어 있어야 한다.

(MySQL Workbench 윈도우에 설치하기)

https://ansan-survivor.tistory.com/1130

1. MariaDB (MySQL)을 구성한 리눅스의 IP주소 확인 (VMWare에서는 Bridge Network를 사용해야 한다.)

2. Workbench에서 리눅스에 연결하기

    +버튼을 눌러 새로만들기

    연결의 이름설정 - IP주소 입력 - Store in Vault에서 해당 계정의 암호 입력 (여기서는 계정을 root로 사용)

    아래와 같이 창이 떠야 연결이 된 것.

3. 데이터베이스 확인하기

    쿼리문 새로만들기 - 아래 쿼리문 작성 - 번개표시로 실행하기

show databases;

    결과로 리눅스 서버에 어떤 db가 있는지 확인할 수 있다.

4. MySQL WorkBench에서 새로운 db를 리눅스서버에 생성시키기

create database newdb;
use newdb;
create table newtable(uname char(10));
insert into newtable values ('hello world');
select * from newtable;

    위 쿼리문을 실행시켜서 newdb라는 새로운 db가 생성되었고, table까지 생성했다.

    table 내용 확인. char(10)으로 설정했기 때문에, 그 이상은 표시되지 않는다.

5. 리눅스 내에서도 잘 생성되었나 확인하기

$ mysql -u root -p

show databases;

    제대로 잘 들어가 있음을 확인.

    테이블 확인. (show tables로 테이블명을 확인하고, desc로 내부인자를 볼 수 있다.)

use newdb;
show tables;
desc newtable;

6. 새로운 테이블 삽입하고 확인하기

insert into newtable values('ich liebe dich');
SELECT * FROM newtable;

7. MySQL Workbench에서 조회 해보기

    다시 조회해보면 새로 추가됨을 알 수 있다.

    이로써 윈도우에 MySQL Workbench와 리눅스의 MySQL이 서로 동일하게 링크걸려있음을 볼 수 있다.

이제 이러한 방식으로 전세계 어딜 가던, 인터넷만 있다면 해당 DB로 연결이 가능하다.

CentOS에 MariaDB를 설치하는 방법은 아래 참고

https://ansan-survivor.tistory.com/1185

그리고나서 루트계정에 암호를 부여하는 방법이다.

1. 루트 패스워드 변경

/usr/bin/mysqladmin -u root password

2. 루트 접속

mysql -u root -p

    패스워드 입력하면 접속 됨.

3. 새로운 계정 생성, 아이디와 패스워드를 생성.

 * wordpress-user : 내가 원하는 ID , your_strong_password : 내 패스워드 입력

CREATE USER 'wordpress-user'@'localhost' IDENTIFIED BY 'your_strong_password';

 * 위에서 만든 wordpress-user 입력. 

CREATE DATABASE `wordpress-db`;
GRANT ALL PRIVILEGES ON `wordpress-db`.* TO "wordpress-user"@"localhost";
FLUSH PRIVILEGES;

exit

( 외부 접속으로 root 계정 접속 허용을 하려면 아래 참고)

https://ansan-survivor.tistory.com/1336

MariaDB를 설치하고 start로 실행시켜로 하는데, 아래와 같은 에러가 발생했다.

[admin@localhost lib]$ sudo systemctl start mariadb.service

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

그래서 위 메세지가 시킨대로 다시 아래와 같이 명령을 썼더니 결국 Fail 이고, 아래와 같은 메세지가 나타났다.

[admin@localhost lib]$ sudo systemctl status mariadb.service

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2022-03-04 15:28:08 KST; 10s ago
  Process: 4606 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=1/FAILURE)

Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: Please check all of the above before submitting a bug report
Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: at http://mariadb.org/jira
Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: Initialization of MariaDB database failed.
Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: Perhaps @sysconfdir@/my.cnf is misconfigured or there is some problem with permissions of /var/lib/mysql.
Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: Initialization of MariaDB database was not finished successfully.
Mar 04 15:28:08 localhost.localdomain mariadb-prepare-db-dir[4606]: Files created so far will be removed.
Mar 04 15:28:08 localhost.localdomain systemd[1]: mariadb.service: control process exited, code=exited status=1
Mar 04 15:28:08 localhost.localdomain systemd[1]: Failed to start MariaDB database server.
Mar 04 15:28:08 localhost.localdomain systemd[1]: Unit mariadb.service entered failed state.
Mar 04 15:28:08 localhost.localdomain systemd[1]: mariadb.service failed.

위 상황 해결방법.

먼저 원인을 찾아보니 설치가 잘못된 경우라고 한다.

https://bobcares.com/blog/mariadb-failed-to-start/

따라서 아래 과정을 따라하면 해결 된다.

1. MariaDB 완전 깔끔하게 제거, 라이브러리 모두 제거

https://ansan-survivor.tistory.com/1339

2. MariaDB 재설치

https://ansan-survivor.tistory.com/1185

3. 그리고 다시 Start 하고, Status로 상태를 확인해보면 되는것을 볼 수 있다!

sudo systemctl start mariadb
sudo systemctl status mariadb

(MariaDB 설치 방법은 아래 참고)

https://ansan-survivor.tistory.com/1185

MariaDB(MySQL)을 구축하고나서 외부에서 접속을 해보면 접속이 되질 않는다.

그 이유는 mariaDB는 default로 외부접속을 차단해놨기 때문이다.

이를 root 외부접속 허용하는 방법이다.

1. 리눅스에서 root계정으로 접속

$ mysql -u root -p

2. 아래 명령을 순차적으로 입력한다. (비밀번호는 본인이 설정한 root 비밀번호)

use mysql

grant all privileges on *.* to 'root'@'%'identified by '비밀번호';

flush privileges;

    그리고 나서 mysql을 종료

exit

3. MySQL을 재시작

sudo systemctl restart mariadb.service

(결과)

윈도우에서 MySQL Workbench로 접속하니 잘 접속이 되었다.