导入测试数据test.sql

-- 请执行以下语句导入数据库:
-- mysql -uroot -p123456 < test.sql

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
USE test;

DROP TABLE IF EXISTS employees,departments,dept_manager,dept_emp,salaries;

CREATE TABLE employees (
    emp_no      INT             NOT NULL COMMENT '主键',
    birth_date  DATE            NOT NULL COMMENT '生日',
    first_name  VARCHAR(14)     NOT NULL COMMENT '用户-姓',
    last_name   VARCHAR(16)     NOT NULL COMMENT '用户-名',
    gender      ENUM ('M','F')  NOT NULL COMMENT '性别',
    hire_date   DATE            NOT NULL COMMENT '入职时间',
    PRIMARY KEY (emp_no)
);

CREATE TABLE departments (
    dept_no     CHAR(32)        NOT NULL COMMENT '主键',
    dept_name   VARCHAR(40)     NOT NULL COMMENT '部门名称',
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL COMMENT '主键',
   dept_no      CHAR(4)         NOT NULL COMMENT '主键',
   from_date    DATE            NOT NULL COMMENT '开始时间',
   to_date      DATE            NOT NULL COMMENT '结束时间',
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL COMMENT '主键',
    dept_no     CHAR(4)         NOT NULL COMMENT '主键',
    from_date   DATE            NOT NULL COMMENT '开始时间',
    to_date     DATE            NOT NULL COMMENT '结束时间',
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

CREATE TABLE salaries (
    emp_no      INT             NOT NULL COMMENT '主键',
    salary      INT             NOT NULL COMMENT '薪水',
    from_date   DATE            NOT NULL COMMENT '主键',
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
);

INSERT INTO `departments` VALUES
('d001','市场部'),
('d002','财务部'),
('d003','人事部'),
('d004','生产部'),
('d005','研发部'),
('d006','质量部'),
('d007','销售部'),
('d008','销售服务部');

INSERT INTO `employees` VALUES 
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
(10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
(10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
(10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
(10014,'1956-02-12','Berni','Genin','M','1987-03-11'),
(10015,'1959-08-19','Guoxiang','Nooteboom','M','1987-07-02'),
(10016,'1961-05-02','Kazuhito','Cappelletti','M','1995-01-27'),
(10017,'1958-07-06','Cristinel','Bouloucos','F','1993-08-03'),
(10018,'1954-06-19','Kazuhide','Peha','F','1987-04-03'),
(10019,'1953-01-23','Lillian','Haddadi','M','1999-04-30'),
(10020,'1952-12-24','Mayuko','Warwick','M','1991-01-26'),
(10021,'1960-02-20','Ramzi','Erde','M','1988-02-10'),
(10022,'1952-07-08','Shahaf','Famili','M','1995-08-22'),
(10023,'1953-09-29','Bojan','Montemayor','F','1989-12-17'),
(10024,'1958-09-05','Suzette','Pettey','F','1997-05-19'),
(10025,'1958-10-31','Prasadram','Heyers','M','1987-08-17'),
(10026,'1953-04-03','Yongqiao','Berztiss','M','1995-03-20'),
(10027,'1962-07-10','Divier','Reistad','F','1989-07-07'),
(10028,'1963-11-26','Domenick','Tempesti','M','1991-10-22'),
(10029,'1956-12-13','Otmar','Herbst','M','1985-11-20'),
(10030,'1958-07-14','Elvis','Demeyer','M','1994-02-17');

INSERT INTO `dept_emp` VALUES 
(10001,'d005','1986-06-26','9999-01-01'),
(10002,'d007','1996-08-03','9999-01-01'),
(10003,'d004','1995-12-03','9999-01-01'),
(10004,'d004','1986-12-01','9999-01-01'),
(10005,'d003','1989-09-12','9999-01-01'),
(10006,'d005','1990-08-05','9999-01-01'),
(10007,'d008','1989-02-10','9999-01-01'),
(10008,'d005','1998-03-11','2000-07-31'),
(10009,'d006','1985-02-18','9999-01-01'),
(10010,'d004','1996-11-24','2000-06-26'),
(10010,'d006','2000-06-26','9999-01-01'),
(10011,'d007','1990-01-22','1996-11-09'),
(10012,'d005','1992-12-18','9999-01-01'),
(10013,'d003','1985-10-20','9999-01-01'),
(10014,'d005','1993-12-29','9999-01-01'),
(10015,'d008','1992-09-19','1993-08-22'),
(10016,'d007','1998-02-11','9999-01-01'),
(10017,'d001','1993-08-03','9999-01-01'),
(10018,'d004','1992-07-29','9999-01-01'),
(10018,'d005','1987-04-03','1992-07-29'),
(10019,'d008','1999-04-30','9999-01-01'),
(10020,'d002','1997-12-30','9999-01-01'),
(10021,'d005','1988-02-10','2002-07-15'),
(10022,'d005','1999-09-03','9999-01-01'),
(10023,'d005','1999-09-27','9999-01-01'),
(10024,'d004','1998-06-14','9999-01-01'),
(10025,'d005','1987-08-17','1997-10-15'),
(10026,'d004','1995-03-20','9999-01-01'),
(10027,'d005','1995-04-02','9999-01-01'),
(10028,'d005','1991-10-22','1998-04-06'),
(10029,'d004','1991-09-18','1999-07-08'),
(10029,'d006','1999-07-08','9999-01-01'),
(10030,'d004','1994-02-17','9999-01-01');

INSERT INTO `dept_manager` VALUES
(10017,'d001','1985-01-01','1991-10-01'),
(10020,'d002','1991-10-01','9999-01-01'),
(10013,'d003','1985-01-01','1989-12-17'),
(10018,'d004','1989-12-17','9999-01-01'),
(10018,'d005','1985-01-01','1992-03-21'),
(10029,'d006','1992-03-21','9999-01-01'),
(10016,'d007','1985-01-01','1988-09-09'),
(10007,'d008','1988-09-09','1992-08-02');

INSERT INTO `salaries` VALUES 
(10001,5000,'1986-06-26','9999-01-01'),
(10002,5100,'1996-08-03','9999-01-01'),
(10003,5200,'1995-12-03','9999-01-01'),
(10004,5300,'1986-12-01','9999-01-01'),
(10005,5400,'1989-09-12','9999-01-01'),
(10006,5500,'1990-08-05','9999-01-01'),
(10007,5600,'1989-02-10','9999-01-01'),
(10008,5700,'1998-03-11','2000-07-31'),
(10009,5800,'1985-02-18','9999-01-01'),
(10010,5900,'1996-11-24','2000-06-26'),
(10010,6000,'2000-06-26','9999-01-01'),
(10011,6100,'1990-01-22','1996-11-09'),
(10012,5000,'1992-12-18','9999-01-01'),
(10013,5100,'1985-10-20','9999-01-01'),
(10014,5200,'1993-12-29','9999-01-01'),
(10015,5300,'1992-09-19','1993-08-22'),
(10016,5400,'1998-02-11','9999-01-01'),
(10017,5500,'1993-08-03','9999-01-01'),
(10018,5600,'1992-07-29','9999-01-01'),
(10018,5700,'1987-04-03','1992-07-29'),
(10019,5800,'1999-04-30','9999-01-01'),
(10020,5900,'1997-12-30','9999-01-01'),
(10021,6000,'1988-02-10','2002-07-15'),
(10022,6100,'1999-09-03','9999-01-01'),
(10023,5000,'1999-09-27','9999-01-01'),
(10024,5100,'1998-06-14','9999-01-01'),
(10025,5200,'1987-08-17','1997-10-15'),
(10026,5300,'1995-03-20','9999-01-01'),
(10027,5400,'1995-04-02','9999-01-01'),
(10028,5500,'1991-10-22','1998-04-06'),
(10029,5600,'1991-09-18','1999-07-08'),
(10029,5700,'1999-07-08','9999-01-01'),
(10030,5800,'1994-02-17','9999-01-01');
package com.bjsxt.servlet; import com.bjsxt.entity.User; import com.bjsxt.service.UserService; import com.bjsxt.service.impl.UserServiceImpl; import javax.servlet.RequestDispatcher; import javax.servlet.ServletContext; import javax.servlet.ServletException; import javax.servlet.http.*; import java.io.IOException; import java.net.URLEncoder; import java.sql.Date; import java.util.ArrayList; import java.util.Arrays; import java.util.List; public class UserServlet extends BaseServlet { // @Override // protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // //解决POST表单的中文乱码问题 // request.setCharacterEncoding("utf-8"); // //接收method属性的值 // String methodName = request.getParameter("method"); // // //根据method属性的值调用相应的方法 // if("login".equals(methodName)){ // this.login(request,response); // }else if("register".equals(methodName)){ // this.register(request,response); // }else if("logout".equals(methodName)){ // this.logout(request,response); // } // // } public void show(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //获取表单的数据 String userId = request.getParameter("userId"); if(userId == null){ userId = ""; } String strAge = request.getParameter("minAge"); int minAge = 0; try{ minAge = Integer.parseInt(strAge); //"12" "abc" }catch(NumberFormatException e){ e.printStackTrace(); } //调用业务层完成查询操作 UserService userService = new UserServiceImpl(); //List<User> userList = userService.findAll(); List<User> userList = userService.find(userId,minAge); //List<User> userList = null; //List<User> userList = new ArrayList<User>(); //跳转到show.jsp显示数据 request.setAttribute("userId",userId); request.setAttribute("minAge",strAge); request.setAttribute("ulist",userList); request.getRequestDispatcher("/admin/show.jsp").forward(request,response); } public void logout(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //结束当前的session request.getSession().invalidate(); //跳转回登录页面 response.sendRedirect(request.getContextPath()+"/admin/login.jsp"); } public void register(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //request.setCharacterEncoding("utf-8"); //1.接收来自视图层的表单数据 String userId = request.getParameter("userId"); String realName = request.getParameter("realName"); String pwd = request.getParameter("pwd"); String rePwd = request.getParameter("repwd"); int age = Integer.parseInt(request.getParameter("age"));// "23" String [] hobbyArr = request.getParameterValues("hobby"); String strDate = request.getParameter("enterDate");//"1999-12-23" Date enterDate = Date.valueOf(strDate); //util.Date SimpleDateFormat //判断两次密码是否相同 if(pwd == null || !pwd.equals(rePwd)){ request.setAttribute("error","两次密码必须相同"); request.getRequestDispatcher("/admin/register.jsp").forward(request,response); return; } //2.调用业务层完成注册操作并返回结果 User user = new User(userId,realName,pwd,age, Arrays.toString(hobbyArr),enterDate); UserService userService = new UserServiceImpl(); int n = userService.register(user); //3.根据结果进行页面跳转 if(n>0){ response.sendRedirect(request.getContextPath()+"/admin/login.jsp"); }else{ request.setAttribute("error","注册失败"); request.getRequestDispatcher("/admin/register.jsp").forward(request,response); } } public void login(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //解决POST表单的中文乱码问题 //request.setCharacterEncoding("utf-8"); //获取用户名和密码 request 内建对象 请求 String username = request.getParameter("username"); String password = request.getParameter("password"); String rememberme = request.getParameter("rememberme"); //调用下一层判断登录是否成功,并返回结果 //进行服务器端的表单验证 if(username ==null || "".equals(username)){ request.setAttribute("error","用户名不能为空JSP"); request.getRequestDispatcher("/admin/login.jsp").forward(request,response); return; } if (username.length()<=6){ request.setAttribute("error","用户名长度必须大于6JSP"); request.getRequestDispatcher("/admin/login.jsp").forward(request,response);//后面语句还会执行 return; //后面的语句不再执行 } // boolean flag = false;//默认失败 // if(username.indexOf("sxt")>=0 || username.contains("尚学堂")){ // flag = true; // } User user = null;//默认登录失败 // UserDao userDao = new UserDaoImpl(); // user = userDao.find(username,password); UserService userService = new UserServiceImpl(); user = userService.login(username,password); //userService.addOrder("shoppingCart"); //输出结果 if(user != null){ //登录成功才记住我 //1.办理会员卡 String username2 = URLEncoder.encode(username,"utf-8"); Cookie cookie1 = new Cookie("uname",username2); Cookie cookie2 = new Cookie("password",password); //2.指定会员卡的作用范围,默认范围是当前目录 /servlet/LoginServlet /admin/login.jsp //cookie1.setPath("/"); //当前服务器 cookie1.setPath("/myservlet2/"); //当前项目 cookie2.setPath("/myservlet2"); //3.指定会员卡的作用时间 if("yes".equals(rememberme)){ cookie1.setMaxAge(60*60*24*10); //默认的时间浏览器不关闭的时间;-1 表示一直有效 cookie2.setMaxAge(60*60*24*10); }else{ cookie1.setMaxAge(0); cookie2.setMaxAge(0); } //4.将会员卡带回家 response.addCookie(cookie1); response.addCookie(cookie2); //成功跳转到成功页面 //out.println("登录成功"); // /servlet/LoginServlet // /servlet/success.jsp // request.getRequestDispatcher("/admin/success.jsp").forward(request,response); HttpSession session = request.getSession(); // session.setAttribute("username",username); session.setAttribute("user",user); //response.sendRedirect("/myservlet2/admin/success.jsp"); //response.sendRedirect("https://www.bjsxt.com:443/news/11377.html"); //response.sendRedirect("http://localhost:8080/myservlet2/admin/success.jsp"); //response.sendRedirect("/myservlet2/admin/success.jsp"); //response.sendRedirect("/myservlet2/admin/success.jsp"); //response.sendRedirect(request.getContextPath()+"/admin/success.jsp"); //http://192.168.58.250:8080/myservlet2/servlet/LoginServlet //http://192.168.58.250:8080/myservlet2/admin/success.jsp //登录成功后,网站的访问人数+1 //1.获取当前的访问人数 ServletContext context = this.getServletContext(); Integer count2 = (Integer) context.getAttribute("count"); //2.人数+1 if(count2 == null){ //第一个用户 count2 = 1; }else{ count2++; } //3.再存放到application作用域中 context.setAttribute("count",count2); //http://192.168.58.250:8080/myservlet2/servlet/admin/success.jsp response.sendRedirect("../admin/success.jsp"); }else{ //失败跳转回登录页面 //out.println("登录失败"); request.setAttribute("error","用户名或者密码错误"); // RequestDispatcher rd = request.getRequestDispatcher("/admin/login.jsp"); // rd.forward(request,response); //RequestDispatcher rd = request.getRequestDispatcher("http://localhost:8080/myservlet2/admin/login.jsp"); //RequestDispatcher rd = request.getRequestDispatcher("/admin/login.jsp"); //http://192.168.58.250:8080/myservlet2/servlet/admin/login.jsp RequestDispatcher rd = request.getRequestDispatcher("../admin/login.jsp"); rd.forward(request,response); } } }
### DBeaver 中导入 `.sql` 文件教程 #### 设置全局 UTF-8 编码 为了防止导入过程中出现乱码,建议先设置全局编码为 UTF-8。这可以通过修改配置文件或在命令行中指定来实现[^2]。 #### 准备工作 确保已经获取到需要导入的 `.sql` 文件,并确认该文件中的 SQL 语法正确无误。如果不确定,可以提前打开文件查看并修正可能存在的问题,比如缺少分号的情况[^4]。 #### 创建目标数据库 从 `.sql` 文件中找到即将创建的数据库名称,在 DBeaver 中手动建立一个新的数据库实例,并为其设定合适的字符集(通常是 utf8mb4)以及排序规则(collation)。完成这些操作之后再继续下一步骤[^3]。 #### 导入过程 1. **连接至目标数据库** - 打开 DBeaver 并连接到之前新建的目标数据库上。 2. **加载 SQL 脚本** - 可以通过直接将 `.sql` 文件拖拽进入 DBeaver 的查询编辑器区域来进行加载;也可以点击工具栏上的 “Open File” 按钮选择本地磁盘路径下的 `.sql` 文件进行读取。 3. **执行 SQL 命令** - 加载完成后,检查一遍整个脚本的内容是否正常显示出来,特别是注意是否有遗漏掉的分隔符 `;` 。一旦确认无误,则可以直接运行此脚本来完成数据表结构及初始数据记录的构建。 - 若遇到任何报错信息,请仔细阅读错误描述并针对性地调整源代码直至成功为止。 4. **验证结果** - 当所有指令都顺利被执行后,应该能够在右侧的对象浏览器里看到新加入的数据对象列表项。如果没有立即显现的话,试着刷新一次当前会话即可。 ```sql -- 示例:简单的SQL语句片段用于测试目的 CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE test_db; CREATE TABLE example_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age TINYINT UNSIGNED ); INSERT INTO example_table VALUES(NULL, 'Alice', 25), (NULL, 'Bob', 30); SELECT * FROM example_table; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值