MySQL8.0 相关汇总Windows 平台下提供两种安装 MySQL 的方式： MySQL 图形化安装（.msi 安

安装

Windows 平台下提供两种安装 MySQL 的方式：

MySQL 图形化安装（.msi 安装文件）。
免安装版（.zip 压缩文件）。

免安装版使用方法：

解压后的目录创建my.ini文件，需要手动创建。
并解压后的bin文件夹目录添加到环境变量
写入基本配置到my.ini：

> [mysqld]
\# 设置导出路径
secure_file_priv="D:\\MySqlOutput"
\# 设置3306端口
port=3306
\# 设置mysql的安装目录，一定要与上面的安装路径保持一致
basedir=D:\\MySQL\\mysql-8.0.20-winx64
\# 设置mysql数据库的数据的存放目录，自动生成，无需手动创建，当然也可以放在其他地方
datadir=D:\\MySQL\\mysql-8.0.20-winx64\\Data
\# 允许最大连接数
max_connections=200
\# 允许连接失败的次数。
max_connect_errors=10
\# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
\# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
\# 默认使用 "mysql_native_password"插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
\# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
\# 设置mysql客户端连接服务端时默认使用的端口，不建议修改，这是公认端口号
port=3306
default-character-set=utf8mb4

以管理员身份运行cmd：

cmd > mysqld --initialize --console  #"root@localhost: "后面的是初始密码
cmd > mysqld --install #安装mysql服务
cmd > net start mysql  # 启动mysql的服务
cmd > net stop mysql   # 关闭mysql服务
cmd > mysql -u root -p刚才复制的密码 #登录mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';  #修改密码

在这里插入图片描述

基本语法

mysql语句以 ; 结尾，不区分大小写，一行可以执行多条语句，一条语句也可以分成多行来写，不想执行一条语句可以通过 \c 来取消。在MySQL中，为了区分MySQL的关键字与普通字符，MySQL引入了一个反引号(键盘左上角)来区别关键字。

shell> mysql -h localhost -u user -p
Enter password: ********
#CURRENT_DATE输出当前日期
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT
    -> USER()
    -> \c
mysql>

关键字：
not like binary 、 union all 、 between ... and ... 、 IN  、 limit 、 and or 、
Inner Left Right Full join 、 as 、 distinct

不同的命令提示符有不同的含义

Prompt	Meaning
mysql>	等待输入
->	多行查询中，等待下一行输入
'>	等待下一行输入，使得之前出现的单引符号 (') 完整
">	等待下一行输入，使得之前出现的双引符号(")完整
`>	等待下一行输入，使得之前出现的撇号，即：backtick (`)完整
/*>	等待下一行输入，使得之前出现的注释符号 /* 完整
例如：

#'>提示符，要先输入'在输入\c来取消命令
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
mysql>

访问数据库

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+
#上面三个是MySQL自带的数据库，mysql数据库记录了用户访问权限，test数据库用于做测试，所有用户均有对test数据库的访问权限。

#USE、QUIT 命令加不加分号都可以，USE命令必须独占一行。
mysql> USE test
Database changed
#查看当前用户权限：
show grants;
#查看其他 MySQL 用户权限：
show grants for 'user'@'localhost';
#赋予用户权限
GRANT ALL ON database_name.* TO 'your_mysql_name'@'your_client_host';
#撤销用户权限
revoke all on *.* from 'user'@'your_client_host';

mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
Database changed
#可以在登录mysql时就选好数据库，这样就不用 USE menagerie 了
shell> mysql -h host -u user -p menagerie
Enter password: ********

重要命令中的menagerie不是密码。如果想在命令行中使用-p选项提供密码，不需要中间的空格(例如-ppassword，而不是作为-p password)。但是，不建议将密码放在命令行上，因为这样做会暴露密码给在您的机器上登录的其他用户。 小贴士 使用，命令SELECT DATABASE()可以在任意时候查看正在操作的数据库

建表

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SHOW TABLES; #查看数据表
mysql> DESCRIBE pet; #查看表结构信息

加载数据

通过文本文件加载数据

假设要加载数据为：

name	owner	species	sex	birth	death
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Whistler	Gwen	bird		1997-12-09

填充空表的一种简单方法是创建一个文本文件pet.txt，其中每一行包含一条记录，然后用一条语句将该文件的内容加载到表中。文件内容的格式如下：

#每一项用tab键隔开，对于缺失值使用NULL代替，文本中使用 \N (backslash, capital-N)来表示NULL
Whistler        Gwen    bird    \N      1997-12-09      \N

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
#如果在Windows系统中换行符为 \r ，则应该使用以下语句
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r';

在命令LOAD DATA中，可以显式指定列值分隔符和行标记结束符 , 但默认的是tab 和 linefeed。

插入数据（表复制、条件插入）

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
mysql> CREATE TABLE t(c VARCHAR(10), i INT);
mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
#条件插入
mysql> INSERT INTO table(field1, field2, fieldn) 
	   SELECT 'field1', 'field2', 'fieldn' 
	   FROM DUAL #Oracle数据库的语法要求，SQLServer语法中直接去掉即可
	   WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)
#条件插入示例
mysql> INSERT INTO 
	   table_name (id,device_id,data) //要插入的字段
	   SELECT
	   id,device.id,data //要插入的值，为空可以用null代替  不能带括号，否则Operand should contain 1 column(s)
       FROM DUAL //dual是为了构建Oracle查询语句而存在的表,SQLServer语句中去除即可
       WHERE NOT EXISTS(SELECT device_id,data FROM 表A WHERE device_id = ‘device.id' AND data = ‘data' )  
        //判断device_id,data相同的值是否存在，存在则不添加

#具有SELECT子句的 INSERT 语句。在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。
#此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表。
#首先，通过复制tasks表的结构，创建一个名为tasks_bak的新表
mysql> CREATE TABLE tasks_bak LIKE tasks;
mysql> SELECT * INTO t1 FROM titles WHERE 1=2; #可用于SQL Server中
#第二步，使用以下INSERT语句将tasks表中的数据插入tasks_bak表
#数据量少的话,在几万行左右建议使用此方案, 如果数据量到达数百万 上千万时, 这个也是不适用的
mysql> INSERT INTO tasks_bak SELECT * FROM tasks;
#数据量大的话,通过select from into outfile 命令来导出数据表数据，通过load data infile into 命令来导入数据表数据
mysql> CREATE TABLE tasks_bak LIKE tasks;
mysql> select * from table_name into outfile "C:\\file.csv" FIELDS TERMINATED BY ',' LINES TERMINATED BY ''
mysql> mysql --local_infile=1 -u username -p 
mysql> set global local_infile=1;
#windows下的换行是\r
mysql> LOAD DATA LOCAL INFILE 'C:\\file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r';

服务器端和客户端都需要打开 local_infile ，创建好待导入的表，正确设置字段分隔符和行分隔符，若出现 ERROR 1300 (HY000): Invalid utf8mb4 character ，利用python逐行读取文件,读取文件时编码方式为 utf8或utf16 ，并写入新的文件，创建新文件时编码方式为 utf8

删除数据

#使用 DELETE 语句来删除表的一行或者多行数据。
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下：
<表名>：指定要删除数据的表名。
ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
#注意：在不使用 WHERE 条件的时候，将删除所有数据。
#示例：
#根据单字段，删除表中该字段重复出现记录，保留最小主键ID
mysql> DELETE FROM Table
	   WHERE Parameter IN
	   (SELECT Parameter FROM Table
	   GROUP BY Parameter HAVING COUNT(Parameter) > 1)
	   AND ID NOT IN
	   (SELECT MIN(ID) FROM Table
	   GROUP BY Parameter HAVING COUNT(Parameter)>1)
mysql> ALTER TABLE <表名> DROP <字段名>； #删除字段
mysql> DROP TABLE table_name;  #删除数据表
mysql> drop database RUNOOB;  #删除数据库

数据表的去重:distinct vs. row_number() over()

#去重查询
#distinct只能放在所有字段的前面，且作用于后面的所有字段
mysql> SELECT DISTINCT owner FROM pet; 
#部分字段去重（同时显示其他可能重复的字段）
mysql> SELECT * FROM 
mysql> ( SELECT *,ROW_NUMBER() OVER(PARTITION BY lat,lng ORDER BY radius) AS rn FROM table_name ) 
mysql> AS temp_view WHERE temp_view.rn=1 AND (city LIKE '新北市' OR city LIKE '台中市' )

查阅资料，关系型运算符优先级高到低为：NOT > AND > OR 如果 where 后面有 OR 条件的话，则 OR 自动会把左右的查询条件分开。

查询与修改数据

mysql> SELECT what_to_select
	FROM which_table
	[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
	WHERE conditions_to_satisfy
	GROUP BY column_1
	HAVING group_conditions
	ORDER BY creditlimit DESC 
	LIMIT offset , count;
#例如，当字段重复时不添加（有条件插入）
mysql> INSERT INTO table(field1, field2, fieldn)
	   SELECT 'field1', 'field2', 'fieldn'  
	   WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)

语句中的 SELECT 和 FROM 语句是必须的，其他部分是可选的。what_to_select 指定查询的列名，用 , 分隔多个列名。或者用 * 指定所有列名。 which_table 指定所使用的的表。 WHERE 子句是可选的， conditions_to_satisfy 指定一个或多个限制条件。在使用 left jion 时， on 和 where 条件的区别如下： 1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。 2、 where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left jion 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

mysql> SELECT * FROM pet; #查询所有数据
#在SELECT语句中使用LIMIT子句来约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。
#LIMIT子句参数：offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。count指定要返回的最大行数。当使用带有一个参数的LIMIT子句时，此参数将用于确定从结果集的开头返回的最大行数。
mysql> SELECT * FROM table LIMIT offset , count;
#LIMIT子句经常与ORDER BY子句一起使用。首先，使用ORDER BY子句根据特定条件对结果集进行排序，然后使用LIMIT子句来查找最小或最大值。
mysql> SELECT customername FROM customers ORDER BY creditlimit DESC LIMIT 5;
#查询行数据
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
#多条件查询
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
#查询列数据
mysql> SELECT name, birth FROM pet;
#去重查询
mysql> SELECT DISTINCT owner FROM pet; #distinct只能放在所有字段的前面，且作用于后面的所有字段
#有序查询
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; #先按照species排序，在同一物种内按照birth排序

更新

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
#修改某一项数据
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

表中字段操作

#修改表名
ALTER  TABLE table_name RENAME TO new_table_name
#修改字段名
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
#允许字段为空
ALTER TABLE tablename ALTER COLUMN columnname 数据类型 NULL;
#修改字段数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>;
#删除字段
ALTER TABLE <表名> DROP <字段名>;
#在末尾添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件]; #添加多个字段时需要用括号括起来，用逗号分隔
#在开头添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
#在中间位置添加字段，只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
#例如：
mysql> ALTER TABLE student ADD age INT(4);
mysql> ALTER TABLE student ADD stuno INT(11) AFTER name;
#遇到错误ERROR 1064 (42000): You have an error in your SQL syntax;
#在表名、字段名上加反引号（位于键盘左上角）即可解决。

日期计算与正则表达式（关键字：not like binary）

#CURDATE() 函数返回当前的日期。
mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
#加入where子句，有条件计算
mysql> SELECT name, birth, death,
       TIMESTAMPDIFF(YEAR,birth,death) AS age
       FROM pet WHERE death IS NOT NULL ORDER BY age;

# 关于日期的计算函数有：YEAR(), MONTH(), DAYOFMONTH().MONTH() 
mysql> SELECT name, birth, MONTH(birth) FROM pet;
mysql> SELECT name, birth FROM pet
       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

#0和‘'不是NULL
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

模糊查询与模式匹配、正则表达式

SQL的模式匹配使用 _ 匹配任意单个字符，使用 % 匹配任意数量的字符（包括0个字符）,如果查询内容中包含通配符，可以使用 \ 转义符。在MySQL中，SQL 模式匹配默认是大小写不敏感的。下面有一些例子。在SQL模式匹配中，比较运算符不要使用 = 或者 <> ，而要使用 LIKE 或者 NOT LIKE 。默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

#在students表中，查找所有以字母 "t"开头的学生姓名，区分大小写.
mysql> SELECT name FROM students WHERE name LIKE BINARY 't%';
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
mysql> SELECT * FROM pet WHERE name LIKE '_____';

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()). The following list describes some characteristics of extended regular expressions:

. matches any single character.

A character class [...] matches any character within the brackets. For example, [abc] matches a, b, or c. To name a range of characters, use a dash. [a-z] matches any letter, whereas [0-9] matches any digit.

* matches zero or more instances of the thing preceding it. For example, x* matches any number of x characters, [0-9]* matches any number of digits, and .* matches any number of anything.

A regular expression pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

To anchor a pattern so that it must match the beginning or end of the value being tested, use ^ at the beginning or $ at the end of the pattern.

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
#To force a regular expression comparison to be case-sensitive, use a case-sensitive collation, 
#or use the BINARY keyword to make one of the strings a binary string, 
#or specify the c match-control character. 
#Each of these queries matches only lowercase b at the beginning of a name:
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

#美元符号$匹配行尾
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
#查找name中包含字符w的项
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
#寻找包含5个字符的name字段， 
#使用 ^ 和 $ 匹配name的开头和结尾, 中间有5个 . （.表示匹配任意单个字符）:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
#write the previous query using the {n} ( "repeat-n-times") operator:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

查询列数

mysql> SELECT COUNT(*) FROM pet;
#查询每一个owner拥有几个宠物
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

在这里插入图片描述

#多表查询
mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;

查看关于数据库、表的信息

mysql> SELECT DATABASE();
mysql> show databases;
mysql> SHOW TABLES;
mysql> DESCRIBE pet;
#注意：describe可以缩写为desc，与关键字降序一样。

批查询

#Run mysql in batch mode：
#Put the statements you want to run in a file, then tell mysql to read its input from the file:
shell> mysql < batch-file
#If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:
C:\> mysql -e "source batch-file"
#If you need to specify connection parameters on the command line, the command might look like this:
shell> mysql -h host -u user -p < batch-file
Enter password: ********
#If you want the script to continue even if some of the statements in it produce errors, 
#you should use the --force command-line option.
#可以对批执行的输出内容进行进一步处理
shell> mysql < batch-file | more
shell> mysql < batch-file > mysql.out
#You can also use scripts from the mysql prompt by using the source command or \. command:
mysql> source filename;
mysql> \. filename

索引

#有索引和没索引，在数据表较大时，查询速度相差十分明显
#查看数据表已有的索引
mysql> SHOW INDEX FROM table_name;
#创建主键索引
mysql> ALTER TABLE test ADD PRIMARY KEY (column_name);
#创建普通索引
mysql> CREATE INDEX index_name ON table_name (column_list)
#删除索引
mysql> DROP INDEX index_name ON talbe_name
mysql> ALTER TABLE table_name DROP INDEX index_name
mysql> ALTER TABLE table_name DROP PRIMARY KEY

with as 用法

指定别名时，AS关键字可以省略，省略后需要将表名和别名用空格隔开。
mysql> SELECT stu.name,stu.height FROM students AS stu; #为表指定别名
mysql> SELECT name AS stu_name, age AS stu_age FROM students; #为字段名指定别名

MySQL 8.0的CTE（使用CTE（公共表表达式）来构建复杂查询）
#公用表表达式（CTE）是一个命名的临时结果集，它存在于单个语句的范围内，以后可以在该语句中引用。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。
格式：#注意如果提供(column_list)，查询中的列数必须与column_list中的列数相同
WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;
例如：
mysql> WITH
    ->  cte1 AS (SELECT a, b FROM table1),
    ->  cte2 AS (SELECT c, d FROM table2)
    -> SELECT b, d FROM cte1 JOIN cte2
    -> WHERE cte1.a = cte2.c;
mysql> WITH cte (col1, col2) AS
    -> ( SELECT 1, 2
    ->   UNION ALL
    ->   SELECT 3, 4 )
    -> SELECT col1, col2 FROM cte;

Union 用法

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

视图view

1、在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。 2、视图是一种不存在的虚拟表: 类似表但是不是表。类似表: 视图有表结构；不是表: 没有数据, 视图的数据来源都是基表； 3、视图中的字段是来自一个或多个数据库中的真实的表中的字段。 4、视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。优势：

简化了操作，把经常使用的数据定义为视图，可以将复杂的SQL查询语句进行封装。

安全性，用户只能查询和修改能看到的数据。因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合

简化了操作，把经常使用的数据定义为视图，可以将复杂的SQL查询语句进行封装。

SQL CREATE VIEW 语法：
#创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

mysql> drop view v_student; #删除视图
mysql> SELECT * FROM view_name where condition; #查询视图

having子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

mysql> SELECT Customer,SUM(OrderPrice) FROM Orders
	-> GROUP BY Customer
	-> HAVING SUM(OrderPrice)<2000

Name	Description
AVG()	Return the average value of the argument
COUNT()	Return a count of the number of rows returned
COUNT(DISTINCT)	Return the count of a number of different values
MAX()	Return the maximum value
MIN()	Return the minimum value
STD()	Return the population standard deviation
SUM()	Return the sum
VARIANCE()	Return the population standard variance
GROUP_CONCAT()	Return a concatenated string
JSON_ARRAYAGG()	Return result set as a single JSON array
JSON_OBJECTAGG()	Return result set as a single JSON object

函数

参考链接1、链接2

导出数据

可以在cmd框中利用mysqldump导出符合条件的数据，命令如下:
mysqldump -uusername -p123456 database table --where="1=1 limit 100" > C:\Users\ymj\Desktop\vehicle.sql
参数：
-u, --user=name #User for login if not current user.
-p, --password[=name]  #Password to use when connecting to server.If password is not given it's solicited on the tty.
-h, --host=name #Connect to host.
-P, --port=num  #Port number to use for connection.
-w, --where='where_condition' #Dump only selected records. Quotes are mandatory.
-d, --no-data #No row information. (不导出数据，有时我们仅仅需要导出表结构，也就是建表语句就行了)
-t, --no-create-info #Don't write table creation info. (不导出建表语句) 
--ignore-table=name  # Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times,once for each table.  Each table must be specified with both database and table names,e.g.,  --ignore-table=database.table. (在导出数据库时，排除某个或者某几个表不导出)

#需要注意：目标文件不能是一个已经存在的文件，需要对目录的写权限。
SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTIONS]
[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，其常用的取值有：
FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t'。
FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR 和 #TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\'。
LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为‘' 。
#注意：FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES的前面。
#例如：
mysql> SELECT * FROM test.person INTO OUTFILE 'C://MySQL//person.txt';
mysql> SELECT * FROM test.person INTO OUTFILE 'C:/person.txt'
    FIELDS TERMINATED BY '\、' 
    OPTIONALLY ENCLOSED BY '\"' 
    LINES STARTING BY '\-' 
    TERMINATED BY '\r';
#其中：
#FIELDS TERMINATED BY '、'：表示字段之间用、分隔；
#ENCLOSED BY '\"'：表示每个字段都用双引号括起来；
#LINES STARTING BY '\-'：表示每行以-开头；
#TERMINATED BY '\r' 表示每行以回车换行符结尾，保证每一条记录占一行。
person.txt 文件内容如下：
-1、"Java"、12
-2、"MySQL"、13
-3、"C"、15
-4、"C++"、22
-5、"Python"、18

> 注意：导出时可能会出现下面的错误：
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
这是因为MySQL 限制了数据的导出路径。MySQL 导入导出文件只能在 secure-file-priv 变量的指定路径下的文件才可以导入导出，如果 secure_file_priv 值为 NULL，则为禁止导出； secure_file_priv 值为 空，则为任意导出。
有以下 2 种解决办法：
**1、首先使用show variables like '%secure%';语句查看 secure-file-priv 变量配置，按secure-file-priv指定的路径输出。**
mysql> show variables like '%secure%'
&emsp;&emsp;&emsp;*Variable_name: require_secure_transport    Value: OFF
&emsp;&emsp;&emsp;Variable_name: secure_auth                         Value: ON
&emsp;&emsp;&emsp;Variable_name: secure_file_priv                    Value: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\
&emsp;&emsp;&emsp;3 rows in set, 1 warning (0.04 sec)*
**2、可以在 MySQL 安装路径下的 my.ini 文件中添加secure_file_priv=设置路径语句，必须添加在[mysqld] 的下面，且路径中不能有汉字，然后重启服务即可。**

#将查询到的结果保存到文件中，不带字段名
mysql> select skind,country,city,count(*) as number from ipip_street group by city 
	   into outfile "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\city_number.csv" 
       FIELDS TERMINATED BY ',' LINES TERMINATED BY '';
#将查询到的结果保存到文件中，带字段名
mysql> select * into outfile 'D:\\MySqlOutput\\columns.csv' 
	   fields terminated by ',' lines terminated by '' 
	   from (select 'name','age' union select name,age from test) b;

详细内容请参考官方教程

数据迁移

数据从SQL Server迁移到 MySQL：

DBMigration的非商业使用（一次最多迁移2个表 亲测可用）免费。百度云连接，提取码：jhak
DB2DB的使用需要注册，个人版299元。
sqlcel作为Excel的插件，在迁移时提示语法错误，不知为何。。。

Python连接数据库

MySQLdb、Mysqlclient、PyMySQL、mysql.connector用法总结和比较
mysql.connector是MySQL的推荐驱动，纯python编写，参考官方教程
数据表内容有更新，必须使用到语句 mydb.commit()

import mysql.connector 
config = { 
      'user': 'root', 
      'password': '123456', 
      'host': 'localhost', 
      'database': 'TESTDB', 
      'charset': 'utf8', #可选
      "connection_timeout": 5, #可选
      "use_pure": True #可选，不一定支持
} 
cnx = mysql.connector.connect(**config)   
# 建立连接,等价于一下语句：
cnx = mysql.connector.connect( 
  host="localhost",       # 数据库主机地址 
  user="root",    # 数据库用户名 
  passwd="000000"，   # 数据库密码 
  database="TESTDB" 
) 
mycursor = cnx.cursor() 
sql = "insert into EMPLOYEE values(%s,%s,%s,%s)" 
val = ('Smith','Tom',15,'M',1500) 
mycursor.execute(sql, val)  
cnx.commit()    # 数据表内容有更新，必须使用到该语句 
cursor.execute("SELECT VERSION()") 
data, = cursor.fetchone()  #使用fetchone()获取数据，单条一定要加 "," 因为fetchone返回的是元组
print(mycursor.rowcount, "记录插入成功。")

如何含有特殊字符的字符串插入到MySQL数据库中（MySQL语句中特殊字符的转义）

#如果将含有特殊字符\ ' "等的字符串直接插入到MySQL数据库中，则可能会出现语法错误或者数据丢失问题。
#pymysql中有专门的转义方法，导入语法如下：
# v1.0.0及以上
from pymysql.converters import escape_string
# v0.10.1及以下
from pymysql import escape_string
# 打开数据库连接
db = pymysql.connect(host="127.0.0.1",user="dsr",password="password",database="db_name")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
sql = "select * from table_name limit 1;"
cursor.execute(sql)
result_list = cursor.fetchone()  #返回的是一个列表，相应的获取多条记录有有fetchall()方法
#使用时只需要先将特定的字符串进行转义，然后再将转义后的字符串插入到数据库中即可
es = escape_string(s)

问题答疑

1、使用 source xxx.sql 语句导入数据库时很出现：Failed to open file 'xxx.sql', error: 2

使用 source xxx.sql 语句导入数据库时很出现： Failed to open file 'xxx.sql', error: 2 解决方法： 导入的数据库的名字不要是中文，先改为英文名并且路径分隔符改为反斜杠（如D:/BaiduYunDownload/mysql/try.sql ）详情可参考文章

2、mysql用户密码遗忘

mysql用户密码遗忘问题的解决方法，可以参考MySQL8.0 Linux系统重置Root密码:smiley:

3、ERROR3948: Loading local data is disabled

ERROR3948: Loading local data is disabled - this must be enabled on both the client and server sides

step1 首先，检查一个全局系统变量 'local_infile' 的状态:

show global variables like 'local_infile';

如果得到如下显示 Value=OFF，则说明这是不可用的。 step2 要把这个值变为 ON, 在mysql客户端输入:

set global local_infile=1;

然后退出quit 退出mysql。再次进入时，便会出现 Value=ON step3 在进入数据库的命令行要加上 --local_infile=1 这个配置，具体如下：

mysql --local_infile=1 -u <username> -p

这个时候，就可以开开心心的运行 load data local 语句添加数据了。