编辑代码

CREATE DATABASE bookdb;
use bookdb;
CREATE TABLE book (
	bid int,
	bname varchar(255),
	author varchar(255),
	price double,
    publisher varchar(255),
    discount double,
    cid int
);
CREATE TABLE user1
(
    uid char(4) primary key not null,
    uname varchar(20),
    email varchar(20),
    tnum varchar(15),
    score int
);
CREATE TABLE category
(
    cid int primary key not null,
    cname varchar(50) not null
);
CREATE TABLE b_order(
    uid int,
    bid int,
    ordernum int,
    orderdate varchar(255)
);
INSERT INTO book VALUES (1, '中国时代', '石永刚', 39.0,'作家出版社',27.8,1);
INSERT INTO book VALUES (2, '大国工匠',  '于万夫',76.0, '石油工业出版社',71,1);
INSERT INTO book VALUES (3, '给孩子的科技史',  '吴军', 69.0,'中信出版社',49,2);
INSERT INTO book VALUES (4, '故乡的泥土（全二册）',  '章云天', 98.0,'作家出版社',98,4);
INSERT INTO book VALUES (5, '老人与海',  '李四家隔壁',66, '北京时代化文书局',76,3);
INSERT INTO book VALUES (6, '舒克贝塔传',  '赵六家隔壁',55, '浙江少年儿童出版社',54,6);
INSERT INTO book VALUES (7, '漫画中国第一季（全书册）',  '上海印象', 76,'北方妇女儿童出版社',118,3);
INSERT INTO user1 VALUES
('1001','春天','spring@163.com','13320105678',20),
('1002','平平人生','123456@123.com','12345678901',300),
('1003','四十不惑','123456@125.com','12345678902',1000),
('1004','桃花岛主','154326@qq.com','158838121234',500),
('1005','水灵','654321@qq.com','1306811234',600),
('1006','感动心灵','gandong@tom.com','13641151234',150);
INSERT INTO category VALUES
(1,'历史'),
(2,'科技'),
(3,'儿童'),
(4,'小说');
 INSERT INTO b_order VALUES(1001,1,2,'2020-03-12');
 INSERT INTO b_order VALUES(1001,3,1,'2020-04-15');
 INSERT INTO b_order VALUES(1001,1,1,'2020-09-15');
 INSERT INTO b_order VALUES(1003,7,1,'2009-12-14');
 INSERT INTO b_order VALUES(1003,3,1,'2020-10-10');
 INSERT INTO b_order VALUES(1005,5,1,'2009-08-17');
 INSERT INTO b_order VALUES(1006,5,1,'2020-09-01');
 INSERT INTO b_order VALUES(1006,1,2,'2020-10-21');
INSERT INTO b_order VALUES(1006,7,2,'2020-12-03');
SELECT bname,price

FROM book 
ORDER BY price DESC;
SELECT bname,price
FROM book
ORDER BY price DESC
LIMIT 3;
SELECT *
FROM b_order
WHERE orderdate='2020-10';
SELECT  DISTINCT ordernum
FROM  b_order;