• MySQL Data Type

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 항목을 살펴본다.

원격 서버 접속

# hostname:3306
mysql --protocol=tcp -h 'hostname' -P 3306 -u [username]  -p [database-name]

• ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol 오류 발생.
• SSL 접속이 기본값으로 바뀌면서 SSL 설정이 안 된 서버 접속시 오류 발생 --ssl-mode=disabled 옵션 주면 일반 접속으로 됨.
• ssl - Cannot conect MySQL (error 2026) after upgrade to Ubuntu 20.04 - Server Fault

혹은 my.cnf 에 다음 설정을 한다.

[client] 
ssl-mode=DISABLED

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 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

-- 현재 사용자 목록
use mysql;
select User,Host from user;

-- 권한 확인 'username'@'hostname'
SHOW GRANTS FOR user_name;
SHOW GRANTS FOR 'username'@'%';

• MySQL :: Security in MySQL :: 5.2 Adding User Accounts
• MySQL :: MySQL 5.6 Reference Manual :: 13.7.1.6 REVOKE Syntax

-- 생성 후 필수 권한만 부여. - 실서비스 계정은 가급적 이렇게 필요 권한만 부여할 것.
CREATE USER username@localhost IDENTIFIED BY 'userpassword'
GRANT SELECT,INSERT,UPDATE,DELETE ON dbname.* TO 'username'@'localhost';
 
-- 생성과 전체 권한 일괄 부여
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, DELETE,SHOW DATABASES, SHOW VIEW ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'pwd';

-- 권한 뺐기
REVOKE CREATE,DROP,ALTER,CREATE VIEW,ALTER ROUTINE,CREATE ROUTINE
  ON dbname.* 
  FROM 'user'@'%';

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

CREATE USER를 통한 사용자 생성이 안될 경우에는 mysql - ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost' 참조하여, 먼저 생성하려던 사용자를 drop 하고 재시도 해본다. 이 현상이 나타나는 이유는, CREATE USER/GRANT 명령으로 사용자와 권한을 추가/관리해야 하는데 mysql.db, mysql.user 테이블을 직접 조작하다가 일관성이 깨졌기 때문이다. 가급적 mysql의 계정/권한 테이블에 대한 직접 조작은 하지 말아야 한다.

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> CREATE DATABASE IF NOT EXISTS dbname;
 
mysql> CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
# 현재 존재하는 데이터베이스 목록을 보여준다.
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
--------------

SELECT SUBSTRING_INDEX(HOST,':',1) AS host, COUNT(*) AS cnt
FROM information_schema.processlist
GROUP BY SUBSTRING_INDEX(HOST,':',1);

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

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;

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

mysql> DELETE FROM tablename WEHRE 조건

• MySQL Delete Join | DELETE with INNER JOIN and LEFT JOIN Examples

-- DELETE 바로 뒤에 삭제할 테이블을 적는다.
-- INNER JOIN
DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
 
-- LEFT JOIN
DELETE T1
FROM T1 
LEFT JOIN T2 ON T1.key = T2.key 
WHERE T2.key IS NULL;

• MySQL 이 join 순서 최적화를 잘 못할경우 무조건 왼쪽 테이블 먼저 조회하게 강제하는 옵션
• 인덱스 힌트와 비슷한 역할을 하므로, 가급적 사용하지 않는게 좋다.

-- C, A, B 순서로 데이터를 읽어서 조인한다.
SELECT STRAIGHT_JOIN * FROM C
LEFT JOIN A
ON C.A_ID = A.A_ID
LEFT JOIN B
ON C.B_ID = B.B_ID  
ORDER BY <가급적 C테이블의 컬럼>

• MySql] JOIN 속도를 빠르게! EXPLAIN, STRAIGHT_JOIN

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

mysql -E -u root -p mysql

mysql> SHOW errors;
mysql> SHOW warnings;

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

SHOW FULL processlist;
SHOW FULL processlist\G;

SHOW VARIABLES LIKE "%version%";

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

• SHOW TABLE STATUS

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

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

-- 테이블의 컬럼 정보를 collation 등 아주 상세하게, 주석까지 포함해 보여준다. 
SHOW FULL COLUMNS FROM [테이블이름];

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

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

information_schema 의 TABLES, COLUMNS를 쿼리하여 테이블, 컬럼 정보를 살펴볼 수 있다.

select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, c.COLUMN_COMMENT from `TABLES` t
	inner join `COLUMNS` c on t.TABLE_NAME = c.TABLE_NAME
order by t.TABLE_SCHEMA, t.TABLE_NAME

• 초보개발자 이야기. :: MySQL : 필드(Field)를 구분자로 묶어 출력하기 (CONCAT)

SELECT keyword, GROUP_CONCAT( syn SEPARATOR  '-' )  -- syn 컬럼들이 '-' 로 묶인 문자열로 나온다.
FROM syn_common
GROUP BY keyword

• GROUP BY를 하면서, COUNT(DISTINCT colName) 을 하면, 동일 그룹에 속하는 colName 이 중복은 제거 상태로 카운팅 된다.
• GROUP BY functions - COUNT(DISTINCT)

• 특정 데이터베이스 스키마에 있는 테이블들의 행수를 조회한다.

SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='<schema_name>'
ORDER BY TABLE_ROWS DESC;