Found 4219 Articles for MySQLi

MySQL follows operator precedence and it has the following list of operators, having the same precedence which is on the same line −INTERVAL BINARY, COLLATE ! - (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + & | =, , >=, >,

As we know that IFNULL() function will return the first argument if it is not NULL otherwise it returns the second argument. On the other hand, COALESCE() function will return first non-NULL argument. Actually, both IFNULL() and COALESCE() functions in MySQL works equivalently if the number of arguments is two only. The reason behind this is that IFNULL() function accepts only two arguments and in contrast, COALESCSE() function can accept any number of arguments.Suppose if we want to use IFNULL() function at the place of COALESCE() function then the number of arguments must be two. Following example will demonstrate it ... Read More

To understand it, we are using the data from the table ‘Employee’ having Salary=NULL for ID = 5 and 6, as follows −mysql> Select * from Employee; +----+--------+--------+ | ID | Name   | Salary | +----+--------+--------+ | 1  | Gaurav | 50000  | | 2  | Rahul  | 20000  | | 3  | Advik  | 25000  | | 4  | Aarav  | 65000  | | 5  | Ram    | NULL   | | 6  | Mohan  | NULL   | +----+--------+--------+ 6 rows in set (0.00 sec)Now, the following queries will use COALESCE() function along with UPDATE and ... Read More

It can be done with the SHOW COLUMNS statement. Its Syntax would be as follows −SyntaxSHOW COLUMNS FROM db_name.tab_nameHere, tab_name is the name of the table from which we want to see the list of columns.Db_name is the name of the database, in which the table is storedExampleIn the example we are currently using the database ‘query’ and getting the list of columns from table named ‘arena’ stored in mysql ‘database’ −mysql> SHOW COLUMNS FROM mysql.arena\G *************************** 1. row ***************************   Field: id    Type: int(10) unsigned zerofill    Null: NO     Key: PRI Default: NULL   Extra: auto_increment ... Read More

It can be done with the SHOW COLUMNS statement. Its Syntax would be as follows −SyntaxSHOW COLUMNS FROM tab_nameHere tab_name is the name of the table from which we want to see the list of columns.ExampleIn the example we are getting the list of columns from a table named Student_info −mysql> SHOW COLUMNS FROM Student_info\G *************************** 1. row ***************************   Field: studentid    Type: int(11)    Null: YES     Key: Default: NULL   Extra: *************************** 2. row ***************************   Field: Name    Type: varchar(40)    Null: YES     Key: Default: NULL   Extra: *************************** 3. row ***************************   ... Read More

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLES IN db_nameHere db_name is the name of the database from which we want to see the list of tables.ExampleWe are currently using the database named ‘query’ and the MySQL query below will show us the list of tables along with table type from the database named mysql.mysql> SHOW FULL TABLES IN mysql; +---------------------------+------------+ | Tables_in_mysql           | Table_type | +---------------------------+------------+ | arena                     | BASE TABLE | | ... Read More

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLES FROM db_nameHere, db_name is the name of the database from which we want to see the list of tables.ExampleWe are currently using the database named ‘query’ and the MySQL query below will show us the list of tables along with table type from the database named mysql.mysql> SHOW FULL TABLES FROM mysql; +---------------------------+------------+ | Tables_in_mysql           | Table_type | +---------------------------+------------+ | arena                     | BASE TABLE | | ... Read More

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −SyntaxSHOW FULL TABLESExampleIn the following example our current database is ‘query’ hence the statement below will show us the table list along with table type in the result set from this database −mysql> SHOW FULL TABLES; +-----------------------------+------------+ | Tables_in_query             | Table_type | +-----------------------------+------------+ | accounts                    | BASE TABLE | | address                     | BASE TABLE | | cars     ... Read More

With the help of following MySQL query, we can see the list of MySQL database −mysql> SELECT schema_name FROM information_schema.schemata; +--------------------+ | schema_name        | +--------------------+ | information_schema | | gaurav             | | mysql              | | performance_schema | | query              | | query1             | | sys                | | tutorials          | +--------------------+ 8 rows in set (0.00 sec)We can also use WHERE clause with this query as follows −mysql> SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE '%schema' OR schema_name LIKE '%s'; +--------------------+ | schema_name        | +--------------------+ | information_schema | | performance_schema | | sys                | | tutorials          | +--------------------+ 4 rows in set (0.00 sec)

As we know that COALESCE() function returns first non-NULL value from the list of values. The following IF-THEN-ELSE statement is equivalent to COALESCE() function.IF value1 is not NULL THEN output = value1; ELSIF value2 is not NULL THEN output = value2; ELSIF value3 is not NULL THEN output = value3; . . . ELSIF valueN is not NULL THEN output = valueN; ELSE output = NULL; END IF;