๐Ÿ“– Coding Study/์›น ํ”„๋กœ๊ทธ๋ž˜๋ฐ

2023.05.02 ํ™” (DB ๊ณ„์†)

๋นต๋ชจ๋ฃจ 2023. 5. 3. 20:03 

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| review             |
| review2            |
| test               |
| testdb             |
+--------------------+
8 rows in set (0.071 sec)

MariaDB [(none)]> drop database testdb;
Query OK, 1 row affected (0.143 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| review             |
| review2            |
| test               |
+--------------------+
7 rows in set (0.001 sec)

MariaDB [(none)]> create database bookshop;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> use bookshop;
Database changed

MariaDB [bookshop]> create table book
    -> (code varchar(10) not null,
    -> titls varchar(50) not null,
    -> author varchar(20) not null,
    -> pdate date not null,
    -> price int(6) not null,
    -> rating decimal(3,1),
    -> primary key(code));
Query OK, 0 rows affected (0.028 sec)

MariaDB [bookshop]> show tables;
+--------------------+
| Tables_in_bookshop |
+--------------------+
| book               |
+--------------------+
1 row in set (0.001 sec)

MariaDB [bookshop]> select * from book;
Empty set (0.010 sec)

MariaDB [bookshop]> desc book;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| code   | varchar(10)  | NO   | PRI | NULL    |       |
| titls  | varchar(50)  | NO   |     | NULL    |       |
| author | varchar(20)  | NO   |     | NULL    |       |
| pdate  | date         | NO   |     | NULL    |       |
| price  | int(6)       | NO   |     | NULL    |       |
| rating | decimal(3,1) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.027 sec)

MariaDB [bookshop]> desc book;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| code   | varchar(10)  | NO   | PRI | NULL    |       |
| titls  | varchar(50)  | NO   |     | NULL    |       |
| author | varchar(20)  | NO   |     | NULL    |       |
| pdate  | date         | NO   |     | NULL    |       |
| price  | int(6)       | NO   |     | NULL    |       |
| rating | decimal(3,1) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
6 rows in set (0.026 sec)

MariaDB [bookshop]> insert into book values('B001', 'Power Java', 'Peter', '2023/01/12', 21000, null);
Query OK, 1 row affected (0.011 sec)

MariaDB [bookshop]> insert into book values('B002', 'Cool Java', 'Berry', '2023/02/21', 28000, 8.5);
Query OK, 1 row affected (0.011 sec)

MariaDB [bookshop]> insert into book values('B003', 'Easy CPP', 'Berry', '2022/11/12', 16000, 7.9);
Query OK, 1 row affected (0.010 sec)

MariaDB [bookshop]> insert into book values('B004', 'Python Programming', 'James', '2022/09/16', 19000, null);
Query OK, 1 row affected (0.009 sec)

MariaDB [bookshop]> insert into book values('B005', 'HTM and CSS', 'Sam', '2022/05/16', 21000, 8.9);
Query OK, 1 row affected (0.003 sec)

MariaDB [bookshop]> select * from book;
+------+--------------------+--------+------------+-------+--------+
| code | titls              | author | pdate      | price | rating |
+------+--------------------+--------+------------+-------+--------+
| B001 | Power Java         | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java          | Berry  | 2023-02-21 | 28000 |    8.5 |
| B003 | Easy CPP           | Berry  | 2022-11-12 | 16000 |    7.9 |
| B004 | Python Programming | James  | 2022-09-16 | 19000 |   NULL |
| B005 | HTM and CSS        | Sam    | 2022-05-16 | 21000 |    8.9 |
+------+--------------------+--------+------------+-------+--------+
5 rows in set (0.001 sec)

MariaDB [bookshop]> select titls from book;
+--------------------+
| titls              |
+--------------------+
| Power Java         |
| Cool Java          |
| Easy CPP           |
| Python Programming |
| HTM and CSS        |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [bookshop]> select titls, pdate, price from book;
+--------------------+------------+-------+
| titls              | pdate      | price |
+--------------------+------------+-------+
| Power Java         | 2023-01-12 | 21000 |
| Cool Java          | 2023-02-21 | 28000 |
| Easy CPP           | 2022-11-12 | 16000 |
| Python Programming | 2022-09-16 | 19000 |
| HTM and CSS        | 2022-05-16 | 21000 |
+--------------------+------------+-------+
5 rows in set (0.001 sec)

MariaDB [bookshop]> select titls, pdate, price * 1.2 from book;
+--------------------+------------+-------------+
| titls              | pdate      | price * 1.2 |
+--------------------+------------+-------------+
| Power Java         | 2023-01-12 |     25200.0 |
| Cool Java          | 2023-02-21 |     33600.0 |
| Easy CPP           | 2022-11-12 |     19200.0 |
| Python Programming | 2022-09-16 |     22800.0 |
| HTM and CSS        | 2022-05-16 |     25200.0 |
+--------------------+------------+-------------+
5 rows in set (0.008 sec)

MariaDB [bookshop]> select * from book where author = 'Berry';
+------+-----------+--------+------------+-------+--------+
| code | titls     | author | pdate      | price | rating |
+------+-----------+--------+------------+-------+--------+
| B002 | Cool Java | Berry  | 2023-02-21 | 28000 |    8.5 |
| B003 | Easy CPP  | Berry  | 2022-11-12 | 16000 |    7.9 |
+------+-----------+--------+------------+-------+--------+
2 rows in set (0.009 sec)

MariaDB [bookshop]> select titls, price from book where author = 'Berry';
+-----------+-------+
| titls     | price |
+-----------+-------+
| Cool Java | 28000 |
| Easy CPP  | 16000 |
+-----------+-------+
2 rows in set (0.001 sec)

MariaDB [bookshop]> select * from book where pdate >= '2023/01/01';
+------+------------+--------+------------+-------+--------+
| code | titls      | author | pdate      | price | rating |
+------+------------+--------+------------+-------+--------+
| B001 | Power Java | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java  | Berry  | 2023-02-21 | 28000 |    8.5 |
+------+------------+--------+------------+-------+--------+
2 rows in set (0.008 sec)

MariaDB [bookshop]> select * from book where pdate >= '2023/01/01' and pdate <= '2023/12/31';
+------+------------+--------+------------+-------+--------+
| code | titls      | author | pdate      | price | rating |
+------+------------+--------+------------+-------+--------+
| B001 | Power Java | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java  | Berry  | 2023-02-21 | 28000 |    8.5 |
+------+------------+--------+------------+-------+--------+
2 rows in set (0.001 sec)

MariaDB [bookshop]> select * from book where pdate between '2023/01/01' and '2023/12/31';
+------+------------+--------+------------+-------+--------+
| code | titls      | author | pdate      | price | rating |
+------+------------+--------+------------+-------+--------+
| B001 | Power Java | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java  | Berry  | 2023-02-21 | 28000 |    8.5 |
+------+------------+--------+------------+-------+--------+
2 rows in set (0.001 sec)
MariaDB [bookshop]> select titls price from book where price between 10000 and 19999;
+--------------------+
| price              |
+--------------------+
| Easy CPP           |
| Python Programming |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [bookshop]> select titls, price from book where price between 10000 and 19999;
+--------------------+-------+
| titls              | price |
+--------------------+-------+
| Easy CPP           | 16000 |
| Python Programming | 19000 |
+--------------------+-------+
2 rows in set (0.001 sec)

MariaDB [bookshop]> select * from book where titls = 'Java';
Empty set (0.001 sec)

MariaDB [bookshop]> select * from book where titls like 'Java';
Empty set (0.008 sec)

MariaDB [bookshop]> select * from book where titls like '%Java%';    // ํผ์„ผํŠธ์ž๋ฆฌ์— ๊ธ€์ž๊ฐ€ ์žˆ๊ฑฐ๋‚˜ ์—†๊ฑฐ๋‚˜ ์ƒ๊ณผ์—†๋‹ค
+------+------------+--------+------------+-------+--------+
| code | titls      | author | pdate      | price | rating |
+------+------------+--------+------------+-------+--------+
| B001 | Power Java | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java  | Berry  | 2023-02-21 | 28000 |    8.5 |
+------+------------+--------+------------+-------+--------+
2 rows in set (0.001 sec)

MariaDB [bookshop]> select * from book where titls like 'Java%';
Empty set (0.001 sec)

MariaDB [bookshop]> select * from book where titls like '%Java';
+------+------------+--------+------------+-------+--------+
| code | titls      | author | pdate      | price | rating |
+------+------------+--------+------------+-------+--------+
| B001 | Power Java | Peter  | 2023-01-12 | 21000 |   NULL |
| B002 | Cool Java  | Berry  | 2023-02-21 | 28000 |    8.5 |
+------+------------+--------+------------+-------+--------+


MariaDB [bookshop]> select * from book where rating = null; 	// ๋„์€ ๊ฐ’์ด ์•„๋‹ˆ๋ผ ์•ˆ๋‚˜์˜ด
Empty set (0.001 sec)

MariaDB [bookshop]> select * from book where rating is null;
+------+--------------------+--------+------------+-------+--------+
| code | titls              | author | pdate      | price | rating |
+------+--------------------+--------+------------+-------+--------+
| B001 | Power Java         | Peter  | 2023-01-12 | 21000 |   NULL |
| B004 | Python Programming | James  | 2022-09-16 | 19000 |   NULL |
+------+--------------------+--------+------------+-------+--------+


MariaDB [bookshop]> select * from book where rating is not null;
+------+-------------+--------+------------+-------+--------+
| code | titls       | author | pdate      | price | rating |
+------+-------------+--------+------------+-------+--------+
| B002 | Cool Java   | Berry  | 2023-02-21 | 28000 |    8.5 |
| B003 | Easy CPP    | Berry  | 2022-11-12 | 16000 |    7.9 |
| B005 | HTM and CSS | Sam    | 2022-05-16 | 21000 |    8.9 |
+------+-------------+--------+------------+-------+--------+
MariaDB [bookshop]> select * from book where rating >= 7 and price < 20000;
+------+----------+--------+------------+-------+--------+
| code | titls    | author | pdate      | price | rating |
+------+----------+--------+------------+-------+--------+
| B003 | Easy CPP | Berry  | 2022-11-12 | 16000 |    7.9 |
+------+----------+--------+------------+-------+--------+


MariaDB [bookshop]> select * from book where rating >= 8 and price < 30000;		// where์ ˆ์—๋Š” and ์ด์šฉํ•ด์„œ ์—ฌ๋Ÿฌ ์กฐ๊ฑด ๋‚˜์—ด์ด ๊ฐ€๋Šฅ
+------+-------------+--------+------------+-------+--------+
| code | titls       | author | pdate      | price | rating |
+------+-------------+--------+------------+-------+--------+
| B002 | Cool Java   | Berry  | 2023-02-21 | 28000 |    8.5 |
| B005 | HTM and CSS | Sam    | 2022-05-16 | 21000 |    8.9 |
+------+-------------+--------+------------+-------+--------+




MariaDB [bookshop]> update book set rating = 8.8;
Query OK, 5 rows affected (0.010 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MariaDB [bookshop]> select * from book;
+------+--------------------+--------+------------+-------+--------+
| code | titls              | author | pdate      | price | rating |
+------+--------------------+--------+------------+-------+--------+
| B001 | Power Java         | Peter  | 2023-01-12 | 21000 |    8.8 |
| B002 | Cool Java          | Berry  | 2023-02-21 | 28000 |    8.8 |
| B003 | Easy CPP           | Berry  | 2022-11-12 | 16000 |    8.8 |
| B004 | Python Programming | James  | 2022-09-16 | 19000 |    8.8 |
| B005 | HTM and CSS        | Sam    | 2022-05-16 | 21000 |    8.8 |
+------+--------------------+--------+------------+-------+--------+
5 rows in set (0.001 sec)

MariaDB [bookshop]> update book set rating = 9.3 where titls like '%CPP%';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [bookshop]> update book set rating = 9.1, price = price * 1.3 where titls like '%CPP%';
Query OK, 1 row affected (0.010 sec)

MariaDB [bookshop]> delete from book where titls like '%CPP%';
Query OK, 1 row affected (0.011 sec)


MariaDB [bookshop]> select * from book;
+------+--------------------+--------+------------+-------+--------+
| code | titls              | author | pdate      | price | rating |
+------+--------------------+--------+------------+-------+--------+
| B001 | Power Java         | Peter  | 2023-01-12 | 21000 |    8.8 |
| B002 | Cool Java          | Berry  | 2023-02-21 | 28000 |    8.8 |
| B004 | Python Programming | James  | 2022-09-16 | 19000 |    8.8 |
| B005 | HTM and CSS        | Sam    | 2022-05-16 | 21000 |    8.8 |
+------+--------------------+--------+------------+-------+--------+
4 rows in set (0.001 sec)
LIST

'๐Ÿ“– Coding Study > ์›น ํ”„๋กœ๊ทธ๋ž˜๋ฐ' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

2023.05.01 ์›” (DB)  (0) 2023.05.01
2023.04.04 ํ™” (PHP ๋‹ค์ฐจ์› ๋ฐฐ์—ด)  (0) 2023.04.04
2023.04.03 ์›” (PHP ๋ฐฐ์—ด)  (0) 2023.04.03
2023.03.28 ํ™” (PHP ๋ฐ˜๋ณต๋ฌธ, breakใ†continue ํ‚ค์›Œ๋“œ, ๋ฐฐ์—ด)  (0) 2023.03.28
2023.03.27 ์›” (PHP ์กฐ๊ฑด๋ฌธ)  (0) 2023.03.27

'๐Ÿ“– Coding Study/์›น ํ”„๋กœ๊ทธ๋ž˜๋ฐ'์˜ ๋‹ค๋ฅธ๊ธ€

  • ํ˜„์žฌ๊ธ€2023.05.02 ํ™” (DB ๊ณ„์†)

๊ด€๋ จ๊ธ€

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”