MYSQL DDL, DML 정리

노요셉·2019년 11월 15일
MySQL DDLMySQL DMLmysqlworkbench erd
0

데이터베이스 목록 보기

show databases;

데이터베이스 생성하기

create database hello_world_db; --hello_world_db라는 이름의 데이터베이스를 생성.

USE hello_world_db;  --hello_world_db데이터베이스를 사용하겠다 라고 MySQL에 알림.

mysql workbench을 쓴다면
좌측 상단에 schemas에 있는 데이터베이스에서 오른쪽 클릭하고 set as Default schemas로 해두면 connection할때마다 use hello_world_db할 필요없음.

데이터베이스 삭제

drop database hello_world_db;  --hello_world_db라는 이름의 데이터베이스 삭제

데이터베이스 사용하기, 현재 사용하는 데이터베이스 보기

use hello_world_db

select database();

테이블 생성하기

mysql> CREATE TABLE hello_world_db (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> age INT UNSIGNED NOT NULL,
    -> married TINYINT NOT NULL,
    -> comment TEXT NULL,
    -> create_at DATETIME NOT NULL DEFAULT now(),
    -> PRIMARY KEY(id),
    -> UNIQUE INDEX name_UNIQUE (name ASC))
    -> COMMENT = '사용자 정보'
    -> DEFAULT CHARSET=utf8
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

레퍼런스:
MYSQL DDL 명령어 사용하는 방법 -
https://kkamikoon.tistory.com/169
mysql utf-8한글 인코딩해결하기 - https://bestcoding.tistory.com/11
charset 과 collation - https://sshkim.tistory.com/128

테이블명 수정

alter table <ASIS테이블명> rename <TOBE테이블명>

https://mcpaint.tistory.com/194

테이블 삭제

drop table <테이블명>;

현재 데이터베이스의 테이블 보기

show tables;

데이터베이스의 테이블 정보

어떤 엔진쓰는지, rows개수 등등

show table status;

특정 테이블의 컬럼 정보 보기

cats 테이블에 무슨 열(column)있는지 확인

desc cats;

테이블 컬럼 추가

-- 컬럼 추가
ALTER TABLE `테이블이름` ADD `컬럼이름` 데이터타입 NOT NULL DEFAULT 0 COMMENT '코멘트내용' AFTER 컬럼명;

https://wakestand.tistory.com/484

mysql user 초기화

ALTER USER 'service'@'%' IDENTIFIED  BY 'MYSQL_ROOT_PASSWORD';

https://serverok.in/you-must-reset-your-password-using-alter-user

mysql 외부접근 허가

GRANT ALL PRIVILEGES ON *.* TO 'service'@'%' WITH GRANT OPTION;

변경내용 메모리에 반영하기

FLUSH PRIVILEGES;

컬럼 업데이트

ALTER TABLE `테이블명` MODIFY 컬럼명` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '코멘트' AFTER 컬럼명;

https://wakestand.tistory.com/484

컬럼 데이터 수정

SET SQL_SAFE_UPDATES = 0;
UPDATE `테이블이름` 
SET 컬럼이름 = 1
WHERE 컬럼이름 = 1;
SET SQL_SAFE_UPDATES = 1;

테이블 컬럼 제거

-- 기존 컬럼 삭제
ALTER TABLE `테이블이름` DROP `컬럼이름`;

테이블 컬럼 코멘트 조회

-- mysql 테이블 컬럼 코멘트 조회 방법 
SELECT A.TABLE_NAME, A.TABLE_COMMENT, B.COLUMN_NAME, B.COLUMN_COMMENT
     , B.COLUMN_TYPE, B.COLUMN_DEFAULT, B.ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.TABLES A INNER JOIN INFORMATION_SCHEMA.COLUMNS B
    ON A.TABLE_SCHEMA = B.TABLE_SCHEMA
   AND A.TABLE_NAME = B.TABLE_NAME
 WHERE A.TABLE_SCHEMA = '스키마명' # DB베이스이름 쓰세요.
   AND A.TABLE_NAME = '테이블명';

https://wakestand.tistory.com/483

테이블에 데이터 넣기

insert into cats(name, age) values ('momo', 7);


insert into cats( name, age) 
	values ('ya', 5),
    values ('mo', 5),
    values ('hae', 5);

데이터에 '따옴표 넣고싶으면 escape 문자 이용 \'

특정 테이블 데이터 조회

  • (asterisk) all을 의미

cats 테이블의 모든 컬럼을 조회하겠음.

select * from cats;

테이블 조회시 특정 조건에 해당하는 데이터만 보고싶을땐

where절 씁니다.

select * 
from city
where Population < 8000000
and population > 7000000;

where절은 다양한 관계연산자를 같이 사용합니다.

데이터가 숫자로 구성되어있어 연속적인 값은 BETWEEN ... AND 사용가능

select * 
from city 
where population between 7000000 and 8000000;

데이터가 숫자가 아니고 문자일때 여러 조건에 모두 해당하는 데이터를 조회하고싶을때 IN을 사용함.

한 글자 매치하기 위해서 '_' 사용

ko땡으로 시작하는 국가코드를 가진 도시 조회하기

select *
from city
where countrycode like 'ko_';

문자 뒤에 %를 쓰면 여러 글자를 매치할 수 있음.

tel 로 시작하는 도시 이름 조회하기.

select *
from city
where name like 'tel %';

문자열을 검색할때는 LIKE

select *
from city
where countrycode like 'ko_';

컬럼 초기값 설정하기

테이블 생성할때 넣으면 됩니다.

CREATE TABLE cats3
  (
    name VARCHAR(20) DEFAULT 'no name provided',
    age INT DEFAULT 99
  );

초기값에 NOT NULL도 추가

CREATE TABLE cats4
  (
    name VARCHAR(20) NOT NULL DEFAULT 'unnamed',
    age INT NOT NULL DEFAULT 99
  );

따라하기

INSERT INTO cats() VALUES();
 
SELECT * FROM cats;
 
INSERT INTO cats3() VALUES();
 
SELECT * FROM cats3;
 
INSERT INTO cats3(name, age) VALUES('Montana', NULL);
 
SELECT * FROM cats3;
 
INSERT INTO cats4(name, age) VALUES('Cali', NULL);

기본키 지정

PRIMARY KEY(컬럼명)

CREATE TABLE unique_cats
  (
    cat_id INT NOT NULL,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
  );

따라하기

mysql> INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'Fred', 23);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT INTO unique_cats(cat_id, name, age) VALUES(2, 'Louise', 3);
Query OK, 1 row affected (0.06 sec)

mysql>
mysql> INSERT INTO unique_cats(cat_id, name, age) VALUES(1, 'James', 3);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

기본키는 유일해야 구분하기 때문에 보통 AUTO_INCREMENT 속성을 사용함.

CREATE TABLE unique_cats2 (
    cat_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);
INSERT INTO unique_cats2(name, age) 
 VALUES('Skippy', 4),
 VALUES('Jiff', 3),
 VALUES('Jiff', 3),
 VALUES('Jiff', 3),
 VALUES('Skippy', 4);

SELECT * FROM unique_cats2;

외래키 지정

CONSTRAINT 제약조건명 FOREIGN KEY (컬럼명) REFERENCES 참고하는테이블 (컬럼명)

ON UPDATE와 ON DELETE는 모두 CASCADE로 설정 : 사용자 정보가 수정되거나 삭제되면 그것과
연결된 댓글 정보도 같이 수정하거나 삭제한다는 뜻입니다. 그래야 데이터가 불일치하는 현상이 일어나지 않습니다.

ALTER TABLE `DB명`.`A테이블명` 
ADD CONSTRAINT `제약조건이름(문자열)`
  FOREIGN KEY (`A컬럼명`)
  REFERENCES `DB명`.`B테이블명` (`B컬럼명`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

http://www.tcpschool.com/mysql/mysql_constraint_foreignKey

만들어진 테이블 확인하는 명령어

mysql> DESC users;
+-----------+------------------+------+-----+-------------------+----------------+
| Field     | Type             | Null | Key | Default           | Extra          |
+-----------+------------------+------+-----+-------------------+----------------+
| id        | int(11)          | NO   | PRI | NULL              | auto_increment |
| name      | varchar(20)      | NO   | UNI | NULL              |                |
| age       | int(10) unsigned | NO   |     | NULL              |                |
| married   | tinyint(4)       | NO   |     | NULL              |                |
| comment   | text             | YES  |     | NULL              |                |
| create_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+------------------+------+-----+-------------------+----------------+
6 rows in set (0.01 sec)

테이블 제거

mysql> DROP TABLE users;

table의 row 데이터를 수정하기

UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';

table row데이터 삭제

DELETE FROM cats WHERE age=4;

테이블 확인

mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| comments         |
| users            |
+------------------+
2 rows in set (0.00 sec)

인덱스 추가

ALTER TABLE `DB명`.`테이블명` 
ADD INDEX `제약조건이름(문자열)` (`컬럼명` ASC) VISIBLE;

컬럼명 변경

ALTER TABLE todos CHANGE completed done TINYINT(1);

컬럼 추가

alter table [테이블명] add [컬럼명] [타입] [옵션];

https://zetawiki.com/wiki/MySQL_%ED%85%8C%EC%9D%B4%EB%B8%94_%EC%BB%AC%EB%9F%BC_%EC%B6%94%EA%B0%80

컬럼 타입 변경

mysql> ALTER TABLE todos MODIFY title varchar(400);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

컬럼 순서 변경

mysql> ALTER TABLE todos MODIFY completed TINYINT(1) AFTER UserId;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

이 외에도 컬럼 순서 바꾸기, 디폴트 값 변경, 컬럼 추가하기 등등

레퍼런스

테이블 컬럼 코멘트 조회

https://extbrain.tistory.com/97

mysql workbench 테이블 마이그레이션

A DB의 table A를 복사해서 B DB에 table A로 붙여넣기

  • MySQLWorkBench 상단메뉴에서 Database > Migration
  • Start Migration
  • Source Selection: 복사할 Database의 접속정보를 선택하고 next
  • Target Selection: 붙여넣을 Database의 접속정보를 선택하고 next
  • Source Objects에서 필요한 테이블만 Object to migrate으로 옮기고 next
    나머지 계속 next하면 B DB에 table A가 생성되고, 데이터도 클론됌

json을 컬럼에 넣기

https://mysqldba.tistory.com/278

서브쿼리

https://victorydntmd.tistory.com/139

join과 subquery 성능차이: https://stackoverflow.com/questions/2577174/join-vs-sub-query

mysql workbench

Generate DB schema in a 10 seconds with MySQL Workbench
https://www.youtube.com/watch?v=RbKEYDtkAJI

mysql workbench를 켭니다.
사용하려는 dbms에 접근합니다.
erd를 보려는 db와 그안에 tables들이 있을 것임.
상단메뉴에서 Database > Reverse Engineer...
단계가 다음과 같은데

select schemas에서 erd로 보려는 db 스키마를 선택해줍니다.
그리고 계속 continue

schema에서 데이터베이스에 set as default Schema 해두면 use db이름 할 필요없음.

mysql_secure_installation

컨테이너에서나 가상머신에서 mysql-server를 설치했으면
root 비밀번호부터 설정할게 많은데 한번에 할 수 있음.
https://medium.com/@alienhue/installing-mysql-in-windows-subsystem-for-linux-ubuntu-18-04-5227e27ddf2a

sudo mysql_secure_installation

mysql_secure_installation 설명
:http://blog.servis.co.kr/index.php/2020/05/14/mysql_secure_installation/

profile
노요셉
서로 아는 것들을 공유해요~
이전 포스트

MYSQL 설치 및 사용자 권한 설정, 외부접근 할당

다음 포스트

MYSQL DDL, DML 정리

0개의 댓글