MySQL

阿里云社区https://developer.aliyun.com/mirror

目录

一:数据库

1.1

information_schema:主要存储系统中的数据库对象信息,
用户表信息、列信息、权限信息、字符信息、分区信息。
performance_schema :主要存储数据库服务器性能参数。
mysql:存储系统的用户权限信息
test:自动创建的测试数据库。

二: MySQL数据库基本操作

2.1 创建数据库:

create database '名字';
如:create database jay; 

2.2 使用某个数据库:

use jay;

2.3 删除数据库:

drop database '名字';
如:drop database jay;

2.4 查询支持的存储引擎

SHOW ENGINES;

2.5 创建表:

 create table table_name ( 
 属性名 数据类型, 
 属性名 数据类型,
 ...
 属性名 数据类型,
 ); --'table_name':所要创建的表的名字。

2.6 查看表结构:

describe table_name;
如:desc |describe jay;

2.7 查看表结构详细信息:

show create table table_name;
如:show create table jay \G;

2.8 删除表:

drop table table_name;
如:drop table jay;

三:表的操作

3.1 修改表名字:

alter table old_table_name RENAME 【to】new_table_name;

3.2 增加字段:

alter table table_name ADD 属性名 数据类型;

3.2.1 在表的第一个位置增加字段:

alter table table_name ADD 属性名 数据类型 FIRST;

3.2.2在表的指定字段之后增加字段

alter table table_name ADD 属性名 数据类型 FIRST 属性名;

3.3 删除字段:

alter table table_name DROP 属性名;

3.4 修改字段:

alter table table_name MODIFY 属性名 数据类型;

3.5 修改字段的名字:

alter tabel table_name CHANGE 旧属性名 新属性名 旧数据类型;

3.6 同时修改字段的名字和属性:

alter table table_name CHANGE 旧属性名 新属性名 新数据类型;

3.7 修改字段的顺序:

alter table table_name MODIFY 属性名1 数据类型 
FIRST|AFTER 属性名2
PS:属性名1 、属性名2 必须是表中已存在的字段名。

四:操作表的约束:

4.1 设置非空约束:(NOT NULL ,NK)

CREATE TABLE table_name (
 属性名 数据类型 NOT NULL ,
 ... ...
 );
PS:NOT NULL 保证所有记录中该字段都有值,用户插入记录时,
 如果为空,则会报错;

4.2 设置字段的默认值:(DEFAULT)

CREATE TABLE table_name (
 属性名 数据类型 DEFAULT 默认值,
 ... ...
 );
PS : 用户插入一条新纪录时,没有为某个字段赋值,
 数据库会自动为此地段插入默认值;

4.3 设置唯一约束(UNIQUE ,UK)

 CREATE TABLE table_name (
 属性名 数据类型 UNIQUE L,
 ... ...
 );
PS:数据库表中的某个字段的内容不允许重复时,使用UK进行约束。

4.3.1 给某字段上的UK约束设置一个名字:

 CREATE TABLE t_dept (
 属性名1 数据类型,
 属性名2 数据类型
 ... ...
CONSTRAINT uk_属性名1 UNIQUE(属性名1)
 );

4.4 设置主键约束:

4.4.1 单字段主键:

 CREATE TABLE t_dept(
 属性名 数据类型 PRIMARY KEY
 ... ...
 );
4.4.1.1 给字段上的PK约束设置一个名字:
 CREATEB TABLE t_dept(
 属性名1 数据类型,
 CONSTRAINT pk_属性名1 PRIMARY KEY(属性名1)
 ); 

4.4.2 多字段主键:

 CREATE TABLE t_dept(
 属性名 数据类型,
 ... ...
 CONSTRAINT 约束名 PRIMARY KEY(属性名1,属性名2,...)
 ); --约束名指的是:pk_dname_deptno 

4.5 设置字段值自动增加(AUTO_INCREMENT)

CREATE TABLE t_dept(
 属性名 数据类型 AUTO_INCREMENT,
... ...
);

4.6 设置外键约束(FOREING KEY ,FK)

CREATE TABLE t_dept(
 属性名 数据类型,
 属性名 数据类型
 ... ...
CONSTRAINT 外键约束名 FOREING KEY(属性名1)
 REFERENCES 表名(属性名2)
 ); --PS:外键约束名:用来标识约束名,
 --属性名1:参数是子表设置外键的字段名,
 --属性名2:参数是父表中设置主键约束的字段名。

六: 索引的操作:


索引是创建在数据库表对象上的,由表中的一个字段或多个字段生成的键组成,
这些键存储在数据结构(B-树或哈希表) 
索引分为:B型树索引(BTREE)哈希索引(HASH) 
InnoDB和MyISAM存储引擎支持BTREE类型索引
MEMORY 存储引擎支持HASH类型索引
MySQL 默认为前者索引;
MySQL 支持6种索引:普通索引、唯一索引、全文索引、
单列索引、多列索引、空间索引
索引优点:提高查找速度,保证字段的唯一性。
缺点:过多的创建索引会占据许多磁盘空间

6.1 创建和查看索引:

6.1.1 创建表时创建普通索引:
 CREATE TABLE t_dept(
 属性名 数据类型,
 属性名 数据类型,
 ... ...
 属性名 数据类型,
 INDEX|KEY【索引名】(属性名1 【(长度)】【ASC|DESC】)
 );
 PS:INDEX|KEY 参数:指定字段为索引,
 '索引名'参数:指定所创建索引名,
 '属性名1':指定索引所关联的字段的名称,
 '长度': 指定索引的长度,
 ASC|DESC :用来指定为 升序|降序。
 **
 CREATE TABLE t_dept(
 deptno INT,
 dname VARCHAR(20),
 loc VARCHAR(40),
 INDEX index_deptno(deptno)
 );
PS: MySQL 支持的储存引擎每个至少支持16个索引,
 总索引长度至少256字节。
6.1.1.2:效验索引:
 校验索引是否创建成功,EXPLAIN:
 EXPLAIN SELECT * FROM t_dept WHERE deptno =1 \G;
6.1.1.2 在已存在的表上创建普通索引:
 CREATE INDEX 索引名 ON 表名(属性名【(长度)】【ASC|DESC】)
PS: CREATE INDEX 创建索引,‘索引名’:创建的索引名
 ON:所要创建索引的表名称
6.1.1.3 通过SQL语句ALTER TABLE 创建普通索引:
 ALTER TABLE tanle_name ADD INDEX|LEY 
 索引名(属性名 【(长度)】【ASC|DESC】)
PS: INDEX|LEY:创建普通索引
 '索引名'所创建索引名
 '属性名'指定索引关联的字段的名称
 '长度'指索引的长度
 ASC|DESC :用来指定为 升序|降序 

6.1.2 创建和查看唯一索引:

6.1.2.1 创建表时创建唯一索引:
 CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ... ...
 属性名 数据类型,
 UNIQUE INDEX|KEY 【索引名】(属性名1 
 【(长度)】【DESC|ASC】);
 PS:UNIQUE INDEX 或 UNIQUE KEY 表时创建唯一索引。
6.1.2.2 在已经存在的表上创建唯一索引:
 CREATE UNIQUE INDEX 索引名 ON 表名(属性名
 【(长度)】【ASC|DESC】)
6.1.2.3 用SQL 语句ALTER TABLE 创建唯一索引:
 ALTER TABLE table_name ADD UNIQUE INDEX |KEY 
 索引名(属性名【(长度)】【ASC|DESC】)

6.1.3 创建和查看全文索引:

6.1.3.1:创建表时创建全文索引:
 CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ... ...
 属性名 数据类型,
 FULLTEXT INDEX|KEY 【索引名】(属性名1 【(长度)】【ASC|DESC】)
 ENGINE =MyISAM|InnoDB
 ); -- FULLTEXT INDEX或FULLTEXT KEY 表示创建全文索引
6.1.3.2 在已经存在的表上创建全文索引
 CREATE FULLTEXT INDEX 索引名 
 ON 表名(属性名【(长度)】【ASC|DESC】);
 CREATE FULLTEXT INDEX 关键字表创建全文索引
6.1.3.3 通过SQL语句ALTER TABLE 创建全文索引
 ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 
 索引名(属性名【(长度)】【ASC|DESC】);

6.1.4 创建和查看多列索引

6.1.4.1 创建表时创建多列索引
 CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ... ...
 属性名 数据类型,
 INDEX|KEY 【索引名】(属性名1 【(长度)】【ASC|DESC】,
 ... ...
 属性名n 【(长度)】【ASC|DESC】)
 ); --创建索引时,所关联的字段至少大于一个字段
6.1.4.2在已存在的表中创建多列索引
 CREATE INDEX 索引名
 ON 表名 (属性名 【(长度)】【ASC|DESC】,
 ... ...
 属性名n 【(长度)】【ASC|DESC】
 );
如: CRETAE INDEX index_loc
 ON t_dept (dname,loc)
 --index_loc:索引名
 --t_dept :表名
 --dname、loc :属性名
 SHOW CREATE TABLE table_name \G;
6.1.4.3 SQL语句ALTER TABLE 创建多列索引
 ALTER TABLE table_name 
 ADD INDEX|KEY 索引名 (属性名 【(长度)】【ASC|DESC】,
 ... ...
 属性名n 【(长度)】【ASC|DESC】
 );
如:ALTER TABLE t_dept 
 ADD INDEX index_dname_loc(dname,loc);
 SHOW CREATE TABLE t_dept \G;

6.2 删除索引

6.2.1 删除索引的语句形式

DROP INDEX index_name ON table_name;
 -- index_name:索引名字
 --table_name : 表对象
效验索引是否被使用:
 EXPLAIN SELECT * FROM t_dept WHERE dname='cjgong' \G;
删除索引:
 DROP INDEX index_dname_loc ON t_dept;
效验是否还存在索引:

SHOW CREATE TABLE t_dept \G;

七 视图的操作

特点:
 1.视图的列可来自不同的表,是表的抽象和在逻辑意义上建立的新关系
 2.视图是由基本表(实表)产生的表(虚表)
 3.视图的建立和删除不影响基本表
 4.对视图内容的更新(添加、删除和修改)直接影响基本表
 5.当视图来自多个基本表时,不允许添加和删除数据

7.1 创建视图的语法形式:

 CREATE VIEW view_name AS 查询语句;
 --视图不能重名
 --视图的功能实际上是封装了复杂的查询语句
如:数据库view 水果产物表 t_product 
 创建隐藏价格字段price的视图selectproduct
选择数据库:
 USE view;
创建视图:
 CREATE VIEW view_selectproduct
 AS 
 SELECT id,name FROM t_product;
查询视图操作:
 SELECT * FROM view_selectproduct;
创建各种视图
如:现有两张分别表示学生和组的表t_student和t_group
1.1 封装实现查询常量语句的视图,即所谓的常量视图
 CREATE VIEW view_test1 AS SELECT 3.1415926;
1.2 封装使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图
CREATE VIEW view_test2 AS SELECT COUNT(name) 
FROM t_student;
1.3 封装了实现排序功能(ORDRE BY)查询语句的视图
CREATE VIEW view_test3 AS SELECT name FROM t_student 
ORDER BY id DESC;
1.4 封装了实现表内连接查询语句的视图
CREATE VIEW view_test4 AS SELECT s.name 
FROM t_student as s,t_group as g WHERE s.group_id=g.id
AND g.id=2; 
1.5封装了实现表外连接(LEFT JOIN 和 RIGHT JOIN)查询语句的视图
CREATE VIEW view_test5 AS SELECT s.name FROM 
t_student as s LEFT JOIN t_group as g ON s.group_id=g.id 
WHEREg.id=2;
1.6 封装了实现子查询相关查询语句的视图
CREATE VIEW view_test6 AS SELECT s.name 
FROM t_student as s WHERE s.group_id IN (SELECT id 
FROM t_group);
1.7封装了实现记录联合(UNION和UNION ALL)查询语句的视图
CREATE VIEW view_test7 AS SELECT id,name 
FROM t_student UNION ALL SELECT id,name FROM t_group;

7.2 查看视图

7.2.1 SHOW TABLES 语句查看视图名

如: USE view;
 SHOW TABLES;

7.2.2 SHOW TABLE STATUS 语句查询视图详细信息

语法: SHOW TABLE STATUS 【FROM db_name】【LIKE 'pattern'】
 如:查询view数据库的视图和表的详细信息
 SHOW TABLE STATUS FROM view \G 
 返回信息解释如下:
 - 字段名 含义
 - Name 表和视图的名
 - Engine 标的存储引擎(在MySQL4.1.2之前,用type 表示)
 - Version 表的.frm 文件的版本号
 - Row_format 表的行存储格式
 - Rows 表中行的数目
 - Avg_row_length 表中行平均行长度
 - Data_length 表数据文件的长度
 - Max_data_length 表数据文件的最大长度
 - Index_length 表索引文件的长度
 - Data_free 表被整序后,但是未使用的字节的数目
 - Auto_increment 表中下一个AUTO_INCREMENT值
 - Create_time 表的创建时间
 - Update_time 表的最后一次更新时间
 - Check_time 表的最后一次检查的时间
 - Collation 表的字符集
 - Checksum 表的活性校验
 - Create_options 表的额外选项
 - Comment 表的注解
SHOW TABLE 可以查看名为select_product视图的详细信息
 SHOW TABLE STATUS FROM view LIKE 
 'view_select_product' \G;

7.2.3 SHOW CREATE VIEW 语句查询视图定义信息

 SHOW CREATE VIEW viewname;
viewname:参数表示为所要查看定义信息的视图名称

7.2.4 DESCRIBE | DESC 语句查看视图设计信息

DESCRIBE | DESC viewname;

7.2.5通过系统表查看视图信息

系统表information_schema.views
 USE information_schema;
 SELECT * FROM views WHERE 
 table_name='view_select_product'\G;

7.3 删除视图的语法形式

DROP VIEW view_name 【,view_name】...
如:删除一个视图表:
 DROP VIEW view_selectproduct
 删除多个视图表:
 DROP VIEW view_dselectproduct1,
 view_selectproduct2,...;

7.4 修改视图

7.4.1 CREATE OR REPLACE VIEW 语句修改视图

7.4.1.1 已存在视图,但是有新的需求,可以先删除原视图,在建立视图:
 DROP VIEW view_selectproduct;
 CREATE VIEW view_selectproduct AS SELECT name FROM t_product;
7.4.1.2 但先删除视图,在建立视图比较麻烦。可用如下语句进行实现替换视图:
 CREATE OR REPLACE view view_name AS 查询语句;
 CREATE OR REPLACE view view_selectproduct 
 AS SELECT name FROM t_product;

7.4.2 ALTER 语句修改视图

 ALTER VIEW view_name AS 查询语句;
 ALTER VIEW view_selectproduct AS SELECT name FROM t_product;

7.5 利用视图操作基本表

7.5.1 检索(查询)数据:

 SELECT * FROM view_selectproduct;

7.5.2 利用视图操作基本表数据

7.5.2.1 添加数据操作
 INSERT INTO view_product (id,name,price,order_id)
 VALUES(11,'PEAR4',12.3,2);
 或者INSERT INTO view_product VALUES(11,'PEAR4',12.3,2);

7.5.2.2 删除数据操作

 DELETE FROM view_product WHERE name='apple1';

7.5.2.3 更新数据操作

 UPDATE view_product SET price=3.5 WHERE name='pear1';

八 触发器

 触发器与编程语言的函数非常类似,都需要声明、执行,
 且触发器的执行是由事件来触发。
 使用触发器可以加强数据库表中数据的完整性约束和业务规则等。

8.1 创建触发器

8.1.1 创建一条执行语句的触发器

CREATE TRIGGER trigger_name BEFORE|AFTER trigger_EVENT 
 ON table_name FOR EACH ROW trigger_STMT;

PS :
trigger_name :表示所要创建的触发器的名字
BEFORE|AFTER :指定触发器执行的时间(触发器事件之前
 执行|触发器事件之后执行)
trigger_EVENT:表触发事件,即触发器执行条件包含
 DELETE、INSERT、UPDATE语句。
teble_name : 表示触发事件操作表的名字
FOR EACH ROW :表示任何一条记录上的操作满足触发时间
 都会触发该触发器。
trigger_STMT:表示激活触发器后被执行的语句

如:两行表:部门表(t_dept)、日记表(t_diary),创建触发器实
现向部门表(t_dept)插入记录时,就会在插入之前向日记表
(t_diary)中插入当前时间;如下:
 1.查看:
 DESCRIBE t_dept; DESCRIBE t_diary;
 2.创建触发器(tri_diarytime):
 CREATE TRIGGER tri_diarytime BEFORE 
 INSERT ON t_dept FOR EACH ROW
 INSERT INTO t_diary VALUES(NULL,'t_dept',now());
 3.效验:
 INSERT INTO t_dept VALUES(1,'cjgongdept','SHangXi');
 SELECT * FROM t_diary;

8.1.2 创建包含多条执行语句的触发器

 CREATE TRIGGER trigger_name BEFORE|AFTER trigger_EVENT
 ON table_name FOR EACH ROW BEIN trigger_STMT END 

 PS:为了解决“;”问题,用SELIMITER关键字,如:“DELIMITER$$”,
 可以用来实现将结束符号设置为“$$”.
 如:两行表:部门表(t_dept)、日记表(t_diary),创建触发器实现向
 部门表(t_dept)插入记录时,
 就会在插入之前向日记表(t_diary)中插入两条记录;如下:
 1.查看:
 DESCRIBE t_dept; DESCRIBE t_diary;
 2.创建触发器(tri_diarytime):
 DELIMITER $$
 CREATE TRIGGER tri_diarytime2 AFTER INSERT ON 
 t_dept FOR EACH ROW
 BEGIN
 INSERT INTO t_diary VALUES(NULL,'t_dept',now());
 INSERT INTO t_diary VALUES(NULL,'t_dept',now());
 END
 $$
 DELIMITER;
 PS:上述语句中,首先用“DELIMITER $$”语句设置结束符号为“$$”,
 后再关键字BEGIN和END之间编写执行语句列表,
 最后通过“DELIMITER;”语句将结束符号还原为默认结束符号“;”
 3.效验:
 INSERT INTO t_dept VALUES(1,'cjgongdept','SHangXi');
 SELECT * FROM t_diary;

8.2 查看触发器

8.2.1 通过SHOW TRIGGERS 语句查看触发器


错误提示:“ERROR 1359(HY000) Trigger already exits”:触发器已存在。
 “ERROR 1235(42000)”:具有相同触发程序动作时间和事件的给定表,
 不能存在两个触发器。

 SHOW TRIGGERS \G ; 
  •  Trigger 表示触发器的名称
    
  •  Event 表示胡发起的激活事件
    
  •  Table 表示触发器对象触发事件所操作的表
    
  •  Student 表示触发器激活执行时所执行的语句
    
  •  Timming 表示触发器所执行的时间
    

8.2.2 通过查看系统表triggers 实现查看触发器

 DESC triggers;
 
 USE information_schema;
 SELECT * FROM triggers \G ;
 查询指定触发器的详细信息:
 SELECT * FROM TRIGGERS WHERE TRIGGER_NAME
 ='tri_diarytime2' \G;

8.3 删除触发器

8.3.1 通过DROP TRIGGER 语句删除触发器

 DROP TRIGGER trigger_name;
 SHOW TRIGGERS \G;

九 数据的操作

9.1 插入数据记录

9.1.1 插入完整数据记录

 INSERT INTO table_name(field1,field2,field3,...fieldn)
 VALUES(value1,value2,value3...valuen);
 table_name:表示所要插入完整记录的表名
 fieldn:表示表中的字段名字
 value:表示所要插入的数值
 最后参数filedn与参数valuen一一对应;
如:
 #查看部门表t_dept:
 DESCRIBE t_dept;
 #插入完整数据记录:
 INSERT INTO t_dept(deptno,dname,loc)VALUES(1,'cjgong1','shanghai');
 #校验部门表t_dept中数据记录是否插入成功:
 SELECT * FROM t_dept;
 插入完整数据记录还可以省略字段参数:
 INSERT INTO table_name VALUES(value1,value2,value3,...valuen);
 INSERT INTO t_dept VALUES(2,'cjgong2','shanghai2');
 SELECT * FROM t_dept;

9.1.2插入数据记录一部分

 INSERT INTO table_name(field1,field2,field3,...fieldn)
 VALUES(value1,value2,value3...valuen);
 如:
 INSERT INTO t_dept(dname,loc)VALUES('cjgong1','shanghai');
 #校验部门表t_dept中数据记录是否插入成功:
 SELECT * FROM t_dept;

9.1.3插入多条数据记录

1.插入多条完整数据记录
 INSERT INTO table_name(field1,field2,field3,...fieldn)
 VALUES(value11,value21,value31...valuen1),
 (value12,value21,value32...valuen2),
 (value13,value23,value33...valuen3),
 ...
 (value1m,value2m,value3m...valuenm),
 或者
 INSERT INTO table_name
 VALUES(value11,value21,value31...valuen1),
 (value12,value21,value32...valuen2),
 (value13,value23,value33...valuen3),
 ...
 (value1m,value2m,value3m...valuenm),
 m:参数表示一次插入m条完整数据记录

如:
 一次插入五条完整数据记录:
 DESCRIBE t_dept;
 INSERT INTO t_dept
 VALUES(1,'cjgongdept1','shanghai1'),
 VALUES(2,'cjgongdept2','shanghai2'),
 VALUES(3,'cjgongdept3','shanghai3'),
 VALUES(4,'cjgongdept4','shanghai4'),
 VALUES(5,'cjgongdept5','shanghai5');
 SELECT * FROM t_dept;
2.插入多条部分数据记录
 INSERT INTO table_name(field1,field2,field3,...fieldn)
 VALUES(value11,value21,value31...valuen1),
 (value12,value21,value32...valuen2),
 (value13,value23,value33...valuen3),
 ...
 (value1m,value2m,value3m...valuenm),
 如:
 INSERT INTO t_dept(dname,loc)
 VALUES('cjgongdept1','shanghai1'),
 VALUES('cjgongdept2','shanghai2'),
 VALUES('cjgongdept3','shanghai3'),
 VALUES('cjgongdept4','shanghai4'),
 VALUES('cjgongdept5','shanghai5');
 SELECT * FROM t_dept;

9.1.4 插入查询结果

 INSERT INTO table_name1(field1,field2,
 field3,...fieldn)
 SELECT (field21,field22,field23,...) 
 FROM table_name2
 WHERE...
 table_name1:参数表示所要插入数值的表
 table_name2:参数表示所要插入数值是哪个表查询出来
 参数(field11,field12,field13...fieldn):表示
 表table_name1中所要插入值的字段
 参数(field21,field22,field23...field2n):表示
 表table_name2中所查询值的字段
 如:
 向部门表t_dept插入表t_loader中关于字段dname和loc的查询结果:
 #查询各个表的信息:
 DESCRIBE t_dept;
 DESC t_loader;
 #查询t_loader中的数据记录:
 SELECT * FROM t_loader;
 #插入查询结果的数据记录:
 INSERT INTO t_dept(dname,loc) SELECT dname,loc 
 FROM t_loader;
 #校验部门表t_dept中数据记录是否插入成功
 SELECT * FROM t_dept;

9.2 更新数据记录

9.2.1 更新特定数据记录

 UPDATE table_name SET field1=value1,field2=value2,
 field3=value3,WHERE CONDITION;
 table_name:参数表示所要更新数据记录的表名
 field:表示表中所要更新数值的字段名字
 value:表示更新后的数值
 CONDITION:指定更新满足条件的特定数据记录
 如:
 在部门表t_dept中使名称(字段dname)为cjgongdept1部门的地址(字段loc)
 由shanghai1更新为shanghai2:
 DESCRIBE t_dept;
 SELECT * FROM t_dept;
 UPDATE t_dept SET loc='shanghai2' WHERE dname='cjgongdept1';
 SELCT * FROM t_dept;

9.2.2 更新所有数据记录

 UPDATE table_name SET field1=value1,field2=value2,field3=value3,
 WHERE CONDITION;
 如:
 在部门表t_dept中使所有数据记录中部门地址(字段loc)都更新为shanxi8:
 DESCRIBE t_dept;
 SELECT * FROM t_dept;
 UPDATE t_dept SET loc='shangxi8'WHERE deptno<6;
 SELECT * FROM t_dept;
 注意:在执行UPDATE时,如果没有关键字WHERE的语句,将更新所有的数据记录:
 如:UPDATE t_dept SET loc='shangxi9';
 SELECT * FROM t_dept;

9.3 删除数据记录

9.3.1 删除特定数据记录

 DELETE FROM table_name WHERE CONDITION
 table_name:参数表示所要删除数据记录的表名
 CONDITION:指定更新满足条件的特定数据记录
 如:在部门表(t_dept)中删除名字(字段dname)为cjgongdept1的部门:
 DESCRIBE t_dept;
 SELECT * FROM t_dept;
 DELETE FROM t_dept WHERE dname='cjgongdept1';
 SELECT * FROM t_dept;

9.3.2 删除所有数据记录

 DELETE FROM table_name WHERE CONDITION
 如:在部门表(t_dept)中删除所有数据记录:
 DESCRIBE t_dept;
 SELECT * FROM t_dept;
 DELETE FROM t_dept WHERE deptno<6;
 SELECT * FROM t_dept;
 注意:如果没有关键字WHERE的语句,将删除所有的数据记
 DELETE FROM t_dept;
 SELECT * FROM t_dept;

十 单表数据记录查询

10.1 简单数据记录查询

 SELECT field1 field2 ... fieldn FROM table_name;
 fieldn 表示所要查询的字段名字
 table_name 表示查询数据记录的表名
单表数据查询遵循原则:
 1.简单数据查询
 2.避免重复数据查询
 3.实现数学四则运算数据查询
 4.设置显示格式数据查询

10.1.1 简单数据查询

1. 查询数据字段数据
 SELECT empno,job,MGR,Hiredate,sal,comm,deptno 
 FROM t_employee;
2."*"符号的使用
 SELECT * FROM table_name;
 SELECT * FROM t_employee;
3.查询指定字段数据
SELECT empno,ename,sal FROM t_employee;

10.1.2 避免重复数据查询---DISTINCT

SELECT DISTINCT field1 field2 ...fieldn FROM table_name;
SELECT DISTINCT job FROM t_employee;

10.1.3 实现数学四则运算数据查询

 - + 加法
 - - 减法
 - * 乘法
 - /(DIV) 除法
 - %(MOD) 求余

如:SELECT ename ,sal*12 FROM t_employee;
语法:SELECT field1 [AS] otherfield1,field2 [AS] otherfield2, 
... fieldn [AS] otherfieldn FROM table_name;
SELECT ename ,sal*12 AS yearsalary FROM t_employee;
或者
SELECT ename ,sal*12 yearsalary FROM t_employee;

10.1.4 设置显示格式数据查询

SELECT CONCAT(ename,'雇员的年薪为: ',sal*12)yearsalary
FROM t_employee;

10.2 条件数据记录查询

SELECT field1 field2 ... fieldn FROM table_name 
WHERE CONDITION
上述语句中通过参数CONDITION对数据进行条件查询:
带关系运算符和逻辑运算符的条件数据查询
带 BETWEEN AND 关键字的条件数据查询
带 IS NULL 关键字的条件数据查询
带 IN 关键字的条件数据查询
带 LIKE 关键字的条件数据查询

10.2.1 带关系运算符和逻辑运算符的条件数据查询

 比较运算符:
 - 运算符 描述
 - > 大于
 - < 小于
 - = 等于
 - !=(<>) 不等于
 - >= 大于等于
 - <= 小于等于
 逻辑运算符
 - AND(&&) 逻辑与
 - OR(||) 逻辑或
 - XOR 逻辑异或
 - NOT(!) 逻辑非
1.单条件数据查询
SELECT ename FROM t_employee WHERE job='CLERK';
或者
select ENAME from T_EMPLOYEE where JOB='clerk';
2.多条件数据查询
SELECT ename FROM t_employee WHERE job='CLERK'
&& sal=800;
或者
SELECT ename FROM t_employee WHERE job='CLERK'
AND sal=800;

10.2.2 带BETWEEN AND 关键字的范围查询

SELECT field1 field2 ...fieldn FROM table_name 
WHERE field BETWEEN VALUE1 AND VALUE2;
1.符合范围的数据记录查询
 SELECT ename FROM t_employee WHERE sal
 BETWEEN 1000 AND 2000;
2.不符合范围的数据记录查询
 SELECT ename FROM e_employee WHERE sal 
 NOT BETWEEN 1000 AND 2000;

10.2.3 带IS NULL 关键字的空值查询

 SELECT field1 field2 ...fieldn FROM table_name
 WHERE field IS NULL;
1 空值数据记录查询
 SELECT ename FROM t_employee WHERE sal IS NULL;
2.不是空值数据记录查询
 SELECT ename FROM t_employee WHERE comm IS NOT NULL;

10.2.4 带IN 关键字的集合查询

 SELECT field1 field2 ...fieldn FROM table_name 
 WHERE field IN(value1,value2,value3...,valuen);
 PS:value 表示集合中的值,关键字IN来判断字段field 的值是否在集合
 (value1,value2,value3...,valuen)中如果field的值在集合中,
 则满足查询条件,即被查询出来,否则不会被查询出来。
1 在集合中数据记录查询
 SELECT ename FROM t_employee WHERE empno=7521 
 or empno=7782 or empno=7566 or empno=7788;
 或者
 SELECT ename FROM t_employee WHERE empno 
 IN(7521,7782,7566,7788);
3.关于集合查询注意点
 1.查询的集合中如果存在NULL,则不会影响查询,如果使用关键字
NOT IN 查询集合中存在NULL,则不会有任何的查询结果。
如1:SELECT ename FROM t_employee IN (7521,7782,7566,7788,NULL);
如2: SELECT ename from t_employee NOT IN 
 (7521,7782,7566,7788,NULL);

10.2.5 带LIKE关键字的模糊查询

 PS:所谓通配符,主要是用来实现匹配部分值得特殊字符。
 SELECT field1,field2...fieldn FROM table_name WHERE
 field LIKE value;
 PS value:表示所匹配的字符串值,通过LIKE来判断字段field的值是否与
 value字符串相匹配,
 如果字段field的值与value值相匹配,则满足查询条件,
 记录就会别查出来,否则不会被查出来。
通配符
“_”通配符,该通配符值能匹配单个字符。
“%”通配符,该通配符值可以匹配任意长度的字符串,既可以是0个字符,
1个字符,也可是多个字符。
1.带"%"通配符的查询
 SELECT ename FROM t_employee WHERE ename LIKE 'A%';
 或者
 SELECT ename FROM t_employee WHERE ename LIKE 'a%';
 查询不是以字母A开头的全部雇员可以执行逻辑运算符(NOT或!):
 SELECT ename FROM t_employee WHERE NOT ename LIKE 'A%';
2.带"_"通配符的查询
 查询字段ename中第二个字母为A的数据记录:
 SELECT ename FROM t_employee WHERE ename LIKE '_A%';
 查询名字第二个不是以字母A开头的全部雇员可以执行逻辑运算符(NOT或!):
 SELECT ename FROM t_employee WHERE NOT ename LIKE '_A%';
3.带LIKE关键字的模糊查询的注意点
 SELECT field1,field2...fieldn FROM table_name WHERE
 field【NOT】LIKE value;
 如:查询字段ename没有字母A的数据记录:
 SELECT ename FROM t_employee WHERE ename NOT LIKE '%A%';
 如:查询工资中带有5的全部雇员:
 SELECT ename FROM t_employee WHERE sal LIKE '%5%';

10.3 排序数据记录查询

 SELECT field1,field2...fieldn FROM table_name WHERE
 CONDITION ORDER BY fileldm1 [ASC|DESC] [,fileldm2 [ASC|DESC]]
 PS: fileldm 表示按照该字段进行排序
 ASC 表示升序排序,DESC 表示降序排序
 ORDER BY:设置多个不同字段进行排序
排序数据记录查询方法
 1.按照单字段排序
 2.按照多字段排序

10.3.1 按照单字段排序

1.升序排序
 SELECT * FROM t_employee ORDER BY sal ASC;
 或者
 SELECT * FROM t_employee ORDER BY sal;
 PS:mysql中ORDER BY 默认的排序顺序为升序。
2.降序排序
 SELECT *FROM t_employee ORDER BY mgr DESC;
 PS:如果字段的值为空值(NULL),则该值为最小值,
 因此在降序中最后一行显示,升序中第一行显示

10.4 限制数据记录查询数量

 SELECT field1,field2...fieldn FROM table_name WHERE
 CONDITION LIMIT OFFSET_START,ROW_COUNT;
 PS:LIMIT:限制数据查询结果数量
 OFFSET_START:表示数据记录的起始偏移量
 ROW_COUNT:表示显示的行数

初始位置的方式

 1.不指定初始位置方式
 2.制定初始位置方式

10.4.1 不指定初始位置

 关键字LIMIT,如果不指定初始位置,默认值为0,
 表示从一条疾苦开始显示。
 语法:LIMIT row_cunt;
 row-count:表示显示roe_count 条数据查询结果数量。
 如:row_count值小于查询结果的总数量,
 将会从第一条数据记录开始,显示row_count条数据记录,
 如果row_count值大于查询结果的总数量,将会显示所有查询结果。
1.显示记录数小于查询结果
 如:查询不领奖金(comm)的所有雇员,且只显示2条记录:
 SELECT *FROM t_employee WHERE comm is NULL LIMIT 2;
2.显示记录数大于查询结果
 如:符合字段comm值为NULL的数据记录有10条,此时,
 设置关键字LIMIT的row_count大于10时:
 SELECT * FROM t_employee WHERE comm is NULL LIMIT 11;
 查询显示为10,因为查询总数量才是10.

10.4.2 指定初始位置

如:查询不领奖金(comm)的所有雇员,后排序根据入职时间(hiredate)
 进行从早到晚排序同时分两次进行显示,
 第一次从第一条记录开始显示,工显示5条记录,
 第二次从第6条记录显示,共5条记录。
 SELECT * FROM t_employee WHERE comm is 
 NULL ORDER BY hiredate LIMIT 0,5;
 由于参数OFFSET_START的值默认为0:
 SELECT * FROM t_employee WHERE comm is 
 NULL ORDER BY hiredate LIMIT 5;
 实现第二次操作:
 SELECT * FROM t_employee WHERE comm is 
 NULL ORDER BY hiredate LIMIT 5,5;

10.5 统计函数和分组数据记录查询

 1.COUNT()函数:该统计函数实现统计表中的记录的条数。
 2.AVG()函数:该函数实现计算字段值的平均值。
 3.SUM()函数:该函数实现计算字段值得总和。
 4.MAX()函数:该函数实现查询字段值得最大值。
 5.MIN()函数:该函数实现穿字段值的最小值。

10.5.1 MySQL支持的统计函数

 SELECT function(field) FROM table_name WHERE CONDITION;
 该语句利用统计函数function来统计关于字段field的值。
1.统计数据记录条数
 COUNT()函数:
 COUNT(*):实现对表中记录进行统计,不管字段中包含的时
 NULL值还是非NULL值.
 COUNT(field):实现对指定字段的记录进行统计,
 在统计时将忽略NULL值。
 如:获取雇员的人数:
 SELECT COUNT(*) number FROM t_employee;
 如:获取领取奖金的雇员人数:
 SELECT COUNT(comm) number FROM t_employee;
 此时查出的记录没有忽略值为0的记录,不合符实际要求,因此:
 SELECT COUNT(comm) number FORM t_employee 
 WHERE NOT comm=0;
2.统计计算平均值
 AVG(field):实现对指定字段的平均值进行计算,
 具体统计时将忽略NULL值。
 如:计算领取奖金雇员的平均值:
 SELECT AVG(comm) average FROM t_employee;
 此时查出的记录没有忽略值为0的记录,不合符实际要求,因此: 
 SELECT AVG(comm)average FROM t_employee WHERE NOT comm=0; 
3.统计计算求和
 SUM(field):实现计算指定字段值之和,具体统计时将会忽略NULL值。
 如:计算雇员的工资(sal)总和:
 SELECT SUM(sal) sumvalue FROM t_employee;
 如:计算雇员的奖金(comm)总和: PS:忽略NULL值
 SELECT SUM(comm)sumvalue FROM t_employee;
 此时查出的记录没有忽略值为0的记录,不合符实际要求,因此:
 SELECT SUM(comm) sunvalue FROM t_employee WHERE NOT comm=0;
4.统计计算最大值和最小值
 MAX(field):实现计算指定字段值中的最大值。在具体统计时忽略NULL值。
 MIN(field):实现计算指定字段值中的最小值,在具体统计时忽略NULL值。
 SELECT MAX(sal) maxval,MIN(sal) minval FROM t_employee;
 如:因为忽略NULL值,既可以统计雇员领取奖金的最大值和最小值。
 SELECT MAX(comm) maxval,MIN(comm) minval FROM t_employee;
 此时查出的记录没有忽略值为0的记录,不合符实际要求,因此:
 SELECT MAX(comm) maxval,MIN(comm) minval FROM t_employee 
 WHERE NOT comm=0;

10.5.2 关于统计函数的注意点

 1.对于MySQL,如果所操作的表中没有任何数据记录,则COUNT()
 函数返回数据0,而其他函数则返回NULL。
 如:SELECT COUNT(deptno) number FROM t_dept;
 如:SELECT AVG(deptno) avgage,SUM(deptno) summer,
 MAX(deptno) maxval,MIN(deptno) minval FROM t_dept;

10.5.3 分组数据查询————简单分组查询

 SELECT function() FROM table_name WHERE 
 CONDITION GROUP BY field;
 PS:参数field对数据记录进行分组;
 如:按照部门号(deptno)对所有雇员进行分组。
 SELECT * FROM t_employee GROUP BY deptno;

10.5.4 分组数据查询————实现统计功能分组查询

 SELECT GROUP_CONCAT(field) FROM table_name WHERE
 CONDITION GROUP BY field;
 如:按照部门号(deptno)对所有雇员进行分组,同时显示
 每组雇员名(ename)和每组雇员的个数:
 执行SQL语句GROUP_CONCAT(),显示每个分组中hiding的字段值:
 SELECT deptno,GROUP_CONCAT(ename) enames 
 FROM t_employee GROUP BY deptno;
 执行统计函数COUNT(),显示每个分组中雇员的个数:
 SELECT deptno,GROUP_CONCAT(ename) enames,
 COUNT(enane) number FROM t_eployee GROUP BY deptno;

10.5.5 分组数据查询————实现多个字段分组查询

 SELECT GROUP_CONCAT(field),function(field) FROM 
 table_name WHERE CONDITION GROUP BY field1,field2,...fieldn;
 PS:按照字段field1进行分组,后针对每组按照字段field2进行分组,
 以此类推;
 如:雇员表(t_employee),首先按照部门号(deptno)对所有雇员进行分组,
 后按照雇用日期(hiredate)对每组进行分组,同时显示
 每组中的雇员名(ename) 和个数:
 1.执行SQL语句GROUP BY ,按照字段deptno进行分组:
 SELECT deptno FROM t_employee GROUP BY deptno;
 2.执行SQL语句GROUP BY 按照字段deptno和hiredate进行分组:
 SELECT deptno,hiredate FROM t_employee 
 GROUP BY deptno,hiredate;
 3.执行SQL语句GROUP_CONCAT()和统计函数COUNT(),
 显示浓眉哥雇员名称和雇员个数:
 SELECT deptno,hiredate,GROUP_CONCAT(ename) enames,
 COUNT(ename) FROM t_employee GROUP BY deptno,hiredate;

10.5.6 分组数据查询————实现HAVING子句限定分组查询

 SELECT function(field) FROM table_name WHERE CONDITION 
 GROUP BY field1,field2,...fieldn HAVING CONTITION;
 通过HAVING关键字来指定分组后的条件;
 如:雇员表(t_employee)按照部门号(ename)对所有雇员进行分组,
 后显示平均工资高于2000的雇员名字:
 1.执行SQL语句GROUP BY ,按照字段deptno 进行分组:
 SELECT deptno FROM t_employee GROUP BY deptno;
 2.执行统计函数AVG(),显示每组中的平均工资:
 SELECT deptno ,AVG(sal) average FROM t_employee 
 GROUP BY deptno;
 3.执行SQL语句HAVING、GROUP_CONCAT()和统计函数COUNT(),
 显示平均工资大于2000的每个分组中雇员名称和雇员个数:
 SELECT deptno,AVG(sal) average,GROUP_CONCAT(ename)
 enames,COUNT(ename) number
 FROM t_employee GROUP BY deptno HAVING AVG(sal)>2000;
 说明:函数AVG()获取每个部门的平均工资,函数GROUP_CONCAT()
 显示每个部门的雇员名和函数COUNT()统计出每个部门雇员人数,
 最后关键字HAVING进行条件的限制。

十一 多表数据记录查询

11.1 关系数据操作

11.1.1 并(UNION)

 "并":将具有相同字段数目和字段类型的表整合到一起:
 如:计算机学生表(t_cstudent)和音乐系学生表(t_mstudent),
 将两张表的护具记录合并在一起,即并新关系的字段数为2(表t_cstudent
 字段数或表t_mstudent字段数),而并新关系的记录数为6
 (表t_cstudent记录数)+6(表t_mstudent记录数)-1(表t_cstudent
 和表t_mstudent重复记录数)=11;

11.1.2 笛卡尔积(CARTESIAN PRODUCR)

笛卡尔积:没有连接条件表关系返回的结果。
如:部门表(t_dept) 雇员表(t_employee) 笛卡尔积返回的结果
 为:前3个字段来自表t_dept,后7个字段来自表t_employee,即笛卡尔积
 新关系的字段为3(表t_dept字段数)+7(表t_employee字段数)=10,
 而该笛卡尔积新关系的记录数为4(表t_dept记录数)X 14
 (表t_employee记录数)=56;

11.1.3 内连接(INNER JOIN)

  • 内连接(INNER JOIN)

  • 外连接(OUTER JOIN)

  • 交叉连接(CROSS JOIN)

    内连接(INNER JOIN):在表关系的笛卡尔积数据记录中,保留表关系
    中所有匹配的数据记录,舍弃不匹配的数据记录。
    按照匹配的条件分为: 自然连接、等值连接、不等连接。
    
1.自然连接(INNER JOIN)
 表关系的笛卡尔积中,首先根据表关系中相同名称的字段自动进行
 记录匹配,后去掉重复的字段。
 -再具体执行自然连接时,会自动判断相同名称的字段,后进行数据值的匹配
 -在纸箱玩自然连接的新关系中,虽可以指定包含哪些字段,
 但不能指定执行过程中的匹配条件,即哪些字段值进行匹配
 -在执行自然连接的新关系中,执行过程中所匹配的字段名只有一个,
 即会去掉重复字段
2.等值连接
 表关系笛卡尔积中,选择所匹配字段值相等(=符号)的数据记录。
3.不等连接
 表关系笛卡尔积中选择所匹配字段值不相等(!=符号)的数据记录。

11.1.4 外连接(OUTER JOIN)

 表关系的笛卡尔积数据记录中,不仅保留表关系中所有匹配的数据记录,
 还会变流部分不匹配的数据记录。
 按照保留不匹配条件数据记录来源分为:左外连接(LEFT OUTER JOIN)、
 右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)
1.左外连接(LEFT OUTER JOIN)
 表关系的笛卡尔积中,除了选择相匹配的数据记录,
 还包含关联左边表中不匹配的数据记录。
2.右外连接(RIGHT OUTER JOIN)
 表关系的笛卡尔积中除了选择相匹配(相等)的数据记录,
 还包含关联右边表中不匹配的数据记录。
3.全外连接(FULL OUTER JOIN)
表关系的笛卡尔积中,除了选择相匹配(相等)的数据记录,
还包含关联左右边表中不匹配的数据记录。

11.2 内连接查询

 SELECT field1 field2 ... fieldn FROM 
 join_tablename1 INNER JOIN join_tablename2 
 【INNER JOIN join_tablenamen】ON join_condition
 PS: 
 field:表示要查询的字段名字,来源于所连接的表join_tablename1
 和join_tablename2
 INNER JOIN:表示表进行内连接
 join_condition:表示进行匹配的条件
 -等值连接
 -不等连接

11.2.1自连接

 自连接:指表与其自身进行连接
 如:执行SQL语句“INNER JOIN...ON”,查询每个雇员的
 姓名、职位、领导姓名
 分析:
 1.确定需要查询的表和查询字段的来源
 两张表:雇员表(t_employee),领导表(t_employee)
 2.确定关联匹配的条件
 t_employee.mgr(雇员表的领导编号)=t_employee.empno(领导表的领导编号)
 1.执行SQL语句SELECT,查询每一位雇员的姓名和职位:
 SELECT e.ename employeename,e.job FROM t_employee e;
 2.修改上述SQL语句,为查询中引入领导表,同时添加一条消除笛卡尔积的匹配条件:
 SELECT e.ename employeename,e.job,l.ename loadername 
 FROM t_employee e INNER JOIN t_employee l ON e.mgr=l.empno;
 PS:语句中,设置笛卡尔积的匹配条件为"e.mgr=l.empno".
 3.SQL语句采用ANSI连接语法形式,通过“SELECT FROM WHERE”关键字:
 SELECT e.ename employeename,e.job,l.ename loadername 
 FROM t_employee e , t_employee l WHERE e.mgr=l.empno;
 PS:为WHERE关键字设置匹配条件“e.mgr=l.empno”
 PS:当表的名字特别长,直接使用表明很不方便,或者是在实现表自连接时,
 直接使用表明没办法区分表:
 SELECT field1,field2,...fieldn [AS] otherfieldn FROM 
 table_name1 [AS] other_table1,...table_namen [AS] other_table_namen
 PS:table_name:表原来的名字
 other_table_name:新表的名字
 如:SELECT e.ename employeename ,e.job FROM t_employee e;
 SELECT e.ename employeename,e.job FROM t_employee AS e;

11.2.2 等值连接

 等值连接:就是在关键字ON后的匹配条件中通过关系运算符(=)来实现等值条件;
 如: 查询每个雇员的编号、姓名、职位、部门名称、位置;
 实例分析:
 (1).确定需要查询的表和查询字段的来源:
 根据需求为两张表,部门表(t_dept)、雇员表(t_employee),
 前者查询部门的名称和位置,后者查询雇员的编号、姓名和职位。
 (2) 确定关联的匹配条件
 1.查询雇员的编号、姓名和职位:
 SELECT e.empno,e.ename,e.job FROM t_employee;
 (3) 新修改SQL语句,引入	部门表,同时添加消除笛卡尔积的匹配条件:
 SELECT e.empno,e.ename,e.job,d.dname,d.loc 
 FROM t_employee e 
 INNER JOIN t_dept d ON e.deptno=d.deptno;
 (4)采用ANSI 连接语法形式为:(SELECT FROM WHERE 实现)
 SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM 
 t_employee e ,t_dept d WHERE e.deptno=d.deptno;
11.2.2.1 多张表等值连接(三张表)
 如:查询每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称、位置。
 实例分析:
 (1)确定需要查询的表和查询字段的来源
 三张表:部门表(t_dept)、雇员表(t_employee)、领导表(t_employee)
 雇员表:雇员的编号、姓名、基本工资和职位
 领导表:领导的姓名
 部门表:部门的名称和位置
 (2)确定关联匹配条件
 领导表连接部门表匹配条件:t_employee.deptno=t_dept.deptno
 雇员表连接领导表匹配条件:t_employee.mgr=t_employee.deptno
 实现步骤:
 1.查询每位雇员的编号、姓名、基本工资和职位:
 SELECT e.empno,e.ename,employeename,e.sal,e,job
 FROM t_employee;
 2.修改SQL语句,引入领导表,消除笛卡尔积的匹配条件:
 SELECT e.empno,e.ename employeename,e.sal,e.job,
 l.ename loadername 
 FROM t_employee e INNER JOIN t_employee l 
 ON e.mgr=l.empno;
 3.修改SQL语句,引入部门表,消除笛卡尔积的匹配条件:
 SELECT e.empno,e.ename employeename,e.sal,e.job,
 l.ename loadername,
 d.dname,d.loc FROM t_employee e 
 INNER JOIN t_employee l 
 ON e.mgr=l.empno INNER JOIN t_dept d
 ON l.deptno=d.deptno;
 4.采用ANSI连接语句形式为:(SELECT FROM WHERE 实现)
 SELECT e.empno,e.ename empoyeename,e.sal,
 e.job,l.ename 
 loadername,d.dname,d.loc FROM t_employee e ,
 t_employee l,t_dept d 
 WHERE e.mgr=l.empno AND l.deptno=d.deptno;

11.2.3 不等连接

 在关键字ON后的匹配条件中通过使用关系运算符“>”、
 “>=”、“<”、“<=”和“!=”等
 如:查询雇员编号大于其领导编号的每个员工的姓名、职位、领导姓名
 分析:
 1.确定需要查询的表和所查询字段的来源
 两张表:雇员表(t_employee)和领导表(t_employee),前者查询雇员
 的姓名和职位,后者查询领导姓名。
 2.确定关联匹配条件
 t_employee.mgr(雇员表的领导编号)=t_employee.empno(领导表的领导编号)
 t_employee.empno(雇员表的雇员编号)>t_employee.empno(领导表的领导编号)
 步骤:
 1.查询每位雇员的姓名和职位:
 SELECT e.ename employee,e.job FROM t_employee e;
 2.修改SQL语句,因为领导表,消除笛卡尔积的匹配条件:
 SELECT e.ename employee,e.job,l.ename loadername FROM t_employee e 
 INNER JOIN t_employee l ON e.mgr=l.empno AND e.empno>l.empno;
 3.采用ANSI连接语法方式:(关键字SELECT FORM WHERE 实现)
 SELECT e.ename employee,e.job,l.ename loadername FROM 
 t_employee e,t_employee l WHERE e.mgr=l.empno AND e.empno>l.empno;

11.3 外连接查询

 SELECT field1 field2 ...fieldn FROM join_tablename1
 LEFT|RIGHT|FULL 
 [OUTER] JOIN join_table2 ON join_condition;
 参数field:表示要查询的字段名字
 join_tablename1和join_tablename2:表示来源于所连接的表
 OUTER JOIN :表示进行外连接
 join_condition:表示进行匹配的条件
外连接分为三类:
 1.左外连接
 2.右外连接
 3.全外连接

11.3.1 左外连接

 指新关系中执行匹配关系时,以关键字LEFT JOIN 左边的表为参考表。
 如:查询KING的雇员的姓名、职位、领导姓名,由于KING为最高位,没有领导信息
 实例分析:
 1.确定查询的表和所查询字段的来源
 两张表:雇员表(t_employee)和领导表(t_employee)前者查询雇员的
 姓名和职位,后者查询领导的姓名。
 2.确定关联匹配条件 
 t_employee.mgr(雇员表的领导编号)=t_employee.empno(领导表的领导编号)
 步骤:
 1.查询每位雇员的姓名和职位:
 SELECT e.ename employee,e.job FROM t_employee e;
 2.修改SQL语句,引入领导表,消除笛卡尔积的匹配条件:
 SELECT e.ename employee,e.job,l.ename loadername 
 FROM t_employee e LEFT JOIN t_employee l ON e.mgr=l.empno;
 3.修改SQL语句为等值连接的内连接:
 SELECT e.ename employee,e.job,l.ename loadername FROM 
 t_employee e INNER JOIN t_employee l ON e.mgr=l.empno;

11.3.2 右外连接

 指新关系中执行匹配条件时,以关键字RIGHT JOIN 右边的表为参考表
 如:查询每位雇员的编号、姓名、职位、部门名称、位置、雇员cjgong所在
 的部门编号50在部门表中不存在,所以该雇员信息没有部门信息。
 实例分析:
 1.确定需要查询的表和所查询字段的来源。
 两张表:部门表(t_employee)和雇员表(t_employee),前者查询部门
 的名称和位置,后者查询雇员的编号、姓名和职位。
 2.确定关联匹配条件
 t_dept.deptno=t_employee.deptno
 步骤:
 1.查询每位雇员的姓名和职位:
 SELECT e.empno,e.ename,e.job FROM t_employee e;
 2.修改SQL语句,引入部门表,同时消除笛卡尔积的匹配条件:
 SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM 
 t_dept d RIGHT JOIN t_employee e ON e.deptno=d.deptno;
 3.修改SQL语句为等值连接的内连接:
 SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM 
 t_employee e, t_dept d WHERE e.deptno=d.deptno; 

11.4 合并查询数据记录

 SELECT field1 field2 ...fieldn FROM tablename1 UNION |UNION 
 ALL SELECT field1 field2 ...fieldn FROM tablename2 UNION |UNION 
 ALL SELECT field1 field2 ...fieldn FROM tablename3 ... ...
1.带有关键字UNION的合并操作
 UNION会把查询结果直接合并在一起,同时将会去掉重复数据记录。
 实例:合并计算机系和音乐系学生的数据记录
 SELET * FROM t_cstudent UNION SELECT * FROM t_mstudent;
2.带关键字UNION ALL 的合并操作
 UNION ALL 会把查询结果直接合并在一起,但不会去掉重复的数据记录。
 SELET * FROM t_cstudent UNION SELECT * FROM t_mstudent;

11.5 子查询

 为什么使用子查询???
 如:SELECT * FROM t_dept t,t_employee l WHERE t.deptno=l.deptno;
 此SQL语句首先会对两个表进行笛卡尔积操作,后选择符合匹配条件的数据记录,
 但如果数据量大,则会在笛卡尔积操作时造成死机。
 所谓子查询,就是指在一个子查询中嵌套了其他的若干查询,即在SELECT 查询语句的
 WHERE或FROM子句中包含另一个SELECT查询语句。
 外层SELECT查询语句成为主查询,WHERE子句中SELECT 查询语句被称为子查询,
 也被称为嵌套查询
 通过子查询可以实现多表查询。可能包含IN、ANY、ALL、EXISTS等关键字,
 还可能包含比较运算符。
 理论上子查询可以出现在查询语句的任意位置。实际开发中,子查询经常出
 现在WHERE和FROM子句中。
 - WHERE子句中的子查询:该位置处的子查询一般返回单行单列、
 多行单列、单行多列数据记录
 - FROM子句中的子查询:该查询处的子句查询一般返回多行多列数据记录,
 可以当做临时表。

11.5.1 返回结果为单行单列和单行多列子查询

1.返回结果为单行单列子查询
 如:SELECT sal FROM t_employee WHERE ename='SMITH';
 由于SQL语句返回单行单列,因此可在主查询WHERE关键字子句中出现
 SELECT * FROM t_employee WHERE sal>(SELECT sal FROM 
 t_employee WHERE ename='SMITH');
2.单行多列子查询
 SELECT sal,job FROM t_employee WHERE ename='SMITH';
 由于SQL语句返回单行多列,因此可在主查询WHERE关键字子句中出现 
 SELECT ename,sal,job FROM t_employee WHERE (sal,job)=
 (SELECT sal,job FROM t_employee WHERE ename='SMITH');

11.5.2 返回结果为多行单列子查询

1.带有关键字IN的子查询
 当查询的条件是子查询的查询结果时,可以通过关键字IN来进行判断,
 相反查询的条件进不是子查询的查询结果时,用NOT IN来进行判断
 SELECT * FROM t_employee WHERE deptno
 IN(SELECT deptno FROM t_dept);
 由于雇员cjgong所在的部门不再部门表中,查询cjgong的详细信息:
 SELECT * FROM t_employee WHERE deptno 
 NOT IN(SELECT deptno FROM t_dept);
2.带有关键字ANY的子查询
 关键字ANY表示主查询的条件为满足子查询返回查询结果中
 任意一条数据记录,分为三种:
 - =ANY:其功能与关键字IN一样
 - >ANY(>=ANY):比子查询返回数据记录中最小的还要大于(大于等于)数据记录;
 - <ANY(<=ANY):比子查询返回数据记录中最大的还要小于(小于等于)数据记录;
 SELECT sal FROM t_employee WHERE job='MANAGET';
 由于SQL语句返回多行单列,可以在主查询WHERE关键字中出现:
 实例:查询职位为MANAGET的雇员的工资信息:
 SELECT ename,sal FROM t_employee WHERE sal>ANY(SELECT sal 
 FROM t_employee WHERE job='MANAGET');
 实例:查询职位MANAGET或该职位以上的雇员姓名和工资:
 SELECT ename,sal FROM t_employee WHERE sal>=ANY(SELECT sal 
 FROM t_employee WHERE job='MANAGET');
3.带有关键字ALL的子查询
 - >ALL(>=ALL): 比子查询中返回数据记录中最大的还要大于(大于小于)数据记录。
 - <ALL(<=ALL):比子查询中返回数据记录中最小的还要小于(大于等于)数据记录。
 实例:查询职位为MANAGET的雇员的工资信息:
 SELECT sal FROM t_employee WHERE job='MANAGET';
 SELECT ename,sal FROM t_employee WHERE sal>ALL(SELECT sal 
 FROM t_employee WHERE job='MANAGET');
 实例:查询雇员职位MANAGET中最大值或该职位以上的工资:
 SELECT ename,sal FROM t_employee WHERE sal>=ALL
 (SELECT sal FROM t_employee WHERE job='MANAGET');
4.带有关键字EXISTS的子查询
 实例:查询部门表(t_dept)中的字段部门编号(deptno)和部门名字(depname),
 如果该部门没有员工,则显示该部门:
 SELECT * FROM t_employee a,t_dept c WHERE a.deptno=c.deptno;
 由于SQL语句返回多行单列,可以在主查询WHERE关键字中出现:
 SELECT * FROM t_dept c WHERE NOT EXISTS(SELECT * FROM 
 t_employee WHERE deptno=c.deptno);
 如需显示出有雇员的部门编号和名称
 SELECT * FROM t_dept c WHERE EXISTS(SELECT * FROM 
 t_employee WHERE deptno=c.deoptno);

11.5.3 返回结果为多行多列子查询

 实例:查询雇员表(t_employee)中和部门的部门号、部门名称、
 部门地址、雇员人数和平均工资:
 SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) number,
 AVG(e.sal) average FROM t_employee e INNER JOIN t_dept d 
 ON e.deptno=d.deptno GROUP BY d.deptno DESC,d.name,d.loc;
 统计记录数:
 SELECT COUNT(*) number FROM t_employee e,t_dept d;
 由于查询结果返回的结果为多行多列:
 SELECT d.deptno,d.dname,d.loc,number,average FROM
 t_dept d INNER JOIN (SELECT deptno dno,
 COUNT(empno) number,AVG(sal)average FROM t_employee
 GROUP BY deptno DESC) employee ON d.deptno=employee.dno;
 SELECT COUNT(*) number FROM t_dept d,(SELECT deptno dno,
 COUNT(empno) number,AVG(sal) average FROM t_employee 
 GROUP BY deptno) employee;

十二 使用MySQL运算符

12.1 使用算术运算符

 -运算符 描述 表达式形式
 - + 加法 x1+x2+x3+...+xn
 - - 减法 x1-x2-x3-...xn
 - * 乘法 x1*x2*x3*...xn
 - /(DIV) 除法 x1/x2/x3/.../xn | x1DIVx2DIVx3DIV...DIVxn
 - %(MOD) 求余 x1%x2%x3%...%xn | x1MODx2MODx3MOD...MODxn
 SELECT 6+4 加法操作
 6-4 减法操作
 6*4 乘法操作
 6/2 除法操作
 6 DIV 2 除法操作
 6%4 求模操作
 6 MOD 4 求模操作
 SELECT ename 雇员,sal 月工资,sal*12 年薪 FROM t_employee;
 除运算符(/和DIV)和求模运算符(%和MOD),
 如果除数为0将是非法运算,返回结果为NULL:
 SELECT 6/0 除法操作
 6 DIV 0 除法操作
 6%0 求模操作
 6 MOD 0 求模操作

12.2 使用比较运算符

 -运算符 描述 表达式形式 
 - > 大于 x1>x2
 - < 小于 x1<x2
 - = <=> 等于 x1=x2 x1<=>x2
 - !=(<>) 不等于 x1!x2 x1<>x2
 - >= 大于等于 x1>=x2
 - <= 小于等于 x1<=x2
 
 - BETWEEN AND 存在于指定范围 x1 BETWEEN m AND n
 - IS NULL 为空 x1 IS NULL
 - IN 存在于指定集合 x1 IN(value1,value2,value3...valuen)
 - LIKE 通配符匹配 x1 LIKE expression
 - REGEXP 正则表达式匹配 x1 REGEXP regularexpression

12.2.1 常用比较运算符

 SELECT 1=1 数值比较,
 'cjgong'='cjgong' 字符串比较,
 1+2=3+3 表达式比较,
 1<=>1 数值比较,
 'cjgong'<=>'cjgong'字符串比较,
 1+2<=>3+3 表达式比较
 SELECT NULL <=>NULL '<=>符号效果',
 NULL =NULL '=符号效果';
 SELECT 1<>1 数值比较,
 'cjgong'<>'cjgong'字符串比较,
 1+2<>3+3 表达式比较,
 1!=1 数值比较,
 'cjgong'!='cjgong'字符串比较,
 1+2!3+3 表达式比较; 
 PS:与“=”和“<=>”比较运算符相比,“!=”和“<>”这两个比较
 运算符不能操作NULL(空值);
 SELECT 1>=1 数值比较,
 'cjgong'>='cjgong' 字符串比较,
 1+2>=3+3 表达式比较,
 1>1 '>符号使用',
 'cjgong' <='cjgong' as '<=符号使用',
 1+2<3+3 '<符号使用';

12.2.2 实现特殊功能比较运算符

 -模式字符 含义
 - ^ 匹配字符串的开始部分
 - $ 匹配字符串的结束部分
 - . 匹配字符串中的任意一个字符
 - [字符集合] 匹配字符集合中的任意一个字符
 - [^字符集合] 匹配字符集合外的任意一个字符
 - str1|str2|str3 匹配str1、str2和str3中的任意一个字符串
 - * 匹配字符,包含0个和1个
 - + 匹配字符,包含1个
 - 字符串{N} 字符串出现N次
 - 字符串(M,N) 字符串出现至少M次,最多N次
SELECT 'cjgong' REGEXP '^c' 特定字符开头,
 'cjgongcjgong' REGEXP '^cjgong' 特定字符串开头;
SELECT 'cjgong' REGEXP 'g$' 特定字符结尾,
 'cjgongcjgong' REGEXP 'cjgong$' 特定字符串结尾;
SELECT 'cjgong' REGEXP '^c...g$' 匹配4个任意字符;
SELECT 'cjgong' REGEXP '[abc]' 指定字符中字符,
 'cjgong'REGEXP '[a-zA-Z]' 指定字符中的集合区间,
 'cjgong'REGEXP '[^abc] 指定字符外字符, 
 'cjgong'REGEXP '[^a-zA-Z0-9]' 指定字符外籍和区间;
SELECT 'cjgong'REGEXP 'a*g','cjgong'REGEXP 'a+g';
SELECT 'cjgong'REGEXP 'cjg' 指定单个字符串,
 'cjgong'REGEXP 'cjg|cjgc' 指定多个字符串;
PS:当指定多个字符串时,需使用“|”模式字符进行隔开,每个字符串与“|”
 之间不能有空格,因为MySQL会将空也当做一个字符。
SELECT 'cccjgong'REGEXP 'c{3}' 匹配3个c,
 'cccjgong'REGEXP 'g{2}' 匹配2个g,
 'cgong' REGEXP 'cj{1,2}' 至少1个最多2个,
 'cjcjgong' REGEXP 'cj{1,2}' 至少1个最多2个;

12.3 使用逻辑运算符

 -运算符 描述 表达式形式
 - AND(&&) 与 x1ANDx2
 - OR(||) 或 x1ORx2
 - NOT(!) 非 NOTx1
 - XOR 异或 x1XORx2
 
 SELECT 3 AND 4,0 AND 4,0 AND NULL,3 AND NULL,3 && 
 4,0 && 4,0 && NULL,3 && NULL;
 SELECT 3 OR 4,0 OR 4,0 OR 0,0 OR NULL,3 OR NULL,3 
 || 4,0 || 4,0 || 0, 0 || NULL,3 || NULL \G;
 SELECT NOT 3,NOT 0,NOT NULL,!3,!0,!NULL;
 SELECT 3 XOR 4,0 XOR 0,NULL XOR NULL,0 XOR 
 NULL,0 XOR NULL, 3 XOR NULL;

12.5 使用位运算符

 -运算符 描述 表达式形式
 - & 按位与 x1&x2
 - | 按位或 x1|x2
 - ~ 按位取反 ~x1
 - ^ 按位异或 x1^x2
 - << 按位左移 x1<<x2 
 - >> 按位右移 x1>>x2
 SELECT 5|6 , BIN(5|6) 二进制数,
 4|5|6,BIN(4|5|6) 二进制数;
 SELECT ~4,BIN(~4) 二进制数;
 SELECT 4^5,BIN(4^5) 二进制数;
 SELECT BIN(5)二进制数,5<<4,BIN(5<<4) 二进制数,
 5>>1,BIN(5>>1) 二进制数;

十三 使用MySQL常用函数

13.1 使用字符串函数

 - 字符串函数:该函数主要用于处理字符串;
 - 数值函数:该函数主要用于处理数字;
 - 日期函数:该函数主要用于处理日期和事件;
 - 系统信息函数:该函数主要用于获取MySQL软件的系统信息; 
 
 - 函数 功能
 - CANCAT(str1,str2...strn) 连接字符串str1,str2...strn为一个完成字符串
 - INSERT(str,x,y,instr) 将字符串str从x位置开始,y个字符长的子串替换为字符串instr
 - LOWER(str) 将字符串str中所有的字符变为小写
 - UPPER(str) 将字符串str中所有的字符变为大写
 - LEFT(str,x) 返回字符串str中最左边的x个字符
 - RIGHT(str,x) 返回字符串str中最右边的x个字符
 - LPAD(str,n,pad) 用字符串pad对字符串str最左边进行填充,知道长为n个字符长度
 - RPAD(str,n,pad) 用字符串pad对字符串str最右边进行填充,知道长为n个字符长度
 - LTRIM(str) 去掉字符串str左边的空格
 - RTRIM(str) 去掉字符串str右边的空格
 - REPEAT(str,x) 返回字符串str重复x次的结果
 - REPLACE(str,a,b) 使用字符串b替换字符str中所有出现的字符串a
 - STRCMP(str1,str2) 比较字符串str1和str2
 - TRIM(str) 去掉字符串str行头和行尾的空格
 - SUBSTRING(str,x,y) 返回字符串str中从x位置起y个字符长度的字符串

13.1.1 合并字符串函数CONCAT()和CONCAT_WS()

 CONCAT(S1,S2...Sn) 
 PS:将传入的参数连接起来返回所合并的字符串类型数据,
 如果其中一个参数为NULL,则返回值为NULL;
 SELECT CONCAT('My','S','QL') 合并后的字符串;
 SELECT CONCAT('My','S','QL',NULL) 合并后的字符串;
 SELECT CONCAT(CURDATE(),12,34) 合并后的字符串;PS:参数CURDATE():当前时间;
 
 CONCAT_WS(SEP,S1,S2...Sn) 
 PS:CONCAT_WS:全称:CONCAT With Separator 是CONCAT()函数的特殊形式
 SELECT CONCAT_WS(NULL,'029',88661234) 合并后的字符串;
 SELECT CONCAT_WS('-','029','NULL',88461234) 合并字符串;

13.1.2 比较字符串大小函数STRCMP()

 STRCMP(str1,str2)
 SELECT STRCMP('abc','abd'),STRCMP('abc','abd'),STRCMP('abc','abd');

13.1.3 获取字符串长度函数LENHT()和字符串函数CHAR_LENGTH()

 LENGTH(str) CHAR_LENGTH(str)
 SELECT 'MySQL' 英文字符串, LENGTH('MySQL') 字符串字节长度, '常建功' '中文字符串',
 LENGTH('常建功') 字符串字节长度; 

13.1.4 实现字母大小写转换函数UPPER()和字符串函数LOWER()

 UPPER(S) 或者 UCASE(S)
 SELECT 'mysql' 字符串, UPPER('mysql') 转换后字符串, UCASE('mysql') 转换后字符串;
 LOWER(S) 或者 LCASE(S) 
 SELECT 'MYSQL' 字符串, LOWER('mysql') 转换后字符串,LCASE('mysql') 转换后字符串;

13.1.5 查找字符串

1.返回字符串位置的FIND_IN_SET()函数
 FIND_IN_SET(str1,str2) PS:函数将会返回在字符串str2中与str1相匹配的字符串的位置。
 SELECT FIND_IN_SET('MySQL','oracle,sql server,MySQL') 位置;
2.返回指定字符串位置的FIELD()函数
 FIELD(str,str1,str2...) PS:函数将会返回第一个与字符串str匹配的字符串的位置。
 SELECT FIELD('MySQL','oracle','sql server','MySQL') 位置; 
3.返回子字符串相匹配的开始位置(三个函数LOCATE()、POSITION()和INSTR() )
 LOCATE(str1,str) POSITION(str1 IN str) INSTR(str,str1)
 SELECT LOCATE('SQL','MySQL') 位置,POSITION('SQL' IN 'MySQL') 位置, 
 INSTR('MySQL','SQL')位置
4.返回指定位置的字符串的ELT()函数
 ELT(str1,str2...) PS:函数将会返回地n个字符串。
 SELECT ELT(1,'MySQL','oracle','sql server') 第1个位置的字符串;
5.选择字符串的MAKE_SET()函数
MAKE_SET(num,str1,str2...strn) PS:函数将数值num转换成二进制数,
后参照二进制数从参数str1,str2,...strn中选取相应的字符串,在通过
二进制数选择字符串时,按从左到右的顺序读取该值,如果值为1选择该字符串,
否则将不选择该字符串。
SELECT BIN(5) 二进制数,MAKE_SET(5,'MySQL','Oracle',
'SQL Server','PodstgreSQL')
选取后的字符串,BIN(7) 二进制数,MAKE_SET
(7,'MySQL','Oracle','SQL Server','PostgreSQL') 选取后字符串;

13.1.6 从现有的字符串中截取子字符串(LEFT(),RIGHT(),SUBSTRING(),MID())

1.从左边或右边截取子字符串
 LEFT(str,num) RIGHT(str,num)
 SELECT 'MySQL' 字符串,LEFT('MySQL',2) 前两个字符串,
 RIGHT('MySQL',3) 后3个字符串;
2.截取指定位置和长度子字符串
 SUBSTRING(str,num,len) MID(str,num,len)
 SELECT 'oraclemysql' 字符串,SUBSTRING('oraclemysql',7,5) 
 截取子字符串,MID('oraclemysql',7,5) 截取字符串;

13.1.7 去除字符串的首尾空格(LTRIM(),RTRIM(),TRIM())

1.去除字符串开始处空格
 LTRIM(str) 
 函数返回去掉开始(左边)空格的字符串str;
 SELECT CONCAT('-',' MySQL ','-') 原来的字符串, CHAR_LENGTH(CONCAT('-','
 MySQL ','-')) 原来字符串长度,CONCAT('-',LTRIM(' MySQL '),'-') 
 处理后的字符串,CHAR_LENGTH(CONCAT('-',LTRIM(' MySQL '),'-')) 
 处理后的字符串长度;
2.去除字符串结束处空格
 RTRIM(str)
 函数返回去掉结束处(右边)空格的字符串str;
 SELECT CONCAT('-',' MySQL ','-') 原来的字符串, CHAR_LENGTH(CONCAT('-',' 
 MySQL ','-')) 原来字符串长度,CONCAT('-',RTRIM(' MySQL '),'-') 处理后的字符 
 串,CHAR_LENGTH(CONCAT('-',RTRIM(' MySQL '),'-')) 处理后的字符串长度;
3.去除字符串首尾空格
 TRIM(str)
 函数返回去掉首尾空格的字符串str;
 SELECT CONCAT('-',' MySQL ','-') 原来的字符串, CHAR_LENGTH(CONCAT('-','
 MySQL','-')) 原来字符串长度,CONCAT('-',TRIM(' MySQL '),'-') 处理后的字符串,
 CHAR_LENGTH(CONCAT('-',TRIM(' MySQL '),'-')) 处理后的字符串长度;

13.1.8 替换字符串

1.INSERT(str,pos,len,newstr);
 函数会将字符串str中的pos位置开始长度为len的字符串用字符串newstr来替换,
 如果参数pos的值超过字符串长度,则返回值为原始字符串str,如果len的长度大于
 原来的长度(str)中所剩字符串的长度,则从位置pos开始进行全部替换,
 若任何一个参数为NULL,则返回值为NULL;
 (1)实现字符串替换功能:
 SELECT '这是MySQL数据库管理系统' 字符串,INSERT('这是MySQL数据库
 管理系统'3,5,'Oracle') 转换后字符串;
 (2)替换的起始位置大于字符串长度:
 SELECT '这是MySQL数据库管理系统' 字符串,CHAR_LENGTH('这是MySQL数据库管理系统') 
 字符串字符数, INSERT('这是MySQL数据库管理系统',16,15,'Oracle') 转换后字符串;
 (3)替换的长度大于原来字符串中所剩字符串的长度:
 SELECT '这是MySQL数据库管理系统' 字符串,CHAR_LENGTH('MySQL数据库管理系统') 
 剩余字符数, INSERT('这是MySQL数据库管理系统',3,15,'Oracle') 转换后字符串;
2.使用REPLACE()函数
 REPLACE(str,substr,newstr)
 函数会将字符串str中的字符串substr用字符串newstr来替换;
 SELECT '这是MySQL数据库管理系统' 原字符串, REPLACE('这是MySQL数据库管理
 系统','MySQL','Oracle') 替换后字符串;

13.2 使用数值函数

 - 函数 功能
 - ABS(x) 返回数值x的绝对值
 - CEIL(x) 返回大于或等于x的最小整数值
 - FLOOR(x) 返回小于或等于x的最大整数值
 - MOD(x,y) 返回x除以y的余数
 - RAND() 返回0~1内的随机数
 - ROUND(x,y) 返回数值x的四舍五入后有y位小数的数值
 - TRUNCATE(x,y) 返回数值x且截断位y位小数的数值

13.2.1 获取随机数(RAND(),RAND(x))

 - RAND():函数返回的数是完全随机的
 - RAND():函数返回的随机数值是相同的
 SELECT RAND(),RAND(),RAND(3),RAND(3);

13.2.2 获取整数的函数(CEIL()(CEILING()),FLOOR())

 - CEIL():函数返回大于或等于数值x的最小整数
 - FLOOR():函数返回小于或等于数值x的最大整数
 SELECT CEIL(4.3),CEIL(-2.5),CEILING(4.3),CEILING(-2.5);
 SELECT FLOOR(4.3),floor(-2.5);

13.2.3 截取数值函数(TRUNCATE())

 TRUNCATE(x,y)
 函数返回数值x保留到小数点后y位的值
 SELECT TRUNCATE(903.53567,2),TRUNCATE(903.53567,-1);

13.2.4 四舍五入函数(ROUND())

 ROUND(x):
 函数返回数值x经过四舍五入操作后的数值
 ROUND(x,y):
 函数返回数值x保留到小数y位的值,在具体截取数值时需要四舍五入操作。
 SELECT ROUND(903.53567),ROUND(-903.53567),ROUND(903.53567,2),
 ROUND(903.53567,-1);

13.3 使用日期和时间函数

 - 函数 功能
 - CURDATE() 获取当前日期
 - CURTIME() 获取当前日期
 - NOW() 获取当前的日期和时间
 - UNIX_TIMESTAMP(date) 获取日期date的UNIX时间戳
 - FROM_UNIXTIME() 获取UNIX时间戳的日期值
 - WEEK(date) 返回日期date为一年中的第几周
 - YEAR(date) 返回日期date的年份
 - HOUR(time) 返回时间time的小时值
 - MINUTE(time) 返回时间time的分钟值
 - MONTHNAME(date) 返回时间time的月份值

13.3.1 获取当前日期和时间的函数

1 获取当前日期和时间:
 NOW()、CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()
 SELECT NOW() now方式,CURRENT_TIMESTAMP() tmestamp方式,
 LOCALTIME() loacltime方式,SYSDATE() sysdate方式;
2获取当前日期CURDATE(),CURRENT_DATE()
 SELECT CURDATE() curdate方式,CURRENT_DATE() current_date方式;
3获取当前时间(CURTIME(),CURRENT_DATE())
 SELECT CURTIME() curtime方式,CURRENT_TIME() CURENT_DATE方式;

13.3.2通过各种方式显示日期和时间

1.通过UNIX方式显示日期和时间
 SELECT NOW() 当前时间,UNIX_TIMESTAMP(NOW()) unix格式,
 FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) 普通格式;
 SELECT NOW() 当前时间,UNIX_TIMESTAMP() unix格式,
 UNIX_TIMESTAMP(NOW()) unix格式;
2通过UTC方式显示日期和时间
 UTC:Universal Coordinated Time,国际协调时间
 UTC_DATE(),UTC_TIME()
 SELECT NOW() 当前日期和时间,UTC_DATE() UTC日期,UTC_TIME() UTC时间;

13.3.3获取日期和时间各部分值

 SELECT now() 当前日期和时间, YEAR(NOW()) 年,QUARTER(NOW()) 
 季度,MONTH(NOW()) 月,
 WEEK(NOW()) 月,DAYOFMONTH()NOW() 天,HOUR(NOW()) 小时,
 MINUTE(NOW()) 分,SECOND(NOW()) 秒;
1.关于月的函数(MONTH())
 SELECT NOW() 当前日期和时间,MONTH(NOW()) 月,MONTHNAME(NOW()) 月;
2.关于星期的函数(DAYNAME(),DAYOFWEEK(),WEEKDAY())
 - DAYNAME()函数:返回日期好时间找那个星期的英文名
 - DAYOFWEEK()函数:返回日期和时间中星期是星期几,
 返回值去1~7,返回1为星期日,2为星期一;
 - WEEKDAY()函数:返回日期和时间中星期是星期几,返回去之0~6,
 返回0为星期一,1为星期二;
 SELECT NOW() 当前日期和时间,WEEK(NOW()) 年中第几个星期,
 WEEKOFYEAR(NOW()) 年中第几个星期,
 DATNAME(NOW()) 星期,DAYOFWEEK(NOW()) 星期,WEEKDAY(NOW()) 星期;
3.关于天的函数
 - DAYOFMONTH():返回日期为当月的第几天,
 - DAYOFYEAR():返回日期为本年的第几天,
 SELECT NOW() 当前日期和时间, DAYOFYEAR(NOW()) 年中第几天,
 DAYOFMONTH(NOW()) 月中第几天;
4.获取指定的EXTRACT()函数
 EXTRACT():统一获取日期和时间的各部分值
 EXTRACT(type FROM date)
 SELECT NOW() 当前日期和时间,EXTRACT(YEAR FROM NOW()) 年,
 EXTRACT(MONTH FROM NOW()) 月,EXTRACT(DAY FROM NOW()) 天,
 EXTRACT(HOUR FROM NOW()) 小时,EXTRACT(MINUTE FROM NOW()) 分,
 EXTRACT(SECOND FROM NOW()) 秒;

13.3.4 计算日期和时间的函数

1.与默认日期和时间操作
 - TO_DAYS(date):该函数来实现计算日期参数date与默认日期和时间
 (0000年1月1日)之间相隔天数
 - FROM_DAYS(number):该函数计算从默认日期和和时间(0000年1月1日)
 开始经历number天后的日期和时间
 SELECT NOW() 当前日期和时间,TO_DAYS(NOW()) 相隔天数,
 FROM_DAYS(TO_DAYS(NOW())) 一段时间后日期和时间
 SELECT NOW() 当前日期和时间, DATEDIFF(NOW(),'2000-12-01')
 相隔天数;
2与指定日期和时间操作
 - ADDDATE(date,n):该函数计算日期参数date加上n天后的日期
 - SUBSATE(date,n):该函数计算日期参数date减去n天后的日期
 SELECT CURDATE() 当前日期,ADDDATE(CURDATE(),5) '5天后日期',
 SUBDATE(CURDATE(),5) '5天前日期';ADDDATE(d,INTERVAL expr type)
 函数返回日期参数d加上一段时间后的日期,表达式expr决定了时间的长度,
 参数type决定了操作的对象 
 SUBDATE(d,INTERVAL expr type)
 函数返回日期参数d减去一段时间后的日期,表达式expr决定了时间的长度,
 参数type决定了操作的对象 
 - type的值 含义 expr表达式
 - YEAR 年 YY
 - MONTH 月 MM
 - DAY 日 DD
 - HOUR 小时 hh
 - MINUTE 分钟 mm
 - SECOND 秒 ss
 - YEAR_MONTH 年和月 YY与MM之间任意符号隔开
 - DAY_HOUR 日和小时 DD与hh之间用任意符号隔开
 - DAY_MINUTE 日和分钟 DD与mm之间用任意符号隔开
 - DAY_SECOND 日和秒 DD与ss之间用任意符号隔开
 - HOUR_MINUTE 小时和分钟 hh与mm之间用任意符号隔开
 - HOUR_SECOND 小时和秒 hh和ss之间用任意符号隔开
 - MINUTE_SECOND 分钟和秒 mm与ss之间用任意符号隔开
 SELECT CURDATE() 当前日期, ADDDATE(CURDATE(),INTERUAL '2,3'
 YEAR_MONTH) '2年3个月后日期',
 SUBDATE(CURDATE(),INTERUAL '2,3' YEAR_MONTH)'2年3个月前日期';
 - 函数ADDTIME(time,n):该函数计算时间参数time加上n秒后的时间,
 - 函数SUBTIME(time,n):该函数计算时间参数time减去n秒后的时间,
 SELECT CURTIME() 当前时间, ADDTIME(CURTIME(),5) '5秒后的时间',
 SUBTIME(CURTIME(),5) '5秒前的时间';

13.4 使用系统信息函数

 - 函数 作用
 - VERSION() 返回数据库的版本号
 - DATABASE() 返回当前数据库名
 - USER() 返回当前用户
 - LAST_INSERT_ID() 返回最近生成的AUTO_INCREMENT值

13.4.1 获取MySQL系统信息

 SELECT VERSION() 版本号,DATABASE() 数据库名,USER() 用户名;

13.4.2 获取AUTO_INCREMENT约束的最后ID值

 CREATE TABLE t_autoincrement (ID INT(11) NOT NULL 
 AUTO_INCREMENT UNIQUE);INSERT INTO t_autoincrement 
 VALUES(NULL), INSERT INTO t_autoincrement VALUES(NULL),INSERT INTO 
 t_autoincrement VALUES(NULL),INSERT INTO t_autoincrement VALUES(NULL);

13.4.3 其他函数

流程函数
 - 函数 作用
 - IF(value,t f) 如果value为真,返回t,否则返回f
 - IFNULL(value1,value2) 如果value1不为空返回value1,
 否则返回value2
 - CASE WHEN[value]THEN[value]
 ...ELSE[default] END 如果value1为真,返回value1,否则返回default;
 - CASE [expr]WHEN[value]THEN
 [result1]...ELSE[default] END 如果expr等于value1,返回result1,否则返回default; 
实现特殊功能的函数
 - 函数 作用
 - PASSWORD(str) 实现了对字符串str进行加密
 - FORMAT(x,n) 实现将数字x进行格式化,保留n位小数
 - INET_ATON(ip) 实现将ip地址转换为数字
 - INET_NTAO(x) 实现将数字转换为IP
 - GET_LOCT(name,time) 创建一个持续时间为time的名为name的锁
 - RELEASE_LOCT(name) 实现将name的锁进行解锁
 - BENCHMARK(count,expr) 实现将表达式重复执行count次
 - CONVERT(s USING cs) 实现将s的字符串集变成cs
 - CONVERT(x,type) 实现将x变成type类型

十四 存储过程和函数的操作

14.1 为什么使用存储过程和函数

存储过程和函数:可以简单理解为一条或多条SQL语句的集合,存储过程和函数
就是事先经过编译并存储在数据库的一段SQL语句集合;
存储过程和函数有何区别:
 函数必须有返回值,存储过程则没有。
 存储过程的参数类型远远多于函数参数类型
 存储过程和函数的优点:
 - 存储过程和函数允许标准组件式编程,提高了SQL语句的重用性、共享性和可移植性
 - 存储过程和函数能够实现较快的执行速度,能减少网络流量
 - 存储过程和函数可以作为一种安全机制来利用
 概括为就是简单和高性能
 缺点:
 - 存储过程和函数的编写比简单SQL语句复杂,需要用户具有更高的技能和更丰富的经验
 - 在编写存储过程和函数时,需要创建这些数据库对象的权限

14.2 创建存储过程和函数

14.2.1 创建存储过程语法形式
 CREATE PROCEDURE procedure_name([procedure_parameter[,...]])
 [characteristic...]routine_body
 - procedure_name:参数表示所要创建的存储过程名字
 - procedure_parameter:参数表示存储过程的参数
 - characteristic:参数表示存储过程的特性
 - routine_body:参数表示存储过程的SQL语句代码
 - BEGIN...END :可以标志SQL语句的开始和结束
procedure_parameter中每个参数的语法形式:
[IN|OUT|INOUT] parameter_name type
此语句中,每个参数有三部分组成,分别为输入/输出类型、参数名和参数类型。
其中输入/输出类型有三种类型分别为:IN:输入类型,OUT:输出类型,INOUT:输入/输出类型
parameter_name表示参数名,type表示参数类型
characteristic参数的取值为:
LENGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL|NO SQL|READS SQL DATA
|MODIFIES SQL DATA} | SQL SECURITY {DEFINER|INVOKER} | COMMENT 'string'
当characterisic参数的值为上述各个值时,分别表示:
- LENGUAGE SQL:表示存储过程的routine_body部分由SQL语句的语句组成,为MySQL
软件所有默认的语句
- [NOT]DETERMINISTIC:表示存储过程的执行结果是否确定,如果值为
DETERMINISTIC,表示执行结果是确定的,即每次执行存储过程时,如果输入
相同的参数得到的相同的输出;如果值为NOT DETERMINISTIC,
表示结果不确定,即相同的输入可能得到不同的输出。默认值为DETERMINISTIC。
- {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA},
表示使用SQL语句的限制。如果值为CONTAINS SQL表示可以包含SQL语句,但不包含
读或写数据的语句 如果值为 NO SQL表示不包含SQL语句;如果值为 READS SQL DATA表示
包含读数据的语 句:如果值为 MODIFIES SQL DATA表示包含读数据的语句。
默认值为 CONTAINS SQL。
- SQL SECURITY {DEFINER|INVOKER},设置谁有权限来执行。
如果值为DEFINER,表示只有定义者自己才能够 执行;如果值为INVOKER表示
调用者可以执行。默认值为DEFINER。
- COMMENT 'string',表示注释语句。

14.2.2 创建函数语法形式

CREATE FUNCTION function_name([function_parameter[,...]]) 
[characteristic...] routine_body 
function name:参数表示所要创建的函数名字; 
function parameter:参数表示函数的参数;
characteristic:参数表示函数的特性,该参数的取值与存储过程中的取值相同。
routine_body:参数表示函数的SQL语句代码,可以用BEGN.END来表示SQL语句的开始和结束。 
注意:在具体创建函数时,函数名不能与已经存在的函数名重名。
function_parameter中每个参数的语法形式:
parameter_name type
parameter name表示参数名。
type表示参数类型,可以是 MySQL软件所支持的任意一个数据类型。

14.2.3 创建简单的存储过程和函数

举例进行说明怎样应用存储过程和函数: 
执行SQL语句CREATE PROCEDURE,在数据库company中创建查询雇员表
(t_employee)中所有的雇员工资的存储过程:
 DELIMITRE $$
 CREATE PROCEDURE proce_employee_sal()
 COMMENT'查询所有雇员的工资'
 BEGIN
 SELECT sal FROM t_employee;
 END$$
 DELIMITER ;
 解析:创建一个名为proce_employee_sal的存储过程,主要是用来实现通过
 SELECT 语句从t_employee表中查询sal字段值。实现查询雇员工资功能。
 PS:通常在创建存储过程时,经常通过命令"DELIMITER $$"将SQL语句的结束符由";"
 修改为"$$"。主要是因为SQL语句中默认的语句结束符号为(;),即存储过程中的SQL
 语句也需要用分号(;)来结束,当将结束符号修改为"$$"符号后就可以在执行过程中
 避免冲突,不过最后一定不要忘记通过命令"DELIMITER;",将结束符号修改为SQL
 语句默认的结束符号。
 如:
 创建查询雇员表(t_employee)中某个雇员工资的函数:
 DELIMITER $$ 
 CREATE FUNCTION func_employee_sal (empno INT(11))
 RETURNS DOUBLE(10,2)
 COMMENT'查询某个雇员的工资'
 BEGIN 
 RETURN (SELECT sal FROM t_employee WHERE t_employee.empno=empno);
 END$$
 DELIMITER ;
 解析:创建一个名为func_employee_sal的函数,改函数有一个类型为INT(11)名为empno
 的参数,返回值为DOUBLE(10,2)类型,SELECT语句从t_employee表中查询empno字段
 值等于所有传入参数empno值得记录,同时并将该条记录得sal字段的值返回。
 "DELIMITER $$"将SQL语句的结束符由";"修改为"$$",通过命令"DELIMITER;",
 将结束符号修改为SQL语句默认的结束符号。

14.3 关于存储过程和函数的表达式

14.3.1 操作变量

1 声明变量(DECLARE)
 DECLARE var_name[,...] type [DECLARE value]
 解析:var_name:参数表示声明的变量名字,
 type:表示声明变量的类型
 DECLARE value:用来实现设置变量的默认值,如果无该语句默认为NULL,
 PS:在具体声明变量时,可以同时定义多个变量。
2 赋值变量(SET),(SELECT...INTO)
 SET var_name=expr[,...]
 解析:var_name:表示所要赋值变量名字
 expr:关于变量的赋值表达式
 PS:为变量赋值时,可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开
 SELECT field_name[,...] INTO var_name[,...] FROM table_name WHERE 
 condition
 解析:field_name:百世查询的字段名,
 var_name:表示变量名
 PS:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行。

实例:
(1)声明一个名为employee_sal的变量:
 DECLARE employee_sal INT DEFAULT 1000;
 解析:声明了一个表示雇员工资的变量employee,并设置该变量的默认值为1000,
(2)为变量employee_sal赋值:
 DECLARE employee_sal INT DEFAULT 1000; SET employee_sal=3500;
 解析:首先声明一个表示雇员工资的变量employee_sal,默认值为1000,
 后设置该变量的值为3500;
(3)将查询结果赋值给变量,即将表t_employee中empno为“7566”记录中字段
 sal的值赋值该变量employee_sal。
 SELECT sal INTO employee_sal FROM t_employee WHERE empno=7566;
 解析:通过SELECT...INTO 语句将t_employee里相应的数据记录中字段sal的
 值赋值给变量employee_sal;

14.3.2 操作条件

1.定义条件(DECLARE)
 DECLARE condition_name FOR condition_value condition_value: 
 SQLSTART[VALUE] sqlstart_value|mysql_error_code
 解析:condition_name:表示所要定义的条件名称
 condition__value:实现设置条件的类型
 mysql_error_code:设置条件的错误
2.定义处理程序
 DECLARE handler_type HANDLER FOR condition_value[,...] 
 sp_statement handler_type:
 CONTINUE | EXIT |UNDO 
 condition_value:
 SQLSTART[VALUE] sqlstate_value | condition_name | 
 SQLWARNING | NOTFOUND
 SQLEXCEPTION | mysql_error_code
 此语句指定每个可以处理一个或者多个条件的处理程序,如果产生
 一个或者多个条件没指定语句
 被执行。对一个CONTINUE处理程序,当前子程序的执行在执行处理程序语句之后继续,
 对于EXIT处理程序,当前BEGIN...END复合语句执行被终止。
 UNDO处理程序类型语句还不被支持。
 - SQLWARNING是对所有以01开头的SQLSTATE代码的速记
 - NOT FOUND 是对所有以02开头的SQLSTATE代码的速记
 - SQLEXCEOTION 是对所有没有被SQLWARNING或NOT FOUND 捕获的SQLSTATE代码的速记

14.3.3 使用游标

 MySQL软件的查询语句可以返回多条记录结果,那么在表达式中如何遍历这些记录结果呢,
 MySQL软件提供了游标来实现。通过指 定由 SELECT语句返回的行集合(包括满足该语句的
 WHERE子句所列条件的所有行),由该语句 返回完整的行集合叫作结果集。应用程序需要一种机制
 来一次处理结果集中的一行或连续的几行, 而游标通过每次指向一条记录完成与应用程序的交互。 
 游标可以看作一种数据类型,可以用来遍历结果集,相当于指针,或者是数组中的下标。
 处理结果集的方法可以通过游标定位到结果集的某一行,从当前结果集的位置搜索一行或一部分行
 或者对结果集中的当前行进行数据修改。
 下面将介绍如何:声明游标、打开游标、使用游标和关闭游标
1.声明游标
 DECLARE cursor_name CURSOR FOR select_statement;
 解析:cursor_name:表示游标的名称
 select_statement:表示SELECT语句
 PS:由于游标需要遍历结果集中的每一行,增加了服务器的负担,导致游标的效率并不高
 如果超过1万行,应采用其他方式,另外使用游标,
 应尽量避免在游标循环中进行连接的操作
2.打开游标(OPEN)
 OPEN cursor_name
 解析:cursor_name:表示所要打开游标的名称。
 PS:打开一个游标时,游标并不是指向第一行记录,而是指向第一条记录的前面
3.使用游标(FETCH)
 FETCH cursor_name INTO var_name[,var_name] ...
 解析:将参数cursor_name中SELECT语句的执行结果保存到变量参数
 var_name中,变量参数var_name必须在游标使用之前定义。
4.关闭游标(CLOSE)
 CLOSE cursor_name
 解析:cursor_name:表示所要关闭游标的名称

 实例:此实例实现的功能为统计工资大于999的雇员人数,此功能可以直接
 通过WHERE条件和COUNT函数直接完成;
 (1)执行DECLARE语句,声明一个名为cursor_employee的游标:
 DECLARE cursor_employee CURSOR FOR SELECT sal FROM t_employee;
 解析:声明一个名为cursor_employee的游标,SELECT语句实现了查询所有雇员的工资;
 (2)执行OPEN语句,打开游标cursor_employee:
 OPEN cursor_employee;
 解析:打开名为cursor_employee的游标
 (3)执行FETCH语句,通过游标cursor_employee查询结果赋值给变量,
 即将表t_employee表中所有记录中字段sal的值赋值给变量employee_sal:
 FETCH cursor_employee INTO employee_sal;
 解析:将游标cursor_employee的查询结果赋值给变量employee_sal。
 (4)执行CLOSE语句,关闭游标cursor_employee:
 CLOSEcursor_employee;
 解析:关闭名为cursor_employee的游标

在具体使用游标时,游标必须在处理程序之前且在变量和条件之后声明,
并且最后一定要关闭游标本实例完成的代码如下:
DROP PROCEDURE IF EXISTS employee_count;
DELIMITER $
#创建存储过程
CREATE PROCEDURE employee_count
(OUT NUM INTEGER)
 BEGIN
#声明变量
DECLARE employee_sal INTEGER;
DECLARE flag INTEGER;
#声明游标
DECLARE cursor_employee 
 CURSOR FOR SELECT sal FROM t_employee; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag= 1;
#设置结束标志
SET flag=0;
SET NUM=0;
#打开游标
OPEN cursor_employee;
#遍历游标指向的结果集
FETCH cursor_employee INTO employee_sal;
WHILE flag<>1 DO
IF employee_sal >999 THEN
SET num=num+1;
END IF;
FETCH cursor_employee INTO employee_sal;
END WHILE;
#关闭游标
CLOSE cuesor_employee;
END
$
DELIMITER ;
上述实例创建了一个存储过程,并使用游标遍历结果集中的每一行,如果发现
工资大于999,则变量NUM加1,最后统计出符合条件的记录条数。

如需调用此存储过程,使用一下以下方法:
#调用存储过程
 CALL employee_count(@count);
 select @count; 

除了使用WHILE...END WHILE 遍历结果集以外,游标的遍历还有以下几种方式
- LOOP...END LOOP
- REPEAT...END REPEAT

使用LOOP循环遍历重写实例:
 DROP PROCEDURE IF EXISTS employee_count;
 DELIMITER $
 #创建存储过程
 CREATE PROCEDURE employee_count
 (OUT NUM INTEGER)
 BEGIN
 #声明变量
 DECLARE employee_sal INTEGER;
 DECLARE flag INTEGER;
 #声明游标
 DECLARE cursor_employee
 CURSOR FOR SELECT sal FROM t_employee;
 SECLARE CONTINUE HANDLER FOR NOT FOUND SET flag= 1;
 #设置结束标志
 SET flag = 0;
 SET NUM = 0;
 #打开游标
 OPEN cursor_employee;
 #遍历游标
 FETCH cuesor_employee INTO employee_sal;
 loop_label:LOOP
 IF employee_sal >999 THEN
 SET num=num+1;
 END IF;
 FETCH cursor_employee INTO employee_sal;
 if(flag=1) then
 LEAVE loop_label;
 end if;
 END LOOP;
 #关闭游标
 CLOSE cursor_employee;
 END
 $
 DELIMITER ;

 使用REPEAT循环遍历重写实例:
 DROP PROCEDURE IF EXISTS employee_count;
 DELIMITER $
 #创建存储过程
 CREATE PROCETUREemployee_count
 (OUT NUM INTEGER)
 BEGIN 
 #声明变量
 DECLARE employee_sal INTEGER;
 DECLARE flag INTEGER;
 #声明游标
 DECLARE cursor_employee
 CURSOR FOR SELET sal FROM t_employee;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
 #设置结束标志
 SET flag=0;
 SET NUM=0;
 #打开游标
 OPEN cursor_employee;
 #遍历游标
 FETCH cursor_employee INTO employee_sal;
 REPEAT
 IF employee_sal >999 THEN
 SET num=num+1;
 END IF;
 FETCH cursor_employee INTO employee_sal;
 UNTIL flag=1
 END REPEAT; 
 #关闭游标
 CLOSE cursor_employee;
 END
 $
 DELIMITER ;

14.3.4 使用流程控制

 流程控制语句主要用来实现控制语句的执行顺序,例如顺序、条件和循环。
 通过IF和CASE来实现条件控制。LOOP、WHILE和REPEAT实现循环控制。
1条件控制语句
 IF语句具体进行条件控制时,根据是否满足条件,执行不同的语句
 CASE语则可以实现更复杂的条件控制:
 IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...
 [ELSE search_condition]
 END IF
 解析:search_condition:参数表示条件的判断
 statement_list:表示不同条件的执行语句

 CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list]...
 [ELSE statement_list]
 END CASE
 解析:case_value:表示条件判断的变量
 when_value:表示条件判断变量的值
 statement_list:表示不同条件的执行语句
2.循环控制语句
 LOOP、WHILE和REPEAT来实现循环控制语句,其中后两个关键字用
 来实现带有条件的循环控制语句。即对于关键字WHILE,只有在满足
 条件的基础上执行循环体。而关键字REPEAT则是在满足的条件时退出循环体:
 [begin_label:] LOOP
 statement_list
 END LOOP [end_label]
 begin_label和end_label参数分别表示循环开始和结束的标志。
 两参数必须相同。并且可以省略。LOOP表示循环体的开始。END LOOP
 表示循环体的结束。statement_list表示所执行的循环体语句。

 如果想实现退出正在执行的循环体,通过LEAVE来实现:
 LEAVE label
 label:表示循环的标志

实现循环执行可以通过WHILE来实现,不过其是带由条件控制的循环。
即当满足条件时才执行循环体语句:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
search_condition:表示循环的条件,当满足该条件时才执行循环体语句statement_list;

 实现循环执行还可以通过关键字REPEAT来实现,其同样也是带有条件控制的循环,
 不过当满足条件则跳出循环体语句:
 [begin_label:]REPEAT search_condition DO
 statement_list
 END REPEAT [end_label]
 search_condition表示循环的条件,当满足该条件时则跳出循环体statement_list语句体

14.4 修改存储过程和函数(ALTER PROCEDURE和ALTER FUNCTION)

1.修改存储过程
 	ALTER PROCEDURE procedure_name [characteristic...]
 procedure_name参数表示所要修改存储过程的名字
 characteristic参数指定修改后存储过程的特性
 与定义存储过程的该参数相比,取值只能是如下值:
 |{CONTAINS SQL | NO SQL |READS SQL DATA | MODIFIES SQL DATA}
 |SQL SECURITY {DEFINER|INVOKER}
 |COMMENT 'string'
 注意:所要修改的存储过程必须在数据库中已经存在

实例:执行SQL语句ALTER TABLE 修改数据库company中t_dept表的名称为tab_dept:
 ALTER TABLE t_dept RENAME tab_dept;
 DESC t_dept;
 DESC tab_dept;

14.4.2 修改函数

 ALTER FUNCTION function_name [characteristic...]
 function_name:表示所要修改的函数的名字
 characteristic:指定修改后函数的特性,与定义函数该参数相比,取值只能如下值:
 |{CONTAINS SQL | NO SQL |READS SQL DATA | MODIFIES SQL DATA}
 |SQL SECURITY {DEFINER|INVOKER}
 |COMMENT 'string'
 注意:所要修改的存储过程必须在数据库中已经存在

14.5 删除存储过程和函数

14.5.1通过DROP PROCEDURE语句删除存储过程

 DROP PROCEDURE proce_name
 DROP PROCEDURE 用来表示实现删除存储过程
 proce_name:参数表示所要删除的存储过程名称

实例:删除company数据库中存储过程对象proce_employee_sal:
USE company;
DROP PROCEDURE proce_employee_sal;
#通过系统表routines查询是否还存在存储过程对象proce_employee_sal:
SELECT * FROM ROUTINES WHERE SPECIFIC_NAME='proce_employee_sal' \G;

14.5.2 通过DROP FUNCTION语句删除函数

 DROP FUNCTION func_name;
 实例:使用DROP FUNCTION 删除函数,在company数据库中删除函数func_employee_sal:
 USE company;
 DROP FUNCTION func_employee_sal;
 #通过系统表routines查询是否还存在函数func_employee_sal:
 SELECT * FROM ROUTINES WHERE SPECIFIC_NAME=func_employee_sal' \G; 

十五 MySQL事务

15.1事务概述

事务具有4个特性:
- 原子性(Atomicity):事务中所有的操作视为一个原子单元,即对于事务所进行的
 数据修改等操作只能是完全提交或者完全回滚。
- 一致性(Consistency):事务在完成时,必须是所有的数据从一种一致性状态变更为另一种
 一致性状态,所有的变更都必须应用于事物的修改,以确保数据的完整性。
- 隔离性(Isolation): 一个事务中的操作语句所做的修改必须与其他事务所做的修改相隔离
 在进行事务查看数据时数据所处的状态,要么是被另一并发事务修改之前的状态,要么是被另一并
 发事务修改之后的状态,即当前事务不会查看由另一个并发事务正在修改的数据。
 这种特性通过锁机制实现。 
- 持久性( Durability):事务完成之后,所做的修改对数据的影响是永久的,即使系统重启
 或者出现系统故障数据仍可以恢复。
1.REDO日志
 事务执行时需要将执行的事务日志写入日志文件里,对应的文件为REDO日。当每条SQL 
 进行数据库更新操作时,首先将REDO日志写入日志缓冲区。当客户端执行 COMMIT命令提交时, 
 日志缓冲区的内容将被刷新到磁盘,日志缓冲区的刷新方式或者时间间隔可以通过参数
 innodb_flush log_at trx_commit控制.
 EDO日志对应磁盘上的 ib_logfile文件,该文件默认为5MB,建议设置为512MB以便容纳 
 较大的事务。在MySQL崩溃恢复时会重新执行REDO日志中的记录,REDO日志如下所示,其中的
 ib_logfile0和ib_logfile即为REDO日志:

 E: \MySQL\data的目录 
 2013-09-03 13:01 <DIR> company
 2013-09-02 17:55 10,485,760 ibdata1
 2013-09-03 09:25 10,485,760 ib_1 logfile0
 2012-11-26 09:38 10,485,760 ib10gfi1e1
 2012-11-26 09:38 <DIR> mysql
 2012-11-26 09:38 <DIR> phpmyadmin
 2013-09-02 16:37 <DIR> test
2.UNDO日志
 UNDO日志主要用于事务异常时的数据回滚,具体内容就是复制事务前的数据库内容到UNDO缓冲区
 然后在合适的时间将内容刷新到磁盘。
 与REDO不同的是,磁盘上不存在单独的UNDO日志文件,所有的UNDO日志均存放在表空间对应的
 .ibd数据文件中,即使用MySQL服务启用了单独表空间,依然如此,UNDO日志又被称作回滚段。

15.2 MySQL事务控制语言

 MySQL中使用BEGIN开始事务,使用COMMIT结束事务,中间使用ROLLBACK回滚事务,通过SET
 AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等语句支持本地事务。
 语法:
 START TRANSACTION | BEGIN [WORK]
 COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
 SET AUTOCOMMIT = {0 | 1}
 在默认设置下,MySQL中的事务时默认提交的,如需对某些语句进行事务控制,则使用START 
 TRANSACTION或者BEGIN开始一个事务比较方便,这样事务结束后可以自动回到自动提交的方式

实例:更新一条记录,为保证数据从一致性状态更新到另一个一致性状态,因此采用事务完成更新过程。
 如果更新失败或者其他原因可以使用回滚,此实例执行时对应的MySQL默认隔离级别为
REPEATABLE-READ;
#查看MySQL隔离级别
SHOW VARIABLES LIKE 'tx_isolation';
#创建测试需要的表,注意存储引擎为InnoDB
USER test;
CREATE TABLE test_1(id INT,username VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO test_1 VALUES(1,'petter'),(2,'bob'),(3,'allen'),(4,'aron');
SELECT * FROM test_1;
#开启一个事务
BEGIN;
#更新一条记录
UPDATE test_1 SET username='test'WHERE id=1;
#提交事务
COMMIT;
#发现记录已经更改生效
SELECT * FROM test_1;
#开启另一个事务
BEGIN;
UPDATE test_1 SET username='petter'WHERE id=1;
SELECT * FROM test_1;
#回滚事务
ROLLBACK;
#此时发现数据已经回滚
SELECT * FROM test_1;

15.3 MySQL事务隔离级别

 事务给级别可以使用一下语句设置:
 #未提交读
 SET GLOBAL TRANSCTION ISOLATION LEVEL READ UNCOMMITTED;
 #提交读
 SET GLOBAL TRANSCTION ISOLATION LEVEL READ COMMITTED;
 #可重复读
 SET GLOBAL TRANSCTION ISOLATION LEVEL REPEATABLE READ;
 #可串行化
 SET GLOBAL TRANSCTION ISOLATION LEVEL SERIALIZABLE;

15.3.1 READ-UNCOMMITTED(读取未提交内容)

 在该隔离级别,所有事物可以看到其他为提交事务的执行结果。
 因其性能不比其他级别高很多,因此隔离级别在实际应用中一般很少用,
 读取未提交的数据被称为脏读(Dirty Read)
 READ-UNCOMMITTED级别造成的脏读问题演示
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:READ-UNCOMMITTED
 BEGIN;
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6;
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:READ-UNCOMMITTED
 BEGIN;
 UPDATE test.test_6 SET a=a*2 WHERE a=2;
 ROLLBACK;
 MySQL的隔离级别为READ-UNCOMMITTED,首先开启A和B事务,
 在B事务未更新但未提交之前,A事务读取到了更新后的数据,但由于
 B事务回滚,A事务出现了脏读的现象。

15.3.2 READ-COMMITTED(读取提交内容)

 这是大多数的数据库系统默认的隔离级别,但不是MySQL默认的隔离级别。
 起满足了隔离的简单定义:
 一个事物从开始提交前所做的任何改变都是不可见的,事务只能看见已经提交事务所做的改变
 这种隔离级别也支持所谓的不可重复读(Nonrepeaatable Read),因同一事物的其他实例
 在该实例处理期间可能会有新的数据提交导致数据改变所以同一查询可能返回不同结果。
 此级别导致的不可重复读问题:
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:READ-COMMITTED
 BEGIN;
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6; 
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:READ-UNCOMMITTED
 BEGIN;
 UPDATE test.test_6 SET a=a*2 WHERE a=2;
 COMMIT;
 MySQL的隔离级别为READ-COMMITTED,首先开启A和B事务,在B事务未更新并提交后,A
 事务读取到了更新后的数据,此时处于同一A事务中的查询出现了不同的查询结果,
 即不可重复读现象。

15.3.3 REPEATABLE-READ(可重读)

这是MySQL默认的事务隔离级别,能确保同一事物的多个实例在并发读取数据时,
会看到同样的数据行,理论上会导致另一问题:幻读(Phantom Read)例如
第1个事务对一个表中的数据进行了修改,这种修改涉及表中的全部数据行。同时,
第2个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会
发生操作第1个事务的用户发现表中还有没有修改的数据行。 InnoDB和 Falcon存储引擎
通过多版本并发控制( Multi Version Concurrency Control,Mvcc)机制解决了该问题。
InnoDB存储引擎MvCC机制: InnoDB通过为每个数据行增加两个隐含值的方式来实现。这两
个隐含值记录了行的创建时间,以及过期时间。每一行存储事件发生时的系统版本号。每一次开始
个新事务时版本号会自动加1,每个事务都会保存开始时的版本号,每个查询根据事务的版本号来
查询结果。

15.3.4 Serializable(可串行化)

这是最高的隔离级别,通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之
是在每个读的数据行上加上共亨锁实现。在这个级别,可能会导致大量的超时现象
和锁竞争,一般不推荐使用
这四种隔离级别采取不同的锁类型来实现,若读取的是同一个数据的话,
就容易发生如下问题。例如
- 脏读( Drity Read),某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于
 某些原因,前一个回滚操作,则后一个事务所读取的数据不正确
- 不可重复读( Non-repeatable read),在一个事务的两次查询之中数据不一致,这可能是两次
 查询过程中间插入了一个事务更新的原有的数据。
- 幻读( Phantom Read),在一个事务的两次查询中数据笔数不一致,例如,有一个事务查询
 了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的
 查询中,就会发现有几列数据是先前没有的。
 REPEATABLE-READ级别操作演示: 
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6;
 COMMIT;
 SELECT * FROM test.test_6;
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 UPDATE test_6 SET a=0;
 COMMIT;
MSQL的隔离级别为 REPEATABLE-READ,首先开启A和B两事务,在B事务更新并提交后后,
A事务读取到的仍然是之前的数据,保证了在同一事务中读取到的数据都是同样的。在同
个事务中,不推荐使用不同存储引擎的表, COMMIT、 ROLLBACK只能对事务类型的表
进行提交和回滚;
MySQL中所有的DDL语句是不能回滚的,并且部分的DDL语句会造成隐式的提交。比如,
ALTER TABLE、 TRUNCATE TABLE和 DROP TABLE等。
隐式提交语句造成的事务自动提交演示
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6;
 SELECT * FROM test.test_6;
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 UPDATE test_6 SET a=0;
 ALTER TABLE test_1 ADD INDEX idx_id(id);
该实例开启一个事物,当B事务更新完成指定的记录时,此时其他事务并
不能看到更改的结果,当执行ALTER语句时造成事务隐式提交,此时事务A看到了更改后的记录。

15.4 InnoDB锁机制

 为解决数据库并发控制问题,如在同一时刻,客户端对于同一个表做更新或者查询操作,
 为保证数据的一致性,需要对并发操作进行控制,因此产生了锁。同时为实现MySQL的
 各个隔离级别,锁机制为其提供了保证。

15.4.1 锁的类型

1.共享锁
 共享锁的代号是S,时Share的缩写,共享锁的粒度时行或者元组(多个行),一个事物
 获取了共享锁之后,可以对锁定范围内的数据执行写操作。
2.排他锁
 排他锁的代号是X,是 eXclusive的缩写,排他锁的粒度与共享锁相同,也是行或者元组。一个
 事务获取了排他锁之后,可以对锁定范围内的数据执行写操作。
 如有两个事务A和B,如果事务A获取了一个元组的共享锁,事务B还可以立即获取这个元
 组的共享锁,但不能立即获取这个元组的排他锁,必须等到事务A释放共享锁之后
 如果事务A获取了一个元组的排他锁,事务B不能立即获取这个元组的排共亨锁,也不能立即
 获取这个元组的排他锁,必须等到A释放排他锁之后
3.意向锁
意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁(IS)和意向排他锁(ⅨX)两类。
意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”表示事务想执行操作
但还没有真 正执行。锁和锁之间的关系,要么是相容的,要么是互斥的。
锁a和锁b相容是指:操作同样一组数据时,如果事务t获取了锁a,另一个事务乜还可以获取锁b
锁a和锁b互斥是指:操作同样一组数据时,如果事务t获取了锁a,另一个事务t在t
释放锁a之前无法获取锁b。
其中共享锁、排他锁、意向共享锁、意向排他锁相互之间的兼容斥关系:
 Y表示相容,N表示互斥。
 - 参数 X S IX IS
 - X N N N N
 - S N Y N Y
 - IX N N Y Y
 - IS N Y Y Y
为了尽可能提高数据库的并发量,诶次锁定的数据范围越小越好,越小的锁其耗费
的系统资源越多,系统性能下降。
为在高并发响应和系统性能两方面进行平衡,产生了‘锁粒度(Lock granularity)’的概念。
15.4.2 锁粒度
锁的粒度主要分为表锁和行锁
表锁管理锁的开销最小,同时允许的并发量也是最小的锁机制。 MyISAM存储引擎使用该锁机制。
当要写入数据时,把整个表记录被锁,此时其他读/写动作一律等待。同时一些特定的动作,如
ALTER TABLE执行时使用的也是表锁。
行锁可以支持最大的并发。 InnoDB存储引擎使用该锁机制。如果要支持并发读/写,建议采用
InnoDB存储引擎,因为其是采用行级锁,可以获得更多的更新性能。
SELECT. LOCK IN SHARE MODE
此操作会加上一个共享锁。若会话事务中査找的数据已经被其他会话事务加上排他锁的话,共
享锁会等待其结束再加,若等待时间过长就会显示事务需要的锁等待超时。
SELE… ORUPDATE
此操作加上一个排他锁,其他会话事务将无法再加其他锁,必须等待其结束:
INSERT、 UPDATE、 DELETE
会话事务会对DML语句操作的数据加上一个排他锁,其他会话的事务都将会等待其释放排他锁
InnodB引擎会自动给会话事务中的共享锁、更新锁以及排他锁,需要加到一个区间值域时,再
加上个间隙锁或称为范围锁,对不存在的数据也锁住,防止出现幻写。
注意:以上语句描述的情况,与 MySQL所设置的事务隔离级别有较大关系。
当开启一个事务,InnoDB存储引擎会在更新记录上加行级锁,
此时其他事务是不可以更新被锁定的记录:
如:
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 UPDATE test_1 SET username='111_new'WHERE id=1;
 COMMIT;
 SELECT * FROM test_1;
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 UPDATE test_1 SET username='222_new'WHERE id=1;
 ERROR 1205(HY000):Lock wait timeout exceeded;try restarting transaction;
 此时,当有不同的事务更新同一条记录时,另外一个事务需要等待另一个
 事务把锁释放,此时查看MySQL中InnoDB
 存储引擎的状态:
 SHOW engine InnoDB status \G;
解析:
如:
‘MySQL thread id 5,OS thread Oxc60,query id 74 localhost 127.0.0.1 root Updating’
表示第二个事务的连接ID为5,当前状态为正在更新,同时当前正在更新的记录需要等待其他事务将锁解放。
当超过事务等待锁允许的最大时间,此时会提示“ERROR 1205(HY000):Lock wait timeout 
exceeded;try restarting transaction”及当前事务执行失败,则自动执行回滚操作。
InnoDB锁为行级锁,同时最小范围的锁定更新记录涉及的范围,注意此时WHERE
条件指定的列要有主键或者索引
 如:
 A事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 ALTER TABLE test_1 ADD UNIQUE KEY idx_id(id); 
 BEGIN;
 UPDATE test_1 SET username='111'WHERE id=1;
 COMMIT;
 SELECT * FROM test_1;
 B事务:
 SHOW VARIABLES LIKE 'tx_isolation'\G;
 Variable_name:tx_isolation
 Value:REPEATABLE-READ
 BEGIN;
 UPDATE test_1 SET username='2222'WHERE id=2;
 COMMIT;
 SELECT * FROM test_1;
 由于InooDB行级锁为间隙锁,加锁只是锁定需要的记录,因此B事务可以更新
 其他记录,两个事务之间互不影响

十六 MySQL安全性机制

16.1 MySQL软件所提供的权限

16.1.1 系统表mysql.user

 在系统数据库mysql中,存在一张非常重要的名为user的权限表,该表由39个字段,
 分为四类:
 用户字段、权限字段、安全字段、资源控制字段。
1.用户字段
 系统表mysql.user中用户字段包含三个字段,主要用来判断用户是否能够登陆成功。
 当用户登陆时,首先会到系统表mysql.user中判断用户字段,如果这三个字段能够
 同时匹配,则会被允许登陆,当创建新用户时,实际上会设置用户字段中包含的三个字段,
 当修改用户密码时,实际上会修改用户字段中的Password字段。
 - 用户字段
 - 用户字段名 含义
 - Host 主机名
 - User 用户名
 - Password 密码
2.权限字段
 系统表mysql.user中拥有一系列以“_prov”字符串结尾的字段,这些字段决定了
 用户的权限。以“_priv”字符串结尾的字段含义如下:
权限字段
 - user表中的列 权限名称 权限的范围
 - Create_priv CREATE 数据库、表或索引
 - Drop_priv DROP 数据库或表
 - Grant_priv GRANT OPTION 数据库、表、存储过程或函数
 - References_priv REFERENCES 数据库和表
 - Alter_priv ALTER 修改表
 - Delete_priv DELETE 删除表
 - Index_priv INDEX 用索引查询表
 - Insert_priv INSERT 插入表
 - Select_priv SELECT 查询表
 - Update_priv UPDATE 更新表
 - Create_view_priv CREATE VIEW 创建视图
 - Show_view_priv SHOW VIEW 查看视图
 - Alter_routine_priv ALTER ROUTINE 修改存储过程或函数
 - Create_routine_priv CREATE ROUTINE 创建存储过程或函数
 - Exceute_priv EXCEUTE 执行存储过程或函数
 - File_priv FILE 加载服务器主机上的文件
 - Create_tmp_table_priv CREATE TEMPORARY TABLES 创建临时表 
 - Lock_tables_priv LOCK TABLES 锁定表
 - Create_user_priv CREATE USER 创建用户
 - Process_priv PROCESS 服务器管理
 - Reload_priv RELOAD 重新加载权限表
 - Repl_client_priv REPLICATION CLIENT 服务器管理
 - Repl_slave_priv REPLICATION SLAVE 服务器管理
 - Show_db_priv SHOW DATABASES 查看数据库
 - Shutdown_priv SHUTDOWN 关闭服务器
 - Super_priv SUPER 超级权限
上述表中所展示的权限大致分为两大类:高级权限和普通权限,其中前者用于对数据库进行管理
,后者用于操作数据库
系统表mysql.user中的权限字段只能是Y和N,前者表示该权限可以用于所有的数据库上,
后者表示该权限不可以用于
所有的数据库上,这些字段的默认值都是N。
3.安全字段
 系统表mysql.user中的安全字段包含4个字段,主要用来判断用户是否能够登陆成功,
 - 用户字段
 - 用户字段名 含义
 - ssl_type 支持ssl标准加密的安全字段
 - ssl_cipher 支持ssl标准加密的安全字段 
 - x509_issuer 支持x509标准的字段
 - x509_subject 支持x509标准的字段
 在MySQL中,包含ssl字符串的字段主要用来实现加密,包含x509字符串的字段主要用来标识用户。
 查询是否支持ssl标注:
 SHOW VARIABLES LIKE 'have_openssl';
4.资源控制字段
 系统表mysql.user中资源控制字段包含4个字段,主要用来判断用户是否能够登陆成功;
 - 用户字段
 - 用户字段名 含义
 - max_questions 每小时允许执行多少次查询
 - max_updates 每小时允许执行多少次更新
 - max_connections 每小时可以建立多少次连接
 - max_user_connections 单个用户可以同时具有的连接数
 系统表mysql.user中的所有资源控制字段的默认值为0,表示没有任何限制

16.1.2 系统表mysql.db和mysql.host

 在系统数据库mysql中权限表除了表user外,还有表db和表host,
 这两张表都存储了某个用户对相关数据库的权限结构大致相同。
 分为用户字段和权限字段
1.用户字段
 系统表mysql.db中的用户字段包含3个字段:
 - 用户字段
 - 用户字段名 含义
 - Host 主机名
 - User 用户名
 - Db 数据库名
 系统表mysql.host时系统表mysql.db的扩展,包含两个字段:
 - 用户字段
 - 用户字段名 含义
 - Host 主机名
 - Db 数据库名
 当查找某个用户的权限,首先会从系统表mysql.db中查找,如果找不到Host
 字段的值,则会到系统表mysql.host去查找。
注意:在具体查找用户权限,系统表mysql.host很少被用到,通常系统表
 mysql.db中的数据记录就满足需求
3.权限字段
 - 用户字段
 - 用户字段名 含义 
 - Alter_routine_priv 修改存储过程或函数
 - Create_routine_priv 创建存储过程或函数 
 用户登录时,首先会到系统表mysql.user中判断用户字段,如果这3个
 字段能够同时匹配。则允许登陆,当创健新用户时,实际上会设置用户字段
 中所包含的3个字段,当修改用户密码时,实际上会修改用户字段中的Password字段。

16.1.3其他权限表

在系统数据库mysql中,还有表tables_priv和cloumns_priv。其中表tables_priv
用来实现单个表的权限设置,表clumns_priv用来实现单个字段列的权限设置。
1.系统表mysql.tables_priv的表结构
 执行DESC关键字段的SQL语句,查询tables_priv的机构:
 tables_priv包含8个字段:前四个分别代表主机名、数据库名、用户名和表名,
 字段Grantor表示权限是由谁设置的,
 字段Timestamp表示存储更新的时间。字段Table_priv表示对表进行操作的权限,
 其值可以是Select、Insert、Update、Delete、Drop、Grant、References、
 Index和Alter中的任意一项。字段Column_priv表示对表中字段列进行操作的权限,
 其值可以是Select、Insert、Update和References中的任意一项。
 DESC tables_priv \G;
2.系统表columns_priv的表结构
 DESC columns_priv \G;
 查询出来后发现,与系统表mysql.tables_priv相比,该表中多出了
 Column_name字段,该字段表示可以对哪些字段列进行操作。
3.系统表procs_priv的表结构
DESC procs_priv \G;
查询后得知 此表中包含8个字段,前三个分别表示主机名、数据库名、用户名。
字段Routine_name表示存储过程或函数的名称,字段Routine_type表示数据库对象类型,
其值只能是PROCESURE(存储过程)和FUNCTION(函数)之一,字段Grantor表示存储权限是
谁设置的。字段Proc_priv表示拥有的权限,其值可以是EXecute、Alter Routine和
Grant,字段Timestamp表示存储更新的时间。

16.2 MySQL软件所提供的用户机制

用户应该对所需的数据具有适当的访问权限,即用户不能对过多的数据库对象具有过多的访问
权,这是 MySQL软件的安全基础。为了实现数据的安全性和完整性,MySQL软件专门提供了一整
套用户管理机制。用户管理机制包括登录和退出MySQL服务器、创建用户、删除用户、修改用户
密码和为用户赋权限等内容
16.2.1 登陆和退出MySQL软件的完整命令
连接MySQL服务器的完整DOS命令:
 mysql -h hostname|hostIP -p port -u username -p DatabaseName -e "SQL语句"
解析:
- 参数-h:用来指定所连接MySQL服务器地址,可以用两种方式来表示。
 hostname表示主机名,hostIP表示主机IP地址
- 参数-p:用来指定所连接MySQL服务器端口号,由于安装时默认为3306,因此一般默认就是3306
- 参数-u:用来指定哪个用户要连接MySQL服务器
- 参数-p:表示将提示输入密码
- 参数DatabaseName:用来指定连接到MySQL服务器,登录到哪个数据库中,
 如没指定,默认为系统数据库mysql;
- 参数-e:用来指定所执行的SQL语句
 如:在DOS窗口中,通过用户账户root登陆到MySQL服务器的数据库company中:
 mysql -h 127.0.0.1 -u root -p company
 通过值127.0.0.1指定所连接MySQL服务器的地址,参数-u指定了登陆MySQL
 服务器的用户账户,参数-p表示会出现输入密码提示信息,最后只“company”
 指定了所连接的数据库。 
 如果想再具体连接中直接设置密码,而不会在输入密码提示中进行设置:
 mysql -h 127.0.0.1 -u root -proot company;
 参数-p制定了用户账户密码 
 注意:再具体连接MySQL服务器时,可以直接设置用户账户密码,不过改密码需要直接
 加在参数-p的后面,且中间绝对不能有空格。
 如:
 通过DOS窗口,用账户root登陆到MySQL服务器的数据库company中,同时执行
 查询表t_dept中所有的数据记录的
 SQL语句:
 mysql -h 127.0.0.1 -u root -p company -e "SELECT * FROM t_dept";
 退出MySQL服务器的DOS命令:
 EXIT|QUIT;

16.2.2 创建普通用户账户

默认的账户为root用户账户,但由于该账户拥有超级权限,每次操作使用此账户,不合适,
权限太大了,一般应严格杜绝
使用root账户登录MySQL服务器,仅在绝对需要使用时,而不应该在日常操作中使用该账户;
 在MySQL中有三种方式来创建普通用户:
1.执行CREATE USER 语句来创建用户账户
 CREATE USER username[IDENTIFIED BY [PASSWORD]'password']
 [,username[IDENTIFIED BY [PASSWORD]'password']]
 ... ...
 [,username[IDENTIFIED BY [PASSWORD]'password']]
 解析:关键字USER用来设置用户账户的名字,
 关键字IDENTIFIED BY 用来设置用户账户的密码,
 值username表示所设置的用户账户名,由用户名和主机名构成
 值password表示所设置的用户账户密码,如果是一个普通的字符串,
 则不需要关键字PASSWORD,该关键字
 主要用来实现对密码进行加密。
 实例:
 创建名为cjgong,密码为123456的用户账户:
 (1)通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 (2)创建用户账户:
 CREATE USER 'cjgong'@'localhost' IDENTIFIED BY '123456';
2.执行INSERT 语句来创建用户
因为系统权限表mysql.user中存储了关于用户账户的信息,因此可以通过向
该表插入数据记录来实现创建用户账户
当向系统表mysql.user中插入数据记录时,一般只需插入Host、User、Password
这三个字段值即可:
INSERT INTO user(Host,User,Password) VALUES('hostname',
'username',PASSWORD'password');
注意:在具体实现创建用户账号时,由于表mysql.user中字段ssl_cipher,
x509_issuer,x509_subject没有默认值,所以还需要设置这些字段的值,
对于字段Password的值,一定要使用PASSWORD()函数进行加密。
实例:
 创建名为cjgong,密码为123456的用户账户:
 (1)通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 (2)创建用户账户:
 INSERT INTO user(Host,User,Password,ssl_cipher,
 x509_issuer,x509_subject) VALUES
 ('localhost','cjgong1',PASSWORD('123456'),'','','');
 (3)执行FLUSH,使用户账户cjgong1生效:
 FLUSH PRIVILEGES;
3.执行GRANT语句创建用户
此前两种方法不便于为用户账号赋权限,于是MySQL又提供GRANT语句,
该语句不仅可以创建用户权限,而且还可以对其进行赋权限:
 GRANT priv_type ON databasename.tablename TO username
 [IDENTIFIED BY[PASSWORD] 'password']
 [,username[IDENTIFIED BY[PASSWORD] 'password']]
 ... ...
 [,username[IDENTIFIED BY[PASSWORD] 'password']]
参数priv_type表示用户实现设置所创建用户账号的权限;
参数databasename.tablename 表示所创建用户账号的权限范围,
即只能在指定的数据库和表上使用这些权限;
 其余部分与CREATE USER 语句一致;
实例:
创建名为cjgong2密码为123456的用户账户,且同时设置其只有SELECT权限:
(1)通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
(2)创建用户账户:
 GRANT SELECT ON company.t_dept TO 'cjgong2'@'localhost'
 IDENTIFIED BY '123456';

16.2.3 利用拥有超级权限用户root修改用户账户密码

 MySQL数据库管理系统中有以下三种方式可以实现修改密码:
 - 通过mtsqladmin命令修改root用户密码
 - 通过SET语句修改root用户密码
 - 通过更新系统表mysql.user数据记录修改root用户密码 
1.通过mysqladmin命令修改root用户密码
 mysqladmin -u username -p password 'new_password';
 参数u表示用户名 参数p表示密码,password不是关键字而是旧密码,
 参数new_password必须用双引号("")括起来;
 如:
 修改root的密码为123456:
 mysqladmin -u root -p password '123456';
 注意:在执行mysqladmin的过程中,只有输入正确的旧密码即可成功修改密码。
 mysqlc-h 127.0.0.1 -u root -p123456;
注意:在执行mysqadmin命令时,如果出现提示:‘mysqladmin’不是内部或外部
命令,也不是可运行的程序或批处理文件是由于MySQL软件安装目录里的path
路径没有添加到path环境变量里。
上述错误可以有两种办法解决:
(1) cd C:\program Files\MySQL\MySQL Server 5.0\bin 
 C:\program Files\MySQL\MySQL Server 5.0\bin 为MySQL软件的安装目录
(2)修改环境变量,添加路径 C:\program Files\MySQL
 \MySQL Server 5.0\bin到该环境变量中。
2.通过SET命令修改密码修改root用户密码
 SET PASSWORD=PASSWORD("new_password");
 上述命令中需要通过PASSWORD()函数来加密新密码new_password;
 如:
 修改root的密码为123456:
 通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 执行SET命令,修改root密码为123456:
 SET PASSWORD=PASSWORD("123456");
 校验:
 mysql -h 127.0.0.1 -u root -p123456;
3.更新系统表mysql.user数据记录修改root用户密码
 UPDATE user SET password=PASSWORD("new_password")
 WHERE user="root" AND host="localhost";
 更新user中字段password的值,条件为user="root" AND host="localhost";
 如:更新系统表user中的信息:
 通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 选择数据库mysql:
 USE mysql;
 修改root密码为123456:
 UPDATE USER SET PASSWORD =PASSWORD("123456")
 WHERE USER="root" AND HOST="LOCALHOST";
 检验:
 mysql -h 127.0.0.1 -u root -p123456;

16.2.4利用超级权限用户root修改普通用户账户密码

 三种方式:
 GRANT命令修改密码
 SET命令修改密码
 更新系统表mysql.user数据记录
 (1)创建一个名为cjgong,密码为cjgong的用户账户:
 GRANT SELECT,CREATE,OPEN ON *.* TO 'cjgong'@
 'localhost'IDENTIFIED BY 'cjgong'
 WHERE GRANT OPTION;
 解析:创建普通用户cjgong,密码cjgong;
1.通过GRANT命令修改cjgong用户密码
 GRANT priv_type ON database.table TO user[IDENTIFIED BY 
 [PASSWORD]'new_password']
 参数priv_type用来设置普通用户的权限。参数database.table用来
 设置用户的权限范围,参数user表示新用户账号由用户名和主机名构成,
 值new_password表示为用户设置的新密码;
 注意:参数new_password必须用双引号("")括起来;
 如:利用root 修改cjgong的密码为123456:
 通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 修改cjgong的密码为123456:
 GRANT SELECT,CREATE,OPEN ON *.* TO 'cjgong'@
 'localhost'IDENTIFIED BY '123456';
 校验:
 mysql -h 127.0.0.1 -u cjgong -p123456;
2.通过SET命令修改密码修改cjgong用户密码
 SET PASSWOED FOR 'username'@'hostname'=PASSWORD("new_password");
 如: 修改cjgong的密码为123456:
 通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 修改cjgong的密码为123456:
 SET PASSWORD FOR 'cjgong'@'localhost'=PASSWORD("123456");
 校验:
 mysql -h 127.0.0.1 -u cjgong -p123456;
3.更新系统表mysql.user数据记录修改cjgong用户密码
 UPDATE user SET password=PASSWORD("new_password")
 WHERE user="cjgong" AND HOST="localhost";
 更新user中字段password的值,条件为user="root" AND host="localhost";
 如:更新系统表user中的信息:
 通过用户root连接到MySQL服务器:
 mysql -h 127.0.0.1 -u root -proot;
 选择数据库mysql:
 USE mysql;
 修改cjgong密码为123456:
 UPDATE USER SET PASSWORD =PASSWORD("123456")
 WHERE USER="cjgong" AND HOST="localhost";
 检验:
 mysql -h 127.0.0.1 -u cjgong -p123456;
4.通过SET命令修改密码修改cjgong用户密码
 SET PASSWORD=PASSWORD("new_password");
 如: 修改cjgong的密码为123456:
 通过用户cjgong连接到MySQL服务器:
 mysql -h 127.0.0.1 -u cjgong -pcjgong;
 修改cjgong密码为123456:
 SET PASSWORD =PASSWORD("123456");
 检验:
 mysql -h 127.0.0.1 -u cjgong -p123456;

16.2.5删除普通用户账号

1.通过 DROP USER 语句删除普通用户
 语法:
 DROP USER user1 [,user2]...

 参数user:表示所要删除的用户
 再MySQL中,用户由用户名(user)和主机名(host)组成

 如:删除普通用户cjgong
 DROP USER 'cjgong'@'localhost';
2.删除系统表mysql.user 数据记录实现删除cjgong用户记录
 通过更新mysql.user的数据记录来修改root账户用户密码:
 DELETE FROM user WHERE user="cjgong" AND host="localhost";
 通过删除语句删除user中相应的数据记录,改数据记录的条件为
 user="cjgong" AND host=localhost;

 如:删除系统表user中的相应数据记录:
 1. 登录MySQL:
 mysql -uroot -proot;
 2. 选择数据库mysql;
 use mysql;
 3. 使用update 删除cjgong用户;
 DELETE FROM user WHERE user ="cjgong" AND host="localhost";

十七 MySQL日志管理

17.1 MySQL软件所支持的日志

日志文件用来记录每天的行为
日志操作是数据库维护中最重要的手段之一,因此当数据库遭到意外的损害时,
不仅可以通过日志查看出错的原因,还可以通过日志文件进行数据恢复。
再MySQL软件所支持的日志文件里,除了二进制日志文件外,其他日志文件
都是文本文件,默认情况下,MySQL只会启动错误日志文件,
而其他日志文件则需要手动启动。
- 二进制日志:该日志文件会以二进制形式记录数据库的各种操作,
 但是却不记录查询语句。
- 错误日志:该日志会记录MySQL服务器启动、关闭和运行出错等信息。
- 通用查询日志:改日志记录MySQL服务器启动和关闭信息、客户端的连接信息、
 更新数据记录SQL 语句和查询数据记录SQL语句
- 慢查询日志:记录执行时间超过指定时间的各种操作,通过工具分
 析慢查询日志可以定位
 MySQL服务器性能瓶颈所在。
启动日志,可以实现对MySQL服务器进行维护,但是会降低MySQL软件的执行速度

17.2 操作二进制日志

 二进制日志详细记录了数据库的变化情况,即SQL语句中的DDL和DML语句。
 但不包括数据记录查询操作。

17.2.1 启动二进制日志

 其操作包括:启动二进制日志、查看二进制日志、停止二进制日志和删除二进制日志
 默认情况下,二进制日志时关闭的:
 可通过设置MySQL服务器的配置文件my.ini来实现:
 [mysqld]
 log-bin [=dir\[filename]]
解析:参数dir:指定二进制文件的存储路径;参数filename指定二进制文件的文件名,
具体格式为filename.number,其中number的格式为000001、000002、000003等;
再具体启动时,如果没有设置参数dir和filename,二进制文件将使用默认文字:
主机名-bin.number;保存到默认目录———数据库文件里
PS:
每次重启MySQL服务器都会生成一个新的二进制日志文件,这些文件的文件名里,
filename部分不会改变,但是number的值会不断递增。
与二进制日志相关的文件除了保存内容的filename.number文件外,
还有一个关于二进制日志文件列表的文件filename.index。

如:修改MySQL软件的配置文件my.ini,启动二进制日志:
(1)打开文件my.ini配置文件,再[mysqld]组里面添加相应语句:
 [mysqld]
 log-bin
启动后没有配置文件的文件名,将在默认路径———数据库文件里创建一个
如:cjgong-bin.000001
(主机名-bin.000001)的文件,如果再次重启,将创建一个
filename部分不变,number值递增的文件(即cjgong-bin.000002)
(2)启动二进制日志时,最好不要与数据库的数据文件放在同一磁盘里,
这样当存放数据的磁盘收到破坏后,即可通过二进制日志文件进行回复,
打开文件my.ini配置文件,再[mysqld]组里添加相应语句:
[mysqld]
 log-bin=D:\mysqllog\binlog

17.2.2 查看二进制日志

 语法:
 mysqlbinlog filename.number 
 参数:filename.number表示所要查看的二进制日志文件

 如:
 (1)进入二进制文件目录。使用cd命令
 cd D:\mysqllog\binlog
 (2)执行mysqlbinlog命令,查看cjgong-bin.000001的二进制日志:
 mysqlbinlog cjgong-bin.000001
 (3)执行mysqlbinlog命令,查看cjgong-bin.000002的二进制日志:
 mysqlbinlog cjgong-bin.000002 

17.2.3 停止二进制日志

 如果想停止二进制日志文件功能,只需删除[mysqld]组里的log-bin内容即可
 但有时某些操作时不需要记录到二进制日志文件里,可以使用SET命令:
 SET SQL_LOG_BIN=0
 SET_SQL_LOG_BIN=1
 解析:当设置 SET SQL_LOG_BIN=0时,表示暂停二进制日志功能;
 当设置 SET_SQL_LOG_BIN=1时,表示重新开启二进制日志功能;
 PS:只有拥有SUPER权限的用户,才可以执行SET语句

17.2.4 删除二进制日志

 语法:
 1. 删除所有的二进制日志文件:
 RESET MASTER;
 2. 删除编号小于number的所有二进制日志文件:
 PURGE MASTER LOGS TO 'filename.number';
 3. 删除指定时间(yyyy-mm-dd hh:MM:ss)之间所创建的所有二进制日志文件:
 PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:MM:ss';

 如:删除编号小于000003的所有的二进制日志文件:
 PURGE MASTER LOGS TO 'cjgong-bin.000003';
 如:删除2012-6-7 20:05:01之前所创建的二进制日志文件:
 PURGE MASTER LOGS BEFORE '2012-6-7 20:05:01';
 如:删除所有的二进制日志文件:
 RESET MASTER;

17.3 操作错误日志

 错误日志包含启动错误日志、查看错误日志、停止错误日志和删除错误日志。

17.3.1 启动错误日志

 在MySQL中,错误日志默认是开启的。同时该类型的日志也是无法被禁止
如果想修改错误日志的存放目录,可以设置MySQL配置文件my.ini实现:
[mysqld]
 error-bin[=dir\[filename]]
 参数dir用来指定错误文件的存储路径,参数filename用来指定错误文件的文件名
 如果没有设置,则为默认(主机名.error)

17.3.2 查看错误日志

 可以直接使用文本文档查看。

17.3.3 删除错误日志

 语法:
 mysqladmin -u root -p flush-logs

17.4 通用查询日志

改日志记录MySQL服务器启动和关闭信息、客户端的连接信息、更新数据记录
SQL语句和查询数据记录SQL语句,如当前实例访问量较大,此日志会急剧增加,
会抢占系统IO导致影响MySQL性能,一般建议关闭此日志,需要时可以通过
设置环境变量打开

17.4.1 启动通用查询日志

通用查询日志包含启动通用查询日志、查看通用查询日志、停止通用查询日志
和删除通用查询日志。通过MySQL配置文件my.ini 来实现:
 [mysqld]
 log [=dir\[filename]]
参数dir用来指定通用查询日志文件的存储路径,参数filename用来指定
通用查询日志的文件名如果没有设置,则为默认(主机名.log)
不需要重启MySQL服务器即可生效的方法: 通过设置环境变量general_log 
进行通用查询日志的动态控制:

通过设置环境变量general_log进行通用查询日志的动态控制,on表示开启

set global general_log=on;
查看相关的环境变量
show variables like '%general_log%' \G;

17.4.2 查看通用查询日志

 可以直接使用文本文档查看。

17.4.3 停止通用查询日志

 两种办法:
 1. 配置文件my.ini文件实现:
 [mysqld]
 #log [=dir\[filename]]
 2. 不需要重启MySQL服务器即可生效的方法: 通过设置环境变量
 general_log 进行通用查询日志的动态控制:
 #通过设置环境变量general_log进行通用查询日志的动态控制,
 off表示关闭
 set global general_log=off;
 # 查看相关的环境变量
 show variables like '%general_log%' \G; 

17.4.4 删除通用查询日志

 语法:
 mysqladmin -u root -p flush-logs
 PS:在具体删除查询日志时,一旦执行mysqladmin命令,就会先
 删除旧的通用查询日志文件,然后创建一个新的查询日志

17.5 慢查询日志

主要记录执行时间超过指定时间的查询语句,通过查询该类型
文件可以找到哪些查询语句执行效率低,以便找出MySQL服务器
的性能瓶颈从而进行优化。

17.5.1 启动慢查询日志

1. 配置文件my.ini文件实现:
 [mysqld]
 log-slow-queries[=dir\[filename]]
 long_query_time=n
参数dir用来指定慢查询日志文件的存储路径,参数filename
用来指定慢查询日志文件的文件名,
具体格式:filename-slow.log。如果没有设置,默认为主机名-slow.log
参数n用来设置时间,该值的单位为秒,如果不设置long_query_time,默认为10秒
2. 设置环境变量slow_query_log,来控制慢查询日志的开启与停止
 #设置环境变量slow_query_log,来控制慢查询日志的动态控制,on表示开启
 set global slow_guery_log=on;
 #设置查询慢查询日志最大允许的时间,单位为秒
 set globe long_query_time=3;
PS:再通过环境变量开启慢查询日志时,slow_query_log针对当前登录的连接实时
生效,而long_query_time针对当前连接并不生效,是针对新增的连接有效,如需
启用修改后的变量值需要重新连接MySQL。

17.5.2 查看慢查询日志

 可以直接使用文本文档查看。

17.5.3 分析慢查询日志

 分析工具为:mysqldumpslow.pl
 常用参数:
 -s:为分析慢查询日志时指定排序参数,
 al:表示平均锁定时间
 ar:表示平均返回记录数
 at:表示平均查询时间
 -t:表示只显示指定的行数
 如:
 D:\MySQL\bin> mysqldumpslow.pl -s at -t 1
解析:使用mysqldumpslow.pl分析慢查询日志,"-s at" 表示将分析结果
按照平均查询时间排序,"-t 1":表示只显示符合条件的第1条;

17.5.4 停止慢查询日志

 1. 配置文件my.ini文件实现:
 [mysqld]
 #log-slow-queries[=dir\[filename]]
 #long_query_time=n
 2. 环境变量slow_query_log 
 #设置环境变量slow_query_log,来控制慢查询日志的动态控制,off表示开启
 set global slow_guery_log=off;

17.5.5 删除慢查询日志

通过windows删除命令直接将慢查询日志文件删除,然后使用一下命令重新创建对应文件:
#删除慢查询日志文件
 D:\MySQL\bin> del D:\MySQL\data\主机名-slow.log
# 重新刷新慢查询日志
 mysqladmin -u root -p flush-logs
作者:瞬间静默原文地址:https://www.cnblogs.com/yl9527/p/18659292

%s 个评论

要回复文章请先登录注册