《MySQL必知必会》读书笔记

四月份的时候在腾讯课堂邵发老师的课上对mysql有了大致的了解,当时是使用C++操作数据库,加之处于迷茫状态,因此只能说是了解到了一点皮毛,连增删查改都不利索。 最近在恰逢学习ThinkPHP,使用数据库模型,M()函数的时候才明白应当回头夯实数据库的基础知识,于是入手了《MySQL必知必会》并从头学习,(杜哥当初推荐的是《SQL必知必会》,只是草草翻阅,这也应该重新看看。PS:我的学习路线是不是十分凌乱?)

<!--more-->

1. SQL基础概念

数据库

数据库是一个以某种有组织的方式存储的数据的集合,也可以理解为有组织的数据的容器

数据表指的是某种特定类型数据的结构化清单,用来存储某种特定类型的数据(指的并不是具体的数据类型,而是某种类型的事物)。 每个数据表都有一个特定且惟一的名字,用来标识自己;然而,不同的数据库中的表明可以是相同的。

将数据表看作是excel表格类似,竖直方向上的列表示表中的一个字段,简称为字段(回想当初后台同学一口一个字段,然而我一脸懵逼。PS:在第10章提到,数据库列一般称为列,而在"计算字段"的连接上称为字段,然而实际上貌似并没有差别)。 正确将数据分解成多个列是十分重要的,这样才能通过特定的列队数据进行排序和过滤(这也是我希望夯实数据库基础知识的目的之一)。 每个列都有相应的数据类型,这个数据类型限制了该列中所存储的数据。

行指的是数据表中的一条记录,(有时候也叫做一个实体?)

主键

主键是一列或一组列,用来区分表中的每一行,因此,用来设置主键的列,对应的任意两行都不能具有相同的值,且不能为空值。

2. 使用MySQL数据库

虽然这很无聊,我还是把每个命令的代码敲在博客里面吧。

登陆mysql.console并创建一个新的数据库

CREATE DATABASE txm;

使用show显示所有的数据库,可以查看到刚建立的数据库

SHOW DATABASES;

切换到对应数据库:必须先使用use打开数据库,才能操作其中的数据

USE txm;

在数据库中创建一张新表。

create table txm_test(id int(2),varchar(5));

使用show显示所有的数据表,可以查看到刚建立的表

SHOW TABLES;

也可以使用show显示列,查看每一列的详情

SHOW COLUMNS FROM txm_test;

还可以采用与之等效的简写方法

DESC txm_test;

注意:

  • MySQL语句是不区分大小写的(包括关键字和表名)(虽然可能在某些情况下表名会区分大小写),但是一般对所有SQL语句进行大小,对所有列和表明进行小写,方便阅读和调试;
  • 多条SQL语句必须使用分号(;)结尾。

3. 检索数据

使用数据库,最重要的操作便是检索并得到想要的数据结果。

3.1. 检索列

一个最简单的查询,检索指定的单个列

SELECT id FROM txm_test;

选择多个列,每个字段之间使用逗号(,)隔开,注意最后一个字段后不能再跟分割符

SELECT id, name FROM txm_test;

选择全部列,也就是整张表的所有数据,使用通配符(*)

SELECT * FROM txm_test;

注意:SQL语句一般返回原始的,无格式的数据,因此,一般很少使用检索出的原始数据

3.2. 检索行

检索不同的行,如果有多个行对应列的值相同则值返回一个值(无法判断具体是哪一行的值?)

SELECT DISTINCT name;

限制SQL语句返回行数,使用LIMIT

SELECT id FROM txm_test LIMIT 10;

为了得到下一组数据,需要显式地指明偏移量

SELECT id FROM txm_test LIMIT 10,5
# 等价写法
SELECT id FROM txm_test LIMIT 10 OFFSET 5

注意:

  • 只带有一个值得LIMIT总是从第一行开始,给出的数是返回的行数;
  • 带有两个值得LIMIT可以指定从行号为第一个值的位置开始,返回的是起始位置后的下一组数据
  • 由于LIMIT子句是为了限制检索的输出结果,因此一般需要放在所有子句最后面(目前得到的结论是如此)

3.3. 排序

使用order by 子句对检索结果进行排序(可以使用某个字段作为排序条件,但不选择输出该字段)。

SELECT id FROM txm_test ORDER BY score;

也可以同时指定多个字段作为排序条件,当前一个字段相等时使用后一个字段作为备选排序条件(也就是说,如果前面的字段不相等,则完全不会使用后面的字段进行排序)

SELECT id FROM txm_test ORDER BY id,score;

默认使用升序排列(ASC),如果需要降序排列,需要显式地声明排序方向为降序(DESC)

SELECT id FROM txm_test ORDER BY score DESC;

需要注意的是在多个字段做为排序条件时,每个字段对应的排序方向都默认为升序,因此DESC只是修改对应字段的排序方向。如果想要所有的排序条件字段都按照降序排列,则需要对这些字段都奸商DESC

SELECT id FROM txm_test ORDER BY score DESC,id DESC;

注意:

  • order by是对检索输出结果进行排序,因此一般排在LIMIT语句之前,而处于其他的子句之后

3.4. 过滤

数据表中含有大量的数据,可以通过指定搜索条件查询特定的数据,搜索条件是通过where子句定义的

SELECT *  FROM txm_test WHERE id = 1;

where支持由多种操作符组成的搜索条件,包括

  • =
  • <> 不等于,等价于 !=
  • 和>=

  • <和<=
  • BETWEEN ... AND ...

其中,如果字符的判断,需要为对应值加上单引号;是数值之间的比较,则不需要对值加单引号:

SELECT *  FROM txm_test WHERE name = 'tang';

另外使用BETWEEN筛选某个范围内的值(不只是针对数值,也可以筛选日期和字符等),必须同时指定起始值和结束值,并用AND隔开

SELECT *  FROM txm_test WHERE id BETWEEN 1 AND 3;

此外多个搜索条件之间也可以通过AND或者OR组合

SELECT *  FROM txm_test WHERE id = 1 AND name='tang';

需要注意的是AND的优先级高于OR,可以使用括号改变组合顺序

SELECT * FROM txm_test WHERE (id = 1 or id=3) AND name='tang';

对于多个OR组成的搜索条件,可以简化成IN (),每个条目之间使用逗号分隔

SELECT *  FROM txm_test WHERE id IN (123);

最后,可以使用NOT对条件进行取反

SELECT *  FROM txm_test WHERE id NOT IN(1,2,3);

3.5. 模糊查询

如果不知道对应列具体的值,可以使用LIKE,配合通配符 % 或者 _ 进行模糊查询,其中:

  • %代表任意数目的字符,可以是多个,也可以是0个
  • _代表有且只有一个字符
    SELECT * FROM txm_test WHERE name LIKE '%ng';
    注意:
  • 根据MySQL的配置方式,搜索是区分大小写的
  • 此外,尾空格可能会干扰通配符的匹配
  • 最后,使用通配符的搜索效率比较低

3.6. 使用正则表达式进行模糊查询

MySQL仅支持正则表达式一个很小的子集,使用REGEXP指定正则筛选条件

SELECT * FROM txm_test WHERE name REGEXP '.ng'

4. 处理数据

存放在数据库中的数据格式一般不是应用程序所希望的格式,因此可以通过计算字段和函数对检索结果进行处理。虽然在客户端也可以对原始数据进行处理,然而,在数据库服务器上完成这些操作比在客户端要快得多。

4.1. 拼接字段

可以使用拼接字段将多个值联结到一起构成单个值

SELECT Concat(name,'(',id,')') FROM txm_test;

由于新的值在数据表中并没有对应的字段,因此,为了在客户端获取该值,可以使用as为该值创建列别名

SELECT Concat(name,'(',id,')') AS id_new FROM txm_test;

4.2. 文本处理函数

文本处理函数主要有去除多余空格,转换大小写等

# 将结果大写
SELECT name, Upper(name) AS upper_name FROM txm_test;
# 去除右空格
SELECT Concat(RTrim(name),'(',id,')');

4.3. 日期处理函数

日期数据一般采用特定的日期数据类型,由于无法确保实际的值及以后是否会发生改动,因此在进行筛选时,一般需要对值使用日期处理函数进行修饰,然后再与筛选条件进行比较。

SELECT * FROM txm_test WHERE Date(create_date) = '2016-09-12'

此外日期函数还有很多比如,Month和Year等...

4.4. 聚集函数

如果只是需要汇总数据而并非每行具体的数据,可以使用聚集函数,分别是:

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM
SELECT AVG(id) AS avg_id FROM txm_test