首页  思维导图  详情

MySQL数据库

2021-01-11 11:48:10   0  举报





AI智能生成

MySQL思维导图

MySQL

mysql

思维导图

最新

作者其他创作

大纲/内容

数据库的概述

数据库

数据库的概念和作用

数据库（Database）是指长期存储在计算机内，有组织的，可共享的数据集合

数据库的特点

1.实现了数据的独立性

2.实现了数据共享

3.减少了数据的冗余度

4.实现了数据的集中控制

5.维护了数据完整性

6.提高了数据的可恢复性

数据模型

数据模型的概率：数据模型（Data Model）是对现实世界数据的模拟和抽象，是数据库系统中用于提供信息表示操作手段形式架构

数据模型的类型

概念数据模型（Conceptual Data Model）

逻辑数据模型（Logixal Data Model）

物理数据模型（Physical Data Model）

数据模型的三要素

数据结构

数据操作

数据完整性约束

数据库类型

层次模型数据库

网状模型数据库

关系模型数据库

数据库系统

数据库系统（Database System,简称（DBS）是为处理大量数据而发展起来的一种较为理想的系统，它是一个存储介质、处理对象和管理系统的集合体）

数据库系统的构成

1.硬件

2.软件

3.数据库

4.用户

数据库管理系统的主要功能有

对数据的定义

对数据进行增、删、改、查等操作

对数据进行组织、存储与管理

维护数据库，保证数据库的正常运行

保护数据库中的数据安全，数据遭到破坏后能够恢复

负责处理数据的传送

常见的关系模型数据库管理系统

1.Oracle

2.DB2

3.SQL Server

MySQL数据库管理系统

MySQL是目前非常流行的关系模型数据库管理系统，关系型数据库管理系统就是管理关系型数据库，并将数据组织为相关的行和列的系统

诞生于 1995年 MySQL 1.0

MySQL的优势

运行速度快

对于大多数人免费

支持多种操作系统

为多种编程语言提供了API

支持多线程

既能作为一个单独的应用程序，也能作为一个库嵌入到其他软件中

支持多种存储引擎

可复制全局事务标识，支持自我修复式集群

结构化查询语言SQL

结构化查询语言（Structuted Query Language,简称SQL）是目前被广泛使用的关系型数据库标准语言，用于增、删、改、查，以及管理关系型数据库系统

SQL语句主要可分为

1.数据定义语句（DDL）

2.数据操作语句（DML）

3.数据控住语句（DCL）

4.事务处理语句

MySQL的安装与配置

1.下载MySQL:网址“https://dev.mysql.com/downloads/mysql” 进入下载页面下载所需的版本的压缩文件

2.将下载的压缩的文件“mysql-5.7.18-winx64.zip”解压到本地磁盘列入F：盘

3.配置文件新建文件“my.ini”

4.用记事本打开文件“my.ini” 添加配置内容

配置文件具体的结构分析：
[mysqld]
port = 3306
socket = /tmp/mysql.sock
# 设置mysql的安装目录
basedir=F:\\Hzq Soft\\MySql Server 51GA
# 设置mysql数据库的数据的存放目录，必须是data，或者是\\xxx-data
datadir=F:\\Hzq Soft\\MySql Server 51GA\\data
#innodb_log_arch_dir 默认datadir
#innodb_log_group_home_dir 默认datadir
# 设置mysql服务器的字符集，默认编码
default-character-set=utf8

#连接数的操作系统监听队列数量，如果经常出现“拒绝连接”错误可适当增加此值
back_log = 50
#不使用接听TCP / IP端口方法，mysqld通过命名管道连接
#skip-networking
# 最大连接数量
max_connections = 100
#打开表的线程数量限定，最大4096，除非用mysqld_safe打开限制
table_open_cache = 2048
#MySql 服务接收针对每个进程最大查询包大小
max_allowed_packet = 16M
#作用于SQL查询单笔处理使用的内存缓存，如果一笔操作的二进制数据超过了限定大小，将会在磁盘上开辟空间处理，一般设为 1-2M即可，默认1M
binlog_cache_size = 2M
#单个内存表的最大值限定
max_heap_table_size = 64M
#为每个线程分配的排序缓冲大小
sort_buffer_size = 8M
#join 连表操作的缓冲大小,根据实际业务来设置，默认8M
join_buffer_size = 32M
#操作多少个离开连接的线程的缓存
thread_cache_size = 8
#并发线程数量，默认为8，可适当增加到2倍以内。如果有多个CPU可以乘上CPU的数量。双核CPU可以乘上当前最核数再乘上70%-85%
thread_concurrency = 16
#专用于具体SQL的缓存，如果提交的查询与几次中的某查询相同，并且在query缓存中存在，则直接返回缓存中的结果。
query_cache_size = 64M
#对应上一条设置，当查询的结果超过下面设置的大小时，将不会趣入到上面设置的缓存区中，避免了一个大的结果占据大量缓存。
query_cache_limit = 2M
#设置加全文检索中的最小单词长度。
#ft_min_word_len = 4
#CREATE TABLE 语句的默认表类型，如果不自己指定类型，则使用下行的类型
default-storage-engine = InnoDB
#线程堆栈大小，mysql说它自己用的堆栈大小不超过64K。这个值可适当设高一点（在RCA的项目中都是共用同一个数据库连接的），默认192K
thread_stack = 800K
#设置事务处理的级别,默认 REPEATABLE-READ，一般用它就即可，以下二行按顺序对应，
#可读写未提交的数据，创建未提交的数据副本读写，未提交之前可读不可写，只允许串行序列招行事务。
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ
#单一内存临时表在内存中的大小，超过此值自动转换到磁盘操作
tmp_table_size = 64M
#启动二进制日志功能，可通过它实现时间点恢复最新的备份
#log-bin=mysql-bin
#二进制日志格式，对就上一条，-建议混合格式
#binlog_format=mixed
#转换查询为缓慢查询
slow_query_log
#对应上一条，如果一个查询超过了下条设定的时间则执行上一条。
long_query_time = 2
#自定义主机ID识别符，用于主从或多服务器之间识别，为一个 int 类型
server-id = 1
#一般用来缓存MyISAM表的主键，也用于临时的磁盘表缓存主键，上面多次出现临时磁盘表，所以就算不用MyISAM也最好为其设置一个不小的值，默认32M
key_buffer_size = 64M
#全表扫描MyISAM表时的缓存，每个线程拥有下行的大小。
read_buffer_size = 2M
#排序操作时与磁盘之间的缓存，分到每个线程，默认16M
read_rnd_buffer_size = 24M
#MyISAM使用特殊树形进行批量插入时的缓存，如insert ... values(..)(..)(..)
bulk_insert_buffer_size = 64M
#MyISAM索引文件的最大限定，
myisam_max_sort_file_size = 10G
#如果一个myisam表有一个以上的索引， MyISAM可以使用一个以上线程来排序并行它们。较耗硬件资源，如果你的环境不错，可以增加此值。
myisam_repair_threads = 2
#自动检查和修复无法正确关闭MyISAM表。
myisam_recover
# *** INNODB Specific options ***
#开启下条将会禁用 INNODB
#skip-innodb
#一般不用设置或者说设了也没多大用，InnoDB会自己与操作系统交互管理其附加内存池所使用InnoDB的存储数据的大小
innodb_additional_mem_pool_size = 16M
#innodb整体缓冲池大小，不宜过大，设为本地内存的 50%-75% 比较合适,在本机开发过程中可以设得较小一点如 64M,256M
innodb_buffer_pool_size = 256M
#InnoDB的数据存储在一个或多个数据文件组成的表空间
innodb_data_file_path = ibdata1:10M:autoextend
#用于异步IO操作的线程数量，默认为 4 ，可适当提高
innodb_file_io_threads = 8
#线程数内允许的InnoDB内核,不宜太高
innodb_thread_concurrency = 16
#InnoDB的事务日志快存行为,默认为 1，为0可减轻磁盘I/0操作，还有以为2
innodb_flush_log_at_trx_commit = 1
#InnoDB的用于的缓冲日志数据的大小
innodb_log_buffer_size = 8M
#日志文件，可设置为25%-90%的总体缓存大小，默认 256M. 修改此项要先删除datadir\ib_logfileXXX
innodb_log_file_size = 256M
#日志组数量，默认为3
innodb_log_files_in_group = 3
#InnoDB的日志文件位置。默认是MySQL的datadir
#innodb_log_group_home_dir
#InnoDB最大允许的脏页缓冲池的百分比，默认90
innodb_max_dirty_pages_pct = 80
#事务死锁超时设定
innodb_lock_wait_timeout = 120

[client]
port = 3306
socket = /tmp/mysql.sock
# 设置mysql客户端的字符集
default-character-set=utf8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[WinMySQLAdmin]
# 指定mysql服务启动启动的文件
Server=F:\\myweb\\MySql Server\\bin\\mysqld.exe

5.右击开始菜单，执行“命令提示符（管理员）”命令输入“F:” 跳转到F盘根目录然后输入“cd mysql-5.7.18-winx64\bin” 回车跳转到“cd mysql-5.7.18-winx64\bin”目录

6.在命令行窗口继续输入“mysqld--initialize-insecure--user=mysql” 次数MySQL会进行初始化自动创建“data”文件夹

7.在命令行窗口继续输入“mysqld--install MySQL--dafaults0file="F:\mysql-5.7.18-winx64\my.ini" ”安装MySQL服务并指定配置文件位置

8.打开 “系统属性”对话框单击“环境变量”按钮在“系统变量”中选择“Path” 单击编辑打开“编辑环境变量”对话框单击“新建”按钮然后输入路径“F:\mysql-5.7.18-winx64\bin\”

9.启动MySQL

启动MySQL服务执行“运行” 命令在输入框中输入“services.msc” 打开“服务” 窗口选择“MySQL” 单击“启动”选项即可启动MySQL

在命令行窗口启动MySQL服务在命令行窗口输入“net start mysql”命令即可启动MySQL服务

10.下载并安装Navicat for MySQL

11.使用Navicat连接MySQL 打开软件单击“文件”菜单执行“新建连接” 在“常规”选项卡下输入正确的主机名或IP地址、端口、用户名和密码之后，单击“确定” 即可与MySQL建立连接

数据库基本操作

创建数据库

1.系统数据库

information_schema:主要存储系统中一些数据库对象信息，包括用户信息，字符集信息和分区信息

mysql：主要存储账户信息、权限信息、存储过程和时区信息等

performance_schema:主要用于收集数据库服务器性能参数

sys:该库通过视图把information_schema、mysql、performance_schema结合起来，查询出更容易理解的数据

2.用户数据库

根据用户实际需求手动创建的数据库

3.创建数据库 SQL语句：create database 数据库名

查看和选择数据库

查看数据库 SQL语句：show databases

选择数据库 SQL语句：use 数据库名

删除数据库

删除数据库 SQL语句： drop database 数据库名

存储引擎、数据类型和字符集

存储引擎

存储引擎是MySQL体系结构的重要组成部分，作用是指定表的类型，规定表如何存储和索引数据、是否支持事务等

查看所有存储引擎：show [engines] \g

查看MySQL默认存储引擎：show variables like 'default_storage_engine'

修改MySQL默认存储引擎：打开my.ini配置文件添加一下内容： default_storage_engine=MyISAM

常用的存储引擎

InnoDB:为MySQL提供具有提交、回滚、崩溃恢复能力和多版本并发控住的事务安全型表，能够高效地处理大量数据

MyISAM:基于ISMA，并对其进行扩展，具有较高的插入和查询速度，但是不支持事务和外键

MEMORY:主要用于内容变化不频繁的表，由于MEMORY存储引擎是将数据存储到内存中，所以太大的表无法使用此引擎

数据类型

1.数值类型：用于存储数字型数据包括整数型（tinyint,smallint,mediumint,int,bigint）、浮点型（float,double）和定点数类型（decimal）,其中整数类型的字段用于存储整数，浮点数和定点数类型的字段用于存储小数

2.日期和时间类型：year,date,time,datetime,timestamp

存储格式

year:yyyy

date:yyyy-mm-dd

time:hh:mm:ss

datetime:yyyy-mm-dd hh:mm:ss

timestamp:yyyy-mm-dd hh:mm:ss

3.字符串类型：char,varchar,binary,varbinary,blob,text,enum,set

char,varchar:用于存储较短的字符串

binary,varbinary:用于存储较短的二级制字符串

text,blob:用于存储较大的数据

enum:用于存储“单选项”形式的数据

set:用于存储“多选项”形式的数据

JSON类型：MySQL新增的数据类型，用于存储JSON数据

字符集

查看MySQL中所有可以的字符集：show character set;

查看字符集所对应的排序规则：show collation like'字符集名称%'

服务器级的字符集

查看当前服务器使用的字符集：show variables like 'character_set_server'

修改字符集和排序规则：打开my.ini配置文件添加一下内容：character-set-server=utf8 collation-server=utf8_general_ci 修改完毕后，重启MySQL服务才能生效

数据库级的字符集

查看数据库使用的字符集：show variables like 'caharacter_set_database'

创建数据库时选择字符集：create database 数据库名 default charset 字符集名

修改数据库字符集：alter database 数据库名 default charset 字符集名

数据表基本操作

创建数据表 create table 表名(列名1 数据类型约束条件,列名2 数据类型约束条件；)

设置主键：

定义字段时：列名数据类型 primary key

定义字段后：primary key(列名)

设置自增约束：

列名数据类型 auto_increment

设置非空约束：

列名数据类型 not null

设置唯一性约束：

定义字段时：列名数据类型 unique

定义字段后：unique key(列名)

设置无符号约束

列名数据类型 unsigned

设置默认约束

列名数据类型 default 默认值

设置外键约束

alter table 表名 add constraint 外键名称 foreign key(你的外键字段名) references 外表表名(对应的表的主键字段名);

设置表的存储引擎

create table 表名(列名1 数据类型约束条件,列名2 数据类型约束条件；) engine=存储引擎名

查看表结构 describe 表名

查看建表语句：show create table 表名 \g

修改表

修改表名

alter table 旧表名 rename 新表名

修改字段数据类型

alter table 表名 modify 列名新的数据类型

修改字段名

alter table 表名 change 旧表名新表名数据类型

添加字段

alter table 表名 add 表名数据类型

删除字段

alter table 表名 drop 列名

修改字段顺序

alter table 表名 modify 列名数据类型 first| after 列名2 其中 first 表示第一列，after 列名2 表示在列名2之后

修改存储引擎

alter table 表名 engine=存储引擎名

删除表

删除没有被关联的表

drop table if exists 表名

删除被其他表关联的主表

alter table 表名 drop foreign key 外键名

数据的插入、修改和删除操作

插入数据

向表中所有字段插入数据

指定列插入其值

insert into 表名(列1、列2、列3) values(值1、值2、值3)

不指定列插入值

insert into 表名 values(值1、值2、值3....值n)

向表中指定字段插入数据

insert into 表名（列1、列2、列3.....列N）values(值1、值2、值3.....值N)

同时插入多条数据

inster into 表名 (列1、列2、列3....列N) values(值1、值2、值3.....值N),(值1、值2、值3.....值N).....(值1、值2、值3.....值N);

将其它表中的数据插入到表中

insert into 表名1（表1中的列） select 表2中的列 from 表名2；语句的意义是将表2中查询指定的列插入到表1指定列中

修改数据

修改所有数据

update 表名 set 列1=值，列2=值...列N=值；

修改指定数据

update 表名 set 列1=值，列2=值.....列N=值 where 限定范围的条件

删除数据

删除所有数据

delete from 表名

删除指定数据

delete from 表名 where 限定范围的条件

单表数据记录查询

基本查询语句

select 列名 from 表名 [where 限定范围条件 | group by 列名 order by 列名 limit 数值]；group by 表示对查询出来的结果分组，order by 表示对查询出来的结果排序升序（ASC）降序（DESC） limit 表示显示每次查询结果数据的条数

简单记录查询

查询所有字段

select * from 表名

查询指定字段

select 列名1，列名2....列名N from 表名

查询指定记录

select * from 表名 where限定范围条件

条件判断符：=,<,>,!=,<=,>=

多条件查询

select * from 表名 where限定范围条件 and 限定范围条件（多条件都符合）

select *from 表名 where限定范围条件 or 限定范围条件（多条件中符合其中一个）

select *from 表名 where 列名 in（值1，值2....值N）；（该列中值等于集合中的任意一个值的记录）

查询空值

select *from 表名 where 列名 is null；（查询该列中值为空的记录）

查询结果去重

select distinct 列名 from 表名；（查询结果去掉列中的重复值）

范围查询

select * from 表名 where 列名 between 值1 and 值2 ；（查询列中值为值1~值2之间的记录）

字符匹配查询

select * from 表名 where 列名 Like 字符；（通配符 : '%'表示可以匹配任意长度的字符 ‘_’表示可以匹配任意位置的一个字符）

排序查询

select *from 表名 order by 列名1，列名2 ASC|DESC (ASC升序，DESC降序) 先按照列名1排序在按照列2排序

限制查询结果的数量

select * from 表名 limit 起始位置条数（起始位置默认为0）

聚合函数和分组数据记录查询

聚合函数有：count() 计数，sum（）求和，avg（）平均值，max（）最大值，min()最小值

使用聚合函数

select 聚合函数名（列名） from 表名 where 限定范围条件

分组查询

select 聚合函数（列名） from 表名 group by 列名1 having 限定范围条件；（按照列1对数据进行分组）

select 聚合函数（列名 1），列名2，列名3 from 表名 group by 列名1 having 限定范围条件；（多列进行分组查询）

多表数据查询

连接查询

内连接查询

select 列名 from 表名1 inner join 表名2 on 连接条件或者（select 列名 from 表名1，表名2 where 表名1.列名1=表名2.列名1）

自连接查询

外连接查询

左连接

select 列名 from 表名1 left join 表名2 on 连接条件

右连接

select 列名 from 表名1 right join 表名2 on 连接条件

复合条件查询

通过连接查询条件过滤条件，以达到限制查询结果和筛选数据的目的

子查询

from子句中的子查询

select 列名 from （select 列名 from 表名）as 表别名 where 限定范围条件

where子句中的子查询

in关键字其中一个

select 列名 from 表名1 where 列名 in （select 列名 from 表名2 where 限定范围条件）

any 和some关键字其中任意一个

select 列名 from 表名1 where 列名 <any （select 列名 from 表名2 where 限定范围条件）表示满足子查询中的任意一个比较条件

all关键字所有

select 列名 from 表名1 where 列名 >all （select 列名 from 表名2 where 限定范围条件）

exists关键字是否存在存在返回 true 不存在返回false

select 列名 from 表名1 where exists（select 列名 from 表名2 where 限定范围条件）

条件判断符

select 列名 from 表名1 where 列名 = （select 列名 from 表名2 where 限定范围条件）

合并查询结果

结果合并且去重

select 列名 from 表名1 union select 列名 from 表名2

结果计合并且不去重

select 列名 from 表名1 union all select 列名 from 表名2

常用函数

数学函数

ABS(X):返回X的绝对值

MOD(N,M)或%:返回N被M除的余数

FLOOR(X):返回不大于X的最大整数值。

CEILING(X):返回不小于X的最小整数值。

ROUND(X) :返回参数X的四舍五入的一个整数。

字符串函数

ASCII(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL

CONCAT(str1,str2,...):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式

LENGTH(str):返回字符串str的长度

LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置

LEFT(str,len):返回字符串str的最左面len个字符

RIGHT(str,len):返回字符串str的最右面len个字符

SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串

TRIM(str):返回字符串str，所有前缀或后缀被删除了

LTRIM(str):返回删除了其前置空格字符的字符串str。

RTRIM(str):返回删除了其拖后空格字符的字符串str

REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替

REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

REVERSE(str):返回颠倒字符顺序的字符串str

INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。

日期和时间函数

DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。

WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)

DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。

DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。

MONTH(date):返回date的月份，范围1到12

DAYNAME(date):返回date的星期名字

MONTHNAME(date) :返回date的月份名字。

QUARTER(date):返回date一年中的季度，范围1到4。

WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。

YEAR(date):返回date的年份，范围在1000到9999

HOUR(time):返回time的小时，范围是0到23

MINUTE(time):返回time的分钟，范围是0到59。

SECOND(time):回来time的秒数，范围是0到59。

DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒
DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒

CURRENT_DATE:以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数在一个字符串还是数字上下文被使用。

CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值

NOW():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间

流程控制函数

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定

Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0，str1<str2返回-1)

系统信息函数

VERSION()函数返回数据库的版本号

CONNECTION_ID()函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；

DATABASE()和SCHEMA()返回当前数据库名。

获取用户名的函数
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER这几个函数可以返回当前用户的名称。

获取字符串的字符集和排序方式的函数
CHARSET(str)函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集；COLLATION(str)函数返回字符串str的字符排列方式。

获取最后一个自动生成的ID值的函数
LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。

加密函数

加密函数PASSWORD(str)
PASSWORD(str)函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。

加密函数MD5(str)
MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。

加密函数ENCODE(str,pswd_str)
ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。

解密函数
DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。

其它函数

FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位

ASCII(s)返回字符串s的第一个字符的ASCII码；BIN(x)返回x的二进制编码；HEX(x)返回x的十六进制编码；OCT(x)返回x的八进制编码；CONV(x,f1,f2)将x从f1进制数变成f2进制数。

INET_ATON(IP)函数可以将IP地址转换为数字表示；INET_NTOA(n)函数可以将数字n转换成IP的形式。其中，INET_ATON(IP)函数中IP值需要加上引号

GET_LOCT(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功，返回1；如果尝试超时，返回0；如果遇到错误，返回NULL。RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULL；IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用，返回0；否则，返回1。

BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。

CONVERT(s USING cs)函数将字符串s的字符集变成cs

索引

索引概述

索引是表的目录，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

索引分类

普通索引（index）

没有任何限制，唯一任务就是加快访问速度

组合索引

指在表的多个字段组合上创建的索引

唯一索引（unique）

与普通索引类似，索引值必须唯一，但允许为空值，一张表只能有一个主键索引

全文索引（fulltext）

在创建索引的列上支持值的全文查找

空间索引（spatial）

创建空间索引的字段，必须将其声明为not null

创建和查看索引

在建表是创建：create table 表名（.....
index 索引名称（列名））

查看索引 show index from 表名\g

创建唯一索引：create table 表名（ .....
unique 索引名称（列名））

创建全文索引：create table 表名（.....
fulltext 索引名称（列名））

创建空间索引：create table 表名（......
spatlal 索引名称（列名））

在已有表上添加索引

alter table 表名 add 索引类型索引名称 (列名)

使用create index语句创建索引

create 索引类型 index索引名称 on 表名（列名）

删除索引

alter table 表名 drop index 索引名

drop index 索引名 on 表名

视图

视图的概述

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成

创建视图

create view 视图名(列1，列2...)
as
select (列1，列2...)from 表名....

查看视图

desc 视图名

修改视图

create or replace view 视图名
as
select [...] from [...]

alter view 视图名
as
select.....

删除视图

drop view if exists 视图名

存储过程和函数

创建存储过程

delimiter $$
create procedure 存储过程名(参数)
begin
SQL语句
end$$
delimiter;

参数有 :“in”表示输入参数 “out”表示输出参数 “inout”表示输入输出参数格式为：in 变量名数据类型

创建存储函数

delimiter $$
create function 存储函数名(参数)
returns 返回值数据类型
begin
SQL语句
return(SQL语句)
end$$
delimiter;

参数有 :“in”表示输入参数 “out”表示输出参数 “inout”表示输入输出参数格式为：in 变量名数据类型

调用存储过程和函数

调用存储过程

call 存储过程名（变量名）
select 变量名

子主题

调用存储函数

select 存储函数名（变量）

变量

用户变量

带有前缀@,用户自定义的变量

局部变量

用于SQL语句中需要通过declare 定义

定义变量语法形式如下：declare 变量名数据类型

为变量赋值语法形式如下： set 变量名 =值

系统变量

带有前缀@@

游标

在存储过程和函数中，当查询语句返回多条记录时，可以使用游标对结果集进行逐条读取

定义游标

declare 游标名 cursor for select语句

打开游标

open 游标名

打开游标时游标指向第一天记录前边

关闭游标

close 游标名

修改存储过程和函数

delimiter $$
alter procedure 存储过程名(参数)
begin
SQL语句
end$$
delimiter;

参数有 :“in”表示输入参数 “out”表示输出参数 “inout”表示输入输出参数格式为：in 变量名数据类型

delimiter $$
alter function 存储函数名(参数)
returns 返回值数据类型
begin
SQL语句
return(SQL语句)
end$$
delimiter;

参数有 :“in”表示输入参数 “out”表示输出参数 “inout”表示输入输出参数格式为：in 变量名数据类型

删除存储过程

drop 存储过程或函数 if exists 存储过程或函数名

触发器

创建触发器

delimiter$$
create trigger 触发器名称触发时机触发事件
on
表名 for each row
begin
SQL语句
end$$
delimiter;

触发时机：“before” 检查约束前触发，“after”检查约束后触发

触发事件：insert,update,delete

查看触发器

show triggers \g

删除触发器

drop trigger 数据库名.触发器名

日志

错误日志

启动和设置错误日志添加配置文件my.ini内容

log-error=/存储路径文件名称.err #定义是否启动错误日志的功能
log-warnings={1|0} #定义是否将警告信息也记录在错误日志中

查看日志

show variables like 'log_error'

删除错误日志

flush error logs

二进制日志

启动和设置二进制日志添加配置文件my.ini内容

log_bin =存储路径/文件名
server_id =1

查看二进制文件

musqlbinlog log-file 其中log-file 为日志文件目录

删除二进制文件

reset master

输出指定日志文件

purge master logs to 日志文件名 before 日期表示删除指定时间前的日志文件

通用日志

启动和设置通用日志添加配置文件my.ini内容

genral_log=1

查看通用日志是否开启

show variables like '%general%'

删除通用日志

flush general logs

慢查询日志

启动和设置慢日志添加配置文件my.ini内容

slow_query_log=1

查看慢日志

使用记事本打开该日志文件

删除慢日志

flush slow logs

数据库的备份与恢复

数据备份

使用mysqldump备份数据库：打开命令窗口输入：mysqldump -u 用户名 -p 登陆密码数据库名

数据恢复

打开命令窗口输入：mysql -u 用户名 -p 登陆密码数据库名

表的导入导出

select ..... into outfile 文件名

使用mysqldump导出文本文件: mysqldumo -T 导出数据的目录 -u 用户名 -p 登陆密码数据库

使用mysql命令导出文本文件：mysql -u 用户名 -p 登陆密码 --execute="select 语句" 数据库名

使用load data infile 导入文本文件：load data infile '文件名' into table 表名

使用Mysqllimport导入文本文件：mysqllimport -u 用户名 -p 登陆密码数据库名文件名

权限与安全

权限表

权限表的存取
(1) 先从user表中的host、user和password这三个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝

(2) 如果通过身份验证，则按照一下权限表的顺序得到数据库权限:
user->db->tables_priv->columns_priv

在这几个表中，权限范围依次递减，全局权限覆盖局部权限
user表：全局权限
db表：次级权限

当赋予用户全局权限时，user相应条目为Y，db为空
当赋予用户局部权限时，user相应条目为N，db相应条目为Y

查看权限

查看账号权限：show grants for 用户名@主机地址

查看全局权限：select * from mysql.user where user='用户名' and host'主机地址'

查看数据库级权限：select * from mysql.db where user='用户名' and host'主机地址'

查看表级权限：select * from mysql.tables_priv where user='用户名' and host'主机地址'

账号管理

创建账号

create user 用户名@主机地址 identified by 密码创建的用户没有赋权

grant 权限类型 on 数据库.数据表 to 用户名@主机地址 identified by 密码表示创建用户同时赋予了权限

删除账号

drop user 用户名@主机地址

修改密码