반응형
 

 

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

mysql timezone convert CONVERT_TZ  (0) 2019.06.22
Oracle Sql Developer에서 Mssql 사용하기  (0) 2019.04.15
Mysql 5.1.73 에 maria jdbc, mysql jdbc 6 붙이면?  (0) 2019.04.09
mysql dump and 복구  (0) 2019.04.09
CentOS7 MariaDB 설치  (0) 2018.12.19
오라클 용량 산정 참고 쿼리  (0) 2018.12.14
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
반응형

1. 확인

[root@goni9071 ~]# rpm -qa | grep MariaDB


2. 저장소 추가

[root@goni9071 ~]# vi /etc/yum.repos.d/MariaDB.repo


[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


2-1. 저장소 확인

[root@goni9071 ~]# yum repolist


repo id                                                  repo name                                                                               status
base/7/x86_64                                            CentOS-7 - Base                                                                         10,019
epel/x86_64                                              Extra Packages for Enterprise Linux 7 - x86_64                                          12,745
extras/7/x86_64                                          CentOS-7 - Extras                                                                          321
mariadb                                                  MariaDB                                                                                     76
updates/7/x86_64                                         CentOS-7 - Updates                                                                         609
repolist: 23,770


3. 설치

[root@goni9071 ~]# yum install MariaDB-server


 
 
=======================================================================================================================================================
 Package                                   Arch                       Version                                        Repository                   Size
=======================================================================================================================================================
Installing:
 MariaDB-client                            x86_64                     10.1.37-1.el7.centos                           mariadb                      40 M
     replacing  mariadb.x86_64 1:5.5.56-2.el7
 MariaDB-server                            x86_64                     10.1.37-1.el7.centos                           mariadb                     104 M
     replacing  mariadb-server.x86_64 1:5.5.56-2.el7
 MariaDB-shared                            x86_64                     10.1.37-1.el7.centos                           mariadb                     1.3 M
     replacing  mariadb-libs.x86_64 1:5.5.56-2.el7
Installing for dependencies:
 MariaDB-common                            x86_64                     10.1.37-1.el7.centos                           mariadb                     123 k
 boost-program-options                     x86_64                     1.53.0-27.el7                                  base                        156 k
 galera                                    x86_64                     25.3.24-1.rhel7.el7.centos                     mariadb                     8.1 M
 jemalloc                                  x86_64                     3.6.0-1.el7                                    epel                        105 k
 lsof                                      x86_64                     4.87-6.el7                                     base                        331 k
 rsync                                     x86_64                     3.1.2-4.el7                                    base                        403 k
 
Transaction Summary
=======================================================================================================================================================
Install  3 Packages (+6 Dependent packages)
 
Total download size: 154 M
Is this ok [y/d/N]:
 
 
Downloading packages:
경고: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-10.1.37-centos73-x86_64-common.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY:39 ETA
Public key for MariaDB-10.1.37-centos73-x86_64-common.rpm is not installed
(1/9): MariaDB-10.1.37-centos73-x86_64-common.rpm                                                                               | 123 kB  00:00:01
(2/9): MariaDB-10.1.37-centos73-x86_64-client.rpm                                                                               |  40 MB  00:01:11
(3/9): boost-program-options-1.53.0-27.el7.x86_64.rpm                                                                           | 156 kB  00:00:00
(4/9): MariaDB-10.1.37-centos73-x86_64-shared.rpm                                                                               | 1.3 MB  00:00:02
(5/9): lsof-4.87-6.el7.x86_64.rpm                                                                                               | 331 kB  00:00:00
(6/9): rsync-3.1.2-4.el7.x86_64.rpm                                                                                             | 403 kB  00:00:00
(7/9): jemalloc-3.6.0-1.el7.x86_64.rpm                                                                                          | 105 kB  00:00:02
(8/9): galera-25.3.24-1.rhel7.el7.centos.x86_64.rpm                                                                             | 8.1 MB  00:00:26
(9/9): MariaDB-10.1.37-centos73-x86_64-server.rpm                                                                               | 104 MB  00:02:16
-------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                  1.1 MB/s | 154 MB  00:02:17
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid     : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
 From       : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Is this ok [y/N]:
 
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-common-10.1.37-1.el7.centos.x86_64                                                                                         1/12
  Installing : MariaDB-client-10.1.37-1.el7.centos.x86_64                                                                                         2/12
  Installing : boost-program-options-1.53.0-27.el7.x86_64                                                                                         3/12
  Installing : galera-25.3.24-1.rhel7.el7.centos.x86_64                                                                                           4/12
  Installing : rsync-3.1.2-4.el7.x86_64                                                                                                           5/12
  Installing : lsof-4.87-6.el7.x86_64                                                                                                             6/12
  Installing : jemalloc-3.6.0-1.el7.x86_64                                                                                                        7/12
  Installing : MariaDB-server-10.1.37-1.el7.centos.x86_64                                                                                         8/12
  Installing : MariaDB-shared-10.1.37-1.el7.centos.x86_64                                                                                         9/12
  Erasing    : 1:mariadb-server-5.5.56-2.el7.x86_64                                                                                              10/12
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
  Erasing    : 1:mariadb-5.5.56-2.el7.x86_64                                                                                                     11/12
  Erasing    : 1:mariadb-libs-5.5.56-2.el7.x86_64                                                                                                12/12
  Verifying  : MariaDB-client-10.1.37-1.el7.centos.x86_64                                                                                         1/12
  Verifying  : jemalloc-3.6.0-1.el7.x86_64                                                                                                        2/12
  Verifying  : MariaDB-shared-10.1.37-1.el7.centos.x86_64                                                                                         3/12
  Verifying  : galera-25.3.24-1.rhel7.el7.centos.x86_64                                                                                           4/12
  Verifying  : lsof-4.87-6.el7.x86_64                                                                                                             5/12
  Verifying  : rsync-3.1.2-4.el7.x86_64                                                                                                           6/12
  Verifying  : boost-program-options-1.53.0-27.el7.x86_64                                                                                         7/12
  Verifying  : MariaDB-common-10.1.37-1.el7.centos.x86_64                                                                                         8/12
  Verifying  : MariaDB-server-10.1.37-1.el7.centos.x86_64                                                                                         9/12
  Verifying  : 1:mariadb-libs-5.5.56-2.el7.x86_64                                                                                                10/12
  Verifying  : 1:mariadb-server-5.5.56-2.el7.x86_64                                                                                              11/12
  Verifying  : 1:mariadb-5.5.56-2.el7.x86_64                                                                                                     12/12
 
Installed:
  MariaDB-client.x86_64 0:10.1.37-1.el7.centos      MariaDB-server.x86_64 0:10.1.37-1.el7.centos      MariaDB-shared.x86_64 0:10.1.37-1.el7.centos
 
Dependency Installed:
  MariaDB-common.x86_64 0:10.1.37-1.el7.centos      boost-program-options.x86_64 0:1.53.0-27.el7      galera.x86_64 0:25.3.24-1.rhel7.el7.centos
  jemalloc.x86_64 0:3.6.0-1.el7                     lsof.x86_64 0:4.87-6.el7                          rsync.x86_64 0:3.1.2-4.el7
 
Replaced:
  mariadb.x86_64 1:5.5.56-2.el7                 mariadb-libs.x86_64 1:5.5.56-2.el7                 mariadb-server.x86_64 1:5.5.56-2.el7
 
Complete!


4. 확인

[root@goni9071 ~]# rpm -qa | grep MariaDB


tcp6       0      0 :::3306                 :::*                    LISTEN


5. root 패스워드 설정

[root@goni9071 ~]# /usr/bin/mysqladmin -u root password -p
Enter password:
New password:
Confirm new password:


6. 접속

[root@goni9071 ~]# /usr/bin/mysql -u root -p
 
 
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.37-MariaDB MariaDB Server
 
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)]>


반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

Oracle Sql Developer에서 Mssql 사용하기  (0) 2019.04.15
Mysql 5.1.73 에 maria jdbc, mysql jdbc 6 붙이면?  (0) 2019.04.09
mysql dump and 복구  (0) 2019.04.09
mysql my session number id  (0) 2019.03.10
오라클 용량 산정 참고 쿼리  (0) 2018.12.14
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
반응형
 

 

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

Mysql 5.1.73 에 maria jdbc, mysql jdbc 6 붙이면?  (0) 2019.04.09
mysql dump and 복구  (0) 2019.04.09
mysql my session number id  (0) 2019.03.10
CentOS7 MariaDB 설치  (0) 2018.12.19
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
반응형

1. 확인

[root@goni9071 ~]# rpm -qa | grep MariaDB


2. 저장소 추가

[root@goni9071 ~]# vi /etc/yum.repos.d/MariaDB.repo


[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


2-1. 저장소 확인

[root@goni9071 ~]# yum repolist


repo id                                   repo name                                                                        status
HP-mcp                                    HP Management Component Pack for ProLiant                                              5
HP-spp                                    HP Service Pack for ProLiant                                                       1,335
base                                      CentOS-6 - Base                                                                  6,704+9
epel                                      Extra Packages for Enterprise Linux 6 - x86_64                                    12,497
extras                                    CentOS-6 - Extras                                                                     33
mariadb                                   MariaDB                                                                               59
rpmforge                                  RHEL 6 - RPMforge.net - dag                                                      4,717+1
updates                                   CentOS-6 - Updates                                                                198+27
repolist: 25,548


3. 설치

[root@goni9071 ~]# yum install MariaDB-server


Dependencies Resolved
 
==================================================================================================================================
 Package                               Arch                   Version                               Repository               Size
==================================================================================================================================
Installing:
 MariaDB-server                        x86_64                 10.2.18-1.el6                         mariadb                 111 M
Installing for dependencies:
 MariaDB-client                        x86_64                 10.2.18-1.el6                         mariadb                  48 M
 MariaDB-common                        x86_64                 10.2.18-1.el6                         mariadb                 154 k
 MariaDB-compat                        x86_64                 10.2.18-1.el6                         mariadb                 4.0 M
 boost-program-options                 x86_64                 1.41.0-28.el6                         base                    108 k
 galera                                x86_64                 25.3.24-1.rhel6.el6                   mariadb                 8.8 M
 libaio                                x86_64                 0.3.107-10.el6                        base                     21 k
 perl-DBI                              x86_64                 1.609-4.el6                           base                    705 k
 
Transaction Summary
==================================================================================================================================
Install       8 Package(s)
 
Total download size: 173 M
Installed size: 730 M
Is this ok [y/N]:
 


Downloading Packages:
(1/8): MariaDB-10.2.18-centos6-x86_64-client.rpm                                                           |  48 MB     00:30
(2/8): MariaDB-10.2.18-centos6-x86_64-common.rpm                                                           | 154 kB     00:00
(3/8): MariaDB-10.2.18-centos6-x86_64-compat.rpm                                                           | 4.0 MB     00:01
(4/8): MariaDB-10.2.18-centos6-x86_64-server.rpm                                                           | 111 MB     00:36
(5/8): boost-program-options-1.41.0-28.el6.x86_64.rpm                                                      | 108 kB     00:00
(6/8): galera-25.3.24-1.rhel6.el6.x86_64.rpm                                                               | 8.8 MB     00:06
(7/8): libaio-0.3.107-10.el6.x86_64.rpm                                                                    |  21 kB     00:00
(8/8): perl-DBI-1.609-4.el6.x86_64.rpm                                                                     | 705 kB     00:00
----------------------------------------------------------------------------------------------------------------------------------
Total                                                                                             2.2 MB/s | 173 MB     01:17
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid: "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 From  : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Is this ok [y/N]:
 



Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
kernel-firmware-2.6.32-754.3.5.el6.noarch is a duplicate with kernel-firmware-2.6.32-642.15.1.el6.noarch
kernel-headers-2.6.32-754.3.5.el6.x86_64 is a duplicate with kernel-headers-2.6.32-642.15.1.el6.x86_64
  Installing : MariaDB-compat-10.2.18-1.el6.x86_64                                                                                                                                                                                       1/8
  Installing : MariaDB-common-10.2.18-1.el6.x86_64                                                                                                                                                                                       2/8
warning: /etc/my.cnf created as /etc/my.cnf.rpmnew
  Installing : libaio-0.3.107-10.el6.x86_64                                                                                                                                                                                              3/8
  Installing : MariaDB-client-10.2.18-1.el6.x86_64                                                                                                                                                                                       4/8
  Installing : boost-program-options-1.41.0-28.el6.x86_64                                                                                                                                                                                5/8
  Installing : galera-25.3.24-1.rhel6.el6.x86_64                                                                                                                                                                                         6/8
  Installing : perl-DBI-1.609-4.el6.x86_64                                                                                                                                                                                               7/8
  Installing : MariaDB-server-10.2.18-1.el6.x86_64                                                                                                                                                                                       8/8
chown: cannot access `/var/lib/mysql': No such file or directory
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
 
'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h  password 'new-password'
 
Alternatively you can run:
'/usr/bin/mysql_secure_installation'
 
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
 
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.
 
Please report any problems at http://mariadb.org/jira
 
The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
 
  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                                                                                                                                               1/8
  Verifying  : MariaDB-server-10.2.18-1.el6.x86_64                                                                                                                                                                                       2/8
  Verifying  : MariaDB-common-10.2.18-1.el6.x86_64                                                                                                                                                                                       3/8
  Verifying  : galera-25.3.24-1.rhel6.el6.x86_64                                                                                                                                                                                         4/8
  Verifying  : MariaDB-compat-10.2.18-1.el6.x86_64                                                                                                                                                                                       5/8
  Verifying  : MariaDB-client-10.2.18-1.el6.x86_64                                                                                                                                                                                       6/8
  Verifying  : libaio-0.3.107-10.el6.x86_64                                                                                                                                                                                              7/8
  Verifying  : boost-program-options-1.41.0-28.el6.x86_64                                                                                                                                                                                8/8
 
Installed:
  MariaDB-server.x86_64 0:10.2.18-1.el6
 
Dependency Installed:
  MariaDB-client.x86_64 0:10.2.18-1.el6  MariaDB-common.x86_64 0:10.2.18-1.el6  MariaDB-compat.x86_64 0:10.2.18-1.el6  boost-program-options.x86_64 0:1.41.0-28.el6  galera.x86_64 0:25.3.24-1.rhel6.el6  libaio.x86_64 0:0.3.107-10.el6
  perl-DBI.x86_64 0:1.609-4.el6
 
Complete!
 


4. 확인

[root@goni9071 ~]# rpm -qa | grep MariaDB


MariaDB-client-10.2.18-1.el6.x86_64
MariaDB-server-10.2.18-1.el6.x86_64
MariaDB-compat-10.2.18-1.el6.x86_64
MariaDB-common-10.2.18-1.el6.x86_64


5. 시작

[root@goni9071 ~]# service mysql start
 
Starting MariaDB.181113 17:49:06 mysqld_safe Logging to '/var/lib/mysql/goni9071.tistory.com.err'.
181113 17:49:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
 SUCCESS!
 


6. 확인

[root@goni9071 ~]# netstat -an|grep 3306
tcp        0      0 :::3306                     :::*                        LISTE


7. root 패스워드 설정

[root@goni9071 ~]# mysqladmin -u root password


8. 접속

[root@goni9071 ~]# /usr/bin/mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.18-MariaDB-log MariaDB Server
 
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)]>



반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

mysql dump and 복구  (0) 2019.04.09
mysql my session number id  (0) 2019.03.10
CentOS7 MariaDB 설치  (0) 2018.12.19
오라클 용량 산정 참고 쿼리  (0) 2018.12.14
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
반응형

[규격 참조]

https://www.w3.org/TR/xml11/#charsets


[2a]       RestrictedChar       ::=       [#x1-#x8] | [#xB-#xC] | [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F]


위의 규격을 참조하여 XML에서 사용할 수 없는 문자열을 오라클에서 치환하는 쿼리 입니다.


SELECT regexp_replace('123?</>abABCDㄱㄴㄷㄹ한글!@#$%^&*()-=_+`~ㅁㄴㅁㄴ98ㅛasd9vh.. ', 
'['|| 
chr(1) || '-' || chr(8) 
||
chr(11) || '-' || chr(12) 
||
chr(14) || '-' || chr(31) 
||
chr(134) || '-' || chr(159) 
||
chr(127) || chr(128) || chr(129) || chr(130) || chr(131) || chr(132)
|| ']'
, '뷁') FROM dual;


[결과]

123?</>abABCDㄱㄴㄷㄹ한글!@#$%^&*()-=_+`~ㅁㄴㅁㄴ98ㅛasd9vh..뷁뷁뷁뷁뷁뷁뷁뷁 뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁뷁 뷁



* 특수문자가 저장이 안되네요. 쿼리상에 들어간 특수 문자 헥사코드 값입니다.( [#x1-#x8] | [#xB-#xC] | [#xE-#x1F] | [#x7F-#x84] | [#x86-#x9F] )

01 02 03 04 05 06 07 08 
0B 0C
0E 0F 10 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
7F 80 81 82 83 84 
86 87 88 89 90 9A 9B 9C 9D 9E 9F




* 아래 쿼리로 테스트하는게 더 정확하겠네요.

select regexp_replace(
  (select listagg(c, ',') within group (order by c) c from (
      select chr(level) c, level from Dual connect by Level <=8
      union
      select chr(level+10) c, level+10 from Dual connect by Level <=2
      union
      select chr(level+13) c, level+13 from Dual connect by Level <=18
      union
      select chr(level+133) c, level+133 from Dual connect by Level <=26
      union
      select chr(level+126) c, level+126 from Dual connect by Level <=6) a
  ), 
'['|| 
chr(1) || '-' || chr(8) 
||
chr(11) || '-' || chr(12) 
||
chr(14) || '-' || chr(31) 
||
chr(134) || '-' || chr(159) 
||
chr(127) || chr(128) || chr(129) || chr(130) || chr(131) || chr(132)
|| ']'
, '뷁') from dual a;


http://sqlfiddle.com/#!4/b62beb/243 여기서 테스트 해볼수 있어요!

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

mysql my session number id  (0) 2019.03.10
CentOS7 MariaDB 설치  (0) 2018.12.19
오라클 용량 산정 참고 쿼리  (0) 2018.12.14
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
오라클 임시 테이블  (0) 2018.07.17
반응형

오라클에서 "&"(Ampersand ) 를 insert하는 방법입니다.


SET DEFINE OFF

위 명령어 후에 하거나


SET DEFINE %

위 명령어로 DEFINE 문자를 다른 문자로 변경한 후


insert 하면 됩니다.!

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

CentOS7 MariaDB 설치  (0) 2018.12.19
오라클 용량 산정 참고 쿼리  (0) 2018.12.14
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
mysql join update  (2) 2018.10.11
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
오라클 임시 테이블  (0) 2018.07.17
오라클 전체 테이블의 데이터 건수 조회하는 쿼리 만들기.  (0) 2018.06.28
반응형


CREATE TABLE IF NOT EXISTS `달력` (
  `idx` int(3) unsigned AUTO_INCREMENT NOT NULL,
  `date` varchar(8)  NOT NULL,  
  PRIMARY KEY (`idx`)
) DEFAULT CHARSET=utf8;
INSERT INTO `달력` (`date`) VALUES
  ('20180101'),
  ('20180102'),
  ('20180103');
  
CREATE TABLE IF NOT EXISTS `생일` (
  `date` varchar(8)  NOT NULL,
  `name` varchar(10) NOT NULL,
  `idx` int(3) unsigned NULL,  
  PRIMARY KEY (`date`, `name`)
) DEFAULT CHARSET=utf8;
INSERT INTO `생일` (`date`, `name`) VALUES
  ('20180101', '흑고니'),
  ('20180102', '백고니');



SELECT * FROM `생일`;


datenameidx
20180101흑고니(null)
20180102백고니(null)

JOIN UPDATE 구문
UPDATE `생일` a INNER JOIN `달력` b ON a.date = b.date
SET a.idx = b.idx
WHERE a.name='백고니';



SELECT * FROM `생일`;


datenameidx
20180101흑고니(null)
20180102백고니

2


Test it ! http://sqlfiddle.com/#!9/a90dd0

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

오라클 용량 산정 참고 쿼리  (0) 2018.12.14
CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
오라클 임시 테이블  (0) 2018.07.17
오라클 전체 테이블의 데이터 건수 조회하는 쿼리 만들기.  (0) 2018.06.28
마리아 db 시작  (0) 2018.03.12
반응형

구문

 기존 데이터가 없으면

기존 데이터가 있으면 

 INSERT IGNORE

데이터 입력 

row affected 1

무시

row affected 0 

 REPLACE INTO

데이터 입력 

row affected 1

기존 데이터 삭제 후 입력
row affected 2

AUTO_INCREMENT 필드가 있다면 값이 바뀝니다.

 ON DUPLICATE UPDATE

데이터 입력 
row affected 1

기존 데이터 수정
row affected 2




<Create Script>

1
2
3
4
5
6
CREATE TABLE `CAR` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `COLOR` VARCHAR(45) NOT NULL,
  `NUMBER` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `NUMBER_UNIQUE` (`NUMBER` ASC));


** 주의 사항 : Primary Key 혹은 Unique Index 필드를 기준으로 중복 체크를 하게 됩니다.


<Data Insert> - 최초 데이터 입력

1
2
INSERT INTO CAR (COLOR, NUMBER) VALUES ('RED', '1234');
INSERT INTO CAR (COLOR, NUMBER) VALUES ('BLUE', '7777');


<Result> 

IDCOLORNUMBER
1RED1234
2BLUE7777

* 각각 1 row affected


<Data Insert Ignore> - INSERT IGNORE 중복 입력

1
2
INSERT IGNORE INTO CAR (COLOR, NUMBER) VALUES ('RED', '1234');
INSERT IGNORE INTO CAR (COLOR, NUMBER) VALUES ('BLUE', '7777');



<Result> 

IDCOLORNUMBER
1RED1234
2BLUE7777

* 각각 0 row affected

* 데이터 변화 없음.


<Data Replace> - Replace 중복 입력

1
2
REPLACE INTO CAR (COLOR, NUMBER) VALUES ('RED', '1234');
REPLACE INTO CAR (COLOR, NUMBER) VALUES ('BLUE', '7777');


<Result> 

IDCOLORNUMBER

5

RED1234

6

BLUE7777

* 각각 2 row affected - 기존에 데이터가 없으면 1 있으면 2 row affected

* ID값이 mysql ver 5.1.73 에서는 3, 4, mysql ver 5.6 에서는 5, 6이 된다. INSERT IGNORE 에서 Auto Increment 되는지 여부가 달라지는 것 같다.


<Data Insert On Duplicate Key Update> - Insert On Duplicate Key Update 중복 입력

1
2
INSERT INTO CAR (COLOR, NUMBER) VALUES ('RED', '1234') ON DUPLICATE KEY UPDATE COLOR='PINK';
INSERT INTO CAR (COLOR, NUMBER) VALUES ('BLUE', '7777') ON DUPLICATE KEY UPDATE COLOR='GREEN';


<Result> 

IDCOLORNUMBER

5

PINK1234

6

GREEN7777

* 각각 2 row affected - 기존에 데이터가 없으면 1 있으면 2 row affected

* ID 값은 변화가 없고 COLOR만 UPDATE 되었다.



이곳에서 테스트 및 확인이 가능합니다.

http://sqlfiddle.com/#!9/9fd7b7/1


반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

CentOS6 MariaDB 설치  (0) 2018.11.13
oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
오라클 임시 테이블  (0) 2018.07.17
오라클 전체 테이블의 데이터 건수 조회하는 쿼리 만들기.  (0) 2018.06.28
마리아 db 시작  (0) 2018.03.12
db별 jdbc url 및 jdbc driverClass 정리  (0) 2018.03.09
반응형
Create Global Temporary Table 임시테이블명 (
  임시컬ㄹ럼 VARCHAR(100)
)
ON COMMIT PRESERVE ROWS; -- 세션 동안 데이터 살아있음
-- ON COMMIT DELETE ROWS; -- 트랜잭션 동안 데이터 살아있음. COMMIT과 동시에 지워짐.



오라클 임시 테이블 만드는 방법입니다.


저는 임시 테이블이라 테이블 자체가 사라질 줄 알았는데,  데이터만 사라지더군요. @@

반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

oracle - replace xml invalid character 오라클 XML 사용제한 문자 치환  (0) 2018.10.18
oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
오라클 전체 테이블의 데이터 건수 조회하는 쿼리 만들기.  (0) 2018.06.28
마리아 db 시작  (0) 2018.03.12
db별 jdbc url 및 jdbc driverClass 정리  (0) 2018.03.09
mysql create database  (0) 2018.03.09
반응형

오라클에서 모든 테이블의 전체 데이터 건수를 조회하고 싶을 경우에

1번 쿼리를 이용하면 2번의 쿼리를 얻을 수 있습니다.


2번 쿼리를 조회하면 전체 데이터의 합을 구할 수 있습니다.


1번 : 쿼리 만들기 위한 쿼리

SELECT 'SELECT (' FROM DUAL UNION ALL
SELECT '(SELECT count(*) cnt  FROM '||TABLE_NAME||') +  '  FROM USER_TABLES UNION ALL
SELECT '0) TOTAL_COUNT FROM DUAL; ' FROM DUAL;




2번 : 결과

SELECT (
(SELECT count(*) cnt  FROM TB_USER) +  
(SELECT count(*) cnt  FROM TB_LOVE_LOVE) +  
(SELECT count(*) cnt  FROM TB_SKY) +  
(SELECT count(*) cnt  FROM TB_MY_MONEY) +  
0) TOTAL_COUNT FROM DUAL; 



반응형
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

oracle Ampersand "&" insert  (0) 2018.10.15
mysql join update  (2) 2018.10.11
oracle merge -> mysql replace or on duplicate key update  (0) 2018.07.25
오라클 임시 테이블  (0) 2018.07.17
마리아 db 시작  (0) 2018.03.12
db별 jdbc url 및 jdbc driverClass 정리  (0) 2018.03.09
mysql create database  (0) 2018.03.09
db2 character set 확인  (0) 2018.02.13

+ Recent posts

티스토리툴바