查找出sql中size最大的table
时间: 2024-03-28 13:42:06 浏览: 29
您可以使用以下SQL查询来查找出SQL数据库中大小最大的表:
```
SELECT table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size(MB)`
FROM information_schema.TABLES
WHERE table_schema = '[your_database_name]'
ORDER BY (data_length + index_length) DESC
LIMIT 1;
```
请将上述查询中的 `[your_database_name]` 替换为您要查询的数据库名称。该查询将返回一个结果集,其中包含大小最大的表的名称和大小(以MB为单位)。
相关问题
Java MySQL swing 查找功能模糊查找的代码实现
下面是一个简单的Java Swing界面,实现MySQL数据库中模糊查找功能的代码:
```java
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
public class SearchDemo extends JFrame implements ActionListener {
private static final long serialVersionUID = 1L;
private JPanel panel;
private JLabel label;
private JTextField textField;
private JButton btnSearch;
private JTable table;
private DefaultTableModel model;
private Connection conn;
private PreparedStatement pstmt;
private ResultSet rs;
public SearchDemo() {
super("模糊查找");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
// 创建界面组件
panel = new JPanel();
label = new JLabel("查找:");
textField = new JTextField(20);
btnSearch = new JButton("查找");
btnSearch.addActionListener(this);
model = new DefaultTableModel();
table = new JTable(model);
JScrollPane scrollPane = new JScrollPane(table);
// 添加组件到面板
panel.add(label);
panel.add(textField);
panel.add(btnSearch);
add(panel, BorderLayout.NORTH);
add(scrollPane, BorderLayout.CENTER);
// 创建数据库连接
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// 显示界面
setSize(600, 400);
setVisible(true);
}
@Override
public void actionPerformed(ActionEvent e) {
if (e.getSource() == btnSearch) {
// 清空表格数据
model.setRowCount(0);
try {
// 模糊查找
String sql = "SELECT * FROM students WHERE name LIKE ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "%" + textField.getText() + "%");
rs = pstmt.executeQuery();
// 显示查询结果
while (rs.next()) {
Object[] row = { rs.getInt("id"), rs.getString("name"), rs.getInt("age") };
model.addRow(row);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
public static void main(String[] args) {
new SearchDemo();
}
}
```
在上面的代码中,我们创建了一个简单的界面,包括一个标签、一个文本框、一个按钮和一个表格。当用户输入要查找的内容并点击查找按钮时,程序会连接到MySQL数据库,执行模糊查找(使用LIKE语句),并将结果显示在表格中。当用户关闭窗口时,程序会关闭数据库连接。
mysql 查找死锁并解锁
在 MySQL 中,可以通过以下步骤进行死锁查找和解锁:
1. 查找死锁:
```sql
SHOW ENGINE INNODB STATUS;
```
执行以上命令,可以看到类似如下的输出:
```
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-11 10:47:09 0x7f3c9e2a1700
*** (1) TRANSACTION:
TRANSACTION 210885, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 110, OS thread handle 139777748852992, query id 2726972 localhost root updating
UPDATE `users` SET `name`='Alice', `age`=30 WHERE `id`=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`users` trx id 210885 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000020d423d; asc =B=;;
2: len 7; hex 00000000000210; asc ;;
*** (2) TRANSACTION:
TRANSACTION 210886, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 111, OS thread handle 139777748919296, query id 2726973 localhost root update
INSERT INTO `users` (`name`, `age`) VALUES ('Bob', 25)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`users` trx id 210886 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000020d423d; asc =B=;;
2: len 7; hex 00000000000210; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 3 n bits 72 index PRIMARY of table `test`.`users` trx id 210886 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000020d423e; asc =B>;;
*** WE ROLL BACK TRANSACTION (2)
```
在输出中,可以看到 LATEST DETECTED DEADLOCK,其中包含了死锁发生的信息。
2. 解锁:
根据上面的输出,可以看到死锁发生在 `test`.`users` 表中的记录上,可以通过如下命令来解锁这个记录:
```sql
SELECT * FROM `information_schema`.`innodb_locks` WHERE `LOCK_TABLE` = 'users' AND `LOCK_INDEX` = 'PRIMARY' AND `LOCK_TRX_ID` = 210885;
```
上述命令可以查询到锁定了这个记录的事务的 ID 是 210885,接下来可以使用如下命令来杀死这个事务:
```sql
KILL 210885;
```
这样就可以解锁这个记录。需要注意的是,杀死事务可能会导致数据不一致,需要谨慎操作。