Mysql 명령어 및 데이터타입 정리

• 테이터베이스 로그인 : mysql -u <id> -p <password> (만일 root 패스워드 잃어버렸을 경우 재설정 방법, APMSetup으로 설치하면 root default 암호 : apmsetup)
• 테스트 데이터베이스 생성 : create database <database명>;   (참조)
• 생성 내역 보기 : show databases;  (참조)
• 데이터 베이스 사용하기 : use <database명>;
• 테이블 보기 : show tables;  

mysql> create database ajax;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ajax               |

| mysql              |

| phpmyadmin         |

+--------------------+

4 rows in set (0.00 sec)

mysql> use ajax;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table comment(

    ->    id  int primary key,

    ->    name varchar(20) not null,

    ->    content long varchar not null

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> create table id_repository(

    ->    name varchar(2) primary key,

    ->    value int

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into id_repository values('commnet', 0);

Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 기본 명령어 정리

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 설정에서 bind-address 항목을 살펴본다.

비밀번호 변경

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

# mysqladmin 이용
mysqladmin -u root password 새비밀번호

# update문 이용
mysql -u root mysql
----
mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;

# Set Password 이용
mysql> SET PASSWORD FOR root=password('새비밀번호');

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

사용자 추가/삭제

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';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.

특정 권한만 주려면, 아래와 같은 형태로 권한을 나열한다.

GRANT INSERT,UPDATE,SELECT ON dbname.* TO username@'localhost' IDENTIFIED BY 'pwd';

MySQL error 1045(28000): Access denied for user ...에 따르면 '%'란 localhost 를 제외한 모든 호스트를 뜻한다고 한다. 즉, localhost에 대해서는 명시적으로 따로 권한을 지정해야 한다.

CREATE USER를 통한 사용자 생성이 안될 경우에는 mysql - ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost' 참조하여, 먼저 생성하려던 사용자를 drop 하고 재시도 해본다.

drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'

불필요한 사용자 삭제는

mysql> DLETE 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 PRIMARY KEY AUTO_INCREMENT,
  column_name2 VARCHAR(15) NOT NULL,
  column_name3 INT
) ENGINE=INNODB;

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

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;

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

mysql> SELECT * FROM grade LIMIT 10;

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

mysql> SELECT * FROM grade LIMIT 100, 10;

UPDATE

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

DELETE

mysql> DELETE FROM tablename WEHRE 조건

mysql에서 쿼리 결과 세로로 보기

-E 옵션을 줘서 실행한다.

mysql -E -u root -p mysql

mysql에서 발생한 오류나 경고 다시 보기

mysql> SHOW errors;
mysql> SHOW warnings;

show processlist

• show processlist
• 접속한 커넥션의 상태를 보여준다.

SHOW FULL processlist;
SHOW FULL processlist\G;

MySQL 버전 알아보기

SHOW VARIABLES LIKE "%version%";

CREATE TABLE 구문 보기

SHOW CREATE TABLE [테이블이름]\G

테이블/컬럼 정보보기

• SHOW TABLE STATUS

-- 전체 테이블 정보를 주석까지 포함해 보여준다.
SHOW TABLE STATUS;

SHOW TABLE STATUS like 'member%'; -- 테이블 이름 매칭 조건

-- 테이블의 컬럼 정보를 주석까지 포함해 보여준다.
SHOW FULL COLUMNS FROM [테이블이름];

테이블/컬럼의 주석(Comment) 지정

-- 테이블의 주석 변경
ALTER TABLE [테이블이름] COMMENT = '테이블설명';

-- 컬럼의 주석 변경
ALTER TABLE [테이블이름] CHANGE COLUMN [컬럼이름] [새컬럼이름] ...여러 속성... COMMENT '새 주석';

데이터 타입

 

• Version : Mariadb 5.5.4.2-WinX64

 

MySQL / MariaDB 데이터 타입에 대해서 알아본다.

 

[숫자형 데이터 타입]

데이터 타입	의미	크기	설명
TINYINT	매우 작은 정수	1 byte	-128 ~ 127 (부호없이 0 ~ 255)
SMALLINT	작은 정수	2 byte	-32768 ~ 32767
MEDIUMINT	중간 크기의 정수	3 byte	-(-8388608) ~ -1(8388607)
INT	표준 정수	4 byte	~ -1
BIGINT	큰 정수	8 byte	- ~ -1
FLOAT	단정도 부동 소수	4 byte	-3.40E+45 ~ 3.40E+45 (no unsigned)
DOUBLE	배정도 부동 소수	8 byte	-1.7976E+320 ~ 1.7976E+320 (no unsigned)
DECIMAL(m,n)	고정 소수	m과 n에 따라 다르다	숫자 데이터지만 내부적으로 String형태로 저장됨. 최대 65자.
BIT(n)	비트 필드	m에 따라 다르다

 

[날짜형 데이터 타입]

데이터 타입	의미	크기	설명
DATE	CCYY-MM-DD	3 byte	1000-01-01 ~ 9999-12-31
DATETIME	CCYY-MM-DD hh:mm:ss	8 byte	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP	CCYY-MM-DD hh:mm:ss	4 byte	1970-01-01 00:00:00 ~ 2037
TIME	hh:mm:ss	3 byte	-839:59:59 ~ 839:59:59
YEAR	CCYY 또는 YY	1 byte	1901 ~ 2155

 

TIMESTAMP는 현재 시간이 자동으로 들어가는 데이터 타입니다.

create table TS_Test( Num int PRIMARY KEY, TS TIMESTAMP);   insert into TS_TEST (NUM) values (1); select * from TS_TEST;

 

 

 

[문자형 데이터 타입]

데이터 타입	의미	크기	설명
CHAR(n)	고정길이 비이진(문자)문자열	n byte
VARCHAR(n)	가변 길이 비이진 문자열	Length + 1 byte
BINARY(n)	고정길이 이진 문자열	n byte
VARBINARY(n)	가변 길이 이진 문자열	Length + 1 byte or 2 byte
TINYBLOB	매우작은 BLOB(Binary Large Object)	Length + 1 byte
BLOB	작은 BLOB	Length + 2 byte	최대크기 64KB
MEDIUMBLOB,	중간 크기 BLOB	Length + 3 byte	최대크기 16MB
LONGBLOB	큰 BLOB	Length + 4 byte	최대크기 4GB
TINYTEXT	매우 작은 비이진 문자열	Length + 1 byte
TEXT	작은 비이진 문자열	Length + 2 byte	최대크기 64KB
MEDIUMTEXT	중간 크기 비이진 문자열	Length + 3 byte	최대크기 16MB
LONGTEXT	큰 비이진 문자열	Length + 4 byte	최대크기 4GB

 

BLOB의 경우는 바이너리 데이터이므로 이미지 파일 등도 저장기 가능하다. 처리성능이 높지 않으않으 사용할 때 주의한다.

create table img_table( NUM int primary key auto_increment, filename varchar(64), image MEDIUMBLOB NOT NULL );   insert into img_table(image,filename) values (load_file("C:\\sql_data\\Daily_report.JPEG"), 'Daily_report.JPEG');   select * from img_table;   select image into DUMPFILE 'c:\\sql_data\\dump.jpg' from img_table where filename = 'Daily_report.JPEG';

 

 

 

 

[공간형 데이터 타입]

데이터 타입	의미
GEOMETRY	공간 값
POINT	포인트 값(한 쌍의 x,y 좌표)
LINESTRING	커브 값(하나 이상의 POINT값)
POLYGON	다각형
GEOMETRYCOLLECTION	기하학(GEOMETRY) 값의 집합
MULTILINESTRING	LINESTRING 값의 집합
MULTIPOINT	POINT 값의 집합
MULTIPOLYGON	다각형 값의 집합

 

[출처] MySql 명령어, 데이터타입|작성자 갓제타