mysql作业·

 

//商品表
CREATE TABLE product(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),pprice INT)
//用户表
CREATE TABLE USER(uid INT PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(20),uage INT)
//订单表
CREATE TABLE orders(opid INT,CONSTRAINT FOREIGN KEY(opid) REFERENCES product(pid),ouid INT,CONSTRAINT FOREIGN KEY(ouid) REFERENCES USER(uid),onumber INT)

INSERT INTO product(pname,pprice) VALUE("手机",2300),
                                       ("电脑",5600),
                                       ("照相机",1200),
                                       ("投影仪",2500);

INSERT INTO USER(uname,uage) VALUE("李三",20),
                                  ("张四",23),
                                  ("赵五",25),
                                  ("孙六",18),
                                  ("孙悟空",24);

INSERT INTO orders VALUE(1,1,123123),
                        (1,1,112233),
                        (2,1,234567),
                        (2,5,787878),
                        (3,4,343421),
                        (3,5,909090),
                        (4,2,212112),
                        (4,1,343421);

SELECT * FROM product;

SELECT * FROM USER;

SELECT * FROM orders;

SELECT COUNT(*) FROM USER WHERE uname LIKE "%孙%";

SELECT uname FROM USER ORDER BY uage DESC LIMIT 0,2;

SELECT pname FROM product,USER,orders WHERE product.pid=orders.opid AND user.uid=orders.ouid AND uname="李三";

SELECT uname FROM product,USER,orders WHERE product.pid=orders.opid AND user.uid=orders.ouid AND pname="电脑";

SELECT uname,pname FROM product,USER,orders WHERE product.pid=orders.opid AND user.uid=orders.ouid AND onumber=909090;

SELECT * FROM product ORDER BY pprice DESC LIMIT 0,2;

CREATE TABLE celebrity(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(20),sage INT,ssex VARCHAR(10))

CREATE TABLE works(wid INT PRIMARY KEY AUTO_INCREMENT,wwork VARCHAR(20),wdynasty VARCHAR(10))

CREATE TABLE summary(sid INT,CONSTRAINT FOREIGN KEY(sid) REFERENCES celebrity(sid),wid INT,CONSTRAINT FOREIGN KEY(wid) REFERENCES works(wid),sassess VARCHAR(50))

INSERT INTO celebrity(sname,sage,ssex) VALUE("李白",34,"男"),
                                            ("杜甫",24,"男"),
                                            ("白居易",31,"男"),
                                            ("李商隐",40,"女"),
                                            ("苏轼",26,"男"),
                                            ("辛弃疾",22,"男");

INSERT INTO works(wwork,wdynasty) VALUE("将进酒","唐代"),
                                       ("蜀道难","唐代"),
                                       ("夜雨寄北","唐代"),
                                       ("静夜思","唐代"),
                                       ("望岳","唐代"),
                                       ("钱塘湖春行","南宋"),
                                       ("念奴娇赤壁怀古","北宋"),
                                       ("水调歌头","唐代");

INSERT INTO summary VALUE(1,1,"天生我材必有用"),
                         (1,2,"危乎高哉！"),
                         (6,8,"明月几时有"),
                         (3,6,"乱花渐欲迷人眼"),
                         (4,3,"却话巴山夜雨时"),
                         (5,7,"大江东去"),
                         (2,5,"一览众山小"),
                         (1,4,"举头望明月");

SELECT * FROM celebrity;

SELECT * FROM works;

SELECT * FROM summary;

SELECT sname,ssex FROM celebrity WHERE ssex="男";

SELECT sname FROM celebrity,works,summary WHERE celebrity.sid=summary.sid AND works.wid=summary.wid AND wwork="静夜思";

SELECT sname,sage FROM celebrity WHERE sname="李白" OR sname="杜甫";

SELECT ssex,COUNT(*) FROM celebrity GROUP BY ssex;

SELECT sassess FROM celebrity,works,summary WHERE celebrity.sid=summary.sid AND works.wid=summary.wid AND sname="白居易";

SELECT COUNT(*) FROM celebrity WHERE sage>25 AND sage<30;

SELECT * FROM celebrity ORDER BY sage LIMIT 0,2;

SELECT sname,wwork,sage,sassess FROM celebrity,works,summary WHERE celebrity.sid=summary.sid AND works.wid=summary.wid AND sname="李白";

UPDATE works SET wdynasty="北宋" WHERE wwork="望岳";

INSERT INTO celebrity(sname,sage,ssex) VALUE("王维",25,"男");