学习记录分享（综合练习）

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();
    }
}