clientbillingdb.sql

挺好的JAVA例子,可用MYSQL,ACCESS,ORACLE做后台,例子中包括联结及查询等涵数

/* 
SQLyog v3.03
Host - localhost : Database - clients
**************************************************************
Server version 3.23.55-nt
*/

create database if not exists clients;

use clients;

/*
Table structure for client
*/

drop table if exists client;
CREATE TABLE client (
  clientID int(11) NOT NULL auto_increment,
  firstName varchar(20) default NULL,
  lastName varchar(20) default NULL,
  phone varchar(20) default NULL,
  email varchar(40) default NULL,
  dob date default NULL,
  address varchar(100) default NULL,
  miscInfo varchar(100) default NULL,
  PRIMARY KEY  (clientID)
) TYPE=MyISAM;

/*
Table data for clients.client
*/

INSERT INTO client VALUES (100,'Joe','Dirt','555-4567','[e-mail]',10/12/1950,'Winnipeg, Manitoba','some stuff');
INSERT INTO client VALUES (200,'Clint','Eastwood','555-1234','[e-mail]',12/5/1930,'Hollywood, California','more stuff');
INSERT INTO client VALUES (300,'Neo','Matrix','555-4488','[e-mail]',10/15/1963,'Nowhere','lots of stuff');

/*
Table struture for transaction
*/

drop table if exists transaction;
CREATE TABLE transaction (
  transactionID int(11) NOT NULL default '0',
  clientID int(11) NOT NULL default '0',
  amount int(11) default NULL,
  tDate date default NULL,
  description varchar(200) default NULL,
  PRIMARY KEY  (transactionID,clientID)
) TYPE=MyISAM;

/*
Table data for clients.transaction
*/

INSERT INTO transaction VALUES (100,100,2500,6/2/2003,"A present.");
INSERT INTO transaction VALUES (100,200,2500,6/2/2003,"A present.");
INSERT INTO transaction VALUES (100,300,2500,6/2/2003,"A present.");
INSERT INTO transaction VALUES (200,200,30,10/2/2003,"Payment for past services");
INSERT INTO transaction VALUES (300,200,40,12/2/2003,"Payment for some past services");
INSERT INTO transaction VALUES (400,200,-60000,6/4/2003,"Invoice");
INSERT INTO transaction VALUES (500,100,5000,6/4/2003,"Payment");
INSERT INTO transaction VALUES (600,100,5000,6/4/2003,"Another payment");