利用MySQL创建一个简单的employee员工表并修改表

-- 使用day14数据库
USE day14;

-- 查看表
SHOW TABLES;

-- 创建一个员工表
CREATE TABLE employee(id INT,NAME VARCHAR(20),gender CHAR(2),birthday DATE,email CHAR(10),remark VARCHAR(50));

-- 查看employee表
DESC employee;

-- 修改表
-- 在员工表基础上增加age列
ALTER TABLE employee ADD COLUMN age CHAR(3);

-- 修改email列长度为50
ALTER TABLE employee MODIFY COLUMN email VARCHAR(50);

-- 删除remark列
ALTER TABLE employee DROP COLUMN remark;

--列名NAME修改为username
ALTER TABLE employee CHANGE COLUMN NAME username VARCHAR(30);

好的,我可以为您提供相关的代码示例来实现您所描述的员工信息系统的基本增删改查功能。以下是一个简单的示例代码: ```java import java.sql.*; public class EmployeeSystem { // 数据库连接信息 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/employee"; static final String USER = "root"; static final String PASS = "password"; // 增加员工信息 public static void addEmployee(int id, String name, String position, double salary) { Connection conn = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql = "INSERT INTO employees(id, name, position, salary) VALUES(" + id + ",'" + name + "','" + position + "'," + salary + ")"; stmt.executeUpdate(sql); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } // 删除员工信息 public static void deleteEmployee(int id) { Connection conn = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql = "DELETE FROM employees WHERE id=" + id; stmt.executeUpdate(sql); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } // 更新员工信息 public static void updateEmployee(int id, String name, String position, double salary) { Connection conn = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql = "UPDATE employees SET name='" + name + "',position='" + position + "',salary=" + salary + " WHERE id=" + id; stmt.executeUpdate(sql); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } // 查询员工信息 public static void queryEmployee() { Connection conn = null; Statement stmt = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(); String sql = "SELECT * FROM employees"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String position = rs.getString("position"); double salary = rs.getDouble("salary"); System.out.println("ID: " + id + ", Name: " + name + ", Position: " + position + ", Salary: " + salary); } rs.close(); } catch (SQLException se) { se.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } } public static void main(String[] args) { // 增加员工信息 addEmployee(1, "Tom", "Engineer", 5000.0); addEmployee(2, "Jerry", "Manager", 8000.0); queryEmployee(); // 更新员工信息 updateEmployee(2, "Jerry Smith", "Senior Manager", 10000.0); queryEmployee(); // 删除员工信息 deleteEmployee(1); queryEmployee(); } } ``` 以上代码演示了如何使用JDBC连接MySQL数据库,并实现了基本的增删改查功能。您可以根据实际需求进行修改和扩展。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值