Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 125 of 341
Insert records from multiple tables in MySQL
To insert records from multiple tables, use INSERT INTO SELECT statement. Here, we will insert records from 2 tables.Let us first create a table −mysql> create table DemoTable1943 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1943 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1943 values('Robert'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1943;This will produce the following output −+--------+ | Name | +--------+ | Chris ...
Read MoreHow to fetch all databases with name having upper case character after some word using MySQL?
For this, use regular expression. The syntax is as follows −select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^yourValue_+[A-Z]';Let us create some databases −mysql> create database bank_APP1; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP2; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP3; Query OK, 1 row affected (0.00 sec)Here is the query to get all databases having upper case character after some word −mysql> select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^bank_+[A-Z]';This will produce the following output −+--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def ...
Read MoreReturn the field with highest count in MySQL
To return the field with highest count, use ORDER BY COUNT(*). Let us first create a table −mysql> create table DemoTable1940 ( FirstName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row ...
Read MoreMySQL query to select everything to left of last space in a column with name records
For this, you can use LEFT(). Let us first create a table −mysql> create table DemoTable1939 ( FullName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1939 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Robert Downey, Jr.'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Sylvester Stallone'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Chris Hemsworth'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> ...
Read MoreDisplay all fields of a table in MySQL?
To display all fields, set the database with table_schema and specific table with table_name as in the below syntax −select column_name as anyAliasName from information_schema.columns where table_schema=database() and table_name=’yourTableName’\GLet us first create a table −mysql> create table DemoTable1938 ( StudentId int, StudentName varchar(20), StudentAge int, StudentCountryName varchar(20), StudentMobileNumber bigint ); Query OK, 0 rows affected (0.00 sec)Here is the query to display all fields of a table −mysql> select column_name as ALL_FIELDS from information_schema.columns where table_schema=database() and table_name='DemoTable1938'\GThis will produce the following output −*************************** 1. row *************************** ALL_FIELDS: StudentId ...
Read MoreMySQL ORDER BY ASC and display NULLs at the bottom?
For this, use CASE statement with ORDER BY. Let us first create a table −mysql> create table DemoTable1937 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1937 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(''); Query OK, 1 row affected (0.00 sec) ...
Read MorePerform multiple inserts with INSERT INTO SELECT and UNION in MySQL
To perform multiple inserts, the syntax is as follows −insert into yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, ..N) select yourValue1 as yourColumnName1, yourValue2 as yourColumnName2, yourValue3 as yourColumnName3, ......N union select yourValue1 as yourColumnName1, yourValue2 as yourColumnName2, yourValue3 as yourColumnName3, ......N . . NTo understand the above syntax, let us create a table −mysql> create table DemoTable1936 ( StudentId int, StudentName varchar(20), StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1936(StudentId, StudentName, StudentCountryName) select 1001 as StudentId, 'Chris' as StudentName, 'US' ...
Read MoreMySQL query to display records from a table filtered using LIKE with multiple words?
For this, use RLIKE and filter records as in the below syntax &Minus;select * from yourTableName where yourColumnName rlike 'yourValue1|yourValue2';Let us first create a table −mysql> create table DemoTable1935 ( Subject varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1935 values('MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('Python'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('MongoDB'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1935 values('SQL Server'); Query OK, 1 row affected (0.00 ...
Read MoreMySQL query to select average from distinct column of table?
For getting average, use AVG() and use it with DISTINCT to calculate from distinct records. Let us first create a table −mysql> create table DemoTable1934 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1934 values('Chris', 56); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('Chris', 56); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('David', 78); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1934 values('David', 78); Query OK, 1 row affected ...
Read MoreSelect items based on value first, then order on the basis of date for rest of the records in MySQL
Use ORDER BY to fix a record and then displayselect * from yourTableName order by yourColumnName1=yourValue desc, yourColumnName2;Let us first create a table −mysql> create table DemoTable1932 ( UserName varchar(20), ShippingDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1932 values('Chris', '2018-10-12'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1932 values('David', '2019-04-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1932 values('Mike', '2016-12-04'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1932 values('Carol', '2017-12-26'); Query OK, 1 ...
Read More