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 More

For sort by distance, use ORDER BY ABS(). Let us first create a table −mysql> create table DemoTable (    Number int ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.51 sec) mysql> insert into DemoTable values(101); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(102); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values(103); Query OK, 1 row affected (0.84 sec) mysql> insert into DemoTable values(104); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

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 More

Let us first create a table −mysql> create table DemoTable (    StudentId varchar(40) ); Query OK, 0 rows affected (0.55 sec)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('~'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('40'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected ... Read More

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 More

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 More

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 More

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 More

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 More

To get the nth highest value in a column, you can use LIMIT OFFSET. Here, OFFSET is used to skip the values. Let us first create a table −mysql> create table DemoTable (    Value int ) ; Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(140); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.09 ... Read More