MySQL数据库学习心得

这学期开始我们一直学习bookstore的数据库创建和使用，所以接下来下面内容几乎全部围绕bookstore的sql语句来展开。

最开始我们学习创建了三个表：Book、sell和members表，分别对应图书表、销售表和读者表。前期我们为这个表的创建花了很多时间，因为对内容和知识点的不熟悉。

我们使用的是使用图形界面操作，后面基本都是sql语句操作：

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `bookstore`.`book`;

DROP TABLE IF EXISTS `bookstore`.`members`;

DROP TABLE IF EXISTS `bookstore`.`sell`;

CREATE TABLE `book` (

  `图书编号` char(10) NOT NULL,

  `图书类别` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `书名` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

  `作者` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `出版社` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `出版时间` date DEFAULT NULL,

  `单价` float(5,2) DEFAULT NULL,

  `数量` int DEFAULT NULL,

  `折扣` float(5,2) DEFAULT NULL,

  `封面图片` blob,

  PRIMARY KEY (`图书编号`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `members` (

  `用户号` char(5) NOT NULL,

  `姓名` varchar(50) NOT NULL,

  `性别` char(1) NOT NULL,

  `密码` varchar(20) NOT NULL,

  `联系电话` varchar(11) DEFAULT NULL,

  `注册时间` date DEFAULT NULL,

  PRIMARY KEY (`用户号`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sell` (

  `订单号` int NOT NULL,

  `用户号` char(5) NOT NULL,

  `图书编号` char(10) NOT NULL,

  `订购册数` int DEFAULT NULL,

  `订购时间` datetime DEFAULT NULL,

  `是否发货` varchar(20) DEFAULT NULL,

  `是否收货` varchar(20) DEFAULT NULL,

  `是否结清` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`订单号`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

BEGIN;

LOCK TABLES `bookstore`.`book` WRITE;

DELETE FROM `bookstore`.`book`;

INSERT INTO `bookstore`.`book` (`图书编号`,`图书类别`,`书名`,`作者`,`出版社`,`出版时间`,`单价`,`数量`,`折扣`,`封面图片`) VALUES ('TP050', '计算机', '好家伙', '我服了', '人民出版社', '2024-03-13', 52.00, 3, 0.52, NULL)

;

UNLOCK TABLES;

COMMIT;

BEGIN;

LOCK TABLES `bookstore`.`members` WRITE;

DELETE FROM `bookstore`.`members`;

INSERT INTO `bookstore`.`members` (`用户号`,`姓名`,`性别`,`密码`,`联系电话`,`注册时间`) VALUES ('A2535', '栗子果', '男', '123456', '12345213628', '2019-11-13')

;

UNLOCK TABLES;

COMMIT;

BEGIN;

LOCK TABLES `bookstore`.`sell` WRITE;

DELETE FROM `bookstore`.`sell`;

INSERT INTO `bookstore`.`sell` (`订单号`,`用户号`,`图书编号`,`订购册数`,`订购时间`,`是否发货`,`是否收货`,`是否结清`) VALUES (1, '2503', 'TP.22610', 50, '2024-03-13 10:45:43', '已发货', '否', '是'),(2, '2256', 'TP.26060', 3, '2019-11-12 10:56:41', '已发货', '是', '是')

;

UNLOCK TABLES;

COMMIT;

创建数据库的sql语句：create database [if not exists] 数据库名称 [库选项];

if not exists：如果数据库不存在，则创建数据库；如果已存在，就会报错。

数据库名称：由字母、下划线和数字等组成的任意字符串。

库选项：用于设置数据库的相关特性，例如字符集：charset utf8。

查看mysql服务器的所有数据库

Show databases;

查看指定的数据库：

show create database 数据库名称;

例如：

show create database bookstore;

指定当前数据库

use 数据库名称;

例如：use bookstore;

删除数据库

Drop database [if exists]数据库名称;

例如：

drop database bookstore;

登录mysql服务器指定当前数据库

Mysql -u用户名 -p[密码] 数据库名称

例如：

mysql -uroot -p123456 bookstore;

1.显示服务器中所有的数据库

show databases;

2. 用SQL语句完成)创建一个名为school的数据库

create database school;

3. (用SQL语句完成)打开数据库school；修改字符集为：utf8mb4, 校对规则为：utf8mb4_0900_as_cs;

alter database school character set utf8mb4 collate utf8mb4_0900_as_cs;

4. (用SQL语句完成)删除数据库school

drop database school;

5. (简答题)

用sql语句完成:

       创建一个如下二维表所示的名为students的表（不添加记录）：

学号                    姓名         班级                  联系电话            出生日期

20230303001   李明        2023计应1班      13822812345     2005年3月12日

20230303001   王晓军      2023计应1班     13562812345     2005年8月3日

20230303001   吴柳红       2023计应1班     13977281235     2005年7月25日

create table students(学号 char(11) not null primary key , 姓名 varchar(20) not null , 班级 varchar(50) not null, 联系电话 char(20),出生日期 date);

6. (简答题)

用SQL语句完成：

  修改学号为20230303003的学生的联系电话为：13677269878

update  students  set 联系电话='13677269878' where 学号=＇20230303003＇;

7.(简答题)

用SQL语句完成：

  删除学号为20230303003的学生记录

delete from students where 学号='20230303003';

8. 显示members表中男性会员的人数

select count(*) as 人数 from members where 性别='男';

9. 显示并统计sell表中各会员的用户号和订购总额（订购总额=订购册数*订购单价）

select 用户号, sum(订购册数*订购单价) as 订购总额 from sell group by 用户号 ;

10. 用SQL语句完成)显示book表中图书的最高价格

select max(单价) as 最高价格 from book ;

11. 用SQL语句完成)对于bookstore数据库（以下各题同），请显示book表中各图书的书名和库存

select 书名,sum(数量) as 库存 from book group by 图书编号;

12. 用SQL语句完成)将book表中按价格从高到低排序

select * from book order by 单价 desc;

13. 用SQL语句完成)将members表中记录按注册时间降序排序

select * from members order by 注册时间 desc;

14. 用SQL语句完成)统计sell表中所有订单的总额（总额=订购册数*订购单价）

select  sum(订购册数*订购单价) as 总额 from sell  ;

15. 用SQL语句完成)显示book表中价格最高的前三个记录

select * from book order by 单价 desc limit 3;

16.对于bookstore数据库（以下各题同），使用“等值连接”显示客户订购的姓名，订购册数，订购时间

select members.姓名,sell.订购册数,订购时间 from sell,members where members.用户号=sell.用户号;

17.使用“join连接”显示客户订购的姓名，订购册数，订购时间

select members.姓名,sell.订购册数,订购时间 from sell join members on members.用户号=sell.用户号;

数据类型：

字节数：float：4；double：8；decimal（m，d）或dec（m，d）：M+2

在mysql中使用浮点数类型和定点数类型表示小数，浮点数包括单精度浮点数（float）和双精度浮点数（double）,定点数类型是decimal

M为全长（不包括小数点和符号）表示一共m位数，d为小数点后长度，m和d又称精度和标度

Decimal的取值范围与double相同，但是decimal的有效取值范围由m和d决定，并且decimal的字节数是m+2，即定点数的储存空间是根据其精度决定的。

浮点数和定点数的比较：

Float和double在db中保存的是近似值，但decimal是以字符串的形式保存数值，入队数据求sum()的话会出现不同结果，float和double会有很多小数点，而decimal得到的是精准数值

Decimal常用于在数据库存储精确的数值，如计算公司金额的数据等

数据库选择decimal类型，长度可以自定义，如decimal(5,2)，保留2位小数，还要注意默认值，一定要写成0.00，不要用默认的null，不然在后续进行加减排序这些操作，会有麻烦

日期和时间型：

字节数:year:1;date:4:time:3;datetime:8;timestamp:4

Year格式：4位字符串或数字，分隔符“-”，还可以用“.”“,”“/”等符号代替

Time格式：字符串‘HH:MM:SS’、‘HHMMSS’或数字HHMMSS，特殊格式：‘D HH:MM:SS’,D表示日，可以取0-34之间的值，插入数据时，小时的值等于Dx24+HH

Datetime格式（1）：‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’或YYYYMMDDHHMMSS

Datetime格式（2）：‘YY-MM-DD HH:MM:SS’或‘YYMMDDHHMMSS’或YYMMDDHHMMSS，YY取值范围位‘00-99’（‘00’-‘69’范围的值会转换为2000-2069范围的值，‘70’-‘99’的值会被转换为1970-1999范围的值）

Timestamp（时间戳）格式：与datetime相同，注意：当无任何输入，或输入null时，实际

保存的时系统当前日期和时间

字符串类型：

由字符串、汉字、数字符号、特殊符号构成

Char：

1. 列的长度固定为创建表时声明长度
2. 长度为0-255的任何值
3. 保存char值时，它们右边填充空格以达到指定长度

Varchar：

1. 长度可为0-65535之间的值
2. Varchar的最大有效长度由最大行大小和使用的字符集确定，整体最大长度为65532字节
3. 同char相比，varchar值保存时只保存需要字符数

Blob：

二级制字符串（字节字符串）

列没有字符集，并且排序和比较基于列数和比较基于列值字节的数值

用于存储声音、视频、图像等数据，在大多数情况下，可以将blob列是为足够大的varlinary列，而varbinary常用于存放小图标

Text：

非二进制字符串

只能存储文本文件，在大多数情况下，可以将text列视为varchar列，、

数据完整性约束

主键约束：

Primary key约束的列不能取空值

MySQL为主键创建唯一性索引，实现数据的唯一性

联合主键：两个及以上列为主键，则数据不可完全相同

例：WWW+YYY不等于999+YYY √

         WWW+YYY=WWW+YYY    ×

参照完整性约束：（外键约束）

1. 创建表同时创建外键：create table 表名 (列名…)
2. 对已有表创建外键：alter table 表名 add
3. 语法格式(外键定义

Foreign key(列名)

   References 表名(父) [列名[(长度)[asc/desc],…]]

修改:on delete {restrict/cascade/set null/no action}

删除:on update {restrict/cascade/set null/no action}

Restrict表示拒绝操作，cascade表示自动删除或更新，set null表示更改后，自动改外键为null

逻辑运算符

模式匹配

Like运算

用于指出一个字符串是否与指定的字符串相匹配，其匹配对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值为true或false

使用like进行模式匹配时，常使用特殊符号_和%，可实现模糊查询

%表示0个或多个字符

_表示单个字符

注意：由于mysql默认不区分大小写，要区分大小写时需要更换字符集的校对规则

例：查询book表图书编号倒数第二为T的图书编号和书名

Select 图书编号,书名 from book

         Where 图书编号 like “%T_”;

范围比较

关键字有两个 between和in  介于（查询创建工具）

Between关键字

格式：表达式[not] between 表达式1 and 表达式2

说明：当不使用not时，若表达式的值在表达式1与表达式2之间（包括这两个值），则返回true，否则返回false；使用not时，返回值刚好相反

注意：表达式1的值不等于大于表达式2的值

In关键字

使用in可以指定一个值表，值表中列出所有可能的值，当与值表中的任一匹配时，即返回true，否则返回false

格式：表达式 in (表达式1[,…n]);

聚合函数

Count函数：

例：求会员总人数

select count(*) 会员数 from members;

Max/min函数：

格式：max/min ([all/distinct]表达式)

Sum函数和avg函数：

也就是求和和平均函数

单表查询

格式：

Select [all/distinct] 输出列表达式,...

[from 表名1[表名2]...]   #from子句

[where条件] #where子句

[group by {列名/表达式/列编号}] #group by子句

[asc/desc],...

[having 条件] #from子句

[order by{列名/表达式/编号}]   #order by子句

[asc/desc],...

[limit {[偏移量,..]行数/行数offset偏移量}]  #limit子句

等值连接

使用where子句设定条件（关联条件）来将结果变少

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

Select book.书名,sell.订购册数,sell.订购时间

From book,sell

Where book.图书编号=sell.图书编号;

join连接

格式：表名1 [inner] join 表名2

on 条件/using(列名)

inner关键字的连接是内连接，内连接是系统默认的，可省略inner关键字

例:查找bookstore数据库中客户订购的图书书名，订购册数和订购时间

select book.书名,sell.订购册数,sell.订购时间

from book.sell

where book.图书编号=sell.图书编号;  

子查询

作为查询条件一部分的查询

在查询条件中，可以使用另一个查询的结果作为条件的一部分

1. in子查询

用于进行一个给定值是否在子查询结果集中的判断格式为：

Expression [not] in (subquery)

当表达式expression与子查询subquery的结果表中的某个值相等时，in谓语返回true，否则返回false，若使用了not，则返回值正好相反

例：寻找在sell表中张三的订单信息

Select sell.*,members.姓名 from sell,members

   Where sell.用户号=members.用户号

           And members.姓名=’张三’;

in子查询

2.比较子查询

可以认为时in子查询的扩展，使表达式的值与子查询的结果进行查询

表达式：{</<=/=/>=/>/!=/<>}{all/some/any}(子查询)

例：查找book表中所有比“数据库”类图书价格都高的图书基本信息

Select * from book

   Where 单价>all

           (select 单价 from book where 图书类别=’数据库’);

内置函数

格式：

delimiter 新语句结束符号       #语句结束符更改与复原

Create function 函数名([参数名 数据类型[,参数名 数据类型,...]])

Returns 返回值类型          #函数头定义

[begin]             #函数体开始

[SQL语句]

Return 返回值;

[end] #函数体结束

新语句结束符号

Delimiter;

新语句结束符号是临时修改语句结束符号（推荐使用$$,//,##,...非系统内置的符号），功能是一旦遇到语句结束符就会自动开始执行，由于函数是一个整体，函数体的SQL语句只有在被调用时才会被执行。

Delimiter修改语句结束符后，函数体中就可正常使用分号结束符，系统由于不再将分号作为语句结束符，从而不会自动执行函数体中的SQL语句

函数名的命名：必须符合MySQL的语法规定，使用字母、数字和下划线，小括号()不管有无参数都需要保留

例：创建一个存储函数，返回book表中的某本书的作者姓名

Delimiter $$

Create function author_book(name char(20))

Returns char(8)

Deterministic

Begin

Return(select 作者 from book where 书名=name);

End

$$

1. R图的绘制

实体关系图：简称E-R图，是指以实体、关系、属性三个基本概念概括数据的基本结构，从而描述静态数据结构的概念模式。

（1）实体：具有相同特征和性质的集合体，用矩形表示，例如读者张三，学生李四等。

（2）属性：实体具有的某一特征，用椭圆形或圆角矩形表示，例如姓名、编号、班级等都是属性。

（3）联系：实体之间相互连接的方法，用菱形表示，并用无向边分别与对应实体相连，联系的类型：1：1，1：n，n：m。

1：1联系：

表示一个实体与另一个实体联系。

1：n联系：

表示一个实体与一个以上实体联系。

n：m

上图是图书表和作者表的E-R图，联系为类别

感想：学习了一学期的sql数据库知识，感觉到数据的庞大，学会了创建及使用数据库，运用了列表的各种功能，学会了函数的使用，E-R图的绘制及转换等，

学习SQL（结构化查询语言）的过程既充满了挑战也充满了收获。SQL作为数据库操作的核心语言，无论是对于数据分析师、数据科学家，还是对于软件开发者，都是一项重要的技能。以下是我学习SQL过程中的一些心得和体会：

1. 学会理论和实践并存

在学习MySQL数据库这门课时，要将课堂上学习到的知识和实际操作结合起来，老师边操作，我们也要边跟着老师操作，这样才能更好地融会贯通。

1. 学会预习和复习

一般老师都是跟着课本顺序走的，所以我们可以在上课之前，先去提前学习一些知识点，大致了解老师要讲的内容，老师讲课时可以更快地吸收；课后也要及时复习，最好是当天内容当天复习，这样能更快地吸收到知识点。

1. 学会利用学习通

学习通的课程里边有很多章节任务点，有很多可以找到的PPT什么的，可以利用起来，老师有时也会发送一些资料，也可以定期查看。

1. 对我来说最难的应该是函数和连接

函数有内置函数和自定义函数，连接有内连接和外连接这些，我在课下也询问了老师的想法，老师也还是说要多多练习，多多学习，还有根据实际情况来判断，仔细读题这些经验。

比如左外连接，就是根据左侧不匹配的行，从右表被选择的列设置为null，匹配的则取之。

参考：

from A left outer join B on T1=T3;

（以表A为参考，加入表B）

5.总结：不论怎样学，只要能学进去，能把自己的知识与实际情况与题结合，能熟练使用就说明你已经掌握了这个知识点，只要用心细心地学习，终将会有收获。