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-mode=disabled 옵션 주면 일반 접속으로 됨.
혹은 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);
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 조건
-- 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;
-- 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테이블의 컬럼>
-E 옵션을 줘서 실행한다.
mysql -E -u root -p mysql
mysql> SHOW errors; mysql> SHOW warnings;
SHOW FULL processlist; SHOW FULL processlist\G;
SHOW VARIABLES LIKE "%version%";
SHOW CREATE TABLE [테이블이름]\G
-- 전체 테이블 정보를 주석까지 포함해 보여준다. 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
SELECT keyword, GROUP_CONCAT( syn SEPARATOR '-' ) -- syn 컬럼들이 '-' 로 묶인 문자열로 나온다. FROM syn_common GROUP BY keyword
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;