mysql练习

某网上商城数据库表结构如下：

# 创建用户表
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), #主键
  foreign key(oid) references orders(oid),
  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);

15.1 综合练习1-【多表查询】

1>查询所有用户的订单

SELECT u.userId,username,oid,totalprice FROM `user` AS u
INNER JOIN orders AS o
ON u.`userId` =o.`userId`;

2>查询用户id为 1 的所有订单详情

SELECT o.oid,o.`totalprice`,o.`userId`,u.`username`,om.`pid`,p.`name`
FROM orders AS o
INNER JOIN `user` AS u
ON o.`userId`=u.`userId`
INNER JOIN orderitem AS om
ON om.`oid`=o.`oid`
INNER JOIN products AS p
ON om.`pid`=p.`pid`
WHERE u.`userId`=1;

15.2 综合练习2-【子查询】

1>查看用户为张三的订单

WHERE userId=(
SELECT userId
FROM `user`
WHERE username='张三'
);

2>查询出订单的价格大于800的所有用户信息。

WHERE userId IN(
SELECT DISTINCT userId
FROM orders
WHERE totalprice>800
);

15.3 综合练习3-【分页查询】

1>查询所有订单信息，每页显示5条数据

SELECT *
FROM products
LIMIT 0,5;

15.4 综合练习4-【使用JDBC实现对每张表的增、删、改】

public class TestjdbcDml {
    public static void main(String[] args) throws  Exception{
        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mark?useUnicode=true&characterEncoding=utf8","root","123456");
        //3.获取发送SQL语句的对象   Statement
        Statement sta = conn.createStatement();
        //增加，修改，删除用户
        String usql1 = "insert into user(username,password,address,phone) values('DHF','123','河南周口','123456')";
        String usql2 = "update user set username = 'DHY',passwprd = '12345' where userid = 1";
        String usql3 = "delete from user where userid = 5";
        //增加，修改，删除商品分类
        String csql1 = "insert into category(cid,cname) values('c005','商业用品')";
        String csql2 = "update category set cname = '办公用品' where cid='c005'";
        String csql3 = "delete from catgory where cid = 'c005'";
        //增加，修改，删除商品
        String psql1 = "insert into products(pid,`name`,price,category_id) values('p011','黑豆',5555.55,'c004')";
        String psql2 = "update products set name = '大米' where pid = 'p011'";
        String psql3 = "delete from producats where pid = 'p011'";
        //增加，修改，删除订单
        String osql1 = "insert into `order`(oid,totalprice,userid) values('o6104',18000,2)";
        String osql2 = "update order set userid = 3 where oid = 'o6104'";
        String osql3 = "delete from order where oid = 'o6104'";
        //增加，修改，删除订单详情
        String oisql1 = "insert into orderitem(oid,pid,num) values('o6104','p011',2)";
        String oisql2 = "update orderitem set num = 3 where oid = 'o6104'";
        String oisql3 = "delete from orderitem where oid = 'o6104'and pid='p011'";

        int user1 = sta.executeUpdate(usql1);//增加一个用户
        if (user1 > 0){
            System.out.println("用户新增成功");
        }else {
            System.out.println("用户新增失败");
        }
        int catgroy1 = sta.executeUpdate(csql1);//增加一个商品分类
        if ( catgroy1 > 0){
            System.out.println("商品分类新增成功");
        }else {
            System.out.println("商品分类新增失败");
        }
        int products1 = sta.executeUpdate(psql1);//增加一个商品信息
        if (products1 > 0){
            System.out.println("商品新增成功");
        }else {
            System.out.println("商品新增失败");
        }
        int order1 = sta.executeUpdate(osql1);//增加一个订单信息
        if ( order1 > 0 ){
            System.out.println("订单新增成功");
        }else {
            System.out.println("订单新增失败");
        }
        int orderitem1 = sta.executeUpdate(oisql1);//增加一个订单详情信息

        if (orderitem1 > 0){
            System.out.println("订单详情新增成功");
        }else {
            System.out.println("订单详情新增失败");
        }

        sta.close();
        conn.close();
    }
}