mysql学习笔记

最新推荐文章于 2024-12-02 14:38:21 发布

原创最新推荐文章于 2024-12-02 14:38:21 发布 · 221 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL

MYSQL 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了MySQL数据库的基础操作，包括连接和断开服务器、查询版本和时间、创建和使用数据库、表的操作、数据的加载和检索、数据更新以及各种查询技巧，如排序、日期计算、模式匹配和计数。

mysql学习笔记：
基础部分
连接和断开服务器：
要连接到服务器，通常需要在调用mysql时提供MySQL用户名，并且很可能是密码。
如果服务器在您登录的计算机以外的计算机上运行，则还需要指定主机名。
请与您的管理员联系，以了解您应该使用哪些连接参数进行连接（即，要使用的主机，用户名和密码）。
一旦知道了正确的参数，就应该能够像这样连接：
shell> mysql -h host -u user -p
Enter password: ********
host并 user表示运行MySQL服务器的主机名和MySQL帐户的用户名。替换适合您的设置的值。该 ********代表你的密码; 当mysql显示Enter password:提示时输入它。

成功连接后，您可以通过在提示符下键入QUIT（或\q）来随时断开连接mysql>：
mysql> QUIT
Bye

查询当前版本号，及系统时间：
SELECT VERSION(), CURRENT_DATE;

关于mysql的查询语句的说明：
 查询通常由一个SQL语句后跟一个分号组成。（有一些例外情况，可以省略分号。QUIT前面提到过，分号就是其中之一。我们稍后会找到其他分号。）

 当您发出查询时，mysql将其发送到服务器以执行并显示结果，然后打印另一个mysql>提示以指示它已准备好进行另一个查询。

 mysql以表格形式（行和列）显示查询输出。第一行包含列的标签。以下行是查询结果。通常，列标签是从数据库表中提取的列的名称。如果您正在检索表达式的值而不是表列（如刚刚显示的示例中所示），则 mysql使用表达式本身标记列。

 mysql显示返回了多少行以及执行查询所需的时间，这使您可以大致了解服务器性能。这些值是不精确的，因为它们代表挂钟时间（不是CPU或机器时间），并且因为它们受到服务器负载和网络延迟等因素的影响。（为简洁起见，本章其余示例中有时未显示“行中的行 ”行。）

关键字可以输入任何字母。可以大小写，以下查询是等效的：
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

几个查询实列：

1，简单的计算
    
mysql> SELECT SIN(PI()/4),(4+1)*4;

+--------------------+---------+
| SIN(PI()/4)    | (4+1)*4 |
+--------------------+---------+
| 0.7071067811865476 |   20 |
+--------------------+---------+
1 row in set (0.01 sec)

2，可以在一行中输入多个语句。用分号结束每一个：
mysql> select VERSION(); select NOW();
+------------+
| VERSION() |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)

+---------------------+
| NOW()        |
+---------------------+
| 2018-10-10 08:53:13 |
+---------------------+
1 row in set (0.00 sec)


3，多行的冗长查询也可进行。 mysql通过查找终止分号来确定语句的结束位置，而不是查找输入行的结尾。
（换句话说，mysql 接受自由格式输入：它收集输入行但在看到分号之前不执行它们。）

这是一个简单的多行语句：
mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;

+----------------+--------------+
| USER()     | CURRENT_DATE |
+----------------+--------------+
| root@localhost | 2018-10-10  |
+----------------+--------------+
1 row in set (0.00 sec)

如果您决定不想执行正在输入的查询，请键入\c以下内容取消它 ：
mysql> select
-> user()
 -> \c
mysql>

   常见的提示，以及它们对mysql所处状态的含义 。

数据库的创建与使用：

使用该SHOW语句查找服务器上当前存在的数据库：

mysql> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zzc |
±-------------------+

该mysql数据库描述了用户访问权限。该test数据库通常可作为用户工作区尝试的事情了。

声明显示的数据库列表可能与您的计算机不同; SHOW DATABASES 如果您没有该SHOW DATABASES 权限，则不会显示您没有权限的数据库。

尝试访问一个数据库zzc：
mysql> USE zzc;
Database changed

创建数据库:
mysql> create database zzcmarkron;
Query OK, 1 row affected (0.01 sec)
建议始终在使用时使用的相同字母

您的数据库只需创建一次，但每次开始mysql 会话时都必须选择它才能使用。您可以通过USE语句来完成使用操作。
mysql> use zzcmarkron
Database changed

表的操作：

查询已经存在的表，show方法：
mysql> show tables;
±--------------+
| Tables_in_zzc |
±--------------+
| students |
±--------------+
1 row in set (0.00 sec)

举例说明：
创建一个宠物表。

更难的部分是决定数据库的结构应该是什么：您需要哪些表以及每个表中应该包含哪些列。
您想要一张包含每只宠物记录的表格。这可以称为pet表格，它应该包含每个动物名称的最低限度。因为名称本身不是很有趣，所以该表应包含其他信息。
例如，如果您家中有多个人饲养宠物，您可能需要列出每只动物的主人。您可能还想记录一些基本的描述性信息，如物种和性别。

年龄怎么样？这可能是有意义的，但存储在数据库中并不是一件好事。随着时间的推移，年龄会发生变化，这意味着您必须经常更新记录。
相反，最好存储固定值，如出生日期。然后，无论何时需要年龄，您都可以将其计算为当前日期和出生日期之间的差异。
MySQL提供了进行日期算术的功能，因此这并不困难。存储出生日期而不是年龄也有其他优点：
您可以使用数据库执行任务，例如为即将到来的宠物生日生成提醒。（如果您认为这种类型的查询有些愚蠢，请注意，您可能会在业务数据库的上下文中询问相同的问题，以确定您需要在当前一周或一个月内向其发送生日祝福的客户，计算机辅助个人触摸。）
您可以计算与当前日期以外的日期相关的年龄。例如，如果您将死亡日期存储在数据库中，则可以轻松计算宠物死亡时的年龄。

综上所述，您可能会想到在pet表格中有用的其他类型的信息，但到目前为止确定的信息是足够的：名称，所有者，物种，性别，出生和死亡。
建表：
使用CREATE TABLE语句指定表的布局：
mysql> CREATE TABLE pet (NAME VARCHAR(20), OWNER VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.06 sec)

将数据加载到表中
创建表后，您需要填充它。使用 LOAD DATA和 INSERT语句来实现添加数据。

假设您的宠物记录可以如下所示进行描述。（注意MySQL使用’YYYY-MM-DD’格式的日期）

从一个空表开始，所以填充它的一种简单方法是为每个动物创建一个包含行的文本文件，然后使用单个语句将该文件的内容加载到表中。
您可以创建一个文本文件pet.txt ，每行包含一个记录，其值由制表符分隔，
并按照CREATE TABLE语句中列出的顺序给出。对于缺失值（例如未知性别或仍然生活的动物的死亡日期），您可以使用NULL 值。要在文本文件中表示这些，请使用 \N（反斜杠，大写-N）。例如，Whistler 鸟的记录看起来像这样（值之间的空格是单个制表符）：
Whistler Gwen bird \N 1997-12-09 \N

要将文本文件加载pet.txt到 pet表中，请使用以下语句：
mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE pet;

如果您在Windows上使用编辑器创建该文件 \r\n作为行终止符，则应使用此语句：
mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE pet
-> LINES TERMINATED BY ‘\r\n’;

如果需要，可以在LOAD DATA语句中显式指定列值分隔符和行结束标记，但默认值为制表符和换行符。这些语句足以使语句pet.txt正确读取文件。如果需要，可以在LOAD DATA语句中显式指定列值分隔符和行结束标记，但默认值为制表符和换行符。这些语句足以使语句pet.txt正确读取文件。
如果语句失败，则默认情况下，您的MySQL安装可能没有启用本地文件功能。有关如何更改此信息的信息，请参见“LOAD DATA LOCAL的安全问题”。
如果要一次添加一条新记录，使用INSERT语句。在最简单的形式中，您按照CREATE TABLE语句中列出的列的顺序为每列提供值。假设Diane得到一只名为“ Puffball ”的新仓鼠。“您可以使用如下INSERT语句添加新记录：
mysql> insert into pet
-> values(‘Puffball’,‘Diane’,‘hmaster’,‘f’,‘1999-08-19’,NULL);
Query OK, 1 row affected (0.01 sec)
insert 一次性插入多行语句，
语法：insert table into values（数值），（数值），（数值）,…,();

从表中检索信息
 选择所有数据
 选择特定行
 选择特定列
 排序行
 日期计算
 使用NULL值
 模式匹配
 计数行
 使用多个表

该SELECT语句用于从表中提取信息。
声明的一般形式是：
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
what_to_select表示你想看到什么。这可以是列列表，也 *可以表示“ 所有列”。
“ which_table表示要从中检索数据的表。该WHERE 语句是可选择进行筛选的，是否用根据具体情况。
如果存在，则 conditions_to_satisfy指定行必须满足的一个或多个条件才有资格进行检索。

 选择所有数据：
要选择所有数据 select * from table；

使用 update 语句修复错误记录：
将Bowser 的年份改为1989年。
语法：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]；

UPDATE pet SET birth = ‘1989-08-31’ WHERE name = ‘Bowser’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

输出结果证实，这一年被正确记录为1989年，而不是1979年。
字符串比较通常不区分大小写，因此您可以将名称指定为’bowser’， 'BOWSER’等等。查询结果是一样的。

 选择特定行:
想知道1998年或之后出生的动物，请测试该 birth栏:
mysql> use zzc
Database changed

mysql> SELECT * FROM pet WHERE birth >= ‘1998-1-1’;

 选择特定列

如果您不想查看表中的整行，
查看特定列只需将特定的列命名用逗号分隔。
查询宠物名字和出生日期：
mysql> select name,birth from pet;
±---------±-----------+
| name | birth |
±---------±-----------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puffball | 1999-03-30 |
±---------±-----------+
9 rows in set (0.00 sec)

使用WHERE子句将行选择与列选择组合在一起
仅获取狗和猫的出生日期。
mysql> select name,species,birth from pet
-> where species='cat’or species=‘dog’;
±--------±--------±-----------+
| name | species | birth |
±--------±--------±-----------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
±--------±--------±-----------+
5 rows in set (0.00 sec)

 排序行
要对结果进行排序，请使用ORDER BY子句，此字句默认升序。

mysql> select name,birth from pet order by birth;
±---------±-----------+
| name | birth |
±---------±-----------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
±---------±-----------+
9 rows in set (0.00 sec)

在字符类型列上，排序与所有其他比较操作一样，通常以不区分大小写的方式执行。

默认排序顺序为升序，首先是最小值。
要按反向（降序）顺序排序，请将DESC关键字添加到要排序的列的名称

mysql> select name,species,birth from pet
->order by species, birth DESC;
±---------±--------±-----------+
| name | species | birth |
±---------±--------±-----------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
±---------±--------±-----------+
9 rows in set (0.00 sec)

 日期计算
MySQL提供了几个可用于执行日期计算的函数，例如，计算年龄或提取日期的部分。
确定每只宠物的年龄，请使用此 TIMESTAMPDIFF()功能。
TIMESTAMPDIFF(unit,begin,end);
它的参数是您希望表达结果的单位，以及两个可以区分的日期。

下查询显示每只宠物的出生日期，当前日期CURDATE（）和年龄。取别名（age）使最终输出列标签更有意义，语法 as name;

mysql> select name,birth,CURDATE(),
->TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
->from pet;
±---------±-----------±-----------±-----+
| name | birth | CURDATE() | age |
±---------±-----------±-----------±-----+
| Fluffy | 1993-02-04 | 2018-10-10 | 25 |
| Claws | 1994-03-17 | 2018-10-10 | 24 |
| Buffy | 1989-05-13 | 2018-10-10 | 29 |
| Fang | 1990-08-27 | 2018-10-10 | 28 |
| Bowser | 1989-08-31 | 2018-10-10 | 29 |
| Chirpy | 1998-09-11 | 2018-10-10 | 20 |
| Whistler | 1997-12-09 | 2018-10-10 | 20 |
| Slim | 1996-04-29 | 2018-10-10 | 22 |
| Puffball | 1999-03-30 | 2018-10-10 | 19 |
±---------±-----------±-----------±-----+
9 rows in set (0.00 sec)
查询有效，但如果以某种顺序显示行，则可以更轻松地扫描结果。这可以通过添加一个ORDER BY name子句来按名称对输出进行排序来完成：
mysql> select name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age from pet order by name;
±---------±-----------±-----------±-----+
| name | birth | CURDATE() | age |
±---------±-----------±-----------±-----+
| Bowser | 1989-08-31 | 2018-10-10 | 29 |
| Buffy | 1989-05-13 | 2018-10-10 | 29 |
| Chirpy | 1998-09-11 | 2018-10-10 | 20 |
| Claws | 1994-03-17 | 2018-10-10 | 24 |
| Fang | 1990-08-27 | 2018-10-10 | 28 |
| Fluffy | 1993-02-04 | 2018-10-10 | 25 |
| Puffball | 1999-03-30 | 2018-10-10 | 19 |
| Slim | 1996-04-29 | 2018-10-10 | 22 |
| Whistler | 1997-12-09 | 2018-10-10 | 20 |
±---------±-----------±-----------±-----+
9 rows in set (0.00 sec)

类似的查询可用于确定死亡动物的死亡年龄。您可以通过检查death值是否确定这些动物NULL。然后，对于那些具有非NULL值的人，计算death和 birth值之间的差异：
mysql> select name,birth,death,
->TIMESTAMPDIFF(YEAR,birth,death) AS age
->from pet where death is NOT NULL
->order by name;
±-------±-----------±-----------±-----+
| name | birth | death | age |
±-------±-----------±-----------±-----+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
±-------±-----------±-----------±-----+
1 row in set (0.00 sec)

想知道哪些动物下个月有生日怎么办？对于这种类型的计算，年和日是无关紧要的; 您只想提取birth列的月份部分。
MySQL提供了用于提取日期的部分，如一些功能 YEAR()， MONTH()和 DAYOFMONTH()。
MONTH()这是适当的功能。看看它是如何工作的，运行，显示两者的价值一个简单的查询birth和 MONTH(birth)：

mysql> select name,birth,MONTH(birth) from pet;
±---------±-----------±-------------+
| name | birth | MONTH(birth) |
±---------±-----------±-------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
±---------±-----------±-------------+
9 rows in set (0.00 sec)

找到5月出生的宠物：

查询下下个月过生日的宠物：
DATE_ADD()使您可以将时间间隔添加到给定日期。
如果您将值添加一个月CURDATE()，然后使用，则提取月份部分MONTH()，结果将生成查找生日的月份
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 2 MONTH));
±---------±-----------+
| name | birth |
±---------±-----------+
| Whistler | 1997-12-09 |
±---------±-----------+
1 row in set (0.00 sec)

下个月将2 MONTH 改 1MONTH即可

模式匹配
是否匹配使使用 LIKE 或 NOT LIKE 比较运算符

含某字符开头LIKE ‘b%’
mysql> SELECT * FROM pet WHERE name LIKE ‘b%’;
±-------±-------±--------±-----±-----------±-----------+
| NAME | OWNER | species | sex | birth | death |
±-------±-------±--------±-----±-----------±-----------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
±-------±-------±--------±-----±-----------±-----------+
2 rows in set (0.00 sec)

MySQL提供的另一种模式匹配使用扩展的正则表达式。
当您测试此类模式的匹配项时，请使用 REGEXP_LIKE()函数.
以下列表描述了扩展正则表达式的一些特征：

 . 匹配任何单个字符。
 字符类[…]匹配括号内的任何字符。例如， [abc]匹配a， b或c。要命名一系列字符，请使用短划线。[a-z] 匹配任何字母，而[0-9] 匹配任何数字。
 匹配前面的事物的零个或多个实例。例如，x 匹配任意数量的x字符， [0-9]*匹配任意数量的数字，并.*匹配任意数量的任何数字。
 如果模式匹配正在测试的值中的任何位置，则正则表达式模式匹配成功。（这与LIKE模式匹配不同，模式匹配仅在模式匹配整个值时才会成功。）
 锚定的图案，使得它必须在值的开头或结尾匹配正在测试中，使用^在一开始或$在图案的端部。为了演示扩展正则表达式的工作原理，LIKE先前显示的查询将在此处重写以供使用 REGEXP_LIKE()。
要查找以某字符开头的名称b，请使用 ^某字符以匹配名称的开头：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, ‘^b’);
±-------±-------±--------±-----±-----------±-----------+
| name | owner | species | sex | birth | death |
±-------±-------±--------±-----±-----------±-----------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
±-------±-------±--------±-----±-----------±-----------+

要强制将正则表达式比较区分大小写，请使用区分大小写的排序规则，或使用 BINARY关键字使其中一个字符串成为二进制字符串，或指定c 匹配控制字符。这些查询中的每b一个在名称的开头仅匹配小写：

SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY ‘^b’);

要查找以？结尾的名称fy，请使用 $REGEXP_LIKE(name, 'fy$ ');
±-------±-------±--------±-----±-----------±------+
| name | owner | species | sex | birth | death |
±-------±-------±--------±-----±-----------±------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
±-------±-------±--------±-----±-----------±------+

要查找包含a的名称w，请使用以下查询：
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, ‘w’);
±---------±------±--------±-----±-----------±-----------+
| name | owner | species | sex | birth | death |
±---------±------±--------±-----±-----------±-----------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
±---------±------±--------±-----±-----------±-----------+

因为正则表达式模式匹配，如果它出现在值的任何位置，则在前一个查询中不需要在模式的任何一侧放置通配符以使其与整个值匹配，就像SQL模式一样。
要查找包含五个字符的名称，请使用 ^和 $REGEXP_LIKE(name, '^.....$ ’);
±------±-------±--------±-----±-----------±------+
| name | owner | species | sex | birth | death |
±------±-------±--------±-----±-----------±------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
±------±-------±--------±-----±-----------±------+

您还可以使用（“ repeat- -times ”）运算符编写上一个查询： {n}n

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, ‘^.{5}$’);
±------±-------±--------±-----±-----------±------+
| name | owner | species | sex | birth | death |
±------±-------±--------±-----±-----------±------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
±------±-------±--------±-----±-----------±------+
计数
COUNT()计算行数，
因此计算动物的查询如下所示：
mysql> SELECT COUNT() FROM pet;
±---------+
| COUNT() |
±---------+
| 9 |
±---------+
1 row in set (0.01 sec)
按某一列输出计数group by
mysql> select owner,count() from pet group by owner;
±-------±---------+
| owner | count(*) |
±-------±---------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
±-------±---------+
4 rows in set (0.00 sec)
获取有关数据库和表的信息
列出了服务器管理的数据库。要找出当前选择的数据库，请使用以下SELECT DATABASE()函数
mysql> SELECT DATABASE();
±-----------+
| DATABASE() |
±-----------+
| zzc |
±-----------+
1 row in set (0.00 sec)