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
MySQL Articles
Page 139 of 355
MySQL 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 MoreExtract the month and year in the following format: "mm-yyyy" (month year) along with all columns in MySQL?
For month and year in a specific format, use DATE_FORMAT() along with STR_TO_DATE(). Let us first create a table −mysql> create table DemoTable1931 ( ShippingDate varchar(40) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1931 values('10-11-2017'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1931 values('31-01-2019'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1931 values('02-02-2018'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1931 values('10-06-2013'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select ...
Read MoreMySQL query to add days with interval of 45 days and display the output in a new column
For this, you can use date_add(). Let us first create a table −mysql> create table DemoTable1930 ( DueTime datetime ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1930 values('2017-10-21'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1930 values('2019-12-14'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1930 values('2018-11-26'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1930 values('2014-06-16'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1930;This will ...
Read MoreReset Primary Key in MySQL
To reset primary key, at first use TRUNCATE table, then use ALTER TABLE. Let us first create a table −mysql> create table DemoTable1929 ( UserId int NOT NULL AUTO_INCREMENT, PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1929 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1929 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1929 values(); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1929;This ...
Read More