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
MySQLi Articles
Page 211 of 341
MySQL query to get the dates between range of records displaying student’s Date of Birth?
For fetching records between dates, use BETWEEN. Let us first create a table −mysql> create table DemoTable863(StudentDateOfBirth date); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable863 values('1998-01-10'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable863 values('2000-10-15'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable863 values('2003-04-20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable863 values('2005-12-31'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable863 values('1999-07-01'); Query OK, 1 row affected (0.27 sec)Display all records from the table using select ...
Read MoreRemove specific word in a comma separated string with MySQL
Let us first create a table −mysql> create table DemoTable836(FirstName SET('John', 'Chris', 'Adam')); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable836 values('John, Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable836 values('John, Chris, Adam'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable836 values('Chris, Adam'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable836 values('John, Adam'); Query OK, 1 row affected (0.37 sec)Display all records from the table using select statement −mysql> select *from DemoTable836;This will produce the following output −+-----------------+ | ...
Read MoreMySQL query to update only month in date?
To update only month in date, use MONTH(). Let us first create a table −mysql> create table DemoTable861(AdmissionDate date); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable861 values('2019-01-21'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable861 values('2018-01-01'); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable861 values('2016-01-02'); Query OK, 1 row affected (1.27 sec) mysql> insert into DemoTable861 values('2018-01-14'); Query OK, 1 row affected (0.47 sec)Display all records from the table using select statement −mysql> select *from DemoTable861;This will produce the following output −+---------------+ ...
Read MoreHow to order by the highest value from two columns in MySQL?
Let us first create a table −mysql> create table DemoTable834( Value1 int, Value2 int ); Query OK, 0 rows affected (1.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable834 values(10, 20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable834 values(40, 50); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable834 values(20, 24); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable834 values(30, 10); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable834;This will produce the following ...
Read MoreHow to skip rows in MySQL?
To skip rows, use LIMIT OFFSET. Let us first create a table −mysql> create table DemoTable860( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100) ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable860(Name) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable860(Name) values('Robert'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable860(Name) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable860(Name) values('Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable860(Name) values('Mike'); Query OK, 1 row ...
Read MoreHow to subtract seconds from all the date records in a MySQL column?
Let us first create a table −mysql> create table DemoTable833(ArrivalTime datetime); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable833 values('2019-01-10 12:40:50'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable833 values('2019-02-16 11:10:30'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable833 values('2018-03-17 10:00:10'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable833;This will produce the following output −+---------------------+ | ArrivalTime | +---------------------+ | 2019-01-10 12:40:50 | | ...
Read MoreHow to make numbers 'human readable' in MySQL?
To make numbers ‘human readable’ i.e. including separators, you need to use format(). Let us first create a table −mysql> create table DemoTable859(Number bigint); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable859 values(747464646473737); Query OK, 1 row affected (0.45 sec) mysql> insert into DemoTable859 values(3836365366464); Query OK, 1 row affected (0.71 sec) mysql> insert into DemoTable859 values(8376437647433); Query OK, 1 row affected (0.46 sec)Display all records from the table using select statement −mysql> select *from DemoTable859;This will produce the following output −+-----------------+ | Number | ...
Read MoreGetting the first part from the Postcode in MySQL
To get the first part, use SUBSTRING(). Let us first create a table −mysql> create table DemoTable858(PostCode varchar(100)); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable858 values('US90 456'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable858 values('UK1 EN789343'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable858 values('ENG78884 736454654'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable858;This will produce the following output −+--------------------+ | PostCode | +--------------------+ | ...
Read MoreInsert non-duplicates value in a MySQL table
To allow inserting only non-duplicate values, use the UNIQUE constraint. Let us first create a table −mysql> create table DemoTable832( FirstName varchar(100), LastName varchar(100), UNIQUE(FirstName, LastName) ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable832 values('John', 'Smith'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable832 values('Adam', 'Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable832 values('John', 'Doe'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable832 values('John', 'Smith'); ERROR 1062 (23000): Duplicate entry 'John-Smith' for key 'FirstName'Above, we ...
Read MoreRemove decimal from records and sum them using a MySQL query
Use aggregate function SUM() along with REPLACE(). Let us first create a table −mysql> create table DemoTable857(Price varchar(100)); Query OK, 0 rows affected (1.40 sec)Insert some records in the table using insert command −mysql> insert into DemoTable857 values('50.60'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable857 values('40.30.20'); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable857 values('10.20'); Query OK, 1 row affected (0.69 sec)Display all records from the table using select statement −mysql> select *from DemoTable857;This will produce the following output −+----------+ | Price | +----------+ | 50.60 | | 40.30.20 | | ...
Read More