MySQLi Articles - Page 34 of 341

For this, you can use WHERE clause along with subquery. Let us first create a table −mysql> create table DemoTable1840      (      UserName varchar(20),      UserType ENUM('GUEST', 'ADMIN')      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1840 values('Chris', 'Admin'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1840 values('David', 'Guest'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1840 values('Chris', 'Guest'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * ... Read More

The syntax for the fastest way is given below. Here, we have used INSERT INTO just once and formed an optimized way −insert into yourTableName values(NULL, yourValue1', yourValue2), (NULL, yourValue1', yourValue2), ....N;Let us first create a table −mysql> create table DemoTable1839      (      ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,      ClientName varchar(20),      ClientAge int      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1839 values(NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', 29), (NULL, 'Chris', ... Read More

To sort search results based on substring position, use ORDER BY LOCATE(). Let us first create a table −mysql> create table DemoTable1838      (      Subject varchar(100)      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1838 values('MongoDB MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1838 values('MySQL Java'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1838 values('JavaWithMySQL'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1838; This will ... Read More

Let us first create a table −mysql> create table DemoTable1837      (      StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,      StudentName varchar(20)      ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1837(StudentName) values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('Mike'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement ... Read More

This error occurs when we use TYPE for ENGINE NAME. The error is as follows −mysql> create table DemoTable1836      (      ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,      ClientName varchar(20)      )Type=MyISAM AUTO_INCREMENT=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Type=MyISAM AUTO_INCREMENT=1' at line 5Now, in MySQL 8, you can use ENGINE instead of Type. Let us first create a table −mysql> create table DemoTable1836      (      ClientId int NOT ... Read More

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

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