'knowhow'에 해당되는 글 92건

MySQL의 기본 명령어와 SQL문 정리. 내가 RDBMS에 완전 문외한이기 때문에 기본 SQL쿼리까지...
Redhat Linux 7.3, MySQL 3.23.58 에서 테스트함.
설치는 Redhat 기본 제공 RPM으로 했다.

데이터베이스 접속

$ mysql -u 사용자명 -p dbname

설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.

$ mysql -u root mysql

비밀번호 변경
MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

1) mysqladmin이용

$ mysqladmin -u root password 새비밀번호

2) update문 이용

$ mysql -u root mysql

mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;

3)Set Password 이용

SET PASSWORD FOR root=password('새비밀번호');

일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

사용자 추가/삭제

mysql> GRANT ALL PRIVILEGES ON dbname.* TO username @ localhost IDENTIFIED BY 'password';

username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는
데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면

GRANT ALL PRIVILEGES ON dbname.* TO username @ '%' IDENTIFIED BY 'password';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.
추가 : '%'를 호스트네임으로 지정해도 모든 호스트에서 접속할 수 없었다. 각 호스트별로 다 지정해야 했다.

불필요한 사용자 삭제는

mysql> DELETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;

데이터베이스 생성/보기

데이터베이스를 생성하고,

mysql> CREATE DATABASE dbname;

현재 존재하는 데이터베이스 목록을 보여준다.

mysql> SHOW DATABASES;

특정 데이타베이스를 사용하겠다고 선언한다.

mysql> USE dbname;

쓸모 없으면 과감히 삭제한다.

mysql> DROP DATABASE [IF EXISTS] dbname;

IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.

테이블 생성/보기

테이블을 생성하고,

mysql> CREATE TABLE tablename (
column_name1 INT,
column_name2 VARCHAR(15),
column_name3 INT );

현재 데이타베이스의 테이블 목록을 보고

mysql> SHOW TABLES;

테이블 구조를 살펴본다.

mysql> EXPLAIN tablesname;
혹은
mysql> DESCRIBE tablename;

이름을 잘못 지정했으면 이름을 변경할 수도 있다.

mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

필요 없으면 삭제한다.

mysql> DROP TABLE [IF EXISTS] tablename;

현재 상태 보기

mysql> status

--------------
mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 26
Current database: study
Current user: study @ localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec

Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 Flush tables: 1
Open tables: 1 Queries per second avg: 0.021
--------------

INSERT

mysql> INSERT INTO tablename VALUES(값1, 값2, ...);

혹은

mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);

SELECT

mysql> SELECT col1, col2, ... FROM tablename;

컬럼명을 *로 하면 모든 컬럼 의미.

mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;

컬럼의 이름을 바꿔서 출력.

mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;

DESC는 내림차순 ASC는 오름차순.

mysql> SELECT * FROM grade WHERE korean < 90;

조건줘서 SELECT.

mysql> SELECT * FROM grade LIMIT 10;

결과중 처음부터 10개만 가져오기

mysql> SELECT * FROM grade LIMIT 100, 10;

결과중 100번째부터 10개만 가져오기. 첫번째 레코드는 0번 부터 시작한다.

UPDATE

mysql> UPDATE tablename SET col1=새값 WEHER 조건

DELETE

mysql> DELETE FROM tablename WEHRE 조건

mysql에서 쿼리 결과 세로로 보기
-E 옵션을 줘서 실행한다.

$ mysql -E -u root -p mysql

1. mysql -h localhost -u root -p < test.sql

2. mysql> \. test.sql

1. host 컬럼에 % 로 되어 있으면 어느 IP에서든지 접속을 허용합니다. 특정한곳에서만도 제한 할수 있습니다. IP 클래스에 맞추어서 적용시켜주시면 가능합니다.

211.111.111.% 

211.111.%

211.%

이렇게요

2. 두가지의 방법이 있는데 첫번째는 user 테이블에 직접 입력/수정 하는 방법이고 두번째는 Grant / Revoke 문을 사용하는것입니다.  단 어느 방법을 사용하던지 작업후에 MySQL 서버를 다시 시작하거나 다음과 같은 명령을 수행해야 합니다.

fulsh privileges;

 직접 입력/수정 하는 방법은 insert / update /delete 문을 사용하여 직접 편집하는 것입니다. 예를 들어..

insert into user (host,user,password,.....) values ('%','test',password('test'),....);

update user set host = '%',  .... where user = 'test';

delete from user where user = 'test';

insert 는 신규입력이고 update 는 기존의 레코드를 수정, delete는 삭제 하는것입니다. insert/update/delete 문의 기본 문법은 아셔야 하며, 각각의 컬럼등을 죄다 적어야 하는 불편함이...-,.ㅡ;;;

아니면 phpMyAadmin 을 사용해본지 오래됐지만, 아마 그리드형태로 데이터를 뿌려주고 각셀에서 편집 또는 추가하는게 있을껍니다. 그것을 이용하셔도 되고요.

 grant / revoke 문은 위와 같이 사용자를 추가하고 삭제하고, 권한을 변경하는 동일한 작업을 합니다. grant 로 신규유저를 추가할경우

grant 문은 문법도 복잡하고 하지만 각 옵션을 통해서 쉽게 사용자를 추가할수 있는 장점이 있습니다. 위 쿼리로 모든 호스트에서 접속을 허용하는 모든 권한을 가진 test 유저를 생성하게 됩니다.

phpMyAdmin에 쿼리를 실행하는곳이 있으니 그곳에서 해당 쿼리를 실행하시면 됩니다. (아 지금 생각해보니 메뉴중에 사용자 관리를 하는게 있을듯도 하는군요 언어를 한글로 선택하셔서 메뉴를 잘 살펴보시길 바래요...)

TABLE에서 행을 삭제하는 세 가지 OPTION의 비교

TABLE에서 모든 행을 삭제하는 방법에는 다음과 같은 세 가지 OPTION이 있다.

1. DELETE 명령어 사용

DELETE 명령어를 사용하여 TABLE의 행을 삭제할 수 있다.
예를 들어 EMP TABLE에서 모든 행을 삭제하는 명령문은 다음과 같다.

DELETE FROM emp;

O. DELETE 문을 사용할 때 TABLE이나 CLUSTER에 행이 많으면 행이 삭제 될 때마다 많은 SYSTEM 자원이 소모된다. 예를 들어 CPU 시간,REDO LOG 영역, TABLE이나 INDEX에 대한 ROLLBACK SEGMENT 영역 등의 자원이 필요하다.
O. TRIGGER가 걸려있다면 각 행이 삭제될 때 실행된다.
O. 이전에 할당되었던 영역은 삭제되어 빈 TABLE이나 CLUSTER에 그대로 남아 있게 된다.

2. DROP 과 CREATE 명령어 사용

TABLE을 삭제한 다음 재생성할 수 있다. 예를 들어 EMP TABLE을 삭제하고 재생성하는 명령문은 다음과 같다.

DROP TABLE emp;
CREATE TABLE emp (......);

O. TABLE이나 CLUSTER를 삭제하고 재생성하면 모든 관련된 INDEX, CONSTRAINT,TRIGGER도 삭제되며, 삭제된 TABLE이나 CLUSTERED TABLE에 종속된 OBJECTS는 무효화 된다.
O. 삭제된 TABLE이나 CLUSTERED TABLE에 부여된 권한도 삭제된다.

3. TRUNCATE 명령어 사용

SQL명령어 TRUNCATE를 사용하여 TABLE의 모든 행을 삭제할 수 있다.
예를 들어 EMP TABLE을 잘라내는 명령문은 다음과 같다.

TRUNCATE TABLE emp:

O. TRUNCATE 명령어는 TABLE이나 CLUSTER에서 모든 행을 삭제하는 빠르고 효율적인 방법이다.
O. TRUNCATE 명령어는 어떤 ROLLBACK 정보도 만들지 않고 즉시 COMMIT한다.
O. TRUNCATE 명령어는 DDL 명령문으로 ROLLBACK될 수 없다.
O. TRUNCATE 명령문은 잘라 버릴 TABLE과 관련된 구조(CONSTRAINT, TRIGGER 등)과 권한에 영향을 주지 않는다.
O. TRUNCATE 명령문은 현재 TABLE에 할당된 영역을 잘라버린 후에 포함되는 TABLESPACE로 복귀되도록 지정한다.
(REUSE STORAGE, DROP STORAGE OPTION 사용)
- DROP STORAGE OPTION 사용 시 : TABLE EXTENTS 수를 MINEXTENTS의 원래 설정값으로 줄인다.
해제된 확장영역은 SYSTEM에 복귀되며, 다른 OBJECTS가 사용할 수 있다.
- REUSE STORAGE OPTION 사용 시 : 현재 TABLE이나 CLUSTER에 할당된 모든 영역이 할당된 채로 남아 있도록 지정한다.
O. TRUNCATE 명령문이 TABLE에서 ROW를 삭제하면 해당 TABLE에 걸려 있는 TRIGGER는 실행되지 않는다.

O. AUDIT 기능이 ENABLE되어 있으면, TRUNCATE 명령문은 DELETE문에 해당하는 AUDIT 정보를 생성하지 않는다. 대신 발생한 TRUNCATE 명령문에 대한 단일 AUDIT RECORD를 생성한다.

* HASH CLUSTER는 잘라버릴 수 없다.

또한 HASH CLUSTER나 INDEX CLUSTER 내의 TABLE도 개별적으로 잘라버릴 수 없다.
INDEX CLUSTER를 잘라버리면 CLUSTER에 있는 모든 TABLE의 모든 ROW가 삭제된다.
모든 ROW가 각각의 CLUSTERED TABLE에서 삭제되어야 한다면 DELETE 명령어를 사용하거나 TABLE을 삭제하고 재생성한다.