Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 269 of 341
How to move data between two tables with columns in different MySQL databases?
For this, you need to use an INSERT SELECT statement. The syntax is as followsINSERT INTO yourDatabaseName1.yourTableName1(yourColumnName1, yourColumnName2, ....N) SELECT yourColumnName1, yourColumnName2, ....N FROM yourdatabaseName2.yourTableName2;Here, I am using the following two databasessampletestLet us create the first table in the “test” databasemysql> use test; Database changed mysql> create table send -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the first table using insert command. The query is as followsmysql> insert into send(Name) values('John'); Query OK, 1 row affected ...
Read MoreDoes MySQL have an expanded output flag similar PostgreSQL?
Yes, you can get expanded out in MySQL using the /G, instead of semicolon(;). The syntax is as followsSELECT *FROM yourTableName\GLet us first create a table as an examplemysql> create table expandedOutputDemo - > ( - > EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > EmployeeName varchar(20), - > EmployeeAge int - > ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into expandedOutputDemo(EmployeeName, EmployeeAge) values('Larry', 27); Query OK, 1 row affected (0.16 sec) mysql> insert into expandedOutputDemo(EmployeeName, EmployeeAge) values('Mike', 29); Query ...
Read MoreCreate a new user with password in MySQL 8?
You need to use CREATE command to create a new user with password in MySQL 8. Let us check the versionmysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.14 sec)The syntax is as follows to create a new user with passwordCREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The following is the syntax to grant all privileges to the created userGRANT ALL ON *.* TO 'yourUserName'@'localhost';Now flush the privileges using flush commandflush privileges; Let us create a new user with the help of the above syntax. The query is as followsmysql> use MySQL; Database ...
Read MoreHow to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery in MySql?
Achieve this with the help of where clause.The syntax is as followsSELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName WHERE ( SELECT COUNT(*) FROM yourTableName )=2;To understand the concept, let us create a table. The query to create a table is as followsmysql> create table CountWithSubqueryDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > EmployeeName varchar(20) - > ); Query OK, 0 rows affected (2.09 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CountWithSubqueryDemo(EmployeeName) values('John'); Query OK, 1 row affected (0.54 sec) mysql> ...
Read MoreHow to select only non - numeric values from varchar column in MySQL?
You need to use REGEXP for this. The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName REGEXP '[a-zA-Z]';To understand the concept, let us create a table. The query to create a table is as followsmysql> create table SelectNonNumericValue -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserId varchar(100) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into SelectNonNumericValue(UserId) values('123John'); Query OK, 1 row affected (0.12 sec) mysql> insert into SelectNonNumericValue(UserId) values('58475Carol98457Taylor24'); Query OK, 1 row affected (0.52 sec) ...
Read MoreIs it mandatory to close JDBC connections?
At the end of your JDBC program, it is required explicitly to close all the connections to the database to end each database session. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.Relying on the garbage collection, especially in database programming, is a very poor programming practice. You should make a habit of always closing the connection with the close() method associated with connection object.To ensure that a connection is closed, you could provide a 'finally' block in your code. A finally block always executes, regardless of an exception occurs or not.To ...
Read MoreWhat are the advantages and limitations of JDBC PreparedStatement?
Following are the advantages of the prepared statement:By avoiding multiple compilation and execution of statements, prepared statements perform faster.Using prepared statements, we can insert values to advanced datatypes such as BLOB, CLOB, OBJECT easily with the help of the setter methods provided by the PreparedStatement interface.By providing setter method to set values prepared statement avoids the use of quotes and other special characters with in the query, and thereby it escapes the SQL injection attacksFollowing are the limitations of prepared statements:Since a PreparedStatement object represents only one SQL statement at a time, we can execute only one statement by one ...
Read MoreCount multiple occurrences of separate texts in MySQL?
You can use aggregate function count along with if() for this. To understand the concept, let us create a table. The query to create a table is as followsmysql> create table CountOccurrencesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> TechnicalSubject varchar(100) -> ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command. The query is as followsmysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MongoDB'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read MoreWhat is ResultSetMetaData in JDBC? What is its significance?
The ResultSetMetaData provides information about the obtained ResultSet object like, the number of columns, names of the columns, datatypes of the columns, name of the table etc…Following are some methods of ResultSetMetaData class.MethodDescriptiongetColumnCount()Retrieves the number of columns in the current ResultSet object.getColumnLabel()Retrieves the suggested name of the column for use.getColumnName()Retrieves the name of the column.getTableName()Retrieves the name of the table.Exampleimport java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class ResultSetMetadataExample { public static void main(String args[]) throws Exception { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection ...
Read MoreWhat is the difference between execute(), executeQuery() and executeUpdate() methods in JDBC?
Once you have created the statement object you can execute it using one of the execute methods of the Statement interface namely, execute(), executeUpdate() and, executeQuery().The execute() method: This method is used to execute SQL DDL statements, it returns a boolean value specifying weather the ResultSet object can be retrieved.Exampleimport java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Example { public static void main(String args[]) throws SQLException { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/sampleDB"; Connection con = ...
Read More