To fetch random rows in MySQL, use ORDER BY RAND(). Let us first create a table −mysql> create table DemoTable1835      (      ListOfIds varchar(20)      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1835 values('10, 20, 30'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('70, 80, 90'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('45, 67, 89'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('98, 96, 49'); Query OK, 1 row affected (0.00 ... Read More

Let us first create a table −mysql> create table DemoTable1833      (      Name varchar(20)      ); Query OK, 0 rows affected (0.00 sec)Alter table −Mysql> alter table DemoTable1833 ADD FULLTEXT(Name); Query OK, 0 rows affected, 1 warning (0.00 sec) Records: 0  Duplicates: 0  Warnings: 1Insert some records in the table using insert command −mysql> insert into DemoTable1833 values('John Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 values('Chris Brown'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 ... Read More

Let us create a table and set a column value with type JSONmysql> create table DemoTable1832      (      ListOfNames JSON      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1832(ListOfNames) values('["Sam", "Mike", "Carol"]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1832(ListOfNames) values('["David", "Bob"]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1832(ListOfNames) values('["Adam", "John", "Sam"]'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1832; This will produce the following ... Read More

Let us first create a table. Here. We have set UserId column with ZEROFILL and AUTO_INCREMENTmysql> create table DemoTable1831      (      UserId int(7) zerofill auto_increment,      PRIMARY KEY(UserId)      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1831 values(101); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec)Display all records ... Read More

To delete only specific rows, use MySQL NOT IN(). Let us first create a table −mysql> create table DemoTable1830      (      StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,      StudentName varchar(20)      )AUTO_INCREMENT=101; Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1830(StudentName) values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1830(StudentName) values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1830(StudentName) values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1830(StudentName) values('Sam'); Query OK, 1 row affected (0.00 ... Read More

Let us first create a table −mysql> create table DemoTable1829      (      Name varchar(20),      isTopper ENUM('YES', 'NO')      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1829 values('Chris', 'yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('David', 'yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('Mike', 'no'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('David', 'yes'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement ... Read More

To add a set of elements in a single cell, use the concept of JSON. Let us first create a table −mysql> create table DemoTable1828      (      EmployeeId int,      EmployeeRecords JSON      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1828 values(1, '[{"EmployeeName":"Chris", "EmployeeAge":29}, {"EmployeeName":"David", "EmployeeAge":27}]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1828 values(2, '[{"EmployeeName":"John", "EmployeeAge":36}, {"EmployeeName":"Mike", "EmployeeAge":32}]'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1828; This ... Read More

To check for NULL or empty variable, use the IF condition. Let us create a stored procedure −mysql> delimiter // mysql> create procedure checkingForNullDemo(Name varchar(20))      begin      if Name is NULL OR Name='' then      select 'Adam Smith';      else      select Name;      end if ;      end      // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;Case 1When NULL is passed. Call the stored procedure using call commandmysql> call checkingForNullDemo(NULL);This will produce the following output −+------------+ | Adam Smith | +------------+ | Adam Smith | +------------+ 1 ... Read More

Let us create a stored procedure. Here, we are calculating amount*quantity i.e. implementing mathematical operations −mysql> delimiter // mysql> create procedure calculation_proc(amount int,quantity int)      begin      select amount,quantity,(amount*quantity) as Total;      end      // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;Now you can call a stored procedure using call command −mysql> call calculation_proc(250,3);This will produce the following output −+--------+----------+-------+ | amount | quantity | Total | +--------+----------+-------+ |    250 |        3 |   750 | +--------+----------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

To search for ^ character, use the LIKE operator as in the below syntax −select table_schema, table_name, column_name  from information_schema.columns  where column_name like '%^%';Let us first create a table −mysql> create table DemoTable1826      (      `^` varchar(20),      Name varchar(20),      `^Age` int      ); Query OK, 0 rows affected (0.00 sec)Here is the query to search for ^ character in a MySQL tablemysql> select table_schema, table_name, column_name      from information_schema.columns      where column_name like '%^%';This will produce the following output −+--------------+---------------+-------------+ | TABLE_SCHEMA | TABLE_NAME    | COLUMN_NAME | +--------------+---------------+-------------+ | ... Read More