编辑代码

CREATE DATABASE bookdb;
use bookdb;
CREATE table user
(
    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 book
(
    bid CHAR(4) PRIMARY KEY NOT NULL,
    bname VARCHAR(50) NOT NULL,
    author CHAR(8),
    price FLOAT,
    publisher VARCHAR(50),
    discount INT,
    cid INT,
    constraint  CK_price CHECK(price > 0),
    CONSTRAINT FK_cid FOREIGN KEY (cid) REFERENCES category(cid)
);

 CREATE TABLE b_order
(
    id char(4) PRIMARY KEY not null,
    bid CHAR(4)  NOT NULL,
    uid char(4) not null,
    ordernum int default 1,
    orderdate datetime,
    deliverydate datetime
);

INSERT INTO user VALUES
('1001','春天','spring@163.com','13320105678',20),
('1002','平平人生','123456@126.com','13545153456',300),
('1003','四十不惑','123456@126.com','18688166789',1000),
('1004','桃花岛主','654321@qq.com','13068011234',600),
('1005','水灵','654321@163.com','15838182345',150),
('1006','感动心灵','gandong@tom.com','13641151234',500);

select * from user;


INSERT INTO category VALUES
(1, '历史'),
(2, '科技'),
(3, '儿童'),
(4, '小说');
select *from category;

insert into book VALUES
('0001','中国时代','师永刚',39.0,'作家出版社',27.8,1),
('0002','大国工匠','于万夫',76.0,'石油工业出版社',71,1),
('0003','给孩子的科技史','吴军',39.0,'中信出版社',49,2),
('0004','故乡的泥土（全二册）','章云天',39.0,'作家出版社',98,4),
('0005','老人与海','海明威',39.0,'北京华文时代书局',20.6,4),
('0006','舒克贝塔传','郑渊洁',39.0,'浙江少年儿童出版社',18,3),
('0007','漫画中国第一季（全手册）','上海印象',140,'北京妇女儿童出版社',118.8,3);

select * from book;

insert into b_order VALUES
('0001','1001',1,2,'2020-03-12',null),
('0002','1001',3,1,'2020-04-15',null),
('0003','1001',1,1,'2020-09-15',null),
('0004','1003',7,1,'2019-12-14',null),
('0005','1003',3,1,'2020-10-10',null),
('0006','1005',5,1,'2019-08-17',null),
('0007','1005',7,3,'2020-11-12',null),
('0008','1006',5,1,'2020-10-21',null),
('0010','1006',7,2,'2020-12-03',null);

select *from b_order;
system cls;
  
DELIMITER //
CREATE PROCEDURE proc_1()
BEGIN
    SELECT * FROM user WHERE uid='1004';
END//
DELIMITER ;

CALL proc_1();

DELIMITER //
CREATE PROCEDURE proc_2(IN username varchar(20),OUT userscore int)
BEGIN
    select score into userscore from user where uname=username;
END//
DELIMITER ;

set @score_result=0;
CALL proc_2('平平人生',@score_result);
select @score_result;

set @score_result=0;
CALL proc_2('感动心灵',@score_result);
select @score_result;