====== MySQL 기본 명령어 정리 ======
  * [[database:mysql:datatype|MySQL Data Type]]
MySQL의 기본 명령어와 SQL문 정리. 내가 RDBMS에 완전 문외한이기 때문에 기본 SQL쿼리까지...

Redhat Linux 7.3, MySQL 3.23.58 에서 테스트함.

설치는 Redhat 기본 제공 RPM으로 했다.

===== 데이터베이스 접속 =====
<code sh>
mysql -u 사용자명 -p dbname
</code>

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

<code sh>
mysql -u root mysql
</code>

외부 서버에서 접속이 불가하면 [[database:mysql:config|MySQL 설정]]에서 ''bind-address'' 항목을 살펴본다.

원격 서버 접속
<code sh>
# hostname:3306
mysql --protocol=tcp -h 'hostname' -P 3306 -u [username]  -p [database-name]
</code>

===== SSL mode 오류 =====
  * ''ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol'' 오류 발생.
  * SSL 접속이 기본값으로 바뀌면서 SSL 설정이 안 된 서버 접속시 오류 발생 ''%%--%%ssl-mode=disabled'' 옵션 주면 일반 접속으로 됨.
  * [[https://serverfault.com/jobs?so_medium=StackOverflow&so_source=SiteNav|ssl - Cannot conect MySQL (error 2026) after upgrade to Ubuntu 20.04 - Server Fault]]

혹은 ''my.cnf'' 에 다음 설정을 한다.
<code>
[client] 
ssl-mode=DISABLED
</code>

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

<code sh>
# mysqladmin 이용
mysqladmin -u root password 새비밀번호
</code>

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

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

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

===== 사용자 확인 =====
<code>
-- 현재 사용자 목록
use mysql;
select User,Host from user;

-- 권한 확인 'username'@'hostname'
SHOW GRANTS FOR user_name;
SHOW GRANTS FOR 'username'@'%';
</code>

===== 사용자 추가 / 권한 =====
  * [[https://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/adding-users.html|MySQL :: Security in MySQL :: 5.2 Adding User Accounts]]
  * [[https://dev.mysql.com/doc/refman/5.6/en/revoke.html|MySQL :: MySQL 5.6 Reference Manual :: 13.7.1.6 REVOKE Syntax]]

<code sql>
-- 생성 후 필수 권한만 부여. - 실서비스 계정은 가급적 이렇게 필요 권한만 부여할 것.
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';
</code>

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

<code sql>
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%' IDENTIFIED BY 'password';
</code>
위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.

특정 권한만 주려면, 아래와 같은 형태로 권한을 나열한다.
<code sql>
GRANT INSERT, UPDATE, SELECT, DELETE,SHOW DATABASES, SHOW VIEW ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'pwd';
</code>

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

[[http://tech.zhenhua.info/2009/01/mysql-error-104528000-access-denied-for.html|MySQL error 1045(28000): Access denied for user ...]]에 따르면 **'%'란 localhost 를 제외한 모든 호스트를 뜻한다고 한다. 즉, localhost에 대해서는 명시적으로 따로 권한을 지정해야** 한다.

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

<code mysql>
drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'
</code>

===== 불필요한 사용자 삭제는 =====
<code sql>
mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;
</code>

===== 데이터베이스 생성/보기 =====

<code sql>
# 데이터베이스를 생성하고,
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;
</code>
''IF EXISTS'' 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.

===== 테이블 생성/보기 =====
==== 테이블을 생성하고, ====
<code sql>
mysql> CREATE TABLE tablename (
  column_name1 INT PRIMARY KEY AUTO_INCREMENT,
  column_name2 VARCHAR(15) NOT NULL,
  column_name3 INT
) ENGINE=INNODB;
</code>

==== 현재 데이타베이스의 테이블 목록을 보고 ====
<code sql>
mysql> SHOW TABLES;
</code>

==== 테이블 구조를 살펴본다. ====
<code sql>
mysql> EXPLAIN tablesname;
혹은
mysql> DESCRIBE tablename;
</code>

==== 이름을 잘못 지정했으면 이름을 변경할 수도 있다. ====
<code sql>
mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];
</code>

==== 필요 없으면 삭제한다. ====
<code sql>
mysql> DROP TABLE [IF EXISTS] tablename;
</code>

===== 현재 상태 보기 =====
<code sql>
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
--------------
</code>

===== 호스트별 커넥션 갯수 =====
<code sql>
select SUBSTRING_INDEX(HOST,':',1) as host, count(*) as cnt
from information_schema.processlist
group by SUBSTRING_INDEX(HOST,':',1);
</code>

===== INSERT =====
<code sql>
mysql> INSERT INTO tablename VALUES(값1, 값2, ...);
혹은
mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);
</code>

===== SELECT =====
<code sql>
mysql> SELECT col1, col2, ... FROM tablename;
</code>

컬럼명을 *로 하면 모든 컬럼 의미.
<code sql>
mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;
</code>

컬럼의 이름을 바꿔서 출력.
<code sql>
mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;
</code>

DESC는 내림차순 ASC는 오름차순.
<code sql>
mysql> SELECT * FROM grade WHERE korean < 90;
</code>

결과중 처음부터 10개만 가져오기
<code sql>
mysql> SELECT * FROM grade LIMIT 10;
</code>

결과중 100번째부터 10개만 가져오기. **첫번째 레코드는 0번 부터 시작한다.**
<code sql>
mysql> SELECT * FROM grade LIMIT 100, 10;
</code>

===== UPDATE =====
<code sql>
mysql> UPDATE tablename SET col1=새값 WEHER 조건
</code>

===== DELETE =====
<code sql>
mysql> DELETE FROM tablename WEHRE 조건
</code>

==== DELETE with JOIN ====
  * [[http://www.mysqltutorial.org/mysql-delete-join/|MySQL Delete Join | DELETE with INNER JOIN and LEFT JOIN Examples]]
<code sql>
-- 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;
</code>

===== STRAIGHT_JOIN =====
  * MySQL 이 join 순서 최적화를 잘 못할경우 **무조건 왼쪽 테이블 먼저 조회하게** 강제하는 옵션
  * 인덱스 힌트와 비슷한 역할을 하므로, 가급적 사용하지 않는게 좋다.
<code sql>
-- 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테이블의 컬럼>
</code>
  * [[https://marobiana.tistory.com/35|MySql] JOIN 속도를 빠르게! EXPLAIN, STRAIGHT_JOIN]]

===== mysql에서 쿼리 결과 세로로 보기 =====
**-E** 옵션을 줘서 실행한다.
<code sh>
mysql -E -u root -p mysql
</code>

===== mysql에서 발생한 오류나 경고 다시 보기 =====
<code sql>
mysql> show errors;
mysql> show warnings;
</code>

===== show processlist =====
  * [[http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html|show processlist]]
  * 접속한 커넥션의 상태를 보여준다.
<code sql>
show full processlist;
show full processlist\G;
</code>

===== MySQL 버전 알아보기 =====
<code>
SHOW VARIABLES LIKE "%version%";
</code>

===== CREATE TABLE 구문 보기 =====
<code>
SHOW CREATE TABLE [테이블이름]\G
</code>

===== 테이블/컬럼 정보보기 =====
  * [[http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html|SHOW TABLE STATUS]]

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

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

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

===== 테이블/컬럼의 주석(Comment) 지정 =====
<code>
-- 테이블의 주석 변경
ALTER TABLE [테이블이름] COMMENT = '테이블설명';

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

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

<code>
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
</code>

===== Group By 의 비 Grouping 컬럼 정보 concat =====
  * [[https://ra2kstar.tistory.com/56|초보개발자 이야기. :: MySQL : 필드(Field)를 구분자로 묶어 출력하기 (CONCAT)]]
<code sql>
SELECT keyword, GROUP_CONCAT( syn SEPARATOR  '-' )  -- syn 컬럼들이 '-' 로 묶인 문자열로 나온다.
FROM syn_common
GROUP BY keyword
</code>

===== GROPU BY, DISTINCT =====
  * ''GROUP BY''를 하면서, ''COUNT(DISTINCT colName)'' 을 하면, 동일 그룹에 속하는 ''colName'' 이 중복은 제거 상태로 카운팅 된다.
  * [[https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count-distinct|GROUP BY functions - COUNT(DISTINCT)]]

===== 전체 테이블의 행수 =====
  * 특정 데이터베이스 스키마에 있는 테이블들의 행수를 조회한다.
<code sql>
select TABLE_NAME, TABLE_ROWS
from information_schema.TABLES
where TABLE_SCHEMA='<schema_name>'
order by TABLE_ROWS desc;
</code>