게시 됨 업데이트 됨Programming / Database22분안에 읽기 (약 3291 단어)

MySQL 5.x 시작 (3)

MySQL

사용자 관리

mysql-server 를 설치하며 만든 root 사용자 패스워드를 사용해서 데이터베이스에 접속한다.

1
$ mysql -u root -p

사용자 데이터베이스

사용자가 사용할 데이터베이스를 만든다.

1
2
mysql>create database mydb;
Query OK, 1 row affected (0.00 sec)

그리고 CREATE USER, INSERT 로 새 사용자를 추가할 수 있다.

create user userid@HOST identified by ‘PASSWORD’;

사용자 foo 를 localhost 와 모든 것을 의미하는 패턴 %로 추가하면:

1
2
mysql > create user foo@localhost identified by 'password';
mysql > create user 'foo'@'%' identified by 'password';

혹은

1
insert into user (host, user, password) values ('localhost', 'hiru', 'password('hirururu'));

사용자 제거
mysql > drop user ‘hiru’;
mysql > delete from user where user =’hiru’

사용자 생성시 다음같이 1396 에러는 CREATE USER/GRANT 명령으로 사용자와 권한을 추가/관리해야 하는데 mysql.db, mysql.user 테이블을 직접 조작하다가 일관성이 깨졌기 때문

1
2
mysql> create user 'shopuser'@'localhost' identified by ')12345';
ERROR 1396 (HY000): Operation CREATE USER failed for 'shopuser'@'localhost'

제대로 사용자를 삭제하고

drop user shopuser@localhost

flush privileges; 로 갱신해 준다.

권한 주기

권한을 추가하고 삭제하기 위해서, GRANT와 REVOKE의 명령을 사용한다. GRANT 명령 등으로 데이터베이스 사용자가 데이터베이스 자원에 접근하는 권한을 만들 수 있다.

GRANT ALL privileges ON DB_NAME.TABLE TO USER_ID@[HOST] IDENTIFIED BY ‘PASSWORD’
GRANT [SELECT,DELETE,INSERT,UPDATE,] ON DB_NAME.TABLE TO USER_ID@[HOST] IDENTIFIED BY ‘PASSWORD’

  • DB_NAME,TABLE 등에 * 패턴을 사용할 수 있다.
  • HOST: 접근하는 소스 호스트
  • PASSWORD: 패스워드

http://www.w3big.com/ko/mysql/mysql-administration.html

현재 머신에서만 접속할 수 있는 사용자 계정, 외부, 원격에서 접속할 수 있는 사용자 계정을 추가해 준다.

1
2
3
mysql> use mysql; # mysql system db
mysql> GRANT ALL privileges ON mydb.* TO foo@localhost IDENTIFIED BY '*****';
mysql> GRANT ALL privileges ON mydb.* TO foo@'%' IDENTIFIED BY '*****';

혹은

1
2
grant select, insert, update, delete on mydb.* to foo@host identified by 'password';
mysql > grant select, insert, update, delete on dbname.table to userid@'192.168.%' identified by 'password';

권한을 확인하는 방법

1
2
mysql > show grants for foo@localhost
mysql > show grants for 'foo'@'%';

변경된 권한을 적용하기

1
mysql > flush privileges;

권한을 삭제하는 방법

1
mysql > revoke all on dbname.table from username@host

추가한 사용자는 SELECT로 확인할 수 있다.

1
mysql> select host,authentication_string from user where user='foo';

사용자 데이터베이스 사용

새로 생성한 사용자 ID로 로그인을 해서 데이터베이스 정보를 확인해 보자.

1
2
3
4
5
6
7
8
9
10
$ mysql -u foo -p
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
+--------------------+

그리고 데이터베이스를 사용하려면 use [DATABASE] 로 변경한다.

1
mysql> use mydb;

Character Set

mysql에서 한글이 ?로 표시되는 경우.

1
2
3
4
5
6
7
8
mysql> SELECT * FROM department;
+----+------+
| id | name |
+----+------+
|  1 | ???  |
|  2 | ???  |
+----+------+
2 rows in set (0.00 sec)

my.cnf의 문자셋과 터미널 문자셋이 일치하지 않아서 그렇다. MySQL은 설치시 지정하지 않았다면 기본적으로 문자셋이 ‘latin1’으로 설정되어 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | latin1_swedish_ci          |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
| core_file                | OFF                        |
+--------------------------+----------------------------+
15 rows in set (0.00 sec)

MySQL 설정 파일에서 문자셋을 변경할 수 있다. 다믕 같이 자신의 my.cnf 파일을 작성한다. client, mysqld, mysql 에 대해서 utf8 사용을 선언해 준다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[client]
..
default-character-set=utf8

[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

mysql> show variables like ‘char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

사용자 관리

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO ‘zara‘@’localhost’
-> IDENTIFIED BY ‘zara123’;

SQL

Structural Query Language로 튜플 간의 관계를 계산해서 결과를 도촐한다.

  • SEQUEL: Structured English QUEry Language; part of SYSTEM R, 1974
  • SQL/86: ANSI & ISO standard
  • SQL/89: ANSI & ISO standard
  • SQL/92 or SQL2: ANSI & ISO standard
  • SQL3: in the works…
  • SQL2 supported by ORACLE, SYBASE, INFORMIX, IBM DB2, SQL SERVER, OPENINGRES,…

SQL의 구성

  • SQL consists of the following parts:
  • Data Definition Language (DDL)
  • Interactive Data Manipulation Language (Interactive DML)
  • Embedded Data Manipulation Language (Embedded DML)
  • Views
  • Integrity
  • Transaction Control
  • Authorization
  • Catalog and Dictionary Facilities

교수 학습 연습

데이터 유형

http://www.w3big.com/ko/mysql/mysql-data-types.html

table professor

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
scode varchar(4) 학번, 기본키
sname varchar(20) 이름
sdept varchar(20) 학과
sphone varchar(15) 전화번호 
1
2
3
4
5
6
CREATE TABLE professor (
    pcode varchar(4) NOT NULL PRIMARY KEY,
    pname varchar(10),
    pdept varchar(12),
    pphone varchar(8)
);
table student

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
scode varchar(4) 학번, 기본키
sname varchar(20) 이름
sdept varchar(20) 학과
sphone varchar(15) 전화번호

create student table

1
2
3
4
5
6
CREATE TABLE student (
    scode char(4) NOT NULL PRIMARY KEY,
    sname char(10),
    sdept char(12),
    sphone char(8)
);
table course

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
ccode varchar(4) 과목코드, 기본키
cname varchar(20) 과목명
ctime int 강의 시수
croom varchar(15) 강의실

create course table

1
2
3
4
5
6
CREATE TABLE course (
    ccode varchar(4) NOT NULL PRIMARY KEY,
    cname varchar(10),
    ctime integer,
    croom varchar(8)
);
table lecture

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
lpcode varchar(4) 교수코드, 기본키
lccode varchar(4) 과목코드, 기본키

create lecture table

1
2
3
4
5
CREATE TABLE lecture (
    lpcode char(4) NOT NULL,
    lccode char(4) NOT NULL,
    PRIMARY KEY (lpcode, lccode)
);
table advice

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
apcode varchar(4) 교수코드, 기본키
asccode varchar(4) 학번, 기본키

create advice table

1
2
3
4
5
CREATE TABLE advise (
    apcode char(4) NOT NULL,
    ascode char(4) NOT NULL,
    PRIMARY KEY (apcode, ascode)
);
table register

파일 Python-Database-class-student.sql

컬럼명 테이터 타입 비고
rscode varchar(4) 학번, 기본키
rcccode varchar(4) 과목코드, 기본키

create register table

1
2
3
4
5
CREATE TABLE register (
    rscode char(4) NOT NULL,
    rccode char(4) NOT NULL,
    PRIMARY KEY (rscode, rccode)
);

데이터 입력

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P001','김 구','컴퓨터공학과','0001');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P002','안창호','컴퓨터공학과','0002');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P003','이육사','국문학과','0003');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P004','박종화','국문학과','0004');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P005','심 훈','사학과','0005');
INSERT INTO professor (pcode, pname, pdept, pphone)\
        VALUES ('P006','한용운','사학과','0006');

외부 데이터 파일 이용

파일에 필드 간의 ‘,’로 구분한 데이터 파일이 있다. 물론 필드 구별 문자는 데이터의 내용에 따라 사용자가 임의로 정 할 수 있다.
파일: studens.txt

1
2
3
4
5
6
S001, 박소명, 컴퓨터공학과, 123-4567
S002, 최민국, 컴퓨터공학과, 234-5678
S003, 이승호, 국문학과, 345-6789
S004, 정수봉, 국문학과, 456-7890
S005, 김상진, 사학과, 567-8901
S006, 황정숙, 사학과, 678-9012

studnets.txt 파일을 읽어들이는 것은 스크립트 파일을 작성하거나 mysql에서 직접 실행할 수 있다.
먼저 스크립트 파일 students.sql은 다음과 같다.

1
2
3
use mydb;
load data local infile "student.txt" into table student
fields terminated by ',' ;

이제 mysql 클라이언트에서 데이터를 읽어 들인다.

1
2
mysql>source students.sql;

데이터 조회

SELECT [DISTINCT] select _expr essi on
FROM table_list
WHERE where_definition
ORDER BY col_name [ASC|DESC]
GROUP BY col _name_list
LIMIT [offset ], rows

예제-1) 전체 교수 리스트를 출력하는 SQL 검색 문을 작성하라.
mysql > sel ect * fromprof;

예제-2) 전체 교수 리스트를 이름순서로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pname;

예제-5) 전체 교수 리스트를 이름 역순으로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pname desc;

전체 교수 리스트를 학과별로 출력하는 검색 문을 작성하라.
mysql > sel ect * fromprof order by pdept, pname;

예제-4) 국문학과 교수 리스트를 이름순서로 출력하는 검색 문을 작성하라.
mysql> select * from professor where pdept =’국문학과’;

JOIN

“FROM 테이블명 AS 별명” 구문은 SQL 문장에서 별명으로 테이블을 참조하 는 역할은 한다.

예제-6) MySQL 과목을 강의하는 교수님의 이름, 전화번호와 강의실을 검색 하는 문장을 작성하라.
mysql> select p.pname, p.pphone, c.croom from professor p, course c, lecture l
where c.cname=’MySQL’ and c.ccode=l.lccode
and l.lpcode=p.pcode;

예제-7) ‘김구’ 교수님이 강의하는 과목명, 강의 시수와 강의실을 검색하는 문장을 작성하라.

1
2
3
4
select c.cname, c.ctime, c.croom
from professor as p, course as c, lecture as l
where p.pname = '김 구' and p.pcode = l.lpcode
     and l.lccode = c.ccode;

예제-8) 각 학생이 수강 신청한 과목에 대해서 학생이름, 전화번호, 과목명, 강의실, 강의 시수를 검색하는 문장을 작성하라.

1
2
3
4
select s.sname, s.sphone, c.cname, c.ctime, c.croom
from student as s , course as c, register as r
where s.scode = r.rscode and r.rccode = c.ccode
order by s.sname, c.cname;

sub-query

예제-9) 각 학생이 신청한 총 학점을 구하는 검색식을 작성하라.

1
2
3
4
select s.sname, s.sdept, s.sphone, sum(c.ctime)
from student as s , course as c, register as r
where s.scode = r.rscode and r.rccode = c.ccode
group by s.sname;
  • WHERE 조건절에 해당하는 결과를 GROUP BY 구절에 명시된 s.sname 필드에 따라 그룹으로 결과를 분류하고 난 후, SELECT 필드에 SUM(c.cti me) 함수를 사용해서 c.cti me 필드에 대한 합을 구함으로써 각 학 생이 신청한 총 학점를 구할 수 있다.

예제-10) 각 학과별 교수님은 몇 분인지 구하는 검색식을 작성하라.

1
2
3
select pdept, count(*)
from professor
group by pdept;

LIMIT 구절

예제-11) 페이지 크기가 2 일 때, (예제-8)의 결과에서 두 번째 페이지를 검색하는 SQL문장은 작성하라.
select s.sname, s.sphone, c.cname, c.ctime, c.croom fromstudent as s , course as c, regi ster as r
where s.scode = r.rscode and r.rccode = c.ccode order by s.sname, c.cname
limit 2, 2;

  • 마지막 행의 limit 2, 2구절에서, 첫 번째 인자는 오프셋(offset)으로 검 색 결과 레코드들의 순번을 의미한다. 오프셋 값은 0 부터 지정하기 때문에 오프셋값2는전체레코드중에서세번째레코드를가리킨다. 두번째는 인자는 출력하는 레코드 수(rows)를 의미한다. 따라서, 레코드 수 2 는 2 개 의 레코드를 출력하라는 의미가 된다.

Update

UPDATE tbl_name
SET col_name1 = expr1, col_name2 = expr2, …
[WHERE where_definition] [LIMIT rows];

예제-12) 교수테이블에서 ‘김 구’ 선생님의 이름을 ‘하은용’ 교수님으로 변 경하는 문장을 작성하라.
update prof set pname =’하은용’ where pname =’김구’;

예제-13) 지도 테이블의 교수코드가 ‘P007’ 인 레코드들을 모두 ‘P005’ 로 변경하라.
update advise set apcode =’P005’ where apcode =’P007’;

예제-14) 강의 시수가 2인 과목들의 강의 시수를 하나 증가 시키고, 강의실 을 Lab1로 변경하라.
update course set ctime=ctime + 1, croom=’Lab1’ where ctime=2;

Delete

DELETE FROM tbl_name
[WHERE where_definition] [LIMIT rows]

예제-15 ) 국문학과 학생 레코드를 삭제하는 문장을 작성하라.
delete fromstudent where sdept =’국문학과’;

PyMySQL 튜토리얼

PyMySQL 설치

1
$ pip install PyMySQL

만약 pip 로 설치가 안되면 다음 같이 setup.py를 이용해 직접 설치 할 수 있다.
$ # X.X is the desired PyMySQL version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

1
2
3
4
5
6
CREATE TABLE users (
    'id' int(11) NOT NULL AUTO_INCREMENT,
    'email' varchar(255) COLLATE utf8_bin NOT NULL,
    'password' varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

데이터베이스 삭제

1
mysql> drop database mydb;

사용자 비밀번호 변경

1
mysql> set password for ''myid''@''localhost'' = password(''password'');

테이블 생성

show 명령으로 데이터베이스 자원 현환을 볼 수 있다.

mysql> help show

다음은 데이터베이스 목록을 보고, ‘mydb’를 사용합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql>use mydb
...

테이블 현황

1
2
3
4
5
6
7
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

테이블 만들기

1
2
3
4
5
6
7
8
mysql>CREATE TABLE users (\
    id int(11) NOT NULL AUTO_INCREMENT,\
    email varchar(255) COLLATE utf8_bin NOT NULL,\
    password varchar(255) COLLATE utf8_bin NOT NULL,\
    PRIMARY KEY (id)\
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin\
AUTO_INCREMENT=1 ;

  • mysql> 터미널에서 여러 줄의 명령을 입력하기 위해서 줄의 끝에 ‘'를 사용해서 여러줄을 입력했다.

이럴게 만들어진 테이블은 show 명령으로 작성 스크립트를 확인할 수 있다.

1
2
3
4
5
6
7
8
9
mysql> show create table users;
| Table | Create Table                                                         | users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `password` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

1 row in set (0.00 sec)

테이블의 구성 요소는 desc 명령을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
mysql> desc users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

데이터 입력

1
2
3
4
mysql> insert into users values (0, 'aaa@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'bbb@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'ccc@example.com', 'sldfjslfj');
mysql> insert into users values (0, 'ddd@example.com', 'sldfjslfj');

Alter

1
2
3
4
5
6
7
8
mysql> #컬럼 추가
mysql> alter table users add first_name varchar(10);
mysql> alter table users add last_name char(10);
mysql> alter table users add point int(5);
mysql> alter table users add gener int(5);

mysql> #컬럼 삭제
mysql> alter table users drop gener;

참조

MySQL 5.x 시작 (3)

https://blog.thinkbee.kr/mysql5-3-start-2b49208d5af4/

Author

Gangtai Goh

Posted on

2017-03-11

Updated on

2023-05-12

Licensed under

#

이 글이 마음에 드시나요? 다음을 통해 후원하실 수 있습니다:

댓글

최근 글

광고

×