Found 4219 Articles for MySQLi

You can get the date/time of the last change to a MySQL database with the help of INFORMATION_SCHEMA.TABLES. The syntax is as follows −SELECT update_time FROM information_schema.tables WHERE table_schema = 'yourDatabaseName’' AND table_name = 'yourTableName’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table TblUpdate    -> (    -> Id int not null auto_increment primary key,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into TblUpdate(Name) ... Read More

You need to use REGEXP for this. The syntax is as follows −select *from yourTableName where yourColumnName REGEXP '[[:digit:]]$';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringEndsWithNumber    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserId varchar(20),    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringEndsWithNumber(UserId, UserName) values('123User', 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert ... Read More

You need to use default keyword for this. The syntax is as follows −alter table yourTableName add yourColumnName yourDataType NULL Default '';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AllowNullDefaulNotNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.65 sec)Let us add a new column that allow NULL value but default value set to NOT NULL. The query is as follows −mysql> alter table AllowNullDefaulNotNullDemo add UserAddress varchar(20) ... Read More

Yes, let us first see the working of ternary operator in C or C++ language.X=(X > 10 && ( X-Y) < 0) ?: X:(X-Y);Here is the demo code in C language. After that we will check in MySQL. The C code is as follows −#include int main() {    int X;    int Y;    int result;    printf("Enter the value for X:");    scanf("%d", &X);    printf("Enter the value for Y:");    scanf("%d", &Y);    result=( X > 1 && (X-Y) < 0) ? X: (X-Y);    printf("The Result is=%d", result);    return 0; }The snapshot of C ... Read More

You can use BIGINT but this is not unlimited but you can use large number of primary keys auto increment using it. The syntax is as follows −yourColumnName BIGINT NOT NULL AUTO_INCREMENT;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table LargeAutoIncrement -> ( -> Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.78 sec)Now in this table you can store large number like 9223372036854775807 i.e. for primary key auto increment.Let us insert ... Read More

To compare two tables and return missing ids, you need to use MySQL LEFT OUTER JOIN.Let us create a table with sample fields and then we will insert records. The query to create the first table −First_Tablemysql> create table First_Table    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.88 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into First_Table values(1); Query OK, 1 row affected (0.68 sec) mysql> insert into First_Table values(2); Query OK, 1 row affected (0.29 sec) mysql> insert into First_Table values(3); ... Read More

You can search between dates stored as varchar using STR_TO_DATE(). The syntax is as follows −select *from yourTableName where STR_TO_DATE(LEFT(yourColumnName, LOCATE('', yourColumnName)), '%m/%d/%Y') BETWEEN 'yourDateValue1' AND 'yourDateValue2’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchDateAsVarchar    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ShippingDate varchar(100),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/28/2011 9:58 AM'); Query OK, 1 ... Read More

Yes, you need to use AND or OR operator. The syntax is as follows −select *from yourTableName where yourColumnName1=yourValue AND yourColumnName=yourValue';For AND condition, both conditions must be true otherwise you will get an empty set.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table WhereDemo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is ... Read More

To correctly use aggregate function with where clause in MySQL, the following is the syntax −select *from yourTableName where yourColumnName > (select AVG(yourColumnName) from yourTableName);To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table EmployeeInformation    -> (    -> EmployeeId int,    -> EmployeeName varchar(20),    -> EmployeeSalary int,    -> EmployeeDateOfBirth datetime    -> ); Query OK, 0 rows affected (1.08 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into EmployeeInformation values(101, 'John', 5510, '1995-01-21'); ... Read More

You need to use != operator along with subquery. The syntax is as follows −select *from yourTableName where yourIdColumnName != (select max(yourIdColumnName) from yourTableName );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AllRecordsExceptLastOne    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(10),    -> UserAge int    -> ,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.65 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More