사용자 도구

사이트 도구


database:mysql:basic

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

원격 서버 접속

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

SSL mode 오류

혹은 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'@'%';

사용자 추가 / 권한

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

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 조건

DELETE with JOIN

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

STRAIGHT_JOIN

  • 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에서 쿼리 결과 세로로 보기

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

mysql -E -u root -p mysql

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

mysql> SHOW errors;
mysql> SHOW warnings;

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 like 'member%'; -- 테이블 이름 매칭 조건

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

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

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

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

테이블과 컬럼의 전체 정보

information_schemaTABLES, 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

Group By 의 비 Grouping 컬럼 정보 concat

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

GROPU BY, DISTINCT

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

전체 테이블의 행수

  • 특정 데이터베이스 스키마에 있는 테이블들의 행수를 조회한다.
SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='<schema_name>'
ORDER BY TABLE_ROWS DESC;
database/mysql/basic.txt · 마지막으로 수정됨: 2023/10/31 09:49 저자 kwon37xi