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 120 of 341
Get 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 MoreAdd 11 days to current date in MySQL
Let us first create a table −mysql> create table DemoTable1994 ( ArrivalDate date ); Query OK, 0 rows affected (5.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1994 values('2019-12-18'); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable1994 values('2019-12-19'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable1994 values('2019-12-20'); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable1994 values('2019-12-25'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable1994 values('2018-12-20'); Query OK, 1 row affected (1.42 sec)Display all records from the table using select statement −mysql> select ...
Read MoreSet custom messages for enum values in MySQL
Use the if else to set custom messages for enum. Let us first create a table −mysql> create table DemoTable1992 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), isActive ENUM('Y', 'N') ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1992(ClientName, isActive) values('Chris', 'N'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1992(ClientName, isActive) values('Bob', 'N'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1992(ClientName, isActive) values('David', 'Y'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1992(ClientName, isActive) values('Carol', ...
Read MoreSelect from table where value does not exist with MySQL?
For this, you can use NOT IN() −mysql> create table DemoTable1991 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1991(StudentName) values('Chris'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1991(StudentName) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1991(StudentName) values('David'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1991(StudentName) values('Sam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1991(StudentName) values('Mike'); Query OK, 1 row affected (0.11 sec)Display all ...
Read MoreMySQL - Select dates that are one week ahead from today?
To get dates that are one week ahead from today, use DATEDIFF. Let us first get the current date −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-12-20 | +------------+ 1 row in set (0.00 sec)We will first create a table −mysql> create table DemoTable1990 ( ShippingDate date ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1990 values('2019-12-13'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1990 values('2019-12-21'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1990 values('2019-12-20'); Query OK, 1 ...
Read MoreConvert VARCHAR data to MySQL date format?
To convert VARCHAR data to date format, you can use STR_TO_DATE() −mysql> create table DemoTable1989 ( DueDate varchar(20) ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1989 values('31/01/2015'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable1989 values('01/12/2018'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1989 values('25/10/2019'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select * from DemoTable1989;This will produce the following output −+------------+ | DueDate | +------------+ | 31/01/2015 | ...
Read MoreConvert DATE timestamp to return the month number
To return only the month number, 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 More