CREATE DATABASE SN123;#创建数据库
USE SN123;#切换数据库
#创建用户表
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)
)CHARSET=utf8;
SELECT * FROM USER
;
#创建分类表
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(100) NOT NULL
)CHARSET=utf8;
SELECT * FROM category;
#商品表
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)
)CHARSET=utf8;
SELECT * FROM products;
#订单表
CREATE TABLE orders(
oid VARCHAR(32) PRIMARY KEY ,
totalprice DOUBLE(12,2), #总计
userId INT,
CONSTRAINT
FOREIGN KEY(userId)
REFERENCES user
(userId)
)CHARSET=utf8;
SELECT * FROM orders;
#订单项表
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)
)CHARSET=utf8;
SELECT * FROM orderitem;
#----------------------------------------
#初始化数据
#用户表添加数据
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 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 category VALUES(‘c001’,‘电器’);
INSERT INTO category VALUES(‘c002’,‘服饰’);
INSERT INTO category VALUES(‘c003’,‘化妆品’);
INSERT INTO category VALUES(‘c004’,‘书籍’);
SELECT * FROM category;
#添加订单
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;
【多表查询】
1.#查询所有用户的订单
SELECT *
FROM user
INNER JOIN orders
ON user
.userId = orders.userId
2.#查询用户id为1的所有订单详情
SELECT username,password
,address,phone
FROM user
SELECT *
FROM user
INNER JOIN orders
ON user
.userId = orders.userId
INNER JOIN orderitem
ON orders.oid = orderitem.oid
WHERE user
.userId
=1;
【子查询】
1.#查看用户为张三的订单
SELECT userId FROM user
WHERE username = ‘张三’;
SELECT *
FROM orders
WHERE userId =
(SELECT userId FROM user
WHERE username = ‘张三’);
2.#查询出订单的价格大于800的所有用户信息
SELECT userId FROM orders WHERE totalprice > 800;
SELECT *
FROM user
WHERE userId
IN(SELECT userId FROM orders WHERE totalprice > 800);
【分页查询】
1.#查询所有订单信息,每页显示5条数据
SELECT * FROM orders LIMIT 0,5;
【使用JDBC实现对每张表的增、删、改】
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestJDBCWork {
public static void main(String[] args) throws Exception {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//将驱动字节码文件加载到JVM中
//2.连接数据库
String url = "jdbc:mysql://localhost:3306/SN2002?useUnicode=true&characterEncoding=utf8";//数据库连接地址
String user = "root";//用户名
String password = "136135";//密码
//3.获取发送SQL语句的对象
Connection connection = DriverManager.getConnection(url, user, password);
//4.编写SQL语句,并执行SQL语句
Statement statement = connection.createStatement();
String sql = "insert into `user` (username,`password`,address,phone) values ('赵四','1234','北京中关村','13812345222')";
int result = statement.executeUpdate(sql);
//5.处理结果
if (result > 0) {
System.out.println("新增成功!");
} else {
System.out.println("新增失败!");
}
//6.释放资源
statement.close();
connection.close();
}
}