본문 바로가기

Engineer/virtual machine

[리눅스] 우분투 ubuntu에 필요한 SQL 구문

자주 쓰는 용어

단위 크기
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
이메일 email 문자 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: 0

insert로 내용물을 넣을 수 있고, 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)