【MySQL~（详细）案例】-编程学习网

SQL目录

以案例来学习知识，也是一种不错的学习方法。可以更好的给人以兴趣，毕竟，实践才是唯一的真理。

例3-1 创建一个名为“Bookstore”的数据库：

create database Bookstore;

例3-2 创建一个名为“Bookstore” 的数据库，采用字符集 gb2312 和校对规则gb2312_chinese_ci

【注意：mysql校对规则的命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci(对大小写不敏感）、_cs(对大小写敏感)、_bin(二元)结束】:

create database lxwdefault character set gb2312collate gb2312_chinese_ci;

例3-3 修改数据库 “Bookstore”的默认字符集为 “utf8mb4”、校对规则为“utf8mb4_09900_ai_ci”:

alter database lxwdefault character set utf8mb4default collate utf8mb4_09900_ai_ci;

Unknown collation: ‘utf8mb4_09900_ai_ci’
报错原因：
生成转储文件的数据库版本为8.0,要导入sql文件的数据库版本为5.6,因为是高版本导入到低版本，引起1273错误

解决方法：
打开sql文件，将文件中的所有
utf8mb4_0900_ai_ci替换为utf8_general_ci
utf8mb4替换为utf8
保存后再次运行sql文件，运行成功

alter database lxwdefault character set utf8default collate utf8_general_ci;

例3-4：假设已经创建了数据库Bookstore，则在该数据库中创建图书目录表“book”。

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

例3-5 ：假设已经在数据库Bookstore中创建了表book，表中存在”书名“列。在表book中增加”浏览次数“列，并将表中的”书名“列删除。

use Bookstore;alter table bookadd 浏览次数 tinyint null,drop column 书名；

例 3-6：假设数据库Bookstore中已经存在表book，将表book重命名为mybook。

use Bookstore;alter table bookrename to mybook;

例 3-7：假设数据库Bookstore中已经存在表mybook和members,将表mybook重命名为mylist，表Members 重命名为memberlist。

use Bookstore;rename table mybook to booklist,members to memberlist;

例 3-8：假设数据库Bookstore中有一个表book，创建表book的一个名为book_copy1的副本。

create table book_copy1 like book;

例 3-9：创建表book的一个名为book_coopy2的副本，并且复制其内容。

create table book_copy2as(select * from book);

例 3-10:删除表test：

drop table if exists test;

例 3-11：显示Bookstore数据库建立的数据表文件。

use Bookstore;show tables;

例 3-12：用DESCRIBE语句查看book表中列的信息。

describe book;

例 3-13：查看book表”图书编号“列的信息。

desc book 图书编号;

疑惑：Empty set (0.00 sec)
解决：提示你实体表为空，说的是你的数据库或表里数据为空，添加上数据就可以了。

例3-14：创建表book_copy,将”书名“定义为主键。

create table book_copy (图书编号 varchar(6) null,书名 varchar(20) not null primary key,出版日期 date);

例3-15：创建course表来记录每门课程的学生的”学号“”姓名“”课程号“”学分““毕业日期“。其中”学号“”课程号“”毕业日期“构成复合主键。

create table course(学号 varchar(6) not null,姓名 varchar(8) not null,毕业日期 date not null,课程号 varchar(3),学分 tinyint,primary key (学号,课程号,毕业日期));

例 3-16：参照例3-15中的course表创建course1表，将索引命名为“INEDX_course”.

create table coursel(学号 varchar(6) not null,姓名 varchar(18) not null,毕业日期 datetime not null,课程号 varchar(3),学分 tinyint,primary key index_course1(学号,课程号,毕业日期));

例 3-17：在表book_copy1中，将“图书编号”作为主键，将“书名”列定义为一个替代键。

create table book_copy3(图书编号 varchar(20) not null,书名 varchar(20) not null unique,出版日期 date null,primary key(图书编号));

create table book_copy4(图书编号 varchar(20) not null,书名 varchar(20) not null,出版日期 date null,primary key(图书编号),unique(书名));

例 3-18：假设book表中主键为设定，为book表建立主键约束为“图书编号”，“书名”为替代键约束。()

alter table bookadd primary key(图书编号),add unique u_idx(书名);

错误点：ERROR 1068 (42000): Multiple primary key defined
原因：有两个主键，删除其中一个即可用。

例 3-19：删除book表中的主键和替代键约束。()

alter table booklistdrop primary key,drop index u_idx;

例 3-20：创建book_ref表，book_ref表中所有“图书编号”都必须出现在book表中，假设已经使用“图书编号”列作为book表主键。()

create table book_ref(图书编号 varchar(20) null,书名 varchar(20) not null,出版日期 date null,primary key (书名),foreign key (图书编号)references booklist (图书编号)on delete restricton update restrict);

—如下select语句不会返回任何行—

select * from book_refwhere 图书编号 not in(select 图书编号 from booklist);

例 3-21：创建带有参照动作“cascade”的book_ref1表。

create table book_ref1(图书编号 varchar(20) null,书名 varchar(20) not null,出版日期 date null,primary key (书名),foreign key (图书编号)references booklist (图书编号)on update cascade);

例 3-22：在网络图书销售系统中，只有会员才能下订单。因此sell表中的所有“用户号”也必须出现在members表的“用户号”列中。这种约束通过定义参照完整性约束来实现。(须先创建表sell）

alter table bookselladd foreign key (用户号)references members (用户号)on delete cascadeon update cascade;

例 3-23：创建表student，只考虑学号和性别两列，性别只包含“男“或”女“两项。()

create table student(学号 char(6) not null,性别 char(2) not null check(性别 in (`男`,`女`)));

出错点：ERROR 3813 (HY000): Column check constraint ‘student_chk_1’ references other column
MySQL错误代码3813：“列检查约束”，无法创建表

例 3-24：创建表student1，只考虑”学号“”出生日期“”学分“列，出生日期必须大于2001年1月1日。()上

create table student1(学号 char(6) not null,出生日期 date not null,学分 int null,check (出生日期>`2001-01-01`));

错误点：ERROR 3820 (HY000): Check constraint ‘student1_chk_1’ refers to non-existing column ‘2001-01-01’

例 3-25：创建表student2,有“学号“”最好成绩“”平均成绩“3列，要求最好成绩必须大于平局成绩。

create table student2(学号 char(6) not null,最好成绩 int(1) not null,平均成绩 int(1) not null,check(最好成绩>平均成绩));

出错点： ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘骞冲潎鎴愮哗`))’ at line 1

例3-26：创建表student3,有”学号“”最好成绩“”平均成绩“3列，要求最好成绩必须大于平均成绩，且最好成绩不得超过100分。

create table student3(学号 char(6) not null,最好成绩 int(1) not null,平均成绩 int(1) not null,check(最好成绩<=100),check(最好成绩>平均成绩));

错误点：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘鏈?ソ鎴愮哗 <= 100))’ at line 1

例3-27：删除book表的主键，删除book_ref表的外键book_ref_ibfk_1,删除student表的check完整性约束student_chk_1.修改student1表的check完整性约束student1_chk_1属性，暂时不强制执行。

alter table book drop primary key;alter table book_ref drop foreign key book_ref_ibfk_1;alter table student drop check student_chk_1;alter table student1 alter check student1_chk_1 not enforced;

—查看表的所有信息—

show create table;

例4-1：向Bookstore数据库中的表book（表中列包括图书编号、图书类别、书名、作者、出版社、出版时间、单价、数量、折扣及封面图片）中插入一行数据"TP.9501,计算机,Dreamwearer,宝丽嘉,高等教育出版社,2022-01-20,33.25,52,0.8.null"。

use bookstore;insert into booklist values ('TP.9501','计算机','宝丽嘉','高等教育出版社','2022-01-20',33.25,52,0.8,'Dreamwearer',2);

错误点：ERROR 1406 (22001): Data too long for column ‘作者’ at row 1
解决方案（暂定）：alter table booklist modify column 作者 char(20);
解决：与表一一对应

例4-2：若表book中”图书类别“列的默认值为“计算机”，“封面图片”列的默认值为null，插入例4-1那行数据。

insert into booklist (图书编号,作者,出版社,出版时间,单价,数量,折扣)values ('TP.9502','宝丽嘉','高等教育出版社','2022-01-20',33.25,52,0.8);

insert into booklistset 图书编号='TP.9503',作者='宝丽嘉',图书类别='DEFAULT',出版社='高等教育出版社',出版时间='2021-02-06',单价=32,数量=20,折扣=0.6;

replace into booklist values('TP.9504','计算机','林小红','高等教育出版社','2020-06-08',23.5,30,0.6);

错误点：ERROR 1136 (21S01): Column count doesn’t match value count at row 1
出现问题：新插入一行数据时候，返回错误
解决方法：检查表格：select * from 表名； desc 表名；
然后与表一一对应加入或改正即可：

replace into booklist values('TP.9504','计算机','林小红','高等教育出版社','2020-06-08',23.5,30,0.6,62015,3);

例4-3：向book表中插入一行数据“TP.2467，计算机，计算机基础，林华中，高等教育出版社，2022-01-23，42.5，45，0.8，ic.jpg"。

insert into booklistvalues('TP.2467','计算机','林华中','高等教育出版社','2020-01-23',42.5,45,0.8,'D:\pic\ic.jpg',null);

例4-4：向members表中插入两行数据”C0138,李华,女,147369,17885091065,2022-01-20“”C0139,张明,男,258456,17684271352,2020-09-23“。

insert into booklist values('C0138','李华','女','147369','17885091065','2022-01-20'),('C0139','张明','男','258456','17684271352','2020-09-23');

----数据修改----

例4-5：将bookstore数据库中book表的所有书籍数量都增加10.将members表中姓名为”张三”的员工的联系电话改为：“18798372060”，密码改为“147369”。

update booklistset 数量=数量+10;update membersset 联系电话='18798372060',密码='147369'where 姓名='张三';

例4-6：订单号为6的客户因某种情况退回2本图书，请在sell表中修改订购册数，同时书退回后，book表中该图书的数量增加2。

update sell,booklistset sell.订购册数=订购册数-2,booklist.数量=数量+2where sell.图书编号=book.图书编号 and sell.订单号='6';

细分：

update booklist set booklist.数量=数量+2;

-------数据删除-------

例4-7：将bookstore数据库中姓名为“张三“的员工的记录删除。

use bookstore;delete from memberswhere 姓名='张三';

例4-8：将book表中数量小于5的所有行删除。

use bookstore;delete from booklistwhere 数量<5;

例4-9：用户号为“D1973”的客户注销例，请在members表中将该用户记录删除，同时将其在sell表中的记录也删除。

—1—

delete sell,membersfrom sell,memberswhere sell.用户号=members.用户号and members.用户号='D1973';

—2----

deletefrom sell,membersusing sell,memberswhere sell.用户号=members.用户号and members.用户号='D1973';

-–消除表数据语句–-

truncate table 表名;

--------数据查询--------

例5-1：查询bookstore数据库的members表中各会员的姓名、联系电话和注册时间。

use bookstore;select 姓名,联系电话,注册时间from members;

[^1]显示members中的所有列：select * from members;

例5-2：查询book表中图书类别为“计算机”的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publicsher。

select 书名 as name,作者 as auther,出版社 as publisherfrom booklistwhere 图书类别='计算机';

select 作者 as auther,出版社 as publisherfrom booklistwhere 图书类别='计算机';

当自定义的列标题中含有空格时，必须使用引号将标题括起来：

select 作者 as 'name of Auther',出版社 as Publishfrom booklist where 图书类别='计算机';

注意：不允许在where子句中使用列别名（这是因为执行where代码时，可能尚未确定列值）。
例如，下述查询时非法的

select 性别 as sex from members where sex='男';

例5-3：查询book表中的图书编号、书名和数量，对其库存数量按一下规则进行替换：若数量为空值，则替换为“尚未进货”；若数量小于5本，替换为“需进货”；若数量在5~50本的范围内，替换为“库存正常”；若数量大于50本，替换为“库存积压”。列标题更改为“库存”。

select 图书编号,书名casewhen 数量 is null then '尚未进货'when 数量<5 then '需进货'when 数量>=5 and 数量<=50 then '库存正常'else '库存积压'end as 库存from booklilst;

select 图书编号,作者casewhen 数量 is null then '尚未进货'when 数量<5 then '需进货'when 数量>=5 and 数量<=50 then '库存正常'else '库存积压'end as 库存from booklilst;

例5-4：对sell表中已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额；

select 图书编号,round(订购册数*订购单价,2) as 订购金额from sellwhere 是否发货='已发货';

–消除结果集中的重复行–

select distinct 列名1，列名2;

例5-5：对book表中只选择图书类别和出版社两列，消除结果集中的重复行。

当对book表中只选择图书类别和出版社两列时，结果集中有很多重复行：

select distinct 图书类别,出版社 from book;

例5-6：查询bookstore数据库book表中书名为“网页程序设计”的记录：

select * from bookwhere 书名='网页程序设计';

例5-7：查询book表中单价大于30元的图书情况：

select *from bookwhere 单价>30;

例5-8：查询sell表中还未收货的订单号、订购时间和是否收货：

select 订单号,订购时间,是否收货from sellwhere 是否收货<=>null;

例5-9：查询book表中“清华大学出版社”or 出版社=“北京大学出版社”出版的价格大于等于35元的图书：

--1--

select * from bookwhere (出版社='清华大学出版社' or 出版社='高等教育出版社')and 单价>=35;

--2--

select * from bookwhere(出版社='高等教育出版社' and 单价>=30)or (出版社='清华大学出版社' and 单价>=30);

例5-10：查询sell表中已收货且已结清的订单情况：

select *from sellwhere 是否收货='已收货' and 是否结清='已结清';

例5-11：查询members表中姓”李“的用户的用户号、姓名及注册时间：

select 用户名,姓名,注册时间from memberswhere 姓名 like '李__';

例5-12：查询book表中图书编号倒数第二位位6的图书的图书编号和书名：

select 图书编号,书名 from bookwhere 图书编号 like '%6_';

例5-13：查询book表中书名包含下划线的图书：‘

select 图书编号,书名from bookwhere 书名 like '%#_A%'eacape '#';

例5-14：查询book表中2020年出版的图书的情况：

--1--

select * from bookwhere 出版时间 between '2020-1-1' and '2020-12-31';

--2--

select * from book where 出版时间>='2020-1-1' and 出版时间<='2020-12-31';

select *from bookwhere 出版时间 not between '2020-1-1' and '2020-12-31';

select * from book where 出版时间<='2020-1-1' and 出版时间>='2020-12-31';

例5-15：a查询book表中”高等教育出版社“ ”北京大学出版社“”人民邮电出版社“出版的图书的情况：

--1--

select * from bookwhere 出版社 in ('高等教育出版社','北京大学出版社','人民邮电出版社');

--2--

select * from bookwhere 出版社='高等教育出版社' or 出版社='北京大学出版社' or '人民邮电出版社';

例5-16：查询sell表中尚未发货的订单记录：

select * from sellwhere 是否发货 is null;

------多表查询------

例5-17：从members表中检索出所有客户的信息，并使用表别名users。

select *from members as users;

例5-18：查找bookstore数据库中客户订购的图书书名、订购册数和订购时间。

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

例5-19：使用内连接实现例5-18所要求的查询：

select book.书名,sell.订购册数,sell.订购时间from book inner join sellon (book.图书编号=sell.图书编号);

例5-·20：用join关键字表达下列查询：查找购买《mysql数据库》且订购数量大于5本的图书信息。

select 书名,订购册数from book join sellon book.图书编号=sell.图书编号where 书名='mysql数据库' and 订购册数>5;

例5-21：用join关键字表达下列查询：查找购买《mysql数据库》且订购数量大于5本的图书信息及用户姓名和订购册数：

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

例5-22：查找bookstore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数：

select distinct a.订单号,a.图书编号,a.订购册数from sell as a join sell as bon a.图书编号=b.图书编号 and a.订单号=b.订单号；

例5-23：查找members表中所有订购过图书的用户的姓名：

--1--

select distinct 姓名 from membersjoin sell using(用户号);

--2--

select distinct 姓名from members join sellon members.用户号=sell.用户号;

例5-24：查找所有“计算机”类图书的图书编号、单价及订购啦图书的客户的用户号，若用户号从未订购过，也要包括其情况：

select book.图书编号,book.单价,用户号from book left outer join sellon book.图书编号=sell.图书编号 and 图书类别='计算机';

例5-25：查找订购了图书的男性用户的订单号、图书编号、订购册数以及用户的姓名和联系电话：

select 订单号,图书编号,订购册数,members.姓名,members.联系电话from sell right join memberson members.用户号=sell.用户号 and 性别='男';

例5-26：查找bookstore数据库中“张三”的订单信息：

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

例5-27：查找购买了除《mysql数据库》以外图书的用户信息：

select * from members where 用户号 in(select 用户号 from sell where 图书编号 not in(select 图书编号 from book where 书名='mysql数据库'));

例5-28：查找购买了图书编号为“TP.2525"的图书的用户信息：

select * from members where 用户号=any(select 用户号 from sellwhere 图书编号='TP.2525');

–可先–

select 用户号 from sell where 图书编号='TP.2525';

例5-29：查找book表中所有比”网页设计“类图书价格都高的图书基本信息：

select 图书编号,图书类别,单价 from bookwhere 单价>all(select 单价 from book where 图书类别='网页设计');

例5-30：查找sell表中订购册数不少于图书编号为”Ts.3035’的图书的任何一个订单的订购册数的订单信息：

select 图书编号,订购册数 from sell where 订购册数>=some(select 订购册数 from sell where 图书编号='Ts.3035');

--先查询--

select 图书编号,订购册数 from sell where 图书编号='Ts.3035';

例5-31：查找每次订购10本以上图书的用户的姓名：

select 姓名 from members where exists( select * from sellwhere 用户号=members.用户号 and 订购册数>10);

例5-32：将sell表中用户“C0138"的订单的sql语句如下：

select 订单号,用户号,图书编号,订购册数 from sell where 用户号='c0138';

select 订单号,用户号,图书编号,订购册数 from sell where 图书编号='TP.5252';

-*-合并如果要保留所有的记录，则使用All–

select 订单号,用户号,图书编号,订购册数 from sell where 用户号='c0138'union [all]select 订单号,用户号,图书编号,订购册书 from sell where 图书编号='TP.2525';

例5-33：将sell表中订购册数大于30本的订单和book表中数量大于50本的距离合并：

select 图书编号,订购册数,订购单价 from sell where 订购册数>30;

select 图书编号,数量,单价 from book where 数量>50;

--合并--

select 图书编号,订购册数,订购单价 from sell where 订购册数>30unionselect 图书编号,数量,单价 from book where 数量>50;

(select 图书编号,订购册数,订购单价 from sell where 订购册数>30order by 订购单价 limit 2)union all(select 图书编号,数量,单价 from book where 数量>50 order by单价 limit 2);

-—分类汇总与排序—-（）：–

例5-34：求会员总人数：

select count (*) as '会员数' from members;

例5-35：统计已收货的订单数：

select count(是否收获) as '已收货的订单数' from sell;

例5-36：统计订购册数在5本以上的订单数：

select count(订购册数) as '订购册书在5本以上的订单数'from sell where 订购册数>5;

例5-37：求图书编号为“TP.3035”的图书的额最高订购册数和最低订购册数：

select max(订购册数),min(订购册数)from sellwhere 图书编号='Ts.3035';

例5-38：求图书编号为“Ts.3035”的图书的订购总册数：

select sum(订购册数) as '订购总册数'from sell where 图书编号='Ts.3035';

例5-39：求图书编号为“Ts.3035”的图书的每笔订单平均册数：

select avg(订购册数) as '每笔订单平均册数'from sell where 图书编号='Ts.3035';

----------GROUP BY 子句----------

例5-40：输出book表中的图书类别名：

select 图书类别from bookgroup by 图书类别;

例5-41：按图书类别统计book表中各类图书的库存数：

select 图书类别,sum(数量) as '库存数'from bookgroup by 图书类别;

例5-42：按图书编号分类统计订单数和订单的平均订购册数：(）；

select 图书编号,avg(订购册数) as '订购册数',count(订单号) as '订单数'from sellgroup by 图书编号;

例5-43：按图书类别、出版社分类统计book表中各类图书的库存数：

–-1--

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

--2--

select 图书类别,出版社,sum(数量) as '库存数'from bookgroup by 图书类别,出版社with rollup;

例5-44：查找sell表中每个用户平均订购册数在5本以上的用户号和平均订购册数。

select 用户号,avg(订购册数) as '平均订购册数'from sellgroup by 用户号having avg(订购册数) >5;

例5-45：查找sell表中用户的订单数在2笔及以上且每笔订购册数都在5本以上的用户。

select 用户号from sellwhere 订购册数 > 5group by 用户号having count(*) >= 2;

–先查找每笔订购册数都在5本以上的用户：—

select 用户号,订购册数 from sell where 订购册数 >5;

例5-46：将book表中的记录按出版时间的先后排序：

select * from bookgroup by 出版时间;

例5-47：将sell表中的记录按订购册数从多到少排序：

select * from sellgroup by 订购册数 desc;

例5-48：查找members表中注册时间最靠前的5位会员的信息：

select * from membersgroup by 注册时间limit 5;

例5-49：查找book表中从第四条记录开始的5条记录：

select * from bookorder by 图书编号limit 3,5;

例6-1：创建Bookstore数据库上的jsj_sell视图，包括计算机类图书的销售订单号、图书编号、书名、订购册数等情况；要保证对该视图的订单修改都要符合计算机类这个条件。

create or replace view jsj_sellasselect 订单号,sell.图书编号,书名,订购册数from book,sellwhere book.图书编号=sell.图书编号and book.图书类别='计算机'with check option;

例6-2：创建Bookstore数据库中”计算机“类图书销售视图sale_avg，包括书名（在视图中列名为”name")和该图书的平均订购册数（在视图中列名为sala_avg);

create view sale_avg (name,sale_avg)asselect 书名,avg(订购册数)from jsj_sellgroup by 书名;

例6-3：在视图jsj_sell中查找“计算机”类图书的订单号和订购册数。

select 订单号,订购册数from jsj_sell;

例6-4：查找平均订购册数大于5本的订购客户的用户号和平均订购册数。

create view kh_avg (userID,order_avg)asselect 用户号,avg(订购册数)from sellgroup by 用户号;select * from kh_avg where order_avg > 5;

例6-5：创建视图jsj_book，视图中包含“计算机”类图书的信息，并向jsj_book视图中插入一条记录"TP.0837,计算机,Office应用实例,李天威,人民邮电出版社,2026-5-20,36,null,null,null";

create or replace view jsj_bookasselect *from bookwhere 图书类别='计算机'with check option;

insert into jsj_bookvalues('TP.0837','计算机','李天威','人民邮电出版社','2026-5-20',36,520,0.8,'jpg',12,'Office应用实例');

例6-6：将jsj_book视图中的所有单价降低5%。

update jsj_bookset 单价=单价*(1-0.05);

例6-7：将jsj_book视图中图书编号为”TP.2525"的书名改为“PHP网站制作”，将订单号位5的订购册数改为100册。

改书名

update jsj_sellset 书名='PHP网站制作'where 图书编号='TP.2525';

改订购册数

update jsj_sellset 订购册数=100where 图书编号=5;

合：

update jsj_sellset 书名='PHP网站制作',订购册数=100where 订单号=5 and 图书编号='TP.2525';

例6-8：删除jsj_book中“人民邮电出版社”的记录。(删除数据）：

delete from jsj_bookwhere 出版社='人民邮电出版社';

例6-9：将 jsj_book 视图修改为只包含“计算机”类图书的图书编号、书名和单价。

（修改视图定义）：

alter view jsj_bookasselect 图书编号,书名,单价 from bookwhere 图书类别='计算机';

例7-1：根据book表“书名”列上的前6个字符建立一个升序索引name_book.

create index name_bookon book(书名(6) asc);

例7-2：在sell表的“用户号”列和”图书编号“列上建立一个复合索引user_bh_sell。

create index user_bh_sellon sell(用户号,图书编号);

例7-3：在book表的”书名“列上创建一个普通索引。

alter table bookadd index(书名);

例7-4：假设book表中主键未设定，为book表创建以”图书编号“为主键的索引，”出版社“”出版时间“为复合索引，以加快表的检索速度。

 alter table bookadd primary key (图书编号),add index (出版社,出版时间);

查看表中创建的索引的情况：

show index from book;

例7-5：创建sell_copy（sell) 表，设置”用户号“”图书编号“为联合主键，并在”订购册数"列上创建索引。

create table sell(用户号 char(18) not null,图书编号 char(20) not null,订购册数 int(5),订购时间 datetime,primary key(用户号,图书编号),index (订购册数));

-*-别–

例7-6：删除book表上索引名为“书名”的索引。

drop index 书名 on book;

例7-7：删除book表上的主键和name_book索引。

alter table bookdrop primary key,drop index name_book;

例7-8：在book表的“出版社”列上创建隐形索引。

alter table bookadd index (出版社) invisible;

例7-9：将sell表中的数据按”订购时间“进行分区，2010年前的数据放在p1分区，2011年——2015年的数据放在p2分区，2015年以后的数据放在p3分区。

alter table sellpartition by range(year(订购时间))(partition p1 values less than (2010),partition p2 values less than (2015),partition p3 values less than maxvalue);

查看分区情况：

selectpartition_name part,partition_expression expr,partition_description descr,table_rowsfrom information_schema.partitionswhere table_schema=schema() and table_name='sell';

例7-10：假设sell表中的”是否结清“列为整数类型，1表示结清，0表示未结清。将sell表中的数据按”是否结清“进行分区，已结清的放在p1分区，未结清的放在p2分区。

alter table sellpartition by list (是否结清)(partition p1 values in (1),partition p2 values in (0));

例7-11：假设sell表中”订单号“为主键，字段为整数类型。将sell表中的数据按订单号进行hash分区，共分为3个分区。

alter table sellpartition by hash(订单号) partitions 3;

例7-12：假设sell表中”订单号“为主键，字段为整数类型。将sell表中的数据按订单号进行key分区，共分为3个分区。

alter talbe sellpartition by key() partitions 3;

例7-13：在例7-10中，对sell表的数据按”是否结清”分成啦2个分区，“是否结清”字段中1表示已结清，放在p1分区；0表示未结清，放在p2分区。现在，因为业务需要，对于免单的订单，中”是否结清“字段中用2表示，需要增加一个分区p3，用于存放免单的订单。

alter table selladd partition (partition p3 values in (2));

例7-14：sell表已经按”是否结清“列分为3个分区，请将p2和p3分区合并为一个分区m.

alter table sellreorganize partition p2,p3 into (partition m values in (0,2));

例7-15：将sell 表中的分区删除。

alter talbe selldrop partition p2;

例7-16：将sell表中的分区移除。

alter table sellremove partitioning;

–编程基础知识–

例8-1：查询book表中图书编号为”Ts.3035"（TP.0837)的书名，并存储在变量b_name中。

set @b_name=(select 书名 from book where 图书编号='TP.0837');

–引用用户变量的值–

select * from book where 书名=@b_name;

例8-2：显示members表中的会员姓名，要求在一列中显示姓氏，在另一列中显示名字。（无）

select substring(姓名,1,1) as 姓,substring(姓名,2,length(姓名)-1) as 名from members order by 姓名;

–length函数的作用：返回一个字符串的长度------

例8-3：求members表中会员注册的年数：

select 姓名,year(now())-year(注册时间) as 注册年数from members;

例8-4：返回members表中名字为两个字的会员姓名和性别。性别为女则显示为“0”，为男则显示为1.

select 姓名,if(性别='男',1,0) as 性别from memberswhere 姓名 like '__';

例8-5：判断输入的两个参数n1和n2哪一个更大，结果放在变量result中。( 不能单独执行)

if n2>n2 thenset result='大于';elseif n1=n2 thenset result='等于';elseset result='小于';end if;

例8-6：判定变量str,当其值为U时返回“上升”，其值为D时返回“下降”，为其他值时返回“不变”。（）

case strwhen 'U' then set direct='上升';when 'D' then set direct='下降';else set direct='不变';end case;

例8-7：采用case语句的第二种格式来实现。()

casewhen str='U' then set direct='上升';when str='D' then set direct='下降';else set direct='不变';end case;

----存储过程----

例8-8：使用while语句创建一个执行5次的循环。

declare a int default 5;where a > 0 DOset a=a-1;end while;

每日一言：

没有不可治愈的伤痛，只有不可结束的沉沦。所有失去的，会以另一种方式归来。

上一章链接：petsotre数据库

持续更新中…

本人不才，若有错误，还请在评论区或者私信留言指点一下。

[^1]注:本文章仅用于参考学习，如有错误，欢迎指正。

来源地址：https://blog.csdn.net/m0_66318554/article/details/124949034

文章详情

【MySQL~（详细）案例】

SQL目录

例3-1 创建一个名为“Bookstore”的数据库：

例3-2 创建一个名为“Bookstore” 的数据库，采用字符集 gb2312 和校对规则gb2312_chinese_ci

例3-3 修改数据库 “Bookstore”的默认字符集为 “utf8mb4”、校对规则为“utf8mb4_09900_ai_ci”:

例3-4： 假设已经创建了数据库Bookstore，则在该数据库中创建图书目录表“book”。

例3-5 ：假设已经在数据库Bookstore中创建了表book，表中存在”书名“列。在表book中增加”浏览次数“列，并将表中的”书名“列删除。

例 3-6：假设数据库Bookstore中已经存在表book，将表book重命名为mybook。

例 3-7：假设数据库Bookstore中已经存在表mybook和members,将表mybook重命名为mylist，表Members 重命名为memberlist。

例 3-8：假设数据库Bookstore中有一个表book，创建表book的一个名为book_copy1的副本。

例 3-9：创建表book的一个名为book_coopy2的副本，并且复制其内容。

例 3-10:删除表test：

例 3-11：显示Bookstore数据库建立的数据表文件。

例 3-12：用DESCRIBE语句查看book表中列的信息。

例 3-13：查看book表”图书编号“列的信息。

例3-14：创建表book_copy,将”书名“定义为主键。

例3-15：创建course表来记录每门课程的学生的”学号“”姓名“”课程号“”学分““毕业日期“。其中”学号“”课程号“”毕业日期“构成复合主键。

例 3-16：参照例3-15中的course表创建course1表，将索引命名为“INEDX_course”.

例 3-17：在表book_copy1中，将“图书编号”作为主键，将“书名”列定义为一个替代键。

例 3-18：假设book表中主键为设定，为book表建立主键约束为“图书编号”，“书名”为替代键约束。()

例 3-19：删除book表中的主键和替代键约束。()

例 3-20：创建book_ref表，book_ref表中所有“图书编号”都必须出现在book表中，假设已经使用“图书编号”列作为book表主键。()

例 3-21：创建带有参照动作“cascade”的book_ref1表。

例 3-22：在网络图书销售系统中，只有会员才能下订单。因此sell表中的所有“用户号”也必须出现在members表的“用户号”列中。这种约束通过定义参照完整性约束来实现。(须先创建表sell）

例 3-23：创建表student，只考虑学号和性别两列，性别只包含“男“或”女“两项。()

例 3-24：创建表student1，只考虑”学号“”出生日期“”学分“列，出生日期必须大于2001年1月1日。()上

例 3-25：创建表student2,有“学号“”最好成绩“”平均成绩“3列，要求最好成绩必须大于平局成绩。

例3-26：创建表student3,有”学号“”最好成绩“”平均成绩“3列，要求最好成绩必须大于平均成绩，且最好成绩不得超过100分。

例3-27：删除book表的主键，删除book_ref表的外键book_ref_ibfk_1,删除student表的check完整性约束student_chk_1.修改student1表的check完整性约束student1_chk_1属性，暂时不强制执行。

例4-1：向Bookstore数据库中的表book（表中列包括图书编号、图书类别、书名、作者、出版社、出版时间、单价、数量、折扣及封面图片）中插入一行数据"TP.9501,计算机,Dreamwearer,宝丽嘉,高等教育出版社,2022-01-20,33.25,52,0.8.null"。

例4-2：若表book中”图书类别“列的默认值为“计算机”，“封面图片”列的默认值为null，插入例4-1那行数据。

例4-3：向book表中插入一行数据“TP.2467，计算机，计算机基础，林华中，高等教育出版社，2022-01-23，42.5，45，0.8，ic.jpg"。

例4-4：向members表中插入两行数据”C0138,李华,女,147369,17885091065,2022-01-20“”C0139,张明,男,258456,17684271352,2020-09-23“。

例4-5：将bookstore数据库中book表的所有书籍数量都增加10.将members表中姓名为”张三”的员工的联系电话改为：“18798372060”，密码改为“147369”。

例4-6：订单号为6的客户因某种情况退回2本图书，请在sell表中修改订购册数，同时书退回后，book表中该图书的数量增加2。

例4-7：将bookstore数据库中姓名为“张三“的员工的记录删除。

例4-8：将book表中数量小于5的所有行删除。

例4-9：用户号为“D1973”的客户注销例，请在members表中将该用户记录删除，同时将其在sell表中的记录也删除。

例5-1：查询bookstore数据库的members表中各会员的姓名、联系电话和注册时间。

例5-2：查询book表中图书类别为“计算机”的图书书名、作者和出版社，结果中各列的标题分别指定为name、auther和publicsher。

例5-4：对sell表中已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额；

例5-5：对book表中只选择图书类别和出版社两列，消除结果集中的重复行。

例5-6：查询bookstore数据库book表中书名为“网页程序设计”的记录：

例5-7：查询book表中单价大于30元的图书情况：

例5-8：查询sell表中还未收货的订单号、订购时间和是否收货：

例5-9：查询book表中“清华大学出版社”or 出版社=“北京大学出版社”出版的价格大于等于35元的图书：

例5-10：查询sell表中已收货且已结清的订单情况：

例5-11：查询members表中姓”李“的用户的用户号、姓名及注册时间：

例5-12：查询book表中图书编号倒数第二位位6的图书的图书编号和书名：

例5-13：查询book表中书名包含下划线的图书：‘

例5-14：查询book表中2020年出版的图书的情况：

例5-15：a查询book表中”高等教育出版社“ ”北京大学出版社“”人民邮电出版社“出版的图书的情况：

例5-16：查询sell表中尚未发货的订单记录：

例5-17：从members表中检索出所有客户的信息，并使用表别名users。

例5-18：查找bookstore数据库中客户订购的图书书名、订购册数和订购时间。

例5-19：使用内连接实现例5-18所要求的查询：

例5-·20：用join关键字表达下列查询：查找购买《mysql数据库》且订购数量大于5本的图书信息。

例5-21：用join关键字表达下列查询：查找购买《mysql数据库》且订购数量大于5本的图书信息及用户姓名和订购册数：

例5-22：查找bookstore数据库中订单不同、图书编号相同的图书的订单号、图书编号和订购册数：

例5-23：查找members表中所有订购过图书的用户的姓名：

例5-24：查找所有“计算机”类图书的图书编号、单价及订购啦图书的客户的用户号，若用户号从未订购过，也要包括其情况：

例5-25：查找订购了图书的男性用户的订单号、图书编号、订购册数以及用户的姓名和联系电话：

例5-26：查找bookstore数据库中“张三”的订单信息：

例5-27：查找购买了除《mysql数据库》以外图书的用户信息：

例5-28：查找购买了图书编号为“TP.2525"的图书的用户信息：

例5-29：查找book表中所有比”网页设计“类图书价格都高的图书基本信息：

例5-30：查找sell表中订购册数不少于图书编号为”Ts.3035’的图书的任何一个订单的订购册数的订单信息：

例5-31：查找每次订购10本以上图书的用户的姓名：

例5-32：将sell表中用户“C0138"的订单的sql语句如下：

例5-33：将sell表中订购册数大于30本的订单和book表中数量大于50本的距离合并：

例5-34：求会员总人数：

例5-35：统计已收货的订单数：

例5-36：统计订购册数在5本以上的订单数：

例5-37：求图书编号为“TP.3035”的图书的额最高订购册数和最低订购册数：

例5-38：求图书编号为“Ts.3035”的图书的订购总册数：

例5-39：求图书编号为“Ts.3035”的图书的每笔订单平均册数：

例5-40：输出book表中的图书类别名：

例5-41：按图书类别统计book表中各类图书的库存数：

例5-42：按图书编号分类统计订单数和订单的平均订购册数：(）；

`例3-1` 创建一个名为“Bookstore”的数据库：

`例3-2` 创建一个名为“Bookstore” 的数据库，采用字符集 gb2312 和校对规则gb2312_chinese_ci

`例3-3` 修改数据库 “Bookstore”的默认字符集为 “utf8mb4”、校对规则为“utf8mb4_09900_ai_ci”:

例3-4：假设已经创建了数据库Bookstore，则在该数据库中创建图书目录表“book”。