How to get MySQL Version

Using this tutorial, we can find the MySQL Version with different methods. We can find MySQL Server using MySQL command or Linux based commands. Following are easy methods

1. By running following MySQL command.

SELECT version();
or
SELECT VERSION();
or
select @@version;

Output:

mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.7.20-0ubuntu0.16.04.1 | +-------------------------+

mysql> select VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.7.20-0ubuntu0.16.04.1 | +-------------------------+

How to check MySQL Server Status on Linux

This article explains to get MySQL Server status information on Linux using mysqladmin command or the mysql init script. The information return uptime of the MySQL Server,Version, Open Tables, Socket type,..

Method 1:Using Status option

mysqladmin -u root -p status

Output

Enter password: Uptime: 98350 Threads: 1 Questions: 18 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.000

Method 2: Using Version option

The maximum number of configured devices has already been reached

Following error recieved when tried to create raw device in the server.

disk init
name=’data43′,
physname=’/data/devices/sb_device_data43′,
size=’40000M’

Msg 5162, Level16, State1: Server 'SKN_UAT1',Line1: The maximum number 42 of configured devices has already been reached. Please reconfigure 'number of devices' to a larger value and retry disk initialization.

Solution:The maximum number of configured devices has already been reached

Increase the The maximum number of configured devices to larger value than 42 by using sp_configure procedure.

How to get PostgreSQL Version

We have following methods to know the version of PosgreSQL running on the server.

Method1: Using psql -V in shell(Linux) or command line(Windows)

psql -V

psql (PostgreSQL) 9.6.3

Method 2: Goto the installation path and open PG_VERSION file. Which contains PostgreSQL version information.)

On Linux
postgres@ubuntu:~/9.5/main$ more PG_VERSION
9.5

R(R Studio) Connection with PostgreSQL

R(R Studio) Connection with PostgreSQL: We have to use R package: RPostgreSQL

1. Install RPostgreSQL package

install.packages(“RPostgreSQL”)

Installing package into ‘C:/Users/pinnapav/Documents/R/win-library/3.3’ (as ‘lib’ is unspecified) trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/RPostgreSQL_0.6-2.zip' Content type 'application/zip' length 433117 bytes (422 KB) downloaded 422 KB package ‘RPostgreSQL’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\pinnapav\AppData\Local\Temp\RtmpWcxGA7\downloaded_packages

2. Load RPostgreSQL library.

library(RPostgreSQL)

Load PostgreSQL sample database dvdrental

PostgreSQL sample database that can used for learning and practice PostgreSQL. We will use the DVD rental database for demonstrating the features of PostgreSQL. The DVD rental database has many objects including: 15 tables,one trigger,7 views, 8 functions, one domain and 13 sequences.

Follow below steps to load(install) PostgreSQL sample database dvdrental.

1. Download the PostgreSQL database from http://k2schools.com/wp-content/uploads/2017/12/dvdrental.zip

Set PostgreSQL environment variables

If we dont set PostgreSQL environmental variable, We cannot access PostgreSQL through the command line in Windows.But, we can access PostgreSQL using pgAdmin or SQL Shell(psql). To access the PostgreSQL through command line in window, follow the below steps.

Set PostgreSQL environment variables

Set PostgreSQL environment variables

Select numeric columns from a Data frame in R

We can get the list of columns in a data frame using sapply function.

In this article, we are going extract the numeric columns of diamonds dataset.

1. Diamonds data frame contains numeric and noon-numeric columns.

head(diamonds)

Load data from a text file into PostgreSQL Database

We can load data from text(flat) file,CSV(comma separated values), excel or from other database engines(Oracle,MySQL,..). Follow steps covers Load data from a text file into PostgreSQL Database.

Syntax:

COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ]

Where option can be:

Export PostgreSQL query result to file

We can take a backup file of database using pg_dump command, similarly can we take backup of a select query result.

Syntax to Export PostgreSQL query result to file:

copy(select * from table_name) to 'path';

Examples:

Powered by k2schools