MySQLi Articles - Page 105 of 388

For this, use aggregate function MAX() along with the GROUP BY clause. Let us first create a table −mysql> create table DemoTable (    Id int,    Value1 int,    Value2 int,    Value3 int,    Value4 int ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Id, Value4) values(100, 30); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Id, Value1, Value2, Value3) values(100, 20, 60, 40); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Id, Value2, Value3, Value4) values(100, 90, 100, 110); Query OK, 1 ... Read More

The easiest way to achieve this is by using REGEXP. Let us first create a table −mysql> create table DemoTable (    Id varchar(50) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John123'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('123Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('99Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('19David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('99S'); Query OK, 1 row affected (0.18 sec) ... Read More

Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(50) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name) values('Mike'); Query OK, 1 row affected (0.13 sec)Display all records ... Read More

We can use the HAVING clause rather than the WHERE in MySQL. Let us first create a table −mysql> create table DemoTable (    Name varchar(50),    Price int ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 40); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris', 10); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Mike', 44); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

To display a single random row, use the RAND() with LIMIT. Here, LIMIT is used to fetch the number of records, since we want only a single row, therefore use LIMIT 1. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(50),    Quote text ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Quote) values('Chris', 'MySQL is a relational database'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name, Quote) values('Robert', 'Java is an ... Read More

Let us first create a table −mysql> create table DemoTable (    AdmissionDate date ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-08-31'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-09-01'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-05-10'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2019-06-12'); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ ... Read More

To set a custom value for NULL values, use the UPDATE command along with IS NULL property in a stored procedure. Let us first create a table −mysql> create table DemoTable (    Id int,    FirstName varchar(50) ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(101, NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(102, 'Mike'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values(103, NULL); Query ... Read More

The autoincrement in MySQL gives a unique number every time. By default, it starts at 1. If you want to start from another number, then you need to change the auto-increment value with the help of ALTER command or you can give value at the time of table creation.Let us first create a table −mysql> create table DemoTable (    UniqueNumber int NOT NULL AUTO_INCREMENT,    PRIMARY KEY(UniqueNumber) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable ... Read More

Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FileID int ) AUTO_INCREMENT=100; Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FileID) values(50); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(FileID) values(60); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(FileID) values(50); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(FileID) values(70); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable(FileID) values(60); Query OK, 1 row affected (0.28 sec) mysql> ... Read More

Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(50),    StudentAge int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentAge) values('Chris', 21); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('David', 23); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('Bob', 22); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('Carol', 21); Query OK, 1 row affected (0.30 sec)Display all records from ... Read More