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

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

To delete, use MySQL DELETE. However, to get records older than 14 days, subtract the current date with date interval of 14 days. The syntax for the same is shown below −delete from yourTableName where yourColumnName< (curdate() - interval 14 day);Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    DueDate date ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(DueDate) values('2019-08-20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(DueDate) values('2019-08-19'); Query OK, 1 row affected ... Read More