MySQL是一个关系型数据库管理系统,MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
以下是MySQL语句:创建库,创建表,增删改查,子查询,高级查询等等。
-- 使用sql语句创建数据库BooksManager,并创建如下结构的表。
create database if not exists BooksManager character set utf8 collate utf8_general_ci;
use BooksManager;
-- 表1: Authors表
drop table if exists Author;
create table if not exists Author(
AuthorID int primary key auto_increment not null comment "标识列",
AuthorName varchar(40) not null comment "作者姓名",
Sex enum("男","女") not null comment "性别",
Birthday datetime comment "生日",
Email varchar(50) default "e@books.com" comment "邮箱",
TelPhone varchar(60) comment "电话",
City varchar(50) default "北京" comment "城市"
)engine=innodb;
-- 表2 Publishers出版社表
drop table if exists Publishers;
create table if not exists Publishers(
PublisherID int primary key auto_increment not null comment "标识列",
PublisherName varchar(200) not null comment "出版社名称",
Address varchar(60) comment "出版社地址",
TelPhone varchar(40) comment "出版社电话"
)engine=innodb;
-- 表 3 Categories类别表
drop table if exists Categories;
create table if not exists Categories(
CategoryID int primary key auto_increment not null comment "类别编号",
CategoryName varchar(50) not null comment "类别名",
Description varchar(1000) comment "类别描述"
)engine=innodb;
-- 表4 Books图书表
drop table if exists Books;
create table if not exists Books(
BookCode varchar(20) primary key not null comment "图书编号",
BookName varchar(200) not null comment "图书名称",
AuthorID int not null comment "作者编号",
PublisherID int not null comment "出版社编号",
UnitPrice float not null default 1.0 comment "单价",
CategoryID int not null comment "图书类别",
Quantity int default 10 comment "图书数量",
foreign key (AuthorID) references Author (AuthorID),
foreign key (PublisherID) references Publishers (PublisherID),
foreign key (CategoryID) references Categories (CategoryID)
)engine=innodb;
-- 表5 Customers会员表
drop table if exists Customers;
create table if not exists Customers(
CustomerID char(5) primary key not null comment "客户编号",
CustomerName varchar(40) not null comment "客户名称",
PWD varchar(6) not null comment "网站登录密码",
RegDate timestamp not null default current_timestamp comment "注册日期",
City varchar(15) comment "所在城市",
TelPhone varchar(40) comment "电话",
PostalCode varchar(20) comment "邮政编码",
Grade int not null default 1 comment "会员等级",
Rebeat float not null default 1.0 comment "折扣",
Birthday datetime not null comment "生日",
Upoint int default 0 comment "积分"
)engine=innodb;
-- 表6 Orders 订单表
drop table if exists Orders;
create table if not exists Orders(
OrderID int primary key auto_increment not null comment "订单编号",
OrderDate timestamp not null default current_timestamp comment "订购日期",
CustomerID char(5) not null comment "客户姓名",
Total float comment "总金额",
SignforDate datetime comment "客户签收日期",
State int not null default 0 comment "订单状态0:在线下订单完成,1:图书签收完成2:退单"
)engine=innodb;
-- 表7 OrderDetails订单详表
drop table if exists OrderDetails;
create table if not exists OrderDetails(
ID int primary key auto_increment not null,
OrderID int not null comment "订单编号",
BookCode varchar(20) not null comment "图书编号",
Quantity int not null default 1 comment "订购数量",
foreign key (OrderID) references Orders (OrderID),
foreign key (BookCode) references Books (BookCode)
)engine=innodb;
-- 二、使用insert语句向表中插入如下数据
-- 1、向会员表customers中插入如下数据(没有的数据使用默认值)
insert into Customers values
("10001","紫罗兰","909088",default,"北京","13800532","100025",1,"1","1977-03-02",0),
("10002","陶洛" ,"561232",default,"广州","150203324","510140",1,"1","1981-06-28",1),
("10008","喜来乐","778899",default,"西安","151978342","710002",3,"0.8","1980-09-12",1);
-- 2、插入作者信息
insert into Author values
(default,"王珊", "女","1968-01-26","ws@163.com", "131099922","北京"),
(default,"潘晓燕","女","1976-02-08","pxy@sina.com","151987098","广州"),
(default,"蒋涛", "男","1982-05-23","jt@tom.com", "132987399","兰州"),
(default,"程杰", "男","1978-5-9", "cj@sina.cn", "152321975","石家庄");
-- 3、图书类别
insert into Categories values
(default,"外语类","包含不同语种的图书"),
(default,"科技类","人类成长的各阶段图书"),
(default,"小说类","包含武侠、科幻等图书"),
(default,"计算机类","编程网页设计等");
-- 5、出版社表
insert into Publishers values
(default,"清华大学出版社","北京","010-85812264"),
(default,"中信出版社","上海","13151356521"),
(default,"东方出版社","上海","13245679898"),
(default,"新华出版社","北京","18023647894");
-- 4、图书表books
insert into Books values
("209223","标准英语教材", 1,1,"43.0",1,20),
("302112","失落的秘符", 2,2,"50",2,10),
("102892","神秘的宇宙", 3,1,"30",3,15);
-- 5、订单表orders
insert into orders values
(null,'2018-12-06','10001',50,'2018-12-12',1),
(null,'2019-2-06','10002',110,'2019-2-5',1),
(null,'2019-3-3','10001',103,'2019-3-20',0);
-- 6、订单详单orderdetails
insert into orderdetails values
(null,1,'302112',1),
(null,2,'102892',2),
(null,2,'302112',1),
(null,3,'102892',2);
-- 1、统计一个月内总订单金额最大的前2位会员信息,
-- 要求显示"会员名称、订单总金额",然后按“订单总金额”降序排序。
select customername as 会员名称,sum(total) as 订单总金额 from orders a,customers b
where b.customerid=a.customerid and orderdate> date_sub(now(),interval 30 day)
group by customername order by total desc limit 2;
-- 2、查询编号为“10001”的会员在半年内的详单信息,
-- 要求显示“订单编号(OrderCode)、会员名称、图书名称、订购数量、订购日期”,
-- 最后按订单编号和图书编号进行升序排序。
select d.orderid as 订单编号,customername as 会员名称,bookname as 图书名称,
d.quantity as 订购数量,orderdate as 订购日期
from customers a,orders b,books c,OrderDetails d
where a.customerid=b.customerid and b.orderid=d.orderid and c.bookcode=d.bookcode and a.customerid='10001'
order by d.orderid asc;
-- 3、查找哪些会员还没有订购过图书,
-- 将这些会员的姓名、居住城市、出生日期显示出来,并按出生日期降序排序。
select customername as 会员姓名,city as 居住城市,birthday as 出生日期
from customers a left outer join orders b
on a.customerid=b.customerid
where orderid is null
order by birthday desc;
-- 应用函数进行查询:
-- 1、查找图书销售网中一个月内的订单,要求显示“订单编号、订购日期、客户编号、总费用”。
-- 实现思路:
-- (1) 分析SELECT语句中的关键代码,关键问题是如何确定一个月内的订单。首先,我们规定一个月为30天。
-- 其次,确定30天之内的订单:即当前日期减去订购日期小于30,即当前日期-订购日期<30。
-- (2) 需要使用日期函数TIMESTAMPDIFF函数计算日期的差值。
select orderid as 订单编号,orderdate as 订购日期,customerid as 客户编号,total as 总费用
from orders where orderdate> date_sub(now(),interval 30 day);
-- 2、使用SELECT语句查找问题订单。
-- 问题订单是指客户下订单后,在网站规定的时间内客户仍未签收的订单,
-- 网站应该及时将这些问题订单查找出来,分析原因及时处理。
-- 本次任务:查找距订购日期15天以上,会员仍未签收的订单。
select * from orders where date_sub(orderdate,interval -15 day)<signfordate;
-- 3、查找今天过生日的会员。
select customername as 会员姓名,birthday as 生日
from customers where Birthday=now();