select tu.tuname,zhe.name from zhe inner join tu on zhe.number=tu.number where zhe.age<(select avg(age) from zhe);
IMG_0790.JPG
IMG_0791.JPG
IMG_0792.JPG
IMG_0793.JPG
IMG_0794.JPG
数据库做增删改查
可以下载mysql-connector-java-5.0.8-bin.jar包操作
package jinchenrui;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Scanner;
public class text {
static String string=null;
static String string2=null;
static String string3=null;
static String string4=null;
static{
string = ResourceBundle.getBundle("db").getString("driver");
string2 = ResourceBundle.getBundle("db").getString("url");
string3 = ResourceBundle.getBundle("db").getString("name");
string4 = ResourceBundle.getBundle("db").getString("paw");
}
static Connection connection=null;
public static void main(String[] args) throws Exception {
try {
Class.forName(string);
connection = DriverManager.getConnection(string2,string3,string4);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("1添加2修改3查看4删除");
int a=new Scanner(System.in).nextInt();
switch(a){
case 1:
insert();
break;
case 2:
updata();
break;
case 3:
select();
break;
case 4:
delete();
}
}
private static void delete() throws Exception {
// TODO Auto-generated method stub
Statement createStatement = connection.createStatement();
String dele="delete from User where Name='王五'";
createStatement.executeUpdate(dele);
createStatement.close();
connection.close();
}
private static void select() throws SQLException {
// TODO Auto-generated method stub
Statement createStatement = connection.createStatement();
String kk="select * from User";
ResultSet query = createStatement.executeQuery(kk);
while(query.next()){
int int1 = query.getInt("Id");
String string5 = query.getString("Name");
String string6 = query.getString("Pwd");
String string7 = query.getString("Email");
Date date = query.getDate("Birthday");
System.out.println("编号是"+int1+"姓名是"+string5+"密码是"+string6+"邮箱是"+string7+"生日是"+date);
}
query.close();
createStatement.close();
connection.close();
}
private static void updata() throws Exception {
// TODO Auto-generated method stub
Statement statement = connection.createStatement();
String lisi="update User set Pwd=9999 where Name='李四'";
statement.executeUpdate(lisi);
statement.close();
connection.close();
}
private static void insert() throws Exception {
// TODO Auto-generated method stub
Statement statement = connection.createStatement();
String insert="insert into User values(null,'张三','6666','zhangshan@163.com',19991021)";
String insert2="insert into User values(null,'李四','7777','lisi@163.com',19980808)";
String insert3="insert into User values(null,'王五','8888','wangwu@123.com',19970701)";
statement.addBatch(insert);
statement.addBatch(insert2);
statement.addBatch(insert3);
int[] is = statement.executeBatch();
statement.close();
connection.close();
}
}
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/2017db
name=root
paw=502900588
无人售货车
package Database;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;
public class ADO {
public static String Driver;
public static String url;
public static String user;
public static String password;
// 获取配置文件方法一
// 1.创建properties对象
// Properties properties = new Properties();
// 2.加载配置
// FileInputStream inputStream = new FileInputStream("db.properties");//前面加xxx/是代表那个目录下
// properties.load(inputStream);
// 3.获取配置文件里面的字段
// String Driver = properties.getProperty("Driver");
// String user = properties.getProperty("user");
// String url = properties.getProperty("url");
// String password = properties.getProperty("password");
// System.out.println(Driver+password);
// 获取配置文件方法二
// static{
// Driver = ResourceBundle.getBundle("db").getString("Driver");//没有后缀明
// url = ResourceBundle.getBundle("db").getString("url");
// user = ResourceBundle.getBundle("db").getString("user");
// password = ResourceBundle.getBundle("db").getString("password");
// System.out.println(Driver);
//
// }
// 数据库工具
public static Connection conn() {
Properties properties = new Properties();
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream("src/db.properties");
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
properties.load(inputStream);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
String Driver = properties.getProperty("Driver");
String user = properties.getProperty("user");
String url = properties.getProperty("url");
String password = properties.getProperty("password");
// System.out.println(Driver + password);
Connection connection = null ;
try {
Class.forName(Driver);
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
public static void closeConnection(Statement stat, Connection conn){
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package Database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.sun.xml.internal.ws.api.streaming.XMLStreamReaderFactory.Default;
public class data {
static Statement statement2=null;
static Scanner scanner=null;
static String name=null;
public static void choice() throws Exception {
//数据库进去
//表进去
//表记录
Connection connection = ADO.conn();
statement2 = connection.createStatement();
scanner = new Scanner(System.in);
boolean b=true;
while(b){
System.out.println("管理商品:0,查看订单 1,新建,2,查看,3,删除,4,修改,5关闭");
int a=scanner.nextInt();
switch(a){
case 0:
System.out.println("1,查看全部订单 2,查看单个订单");
int kk=scanner.nextInt();
if(kk==1){
String nn=" select * from yan y inner join yan2 y2 on y.id=y2.wuping_id inner join enter y1 on y2.ren_id=y1.id;";
ResultSet query1 = statement2.executeQuery(nn);
while (query1.next()) {
String string2 = query1.getString("user");
String string3 = query1.getString("password");
int int1 = query1.getInt("id");
String name = query1.getString("name");
int price=query1.getInt("price");
int int2 = query1.getInt("liang");
String string = query1.getString("leixing");
System.out.println("订单名:"+string2+"\t订单密码:"+string3+"\t购买商品编号:"+int1+"\t商品:"+name+"\t价格:"+price+"\t数量:"+int2+"\t类型:"+string);
}query1.close();
}else if(kk==2){
System.out.println("请输入单号名");
String next = scanner.next();
String hh="select * from yan where id in(select wuping_id from yan2 where ren_id in(select id from enter where user='"+next+"'));";
ResultSet query11 = statement2.executeQuery(hh);
while (query11.next()) {
int int1 = query11.getInt("id");
String name = query11.getString("name");
int price1=query11.getInt("price");
int int21 = query11.getInt("liang");
String string1 = query11.getString("leixing");
System.out.println("编号:"+int1+"\t商品:"+name+"\t价格:"+price1+"\t数量:"+int21+"\t类型:"+string1);
} query11.close();
ResultSet query111 = statement2.executeQuery(hh);//在另建一个
if(query111.next()){
}else{
System.out.println("没有订单");
}query111.close();
}
break;
case 1:
System.out.println("请按格式输入(添加)内容");
System.out.println("请输入商品名");
String no=scanner.next();
System.out.println("请输入商品价格");
int add=scanner.nextInt();
System.out.println("请输入商品数量");
int bdd=scanner.nextInt();
System.out.println("请输入商品类型");
String cdd=scanner.next();
String ak47="insert into yan values(null,?,?,?,?);";
PreparedStatement statement = connection.prepareStatement(ak47);
statement.setString(1, no);
statement.setInt(2, add);
statement.setInt(3, bdd);
statement.setString(4, cdd);
int j = statement.executeUpdate();
if(j==1){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
break;
case 2:
System.out.println("1,查询所有商品 2,查询单个商品");
int as=scanner.nextInt();
if(as==1){
String yes="select * from yan";
ResultSet query = statement2.executeQuery(yes);
while (query.next()) {
int int1 = query.getInt("id");
String name = query.getString("name");
int price=query.getInt("price");
int int2 = query.getInt("liang");
String string = query.getString("leixing");
System.out.println("编号:"+int1+"\t商品:"+name+"\t价格:"+price+"\t数量:"+int2+"\t类型:"+string);
}query.close();
}else{
System.out.println("请输入商品名");
String shu1=scanner.next();
String yes="select * from yan where name='"+shu1+"'";
ResultSet query = statement2.executeQuery(yes);
if (query.next()) {
int int1 = query.getInt("id");
String name = query.getString("name");
int price=query.getInt("price");
int int2 = query.getInt("liang");
String string = query.getString("leixing");
System.out.println("编号"+int1+"\t商品"+name+"\t价格"+price+"\t数量"+int2+"\t类型"+string);
}else{
System.out.println("没有此商品");
}query.close();
}
break;
case 3:
System.out.println("请输入(删除)商品名");
String no0=scanner.nextLine();
String no2=scanner.nextLine();
String delete="delete from yan where name='"+no2+"'";
int i = statement2.executeUpdate(delete);
System.out.println("修改"+i+"个");
break;
case 4:
System.out.println("请选择修改名:1,商品名 2,价格 3,数量 4,类型 ");
int xuan=scanner.nextInt();
switch(xuan){
case 1:
name="name";
xuan();
break;
case 2:
name="price";
xuannumber();
break;
case 3:
name="liang";
xuannumberliang();
break;
case 4:
name="leixing";
xuan1();
break;
default:
System.out.println("请选择1-4");
break;
}
break;
case 5:
try {
ADO.closeConnection(statement2, connection);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
b=false;
System.out.println("推出菜单");
break;
default:
System.out.println("请输入1-5(*^_^*)");
break;
}
}
}
private static void xuannumberliang() throws Exception {
// TODO Auto-generated method stub
System.out.println("请选择:[1,商品名 2,商品id] 来修改商品数量");
int xuan=scanner.nextInt();
// TODO Auto-generated method stub
if(xuan==1){
String name1="name";
System.out.println("请输入商品名");
// String set00=scanner.next();
String set11=scanner.next();
System.out.println("请输入修改后的值");
int set222=scanner.nextInt();
String set55="update yan set "+name+"="+set222+" where "+name1+"='"+set11+"'";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}
else if(xuan==2){
String name2="id";
System.out.println("请输入编号");
// String set00=scanner.next();
int set11=scanner.nextInt();
System.out.println("请输入修改后的值");
int set222=scanner.nextInt();
String set55="update yan set "+name+"="+set222+" where "+name2+"="+set11+"";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}else{
System.out.println("输入错误");
}
}
private static void xuan1() throws Exception {
System.out.println("请选择:[1,商品名 2,商品id] 来修改商品类型");
int xuan=scanner.nextInt();
// TODO Auto-generated method stub
if(xuan==1){
String name1="name";
System.out.println("请输入商品名");
// String set00=scanner.next();
String set11=scanner.next();
System.out.println("请输入修改后的值");
String set222=scanner.next();
String set55="update yan set "+name+"='"+set222+"' where "+name1+"='"+set11+"'";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}
if(xuan==2){
String name2="id";
System.out.println("请输入编号");
// String set00=scanner.next();
int set11=scanner.nextInt();
System.out.println("请输入修改后的值");
String set222=scanner.next();
String set55="update yan set "+name+"='"+set222+"' where "+name2+"="+set11+"";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}else
if(xuan!=1&&xuan!=2){
System.out.println("输入错误");
}//?
}
private static void xuannumber() throws Exception {
System.out.println("请选择:[1,商品名 2,商品id] 来修改商品价格");
int xuan=scanner.nextInt();
// TODO Auto-generated method stub
if(xuan==1){
String name1="name";
System.out.println("请输入商品名");
// String set00=scanner.next();
String set11=scanner.next();
System.out.println("请输入修改后的值");
int set222=scanner.nextInt();
String set55="update yan set "+name+"="+set222+" where "+name1+"='"+set11+"'";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}else
if(xuan==2){
String name2="id";
System.out.println("请输入编号");
// String set00=scanner.next();
int set11=scanner.nextInt();
System.out.println("请输入修改后的值");
int set222=scanner.nextInt();
String set55="update yan set "+name+"="+set222+" where "+name2+"="+set11+"";
int i = statement2.executeUpdate(set55);
System.out.println("修改"+i+"个");
}
else
if(xuan!=1&&xuan!=2){
System.out.println("输入错误");
}//?
}
private static void xuan() throws Exception {
// TODO Auto-generated method stub
System.out.println("请输入修改前的值");
String set0=scanner.nextLine();
String set1=scanner.nextLine();
System.out.println("请输入修改后的值");
String set22=scanner.nextLine();
String set="update yan set "+name+"='"+set22+"' where "+name+"='"+set1+"'";
int i = statement2.executeUpdate(set);
System.out.println("修改"+i+"个");
}
}
package Database;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class password {
public static int int1;
public static String name1;
public static void Enter() throws Exception{
Connection conn = ADO.conn();//注册驱动并连接
Statement statement = conn.createStatement();//执行注册
while(true){
System.out.println("欢迎来到无人商店:1,注册 2,登陆 3,管理员登陆 4,关闭");
int a = new Scanner(System.in).nextInt();
switch(a){
case 1:
System.out.println("请输入注册的用户名");
name1 = new Scanner(System.in).next();
System.out.println("请输入密码");
int pwd1 = new Scanner(System.in).nextInt();
String sq2="insert into enter values(null,'"+name1+"',"+pwd1+")";
String sq3="select * from enter where user='"+name1+"' and password="+pwd1;
int executeUpdate = statement.executeUpdate(sq2);
ResultSet query = statement.executeQuery(sq3);
if(query.next()){//----------------------------
int1 = query.getInt("id");
}query.close();
String diang="insert into yan2 values("+int1+",null)";
int executeUpdate2 = statement.executeUpdate(diang);
if(executeUpdate==1&&executeUpdate2==1){
System.out.println("添加成功");
}else{
System.out.println("失败了请重试");
// String sq4="delete from enter where user='"+name1+"'";
// statement.executeUpdate(sq4);
}
break;
case 2:
System.out.println("请输入用户名");
String name = new Scanner(System.in).next();
System.out.println("请输入密码:");
int pwd = new Scanner(System.in).nextInt();
String sql="select * from enter where user=? and password=?";
PreparedStatement statement2 = conn.prepareStatement(sql);
statement2.setString(1, name);
statement2.setInt(2, pwd);
ResultSet set = statement2.executeQuery();
if (set.next()) {
System.out.println("登录成功!");
String s3="select * from enter where user='"+name+"' and password="+pwd;
ResultSet q9ry = statement.executeQuery(s3);
if(q9ry.next()){
int1 = q9ry.getInt("id");
}
String s4="select * from enter where id="+int1;
ResultSet r11y = statement.executeQuery(s4);
if(r11y.next()){
name1 = r11y.getString("user");
}
try {
user.user1();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else {
System.out.println("用户名和密码不匹配!");
}
set.close();
statement2.close();
break;
case 3:
System.out.println("请输入管理员账号");
int name3 = new Scanner(System.in).nextInt();
System.out.println("请输入密码:");
int pwd3 = new Scanner(System.in).nextInt();
String sql3="select * from guan where z="+name3+" and y= "+pwd3;
ResultSet ret3 = statement.executeQuery(sql3);
if (ret3.next()) {
System.out.println("登录成功!");
try {
data.choice();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} else {
System.out.println("用户名和密码不匹配!");
}
ret3.close();
break;
case 4:
System.out.println("拜拜");
ADO.closeConnection(statement, conn);
System.exit(0);
break;
default:
System.out.println("请输入1-4(*^_^*)");
break;
}
}}}
开启
package Database;
public class text {
public static void main(String[] args) {
try {
password.Enter();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package Database;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class user {
static Scanner scanner=null;
public static void user1() throws Exception{
Connection conn = ADO.conn();
Statement statement2 = conn.createStatement();
scanner = new Scanner(System.in);
boolean b=true;
while(b){
System.out.println("欢迎请选择:0,查看商品 1,购买 2,删除(不要) 3,查看我的订单 4,结账 5,推出");
int a=scanner.nextInt();
switch(a){
case 0:
System.out.println("1,查询所有2,查询单个商品");
int as=scanner.nextInt();
if(as==1){
String yes="select * from yan";
ResultSet query = statement2.executeQuery(yes);
while (query.next()) {
int int1 = query.getInt("id");
String name = query.getString("name");
int price=query.getInt("price");
int int2 = query.getInt("liang");
String string = query.getString("leixing");
System.out.println("编号:"+int1+"\t商品"+name+"\t价格:"+price+"\t数量:"+int2+"\t类型:"+string);
}
}else{
System.out.println("请输入商品名");
String shu=scanner.next();
String yes="select * from yan where name='"+shu+"'";
ResultSet query = statement2.executeQuery(yes);
if (query.next()) {
int int1 = query.getInt("id");
String name = query.getString("name");
int price=query.getInt("price");
int int2 = query.getInt("liang");
String string = query.getString("leixing");
System.out.println("编号:"+int1+"\t商品:"+name+"\t价格:"+price+"\t数量:"+int2+"\t类型:"+string);
}else{
System.out.println("没有此商品");
}
}
break;
case 1:
int int110=0;
boolean m=true;
while(m){
conn.setAutoCommit(false);
System.out.println("请输入商品名");
String shu=scanner.next();
String yes="select * from yan where name='"+shu+"'";
ResultSet query = statement2.executeQuery(yes);
while(query.next()){
int110 = query.getInt("id");
String name = query.getString("name");
int price=query.getInt("price");
int int2 = query.getInt("liang");
String string = query.getString("leixing");
System.out.println("编号:"+int110+"\t商品:"+name+"\t价格:"+price+"\t数量:"+int2+"\t类型:"+string);
// String liang="up";
// 怎么把liang进行加和减
System.out.println("购买成功");
}
String shang="insert into yan2 values("+password.int1+","+int110+")";
statement2.executeUpdate(shang);
ResultSet query12 = statement2.executeQuery(yes);
conn.commit();
if (query12.next()) {
}else{
System.out.println("没有此商品");
}
System.out.println("是否继续购买?");
String xuan=scanner.next();
if(xuan.equals("否")){
m=false;
System.out.println("好的ok");
}}
break;
case 2:
conn.setAutoCommit(false);
System.out.println("请输入商品名");
String shu=scanner.next();
String yes="select * from yan where name='"+shu+"'";
ResultSet query = statement2.executeQuery(yes);
if (query.next()) {
int int1 = query.getInt("id");
String yes2="delete from yan2 where wuping_id="+int1;
int i = statement2.executeUpdate(yes2);
System.out.println("删除"+i+"个");
}else{
System.out.println("没有此商品");
}
conn.commit();
break;
case 3:
System.out.println("查看订单");
String hh="select * from yan where id in(select wuping_id from yan2 where ren_id in(select id from enter where user='"+password.name1+"'));";
// select * from yan where id in(select wuping_id from yan2 where ren_id in(select id from enter where user=' 我 '));
ResultSet query1 = statement2.executeQuery(hh);
if(query1.next()){
int int1 = query1.getInt("id");
String name = query1.getString("name");
int price=query1.getInt("price");
int int2 = query1.getInt("liang");
String string = query1.getString("leixing");
System.out.println("编号"+int1+"商品"+name+"价格"+price+"数量"+int2+"类型"+string);
while (query1.next()) {
int llmm = query1.getInt("id");
String nanme = query1.getString("name");
int priee=query1.getInt("price");
int intoo2 = query1.getInt("liang");
String strijjng = query1.getString("leixing");
System.out.println("编号"+llmm+"商品"+nanme+"价格"+priee+"数量"+intoo2+"类型"+strijjng);
}}
else{
System.out.println("没有订单");
}
break;
case 4:
System.out.println("结账了哦");
String mm=" select sum(price) from yan where id in(select wuping_id from yan2 where ren_id in(select id from enter where user='"+password.name1+"'));";
ResultSet query2 = statement2.executeQuery(mm);
if(query2.next()){
int int1 = query2.getInt("sum(price)");
System.out.println("商品总价是:"+int1);
System.out.println("走出门口自动结账");
}else{
System.out.println("购物车空空如也");
}
break;
case 5:
ADO.closeConnection(statement2, conn);
System.out.println("欢迎下次购买(*^_^*)");
b=false;
break;
}
}
}
}
db.properties
Driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/data
user=root
password=502900588
