MySQL 必知必会知识点总结 _必知必会

高斋晓开卷，独共圣人语。这篇文章主要讲述MySQL 必知必会知识点总结相关的知识，希望能为你提供帮助。
mysql selectfromwheregroup byhavingorder bylimit
使用mysql 连接

主机名----如果连接到本机则是 localhost
端口号----默认是3306
一个合法的用户名
用户口令

选择数据库

使用use 关键词----use database(数据库名称)；

查询数据库

show database;

查询表

show tables;

查询列信息

show columns from 表名；（它对每个列返回一行信息）

服务器状态查询

show status; （显示服务器状态信息）

显示其他信息

show grants; （用来显示授予用户的安全权限）
show errors; show warnings; (显示服务器错误或警告信息)

检索数据sql 不区分大小写一般关键词大写列和表名小写
查询单个列多个列

select 列名（*） from 表名；（返回所有行）

查询不想要值的列（去重 distinct ）

select distinct 列名 from 表名；（告诉mysql 只返回唯一的列）
不能部分使用 distinct 如果后跟多个列则都去重

Limit 限制结果

select 列 from 表 limit 5; (返回不多于5条)
select 列 from 表 limit 5,5; (返回第二个5条第一个5表示跳过5条)

使用完全限定名称查询

select 表名.列名 from 数据库名.表名；

排序检索数据排序数据（order by 默认升序使用 desc 降序排列）

select 列 from 表 order by 列；
select 列1，列2，列2 from 表 order by 列1 desc，列2；(列1 降序，列2升序)
如果多个需要降序在各个列后面添加desc
如果使用order by 和 Limit 结合要保证 order by 位于from 或 where后 limie位于order by 后

过滤数据 where

MySQL在执行匹配时是不区分大小写的所以where name=" aa" 和where name=" AA" 是相同的
between 两者之间(where id between 5 AND 10; 包括开始和结尾)
任何时候where 后使用OR 或者AND 都应该使用括号
一般使用IN操作符原因它比OR操作符要快直白

通配符 LIKE

百分号% 表示任意字符出现任意次数
下划线_表示匹配单个字符
缺点：搜索速度慢除非必须使用

正则表达式

使用 REGEXP 关键词替代 like 告诉MySQL 我后面所跟为正则表达式
select name where id REGEXP " .000" ; 其中.表示匹配任一字符
匹配过程不区分大小写如果需要可使用BINARY
where name REGEXP BINARY " Jet"
where id REGEXP " 1000|2000|3000" 搜索多个串之一
可使用 [123] 它和 [1|2|3] 一样
匹配范围 [1-5] [a-z]
匹配特殊字符使用双斜杠\\ \\-

创建计算字段拼接字段

将两值拼接构成单个值可使用Concat() 函数将俩列拼接
Concat() 需要一个或多个指定的串各个串之间用逗号隔开
例子：select Concat(name, \'(\' , Rtrim(id), \')\' ) from 表名；
其中 Rtrim 表示删除数据右侧空格
也可以使用别名关键词 AS

计算字段

select 3*2; 返回6 select NOW(); 返回当前时间
select 列1*列2 As a from 表名;

数据处理函数文本处理函数

Upper(列名) 转换大写
发音搜索 Soundex
例子：where Soundex(name) = Soundex(" Li hua" );

日期处理函数

不管是插入还是查询日期必须为 yyyy-mm-dd
DateDiff()返回两个时间的日期之差
AddDate() 增加一个日期
Time() 返回一个时间的时间部分
Date() 返回一个时间的日期部分

汇总数据聚集函数

AVG()函数返回某列的平均值
select AVG( 列 ) AS 别名 from 表名
也可以为select AVG( distinct 列 ) AS 别名 from 表名 where...( 去重 )
Count() 函数有两种使用方式
使用Count(*) 对表中行的数目进行计数不管表列中包含的是null还是非null值
select count(*) from 表；返回所有行不管行中各列有什么值
使用Count(列) 对特定列中的具有值的行进行计数忽略null值
select count(email) from 表；对表中有email的客户进行计数
MAX() 函数返回指定列中最大值包括日期
MIN() 函数返回指定列中最小值包括日期
Sum() 返回指定列值的和

分组数据创建分组

使用group by 关键词位于where之后 order by 之前
一般在group by 之后跟having 或者跟order by 保证数据正确

过滤分组 having

having 和 where区别 having 过滤分组 where过滤行
having 数据分组后过滤 where 分组前过滤
select id,count(*) AS aa from 表 GROUP BY id;

联结表外键

为某个表中的一列，它包含另一张表的主键值

可伸缩性

能够适应不断增加的工作量而不失败，设计良好的数据库或应用程序称之为可伸缩性好

创建联结

规定要联结的所有表以及他们如何关联即可
例子：select v.name,p.name,p.price from v,p where v.id=p.id order by v.name,p.name;
以上查询两张表 from两张表最后把他们的联结关系对应，然后进行排序
联结条件的时候要使用完全限定名

笛卡尔积

由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目由两张表的乘积
所以不要忘记where子句

内部联结

目前为止所用的是等值联结也称为内部联结，它基于两张表之间的相等测试
例子：select v.name, p.name, p.price from vINNER JOIN p ON v.id = p.id;
传递给ON 的条件和 where 条件相同
一般使用内部联结较多方便程序员不忘记联结条件

联结多个表

首先列出所有表，然后定义表之间的关系
例子：selecta.name,b.name,c.namefrom a,b,cwhere a.id = b.idAND b.id = c.id;
性能：不要联结没有不必要的表

创建高级联结使用表别名-----AS

缩短SQL语句
允许在单条select语句中多次使用相同的表
例子：selectstudent.name,teacher.name,person.namefrom student AS s,
teacher AS t,person AS pwhere s.id = t.idAND t.id = p.id;
例子二--应用检索语句： < 还可以应用 ORDER BY ，语句其他部分> 
selects.name,t.name,p.namefrom student AS s,
teacher AS t,person AS pwhere s.id = t.idAND t.id = p.id;

自联结

如果你发现某物品（其ID 为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他
物品是否存在问题------首先要找到生产该物品的厂家，然后再找出其他物品
 使用子联结查询
select prod_id ,prod_name FROMproductsWHEREvend_id =
（select vend_idFROM products WHERE prod_id=" DTNTR" );
【MySQL 必知必会知识点总结】 使用自联结查询
select p1.prod_id , p1.prod_name FROM products AS p1, products AS p2
WHERE p1.prod_id = p2.prod_id AND p2.prod_id = " DTNTR" ;

外部链接

例子：selectc.id , o.num FROM customers AS cLEFT OUTER JOIN orders AS o
ON c.id = o.id;
在使用OUTER JOIN必须使用 LEFT 或者 RIGHT 指定包括所有行的表

使用带聚集函数的连接

SELECT c.id , c.name , COUNT(o.num)AS num FROM customers AS c LEFT OUTER JOIN orders AS o ON c.id = o.idGROUP BY c.id;

小结

一般使用内部联结
保证正确的联结条件，否则返回不正确的数据
应该总是提供联结条件否则出现笛卡尔积

组合查询组合查询

MySQL 也允许执行多个查询并将结果作为单个查询结果集返回，这些组合查询
通常称为并或复合查询
两种情况需要用到组合查询
- 在单个查询中从不同的表返回类似结构的数据
- 在对单个表执行多个查询，按单个查询返回数据

组建组合查询

利用UNION 关键词，可给出多条select语句，将它们的结果作为结果集

使用UNION

使用它很简单，只需要在多条select中间放上UNION
例子：假设需要价格小于等于5的所有产品的一个列表，而且还想提供包括
供应商1001和1002生产的所有产品
select v_id, p_id, p_price FROM products WHERE p_price < =5
UNION
select v_id, p_id, p_price FROM products WHERE v_id IN（1001，1002）；

UNION 规则

UNION 必须由两条或者多条select语句组成，语句之间用UNION分割
UNION 中的每个查询必须包含相同的列，表达式或聚集函数
列数据类型必须兼容:类型不必完全相同，但必须DBMS可以隐含的转换的类型

包含或取消重复的行

重复的行自动取消，如果需要可使用UNION ALL 而不是UNION

排序

在使用UNION 组合查询时，只能使用一条ORDER BY 子句，他必须出现在
最后一条 selcet 语句之后（实际上对整个结果集排序）

全文本搜索概念

MySQL 中MyISAM引擎支持全文本搜索
性能 — 通配符Like 和正则表达式匹配通常都会要求MySQL尝试匹配所有行，
因此这样非常耗时（因为极少使用索引）
因为全文本搜索会使MySQL 创建指定列中各词的一个索引，搜索可以针对
这些词进行，比较快。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的
重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新
索引。在索引之后 SELECT 可与 Match() 和 Against() 一起使用执行搜索。

启动全文本搜索支持

创建表的时候启用全文本搜索，create table 接受 FULL TEXT ，它给出被索
引列的一个逗号分割的列表。

CREATE TABLE productbotes ( note_id int NOT NULL AUTO_INCREMENT, //自增 prod_id char(20) NOT NULL, note_date datetime NOT NULL, note_text text NULL, PRIMARY KEY(note_id),//主键 FULLTEXT (note_text)//索引 )ENGINE = MyISAM;

表中有个note_text 列，MySQL根据子句FULLTEXT（note_text）对其索引
这里 FULLTEXT 索引单个列，也可以索引多个列。定义索引之后MySQL会
自动维护索引。在增加，删除，更新行时，索引也会随之更新。
可以在创建表时指定索引，也可之后再指定但必须针对已有数据立即索引
不要在导入数据时使用FULLTEXT，先导入数据再启用索引
再索引之后，使用两个函数 Match() , Against() , 进行全文本搜索，其中
Match() 指定被搜索的列，Against() 指定要使用搜索的表达式。

SELECT note_text FROM productbotes WHERE Match(note_text) Against("rabbit");

解析：此检索语句只查询 note_text 列Match(note_text) 针对指定的列搜索，
Against(" rabbit" ) 作为搜索文本返回两行包括 rabbit 的内容。
传递给Match() 函数的参数必须与 FULLTEXT 内容相同，如果指定多个列
则必须列出他们（而且次序相同）
搜索不区分大小写，除非使用BINARY
全文本搜索重要部分----对结果排序，具有较高级别的行先返回

SELECT note_text Match(note_text) Against("rabbit") AS rank FROM productnotes;

上面例子rank 列会显示等级值（根据包含 rabbit 个数和它所在的行数）
根据等级制排序越高越靠前显示

查询扩展

首先全文本搜索要查询词的行
MySQL 匹配这些行并且选出有用的词
再次进行全文本搜索不仅使用原本的还使用有用的词

SELECT note_text FROM productnotes WHERE Mctch(note_text) Against("anvils" WITH QUERY EXPANSION); //使用关键词扩展查询

布尔文本搜索

要匹配的词
要排斥的词
排列提升
表达式分组
即使没有FULLTEXT 也可以使用

//匹配包含 heavy 词但不包含任意以 rope 开始的词的行 SELECT note_text FROM productnotes WHERE Mctch(note_text) Against("heavy -rope*" IN BOOLEAN MODE); //- 代表排除 * 代表截断

全文本搜索注意事项

在索引全文本数据时，短词（3个或以下的字符）被忽略且从索引中排除
MySQL 带有一个内建的非用词，往往在索引全文本时总是被忽略
一个词语超过50%的行中，视为一个非用词
如果表中的行数少于3 行，则全文本搜索不返回结果
忽略词的单引号

插入数据使用方式

INSERT 用来插入（或添加）行到数据库中
插入完整的行
插入行的一部分
插入多行
插入某些查询的结果

插入完整的行

这种方式不安全

INSERT INTO Students VALUES(NULL,//此处id会自动增量 "LiHua",//没有值的列应该使用NULL（前提该列允许NULL） 28, "AnHui");
安全但繁琐的插入
优点：即使表的结构发生改变也能正常使用
必须每一个列都要给默认值

INSERT INTO students(id, name, age, address) VALUES(NULL, "LiHua", 28, "AnHui"); //降低 INSERT 语句的优先级目的：为了高性能的 SELECT INSERT LOW_PRIORITY INTO...

插入多个行

INSERT INTO students(id, name, age, address) VALUES(NULL, "LiHua", 24, "ShangHai" ),(//每组值用括号括起来逗号分开 NULL,//使用单条语句插入要比多条语句插入性能要快 "zhangsan", 26, "ANHUI" );

插入检索出的数据

/*例子：把 custnew 表中的数据导入到 customers 应该首先创建和填充custnew 表不应该使用在 customers 中使用过的cust_id 值防止后续插入错误*/INSERT INTO customers(cust_id, cust_contact, cust_name) SELECT cust_id, cust_contact, cust_name FROM custnew; /* SELECT 中列出的每个列对应 customers 后所跟列（也可以不用两张表列名相同只需对应各自位置即可，其中select 语句可跟WHERE 筛选）例子中导入了 cust_id 前提是确保 cust_id 不重复也可以不导入此列*/

更新数据----Update 更新数据

更新表中所有的行
更新表中特定的行
注意：不要忽略 WHERE 子句，稍不注意就会更新表中所有行
安全：可以限制和控制 UPDATE 语句的使用

组成部分

要更新的表
列名，和它的新值
确定要更新行的过滤条件

UPDATE personSET name = "刘德华"WHERE id = 111; /*更新多个列中间用逗号隔开*/UPDATE personSET name = "刘德华"，age = 27 WHERE id = 111;

在 UPDATE 语句中使用子查询用以更新需要的数据
IGNORE 关键词
如果用 UPDATE 语句更新多列时，在更新的过程中如果发生错误，
则整个 UPDATE 操作被取消（错误发生去更新的数据回复原样）
 即使发生错误需要继续执行的时候使用IGNORE 关键词

UPDATE IGNORE person.....

删除某列的值

UPDATE person SET name = null/* 可以设置其为 NULL*/WHERE id = 111;

删除数据 DELETE

从表中删除特定的行
从表中删除所有的行
不要忽略 WHERE 语句

DELETE FROM personWHERE id = 111; /*删除 id 为 111 的一行数据*/

删除所有行TRUNCATETABLE

不要使用 DELETE 因为 DELETE 是逐条删除，而 TRUNCATETABLE
是删除表，再重新创建表，速度更快

注意事项

除非确实需要删除或者更新每一行，否则不要忽略 WHERE
保证每个表都有主键
在对表进行更新和删除时，应该先检索下数据是否正确
使用强制实施引用完整性的数据库，这样MySQL 将不允许删除
具有与其他表相关联的数据的行

创建和操作表创建表

使用交互式的工具
使用MySQL 语句

创建表的语句

新表的名字，必须在 CREATE TABLE 之后
表列的名字和定义，用逗号隔开

CREATE TABLE person/*如果想在表不存在创建表则只需要在表名后添加 IF NOT EXISTS */( per_idintNOT NULLAUTO_INCREMENT, /*自增*/per_name var(20)NOT NULL,per_ageintNOT NULL,PRIMARY KEY (per_id)/*定义主键*/) ENGINE = InnoDB;

主键再介绍

PRIMARY KEY ( 列名 ) ;
如果主键使用单个列，则它的值必须唯一。
PRIMARY KEY ( 列名，列名 ) ;
如果使用多个列，则这些列的组合值必须唯一。
解析：每个订单有很多物品，都要第一个物品，第二个物品
所以订单号和订单物品组合作为主键
主键只能使用不允许 NULL 值的列

使用 AUTO_INCREMENT

每个表只允许一个自增列，而且他必须被索引
如果插入使用新的值，则以后的数据按照此值自增

SELECT last_insert_id() /*返回最后一个 AUTO_INCREMENT 值*/

指定默认值

如果在插入行时没有给出值，MySQL 允许指定此时使用的默认值

CREATE TABLE person(id intNOT NULL AUTO_INCREMENT,age intNOT NULL DEFAULT 25,/*默认25岁 */name var(20) NOT NULL,PRIMARY KEY(id))ENGINE = InnoDB;

引擎

InnoDB 是一个可靠的事务处理引擎，不支持全文本搜索。
MEMORY 在功能上等同于 MyISAM ，但由于数据存在内存，速度快（适合临时表）
MyISAM 是一个性能极高的引擎，支持全文本搜索，不支持事务处理
 使用一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

在 ALTER TABLE 之后给出要更改的表名
所做更改的列表

ALTER TABLE personADD height int NOT NULL;

复杂的表结构更改需要注意：

用新的列布局创建一个新表
使用 INSERT SELECT 语句，从旧表复制数据到新表
检验包含所需数据的新表
重命名旧表
用旧表原来的名字重命名新表
根据需要创建触发器，存储过程，索引，外键

删除表

DROP TABLE person;

重命名表

RENAME TABLE person TO persons;

视图概念

视图是虚拟的表，视图只包含使用时动态检索数据的查询。

使用视图的好处

重用 SQL 语句
简化复杂的 SQL 操作
使用表的组成部分而不是整个表
保护数据 ------- 可以给用户授予表的特定部分的访问权限而不是整个表
更改数据格式和表示 ------- 视图可返回与底层表的表示和格式不同的数据

视图的规则和限制

与表一样，视图名称必须唯一
视图数量没有限制
为了创建视图，必须有足够的访问权限
视图可以嵌套
ORDER BY 可运用在视图中，但如果运用视图检索的语句中有ORDER BY
则视图的ORDER BY 会被覆盖
视图不能索引，也不能有触发器或默认值
视图可以和表一起使用 ------ 编写一个视图和表的联结查询

使用视图

视图使用 CREATE VIEW 语句创建
使用 SHOW CREATE VIEW 视图名 ; 来查看创建视图的语句
用 DROP 删除视图， DROP VIEW 视图名；
更新视图的时候，可以先用DROP 再用 CREATE ,也可以直接用 CREATE OR
REPLACE VIEW ，如果更新的视图存在，则第二条更新语句会替换原有视图，
如果更新的视图不存在，则第二条更新语句会创建一个视图

SELECT cust_name,cust_contact/*三表联查*/ FROM customers, orders,orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = "TNT2"; /*创建视图*/ CREATE VIEW productcustomers AS/*使用 CREATE VIEW 创建视图*/ SELECT cust_name,cust_contact FROM customers, orders,orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num /*进行查询*/ SELECT cust_name,cust_contact FROM productcustomers/*作为视图，不包含表中数据，包含的是SQL查询*/ WHERE prod_id = "TNT2"; /*如果原表更改则视图返回更改之后的数据*/

用视图重新格式化检索出的数据

CREATE VIEW good AS SELECT Contact(RTrim(vend_name),"(," RTrim(vend_country),")") AS vendors ORDER BY vend_name;

使用视图过滤不想要的数据

CREATE VIEW well AS SELECT id,name/*如果有两个WHERE语句则会组合（一组在视图中一组在查询中）*/ WHERE age> 18;

使用视图与计算字段

CREATE VIEW gg AS SELECT name , age*height FROM person;

更新视图

视图是可更新的（可以对他们使用 INSERT UPDATE 和 DELETE）
如果视图中包含以下操作不能更新视图
一般不需要更新因为视图用于查询比较多
- 分组（使用 GROUP BY 和 HAVING）
- 联结
- 子查询
- 并
- 聚集函数（ MIN() ,Count(),Sum() 等）
- DISTINCT
- 导出列

使用存储过程存储过程

把多条语句封装成一个集合

使用存储过程的好处

简化复杂的操作
假设都使用此存储过程，数据的完整性
如需更改表名，只需要更改存储过程其代码
使用存储过程要比使用单独的SQL 语句要快

创建存储过程

CREATE PROCEDURE productpricing()/* 存储过程名为 productpricing 如果接受参数则需在括号定义*/ BEGIN SELECT Avg( prod_price ) AS priceaverage FROM products; END; /* BEGIN 和 END 定义存储体 *//* 由于MySQL语句分隔符和命令行实用程序都为\' ; \'这可能出现错误 */ DELIMITER ///*告诉命令行实用程序使用 // 作为新的语句分隔符*/CREATE PROCEDURE productpricing() BEGIN SELECT Avg( prod_price ) AS priceaverage FROM products; END//DELIMITER ; /* 恢复原来的语句分隔符 */

使用存储过程

CALL productpricing(); /*调用无参*/

删除存储过程

DROP PROCEDURE IF EXISTS productpricing; /*仅当存在时删除，后面没有括号*/

使用参数

存储过程不显示结果，而是把结果返回给你指定的变量

CREATE PROCEDURE productpricing ( OUT pl DECIMAL(8,2), /* OUT 从存储过程传出给调用者 MySQL 还支持 IN 传递给存储过程*/OUT ph DECIMAL(8,2), /* INOUT 对存储过程传入和传出类型的参数 */OUT pa DECIMAL(8,2))BEGIN/* 存储过程的代码位于 BEGIN 和 END */ SELECT Min(prod_price)INTO pl/*存储最低价格通过 INTO 保存在指定的变量*/ FROM products; SELECT Max(prod_price)INTO ph/*存储最高价格*/FROM products; SELECT Avg(prod_price)INTO pa/*存储平均价格*/FROM products; END; /* 调用它 */CALL productpricing(@pricelow@pricehigh@priceaverage); /*调用有参他们是保存结果的三个变量所有MySQL变量都以@开头*//* 调用后不显示任何结果 */SELECT @pricelow ; /* 或者 */SELECT @pricelow, @pricehigh, @priceaverage;

使用 INOUT传入传出参数

CREATE PROCEDURE ordertotal(IN onumber INT,/* IN 代表 onumber 被传入存储过程 */OUT ototal DECIMAL(8,2)/* OUT 代表 ototal 被传出存储过程 */)BEGINSELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = numberINTO ototal; /* INTO 使用ototal存储计算出来的合计 */END; /*调用*/CALL ordertotal(2005,@total); /*第一个参数为订单号，第二个参数为包含计算出来的合计的变量名*/SELECT @total;

智能存储过程

CREATE PROCEDURE ordertotal(IN onumber INT,IN taxable BOOLEAN,/*添加一个boolean参数*/OUT ototal DECIMAL(8,2))COMMENT "Obtain order total,optionally adding tax " /*comment 不是必须的加上将可以在*/BEGIN/*SHOW PROCEDURE STATUS 的结果中显示*//*定义两个局部变量*/DECLARE total DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6; /*默认6%*/SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumberINTO total; /*暂时保存局部变量*/IF taxable THENSELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal; END; /*调用*/CALL ordertotal(2021,0,@ototal); /* 0 表示假 1表示真*/SELECT @ototal;

检查存储过程

SHOW CREATE PROCEDURE ordertotal; /*为了获取何时由谁创建等信息*/

游标概念

有时需要对检索出来的行中前进或者后退一行或多行
存储在MySQL 服务器上的数据库查询
主要用在交互式应用，其中用户需要滚动屏幕上的数据

使用游标

声明游标，只是定义要使用的SELECT 语句
打开游标，把之前定义的SELECT 语句检索出来
对于填有数据的游标，根据需要取出各行
关闭游标

创建游标

CREATE PROCEDURE processorders()BEGINDECLARE o INT; /*定义变量*/DECLARE ordernumbers CURSOR/*定义和命名游标*/FOR SELECT order_num FROM orders; OPEN ordernumbers; /*打开游标会执行查询 */FETCH ordernumbers INTO o; /*使用 FETCH 将检索的数据自动从第一行开始到O这个局部变量*/CLOSE ordernumbers; /*关闭游标*/END;

触发器概念

某个表发生改动时，自动处理
响应以下任意语句自动执行

DELETEINSERTUPDATE/*其他语句不支持触发器*/

创建触发器

唯一的触发器名
触发器关联的表
触发器应该响应的活动（增删改）
触发器何时执行
只有表才支持触发器
保证每个数据库的触发器名唯一
每个表每个事件每次只允许一个触发器，所以一张表只有6个触发器
( INSERT UPDATE DELETE 在他们之前之后发生 )
单一触发器不能与多个事件或多个表关联

/*CREATE TRIGGER 创建在Insert products 后每一行只要插入就会出现 "Product added" */ CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT "Product added";

删除触发器

DROP TRIGGER newproduct; /*触发器不能更新，只能先删除后创建*/

INSERT 触发器

在 INSERT 触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）。
对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含0，在 INSERT
执行之后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders /*创建neworder 触发器在AFTER INSERT 之后执行*/ /*创建一个NEW 表，每次插入就会在order_num 插入值，触发器从NEW.order_num取得值并返回它*/ FOR EACH ROW SELECT NEW.order_num;

通常 BEFORE 用于验证数据正确性，保证插入值正确### DELETE 触发器- ## 事务### 事务处理- InnoDB 引擎支持事务处理 - 维护数据的完整性，保证成批的MySQL 要么完全执行，要么完全不执行### 概念- 事务处理：是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者不完全执行，如果发生错误则进行回退，恢复数据库到某个已知且安全的状态，如果不发生错误整组语句提交到数据库### 名词- 事务：指一组SQL语句transaction - 回退：指撤销指定SQL语句的过程rollback - 提交：指将未存储的SQL语句写入数据库commit - 保留点：指事务处理中设置的临时占位符savepoint### 控制事务处理~~~mysql SELECT * FROM ordertotals; START TRANSACTION; /*标志事务的开始*/ DELETE FROM ordertotals; SELECT * FROM ordertotals; ROLLBACK; /*回退 START 之后的所有语句（用来撤销MySQL语句）只能回退增删改*/ SELECT * FROM ordertotals;

使用COMMIT

START TRANSACTION; DELETE FROM orderitems WHERE order_num = 2021; /*因设计两表的语句使用事务*/ DELETE FROM orders WHERE order_num = 2021; COMMIT; /*仅在两条语句不出错的情况下提交*/

使用保留点

为了回退部分事务，需要在合适的地方添加占位符即保留点。

SAVEPOINT delete1; /*设置保留点保证名称唯一*/ ROLLBACK TO delete1; /*回退到保留点*/

更改默认的提交行为

SET autocommit = 0; /*0表示关闭自动提交*/

客户权限访问控制

用户对他们的数据具有适当的访问权，不能多也不能少
防止无意的错误
不要使用 root

管理用户

USE mysql; /*MySQL 用户账户和信息存储在名为mysql 的数据库中*/ SELECT user FROM user; /*user 表存储所有用户账号，user列存储用户登录名*/

创建用户账号

CREATE USER ben IDENTIFIED BY \'P@$$WOrd\';

重新命名用户账号

RENAME USER ben TO bforta;

删除用户账号

DROP USER bforta;

设置访问权限

查看用户权限（一般新建的用户没有访问权限）

SHOW GRANTS FOR bforta;

设置权限需要使用 GRANT ，并且给出以下信息
- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名

GRANT SELECT ON crashcourse.* TO bforta; /*允许bforta 在crashcourse数据库上所有表使用查询*/

撤销用户权限

REVOKE SELECT ON crashcourse.* FROM bforta;

注意事项

当某个数据库或者表被删除时权限仍然存在，如果将来重新创建数据库或表权限仍然起作用

更改口令

SET PASSWORD FOR bforta = Password(\'root\'); /*新口令必须到Password()函数加密*/ /*设置当前登录用户的口令*/ SET PASSWORD = Password(\'root\');

数据库维护诊断启动问题

--help显示帮助
--safe-mode 装载减去某些最佳配置的服务器
--verbose 显示全文本消息
--version 显示版本消息然后退出

查看日志
可用 FLUSH LOGS 用来刷新或重新开始所有日志文件

错误日志：包含启动和关闭问题以及任意关键错误的信息
日志通常名为 hostname.err 位于data目录中，此日志名可
用--log--error命令行选项更改
查询日志
它记录所有MySQL活动，在诊断问题时非常有用，通常命名
hostname.log，位于data目录中
二进制日志
它记录更新过数据的所有语句命名为hostname-bin 位于data
 缓慢查询日志
记录查询缓慢的日志后期优化很有用，命名 hostname-slow.log
位于data目录