3.29

练习

DROP DATABASE SHOP;
CREATE DATABASE SHOP CHARACTER SET UTF8;


#创建用户表
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),
	CONSTRAINT FOREIGN KEY(OID) REFERENCES ORDERS(OID),
	CONSTRAINT 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');

#给商品表初始化数据
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);


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

#添加订单
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);

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

#1.1
SELECT *
FROM ORDERS;
#1.2
SELECT B.`USERID`,A.`OID`,A.`PID`,A.`NUM`
FROM ORDERITEM AS A
INNER JOIN ORDERS AS B
ON A.`OID` = B.`OID`
WHERE USERID = 1;
#2.1
SELECT *
FROM ORDERS 
WHERE USERID = (SELECT USERID FROM USER WHERE USERNAME = '张三');
#2.2
SELECT *
FROM USER
WHERE USERID 
IN 
(SELECT USERID FROM ORDERS WHERE TOTALPRICE > 800);
#3.1
SELECT *
FROM ORDERS
LIMIT 0,5;

package t1;

import com.mysql.jdbc.Driver;

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

public class TestJDBC {
    public static void main(String[] args) throws Exception {

        Class.forName("com.mysql.jdbc.Driver");


        String url = "jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url,user,password);


        Statement statement = connection.createStatement();


        String sql1 = "INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES('AL','阿尔巴尼亚');";
        String sql2 = "UPDATE t_employees SET FIRST_NAME='TOM', LAST_NAME = 'Jackson' WHERE EMPLOYEE_ID = '206';";
        String sql3 = "DELETE FROM t_employees WHERE EMPLOYEE_ID = '206'";

        int result = statement.executeUpdate(sql1);
        int result2 = statement.executeUpdate(sql2);
        int result3 = statement.executeUpdate(sql3);


        if(result > 0 && result2 > 0 && result3 > 0){
            System.out.println("新增成功！");
        }else{
            System.out.println("新增失败");
        }


        statement.close();
        connection.close();

    }
}