Found 4219 Articles for MySQLi

To check the read/write ratio, you need to use SHOW STATUS command. This will give all the ratios.Case 1 − The syntax is as follows to get the read/write ratio −SHOW STATUS LIKE ‘Com_%’;Case 2 − If you want the insert, update, select and delete ratio, use the below syntax −SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR Variable_name = 'Com_Update' OR Variable_name = 'Com_delete';Here is no need to do any changes in the above syntax. The query is as follows −mysql> SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR    -> ... Read More

To select part of a timestamp in a query, you need to use YEAR() function. The syntax is as follows in MySQL.select YEAR(yourTimestampColumnName) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SelectPartOfTimestampDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingTime TIMESTAMP -> ); Query OK, 0 rows affected (1.11 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More

To perform search/ replace for only the first occurrence, use the CONCAT and REPLACE() function.The query is as follows to set user defined session variable −mysql> set @Sentence='Thks ks is a my string'; Query OK, 0 rows affected (0.00 sec)In this k will be replaced with i only once. The query is as follows. We have used INSTR() also −mysql> select @Sentence as NewString ,CONCAT(REPLACE(LEFT(@Sentence, INSTR(@Sentence, 'k')), 'k', 'i'),    -> SUBSTRING(@Sentence, INSTR(@Sentence, 'k') + 1)) as ChangeOnlyOneTime;The following is the output displaying only the first occurrence of a character is replaced −+------------------------+------------------------+ | NewString ... Read More

To exactly count all rows, you need to use the aggregate function COUNT(*). The syntax is as follows −select count(*) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CountAllRowsDemo    -> (    -> Id int,    -> Name varchar(10),    -> Age int    -> ); Query OK, 0 rows affected (1.49 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into CountAllRowsDemo values(1, 'John', 23); Query OK, 1 row affected (0.15 ... Read More

There is no equivalent of ROW_NUMBER() in MySQL for inserting but you can achieve this with the help of variable. The syntax is as follows −SELECT (@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`, yourColumnName1, yourColumnName2, ...N FROM yourTableName ,(SELECT @yourVariableName:=0) AS anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowNumberDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The ... Read More

To set column values as column names in the query result, you need to use a CASE statement.The syntax is as follows −select yourIdColumnName, max(case when (yourColumnName1='yourValue1') then yourColumnName2 else NULL end) as 'yourValue1', max(case when (yourColumnName1='yourValue2') then yourColumnName2 else NULL end) as 'yourValue2', max(case when yourColumnName1='yourValue3') then yourColumnName2 else NULL end) as 'yourValue3’, . . N from valueAsColumn group by yourIdColumnName order by yourIdColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table valueAsColumn    -> (    -> UserId int,    -> UserColumn1 varchar(10),    -> ... Read More

To merge selects together, you need to use GROUP BY clause. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table MergingSelectDemo    -> (    -> RoomServicesId int,    -> RoomId int,    -> ServiceId int    -> ); Query OK, 0 rows affected (1.98 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into MergingSelectDemo values(10, 10, 10); Query OK, 1 row affected (0.29 sec) mysql> insert into MergingSelectDemo values(20, 10, 20); Query OK, 1 row affected ... Read More

After creating a user and giving all privileges to the user, you need to FLUSH PRIVILEGES to set up and want the new settings to work correctly.The syntax is as follows −FLUSH PRIVILEGES;Here is the query to create a new user which has the name ‘Bob’ in my case. The query to create a new user is as follows −mysql> CREATE USER 'Bob'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.56 sec)Now given all privileges to user Bob −mysql> GRANT ALL PRIVILEGES ON *.* TO 'Bob'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.23 sec)Now flush the privileges. ... Read More

You need to use MySQL event scheduler. It manages the execution of events as well as scheduling.First, you need to create a table. After that you can create a event that will schedule every single day.Let us create a table. The query to create a table is as follows −mysql> create table EventDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EventDateTime datetime -> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table using insert command. The query ... Read More

The syntax for multiple inserts for a single column in MySQL is as follows −INSERT INTO yourTableName(yourColumnName) values(‘yourValue1'), (‘yourValue2'), (‘yourValue3'), (‘yourValue4'), .........N;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InsertMultipleDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(10), -> UserRole varchar(20) -> , -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (3.14 sec)Now you can insert some records in the ... Read More