내숭이

MySQL 기본 명령어 정리

데이터베이스 접속

• cmd 창에서

• mysql -u 사용자명 -p

데이터베이스 리스트 확인

• mysql> SHOW DATABASES;

데이터베이스 사용

• mysql> USE 데이터베이스명;

테이블 리스트 확인

• mysql> SHOW TABLES;

사용자 확인

• mysql> USE mysql;
  mysql> SELECT user, host FROM user;

사용자 권한 확인

• mysql> SHOW GRANTS FOR '아이디'@'%';

사용자 추가

• mysql> CREATE USER '아이디'@localhost IDENTIFIED BY '비밀번호';

사용자 삭제

• mysql> DELETE FROM USER WHERE USER='username';

MySQL 버전 확인

• mysql> SHOW VARIABLES LIKE "%version%";

쿼리 결과 세로로 보기

• cmd창에서 mysql을 접속할 때 -E 옵션을 이용하여 접속

• mysql -E -u root -p

현재 상태 확인하기

• mysql> STATUS;

테이블 정보 확인하기

• mysql> EXPLAIN 테이블명;

SQL(Structed Query Language)

• 관계형 데이터베이스 관리 시스템의 데이터를 관리하기 위해 설계된 특수 목적의 프로그램 언어
• 자료의 검색과 관리, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해 고안

SQL 문법의 종류

1. 데이터 정의어 (DDL : Data Definition Language)

1.1 스키마 / 데이터베이스 정의

• 정확한 의미의 스키마와 데이터베이스는 다르지만 MySQL에서는 같은것으로 봄

• 스키마 생성 / 데이터베이스 생성

  • CREATE SCHEMA 스키마명; 
    CREATE DATABASE 데이터베이스명; 

• 스키마 삭제 / 데이터베이스 삭제

  • DROP SCHEMA 스키마명; 
    DROP DATABASE 데이터베이스명; 

1.2 테이블 정의

• 테이블 생성

  • CREATE TABLE 테이블명( 
     컬럼명1 데이터타입 [DEFAULT 형식], 
        컬럼명2 데이터타입 [DEFAULT 형식] 
    ); 

  • 제약조건

    • PRIMARY KEY : 기본키
      • 기본키 설정
      • PRIMARY KYE = UNIQUE KEY & NOT NULL
    • UNIQUE KEY : 고유키
      • NULL 가능
      • 중복된 값 불가
    • NOT NULL
      • NULL 값을 금지
    • CHECK
      • 입력할 수 있는 값의 범위 등을 제한
      • TRUE 또는 FALSE로 평가할 수 있는 논리식을 지정
      • MySQL에서 적용을 할 수 있지만 데이터의 무결성을 강요하지 않음 => 적용안됨E
    • FOREIGN KEY : 외래키
      • 테이블 간의 관계를 정의하기 위해 다른 테이블의 기본키를 외래키로 지정
      • 외래키 지정시 참조 무결성 제약 옵션 선택 가능

  • CREATE TABLE 테이블명( 
     컬럼명1 데이터타입 [DEFAULT 형식], 
        컬럼명2 데이터타입 [DEFAULT 형식], 
        UNIQUE INDEX 컬럼명_UNIQUE(컬럼명 ASC), 
        CONSTRAINT 테이블명_PK PRIMARY KEY(컬럼명), 
        CONSTRAINT 테이블명_FK FOREIGN KEY(컬럼명) REFERENCES 상대테이블명(기본키), 
        CONSTRAINT CHK_테이블명 CHECK (논리식) 
    ); 

  • CREATE TABLE TEST( 
    NO INT NOT NULL, 
       NO2 INT, 
       NO3 INT, 
       UNIQUE INDEX NO2_UNIQUE(NO2 ASC), 
       CONSTRAINT TEST_PK PRIMARY KEY(NO), 
       CONSTRAINT TEST_FK FOREIGN KEY(NO3) REFERENCES STUDENT(NUM), 
       CONSTRAINT CHK_TEST CHECK(NO>=1) 
    ); 

• 테이블 수정

  • 컬럼 추가

  • ALTER TABLE 테이블명 
     ADD 컬럼명 데이터타입; 

  • 컬럼 삭제

  • ALTER TABLE 테이블명 
     DROP 컬럼명; 

  • 컬럼 수정

  • ALTER TABLE 테이블명 
     MODIFY 컬럼명 데이터타입; 

  • 컬럼명 수정

  • ALTER TABLE 테이블명 
     CHANGE 기존컬럼명 새컬럼명 데이터타입; 

  • 제약조건 추가

  • ALTER TABLE 테이블명 
     ADD CONSTRAINT 제약조건명 제약조건(컬럼명); 

  • 제약조건 삭제

  • ALTER TABLE 테이블명 
     DROP 제약조건명; 

• 테이블 초기화

  • TRUNCATE TABLE 테이블명; 

  • 테이블의 모든 행을 지움

  • 테이블은 유지

• 테이블 삭제

  • DROP TABLE 테이블명; 

  • DROPT은 테이블 자체를 삭제하고 TRUNCATE는 테이블은 유지하고 데이터들만 삭제

2. 데이터 조작어(DML : Data Manipulation Language)

3. 데이터 제어어(DCL : Data Control Language)

3.1 GRANT

• 사용자에게 권한을 부여하는 명령어

• 권한 허용

  • GRANT 명령어 ON 데이터베이스명.테이블 TO '아이디'@'localhost'; 

  • '아이디' 사용자에게 데이터베이스명.테이블에서 명령어를 사용할 수 있는 권한을 부여

  • GRANT ALL ON TEST.* TO 'test'@'localhost'; 
    

    • test 사용자에게 TEST 데이터베이스에 있는 모든 테이블의 모든 명령어를 사용할 수 있는 권한을 부여
    • 모든 권한 : SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,REFERENCES, INDEX, ALTER, CREATE TMP TABLE, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT TRIGGER

  • GRANT INSERT ON TEST.* TO 'test'@'localhost'; 

    • test 사용자에게 TEST 데이터베이스에 있는 INSERT 명령어를 사용할 수 있는 권한을 부여

3.2 REVOKE

• 사용자에게 권한을 회수하는 명령어

• 권한 회수

  • REVOKE 명령어 ON 데이터베이스명.테이블 FROM '아이디'@'localhost' 

  • '아이디' 사용자에게 데이터베이스명.테이블에서 명령어를 사용할 수 있는 권한을 회수

  • REVOKE ALL ON TEST.* FROM 'test'@'localhost'; 
    REVOKE INSERT ON TEST.* FROM 'test'@'localhost'; 

3.3 COMMIT

• 작업한 결과를 물리적 디스크로 저장하고, 작업이 정상적으로 완료됨을 관리자에게 알려주는 명령어

• COMMIT; 

3.4 ROLLBACK

• 작업했던 내용을 원래의 상태로 복구하는 명령어

• INSERT, UPDATE, DELETE와 같은 트랜잭션의 작업을 취소

• COMMIT 명령어를 사용하기 이전의 상태만 ROLLBACK 가능

• ROLLBACK; 

DELETE, TRUNCATE, DROP의 차이