Found 4219 Articles for MySQLi

The DELIMITER // can be used to change the statement from semicolon (;) to //. Now you can write multiple statements with semi-colon in a trigger.Here is the demo of triggers. In this example whenever you enter the EmployeeSalary less than 1000 then it will by default set to 10000.Firstly, let us create a table. The query to create a table is as follows -mysql> create table EmployeeTable -> ( -> EmployeeId int, -> EmployeeName varchar(100), -> EmployeeSalary float -> ); Query OK, 0 rows affected (0.76 sec)After creating a table, you need to create a trigger on insert command. ... Read More

You can set value for a column of all records with the help of update command.The syntax is as follows if you want set NULL value for all the records in a column −update yourTableName set yourColumnName = NULL;Or if you want to use empty string, the following is the syntax −update yourTableName set yourColumnName = ’’;To understand the above concept, let us create a table. The query to create a table.mysql> create table StudentDemo    −> (    −> Studentid int,    −> StudentName varchar(100),    −> Age int    −> ); Query OK, 0 rows affected (0.64 sec)The ... Read More

The value in the parentheses is used to display only the width and sets the zerofill. The width is 5 for int(5), whereas 10 for int(10). Let us see another example with a different width value set for int.Let us first create a table. Here, we have set the int to int(11) and int(13). The following is the query to create a table −mysql> create table intVsIntAnyThingDemo −> ( −> Number1 int(11) unsigned zerofill, −> Number int(13) unsigned zerofill −> ); Query OK, 0 rows affected (1.17 sec)Now you can insert record in the table with the help of insert ... Read More

You can set the result of a query using select into command. The syntax is as follows.select yourColumnName1 into @anyVariableName from yourTableName where yourColumnName2='anyValue';Check the result is present in the variable or not using the select command. The syntax is as follows -select @anyVariableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table StudentInformation -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert ... Read More

The difference between MySQL BigInt and int is that INT is a 32-bit long while BIGINT is 64-bit long.The following are some of the points −The BigInt takes 8 bytes of storage while int takes 4 bytes of storage.The int takes 4294967295 maximum values for int(10), whereas 18, 446, 744, 073, 709, 551, 615 for the bigint(20).The BigInt(20) and int(10), in this 20 and 10 can be used for width display with zerofill.Here is the demo of Bigint and int with zerofill. The following is the query to create a table.mysql> create table BigintandintDemo −> ( ... Read More

To make MySQL’s NOW() and CURDATE() functions use UTC, you need to write my.cnf file. Write the below instruction in my.cnf −[mysqld_safe] timezone = UTCFirstly, reach the directory with the help of the following query −mysql> select @@datadir;The following is the output −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the directory for which the link ... Read More

You can count multiple COUNT() for multiple conditions in a single query using GROUP BY.The syntax is as follows -SELECT yourColumnName, COUNT(*) from yourTableName group by yourColumnName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table MultipleCountDemo -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (2.17 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into MultipleCountDemo values(1, 'Carol', 21); Query OK, 1 row affected (0.27 sec) mysql> insert into MultipleCountDemo values(2, ... Read More

In MySQL, now() can be used to insert current date/time. The syntax is as follows −insert into yourTableName values(now());To understand the above concept of inserting current date/time in the table, let us first create a table −mysql> create table CurrentDateTimeDemo −> ( −> YourTime datetime −> ); Query OK, 0 rows affected (0.58 sec)Inserting current date/time using now(). The query is as follows −mysql> insert into CurrentDateTimeDemo values(now()); Query OK, 1 row affected (0.20 sec)Now you can check the current date/time has been inserted or not −mysql> select *from CurrentDateTimeDemo;The following ... Read More

You can use WHERE clause and OR operator to show table with multiple LIKE. The syntax is as follows:show table from yourDatabaseName where tables_in_yourDatabaseName Like ‘%anyTableName%’ or tables_in_yourDatabaseName Like ‘%anyTableName2%’ or tables_in_yourDatabaseName Like ‘%anyTableName3%’ . . . . or tables_in_yourDatabaseName Like ‘%anyTableNameN%’In the above syntax, only the table name in the database is displayed.Here the database ‘test’ and the tables in the same database is considered. The query to show tables with multiple LIKE is as follows -mysql> show tables from test -> where tables_in_test like '%userrole%' -> or tables_in_test like '%view_student%' -> or tables_in_test like '%wholewordmatchdemo%';The following is the ... Read More

To update values incrementally in MySQL, you need to create a variable with the help of SET command. The syntax to create a variable is as follows −set @anyVariableName := 0;To update value, you need to use UPDATE command. Let us begin with creating a table. The query to create a table −mysql> create table UpdateValueIncrementally −> ( −> ProductId int −> ); Query OK, 0 rows affected (0.90 sec)Insert records in the table with the help of select statement. The query is as follows −mysql> insert into UpdateValueIncrementally values(10); Query ... Read More