Found 4219 Articles for MySQLi

The rank() function can be used to give a rank for every row within the partition of a result set. First, let us create a table − mysql> create table RankDemo mysql> ( mysql> id int mysql> ); Query OK, 0 rows affected (0.53 sec) Inserting records into table. mysql> insert into RankDemo values(1); Query OK, 1 row affected (0.19 sec) mysql> insert into RankDemo values(3); Query OK, 1 row affected (0.12 sec) mysql> insert into RankDemo values(3); Query OK, 1 row affected (0.11 ... Read More

To change MySQL column definition, we can use modify or change clause with ALTER command. Let us first create a table with a column as ID, with int data type. We will modify the same column name with varchar data type. Creating a table. mysql> create table ModifyColumnDemo -> ( -> id int -> ); Query OK, 0 rows affected (0.52 sec) Now, let us write the syntax to change the column definition. The syntax is as follows − alter table yourTableName modify column columnName data type; ... Read More

To select the last row, we can use ORDER BY clause with desc (descending) property and Limit 1. Let us first create a table and insert some records with the help of INSERT command. The query is as follows. mysql> create table getLastRecord -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.61 sec) After creating the above table, we will insert records with the help of INSERT command. mysql> insert into getLastRecord values(1, 'John'); Query OK, 1 row affected (0.13 sec) ... Read More

To cast VARCHAR to INT, we can use the cast() function from MySQL. Here is the syntax of cast() function. cast(anyValue as dataType) For our example, we will create a table with the help of CREATE command. mysql> create table VarchartointDemo -> ( -> Value varchar(100) -> ); Query OK, 0 rows affected (0.51 sec) After creating a table, let us insert some records into the table with the help of INSERT command. The query is as follows − mysql> insert into VarchartointDemo values('123'); Query OK, 1 row affected (0.26 sec) ... Read More

Yes, primary key is automatically indexed in MySQL because primary key, index, etc gets stored into B-trees. All engines including InnoDB as well as MyISAM automatically supports the primary key to be indexed. The primary key is implicitly indexed in InnoDB, MyISAM, and other engines. Let us create a table with primary key − mysql> create table DemoIndex -> ( -> Id int not null, -> primary key(Id) -> ); Query OK, 0 rows affected (1.21 sec) In the above table, Id is implicitly indexed.

To get the count of all the records in MySQL tables, we can use TABLE_ROWS with aggregate function SUM. The syntax is as follows. SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDatabaseName'; Apply the above syntax in order to get the count of records for all tables. The query is as follows − mysql> SELECT SUM(TABLE_ROWS) ->FROM INFORMATION_SCHEMA.TABLES ->WHERE TABLE_SCHEMA = 'business'; The following table returns the count of records. +-----------------+ | SUM(TABLE_ROWS) | +-----------------+ | ... Read More

To count the number of occurrences of a string in a VARCHAR, we can use the logic of subtraction with length. First, we will create a table with the help of create command. mysql> create table StringOccurrenceDemo -> ( -> Cases varchar(100), -> StringValue varchar(500) -> ); Query OK, 0 rows affected (0.56 sec) After executing the above table, we will insert records into the table. The query is as follows − mysql> insert into StringOccurrenceDemo values('First', 'This is MySQL Demo and MySQL is ... Read More

The active or total connection can be known with the help of threads_connected variable. The variable tells about the number of currently open connections. The query is as follows − mysql> show status where `variable_name` = 'Threads_connected'; Here is the output. +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 row in set (0.06 sec) We can check the same with the help of show command. The query is as follows − mysql> show processlist; Here is the output. +----+-----------------+-----------------+----------+---------+--------+------------------------+------------------+ ... Read More

The MySQL JDBC connection string look like this − Class.forName("com.mysql.jdbc.Driver"); Above, Driver is an interface. Whenever your JDBC is running outside an application server, then the class DriverManager establish the connection. The DriverManager class is as follows − conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/yourdatabaseName",”yourRootName","yourPassword"); Now, I am applying the above connection string to connect Java to MySQL database. The code is as follows. The following is the output that shows that MySQL connection with Java is successful.

First, we will create a table and insert some values into the table. Let us create a table. mysql> create table RowValueDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.69 sec) Insert records using the insert command. We have added duplicate values as well for our example. mysql> insert into RowValueDemo values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into RowValueDemo values('Bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into RowValueDemo values('Carol'); Query OK, 1 row affected ... Read More