MySQL官方手册阅读第一部分

1.3 讲解

1.3.1 将数据加载到表中

​ 创建一个文本文件pet.txt ，每行包含一个记录，其值由制表符分隔。缺少的值（例如未知的性别或仍在生活中的动物的死亡日期），可以使用NULL 值。要在文本文件中表示这些字符，请使用 \N（反斜杠，大写N）

要将文本文件加载student.txt到 student表：

vim student.txt 
  1 sssssg  \N	#制表符进行分隔
  2 llllll  NULL  #想使用NULL表示空值,从下面的结果可以看到失败
  3 kkkk 111 #想使用空格进行分隔,从下面的结果可以看到失败
+-------------+------+
| name        | age  |
+-------------+------+
| sssssg      | NULL |  
| llllll      |    0 |
| kkkk 111    |    0 |
+-------------+------+

load data local infile '文件所在路径' into table xxx

如果您在Windows上使用\r\n用作行终止符的编辑器创建了文件 ，则应改用以下语句：

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

1.3.2排序

​ 字符类型列上的排序通常以不区分大小写的, 强制的列区分大小写的排序BINARY， ORDER BY BINARY col_name

1.3.3 日期计算

​ CURRENDATE() 当前日期函数

​ TIMESTAMPDIFF() 它的参数是要表示结果的单位，以及两个日期之间的差值 如：

​ MySQL提供了几个用于提取日期的部分，如 YEAR()， MONTH()和 DAYOFMONTH()。

计算下一个下个月：（用例都用斜体)

​ 方法一： DATE_ADD()使您可以将时间间隔添加到给定的日期

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

​ 方法二： 使用模函数（MOD）将月份值包装为0（如果当前值为12）

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

1.3.4 NULL值

- 在GROUP BY中，两个NULL值被视为相等
- 在执行ORDER BY时，如果执行ORDER BY ... ASC，则将首先显示NULL值；如果执行ORDER BY ... DESC，则将最后显示NULL值。

1.3.5 模式匹配

MySQL提供了标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式

• _ 用来匹配任何单个字符
• %匹配任意数量的字符（包括零个字符）
• 请勿使用 =或<>使用SQL模式时。改用LIKE或 NOT LIKE比较运算符。

MySQL提供的另一种模式匹配使用扩展的正则表达式。测试使用 REGEXP和NOT REGEXP运算符（或 RLIKE和 NOT RLIKE，它们是同义词）如：

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

注

​ 强制REGEXP比较区分大小写，请使用BINARY 关键字将字符串之一设置为二进制字符串

查找正好包含五个字符的名称：

 SELECT * FROM pet WHERE name REGEXP '^.....$'

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

SELECT * FROM pet WHERE name REGEXP '^.{5}$';

注： 这里的匹配只适合英文字符 ，中文不适用

1.3.6 在批处理模式下使用mysql

将要运行的语句放在文件中，然后告诉 mysql从文件中读取其输入：

当前你路径下创建b.txt文件

vim b.txt
  1 use test
  2 insert into student values("liushishi",30),("yangmi",30);
  3 select * from student

mysql -uxxx -pxxx < ./b.txt 

获取交互式输出格式，请使用mysql -t,要将执行的语句回显到输出，请使用mysql -v

mysql -uxxx -pxxx -t < ./b.txt > a.txt

其中的某些语句产生错误,您希望脚本继续运行,使用 --force命令行选项

vim b.txt
  1 use test
  2 insert into student values("liushishi",30),("yangmi",30);
  3 insert into studenti values(lfldl,o2o,3oo);
  4 select * from student
//第三行错误

mysql -uxxx -pxxx -t --force < ./b.txt > a.txt 使用了–force，依然可以运行成功

从cron作业运行查询时。在这种情况下，您必须使用批处理模式

Cron是一个实用程序，用于在特定的时间自动执行重复任务

可以使用以下命令 从mysql提示符中使用脚本： source``\.

source [文件路径];
或
\. [文件路径](不能用;结尾)

1.3.7 常见查询示例

​ 对于InnoDB 以外的其他存储引擎，可以使用REFERENCES tbl_name（col_name）子句，但是该子句没有实际作用，并且仅作为备忘录或注释，告知您当前定义的列是预期的引用另一个表中的列:

• MySQL不会执行任何形式的检查来确保它 *col_name*确实存在 tbl_name（甚至 *tbl_name*本身是否存在）。
• MySQL不执行任何类型的操作， *tbl_name*例如响应定义的表中的行而删除行。换句话说，这种语法不引起ON DELETE或ON UPDATE行为的任何责任。（尽管您可以将ON DELETEor ON UPDATE子句编写为该REFERENCES子句的一部分，但也将其忽略。）
• 此语法创建一列；它并 不会创建任何类型的索引或关键的。

换句话说上面这些对外键的要求只在InnoDB引擎中生效

计算每天的访问量：

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

BIT_COUNT(expr)： 返回 expr 的二进制表达式中1的个数

BIT_OR(expr): 返回 expr的二进制表达式进行位或运算

使用AUTO_INCREMENT

​ 更新表中的现有AUTO_INCREMENT列值InnoDB不会AUTO_INCREMENT像MyISAM和NDB表一样重置序列 ，

可以使用LAST_INSERT_ID（）SQL函数或mysql_insert_id（）C API函数检索最新自动生成的AUTO_INCREMENT值

要以AUTO_INCREMENT非1 的值开头，请使用CREATE TABLE或ALTER TABLE设置该值

ALTER TABLE tbl AUTO_INCREMENT = 100;
或
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
)AUTO_INCREMENT =10;

MyISAM笔记

​ 对于MyISAM表，可以在多列索引的第二列上指定AUTO_INCREMENT。 在这种情况下，将为AUTO_INCREMENT列生成的值计算为MAX（auto_increment_column）+ 1 WHERE prefix = given-prefix。 当您要将数据放入有序组中时，这很有用。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

​ 当AUTO_INCREMENT 列是多列索引的一部分时）， AUTO_INCREMENT如果删除AUTO_INCREMENT任何组中具有最大值的行，则将重用值

delete from animals where grp='mammal' and id = 3;
insert into animals values('mammal',NULL,'pig');
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | pig     | //id=3 被重用了
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

​ 如果AUTO_INCREMENT列是多个索引的一部分，则MySQL使用以AUTO_INCREMENT列开头的索引（如果有的话）生成序列值。 例如，如果动物表包含索引PRIMARY KEY（grp，id）和INDEX（id），则MySQL将忽略PRIMARY KEY来生成序列值。 结果，该表将包含单个序列，而不是每个grp值的序列。

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id),
    index(id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| bird   |  3 | penguin |
| fish   |  4 | lax     |
| mammal |  5 | whale   |
| bird   |  6 | ostrich |
+--------+----+---------+