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 134 of 355
Using LIKE clause twice in a MySQL query
Let us first create a table −mysql> create table DemoTable2009 ( Name varchar(20) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2009 values('John Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2009 values('Adam Smith'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable2009 values('John Smith'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2009 values('David Miller'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable2009;This will produce the following output ...
Read MoreMySQL query to insert multiple records quickly
To insert multiple records quickly, use a single INSERT and follow the below syntax −insert into yourTableName values(yourValue1, yourValue2, ...N), (yourValue1, yourValue2, ...N).....N;To understand the above syntax, let us create a table −mysql> create table DemoTable2007 ( Amount1 int, Amount2 int, Amount3 int ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2007 values(450, 600, 700), (1000, 200, 3000), (800, 900, 1200), (1300, 1500, 2000), (40000, 50000, 6700); Query OK, 5 rows affected (0.11 sec) Records: 5 Duplicates: 0 Warnings: 0Display all records from the table ...
Read MoreFetch a specific record from a column with string values (string, numbers and special characters) in MySQL
For this, you can use ORDER BY CAST(). Let us see an example −mysql> create table DemoTable2006 ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserCode varchar(20) ); Query OK, 0 rows affected (1.14 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2006(UserCode) values('John_12'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable2006(UserCode) values('John_34'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2006(UserCode) values('John_56'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_101'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2006(UserCode) values('Chris_103'); Query ...
Read MoreGet minimum value from a column (floating values) with corresponding duplicate ids in MySQL
To get minimum value from a column with corresponding duplicate ids, use GROUP BY and MIN() −select min(yourColumnName) from yourTableName group by yourColumnName;To understand the above syntax, let us create a table −mysql> create table DemoTable2005 ( Id int, Price float ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2005 values(1, 56.88); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2005 values(1, 120.56); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select * from DemoTable2005;This will produce ...
Read MoreUse IN() to get only a particular record in a MySQL stored procedure?
Let us first create a table −mysql> create table DemoTable2004 ( UserId varchar(20), UserName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2004 values('John_123', 'John'); Query OK, 1 row affected (0.93 sec) mysql> insert into DemoTable2004 values('23456_Carol', 'Carol'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable2004 values('111_Bob', 'Bob'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable2004;This will produce the following output −+-------------+----------+ | UserId | UserName | +-------------+----------+ | ...
Read MoreDisplay records by grouping dates in MySQL
To group dates in MySQL, use the GROUP BY clause −mysql> create table DemoTable2002 ( CustomerName varchar(20), CustomerShippingDate datetime ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2002 values('Chris', '2019-01-10'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable2002 values('David', '2018-12-31'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2002 values('David', '2019-12-16'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2002 values('Chris', '2018-12-01'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * ...
Read MoreGet the maximum exam date using a user-defined variable in SQL
To get the maximum exam date with a user-defined variable, the code is as follows −select date(max(yourColumnName )) into @yourVariableName from yourTableName;To understand the above syntax, let us first create a table −mysql> create table DemoTable2001 ( ExamDate date ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2001 values('2019-01-10'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2001 values('2018-12-31'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2001 values('2018-11-18'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable2001 values('2019-07-25'); Query OK, 1 ...
Read MoreMySQL isn't displaying right single quotation mark(') after insertion of records
To display right single quotation marks, you need to alter the table with COLLATE='utf8_unicode_ci'.Let us first create a table −mysql> create table DemoTable2000 ( Name varchar(20) ); Query OK, 0 rows affected (0.81 sec)Here is the query to use collate −mysql> ALTER TABLE DemoTable2000 COLLATE='utf8_unicode_ci'; Query OK, 0 rows affected (0.90 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable2000 values('Chris’s Brown'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable2000 values('David’s Miller'); Query OK, 1 row affected (0.67 sec) mysql> insert into DemoTable2000 values('Robert’s Downey'); Query ...
Read MoreConvert DATE timestamp to return only the month name in MySQL
To return only the month name, you can use DATE_FORMAT() −mysql> create table DemoTable1999 ( ArrivalDate timestamp ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1999 values('2019-01-01 12:34:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1999 values('2019-12-31 10:04:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1999 values('2018-10-11 04:04:30'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1999;This will produce the following output −+---------------------+ | ArrivalDate | +---------------------+ ...
Read MoreReturn only a single row from duplicate rows with MySQL
To return only a single row from duplicate rows, use DISTINCT keyword −mysql> create table DemoTable1998 ( Name varchar(20) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1998 values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1998 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1998 values('Robert'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable1998 values('David'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1998 values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1998 ...
Read More