MySQLi Articles - Page 228 of 422

Let us first create a table. Following is the query −mysql> create table gettingAndOrderingRowsDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Value int    -> ); Query OK, 0 rows affected (1.35 sec)Following is the query to insert some records in the table using insert command −mysql> insert into gettingAndOrderingRowsDemo(Value) values(10); Query OK, 1 row affected (0.33 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(13); Query OK, 1 row affected (0.32 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(0); Query OK, 1 row affected (0.17 sec) mysql> insert into gettingAndOrderingRowsDemo(Value) values(20); Query OK, 1 ... Read More

In order to check a table exists in MySQL, you can use INFORMATION_SCHEMA.TABLES. Let us first create a table −mysql> create table Client_information    -> (    -> Id int,    -> Name varchar(10)    -> ); Query OK, 0 rows affected (0.48 sec)Following is the query to insert some records in the table using insert command −mysql> insert into Client_information values(1, 'Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into Client_information values(2, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into Client_information values(3, 'Sam'); Query OK, 1 row affected (0.19 sec)Following is the ... Read More

To write stored procedure to insert data into a table, at first you need to create a table −mysql> create table insertDataUsingStoredProcedure    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY ,    -> Name varchar(20),    -> Age int    -> ); Query OK, 0 rows affected (0.51 sec)Following is the query to write stored procedure to insert data into the table −mysql> DELIMITER // mysql> CREATE PROCEDURE StoredProcedureInsertData(IN StudentName varchar(100), IN StudentAge int)    -> BEGIN    -> insert into insertDataUsingStoredProcedure(Name, Age) values (StudentName, StudentAge );    -> END    -> // Query OK, 0 ... Read More

To get the max value, use the max() function. Let us create a table first −mysql> create table findMaxValueInVarcharField    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Value varchar(200)    -> ); Query OK, 0 rows affected (1.09 sec)Following is the query to insert some records in the table using insert command −mysql> insert into findMaxValueInVarcharField(Value) values('200'); Query OK, 1 row affected (0.14 sec) mysql> insert into findMaxValueInVarcharField(Value) values('1000'); Query OK, 1 row affected (0.25 sec) mysql> insert into findMaxValueInVarcharField(Value) values('899474'); Query OK, 1 row affected (0.18 sec) mysql> insert into ... Read More

When we want to add a pause in MySQL triggers or stored procedures, we use the SLEEP() function. This function stops the execution of a query or procedure for a set amount of time. It is useful in different scenarios such as checking how an app acts when operations slow down or stimulating network slowness. SLEEP() Function MySQL has this SLEEP() function that will suspend the operation for a specified time duration. For example, if you want to pause for 5 seconds, you would write - SLEEP(5); This causes the database to wait for 5 ... Read More

Yes, we can do that. To understand, let us first create a table −mysql> create table enforceCompoundUniqueness    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(40) NOT NULL,    -> StudentMobileNumber varchar(12) NOT NULL,    -> UNIQUE StudentName_StudentMobileNumber(StudentName, StudentMobileNumber)    -> ); Query OK, 0 rows affected (0.60 sec)Following is the query to insert records in the table using insert command −mysql> insert into enforceCompoundUniqueness(StudentName, StudentMobileNumber) values('Larry', '2322245676'); Query OK, 1 row affected (0.18 sec) mysql> insert into enforceCompoundUniqueness(StudentName, StudentMobileNumber) values('Larry', '2322245676'); ERROR 1062 (23000): Duplicate entry 'Larry-2322245676' for key 'StudentName_StudentMobileNumber' ... Read More

In order to show some columns, use NOT IN and set those columns which you do not want to display. Let us first create a table. Following is the query −mysql> create table student_Information    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(50),    -> StudentAge int,    -> StudentAddress varchar(100),    -> StudentAllSubjectScore int    -> ); Query OK, 0 rows affected (0.69 sec)Following is the query to display a description about the above table −mysql> desc student_Information;This will produce the following output −+------------------------+--------------+------+-----+---------+----------------+ | Field             ... Read More

To select min and max value from the part of a table in MySQL, use the following syntax −select min(yourColumnName) as yourAliasName1, max(yourColumnName) as yourAliasName2 from (select yourColumnName from yourTableName limit yourLimitValue) tbl1;Let us first create a table. Following is the query −mysql> create table MinAndMaxValueDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Value int    -> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. Following is the query −mysql> insert into MinAndMaxValueDemo(Value) values(10); Query OK, 1 row affected (0.16 sec) mysql> insert into MinAndMaxValueDemo(Value) ... Read More

To merge two tables with UNION, you can use create table select statement. Following is the syntax −create table yourNewTableName select * from yourFirstTableName UNION select * from yourSecondTableName;Let us first create a table. Following is the query −mysql> create table FirstTable    -> (    -> Id int,    -> PersonName varchar(20)    -> ); Query OK, 0 rows affected (2.10 sec)Following is the query to insert some records in the table using insert command −mysql> insert into FirstTable values(10, 'Larry'); Query OK, 1 row affected (0.12 sec) mysql> insert into FirstTable values(20, 'David'); Query OK, 1 row ... Read More

To compare two strings, which are numbers, let us first create a table. Following is the query −mysql> create table compareTwoStringsDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Value varchar(100)    -> ); Query OK, 0 rows affected (0.52 sec)Following is the query to insert some records in the table using insert command −mysql> insert into compareTwoStringsDemo(Value) values('1235667'); Query OK, 1 row affected (0.66 sec) mysql> insert into compareTwoStringsDemo(Value) values('999999'); Query OK, 1 row affected (0.11 sec) mysql> insert into compareTwoStringsDemo(Value) values('999888'); Query OK, 1 row affected (0.17 sec) mysql> ... Read More