- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4219 Articles for MySQLi
527 Views
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
130 Views
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
143 Views
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
329 Views
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
602 Views
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
3K+ Views
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
128 Views
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
325 Views
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
3K+ Views
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
4K+ Views
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