导图社区 MySQL 基础架构
- 244
- 11
- 0
- 举报
MySQL 基础架构
MySQL 基础类。包括安装到运行，到命令使用，数据库优化
编辑于2020-06-05 15:47:36
- 相似推荐
- 大纲
MySQL
1、数据库database
概念：保存有组织的数据的容器(通常是一个文件或一组文件)
数据库软件DBMS：数据库管理系统
表table
某种特定类型数据的结构化清单
列column和数据类型datatype
行row
主键primary key
定义：一列（一组列），其值能唯一区分表中每个行
满足条件：1、任意两行都不具有相同的主键值 2、每个行都必须具有一个主键值（主键不允许为null）
SQL
SQL是结构化查询语言（Structured Query Language)。SQL是一种专门用来和数据库通信的语言
MySQL简介
MySQL是一种数据库管理软件（开源）
DBMS的两种分类
一类是基于共享文件系统的DBMS如：microsoft Access、FileMaker
二类是基于客户机-服务器的DBMS如：MySQL、Oracle、microsoft SQL Server
服务器：是负责所有数据访问和处理的一个软件
客户机：是与用户打交道的软件
MySQL版本介绍（待学习）
使用MySQL（简单命令）
连接数据库
选择数据库 use database
数据库的库和表
show databases 返回可用数据库的一个列表
show tables 获得一个数据库内的表的列表
show columns from table_name 返回table_name表的列信息
MySQL支持describe table_name语句作为上述语句的快捷方式
show stasus 用于显示广泛的服务器状态信息
show create database database_name 、show create table table_name用来显示创建特定的数据库和表的MySQL语句
show grants 用来显示授予用户的安全权限
show errors、show warnings 用来显示服务器错误或警告信息
help '命令' 显示允许的命令例 help 'show'
mysql5新增要给命令information_schema 是show databases的结果，用来对整个数据库进行获取和过滤信息
检索数据select
检索单个列
检索多个列
检索所有列 *
检索不同的行 关键字 distinct
distinct 作用于所有列，而不是前置列
限制查询结果 limit a,b 、limit b offset a（MySQL专用）
select * from table_name limit 2,1 检索一行数据，从第二行开始
使用完全限定的表名
select table.column from database.table
排序检索数据 order by 数据
子句（clause）SQL由子句构成，有些是必须的，有些是可选的。一个子句通常由一个关键字和所提供的数据组成。例如 select 的from子句
order by 子句 取一个或多个列的名字，据此对输出的数据进行排列
指定排序方向
降序desc关键字
默认排序也是升序asc
order by和limit的组合
order by 子句应位于 from子句的后面，如果使用limit，它必须位于order by后面
过滤数据 where 子句
同时使用where子句和order by子句时，order by子句应在where子句之后
where子句操作符
单个值检查
范围检查 between 开始值 and 结束值 操作符
空值检查
特殊where子句 is null 来检查空值
空值和不匹配：在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有null的行
and 操作符：给where子句附加条件
or操作符：和and 区别，提示检索匹配任一条件的行
计算次序：当and和or同时存在，SQL在处理or操作符前优先处理and操作符。可使用括号进行分开处理
in操作符：指定条件范围，范围内每个条件都可进行匹配。：in的好处
显示直观
计算次序更容易管理
in操作符比or操作符执行速度更快
in 可以包含其他select语句
not操作符：否定它之后所跟的任何条件
通配符：用来匹配值的一部分的特殊字符
为搜索子句使用通配符必须使用like操作符
%通配符
任意字符出现的任意次数 like 'abc%'、'%abc%'
null 和 空格不能匹配
_ 通配符
和%的区别_只匹配单个字符
通配符技巧：通配符检索时间比其他检索耗费时间长
不要过度使用通配符
除非必要，不然不要把通配符检索放在最开始处，这是最耗费时间
正则表达式：关键字 regexp
基本字符匹配
匹配不区分大小：若需要使用关键字binary 例：where column regexp binary 'abc .000';
or 匹配：|
where column regexp '1|2|3'
匹配几个字符之一
where column regexp '[123abc]'
where column regexp '[^123abc]' :和上面的结果相反
匹配范围
[123456]、[2-6]也可以[a-z]匹配任一字母
匹配特殊字符：需要转义处理
where column regexp '\\.'
元字符：待了解
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
匹配反斜杠：\\\
匹配字符类：可以使用预定义的字符集，称为字符类
[:alnum:] 任意字母和数字 同[a-zA-Z0-9]
[:alpha:] 任意字符 同[a-zA-Z]
[:blank:] 空格和制表 同[\\t]
[:cntrl:] ASCII控制字符 ASCII 0到31和127
[:digit:] 任意数字 同[0-9]
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母 同 [a-z]
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符 同 [\\f\\n\\r\\t\\v]
[:upper:] 任意大写字母 同[A-Z]
[:xdigit:] 任意十六进制数字 同[a-fA-f0-9]
匹配多个实例
重复元字符
* 0个或多个匹配
+ 1个或多个匹配 同{1,}
? 0个或1个匹配 同{0,1}
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围 注：m不超过255
实例
where column regexp '\\([0-9] abc?\\)'
where column regexp '[[:digit:]]{4}' 同 '[0-9][0-9][0-9][0-9]'
定位符：定位元字符
^ 文本的开始
^两种用法。在集合中（用[和]定义），用它来否定集合，否则，用来指串的开始处。
$ 文本的结束
[[:<:]] 词的开始
[[:>:]] 词的结尾
regexp和like的不同在于，like匹配整个串而regexp匹配子串。利用定位符 开始^和结束$，可以使他们两个作用一样
简单正则测试：可以在不使用数据库的情况下select 来测试正则表达式。regexp检查总是返回0（没有匹配）或1（匹配）
MySQL工具
MySQL命令行使用程序
例：mysql -h localhost -u root -p
MySQL Administrator（MySQL管理器）
一个图形交互客户机，用来简化MySQL服务器的管理
MySQL Query Browser 一个图形交互客户机，编写和执行MySQL命令
子主题
创建计算机字段
字段（field）基本上与列（column）的意思相同
拼接字段：拼接（concatenate）将值连接到一起构成单个值
实现：函数concat()
例：select concat(column_name1,column_name2) from table
函数rtrim() 去除值右边的空格
例：select concat（rtirm(column_name1),rtirm(column_name2)）from table
函数ltrim() 去除值左边的空格
函数trim() 去除值左右两边的空格
别名：关键字 as
执行算数计算
算数操作符：+ - * /
使用数据处理函数
文本函数：处理文本串（删除或甜充值，转换值的大小写）
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
Trim() 去掉串左右的空格（Ltrim()、Ltrim()去左和去右）
Right() 返回串右边的字符
Soundex() 返回串的Soundex值
Upper() 将串转换为大写
数值函数：用于算数操作
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回随机数
Sin() 返回一个角度的正弦
Sprt() 返回一个数的平方根
Tan() 返回一个角度的正切
日期和时间函数：处理日期和时间值，从中提取特定成分
AddDate() 增加要给日期（天、周）
AddTime() 增加一个时间（时、分）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期计算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 返回一个日期对应的星期
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Mouth() 返回一个日期的月份
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回要给日期时间的时间部分
Year() 返回要给日期的年部分
系统函数：返回DBMS正使用的特殊信息
汇总数据
聚集函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列之和
聚集不同值：关键字DISTINCT
组合聚集函数
分组数据GROUP BY子句和HVGING 子句、ORDER BY子句
GROUP BY分组注意事项（数据分组、创建分组）
子句可以包含任意数量的列
子句的列中都必须是检索列或有效的表达式（不能是聚集函数）
如果分组列中有空值，则作为一个分组返回
子句必须在 where 子句之后 order by子句之前
HVGING 过滤分组
和where的区别，前者过滤组后者过滤行，支持所有where的操作符
分组和排序
select 子句顺序
select 要返回的列或表达式 必须
from 从中检索数据的表 必须
where 行级过滤 不必须
group by 分组说明 仅在按组计算时使用
having 组级过滤 不必须
order by 输出排序顺序 不必须
limit 要检索的行数 不必须
使用子查询
联合查询
笛卡尔积
创建高级连接
组合查询：关键字 UNION
union使用规则
必须由两条或两条以上的select语句组成
union的每个查询必须包含相同的列、表达式或聚集函数
列数据类型必须兼容
union包含或取笑重复的行
从查询结果中自动去除重复行
如果匹配所有行使用 union all
组合查询的排序
组合查询在使用order by排序时只能使用一条子句，并且在最后一个select语句后
全文本搜索
关键字：FULL TEXT 索引
使用关键子 Match()指定搜索的列 和关键字 Against()指定搜索的表达式
例如：select 索引列 from table where match(索引列) against('搜索内容')
搜索不区分大小写，除非使用binary方式
查询扩展：with query expansion
select 索引列 from table where match(索引列) against('搜索内容' with query expansion)
布尔文本搜索：boolean mode
要匹配的词：select 搜索列 from table where match(索引列) against('搜索内容' in boolean mode)
要排斥的词：select 搜索列 from table where match(索引列) against('搜索内容 -排斥的词' inboolean mode )
操作符 + 包含词不许在
- 排除，词必须不在
>包含，并且增加等级值
<包含，减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~取消一个词的排序值
*词尾的通配符
“”定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
使用说明
如果要给词出现在50%以上MySQL认定忽略这个词，不过不适用于in boolean mode
如果表中行数少于3行，全文本搜索不返回结果（50%原理）
忽略词中单引号，例如 don't 索引为 dont
不具备词分隔符
仅在MySQL中支持全文本搜索
插入数据
关键字：insert
插入完整的行
insert into table values('','') 注：不安全避免使用
安全的是 insert intotable() values() 给出对应的列和值
这种可以省略列，但是省略的列必须允许为null
定义的列需要给出默认值
性能优化：存在很多索引时 insert语句可能会很耗费性能，这时可以使用关键字 LOW_PRIORITY，指示MySQL降低insert语句的优先级：insert low_priority into ，同时使用于update和delete语句
插入行的一部分
插入多行
支持多条语句一块提交或者表和列相同的情况下可以在values后已逗号隔开多个需要插入的数据 注：此办法也是优化SQL性能
插入某些查询的结果
insert into table(列名) select 列名from table where 条件
更新和删除数据
update 更新数据两种方式 注：关键字 ignore 可以在错误发生前更新的所有行被恢复到他们原来的值update ignore
更新所有行
更新特定行
为了删除某个列的值可以更该这个列值为null
删除数据 delete是删除表中的内容而不是删除表
从表中删除特定的行
从表中删除所有的行
更快速的删除 使用truncate table速度更快（原理是删除要给表，并重新创建他，delete是逐行删除）
更新和删除的指导原则
除非打算更新和删除每一行，否则一定不要使用不带where子句的语句
保证每个表都有主键，
在对update和delete语句使用where子句前，先select测试保证过滤的是正确的记录
MySQL没有撤销功能（undo），谨慎使用update和delete
创建和操纵表
create table
主键：primary key(主键列) 语句末尾
仅在表不存在的时候创建在表名后跟：if not exists
null 值
允许：该行不给出任何值
不允许：建表时给出 not null
AUTO_INCREMENT ：设置自增列
注：每个表只允许一个自增列，且必须被索引（例：成为主键）
指定默认值：关键字 default 指定
更新表alter table注：在表里已经存在数据的时候，理想状态下，就不应该在更新表
alter table table_name add column 数据类型
alter table table_name drop column 列名
删除表：drop table
重命名表：rename table 旧表名 to 新表名
使用视图 可以对视图实行和表同样的select操作
视图是虚拟的表，和包含数据的表不同，视图只包含使用时动态检索数据的查询
为何使用视图
重用SQL语句
简化复杂的SQL操作
使用表的组成部分，而不是整个表
保护数据。可以给用户授权表的特定部分访问权限而不是整个表的访问权限
更改数据格式和显示。视图可返回和底层数据表的显示和格式不同的数据
性能问题：视图不包含数据使用前必须先执行视图里的每一个检索
视图的规则和限制
与表相同，表名唯一
对于可以创建的视图数目没有限制
创建视图需要足够的访问权限
视图可以嵌套，可以从其他视图里检索数据在构成视图
order by 可以存在视图中，但是该视图中检索数据的select中也含有order by，那么视图中的order by 将被覆盖
视图不能有索引，也不能有关联的触发器或默认值
视图可以和表一起使用
使用视图
创建 create view
查看 show create view viewname
删除 drop view viewname
更新视图：先drop 在create 或 create or replace view 如果更新的视图不存在会创建一个，如果存在会直接覆盖
更新视图
理论更新视图是需要更新基表
视图中有一下操作，不可更新
分组 group by order by having
联结
子查询
并
聚集函数
distinct 去重函数
导出计算
存储过程
基础
是为了以后的使用而保存的一条或多条SQL语句的集合
优点
使用存储过程：简单、安全、高性能
缺点
编写比SQL复杂
需要一定权限
运行存储过程
call 存储过程 (返回值1，返回值2，返回值3)
创建存储过程
语句：create procedure 存储过程_name() begin 查询语句 end;
注：分隔符delimiter
删除存储过程
drop procedure 存储过程_name
仅当存在时删除：drop procedure if exists
智能存储过程
局部变量
检查存储过程
显示一个存储过程的语句：show create procedure 存储过程_name
show procedure status 列出所有存储过程，限制输出可用like：show procedure status like '存储过程名字'
使用游标
游标：是存在MySQL数据库上的数据库查询，它不是一条select语句而是被该语句检索出来的结果集 游标主要用于交互作用，滚动屏幕上的数据，并对数据进行浏览和更该 MySQL 的游标只能用于存储过程
游标使用步骤
创建游标：使用前，进行游标定义。这个过程没有进行检索，只是定义要使用的select语句
声明之后，需要打开游标才能使用。
对于填有数据的游标根据需要检索出各行
在结束游标使用时，关闭游标
触发器MySQL5
MySQL中，除了insert delete update 其他语句不支持触发器
创建触发器注意四点
唯一的触发器名
触发器关联的表
触发器应该响应的活动
触发器何时执行（处理之前还是之后）
创建触发器
create trigger
只有表支持触发器，视图、临时表不支持
每个表只支持六个触发器
删除触发器 drop trigger
使用触发器
insert 触发器 在执行之前或之后
在insert触发器代码内，可以引用要给名为now的虚拟表，访问被插入的行
在before insert 触发器中，new中的值也可以被更新（允许更该被插入的值）
对于AUTO_INCREMENT列，new在insert 执行之前包含0，在insert执行过之后包含自动生成值
delete触发器
在delete触发器代码内可以引用一个OLD的虚拟表访问被删除的行
OLD的值全部是已读的，不能更新
update触发器
在update触发器的代码内可以引用一个OLD的虚拟表访问以前的数据（update之前的），引用一个new的虚拟表来访问更新以后的数据
在before update触发器中，new中的值可能也被更新（允许更该将要用于update语句中的值）
OLD中的值全部都是只读的，不能更新
管理事务处理
事务处理transaction processing：可以用来维护数据的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行
使用事务和事务处理时的几个术语
事务transaction：指一组SQL的语句
回退rollback：指撤销指定SQL语句的过程
提交commit：指将未存储的SQL语句结果写入数据库表
保留点savepoint：指事务处理中设置的临时占位符（placeholder），可以对它发布回退
控制事务处理
MySQL使用start transaction 来标识事务的开始
rollback命令来回退MySQL语句 注：只能在一个事务中使用，在事务开始的后面使用
commit提交事务
一般MySQL的提交是隐含的，是自动处理的，但在事务中是没有自动提交的，只能使用commit
commit 语句只在不出错的时候执行提交
当rollback或commit之后事务会自动关闭
保留点，也是占位符。创建占位符使用savepoint语句
创建保留点 savepoint 保留点名字
rollback to savepoint 保留点名字 回退到保留点
保留点在执行rollback或commit之后自动释放，也可用 release savepoint 名字 明确的释放保留点
更改默认的提交行为
set AUTOcommit =0；设置之后MySQL默认都不会自动提交，直到其值为真
全球化和本地化
安全管理
访问控制：用户应该对他们需要的数据库有适当的访问权，不能多也不能少
多数用户需要读和写，少数用户需要创建和删除
某些用户需要读表，但不需要更新表
可以能想允许用户添加数据，不允许删除数据
某些用户（管理员）需要处理用户账户的权限，但大多数用户不需要
你可能想用户通过存储过程访问数据，不是直接访问数据库
你可能想根据用户登陆地点限制某些功能的访问
管理用户
创建用户账号
简单创建：create user aen identified by '密码';
重命名 rename user : rename user aen to baen
删除 drop user baen;
设置访问权限
授予权限 grant：grant select on dbname_1.* to baen;
要授予的权限
被授予权限的数据库或表
用户名
撤销授权 revoke
被撤销的 权限必须存在，不然会出错
grant和revoke 可在几个层次上控制访问权限
整个服务器，grant all /revoke all
整个数据库，on database.*
特定的表，on database.table
特定的列
特定的存储过程
可撤销或授予的部分权限
all 除grant option 外的所有权限
alter alter table 修改表
alter routine 使用alter procedure 和drop procedure
create create table
create routine 使用 create procedure
create temporary tables 使用 create temporary table
create user使用 create user、drop user、rename user和revoke all privileges
create view 使用 create view
delete delete
drop 使用drop table
execute 使用call和存储过程
file使用 select into outfile 和 load data infile
grant option 使用grant和revoke
index 使用 create index 和drop index
lock tables 使用 lock tables
process 使用show full processlist
reload 使用 flush
replication client 服务器位置的访问
replication slave 由复制从属使用
select 使用select
show databases 使用show databases
show view 使用show create view
shutdown 使用mysqladmin shutdown（用来关闭MySQL）
SUPER 使用 change master、kill、logs、purge、master和set global。还允许MySQL调试登陆
update 使用update
usage 无权访问
更该口令
set password for baen=password('new_password');
set password = password ('password');在不指定用户名时，更新的是当前用户的口令
数据库维护
备份数据
使用命令程序mysqldump转储所有数据库内容到某个外部文件。
可用命令行使用程序mysqlhotcopy从一个数据库复制所有数据（并非所有的数据库都支持这个程序）
可使用mysql的backup table或select into outfile 转储所有数据到某个外部文件。这两个语句都接受将要创建的系统文件名，此系统文件必须不存，否则出错。数据可用restore table来复原
首先刷新末写数据：为了保证所有数据被写到磁盘上（包括索引数据），可能需要在备份前使用flush tables语句
进行数据库维护：MySQL提供了一系列的语句，用来保证数据库正确和正常运行
analyze table ：用来检查表键是否正确
check table ：用来针对许多问题对表进行检查
如果访问表产生不正确和不一致的结果，可能需要repair table 来修复相应的表。注：这个语句不应该经常使用，如果使用过多，那就是有更大的问题要解决
如果从一个表中删除大量的数据，应该使用optimize table来收回所用的空间，从而优化表的性能
诊断启动问题
在排除系统问题时，首先应尽量手动启动服务器。在命令行上执行mysqld启动
--help 显示帮助 一个选项列表
--safe-mode 装在减去某些最佳配置的服务器
--verbose显示全文本信息（获取更详细的帮助信息与--help 联合使用）
--version 显示版本信息然后退出
查看日志文件：MySQL维护管理员依赖的一系列日志文件
错误日志：包含启动和关闭问题以及任意关键错误的细节。通常命名为hostname.err，位于data目录。此日志名可用--log-error命令行选项进行更该
查询日志：它记录所有MySQL活动，此日志会非常大，不应该长期使用。日期通常命名为hostname.log位于data目录。可用--log命令行选项修改
二进制日志：记录更新过数据（或可能更新过数据）的所有语句。通常命名为hostname-bin，位于data目录下。可用--log-bin命令行选项更改。MySQL5后添加的
缓慢查询日志：记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。名为hostname-slow.log，位于data目录中。可用--log-slow-queries 命令行选项更改
改善性能
硬件、和专用服务器
MySQL一般开始是一些默认配置，但是过一段时间过后需要调整内存分配、缓冲区大小等。（查看当前设置，show variables；和show status；）
MySQL是多线程多用户的DBMS，经常执行多个任务时，某一个执行缓慢，则所有的执行都会缓慢。如果碰到显著的性能不良，使用show processlist显示所有活动进程（以及他们的线程ID和执行时间）还可以用kill命令终极某个特定的进程（这个命令需要作为管理员才可以）
select 优化，尝试不同的方法来简化SQL
使用explain语句让MySQL解释如何执行一条select语句
一般存储过程执行比一条一条语句执行速度快
数据类型使用正确，确保不会浪费内存
避免检索比需求要多的数据 例如 *
导入数据时应关闭自动提交。可能会删除索引后在导入数据在建立索引
必须索引数据库表用来改善数据检索的性能。分析select语句找出重复的where、order by子句。如果一个简单的where子句返回结果花费的时间太长，断定使用的列就是检索的对象
select语句中碰到一系列的or条件，通过多条select语句和连接它们的union语句，能看到极大的性能改进
索引可以改善数据的检索性能，但是会损害数据插入、删除和更新的性能。索引可根据需要增加或删除
like很慢。一般最好使用fulltext而不是like
数据库是不断变化的实体，一开始的优化好的表也会改变
子主题