실습을 해보자
== 'dbadmin'@'%' 계정을 생성하세요.
== 'dbadmin'@'%' 계정 암호를 mypass 로 설정하세요
create user 'dbadmin'@'%' Identified by 'mypass';
MariaDB [(none)]> select user, host from mysql.user;
+---------------+-----------+
| User | Host |
+---------------+-----------+
| dbadmin | % |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
| wordpressuser | localhost |
| wpuser | localhost |
+---------------+-----------+
6 rows in set (0.009 sec)
=='dbadmin'@'%' 계정한테 grant 권한을 제외한 모든 권한을 부여하세요.
MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, REFERENCES, INDEX, CREATE VIEW, SHOW VIEW ON *.* TO 'dbadmin'@'%';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
grant <======> reboke
아래 그림을 보고 테이블을 생성한다.
*. 테이블 생성이 어려우면 아래의 테이블 생성파일 참조
=> http://twoseven.kr/class803/newdb/sample_table.txt
테이블을 생성후 아래의 파일을 다운로드 받아서
각각의 테이블에 레코드를 입력하세요.
http://twoseven.kr/class803/newdb/db_data.tar
* tar 파일을 풀면 README 가 있습니다.
여기서 잠깐 !!!
DESCRIBE 명령어에서 Key 열이 MUL로 표시되는 경우,
이는 해당 열이 인덱스로 설정되어 있다는 것을 나타냅니다.
즉, MUL은 Multiple Index를 의미합니다.
Multiple Index는 테이블에서 여러 열에 대한 복합 인덱스를 의미합니다. 복합 인덱스는 둘 이상의 열을 포함하는 인덱스로, 이를 통해 여러 열에 대한 검색 및 정렬을 더 효율적으로 수행할 수 있습니다.
복합 인덱스는 다음과 같은 특징을 가집니다:
- 다중 열 검색 및 정렬: 복합 인덱스를 사용하면 여러 열을 기반으로 데이터를 검색하거나 정렬할 수 있습니다. 예를 들어, 두 개의 열이 복합 인덱스로 지정된 경우, 이 두 열을 사용하여 WHERE 절이나 ORDER BY 절에서 더 효율적으로 작업할 수 있습니다.
- 쿼리 최적화: 특정 쿼리에 대한 성능을 최적화하는 데 사용됩니다. 특히 WHERE 절에서 여러 조건을 사용하는 쿼리에 유용합니다.
- 인덱스 크기 및 저장 공간 절약: 각 복합 인덱스는 여러 열에 대한 정보를 저장하므로, 별도의 단일 인덱스를 생성하는 것보다 저장 공간을 절약할 수 있습니다.
다음은 buy 테이블에서 id 열에 별도의 인덱스를 추가하는 방법입
CREATE INDEX idx_id ON buy (id);
또는
constraint foreign key(id) references member(id)
on delete restrit on update cascade입니다.
tar 파일을 다운로드 하고 풀어줍니다.
그 후 , member.data buy.data notebook_prod.data와 README가 생성되었는지 확인합니다.
[root@localhost mysql]# wget http://twoseven.kr/class803/newdb/db_data.tar
--2024-02-05 10:55:30-- http://twoseven.kr/class803/newdb/db_data.tar
Resolving twoseven.kr (twoseven.kr)... 129.154.222.241
Connecting to twoseven.kr (twoseven.kr)|129.154.222.241|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 10240 (10K) [application/x-tar]
Saving to: `db_data.tar'
db_data.tar 100%[===================>] 10.00K --.-KB/s in 0s
2024-02-05 10:55:30 (221 MB/s) - `db_data.tar' saved [10240/10240]
[root@localhost mysql]# tar -xf db_data.tar
[root@localhost mysql]# ls
README db_data.tar member mysql.sock
aria_log.00000001 ib_buffer_pool member.data mysql_upgrade_info
aria_log_control ib_logfile0 multi-master.info notebook_prod.data
bank ibdata1 myshop performance_schema
buy.data ibtmp1 mysql wordpress
myshop db에서 테이블을 생성하고 데이터 파일을 table에 로드합니다.
MariaDB [myshop]> create table notebook_prod (
-> vender char(10) not null,
-> model varchar(20) not null,
-> price int not null,
-> primary key(model));
Query OK, 0 rows affected (0.003 sec)
load data local infile '/tmp/sql/notebook_prod.data' into table notebook_prod;
MariaDB [myshop]> select * from notebook_prod;
+--------------------+----------------+---------+
| vender | model | price |
+--------------------+----------------+---------+
| 삼성전자 T930Q | 2054810 | 0 |
| 삼성전자 | NT350XCR-AD5WA | 651030 |
| 삼성전자 | NT550XCR-AD3A | 726210 |
| 삼성전자 | NT550XDA-KC35W | 751440 |
| 삼성전자 | NT550XDA-KC58G | 110889 |
| 삼성전자 | NT550XDZ-AD1AG | 459000 |
| 삼성전자 | NT550XDZ-GD7AG | 1320600 |
| 삼성전자 | NT560XDA-XC58 | 1370520 |
| 삼성전자 | NT560XDZ-G58A1 | 1185050 |
| 삼성전자 | NT560XDZ-G78A | 1548490 |
| 삼성전자 | NT750QCR-A78A | 1697990 |
| 삼성전자 | NT750QCR-A78A1 | 1710000 |
| 삼성전자 | NT767XCM-K58S | 1109000 |
| 삼성전자 | NT930QCA-KC71S | 2569310 |
| 삼성전자 | NT930XDZ-A58AW | 1599000 |
| 삼성전자 | NT950QDA-XF71B | 2219850 |
| 삼성전자 | NT950XDA-X71AW | 2081200 |
| 삼성전자 | NT950XDA-XF58W | 1613420 |
| 삼성전자 | NT950XDZ-A58AW | 1467450 |
| 삼성전자 | NT950XDZ-G58AW | 1614290 |
+--------------------+----------------+---------+
20 rows in set (0.000 sec)
member와 buy도 마찬가지로 진행한다.
1. member table 에서 주소가 서울인 사람을 출력하세요
(* 출력결과는 name 오름차순으로 출력하세요)
MariaDB [myshop]> select * from member where address='서울' order by name ;
+-----------+---------+---------+---------------+
| name | id | address | mobile |
+-----------+---------+---------+---------------+
| 김건모 | kunmo | 서울 | 010-1131-1011 |
| 마마무 | mamamu | 서울 | 010-3230-1788 |
| 박기영 | kiyoung | 서울 | 010-1323-0099 |
| 샤이니 | shaini | 서울 | 010-3232-1783 |
| 아이유 | iu | 서울 | 010-1231-1112 |
| 에일리 | ailee | 서울 | 010-1323-1301 |
| 이효리 | hyory | 서울 | 010-1231-8900 |
| 임재범 | jabum | 서울 | 010-3231-9910 |
| 장나라 | nara | 서울 | 010-1234-5678 |
| 차지연 | jiyeon | 서울 | 010-1234-1121 |
| 태연 | taeyeon | 서울 | 010-1311-3231 |
| 현빈 | hyunbin | 서울 | 010-2341-1010 |
+-----------+---------+---------+---------------+
12 rows in set (0.000 sec)
2. member table 에서 주소가 서울이거나 또는 대전인 사람을 출력하세요
MariaDB [myshop]> select * from member where address='서울' or address='대전' order by name;
+-----------+---------+---------+---------------+
| name | id | address | mobile |
+-----------+---------+---------+---------------+
| 김건모 | kunmo | 서울 | 010-1131-1011 |
| 마마무 | mamamu | 서울 | 010-3230-1788 |
| 박기영 | kiyoung | 서울 | 010-1323-0099 |
| 샤이니 | shaini | 서울 | 010-3232-1783 |
| 아이유 | iu | 서울 | 010-1231-1112 |
| 에일리 | ailee | 서울 | 010-1323-1301 |
| 이해리 | haeri | 대전 | 010-1130-1323 |
| 이효리 | hyory | 서울 | 010-1231-8900 |
| 임재범 | jabum | 서울 | 010-3231-9910 |
| 장나라 | nara | 서울 | 010-1234-5678 |
| 제시 | jessi | 대전 | 010-1323-1111 |
| 지아 | jia | 대전 | 010-8780-1032 |
| 차지연 | jiyeon | 서울 | 010-1234-1121 |
| 태연 | taeyeon | 서울 | 010-1311-3231 |
| 현빈 | hyunbin | 서울 | 010-2341-1010 |
+-----------+---------+---------+---------------+
15 rows in set (0.001 sec)
3. 노트북 제품중 가격이 100만원 이상이고 200만원 미만인 제품을 출력하세요.
MariaDB [myshop]> select * from notebook_prod where price<2000000 and price>=1000000 order by price;
+--------------+----------------+---------+
| vender | model | price |
+--------------+----------------+---------+
| 삼성전자 | NT767XCM-K58S | 1109000 |
| 삼성전자 | NT560XDZ-G58A1 | 1185050 |
| 삼성전자 | NT550XDZ-GD7AG | 1320600 |
| 삼성전자 | NT560XDA-XC58 | 1370520 |
| 삼성전자 | NT950XDZ-A58AW | 1467450 |
| 삼성전자 | NT560XDZ-G78A | 1548490 |
| 삼성전자 | NT930XDZ-A58AW | 1599000 |
| 삼성전자 | NT950XDA-XF58W | 1613420 |
| 삼성전자 | NT950XDZ-G58AW | 1614290 |
| 삼성전자 | NT750QCR-A78A | 1697990 |
| 삼성전자 | NT750QCR-A78A1 | 1710000 |
+--------------+----------------+---------+
11 rows in set (0.000 sec)
4. 노트북 제품의 평균값은 얼마인가요?
MariaDB [myshop]> select avg(price) from notebook_prod;
+--------------+
| avg(price) |
+--------------+
| 1290236.9500 |
+--------------+
1 row in set (0.000 sec)
5. 노트북 구매자중 노트북을 가장 많이 구매한 사람을 출력하세요
MariaDB [myshop]> select id,sum(count) from buy group by id order by sum(count)
-> desc limit 1;
+-------+------------+
| id | sum(count) |
+-------+------------+
| ailee | 3 |
+-------+------------+
6. inner join 으로 아래처럼 name,id,model,count 칼럼의 모든 레코드를
출력하세요.
+-----------+---------+----------------+-------+
| name | id | model | count |
+-----------+---------+----------------+-------+
| 장나라 | nara | NT350XCR-AD5WA | 1 |
| 이효리 | hyory | NT560XDZ-G78A | 1 |
| 임재범 | jabum | NT950XDZ-A58AW | 2 |
.... 이하 생략
MariaDB [myshop]> SELECT m.name, b.id, b.model, b.count
-> FROM member AS m
-> INNER JOIN buy AS b ON m.id = b.id;
+-----------+---------+----------------+-------+
| name | id | model | count |
+-----------+---------+----------------+-------+
| 에일리 | ailee | NT950QDA-XF71B | 3 |
| 이해리 | haeri | NT550XDA-KC35W | 1 |
| 박혜원 | hewon | NT750QCR-A78A1 | 1 |
| 이효리 | hyory | NT560XDZ-G78A | 1 |
| 박정현 | hyun | NT550XCR-AD3A | 1 |
| 아이유 | iu | NT350XCR-AD5WA | 1 |
| 임재범 | jabum | NT950XDZ-A58AW | 2 |
| 제시 | jessi | NT950QDA-XF71B | 1 |
| 지아 | jia | NT750QCR-A78A1 | 1 |
| 마마무 | mamamu | NT750QCR-A78A | 2 |
| 장나라 | nara | NT350XCR-AD5WA | 1 |
| 태연 | taeyeon | NT750QCR-A78A1 | 2 |
| 화요비 | yobi | NT560XDZ-G58A1 | 1 |
+-----------+---------+----------------+-------+
7. 노트북을 구매하지 않은 사람들의 이름과 id 를 출력하세요.
MariaDB [myshop]> select member.id as id, buy.count as count
from member left join buy on member.id=buy.id where count is null;
+---------+-------+
| id | count |
+---------+-------+
| hyunbin | NULL |
| jiyeon | NULL |
| kiyoung | NULL |
| kunmo | NULL |
| shaini | NULL |
+---------+-------+
8. 아래처럼 name,id,model,price,count 컬럼의 모든 레코드를 출력하세요.
+-----------+---------+----------------+---------+-------+
| name | id | model | price | count |
+-----------+---------+----------------+---------+-------+
| 장나라 | nara | NT350XCR-AD5WA | 651030 | 1 |
| 이효리 | hyory | NT560XDZ-G78A | 1548490 | 1 |
| 임재범 | jabum | NT950XDZ-A58AW | 1467450 | 2 |
| 아이유 | iu | NT350XCR-AD5WA | 651030 | 1 |
... 이하 생략
9. 아래처럼 총구매금액까지 포함해서 출력하세요.
+-----------+---------+----------------+---------+-------+-----------------+
| name | id | model | price | count | 총구매금액 |
+-----------+---------+----------------+---------+-------+-----------------+
| 장나라 | nara | NT350XCR-AD5WA | 651030 | 1 | 651030 |
| 이효리 | hyory | NT560XDZ-G78A | 1548490 | 1 | 1548490 |
| 임재범 | jabum | NT950XDZ-A58AW | 1467450 | 2 | 2934900 |
MariaDB [myshop]> select m.name name,m.id id,
n.model model,n.price price,
b.count count, b.count*n.price as '총구매금액' from buy as b inner join notebook_prod
as n on b.model=n.model inner join member as m on m.id=b.id;
+-----------+---------+----------------+---------+-------+-----------------+
| name | id | model | price | count | 총구매금액 |
+-----------+---------+----------------+---------+-------+-----------------+
| 에일리 | ailee | NT950QDA-XF71B | 2219850 | 3 | 6659550 |
| 이해리 | haeri | NT550XDA-KC35W | 751440 | 1 | 751440 |
| 박혜원 | hewon | NT750QCR-A78A1 | 1710000 | 1 | 1710000 |
| 이효리 | hyory | NT560XDZ-G78A | 1548490 | 1 | 1548490 |
| 박정현 | hyun | NT550XCR-AD3A | 726210 | 1 | 726210 |
| 아이유 | iu | NT350XCR-AD5WA | 651030 | 1 | 651030 |
| 임재범 | jabum | NT950XDZ-A58AW | 1467450 | 2 | 2934900 |
| 제시 | jessi | NT950QDA-XF71B | 2219850 | 1 | 2219850 |
| 지아 | jia | NT750QCR-A78A1 | 1710000 | 1 | 1710000 |
| 마마무 | mamamu | NT750QCR-A78A | 1697990 | 2 | 3395980 |
| 장나라 | nara | NT350XCR-AD5WA | 651030 | 1 | 651030 |
| 태연 | taeyeon | NT750QCR-A78A1 | 1710000 | 2 | 3420000 |
| 화요비 | yobi | NT560XDZ-G58A1 | 1185050 | 1 | 1185050 |
+-----------+---------+----------------+---------+-------+-----------------+
'DB' 카테고리의 다른 글
| Database 와 Web연동 (1) | 2024.02.02 |
|---|---|
| MySQL Transaction 설정 및 예제 (1) | 2024.02.02 |
| MariaDB/MySQL 뷰 백업/복원 가이드 (0) | 2024.02.02 |
| MariaDB/MySQL 뷰(View) (2) | 2024.02.02 |
| MySql 샘플 DB (0) | 2024.02.01 |
