创建商品表
CREATE TABLE product(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),pprice INT)
插入数据
INSERT INTO product(pname,pprice) VALUE("手机",2300),
("电脑",5600),
("照相机",1200),
("投影仪",2500);
创建用户表
CREATE TABLE USER(uid INT PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(20),uage INT)
插入数据
INSERT INTO USER(uname,uage) VALUE("李三",20),
("张四",23),
("赵五",25),
("孙六",18),
("孙悟空",24);
创建订单表
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 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 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="电脑";
查询订单是909090对应的用户名和商品名
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))
插入数据
INSERT INTO celebrity(sname,sage,ssex) VALUE("李白",34,"男"),
("杜甫",24,"男"),
("白居易",31,"男"),
("李商隐",40,"女"),
("苏轼",26,"男"),
("辛弃疾",22,"男");
创建代表作品表
CREATE TABLE works(wid INT PRIMARY KEY AUTO_INCREMENT,wwork VARCHAR(20),wdynasty VARCHAR(10))
插入数据
INSERT INTO works(wwork,wdynasty) VALUE("将进酒","唐代"),
("蜀道难","唐代"),
("夜雨寄北","唐代"),
("静夜思","唐代"),
("望岳","唐代"),
("钱塘湖春行","南宋"),
("念奴娇赤壁怀古","北宋"),
("水调歌头","唐代");
创建总结表
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 summary VALUE(1,1,"天生我材必有用"),
(1,2,"危乎高哉!"),
(6,8,"明月几时有"),
(3,6,"乱花渐欲迷人眼"),
(4,3,"却话巴山夜雨时"),
(5,7,"大江东去"),
(2,5,"一览众山小"),
(1,4,"举头望明月");
查询名人表中性别是男的作者
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="白居易";
查询年纪在25岁到30岁之间的个数
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="望岳";
新增名人表中的一个作者王维,年纪25,性别为男
INSERT INTO celebrity(sname,sage,ssex) VALUE("王维",25,"男");