sql server 查询结果新建表 sql server新建查询菜单在哪

转载

小题大作 2023-09-24 20:08:00

文章标签 sql server 查询结果新建表数据库 java jsp sql 文章分类 SQL Server 数据库

前言：刚接触编程，所以实现方式比较简单，将数据库连接等Java语言都写在了JSP页面

实现功能：

1，分增加页面，修改页面，列表页面

2，增加页面能往数据库中插入一条记录

列表页面能查出所有记录，能删除记录，选中记录后能进入编辑页面

列表页面能按条件查询记录

3，数据类型不能单一

1，在SQLserver里创建一个表：

—→生成Stu_DB数据库，在此数据库中生成stuinfo表

--如果该实例中已存在数据库Stu_DB则报错，不存在则创建新的名为Stu_DB的数据库
use master 
go

if exists (select * from sysdatabases where name='Stu_DB')
drop database Stu_DB
go

create database Stu_DB
go

use Stu_DB
go

//创建表
create table stuinfo
(
Stuid int primary key  not null,//primary key主键，identity(1,1)自增长，从1开始+1（主键唯一）not null不能为空
Sname char(10),
Ssex char(2),//check(egendar='1' or egendar='0')限定为两种选择
Sphone int//最后一列不用逗号
)
go


/*暂时没用到
create table stugrade
(
ExamNo int primary key identity(1,1) ,

stuid int references stuinfo(stuid), --外键（学号）

subject char(20),   --科目

score int     --分数
)

go

*/

注意事项：

（1）输入较长的中文字时，数据类型需改为varchar( )[查看：表-右键-设计；修改：表-右键-编写表脚本为- CREATE-新查询编辑器窗口]（？不太记得这个如何使用了，更改方法为：Alter table [usersInfo] Alter column [pwd] [varchar](50)）

（2）查询所有：select * from stuinfo 按条件查询：select Sname,Ssex from stuinfo where Stuid='XXX'

2，实现语句：

（1）输入信息页面：schoolInfo.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 <head>
   <base href="<%=basePath%>">
    
    <title>My JSP 'schoolInfo.jsp' starting page</title>
    
<span style="white-space:pre">	</span><meta http-equiv="pragma" content="no-cache">
<span style="white-space:pre">	</span><meta http-equiv="cache-control" content="no-cache">
<span style="white-space:pre">	</span><meta http-equiv="expires" content="0">    
<span style="white-space:pre">	</span><meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<span style="white-space:pre">	</span><meta http-equiv="description" content="This is my page">
<span style="white-space:pre">	</span><!--
<span style="white-space:pre">	</span><link rel="stylesheet" type="text/css" href="styles.css">
<span style="white-space:pre">	</span>-->
  </head>
  
  <body><form action="schoolInsert.jsp" method="post">
 <table id="students" border="1">  
 <caption>  
 <h3>学生信息</h3> 
 </caption>  
    <tr>  
    <th scope="col">学号</th>  
    <th scope="col">姓名</th>  
    <th scope="col">性别</th> 
    <th scope="col">电话</th> 
    </tr>  
      <tr>  
        <td><input type="text" id="Stuid" name="Stuid"></td>  
        <td><input type="text" id="Sname" name="Sname"></td>  
        <td><input type="text" id="Ssex" name="Ssex"></td> 
        <td><input type="text" id="Sphone" name="Sphone"></td> 
      </tr>  
</table> 
<input type="submit" value="提交" /> 
<input type="button" value="查询" οnclick="javascript:document.location.href='list.jsp';">
</form>
</body>  
</html>

（2）添加页面：schoolInsert.jsp

<%@ page language="java" import="java.util.*"  pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>

<%
  String path = request.getContextPath();
  String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

  request.setCharacterEncoding("UTF-8");
  // 加载数据库驱动
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  // 指定服务器地址、用户、密码
  String URL="jdbc:sqlserver://localhost:1433;DatabaseName=Stu";
  String user="123";
  String password="123";
  // 连接数据库的服务器
  Connection con=DriverManager.getConnection(URL,user,password);
  Statement st=con.createStatement();

  int Stuid=Integer.parseInt(request.getParameter("Stuid"));
  out.println("接收到:"+Stuid);
  String Sname=request.getParameter("Sname");

  String Ssex=request.getParameter("Ssex");

  String Sphone=request.getParameter("Sphone");


   try
	  { 
      String sql = "insert into stuinfo(Stuid,Sname,Ssex,Sphone) values('"+Stuid+"','"+Sname+"','"+Ssex+"','"+Sphone+"')";
      st.executeUpdate(sql);

         }catch(Exception e){
         e.printStackTrace();
                            }finally{
        	                 st.close();
                             con.close();
                                     }
 
 %>

(3)列表页面：查询出所有的数据和按条件查询list.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<html>
  <head>
    <base href="<%=basePath%>">
    <script type="text/javascript">
     function modifyStu()
	     {
             var isMod=confirm("确定要修改该学生信息吗?");    
             if(!isMod)
             return false;
          }
     function deleteStu()
	     {
             var isDel=confirm("确定要删除该学生的信息吗?");   
             if(!isDel)
              return false;
         }
     function go() 
		 {
		     if (FSname==""|| FSname== null) {
			 alert("用户名不能为空！");
			 return false;
		} 
    </script>
  </head>  
   <body> 
   <table border="1" bgcolor="#ffffff" align="center" cellspacing="1" cellpadding="1">
     <caption>学生信息列表</caption>
     <tr>
      <td align="center" width=16%>学号</td>
      <td align="center" width=16%>姓名</td>
      <td align="center" width=8%>性别</td>
      <td align="center" width=8%>电话</td> 
      <td align="center" width=8%>操作</td>
      <td align="center" width=8%>操作</td>
     </tr>    
  <%
   request.setCharacterEncoding("UTF-8");
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String URL="jdbc:sqlserver://localhost:1433;DatabaseName=Stu_DB";
   String user="sa";
   String password="123456";
   Connection con=DriverManager.getConnection(URL,user,password);
   Statement st=con.createStatement();
  
   try{ 
 
   String sql="select * from stuinfo ";
   ResultSet rs=st.executeQuery(sql);
    
   while(rs.next()){
    	
   %>
    
   <tr>
    <td width=16% align="center"><%out.print(rs.getInt(1)); %></td>
    <td width=16% align="center"><%out.print(rs.getString(2));%></td>
    <td width=8%  align="center"><%out.print(rs.getString(3));%></td>
    <td width=8%  align="center"><%out.print(rs.getString(4)); %></td>
    <td width=12% align="center" οnclick="modifyStu()"><a href="change.jsp?Stuid=<%=rs.getInt(1)%>">修改</a></td>
    <td width=12% align="center" οnclick="deleteStu()"><a href="delete.jsp?Stuid=<%=rs.getInt(1)%>">删除</a></td>
   </tr>
   <%}%>
  
   </table>
    <div align="center"><a href="schoolInfo.jsp">添加新记录</a></div> <br><br>
    
    <div align="center">  
       <h5>查询学生信息</h5>
       <form name="Fform" action="" method="post"> 
          <input type="text" id="FSname" name="FSname" value="根据姓名查询" />
          <input type='submit' value='查询' οnclick="go();"/>  
       </form>
    </div>
   <table border="1" bgcolor="#ffffff" align="center" cellspacing="1" cellpadding="1">
     <tr>
      <td align="center" width=16%>学号</td>
      <td align="center" width=16%>姓名</td>
      <td align="center" width=8%>性别</td>
      <td align="center" width=8%>电话</td> 
     </tr> 
     <% 
    request.setCharacterEncoding("UTF-8");  
    String FSname=request.getParameter("FSname");
    String Fsql="select Stuid,Sname,Ssex,Sphone from stuinfo where Sname="+ "'" + FSname+ "'";
    ResultSet Frs=st.executeQuery(Fsql);
      while(Frs.next())
      {%>
   <tr>
    <td width=16% align="center"><%out.print(Frs.getInt(1)); %></td>
    <td width=16% align="center"><%out.print(Frs.getString(2));%></td>
    <td width=8%  align="center"><%out.print(Frs.getString(3));%></td>
    <td width=8%  align="center"><%out.print(Frs.getString(4)); %></td>
   <% }%>
 </table>
  <%
   Frs.close();
   rs.close();
   st.close();
   con.close();
   }catch(Exception e){
    e.printStackTrace();
   }
   %>  
  
  </body>
</html>

(4)删除页面：delete.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
 
  </head>
  
 < 
    try{   
	body>
 <%
  
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String URL="jdbc:sqlserver://localhost:1433;DatabaseName=Stu_DB";
   String user="sa";
   String password="123456";
   Connection con=DriverManager.getConnection(URL,user,password);
   Statement st=con.createStatement();
   
   int Stuid=Integer.parseInt(request.getParameter("Stuid"));
   String Sname=request.getParameter("Sname"); 
   String Ssex=request.getParameter("Ssex"); 
   String Sphone=request.getParameter("Sphone");
  	st.executeUpdate("delete from stuinfo where Stuid='"+Stuid+"'");  
       }catch(Exception e){
       e.printStackTrace();
    }finally{
   	  st.close();
      con.close();
   }
 
 %>
  <input type="button" value="查询是否删除成功" οnclick="javascript:document.location.href='list.jsp';">
</body>
</html>

(5)修改页面：change.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<html>
  <head>

  </head>
 <body>
 <%
    request.setCharacterEncoding("UTF-8");
    String Stuid=request.getParameter("Stuid");
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    String URL="jdbc:sqlserver://localhost:1433;DatabaseName=Stu_DB";
    String user="sa";
    String password="123456";
    Connection con=DriverManager.getConnection(URL,user,password);
    Statement st=con.createStatement();
    ResultSet rs=st.executeQuery("select * from stuinfo where Stuid="+Stuid+""); 
%>
 <h3>将学生信息更改为：</h3>
 <form name="forml" action="change_item.jsp" method="post"> 
 <table id="students" border="1">  
    <tr>  
    <th scope="col">学号</th>  
    <th scope="col">姓名</th>  
    <th scope="col">性别</th> 
    <th scope="col">电话</th> 
    </tr> 
    <%   while(rs.next())  {  %>
      <tr>  
        <td><input type="text" id="Stuid" name="Stuid" readonly="readonly"  value='<%=rs.getString(1)%>'></td>  
        <td><input type="text" id="Sname" name="Sname"   value='<%=rs.getString(2)%>'></td>  
        <td><input type="text" id="Ssex" name="Ssex"    value='<%=rs.getString(3)%>'></td> 
        <td><input type="text" id="Sphone" name="Sphone"  value='<%=rs.getString(4)%>'></td> 
      </tr>  
</table> 
<%} %>
<input type="submit" value="确认修改" /> 
</form>
</body>
</html>

(6)

实现修改功能页面：change_item.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

  </head>
  
  <body>
  
 <%
   request.setCharacterEncoding("UTF-8");
   int Stuid=Integer.parseInt(request.getParameter("Stuid"));
   out.println("接收到:"+Stuid);
   String Sname=request.getParameter("Sname"); 

   String Ssex=request.getParameter("Ssex");
   
   String Sphone=request.getParameter("Sphone");
  
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String URL="jdbc:sqlserver://localhost:1433;DatabaseName=Stu_DB";
   String user="sa";
   String password="123456";
   Connection con=DriverManager.getConnection(URL,user,password);
   Statement st=con.createStatement();
   
     st.executeUpdate("update stuinfo set Sname='"+Sname+"',Ssex='"+Ssex+"',Sphone='"+Sphone+"'where Stuid="+Stuid+"");   
   
   st.close();
   con.close();
 %>
 <input type="button" value="查询是否修改成功" οnclick="javascript:document.location.href='list.jsp';"> 
</body>
</html>

注意事项：

（1）增加语句request.setCharacterEncoding("UTF-8");是因为开始默认的不是UTF-8编码，我手动改成的UTF-8,出现了乱码问题

（2）需要添加jar包，sqljdbc4.jar下载：http://pan.baidu.com/s/1hrDv0qC

添加jar包方法：

（1），在项目里新建lib文件夹，将需要的jar包粘贴过去

（2），最后一步改为选（add jars）导入lib里的包。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。