MySQL进阶(MySQL学习笔记)

接上回MySQL基础篇

数据完整性约束

定义完整性约束

实体完整性

主键约束

（1）作为列的完整性约束

（2）作为表的完整性约束

2.候选键约束

将id字段和user字段设置为候选键

参照完整性

将classid字段设置为外键

用户定义完整性

非空约束

将name字段添加非空约束

CHECK约束

列约束，限制age字段的值

表约束，限制classid字段

命名完整约束

添加命名外键完整约束

更新完整性约束

删除完整性约束

删除fk-classid

修改完整性约束

更新fk-classid的外键约束为级联删除和级联更新

存储过程与存储函数

创建存储过程和存储函数

创建存储过程

创建一个存储过程

创建存储函数

创建存储函数

变量的应用

局部变量

全局变量

为变量赋值

declare var_name[...] type [default value]

set var_name=expr[,var_name=expr]..

光标应用

声明光标

declare cursor_name cursor for select_statement

打开光标

open info_of_student

使用光标

fetch info_of_student into tmp_name,temp_tel;

关闭光标

close info_of_student

调用存储过程和存储函数

调用从存储过程

调用存储函数

查看存储过程和存储函数

SHOW STATUS语句

显示所有db_database14的存储过程

show procedure status where db='db_database14';

SHOW CREATE语句

修改存储过程和存储函数

删除存储过程和存储函数

触发器

MySQL触发器

创建MySQL触发器

创建具有多条执行语句的触发器

模拟一个删除日志数据表和一个删除时间表

查看触发器

SHOW TRIGGERS语句

查看triggers表中触发器信息

select * from information_schema.triggers;

使用触发器

触发器的执行顺序

使用触发器维护冗余数据

使用触发器维护库存数量

删除触发器

事务

事务机制

事务的概念

原子性，一致性，隔离性，持久性

事务机制的必要性

实现从借记卡账户A向B转账700元，成功后再从A向B转账500元

关闭MySQL自动提交

事务回滚

实现A向B转账500，出错时进行事务回滚

事务提交

显示提交

commit

隐式提交

set autocommit=1

MySQL中的事务

在存储过程中创建事务

回退点

创建一个事务，当出现错误时，回滚到提前定义的回退点

锁机制

锁机制的基本知识

1.锁的类型

读锁（共享锁）

写锁（排他锁）

2.锁粒度

3.锁策略

（1）表级锁

（2）行级锁

4.锁的生命周期

MySAM表的表级锁

以读方式锁定数据表

以读方式锁定数据库中用户的数据表

以写方式锁定数据表

在新的窗口查询则显示失败，关闭后则显示成功

InnoDB表的行级锁

窗口二

事件

事件概述

查看事件是否开启

开启事件

1.设置全局参数

set clobal event_scheduler=ON;

更改配置文件

event_scheduler=on

创建事件

创建一个事件，用于每隔5秒向数据表tb_eventtest中插入一条数据

修改事件

修改事件，让其每隔30秒向数据表插入数据

删除事件

备份与恢复

数据备份

使用mysqldump命令备份

备份一个数据库

mysqldump -u root -p test student >E:\ student.sql

备份多个数据库

mysqldump -u root -p --database test mysql? >E:\backup.sql

备份所有数据库

mysqldump -u root -p --all -database? >E:\all.sql

直接复制整个数据库目录

show variables like '%datadir%';

数据恢复

使用mysql命令还原

mysql -u root -p <E:\all.sql

数据库迁移

mysqldump -h name1 -u root -password=password1 --all-databases |

mysql -h host2 -u root -password=password2

表的导出和导入

用select...into outfile 语句导出文本文件

实现导出信息表记录

select*from tb_bookinfo into outfile 'D:/bookinfo.txt'
? ? -> fields terminated by ?'\,' optionally enclosed by '\"'
? ? -> lines starting by '\>' terminated by '\r\n';

用mysqldump命令导出文本文件

mysqldump -u root -p -T "D:\" db_database18 tb_library "--lines-terminated-by=\r\n"

"--fields_terminated_by=," "--fields_optionally_enclosed-by="";

用mysql命令导出文本文件

mysql -u root -p --html? -e "select*from tb_bookinfo" db_database18>E:\bookinfo.html

用load data infile命令将文本文件导入数据表

将文本文件中的数据导入表中

load data infile 'E:\bookinfo.txt' into table tb_bookinfo

fields terminated by '\t'

lines terminated by '\r\n'

ignore 1 lines;

用mysqlimport命令导入文本文件

mysqlimport -u root -p? db_database18 "E:\tb_bookinfo.txt"

"--lines-terminated-by=\r\n" "--fields-terminated-by=\t" "--fields-optionally-enclosed-by=\"

MySQL性能优化

分析MySQL数据库的性能

connections：服务器连接次数

uptime：服务器上线时间

slow_queries：慢查询次数

com_select：查询操作次数

con_insert：插入操作的次数

com_delete：删除操作次数

查看服务器的连接和查询次数

通过profile工具分析语句消耗性能

优化查询

分析查询语句

索引对查询速度的影响

使用索引查询

应用关键字like优化索引查询

查询语句中使用多列索引

?create index index_book_info on tb_bookinfo(bookname,price);

查询语句中使用关键字or

select*from tb_bookinfo where bookname='MySQL从入门到精通' or price=89;

优化数据库结构

将字段很多的表分解成多个表

?desc tb_student_extra;

增加中间表

?insert into temp_student select s.id,s.name,c.name
? ? -> from tb__student s,tb_class1 c where s.classid=c.id;

优化插入记录的速度

禁用索引

alter table 表名 disable keys;

禁用唯一性检查

set unique_checks=0;

set unique_checks=1;

优化insert语句

insert into tn_food values

(null,'果冻','CC果冻',1.8,'2011','北京'),

...;

分析表、检查表和优化表

分析表

检查表

check table 表名1[，表名2...];

优化表

optimize table 表名1[,表名2...];

优化多表查询

优化学生姓名和班级名称

SELECT s.name FROM tb_student s,tb_classes c WHERE s.classid=c.id AND c.name='一年三班';

SELECT name FROM tb_student WHERE class_id=(SELECT id FROM tb_classes c WHERE name='一年三班');

权限管理及安全控制

用户和权限管理

使用CREATE USER命令创建用户

使用DROP USER命令删除用户

drop user mrsoft

使用RENAME USER命令重命名用户

使用GRANT和REVOKE命令管理访问权限

查看用户权限

设置用户权限

grant all on *.* to 'super'@'localhost'with grant option

注：本章源代码来自《MySQL从入门到精通》