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 292 of 341
Select first element of a commaseparated list in MySQL?
To select first element of a comma-separated list, you can use SUBSTRING_INDEX(). Let us first create a table:mysql> create table DemoTable ( CSV_Value varchar(200) ); Query OK, 0 rows affected (0.81 sec)Following is the query to insert some records in the table using insert command. We have inserted records in the form of comma-separated integer list:mysql> insert into DemoTable values('10, 20, 50, 80'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('100, 21, 51, 43'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('1, 56, 103, 1090'); Query OK, 1 row affected (0.26 ...
Read MoreFormat date in MySQL to return MonthName and Year?
Use DATE_FORMAT() and set the specifiers to display only the MonthName and Year. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, AdmissionDate date ); Query OK, 0 rows affected (0.69 sec)Insert records in the table using insert command −mysql> insert into DemoTable(AdmissionDate) values('2013-04-21'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(AdmissionDate) values('2014-01-31'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(AdmissionDate) values('2016-09-01'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(AdmissionDate) values('2018-12-12'); Query OK, 1 row affected (0.11 sec) mysql> insert ...
Read MoreHow to select particular range of values in a MySQL table?
In order to select particular range of values in a MySQL table, you can use WHERE clause. Let us first create a table:mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(200), CustomerAge int, isRegularCustomer bool ); Query OK, 0 rows affected (0.57 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(CustomerName, CustomerAge, isRegularCustomer)values('Chris', 24, true); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(CustomerName, CustomerAge, isRegularCustomer)values('Robert', 26, false); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(CustomerName, CustomerAge, isRegularCustomer)values('Mike', ...
Read MoreCan I get Age using BirthDate column in a MySQL query?
To get Age using BirthDate column in a MySQL query, you can use datediff(). Let us first create a table:mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, DateOfBirth date ); Query OK, 0 rows affected (1.46 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(DateOfBirth) values('2010-01-21'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(DateOfBirth) values('1993-04-02'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(DateOfBirth) values('1999-12-01'); Query OK, 1 row affected (1.53 sec) mysql> insert into DemoTable(DateOfBirth) values('1998-11-16'); Query OK, 1 row ...
Read MoreGet the names beginning with a particular character using LIKE in MySQL
To get the names beginning with a particular character, you need to use LIKE. Let us first create a table:mysql> create table DemoTable ( StudentFirstName varchar(20) ); Query OK, 0 rows affected (1.01 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Carol'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Johnny'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Chris'); ...
Read MoreHow to convert string to 24-hour datetime format in MySQL?
To convert string to 24 hour datetime format in MySQL, you can use STR_TO_DATE() method. With that use the following format for datetime as the parameter:'%Y-%m-%d %H:%i:%s'Following is the syntaxSELECT STR_TO_DATE(yourColumnName, '%Y-%m-%d %H:%i:%s') FROM yourTableName;Let us first create a table:mysql> create table DemoTable (ArrivalDate varchar(200)); Query OK, 0 rows affected (0.57 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('2019-01-31 15:45:23'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('2012-12-12 20:30:26'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('2016-06-07 21:04:05'); Query OK, 1 row ...
Read MoreIs there a way to convert Integer field into Varchar without losing data in MySQL?
You can use ALTER command to convert Integer into Varchar. Let us first create a tablemysql> create table DemoTable ( UserId int, UserFirstName varchar(20), UserLastName varchar(20), UserAge int ); Query OK, 0 rows affected (0.73 sec)Now check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output −+---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | UserId | int(11) | YES | | NULL | ...
Read MoreHow to auto-increment value of tables to lower value in MySQL?
If you’re using InnoDB engine, then you cannot set auto_increment value of tables to lower value. You need to change your engine from InnoDB to MyISAM.Note: The engine MyISAM allows you to set lower value. Here, we are using the same.According to the official documents:You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than ...
Read MoreHow to return today's records using DATE from DATETIME Field?
Let us first create a table in which one of the column is of datetime type;mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ShippingDate datetime ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.mysql> insert into DemoTable(ShippingDate) values('2019-03-01 05:45:32'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(ShippingDate) values('2019-04-13 11:34:56'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ShippingDate) values('2019-03-15 04:45:23'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ShippingDate) values('2019-04-11 12:10:02'); Query OK, 1 row affected (0.17 sec)Following is the ...
Read MoreHow to use COUNT(*) to return a single row instead of multiple?
You need to use GROUP BY with COUNT(*) for this to group the values and display the count eliminating multiple values. Let us first create a table:mysql> create table DemoTable (Value int); Query OK, 0 rows affected (0.55 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.14 sec) mysql> ...
Read More