SQL Server 数据库设计与应用：个人简历管理系统

本项目使用 SQL Server 数据库设计并实现了一个简单的个人简历管理系统，包括数据库设计、存储过程、触发器、数据库安全等内容。该系统具有录入、浏览、查询、删除、修改、增加个人简历信息等功能，并提供 Java 代码实现用户界面交互。

1. 数据库设计

CREATE TABLE dbo.person (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    birthdate DATE NOT NULL,
    id_number VARCHAR(18) NOT NULL
);

CREATE TABLE dbo.contact (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    email VARCHAR(50)
);

2. 数据导入

id	name	gender	birthdate	id_number	phone	email
1003	'本紫'	'男'	1990-01-01	410211199901011234	521	sutt@qq.com
1004	'李兰'	'女'	1987-12-12	320602198712121234	520	syt@qq.com
1005	'易祺瑞'	'男'	1985-11-11	440308198511111234	519	sew@qq.com
1006	'周笑临'	'男'	1978-12-09	530112197812091234	518	yut@qq.com
1007	'余囡'	'女'	1966-03-30	620402196603301234	517	yyuyu'yyuyyuy@qq.com

3. 存储过程

3.1 添加个人信息存储过程

CREATE PROCEDURE dbo.add_person_info
    @name VARCHAR(50),
    @gender VARCHAR(10),
    @birthdate DATE,
    @id_number VARCHAR(18)
AS
BEGIN
    INSERT INTO dbo.person (name, gender, birthdate, id_number)
    VALUES (@name, @gender, @birthdate, @id_number);
END;
GO

3.2 更新个人信息存储过程

CREATE PROCEDURE dbo.update_person_info
    @id INT,
    @name VARCHAR(50) = NULL,
    @gender VARCHAR(10) = NULL,
    @birthdate DATE = NULL,
    @id_number VARCHAR(18) = NULL
AS
BEGIN
    UPDATE dbo.person
    SET name = ISNULL(@name, name),
        gender = ISNULL(@gender, gender),
        birthdate = ISNULL(@birthdate, birthdate),
        id_number = ISNULL(@id_number, id_number)
    WHERE id = @id;
END;
GO

3.3 删除个人信息存储过程

CREATE PROCEDURE dbo.delete_person_info
    @id INT
AS
BEGIN
    DELETE FROM dbo.person WHERE id = @id;
END;
GO

4. 触发器

4.1 添加个人信息触发器

CREATE TRIGGER dbo.tr_person_insert
ON dbo.person
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.contact (id, phone, email)
    SELECT id, NULL, NULL FROM inserted;
END;
GO

4.2 更新个人信息触发器

CREATE TRIGGER dbo.tr_person_update
ON dbo.person
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.contact
    SET phone = ISNULL(inserted.phone, contact.phone),
        email = ISNULL(inserted.email, contact.email)
    FROM dbo.contact
    JOIN inserted ON contact.id = inserted.id;
END;
GO

4.3 删除个人信息触发器

CREATE TRIGGER dbo.tr_person_delete
ON dbo.person
AFTER DELETE
AS
BEGIN
    DELETE FROM dbo.contact WHERE id IN (SELECT id FROM deleted);
END;
GO

5. 数据库安全

5.1 创建 SQL Server 身份验证登录名

在 SQL Server 中创建 SQL Server 身份验证登录名“SQLUser01”，密码为“abc@123”，默认数据库为你的数据库。

5.2 创建数据库用户账户

在本数据库中创建 SQL Server 身份验证数据库用户账户“db_user01”，并将其映射到登录名“SQLUser01”。

5.3 创建自定义角色

创建自定义角色“role01”，并且将数据库用户“db_user01”添加为该自定义角色的成员。

5.4 授予权限

为角色“role01”授予以下权限：

select, insert, update, delete on dbo.person
select, insert, update, delete on dbo.contact

5.5 收回权限

从角色“role01”收回以下权限：

insert on dbo.contact

6. Java 代码

import javax.swing.*;
import javax.swing.border.EmptyBorder;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.Scanner;

public class ResumeSystem {
    private static final String DB_URL = "jdbc:sqlserver://localhost:1433;databaseName=resume_db";
    private static final String USER = "sa";
    private static final String PASSWORD = "password";

    private static JFrame frame;
    private static JPanel panel;
    private static JTextArea textArea;

    public static void main(String[] args) {
        try {
            UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
        } catch (Exception e) {
            e.printStackTrace();
        }

        frame = new JFrame("个人简历管理系统");
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(800, 600);

        panel = new JPanel(new BorderLayout());
        panel.setBorder(new EmptyBorder(20, 20, 20, 20));

        textArea = new JTextArea();
        textArea.setEditable(false);
        textArea.setFont(new Font("宋体", Font.PLAIN, 16));
        JScrollPane scrollPane = new JScrollPane(textArea);
        panel.add(scrollPane, BorderLayout.CENTER);

        JMenuBar menuBar = new JMenuBar();
        frame.setJMenuBar(menuBar);

        JMenu menuFile = new JMenu("文件");
        menuBar.add(menuFile);

        JMenuItem menuItemExit = new JMenuItem("退出");
        menuItemExit.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                System.exit(0);
            }
        });
        menuFile.add(menuItemExit);

        JMenu menuResume = new JMenu("个人简历");
        menuBar.add(menuResume);

        JMenuItem menuItemInsert = new JMenuItem("录入");
        menuItemInsert.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    insertResume(getConnection(), new Scanner(System.in));
                    viewResume(getConnection());
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemInsert);

        JMenuItem menuItemView = new JMenuItem("浏览");
        menuItemView.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    viewResume(getConnection());
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemView);

        JMenuItem menuItemQuery = new JMenuItem("查询");
        menuItemQuery.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    queryResume(getConnection(), new Scanner(System.in));
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemQuery);

        JMenuItem menuItemDelete = new JMenuItem("删除");
        menuItemDelete.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    deleteResume(getConnection(), new Scanner(System.in));
                    viewResume(getConnection());
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemDelete);

        JMenuItem menuItemUpdate = new JMenuItem("修改");
        menuItemUpdate.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    updateResume(getConnection(), new Scanner(System.in));
                    viewResume(getConnection());
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemUpdate);

        JMenuItem menuItemAdd = new JMenuItem("增加");
        menuItemAdd.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                try {
                    addResume(getConnection(), new Scanner(System.in));
                    viewResume(getConnection());
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        });
        menuResume.add(menuItemAdd);

        frame.setContentPane(panel);
        frame.setVisible(true);
    }
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, USER, PASSWORD);
    }

    // 创建表
    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE resume (" +
                    "id INT IDENTITY(1,1) PRIMARY KEY," +
                    "name VARCHAR(50) NOT NULL," +
                    "gender VARCHAR(10) NOT NULL," +
                    "birthdate DATE NOT NULL," +
                    "id_number VARCHAR(20) NOT NULL UNIQUE," +
                    "phone VARCHAR(20) NOT NULL," +
                    "email VARCHAR(50) NOT NULL");"
            stmt.executeUpdate(sql);
        }
    }

    // 个人简历信息录入
    private static void insertResume(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入姓名：");
        String name = scanner.nextLine();
        System.out.print("请输入性别（男/女）：");
        String gender = scanner.nextLine();
        System.out.print("请输入出生日期（格式为yyyy-MM-dd）：");
        String birthdate = scanner.nextLine();
        System.out.print("请输入身份证号：");
        String idNumber = scanner.nextLine();
        System.out.print("请输入电话号码：");
        String phone = scanner.nextLine();
        System.out.print("请输入电子邮件地址：");
        String email = scanner.nextLine();

        String sql = "INSERT INTO resume (name, gender, birthdate, id_number, phone, email) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, gender);
            pstmt.setDate(3, Date.valueOf(birthdate));
            pstmt.setString(4, idNumber);
            pstmt.setString(5, phone);
            pstmt.setString(6, email);
            pstmt.executeUpdate();
        }
        textArea.setText("个人简历信息录入成功。\n");
    }

    // 个人简历信息浏览
    private static void viewResume(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM resume";
            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                Date birthdate = rs.getDate("birthdate");
                String idNumber = rs.getString("id_number");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                sb.append("ID：").append(id).append("\n");
                sb.append("姓名：").append(name).append("\n");
                sb.append("性别：").append(gender).append("\n");
                sb.append("出生日期：").append(birthdate).append("\n");
                sb.append("身份证号：").append(idNumber).append("\n");
                sb.append("电话号码：").append(phone).append("\n");
                sb.append("电子邮件地址：").append(email).append("\n\n");
            }
            textArea.setText(sb.toString());
        }
    }

    // 按身份证号查询信息
    private static void queryResume(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要查询信息的身份证号：");
        String idNumber = scanner.nextLine();
        String sql = "SELECT * FROM resume WHERE id_number = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, idNumber);
            ResultSet rs = pstmt.executeQuery();
            StringBuilder sb = new StringBuilder();
            if (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                Date birthdate = rs.getDate("birthdate");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                sb.append("ID：").append(id).append("\n");
                sb.append("姓名：").append(name).append("\n");
                sb.append("性别：").append(gender).append("\n");
                sb.append("出生日期：").append(birthdate).append("\n");
                sb.append("身份证号：").append(idNumber).append("\n");
                sb.append("电话号码：").append(phone).append("\n");
                sb.append("电子邮件地址：").append(email).append("\n\n");
            } else {
                sb.append("身份证号对应的个人简历信息不存在。\n");
            }
            textArea.setText(sb.toString());
        }
    }

    // 个人简历信息删除
    private static void deleteResume(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要删除信息的身份证号：");
        String idNumber = scanner.nextLine();
        String sql = "DELETE FROM resume WHERE id_number = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, idNumber);
            int count = pstmt.executeUpdate();
            if (count > 0) {
                textArea.setText("个人简历信息删除成功。\n");
            } else {
                textArea.setText("身份证号对应的个人简历信息不存在。\n");
            }
        }
    }

    // 个人简历信息修改
    private static void updateResume(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要修改信息的身份证号：");
        String idNumber = scanner.nextLine();
        String sql = "SELECT * FROM resume WHERE id_number = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, idNumber);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.print("请输入要修改的项（姓名(name)/性别(gender)/出生日期(birthdate)/电话号码(phone)/电子邮件地址(email)）：");
                String field = scanner.nextLine();
                System.out.print("请输入要修改的值：");
                String value = scanner.nextLine();

                String updateSql = "UPDATE resume SET " + field + " = ? WHERE id_number = ?";
                try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                    updateStmt.setString(1, value);
                    updateStmt.setString(2, idNumber);
                    updateStmt.executeUpdate();
                }
                textArea.setText("个人简历信息修改成功。\n");
            } else {
                textArea.setText("身份证号对应的个人简历信息不存在。\n");
            }
        }
    }

    // 增加个人简历信息
    private static void addResume(Connection conn, Scanner scanner) throws SQLException {
        System.out.print("请输入要增加信息的身份证号：");
        String idNumber = scanner.nextLine();
        String sql = "SELECT * FROM resume WHERE id_number = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, idNumber);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.print("请输入要增加的项（姓名(name)/性别(gender)/出生日期(birthdate)/电话号码(phone)/电子邮件地址(email)）：");
                String field = scanner.nextLine();
                System.out.print("请输入要增加的值：");
                String value = scanner.nextLine();

                String updateSql = "UPDATE resume SET " + field + " = ? WHERE id_number = ?";
                try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                    updateStmt.setString(1, value);
                    updateStmt.setString(2, idNumber);
                    updateStmt.executeUpdate();
                }
                System.out.println("个人简历信息增加成功。");
            } else {
                System.out.println("身份证号对应的个人简历信息不存在。");
            }
        }
    }
}

7. 调用存储过程

// 建立数据库连接
Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

// 创建CallableStatement对象
String sql = "{CALL dbo.add_person_info(?,?,?,?)} ";
try (CallableStatement cs = conn.prepareCall(sql)) {
    // 设置参数值
    cs.setString(1, "张三");
    cs.setString(2, "男");
    cs.setDate(3, Date.valueOf("1990-01-01"));
    cs.setString(4, "110101199001010001");

    // 执行存储过程
    cs.executeUpdate();
}

// 关闭数据库连接
conn.close();

8. 总结

本项目使用 SQL Server 数据库设计并实现了一个简单的个人简历管理系统，涵盖了数据库设计、存储过程、触发器、数据库安全等方面的内容，并提供了 Java 代码实现用户界面交互。该项目可以帮助学习者深入了解 SQL Server 数据库的基本操作和应用。

注意：

请根据实际情况修改数据库连接信息、用户名和密码。
本项目仅供学习参考，实际应用中可能需要根据具体需求进行调整和扩展。