博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
sql jsp
阅读量：4047 次
发布时间：2019-05-25

本文共 12359 字，大约阅读时间需要 41 分钟。

分类:

分类:

if object_id('dbo.backtable') is not null

    drop proc dbo.backtable
go
create proc dbo.backtable
    @EmployeeID int=5,
    @TableName varchar(128),
    @Path varchar(256)
AS
declare @str varchar(4000)
set @str='bcp "select * from where EmployeeID='+cast(@EmployeeID as varchar(10))+'" queryout -c -Usa -Padmin'
exec master..xp_cmdshell @str
go

exec dbo.backtable 5,'Orders','d:dd.txt'

2007.9.30 19:43 作者： | |

分类:

刚学sql server两天,写了一个简单的触发器.纪念一下.

if exists(select name from sysobjects

          where name='tri_update' and type='tr')
    drop trigger tri_update
go

create trigger tri_update

on Student for update
AS
    declare @old_id int
    declare @new_id int
    declare @row_count int
    select @row_count=@@rowcount
    if @row_count>=1
    begin
        select @old_id=d.StudentID from deleted d
        select @new_id=i.StudentID from inserted i
       
        if exists(select stu_id from stu_new where stu_id=(select StudentID from inserted i where ))
             update stu_new
             set stu_id=i.StudentID,stu_name=i.StudentName,stu_sex=i.StudentSex
             from inserted i
        else
             insert into stu_new
             select * from inserted i

        if exists(select stu_id from stu_old where stu_id=(select StudentID from deleted d where ))

             update stu_old
             set stu_id=d.StudentID,stu_name=d.StudentName,stu_sex=d.StudentSex
             from deleted d
        else
             insert into stu_old
             select * from deleted d
    end

2007.9.12 15:33 作者： | |

分类:

触发器能进行一些约束.

这是个小例子,当Student表的StudentID列被发生更改时,BorrowStudent表的StudentID列也跟着更改.如果Student表删除某记录,BorrowStudent也删除对应StudentID的记录.

创建表的脚本就不贴出来了.

 

delimiter ＄＄

drop trigger if exists tduStudent＄＄
drop trigger if exists tddStudent＄＄
create trigger tduStudent before update
on Student for each row
begin
    if new.StudentID!=old.StudentID then
        update BorrowStudent
        set BorrowStudent.StudentID=new.StudentID
        where BorrowStudent.StudentID=old.StudentID;
    end if;
end＄＄

create trigger tddStudent before delete

on Student for each row
begin
    delete
    from BorrowStudent
    where BorrowStudent.StudentID=old.StudentID;
end＄＄
delimiter ;

 

2007.9.10 09:40 作者： | |

分类:

drop table if exists `Card`;
drop table if exists `Computer`;
drop table if exists `Record`;
create table `Card`(
    `ID` int not null auto_increment,
    `CardID` char(8) not null,
    `pwd` varchar(16) not null,
    `balance` float not null,
    `name` varchar(16) not null,
    primary key(`ID`)
)engine=myisam;
create table `Computer`(
    `ID` int not null auto_increment,
    `ComputerID` char(3) not null,
    `CardID` char(8) not null,
    `isUse` bit(1) not null default 0,
    `remark` varchar(300),
    primary key(`ID`),
    foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;
create table `Record`(
    `ID` int not null auto_increment,
    `CardID` char(8) not null,
    `ComputerID` char(8) not null,
    `BeginTime` datetime ,
    `EndTime` datetime,
    primary key(`ID`),
    foreign key(`ComputerID`) references `Computer`(`ComputerID`),
    foreign key(`CardID`) references `Card`(`CardID`)
)engine=myisam;
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000001','123456',15.3,'purana');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000002','756321',15.3,'jiajia');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000003','12345',15.3,'jiadf');
insert into `Card`(`CardID`,`pwd`,`balance`,`name`) Values('00000004','75145',15.3,'mysql');
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('001','123456',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('002','756321',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('003','12345',1);
insert into `Computer`(`ComputerID`,`CardID`,`isUse`) Values('015','75145',1);
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000001','001','2007-09-09 10:15:12','2007-09-09 11:01:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000002','002','2007-09-09 15:35:33','2007-09-09 16:22:33');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000003','003','2007-09-09 20:14:01','2007-09-10 01:33:02');
insert into `Record`(`CardID`,`ComputerID`,`BeginTime`,`EndTime`) Values('00000004','015','2007-09-10 01:42:10','2007-09-10 22:01:02');
    select Card.CardID,name,t.BeginTime,t.EndTime
    from `Card` join (select CardID,BeginTime,EndTime,(EndTime-BeginTime) as `time`
                          from Record
                          where BeginTime between '2007-09-09 00:00:00' and '2007-09-09 23:55:55') As t
                on Card.CardID=t.CardID
    order by t.`time` desc
    limit 3;
2007.9.9 15:07 作者： | |

分类:

例如现在有一个test表,结构如下
name       num
tom        3
jerry      2
lily       1
现在要得出如下结果集
tom
tom
tom
jerry
jerry
lily
编写存储过程如下:
delimiter ＄＄
drop procedure if exists sp_test ＄＄
create procedure sp_test()
begin
    declare done int default 0;
    declare n_num int default 0;
    declare n_count int default 0;
    declare i int default 0;
    declare sname varchar(64);
    declare cur cursor for (select name,num from test);
    declare continue handler for sqlstate '02000' set done=1;
    create temporary table temp_test(name varchar(64));
    open cur;
    fetch cur into sname,n_count;
    while done=0 do
         set n_num=0;
         while n_num<n_count do
              insert into temp_test values(sname);
              set n_num=n_num+1;
         end while;
         fetch cur into sname,n_count;
    end while;
    close cur;
    select name from temp_test;
end＄＄
delimiter ;
如果相反,就更简单了.
insert into test
select name,count(name)
from temp
group by name;
2007.9.6 15:59 作者： | |

分类:

缺点：

1、可移植性是存储过程和触发器最大的缺点。

2、占用服务器端太多的资源，对服务器造成很大的压力

3、不能做DDL。

4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。

优点：

1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。

2、存储过程可以重复使用，减少开发人员的工作量。

3、业务逻辑封装性好，修改方便。

4、安全。不会有SQL语句注入问题存在。

2007.9.6 14:53 作者： | |

分类:

   
数据库里数据导出某个特定格式的文件,在查阅数据库数据是经常做的事情,例如导出Html,XML,CSV等.其实可以不用第三方的工具,MySQL就可以做到了.
    使用mysql可以导出html和xml.
    用法:
    mysql -uroot -p --html "--execute=select * from tblname" dbname > filename
    这样就可以导出到一个html文件
    mysql -uroot -p --xml "--execute=select * from tblname" dbname > filename
    这样可以导出xml文件.
    还可以利用其它工具进行导出.
    在这里也介绍几个我个人觉得比较好用的工具.
    MySQL Administrator     在管理MySQL,备份,恢复..都是很不错的..
    MySQL Query Browser     用来查询,执行脚本.比较两个查询的差别..都是很好的.
    SQLyog                  则是强烈推荐的工具,无论在哪方面.都做得很出色.
2007.9.4 15:09 作者： | |

分类:

   通常我们使用sql的聚合函数.可以求某一列的和,平均值,最大值,最小值等.但是没有求积的函数,所以在某些需求上,就达不到要求了,但我们可以自行去写sql去实现该功能.
delimiter ＄＄
drop procedure if exists sp_mul＄＄
create procedure sp_mul(out o_mul int)
begin
    declare i_temp int default 1;
    declare i_error int default 0;
    declare i_mul int default 1;
    declare rs_cursor cursor for (select number1 from temp);
    declare continue handler for sqlstate '02000' set i_error=1;
    open rs_cursor;
    while i_error=0 do
        fetch rs_cursor into i_temp;
        set i_mul=i_mul*i_temp;
    end while;
    close rs_cursor;
    set o_mul=i_mul;
end＄＄
delimiter ;

通常调用以上的存储过程.就可以实现了.
2007.9.3 10:47 作者： | |

分类:

该过程的作用是求某列的最大值.当然了.可以使用Max函数.不过主要是为了学习游标之用.
delimiter ＄＄
drop procedure if exists sp_max ＄＄
create procedure sp_max(in i_count int,out o_max int)
begin
    declare i,iMax,iTemp int;
    declare i_error int default 0;
    declare rs_cursor cursor for select eid from emp;
    declare continue handler for sqlstate '02000' set i_error=1;
    set i=0;
    set iMax = 0;
    open rs_cursor;
    while i<i_count do
        fetch rs_cursor into iTemp;
        if iTemp>iMax then
            set iMax=iTemp;
        end if;
        set i=i+1;
    end while;
    close rs_cursor;
    set o_max=iMax;
end＄＄
delimiter ;
再加上刚写的一个函数,一起帖出来.
delimiter ＄＄
drop function if exists sp_GetNameStr ＄＄
create function sp_GetNameStr(in_name varchar(10))
                   returns varchar(300)
begin
    declare nameStr varchar(300);
    declare tempStr varchar(10);
    declare i_error int default 0;
    declare iCount int;
    declare rs_cur cursor for (select admin from t2 where t2.name=in_name);
    declare continue handler for sqlstate '02000' set i_error=1;
    set nameStr='';
    open rs_cur;
    while i_error=0 do
       fetch rs_cur into tempStr;
       if nameStr='' then
           set nameStr=tempStr;
       else
           set nameStr=concat(nameStr,',',tempStr);
       end if;
    end while;
    close rs_cur;
    return nameStr;
end＄＄
delimiter ;
2007.9.1 18:54 作者： | |

分类:

/*

 * TempTest.java
 *
 * Created on 2007年8月28日, 上午10:55
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.test;

import java.util.*;

import java.io.*;

/**

 *
 * @author Administrator
 */
public class TempTest {
    public static void main(String[] args) throws Exception{
        TreeMap<String,Integer> tm=new TreeMap<String,Integer>();
        Scanner scanner=new Scanner(new File("e:/dj5.txt"));
        while(scanner.hasNext()){
            String word=scanner.next();
            int count=getCount(word,tm)+1;
            tm.put(word,new Integer(count));
        }
       
        System.out.println(tm.toString());
    }
   
    public static int getCount(String word,TreeMap<String,Integer> tm){
        if(tm.containsKey(word)){
            return tm.get(word).intValue();
        }else{
            return 0;
        }
    }
}

2007.8.29 22:52 作者： | |

分类:

 
 

< contentType="text/html"%>

< pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="com.test.*" %>
<%@ page import="java.util.*" %>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%--
< uri="" prefix="c"%>
--%>

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

   "">
   <%
       ConnectionImpl conImpl=new ConnectionImpl();
       Connection con=null;
       try{
           con=conImpl.getConnection();
       }catch(Exception exc){
           out.println(exc.toString());
       }
   %>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Search Data</title>
    </head>
    <body>
        <center>
            <form action="index.jsp" method="post">
                <%
                    Statement stmt=con.createStatement();
                    ResultSet rs=stmt.executeQuery("select playerno from players");
                %>
                <select name="number">
                    <% while(rs.next()) { %>
                    <option value="<%= rs.getString(1)%>"><%= rs.getString(1)%></option>
                    <% } %>
                </select>
                <%
                    rs.close();
                    stmt.close();
                %>
                <input type="submit" value="提交">
            </form>
           
            <%
                String number=request.getParameter("number");
                if(number!=null){
                    int n=Integer.parseInt(number);
                    stmt=con.createStatement();
                    rs=stmt.executeQuery("select * from players where playerno="+n);
                    ResultSetMetaData rsmd=rs.getMetaData();
                    int columnCount=rsmd.getColumnCount();
                   
                    if(rs.next()){
                        out.print("<table>");
                        for(int i=1;i<=columnCount;i++){
                            out.print("<tr>");
                            out.print("<td bgcolor=pink>"+rsmd.getColumnName(i)+"</td>");
                            out.print("<td>"+rs.getString(i)+"</td>");
                            out.print("</tr>");
                        }
                        out.print("</table>");
                    }
                    rs.close();
                    stmt.close();
                }
            %>
        </center>
    </body>
    <%
        conImpl.releaseConnection(con);
    %>
</html>

  
 
2007.8.25 23:36 作者： | |

分类:

 
 

<%@ page contentType="image/jpeg" %>

<%@ page import="java.awt.*" %>
<%@ page import="java.awt.image.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.imageio.*" %>

<%

    response.setHeader("Pragma","No-Cache");
    response.setHeader("Cache-Control","No-Cache");
    response.setDateHeader("Expires",0);
   
    int width=200;
    int height=30;
   
    String valueWidth=request.getParameter("valueWidth");
    int intWidth=Integer.parseInt(valueWidth);
   
    BufferedImage image=new BufferedImage(width,height,BufferedImage.TYPE_INT_RGB);
   
    Graphics g=image.getGraphics();
   
    g.setColor(Color.white);
    g.fillRect(0,0,width,height);
   
    g.setColor(Color.darkGray);
    g.fillRect(0,0,intWidth,height);
   
    g.setColor(Color.blue);
    g.drawRect(0,0,width-1,height-1);
   
    g.dispose();
   
    ImageIO.write(image,"JPEG",response.getOutputStream());
 %>
 

转载地址：http://wobci.baihongyu.com/

你可能感兴趣的文章
分布式缓存负载均衡负载均衡的缓存处理：虚拟节点对一致性hash的改进
查看>>
分布式存储系统设计（1）—— 系统架构
查看>>
MySQL数据库的高可用方案总结
查看>>
常用排序算法总结(一) 比较算法总结
查看>>
SSH原理与运用
查看>>
SIGN UP BEC2
查看>>
S3C2440中对LED驱动电路的理解
查看>>
《天亮了》韩红
查看>>
Windows CE下USB摄像头驱动开发(以OV511为例，附带全部源代码以及讲解) [转]
查看>>
模拟屏学习资料_什么是PAL制式
查看>>
模拟屏学习资料_模拟视频 入门
查看>>
西藏之旅
查看>>
Oracle中定时执行问题
查看>>
三时业
查看>>
佛教三宝-三皈依
查看>>
杂阿含经喻世间有四等马
查看>>
考研前夜涂笔
查看>>
英语复试自我介绍
查看>>
什么是熵?
查看>>
拼凑、摘抄-评李代平的软件工程第二版
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-09-19 21:00:05   当前IP: 52.15.86.164   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我