首页  思维导图  详情

mysql入门（文件大，打开慢）

2023-09-02 12:21:29   0  举报





AI智能生成

mysql入门，总结全面

工作总结

毕业升学

读书笔记

考研考证

产品设计

作者其他创作

大纲/内容

1、数据库概述

1.1数据库理论基础

数据存储方式演进

数据库范式

数据库在开发中作用

1.2数据库技术构成

数据库系统

数据库系统由数据库、数据库管理系统、应用开发工具构成

SQL

DDL

定义数据库、表、视图、索引和触发器等
create、alter、drop

DML

插入、查询、更新和删除数据
insert、select、update、delete

DCL

控制用户访问权限
grant、revoke

数据库访问技术

C# ADO.NET

java JDBC

ODBC

1.3mysql基础

常见数据库

甲骨文的Oracle

开源Mysql

IBM的DB2

微软的Access和SQL Server

开源的PostgreSQL

mysql优势

开源

跨平台性

价格便宜

功能强大方便

1.4mysql配置文件

服务器端各个参数

basedir=“”
表示Mysql安装路径

datadir=""
表示mysql数据文件存储位置

default-character-set=latin1
服务端默认字符集

port=3306
表示mysql数据库的端口，默认为3306

default-storage-engine=INNODB
# 参数表示默认的村塾引擎。存储引擎表示数据的存储方式

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# 表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格成图

max_connections=100
# 参数标识允许同时访问MySql服务的最大连接数，其中一个将保留，作为管理员登录。如果希望改变最大连接数，可以在此处进行修改

query_cache_size=0
# 参数标识查询时

table_cache=256
表示所有进程打开表的总数

tmp_table_size=9M
表示内存中临时表的最大值

thread_cache_size=8
表示保留客户端线程的混村

myisam_max_sort_file_size=100G
表示mysql重建索引时允许的最大临时文件的大小，默认为100G

myisam_sort_buffer_size=18M
表示参数重建索引的缓存大小

key_buffer_size
表示关键词缓存大小，该缓冲区一般来缓冲MyISAM表的索引块

read_buffer_size=64K
表示将排序好的数据存入该缓存中

sort_buffer_size=256K
表示用于排序的缓存的大小

客户端各个参数

default-character-set=latin1
服务端默认字符集

port=3306

password=your password
表示用户的登录密码，用户可以将密码存入该文件中，登陆时就可以不用输入密码

InnoDB存储引擎参数

innodb_additional_mem_pool_size=2M
表示附加的内存池，用来存储InnoDB表的内容

innodb_flush_log_at_trx_commit=1
设置为1，表示会在每次提交后将事务日志写到磁盘上

innodb_log_buffer_size=1M
表示用来存储日志数据的缓冲区的大小

innodb_buffer_pool_size=18M
表示缓存的大小，InnoDB使用一个缓冲池来保存索引和原始数据

innodb_log_file_size=10M
表示日志文件的大小

innodb_thread_concurrency=8
表示在InnoDB存储引擎允许的线程最大数

2、数据库基本操作

mysql数据类型

整数类型

TINYINT 字节数: 1 无符号的取值范围: 0~255 有符号范围：-128~127

SMALLINT 字节数: 2 无符号的取值范围: 0~65535 有符号范围：-32768~32767

MEDIUMINT 字节数: 3 无符号的取值范围: 0~16777125 有符号范围：-8388608~8388607

INT 字节数: 4 无符号的取值范围: 0~4294967295 有符号范围：-2147483648~2147483647

INTEGER 字节数: 4 无符号的取值范围: 0~4294967295 有符号范围：-2147483648~2147483647

BIGINT 字节数: 8 无符号的取值范围: 0~2的64次方-1 有符号范围：-2的63次方~2的63次方-1

浮点数类型和定点数类型

FLOAT 字节数 4

DOUBLE 字节数 8

DECIMAL(M,D) 字节数M+2 取值范围同DOUBLE

日期与时间类型

YEAR 字节 1 范围1901-2155 超过为0000

以YYYY的形式显示YEAR类型的值。输入格式‘YYYY’或 YYYY

DATE 字节 4

YYYY-MM-DD

YYYYMMDD

YYYY/MM/DD

YYYY@MM@DD

YY.MM.DD

CURRENT_DATE NOW()

TIME 字节 3

D HH:MM:SS D代表天数保存时小时的值等于D*24+HH

HHMMSS 保存时转化为HH:MM:SS

CURRENT_TIME NOW() 系统时间

DATETIME 字节 8

YYYY-MM-DD HH:MM:SS

YYYYMMDDHHMMSS

YYMMDDHHMMSS

YYYY-MM-DD HH:MM:SS

NOW()

TIMESTAMP 字节 4

与DATETIME类型不同：
1、使用CURRENT_TIMESTAMP来输入当前日志与时间
2、输入NULL时，系统会输入系统当前日期与时间
3、无任何输入时，系统会输入系统当前日期与时间

字符串类型

CHAR VARCHAR

字符串类型（M)

CHAR的字节长度是M ，VARCHAR的字节长度是M+1

CHAR会执行trim()功能， VARCHAR不会

CHAR长度范围0~255 VARCHAR长度范围0~65535

TEXT

TINYTEXT

存储空间值的长度+2字节

0~225字节

TEXT

存储空间值的长度+2字节

0~65535字节

MEDIUMTEXT

存储空间值的长度+3字节

0~167772150字节

LONGTEXT

存储空间值的长度+4字节

0~4294967295字节

ENUM

属性名 ENUM('value1 ','value 2',....,'value n')

没加上NOT NULL 允许插入NULL，而且NULL为默认值

加上了NOT NULL,允许插入NULL，默认取第一个值

SET

属性名 SET('value1 ','value 2',....,'value n')

SET类型的值可以取一个元素或者多个元素，取多个元素时，不同元素之间用逗号隔开，并且会按照定义的顺序排列

二进制类型

BINARY(M)

允许长度为M的二进制字符串

不足位补0

VARBINARY(M)

允许为0~M的变长二进制字符串，字节数为值的长度加1

BIT(M)

M位二进制数据，M最大值为64

TINYBLOB

可变长二进制数据，最多256个字节

BLOB

可变长二进制数据，最多2的16次方-1个字节

MEDIUMBLOB

可变长二进制数据，最多2的24次方-1个字节

LONGBLOB

可变长二进制数据，最多2的32次方-1个字节

如何选择数据类型

整数和浮点数

区别在于能否表达小数。

浮点数和定点数（decimal）

浮点数四舍五入系统不会报警

定点数四舍五入系统会出现警告

CHAR VARCAHR

CHAR长度固定，VARCHAR长度是范围内可变，所以VARCHAR类型占用的空间比CHAR类型小

VARCHAR类型灵活

CHAR处理速度更快

时间和日期类型

YEAR只记录年份

TIME只表示时间

DATE只表示日期

DATETIME范围比TIMESTAMP大

TIMESTAMP是根据时区来显示的

ENUM SET

ENUM最多65535个成员，SET最多64个成员

ENUM只能选一个，SET可以选多个

TEXT和BLOB

TEXT只能存储字符数据

BLOB可以存储二进制数据

纯文本选择TEXT

图片等二进制选择BLOB

常见问题

1、mysql中什么数据类型能够存储路径

MySQL中，CHAR，VARCHAR和TEXT等字符串类型都可以存储路径，
但是如果路径中使用"\"符号时，这个符号会被过滤。
解决办法是，路径中用"/"或者"\\"来代替"\"，
这样MySQL就不会自动过滤路径的分隔符号，可以完整的表示路径

2、MySQL中如何使用布尔类型

在SQL标准中，存在BOOL和BOOLEAN类型，
mysql为了支持SQL标准，也是定义了BOOL和BOOLEAN类型
但是，BOOL和BOOLEAN类型最后转化成的是TINYINT(1),
在MySQL中，布尔类型等价于TINYINT(1)，
所以创建表时定义BOOL和BOOLEAN，真实定义是TINYINT(1)

3、MySQL中如何存储JPG图片和MP3音乐

一般存储图片和音频的路径。
实在需要，选择BLOB类型存储

操作数据库

查看数据库

SHOW DATABASE;

创建数据库

CREATE DATABASE 数据库名;

删除数据库

DROP DATABASE 数据库名;

数据库存储引擎

查看支持的存储引擎

SHOW ENGINES;

参数含义

Engine

存储引擎名称

Support

是否支持

Comment

Transactions

是否支持事务

是否分布式交易处理的XA规范

Savepoints

是否支持保存点，以便事务回滚到保存点

SHOW VARIABLES LIKE 'have%';

参数含义

variable_name

存储引擎名称

value

支持情况

SHOW VARIABLES LIKE 'storage_engine';

InnoDB存储引擎

支持自动增长列AUTO_INCREMENT

支持外键 FOREIGN KEY

创建的表存储在.frm文件中，
数据和索引存储在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中

优点：提供良好的事务管理、奔溃修复和并发控制

缺点：读写效率稍差，占用数据空间相对比较大

MyISAM存储引擎

MyISAM存储引擎的表存储成3个文件。
文件的名称与表名相同。扩展名包含frm、MYD和MYI

frm为扩展名的文件【存储表结构】

MYD为扩展名的文件【存储数据】 MYData

MYI为扩展名的文件【存储索引】 MYIndex

基于MyISAM存储引擎的表，支持三种不同的存储格式。
包含静态型、动态型和压缩型。

静态型为MyISAM存储引擎的默认存储格式，其字段是固定长度的

动态型包含变成字段，记录的长度不是固定的

压缩型需要使用myisampack工具创建，占用的磁盘空间较小

优点：占用空间小、处理速度快

缺点：不支持事务的完整性和并发性

MEMORY存储引擎

使用存储在内存中的内容来创建表，而且所有数据也放在内存中。
每个基于MEMORY存储引擎的表实际对应一个磁盘文件。
该文件的文件名与表名相同，类型为frm类型。
该文件中只存储表的结构。
而其数据文件存储在内存中，有利于对数据快速的处理，提高整个表的处理效率。

使用哈希（HASH）索引。速度要比B型树（BTREE）索引快。

表的大小是受限制的。表的大小主要取决于max_rows 和 max_heap_table_size

max_rows可以在创建表时指定

max_heap_table_size的大小默认为16MB

优点：处理速度非常快

缺点：数据易丢失，生命周期短

存储引擎对比

创建修改删除表

创建表

CREATE TABLE 表名(
属性名数据类型 [完整性约束条件]，
属性名数据类型 [完整性约束条件]，
...
属性名数据类型 [完整性约束条件]
);

完整性约束条件

PRIMARY KEY

标识该属性为该表的主键，可以唯一的标识对应的元组。

FOREIGN KEY

标识该属性为该表的外键，是与之联系的某表的主键。

NOT NULL

标识该属性的值不能为空

UNIQUE

标识该属性的值是唯一的

AUTO_INCREMENT

标识该属性的值自动增加，这是mysql的SQL语句的特色

DEFAULT

为该属性设置默认值

设置表的主键

单字段：属性名数据类型 PRIMARY KEY

多字段： PRIMARY KEY(属性名1，属性名2， ... ，属性名n)

设置表的外键

CONSTRAINT 外键别名 FOREIGN KEY(属性1.1, 属性1.2，...，属性1.n)
REFERENCES 表名(属性2.1，属性2.2，...，属性2.n)

字表的外键关联的必须是父表的主键，而且数据类型必须是一致的。

设置表的非空约束

属性名数据类型 NOT NULL

设置表的唯一性约束

属性名数据类型 UNIQUE

设置表的属性自动增加

属性名数据类型 AUTO_INCREMENT

设置表的属性的默认值

属性名数据类型 DEFAULT 默认值

查看表

DESCRIBE 表名;

查看表的基本定义，字段名、字段数据类型、是否为主键和默认值等

SHOW CREATE TABLE 表名;

查看表的详细定义，查看表的字段名，字段的数据类型，完整性约束条件等，
还可以查看默认的存储引擎和字符编码。

修改表

ALTER TABLE 旧表名 RENAME [TO] 新表名

修改字段的数据类型

ALTER TABLE 表名 MODIFY 属性名数据类型 [约束性条件];

修改字段名

ALTER TABLE 表名 CHANGE 旧属性名新属性名新数据类型 [约束性条件];

增加字段

ALTER TABLE 表名 ADD 属性名1 数据类型 [约束性条件] [FIRST | AFTER 属性名2];

删除字段

ATLER TABLE 表名 DROP 属性名;

修改字段的排列位置

ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST | AFTER 属性名2;

更改表的存储引擎

ALTER TABLE 表名 ENGINE=存储引擎名;

删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

删除表

删除没有被关联的普通表

DROP TABLE 表名;

删除被其他表关联的父表

1、show create table 子表名; 找到外键约束。

2、删除子表外键约束

3、删除父表

索引

含义和特点

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。

优点：
提高检索数据的速度，这是创建索引的主要原因。
对于有依赖关系的父子表之间的联查是，可以提高查询速度；
可以显著节省查询中分组和排序的时间。

缺点：
创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加。
索引需要占用物理空间，每一个索引占用一定的物理空间；
增加、删除、修改索引数据时，要动态的维护索引，造成数据的维护速度降低了。

索引能提高查询速度，但是会影响插入记录的速度。
因为向有索引的表中插入记录时，数据库系统会按照索引进行排序。

分类

普通索引

不附加任何限制条件，可以创建在任何数据类型中，
其值是否唯一和非空有字段本身的完整性约束条件决定。

唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引。
在创建唯一性索引时，限制该索引的值必须是唯一的。
注：主键就是一种特殊唯一性索引。

全文索引

使用FULLTEXT参数可以设置索引为全文索引。
全文索引只能创建在CHAR,VARCHAR或TEXT类型的字段上。
查询数据量大的字符串类型的字段时，使用全文索引可以提高查询速度
只有MyISAM存储引擎支持全文索引。

单列索引

在表中的某个字段上创建索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。

多列索引

多列索引是在表的多个字段上创建一个索引。
只有查询中使用了这些字段中的第一个字段时，索引才会被使用。

空间索引

使用SPATIAL参数可以设置索引为空间索引。
空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。
MySQL空间数据类型包含GEOMETRY 和 POINT、LINESTRING、POLYGON等。
只有MyISAM支持空间检索，而且索引的字段不能为空值。

设计索引

1、选择唯一性索引

值是唯一的，可以更快速的通过索引确定某条记录

2、为经常需要排序、分组和联合操作的字段建立索引

经常需要GROUP BY、ORDER BY、DISTINCT、UNION等操作的字段

3、为常作为查询条件的字段建立索引

4、限制索引的数量

5、尽量使用数据量少的索引

6、尽量使用前缀来索引

字段名很长的使用前缀作为索引名

7、删除不再使用或很少使用的索引

创建索引

在创建表的时候创建索引

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名] (属性名1 [(长度)] [ASC|DESC])

UNIQUE 标识索引为唯一性索引

FULLTEXT 标识索引为全文索引

SPATIAL标识索引为空间索引

INDEX和KEY参数指定字段为索引，二选一

别名索引的取的新名称

属性1 参数指定索引对应的字段名

长度索引的长度，必须是字符串类型才可以使用

ASC DESC 升降序

在已经存在的表上创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ ASC|DESC])

通过ALTER创建索引

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名 [(长度)] [ ASC|DESC])

删除索引

DROP INDEX 索引名 ON 表名;

视图

含义和作用

含义

视图是一种虚拟的表，是从数据库中一个或多个表中导出来的表。
视图还可以从已经存在的视图的基础上定义。
数据库中只存放了视图的定义，而没有存放视图的数据，这些数据存在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图数据与原来表的数据同步。

作用

1、使数据简单化

视图的目的就是所见即所需

2、增加数据的安全性

通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户接触不到。

3、提高表的逻辑独立性

视图可以屏蔽原来表结构变化带来的影响。

创建视图

语法

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名 [(属性清单)]
AS SELECT 语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM

视图的算法

UNDEFINED

表示MySQL自动选择所要使用的算法

MERGE

表示将使用视图的语句和视图定义合并起来，使得视图定义的某一部分取代语句的对应部分

TEMPTABLE

表示将视图的结果存入临时表，然后使用临时表执行语句

视图名

属性清单

指定视图中各个属性的名词

WITH CHECK OPTION

标识更新视图时保证在该视图的权限范文内

CASCADED

表示更新视图时要满足所有相关视图和表的条件

LOCAL

表示更新视图时，要满足该视图本身的定义的条件即可。

注：使用create view创建视图时，最好加上WITH CHECK OPTION,
而且最好加上CASCADED参数，这样从视图上派生出来的新视图后，更新新视图需要考虑父视图的约束条件。
可以保证数据的安全性。

单表创建视图

多表创建视图

查看视图

DESCRIBE 视图名;

DESC 视图名;

SHOW TABLE STATUS LIKE '视图名';

SHOW CREATE VIEW 视图名;

SELECT * FROM information_schema.views;

修改视图

CREATE OR REPLACE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];

没有就创建，有就修改

ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT 语句
[WITH [CASCADED | LOCAL] CHECK OPTION];

删除视图

DROP VIEW [IF EXISTS] 视图名称列表 [RESTRICT | CASCADE]

更新视图

视图虽然可以更新，但是有很多限制，一般情况，最好将视图作为查询数据的虚拟表。

视图与表的区别与联系。

触发器

含义作用

触发器是由INSERT UPDATE DELETE等事件来出发某种特性操作。
满足触发器的条件时，数据库系统就会执行触发器重定义的程序语句。

创建

一个执行语句

CREATE TRIGGER 触发器名 BEFORE | AFTER 出发事件 ON 表名 FOR EACH ROW 执行语句.

BEFORE | AFTER

指定了出发器执行的时间

触发事件

指触发的条件，包含 INSERT UPDATE DELETE

表名

触发事件操作的表的名称

FOR EACH ROW

每一行记录的操作满足触发事件都会触发该触发器

执行语句

触发器被触发后执行的语句

多个执行语句

CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END

多条执行语句结尾不能用分号结束，必须用 DELIMITER 来修改程序结束符

查看

SHOW TRIGGERS;

SELECT * FROM information_schema.triggers;

删除

DROP TRIGGER 触发器名

3、SQL查询语句

查询数据

基本语法

单表查询

查询所有字段

不建议这种写法

查询指定字段

推荐

WHERE

[NOT] IN (元素1，元素2，元素3...元素n)

[NOT] BETWEEN 取值1 AND 取值2

[NOT] LIKE '字符串'

%：代表任意长度的字符串，长度可以为0

_ :只能表示单个字符

IS [NOT] NULL

条件表达式1 AND 条件表达式2 [... AND 条件表达式n]

条件表达式1 OR 条件表达式2 [... OR 条件表达式n]

SELECT DISTINCT 属性名 ......

结果不重复

ORDER BY 属性名 [ASC|DESC]

如果属性记录有null值，则升序null是第一个，降序null值是最后一个

GROUP BY 属性名

GROUP BY 属性名 HAVING 条件表达式

聚合函数查询

COUNT()

统计记录的条数

SUM()

求和函数

AVG()

平均值的函数

MAX()

最大值函数

MIN()

最小值函数

多表联查

内连接查询

外连接查询

LEFT JOIN

进行左连接查询时，可以查出'表名1'所指的表中的所有记录。
而'表名2'所指的表中，只能查询出匹配的记录

RIGHT JOIN

进行右连接查询时，可以查出'表名2'所指的表中的所有记录。
而'表名1'所指的表中，只能查询出匹配的记录。

复合条件连接查询

增加更多的限制条件

子查询

带IN的子查询

带比较运算符的子查询

带EXIST的子查询

EXISTS关键字表示存在，
使用EXISTS关键字时，内存查询语句不返回查询的记录，而是返回一个真假值。
如果内层查询语句返回true，外层将进行查询，否则，外层语句不进行查询或者查询不到任何记录。

带ANY的子查询

带ALL的子查询

合并查询结果

UNION

去重

UNION ALL

不去重

为表和字段取别名

表取别名

字段取别名

使用正则查询

匹配字符串开始的部分

匹配字符串结束的部分

匹配字符串中任意一个字符，包含回车与换行

[字符集合]

匹配“字符集合”中的任何一个字符

[^字符集合]

匹配除了“字符集合”以外的任意一个字符

S1|S2|S3

匹配S1、S2和S3中的任意一个字符串

代表多个该字符之前的字符，包含0和1个

代表多个该字符之前的字符，包含1个

字符串{N}

字符串出现N次

字符串[M，N]

字符串出现至少M次，最多N次

插入更新删除数据

插入

更新

删除

mysql运算符

算术运算符

加 | 减 | 乘 | 除

比较运算符

true返回1 false返回0

逻辑运算符

与、或、飞、异或等

位运算符

按位与、按位或、按位取反、按位异或、按位左移、按位右移等

只能在二进制的基础上操作

运算符的优先级

mysql函数

数学函数

ABS(X)

返回x的绝对值

CEIL(X), CEILING(X)

向下取整

FLOOR(X)

向上取整

RAND()

返回0-1的随机数

RAND(X)

返回0-1的随机数, 比如同时有多个RAND(X), X相同时，返回的随机数是一样的。

SIGN(X)

返回X的符号,正数、负数、0 返回 1、-1、0

PI()

圆周率

TRUNCATE(X,Y)

按y位取保留精度，比如TRUNCATE(3.14,1) = 3.1

ROUND(X)

四舍五入到整数

ROUND(X,Y)

四舍五入到y位小数点

POW(X,Y),POWER(X,Y)

返回x的y次方

SORT(X)

返回x的平方根

EXP(X)

返回e的x次方

MOD(X,Y)

返回x除以y以后的余数

LOG(X)

返回自然对数，以e为底的对数

LOG10(X)

返回以10为底的对数

RADIANS(X)

将角度转化为弧度

DEGREES(X)

将弧度转化为角度

SIN(X)

正弦

ASIN(X)

反正弦

COS(X)

余弦

ACOS(X)

反余弦

TAN(X)

正切

ATAN(X),ATAN2(X,Y)

反正切

COT(X)

余切

字符串函数

CHAR_LENGTH(s)

返回字符串s的字符数

LENGTH(s)

返回字符串s的长度

CONCAT(s1,s2,...)

字符串连接

CONCAT_WS(x,a1,a2,....)

指定分隔符连接字符串

INSERT(s1,x,len,s2)

将字符串s2替换s1的x位置开始长度为len的字符串

insert('abcdef',1,2,'xxxx')=xxxxcdef

UPPER(s),UCASE(s)

大写

LOWER(s),LCASE(s)

小写

LEFT(s,n)

返回字符串s的前n个字符

RIGHT(s,n)

返回字符串s的后n个字符

LPAD(s1,len,s2)

用字符串s2来填充s1的开始处，使字符串长度达到len

比如二进制前面填充0，比较适合

RPAD(s1,len,s2)

用字符串s2来填充s1的结尾处，是字符串长度达到len

LTRIM(s)

将字符串s左边的空格去掉

RTRIM(s)

将字符串s右边的空格去掉

TRIM(s)

将字符串s两端的空格去掉

TRIM(s1 FROM s)

将字符串s两端的s1字符串去掉

REPEAT(s, n)

返回重复n次字符串s的新字符串

SPACE(n)

返回n个空格

REPLACE(s,s1,s2)

用字符串s2替换字符串s中的s1

STRCMP(s1,s2)

比较字符串

SUBSTRING(s,n,len)

截断字符串，从n开始的len长度

MID(s,n,len)

同上

LOCATE(s1,s), POSITION(s1 IN s)

从字符串s中获取s1的开始位置

INSTR(s,s1)

从字符串s中获取s1的开始位置

REVERSE(s)

倒过来

ELT(n,s1,s2,....)

返回第n个字符串

EXPORT_SET(x,s1,s2,s,len)

x转化为二进制，0用s1代替，1用s2代替，分隔符为s，总共长度为len

SELECT EXPORT_SET(5,'Y','N',',',4); == 'Y,N,Y,N'

SELECT EXPORT_SET(5,'Y','N',',',3); == 'Y,N,Y'

SELECT EXPORT_SET(5,'Y','N',',',6); == 'Y,N,Y,N,N,N'

FIELD(s,s1,s2)

返回第一个字符串s匹配的字符串的位置，从1开始

SELECT FIELD('A','B','A','D','E') = 2

FIND_IN_SET(s1,s2)

返回字符串s2与s1匹配的字符串的位置

MAKE_SET(x,s1,s2,...)

按x的二进制从s1，s2，...中取字符串,取二进制1的位置的字符串

SELECT MAKE_SET(5,'A','B','A','D','E') ; == 101 = 'A','A'

SELECT MAKE_SET(3,'A','B','A','D','E') ; == 11 = 'A','B'

日期和时间函数

CURDATE()
CURRENT_DATE()

返回当前日期

CURTIME()
CURRENT_TIME()

返回当前时间

NOW()
CURRENT_TIMESTAMP()
LOCALTIME()
SYSDATE()
LOCALTIMESTAMP()

返回当前日期和时间

UNIX_TIMESTAMP()

以UNIX时间戳的形式返回当前时间

也就是long类型的数字形式

UNIX_TIMESTAMP(d)

将时间d以UNIX时间戳的形式返回

也就是long类型的数字形式

FROM_UNIXTIME(d)

把UNIX时间戳的时间转换为普通格式的时间

上面的逆向操作

UTC_DATE()

返回UTC(Universal Coordinated Time,国际协调时间)日期

UTC也就是英国伦敦的时区来返回时间

UTC_TIME()

返回UTC时间

UTC也就是英国伦敦的时区来返回时间

MONTH(d)

返回月 1-12

MONTHNAME(d)

月名称 January February...

DAYNAME(d)

星期几 Monday, TuesDay

DAYOFWEEK(d)

返回星期几 1星期天 2星期一

WEEKDAY(d)

星期几 0星期一 1星期二

WEEK(d)

本年第几个星期 0-53

WEEKOFYEAR(d)

本年第几个星期 1-53

DAYOFYEAR(d)

本年第几天 1-366

DAYOFMONTH(d)

本月第几天 1-31

YEAR(d)

年

QUARTER(d)

季度

HOUR(t)

小时

MINUTE(t)

分

SECOND(t)

秒

EXTRACT(type FROM d)

从日期d中获取指定的值，type为指定返回的类型，如YEAR，HOUR等

TIME_TO_SEC(t)

将时间t转化为秒

SEC_TO_TIME(s)

以秒为单位的时间s转化为分秒的格式

TO_DAYS(d)

计算日期d~0000年1月1日的天数

FROM_DAYS(n)

计算从0000年1月1日的开始n天后的日期

DATEDIFF(d1,d2)

计算d1~d2之间相隔的天数

ADDDATE(d,n)

计算起始日期d加上n天的日期

ADDDATE(d,INTERVAL expr type)

计算起始日期d加上一个时间段后的日期

DATE_ADD(d,INTERVAL expr type)

同ADDDATE(d,INTERVAL expr type)

SUBDATE(d,n)

计算起始日期d减去n天后的日期

SUBDATE(d, INTERVAL expr type)

计算起始日期d减去一个时间段后的日期

ADDTIME(t,n)

计算起始时间t加上n秒的时间

SUBTIME(t,n)

计算起始时间t减去n秒的时间

DATE_FORMAT(d,f)

按照表达式f的格式显示日期d

TIME_FORMAT(t,f)

按照表达式f的格式显示时间t

GET_FORMAT(type,s)

根据字符串s获取type类型数据的显示格式，s的取值包含：

USA

'%m.%d.%Y'

JIS

'%Y-%m-%d'

ISO

'%Y-%m-%d'

INTERNAL

'%Y%m%d'

EUR

'%d.%m.%Y'

条件判断函数

IF(expr, v1, v2)

如果表达式expr成立，返回v1，否则返回v2

IFNULL（v1, v2）

如果v1不为空，显示v1，否则显示v2

CASE

CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2 ...] [ELSE vn] END

CASE表示函数开始，END表示函数结束，
如果expr1成立，返回v1的值。如果expr2成立返回v2，...
最后ELSE 返回vn的值

CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...] [ELSE vn] END

系统信息函数

VERSION()

返回数据库的版本号

CONNECTION_ID()

返回服务器的连接数

DATABASE(), SCHEMA()

返回当前数据库名

USER(), SYSTEM_USER(), SESSION_USER()

返回当前用户

CURRENT_USER(), CURRENT_USER

返回当前用户

CHARSET(str)

返回字符串str的字符集

COLLATION(str)

返回字符串str的字符串排列方式

LAST_INSERT_ID()

返回最近生成的AUTO_INCREMENT值

加密函数

PASSWORD(str)

主要用来给mysql用户密码加密，存到user表

MD5(str)

对普通数据进行加密

ENCODE(str,pswd_str)

使用字符串pswd_str来加密字符串str，加密的结果是一个二进制数，必须使用BLOB类型的字段保存

DECODE(cry_str,pswd_str)

与加密函数逆向

其他函数

FORMAT(x,n)

将数字x进行格式化，将x保留到小数后n位，四舍五入

进制转换函数

ASCii(s)

返回ASCII码

BIN(x)

HEX(x)

OCT(x)

CONV(x,f1,f2)

将x从f1进制转化为f2进制

加锁解锁

GET_LOCK(name, time)

定一个name，时间为time的锁

RELEASE_LOCKname)

解锁

IS_FREE_LOCK(name)

判断是否存在name的锁

改变字符集

CONVERT(s USING cs)

将s的字符集变为cs

重复执行

BENCHMARK(count, expr)

将表达式expr执行count次

改变字段数据类型

CASE(x AS type)

CONVERT(x, type)

存储过程和函数

创建存储过程

sp_name

存储过程的名称

proc_parameter

存储过程的参数列表

[IN | OUT | INOUT] param_name type

IN输入 OUT输出 INOUT既是输入又是输出

param_name 存储过程的参数名称

type 存储过程的参数类型

characteristic

指定存储过程的特性

LANGUAGE SQL

说明routine_body部分是由SQL语言的语句组成，系统默认的语言

[NOT] DETERMINISTIC

指存储过程的执行结果是否是确定的

DETERMINISTIC 同样的输入得到同样的结果

[默认情况]NOT DETERMINISTIC 同样的输入可能得到不同的结果

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

指明子程序使用SQL语句的限制
CONTAINS SQL 表示子程序包含SQL语言，但不包含读或者写数据的语句（默认）
NO SQL表示子程序不包含SQL语句
READS SQL DATA表示子程序中包含读数据的语句
MODIFIES SQL DATA表示子程序中包含写数据的语句

NO SECURITY {DIFINER | INVOKER}

指明谁有权限来执行，
DEFINER表示只有定义者才能够执行（默认）
INVOKER表示调用者可以执行

COMMENT 'string'

注释信息

routine_body

SQL代码的内容，可以用BEGIN..END来标记开始和结束

创建存储函数

func_paramter

存储函数的参数

RETURNS type

返回值的类型

characteristic

指定存储过程的特性

LANGUAGE SQL

说明routine_body部分是由SQL语言的语句组成，系统默认的语言

[NOT] DETERMINISTIC

指存储过程的执行结果是否是确定的

DETERMINISTIC 同样的输入得到同样的结果

[默认情况]NOT DETERMINISTIC 同样的输入可能得到不同的结果

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}

NO SECURITY {DIFINER | INVOKER}

指明谁有权限来执行，
DEFINER表示只有定义者才能够执行（默认）
INVOKER表示调用者可以执行

COMMENT 'string'

注释信息

routine_body

SQL代码的内容

变量的使用

定义变量

DECLARE

声明变量

var_name

变量的名称，可以定义多个

type

变量的类型

DEFAULT value

变量默认值

变量赋值

SET

为变量赋值

var_name

变量的名称

expr

复制表达式

定义条件和处理程序

实现定义程序执行过程中可能遇到的问题。
并且可以在处理程序中定义解决这些问题的办法。
这种方式可以提前预测可能出现的问题，并提出解决办法。
可以增强程序处理问题的能力，避免程序异常停止。

定义条件

condition_name

条件名字

condition_value

条件类型

sqlstate_value/mysql_error_code

ERROR 1146(42S02)

定义处理程序

handler_type

错误处理方式

CONTINUE

遇到错误不进行处理

EXIT

遇到错误马上退出

UNDO

遇到错误撤回之前的操作[暂不支持]

condition_value

错误类型

sqlstate_value/mysql_error_code

同条件定义

sp_statement

表示一些存储过程或函数的执行语句

condition_name

条件名称

光标的使用

声明光标

cursor_name

光标名称

select_statement

SELECT 语句内容

打开光标

使用光标

将光标对应的查询结果存入INTO的字段内

关闭光标

流程控制的使用

search_conditon

条件判断语句

statement_list

不同条件的执行语句

CASE

case_value

条件判断的变量

when_value

变量的取值

statement_list

执行语句

search_condition

条件判断语句

LOOP

使特定的语句重复执行，实现一个简单的额循环，没有停止循环的语句，必须遇到LEAVE语句才能停止。

LEAVE

结束循环

ITERATE

跳出本次循环，进入到下一次循环

REPEAT

条件循环语句，当满足特性条件时，就会跳出循环

WHILE

条件控制的循环语句，满足条件就执行语句

调用存储过程和函数

查看存储过程和函数

修改存储过程和函数

与创建相同，只是将create 换成 alter

删除存储过程和函数

4、MySQL数据库高级管理

用户管理

权限表

user表

用户、权限、安全、资源控制

db表、host表

db表储存某个用户对一个数据库的权限

table_priv表、columns_prv表

tables_priv表可以对单个表进行权限设置
columns_priv表可以对单个数据列进行权限设置

procs_priv表

对存储过程和存储函数进行权限设置

mysql -h hostname|hostIP -P port -u username -p DatabaseName -e 'SQL语句'

exit、quit

创建删除普通用户

新建

删除

普通用户和root用户密码管理

root用户

root修改普通用户

普通用户修改密码

权限管理

授权 GRANT

with_option

GRANT_OPTION

被授权的用户可以将这些权限赋予给别的用户

MAX_QUERIES_PER_HOUR count

设置每个小时允许执行的count次查询

MAX_UPDATES_PER_HOUR count

设置每个小时允许执行的count次更新

MAX_CONNECTIONS_PER_HOUR count

设置每个小时允许执行的count次连接数

MAX_USER_CONNECTIONS count

设置每个用户同时count个连接

收回权限REVOKE

查看权限

数据备份与还原

数据备份

使用msqldump命令备份

备份一个数据库

备份多个数据库

备份所有数据库

直接复制整个数据库目录（不推荐）

使用mysqlhotcopy工具快速备份，不需要停止MySQL服务器（不能备份InnoDB类型的表）

数据还原

使用mysql命令还原

直接复制到数据库目录

必须保证数据库主版本号一致

数据库迁移

相同版本迁移

不同版本迁移

从低版本到高版本可以用上面的方法

不同数据库迁移

可以生成sql文件导入到新的数据库

表的导入和导出

导出

用SELECT ... INTO OUTFILE 导出文本文件

用mysqldump命令导出文本文件

用mysql命令导出文本文件

导入

用LOAD DATA INFILE方式导入文本文件

mysql日志

错误日志

查看

my.cnf(linux) my.ini(windows) 配置的日志文件log-error=

删除

mysqladmin -u root -p flush-logs

通用日志

查看

my.cnf(linux) my.ini(windows) 配置的日志文件log=

删除

mysqladmin -u root -p flush-logs

慢查询日志

查看

配置中 log-slow-queries=

配置中 long_query_time=n

删除

mysqladmin -u root -p flush-logs

性能优化

简介

SHOW STATUS LIKE 'value'

value常用的几个统计参数
Connections:连接MySQL服务器的次数
Uptime: MySQL服务器的上线时间
Slow_queries:慢查询次数
Com_select:查询操作的次数
Com_insert:插入操作的次数
Com_update:更新操作的次数
Com_delete:删除操作的次数

优化查询

分析查询语句

EXPLAIN SELECT 语句;

id: 标识SELECT语句的编号

select_type: 标识SELECT语句的类型。

SIMPLE

标识简单查询，不包含子查询和连接查询；

PRIMARY

表示主查询，或者最外层的查询语句；

UNION

表示连接查询的第二个或后面的查询语句；

table: 表示查询的表