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 196 of 355
Check if the column values are the same among multiple records and set these records in a single row separated by a special character in MySQL
For this, you can use GROUP_CONCAT() along with DISTINCT. Let us first create a table −mysql> create table DemoTable ( Id int, Subject varchar(40) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'MySQL'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(100, 'MongoDB'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(100, 'Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 'MongoDB'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(101, 'MySQL'); ...
Read MoreMySQL query to display only the records that contains single word?
For this, you can filter records on the basis of LIKE. Let us first create a table −mysql> create table DemoTable ( Name varchar(50) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Adam Smith'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Carol'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('David Miller'); Query OK, 1 ...
Read MoreFetch date record that equals today in MySQL
For this, compare the date records with the current date using the CURDATE() method. Let us first create a table −mysql> create table DemoTable ( RegistrationLastDate datetime ); Query OK, 0 rows affected (0.61 sec)Let’s say the current date is −2019-09-03Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-08-01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-09-03 9:50:56'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('2019-09-03'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-09-02'); Query OK, 1 row affected (0.18 sec) mysql> ...
Read MoreHow to create NVARCHAR column in MySQL?
The MySQL converts NVARCHAR() to VARCHAR(). NVARCHAR stands for National Varchar in MySQL. Let us first create a table with one of the columns “StudentName” as NVARCHAR −mysql> create table DemoTable ( StudentName NVARCHAR(40), StudentCountryName VARCHAR(50) ); Query OK, 0 rows affected, 1 warning (0.49 sec)Let us check the description of the table −mysql> desc DemoTable;This will produce the following output. As you can see below, the StudentName column with NVARCHAR type is automatically converted to VARCHAR in MySQL −+--------------------+-------------+------+-----+---------+-------+ | Field | Type ...
Read MoreMySQL query to update all the values in a column with numeric incremental values like John1, John2, John3, etc.
To update all the values in a column to John1, John2, etc.; you need to set incremental values 1, 2, 3, etc. and concatenate them to the records. Let us first create a table −mysql> create table DemoTable ( StudentId varchar(80) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. Here, for our example, we have set similar names −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row ...
Read MoreHow to delete records based on a word with underscore like MONTH_JAN'?
For this, you can use LIKE operator. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(40), BornMonth varchar(40) ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, BornMonth) values('Chris', 'MONTH_DEC'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, BornMonth) values('Bob', 'MONTH_JAN'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name, BornMonth) values('Mike', 'MONTH_FEB'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name, BornMonth) values('David', 'MONTH_JAN'); Query OK, 1 ...
Read MoreMySQL edit and update records including employee salary
The UPDATE command is used in MySQL to update records. With it, the SET command is used to set new values. Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(50), EmployeeSalary int ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Chris', 56780); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Robert', 45670); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary) values('Mike', 87654); Query OK, 1 ...
Read MoreDoes using SERIAL as column name already includes 'NOT NULL' in MySQL?
In MySQL, SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. So, do not use NOT NULL after SERIAL, since it already includes NOT NULL as part of its definition.Let us see an example and create a table. Here, we have a column with the name “serial” −mysql> create table DemoTable ( Id serial ); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command. We aren’t including any value while inserting −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(); Query OK, 1 ...
Read MoreHow to find last date from records with date values in MySQL?
To get the last date i.e. the latest, use aggregate function MAX() with a subquery. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ExpiryDate date ); Query OK, 0 rows affected (1.40 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ExpiryDate) values('2018-12-31'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(ExpiryDate) values('2019-09-01'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(ExpiryDate) values('2019-09-01'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(ExpiryDate) values('2016-08-30'); Query OK, 1 row affected (0.13 ...
Read MoreIs it compulsory to set PRIMARY KEY for AUTO_INCREMENT value?
Yes, use AUTO_INCREMENT with PRIMARY KEY. Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT, EmployeeName varchar(40), EmployeeAge int, PRIMARY KEY(EmployeeId), UNIQUE KEY(EmployeeName, EmployeeAge) ); Query OK, 0 rows affected (0.96 sec)Let us check the table description of the table −mysql> desc DemoTable;This will produce the following output −+--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | EmployeeId | int(11) | NO | PRI | NULL ...
Read More