java 作业

#千锋逆战班#
在千锋“逆战”学习第 41 天，
近期学习的是MySQL与JDBC的相关内容。
中国加油！武汉加油！千锋加油！
学习的脚步不停止！

天道酬勤，继续加油！

Day 41

综合练习

JDBC的核心六步实现增、删、改

某网上商城数据库表结构如下：

• 综合练习1-【多表查询】

  • 1>查询所有用户的订单
  • 2>查询用户id为 1 的所有订单详情

• 综合练习2-【子查询】

  • 1>查看用户为张三的订单
  • 2>查询出订单的价格大于800的所有用户信息。

• 综合练习3-【分页查询】

  • 1>查询所有订单信息，每页显示5条数据

• 综合练习4-【使用JDBC实现对每张表的增、删、改】

CREATE DATABASE shop;
ALTER DATABASE shop CHARACTER SET UTF8;
DROP DATABASE shop;
SELECT DATABASE();

# 创建用户表
CREATE TABLE USER(
	userId INT PRIMARY KEY AUTO_INCREMENT,
  	 username VARCHAR(20) NOT NULL,
  	 PASSWORD VARCHAR(18) NOT NULL,
  	 address VARCHAR(100),
  	 phone VARCHAR(11)
);

#创建分类表
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY ,
	cname VARCHAR(100) NOT NULL		
);

# 商品表
CREATE TABLE `products` (
	`pid` VARCHAR(32) PRIMARY KEY,
	`name` VARCHAR(40) ,
	`price` DOUBLE(7,2),
	 category_id VARCHAR(32),
	 CONSTRAINT FOREIGN KEY(category_id) REFERENCES category(cid)
);

#订单表
CREATE TABLE `orders`(
	`oid` VARCHAR(32) PRIMARY KEY ,
	`totalprice` DOUBLE(12,2), #总计
	`userId` INT,
	CONSTRAINT FOREIGN KEY(userId) REFERENCES USER(userId) #外键
);

# 订单项表
CREATE TABLE orderitem(
	oid VARCHAR(32),	#订单id
	pid VARCHAR(32),	#商品id
	num INT ,         #购买商品数量
	PRIMARY KEY(oid,pid), #主键
	FOREIGN KEY(oid) REFERENCES orders(oid),
	FOREIGN KEY(pid) REFERENCES products(pid)
);

#-----------------------------------------------
#初始化数据

#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) 
VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) 
VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) 
VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) 
VALUES('田七','123','北京大兴','13812345687');

#查询所有用户信息
SELECT * FROM USER;

#给分类表初始化数据
INSERT INTO category VALUES('c001','电器');
INSERT INTO category VALUES('c002','服饰');
INSERT INTO category VALUES('c003','化妆品');
INSERT INTO category VALUES('c004','书籍');

#查询所有分类
SELECT * FROM category;

#给商品表初始化数据
INSERT INTO products(pid,NAME,price,category_id) VALUES('p001','联想',5000,'c001');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p002','海尔',3000,'c001');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p003','雷神',5000,'c001');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p004','JACK JONES',800,'c002');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p005','真维斯',200,'c002');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p006','花花公子',440,'c002');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p007','劲霸',2000,'c002');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p008','香奈儿',800,'c003');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p009','相宜本草',200,'c003');
INSERT INTO products(pid,NAME,price,category_id) VALUES('p010','梅明子',200,NULL);

#查询所有商品
SELECT * FROM products;

#添加订单
INSERT INTO orders VALUES('o6100',18000.50,1);
INSERT INTO orders VALUES('o6101',7200.35,1);
INSERT INTO orders VALUES('o6102',600.00,2);
INSERT INTO orders VALUES('o6103',1300.26,4);

#查询所有订单
SELECT * FROM orders;

#订单详情表
INSERT INTO orderitem VALUES('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

#查询订单详情
SELECT * FROM orderitem;

#【多表查询】
#查询所有用户的订单

SELECT u.userId , username , oid 
FROM `user` AS u INNER JOIN `orders` AS o ON u.`userId` = o.`userId`;

#查询用户ID为 1 的所有订单详情

SELECT u.userId , username , o.oid , pid , num FROM `user` AS u 
INNER JOIN `orders` AS o ON u.`userId` = o.`userId` 
INNER JOIN `orderitem` AS i ON o.`oid` = i.`oid`;

#【子查询】
#查看用户为张三的订单

SELECT userId , username , oid 
FROM 
(SELECT u.userId , username , oid 
FROM `user` AS u INNER JOIN `orders` AS o ON u.`userId` = o.`userId`) AS temp
WHERE username = '张三';

#查询出订单的价格大于 800 的所有用户信息

SELECT  `userId` , username , totalprice , address , phone 
FROM
(SELECT  u.`userId`, username , totalprice , address , phone 
FROM `user` AS u INNER JOIN `orders` AS o ON u.`userId` = o.`userId`)AS temp
WHERE totalprice > 800 ;

#【分页查询】
#查询所有订单信息，每页显示 5 条数据
SELECT * FROM orders LIMIT 0,5;

【使用JDBC实现对每张表的增、删、改】

package com.day41.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class TestJDBC {
    public static void main(String[] args) throws Exception{
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //将驱动字节码文件加载到JVM中

        //2.连接数据库
        String url = "jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8"; //数据库连接地址
        String user = "root"; //用户名
        String password = "1234"; //密码
        Connection connection = DriverManager.getConnection(url,user,password);

        //3.获取发送SQL语句的对象
        Statement statement = connection.createStatement();

        //4.编写SQL语句，并执行SQL语句
        //增加用户
        String user1 = "insert into user(username,password,address,phone) values('小明','666','河南省郑州市','18889898989')";
        int userresult1 = statement.executeUpdate(user1);
        //修改用户
        String user2 = "update user set username = '阿强',password = '999' where userid = 1";
        int userresult2 = statement.executeUpdate(user2);
        //删除用户
        String user3 = "delete from user where userid = 2";
        int userresult3 = statement.executeUpdate(user3);

        //5.处理结果
        if(userresult1 > 0){
            System.out.println("用户新增成功");
        }else {
            System.out.println("用户新增失败");
        }

        if(userresult2 > 0){
            System.out.println("用户修改成功");
        }else {
            System.out.println("用户修改失败");
        }

        if(userresult3 > 0){
            System.out.println("用户删除成功");
        }else {
            System.out.println("用户删除失败");
        }

        //6.释放资源 先开后关
        statement.close();
        connection.close();

    }
}