MySQL的知识概要

MySQL中一些基础知识

1.数据库：

存储数据的仓库，有规律存储数据在磁盘上。

MySQL：多用户、多线程、客户机/服务器。
启动、连接、断开和停止MySQL服务器，net start/stop mysql。
 图形化工具phpMyAdmin等

2.存储引擎：

加速查询速度等。

InnoDB:良好的事务管理、读写速率稍差。
MyISAM:占用空间小、处理速度快、不支持事务的完整性和并发性。
MEMORY:表大小受限制、提供“内存表”（数据存储在内存中），处理速度快。
创建表时选最小可用的数字类型。
数据类型：数字、字符串、日期和时间。
运算符：算术、比较、逻辑;运算符优先级。

3、基本操作：

创建库：CREATE DATABASE 数据库名;
查看库：SHOW DATABASES;
使用库：USE 数据库名；
查看表结构：SHOW COLUMNS 和 DESCRIBE 
创建、修改、删除表：CREATE/ALTER/DROP TABLE ...
数据表操作：INSERT、SELECT、UPDATE、DELETE、GROUP BY 、ORDER BY 、LIKE、CONCAT等等。

4、函数：

数学：ABS(x),FLOOR(x),RAND(x)等
字符串：LENGTH(s),CONCAT(s1,s2,..),LOWER(s)等
日期和时间：CURDATE(),NOW()等
条件判断：IF(expr,v1,v2),CASE WHEN expr THEN v END等
系统信息：VSRSION(),SCHEMA()等
加密函数：PASSWORD(str),MD5(str)等
格式化函数：FORMAT(x,n)等

5、数据查询:

查询条件限制：IN,BETWEEN AND,LIKE,IS NULL,AND,OR,DISTINCT,ORDER BY,GROUP BY,LIMIT。
聚合函数查询：COUNT(),SUM(),AVG(),MAX(),MIN()。
连接查询：内连接(INNER JOIN)、外连接(LEFT JOIN、RIGHT JOIN)。
子查询：从内层开始查询，将结果赋给外层。有带IN、带比较符(如>= )、带EXISTS、带ANY(任意)、带ALL(满足所有条件)。
合并查询：UNION(去除相同记录)，UNION ALL。
正则表达式查询： 字段名 REGEXP ‘匹配方式’ （模式字符：^,$,*等等）。

6、索引：

可以加快查询速度，但创建和维护索引需要耗费时间，并且索引会占用物理空间，所以会影响用户的插入操作。

分类：普通、唯一、全文、单列、多列、空间等等。
在创建数据表时创建索引：在某个表至少一个列建立索引，以提高数据库性能。
普通索引 create table tab_name {... index(id) ... }
唯一索引 create table tab_name {... unique index tab_name(id) ... }
全局索引 create table tab_name {... fulltext key tab_name_id(id) ... }
多列索引 create table tab_name {... index info (col_a,col_b) ... }

在已建立的数据表中创建索引：
普通索引：create index 索引名 on 表名(字段名)
唯一索引：create unique index 索引名 on 表名(字段名)
全局索引：create fulltext index 索引名 on 表名(字段名)
多列索引：create index 索引名 on 表名(字段名1,字段名1)

修改索引：如
alter table 表名 add unique index 索引名(字段名)
删除索引
drop index 索引名 on 表名

7、存储过程：

一些SQL语句的集合，可减少重复相同的SQL语句，减少客户端和服务端的数据传输。

语法形式：
创建：create procedure sp_name ....
删除：drop procedure sp_name

更改结束标志可以用delimiter定义。

存储函数：
创建：create function fn_name ....

变量的应用：
局部变量：begin...end范围内有效。如declare a int 
全局变量：不必声明即可使用，在整个过程有效，以'@'开始，如：select @t;
为变量赋值 set var = expr...   或者 select col[,...] into var [,...] from table ....

光标使用：可以实现逐条读取结果集中的记录。
1)声明光标：declare cursor_name cursor for select_statement;
2)打开光标：open cursor_name
3)使用光标读取数据：fetch cursor_name into var_name[,var_name]...
4)关闭光标：close cursor_name

流程控制语句：
if语句：if condition then ... [else condition then ...] [else] ... endif
case语句(多分支结构)：case exp_value when value then ... end case 
while循环语句：while condition do ... end while 
loop循环语句：没有内置循环条件，通过leave语句退出  loop ... end loop
repeat循环语句：先执行一次循环体，再判断条件是否为真  repeat ... until condition  end repeat

调用存储过程:call sp_name ([parameter[,...]])
调用存储函数:call function_name ([parameter[,...]])

查看存储过程和函数：show {procedure | function} status {like 'pattern'}
修改存储过程和函数：alter {procedure | function} sp_name ...
删除存储过程和函数：drop {procedure | function} {if exists} sp_name 

8、触发器：

由事件来触发某个操作。这些事件包括insert语句、update语句、delete语句等等。

创建触发器：create trigger 触发器名 before | after 触发事件 on 表名 for each row 执行语句
可有多个执行语句，多个时用begin...end

查看触发器：show triggers
所有触发器都放在information_scheme库中的triggers表中。

删除触发器：drop trigger 触发器名称

9、视图：

是一个虚拟表，是从数据库中一个或多个表中导出来的表，依赖原来表中的数据。

数据库中只存放视图的定义，并没有存放视图中的数据。
优点：安全，可以隐藏一些数据;可使复杂的查询易于理解和使用。

查看是否就有创建视图的权限 ：select Select_priv,Create_view_priv from 表名 ....
创建视图：create [...] view 视图名 [(属性清单)] as select 语句 ...
查看视图：describe 视图名  或者  show table status like '视图名'
修改视图：alter view 视图名 ...
删除视图：drop view if exists 视图名

10.事务：

由单独单元的一个或多个SQL语句组成，单元的语句相互依赖。若某个语句执行失败

或产生错误，整个单元将会回滚。
事务的特性：原子性、一致性、独立性、持久性。

事务的创建
1)初始化事务：start transaction
2)创建事务
3)提交事务：commit
4)撤销事务(事务回滚):rollback

事务存在周期：从start transaction开始，直到commit结束。

控制行为：
自动提交：set autocommit=1
事务的孤立级：
serializable(序列化)、repeatable read(可重读)、read committed(提交后读)、read uncommitted(未提交读)
获取事务孤立值：select @@tx_isolation

死锁：当两个或者多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致一直
处于等待状态。MySQL的InnoDB就有检查死锁的功能，若发现死锁，就撤销其中一个事务解除死锁。

伪事务：用表锁定来替代事务的事件。MyISAM不能支持事务处理，使用表锁定来替代。
为指定数据表添加锁定：lock tables table_name lock_type ...
解除锁定：unlock tables
lock_type : read(读方式锁表)、write(写方式锁表)

11.系统管理：

mysqld(服务器)、MySQL数据目录。
数据表在文件系统中表示：每一个数据表在磁盘上至少被表示为一个文件 .frm文件 .ISD文件 .ISM文件
SQL语句映射为数据表文件操作
MySQL状态文件和日志文件：如进程ID文件、慢查询文件、错误文件等等。

12.数据库备份和还原：

为了保证数据的安全，需要定期对数据进行备份。
数据库备份：
使用mysqldump命令备份：
(1)备份一个数据库：mysqldump -u username -p dbname table1 table2 ...>BackupName.sql
(2)备份多个数据库：mysqldump -u username -p --databases dbname1 dbname2 >BackupName.sql
(3)备份所有数据库：mysqldump -u root -p --all -databases >BackupName.sql

直接复制整个数据库目录简单实现备份或还原。

数据还原：
使用mysql命令还原： mysql -u root -p[dbname] <backup.sql

数据库迁移：将数据从一个系统移到另一个系统上。
表的导出导入：使用select...into outfile ..导出文本文件  或者使用mysql命令导出文本文件。

13.MySQL性能优化：

目的是使MySQL数据运行速度更快、占用的磁盘空间更小。

优化查询：使用explain、describe分析查询语句，使用索引查询
优化数据库结构：将字段多的表分解成多个表；增加中间表；
优化插入记录的速度：禁用索引；禁用唯一性检查；优化insert语句；
查询高速缓存：select语句查询数据时，会放入高级缓存中，检查是否开启
show variables like '%query_cache%'
优化多表查询、优化表设计。

14.数据库安全：

打补丁、禁用不使用服务、关闭端口、设置用户密码。

使用grant、revoke管理访问权限。
日志文件管理