千峰第41天作业

在千峰学习的第41天，
“做自己需要的自己”
中国加油！武汉加油！千峰加油！我自己加油！
1、
查询所有用户订单
SELECT orders.userId,username,oid,totalprice FROM orders INNER JOIN USER ON orders.userId=user.userId;

查询用户ID为1的所有订单
SELECT DISTINCT orders.userId,username,oid,totalprice FROM orders INNER JOIN USER ON orders.userId=user.userIdWHERE user.userId=1;

2、
查看用户为张三的订单
SELECT *FROM orders WHERE userId IN
(SELECT userId FROM USER WHERE username=‘张三’);

查询出订单的价格大于800的所有用户信息
SELECT *FROM USER WHERE userId IN (
SELECT DISTINCT userId FROM orders WHERE totalprice>800
);
3、
SELECT *FROM orders LIMIT 0,5;
4、
用户表

public class jdbc {
    public static void main(String[]args)throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8";
        String user ="root";
        String password="root";
        Connection connection=DriverManager.getConnection(url,user,password);
        System.out.println(connection);
        Statement statement=connection.createStatement();
        // 增
        String addsql = "insert into user(username,password,address,phone) values ('小明','12345','北京','1553645250')";
        int addresult = statement.executeUpdate(addsql);
        check(addresult);
        //改
        String updatasql = "update user set address='北京' where username = '山西'";
        int updateresult = statement.executeUpdate(updatasql);
        check(updateresult);
        //删
        String delsql = "delete from user where username ='小明' ";
        int delresult = statement.executeUpdate(delsql);
        check(delresult);
        //结果
        statement.close();
        connection.close();
    }

    public static void check(int result){
        if (result > 0) {
            System.out.println("操作成功");
        } else {
            System.out.println("操作失败");
        }
    }
}

分类表

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

        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8";
        String user ="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        System.out.println(connection);

        Statement statement=connection.createStatement();
        // 增
        String addsql = "insert into category(cid,cname) values ('c005','汽车')";
        int addresult = statement.executeUpdate(addsql);
        check(addresult);
        //改
        String updatasql = "update category set cname='跑车' where cid = 'c005'";
        int updateresult = statement.executeUpdate(updatasql);
        check(updateresult);
        //删
        String delsql = "delete from category where cid = 'c005' ";
        int delresult = statement.executeUpdate(delsql);
        check(delresult);
        //结果
        statement.close();
        connection.close();
    }

    public static void check(int result){
        if (result > 0) {
            System.out.println("操作成功");
        } else {
            System.out.println("操作失败");
        }
    }
}

商品表

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

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

        String url="jdbc:mysql://localhost:3306/store?useUnicode=true&characterEncoding=utf8";
        String user ="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        System.out.println(connection);
        Statement statement=connection.createStatement();
        // 增
        String addsql = "insert into products(pid,name,price,category_id) values ('p011','小当家',450,'c004')";
        int addresult = statement.executeUpdate(addsql);
        check(addresult);
        //改
        String updatesql = "update products set price=420 where pid = 'p011'";
        int updateresult = statement.executeUpdate(updatesql);
        check(updateresult);
        //删
        String delsql = "delete from products where pid = 'p011' ";
        int delresult = statement.executeUpdate(delsql);
        check(delresult);
        //结果
        statement.close();
        connection.close();
    }

    public static void check(int result){
        if (result > 0) {
            System.out.println("操作成功");
        } else {
            System.out.println("操作失败");
        }
    }
}

订单表

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

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

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

        Statement statement=connection.createStatement();
        // 增
        String addsql = "insert into orders(oid,totalprice,userId) values ('o6104',1550.00,'3')";
        int addresult = statement.executeUpdate(addsql);
        check(addresult);
        //改
        String updatasql = "update orders set totalprice=1650 where oid='o6104'";
        int updateresult = statement.executeUpdate(updatasql);
        check(updateresult);
        //删
        String delsql = "delete from orders where oid='o6104'";
        int delresult = statement.executeUpdate(delsql);
        check(delresult);
        //结果子
        statement.close();
        connection.close();
    }
    public static void check(int result){
        if (result > 0) {
            System.out.println("操作成功");
        } else {
            System.out.println("操作失败");
        }
    }
}

订单项表

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

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

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

        Statement statement=connection.createStatement();
        // 增
        String addsql = "insert into orderitem(oid,pid,num) values ('o6102','p002',1)";
        int addresult = statement.executeUpdate(addsql);
        check(addresult);
        //改
        String updatasql = "update orderitem set num='2' where oid = 'o6102'";
        int updateresult = statement.executeUpdate(updatasql);
        check(updateresult);
        //删
        String delsql = "delete from orderitem where oid='o6102' ";
        int delresult = statement.executeUpdate(delsql);
        check(delresult);
        //结果
        statement.close();
        connection.close();
    }

    public static void check(int result){
        if (result > 0) {
            System.out.println("操作成功");
        } else {
            System.out.println("操作失败");
        }
    }
}