Create a Temporary Table in a MySQL Procedure

Kumar Varma
Updated on 30-Jun-2020 12:56:38

3K+ Views

To create a temporary table in a MySQL procedure, following is the syntax −CREATE PROCEDURE yourProcedureName()    BEGIN       CREATE TEMPORARY TABLE yourTemporaryTableName SELECT yourValue;    ENDLet us implement the above syntax to create a temporary table and insert some records in the table. Following is the query to create a stored procedure and a temporary table in it −mysql> DELIMITER // mysql> CREATE PROCEDURE create_Temporary_Table()    -> BEGIN    ->    CREATE TEMPORARY TABLE tmpDemoTable SELECT 500;    -> END// Query OK, 0 rows affected (0.15 sec)Following is the query to insert record in the table −mysql> ... Read More

Get Everything Before the Last Occurrence of a Character in MySQL

Sharon Christine
Updated on 30-Jun-2020 12:55:53

678 Views

You can use below syntax. Following is the syntax −update yourTableName set yourColumnName=REVERSE(SUBSTRING(REVERSE(yourColumnName), INSTR(REVERSE(yourColumnName), '.')));Let us first create a table −mysql> create table DemoTable -> ( -> Words text -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Value1. Value2 .Value3.Value4.Value5'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+--------------------------------------+ | Words ... Read More

Find Minimum Values of Two or More Fields in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 12:54:44

856 Views

To find the minimum values of two or more fields, use LEAST() function from MySQL −select least(yourColumnName1, yourColumnName2, ...N) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Date1 date, -> Date2 date, -> Date3 date -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-03-31', '2019-01-01', '2019-03-05'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------------+------------+------------+ | Date1      | Date2      | Date3 ... Read More

Split a Column After Specific Characters in MySQL Query

karthikeya Boyini
Updated on 30-Jun-2020 12:53:12

964 Views

To split a column after specific characters, use the SUBSTRING_INDEX() method −select substring_index(yourColumnName, '-', -1) AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> StreetName text -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Paris Hill St.-CA-83745646') ; Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('502 South Armstrong Street-9948443'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------------------------------------+ | ... Read More

Copy Selected Columns from One Table to Another in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 12:52:17

392 Views

Let us first create a table −mysql> create table DemoTable1    -> (    -> Id int,   -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10, 'John'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1 values(11, 'Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values(12, 'Robert'); Query OK, 1 row affected (0.32 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------+--------+ | Id ... Read More

Select Sum of Column Values with Higher Value in Each Row with MySQL

Rama Giri
Updated on 30-Jun-2020 12:51:01

145 Views

Use the CASE statements and set conditions for the same. Let us first create a table −mysql> create table DemoTable    -> (    -> X int,    -> Y int    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20, 30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(40, 15); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(80, 85); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select ... Read More

MySQL Query to Display Structure of a Table

Kumar Varma
Updated on 30-Jun-2020 12:50:19

1K+ Views

To display structure of a table, following is the syntax −show create table yourTableName;Let us first create a table −mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeFirstName varchar(100),    -> EmployeeLastName varchar(100),    -> EmployeeAge int,    -> isMarried tinyint(1),    -> EmployeeAddress varchar(100),    -> EmployeeCountryName varchar(100)    -> ); Query OK, 0 rows affected (0.62 sec)Here is the query to display structure −mysql> show create table DemoTable;OutputThis will produce the following output −+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table        | Create Table             ... Read More

Fetch Records Where Timestamp is Before 15 Days in MySQL

Rama Giri
Updated on 30-Jun-2020 12:49:39

262 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> ArrivalDate datetime    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command. Let’s say the current date is 2019-07-03 −mysql> insert into DemoTable values('2019-07-03'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-06-20'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2019-06-15'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2018-06-11'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2018-06-01'); Query ... Read More

Multiple Column Sorting in MySQL

Kumar Varma
Updated on 30-Jun-2020 12:48:49

169 Views

Let us first create a table −mysql> create table DemoTable    -> (    -> Id int,    -> Value int    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 85885); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(101, 885995474); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 895943); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------+-----------+ | Id ... Read More

Mathematical Statistics Functions in Python

Samual Sam
Updated on 30-Jun-2020 12:48:26

596 Views

The statistics module of Python library consists of functions to calculate statistical formulae using numeric data types including Fraction and Decimal types.Following import statement is needed to use functions described in this article.>>> from statistics import *Following functions calculate the central tendency of sample data.mean() − This function calculates the arithmetic mean of data in the form of sequence or iterator.>>> from statistics import mean >>> numbers = [12, 34, 21, 7, 56] >>> mean(numbers) 26The sample data may contain Decimal object or Fraction object>>> from decimal import Decimal >>> numbers = [12, 34, 21, Decimal('7'), 56] >>> mean(numbers) Decimal('26') ... Read More

Advertisements