자주 쓰는 용어
| 단위 | 크기 |
| tinyint | 1byte |
| smallint | 2byte |
| char(50) | |
| varchar(50) | |
| 스키마 : 테이블에 적재될 데이터의 구조와 형식 | |
| CRUB-> Create, read-select, update, delete select from where group by order by |
|
우분투에서 SQL실행하기
DB확인하기
show databases
root@ubuntu:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.001 sec)상태확인
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
Connection id: 31
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb3
Conn. characterset: utf8mb3
UNIX socket: /run/mysqld/mysqld.sock
Uptime: 3 min 39 sec
Threads: 1 Questions: 62 Slow queries: 0 Opens: 33 Open tables: 26 Queries per second avg: 0.283
--------------flush privileges
서비스를 저장
exit or ctrl+D
나가기db생성명령어
root@ubuntu:~# create database testdb;
root@ubuntu:~# drop database testdb;
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.000 sec)mysql에 들어가서 보면 만들어진 테이블을 확인할 수 있다.
MariaDB [testdb]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> show tables
-> ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.000 sec)이제 만든 testdb에 테이블을 만들어 보자.
MariaDB [mysql]> use testdb;
Database changed
MariaDB [testdb]> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.000 sec)
MariaDB [testdb]> create table testT (num int(10), name char(20));
Query OK, 0 rows affected (0.005 sec)explain 혹은 desc로 만든 테이블을 상세히 볼 수 있다.
MariaDB [testdb]> explain testT;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.001 sec)
MariaDB [testdb]> desc testT
-> ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| num | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.001 sec)MariaDB [testdb]> drop table testT
-> ;
Query OK, 0 rows affected (0.004 sec)
MariaDB [testdb]> create table testT (uid int(4), name char(20), email char(30));
Query OK, 0 rows affected (0.004 sec)
MariaDB [testdb]> desc testT;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| uid | int(4) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| email | char(30) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)수정할 경우 아래와 같이 명령하면 된다.
MariaDB [testdb]> alter table testT rename testT1;
Query OK, 0 rows affected (0.003 sec)
MariaDB [testdb]> alter table testT1 add column homepage char(50);
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc testT1;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| uid | int(4) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| email | char(30) | YES | | NULL | |
| homepage | char(50) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.001 sec)table안에서 지울 경우
MariaDB [testdb]> alter table testT1 drop column email;
Query OK, 0 rows affected (0.051 sec)
Records: 0 Duplicates: 0 Warnings: 0#이름 변경할 경우 change
MariaDB [testdb]> alter table testT1 change column homepage home char(80);
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0
#이름 변경 안할 경우 modify
MariaDB [testdb]> alter table testT1 modify column home char(200);
Query OK, 0 rows affected (0.006 sec)
Records: 0 Duplicates: 0 Warnings: 0테이블 만들기
형식 create 테이블 (필드명 데이터타입&크기 [not null]
| 필드명 | 필드길이 | ||
| 회원번호 | uid | 숫자 | 4 |
| 이름 | name | 문자 | 10 |
| 이메일 | 문자 | 20 |
MariaDB [testdb]> create table testT2 (uid int(4), name char(10), email char(20));
Query OK, 0 rows affected (0.003 sec)
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| uid | int(4) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| email | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.001 sec)
MariaDB [testdb]> alter table testT2 add column homepage char(50);
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0insert로 내용물을 넣을 수 있고, select로 가져올 수 있다.
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(1,'song','song@abc.com','user1@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> select * from testT2;
+------+------+--------------+---------------+
| uid | name | email | homepage |
+------+------+--------------+---------------+
| 1 | song | song@abc.com | user1@abc.com |
+------+------+--------------+---------------+
1 row in set (0.000 sec)
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(2,'park','park@abc.com','user2@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(3,'choi','choi@abc.com','user3@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(4,'young','yong@abc.com','user3@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(5,'jung','jung@abc.com','user3@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert into testT2(uid,name,email,homepage) value(6,'jung','jung@abc.com','user3@abc.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> select * from testT2;
+------+-------+--------------+---------------+
| uid | name | email | homepage |
+------+-------+--------------+---------------+
| 1 | song | song@abc.com | user1@abc.com |
| 2 | park | park@abc.com | user2@abc.com |
| 3 | choi | choi@abc.com | user3@abc.com |
| 4 | young | yong@abc.com | user3@abc.com |
| 5 | jung | jung@abc.com | user3@abc.com |
| 6 | jung | jung@abc.com | user3@abc.com |
+------+-------+--------------+---------------+
6 rows in set (0.000 sec)특수한 조건을 가져오고 싶을 땐 이렇게 입력한다.
MariaDB [testdb]> select name, email from testT2 where uid=3;
+------+--------------+
| name | email |
+------+--------------+
| choi | choi@abc.com |
+------+--------------+
1 row in set (0.000 sec)update를 해보자. 내용물을 수정할 땐 update, 틀을 수정할 땐 alter를 사용한다.
MariaDB [testdb]> update testT2 set email='song@naver.com' where name='song';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> select * from testT2 where uid=1;
+------+------+----------------+---------------+
| uid | name | email | homepage |
+------+------+----------------+---------------+
| 1 | song | song@naver.com | user1@abc.com |
+------+------+----------------+---------------+
1 row in set (0.000 sec)deltete : 조건이 없으면 전부 삭제
MariaDB [testdb]> delete from testT2 where uid=6;
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> select * from testT2
-> ;
+------+-------+----------------+---------------+
| uid | name | email | homepage |
+------+-------+----------------+---------------+
| 1 | song | song@naver.com | user1@abc.com |
| 2 | park | park@abc.com | user2@abc.com |
| 3 | choi | choi@abc.com | user3@abc.com |
| 4 | young | yong@abc.com | user3@abc.com |
| 5 | jung | jung@abc.com | user3@abc.com |
+------+-------+----------------+---------------+
5 rows in set (0.000 sec)limit을 사용해서 어떤 테이블인지 확인 후 select를 사용한다.(너무 방대하므로 확인)
MariaDB [testdb]> select * from testT2 limit 2;
+------+------+----------------+---------------+
| uid | name | email | homepage |
+------+------+----------------+---------------+
| 1 | song | song@naver.com | user1@abc.com |
| 2 | park | park@abc.com | user2@abc.com |
+------+------+----------------+---------------+
2 rows in set (0.000 sec)select name, age from testT3 limit 3;
select distinct name from testT3;
select name, uid from testT3 where email like"%d%'
like %찾기 패턴 찾기
MariaDB [testdb]> select name, uid from testT3 where age is not null;
+------+------+
| name | uid |
+------+------+
| aaa | 1 |
| aaa | 2 |
| bbb | 3 |
| ccc | 4 |
| ddd | 5 |
| aaa | 6 |
| bbb | 7 |
| ccc | 8 |
| ddd | 9 |
| eee | 10 |
+------+------+MariaDB [testdb]> select * from testT3 where age > 20 and age < 30;
+------+------+-------------+------+------+------+--------+-------+
| uid | name | email | age | sex | save | milage | level |
+------+------+-------------+------+------+------+--------+-------+
| 8 | ccc | ccc@abc.com | 22 | M | 3111 | 3000 | 31 |
| 9 | ddd | ddd@abc.com | 22 | F | 4111 | 4000 | 41 |
| 10 | eee | eee@abc.com | 22 | M | 5111 | 5000 | 51 |
+------+------+-------------+------+------+------+--------+-------+
3 rows in set (0.000 sec)정렬: 기본값 asc, 설정 desc 내림차순
MariaDB [testdb]> select * from testT3 where age > 20 order by save desc
-> ;
+------+------+-------------+------+------+------+--------+-------+
| uid | name | email | age | sex | save | milage | level |
+------+------+-------------+------+------+------+--------+-------+
| 5 | ddd | ddd@abc.com | 44 | M | 5111 | 5000 | 51 |
| 10 | eee | eee@abc.com | 22 | M | 5111 | 5000 | 51 |
| 4 | ccc | ccc@abc.com | 44 | F | 4111 | 4000 | 41 |
| 9 | ddd | ddd@abc.com | 22 | F | 4111 | 4000 | 41 |
| 3 | bbb | bbb@abc.com | 33 | W | 3111 | 2000 | 31 |
| 8 | ccc | ccc@abc.com | 22 | M | 3111 | 3000 | 31 |
| 2 | aaa | aaa@abc.com | 33 | F | 2111 | 1000 | 21 |
| 6 | aaa | aaa@abc.com | 33 | M | 1111 | 1000 | 11 |
+------+------+-------------+------+------+------+--------+-------+
8 rows in set (0.000 sec)
MariaDB [testdb]> select * from testT3 where age > 20 order by save asc;
+------+------+-------------+------+------+------+--------+-------+
| uid | name | email | age | sex | save | milage | level |
+------+------+-------------+------+------+------+--------+-------+
| 6 | aaa | aaa@abc.com | 33 | M | 1111 | 1000 | 11 |
| 2 | aaa | aaa@abc.com | 33 | F | 2111 | 1000 | 21 |
| 3 | bbb | bbb@abc.com | 33 | W | 3111 | 2000 | 31 |
| 8 | ccc | ccc@abc.com | 22 | M | 3111 | 3000 | 31 |
| 4 | ccc | ccc@abc.com | 44 | F | 4111 | 4000 | 41 |
| 9 | ddd | ddd@abc.com | 22 | F | 4111 | 4000 | 41 |
| 5 | ddd | ddd@abc.com | 44 | M | 5111 | 5000 | 51 |
| 10 | eee | eee@abc.com | 22 | M | 5111 | 5000 | 51 |
+------+------+-------------+------+------+------+--------+-------+
8 rows in set (0.000 sec)출력되는 컬럼 값 변경하기
MariaDB [testdb]> select level as rank from testT3;
+------+
| rank |
+------+
| 11 |
| 21 |
| 31 |
| 41 |
| 51 |
| 11 |
| 21 |
| 31 |
| 41 |
| 51 |
+------+
10 rows in set (0.000 sec)MariaDB [testdb]> select name, 'milage is', milage+300 as bonus from testT3 where milage > 2000 order by milage desc;
+------+-----------+-------+
| name | milage is | bonus |
+------+-----------+-------+
| ddd | milage is | 5300 |
| eee | milage is | 5300 |
| ccc | milage is | 4300 |
| ddd | milage is | 4300 |
| ccc | milage is | 3300 |
+------+-----------+-------+
5 rows in set (0.000 sec)MariaDB [testdb]> select count(*) from testT3 where age=33;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.000 sec)max, avg, min,sum
MariaDB [testdb]> select max(milage) as milageMax from testT3 where age = 33;
+-----------+
| milageMax |
+-----------+
| 2000 |
+-----------+
1 row in set (0.000 sec)
MariaDB [testdb]> select avg(milage) as milageAvg from testT3 where age = 33;
+-----------+
| milageAvg |
+-----------+
| 1333.3333 |
+-----------+
1 row in set (0.000 sec)
MariaDB [testdb]> select min(milage) as milageMin from testT3 where age = 33;
+-----------+
| milageMin |
+-----------+
| 1000 |
+-----------+
1 row in set (0.000 sec)
MariaDB [testdb]> select sum(milage) as milageSum from testT3 where age = 33;
+-----------+
| milageSum |
+-----------+
| 4000 |
+-----------+
1 row in set (0.000 sec)MariaDB [testdb]> select sex level, max(save), max(milage) from testT3 group by sex;
+-------+-----------+-------------+
| level | max(save) | max(milage) |
+-------+-----------+-------------+
| F | 4111 | 4000 |
| M | 5111 | 5000 |
| W | 3111 | 2000 |
+-------+-----------+-------------+
3 rows in set (0.000 sec)order by
select sex ,level, max(save), max(milage) from testT3 group by level order by level desc;
+------+-------+-----------+-------------+
| sex | level | max(save) | max(milage) |
+------+-------+-----------+-------------+
| M | 51 | 5111 | 5000 |
| F | 41 | 4111 | 4000 |
| W | 31 | 3111 | 3000 |
| F | 21 | 2111 | 2000 |
| M | 11 | 1111 | 1000 |
+------+-------+-----------+-------------+
5 rows in set (0.000 sec)having by
MariaDB [testdb]> select sex ,level, max(save), max(milage) from testT3 group by level having sex='M';
+------+-------+-----------+-------------+
| sex | level | max(save) | max(milage) |
+------+-------+-----------+-------------+
| M | 11 | 1111 | 1000 |
| M | 51 | 5111 | 5000 |
+------+-------+-----------+-------------+
2 rows in set (0.000 sec)between
MariaDB [testdb]> select milage,uid, level from testT3 where milage between 3000 and 4000;
+--------+------+-------+
| milage | uid | level |
+--------+------+-------+
| 4000 | 4 | 41 |
| 3000 | 8 | 31 |
| 4000 | 9 | 41 |
+--------+------+-------+
3 rows in set (0.000 sec)in과 not in
MariaDB [testdb]> select uid, milage from testT3 where milage in (1000,2000,3000);
+------+--------+
| uid | milage |
+------+--------+
| 1 | 1000 |
| 2 | 1000 |
| 3 | 2000 |
| 6 | 1000 |
| 7 | 2000 |
| 8 | 3000 |
+------+--------+
6 rows in set (0.000 sec)
MariaDB [testdb]> select uid, milage from testT3 where milage not in (1000,2000,3000);
+------+--------+
| uid | milage |
+------+--------+
| 4 | 4000 |
| 5 | 5000 |
| 9 | 4000 |
| 10 | 5000 |
+------+--------+
4 rows in set (0.000 sec)MariaDB [testdb]> select uid, substring(age,1,1) from testT3;
+------+--------------------+
| uid | substring(age,1,1) |
+------+--------------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 3 |
| 4 | 4 |
| 5 | 4 |
| 6 | 3 |
| 7 | 1 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
+------+--------------------+
10 rows in set (0.000 sec)
MariaDB [testdb]> select uid, substring(age,1,2) from testT3;
+------+--------------------+
| uid | substring(age,1,2) |
+------+--------------------+
| 1 | 11 |
| 2 | 33 |
| 3 | 33 |
| 4 | 44 |
| 5 | 44 |
| 6 | 33 |
| 7 | 11 |
| 8 | 22 |
| 9 | 22 |
| 10 | 22 |
+------+--------------------+
10 rows in set (0.000 sec)offset사용
MariaDB [testdb]> select * from testT3 limit 3,2;
+------+------+-------------+------+------+------+--------+-------+
| uid | name | email | age | sex | save | milage | level |
+------+------+-------------+------+------+------+--------+-------+
| 4 | ccc | ccc@abc.com | 44 | F | 4111 | 4000 | 41 |
| 5 | ddd | ddd@abc.com | 44 | M | 5111 | 5000 | 51 |
+------+------+-------------+------+------+------+--------+-------+
2 rows in set (0.000 sec)
'Engineer > virtual machine' 카테고리의 다른 글
| [리눅스] 우분투 ubuntu 마스터 네임서버 설치 및 운영 (0) | 2023.08.04 |
|---|---|
| [리눅스] 우분투 ubuntu 원격지 시스템관리 Telnet, SSH, XRDP (0) | 2023.08.01 |
| [리눅스] 우분투 ubuntu 방화벽 (0) | 2023.07.31 |
| [리눅스] 우분투 ubuntu owncloud로 웹하드 만들기 (0) | 2023.07.31 |
| [리눅스] 우분투 ubuntu 각종 스크립트 (0) | 2023.07.30 |
