MySQL基础必知必会
MySQL基础必知必会
什么是数据库？
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database
RDBMS 术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：
-
数据库: 数据库是一些关联表的集合。
-
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
-
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
-
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
-
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
-
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
-
外键：外键用于关联两个表。
-
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
-
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
-
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
-
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
-
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
启动及关闭 MySQL 服务器
1.Windows下
net start mysql （需用管理权权限启动cmd，否则会报发生系统错误 5。 拒绝访问）
注意 如果mysql server 名称不是默认的，则应该用命令 net start mysql5717 ，其中mysql5717是安装时mysql的名称。
关闭服务
mysqladmin -uroot shudown
如果有设置密码，则为 mysqladmin -uroot -p123456 shudown 123456为root密码
或
net stop mysql || net start mysql5717
2.Linux下
检查MySQL服务器是否启动：ps -ef | grep mysqld
命令行方式：
开启 ./mysqld_safe &
关闭 mysqladmin -uroot shutdown
rpm方式安装的
开启 service mysql start
关闭 service mysql stop
重启服务 service restart stop
## mysql.user表中的字段定义：collate utf_bin是 以二进制值比较，也就是区分大小写，collate是核对的意思
`authentication_string` text COLLATE utf8_bin
针对数据库的基本操作，非业务相关
use hello_spring;
SELECT * from user;
-- mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。
FLUSH PRIVILEGES;
#在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。
#password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
#在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
INSERT INTO user (host,user,authentication_string, select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost', 'guest',
PASSWORD('123456'), 'Y', 'Y', 'Y','(BLOB)','(BLOB)','(BLOB)');
#列出 MySQL 数据库管理系统的数据库列表。
SHOW DATABASES;
#显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库
SHOW TABLES;
#显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
SHOW COLUMNS FROM t_developer;
#显示数据表的详细索引信息，包括PRIMARY KEY（主键）
SHOW INDEX FROM t_developer;
#添加索引
ALTER TABLE t_developer ADD INDEX idx_name(name);
#该命令将输出Mysql数据库管理系统的性能及统计信息。
-- 注意可以用这个查看表的字符集编码格式，数据量，总的数据数等详细信息
SHOW TABLE STATUS from hello_spring LIKE '%%';
#加上 \G，查询结果按列打印 ,这个得在命令行里才能使用
SHOW TABLE STATUS from hello_spring LIKE 't_d%'\G;
MySQL 连接
注意：mysql 不区分大小写。
#连接mysql服务器命令
[root@host]# mysql -u root -p
Enter password:******
#如果直接写成下面这种，并不能直接登录，因为密码是要另外写的，-p后面接的是数据库的名字
mysql -u root -p 123456
mysql -u root -p mysql(数据库名)
#退出
exit
数据库命令
#创建数据库
CREATE DATABASE hello_spring_backup;
#删除数据库
DROP DATABASE hello_spring_backup;
#选择数据库
USE hello_spring_backup;
MySQL 数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
注意：CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。检索过程中是大小写不敏感的。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
MySQL 创建数据表
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
- ENGINE 设置存储引擎，CHARSET 设置编码。
建表demo：
CREATE TABLE IF NOT EXISTS t_developer (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
name VARCHAR(30) NOT NULL COMMENT '姓名',
work_level INT(6) NOT NULL COMMENT '等级',
position VARCHAR(30) DEFAULT NULL COMMENT '职位',
salary DECIMAL(13,2) DEFAULT '0' COMMENT '薪水',
status SMALLINT(2) DEFAULT NULL COMMENT '状态',
create_time datetime DEFAULT NOW() COMMENT '创建时间',
create_emp_id BIGINT(10) NOT NULL COMMENT '创建人',
PRIMARY KEY (id)
)ENGINE = INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT '开发员工表';
CREATE TABLE IF NOT EXISTS t_bonus (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
developer_id BIGINT(20) NOT NULL COMMENT '员工id',
bonus DECIMAL(13,2) DEFAULT '0' COMMENT '奖金',
performance SMALLINT(2) DEFAULT NULL COMMENT '绩效',
PRIMARY KEY (id)
)ENGINE = INNODB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT '开发员工奖金表';
数据表操作
注意： 在shell命令下使用mysql时，使用箭头标记 -> 不是 SQL 语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写 SQL 语句，SQL 语句的命令结束符为分号 ;
数据查询
- 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以使用 LIMIT 属性来设定返回的记录数。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
引申MySQL中使用LIMIT进行分页的方法：
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1) 。
#MySQL中使用LIMIT进行分页的方法，利用分页函数limit m,n
#其中currentPage是页码(当前页码)，pageSize是每页显示的条数。
select * from table limit (currentPage-1)*pageSize,pageSize;
-------------------------函数limit m,n-----------------------------
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
##为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
--如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n 等价于 LIMIT 0,n。
SELECT * FROM table LIMIT 5; //检索前 5 个记录行
#删除表
DROP TABLE t_developer_backup;
#MySQL插入数据，如果数据是字符型，必须使用单引号或者双引号，如："value"。
#-------------------------插入数据-----------------------------
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
-------------------------格式------------------
#插入数据时可以指定字符串形式的时间
INSERT INTO t_developer(name,work_level,position,salary,status,create_time,create_emp_id)VALUES('小甜甜',7,"shanghai",63521.02,
1,'2020-03-30 10:10:10', 690050);
#插入数据时也可以指定字符串形式的日期，时分秒mysql会自动补充0
INSERT INTO t_developer(name,work_level,position,salary,status,create_time,create_emp_id)VALUES('甜甜',7,"shanghai",63521.02,
1,'2020-03-30', 690060);
#插入数据时也可以不指定字符串形式的时间，因为在建表时默认时间不指定则为now()
INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES('甜甜',7,"shanghai",63521.02,
1, 690060);
SELECT * FROM t_developer;
SELECT DATE_FORMAT(b.create_time, "%Y-%m-%d %H:%i:%s") FROM t_developer b WHERE b.id=1000;
#DATE_FORMAT(date,format) 函数的fomat与java不同，下面这个语句是不能把时间转换成想要的字符串格式的时间的
SELECT DATE_FORMAT('2020-03-30 10:10:10','YYYY-MM-DD HH:MM:SS') FROM DUAL;
#要使用这种format格式才行
SELECT DATE_FORMAT('2020-03-30 10:10:10',"%Y-%m-%d %H:%i:%s") FROM DUAL;
#-------------------------插入数据-----------------------------
#-------------------------查询数据-----------------------------
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
-------------------------格式------------------
SELECT d.`name`,d.salary,d.work_level FROM t_developer d LIMIT 2;
#limit和offset用法,下面两种写法都表示取2,3,4三条条数据；OFFSET要配合limit使用才行，而limit可以单独使用
SELECT * FROM t_developer LIMIT 1,3;
SELECT * FROM t_developer LIMIT 3 OFFSET 1;
#LIKE子句 模糊查询
#like 匹配/模糊匹配，会与 % 和 _ 结合使用。 _ 单字母或者单个中文匹配。
SELECT * FROM t_developer d where d.`name` like 'z%';
SELECT * FROM t_developer d where d.`name` like '小甜_';
#MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
# UNION 会去重，UNION ALL则返回所有查询到的数据，因此UNION ALL 效率更高。
#UNION 联合结果集合 去重
SELECT d.`name` FROM t_developer d
UNION
SELECT dd.`name` FROM t_developer_backup dd ;
#全部集合不去重
SELECT d.`name` FROM t_developer d
UNION ALL
SELECT dd.`name` FROM t_developer_backup dd ;
#数据排序 默认情况下，它是按升序排列。
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
SELECT * FROM t_developer ORDER BY create_time;
SELECT * FROM t_developer ORDER BY create_time DESC;
# GROUP BY 语句
#GROUP BY 语句根据一个或多个列对结果集进行分组。
#在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
------------------------------------------
SELECT d.`name`,count(*) FROM t_developer d GROUP BY d.`name`;
SELECT d.`name`,d.salary,count(*) FROM t_developer d GROUP BY d.`name`;
#按名称分组，统计每个人总的薪水
SELECT d.`name`,SUM(d.salary),count(*) FROM t_developer d GROUP BY d.`name`;
--使用 WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
--也就是说，它会把我们每个人的总薪水都加起来算出总的统计或者平均统计，这要看分组中的函数是总的还是平均函数
/**
coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。**/
#按名称分组，统计每个人总的薪水和平均薪水
SELECT COALESCE(d.`name`,'总额'),SUM(d.salary),count(*),AVG(d.salary) FROM t_developer d GROUP BY d.`name` WITH ROLLUP;
#-------------------------查询数据-----------------------------
#-------------------------UPDATE更新数据-----------------------------
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
-------------------------格式------------------
UPDATE t_developer d SET d.`name`='MM',d.position='quanzhou' WHERE d.id=1002;
#-------------------------UPDATE更新数据-----------------------------
#-------------------------删除数据-----------------------------
DELETE FROM table_name [WHERE Clause]
#多表的删除：
delete 别名1，别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
truncate table 表名
--执行的速度上，drop>truncate>delete---
-------------------------格式------------------
#删除
DELETE FROM t_developer where id=1004;
#表名不能用别名去操作删除，会报错
DELETE FROM t_developer d where d.id=1004;
#多表删除
#通过备份数据删除
DELETE t_developer FROM t_developer ,t_developer_backup b WHERE b.id = t_developer.id;
#多表删除中两张表都匹配到的数据就都会删除
DELETE t_developer ,b FROM t_developer ,t_developer_backup b WHERE b.id = t_developer.id;
# 注意多表删除时要对两张表都要加where限定条件，没加条件限制的表将整表删除。
DELETE d,b FROM t_developer d,t_developer_backup b WHERE d.id=1008 and b.id=1000;
#整张表数据删除
TRUNCATE t_developer_backup1;
操作图：
MySQL JOIN 联表查询数据
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
--INNER JOIN (也可以省略 INNER 使用 JOIN，效果一样)
SELECT d.`name`,d.position,b.bonus,b.performance from t_developer d INNER JOIN t_bonus b ON d.id = b.developer_id;
-- 不建议只使用 JOIN来关联查询，要嘛全名INNER JOIN要嘛用,
SELECT d.`name`,d.position,b.bonus,b.performance from t_developer d JOIN t_bonus b WHERE d.id = b.developer_id;
SELECT d.`name`,d.position,b.bonus,b.performance from t_developer d ,t_bonus b WHERE d.id = b.developer_id;
-- LEFT JOIN 左连 会读取左边数据表的全部数据，即便右边表无对应数据。
SELECT d.`name`,d.position,b.bonus,b.performance from t_developer d LEFT JOIN t_bonus b ON d.id = b.developer_id;
--RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
SELECT d.`name`,d.position,b.bonus,b.performance from t_developer d RIGHT JOIN t_bonus b ON d.id = b.developer_id;
--多表关联 没错LEFT JOIN 都会有对应的 ON 关联字段把表关联起来
SELECT d.`name`,d.position,b.bonus,b.performance
from t_developer d
LEFT JOIN t_bonus b ON d.id = b.developer_id
INNER JOIN t_developer_backup1 db ON d.id=db.id;
联表查询包含关系：
MySQL NULL 值处理
我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。
MySQL 正则表达式
MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 | |
| m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
例子：
--查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据
SELECT name FROM t_developer WHERE name REGEXP '^[aeiou]|ne$';
MySQL 事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行，客户端的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句：
- BEGIN 或 START TRANSACTION 显式地开启一个事务；
- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier 把事务回滚到标记点；
- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
#即使在 AUTOCOMMIT=1 时，只要开启了begin事务，就不会被影响到，该回滚就回滚。
BEGIN;
INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES('守及1',7,"shanghai",10,
1, 690050);
INSERT INTO t_developer(name,work_level,position,salary,status,create_emp_id)VALUES('守集1',7,"shanghai",10,
1, 690050);
#COMMIT;
ROLLBACK;
--------------------------------------------------------
#禁止自动提交
SET AUTOCOMMIT=1;
#开启自动提交
SET AUTOCOMMIT=0;
表定义操作DDL
MySQL ALTER命令
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令 。
----------------------------DDL--------------------------
#查看表字段的定义
SHOW COLUMNS FROM t_developer_backup;
DESC t_developer_backup;
#删除表字段 create_time
ALTER TABLE t_developer_backup DROP create_time;
#最少DDL必须包含字段名和字段类型即可。
ALTER TABLE t_developer_backup ADD create_time1 datetime;
#推荐使用DDL类型，ADD添加字段
ALTER TABLE t_developer_backup ADD create_time1 datetime DEFAULT NOW() COMMENT '创建时间';
#可以定义添加字段的位置，使用 AFTER 字段名; 或 FIRST
ALTER TABLE t_developer_backup ADD modify_time datetime DEFAULT NOW() COMMENT '创建时间' AFTER status;
#FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
ALTER TABLE t_developer_backup ADD first_time datetime DEFAULT NOW() COMMENT '创建时间' FIRST;
#没有表示 NOT NULL 的都是表示默认可以为NULL的，而没有加 DEFAULT 1 或者 now()等的则表示没有默认值，或者说默认是NULL
ALTER TABLE t_developer_backup ADD change_time datetime NOT NULL DEFAULT NOW() COMMENT '创建时间';
#MODIFY可以对该字段修改，追加定义
ALTER TABLE t_developer_backup MODIFY create_time1 date DEFAULT NULL COMMENT '创建日期';
#AFTER 关键字也可用于 MODIFY 子句
ALTER TABLE t_developer_backup MODIFY first_time date DEFAULT NULL COMMENT '首次日期' AFTER modify_time;
ALTER TABLE t_developer_backup MODIFY first_time date DEFAULT NULL COMMENT '首次日期' FIRST;
#修改字段时，你可以指定是否包含值 或者是否设置默认值。
ALTER TABLE t_developer_backup MODIFY first_time datetime NOT NULL DEFAULT NOW() COMMENT '首次日期' FIRST;
#在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。
ALTER TABLE t_developer_backup CHANGE create_time1 create_time2 datetime NULL ;
#如果不修改字段名则紧跟着的是原字段名
ALTER TABLE t_developer_backup CHANGE create_time2 create_time2 datetime NULL ;
--注意,修改表字段时报错：Data truncated for column 'create_time1' at row 1
--这是因为当原来是自动为空且有数据时如果要修改字段为NOT NULL ,就会报这个错。
#修改字段默认值
ALTER TABLE t_developer_backup ALTER first_time SET DEFAULT NULL;
#使用MODIFY或者change来修改字段默认值 字段名和字段类型是不可以缺少的，因此如果只是修改默认值还是使用上面的
ALTER TABLE t_developer_backup MODIFY first_time datetime DEFAULT NOW() COMMENT '首次日期1';
#使用 ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE t_developer_backup ALTER first_time DROP DEFAULT;
#查看数据表类型可以使用 SHOW TABLE STATUS 语句,想修改说明TYPE都行。
SHOW TABLE STATUS;
#修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成 修改表的引擎
ALTER TABLE t_developer_backup ENGINE = MYISAM;
#编码格式
ALTER TABLE t_developer_backup CHARSET = utf16;
ALTER TABLE t_developer_backup COMMENT = '员工开发表备份';
ALTER TABLE t_developer_backup AUTO_INCREMENT = 1;
#修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
ALTER TABLE t_developer_backup RENAME TO t_developer_backup2;
------------------------------------------------
#删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;
#删除索引
ALTER TABLE t_developer_backup DROP KEY idxName;
#添加索引
ALTER TABLE t_developer_backup ADD INDEX idxName(name);
--修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;
MySQL 索引
索引可以大大提高MySQL的检索速度，就像汉语字典的目录页 。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
---创建索引格式---------
CREATE INDEX indexName ON mytable(username(length));
CREATE INDEX indexName ON t_developer_backup(first_time) ;
--组合索引
ALTER TABLE t_developer ADD INDEX idx_create_info(create_time,create_emp_id);
--添加索引，推荐用这种方式
ALTER table tableName ADD INDEX indexName(columnName)
ALTER TABLE t_developer ADD INDEX idx_salary(salary);
--创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
DROP INDEX [indexName] ON mytable;
#删除索引
DROP INDEX idx_salary ON t_developer;
#删除索引，推荐，总的来说推荐使用ALTER开头的命名操作表结构
ALTER TABLE t_developer DROP INDEX idx_salary;
#添加唯一索引，不推荐
CREATE UNIQUE INDEX idx_salary ON t_developer(salary);
#添加唯一索引，推荐
ALTER TABLE t_developer ADD UNIQUE idx_salary(salary);
#创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
#删除主键，注意如果主键是AUTO_INCREMENT则删除不了
ALTER TABLE t_developer DROP PRIMARY KEY;
#格式
SHOW INDEX FROM table_name;
#显示索引信息
SHOW INDEX FROM t_developer;
---------索引效果------------
CREATE TABLE `t_developer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(30) NOT NULL COMMENT '姓名',
`work_level` int(6) NOT NULL COMMENT '等级',
`position` varchar(30) DEFAULT NULL COMMENT '职位',
`salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水',
`status` smallint(2) DEFAULT NULL COMMENT '状态',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_emp_id` bigint(10) NOT NULL COMMENT '创建人',
PRIMARY KEY (`id`),
KEY `idxCreateInfo` (`create_time`,`create_emp_id`),
KEY `idx_create_info` (`create_time`,`create_emp_id`),
KEY `idx_salary` (`salary`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='开发员工表';
ALTER TABLE t_developer DROP PRIMARY KEY;
删除主键报错： Incorrect table definition; there can be only one auto column and it must be defined as a key
1.只能有一个自增列;
2.它必须被定义为键(当然，id一般都是作为主键使用的)
设为自增的字段必须是主键，而我用的是客户端，表是批量从SQLServer复制到MySQL的，导致最后MySQL表中主键丢失，设置id为主键并设为自增后，保存成功，问题解决。
MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
创建临时表就是在创建普通表时的前面加上TEMPORARY 关键字。
当我们退出或者切换出当前MySQL会话，换个新的窗口时（在旧窗口还存在），该临时表是不存在的，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。
#创建临时表
CREATE TEMPORARY TABLE t_developer_temporary (
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
name VARCHAR(30) NOT NULL COMMENT '姓名',
work_level INT(6) NOT NULL COMMENT '等级',
position VARCHAR(30) DEFAULT NULL COMMENT '职位',
salary DECIMAL(13,2) DEFAULT '0' COMMENT '薪水',
status SMALLINT(2) DEFAULT NULL COMMENT '状态',
create_time datetime DEFAULT NOW() COMMENT '创建时间',
create_emp_id BIGINT(10) NOT NULL COMMENT '创建人',
PRIMARY KEY (id)
)ENGINE = INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT '开发员工表';
#插入数据
INSERT INTO t_developer_temporary(name,work_level,position,salary,status,create_emp_id)VALUES('cc',7,"qz",11,
12, 10001);
#查询
SELECT * from t_developer_temporary;
#手动删除临时表，和普通版删除一样
DROP TABLE t_developer_temporary;
#用查询直接创建临时表的方式，这种也可以用于普通表
CREATE TEMPORARY TABLE t_developer_temporary AS
(
SELECT * FROM t_developer
LIMIT 0,10000
);
##备份表名
CREATE TABLE backup_tables.t_demo_20180322 LIKE t_demo;
INSERT INTO backup_tables.t_demo_20180322
SELECT * FROM t_demo WHERE order_id ='123123';
#该语句可以查看表的创建语句，这样我们就可以直观地看到表结构或者复制表创建语句
SHOW CREATE TABLE t_developer;
#输出
CREATE TABLE `t_developer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(30) NOT NULL COMMENT '姓名',
`work_level` int(6) NOT NULL COMMENT '等级',
`position` varchar(30) DEFAULT NULL COMMENT '职位',
`salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水',
`status` smallint(2) DEFAULT NULL COMMENT '状态',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_emp_id` bigint(10) NOT NULL COMMENT '创建人',
PRIMARY KEY (`id`),
KEY `idxCreateInfo` (`create_time`,`create_emp_id`),
KEY `idx_create_info` (`create_time`,`create_emp_id`),
FULLTEXT KEY `inx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='开发员工表';
获取服务器元数据
以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。
| 命令 | 描述 |
|---|---|
| SELECT VERSION( ) | 服务器版本信息 |
| SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
| SELECT USER( ) | 当前用户名 |
| SHOW STATUS | 服务器状态 |
| SHOW VARIABLES | 服务器配置变量 |
MySQL 序列使用
MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。
MySQL 处理重复数据
防止表中出现重复数据
你可以在 MySQL 数据表中设置指定的字段为
PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。
统计重复数据
一般用GROUP BY 统计重复数据，用HAVING子句设置重复数大于1。
过滤重复数据
在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
删除重复数据
#先用GROUP BY过滤语句插入一张新表，删除旧表，把新表重命名为旧表名。
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
SQL 注入
SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。
防止SQL注入，我们需要注意以下几个要点：
- 1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
- 2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
- 3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
- 4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
- 5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
- 6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
MySQL 导出数据
MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。
#导出普通数据txt格式的。自己指定文件名，前面的路径要通过secure_file_priv设置
SELECT * from t_developer INTO OUTFILE 'D:/book/text.txt';
#查看secure-file-priv参数的值
show variables like '%secure%';
#导出 CSV 格式
SELECT * FROM t_developer INTO OUTFILE 'D:/book/text1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
#也是CSV格式，值分割不一样，这种格式可以被许多程序使用。
SELECT * INTO OUTFILE 'D:/book/text2.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t_developer;
报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
答：出现这个错误是因为没有给数据库指定写出文件的路径或者写出的路径有问题。这是因为导出的secure_file_priv 位置不是我们系统设置的安全位置，因此我们要修改secure_file_priv对应的安全位置的值。我们进入到（window为例）MySQL数据路径寻找到my.ini配置文件。
位置： C:\ProgramData\MySQL\MySQL Server 5.7
修改指定位置
#secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
secure_file_priv = "D:/book"
重启mysql服务端即可。
注意路径保存和sql的路径位置要用斜杠，反斜杠有点问题。
SELECT ... INTO OUTFILE 语句有以下属性:
- LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
- SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
- 输出不能是一个已存在的文件。防止文件数据被篡改。
- 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
- 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。
mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
以下实例将数据表 runoob_tbl 导出到 /tmp 目录中
#如果不指定导出位置，则默认导出文件在该命令行窗口对应的文件夹位置
#在Windows上则是在cmd.exe对应的位置 C:\Windows\System32
#导出 SQL 格式的数据到指定文件
mysqldump -u root -p hello_spring t_developer > dump.txt
#导出 SQL 格式的数据到指定位置
mysqldump -u root -p hello_spring t_developer > C:/Users/Administrator/Desktop/t_developer_dump.txt
#导出整个数据库的数据
mysqldump -u root -p hello_spring > database_dump.txt
#备份所有数据库
mysqldump -u root -p --all-databases > alldatabase_dump.txt
mysqldump -u root -p hello_spring t_developer > dump_backup.txt
mysqldump -u root -p hello_spring > dump_backup_all.txt
#将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：这样即使mysql数据删除了也可以用该文件恢复，冷备
mysql -u root -p hello_spring < dump_backup_all.txt
#可以直接指定要导出数据的位置
mysqldump -u root -p --all-databases > C:/Users/Administrator/Desktop/database_dump.txt
#mysqldump -u root -p hello_spring t_developer >
#C:/Users/Administrator/Desktop/t_developer_dump.txt
#导出sql文件如下：
-- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: localhost Database: hello_spring
-- ------------------------------------------------------
-- Server version 5.7.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `t_developer`
--
DROP TABLE IF EXISTS `t_developer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_developer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(30) NOT NULL COMMENT '姓名',
`work_level` int(6) NOT NULL COMMENT '等级',
`position` varchar(30) DEFAULT NULL COMMENT '职位',
`salary` decimal(13,2) DEFAULT '0.00' COMMENT '薪水',
`status` smallint(2) DEFAULT NULL COMMENT '状态',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_emp_id` bigint(10) NOT NULL COMMENT '创建人',
PRIMARY KEY (`id`),
KEY `idxCreateInfo` (`create_time`,`create_emp_id`),
KEY `idx_create_info` (`create_time`,`create_emp_id`),
FULLTEXT KEY `inx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='开发员工表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t_developer`
--
LOCK TABLES `t_developer` WRITE;
/*!40000 ALTER TABLE `t_developer` DISABLE KEYS */;
INSERT INTO `t_developer` VALUES (1,'wayne',7,'shanghai',3000.00,1,'2020-03-22 20:43:25',690050),(2,'mike',7,'shanghai',4000.00,1,'2020-03-22 20:43:42',690050),(3,'mike',7,'shanghai',1000.00,1,'2020-03-22 20:43:48',690050),(4,'jhon',7,'shanghai',10000.00,1,'2020-03-22 20:44:09',690050),(5,'jhon',7,'shanghai',1005.00,1,'2020-03-22 20:44:22',690050),(6,'tingting',7,'shanghai',500.00,1,'2020-03-22 20:44:35',690050),(7,'tingting',7,'shanghai',6500.00,1,'2020-03-22 20:44:43',690050),(8,'tingting',7,'shanghai',101.00,1,'2020-03-22 20:45:13',690050),(9,'守及',7,'shanghai',102.00,1,'2020-03-22 23:01:16',690050),(10,'守集',7,'shanghai',104.00,1,'2020-03-22 23:01:17',690050);
/*!40000 ALTER TABLE `t_developer` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-03-29 17:33:32
MySQL 导入数据
#mysql 命令导入语法格式为：
mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)
#1、mysql命令导入，在命令行上执行，无需登录数据库终端
mysql -u root -p < C:/Users/Administrator/Desktop/database_dump.txt
#2、source 命令导入 source命令导入数据库需要先登录到数库终端。
#先执行登录
mysql -u root -p
password:
# 创建要导入数据的数据库
create database hello_spring_backup;
# 使用已创建的数据库
use hello_spring_backup;
# 设置编码
set names utf8;
# 导入备份数据库
source /home/abc/abc.sql
#Windows下执行，txt文件也是可以的
source C:/Users/Administrator/Desktop/database_dump.txt
#3、使用 LOAD DATA 导入数据，mysql客户端就可执行的sql语句。
#MySQL 中提供了LOAD DATA INFILE语句来插入数据，如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。
#可以指定LOAD DATA语句中的列值的分隔符和行尾标记，但是默认标记是定位符和换行符。
#执行导入数据sql
LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/t_developer_dump.txt' INTO TABLE t_developer;
#也可以指定要导入的数据的分割符类型，比如要导入csv文件数据入数据库
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY '\r\n';
#LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。
#如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a);
#4、使用 mysqlimport 导入数据
mysqlimport -u root -p --local C:/Users/Administrator/Desktop/t_developer_dump.txt
mysqlimport -u root -p --local --fields-terminated-by=":" \--lines-terminated-by="\r\n" mytbl dump.txt
mysqlimport的常用选项介绍
| 选项 | 功能 |
|---|---|
| -d or --delete | 新数据导入数据表中之前删除数据数据表中的所有信息 |
| -f or --force | 不管是否遇到错误，mysqlimport将强制继续插入数据 |
| -i or --ignore | mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。 |
| -l or -lock-tables | 数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。 |
| -r or -replace | 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。 |
| --fields-enclosed- by= char | 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。 |
| --fields-terminated- by=char | 指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab） |
| --lines-terminated- by=str | 此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。 |
MySQL 函数
-----------------字符串-----------------------
SELECT ASCII(name) AS first_name FROM t_developer;
#################sql函数#########################
#合并多个字符串
SELECT CONCAT("this",'is','my','destiny','!') as goal;
SELECT CONCAT(d.`name`,d.create_time) from t_developer d;
#合并多个字符串，并添加分隔符
SELECT CONCAT_WS(' ',"this",'is','my','destiny','!') as goal;
#返回字符串 c 在列表值中的位置：
SELECT FIELD("c", "a", "b", "c", "d", "e");
#函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。
SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
#字符串 RUNOOB 转换为小写：
SELECT LOWER('ALAN') -- alan
#将字符串 爱到发烧 重复三次：
SELECT REPEAT('爱到发烧',3)；
#将字符串 xyz 的顺序反过来
SELECT REVERSE('xyz');
#返回 10 个空格：
SELECT SPACE(10);
#将字符串 runoob 转换为大写：
SELECT UPPER('china');
-----------------数值-----------------------
#返回 x 的绝对值
SELECT ABS(-10); -- 10
#返回 x 的 y 次方
SELECT POW(3,3) -- 27
SELECT POWER(3,3) -- 27
#四舍五入为整数
SELECT ROUND(3.1415); -- 3
SELECT ROUND(3.5415); -- 4
-----------------日期函数-----------------------
#计算起始日期 d 加上 n 天的日期
SELECT ADDDATE("2017-06-15", 20); -- 2017-07-05
SELECT ADDDATE("2017-06-15", INTERVAL 20 DAY); -- 2017-07-05
SELECT ADDDATE("2017-02-28", INTERVAL 10 DAY); -- 2017-03-10
#如果日期时间是错误的，则会返回null
SELECT ADDDATE("2017-02-30", INTERVAL 10 DAY); -- null
#如果传入的间隔时间是小数，则会四舍五入
SELECT ADDDATE("2017-02-28", 10.5); -- 2017-03-11
SELECT ADDDATE("2017-02-28", 10.4); -- 2017-03-10
# 计算起始日期 d 加上一个时间段后的日期 可以是正负值,可以操作从年到秒
#DATE_ADD(d，INTERVAL expr type)
# (默认是天)
SELECT ADDDATE('2011-11-11 11:11:11',1);
#(TYPE的取值与上面那个列出来的函数类似)
SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE); -- 2011-11-11 11:16:11
SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 YEAR); -- 2016-11-11 11:11:11
SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 SECOND); -- 2011-11-11 11:11:16
#时间 t 加上 n 秒的时间
SELECT ADDTIME("2021-11-11 11:11:11", 5); -- 2021-11-11 11:11:16
#返回当前日期
SELECT CURDATE(); -- 2016-03-29
SELECT CURRENT_DATE();
#返回当前时间 注意是不包含日期的
SELECT CURRENT_TIME(); -- 21:52:59
#返回当前日期和时间
SELECT CURRENT_TIMESTAMP(); -- 2016-03-29 21:53:52
#从日期或日期时间表达式中提取日期值
SELECT DATE("2017-06-15");
#计算日期 d1->d2 之间相隔的天数,相当于是前面减去后面
SELECT DATEDIFF('2017-06-15','2017-06-29'); -- -14
#按表达式 f的要求显示日期 d
#DATE_FORMAT(d,f)
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -- 2011-11-11 11:11:11 AM
#函数从日期减去指定的时间间隔
SELECT DATE_SUB('2011-11-11 11:11:11', INTERVAL 5 SECOND); -- 2011-11-11 11:11:06
SELECT DATE_SUB('2011-11-11 11:11:11', INTERVAL 5 DAY); -- 2011-11-06 11:11:11
#返回给给定日期的那一月份的最后一天
SELECT LAST_DAY("2017-06-20"); -- 2017-06-30
#返回当前日期和时间
SELECT NOW();
SELECT SYSDATE();
-----------------高级函数-----------------------
#返回 x 的二进制编码
SELECT BIN(20); -- 10100
SELECT * from t_developer;
/**
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END
*/
#CASE 表示函数开始，END 表示函数结束。
SELECT
(CASE WHEN d.salary =10.00
THEN 'low'
WHEN d.salary = 100.00
THEN 'mid'
WHEN d.salary =1000.00
THEN 'good'
when d.salary = 10000.00
THEN 'excellent'
END)
as
title,`name`
FROM t_developer d;
#返回服务器的连接数
SELECT CONNECTION_ID();
#返回当前用户
SELECT CURRENT_USER(); -- root@localhost
#返回当前数据库名
SELECT DATABASE(); -- hello_spring
#如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
#if 表达式 IF(expr,v1,v2)
SELECT IF(d.salary >= 1000,'good','bad') reputation,d.`name`
FROM t_developer d;
#如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
# IFNULL(v1,v2) 即给字段设置默认值
SELECT IFNULL('ha','Hello Word');
#判断表达式是否为 NULL
SELECT ISNULL(NULL); -- 1
SELECT ISNULL('1'); -- 0
#返回最近生成的 AUTO_INCREMENT 值 ,我想知道这个是针对表还是整个数据库的AUTO_INCREMENT？
SELECT LAST_INSERT_ID();
#在两值之间 >=min&&<=max
select d.salary FROM t_developer d where d.salary between 100 and 1000;
MySQL 字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; |
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 | 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; |
| FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 | 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e"); |
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 | 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e"); |
| FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 |
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runnob"); -- 输出：runoob.com |
| LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 | 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2 |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob |
| LEFT(s,n) | 返回字符串 s 的前 n 个字符 | 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob |
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc |
| LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
| MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2 |
| REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc |
| REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba |
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob |
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx |
| RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB |
| SPACE(n) | 返回 n 个空格 | 返回 10 个空格：SELECT SPACE(10); |
| STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 | 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0 |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB |
MySQL 数字函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) | 返回 x 的绝对值 | 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1 |
| ACOS(x) | 求 x 的反余弦值(参数是弧度) | SELECT ACOS(0.25); |
| ASIN(x) | 求反正弦值(参数是弧度) | SELECT ASIN(0.25); |
| ATAN(x) | 求反正切值(参数是弧度) | SELECT ATAN(2.5); |
| ATAN2(n, m) | 求反正切值(参数是弧度) | SELECT ATAN2(-0.8, 2); |
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 |
| CEILING(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 |
| COS(x) | 求余弦值(参数是弧度) | SELECT COS(2); |
| COT(x) | 求余切值(参数是弧度) | SELECT COT(6); |
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| DEGREES(x) | 将弧度转换为角度 | SELECT DEGREES(3.1415926535898) -- 180 |
| n DIV m | 整除，n 为被除数，m 为除数 | 计算 10 除于 5：SELECT 10 DIV 5; -- 2 |
| EXP(x) | 返回 e 的 x 次方 | 计算 e 的三次方：SELECT EXP(3) -- 20.085536923188 |
| FLOOR(x) | 返回小于或等于 x 的最大整数 | 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1 |
| GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 | 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob |
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 | 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple |
| LN | 返回数字的自然对数 | 返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453 |
| LOG(x) | 返回自然对数(以 e 为底的对数) | SELECT LOG(20.085536923188) -- 3 |
| LOG10(x) | 返回以 10 为底的对数 | SELECT LOG10(100) -- 2 |
| LOG2(x) | 返回以 2 为底的对数 | 返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156 |
| MAX(expression) | 返回字段 expression 中的最大值 | 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | 返回字段 expression 中的最小值 | 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS LargestPrice FROM Products; |
| MOD(x,y) | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数：SELECT MOD(5,2) -- 1 |
| PI() | 返回圆周率(3.141593） | SELECT PI() --3.141593 |
| POW(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POW(2,3) -- 8 |
| POWER(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POWER(2,3) -- 8 |
| RADIANS(x) | 将角度转换为弧度 | 180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898 |
| RAND() | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 |
| ROUND(x) | 返回离 x 最近的整数 | SELECT ROUND(1.23456) --1 |
| SIGN(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | SELECT SIGN(-10) -- (-1) |
| SIN(x) | 求正弦值(参数是弧度) | SELECT SIN(RADIANS(30)) -- 0.5 |
| SQRT(x) | 返回x的平方根 | 25 的平方根：SELECT SQRT(25) -- 5 |
| SUM(expression) | 返回指定字段的总和 | 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TAN(x) | 求正切值(参数是弧度) | SELECT TAN(1.75); -- -5.52037992250933 |
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
MySQL 日期函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 | SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25 |
| ADDTIME(t,n) | 时间 t 加上 n 秒的时间 | SELECT ADDTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:16 (秒) |
| CURDATE() | 返回当前日期 | SELECT CURDATE(); -> 2018-09-19 |
| CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE(); -> 2018-09-19 |
| CURRENT_TIME | 返回当前时间 | SELECT CURRENT_TIME(); -> 19:59:02 |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 | SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43 |
| CURTIME() | 返回当前时间 | SELECT CURTIME(); -> 19:59:02 |
| DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE("2017-06-15"); -> 2017-06-15 |
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32 |
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期 | SELECT ADDDATE('2011-11-11 11:11:11',1) -> 2011-11-12 11:11:11 (默认是天) SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE) -> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似) |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM |
| DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 | Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders |
| DAY(d) | 返回日期值 d 的日期部分 | SELECT DAY("2017-06-15"); -> 15 |
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday | SELECT DAYNAME('2011-11-11 11:11:11') ->Friday |
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 | SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11 |
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 | SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6 |
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315 |
| EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH | SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11 |
| FROM_DAYS(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 | SELECT FROM_DAYS(1111) -> 0003-01-16 |
| HOUR(t) | 返回 t 中的小时值 | SELECT HOUR('1:2:3') -> 1 |
| LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY("2017-06-20"); -> 2017-06-30 |
| LOCALTIME() | 返回当前日期和时间 | SELECT LOCALTIME() -> 2018-09-19 20:57:43 |
| LOCALTIMESTAMP() | 返回当前日期和时间 | SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43 |
| MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 | SELECT MAKEDATE(2017, 3); -> 2017-01-03 |
| MAKETIME(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 | SELECT MAKETIME(11, 35, 4); -> 11:35:04 |
| MICROSECOND(date) | 返回日期参数所对应的微秒数 | SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23 |
| MINUTE(t) | 返回 t 中的分钟值 | SELECT MINUTE('1:2:3') -> 2 |
| MONTHNAME(d) | 返回日期当中的月份名称，如 November | SELECT MONTHNAME('2011-11-11 11:11:11') -> November |
| MONTH(d) | 返回日期d中的月份值，1 到 12 | SELECT MONTH('2011-11-11 11:11:11') ->11 |
| NOW() | 返回当前日期和时间 | SELECT NOW() -> 2018-09-19 20:57:43 |
| PERIOD_ADD(period, number) | 为 年-月 组合日期添加一个时段 | SELECT PERIOD_ADD(201703, 5); -> 201708 |
| PERIOD_DIFF(period1, period2) | 返回两个时段之间的月份差值 | SELECT PERIOD_DIFF(201710, 201703); -> 7 |
| QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 | SELECT QUARTER('2011-11-11 11:11:11') -> 4 |
| SECOND(t) | 返回 t 中的秒钟值 | SELECT SECOND('1:2:3') -> 3 |
| SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 | SELECT SEC_TO_TIME(4320) -> 01:12:00 |
| STR_TO_DATE(string, format_mask) | 将字符串转变为日期 | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10 |
| SUBDATE(d,n) | 日期 d 减去 n 天后的日期 | SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天) |
| SUBTIME(t,n) | 时间 t 减去 n 秒的时间 | SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒) |
| SYSDATE() | 返回当前日期和时间 | SELECT SYSDATE() -> 2018-09-19 20:57:43 |
| TIME(expression) | 提取传入表达式的时间部分 | SELECT TIME("19:30:10"); -> 19:30:10 |
| TIME_FORMAT(t,f) | 按表达式 f 的要求显示时间 t | SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM |
| TIME_TO_SEC(t) | 将时间 t 转换为秒 | SELECT TIME_TO_SEC('1:12:00') -> 4320 |
| TIMEDIFF(time1, time2) | 计算时间差值 | SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01 |
| TIMESTAMP(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 | SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11 |
| TO_DAYS(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 | SELECT TO_DAYS('0001-01-01 01:01:01') -> 366 |
| WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEK('2011-11-11 11:11:11') -> 45 |
| WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 | SELECT WEEKDAY("2017-06-15"); -> 3 |
| WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45 |
| YEAR(d) | 返回年份 | SELECT YEAR("2017-06-15"); -> 2017 |
| YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 | SELECT YEARWEEK("2017-06-15"); -> 201724 |
MySQL 高级函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) | 返回 x 的二进制编码 | 15 的 2 进制编码:SELECT BIN(15); -- 1111 |
| BINARY(s) | 将字符串 s 转换为二进制字符串 | SELECT BINARY "RUNOOB"; -> RUNOOB |
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END |
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0 |
| CAST(x AS type) | 转换数据类型 | 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29 |
| COALESCE(expr1, expr2, ...., expr_n) | 返回参数中的第一个非空表达式（从左向右） | SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com |
| CONNECTION_ID() | 返回服务器的连接数 | SELECT CONNECTION_ID(); -> 4292835 |
| CONV(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 | SELECT CONV(15, 10, 2); -> 1111 |
| CONVERT(s USING cs) | 函数将字符串 s 的字符集变成 cs | SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk |
| CURRENT_USER() | 返回当前用户 | SELECT CURRENT_USER(); -> guest@% |
| DATABASE() | 返回当前数据库名 | SELECT DATABASE(); -> runoob |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,'正确','错误') ->正确 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,'Hello Word') ->Hello Word |
| ISNULL(expression) | 判断表达式是否为 NULL | SELECT ISNULL(NULL); ->1 |
| LAST_INSERT_ID() | 返回最近生成的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID(); ->6 |
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 | SELECT NULLIF(25, 25); -> |
| SESSION_USER() | 返回当前用户 | SELECT SESSION_USER(); -> guest@% |
| SYSTEM_USER() | 返回当前用户 | SELECT SYSTEM_USER(); -> guest@% |
| USER() | 返回当前用户 | SELECT USER(); -> guest@% |
| VERSION() | 返回数据库的版本号 | SELECT VERSION() -> 5.6.34 |
MySQL 运算符
| 符号 | 描述 | 备注 |
|---|---|---|
| = | 等于 | |
| <>, != | 不等于 | |
| > | 大于 | |
| < | 小于 | |
| <= | 小于等于 | |
| >= | 大于等于 | |
| BETWEEN | 在两值之间 | >=min&&<=max |
| NOT BETWEEN | 不在两值之间 | |
| IN | 在集合中 | |
| NOT IN | 不在集合中 | |
| <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| LIKE | 模糊匹配 | |
| REGEXP 或 RLIKE | 正则式匹配 | |
| IS NULL | 为空 | |
| IS NOT NULL | 不为空 |
疑问：
Q:CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
A:经过验证，检索过程中是大小写不敏感的，mysql会把无论是大写或者小写的值匹配的都检索出来。
Q: SELECT LAST_INSERT_ID();返回最近生成的 AUTO_INCREMENT 值 ,我想知道这个是针对表还是整个数据库的AUTO_INCREMENT？
扩展阅读
postgresql和mysql哪个好？
postgresql和mysql都是免费且功能强大的开源数据库，很多用户面对这两个库都会有一个问题，那就是哪一个才是最好的开源数据库，MySQL还是PostgreSQL呢？该选择哪一个开源数据库呢？
一.PostgreSQL相对于MySQL的优势
1、在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
2、存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
3、对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
4、postgresql主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
5、postgresql的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
6、MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而postgresql不存在。
二、MySQL相对于PostgreSQL的优势：
1、innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；
2、MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；
3、MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；
4、MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。
5、MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。
三、结论
总的来说，开源数据库都还不是很完善，和这两者相比，商业数据库oracle无论在架构还是功能方面都要完善很多。
postgresql和mysql相比，postgresql更加适合严格的企业应用场景（比如金融、电信、ERP、CRM），而MySQL则是更加适合业务逻辑相对简单、对数据可靠性要求比较低的互联网场景（比如google、facebook、alibaba）。
Mysql登录
RDBMS(Relational Database Management System：关系数据库管理系统 。
安装地址：C:\Program Files\MySQL\MySQL Server 5.7
登入mysql服务器
1、打开cmd输入mysql –u root –p
2、输入root的密码
