1、创建数据库test01_bookstore
CREATE DATABASE test01_bookstore
USE test01_bookstore
2、创建如下表格
(1)图书表books
#创建books表
CREATE TABLE books(
id INT(11) AUTO_INCREMENT,
title VARCHAR(100),
author VARCHAR(100),
price DOUBLE(11,2),
sales INT(11),
stock INT(11),
img_path VARCHAR(100),
PRIMARY KEY (id) );
DESC books
(2)用户表users
#创建users表
CREATE TABLE users(
id INT(11),
username VARCHAR(100) UNIQUE ,
PASSWORD VARCHAR(100)NOT NULL,
email VARCHAR(100) );
DESC users;
#创建orders表
CREATE TABLE orders(
id VARCHAR(100),
order_time DATETIME ,
total_count INT(11),
total_amount DOUBLE(11,2),
state INT(11),
user_id INT(11) ,
PRIMARY KEY(id),
CONSTRAINT orders_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id));
DESC orders;
#创建order_items表
CREATE TABLE order_items(
id INT(11) NOT NULL AUTO_INCREMENT,
COUNT INT(11)NOT NULL,
amount DOUBLE(11,2)NOT NULL,
title VARCHAR(100)NOT NULL,
author VARCHAR(100)NOT NULL,
price DOUBLE (11,2)NOT NULL,
img_path VARCHAR(100) NOT NULL,
order_id VARCHAR(100)NOT NULL,
PRIMARY KEY(id),
CONSTRAINT order_items_orders_id_fk FOREIGN KEY (order_id) REFERENCES
orders (id) );
DESC order_items;
INSERT INTO books(id,title,author,price,sales,stock,img_path)
VALUES
(1,'解忧杂货店','东野圭吾',27.20,102,98,'upload/books/解忧杂货店.jpg'),
(2,'边城','沈从文',23.00,102,98,'upload/books/边城.jpg'),
(3,'中国哲学史','冯友兰',44.50,101,99,'upload/books/中国哲学史.jpg'),
(4,'忽然七日',' 劳伦',19.33,101,99,'upload/books/忽然七日.jpg'),
(5,'苏东坡传','林语堂',19.30,100,100,'upload/books/苏东坡传.jpg'),
(6,'百年孤独','马尔克斯',29.50,100,100,'upload/books/百年孤独.jpg'),
(7,'扶桑','严歌苓',19.80,100,100,'upload/books/扶桑.jpg'),
(8,'给孩子的诗','北岛',22.20,100,100,'upload/books/给孩子的诗.jpg'),
(9,'为奴十二年','所罗门',16.50,100,100,'upload/books/为奴十二年.jpg'),
(10,'平凡的世界','路遥',55.00,100,100,'upload/books/平凡的世界.jpg'),
(11,'悟空传','今何在',14.00,100,100,'upload/books/悟空传.jpg'),
INSERT INTO users(id,username,`password`,email)
VALUES
(1,'aaa','123456','111@qq.com'),
(2,'bbb','123454','112@qq.com'),
(3,'ccc','123453','113@qq.com'),
(4,'ddd','123452','114@qq.com');
INSERT INTO orders(id,order_time,total_count,total_amount,state,user_id)
VALUES
('12345677890','2023-8-25 20:51:22',1,222,33,5),
('23434567890','2023-8-26 20:34:22',2,122,34,1);
INSERT INTO order_items (`count`, amount, title, author, price, img_path, order_id)
VALUES
( 1, 27.20, '解忧杂货店', '东野圭吾', 27.20, 'upload/books/解忧杂货店.jpg', 1),
( 1, 23.00, '边城', '沈从文', 23.00, 'upload/books/边城.jpg', 2),
( 1, 44.50, '中国哲学史', '冯友兰', 44.50, 'upload/books/中国哲学史.jpg', 2),
( 1, 19.33, '忽然七日', '劳伦', 19.33, 'upload/books/忽然七日.jpg', 3),
( 1, 27.20, '解忧杂货店', '东野圭吾', 27.20, 'upload/books/解忧杂货店.jpg', 4),
( 1, 23.00, '边城', '沈从文', 23.00, 'upload/books/边城.jpg', 5);
DELETE FROM order_items WHERE order_id NOT IN (SELECT id FROM orders);
ALTER TABLE `order_items` DROP FOREIGN KEY `order_items_orders_id_fk`;
ALTER TABLE `order_items` ADD CONSTRAINT `order_items_orders_id_fk` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`);
DELETE FROM order_items;
SELECT * FROM order_items WHERE order_id NOT IN (SELECT id FROM orders);
第4题
无法加密
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class Test1 {
public static void main(String[] args) throws Exception{
Scanner input =new Scanner(System.in);
System.out.println("请输入用户名");
String username = input.nextLine();
System.out.println("请输入密码");
String password = input.nextLine();
System.out.println("请输入邮箱");
String email = input.nextLine();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="insert into users value(null,?,password(?),?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);
ps.setString(2,password);
ps.setString(3,email);
int len = ps.executeUpdate();
System.out.println(len>0?"添加成功":"添加失败");
ps.close();
conn.close();
input.close();
}
}
第5题
public class Test2 {
//使用JDBC实现往图书表中添加1本图书
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="insert into books(id,title,author,price,sales,stock,img_path)values (null,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,"《Java基础》");
ps.setString(2,"张一");
ps.setString(3, String.valueOf(22.3));
ps.setString(4, String.valueOf(0));
ps.setString(5, String.valueOf(100));
ps.setString(6,"upload/books/Java基础.jpg");
int len = ps.executeUpdate();
System.out.println(len>0?"添加成功":"添加失败");
ps.close();
conn.close();
}
}
第6题
public class Test3 {
public static void main(String[] args) throws Exception{
Scanner input =new Scanner(System.in);
System.out.println("请登录");
System.out.println("请输入用户名");
String username = input.nextLine();
System.out.println("请输入密码");
String password = input.nextLine();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="select * from users where username =? and password = SHA2(?, 256)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);
ps.setString(2,password);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败,用户名或密码错误");
}
rs.close();
ps.close();
conn.close();
input.close();
}
}
结果
请登录
请输入用户名
chai
请输入密码
123456
登录失败,用户名或密码错误
第7题
public class Test4 {
//使用JDBC实现查询所有图书信息
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="select id,title,author,sales,stock,img_path from books";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()){
for (int i = 1; i < 7; i++) {
System.out.println(rs.getObject(i)+"\t");
}
System.out.println();
}
rs.close();
ps.close();
conn.close();
}
}
结果
1
解忧杂货店
东野圭吾
102
98
upload/books/解忧杂货店.jpg
第8题
public class Test5 {
//8、使用JDBC实现查询销量最大的图书信息
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="select id,title,author,sales,stock,img_path from books where sales =(select max(sales)from books) ";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()){
for (int i = 1; i < 7; i++) {
System.out.println(rs.getObject(i)+"\t");
}
System.out.println();
}
rs.close();
ps.close();
conn.close();
}
}
结果
1
解忧杂货店
东野圭吾
102
98
upload/books/解忧杂货店.jpg
2
边城
沈从文
102
98
upload/books/边城.jpg
第9题
public class Test6 {
//9、使用JDBC实现修改库存量小于10本的图书的库存量为100
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="update books set stock = 100 where stock<10";
PreparedStatement ps = conn.prepareStatement(sql);
int len = ps.executeUpdate();
System.out.println(len>=0? "修改成功" : "修改失败");
ps.close();
conn.close();
}
}
结果
修改成功
第10题
public class Test7 {
//从键盘输入用户名,实现查询该用户的订单和订单明细
public static void main(String[] args) throws Exception{
Scanner input =new Scanner(System.in);
System.out.println("请输入用户名");
String username=input.nextLine();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="SELECT * FROM order_items INNER JOIN orders ON\n" +
"order_items.order_id = orders.id WHERE user_id = (SELECT id FROM users WHERE\n" +
"username = ?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,username);
ResultSet rs = ps.executeQuery();
while(rs.next()){
for (int i = 1; i <= 14; i++) {
System.out.println(rs.getObject(i)+"\t");
}
System.out.println();
}
rs.close();
ps.close();
conn.close();
input.close();
}
}
第11题
public class Test8 {
//11、使用JDBC实现删除订单“15275760194821”的相关信息,注意涉及到两张表
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01_bookstore","root","root");
String sql ="delete from orders where id ='12334567890'";
PreparedStatement ps = conn.prepareStatement(sql);
int len = ps.executeUpdate();
System.out.println(len>=0? "删除成功" : "删除失败");
ps.close();
conn.close();
}
}
结果
删除成功