内连接—inner join

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for categroy
-- ----------------------------
DROP TABLE IF EXISTS categroy;
CREATE TABLE categroy (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of categroy
-- ----------------------------
INSERT INTO categroy VALUES ('1', '手机数码');
INSERT INTO categroy VALUES ('2', '电脑办公');
INSERT INTO categroy VALUES ('3', '烟酒茶糖');
INSERT INTO categroy VALUES ('4', '鞋靴箱包');

-- ----------------------------
-- Table structure for orderlist
-- ----------------------------
DROP TABLE IF EXISTS orderlist;
CREATE TABLE orderlist (
  id int(11) NOT NULL AUTO_INCREMENT,
  number varchar(20) DEFAULT NULL,
  uid int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ou_fk1 (uid),
  CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orderlist
-- ----------------------------
INSERT INTO orderlist VALUES ('1', 'hw1', '1');
INSERT INTO orderlist VALUES ('2', 'hw2', '1');
INSERT INTO orderlist VALUES ('3', 'hw3', '2');
INSERT INTO orderlist VALUES ('4', 'hw4', '2');
INSERT INTO orderlist VALUES ('5', 'hw5', '3');
INSERT INTO orderlist VALUES ('6', 'hw6', '3');
INSERT INTO orderlist VALUES ('7', 'hw7', null);

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS product;
CREATE TABLE product (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  cid int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY cp_fk1 (cid),
  CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES categroy (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO product VALUES ('1', '华为手机', '1');
INSERT INTO product VALUES ('2', '苹果手机', '1');
INSERT INTO product VALUES ('3', '联想电脑', '2');
INSERT INTO product VALUES ('4', '苹果电脑', '2');
INSERT INTO product VALUES ('5', '中华香烟', '3');
INSERT INTO product VALUES ('6', '玉溪香烟', '3');
INSERT INTO product VALUES ('7', '计生用品', null);

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO users VALUES ('1', '张三', '21');
INSERT INTO users VALUES ('2', '李四', '25');
INSERT INTO users VALUES ('3', '王五', '20');
INSERT INTO users VALUES ('4', '周奇', '18');
INSERT INTO users VALUES ('5', '赵六', '20');

-- ----------------------------
-- Table structure for us_pro
-- ----------------------------
DROP TABLE IF EXISTS us_pro;
CREATE TABLE us_pro (
  upid int(11) NOT NULL AUTO_INCREMENT,
  uid int(11) DEFAULT NULL,
  pid int(11) DEFAULT NULL,
  PRIMARY KEY (upid),
  KEY up_fk1 (uid),
  KEY up_fk2 (pid),
  CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES users (id),
  CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product (id)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of us_pro
-- ----------------------------
INSERT INTO us_pro VALUES ('1', '1', '1');
INSERT INTO us_pro VALUES ('2', '1', '2');
INSERT INTO us_pro VALUES ('3', '1', '3');
INSERT INTO us_pro VALUES ('4', '1', '4');
INSERT INTO us_pro VALUES ('5', '1', '5');
INSERT INTO us_pro VALUES ('6', '1', '6');
INSERT INTO us_pro VALUES ('7', '2', '1');
INSERT INTO us_pro VALUES ('8', '2', '2');
INSERT INTO us_pro VALUES ('9', '2', '3');
INSERT INTO us_pro VALUES ('10', '2', '4');
INSERT INTO us_pro VALUES ('11', '2', '5');
INSERT INTO us_pro VALUES ('12', '2', '6');
INSERT INTO us_pro VALUES ('13', '2', '7');
INSERT INTO us_pro VALUES ('14', '3', '1');
INSERT INTO us_pro VALUES ('15', '3', '2');
INSERT INTO us_pro VALUES ('16', '3', '3');
INSERT INTO us_pro VALUES ('17', '3', '4');
INSERT INTO us_pro VALUES ('18', '3', '5');
INSERT INTO us_pro VALUES ('19', '3', '6');
INSERT INTO us_pro VALUES ('20', '3', '7');
INSERT INTO us_pro VALUES ('21', '4', '1');
INSERT INTO us_pro VALUES ('22', '4', '2');
INSERT INTO us_pro VALUES ('23', '4', '3');
INSERT INTO us_pro VALUES ('24', '4', '4');
INSERT INTO us_pro VALUES ('25', '4', '5');
INSERT INTO us_pro VALUES ('26', '4', '6');
INSERT INTO us_pro VALUES ('27', '4', '7');
INSERT INTO us_pro VALUES ('28', '5', '1');
INSERT INTO us_pro VALUES ('29', '5', '2');
INSERT INTO us_pro VALUES ('30', '5', '3');
INSERT INTO us_pro VALUES ('31', '5', '4');
INSERT INTO us_pro VALUES ('32', '5', '5');
INSERT INTO us_pro VALUES ('33', '5', '6');
INSERT INTO us_pro VALUES ('34', '5', '7');
INSERT INTO us_pro VALUES ('35', '1', '7');