mysql 1강

mysql 사용

• 홈페이지, 쇼핑몰, 웹 개발 관리 시스템
• 누구나 사용 가능
• ssh, telnet에 접속하기 위한 원격 접속 프로그램이다.
• 프리웨어이다.
• 구글, 네이버 포털사이트에서 검색을 통해서 쉽게 다운 가능.
• mysql 무료라고해도 성능이 떨어지지 않는다.

putty 간략

• Saved Sessions 내가 저장한 이름

mysql 쿼리 시작

• mysql root 계정 외 사용자 권한을 부여하는 쿼리문
  • create user 'myalzio'@'localhost' identified by 'myalzio';
    • 서버가 동작하는 호스트에서는 localhost라고 사용하면 되고 서버가 동작하지 않는 호스트에서 mysql 서버에 접속하려 한다면 localhost자리에 작업하려는 컴퓨터 이름을 기재하면 된다.
    • 아이디 생성 완료(사용자 권한 부여 완료).
• 자세한 내용은 뒤에서
• 모든 완전한 접근 권한을 부여하는

명령어

• grant all on sampdb.* to 'myalzio'@'localhost';
  • grant라는 것은 sampdb를 생성하는 것과는 상관이 없다.
• 데이터베이스가 존재하기 전에 권한을 먼저 부여한 것 뿐이다.
• 사용자 아이디를 생성하고!
• 해당 사용자 아이디한테 sampdb 데이터베이스의 접근 권한 모두를 부여하는 2가지 작업 완료.

• mysql 프로그램에는 여러가지 option이 올 수 있다.

• mysql -h hostname

• -p 패스워드

• -u username

• hostname : mysql 서버가 실행되고 있는 호스트를 기술

  • mysql 실행 중인 컴퓨터가 동일하다면 생략 가능하다.

• mysql -u myalzio -p

• mysql -u myalzio -pmyalzio

  • mysql에 접속을 할 때, 비밀번호를 별도로 입력하지 않고 접속 아이디와 함께 기재를 할 수 있으나 보안상 위험하다.
  • 만약 비밀번호를 mysql에 접속시 바로 입력을 한다고 하면
    • -p과 붙여서 입력을 해야 한다.
  • 로그들은 모두 파일로 남게되는데 이 기록을 해커가 보게된다면 마음대로 mysql에 접속해서 정보를 이용할 것이다.

• mysql -umyalzio -p 처럼 username도 붙여서 쓸 수 있다.

• 스페이스 한개라도 줄이는게 아주 편리할 수 있다.

• 호스트 입력 예시

  • mysql -h aa.test.com -u myalzio -p

• -p 패스워드만 붙여서 입력하고 나머지는 모두 띄어쓰기 준수

mysql을 종료하는 명령어 종류 4가지

• exit

• quit

• \q

• ctrl + d

• ; 세미콜론 -> mysql에 구문이 완성되었다고 알려주는 것.

  • '구문종료자'

• mysql이 서버로 질문을 전달해준다.

• 서버는 이것을 처리한 뒤에 mysql에 답을 전달해준다.

• ; 세미콜론 대신에 \g 입력 가능

  • 여기에서 g는 go의 약자이다.

쿼리 시작

• select now();

  • 현재 시간을 알려주는구나.

• select now(), user(), version()\G

  • \G는 선택한 질문의 답을 세로로 보여준다.
  • 한줄로 보기 많은 양의 경우 \G를 입력해주면 가독성이 상당히 높아진다.

• ->화살표가 나왔다는 것은 아직 질의가 완성되지 않았다는 의미이고, 서버가 전송하지 않는다는 의미이다.

• 여러줄로 쿼리를 작성하다가 잘못써서 다시 쓰려고 한다면 \c를 입력해주면 쿼리를 다시 작성할 수 있다.

• select now(); select user(); select version();

  • 처럼 한 줄에 select를 여러개 쓸 수도 있다.
  • 대소문자를 구별하지 않기 때문에 대소문자를 섞어서 써도 무관하다.

• 데이터베이스 생성

  • create database sampdb;
    • 데이터베이스를 생성한다고해서해당 데이터베이스기 디폴트가 되는게 절대 아니다.

• 디폴트 데이터베이스를 해주어야 한다.

  • select database();
    • 디폴트 데이터베이스를 알려준다.

• 사용하려는 데이터베이스 선택

  • USE sampdb;

사용하려는 데이터베이스를 mysql 계정 로그인을 할 때, 바로 지정해서 사용할 수도 있다.

• mysql -u myalzio -p sampdb

• select database();

CREATE TABLE president

(

last\_name VARCHAR(15) NOT NULL,

first\_name VARCHAR(15) NOT NULL,

suffix VARCHAR(5) NULL,

city VARCHAR(20) NOT NULL,

state VARCHAR(2) NOT NULL,

birth DATE NOT NULL,

death DATE NULL

);

• (숫자) 가변길이 값을 담는 칼럼명(저장할 길이 예상해서 지정하는 것)

• DATE -> 날짜 취급 타입

  • CCYY-MM-DD
  • 세기 년도 월 일자

• member table 생성 (역사 연구회에 소속되어있는 멤버 정보)

CREATE TABLE member

(

member\_id INT UNSIGNED NOT NULL AUTO\_INCREMENT,

PRIMARY KEY (member\_id),

last\_name VARCHAR(20) NOT NULL,

first\_name VARCHAR(20) NOT NULL,

suffix VARCHAR(5) NULL,

expiration DATE NULL,

email VARCHAR(100) NULL,

street VARCHAR(50) NULL,

city VARCHAR(50) NULL,

state VARCHAR(2) NULL,

zip VARCHAR(10) NULL,

phone VARCHAR(20) NULL,

interests VARCHAR(255) NULL

);

• AUTO_INCREMENT 새로운 멤버가 생성될 때 마다 mysql이 자동으로 고유번호를 생성해주는 속성

• INT 해당 칼럼이 정수임을 표현

• UNSIGNED 정수를 허용하지 않는다?? ??????

• mysql은 다음 번호를 생성한다.(AUTO_INCREMENT)

• PRIMARY KEY (member_id)가 기본 키이다.

• member_id를 인덱스를 허용하겠다라는 의미.

• 속성을 확인하는 쿼리문(5가지)

• DESCRIBE president;

• DESC president;

  • 위 두개의 쿼리는 동일한 결과를 보여준다.
  • 같은 기능이다.

• EXPLAIN president;

• SHOW COLUMNS FROM president;

• SHOW FIELDS FROM president;

  • 다섯개 다 사용 가능.

• 속성, 데이터를 확인하는 이유

  • 해당 테이블 구조를 확인할 수 있다.
  • 테이블 내의 칼럼명, 데이터 타입, 칼럼 길이 등을 알고 싶을 때 사용
  • mysql이 칼럼을 저장하는 순서를 확인할 때 사용.
  • INSERT 구문을 사용할 때, 디폴트 칼럼 순서를 유지해야 하기 때문에 순서가 중요하다.

• SHOW COLUMNS FROM president;

• SHOW FIELDS FROM president;

  • 위 두개의 경우에는 다른 코드를 붙여 사용할 수 있다.
  • 예시)
  • SHOW FIELDS FROM president LIKE '%name';
    • 의미 : name으로 끝나는 칼럼을 보여준다.
    • %부분은 name 앞에 어떠한 것이 와도 상관없다. 어떠한 것이 와도 괜찮다.
    • 끝이 name으로 끝나는 칼럼만을 보여달라는 의미이다.

• DESCRIBE president '%name';

  • 이라고 입력해도 name으로 끝나는 칼럼의 속성을 확인할 수 있다.

• SHOW FULL COLUMNS FROM president;

  • 부가적인 아주 상세한 칼럼 정보까지 출력을 해준다.

• SHOW TABLES;

  • 디폴트 데이터베이스에 있는 테이블들을 나열해준다.

• SHOW DATABASES;

  • 현재 연결된 서버에 의해서 관리되는 데이터베이스들을 나열해준다.

• performance_schema
  • mysql 설치과정에서 생성된 데이터베이스
  • (나는 mysql을 설치하지 않아서 확인되지 않음)
    • mysql 접근 권한에 따라서 어떤 사용자가 무엇을 할 수 있는지 그러한 권한을 제어하는 테이블을 가지고 있는 데이터베이스.

성적관리 테이블을 만들기 위한 준비

• 학생 테이블에서 기본적인 학생의 이름과 성별, 학생 번호를 관리하고 성적 데이터를 연결시켜 사용할 수 있다.

• 어떤 시험에 대한 성적인지를.grade_event 시험날짜, 어떤 시험인지 등 가변길이문자열 칼럼(name~)

• ENUM(열거형) 여학생이면 F, 남학생이면 M을 입력할 수 있다.

• 다른 타입을 사용할 수도있지만 ENUM이 가장 적절하다고 할 수 있다.

• 한문자로 나타냈지만 단어로 나타내도 상관없다.

• PRIMARY KEY(중복하지 않겠다)

• mysql 이 테이블을 생성하기 위해서 사용할 스토리지 엔진의 이름을 뜻한다.(ENGINE)

  • 특정 종류의 테이블을 관리하는 핸들러로써 mysql에서 흔히 사용하는 엔진은 InnoDB와 추가로 한개 더 있지만 뒤에서 설명.
  • 참조무결성 특성을 이용하기 위해서 지금은 엔진을 InnoDB로 했다
    • 정도로만 알면 된다.

'참조무결성'이란?

• score에 존재하는 id는 학생 테이블에 있는 id 중 하나여야 한다.
• 아니면 아예 데이터가 존재하지 않는 NULL값이던지
  • 둘중 하나여야 한다는 것.

CREATE TABLE student

(

name VARCHAR(20) NOT NULL,

sex ENUM('F', 'M') NOT NULL,

student\_id INT UNSIGNED NOT NULL AUTO\_INCREMENT,

PRIMARY KEY (student\_id)

) ENGINE=InnoDB;

• DESCRIBE student 'sex';
  • 테이블의 원하는 칼럼을 입력하면 enum 칼럼에 대해서 mysql은 입력 가능한 열거형의 값들을 보여주게 된다.

CREATE TABLE grade_event

(

date DATE NOT NULL,

category ENUM('T', 'Q') NOT NULL,

event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

PRIMARY KEY (event\_id)

) ENGINE=InnoDB;

• T는 test Q는 Quiz의미하는.
• 둘 중에 하나를 선택할 수 있다.

CREATE TABLE score
(

student_id INT UNSIGNED NOT NULL,

event_id INT UNSIGNED NOT NULL,

score INT NOT NULL,

PRIMARY KEY (event_id, student_id),

INDEX(student_id),

FOREIGN KEY (event_id) REFERENCES grade_event (event_id),

FOREIGN KEY (student_id) REFERENCES student (student_id)

) ENGINE=InnoDB;

• 소수점까지 사용하려 한다면 데시멀과 같은 데이터 타입을 사용하면 된다

  • (INT 대신)

• 이벤트에 의해, 학생 아이디는 중복될 수 없다..?

• FOREIGN KEY (event_id) 칼럼의 값은 반드시 grade_event 테이블의 event_id 칼럼값과 매칭되어야 한다.

  • 라는 의미이다.

• 참조하고 있다.라고 해석한다.

• INDEX는 검색을 빠르게 할 수 있도록 도와준다.

  • 명시적으로 사용할 수 있다.

CREATE TABLE absence

(

student\_id INT UNSIGNED NOT NULL,

date DATE NOT NULL,

PRIMARY KEY (student\_id, date),

FOREIGN KEY (student\_id) REFERENCES student (student\_id)

) ENGINE=InnoDB;

• score, absence 테이블은 student와 grade_event 테이블을 참조하므로 후자 2개가 먼저 생성되어야 사용할 수 있다.

• 반대로 student와 grade_event를 삭제하기 위해서는 이 둘을 참조하고 있는 score와 absence를 먼저 삭제한 후 삭제해야 한다.

• mysqlshow -umyalzio -p myalzio

  • 로그인하지 않도 데이터베이스 테이블을 확인하는 것....

• select * from student;

INSERT 문

• mysql에 접속해서 실행시키는 것

• INSERT INTO 테이블명 VALUES('Kim1', 'M', NULL);

• NULL을 입력하면 자동으로 AUTO_INCREMENT 다음 번호를 생성한다.

• 순서대로 각 칼럼 값을 입력해주면 된다.

  • 저장된 순서대로.

• 순서가 정확히 기억나지 않는다면 테이블 칼럼 순서를 조회해서 입력한다.

• INSERT INTO grade_event VALUES('2020-01-01', 'Q', NULL);

• 큰 따옴표를 사용해도 되지만 일반적으로 작은 따옴표를 많이 사용한다.

• select * from student;

• select * from grade_event;

• 예시)

  • INSERT INTO student VALUES('Kim2', 'F', NULL),('Kim3', 'M', NULL);
  • 이렇게 한 행에 여러 값을 입력할 수도 있다.

INSERT 쿼리 입력

• INSERT 쿼리를 줄일 수 있고, 서버가 더 효율적으로 작동할 수 있다.

• 한 행씩 괄호를 묶어서 사용하도록 한다.

• INSERT INTO student (name, sex) VALUES('Lee1','M');

  • 이것도 입력

• 테이블 칼럼중 원하는 칼럼만 지정해서 값을 입력할 수도 있다.

• 칼럼에 맞게 데이터를 입력해주면 된다.

• 빠져도 되는 값들은 물론 NOT NULL이 아닌 값들만 뺄 수 있다.

• student_id는 자동생성으로 입력해주지 않아도 된다.!

• 마지막에 select * from student; 조회해보기.

• 입력해서 입력한 값들을 확인해본다.

• INSERT INTO student SET name='Lee2', sex='F';

• INSERT문을 이렇게도 사용할 수 있다.

• INSERT INTO score( event_id, student_id, score) VALUES(999,999,0);

  • 에러가 나는 쿼리이다.
    • 이유: 반드시 student_id, event_id가 있는 값을 사용해야한다.

• 제약 조건에 걸려서 값을 넣지 못하게 되는 것이다.