Found 4219 Articles for MySQLi

The char_length() can be used to display the length of a string. Let us see an example to get the length of the string included as a parameter.mysql> select char_length('John');The following is the output.+---------------------+ | char_length('John') | +---------------------+ | 4 | +---------------------+ 1 row in set (0.00 sec)The length() function can be used to display the length of string measured in bytes. In many cases characters and bytes gives the same length.Here is an example of length()mysql> select length('Tim'); The following is ... Read More

We can get new record key with the help of LAST_INSERT_ID() function from MySQL. First, we will create a table and for inserting record, we will use LAST_INSERT_ID(). Let us create a table with the help of create command. The query is as follows − mysql> create table LastInsertRecordIdDemo -> ( -> id int auto_increment, -> value varchar(100), -> primary key(id) -> ); Query OK, 0 rows affected (0.52 sec) After creating a table, we will insert records and set it using LAST_INSERT_ID() ... Read More

Firstly, let us get the current datetime with the help of the now() function. mysql> select now(); The following is the output. +---------------------+ | now()               | +---------------------+ | 2018-11-01 19:55:56 | +---------------------+ 1 row in set (0.00 sec) Syntax to subtract 10 days with the help of DATE_SUB() select DATE_SUB(now(),interval integer_value day ); Applying the above syntax to subtract 10 days from the current datetime. mysql> select DATE_SUB(now(),interval 10 day); Here is the output. +---------------------------------+ | DATE_SUB(now(),interval 10 day) | +---------------------------------+ | 2018-10-22 19:56:07             | +---------------------------------+ 1 row in set (0.00 sec)

We can know that with the help of the column name ‘UPDATED_TIME’ using information_schema.tables with WHERE clause. Let us first create a table for our example. mysql> create table MyISAMTableDemo   -> (   -> id int   -> ); Query OK, 0 rows affected (0.56 sec) Inserting some records into table. mysql> insert into MyISAMTableDemo values(1); Query OK, 1 row affected (0.72 sec) mysql> insert into MyISAMTableDemo values(2); Query OK, 1 row affected (0.16 sec) Syntax to know the last updated time. SELECT UPDATE_TIME FROM   information_schema.tables WHERE  TABLE_SCHEMA = 'yourDatabaseName' AND TABLE_NAME = ... Read More

To show the schema, we can use the DESC command. This gives the description about the table structure. The following is the syntax. DESCRIBE yourDatabasename.yourTableName; Let us implement the above syntax. mysql> DESCRIBE business.student; The following is the output. +-------+--------------+------+-----+---------+-------+ | Field | Type         | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id    | int(11)      | YES | MUL | NULL    | | | Name  | varchar(100) | YES  | MUL | NULL |  | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.05 ... Read More

To check if the given value is a string or not ,we use the cast() function. If the value is not numeric then it returns 0, otherwise it will return the numeric value. In this way, we can check whether the value is an integer or not. Case 1 − Checking for a string with integers mysql> select cast('John123456' AS UNSIGNED); The following is the output. It shows that the value is not numeric, therefore 0 is returned. +--------------------------------+ | cast('John123456' AS UNSIGNED) | +--------------------------------+ |                              0 | +--------------------------------+ 1 row in set, 1 warning (0.00 sec) ... Read More

To swap two columns, we can apply the below swapping logic. Add both values and store them into the first column Subtract the first column’s value from the second and store it into the second column. Subtract the first column’s value from the updated second column and store it into the first. The above rule structure is as follows. Suppose, the first column is a and the second column is b. 1. a = a+b; 2. b = a-b; 3. a = a-b; Now we will apply the above rule in order to swap the two ... Read More

Use the function CONVERT() or Regular Expression. The CONVERT() method converts a value from one datatype to another. This will ecnetually fetch digits for us. Let us see an example. Firstly, we will create a table. mysql> create table textIntoNumberDemo   -> (   -> Name varchar(100)   -> ); Query OK, 0 rows affected (0.47 sec) Inserting some records. mysql> insert into textIntoNumberDemo values('John-11'); Query OK, 1 row affected (0.11 sec) mysql> insert into textIntoNumberDemo values('John-12'); Query OK, 1 row affected (0.17 sec) mysql> insert into textIntoNumberDemo values('John-2'); Query OK, 1 row affected (0.11 ... Read More

We can get the current time with the help of now() and adding 2 hours is done by giving the interval as 2. Firstly, collect information of the current time in the system with the help of now(). The current time is . The following is the query to get the current date and time. mysql> select now(); Here is the output. +---------------------+ | now()               | +---------------------+ | 2018-11-01 12:58:40 | +---------------------+ 1 row in set (0.00 sec) To add 2 hours in the current time, we will use the DATE_ADD() function. mysql> select ... Read More

To create a temporary table in a SELECT statement we use TEMPORARY keyword. This temporary table will be visible for the current session and whenever a session is closed, it is automatically destroyed. Two sessions can use the same temporary table. Creating a table. mysql> create table MyTableDemo   -> (   -> id int,   -> Name varchar(100)   -> ); Query OK, 0 rows affected (0.69 sec) Inserting some records. mysql> insert into MyTableDemo values(1, 'John'); Query OK, 1 row affected (0.18 sec) mysql>  insert into MyTableDemo values(2, 'Carol'); Query OK, 1 row affected (0.13 ... Read More