数据库练习

创建商品表

 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,"男");