2019. 3. 4. 18:22ㆍDataBase
DML(Data Manipulation Language)
DML은 DBMS에서 정보를 저장/수정/삭제/조회 하는데 많이 사용하게 될 것입니다. 한 번 정리해보도록 하겠습니다!
DML의 종류
- SELECT : 검색
- INSERT : 등록
- UPDATE : 수정
- DELETE : 삭제
SELECT
ex)SELECT 컬럼이름 FROM 테이블이름;SELECT DISTINCT 컬럼이름 FROM 테이블이름; --> DISTINCT를 사용하면 column의 중복되는 행을 제거합니다.SELECT 컬럼이름 as 별칭 FROM 테이블이름; --> 컬럼이름을 as를 사용해 별칭(Alias)을 부여해 검색할 수 있습니다!SELECT concat(컬럼, ' - ' ,다른 컬럼) as '별칭' FROM 테이블이름;--> 컬럼의 합성 (Concatenation). 문자열을 결합하는 concat()을 사용해 두 개의 컬럼을 하나의 칼럼으로 출력할 수 있습니다.이 때 컬럼과 컬럼사이에 어떤 문자열을 넣으면 그 사이에 문자열을 넣어 출력합니다!
mysql> select * from products;
+------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | | 1003 | PCL | Pencil 2H | 10000 | 0.49 | | 1004 | PCL | Pencil 2B | 9000 | 0.48 | +------+----------+-----------+----------+-------+ 4 rows in set (0.39 sec)
mysql> select name from products; +-----------+ | name | +-----------+ | Pen Red | | Pen Blue | | Pencil 2H | | Pencil 2B | +-----------+ 4 rows in set (0.00 sec) mysql> select name,price from products; +-----------+-------+ | name | price | +-----------+-------+ | Pen Red | 1.23 | | Pen Blue | 1.25 | | Pencil 2H | 0.49 | | Pencil 2B | 0.48 | +-----------+-------+ 4 rows in set (0.00 sec)
mysql> select name as 이름, price as 가격 from products; +-----------+--------+ | 이름 | 가격 | +-----------+--------+ | Pen Red | 1.23 | | Pen Blue | 1.25 | | Pencil 2H | 0.49 | | Pencil 2B | 0.48 | +-----------+--------+ 4 rows in set (0.00 sec)
mysql> select concat(name,' - ',price) as '이름-가격' from products; +------------------+ | 이름-가격 | +------------------+ | Pen Red - 1.23 | | Pen Blue - 1.25 | | Pencil 2H - 0.49 | | Pencil 2B - 0.48 | +------------------+ 4 rows in set (0.00 sec)
WHERE
SELECT 칼럼명 FROM 테이블명 WHERE 조건식;
mysql> select * from products where price < 1.0 AND quantity > 8000 AND name LIKE '%B'; +------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1004 | PCL | Pencil 2B | 9000 | 0.48 | +------+----------+-----------+----------+-------+ 1 row in set (0.00 sec)
SELECT구문에서 함수 사용
SELECT구문의 그룹함수
mysql> select count(quantity) from products; +-----------------+ | count(quantity) | +-----------------+ | 4 | +-----------------+ 1 row in set (0.00 sec)
mysql> select avg(price), sum(quantity) from products; +--------------------+---------------+ | AVG(price) | SUM(quantity) | +--------------------+---------------+ | 0.8625000044703484 | 32000 | +--------------------+---------------+ 1 row in set (0.00 sec)
mysql> select min(price), max(price) from products; +---------------------+------------+ | min(price) | max(price) | +---------------------+------------+ | 0.47999998927116394 | 1.25 | +---------------------+------------+ 1 row in set (0.00 sec)
mysql> select group_concat(quantity) from products; +------------------------+ | group_concat(quantity) | +------------------------+ | 5000,8000,10000,9000 | +------------------------+ 1 row in set (0.00 sec)
INSERT (데이터 입력)
INSERT INTO 테이블 (co1, col2, col3, ....) VALUES ( 24, 'str1', 'str2', ...);
mysql> select * from products; +------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | | 1003 | PCL | Pencil 2H | 10000 | 0.49 | | 1004 | PCL | Pencil 2B | 9000 | 0.48 | +------+----------+-----------+----------+-------+ 4 rows in set (0.00 sec)
mysql> INSERT INTO products (id,category,name,quantity,price) values (1005,'FOU','monc', 1500, 8.00); Query OK, 1 row affected (0.01 sec)
mysql> select * from products; +------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | | 1003 | PCL | Pencil 2H | 10000 | 0.49 | | 1004 | PCL | Pencil 2B | 9000 | 0.48 | | 1005 | FOU | monc | 1500 | 8 | +------+----------+-----------+----------+-------+ 5 rows in set (0.00 sec)
UPDATE (데이터 수정)
UPDATE 테이블명 SET 필드1 = 수정할 값, 필드2 = 수정할 값, ... WHERE 조건식;
mysql> select * from products; +------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | | 1003 | PCL | Pencil 2H | 10000 | 0.49 | | 1004 | PCL | Pencil 2B | 9000 | 0.48 | | 1005 | FOU | monc | 1500 | 8 | +------+----------+-----------+----------+-------+ 5 rows in set (0.00 sec) mysql> update products -> set name = 'fountain pen' where id=1005; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from products; +------+----------+--------------+----------+-------+ | id | category | name | quantity | price | +------+----------+--------------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 8000 | 1.25 | | 1003 | PCL | Pencil 2H | 10000 | 0.49 | | 1004 | PCL | Pencil 2B | 9000 | 0.48 | | 1005 | FOU | fountain pen | 1500 | 8 | +------+----------+--------------+----------+-------+ 5 rows in set (0.00 sec)
mysql> update products set quantity = 2500; Query OK, 5 rows affected (0.35 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from products; +------+----------+--------------+----------+-------+ | id | category | name | quantity | price | +------+----------+--------------+----------+-------+ | 1001 | PEN | Pen Red | 2500 | 1.23 | | 1002 | PEN | Pen Blue | 2500 | 1.25 | | 1003 | PCL | Pencil 2H | 2500 | 0.49 | | 1004 | PCL | Pencil 2B | 2500 | 0.48 | | 1005 | FOU | fountain pen | 2500 | 8 | +------+----------+--------------+----------+-------+ 5 rows in set (0.00 sec)
DELETE (데이터 삭제)
DELETE FROM 테이블명 WHERE 조건식;
mysql> select * from products; +------+----------+--------------+----------+-------+ | id | category | name | quantity | price | +------+----------+--------------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 5500 | 1.25 | | 1003 | PCL | Pencil 2H | 3000 | 0.49 | | 1004 | PCL | Pencil 2B | 3300 | 0.48 | | 1005 | FOU | fountain pen | 1500 | 8 | +------+----------+--------------+----------+-------+ 5 rows in set (0.00 sec) mysql> delete from products where id=1005; Query OK, 1 row affected (0.00 sec) mysql> select * from products; +------+----------+-----------+----------+-------+ | id | category | name | quantity | price | +------+----------+-----------+----------+-------+ | 1001 | PEN | Pen Red | 5000 | 1.23 | | 1002 | PEN | Pen Blue | 5500 | 1.25 | | 1003 | PCL | Pencil 2H | 3000 | 0.49 | | 1004 | PCL | Pencil 2B | 3300 | 0.48 | +------+----------+-----------+----------+-------+ 4 rows in set (0.00 sec)
[ edwith - 웹프로그래밍 부스트코스 ] 를 개인적으로 공부하고 정리한 공간입니다. 잘못된 부분은 피드백 주시면 감사하겠습니다
'DataBase' 카테고리의 다른 글
[MySQL]DDL (0) | 2019.03.05 |
---|---|
[MySQL] SQL (0) | 2019.03.04 |
[MySQL] DB vs DBMS (0) | 2019.03.02 |