Java操作数据库、表笔记
1.创建数据库 创建数据库的步骤如下:
- 导入所需的Java数据库连接驱动(如MySQL驱动)。
- 使用JDBC连接到数据库。
- 执行SQL语句创建数据库。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateDatabaseExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/";
String username = "root";
String password = "your_password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "CREATE DATABASE mydatabase";
statement.executeUpdate(sql);
System.out.println("Database created successfully");
statement.close(); connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }
2.查询数据库 查询数据库的步骤如下:
- 使用JDBC连接到数据库。
- 执行SQL查询语句并获取结果。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class QueryDatabaseExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root"; String password = "your_password";
try { Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM mytable";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) { int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("ID: " + id + ", Name: " + name); }
resultSet.close();
statement.close();
connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }
3.备份恢复数据库 备份和恢复数据库的步骤如下:
- 使用JDBC连接到数据库。
- 执行SQL备份或恢复语句。
代码示例(备份数据库):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class BackupDatabaseExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root"; String password = "your_password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "BACKUP DATABASE mydatabase TO 'C:\\backup.sql'";
statement.executeUpdate(sql);
System.out.println("Database backup created successfully");
statement.close();
connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }
代码示例(恢复数据库):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement; public class RestoreDatabaseExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/";
String username = "root"; String password = "your_password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "RESTORE DATABASE mydatabase FROM 'C:\\backup.sql'";
statement.executeUpdate(sql);
System.out.println("Database restored successfully");
statement.close();
connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }
4.创建表 创建表的步骤如下:
- 使用JDBC连接到数据库。
- 执行SQL语句创建表。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement; public class CreateTableExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "your_password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "CREATE TABLE mytable (id INT PRIMARY KEY, name VARCHAR(50))";
statement.executeUpdate(sql);
System.out.println("Table created successfully");
statement.close();
connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }
5.修改表 修改表的步骤如下:
- 使用JDBC连接到数据库。
- 执行SQL语句修改表结构。
代码示例(添加列):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement; public class AlterTableExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "your_password";
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "ALTER TABLE mytable ADD COLUMN age INT";
statement.executeUpdate(sql);
System.out.println("Table altered successfully"); statement.close();
connection.close(); }
catch (Exception e) { e.printStackTrace(); } } }