MySQL的一些操作

最新推荐文章于 2021-03-17 17:27:47 发布

fangsi1994_

最新推荐文章于 2021-03-17 17:27:47 发布

阅读量138

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/fangsi1994_/article/details/97979099

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

行分类

然而，当行按某种方式排序时，检查查询输出的时候通常变得相对简单一点。为了给结果排序，使用 ORDER BY 子句。这里是对动物生日按日期排序：

mysql> SELECT name, birth FROM pet ORDER BY birth;

由于字符串类型的数据对大小写并不敏感。这意味着，对于大小写不同其他都一样的列，排序后的结果都不会更改。这种情况下，可以使用 BINARY 强制执行区分大小写的排序功能，如：ORDER BY BINARY col_name 。

默认排序是升序，也就是最小的值排在第一。要想以降序排序，在你正在排序的列名旁边增加 DESC（降序）关键字：

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

注意 DESC 关键字仅适用于在它前面的列名 birth ；不影响 species 列的排序顺序

日期计算

MySQL 有用来计算日期的函数，比如计算年龄或提取日期部分。

要想确定每个宠物有多大，可以使用函数 TIMESTAMPDIFF 计算当前日期的年和出生日期之间的差也可以按照直接使用语句 (YEAR(CURDATE())-YEAR(birth)) 计算，其中函数 CURDATE 是计算当前的日期。如果当前日期的日历年比出生日期早，则减去一年。以下代码是查询每个宠物的出生日期、当前日期和年龄（以年作为计算单位），其中关键字 age 是年龄这个计算结果的标签。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
    -> FROM pet;

# 比较这两个查询语句的结果
mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE()) - YEAR(birth)) - (RIGHT(CURDATE(), 5) < RIGHT(birth, 5))
    -> AS age FROM pet;

此处，YEAR() 提取日期的年数，RIGHT() 提取日期最右面 5 个字符的 MM-DD（月份和日期）部分。MM-DD 值的表达式部分的值一般为 1 或 0，如果 CURDATE() 的年比 birth 的年早，则年份应减去 1。整个表达式看起来有些难懂，使用 age 来使输出的列标记更有意义。

(RIGHT(CURDATE(), 5) < RIGHT(birth, 5)) 当这个式子成立，式子的布尔值为真，这个式子的值就会等于 1 ；反之式子不成立，布尔值为假，式子的值为 0 。

尽管查询可行，但整体输出结果缺乏秩序，如果以某个顺序排列行，那么会使得浏览结果变得更加轻松。添加 ORDER BY name 子句则能够实现按照名字进行排序输出。

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;

为了按 age 而非 name 排序输出，只要再使用一个 ORDER BY 子句：

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY age;

可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查 death 值是否为 NULL 来确定是哪些动物已经死亡，然后对于那些非 NULL 值的动物，需要计算出 death 和 birth 值之间的差来知道他们在这个世界上所存在的时间：

mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

查询使用 death IS NOT NULL 而非 death != NULL ，因为NULL是特殊的值，不能使用普通比较符来比较。

如果你想要知道哪个动物下个月过生日怎么办？对于这类计算，年和天是无关的，你只需要提取 birth 列的月份部分。MySQL 提供几个日期方面的提取函数，例如 YEAR 、MONTH 和 DAYOFMONTH 。在这里 MONTH 是我们需要的函数。为了观察它的实现原理，可以运行以下简单的查询显示 birth 和 MONTH(birth) 的值：

mysql> SELECT name, birth, MONTH(birth) FROM pet;

找出下个月生日的动物也很简单。假定当前月是4月，那么月值是 4 ，你需要找在 5 月出生的动物，方法是：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

如果当前月份是 12 月，就有点复杂了。你不能只把 1 加到月份数 12 上并寻找在 13 月出生的动物。相反，你应寻找在 1 月出生的动物。

你甚至可以编写查询，不管当前月份是什么它都能执行。因此不必在查询中使用一个特定的月份，DATE_ADD 允许在一个给定的日期上加上时间间隔。如果在 NOW 值上加上一个月，然后用 MONTH 提取月份，产生生日所在月份：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成该任务的另一个方法是加 1 得出当前月份的下一个月（在使用取模函数 MOD() 后，如果月份当前值是 12 ，则“返回”到值 0 )：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

注意，MONTH 返回在 1 和 12 之间的一个数字，且 MOD(something,12) 返回在 0 和 11 之间的一个数字，因此必须在 MOD() 后加 1 ，否则我们将从 11 月跳到 1 月。

NULL 值操作

NULL 值可能令人感到奇怪因此你需要习惯它。概念上，NULL 意味着“没有值”或“未知值”，并且它被看作使与众不同的值。为了测试 NULL ，你不能使用算术比较操作符例如 = 、< 或 != 。为了了解它，试试下列查询：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

很显然你不能通过这些得到有意义的结果，因为任何使用算数比较操作符对 NULL 进行比较的结果都是 NULL 。因此使用 IS NULL 和 IS NOT NULL 操作符：

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

请注意在 MySQL 中，0 或 NULL 意味着假而其它值意味着真。布尔运算的默认真值是 1 。

为了查询出哪个动物不再是活着的，使用 death IS NOT NULL 而不使用 death != NULL 的原因。

在 GROUP BY 中，两个 NULL 值被视为相同等价的

执行 ORDER BY 语句排序时，如果运行 ORDER BY ... ASC ，则 NULL 值出现在最前面，若运行 ORDER BY ... DESC ，则 NULL 值出现在最后面。

NULL 操作的常见错误是认为不能在定义为 NOT NULL 的列内插入 0 或空字符串，但事实并非如此。这样的结果是在 NULL 表示 "没有数值" 的时候恰恰是有数值 0 存在的。因此使用 IS [NOT] NULL 则可以很容易地进行区分，如下所示：

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

因此完全可以在定义为 NOT NULL 的列内插入 0 或空字符串，因为它们实际是 NOT NULL 。

模式匹配

MySQL 提供标准的 SQL 模式匹配，以及一种基于类 Unix 里的程序如 vi 、grep 和 sed 里的扩展正则表达式模式匹配的格式。 SQL 模式匹配允许你使用 _ 匹配任何单个字符，而 % 匹配任意数量（包括零）的字符。在 MySQL 中，SQL 的模式默认是忽略大小写的。下面给出一些例子。注意使用 SQL 模式时，不能使用 = 或 != ；而应使用 LIKE 或 NOT LIKE 比较操作符。要想找出以 “b” 开头的名字的动物信息：

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

要想找出以 “fy” 结尾的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

要想找出包含 “w” 的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

要想找出正好包含 5 个字符的名字，使用 _ 模式字符：

mysql> SELECT * FROM pet WHERE name LIKE '_____';

由 MySQL 提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用 REGEXP 和 NOT REGEXP 操作符（或 RLIKE 和 NOT RLIKE ，它们是同义词）。

扩展正则表达式的一些字符是：

‘.’ 匹配任何单个的字符。
字符类 “[...]” 匹配在方括号内的任何字符。例如，“[abc]” 匹配 “a” 、“b” 或 “c”。为了命名字符的范围，使用一个 “-”。“[a-z]” 匹配任何字母，而 “[0-9]” 匹配任何数字。
* 匹配零个或多个在它前面的东西。例如，x* 匹配任何数量的 “x” 字符，[0-9]* 匹配的任何数量的数字，而 .* 匹配任何数量的任何东西。

如果 REGEXP 模式与被测试值的任何地方匹配，模式就匹配(这不同于 LIKE 模式匹配，只有与整个值匹配，模式才匹配)。为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用 “^” 或在模式的结尾用 “$”。为了说明扩展正则表达式如何工作，下面使用 REGEXP 重写上面所示的 LIKE 查询：

为了找出以 “b” 开头的名字，使用 “^” 匹配名字的开始：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

如果你想强制使 REGEXP 比较区分大小写，使用 BINARY 关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写 ‘b’。

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以 “fy” 结尾的名字，使用 “$” 匹配名字的结尾：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

为了找出包含一个 “w” 的名字，使用以下查询：

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

既然如果一个正则表达式出现在值的任何地方，他就会被模式匹配，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个 SQL 模式那样。

为了找出包含正好 5 个字符的名字，使用 “^” 和 “$” 匹配名字的开始和结尾，和 5 个 “.” 实例在两者之间：

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

你也可以使用 “{n}” 重复 n 次操作符，重写前面的查询：

mysql> SELECT * FROM pet WHERE name REGEXP

计算行数

数据库经常用于回答这个问题，“查询出某个类型的数据在表中出现的频数是多少?”

例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要对你的动物进行各种类型的普查。

计算你拥有动物的总数目与 “在 pet 表中有多少行?” 是同样的问题，因为每个宠物都对应一条记录。COUNT 函数计算行数，所以计算动物数目的查询应为：

mysql> SELECT COUNT(*) FROM pet;

在前面的章节中，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你也可以使用 COUNT(*) 函数：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

注意，使用 GROUP BY 对每个 owner 的所有记录分组，没有它，你会得到错误消息：

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT(*) 和 GROUP BY 以各种形式分类你的数据。下列例子显示出以不同方式进行动物普查操作。

查看每种动物的数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

查看每种性别的动物数量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

按种类和性别组合分类的动物数量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

若使用 COUNT(*)，你不必检索整个表。例如当只对狗和猫进行查询时，应为：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;

如果你仅需要知道已知性别的按性别分组的动物数目：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;

使用多张表

pet 表追踪你拥有的宠物。如果你想要记录其它相关信息，例如在它们看兽医的情况或后代出生的情况，那么你需要另外的表。这张表应该拥有些什么呢？它需要：

需要包含宠物名字以便你知道每个发生的事件属于哪个动物
需要一个日期以便你知道事件是什么时候发生的
需要一个描述事件的字段
如果你想要对事件进行分类，则需要一个事件类型字段

综上所述，event 表的 CREATE TABLE 语句应为：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

类似于 pet 表，最简单的方法是创建一个用定位符分隔的文本文件来加载载初始记录，记住仍然使用 gedit 来编辑文件：

$ gedit /home/shiyanlou/Desktop/event.txt

此处输入图片的描述

采用如下方式加载记录：

mysql> LOAD DATA  INFILE '/home/shiyanlou/Desktop/event.txt' INTO TABLE event;

此处输入图片的描述

由于你已经在 pet 表上的查询中学到了一定的知识，你应该能执行对 event 表中记录的检索；原理是一样的。但是有没有 event 表本身不能回答你可能问的问题呢?

当宠物们生了了一窝小动物时，假定你想要找出这时候每只宠物的年龄。我们前面看到了如何通过两个日期计算年龄。event 表中有母亲的生产日期，但是为了计算母亲的年龄，你需要她的出生日期，存储在 pet 表中。说明查询需要两个表：

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND event.type = 'litter';

此处输入图片的描述

关于该查询要注意以下几件事：

FROM 子句连接两个表，因为查询需要从两个表中提取信息。
当从多个表组合（联结）信息时，你需要指定其中一个表中的列明以期匹配其它表的列名。这很简单，因为它们都有一个 name 列，查询可以通过使用 WHERE 子句基于 name 值来匹配两个表中的记录。
因为 name 列都存在两个表中，因此当引用该列时，一定要指定是哪个表，把表名附在列名前即可以实现。如果你想要将一个表的记录与该表的其它记录进行比较，可以将该表联结到自身。例如，为了在你的宠物之中选择繁殖中的配偶，你可以用 pet 表联结自身来进行相同种类的雄雌配对：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1, pet AS p2
    -> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

在这个查询中，我们为表名指定别名 p1 和 p2 以便能引用它们的列并且使得每一个列的引用更直观。

获得数据库和表的信息

如果你忘记数据库或表的名字，或给定的表的结构是什么（例如，它的列叫什么），怎么办？MySQL 提供一些语句解决这个问题。你已经知道 SHOW DATABASES 可以列出由服务器管理的所有数据库。为了找出当前选择了哪个数据库，使用 DATABASE 函数：

mysql> SELECT DATABASE();

如果你还没选择任何数据库，结果是 NULL 。

为了找出当前的数据库包含什么表（例如，当你不能确定一个表的名字），使用这个命令：

mysql> SHOW TABLES;

如果你想要知道一个表的结构，可以使用 DESCRIBE 命令；它显示表中每个列的信息：

mysql> DESCRIBE pet;

Field 显示列名字，Type 是列的数据类型，Null 表示列是否能包含 NULL 值，key 显示列是否被索引而 Default 指定列的默认值。

如果表有索引，SHOW INDEX FROM tbl_name 生成有关索引的信息。

fangsi1994_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL的一些操作

行分类然而，当行按某种方式排序时，检查查询输出的时候通常变得相对简单一点。为了给结果排序，使用 ORDER BY 子句。这里是对动物生日按日期排序：mysql> SELECT name, birth FROM pet ORDER BY birth;由于字符串类型的数据对大小写并不敏感。这意味着，对于大小写不同其他都一样的列，排序后的结果都不会更改。这种情况下，可以使用 BI...
复制链接

扫一扫

专栏目录