mysql学习笔记NO.2

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(); } } }