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
MySQL Articles
Page 316 of 355
List of non-empty tables in all your MySQL databases?
To list non-empty tables in MySQL database, use “information_schema.tables”. The following is the query for all database tables −mysql> select table_type, table_name from information_schema.tables −> where table_rows >= 1;Above, we have considered only the table that have 1 or more than 1 rows i.e. non-empty table.The following is the output −+------------+------------------------------------------------------+ | TABLE_TYPE | TABLE_NAME | +------------+------------------------------------------------------+ | BASE TABLE | innodb_table_stats ...
Read MoreDisplay all tables inside a MySQL database using Java?
We will see here how to display all tables inside a MySQL database using Java. You can use show command from MySQL to get all tables inside a MySQL database.Let’s say our database is ‘test’. The Java code is as follows to show all table names inside a database ‘test’.The Java code is as follows. Here, connection is established between MySQL and Java −import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.DatabaseMetaData; public class GetAllTables { public static void main(String[] args) throws SQLException { Connection conn = null; try { ...
Read MoreSelect last 20 records ordered in ascending order in MySQL?
To select last 20 records in ascending order, you can use subquery LIMIT clause. The syntax is as followsSELECT *FROM ( SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20 ) anyVariableName order by anyVariableName.yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ProductInformation -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into ProductInformation values(101, 'Product-1', ...
Read MoreHow to Auto Generate Database Diagram in MySQL?
To auto generate database diagram in MySQL, use MySQL workbench. For this, select the option from database as shown below −Database->Reverse EngineerHere is the snapshot showing the Database tab −After clicking “Database” above, choose the option “Reverse Engineer”. This state the “Reverse Engineer” mode.Clicking above will display the following table automatically. We had one table in the database “business”. The same table “tblstudent” is visible here −
Read MoreHow can we use nested transactions in MySQL?
We can work with nested transactions in MySQL with the help of SAVEPOINT.Firstly, create a table. After that, begin the transaction.Now, insert records in the table created above. Use SAVEPOINT statement to set a named transaction savepoint with a name of identifier.Here are all the steps shown in the form of query −Create tablemysql> create table NestedTransactionDemo -> ( -> Name varchar(200) -> ); Query OK, 0 rows affected (0.63 sec)Start the transaction −mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)Now, insert a record in the tablemysql> insert into ...
Read MoreHow can I merge two MySQL tables?
To merge two MySQL tables, use the following syntax −INSERT IGNORE INTO yourTableName1 select *from yourTableName2;We will create two tables with some records. After that the merge process will begin using the above syntax.Creating first table −mysql> create table MergeDemo1 -> ( -> id int, -> primary key(id), -> Name varchar(200) -> ); Query OK, 0 rows affected (1.00 sec)Inserting records into the table −mysql> insert into MergeDemo1 values(1, 'John'); Query OK, 1 row affected (0.21 sec)Displaying records from the tablemysql> select *from MergeDemo1;The following ...
Read MoreHow to conduct an Accent Sensitive search in MySQL?
To conduct an Accent sensitive search in MySQL, we can use collation with utf8_bin. Here is the syntax to conduct accent sensitive search −yourColumName dataType collate utf8_bin;Apply the above syntax to conduct accent sensitive search. First, let us create a table −mysql> create table AccentSearchDemo -> ( -> Id varchar(100) collate utf8_bin -> ); Query OK, 0 rows affected (0.51 sec)Inserting three records into the table −mysql> insert into AccentSearchDemo values('John123'); Query OK, 1 row affected (0.31 sec) mysql> insert into AccentSearchDemo values('Smith123'); Query OK, 1 row affected (0.15 sec) mysql> ...
Read MoreHow to find the MySQL data directory from command line in Windows?
To find the MySQL data directory, we can simply use the variable datadir. Let us see how to use the variable with select statement.The query is as follows −mysql> select @@datadir;Here is the output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now we can reach the directory from the above sample output.Here is the snapshot that displays the MySQL data directory.
Read MoreHow to take MySQL database backup using MySQL Workbench?
To make MySQL database backup using MySQL, first we need to install MySQL Workbench. Follow the below link to install MySQL workbench.https://dev.mysql.com/downloads/windows/installer/8.0.htmlAfter installing successfully, we need to open MySQL Workbench. Choose the option “Data Export”. Here is the snapshot.Select the database you want to export. You can also set the path here where you want to save the database.After successful completion, you can see the following screenshot.You have successfully created a backup of the above database. The following message is now visible.Export of E:\BackupDatabase has finished
Read MoreWhat are some good tools to visualize MySQL database schema?
There are many tools to visualize MySQL database schema. Let us see some of them −SchemaSpyThis tool is based on java and can be used to analyze the metadata of MySQL database schema. Also use it to generate a visual representation of schema. A type of command line tool.The following are the featuresSupports most JDBC compliant DBMSGenerates ER diagram for foreign keysGenerates ER diagram for implied relationships (name, type) of a column matches a primary keyGenerates ER diagram for relationships based on rails naming conventionsShows column relationship and actionsShows routinesSchemaCrawlerThis is also a tool and an API that can be ...
Read More