源码

drop database if exists bookstore;
create database bookstore;
use bookstore;

create table book(
图书编号 char(20) not null,
类别 varchar(20) not null default '计算机',
书名 varchar(40) not null,
作者 varchar(10) not null,
出版社 varchar(20) not null,
出版时间 date not null,
单价 float(5,2) null,
数量 int(0) null,
折扣 float(3,2) not null,
封面图片 varchar(40) null,
primary key(图书编号));
desc book;
CREATE TABLE members (
用户号 char(18) NOT NULL,
  姓名 char(10) NOT NULL,
  性别 char(2) NOT NULL,
  密码 char(6) NOT NULL,
  联系电话 varchar(20) NOT NULL,
  注册时间 datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (用户号)
) ;
desc book;
create table sell(
 订单号 int(0) not null primary key,
用户号 char(18) not null,
图书编号 char(20) not null,
订购册数 int(5) not null,
订购时间 datetime not null,
订购单价 float(5,2) not null,
是否发货 char(6)  null,
是否收货 char(6) null,
是否结清 char(6) null);
desc sell;

alter table sell
add foreign key(用户号)
references members(用户号)
on delete restrict
on update restrict;
alter table sell
add foreign key(图书编号)
references book(图书编号)
on delete cascade
on update cascade;

alter table members
add check(性别 in('男','女'));

show create table sell; 
show create table members; 
show create table book;

insert into book values
('TP.2462','计算机','计算机基础','陆大强','清华大学出版社','2018-10-19',45.00,45,0.80,null);
 select * from book;

insert into book
(图书编号,书名,作者,出版社,出版时间,单价)
values
('Ts.3010','Oracle','张小刚','北京大学出版社','2019-08-02',28);

alter table book
 modify 折扣 float(3,2) null;

insert into book
(图书编号,书名,作者,出版社,出版时间,单价)
values
('Ts.3010','Oracle','张小刚','北京大学出版社','2019-08-02',28);

update book
set 类别='数据库'
where 图书编号='Ts.3010';
select * from book;

delete from book
where 图书编号='TP.2462';

delete from book
where 类别='数据库';


INSERT INTO `book` VALUES ('TP.2462', '计算机', '计算机应用基础', '陆大强', '清华大学出版社', '2018-10-19', 45.00, 45, 0.80, NULL);
INSERT INTO `book` VALUES ('TP.2463', '计算机', '计算机网络技术', '林力辉', '清华大学出版社', '2020-10-16', 25.50, 31, 0.80, 'D\\pic\\ll.jpg');
INSERT INTO `book` VALUES ('TP.2525', '计算机', 'PHP高级语言', '王大卫', '中国青年出版社', '2020-06-20', 33.25, 50, 0.80, 'D:\\pic\\js.jpg');
INSERT INTO `book` VALUES ('TP.6625', '计算机', 'JavaScript编程', '谢为民', '中国青年出版社', '2020-08-05', 33.00, 60, 0.80, NULL);
INSERT INTO `book` VALUES ('Ts.3010', '数据库', 'ORACLE', '张小刚', '北京大学出版社', '2019-08-02', 28.00, NULL, NULL, NULL);
INSERT INTO `book` VALUES ('Ts.3035', '数据库', 'MYSQL数据库', '李刚', '北京大学出版社', '2020-12-26', 20.00, 500, 0.80, 'D:\\pic\\jp.jpg');
INSERT INTO `book` VALUES ('Tw.1283', '网页设计', 'DW网站制作', '李华', '人民邮电出版社', '2015-10-01', 27.00, NULL, NULL, NULL);
INSERT INTO `book` VALUES ('Tw.2562', '网页设计', 'ASP网站制作', '胡莉惠', '中国青年出版社', '2020-07-24', 30.50, 50, 0.80, NULL);
INSERT INTO `book` VALUES ('Tw.3020', '网页设计', '网页程序设计', '刘辉', '清华大学出版社', '2019-02-15', 25.00, NULL, NULL, NULL);
select * from book;

INSERT INTO `members` VALUES ('A0012', '赵宏宇', '男', '080100', '13601234123', '2017-03-04 18:23:45');
INSERT INTO `members` VALUES ('A3013', '张凯', '男', '080100', '13611320001', '2018-01-15 09:12:23');
INSERT INTO `members` VALUES ('B0022', '王林', '男', '080100', '12501234123', '2019-01-12 08:12:30');
INSERT INTO `members` VALUES ('B2023', '李小冰', '女', '080100', '13651111081', '2019-01-18 08:57:18');
INSERT INTO `members` VALUES ('C0132', '张莉', '女', '123456', '13822555432', '2019-09-23 00:00:00');
INSERT INTO `members` VALUES ('C0138', '李华', '女', '123456', '13822551234', '2018-08-23 00:00:00');
INSERT INTO `members` VALUES ('D1963', '张三', '男', '222222', '51985523', '2019-01-23 08:15:45');
select * from members;

INSERT INTO `sell` VALUES (1, 'C0132', 'TP.2525', 13, 20.00, '2020-11-14 12:13:49', '已发货', NULL, NULL);

alter table sell
drop column 订购时间;
alter table sell
add column 订购时间 datetime not null after 订购单价;

INSERT INTO `sell` VALUES (1, 'C0132', 'TP.2525', 13, 20.00, '2020-11-14 12:13:49', '已发货', NULL, NULL);
select * from sell;

INSERT INTO `sell` VALUES (2, 'D1963', 'TP.2463', 3, 31.50, '2020-11-21 12:25:12', '已发货', NULL, NULL);
INSERT INTO `sell` VALUES (3, 'D1963', 'TP.2525', 6, 23.45, '2020-03-26 12:25:23', '已发货', '已收货', NULL);
INSERT INTO `sell` VALUES (4, 'C0138', 'Ts.3035', 10, 23.50, '2020-08-01 12:13:49', '已发货', '已收货', '已结清');
INSERT INTO `sell` VALUES (5, 'C0138', 'TP.2525', 133, 33.50, '2020-08-01 12:13:49', NULL, NULL, NULL);
INSERT INTO `sell` VALUES (6, 'A3013', 'Tw.2562', 4, 89.00, '2020-08-20 00:00:00', NULL, NULL, NULL);
INSERT INTO `sell` VALUES (7, 'C0138', 'TP.2463', 43, 30.00, '2020-11-08 12:13:49', '已发货', NULL, NULL);
INSERT INTO `sell` VALUES (8, 'C0138', 'Ts.3035', 5, 45.50, '2020-11-21 00:00:00', NULL, NULL, NULL);
INSERT INTO `sell` VALUES (9, 'C0132', 'Tw.1283', 6, 23.00, '2020-11-28 18:23:35', '已发货', '已收货', NULL);
select * from sell;

insert into sell
(订单号,用户号,图书编号,订购册数,订购单价,订购时间)
values
 (10,'D0132','Tw.1283',6,'23.00','2022-10-21 9:54:10');

insert into sell
(订单号,用户号,图书编号,订购册数,订购单价,订购时间)
values
 (10,'D0132','Tw.1283',6,'23.00','2022-10-21 9:54:10');

insert into sell
(订单号,用户号,图书编号,订购册数,订购单价,订购时间)
values
(10,'B2023','Tw.1283',6,'23.00','2022-10-21 9:54:10');


select* from book;select* from sell;

update book
set 图书编号='TP.2626'
where 图书编号='TP.2525';

select* from book;select* from sell;

delete from sell
where 订单号=10;
select* from sell;

desc book;
alter table book
change 类别 图书类别 varchar(20) not null;

desc book;
alter table book
modify 图书类别 varchar(20) not null default '计算机';

desc book;

show databases;
use bookstore;
show tables;
desc book;
select * from book;
show create table sell;
show variables like 'character%';
select 姓名,联系电话,注册时间 from members;
select 书名 as name,作者 as auther,出版社 as publisher from book
where 图书类别='计算机';
select 书名 as 'name of book',作者 as 'Name of Auther',出版社 as publisher from book
where 图书类别='计算机';
select 书名 as name,作者 as auther,出版社 as publisher from book
where 图书类别='数据库';
select 图书编号,书名,数量 from book;
select 图书编号,书名,
 case
when 数量 is null then '尚未进货'
when 数量<5 then '需进货'
when 数量>=5 and 数量<=50 then '库存正常'
else '库存积压'
end as 库存
from book;
 select 图书编号,订购册数,订购单价 from sell;
select 图书编号,订购册数*订购单价 as 订购金额 from sell
 where 是否发货='已发货';
select 图书类别 from book;
select distinct 图书类别 from book;
select 出版社 from book;
select distinct 出版社 from book;
select 图书类别,出版社 from book
select distinct 图书类别,出版社 from book;
select 书名,单价 from book
where 书名='网页程序设计';
select * from book
where 单价>30;
select 书名,单价 from book
where 单价>30 and 图书类别='网页设计';
select 书名,单价,图书类别 from book
where 单价>25 and (图书类别='网页设计' or 图书类别='数据库');
select 书名,单价,图书类别 from book
where 单价>25 and 图书类别 in('网页设计','数据库');
select 书名,单价,图书类别 from book
where 单价>30 and 图书类别!= '数据库';
select 订单号,是否收货,是否结清 from sell
where 是否收货='已收货' and 是否结清='已结清';
select 书名,出版社,单价 from book
where 出版社 in('清华大学出版社','北京大学出版社') and 单价>25;
SELECT 书名,出版社,单价 FROM  Book
   WHERE  (出版社='清华大学出版社' OR 出版社='北京大学出版社' )
      AND 单价>25;
  SELECT 书名,出版社,单价   FROM  Book 
        WHERE  出版社='清华大学出版社'  OR 出版社='北京大学出版社' 
            AND 单价>25;


select 书名,作者 from book
where 出版社='中国青年出版社' and 数量>50;

select 用户号,姓名,注册时间 from members
where 姓名 like '张%';
select 图书编号,书名 from book
where 图书编号 like '%3_';

update book
set 书名='PHP_高级语言'
where 图书编号='TP.2626';
SELECT  *    FROM Book   WHERE 出版时间  BETWEEN '2020-1-1'  AND '2020-12-31'; 
select * from book
where 出版时间>'2020-1-1'and 出版时间<'2020-12-31';
 select * from book
where 出版时间>'2019-1-1'and 出版时间<'2019-12-31';


select *from book,sell;

 select book.图书编号,book.书名,sell.图书编号,sell.订购册数,sell.订购时间 from book,sell;

 select book.图书编号,book.书名,sell.图书编号,sell.订购册数,sell.订购时间 from book,sell
 where book.图书编号=sell.图书编号;

select 图书编号,订购册数*订购单价 as 订购金额 from sell
where 是否发货='已发货';
select book.书名,sell.订购册数*sell.订购单价 as 订购金额 from book,sell
where book.图书编号=sell.图书编号 and  是否发货='已发货';

select 书名,订购册数*订购单价 as 订购金额,订购时间 from book join sell
using(图书编号) 
where  是否发货='已发货';

select 书名,订购册数 from book join sell
using(图书编号)
where 书名='MYSQL数据库';

select 书名,订购册数 from book join sell
using(图书编号)
where 书名='计算机网络技术';

select 书名,订购册数 from book join sell
using(图书编号)
where 订购册数>5;

select 书名,姓名,订购册数 from sell join book
using(图书编号) join members 
using(用户号)
where 书名='MYSQL数据库' and 订购册数>5;

 select * from sell
   join members
   using(用户号)
   where 姓名='张三';

 select * from sell
    where 用户号 in
    (select 用户号 from members
   where 姓名='张三');

SELECT * FROM  Members  WHERE  用户号 IN
   (SELECT 用户号  FROM  Sell  WHERE  图书编号 NOT  IN
 ( SELECT 图书编号 FROM  Book WHERE  书名='网页程序设计'));

select 图书编号,图书类别,单价 from book;

select 图书编号,图书类别,单价 from book
   where 单价>ALL
    (select 单价 from book where 图书类别='网页设计');
SELECT 订购册数 FROM Sell
                                WHERE 图书编号 ='Ts.3035';

SELECT 图书编号,订购册数 FROM  Sell
                 WHERE 订购册数>SOME
                         (SELECT 订购册数 FROM Sell
                                WHERE 图书编号 ='Ts.3035' );

 SELECT 订单号,用户号,图书编号,订购册数 FROM sell   WHERE 用户号= 'C0138 ' UNION
     SELECT 订单号,用户号,图书编号,订购册数 FROM sell   WHERE 图书编号='Tw.2562';

SELECT 图书编号,订购册数,订购单价 FROM sell WHERE 订购册数>30  UNION 
SELECT 图书编号,数量,单价 FROM book WHERE 数量>50; 

 (SELECT 图书编号,订购册数,订购单价 FROM sell WHERE 订购册数>30 ORDER BY 订购单价 LIMIT 2) 
UNION ALL (SELECT 图书编号,数量,单价 FROM book WHERE 数量>50 ORDER BY 单价  LIMIT 2 );




select count(*) as '会员数' from members;
select * from sell;
select count(是否结清) as '结清记录的订单数' from sell;
select count(订购册数) as '订购册数在5以上的订单数'
from sell
where 订购册数>5;

select count(用户号) from sell
where 用户号='C0138';

select count(用户号) as 'D1963的订单数'  from sell
where 用户号='D1963';

 select count(用户号) as'张三的订单数' from sell
 where 用户号=
 (
 select 用户号 from members
 where 姓名='张三'
 );



select count(用户号) as '张三的订单数' from sell
where 用户号 in
 (select 用户号 from members
  where 姓名='张三');

select count(用户号) as '张三的订单数' from sell
join members
using(用户号)
where 姓名='张三';

select max(订购册数) as '订单中最大的订购册数'from sell;

 select 订单号 as '订购册数最大订单号'from sell
 where 订购册数=(select max(订购册数) from sell);

select max(订购册数) as 'Ts.3035最大的订购册数',MIN(订购册数) as 'Ts.3035最小的订购册数' from sell
where 图书编号='Ts.3035';


select max(订购册数) as 'C0138最大的订购册数',MIN(订购册数) as 'C0138最小的订购册数' from sell
where 用户号='C0138';

select max(订购册数) as '张三最大的订购册数',MIN(订购册数) as '张三最小的订购册数' from sell
where 用户号 in
(select 用户号 from members
where 姓名='张三');

select max(订购册数) as '张三最大的订购册数',MIN(订购册数) as '张三最小的订购册数' from sell
join members
using(用户号)
where 姓名='张三';

select sum(订购册数) as '订购总册数' ,avg(订购册数) as '平均订购册数' from sell;

select sum(订购册数) as 'TP.2626订购总册数' ,avg(订购册数) as 'TP.2626平均订购册数' from sell
where 图书编号='TP.2626';

select sum(订购册数) as 'C0138订购总册数' ,avg(订购册数) as 'C0138平均订购册数' from sell
where 用户号='C0138';

select sum(订购册数) as '张三订购总册数' ,avg(订购册数) as '张三平均订购册数' from sell
where 用户号 in 
(select 用户号 from members
where 姓名='张三');

select sum(订购册数) as '张三订购总册数' ,avg(订购册数) as '张三平均订购册数' from sell
join members
using(用户号) 
where 姓名='张三';

select distinct 图书类别 from book;

select 图书类别,count(*) as '各类别库存记录数' from book
group by 图书类别;

select 图书类别,sum(数量) as '各类别库存总数' from book
group by 图书类别;

select 图书类别,avg(数量) as '各类别库存平均数' from book
group by 图书类别;

select 图书编号,count(订单号) as '每本书的订单数',avg(订购册数) as '平均订购册数',
sum(订购册数) as '订购总册数' from sell
 group by 图书编号;

 select 书名,count(订单号) as '每本书的订单数',avg(订购册数) as '平均订购册数'
    ,sum(订购册数) as '订购总册数' from sell
    join book using(图书编号)
    group by 图书编号;

 SELECT 图书类别, 出版社, Sum(数量) AS '库存数'
             FROM Book  GROUP BY 图书类别, 出版社; 

SELECT 用户号, AVG(订购册数) AS '平均订购册数'
            FROM Sell
            GROUP BY 用户号
            HAVING AVG(订购册数) >10; 

SELECT 用户号, AVG(订购册数) 
            FROM Sell
            WHERE 订购册数 >5
            GROUP BY 用户号
            HAVING COUNT(*) > 2; 

select 姓名,avg(订购册数)
 from sell join members using(用户号)
 where 订购册数>5
 group by 用户号
  having count(*)>2;

SELECT * FROM Book
ORDER BY 出版时间; 

select * from book
order by 出版时间 desc;

SELECT * FROM Book
                ORDER BY 出版时间 desc;

 SELECT  *  FROM Members
	ORDER BY 注册时间
	LIMIT 5; 

select * from book
 order by 图书编号
  limit 3,3;

select * from book;
select * from sell;
select 书名,订购册数,订购时间 from sell join book using(图书编号);

create view b_s as
select 书名,订购册数,订购时间 from sell join book using(图书编号);
show tables;
select * from b_s;

select 书名,avg(订购册数) from b_s
group by 书名;
create view s_avg(name,sale_avg) as
select 书名,avg(订购册数) from b_s group by 书名;
select * from s_avg;
select * from s_avg
where sale_avg>5;
show tables;


select * from sell;
select * from book;
select * from b_s;
 update b_s
 set 订购册数=4
 where 书名='DW网站制作';
select * from b_s;
 select * from sell;
select * from book;

insert into b_s values('计算机基础',10);
delete from b_s
 where 书名='DW网站制作';

 create view jsj_book as
select * from book
where 图书类别='计算机';
 show tables;
select * from jsj_book;

INSERT INTO  book  VALUES('TP.0837','计算机','Office应用实例','张瑜海','人民邮电出版社','2020-10-21',34.5,NULL,NULL,NULL);
select * from jsj_book;
select * from book;
INSERT INTO  jsj_book  VALUES('TP.0838','计算机','Office应用实例2','张瑜海','人民邮电出版社','2020-10-21',34.5,NULL,NULL,NULL);
select * from jsj_book;
select * from book;

INSERT INTO  book  VALUES('TP.0839','数据库','Office应用实例3','张瑜海','人民邮电出版社','2020-10-21',34.5,NULL,NULL,NULL); 
select * from jsj_book;
select * from book;
INSERT INTO  jsj_book  VALUES('TP.0840','数据库','Office应用实例4','张瑜海','人民邮电出版社','2020-10-21',34.5,NULL,NULL,NULL);
select * from jsj_book;
select * from book;

UPDATE jsj_book 
SET 单价 = 单价*(1-0.05);
select * from jsj_book;
select * from book;
DELETE FROM jsj_book 
WHERE 出版社 ='中国青年出版社';
select * from jsj_book;
select * from book;
 DELETE FROM book
WHERE 出版社 ='中国青年出版社';
select * from book;
 ALTER VIEW jsj_book
            AS
            SELECT 图书编号,书名,单价  FROM  Book
            WHERE 图书类别 = '计算机';
show tables;
drop view jsj_book;
show tables;
drop view b_s;
drop view s_avg;
show tables;

首先，创建名为bookstore的数据库，并使用该数据库：

drop database if exists bookstore;
create database bookstore;
use bookstore;

接下来，创建book表：

create table book(
图书编号 char(20) not null,
类别 varchar(20) not null default '计算机',
书名 varchar(40) not null,
作者 varchar(10) not null,
出版社 varchar(20) not null,
出版时间 date not null,
单价 float(5,2) null,
数量 int(0) null,
折扣 float(3,2) not null,
封面图片 varchar(40) null,
primary key(图书编号));
desc book;

然后，创建members表：

CREATE TABLE members (
用户号 char(18) NOT NULL,
姓名 char(10) NOT NULL,
性别 char(2) NOT NULL,
密码 char(6) NOT NULL,
联系电话 varchar(20) NOT NULL,
注册时间 datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (用户号)
);
desc book;

接下来，为sell表添加外键约束：

alter table sell
add foreign key(用户号)
references members(用户号)
on delete restrict
on update restrict;
alter table sell
add foreign key(图书编号)
references book(图书编号)
on delete cascade
on update cascade;

为members表添加检查约束：

alter table members
add check(性别 in('男','女'));

显示创建表的语句：

show create table sell; 
show create table members; 
show create table book;

插入数据：

insert into book values
('TP.2462','计算机','计算机基础','陆大强','清华大学出版社','2018-10-19',45.00,45,0.80, null);
 select * from book;

Views: 2