Vagrant – 共有設定・カーネル更新

2021-02-23 / tau / コメントする

概要

Vagrantの仮想環境での共有フォルダ－設定
環境はWindows10、Vagrant、VirtualBox、bento/Centos-7.7
この過程でカーネルのアップデートが必要

Vagrant初期起動

bento/Centos-7.7の前にCentos/7のBoxで共有設定をしたときにvagrant-vbguestパッケージをインストール済みだったので、GuestAdditionsの不整合は解決されている様子。

ただしkernelのバージョン不整合は解決されない。

C:\vagrant\centos7>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'bento/centos-7.7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...

.....

[default] GuestAdditions versions on your host (6.1.18) and guest (6.1.6) do not match.
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.jaist.ac.jp
* extras: ftp.jaist.ac.jp
* updates: ftp.jaist.ac.jp
Resolving Dependencies
--> Running transaction check
---> Package centos-release.x86_64 0:7-8.2003.0.el7.centos will be updated
---> Package centos-release.x86_64 0:7-9.2009.1.el7.centos will be an update
--> Finished Dependency Resolution

Dependencies Resolved

.....

Updated:
  centos-release.x86_64 0:7-9.2009.1.el7.centos

Complete!
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.jaist.ac.jp
* extras: ftp.jaist.ac.jp
* updates: ftp.jaist.ac.jp
No package kernel-devel-3.10.0-1127.el7.x86_64 available.
Error: Nothing to do
Unmounting Virtualbox Guest Additions ISO from: /mnt
umount: /mnt: not mounted
==> default: Checking for guest additions in VM...
The following SSH command responded with a non-zero exit status.
Vagrant assumes that this means the command failed!

umount /mnt

Stdout from the command:



Stderr from the command:

umount: /mnt: not mounted

C:\vagrant\centos7>vagrant up

Bringing machine 'default' up with 'virtualbox' provider...

==> default: Importing base box 'bento/centos-7.7'...

==> default: Matching MAC address for NAT networking...

==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...

.....

[default] GuestAdditions versions on your host (6.1.18) and guest (6.1.6) do not match.

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.jaist.ac.jp

* extras: ftp.jaist.ac.jp

* updates: ftp.jaist.ac.jp

Resolving Dependencies

--> Running transaction check

---> Package centos-release.x86_64 0:7-8.2003.0.el7.centos will be updated

---> Package centos-release.x86_64 0:7-9.2009.1.el7.centos will be an update

--> Finished Dependency Resolution

Dependencies Resolved

.....

Updated:

centos-release.x86_64 0:7-9.2009.1.el7.centos

Complete!

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.jaist.ac.jp

* extras: ftp.jaist.ac.jp

* updates: ftp.jaist.ac.jp

No package kernel-devel-3.10.0-1127.el7.x86_64 available.

Error: Nothing to do

Unmounting Virtualbox Guest Additions ISO from: /mnt

umount: /mnt: not mounted

==> default: Checking for guest additions in VM...

The following SSH command responded with a non-zero exit status.

Vagrant assumes that this means the command failed!

umount /mnt

Stdout from the command:

Stderr from the command:

umount: /mnt: not mounted

SSH接続・カーネルアップデート

VagrantからSSH接続して仮想環境にログイン
仮想環境下でyumによってカーネルをアップデート

[vagrant@localhost ~]$ sudo yum -y update kernel
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.jaist.ac.jp
* extras: ftp.jaist.ac.jp
* updates: ftp.jaist.ac.jp
Resolving Dependencies
--> Running transaction check
---> Package kernel.x86_64 0:3.10.0-1160.15.2.el7 will be installed
--> Processing Dependency: linux-firmware >= 20190429-72 for package: kernel-3.10.0-1160.15.2.el7.x86_64
--> Running transaction check
---> Package linux-firmware.noarch 0:20200421-80.git78c0348.el7_9 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package           Arch      Version                           Repository  Size
================================================================================
Installing:
kernel            x86_64    3.10.0-1160.15.2.el7              updates     50 M
Installing for dependencies:
linux-firmware    noarch    20200421-80.git78c0348.el7_9      updates     80 M

Transaction Summary
================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 131 M
Installed size: 458 M
Downloading packages:
No Presto metadata available for updates
(1/2): kernel-3.10.0-1160.15.2.el7.x86_64.rpm              |  50 MB   00:07
(2/2): linux-firmware-20200421-80.git78c0348.el7_9.noarch. |  80 MB   00:11
--------------------------------------------------------------------------------
Total                                               11 MB/s | 131 MB  00:11
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : linux-firmware-20200421-80.git78c0348.el7_9.noarch           1/2
  Installing : kernel-3.10.0-1160.15.2.el7.x86_64                           2/2

  Verifying  : linux-firmware-20200421-80.git78c0348.el7_9.noarch           1/2
  Verifying  : kernel-3.10.0-1160.15.2.el7.x86_64                           2/2

Installed:
  kernel.x86_64 0:3.10.0-1160.15.2.el7

Dependency Installed:
  linux-firmware.noarch 0:20200421-80.git78c0348.el7_9

Complete!

[vagrant@localhost ~]$ sudo yum -y update kernel

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.jaist.ac.jp

* extras: ftp.jaist.ac.jp

* updates: ftp.jaist.ac.jp

Resolving Dependencies

--> Running transaction check

---> Package kernel.x86_64 0:3.10.0-1160.15.2.el7 will be installed

--> Processing Dependency: linux-firmware >= 20190429-72 for package: kernel-3.10.0-1160.15.2.el7.x86_64

--> Running transaction check

---> Package linux-firmware.noarch 0:20200421-80.git78c0348.el7_9 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

================================================================================

Package Arch Version Repository Size

================================================================================

Installing:

kernel x86_64 3.10.0-1160.15.2.el7 updates 50 M

Installing for dependencies:

linux-firmware noarch 20200421-80.git78c0348.el7_9 updates 80 M

Transaction Summary

================================================================================

Install 1 Package (+1 Dependent package)

Total download size: 131 M

Installed size: 458 M

Downloading packages:

No Presto metadata available for updates

(1/2): kernel-3.10.0-1160.15.2.el7.x86_64.rpm | 50 MB 00:07

(2/2): linux-firmware-20200421-80.git78c0348.el7_9.noarch. | 80 MB 00:11

--------------------------------------------------------------------------------

Total 11 MB/s | 131 MB 00:11

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : linux-firmware-20200421-80.git78c0348.el7_9.noarch 1/2

Installing : kernel-3.10.0-1160.15.2.el7.x86_64 2/2

Verifying : linux-firmware-20200421-80.git78c0348.el7_9.noarch 1/2

Verifying : kernel-3.10.0-1160.15.2.el7.x86_64 2/2

Installed:

kernel.x86_64 0:3.10.0-1160.15.2.el7

Dependency Installed:

linux-firmware.noarch 0:20200421-80.git78c0348.el7_9

Complete!

Vagrant再起動

exitで仮想環境から抜けてホスト環境に戻る
ホスト環境でVagrantを再起動
ここでかなり時間をかけて環境設定が行われる

[vagrant@localhost ~]$ exit
logout
Connection to 127.0.0.1 closed.

C:\vagrant\centos7>vagrant reload
==> default: Attempting graceful shutdown of VM...
==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection reset. Retrying...
==> default: Machine booted and ready!
[default] No Virtualbox Guest Additions installation found.
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.jaist.ac.jp
* extras: ftp.jaist.ac.jp
* updates: ftp.jaist.ac.jp
Package 1:make-3.82-24.el7.x86_64 already installed and latest version
Package bzip2-1.0.6-13.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package binutils.x86_64 0:2.27-43.base.el7 will be updated
---> Package binutils.x86_64 0:2.27-44.base.el7 will be an update
---> Package elfutils-libelf-devel.x86_64 0:0.176-5.el7 will be installed
--> Processing Dependency: elfutils-libelf(x86-64) = 0.176-5.el7 for package: elfutils-libelf-devel-0.176-5.el7.x86_64
--> Processing Dependency: pkgconfig(zlib) for package: elfutils-libelf-devel-0.176-5.el7.x86_64
---> Package gcc.x86_64 0:4.8.5-44.el7 will be installed
--> Processing Dependency: libgomp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
--> Processing Dependency: cpp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
--> Processing Dependency: libgcc >= 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64
--> Processing Dependency: glibc-devel >= 2.2.90-12 for package: gcc-4.8.5-44.el7.x86_64
---> Package kernel-devel.x86_64 0:3.10.0-1160.15.2.el7 will be installed
---> Package perl.x86_64 4:5.16.3-295.el7 will be updated
---> Package perl.x86_64 4:5.16.3-299.el7_9 will be an update
--> Processing Dependency: perl-libs = 4:5.16.3-299.el7_9 for package: 4:perl-5.16.3-299.el7_9.x86_64
--> Running transaction check
---> Package cpp.x86_64 0:4.8.5-44.el7 will be installed
---> Package elfutils-libelf.x86_64 0:0.176-4.el7 will be updated
--> Processing Dependency: elfutils-libelf(x86-64) = 0.176-4.el7 for package: elfutils-libs-0.176-4.el7.x86_64
---> Package elfutils-libelf.x86_64 0:0.176-5.el7 will be an update
---> Package glibc-devel.x86_64 0:2.17-323.el7_9 will be installed
--> Processing Dependency: glibc-headers = 2.17-323.el7_9 for package: glibc-devel-2.17-323.el7_9.x86_64
--> Processing Dependency: glibc = 2.17-323.el7_9 for package: glibc-devel-2.17-323.el7_9.x86_64
--> Processing Dependency: glibc-headers for package: glibc-devel-2.17-323.el7_9.x86_64
---> Package libgcc.x86_64 0:4.8.5-39.el7 will be updated
---> Package libgcc.x86_64 0:4.8.5-44.el7 will be an update
---> Package libgomp.x86_64 0:4.8.5-39.el7 will be updated
---> Package libgomp.x86_64 0:4.8.5-44.el7 will be an update
---> Package perl-libs.x86_64 4:5.16.3-295.el7 will be updated
---> Package perl-libs.x86_64 4:5.16.3-299.el7_9 will be an update
---> Package zlib-devel.x86_64 0:1.2.7-19.el7_9 will be installed
--> Processing Dependency: zlib = 1.2.7-19.el7_9 for package: zlib-devel-1.2.7-19.el7_9.x86_64
--> Running transaction check
---> Package elfutils-libs.x86_64 0:0.176-4.el7 will be updated
---> Package elfutils-libs.x86_64 0:0.176-5.el7 will be an update
---> Package glibc.x86_64 0:2.17-307.el7.1 will be updated
--> Processing Dependency: glibc = 2.17-307.el7.1 for package: glibc-common-2.17-307.el7.1.x86_64
---> Package glibc.x86_64 0:2.17-323.el7_9 will be an update
---> Package glibc-headers.x86_64 0:2.17-323.el7_9 will be installed
--> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.17-323.el7_9.x86_64
--> Processing Dependency: kernel-headers for package: glibc-headers-2.17-323.el7_9.x86_64
---> Package zlib.x86_64 0:1.2.7-18.el7 will be updated
---> Package zlib.x86_64 0:1.2.7-19.el7_9 will be an update
--> Running transaction check
---> Package glibc-common.x86_64 0:2.17-307.el7.1 will be updated
---> Package glibc-common.x86_64 0:2.17-323.el7_9 will be an update
---> Package kernel-headers.x86_64 0:3.10.0-1160.15.2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package                  Arch      Version                    Repository  Size
================================================================================
Installing:
elfutils-libelf-devel    x86_64    0.176-5.el7                base        40 k
gcc                      x86_64    4.8.5-44.el7               base        16 M
kernel-devel             x86_64    3.10.0-1160.15.2.el7       updates     18 M
Updating:
binutils                 x86_64    2.27-44.base.el7           base       5.9 M
perl                     x86_64    4:5.16.3-299.el7_9         updates    8.0 M
Installing for dependencies:
cpp                      x86_64    4.8.5-44.el7               base       5.9 M
glibc-devel              x86_64    2.17-323.el7_9             updates    1.1 M
glibc-headers            x86_64    2.17-323.el7_9             updates    691 k
kernel-headers           x86_64    3.10.0-1160.15.2.el7       updates    9.0 M
zlib-devel               x86_64    1.2.7-19.el7_9             updates     50 k
Updating for dependencies:
elfutils-libelf          x86_64    0.176-5.el7                base       195 k
elfutils-libs            x86_64    0.176-5.el7                base       291 k
glibc                    x86_64    2.17-323.el7_9             updates    3.6 M
glibc-common             x86_64    2.17-323.el7_9             updates     12 M
libgcc                   x86_64    4.8.5-44.el7               base       103 k
libgomp                  x86_64    4.8.5-44.el7               base       159 k
perl-libs                x86_64    4:5.16.3-299.el7_9         updates    690 k
zlib                     x86_64    1.2.7-19.el7_9             updates     90 k

Transaction Summary
================================================================================
Install  3 Packages (+5 Dependent packages)
Upgrade  2 Packages (+8 Dependent packages)

Total download size: 81 M
Downloading packages:
No Presto metadata available for base
No Presto metadata available for updates
--------------------------------------------------------------------------------
Total                                               11 MB/s |  81 MB  00:07
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : libgcc-4.8.5-44.el7.x86_64                                  1/28
  Updating   : glibc-common-2.17-323.el7_9.x86_64                          2/28
  Updating   : glibc-2.17-323.el7_9.x86_64                                 3/28
  Updating   : zlib-1.2.7-19.el7_9.x86_64                                  4/28
  Updating   : elfutils-libelf-0.176-5.el7.x86_64                          5/28
  Updating   : 4:perl-libs-5.16.3-299.el7_9.x86_64                         6/28
  Updating   : 4:perl-5.16.3-299.el7_9.x86_64                              7/28
  Updating   : binutils-2.27-44.base.el7.x86_64                            8/28
  Installing : cpp-4.8.5-44.el7.x86_64                                     9/28
  Installing : zlib-devel-1.2.7-19.el7_9.x86_64                           10/28
  Updating   : libgomp-4.8.5-44.el7.x86_64                                11/28
  Installing : kernel-headers-3.10.0-1160.15.2.el7.x86_64                 12/28
  Installing : glibc-headers-2.17-323.el7_9.x86_64                        13/28
  Installing : glibc-devel-2.17-323.el7_9.x86_64                          14/28
  Installing : gcc-4.8.5-44.el7.x86_64                                    15/28
  Installing : elfutils-libelf-devel-0.176-5.el7.x86_64                   16/28
  Installing : kernel-devel-3.10.0-1160.15.2.el7.x86_64                   17/28
  Updating   : elfutils-libs-0.176-5.el7.x86_64                           18/28
  Cleanup    : elfutils-libs-0.176-4.el7.x86_64                           19/28
  Cleanup    : 4:perl-libs-5.16.3-295.el7.x86_64                          20/28
  Cleanup    : 4:perl-5.16.3-295.el7.x86_64                               21/28
  Cleanup    : elfutils-libelf-0.176-4.el7.x86_64                         22/28
  Cleanup    : binutils-2.27-43.base.el7.x86_64                           23/28
  Cleanup    : zlib-1.2.7-18.el7.x86_64                                   24/28
  Cleanup    : libgomp-4.8.5-39.el7.x86_64                                25/28
  Cleanup    : glibc-common-2.17-307.el7.1.x86_64                         26/28
  Cleanup    : glibc-2.17-307.el7.1.x86_64                                27/28
  Cleanup    : libgcc-4.8.5-39.el7.x86_64                                 28/28
  Verifying  : glibc-headers-2.17-323.el7_9.x86_64                         1/28
  Verifying  : zlib-1.2.7-19.el7_9.x86_64                                  2/28
  Verifying  : binutils-2.27-44.base.el7.x86_64                            3/28
  Verifying  : glibc-devel-2.17-323.el7_9.x86_64                           4/28
  Verifying  : kernel-headers-3.10.0-1160.15.2.el7.x86_64                  5/28
  Verifying  : glibc-2.17-323.el7_9.x86_64                                 6/28
  Verifying  : cpp-4.8.5-44.el7.x86_64                                     7/28
  Verifying  : kernel-devel-3.10.0-1160.15.2.el7.x86_64                    8/28
  Verifying  : glibc-common-2.17-323.el7_9.x86_64                          9/28
  Verifying  : zlib-devel-1.2.7-19.el7_9.x86_64                           10/28
  Verifying  : elfutils-libelf-0.176-5.el7.x86_64                         11/28
  Verifying  : elfutils-libelf-devel-0.176-5.el7.x86_64                   12/28
  Verifying  : gcc-4.8.5-44.el7.x86_64                                    13/28
  Verifying  : 4:perl-5.16.3-299.el7_9.x86_64                             14/28
  Verifying  : 4:perl-libs-5.16.3-299.el7_9.x86_64                        15/28
  Verifying  : libgcc-4.8.5-44.el7.x86_64                                 16/28
  Verifying  : libgomp-4.8.5-44.el7.x86_64                                17/28
  Verifying  : elfutils-libs-0.176-5.el7.x86_64                           18/28
  Verifying  : 4:perl-libs-5.16.3-295.el7.x86_64                          19/28
  Verifying  : zlib-1.2.7-18.el7.x86_64                                   20/28
  Verifying  : glibc-common-2.17-307.el7.1.x86_64                         21/28
  Verifying  : libgcc-4.8.5-39.el7.x86_64                                 22/28
  Verifying  : libgomp-4.8.5-39.el7.x86_64                                23/28
  Verifying  : elfutils-libelf-0.176-4.el7.x86_64                         24/28
  Verifying  : glibc-2.17-307.el7.1.x86_64                                25/28
  Verifying  : binutils-2.27-43.base.el7.x86_64                           26/28
  Verifying  : elfutils-libs-0.176-4.el7.x86_64                           27/28
  Verifying  : 4:perl-5.16.3-295.el7.x86_64                               28/28

Installed:
  elfutils-libelf-devel.x86_64 0:0.176-5.el7      gcc.x86_64 0:4.8.5-44.el7
  kernel-devel.x86_64 0:3.10.0-1160.15.2.el7

Dependency Installed:
  cpp.x86_64 0:4.8.5-44.el7
  glibc-devel.x86_64 0:2.17-323.el7_9
  glibc-headers.x86_64 0:2.17-323.el7_9
  kernel-headers.x86_64 0:3.10.0-1160.15.2.el7
  zlib-devel.x86_64 0:1.2.7-19.el7_9

Updated:
  binutils.x86_64 0:2.27-44.base.el7       perl.x86_64 4:5.16.3-299.el7_9

Dependency Updated:
  elfutils-libelf.x86_64 0:0.176-5.el7   elfutils-libs.x86_64 0:0.176-5.el7
  glibc.x86_64 0:2.17-323.el7_9          glibc-common.x86_64 0:2.17-323.el7_9
  libgcc.x86_64 0:4.8.5-44.el7           libgomp.x86_64 0:4.8.5-44.el7
  perl-libs.x86_64 4:5.16.3-299.el7_9    zlib.x86_64 0:1.2.7-19.el7_9

Complete!
Copy iso file C:\Program Files\Oracle\VirtualBox\VBoxGuestAdditions.iso into the box /tmp/VBoxGuestAdditions.iso
Mounting Virtualbox Guest Additions ISO to: /mnt
mount: /dev/loop0 is write-protected, mounting read-only
Installing Virtualbox Guest Additions 6.1.18 - guest version is unknown
Verifying archive integrity... All good.
Uncompressing VirtualBox 6.1.18 Guest Additions for Linux........
VirtualBox Guest Additions installer
Removing installed version 6.1.6 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
VirtualBox Guest Additions: Starting.
VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel
modules.  This may take a while.
VirtualBox Guest Additions: To build modules for other installed kernels, run
VirtualBox Guest Additions:   /sbin/rcvboxadd quicksetup <version>
VirtualBox Guest Additions: or
VirtualBox Guest Additions:   /sbin/rcvboxadd quicksetup all
VirtualBox Guest Additions: Building the modules for kernel
3.10.0-1160.15.2.el7.x86_64.
Redirecting to /bin/systemctl start vboxadd.service
Redirecting to /bin/systemctl start vboxadd-service.service
Unmounting Virtualbox Guest Additions ISO from: /mnt
==> default: Checking for guest additions in VM...
==> default: Mounting shared folders...
    default: /vagrant => C:/vagrant/centos7
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

[vagrant@localhost ~]$ exit

logout

Connection to 127.0.0.1 closed.

C:\vagrant\centos7>vagrant reload

==> default: Attempting graceful shutdown of VM...

==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...

==> default: Clearing any previously set forwarded ports...

==> default: Clearing any previously set network interfaces...

==> default: Preparing network interfaces based on configuration...

default: Adapter 1: nat

==> default: Forwarding ports...

default: 22 (guest) => 2222 (host) (adapter 1)

==> default: Booting VM...

==> default: Waiting for machine to boot. This may take a few minutes...

default: SSH address: 127.0.0.1:2222

default: SSH username: vagrant

default: SSH auth method: private key

default: Warning: Connection reset. Retrying...

==> default: Machine booted and ready!

[default] No Virtualbox Guest Additions installation found.

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* base: ftp.jaist.ac.jp

* extras: ftp.jaist.ac.jp

* updates: ftp.jaist.ac.jp

Package 1:make-3.82-24.el7.x86_64 already installed and latest version

Package bzip2-1.0.6-13.el7.x86_64 already installed and latest version

Resolving Dependencies

--> Running transaction check

---> Package binutils.x86_64 0:2.27-43.base.el7 will be updated

---> Package binutils.x86_64 0:2.27-44.base.el7 will be an update

---> Package elfutils-libelf-devel.x86_64 0:0.176-5.el7 will be installed

--> Processing Dependency: elfutils-libelf(x86-64) = 0.176-5.el7 for package: elfutils-libelf-devel-0.176-5.el7.x86_64

--> Processing Dependency: pkgconfig(zlib) for package: elfutils-libelf-devel-0.176-5.el7.x86_64

---> Package gcc.x86_64 0:4.8.5-44.el7 will be installed

--> Processing Dependency: libgomp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64

--> Processing Dependency: cpp = 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64

--> Processing Dependency: libgcc >= 4.8.5-44.el7 for package: gcc-4.8.5-44.el7.x86_64

--> Processing Dependency: glibc-devel >= 2.2.90-12 for package: gcc-4.8.5-44.el7.x86_64

---> Package kernel-devel.x86_64 0:3.10.0-1160.15.2.el7 will be installed

---> Package perl.x86_64 4:5.16.3-295.el7 will be updated

---> Package perl.x86_64 4:5.16.3-299.el7_9 will be an update

--> Processing Dependency: perl-libs = 4:5.16.3-299.el7_9 for package: 4:perl-5.16.3-299.el7_9.x86_64

--> Running transaction check

---> Package cpp.x86_64 0:4.8.5-44.el7 will be installed

---> Package elfutils-libelf.x86_64 0:0.176-4.el7 will be updated

--> Processing Dependency: elfutils-libelf(x86-64) = 0.176-4.el7 for package: elfutils-libs-0.176-4.el7.x86_64

---> Package elfutils-libelf.x86_64 0:0.176-5.el7 will be an update

---> Package glibc-devel.x86_64 0:2.17-323.el7_9 will be installed

--> Processing Dependency: glibc-headers = 2.17-323.el7_9 for package: glibc-devel-2.17-323.el7_9.x86_64

--> Processing Dependency: glibc = 2.17-323.el7_9 for package: glibc-devel-2.17-323.el7_9.x86_64

--> Processing Dependency: glibc-headers for package: glibc-devel-2.17-323.el7_9.x86_64

---> Package libgcc.x86_64 0:4.8.5-39.el7 will be updated

---> Package libgcc.x86_64 0:4.8.5-44.el7 will be an update

---> Package libgomp.x86_64 0:4.8.5-39.el7 will be updated

---> Package libgomp.x86_64 0:4.8.5-44.el7 will be an update

---> Package perl-libs.x86_64 4:5.16.3-295.el7 will be updated

---> Package perl-libs.x86_64 4:5.16.3-299.el7_9 will be an update

---> Package zlib-devel.x86_64 0:1.2.7-19.el7_9 will be installed

--> Processing Dependency: zlib = 1.2.7-19.el7_9 for package: zlib-devel-1.2.7-19.el7_9.x86_64

--> Running transaction check

---> Package elfutils-libs.x86_64 0:0.176-4.el7 will be updated

---> Package elfutils-libs.x86_64 0:0.176-5.el7 will be an update

---> Package glibc.x86_64 0:2.17-307.el7.1 will be updated

--> Processing Dependency: glibc = 2.17-307.el7.1 for package: glibc-common-2.17-307.el7.1.x86_64

---> Package glibc.x86_64 0:2.17-323.el7_9 will be an update

---> Package glibc-headers.x86_64 0:2.17-323.el7_9 will be installed

--> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.17-323.el7_9.x86_64

--> Processing Dependency: kernel-headers for package: glibc-headers-2.17-323.el7_9.x86_64

---> Package zlib.x86_64 0:1.2.7-18.el7 will be updated

---> Package zlib.x86_64 0:1.2.7-19.el7_9 will be an update

--> Running transaction check

---> Package glibc-common.x86_64 0:2.17-307.el7.1 will be updated

---> Package glibc-common.x86_64 0:2.17-323.el7_9 will be an update

---> Package kernel-headers.x86_64 0:3.10.0-1160.15.2.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

================================================================================

Package Arch Version Repository Size

================================================================================

Installing:

elfutils-libelf-devel x86_64 0.176-5.el7 base 40 k

gcc x86_64 4.8.5-44.el7 base 16 M

kernel-devel x86_64 3.10.0-1160.15.2.el7 updates 18 M

Updating:

binutils x86_64 2.27-44.base.el7 base 5.9 M

perl x86_64 4:5.16.3-299.el7_9 updates 8.0 M

Installing for dependencies:

cpp x86_64 4.8.5-44.el7 base 5.9 M

glibc-devel x86_64 2.17-323.el7_9 updates 1.1 M

glibc-headers x86_64 2.17-323.el7_9 updates 691 k

kernel-headers x86_64 3.10.0-1160.15.2.el7 updates 9.0 M

zlib-devel x86_64 1.2.7-19.el7_9 updates 50 k

Updating for dependencies:

elfutils-libelf x86_64 0.176-5.el7 base 195 k

elfutils-libs x86_64 0.176-5.el7 base 291 k

glibc x86_64 2.17-323.el7_9 updates 3.6 M

glibc-common x86_64 2.17-323.el7_9 updates 12 M

libgcc x86_64 4.8.5-44.el7 base 103 k

libgomp x86_64 4.8.5-44.el7 base 159 k

perl-libs x86_64 4:5.16.3-299.el7_9 updates 690 k

zlib x86_64 1.2.7-19.el7_9 updates 90 k

Transaction Summary

================================================================================

Install 3 Packages (+5 Dependent packages)

Upgrade 2 Packages (+8 Dependent packages)

Total download size: 81 M

Downloading packages:

No Presto metadata available for base

No Presto metadata available for updates

--------------------------------------------------------------------------------

Total 11 MB/s | 81 MB 00:07

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Updating : libgcc-4.8.5-44.el7.x86_64 1/28

Updating : glibc-common-2.17-323.el7_9.x86_64 2/28

Updating : glibc-2.17-323.el7_9.x86_64 3/28

Updating : zlib-1.2.7-19.el7_9.x86_64 4/28

Updating : elfutils-libelf-0.176-5.el7.x86_64 5/28

Updating : 4:perl-libs-5.16.3-299.el7_9.x86_64 6/28

Updating : 4:perl-5.16.3-299.el7_9.x86_64 7/28

Updating : binutils-2.27-44.base.el7.x86_64 8/28

Installing : cpp-4.8.5-44.el7.x86_64 9/28

Installing : zlib-devel-1.2.7-19.el7_9.x86_64 10/28

Updating : libgomp-4.8.5-44.el7.x86_64 11/28

Installing : kernel-headers-3.10.0-1160.15.2.el7.x86_64 12/28

Installing : glibc-headers-2.17-323.el7_9.x86_64 13/28

Installing : glibc-devel-2.17-323.el7_9.x86_64 14/28

Installing : gcc-4.8.5-44.el7.x86_64 15/28

Installing : elfutils-libelf-devel-0.176-5.el7.x86_64 16/28

Installing : kernel-devel-3.10.0-1160.15.2.el7.x86_64 17/28

Updating : elfutils-libs-0.176-5.el7.x86_64 18/28

Cleanup : elfutils-libs-0.176-4.el7.x86_64 19/28

Cleanup : 4:perl-libs-5.16.3-295.el7.x86_64 20/28

Cleanup : 4:perl-5.16.3-295.el7.x86_64 21/28

Cleanup : elfutils-libelf-0.176-4.el7.x86_64 22/28

Cleanup : binutils-2.27-43.base.el7.x86_64 23/28

Cleanup : zlib-1.2.7-18.el7.x86_64 24/28

Cleanup : libgomp-4.8.5-39.el7.x86_64 25/28

Cleanup : glibc-common-2.17-307.el7.1.x86_64 26/28

Cleanup : glibc-2.17-307.el7.1.x86_64 27/28

Cleanup : libgcc-4.8.5-39.el7.x86_64 28/28

Verifying : glibc-headers-2.17-323.el7_9.x86_64 1/28

Verifying : zlib-1.2.7-19.el7_9.x86_64 2/28

Verifying : binutils-2.27-44.base.el7.x86_64 3/28

Verifying : glibc-devel-2.17-323.el7_9.x86_64 4/28

Verifying : kernel-headers-3.10.0-1160.15.2.el7.x86_64 5/28

Verifying : glibc-2.17-323.el7_9.x86_64 6/28

Verifying : cpp-4.8.5-44.el7.x86_64 7/28

Verifying : kernel-devel-3.10.0-1160.15.2.el7.x86_64 8/28

Verifying : glibc-common-2.17-323.el7_9.x86_64 9/28

Verifying : zlib-devel-1.2.7-19.el7_9.x86_64 10/28

Verifying : elfutils-libelf-0.176-5.el7.x86_64 11/28

Verifying : elfutils-libelf-devel-0.176-5.el7.x86_64 12/28

Verifying : gcc-4.8.5-44.el7.x86_64 13/28

Verifying : 4:perl-5.16.3-299.el7_9.x86_64 14/28

Verifying : 4:perl-libs-5.16.3-299.el7_9.x86_64 15/28

Verifying : libgcc-4.8.5-44.el7.x86_64 16/28

Verifying : libgomp-4.8.5-44.el7.x86_64 17/28

Verifying : elfutils-libs-0.176-5.el7.x86_64 18/28

Verifying : 4:perl-libs-5.16.3-295.el7.x86_64 19/28

Verifying : zlib-1.2.7-18.el7.x86_64 20/28

Verifying : glibc-common-2.17-307.el7.1.x86_64 21/28

Verifying : libgcc-4.8.5-39.el7.x86_64 22/28

Verifying : libgomp-4.8.5-39.el7.x86_64 23/28

Verifying : elfutils-libelf-0.176-4.el7.x86_64 24/28

Verifying : glibc-2.17-307.el7.1.x86_64 25/28

Verifying : binutils-2.27-43.base.el7.x86_64 26/28

Verifying : elfutils-libs-0.176-4.el7.x86_64 27/28

Verifying : 4:perl-5.16.3-295.el7.x86_64 28/28

Installed:

elfutils-libelf-devel.x86_64 0:0.176-5.el7 gcc.x86_64 0:4.8.5-44.el7

kernel-devel.x86_64 0:3.10.0-1160.15.2.el7

Dependency Installed:

cpp.x86_64 0:4.8.5-44.el7

glibc-devel.x86_64 0:2.17-323.el7_9

glibc-headers.x86_64 0:2.17-323.el7_9

kernel-headers.x86_64 0:3.10.0-1160.15.2.el7

zlib-devel.x86_64 0:1.2.7-19.el7_9

Updated:

binutils.x86_64 0:2.27-44.base.el7 perl.x86_64 4:5.16.3-299.el7_9

Dependency Updated:

elfutils-libelf.x86_64 0:0.176-5.el7 elfutils-libs.x86_64 0:0.176-5.el7

glibc.x86_64 0:2.17-323.el7_9 glibc-common.x86_64 0:2.17-323.el7_9

libgcc.x86_64 0:4.8.5-44.el7 libgomp.x86_64 0:4.8.5-44.el7

perl-libs.x86_64 4:5.16.3-299.el7_9 zlib.x86_64 0:1.2.7-19.el7_9

Complete!

Copy iso file C:\Program Files\Oracle\VirtualBox\VBoxGuestAdditions.iso into the box /tmp/VBoxGuestAdditions.iso

Mounting Virtualbox Guest Additions ISO to: /mnt

mount: /dev/loop0 is write-protected, mounting read-only

Installing Virtualbox Guest Additions 6.1.18 - guest version is unknown

Verifying archive integrity... All good.

Uncompressing VirtualBox 6.1.18 Guest Additions for Linux........

VirtualBox Guest Additions installer

Removing installed version 6.1.6 of VirtualBox Guest Additions...

Copying additional installer modules ...

Installing additional modules ...

VirtualBox Guest Additions: Starting.

VirtualBox Guest Additions: Building the VirtualBox Guest Additions kernel

modules. This may take a while.

VirtualBox Guest Additions: To build modules for other installed kernels, run

VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup <version>

VirtualBox Guest Additions: or

VirtualBox Guest Additions: /sbin/rcvboxadd quicksetup all

VirtualBox Guest Additions: Building the modules for kernel

3.10.0-1160.15.2.el7.x86_64.

Redirecting to /bin/systemctl start vboxadd.service

Redirecting to /bin/systemctl start vboxadd-service.service

Unmounting Virtualbox Guest Additions ISO from: /mnt

==> default: Checking for guest additions in VM...

==> default: Mounting shared folders...

default: /vagrant => C:/vagrant/centos7

==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`

==> default: flag to force provisioning. Provisioners marked to run always will still run.

共有設定・再起動

共有設定の最終段階
ホスト側の共有フォルダ－を作成
vagrantfileに1行追加
vagrant reloadでVagrantを再起動
仮想環境下にもフォルダーが作成され、同期される

vagrantfile設定

config.vm.synced_folder "ホスト側パス", "ゲスト側パス"

第1引数はホスト側パスで、vagrantfileからの相対パスで指定
- ホスト側で共有ディレクトリーを作成しておく必要がある
第2引数はゲストパスで、絶対パスで指定
- ゲスト側の共有ディレクトリーはVagrantが作成してくれる

Vagrant.configure("2") do |config|
  .....
  config.vm.box = "bento/centos-7.7"
  .....
  config.vm.synced_folder "./share", "/home/vagrant/share"
  .....
end

Vagrant.configure("2") do |config|

.....

config.vm.box = "bento/centos-7.7"

.....

config.vm.synced_folder "./share", "/home/vagrant/share"

.....

end

Vagrant再起動

Vagrantを再起動すると、共有フォルダ－が作成される。

C:\vagrant\centos7>vagrant reload
==> default: Attempting graceful shutdown of VM...
==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection reset. Retrying...
    default: Warning: Connection aborted. Retrying...
==> default: Machine booted and ready!
[default] GuestAdditions 6.1.18 running --- OK.
==> default: Checking for guest additions in VM...
==> default: Mounting shared folders...
    default: /vagrant => C:/vagrant/centos7
    default: /home/vagrant/share => C:/vagrant/centos7/share
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.

C:\vagrant\centos7>vagrant reload

==> default: Attempting graceful shutdown of VM...

==> default: Checking if box 'bento/centos-7.7' version '202005.12.0' is up to date...

==> default: Clearing any previously set forwarded ports...

==> default: Clearing any previously set network interfaces...

==> default: Preparing network interfaces based on configuration...

default: Adapter 1: nat

==> default: Forwarding ports...

default: 22 (guest) => 2222 (host) (adapter 1)

==> default: Booting VM...

==> default: Waiting for machine to boot. This may take a few minutes...

default: SSH address: 127.0.0.1:2222

default: SSH username: vagrant

default: SSH auth method: private key

default: Warning: Connection reset. Retrying...

default: Warning: Connection aborted. Retrying...

==> default: Machine booted and ready!

[default] GuestAdditions 6.1.18 running --- OK.

==> default: Checking for guest additions in VM...

==> default: Mounting shared folders...

default: /vagrant => C:/vagrant/centos7

default: /home/vagrant/share => C:/vagrant/centos7/share

==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`

==> default: flag to force provisioning. Provisioners marked to run always will still run.

フォルダー配置

ホスト側：C\vagrant\centos7\share
ゲスト側：/home/vagrant/share

Vagrant – 環境構築(旧)

2021-02-23 / tau / コメントする

概要

以降はVagrant導入後最初にインストールしたBoxの導入記録。

Vagrant CloudのCentos/7を選び、共有フォルダ－設定を試みたところ、なかなかうまく共有できなかった。

このBoxのリリースがv2004.01と結構古かったので、その後bento/Centos-7.7に変更し、比較的スムーズにフォルダー共有ができた。

Boxの導入

Boxのインストール

仮想環境のBoxをインストール。今回はVagrant Cloudから提供されているCentosOS/7を選んだ。

Vagrantからのインストールの場合は以下のコマンド。
- >vagrant box add centos/7
他のサイトからの場合、名前を指定してインストールする場合は以下のコマンド。
- >vagrant box add name url/local_file

C:\>vagrant box add centos/7
==> box: Loading metadata for box 'centos/7'
    box: URL: https://vagrantcloud.com/centos/7
This box can work with multiple providers! The providers that it
can work with are listed below. Please review the list and choose
the provider you will be working with.

1) hyperv
2) libvirt
3) virtualbox
4) vmware_desktop

Enter your choice:

C:\>vagrant box add centos/7

==> box: Loading metadata for box 'centos/7'

box: URL: https://vagrantcloud.com/centos/7

This box can work with multiple providers! The providers that it

can work with are listed below. Please review the list and choose

the provider you will be working with.

1) hyperv

2) libvirt

3) virtualbox

4) vmware_desktop

Enter your choice:

今回はVirtualBoxの仮想環境なので3を選択。以下の処理に10分オーダーの時間がかかる。

Enter your choice: 3
==> box: Adding box 'centos/7' (v2004.01) for provider: virtualbox
    box: Downloading: https://vagrantcloud.com/centos/boxes/7/versions/2004.01/providers/virtualbox.box
Download redirected to host: cloud.centos.org
    box:
    box: Calculating and comparing box checksum...
==> box: Successfully added box 'centos/7' (v2004.01) for 'virtualbox'!

Enter your choice: 3

==> box: Adding box 'centos/7' (v2004.01) for provider: virtualbox

box: Downloading: https://vagrantcloud.com/centos/boxes/7/versions/2004.01/providers/virtualbox.box

Download redirected to host: cloud.centos.org

box:

box: Calculating and comparing box checksum...

==> box: Successfully added box 'centos/7' (v2004.01) for 'virtualbox'!

インストールされたBoxの存在確認。

>vagrant box list
centos/7 (virtualbox, 2004.01)

1 2	>vagrant box list centos/7 (virtualbox, 2004.01)

この時点でSSDの容量は141 /237GB free。

仮想環境のVagrant設定

ドライブ直下にVagrantディレクトリー、その下にCentOS7のディレクトリーを作成し、そこに移動。

>mkdir \vagrant
>cd \vagrant
>mkdir centos7
>cd centos7

>mkdir \vagrant

>cd \vagrant

>mkdir centos7

>cd centos7

移動後のディレクトリー内で初期設定。ここでこのディレクトリー内にvagrantfileが作成されて、仮想環境が利用可能になる。

C:\vagrant\centos7>vagrant init centos/7
A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

C:\vagrant\centos7>vagrant init centos/7

A `Vagrantfile` has been placed in this directory. You are now

ready to `vagrant up` your first virtual environment! Please read

the comments in the Vagrantfile as well as documentation on

`vagrantup.com` for more information on using Vagrant.

この時点でSSD 141/237GB freeとなり、6～7GBを使っている。

仮想環境の利用

仮想環境の起動

vagrant upコマンドで仮想環境を起動。

vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'centos/7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'centos/7' version '2004.01' is up to date...
==> default: Setting the name of the VM: centos7_default_1613521493035_2746
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default: Warning: Connection aborted. Retrying...
    default: Warning: Connection reset. Retrying...
    default:
    default: Vagrant insecure key detected. Vagrant will automatically replace
    default: this with a newly generated keypair for better security.
    default:
    default: Inserting generated public key within guest...
    default: Removing insecure key from the guest if it's present...
    default: Key inserted! Disconnecting and reconnecting using new SSH key...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: No guest additions were detected on the base box for this VM! Guest
    default: additions are required for forwarded ports, shared folders, host only
    default: networking, and more. If SSH fails on this machine, please install
    default: the guest additions and repackage the box to continue.
    default:
    default: This is not an error message; everything may continue to work properly,
    default: in which case you may ignore this message.
==> default: Rsyncing folder: /cygdrive/c/vagrant/centos7/ => /vagrant

vagrant up

Bringing machine 'default' up with 'virtualbox' provider...

==> default: Importing base box 'centos/7'...

==> default: Matching MAC address for NAT networking...

==> default: Checking if box 'centos/7' version '2004.01' is up to date...

==> default: Setting the name of the VM: centos7_default_1613521493035_2746

==> default: Clearing any previously set network interfaces...

==> default: Preparing network interfaces based on configuration...

default: Adapter 1: nat

==> default: Forwarding ports...

default: 22 (guest) => 2222 (host) (adapter 1)

==> default: Booting VM...

==> default: Waiting for machine to boot. This may take a few minutes...

default: SSH address: 127.0.0.1:2222

default: SSH username: vagrant

default: SSH auth method: private key

default: Warning: Connection aborted. Retrying...

default: Warning: Connection reset. Retrying...

default:

default: Vagrant insecure key detected. Vagrant will automatically replace

default: this with a newly generated keypair for better security.

default:

default: Inserting generated public key within guest...

default: Removing insecure key from the guest if it's present...

default: Key inserted! Disconnecting and reconnecting using new SSH key...

==> default: Machine booted and ready!

==> default: Checking for guest additions in VM...

default: No guest additions were detected on the base box for this VM! Guest

default: additions are required for forwarded ports, shared folders, host only

default: networking, and more. If SSH fails on this machine, please install

default: the guest additions and repackage the box to continue.

default:

default: This is not an error message; everything may continue to work properly,

default: in which case you may ignore this message.

==> default: Rsyncing folder: /cygdrive/c/vagrant/centos7/ => /vagrant

仮想環境へのログイン

現バージョンでは、Windowsでもvagrant sshで接続できた。

>vagrant ssh
[vagrant@localhost ~]$

1 2	>vagrant ssh [vagrant@localhost ~]$

バージョンを確認。

[vagrant@localhost ~]$ cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)

1 2	[vagrant@localhost ~]$ cat /etc/redhat-release CentOS Linux release 7.8.2003 (Core)

実行中の仮想環境の確認

仮想マシンを立ち上げたコンソールとは別のコンソールを立ち上げ、vagrant statusで確認。

C:\vagrant\centos7>vagrant status
Current machine states:

default                   running (virtualbox)

The VM is running. To stop this VM, you can run `vagrant halt` to
shut it down forcefully, or you can run `vagrant suspend` to simply
suspend the virtual machine. In either case, to restart it again,
simply run `vagrant up`.

C:\vagrant\centos7>vagrant status

Current machine states:

default running (virtualbox)

The VM is running. To stop this VM, you can run `vagrant halt` to

shut it down forcefully, or you can run `vagrant suspend` to simply

suspend the virtual machine. In either case, to restart it again,

simply run `vagrant up`.

仮想環境からのログアウト

CentOSのexitコマンドでログアウト。

[vagrant@localhost ~]$ exit
logout
Connection to 127.0.0.1 closed.

[vagrant@localhost ~]$ exit

logout

Connection to 127.0.0.1 closed.

仮想環境の停止

Windowsプロンプトでvagran haltコマンドを実行して停止。

>vagrant halt
==> default: Attempting graceful shutdown of VM...

1 2	>vagrant halt ==> default: Attempting graceful shutdown of VM...

仮想環境停止の確認

停止後のWindows環境でvagrant statusを実行すると仮想環境の停止を確認できる。

>vagrant status
Current machine states:

default                   poweroff (virtualbox)

The VM is powered off. To restart the VM, simply run `vagrant up`

>vagrant status

Current machine states:

default poweroff (virtualbox)

The VM is powered off. To restart the VM, simply run `vagrant up`

MySQL – 日付・時刻のフォーマット

2021-02-23 / tau / コメントする

概要

日付、時刻のフォーマットにはDATE_FORMAT()、TIME_FORMAT()を使う。

DATE_FORMAT(date, format);

TIME_FORMAT(time, format);

date、timeにはDATETIME型の値を入れてもよい。

書式の雛形

以下のデータで日付・時刻の書式を確認する。

mysql> SELECT * FROM test_datetime;
+---------------------+
| datetime            |
+---------------------+
| 2020-12-31 23:59:59 |
| 2021-01-01 00:01:02 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_datetime;

+---------------------+

| datetime |

+---------------------+

| 2020-12-31 23:59:59 |

| 2021-01-01 00:01:02 |

+---------------------+

2 rows in set (0.00 sec)

日付

日付4or2桁でゼロ埋め	`%Y %m %d`
日付ゼロ埋めなし	`%y %c %e`
月名(短縮／全部)	`%b / %M`
曜日名(番号／短縮／全部)	`%w / %a / %W`

※曜日番号は日曜～土曜が0～6

mysql> SELECT
    ->   DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',
    ->   DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',
    ->   DATE_FORMAT(datetime, '%b : %M') AS 月名,
    ->   DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日
    -> FROM test_datetime;
+----------------+--------------+----------------+--------------------+
| 日付0埋め      | 日付0なし    | 月名           | 曜日               |
+----------------+--------------+----------------+--------------------+
| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |
| 2021年01月01日 | 21年1月1日   | Jan : January  | 5 : Fri : Friday   |
+----------------+--------------+----------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(datetime, '%Y年%m月%d日') AS '日付0埋め',

-> DATE_FORMAT(datetime, '%y年%c月%e日') AS '日付0なし',

-> DATE_FORMAT(datetime, '%b : %M') AS 月名,

-> DATE_FORMAT(datetime, '%w : %a : %W') AS 曜日

-> FROM test_datetime;

+----------------+--------------+----------------+--------------------+

| 日付0埋め | 日付0なし | 月名 | 曜日 |

+----------------+--------------+----------------+--------------------+

| 2020年12月31日 | 20年12月31日 | Dec : December | 4 : Thu : Thursday |

| 2021年01月01日 | 21年1月1日 | Jan : January | 5 : Fri : Friday |

+----------------+--------------+----------------+--------------------+

2 rows in set (0.00 sec)

時刻

24時間制の時刻(ゼロ埋め)	%H
24時間制の時刻(ゼロ埋めなし)	%k
12時間制の時刻	%h
分(ゼロ埋め)	%i
秒(ゼロ埋め)	%s
AM/PM	`%p`

※12時間制の時刻は0時／12時の扱いに一貫性がない。

mysql> SELECT
    ->   TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',
    ->   TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',
    ->   TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',
    ->   TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'
    -> FROM test_datetime;
+--------------+--------------+-----------------+-----------------+
| 0埋め24H     | 0なし24H     | 0埋め12H        | 0なし12H        |
+--------------+--------------+-----------------+-----------------+
| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |
| 00時01分02秒 | 0時00分02秒  | 12時01分02秒 AM | 12時01分02秒 AM |
+--------------+--------------+-----------------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT

-> TIME_FORMAT(datetime, '%H時%i分%s秒') AS '0埋め24H',

-> TIME_FORMAT(datetime, '%k時%m分%s秒') AS '0なし24H',

-> TIME_FORMAT(datetime, '%h時%i分%S秒 %p') AS '0埋め12H',

-> TIME_FORMAT(datetime, '%l時%i分%S秒 %p') AS '0なし12H'

-> FROM test_datetime;

+--------------+--------------+-----------------+-----------------+

| 0埋め24H | 0なし24H | 0埋め12H | 0なし12H |

+--------------+--------------+-----------------+-----------------+

| 23時59分59秒 | 23時00分59秒 | 11時59分59秒 PM | 11時59分59秒 PM |

| 00時01分02秒 | 0時00分02秒 | 12時01分02秒 AM | 12時01分02秒 AM |

+--------------+--------------+-----------------+-----------------+

2 rows in set (0.00 sec)

Vagrant -vagrantfile

2021-02-23 / tau / コメントする

初期状態

VagrantとvirtualBoxのCentOS7をインストールして初期実行後のvagrantfileの内容。ファイルはCentOS7の環境構築で指定したディレクトリー下にある。

# -*- mode: ruby -*-
# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure
# configures the configuration version (we support older styles for
# backwards compatibility). Please don't change it unless you know what
# you're doing.
Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  # For a complete reference, please see the online documentation at
  # https://docs.vagrantup.com.

  # Every Vagrant development environment requires a box. You can search for
  # boxes at https://vagrantcloud.com/search.
  config.vm.box = "bento/centos-7.7"

  # Disable automatic box update checking. If you disable this, then
  # boxes will only be checked for updates when the user runs
  # `vagrant box outdated`. This is not recommended.
  # config.vm.box_check_update = false

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine. In the example below,
  # accessing "localhost:8080" will access port 80 on the guest machine.
  # NOTE: This will enable public access to the opened port
  # config.vm.network "forwarded_port", guest: 80, host: 8080

  # Create a forwarded port mapping which allows access to a specific port
  # within the machine from a port on the host machine and only allow access
  # via 127.0.0.1 to disable public access
  # config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

  # Create a private network, which allows host-only access to the machine
  # using a specific IP.
  # config.vm.network "private_network", ip: "192.168.33.10"

  # Create a public network, which generally matched to bridged network.
  # Bridged networks make the machine appear as another physical device on
  # your network.
  # config.vm.network "public_network"

  # Share an additional folder to the guest VM. The first argument is
  # the path on the host to the actual folder. The second argument is
  # the path on the guest to mount the folder. And the optional third
  # argument is a set of non-required options.
  # config.vm.synced_folder "../data", "/vagrant_data"
  config.vm.synced_folder "./share", "/home/vagrant/share" # フォルダー共有のため追加

  # Provider-specific configuration so you can fine-tune various
  # backing providers for Vagrant. These expose provider-specific options.
  # Example for VirtualBox:
  #
  # config.vm.provider "virtualbox" do |vb|
  #   # Display the VirtualBox GUI when booting the machine
  #   vb.gui = true
  #
  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"
  # end
  #
  # View the documentation for the provider you are using for more
  # information on available options.

  # Enable provisioning with a shell script. Additional provisioners such as
  # Ansible, Chef, Docker, Puppet and Salt are also available. Please see the
  # documentation for more information about their specific syntax and use.
  # config.vm.provision "shell", inline: <<-SHELL
  #   apt-get update
  #   apt-get install -y apache2
  # SHELL
end

# -*- mode: ruby -*-

# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure

# configures the configuration version (we support older styles for

# backwards compatibility). Please don't change it unless you know what

# you're doing.

Vagrant.configure("2") do |config|

# The most common configuration options are documented and commented below.

# For a complete reference, please see the online documentation at

# https://docs.vagrantup.com.

# Every Vagrant development environment requires a box. You can search for

# boxes at https://vagrantcloud.com/search.

config.vm.box = "bento/centos-7.7"

# Disable automatic box update checking. If you disable this, then

# boxes will only be checked for updates when the user runs

# `vagrant box outdated`. This is not recommended.

# config.vm.box_check_update = false

# Create a forwarded port mapping which allows access to a specific port

# within the machine from a port on the host machine. In the example below,

# accessing "localhost:8080" will access port 80 on the guest machine.

# NOTE: This will enable public access to the opened port

# config.vm.network "forwarded_port", guest: 80, host: 8080

# Create a forwarded port mapping which allows access to a specific port

# within the machine from a port on the host machine and only allow access

# via 127.0.0.1 to disable public access

# config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"

# Create a private network, which allows host-only access to the machine

# using a specific IP.

# config.vm.network "private_network", ip: "192.168.33.10"

# Create a public network, which generally matched to bridged network.

# Bridged networks make the machine appear as another physical device on

# your network.

# config.vm.network "public_network"

# Share an additional folder to the guest VM. The first argument is

# the path on the host to the actual folder. The second argument is

# the path on the guest to mount the folder. And the optional third

# argument is a set of non-required options.

# config.vm.synced_folder "../data", "/vagrant_data"

config.vm.synced_folder "./share", "/home/vagrant/share" # フォルダー共有のため追加

# Provider-specific configuration so you can fine-tune various

# backing providers for Vagrant. These expose provider-specific options.

# Example for VirtualBox:

# config.vm.provider "virtualbox" do |vb|

# # Display the VirtualBox GUI when booting the machine

# vb.gui = true

# # Customize the amount of memory on the VM:

# vb.memory = "1024"

# end

# View the documentation for the provider you are using for more

# information on available options.

# Enable provisioning with a shell script. Additional provisioners such as

# Ansible, Chef, Docker, Puppet and Salt are also available. Please see the

# documentation for more information about their specific syntax and use.

# config.vm.provision "shell", inline: <<-SHELL

# apt-get update

# apt-get install -y apache2

# SHELL

end

実質的な内容は以下のとおり。

Vagrant.configure("2") do |config|
  config.vm.box = "centos/7"
end

Vagrant.configure("2") do |config|

config.vm.box = "centos/7"

end

Vagrant – メモリーとディスクの容量設定

2021-02-23 / tau / コメントする

準備

vagrantfile中、virtualBox設定の部分をコメントアウトし有効にする。

  config.vm.provider "virtualbox" do |vb|
  #   # Display the VirtualBox GUI when booting the machine
  #   vb.gui = true
  #
  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"
  end

config.vm.provider "virtualbox" do |vb|

# # Display the VirtualBox GUI when booting the machine

# vb.gui = true

# # Customize the amount of memory on the VM:

# vb.memory = "1024"

end

メモリーの設定

メモリーの割り当て容量設定は、vagrantfileに1行書くか、コメントアウトされている以下の行のコメントを外して容量を書き換えるとよい。

  #   # Customize the amount of memory on the VM:
  #   vb.memory = "1024"

1 2	# # Customize the amount of memory on the VM: # vb.memory = "1024"

数値はMB単位のメモリー割当量で、上記は1024MB = 1GBを意味している。

ディスク容量の設定

仮想マシンのディスクに割り当てる容量を設定・変更する場合、プラグインをインストールした上でvagrantfileに1行追加する。

プラグインのインストール

ホスト側でvagrant-disksizeプラグインをインストールする。

>vagrant plugin install vagrant-disksize
Installing the 'vagrant-disksize' plugin. This can take a few minutes...
Fetching vagrant-disksize-0.1.3.gem
Installed the plugin 'vagrant-disksize (0.1.3)'!

>vagrant plugin install vagrant-disksize

Installing the 'vagrant-disksize' plugin. This can take a few minutes...

Fetching vagrant-disksize-0.1.3.gem

Installed the plugin 'vagrant-disksize (0.1.3)'!

vagrantfileへの追加

以下の1行をvagrantfileに追加する(***に割り当てる容量をGB単位で指定)。

config.disksize.size = "***GB"

    config.disksize.size = "50GB"

1	config.disksize.size = "50GB"

設定の反映

メモリー／ディスクの設定をvagrantfileに書いた後、vagrantを起動／再起動する。

メモリーとディスクの容量のみ設定したvagrantfileの有効部分は以下のようになる。

Vagrant.configure("2") do |config|
  config.vm.provider "virtualbox" do |vb|
    vb.memory = "2048"
    config.disksize.size = "50GB"
  end
end

Vagrant.configure("2") do |config|

config.vm.provider "virtualbox" do |vb|

vb.memory = "2048"

config.disksize.size = "50GB"

end

メモリー容量の確認

[vagrant@localhost ~]$ free m
              total        used        free      shared  buff/cache   available
Mem:        1882092       94880     1590960        8632      196252     1639440
Swap:       2097148           0     2097148

[vagrant@localhost ~]$ free m

total used free shared buff/cache available

Mem: 1882092 94880 1590960 8632 196252 1639440

Swap: 2097148 0 2097148

ディスク容量の確認

[vagrant@localhost ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        912M     0  912M   0% /dev
tmpfs           919M     0  919M   0% /dev/shm
tmpfs           919M  8.5M  911M   1% /run
tmpfs           919M     0  919M   0% /sys/fs/cgroup
/dev/sda1        40G  3.2G   37G   8% /
tmpfs           184M     0  184M   0% /run/user/1000

[vagrant@localhost ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 912M 0 912M 0% /dev

tmpfs 919M 0 919M 0% /dev/shm

tmpfs 919M 8.5M 911M 1% /run

tmpfs 919M 0 919M 0% /sys/fs/cgroup

/dev/sda1 40G 3.2G 37G 8% /

tmpfs 184M 0 184M 0% /run/user/1000

MySQL – DISTINCTによる重複の排除

2021-02-23 / tau / コメントする

概要

DISTINCTはフィールドの重複を除いた結果を返す。

確認に使用するデータ

以下のデータを使う。日時ごとのパスタのメニューの注文票で、パスタはソースと麺の種類の組み合わせ。

mysql> SELECT * FROM pasta_order;
+---------------------+----------+----------------+-------+
| order_time          | source   | pasta          | price |
+---------------------+----------+----------------+-------+
| 2021-02-01 10:30:00 | トマト   | スパゲッティー |   900 |
| 2021-02-01 11:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-01 12:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-01 12:30:00 | クリーム | リングイネ     |   850 |
| 2021-02-01 13:00:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 11:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-02 12:00:00 | クリーム | スパゲッティー |   850 |
| 2021-02-02 12:30:00 | トマト   | リングイネ     |   900 |
| 2021-02-02 13:00:00 | トマト   | スパゲッティー |   900 |
| 2021-02-03 10:30:00 | クリーム | スパゲッティー |   850 |
| 2021-02-03 11:00:00 | オイル   | スパゲッティー |   800 |
| 2021-02-03 11:30:00 | オイル   | フィットチーネ |   800 |
| 2021-02-03 12:00:00 | クリーム | リングイネ     |   850 |
| 2021-02-03 12:30:00 | トマト   | フィットチーネ |   900 |
| 2021-02-03 13:00:00 | オイル   | スパゲッティー |   800 |
+---------------------+----------+----------------+-------+
15 rows in set (0.00 sec)

mysql> SELECT * FROM pasta_order;

+---------------------+----------+----------------+-------+

+---------------------+----------+----------------+-------+

| 2021-02-01 10:30:00 | トマト | スパゲッティー | 900 |

| 2021-02-01 11:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-01 12:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-01 12:30:00 | クリーム | リングイネ | 850 |

| 2021-02-01 13:00:00 | トマト | リングイネ | 900 |

| 2021-02-02 11:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-02 12:00:00 | クリーム | スパゲッティー | 850 |

| 2021-02-02 12:30:00 | トマト | リングイネ | 900 |

| 2021-02-02 13:00:00 | トマト | スパゲッティー | 900 |

| 2021-02-03 10:30:00 | クリーム | スパゲッティー | 850 |

| 2021-02-03 11:00:00 | オイル | スパゲッティー | 800 |

| 2021-02-03 11:30:00 | オイル | フィットチーネ | 800 |

| 2021-02-03 12:00:00 | クリーム | リングイネ | 850 |

| 2021-02-03 12:30:00 | トマト | フィットチーネ | 900 |

| 2021-02-03 13:00:00 | オイル | スパゲッティー | 800 |

+---------------------+----------+----------------+-------+

15 rows in set (0.00 sec)

フィールドの重複を除いた抽出

まずソース、麺それぞれのフィールドから重複を除いた結果を抽出。ソースの種類、麺の種類は3種類のため、それぞれ3つのレコードが返される。

mysql> SELECT
    ->   DISTINCT source
    -> FROM pasta_order;
+----------+
| source   |
+----------+
| トマト   |
| オイル   |
| クリーム |
+----------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   DISTINCT pasta
    -> FROM pasta_order;
+----------------+
| pasta          |
+----------------+
| スパゲッティー |
| フィットチーネ |
| リングイネ     |
+----------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT source

-> FROM pasta_order;

+----------+

| source |

+----------+

| トマト |

| オイル |

| クリーム |

+----------+

3 rows in set (0.00 sec)

mysql> SELECT

-> DISTINCT pasta

-> FROM pasta_order;

+----------------+

| pasta |

+----------------+

| スパゲッティー |

| フィットチーネ |

| リングイネ |

+----------------+

3 rows in set (0.00 sec)

日付による集計

日付単位の表示

DATETIMEで登録されているフィールドを日付のみの表現に変更する。その結果、同じ日付で複数のソースと麺の組み合わせのレコードになる。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   source,
    ->   pasta
    -> FROM pasta_order;
+------------+----------+----------------+
| date       | source   | pasta          |
+------------+----------+----------------+
| 2021-02-01 | トマト   | スパゲッティー |
| 2021-02-01 | トマト   | フィットチーネ |
| 2021-02-01 | オイル   | スパゲッティー |
| 2021-02-01 | クリーム | リングイネ     |
| 2021-02-01 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-02 | クリーム | スパゲッティー |
| 2021-02-02 | トマト   | リングイネ     |
| 2021-02-02 | トマト   | スパゲッティー |
| 2021-02-03 | クリーム | スパゲッティー |
| 2021-02-03 | オイル   | スパゲッティー |
| 2021-02-03 | オイル   | フィットチーネ |
| 2021-02-03 | クリーム | リングイネ     |
| 2021-02-03 | トマト   | フィットチーネ |
| 2021-02-03 | オイル   | スパゲッティー |
+------------+----------+----------------+
15 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> source,

-> pasta

-> FROM pasta_order;

+------------+----------+----------------+

| date | source | pasta |

+------------+----------+----------------+

| 2021-02-01 | トマト | スパゲッティー |

| 2021-02-01 | トマト | フィットチーネ |

| 2021-02-01 | オイル | スパゲッティー |

| 2021-02-01 | クリーム | リングイネ |

| 2021-02-01 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-02 | クリーム | スパゲッティー |

| 2021-02-02 | トマト | リングイネ |

| 2021-02-02 | トマト | スパゲッティー |

| 2021-02-03 | クリーム | スパゲッティー |

| 2021-02-03 | オイル | スパゲッティー |

| 2021-02-03 | オイル | フィットチーネ |

| 2021-02-03 | クリーム | リングイネ |

| 2021-02-03 | トマト | フィットチーネ |

| 2021-02-03 | オイル | スパゲッティー |

+------------+----------+----------------+

15 rows in set (0.00 sec)

日付ごとのユニークデータの数

単一のフィールド

単一フィールド中のユニークなデータの数は以下のようにしてカウントする。

COUNT(DISTINCT col)

以下は、日付ごとにグルーピングし、各日付で重複を除いたソースの数をカウントしている。2/2はクリームとトマトの2種類だけというのが反映されている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT source)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+------------------------+
| date       | COUNT(DISTINCT source) |
+------------+------------------------+
| 2021-02-01 |                      3 |
| 2021-02-02 |                      2 |
| 2021-02-03 |                      3 |
+------------+------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT source)

-> FROM pasta_order

-> GROUP BY date;

+------------+------------------------+

| date | COUNT(DISTINCT source) |

+------------+------------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+------------------------+

3 rows in set (0.00 sec)

また、日付ごとの麺の種類の数もカウントしてみる。2/2がスパゲッティーとリングイネの2種類になっている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT pasta)
    -> FROM pasta_order
    -> GROUP BY date;
+------------+-----------------------+
| date       | COUNT(DISTINCT pasta) |
+------------+-----------------------+
| 2021-02-01 |                     3 |
| 2021-02-02 |                     2 |
| 2021-02-03 |                     3 |
+------------+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT pasta)

-> FROM pasta_order

-> GROUP BY date;

+------------+-----------------------+

| date | COUNT(DISTINCT pasta) |

+------------+-----------------------+

| 2021-02-01 | 3 |

| 2021-02-02 | 2 |

| 2021-02-03 | 3 |

+------------+-----------------------+

3 rows in set (0.00 sec)

複数フィールド

複数フィールドの組み合わせがユニークなものの数のカウントは以下のようにする。

COUNT(DISTINCT CONCAT(col1, col2, ...))

以下の例では、ソースとパスタの組み合わせがユニークなものの数を日付ごとに集計する。2/2はトマトスパゲッティーが2回、2/3はオイルスパゲッティーがあり、重複を除いてカウントしている。

mysql> SELECT
    ->   DATE_FORMAT(order_time, '%Y-%m-%d') AS date,
    ->   COUNT(DISTINCT CONCAT(source, pasta))
    -> FROM pasta_order
    -> GROUP BY date;
+------------+---------------------------------------+
| date       | COUNT(DISTINCT CONCAT(source, pasta)) |
+------------+---------------------------------------+
| 2021-02-01 |                                     5 |
| 2021-02-02 |                                     3 |
| 2021-02-03 |                                     5 |
+------------+---------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT

-> DATE_FORMAT(order_time, '%Y-%m-%d') AS date,

-> COUNT(DISTINCT CONCAT(source, pasta))

-> FROM pasta_order

-> GROUP BY date;

+------------+---------------------------------------+

| date | COUNT(DISTINCT CONCAT(source, pasta)) |

+------------+---------------------------------------+

| 2021-02-01 | 5 |

| 2021-02-02 | 3 |

| 2021-02-03 | 5 |

+------------+---------------------------------------+

3 rows in set (0.00 sec)

MySQL – グループごとの最大値のレコード

2021-02-23 / tau / コメントする

概要

インラインビューとグルーピングを使って、レコード中のある列の値が最大値／最小値となるレコードをグループごとに抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは各科目ごとの最高点をとった学生と教科を抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

手順

グループごとの最大値

科目によってグルーピングし、各科目の最高得点を得る。

SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

1	SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;
+---------+------------+
| subject | MAX(score) |
+---------+------------+
| 数学    |         85 |
| 物理    |         90 |
| 英語    |         85 |
| 文学    |         95 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> SELECT subject, MAX(score) FROM student_scores GROUP BY subject;

+---------+------------+

| subject | MAX(score) |

+---------+------------+

| 数学 | 85 |

| 物理 | 90 |

| 英語 | 85 |

| 文学 | 95 |

+---------+------------+

4 rows in set (0.00 sec)

インラインビューによる最高得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最高スコアの列が追加される。

SELECT name, subject, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
;

SELECT name, subject, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

;

mysql> SELECT name, subject, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> ;
+------+---------+---------+---------+
| name | subject | max_sub | max_scr |
+------+---------+---------+---------+
| 江藤 | 文学    | 数学    |      85 |
| 江藤 | 物理    | 数学    |      85 |
| 江藤 | 数学    | 数学    |      85 |
| 宇藤 | 文学    | 数学    |      85 |
| 宇藤 | 英語    | 数学    |      85 |
| 宇藤 | 物理    | 数学    |      85 |
| 伊藤 | 文学    | 数学    |      85 |
| 伊藤 | 数学    | 数学    |      85 |
| 安藤 | 英語    | 数学    |      85 |
| 安藤 | 物理    | 数学    |      85 |
| 安藤 | 数学    | 数学    |      85 |
| 江藤 | 文学    | 物理    |      90 |
| 江藤 | 物理    | 物理    |      90 |
| 江藤 | 数学    | 物理    |      90 |
| 宇藤 | 文学    | 物理    |      90 |
.....

mysql> SELECT name, subject, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> ;

+------+---------+---------+---------+

+------+---------+---------+---------+

| 江藤 | 文学 | 数学 | 85 |

| 江藤 | 物理 | 数学 | 85 |

| 江藤 | 数学 | 数学 | 85 |

| 宇藤 | 文学 | 数学 | 85 |

| 宇藤 | 英語 | 数学 | 85 |

| 宇藤 | 物理 | 数学 | 85 |

| 伊藤 | 文学 | 数学 | 85 |

| 伊藤 | 数学 | 数学 | 85 |

| 安藤 | 英語 | 数学 | 85 |

| 安藤 | 物理 | 数学 | 85 |

| 安藤 | 数学 | 数学 | 85 |

| 江藤 | 文学 | 物理 | 90 |

| 江藤 | 物理 | 物理 | 90 |

| 江藤 | 数学 | 物理 | 90 |

| 宇藤 | 文学 | 物理 | 90 |

.....

最高得点のレコードの追加

各学生・科目のレコードのうち、スコアが最高スコアに等しいレコードを抽出する。

SELECT name, subject, score, max_sub, max_scr
FROM
  student_scores,
  (
    SELECT subject AS max_sub, MAX(score) AS max_scr
    FROM student_scores
    GROUP BY subject
  ) AS max_table
WHERE subject = max_sub AND score = max_scr
;

SELECT name, subject, score, max_sub, max_scr

FROM

student_scores,

(

SELECT subject AS max_sub, MAX(score) AS max_scr

FROM student_scores

GROUP BY subject

) AS max_table

WHERE subject = max_sub AND score = max_scr

;

mysql> SELECT name, subject, score, max_sub, max_scr
    -> FROM
    ->   student_scores,
    ->   (
    ->     SELECT subject AS max_sub, MAX(score) AS max_scr
    ->     FROM student_scores
    ->     GROUP BY subject
    ->   ) AS max_table
    -> WHERE subject = max_sub AND score = max_scr
    -> ;
+------+---------+-------+---------+---------+
| name | subject | score | max_sub | max_scr |
+------+---------+-------+---------+---------+
| 安藤 | 物理    |    90 | 物理    |      90 |
| 宇藤 | 英語    |    85 | 英語    |      85 |
| 宇藤 | 文学    |    95 | 文学    |      95 |
| 江藤 | 数学    |    85 | 数学    |      85 |
+------+---------+-------+---------+---------+
4 rows in set (0.00 sec)

mysql> SELECT name, subject, score, max_sub, max_scr

-> FROM

-> student_scores,

-> (

-> SELECT subject AS max_sub, MAX(score) AS max_scr

-> FROM student_scores

-> GROUP BY subject

-> ) AS max_table

-> WHERE subject = max_sub AND score = max_scr

-> ;

+------+---------+-------+---------+---------+

+------+---------+-------+---------+---------+

| 安藤 | 物理 | 90 | 物理 | 90 |

| 宇藤 | 英語 | 85 | 英語 | 85 |

| 宇藤 | 文学 | 95 | 文学 | 95 |

| 江藤 | 数学 | 85 | 数学 | 85 |

+------+---------+-------+---------+---------+

4 rows in set (0.00 sec)

MySQL – シェアの計算

2021-02-23 / tau / コメントする

概要

インラインビューを使って、各レコードのある列の値のシェアを計算できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM components;
+-----------+----------+
| component | quantity |
+-----------+----------+
| 成分A     |       60 |
| 成分B     |       35 |
| 成分C     |       15 |
+-----------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM components;

+-----------+----------+

| component | quantity |

+-----------+----------+

| 成分A | 60 |

| 成分B | 35 |

| 成分C | 15 |

+-----------+----------+

3 rows in set (0.00 sec)

ここでは各成分の量quantityのシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

手順

合計値の計算

合計値はSUM()関数で計算。

SELECT SUM(quantity) FROM components;

1	SELECT SUM(quantity) FROM components;

mysql> SELECT SUM(quantity) FROM components;
+---------------+
| SUM(quantity) |
+---------------+
|           110 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(quantity) FROM components;

+---------------+

| SUM(quantity) |

+---------------+

| 110 |

+---------------+

1 row in set (0.00 sec)

インラインビューによる合計列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな合計値の列が追加される。

SELECT component, quantity, total
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT component, quantity, total

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT component, quantity, total
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+
| component | quantity | total |
+-----------+----------+-------+
| 成分A     |       60 |   110 |
| 成分B     |       35 |   110 |
| 成分C     |       15 |   110 |
+-----------+----------+-------+
3 rows in set (0.00 sec)

mysql> SELECT component, quantity, total

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+

| component | quantity | total |

+-----------+----------+-------+

| 成分A | 60 | 110 |

| 成分B | 35 | 110 |

| 成分C | 15 | 110 |

+-----------+----------+-------+

3 rows in set (0.00 sec)

各レコードのシェアの計算

各レコードのquantityを合計値で割ってシェアを計算する。

SELECT
  component,
  quantity,
  total,
  quantity / total AS rate
FROM
  components,
  (SELECT SUM(quantity) AS total FROM components) AS view
;

SELECT

component,

quantity,

total,

quantity / total AS rate

FROM

components,

(SELECT SUM(quantity) AS total FROM components) AS view

;

mysql> SELECT
    ->   component,
    ->   quantity,
    ->   total,
    ->   quantity / total AS rate
    -> FROM
    ->   components,
    ->   (SELECT SUM(quantity) AS total FROM components) AS view
    -> ;
+-----------+----------+-------+--------+
| component | quantity | total | rate   |
+-----------+----------+-------+--------+
| 成分A     |       60 |   110 | 0.5455 |
| 成分B     |       35 |   110 | 0.3182 |
| 成分C     |       15 |   110 | 0.1364 |
+-----------+----------+-------+--------+
3 rows in set (0.00 sec)

mysql> SELECT

-> component,

-> quantity,

-> total,

-> quantity / total AS rate

-> FROM

-> components,

-> (SELECT SUM(quantity) AS total FROM components) AS view

-> ;

+-----------+----------+-------+--------+

+-----------+----------+-------+--------+

| 成分A | 60 | 110 | 0.5455 |

| 成分B | 35 | 110 | 0.3182 |

| 成分C | 15 | 110 | 0.1364 |

+-----------+----------+-------+--------+

3 rows in set (0.00 sec)

MySQL – 最小値・最大値のレコードの抽出

2021-02-23 / tau / コメントする

概要

インラインビューを使って、レコード中のある列が最大値／最小値となるレコードを抽出できる。

以下、実行例に沿ってその方法を整理する。

実行例

以下のデータを使う。

mysql> SELECT * FROM student_scores;
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 安藤 | 数学    |    80 |
| 安藤 | 物理    |    90 |
| 安藤 | 英語    |    75 |
| 伊藤 | 数学    |    65 |
| 伊藤 | 文学    |    85 |
| 宇藤 | 物理    |    70 |
| 宇藤 | 英語    |    85 |
| 宇藤 | 文学    |    95 |
| 江藤 | 数学    |    85 |
| 江藤 | 物理    |    80 |
| 江藤 | 文学    |    65 |
+------+---------+-------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM student_scores;

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 安藤 | 数学 | 80 |

| 安藤 | 物理 | 90 |

| 安藤 | 英語 | 75 |

| 伊藤 | 数学 | 65 |

| 伊藤 | 文学 | 85 |

| 宇藤 | 物理 | 70 |

| 宇藤 | 英語 | 85 |

| 宇藤 | 文学 | 95 |

| 江藤 | 数学 | 85 |

| 江藤 | 物理 | 80 |

| 江藤 | 文学 | 65 |

+------+---------+-------+

11 rows in set (0.00 sec)

ここでは、全学生・全教科のうち、最低得点とその得点を取った学生と科目を表示させる。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

手順

最低得点の取得

まず、全体の最低得点は以下で得られる。

SELECT MIN(score) AS min_score FROM student_scores;

1	SELECT MIN(score) AS min_score FROM student_scores;

mysql> SELECT MIN(score) AS min_score FROM student_scores;
+-----------+
| min_score |
+-----------+
|        65 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT MIN(score) AS min_score FROM student_scores;

+-----------+

| min_score |

+-----------+

| 65 |

+-----------+

1 row in set (0.00 sec)

インラインビューによる最低得点列の追加

インラインビューに別名を定義し、元のテーブルと併記することで、全レコードに新たな最低スコアの列が追加される。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 安藤 | 数学    |    80 |        65 |
| 安藤 | 物理    |    90 |        65 |
| 安藤 | 英語    |    75 |        65 |
| 伊藤 | 数学    |    65 |        65 |
| 伊藤 | 文学    |    85 |        65 |
| 宇藤 | 物理    |    70 |        65 |
| 宇藤 | 英語    |    85 |        65 |
| 宇藤 | 文学    |    95 |        65 |
| 江藤 | 数学    |    85 |        65 |
| 江藤 | 物理    |    80 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
11 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 安藤 | 数学 | 80 | 65 |

| 安藤 | 物理 | 90 | 65 |

| 安藤 | 英語 | 75 | 65 |

| 伊藤 | 数学 | 65 | 65 |

| 伊藤 | 文学 | 85 | 65 |

| 宇藤 | 物理 | 70 | 65 |

| 宇藤 | 英語 | 85 | 65 |

| 宇藤 | 文学 | 95 | 65 |

| 江藤 | 数学 | 85 | 65 |

| 江藤 | 物理 | 80 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

11 rows in set (0.00 sec)

最低得点レコードの抽出

各学生のレコードのうち、スコアが最低スコアに等しいレコードを抽出する。

SELECT name, subject, score, min_score
FROM
  student_scores,
  (SELECT MIN(score) AS min_score FROM student_scores) as mint
WHERE score = min_score
;

SELECT name, subject, score, min_score

FROM

student_scores,

(SELECT MIN(score) AS min_score FROM student_scores) as mint

WHERE score = min_score

;

mysql> SELECT name, subject, score, min_score
    -> FROM
    ->   student_scores,
    ->   (SELECT MIN(score) AS min_score FROM student_scores) as mint
    -> WHERE score = min_score
    -> ;
+------+---------+-------+-----------+
| name | subject | score | min_score |
+------+---------+-------+-----------+
| 伊藤 | 数学    |    65 |        65 |
| 江藤 | 文学    |    65 |        65 |
+------+---------+-------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT name, subject, score, min_score

-> FROM

-> student_scores,

-> (SELECT MIN(score) AS min_score FROM student_scores) as mint

-> WHERE score = min_score

-> ;

+------+---------+-------+-----------+

+------+---------+-------+-----------+

| 伊藤 | 数学 | 65 | 65 |

| 江藤 | 文学 | 65 | 65 |

+------+---------+-------+-----------+

2 rows in set (0.00 sec)

MySQL – インラインビュー

2021-02-23 / tau / コメントする

確認用データ

以下のデータを使う。

mysql> SELECT * FROM subject_scores;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM subject_scores;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

基本形

FROM句の中にSELECTクエリーを書いてエイリアスをつけると、それがテーブルとして参照される。

SELECT *
FROM
  (SELECT subject, score FROM subject_scores) as view
;

SELECT *

FROM

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+
| subject     | score |
+-------------+-------+
| mathematics |    85 |
| physics     |    95 |
| chemistry   |    80 |
| geology     |    70 |
| literature  |  NULL |
| philosophy  |    80 |
+-------------+-------+
6 rows in set (0.00 sec)

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+

| subject | score |

+-------------+-------+

| mathematics | 85 |

| physics | 95 |

| chemistry | 80 |

| geology | 70 |

| literature | NULL |

| philosophy | 80 |

+-------------+-------+

6 rows in set (0.00 sec)

インラインビューにはエイリアスを付けないとエラーになる。

mysql> SELECT *
    -> FROM
    ->   (SELECT subject, score FROM subject_scores)
    -> ;
ERROR 1248 (42000): Every derived table must have its own alias

mysql> SELECT *

-> FROM

-> (SELECT subject, score FROM subject_scores)

-> ;

ERROR 1248 (42000): Every derived table must have its own alias

テーブルとインラインビューの併記

エイリアスによる修飾

テーブルとインラインビューを並べて使える。不明確さをなくすために、テーブル名またはエイリアスで列名を修飾する。

SELECT org.subject, org.score, view.subject, view.score
FROM
  subject_scores as org,
  (SELECT subject, score FROM subject_scores) as view
;

SELECT org.subject, org.score, view.subject, view.score

FROM

subject_scores as org,

(SELECT subject, score FROM subject_scores) as view

;

mysql> SELECT org.subject, org.score, view.subject, view.score
    -> FROM
    ->   subject_scores as org,
    ->   (SELECT subject, score FROM subject_scores) as view
    -> ;
+-------------+-------+-------------+-------+
| subject     | score | subject     | score |
+-------------+-------+-------------+-------+
| philosophy  |    80 | mathematics |    85 |
| literature  |  NULL | mathematics |    85 |
| geology     |    70 | mathematics |    85 |
| chemistry   |    80 | mathematics |    85 |
| physics     |    95 | mathematics |    85 |
| mathematics |    85 | mathematics |    85 |
| philosophy  |    80 | physics     |    95 |
| literature  |  NULL | physics     |    95 |
| geology     |    70 | physics     |    95 |
| chemistry   |    80 | physics     |    95 |
| physics     |    95 | physics     |    95 |
| mathematics |    85 | physics     |    95 |
| philosophy  |    80 | chemistry   |    80 |
| literature  |  NULL | chemistry   |    80 |
| geology     |    70 | chemistry   |    80 |
| chemistry   |    80 | chemistry   |    80 |
| physics     |    95 | chemistry   |    80 |
| mathematics |    85 | chemistry   |    80 |
| philosophy  |    80 | geology     |    70 |
| literature  |  NULL | geology     |    70 |
| geology     |    70 | geology     |    70 |
| chemistry   |    80 | geology     |    70 |
| physics     |    95 | geology     |    70 |
| mathematics |    85 | geology     |    70 |
| philosophy  |    80 | literature  |  NULL |
| literature  |  NULL | literature  |  NULL |
| geology     |    70 | literature  |  NULL |
| chemistry   |    80 | literature  |  NULL |
| physics     |    95 | literature  |  NULL |
| mathematics |    85 | literature  |  NULL |
| philosophy  |    80 | philosophy  |    80 |
| literature  |  NULL | philosophy  |    80 |
| geology     |    70 | philosophy  |    80 |
| chemistry   |    80 | philosophy  |    80 |
| physics     |    95 | philosophy  |    80 |
| mathematics |    85 | philosophy  |    80 |
+-------------+-------+-------------+-------+
36 rows in set (0.00 sec)

mysql> SELECT org.subject, org.score, view.subject, view.score

-> FROM

-> subject_scores as org,

-> (SELECT subject, score FROM subject_scores) as view

-> ;

+-------------+-------+-------------+-------+

+-------------+-------+-------------+-------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

| geology | 70 | physics | 95 |

| chemistry | 80 | physics | 95 |

| physics | 95 | physics | 95 |

| mathematics | 85 | physics | 95 |

| philosophy | 80 | chemistry | 80 |

| geology | 70 | chemistry | 80 |

| chemistry | 80 | chemistry | 80 |

| physics | 95 | chemistry | 80 |

| mathematics | 85 | chemistry | 80 |

| philosophy | 80 | geology | 70 |

| geology | 70 | geology | 70 |

| chemistry | 80 | geology | 70 |

| physics | 95 | geology | 70 |

| mathematics | 85 | geology | 70 |

| philosophy | 80 | philosophy | 80 |

| geology | 70 | philosophy | 80 |

| chemistry | 80 | philosophy | 80 |

| physics | 95 | philosophy | 80 |

| mathematics | 85 | philosophy | 80 |

+-------------+-------+-------------+-------+

36 rows in set (0.00 sec)

列名のエイリアス

インラインビューの列名にエイリアスを定義してもよい。

SELECT subject, score, view_subject, view_score
FROM
  subject_scores,
  (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
;

SELECT subject, score, view_subject, view_score

FROM

subject_scores,

(SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

;

mysql> SELECT subject, score, view_subject, view_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view
    -> ;
+-------------+-------+--------------+------------+
| subject     | score | view_subject | view_score |
+-------------+-------+--------------+------------+
| philosophy  |    80 | mathematics  |         85 |
| literature  |  NULL | mathematics  |         85 |
| geology     |    70 | mathematics  |         85 |
| chemistry   |    80 | mathematics  |         85 |
| physics     |    95 | mathematics  |         85 |
| mathematics |    85 | mathematics  |         85 |
| philosophy  |    80 | physics      |         95 |
| literature  |  NULL | physics      |         95 |
.....

mysql> SELECT subject, score, view_subject, view_score

-> FROM

-> subject_scores,

-> (SELECT subject AS view_subject, score AS view_score FROM subject_scores) as view

-> ;

+-------------+-------+--------------+------------+

+-------------+-------+--------------+------------+

| philosophy | 80 | mathematics | 85 |

| geology | 70 | mathematics | 85 |

| chemistry | 80 | mathematics | 85 |

| physics | 95 | mathematics | 85 |

| mathematics | 85 | mathematics | 85 |

| philosophy | 80 | physics | 95 |

.....

集約関数の利用

インラインビューで集約関数を使うと威力を発揮。以下の例では、全レコード中の最高得点と最低得点を各レコードに付加している。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> ;
+-------------+-------+-----------+-----------+
| subject     | score | max_score | min_score |
+-------------+-------+-----------+-----------+
| mathematics |    85 |        95 |        70 |
| physics     |    95 |        95 |        70 |
| chemistry   |    80 |        95 |        70 |
| geology     |    70 |        95 |        70 |
| literature  |  NULL |        95 |        70 |
| philosophy  |    80 |        95 |        70 |
+-------------+-------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> ;

+-------------+-------+-----------+-----------+

+-------------+-------+-----------+-----------+

| mathematics | 85 | 95 | 70 |

| physics | 95 | 95 | 70 |

| chemistry | 80 | 95 | 70 |

| geology | 70 | 95 | 70 |

| literature | NULL | 95 | 70 |

| philosophy | 80 | 95 | 70 |

+-------------+-------+-----------+-----------+

6 rows in set (0.00 sec)

このようにすると、最高得点や最低得点のレコードを抽出することができる。

SELECT subject, score, max_score, min_score
FROM
  subject_scores,
  (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
  (SELECT MIN(score) as min_score FROM subject_scores) as min_table
WHERE
  score = max_score
;

SELECT subject, score, max_score, min_score

FROM

subject_scores,

(SELECT MAX(score) as max_score FROM subject_scores) as max_table,

(SELECT MIN(score) as min_score FROM subject_scores) as min_table

WHERE

score = max_score

;

mysql> SELECT subject, score, max_score, min_score
    -> FROM
    ->   subject_scores,
    ->   (SELECT MAX(score) as max_score FROM subject_scores) as max_table,
    ->   (SELECT MIN(score) as min_score FROM subject_scores) as min_table
    -> WHERE
    ->   score = max_score
    -> ;
+---------+-------+-----------+-----------+
| subject | score | max_score | min_score |
+---------+-------+-----------+-----------+
| physics |    95 |        95 |        70 |
+---------+-------+-----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT subject, score, max_score, min_score

-> FROM

-> subject_scores,

-> (SELECT MAX(score) as max_score FROM subject_scores) as max_table,

-> (SELECT MIN(score) as min_score FROM subject_scores) as min_table

-> WHERE

-> score = max_score

-> ;

+---------+-------+-----------+-----------+

+---------+-------+-----------+-----------+

| physics | 95 | 95 | 70 |

+---------+-------+-----------+-----------+

1 row in set (0.00 sec)