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 121 of 341
MySQL 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 MoreSet multiple values for custom columns in MySQL?
For this, you can use UNION ALL. Let us first create a table −mysql> create table DemoTable1987 ( UserValue int ); Query OK, 0 rows affected (2.90 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1987 values(4); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1987 values(5); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1987 values(6); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1987 values(7); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select * from DemoTable1987;This ...
Read More