MySQL 명령어 및 쿼리 (익숙하지 않은 문법 기록용)

 

USER 생성 명령어

CREATE USER 유저이름@localhost or ip 주소 IDENTIFIED BY ‘비밀번호’;

 

사용자 권한 부여 및 제거

// 권한 부여
GRANT ALL PRIVILEGES ON 데이터베이스명.테이블명TO 유저이름@localhost or ip;

FLUSH PRIVILEGES;

/*
	`ALL PRIVILEGES는 모든 권한 부여 (SELECT, INSERT, DELETE 등 부여 가능)
	*(데이터베이스명).*(테이블명) 로 설정시 모든 데이터베이스의 테이블을 의미
	마지막 FLUSH 해줘야 적용됨
*/

// 권한 제거
REVOKE ALL ON 데이터베이스명.테이블명FROM 유저이름@localhost or ip;

 

제약조건정의(CONSTRAINT)

//테이블 생성시 맨 아래에서 제약 조건을 적용할 경우 ALTER TABLE 테이블명를 생략

//제약조건
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건[UNIQUE, CHECK, NOT NULL ...] (적용할컬럼);
//제약조건 예시)
ALTER TABLE members ADD CONSTRAINT id_unique UNIQUE (id);

//DEFAULT
ALTER TABLE 테이블명ALTER 속성SET DEFAULT 기본값;
//DEFAULT 예시)
ALTER TABLE members ALTER name SET DEFAULT '입력 없음';

제약조건 삭제

//제약조건 삭제
ALTER TABLE 테이블명DROP CONSTRAINT 제약조건이름;

//DEFAULT 삭제
ALTER TABLE 테이블명ALTER 속성DROP DEFAULT

컬럼 추가 및 삭제

//컬럼 추가
ALTER TABLE 테이블명 ADD COLUMN 컬럼명 타입 제약조건; 
//컬럼 추가 예시)
ALTER TABLE members ADD COLUMN address VARCHAR(25) NOT NULL;


//컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
//컬럼 삭제 예시)
ALTER TABLE members DROP COLUMN address;

컬럼 이름변경

//컬럼명 변경
ALTER TABLE 테이블명 CHANGE COLUMN 기존컬럼명 바꿀컬럼명 타입 제약조건;

//예시)
ALTER TABLE members CHANGE COLUMN name member_name VARCHAR(10) NOT NULL;

컬럼 속성 및 제약조건 변경

//타입 및 제약조건 변경
ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 타입 제약조건;

//예시)
ALTER TABLE members MODIFY COLUMN id VARCHAR(10) UNIQUE NOT NULL;

테이블 이름 변경

//테이블명 변경
ALTER TABLE 기존테이블명 RENAME 변경할테이블명;

//예시)
ALTER TABLE members RENAME users;

KEY 설정

/*기본키 PRIMARY KEY*/
CONSTRAINT PK명 PRIMARY KEY (기본키 속성1,기본키 속성2 ...)
/*기본키 예시*/
CONSTRAINT name_created_pk PRIMARY KEY (name, crated)

/*외례키 FOREIGN KEY*/
FOREIGN KEY (참조할속성) REFERENCES 참조되는테이블(참조되는속성)
/*FOREIGN KEY 예시*/
FOREIGN KEY (item_id) REFERENCES items(id)

//테이블 생성 예시)
CREATE TABLE members(
    id 		INT		PRIMARY KEY,
    name 	VARCHAR(10),
    created 	DATE,
    item_id 	INT,
    
    /*
    위에 id 에 PRIMARY KEY를 주지 않으면 아래와 같이 뒤에 설정할 수있으며 
    여러 속성으로 PK값을 만들때는 아래와 같이 만들어야함
    */
    CONSTRAINT name_created_pk PRIMARY KEY (name, crated),
    FOREIGN KEY (item_id) REFERENCES items(id)
);

인덱스 추가 및 삭제

// 추가
CREATE INDEX 인덱스명ON 테이블명(컬럼1, 컬럼2, ...);

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (컬럼1, 컬럼2, ...);

// 삭제
ALTER TABLE 테이블명 DROP INDEX 인덱스명;

 

서브쿼리 사용 예

SELECT *, weight / (height / 100 * height / 100) AS bmi
FROM student
WHERE weight / (height / 100 * height / 100) < 18.5 OR weight / (height / 100 * height / 100) > 25.0;

/*
연산식을 계속 넣는것이 아니라 
테이블에서 모든 속성과 (weight / (height / 100 * height / 100)) 
값을 derived_student 아래로 SELECT 해오고 그 값을 활용
*/
SELECT derived_student.*
FROM (
    SELECT 
        * , 
        (weight / (height / 100 * height / 100)) AS bmi
    FROM student
 ) AS derived_student
WHERE derived_student.bmi < 18.5 
    OR derived_student.bmi > 25.0;