MySQL必知必会——读书笔记

说来惭愧,上大学时学习的一点数据库知识都丢回给老师了。不过既然现在正值过年有时间,不如把它们都捡回来,说不定以后哪天就用上了,以下是本人阅读《MySQL必知必会》的读书笔记:

一. 基础概念

1.数据库:保存有组织的数据的容器(通常是一个文件或一组文件)。
2.DBMS:数据库管理系统,即数据库软件。数据库是通过DBMS创建和操纵的容器。
3.:某种特定类型数据的结构化清单。
4.模式(schema):关于数据库和表的布局及特性的信息。
5.:表中的一个字段。所有表都是由一个或多个列组成。
6.数据类型:限制列中所存储数据的类型。
7.:表中的一个记录。
8.主键:一列(或一组列),其值能唯一区分表中每一行。
主键其实用来表示一个特定的行,没有主键,删除和更新都会异常困难,因为没有方法只涉及相关的行。所以应该总是定义主键
作为主键的列必须满足以下两个条件:
(1)任意两行都不具有相同的主键值;
(2)每个行都必须有一个主键值(主键值不允许为null);
定义和使用主键的一些好的实践:
(1)不更新主键列中的值;
(2)不重用主键列的值;
(3)不在主键列中使用可能会更改的值;
9.SQL(Structured Query Language):结构化查询语言。
10.子句:SQL语句由子句构成,有些子句是必须的(比如select语句中的from子句),有些则是非必须的(比如select语句中的limit子句,order by子句等)。
11.操作符:用来联结或改变WHERE子句中的子句的关键字,例如and,or,not,in。也称为逻辑操作符(logical operator)。
12.聚集函数:运行在行组上,计算和返回单个值的函数。
13.子查询:嵌套在其他查询中的查询。
14.外键:外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
15.完全限定列名:在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误。
16.笛卡尔积:由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

二.检索数据

注意多条Sql语句最好以分号分隔。SQL语句不区分大小写。

select语句检索出的结果将会保存在一个称为结果集的表中。

1.检索单个列
select column_name from table_name; 这样得到的将是无特定顺序(底层表中的顺序)的数据。

2.检索多个列
select id, name, amount form balance;

3.检索所有列
select * from table_name;

4.检索不同的行,可以使用distinct关键字
select distinct amount from balance;
如果在多列检索中使用distinct,那么除非指定的多列值均不同,否则所有的行都将会被检索出来。

5.限制结果
select语句返回所有匹配的行,如果为了返回第一行或者是前几行,可以使用limit子句
select id from balance limit 5; limit 5指示mysql返回的行不多于5行。
为了得出下一个五行,可以指定开始行行数
select id from balance limit 5 5 limit 5 5指示mysql返回从行5开始(注意表中的行是从行0开始的,所以limit 1 1将检索出第二行)的5行。
如果检索的范围行数不够,那么有多少行,返回多少行。
MySQL5中为了避免出现歧义,还允许另一种limit的替代语法:select id from balance limit 4 offset 3;含义与limit 3 4相同,取行3开始的4行。

6.完全限定的表名和列名
select table_name.column_name from table_name;
select table_name.column_name from dataTable_name.table_name;

三.排序检索数据:

本节将介绍select语句的order by子句,它可以帮助我们根据需要排序检索出的数据。

1.排序数据
select prod_name from products order by prod_name;
该语句检索出prod_name并指示其结果按照字母顺序进行排列。
值得注意的是,order by的列并不一定非要是检索出来的数据列。比如:
select prod_name from products order by prod_id也是可以的。

2.按多个列排序
select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
表示检索出的三列按照其中两个列prod_price以及prod_name进行排序(首先按照prod_price,然后按照prod_name)。

3.指定排序方向
order by的排序默认是升序,我们也可以要求其按照降序排列,这需要用到desc关键字
select prod_id, prod_price, prod_name from products order by prod_price desc;
desc关键字值会应用到直接位于其前面的列名。所以,对于多列排序,如果我们想先对prod_price降序,再对prod_name升序可以这样写:
select prod_id, prod_price, prod_name from products order by prod_price desc, prod_name;
理所当然的,如果想在每一列都是降序排序,那么需要在每一列的后面都添加desc关键字。
与desc相对应的是asc,但是往往没有什么作用,因为默认就是升序。

结合order by子句与limit子句,我们可以检索出一列中最高或最低的值:
select prod_price from products order by prod_price desc limit 1

注意:order by子句必须在from子句之后,limit子句必须在order by子句之后。不同的子句是有着顺序要求的

四.过滤数据

本节将介绍where子句指定搜索的条件(过滤条件,filter condition)。

1.使用where子句
select prod_name, prod_price from products where prod_price = 2.50

注意:order by子句必须在where子句之后

2.where子句支持的操作符
(1)=
(2)<>:不等于
(3)!=:不等于
(4)<
(5)<=
(6)>
(7)>=
(8)between: 在指定的两个值之间

3.检查单个值
select prod_name, prod_price from products where prod_name = "fuses"(mysql在执行匹配时,默认不区分大小写)

4.范围值检查
select prod_name, prod_price from products where prod_price between 5 and 10

5.空值检查
创建表时,开发人员可以指定该列是否可以为空。当一个列中某一行不包含值时,称其为包含空值null(no value)。null与空字符串,仅包含空格都不同。
select语句中有一个特殊的where子句,可以用来检查索引列中某值为空(null)的行
select prod_name from products where prod_price is null;

五.数据过滤:

本节将介绍where子句组合以及NOT和IN操作符。

1.组合where子句
之前介绍的where子句在过滤数据时采用的都是单一的条件。为了进行更强的过滤控制,MySQL允许给出多个where子句。
注意:以下给出的操作符都是where子句中使用的操作符。

(1)and操作符,可以为where子句添加附加条件:
select prod_name, prod_id, prod_price form products where prod_price <= 100 and vend_id = 1003 and prod_price > 20 and ......

(2)or操作符,匹配任一条件的行都满足:
select prod_name, prod_id, prod_price from products where vend_id = 1001 or vend_id = 1002;

(3)计算次序
where子句中可以包含任意数目的and和or操作符,允许而这结合已进行更为复杂,高级的过滤。这就有必要考虑到计算操作符的次序问题:and优先级高于or。例如:
select prod_name, prod_price form products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;(过滤出vend_id为1002或者是vend_id为1003且prod_price >= 10美元的prod_name和prod_price列)

select prod_name, prod_price form products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;(过滤出vend_id为1002或1003(这个集合)中prod_price >= 10美元的prod_name和prod_price列)
的含义是完全不同的。

所以为了语义清晰,尽量在where子句中组合使用操作符时采用括号。

(4)in操作符
in操作符用于指定条件范围,范围中的每个条件都可以进行匹配。例如:
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;
in操作符实质上是简化or操作符的写法,例如以上sql语句还可以用or进行如下改写:
select prod_name, prod_price from products where vend_id = 1002 or ven_id = 1003 order by prod_name

in操作符的优点
1.清楚直观;
2.执行效率比or更快;
3.最大的优点:in中可以包含其他的select语句,可以动态创建where子句。

(5)not操作符
where子句中not操作符用于否定其后的任何条件。例如:
select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_name

六. 用通配符进行过滤:

本节介绍通配符及like操作符。

1.like操作符
like的作用有点类似于模糊搜索(通配符搜索),前面介绍的几种操作符都是针对已知值进行过滤的。例如搜索产品名中包含”avail”的所有产品,就需要用上like操作符以及通配符。

通配符定义:用来匹配值的一部分的特殊字符,sql支持几种通配符。

通配符匹配是区分大小写的

(1)百分号(%)通配符:
%表示任何字符出现的任意次数。例如,为了找到所有以jet开头的产品,可以使用如下sql语句:

select prod_id, prod_name from products where prod_name like "jet%";

%通配符可以在搜索模式(例如”jet%”)中任意使用:

select prod_id, prod_name from products where prod_name like "%avail%";
select prod_id, prod_name from products where prod_name like "s%e";

总之,%类似于正则中的*.

另外需要注意的是尾空格,如果想要匹配的某个字段有尾空格,例如:”avail “,那么”%avail”将匹配不到。(可以通过函数去掉尾空格)。

还有就是%是匹配不到null的。

(2)下划线(_)通配符:
_ 只匹配单个字符。
select prod_id, prod_name from products where prod_name like "_avail"

2.关于通配符匹配使用时的一些技巧
(1)不要过度使用通配符,效率较低。
(2)尽量不要把通配符置于搜索模式的开始处,这样匹配起来是最慢的,例如:”_avail”, “%avail”。

七.用正则表达式进行搜索

正则表达式很强大,但是MySQL中允许使用的正则表达式仅是一个很小的子集!(此书作者当时使用的是MySQL5,不知道现在最新的版本是否还有此限制)

MySQL中使用正则需要用到regexp关键字

MySQL中的正则表达式匹配不区分大小写

1.使用MySQL正则表达式

(1)基本字符匹配:
select prod_name from products where regexp "avail" order by prod_name;

这与前面使用like的例子完成相同的功能。

select prod_name from products where regexp ".000" order by prod_name;
其中的.表示的匹配任意一个字符。所以1000和2000都将匹配。

(2)进行or匹配(|):
select prod_name from products where regexp "1000|2000" order by prod_name;

(3)匹配几个字符之一([]):
select prod_name from products where regexp "[123]000" order by prod_name;

select prod_name from products where regexp "[^123]000" order by prod_name;

(4)匹配范围:
select prod_name from products where regexp "[0-9]000" order by prod_name;

(5)匹配特殊字符
如果想要匹配字符.,按照下面的写法将不会得到想要的结果,因为.在正则中表示任意的字符。因此需要转义。
select prod_name from products where regexp "." order by prod_name;
而转义必须使用前导符号:\
select prod_name from products where regexp "\\." order by prod_name;

熟悉正则的同学都知道,正则中转义往往使用\即可,但是MySQL中的转义为何要求\。这是因为MySQL自己解释一个,正则表达式库会解释另一个(相当于双重转义)。

(6)匹配字符类:
可以使用一些预定义的字符集,如图:

(7)匹配多个实例:
这里提及的基本就是正则里的量词概念(+, ?, {2, }, {2, 3}, {2},* )

select prod_name from products where regexp "\\([0-9] sticks?\\)" order by prod_name;

如果要匹配四个数字可以这样写:
select prod_name from products where regexp "[[:digit:]]{4}" order by prod_name;

(8)定位符:

八. 创建计算字段

计算字段这个概念个人理解上有点类似于Vue中的计算属性(Vue是一种前端框架)。计算字段中的字段实际上指的就是列(计算,转换,合并,格式化多列中的数据为一列并输出)。

由于存储在数据库表中的数据一般不是应用程序所需要的格式,所以往往需要我们直接从数据库中检索出转换、计算或格式化过的数据,而不是先检索出数据,然后再在客户机应用程序中重新格式化。(并不是说后者不行,而是在DBMS中转换数据更快,所以最好采用前者

此时计算字段就能够发挥作用了。计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

下面假设我们的表中有两列数据:vend_name和vend_country。然后我们需要返回如下拼接格式的数据:vend_name(vend_country)为一列,然后返回。

可以这样做,直接使用现成的Concat函数:

select Concat(vend_name, "(", vend_country, ")") from venders order by vend_name;

最终我们可以得到如下示例结果:

如果想要在拼接前,清除数据前后的空格,可以采用Trim()函数(还有RTrim,LTrim)。

select Concat(Trim(vend_name), "(", Trim(vend_country), ")") from venders order by vend_name;

最后,按照我们的要求输出了拼接好的一列数据,但是这一列并没有名称,需要我们设置一个别名,采用as关键字

select Concat(Trim(vend_name), "(", Trim(vend_country), ")") as vend_title from venders order by vend_name;

就这样,我们为该列设置了列名:”vend_title”。

另一个例子,将拼接的列与原列合并为一张结果集的新表:

select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems where order_num = 20005;

最终可以得到如下表:

九.使用数据处理函数

不同的DBMS都提供了不同的函数来完成一些功能,所以相比与多数的sql语句是可移植的,函数的可移植性较差。

大多数SQL实现了以下类型的函数:
(1)处理文本串的文本函数;
(2)在数值数据上进行算术操作的数值函数;
(3)处理日期的时间函数;
(4)返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数;

1.文本函数

soundex函数具体是用来匹配发音相近的元素。例如:
select cust_name. cust_contact from customers where soundex(cust_name) = soundex("Y.Lie");
最终返回的结果是发音与Y.Lie相近的数据。

2.日期和时间处理函数

select cust_id, order_num from orders where Date(order_date) = "2019-02-08";

select cust_id, order_num from orders where Year(order_date) = 2019 and month(order_date) = 2;

3.数值处理函数

十.汇总数据

1.聚集函数
聚集函数用于汇总检索行的信息,比如最大值,最小值,平均值等。MySQL中提供了5种聚集函数。

(1)avg函数:
select avg(prod_price) as avg_price from products where prod_id = 1002;
avg函数计算时会忽略为null的值。

(2)count函数:
第一种:count(*) 计算表中的行数。(包含null值)
select count(*) as num_cust from customers;

第二种:count(column)对特定列中的具有值的行计数(忽略null)。
select count(prod_name) as num_prod_name from products;

(3)max函数:
select max(prod_price) as max_prod_price from products;

max函数会忽略null值。

(4)min函数:
select min(prod_price) as min_prod_price from products;

(5)sum函数:
select sum(quantity) as items_ordered from orderitems where order_num = 1002;

select sum(quantity * item_price) as items_ordered from orderitems where order_num = 1002;

2.聚集不同的值
每一个聚集函数还依赖两个关键字从而有不同的行为:all(默认)和distinct。

all:聚集所有检索出来的行的值(特定列)。
distinct:先去重再聚集。

select avg(distinct prod_price) as avg_price from products where prod_id = 1002;

当然,distinct用在max和min函数上是没有意义的。

3.聚集函数的组合使用
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

十一. 分组数据

本节将介绍group by子句和having子句。

之前的操作都是在一张表上,根据某些条件筛选出特定的行后,进行聚合。但是实际上,我们还可以针对筛选出的结果表进行分组在聚合为多个值:

1.创建分组
创建分组由group by子句完成。例如:
select vend_id, count(*) as num_prods from products group by vend_id;
这表示先将表按照vend_id进行分组(相同的vend_id行为一组),然后在分组的内部进行聚合,计算表中不同的vend_id共有多少行。

group by子句的使用有以下规则:
(1)GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制;
(2)如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总;
(3)GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名;
(4)除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出;
(5)如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组;
(6)GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前;

2.过滤分组
group by帮助我们创建分组,而having子句则可以帮助我们过滤分组。where子句其实也起到的是过滤的作用,不过where针对的是表中的行进行条件过滤,而无法对分组中的行进行条件过滤。总之,记住where过滤行,having过滤分组。而且having子句支持所有的where子句的操作符

例如:
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

having子句最终过滤掉了count值<2的数据。

having和where还可以这样理解:where子句在数据分组前过滤,having子句在数据分组后过滤。所以where子句和having子句也是可以一起使用的,只不过where子句先过滤数据得到数据集,在此基础上通过group by创建分组,然后having子句再在分组中进行过滤,得到最终的结果集。例如:

select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;

3.分组(group by)和排序(order by)
差别:

分组并无法保证顺序输出,所以使用group by子句时,往往还可以结合order by子句使用。

例如:
select order_num, sum(quantity * item_price) as ordertotal from orderitems group by order_num having sum(quantity * item_price) >= 50;得到如下表(结果集):

如果想要结果按照ordertotal列进行排序,则需要使用order by:
select order_num, sum(quantity * item_price) as ordertotal from orderitems group by order_num having sum(quantity * item_price) >= 50 order by ordertotal;得到如下表(结果集):

4.select子句的顺序

十二.使用子查询

1.使用子查询进行过滤
嵌套的子查询往往实质上是组合不同select语句,而且索引的往往是不同的表。

子查询往往在where子句的in操作符中使用,前面也提到过in操作符相比于or的优点就在于在in操作符中,可以嵌套使用select语句进行子查询。例如:

select cust_id from orders where order_num in (select order_num from orderitems where prod_id = "TNT2");

注意:子查询中的列字段(order_num)必须与in操作符前面的字段一致(理所当然)。

2.作为计算属性的子查询
select cust_name, cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;

相关子查询:涉及外部查询的子查询(子查询涉及到不同表的情况),上例就是。

最后,建议少用子查询(性能问题),而且就算用,嵌套层数也不能过深,尽量采用其他替代方案。

十三.联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。

关系型数据库由关系表组成。关系表的设计一般需要考虑到尽量避免出现数据冗余以保证数据的一致性,最好每张表表示的都是各自独立的实体。

关系表:关系表的设计就是要保证把信息分解为多个表,一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联(即外键)。

低耦合的关系表能够保证可伸缩性(能够适应不断增加的工作量而不失败)。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。

设计好关系表后,如何通过一条select语句就将不同表中关联的信息读取出来,这就要用到联结(join)。

联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。

1.创建联结
联结的创建非常简单,规定要联结的所有表以及它们如何关联即可。例如:

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name;

以上sql语句中,选择的三列里,vend_name在vendors表中,prod_name,prod_price在products表中,这里就需要联结vendors表和products表。而这俩表间的join是通过vendors表中的主键vend_id以及products表中的外键vend_id完成的(这里为了区分需要用到完全限定列名)。

(1)where子句的重要性:
以上例子中,联结实际是在where子句中完成的,而这里where子句的作用就是逻辑上将vendors表中的每一行与products表中的每一行进行匹配(重组为一张新表)。也就是说,在联结两个表时,你实际上做的是将第一个表中的每一行与第二个表中的每一行配对

WHERE子句作为过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。没有WHERE子句,第一个表中的每个行将与第二个表中的每个行配对,而不管它们逻辑上是否可以配在一起(这也称为笛卡尔积,这样联结得到的结果集往往是没用的)。例如上例中我们去掉where子句中的主键和外键联结行匹配关系,将得到以下结果:

select vend_name, prod_name, prod_price from vendors, products order by vend_name, prod_name;

最终就是三列的vendors表中vend_name列的行数乘以products表中prod_name(prod_price)的行数组合得到的结果集。

所以,为了避免这种情况发生,应该保证所有联结都有WHERE子句,否则MySQL将返回比想要的数据多得多的数据。

以上通过主键外键在不同关系表间创建的联结,称为等值联结(equijoin),这种联结也称为内联结(inner join)

(2)内部联结:
我们通过内部联结(sql语句中指定联结方式)改写上面通过where子句创建联结的例子,达到输出相同结果集的目的:

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

on子句后跟的就是内联结的依据(主键,外键匹配依据)。

针对以上两种写法,ANSI SQL规范首选INNER JOIN语法。

(3)联结多个表:
SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系:

但是出于性能的考量,联结的表越少越好。

发表评论

电子邮件地址不会被公开。 必填项已用*标注