Convert all the records in a MySQL table from uppercase to lowercase using UPDATE command along with LOWER() method.Let us first create a table −mysql> create table DemoTable    (    Id varchar(100),    StudentFirstName varchar(20),    StudentLastName varchar(20),    StudentCountryName varchar(10)    ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-101', 'John', 'Smith', 'US'); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values('STU-102', 'John', 'Doe', 'UK'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('STU-103', 'David', 'Miller', 'AUS'); Query OK, 1 ... Read More

To display IDs in a particular order i.e. the order of your choice use FIELD() method.Let us first create a table −mysql> create table DemoTable    (    UserId int    ); Query OK, 0 rows affected (0.64 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.13 sec) ... Read More

You can use CASE statement for this and set conditions to get result in the temporary column.Let us first create a table −mysql> create table DemoTable    (    EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    EmployeeName varchar(20),    EmployeeSalary int,    EmployeeExperience int    ); Query OK, 0 rows affected (0.64 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, EmployeeSalary, EmployeeExperience) values('Larry', 4500, 5); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(EmployeeName, EmployeeSalary, EmployeeExperience) values('Mike', 130000, 8); Query OK, 1 row affected (0.21 sec) mysql> ... Read More

You can use system variables character_set_server to know the default server character set in MySQL. Following is the syntax −SHOW VARIABLES LIKE 'character_set_server';Additionally, to u can use collation_server system variable to know the default collation in MySQL. Following is the syntax −SHOW VARIABLES LIKE 'collation_server';Let us execute the above syntaxes to know the default character set and collation.Following is the query −mysql> SHOW VARIABLES LIKE 'character_set_server';This will produce the following output −+----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | character_set_server | utf8 | +----------------------+-------+ 1 row in set (0.25 sec)Following is the ... Read More

To copy data from one field to another on every row, use the UPDATE command.Let us first create a table −mysql> create table DemoTable    (    StudentId int,    StudentFirstName varchar(20),    StudentMarks int default 0    ); Query OK, 0 rows affected (0.49 sec)Following is the query to insert records in the table using insert command −mysql> insert into DemoTable(StudentId, StudentFirstName) values(89, 'Larry'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentId, StudentFirstName) values(35, 'Robert'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(StudentId, StudentFirstName) values(48, 'Chris'); Query OK, 1 row affected (0.13 sec) ... Read More

To retrieve records from the part of a comma-separated list, you can use built in function FIND_IN_SET().Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(20),    Marks varchar(200)    ); Query OK, 0 rows affected (0.61 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable(Name, Marks) values('Larry', '98, 34, 56, 89'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, Marks) values('Chris', '67, 87, 92, 99'); Query OK, 1 row affected (0.15 sec) mysql> insert ... Read More

In MySQL, it is not possible to use select from procedure in FROM clause. You can use CALL command and after that the SELECT statement can be executed.Let us first create a table:mysql> create table DemoTable2    -> (    -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> CustomerName varchar(100),    -> ShippingDateTime datetime    -> ); Query OK, 0 rows affected (0.66 sec)Following is the query to create stored procedure:mysql> DELIMITER // mysql> CREATE PROCEDURE insert_information(Name varchar(100), shippingtime datetime)    -> BEGIN    ->    -> INSERT INTO DemoTable2(CustomerName, ShippingDateTime) VALUES(Name, shippingtime);    -> END    -> ... Read More

MySQL does not support array variables. To get the same result, use the table DUAL. Following is the syntax:SELECT yourValue1 AS ArrayValue FROM DUAL UNION ALL SELECT yourValue2 FROM DUAL UNION ALL SELECT yourValue3 FROM DUAL UNION ALL SELECT yourValue4 FROM DUAL UNION ALL . . . . . . SELECT yourValueN FROM DUAL;Let us create a sample table:mysql> SELECT 1 AS ArrayValue FROM DUAL       UNION ALL       SELECT 2 FROM DUAL       UNION ALL       SELECT 3 FROM DUAL       UNION ALL       SELECT 4 FROM ... Read More

To delete nth row in MySQL, use DELETE statement and work with subquery. Let us first create a table:mysql> create table DemoTable1    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(100)    -> ); Query OK, 0 rows affected (0.99 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable1(StudentName) values('Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1(StudentName) values('Sam'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1(StudentName) values('Mike'); Query OK, 1 row affected (0.18 sec) mysql> ... Read More

Set the same condition like “OR” in a MySQL CASE expression. Let us first create a sample table.Following is the querymysql> create table caseOrConditionDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Score int    -> ); Query OK, 0 rows affected (0.49 sec)Following is the query to insert some records in the table using insert command:mysql> insert into caseOrConditionDemo(Name, Score) values('Larry', 85); Query OK, 1 row affected (0.18 sec) mysql> insert into caseOrConditionDemo(Name, Score) values('Sam', 74); Query OK, 1 row affected (0.20 sec) mysql> insert into caseOrConditionDemo(Name, ... Read More